CODEWITHSUNDEEP
google-analyticsgoogle-bigquery
Balkan
Balkan

Reputation: 711

Does BigQuery include intraday tables when I query over all dates up to current date?

Google Analytics 360 data in BigQuery has two intraday tables for the past two days, and permanent partitioned tables for the dates before that. When I run a query on the ga_sessions_ tables for the past 30 days, does this automatically include the two days' data in the ga_sessions_intraday_ tables or do I have to include them specifically?

Edit; here is a query that illustrates this:

SELECT date, visitId, totals.transactions FROMdataset.ga_sessions_2018* WHERE _TABLE_SUFFIX BETWEEN "0401" AND CAST(CURRENT_DATE() as STRING) ORDER BY date DESC

The result is that the most recent date is two days ago (ie not including intraday tables.) That's my question answered I guess, thanks anyway.

Upvotes: 3

Views: 3443

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

You can query across whatever tables you want; just write a filter that matches the right suffixes. For example,

SELECT date, visitId, totals.transactions, _TABLE_SUFFIX AS suffix
FROM `dataset.ga_sessions_*` WHERE REGEXP_EXTRACT(_TABLE_SUFFIX, r'[0-9]+')
  BETWEEN "20180401" AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
ORDER BY date DESC

I put the suffix in the select list so you can tell which table is matched.

Upvotes: 6

Related Questions