Insert a row in to google BigQuery table from the values of a python list

Question

I am a newbie who is exploring Google BigQuery. I would like to insert a row into the BigQuery table from a python list which contains the row values.

To be more specific my list looks like this: [100.453, 108.75, 50.7773]

I found a couple of hints from BigQuery-Python library insert and also looked in to pandas bigquery writer but not sure whether they are perfect for my usecase.

What would be the better solution?

Thanks in advance.

Solution 1

To work with Google Cloud Platform services using Python, I would recommend using python google-cloud and for BigQuery specifically the submodule google-cloud-bigquery(this was also recommended by @polleyg. This is an open-source Python idiomatic client maintained by the Google. This will allow you to easily use all the google cloud services in a simple and consistent way.

More specifically, the example under Insert rows into a table’s data in the documentation shows how to insert Python tuples/lists into a BigQuery table.

However depending on your needs, you might need other options, my ordering of options:

1. If the you use code that has a native interface with Google Services (e.g. BigQuery) and this suits your needs, use this. In your case test if Pandas-BigQuery works for you.
2. If your current code/modules don't have a native interface, try the Google maintained idiomatic client google-cloud.
3. If that doesn't suit your needs, use an external idiomatic client like tylertreat/BigQuery-Python. The problem is that you will have different inconsistent clients for the different services. The benefit can be that it adds some functionalities not provided in the google-cloud module.
4. Finally, if you work with very new alpha/beta features, use the APIs directly with the Google API module, this is will always give you access to the latest APIs, but is a bit harder to work with. So only use this if the previous options don't give you what you need.

Solution 2

Lot's of resources but I usually find code examples to be the most informative for beginning.

Here's an excellent collection of bigquery python code samples: https://github.com/googleapis/python-bigquery/tree/master/samples.

One straight forward way to insert rows:

from google.cloud import bigquery
bq_client = bigquery.Client()
table = bq_client.get_table("{}.{}.{}".format(PROJECT, DATASET, TABLE))

rows_to_insert = [{u"COL1": 100.453, u"COL2": 108.75, u"COL3": 50.7773}, {u"COL1": 200.348, u"COL2": 208.29, u"COL3": 60.7773}]

errors = bq_client.insert_rows_json(table, rows_to_insert)
if errors == []:
    print("success")

Lastly to verify if it's inserted successfully use:

bq query --nouse_legacy_sql 'SELECT * FROM `PROJECT.DATASET.TABLE`'

Hope that helps everyone!

Comments

[Rakesh Vidya Chandra]

Thanks @Fematich for the elaborative list of resources. My main intention was to know on how I can insert the values from the "Python list" in to a row in the BigQuery table. With respect to the libraries you mentioned, to insert a row, the data should be in a JSON format but in my condition I am dealing with a "Python list". This is adding an extra effort for every iteration to convert the data.

[Fematich]

Did you try the example under Insert rows into a table’s data in the google-cloud-bigquery docs? That shows an example using Python tuples/lists.

[Rakesh Vidya Chandra]

Hello @fematich, I already tried it, but not successful in inserting it as it turned to be working erroneously for which I have updated the Google team on. Regret for the late response.

[Rakesh Vidya Chandra]

Could you please update your answer with "Insert rows into a table’s data" with google cloud bigquery module, so that I can accept your answer. For your information, I have recently received a response from google team that the bug has been fixed.

[Fematich]

Thank you for the update! I just changed my response.

[Nick Settje]

FYI, google-cloud-bigquery is incompatible with Google App Engine. Instead, you must use the poorly documented Jobs Insert API.

[confiq]

thank you! more explicit example: github.com/googleapis/python-bigquery/blob/master/samples/…