BigQuery：如何声明一个数组变量并使用select语句设置数据？

Question

我想在BigQuery中声明一个数组变量，但是我无法将SQL语句放入我的变量中。我找不到任何相关的主题。

我想将表格中所有列的名称放入我的变量中，所以我尝试了以下代码：

DECLARE my_array ARRAY <STRING>;

SET my_array = (
  SELECT column_name
  FROM my_project.my_dataset.INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'my_table'
);

SELECT my_array

我认为我有语法问题，因为错误信息是：

Query error: Cannot coerce expression (
  SELECT column_name
  FROM my_project.my_dataset.INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'my_table'
) to type ARRAY<STRING> at [3:16]

Thanks,

- aloux

Answer 1

你会尝试这个吗？

DECLARE my_array ARRAY <STRING>;

SET my_array = ARRAY(
  SELECT column_name
  FROM my_project.my_dataset.INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'my_table'
);

SELECT my_array;

或者，这个也可以。

SET my_array = (
  SELECT ARRAY_AGG(column_name)
  FROM my_project.my_dataset.INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'my_table'
);

- Jaytiger

Answer 2

这样查询的输出结果并不是一个数组，因此您需要将结果聚合为一个数组以匹配正确的类型：

DECLARE something ARRAY<STRING>;

SET something = (
  SELECT ARRAY_AGG(column_name)
  FROM my_project.my_dataset.INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'my_table'
);

SELECT something;

你可以在 此页面 找到文档

- Gianfranco Reppucci

---

它可以工作。同时感谢提供文档。 - aloux

Answer 3

您可以使用字符串数组作为#1，但是您也可以使用#2方法，只需返回带有table_name和column_name的表即可。

# 1) 数组

DECLARE colum_name_array ARRAY <STRING>;

SET colum_name_array = (
  SELECT ARRAY_AGG(column_name)
  FROM `[project_id].[datset_name].INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = '[table_name]'
);

SELECT colum_name_array;

# 2) 表名及列名（未嵌套）

WITH
table_columns AS (
  SELECT table_name, ARRAY_AGG(column_name) as column_name_array
  FROM `[project_id].[datset_name].INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = '[table_name]'
)
SELECT table_name, colum_name
FROM table_columns, UNNEST(column_name_array) as colum_name
;

- Jiho Choi