BigQuery - 从 UI 提交时相同的查询有效，并从批处理报告 SQL 语法错误 [英] BigQuery - same query works when submitted from UI and reports SQL syntax error from batch

问题描述

我有一个 SQL 查询，涉及不同字段上的两个连接.

I have a SQL query involving two joins on different fields.

当我在 UI 中以交互方式运行此查询时，我会返回一个结果集，没问题.当我批量提交完全相同的查询时，我得到一个 SQL 语法错误:

When I run this query interactively in the UI, I get back a result set, no problem. When I submit the exact same query in batch, I get back a SQL syntax error:

JOIN 中的字段名称video"不明确.请在字段名称前使用表限定符.

Ambiguous field name 'video' in JOIN. Please use the table qualifier before field name.

但是连接已经是完全限定的:

But the joins are already fully qualified:

SELECT 
t1.video AS video
t1.session AS session
...
FROM
(select video, session, ...) t1
LEFT JOIN EACH
(select video, ...) t2
ON t1.video = t2.video
LEFT JOIN EACH
(select session ...) t3
ON t1.session = t3.session

Google Big Query 的人是否看到了这个，项目是rising-ocean-426，批处理作业 ID 是 job_1YPDj1wNHPg82aZcvRKjD3coykg

Is someone from Google Big Query is seeing this, the project is rising-ocean-426, the batch job id is job_1YPDj1wNHPg82aZcvRKjD3coykg

如果我从这项工作中提取查询并将其粘贴到 UI 中，它就可以工作(在将 更改为返回后).

If I pull the query from this job and paste it into the UI, it works (after changing to returns).

推荐答案

成功和失败的查询之间的区别似乎是您在批量运行查询时设置了flattenResults=false.这种模式与 JOIN 的行为略有不同，可能会导致类似这样的微妙问题.

The difference between the succeeding and the failing query appears to be that you are setting flattenResults=false when you run the query in batch. This mode has slightly different behavior with JOINs that can cause subtle issues like this one.

来自 关于 JOIN 的 BigQuery 文档:

BigQuery 成对执行多个 JOIN 操作，从FROM 关键字之后的第一对输入.后续加入操作使用前一个 JOIN 操作的结果作为左边加入输入.

BigQuery executes multiple JOIN operations pairwise, starting with the first pair of inputs after the FROM keyword. Subsequent JOIN operations use the results of the previous JOIN operation as the left JOIN input.

这里的潜在问题是查询中最终 JOIN 的左源试图将 t1.video 和 t2.video 折叠到同一个范围内，两者的名称均为video"，并导致此错误.

The underlying issue here is that the left source of the final JOIN in your query is trying to collapse t1.video and t2.video into the same scope, both with the name "video", and causing this error.

这个问题的简单重现器如下，flattenResults=false:

A simple reproducer for this issue is the following, with flattenResults=false:

SELECT t1.video
FROM (SELECT 17 as video, 18 as session) t1
JOIN (SELECT 17 as video) t2
ON t1.video = t2.video
JOIN (SELECT 18 as session) t3
ON t1.session = t3.session

您有几个选项可以使此查询正常工作:

You have a couple options to make this query work:

1. 停止设置flattenResults=false.我可能错了，但从您的查询来看，它看起来不会返回任何嵌套或重复的字段，因此可能没有必要.

2. 重命名 t1 或 t2 中的视频字段之一.例如:

1. Stop setting flattenResults=false. I might be wrong, but from glancing at your query, it didn't look like it would return any nested or repeated fields, so it may not be necessary.

2. Rename one of the video fields in either t1 or t2. e.g.:

SELECT t1.video
FROM (SELECT 17 as video, 18 as session) t1
JOIN (SELECT 17 as video2) t2
ON t1.video = t2.video2
JOIN (SELECT 18 as session) t3
ON t1.session = t3.session

将第一个 JOIN 包裹在子选择中.这允许您通过仅选择视频字段之一来执行消歧.例如:

Wrap the first JOIN in a subselect. This allows you to perform the disambiguation by only selecting one of the video fields. e.g.:

SELECT t1_and_t2.video FROM
  (SELECT t1.video as video, t1.session as session
  FROM (SELECT 17 as video, 18 as session) t1
  JOIN (SELECT 17 as video) t2 
  ON t1.video = t2.video) t1_and_t2
JOIN (SELECT 18 as session) t3
ON t1_and_t2.session = t3.session;

这篇关于BigQuery - 从 UI 提交时相同的查询有效，并从批处理报告 SQL 语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！