使用联接表的大查询视图时，Data Studio查询错误 [英] Data Studio query error when using Big Query view that joins tables

问题描述

我已经创建了一个视图来联接Big Query中的两个表.当我将该视图用作Data Studio中的数据源时，出现配置错误.用于创建视图的查询如下所示:

I've created a view to join two tables in Big Query. When I use that view as a data source in Data Studio I get a configuration error. The query used to create the view looks like this:

SELECT emp.name, emp.gender, emp.age, pay.salary
FROM [project:doug.employees] as emp
JOIN [project:doug.payrates] as pay on emp.name = pay.name

在大查询"中，该视图按预期工作.我可以毫无问题地查询它.如果尝试将视图直接用作Data Studio中的数据源，则在页面上放置报表控件或查看报表后，会出现以下错误.

Within Big Query the view works as expected. I can query it without any problems. If I try to use the view directly as a data source in Data Studio, I get the following error as soon as I drop a report control on the page or view the report.

There`s a query error.
Field 't0.gender' not found; did you mean 'emp.Gender'?
Error ID: b07b8f27

我还尝试使用视图的自定义查询作为数据源.自定义查询是...

I also tried using a custom query of the view as a data source. The custom query is...

select * from [project:doug.employee_salaries_view]

这给了我以下错误.

There`s a query error.
Field 't0.emp_name' not found; did you mean 'emp.Name'?
Error ID: 98782922

相同，但错误编号不同.

Which is identical but has a different error number.

我已经能够使用不涉及联接的表和视图作为数据源，而不会出现任何问题.

I've been able to use tables and views that do not involve joins as data sources without any problems.

最后，所有正在使用的查询都在Big Query中使用旧版SQL.

Finally, all the queries in use are using Legacy SQL in Big Query.

使用Big Query视图结合其他表作为Data Studio中的源源是否有技巧?

Is there some trick to using Big Query views that join other tables as a source in Data Studio?

推荐答案

请为该视图尝试以下语法:

Please try the following syntax for the view:

SELECT emp.name as _name, emp.gender as _gender, emp.age as _age, pay.salary as _salary 
FROM [project:doug.employees] as emp 
JOIN [project:doug.payrates] as pay on emp.name = pay.name

BigQuery会对使用点表示法的字段进行自动重命名.有时，当从其他工具(如Tableau或Data Studio)中调用时，会引起问题.

There's an automatic renaming that happens in BigQuery for fields that use dot notation. Sometimes, when called from other tools, such as Tableau or Data Studio, it causes problems.

这篇关于使用联接表的大查询视图时，Data Studio查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！