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

问题描述

我正在尝试插入表"，并看到我们需要显式指定列名.有没有一种方法可以不必手动输入就获取此数据?我正在BigQuery上执行此操作.

解决方案

下面是一个示例，该示例从表中获取列名(不产生成本)并同时构建INSERT列表:

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

这将返回:

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

2019年6月更新

现在在Beta中提供了对在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屋！