Query multiple params in multiple tables with TABLE_DATE_RANGE for Firebase Analytics

Question

I intend to get from the events I have in the applications a stat for most played audios within an article. In the event I send articleId and the audioID that has been played.

I want to obtain as result rows like this ordered by number of ocurrences:

| ID of the article | ID of the audio | number of occurrences

Since firebase analytics exports to bigquery in a diary basis and I want those events per month I created a query that takes the values from multiple tables, and mixed it with the info I found in this thread.

The resulting query is:

SELECT 
  (SELECT params.value.int_value FROM x.params 
                                 WHERE params.key = 'Article_ID') AS Article_ID,
  (SELECT params.value.int_value FROM x.params 
                                 WHERE params.key = 'Audio_ID') AS Audio_ID,
  COUNT(event_dim.name) as Number_Of_Plays
FROM 
TABLE_DATE_RANGE([project-id:my_app_id.app_events_], DATE_ADD(CURRENT_TIMESTAMP(), -30, 'DAY'), CURRENT_TIMESTAMP()), UNNEST(event_dim) AS x
WHERE event_dim.name = 'Audio_Play'
GROUP BY Audio_ID, Article_ID
ORDER BY Number_Of_Plays desc

Unfortunately this query is not being parsed correctly provided me an error:

Error: Table name cannot be resolved: dataset name is missing.
RUN QUERY

I am pretty sure the issue is related to querying multiple tables in a range, but not sure how to fix it. Thanks.

Pentium10 · Accepted Answer · 2017-01-24T10:59:27.893

The other answer you reference, is using StandardSQL and you are trying to use TABLE_DATE_RANGE which is only available in LegacySQL.

This is the query in Standard SQL that allows you multiple tables

#standardSql
SELECT 
  (SELECT params.value.int_value FROM x.params 
                                 WHERE params.key = 'Article_ID') AS Article_ID,
  (SELECT params.value.int_value FROM x.params 
                                 WHERE params.key = 'Audio_ID') AS Audio_ID,
  COUNT(event_dim.name) as Number_Of_Plays
FROM 
 `project-id:my_app_id.app_events_*`, UNNEST(event_dim) AS x
 WHERE _TABLE_SUFFIX BETWEEN  cast(DATE_ADD(current_date(), INTERVAL -30 DAY) as string)  AND cast(current_date() as string)
   AND event_dim.name = 'Audio_Play'
   GROUP BY Audio_ID, Article_ID
   ORDER BY Number_Of_Plays desc

See this From clause: project-id:my_app_id.app_events_* and the WHERE _TABLE_SUFFIX BETWEEN syntax line.

Oh man thanks I will test it in a couple of hours and accept if it works properly! Thanks also for the tip about standard SQL. It is a pitty that the errors doesn't state something like this for the syntax. — droidpl, Jan 24 '17 at 11:05

Query multiple params in multiple tables with TABLE_DATE_RANGE for Firebase Analytics

1 Answers1