Geek asks and answers

How to create a nested JSON format table from a flat table in BigQuery?

I have a spreadsheet stored in google bigquery in the following format:

log_date: integer, SESSIONID: integer, computer: string, IP: string, event_id: integer, quantity: float

I am trying to create this table in a hierarchical nested format with two nested levels, as shown below:

 [
  {
    "name": "log_date",
    "type": "integer"
  }, 
  {
    "name": "session",
    "type": "record",
    "mode": "repeated",
    "fields": [                 
     {
       "name": "sessionid",
       "type": "integer"
         },
     {
       "name": "computer",
       "type": "string"
        },
        {
       "name": "ip",
       "type": "string"
        },
        {
    "name": "event",
    "type": "record",
    "mode": "repeated",
    "fields": [
    {
       "name": "event_id",
       "type": "integer"
     },
     {
       "name": "amount",
       "type": "float"
     }]] } ]

What is the best way to create a formatted json file file from a bigquery table? Is there a different and faster approach than 1. Load the table into an external csv 2. Create a json record and write it to an external file 3. Load the external json file into a new bigquery table.

Do we have a direct process that generates json from existing tables?

Thanks, H

+5
1

. json, CSV, commend --destination_format, NEWLINE_DELIMITED_JSON.

bq extract \
    --destination_format=NEWLINE_DELIMITED_JSON \
    yourdataset.table \
    gs://your_bucket/result*.json
+1

More articles:


All Articles