Yappli Tech Blog

株式会社ヤプリの開発メンバーによるブログです。最新の技術情報からチーム・働き方に関するテーマまで、日々の熱い想いを持って発信していきます。

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

f:id:abe_masatoshi:20210531194020p:plain

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

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

Youtube


コード

#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つ目のステージの[結果を表示]から、最終的な出力結果を確認できます

f:id:abe_masatoshi:20210531191659p:plain
出力結果例

f:id:abe_masatoshi:20210531191910p:plain
出力テーブル例

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

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

Copyright © 2018 Yappli, Inc. All rights reserved