CODEWITHSUNDEEP
python-2.7google-cloud-platforminsertgoogle-bigquerytry-catch
Kaustubh Ghole
Kaustubh Ghole

Reputation: 577

Unable to insert data into existing BigQuery Table?

I am trying to insert some data into bigquery table which is already exists. But I am unable to get that data into the table.

I tried standard example provided by google (insert_rows) but no luck. I have also referred this:https://github.com/googleapis/google-cloud-python/issues/5539 I have tried passing this data as list of tupples as well but same issue with that too.

from google.cloud import bigquery
import datetime
bigquery_client = bigquery.Client()
dataset_ref = bigquery_client.dataset('my_dataset_id')
table_ref = dataset_ref.table('my_destination_table_id')
table = bigquery_client.get_table(table_ref)
rows_to_insert = [
    {u'jobName': 'writetobigquery'},
    {u'startDatetime': datetime.datetime.now().strftime('%Y-%m-%d-%H%M%S')},
    {u'jobStatus': 'Success'},
    {u'logMessage': 'NA'},
]
errors = bigquery_client.insert_rows(table, rows_to_insert)

When I execute this, I don't get an error, but its not writing anything into table. It will be really great if anyone suggested something that would work for me. Thank You!

Upvotes: 0

Views: 1763

Answers (2)

itroulli
itroulli

Reputation: 2094

After making some modifications on your code I could make it work as expected. I changed your row from being a list of dictionaries of one value each to be a dictionary with all the columns in one row. I also changed the datetime format as it was invalid for BigQuery (valid format can be found here). So the following snippet should work fine:


from google.cloud import bigquery
import datetime

bigquery_client = bigquery.Client()
dataset_ref = bigquery_client.dataset('my_dataset_id')
table_ref = dataset_ref.table('my_destination_table_id')
table = bigquery_client.get_table(table_ref)
rows_to_insert = [
    {u'jobName': 'writetobigquery',
    u'startDatetime': datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
    u'jobStatus': 'Success',
    u'logMessage': 'NA'}
]
errors = bigquery_client.insert_rows(table, rows_to_insert)
print "Errors occurred:", errors

Upvotes: 1

saifuddin778
saifuddin778

Reputation: 7277

Shouldn't your rows be a list of dictionaries? I assume your table schema is like jobName, startDatetime, jobStatus, logMessage, then:

rows_to_insert = [
    {
      u'jobName': 'writetobigquery',
      u'startDatetime': datetime.datetime.now().strftime('%Y-%m-%d-%H%M%S'),
      u'jobStatus': 'Success',
      u'logMessage': 'NA'
    }
]
errors = bigquery_client.insert_rows(table, rows_to_insert)

Upvotes: 0

Related Questions