查找要插入 BigQuery 的列名 [英] Find column names to insert into BigQuery

问题描述

我正在尝试执行插入表"并看到我们需要明确指定列名.有没有办法在无需手动输入的情况下获取这些数据?我正在 BigQuery 上执行此操作.

解决方案

这是一个从表中获取列名(不产生成本)并同时构建 INSERT 列表的示例时间:

WITH EmptyReference AS (选择 *从`bigquery-public-data.samples.shakespeare`限制 0)选择康卡特('插入数据集.表名(',ARRAY_TO_STRING(REGEXP_EXTRACT_ALL(TO_JSON_STRING((SELECT AS STRUCT t.*)),r'"([^"]+)":'),', '),')')从 (选择作为值FROM EmptyReference AS tUNION ALL SELECT AS VALUE NULL) AS t

返回:

INSERT dataset.tablename (word, word_count, corpus, corpus_date)

<块引用>

2019 年 6 月更新

对在 INSERT 和 MERGE 语句中省略列名的支持现在处于测试阶段.

当省略列名时，目标表中的所有列都会根据它们的序号位置升序包含

更多详情此处>

I am trying to do the "insert into table" and see that we need to explicitly specify the column names. Is there a way to get this data without having to manual type it out? I am doing this on BigQuery.

解决方案

Here is an example to get the column names from a table (without incurring a cost) and build the INSERT list at the same time:

WITH EmptyReference AS (
  SELECT *
  FROM `bigquery-public-data.samples.shakespeare`
  LIMIT 0
)
SELECT
  CONCAT(
    'INSERT dataset.tablename (',
    ARRAY_TO_STRING(
      REGEXP_EXTRACT_ALL(
        TO_JSON_STRING((SELECT AS STRUCT t.*)),
        r'"([^"]+)":'),
      ', '),
    ')')
FROM (
  SELECT AS VALUE t
  FROM EmptyReference AS t
  UNION ALL SELECT AS VALUE NULL
) AS t

This returns:

INSERT dataset.tablename (word, word_count, corpus, corpus_date)     

June 2019 Update

Support for omitting column names in INSERT and MERGE statements is now in Beta.

When the column names are omitted, all columns in the target table are included in ascending order based on their ordinal positions

More details here

这篇关于查找要插入 BigQuery 的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！