创建一个列类型为 RECORD 的表 [英] create a table with a column type RECORD

问题描述

我正在使用大查询，我想创建一个作业，用记录"类型的列填充表.数据将由查询填充 - 那么我如何编写返回记录"类型列的查询.

谢谢！

解决方案

Pentium10 提出的不知何故选项在 GBQ UI 或 API Explorer 中从未对我有用.
我可能错过了什么

与此同时，我发现的解决方法如下例

SELECT location.state, location.city FROM JS((//输入表SELECT NEST(CONCAT(state, ',', city)) AS 位置从 (选择州、城市 FROM(选择佛罗里达"作为州，迈阿密"作为城市)，(SELECT 'california' AS state, 'la' AS city),(选择罗马尼亚"作为州，特兰西瓦尼亚"作为城市))),location,//输入列"[//输出模式{'名称':'位置'，'类型':'记录'，'模式':'重复'，字段":[{'name': 'state', 'type': 'STRING'},{'name': 'city', 'type': 'STRING'}]}]",函数(行，发射){//函数for (var i = 0; i < row.locations.length; i++) {var c = [];x = row.locations[i].split(',');t = {州:x[0]，城市:x[1]}c.push(t);发射({位置:c})；};}")

请注意:
您应该使用 Allow Large Results 和未选中的 Flatten Results

设置目标表

输出表的结果是(在 JSON 模式下)

<预><代码>[{地点": [{"state": "加利福尼亚",城市":拉"}]},{地点": [{"state": "佛罗里达",城市":迈阿密"}]},{地点": [{"state": "罗马尼亚",城市":特兰西瓦尼亚"}]}]

<块引用>

添加以解决@AdiCohen 在他的真实示例中遇到的一些问题他在最近的评论中表示:

问:我的查询除了记录列之外还有其他列，但是当我运行时查询，它们返回为空.我怎样才能创建一个表类型?

SELECT 金额、货币、location.state、location.city FROM JS((//输入表SELECT NEST(CONCAT(state, ',', city)) AS 位置，SUM(amount) 作为金额，MAX(currency) 作为货币从 (SELECT state、city、amount、currency、ROW_NUMBER() OVER() as grp FROM(选择佛罗里达"作为州，迈阿密"作为城市，硬币"作为货币，40 作为金额)，(SELECT 'california' AS state, 'la' AS city, 'coins' AS货币, 40 AS金额),(选择罗马尼亚"作为州，特兰西瓦尼亚"作为城市，硬币"作为货币，40作为金额)) 按 grp 分组),金额，货币，位置，//输入列"[//输出模式{'name': 'location', 'type': 'RECORD', 'mode': 'REPEATED',字段":[{'name': 'state', 'type': 'STRING'},{'name': 'city', 'type': 'STRING'}] },{ 'name': 'amount', 'type': 'INTEGER'},{ 'name': 'currency', 'type': 'STRING'}]",函数(行，发射){//函数for (var i = 0; i < row.locations.length; i++) {var c = [];x = row.locations[i].split(',');t = {州:x[0]，城市:x[1]}c.push(t);发出({金额:row.amount，货币:row.currency，位置:c})；};}")

这里的输出是:

<预><代码>[{"金额": "40",货币":硬币"，"location_state": "罗马尼亚","location_city": "特兰西瓦尼亚"},{"金额": "40",货币":硬币"，"location_state": "佛罗里达州",location_city":迈阿密"},{"金额": "40",货币":硬币"，"location_state": "加利福尼亚州","location_city": "la"}]

I'm using big query and i want to create a job which populates a table with a "record" type columns. The data will be populated by a query - so how can i write a query which returns "record" type columns.

Thanks!

解决方案

Somehow option proposed by Pentium10 never worked for me in GBQ UI or API Explorer.
I might be missing something

Meantime, the workaround I found is as in below example

SELECT location.state, location.city FROM JS(
  (      // input table
  SELECT NEST(CONCAT(state, ',', city)) AS locations
  FROM (
    SELECT state, city FROM 
    (SELECT 'florida' AS state, 'miami' AS city),
    (SELECT 'california' AS state, 'la' AS city),
    (SELECT 'romania' AS state, 'transylvania' AS city)
    ) 
  ),
  locations,     // input columns
  "[    // output schema
    {'name': 'location', 'type': 'RECORD',
     'mode': 'REPEATED',
     'fields': [
       {'name': 'state', 'type': 'STRING'},
       {'name': 'city', 'type': 'STRING'}
     ]    
    }
  ]",
  "function(row, emit){    // function 
    for (var i = 0; i < row.locations.length; i++) {
      var c = [];
      x = row.locations[i].split(',');
      t = {state:x[0], city:x[1]}
      c.push(t);
      emit({location: c});  
    };
  }"
)  

Please note:
you should set destination table with Allow Large Results and unchecked Flatten Results

Result from output table is (in JSON Mode)

[
  {
    "location": [
      {
        "state": "california",
        "city": "la"
      }
    ]
  },
  {
    "location": [
      {
        "state": "florida",
        "city": "miami"
      }
    ]
  },
  {
    "location": [
      {
        "state": "romania",
        "city": "transylvania"
      }
    ]
  }
]

Added to address some issue @AdiCohen has with his real example that he showed in his recent comments:

Q: my query has other columns besides the record column, but when i ran the query, they return as null. how can i create a table with both of the types?

SELECT amount, currency, location.state, location.city FROM JS( 
  ( // input table 
    SELECT NEST(CONCAT(state, ',', city)) AS locations, 
      SUM(amount) AS amount, MAX(currency) as currency 
    FROM ( 
      SELECT state, city, amount, currency, ROW_NUMBER() OVER() as grp FROM 
        (SELECT 'florida' AS state, 'miami' AS city, 'coins' AS currency, 40 AS amount), 
        (SELECT 'california' AS state, 'la' AS city, 'coins' AS currency, 40 AS amount), 
        (SELECT 'romania' AS state, 'transylvania' AS city,'coins' AS currency, 40 AS amount) 
    ) GROUP BY grp
  ), 
  amount, currency, locations, // input columns 
  "[ // output schema 
    {'name': 'location', 'type': 'RECORD', 'mode': 'REPEATED', 
    'fields': [ 
      {'name': 'state', 'type': 'STRING'}, 
      {'name': 'city', 'type': 'STRING'} 
    ] }, 
    { 'name': 'amount', 'type': 'INTEGER'}, 
    { 'name': 'currency', 'type': 'STRING'} 
  ]", 
  "function(row, emit) { // function 
    for (var i = 0; i < row.locations.length; i++) { 
      var c = []; 
      x = row.locations[i].split(','); 
      t = {state:x[0], city:x[1]} 
      c.push(t); 
      emit({amount: row.amount, currency: row.currency, location: c}); 
    }; 
  }"
) 

Output here is:

[
  {
    "amount": "40",
    "currency": "coins",
    "location_state": "romania",
    "location_city": "transylvania"
  },
  {
    "amount": "40",
    "currency": "coins",
    "location_state": "florida",
    "location_city": "miami"
  },
  {
    "amount": "40",
    "currency": "coins",
    "location_state": "california",
    "location_city": "la"
  }
]

这篇关于创建一个列类型为 RECORD 的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！