Googleスプレッドシートの表をBigQueryに連携して縦持ちテーブルに動的変換【Sexy Tech for You #1】

データサイエンティストの阿部です。今後不定期に、データ分析のためのデータハンドリングTipsを、Youtubeで発信します。各回3分でBigQueryに関する話題が中心です。"Sexy Tech for You" と題して、思わず使いたくなるような魅力的なTipsを紹介していきたいと思います。

動画中に記載したコードや補足事項を本ブログに掲載しますので、少しでも皆様のお役に立てたら幸いです。では早速、第1回目の配信についてです。

Youtube

BigQuery クエリ

#StandardSQL

DECLARE id,target,not_target STRING;

# INFORMATION_SCHEMA.COLUMNSからフィールド名取得
SET (target, not_target) = (
SELECT AS STRUCT
  CONCAT('(', STRING_AGG(IF(STARTS_WITH(column_name,'string_field_')=TRUE , column_name, NULL), ','), ')'),
  IFNULL(CONCAT('EXCEPT (', STRING_AGG(IF(STARTS_WITH(column_name,'string_field_')=FALSE, column_name, NULL), ','), ')'), ' ')  #string_field_*以外のフィールドがあった場合にUNPIVOT対象から除外するためのフィールドセット。NULLのとき半角スペースにしておかないと後続でエラーになる
FROM
  `project_id.dataset_name`.INFORMATION_SCHEMA.COLUMNS #データセット名（青）
WHERE
  table_name = 'table_name' #テーブル名のみ（青）
  AND column_name != 'string_field_1' #キーにしたいフィールド名（赤）
);

# 動的なフィールド名SETを読み込むため、EXECUTE IMMEDIATE FORMATでクエリ発行
EXECUTE IMMEDIATE FORMAT("""
WITH base AS (
SELECT
  *,
  string_field_1 AS value_type
FROM
  (SELECT * EXCEPT %s FROM `project_id.dataset_name.table_name` #キーにしたいフィールド名（赤）
  UNPIVOT(value FOR key IN %s)
)

SELECT
  B.value AS id,
  A.value_type,
  A.value
FROM
  base A INNER JOIN
  (SELECT * FROM base WHERE value_type = '月') B ON A.key = B.key #キーにしたいスプレッドシート入力値（黄）
WHERE
  A.value_type != '月' #キーにしたいスプレッドシート入力値（黄）
""", not_target, target);

補足

結果は以下のように出てきます。3つ目のステージの[結果を表示]から、最終的な出力結果を確認できます

出力結果例

出力テーブル例

string_field_* をtarget というフィールド名のセットとして使い、それ以外のフィールドをnot_target として除いてUNPIVOT しています。別の型のフィールドを扱う必要がある場合は、この辺りを変更すれば対応できるかと思います。

以上、Moblie Tech for All のヤプリがお送りする Sexy Tech for You でした！次回またお会いしましょう。