“错误:在JOIN的任一侧都找不到字段'[REDACTED] .field_id'"，Google BigQuery [英] "Error: Field '[REDACTED].field_id' not found on either side of the JOIN", Google BigQuery

问题描述

作为Google BigQuery平台的初学者，我发现它的语法几乎类似于MySql.但是，我的查询出现问题，无法在我正在执行的内部联接的任一侧找到一列.

As a beginner to Google's BigQuery platform, I have found it almost similar to MySql regarding its syntax. However, I am receiving an issue with my query where it is not finding a column on either side of the Inner Join I am performing.

下面的示例查询:

SELECT
  base_account.random_table_name_transaction.context_id,
  base_account.random_table_name_transaction.transaction_id,
  base_account.random_table_name_transaction.meta_recordDate,
  base_account.random_table_name_transaction.transaction_total,
  base_account.random_table_name_transaction.view_id,
  base_account.random_table_name_view.user_id,
  base_account.random_table_name_view.view_id,
  base_account.random_table_name_view.new_vs_returning,
  base_account.random_table_name_experience.view_id,
  base_account.random_table_name_experience.experienceId,
  base_account.random_table_name_experience.experienceName,
  base_account.random_table_name_experience.variationName,
  base_account.random_table_name_experience.iterationId,
  base_account.random_table_name_experience.isControl
FROM
  [base_account.random_table_name_transaction] transactiontable
INNER JOIN
  base_account.random_table_name_view viewtable
ON
  transactiontable.view_id=viewtable.view_id
INNER JOIN
  [base_account.random_table_name_experience] experiencetable
ON
  viewtable.view_id=experiencetable.view_id
WHERE experiencetable.experienceId = 96659 or experiencetable.experienceId = 96660

在这种情况下，当我在BigQuery平台中运行它时，在运行查询几秒钟后，我返回了一个错误: "Error: Field 'base_account.random_table_name_experience.experienceId' not found on either side of the JOIN".

In this case, when I run it within the BigQuery platform, after a few seconds of the query running I am returned an error: "Error: Field 'base_account.random_table_name_experience.experienceId' not found on either side of the JOIN".

但是，当我运行相同的查询但是执行SELECT *查询时，它会正确执行并返回我期望的数据.

However, when I run the same query however I perform a SELECT * query, it does execute properly and returns the data I expect.

我的语法是否缺少某些有关其为何失败的信息?我可以确认我要返回的每一列确实存在于每个受尊重的表中.

Is there something missing with my syntax as to why it is failing? I can confirm that each column I am trying to return does exist in each respected table.

推荐答案

请确保使用标准SQL 来避免使用遗留SQL产生一些令人惊讶的别名规则，并获得更多有用的错误消息.您的查询将是:

Make sure to use standard SQL for your query to avoid some of the surprising aliasing rules with legacy SQL and to get more informative error messages. Your query would be:

#standardSQL
SELECT
  base_account.random_table_name_transaction.context_id,
  base_account.random_table_name_transaction.transaction_id,
  base_account.random_table_name_transaction.meta_recordDate,
  base_account.random_table_name_transaction.transaction_total,
  base_account.random_table_name_transaction.view_id,
  base_account.random_table_name_view.user_id,
  base_account.random_table_name_view.view_id,
  base_account.random_table_name_view.new_vs_returning,
  base_account.random_table_name_experience.view_id,
  base_account.random_table_name_experience.experienceId,
  base_account.random_table_name_experience.experienceName,
  base_account.random_table_name_experience.variationName,
  base_account.random_table_name_experience.iterationId,
  base_account.random_table_name_experience.isControl
FROM
  `base_account.random_table_name_transaction` transactiontable
INNER JOIN
  base_account.random_table_name_view viewtable
ON
  transactiontable.view_id=viewtable.view_id
INNER JOIN
  `base_account.random_table_name_experience` experiencetable
ON
  viewtable.view_id=experiencetable.view_id
WHERE experiencetable.experienceId = 96659 OR experiencetable.experienceId = 96660;

请注意，我所做的唯一更改是将#standardSQL放在开头(以启用标准SQL)，并使用反引号(而不是方括号)对表名进行转义.

Note that the only changes I made were to put the #standardSQL at the start (to enable standard SQL) and to escape the table names with backticks rather than brackets.

这篇关于“错误:在JOIN的任一侧都找不到字段'[REDACTED] .field_id'"，Google BigQuery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！