Bigquery - how do i insert data into nested table

Question

I'm trying to intentionally insert one dummy record with full schema data into bigquery GA360 table.

One way I figured it out is to "INSERT" all fields:

INSERT INTO <project.dataset.table> (visitorId,visitStartTime,date,totals,customDimension.......)
VALUES ( 1234, 12345, 20200101, ( 1,2,3,4,5,6,7,8,9,10,11,12,13),[(1,"asd"),....].....)

I gave up when I encountered "hits" field which has complicated nested structure.

Is there any other way (ex. something I can imagine is to code with client API using table schema JSON file) or anyone who has succeed with DDL?

Felipe Hoffa · Accepted Answer · 2020-01-29 04:52:22Z

3

How would I solve this:

Get a sample as JSON object:

SELECT TO_JSON_STRING(a)
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` a
LIMIT 1

Save that long JSON string to a file.
Modify that file as much as you want.
Create a new table to insert that file:

CREATE TABLE `temp.analy` AS
SELECT *
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801` 
LIMIT 0

Insert your file with the manually modified values:

bq load --source_format=NEWLINE_DELIMITED_JSON temp.analy my.json

Or

5b. Create a federated table, so you could

INSERT INTO 'table'
SELECT * 
FROM `federated_table_that_reads_the_json_file`

answered Jan 29, 2020 at 4:52

Felipe Hoffa

59.8k23 gold badges185 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LSJ Over a year ago

Thanks for the tip !- it took sometime to modify values but was much easier than inserting all values

Collectives™ on Stack Overflow

Bigquery - how do i insert data into nested table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related