Implementing UNION on distinct partitioned tables

[jicelavocat]

Hi all,

Context

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.

Goal

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.

Issue

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_*`
      UNION ALL
      SELECT *
      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]

 SQL QUERY

Google BigQuery Standard SQL

Any hint about how we could address the issue?