Implementing UNION on distinct partitioned tables

Hi all,



We have several distinct apps that we track with Firebase. We connected Firebase to Google BigQuery with the native integration and we use Looker Firebase block to get started. This code allow us to explore the event of one single Google BQ table at a time.


We want to merge the data from our several distinct apps into one single Looker Explore. We believe it’s doable because all tables use the same schema structure (Firebase native). Like this, we could display, let’s say, the session count on per-app basis.


We tried to do a union directly in the Looker view, but we don’t manage to handle the partition system correctly. We are looking for a way to correctly UNION two distinct partitioned tables into one.


What we did

We started with the initial view defined in the block:

view: events {
sql_table_name: `project_name.dataset_name.events_*` ;;



We have two projects: 

  • project_name_A
  • project_name_B

with their respective datasets:

  • dataset_name_A
  • dataset_name_B

We tried to follow different routes and we stopped like so

view: events {
# Default import, from one table
#sql_table_name: `project_name.analytics_163444744.events_*` ;;

# Instead we try to UNION the various Big Query tables to have the dimensions for all apps/platforms.
derived_table: {
publish_as_db_view: yes
partition_keys: ["TIMESTAMP(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(event_date,r'\d\d\d\d\d\d\d\d')))"]
sql: SELECT *
FROM `project_name_A.dataset_A.events_*`
FROM `project_name_B.dataset_B.events_*`
datagroup_trigger: daily

When we run an explore, we get the following error:

Query execution failed: - Unrecognized name: _TABLE_SUFFIX at [15:57]


Google BigQuery Standard SQL


Any hint about how we could address the issue?


This topic has been closed for comments