CODEWITHSUNDEEP
google-bigquery
vkumar
vkumar

Reputation: 31

BigQuery Insert update on nested fields

I have multiple JSON files. The files have two nested fields. The files are generated daily so I need to perform daily insert and update operations in the BigQuery table. I have shared Table schema in the image.

How to perform update operation on nested fields?

BigQuery dataset

Upvotes: 3

Views: 6067

Answers (2)

koari
koari

Reputation: 66

A little late, but in case someone else is searching. If you can use Standard SQL:

INSERT INTO your_table (optout_time, clicks, profile_id, opens, ... ) 
VALUES (
  1552297347, 
  [
   STRUCT(1539245347 as ts, 'url1' as url), 
   STRUCT(1539245341 as ts, 'url2' as url)
  ], 
  'whatever', 
  [ 
   STRUCT(1539245347 as ts), 
   STRUCT(1539245341 as ts)
  ], 
  ...
)

Upvotes: 5

rilla
rilla

Reputation: 793

The BigQuery UI just provides import of JSONs to create new tables. So, to stream the content of the files into already existing tables BigQuery, you can write a small program in your favorite programming language using the client library.

I am going to assume you have your data as line-delimited JSONs looking like this:

 {"optout_time": 1552297349, "clicks": {"ts": 1539245349, "url": "www.google.com"}, "profile_id": "foo", ...}
 {"optout_time": 1532242949, "clicks": {"ts": 1530247349, "url": "www.duckduckgo.com"}, "profile_id": "bar", ...}

A python script to the job would look like this. It takes the json file names as command line arguments:

import json
import sys

from google.cloud import bigquery


dataset_id = "<DATASET-ID>"  # the ID of your dataset
table_id = "<TABLE-ID>"  # the ID of your table

client = bigquery.Client()
table_ref = client.dataset(dataset_id).table(table_id)
table = client.get_table(table_ref)

for f in sys.argv[1:]:
    with open(f) as fh:
        data = [json.loads(x) for x in fh]
        client.insert_rows_json(table, data)

The nesting is taken care of automatically.

For pointers of how this sort of operation would look like in other languages, you can take a look at this documentation.

Upvotes: 1

Related Questions