bq 命令行工具 - 如何插入具有嵌套字段的大查询表？

我有三个 BigQuery 表，如下所示

Employee

Employee_id | Department_id | Location_id | Name | Age

部门

Department_id | Department_Name | Department_Code

Location

Location_id | Country | State | City

下面的查询用于连接所有三个表，

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

如何使用 bq 命令行实用程序（bq 查询命令）将此结果集插入具有以下架构和嵌套字段的 BigQuery 表中？

如何将此结果集插入具有以下架构和嵌套字段的 BigQuery 表中？

以下是针对BigQuery 标准 SQL https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-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

...使用 bq 命令行实用程序（bq 查询命令）？

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

see here https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql#bigquery-enable-sql-cli and bq 命令行工具 https://cloud.google.com/bigquery/bq-command-line-tool更多细节