0

I'm trying to create a view with StandardSQL that will automatically have the most updated data every time you open it (BigQuery creates a file with the traffic data per day with the date in the name extension).

I'm using something like 

FROM `whatever.ga_sessions_201*` as GA WHERE _TABLE_SUFFIX BETWEEN '70101' AND '81231'

Even though this works when I wanna run the query normally, it does not when I try to create a view with it. I guess I could use a scheduled query but was wondering if there is any way to build a view with an open date frame (just like views work on Athena if the files are correctly uploaded to the S3 bucket you are pointing at).

Thanks in advance!

Marc
  • 3
  • 2
  • _"it does not when I try to create a view with it"_ - what the error you get? Also, look at this: https://stackoverflow.com/questions/28422504/i-have-daily-tables-on-bigquery-how-to-query-the-newest-one – Graham Polley Sep 18 '18 at 11:18
  • that's the error: Failed to save view. Bad table reference "whatever.ga_sessions_201*"; table references in standard SQL views require explicit project IDs. – Marc Sep 18 '18 at 13:37
  • the link you gave me seems to be for tables, which i have no problems creating like this, but not for views? – Marc Sep 18 '18 at 13:37
  • For views I think you need the project name in too. eg. `projectname.dataset.table` – Bobbylank Sep 18 '18 at 14:22
  • @Bobbylank yup, it works. thanks!! – Marc Sep 18 '18 at 16:08

1 Answers1

0

You could do something like this:

WITH CTE AS
(SELECT max(_TABLE_SUFFIX) tableSuffix
FROM `projectname.dataset.table_*`)

SELECT *
FROM `projectname.dataset.table_*`
join CTE ON _table_suffix = tableSuffix

Might be expensive with lots of tables though?

Might be better to use a WHERE statement and calculate the date you need from the current_date but I appreciate the GA tables don't always arrive at a set time.

Bobbylank
  • 1,906
  • 7
  • 15
  • the problem is not exactly that i hard code the beginning and the end of the time period with _table_suffix but that the FROM 'whatever.ga_sessions_201*' has an * at the end – Marc Sep 18 '18 at 15:21