1

I am querying from the Firebase Analytics tables.

The table I am using has a partition for each day. Here are some examples of the various daily partitions:

arm_studio.analytics_177892322.events_20180628
rm_studio.analytics_177892322.events_20180627
arm_studio.analytics_177892322.events_20180629

Essentially I am asking how to query the .all (every partition) of the table. Ie the partition from every single date recorded. Would also be great to query n days ago.

I have gone through BQ documentation on partitioned tables and the regular syntax seems to not work as intended. I have also noticed that normal partitioned tables have just the date after the table name as opposed to ".events_[date]" as shown in the example above.

Currently I am just unioning every partition which is extremely long and obviously unscalable. Anyone have experience with querying these tables?

Frank van Puffelen
  • 565,676
  • 79
  • 828
  • 807
codeisfuntodo
  • 19
  • 2

1 Answers1

9

To query events for all days use a wildcard:

FROM arm_studio.analytics_177892322.events_*

To query events for a particular month or year use wildcards and leave the month or year prefix, e.g.:

FROM arm_studio.analytics_177892322.events_201806*

or

FROM arm_studio.analytics_177892322.events_2018*

To set an arbitrary partition filter use _TABLE_SUFFIX column:

FROM arm_studio.analytics_177892322.events_*
WHERE _TABLE_SUFFIX BETWEEN '20180627' and '20180630'
medvedev1088
  • 3,645
  • 24
  • 42