google-bigquery - 大查询 DML insert-select as into repeated tables

Question

我有一个表结构(表名:Recalled_transaction)如下:

[{
"name": "STR_NBR",
"type": "STRING",
"mode": "NULLABLE"
},
{
"name": "RGSTR_NBR",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
"name": "POS_TRANS_ID",
"type": "INTEGER",
"mode": "NULLABLE"
},
{
 "name": "SLS_DT",
"type": "DATE",
"mode": "NULLABLE"
},
{
"name": "TRANS_ORIG_SRC",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
  {
  "name": "POS_APPL_TYP_CD",
  "type": "STRING",
  "mode": "NULLABLE"
  },
  {
  "name": "USER_ID",
  "type": "STRING",
  "mode": "NULLABLE"
  }
 ]
},
{
"name": "RECALLED_TXN",
"type": "RECORD",
"mode": "REPEATED",
"fields": [
  {
  "name": "POS_SEQ_NBR",
  "type": "STRING",
  "mode": "REPEATED"
  },
  {
  "name": "SUB_SYS_CD",
  "type": "STRING",
  "mode": "NULLABLE"
  }
 ]
 }
 ]

我想使用 insert-select as(标准 sql 中的 DML)从一组常规表插入到此结构中。有没有人做过。感谢您的帮助。

谢谢

Answer 1

我创建了一个具有相同架构的表，并将一个示例查询放在一起以插入其中。在您的特定情况下，由于您有两个表，您可能需要 JOIN 它们，然后使用 GROUP BY。

    INSERT mydataset.SampleDmlTable
      (STR_NBR, RGSTR_NBR, POS_TRANS_ID, SLS_DT, TRANS_ORIG_SRC, RECALLED_TXN)
    WITH T AS (
      SELECT CAST(x AS STRING) AS STR_NBR,
        10 - x AS RGSTR_NBR,
        x AS POS_TRANS_ID,
        DATE_SUB(CURRENT_DATE(), INTERVAL x DAY) AS SLS_DT,
        CONCAT('foo_', CAST(x AS STRING)) AS POS_APPL_TYP_CD,
        CAST(x AS STRING) AS USER_ID,
        [CONCAT('bar_', CAST(x AS STRING)), 'baz'] AS POS_SEQ_NBR,
        CAST(10 - x AS STRING) AS SUB_SYS_CD
      FROM UNNEST([1, 1, 0, 3, 2, 2, 2]) AS x
    )
    SELECT
      STR_NBR,
      RGSTR_NBR,
      POS_TRANS_ID,
      SLS_DT,
      ARRAY_AGG(STRUCT(POS_APPL_TYP_CD, USER_ID)) AS TRANS_ORIG_SRC,
      ARRAY_AGG(STRUCT(POS_SEQ_NBR, SUB_SYS_CD)) AS RECALLED_TXN
    FROM T
    GROUP BY 1, 2, 3, 4;