如何在bigquery中删除/避免前缀表名？ [英] How to remove/avoid prepended tablename in bigquery?

问题描述

GBQ预加载连接上的表名：

  SELECT * 
 FROM dataset.table1 a 
 JOIN dataset.table2 b 
 ON a.key_a = b.key_b 
  

会产生一个表名为 a_field_1，a_field_2，...，b_field_a 。

的字段有没有办法查询表将表名添加到结果表中的字段中？

我想要一个名为field_1，field_2，field_a的字段的最终表（假定a和b都是您可以使用BigQuery标准SQL（请参阅 /cloud.google.com/bigquery/sql-reference/enabling-standard-sqlrel =nofollow>启用标准SQL ），其中表的别名不会预先添加，因此您可以按原样运行查询并获取预期结果。

在BigQuery Legacy SQL中，您可以通过明确列出要输出的字段来实现此目的。

  SELECT key_a，key_b，field_1，field_2，field_a 
 FROM dataset.table1 a 
 JOIN dataset.table2 b 
 ON a.key_a = b.key_b 
  

GBQ prepends the table name on a join:

SELECT *
FROM dataset.table1 a
JOIN dataset.table2 b
  ON a.key_a = b.key_b

Will produce a table with fields named a_field_1, a_field_2, ..., b_field_a.

Is there a way to query tables without prepending the table names to the fields in the resulting table?

I want a final table with fields named `field_1, field_2, field_a (with the assumption that a and b don't have fieldnames that are identical).

解决方案

You can use BigQuery Standard SQL (see Enabling Standard SQL) where tables' aliases are not prepended so you can run your query as is and get expected result.

In BigQuery Legacy SQL you can use achieve this by explicitly listing fields to be in output

SELECT key_a, key_b, field_1, field_2, field_a
FROM dataset.table1 a
JOIN dataset.table2 b
  ON a.key_a = b.key_b

这篇关于如何在bigquery中删除/避免前缀表名？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！