[BigQuery] SELECT文からパーティション付きテーブルの作成

はじめに

BigQueryにてパーティション付きテーブルの作成クエリです。
with句を使ったサンプルです。
ついでにFirebaseAnalyticsのサンプルクエリを記載します。

パーティション付きテーブルのSQL

CREATE TABLE
  `[出力先プロジェクトID].[出力先データセット名].[出力先テーブル名]`
PARTITION BY
  　[パーティション列] OPTIONS( 
    require_partition_filter = TRUE -- パーティション フィルタを必須にする設定
) AS
WITH [with句名] as (
 [select 文]
)
SELECT
  *
FROM
  [with句名]

FirebaseAnalyticsのイベントテーブルからテーブル作成例

CREATE TABLE
  `[出力先プロジェクトID].[出力先データセット名].[出力先テーブル名]`
PARTITION BY
  event_date OPTIONS( require_partition_filter = TRUE ) AS
WITH
  select_for_create_table AS (
  SELECT
    events.event_date,
    events.event_timestamp,
    events.event_name,
    events.user_id,
    events.user_pseudo_id,
    events.entrances,
    events.engaged_session_event,
    events.engagement_time_msec,
    events.ga_session_id,
    events.ga_session_number,
    events.fa_event_origin,
    events.fa_screen,
    events.fa_previous_screen,
    events.user_first_touch_timestamp,
    events.app_name,
    events.app_version,
    events.device_mobile_model_name,
    events.device_operating_system,
    events.device_operating_system_version,
    events.geo_region,
    events.geo_city,
  FROM (
    SELECT
      (
      SELECT
        AS STRUCT ANY_VALUE(CASE
            WHEN fa_param.key = 'entrances' THEN fa_param.value.int_value
        END
          ) AS entrances,
        ANY_VALUE(CASE
            WHEN fa_param.key = 'engaged_session_event' THEN fa_param.value.int_value
        END
          ) AS engaged_session_event,
        ANY_VALUE(CASE
            WHEN fa_param.key = 'engagement_time_msec' THEN fa_param.value.int_value
        END
          ) AS engagement_time_msec,
        ANY_VALUE(CASE
            WHEN fa_param.key = 'ga_session_id' THEN fa_param.value.int_value
        END
          ) AS ga_session_id,
        ANY_VALUE(CASE
            WHEN fa_param.key = 'ga_session_number' THEN fa_param.value.string_value
        END
          ) AS ga_session_number,
        ANY_VALUE(CASE
            WHEN fa_param.key = 'firebase_event_origin' THEN fa_param.value.string_value
        END
          ) AS fa_event_origin,
        ANY_VALUE(CASE
            WHEN fa_param.key = 'firebase_screen' THEN fa_param.value.string_value
        END
          ) AS fa_screen,
        ANY_VALUE(CASE
            WHEN fa_param.key = 'firebase_previous_screen' THEN fa_param.value.string_value
        END
          ) AS fa_previous_screen,
      FROM
        UNNEST(event_params) AS fa_param ).*,
      event_name,
      PARSE_DATE('%Y%m%d',
        event_date) AS event_date,
      DATETIME_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp),
          'Asia/Tokyo'),
        SECOND) AS event_timestamp,
      SAFE_CAST(user_id AS int64) AS user_id,
      user_pseudo_id,
      DATETIME_TRUNC(DATETIME(TIMESTAMP_MICROS(user_first_touch_timestamp),
          'Asia/Tokyo'),
        SECOND) AS user_first_touch_timestamp,
      app_info.id AS app_name,
      app_info.version AS app_version,
      COALESCE(device.mobile_model_name,
        device.mobile_os_hardware_model) AS device_mobile_model_name,
      device.operating_system AS device_operating_system,
      device.operating_system_version AS device_operating_system_version,
      geo.region AS geo_region,
      geo.city AS geo_city,
    FROM
      `[プロジェクトID].analytics_xxxxxxxxx.events_*` ) AS events)
SELECT
  *
FROM
  select_for_create_table

さいごに

GoogleAnalytics4がデフォルトになってきて、BigQueryとFirebaseAnalyticsやGoogleAnalyticsと連携することが簡単になってきました。
溜まった日付テーブルから一括で中間テーブルを作成する時に役立つと思います。

sqlだけで実行できるとマネージドコンソールだけで完結するので楽ですね。