0

we are using Looker (dashboard/reporting solution) to create persistent derived tables in BigQuery. These are normal tables as far as bigquery is concerned, but the naming is as per looker standard (it creates a hash based on DB + SQL etc.) and names the table accordingly. These tables are generated through view in scheduled time daily. The table names in BigQuery look like below. 

table_id 

LR_Z504ZN0UK2AQH8N2DOJDC_AGG__table1         
LR_Z5321I8L284XXY1KII4TH_MART__table2

LR_Z53WLHYCZO32VK3FWRS2D_JND__table3

If I query the resulting table in BQ by explicit name then the result is returned as expected. 

select * from `looker_scratch.LR_Z53WLHYCZO32VK3FWRS2D_JND__table3`

Looker changes the hash value in the table name when the table is regenerated after a query/job change. Hence I wanted to create a view with a wildcard table query to make the changes in the table name transparent to outside world.

But the below query always fails. 

SELECT * 
FROM \`looker_scratch.LR_*\` 
where _table_suffix like '%JND__table3'

I either get a completely random schema with null values or errors such as:

Error: Cannot read field 'reportDate' of type DATE as TIMESTAMP_MICROS

There are no clashing table suffixes and I have used all sort of regular expression checks (lower , contains, etc)

Is this happening since the table names have hash values in them? I have run multiple tests on other datasets and there are absolutely no problem, we have been running wildcard table queries since a long time and have faced no issues whatsoever.

Please let me know your thoughts.

CDspace
  • 2,639
  • 18
  • 30
  • 36
Pruthviraj Shivanna
  • 165
  • 2
  • 6

1 Answers1

2

When you are using wildcard like below 

`looker_scratch.LR_*`

you actually looking for ALL tables with this prefix and than - when you apply below clause 

LIKE '%JND__table3'

you further filter in tables with such suffix

So the trick here is that very first (chronologically) table defines the schema of your output

To address your issue - verify if there are more tables that match your query and than look into very first one (the one that was created first)

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • Thanks Mikhail, now I understand why the queries were failing. I assumed that it was smart enough to find the underlying table based on the table_suffix match. – Pruthviraj Shivanna Sep 26 '17 at 06:50