bq command line tool - How to insert into Big query tables that has nested fields?

Question

I have three BigQuery tables as shown below

Employee

Employee_id | Department_id | Location_id | Name | Age

Department

Department_id | Department_Name | Department_Code

Location

Location_id | Country | State | City

Below Query is used to join all three tables,

SELECT
  e.Employee_id,
  e.Name,
  e.Age,
  e.Department_id,
  d.Department_Name,
  d.Department_Code,
  l.Location_id,
  l.Country , 
  l.State,
  l.City
FROM Employee e
JOIN Department d
  ON e.Department_id = d. Department_id
JOIN Location l
  ON e.Location_id = l.Location_id

How to insert this result set into BigQuery table which has below schema with nested fields using bq command line utility(bq query command)?

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2016-12-17T07:20:29

How to insert this result set into BigQuery table which has below schema with nested fields?

Below is for BigQuery Standard SQL

SELECT
    e.Employee_id,
    e.Name,
    e.Age,
    STRUCT<Department_id STRING, Department_Name STRING, Department_Code STRING>(
                  e.Department_id, d.Department_Name, d.Department_Code) AS Department,
    STRUCT<Location_id STRING, Country STRING, State STRING, City STRING>(
                                l.Location_id, l.Country, l.State, l.City) AS Location
FROM Employee e
JOIN Department d
    ON e.Department_id = d. Department_id
JOIN Location l
    ON e.Location_id = l.Location_id

... using bq command line utility(bq query command)?

bq query --use_legacy_sql=false --append_table --destination_table 'dataset.table' '**`above query`**'

see here and bq-command-line-tool for more details

bq command line tool - How to insert into Big query tables that has nested fields?

1 Answers