CODEWITHSUNDEEP
google-bigquery
Raina
Raina

Reputation: 29

Bigquery insert - multiple rows at a time from python dict

How do I insert a python dictionary with 5k rows into bigquery? I used the documentation at https://github.com/GoogleCloudPlatform/python-docs-samples/blob/master/bigquery/api/streaming.py but I am able only to insert 1 row at a time.

How can insert all the 5k rows in the dictionary at a time? If I use a pandas dataframe to insert, I get the error NotImplementedError: Google's libraries do not support Python 3 yet.

Here's my code:

for rows in dict1:
    insert_all_data = {
                       'rows': [{'json' : rows}]
                       }
    bigquery_service.tabledata().insertAll(projectId='na-sem',datasetId='Rules',tableId='my_table',body=insert_all_data).execute(num_retries=2)

Pandas data frame method

bigquery_results_df.to_gbq('samples.test', project_id='sample', chunksize=10000, verbose=True, reauth=False)

That returns the error:

NotImplementedError: Google's libraries do not support Python 3 yet.

Upvotes: 3

Views: 3537

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

In our self-serve environment for non-tech staff we address this mostly in one of two below ways. Of course - how relevant it is for you case - depends :)

  1. If dictionary is static and available in advance we are uploading it to storage and then loading to bigquery - that is classic scenario

  2. If dictionary is dynamic and actually being created on fly within app (self-serve env) - we are building "fake" query consisting of as many select statement with dictioinary data as bigquery size allows and than execute query job with destination table of choice

So like below simplified:

SELECT id, itemA, itemB FROM
  (SELECT 1 as id, 'a1' as itemA, 'b1' as itemB),
  (SELECT 2 as id, 'a2' as itemA, 'b2' as itemB),
  (SELECT 3 as id, 'a3' as itemA, 'b3' as itemB)

Upvotes: 2

Related Questions