英文:
When running a For loop in Big Query the sql_expression_list is not being evaluated the same as if it was run alone
问题
以下是翻译好的部分：
我正在运行一个 For 循环，从数据集的信息模式中收集一个 table_expression，其中包含表中 ARRAY<STRING> 类型的列的名称。我能够按预期生成这个 table_expression。从这里，我尝试循环遍历这些列并评估每列的内容。所有这些列都包含一个字符串数组，其中"empty"项目具有一个单个空格。
当我运行这个查询时，我收到以下错误：
> 查询错误：函数 ARRAY_LENGTH 的参数类型不匹配：STRING。支持的签名：ARRAY_LENGTH(ARRAY)
如果我将 DO 后的查询孤立出来，查询将正常运行，如预期一样处理。
我曾运行过类似的循环以生成列上的空值计数，并取得了成功，因此似乎是评估数组时出现了问题，但我不确定是什么原因导致的。
我尝试以相同的方式运行相同的查询，将 field.field_path 视为字符串值，但我没有收到与单独运行 DO 查询时相同的结果，我认为它可能将 field.field_path 评估为字符串值而不是变量。
英文:
I am running a For loop to gather a table_expression from a dataset's information schema that contains the names of columns that are of ARRAY<STRING> type in a table. I am able to generate this table_expression as intended. From here I am trying to loop through the columns and evaluate each column's contents. The columns all contain an Array of Strings with "empty" items having a single whitespace.
FOR field IN (
SELECT
field_path, data_type
FROM
`project.dataset.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
WHERE
table_name = 'table_1'
AND data_type LIKE 'ARRAY%' ) DO
SELECT
ARRAY_LENGTH(field.field_path) AS s3,
IF
(REGEXP_CONTAINS(ARRAY_TO_STRING(field.field_path, ','),r'.*?\,\s\,.*?'),1,0) AS b1,
IF
(REGEXP_CONTAINS(ARRAY_TO_STRING(field.field_path, ','),r'^\s\,.*?'),1,0) AS b2,
IF
(REGEXP_CONTAINS(ARRAY_TO_STRING(field.field_path, ','),r'.*?\,\s$'),1,0) AS b3
FROM
`project.dataset.table_1`);
END
FOR;
When I run this query I receive the error:
> Query error: No matching signature for function ARRAY_LENGTH for
> argument types: STRING. Supported signature: ARRAY_LENGTH(ARRAY)
If I isolate the query after the DO, the query runs without this error and processes as intended.
I have run a similar loop to produce null counts on columns and had success, so it seems to be an issue with evaluating Arrays but I am unsure of what is causing it.
I have tried running the same query, treating field.field_path as a String value, but I do not receive the same results as when I run the DO query stand-alone and I believe it might be evaluating the field.field_path as a string value and not a variable.
答案1
得分: 0
我认为它可能会将field.field_path评估为字符串值而不是变量。
由于field是一个变量，它不能作为列名使用。相反，您可以考虑像下面这样的动态SQL。
例如，
FOR field IN (
SELECT
field_path, data_type
FROM
`project.dataset.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
WHERE
table_name = 'table_1'
AND data_type LIKE 'ARRAY%'
) DO
EXECUTE IMMEDIATE FORMAT('
SELECT
ARRAY_LENGTH(%s) AS s3
FROM
`project.dataset.table_1`
', field.field_path);
END FOR;
英文:
>I believe it might be evaluating the field.field_path as a string value and not a variable.
Since field is a variable, it can't be used as a column name. Instead, you can consider a dynamic SQL like below.
For example,
FOR field IN (
SELECT
field_path, data_type
FROM
`project.dataset.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
WHERE
table_name = 'table_1'
AND data_type LIKE 'ARRAY%'
) DO
EXECUTE IMMEDIATE FORMAT("""
SELECT
ARRAY_LENGTH(%s) AS s3
FROM
`project.dataset.table_1`
""", field.field_path);
END FOR;
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论