Data Studio query error when using Big Query view that joins tables
I have created a view to combine two tables in Big Query. When I use this view as a data source in Data Studio, I get a configuration error. The request 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
Inside Big Query, the view works as expected. I can request it without any problem. If I try to use the view directly as a data source in Data Studio, I get the following error as soon as I delete the 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've also tried using a custom view query as the data source. Custom query ...
select * from [project:doug.employee_salaries_view]
This gives me the following error.
There`s a query error.
Field 't0.emp_name' not found; did you mean 'emp.Name'?
Error ID: 98782922
Something that is identical but has a different error number.
I was able to use tables and views that do not require joins as data sources without any problem.
Finally, all queries used use Legacy SQL in Big Query.
Is there some trick for using Big Query views that combine other tables as a source in Data Studio?
source to share
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 emp payment. name = pay.name
There's an automatic renaming that's happening in BigQuery for fields using dot notation. Sometimes when called from other tools such as Tableau or Data Studio, it causes problems.
source to share