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

问题描述

我有三个BigQuery表，如下所示：

员工

  Employee_id | Department_id | Location_id |名称|年龄
  

部门

  Department_id | Department_Name | Department_Code 
  

地点

  Location_id |国家|状态|市
  

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

 选择
 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 
加入部门d 
 ON e.Department_id = d。 Department_id 
 JOIN位置l 
 ON e.Location_id = l.Location_id 
  

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

  SELECT 
 e.Employee_id，
 e.Name，
 e.Age，
 STRUCT< Department_id STRING，Department_Name STRING，Department_Code STRING>（
 e.De partment_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，城市）AS位置
 FROM员工e 
加入部门d 
 ON e.Department_id = d。 Department_id 
 JOIN位置l 
 ON e.Location_id = l.Location_id 
  

bq查询--use_legacy_sql = false --append_table --destination_table'dataset.table''**`上面的查询** **

查看了解更多详情

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)?

解决方案

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

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命令行工具 - 如何插入具有嵌套字段的Big查询表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！