Data Studio query error when using Big Query view that joins tables

Question

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

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]

That gives me the following error.

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.

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

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

Answer 1

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

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.