Yappli Tech Blog

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

BigQueryの作業効率がアップする関数&構文3選【Sexy Tech for You #8】

BigQuery

データサイエンティストの阿部です。30㎡の貸し農園で野菜を育てていますが、まさに収穫の秋です。ブロッコリーやキャベツの、一株の大きさには驚きました。

さて、明日から使いたくなるデータハンドリング術を紹介する "Sexy Tech for You" の第8話を配信しました。自分は高頻度で利用するクエリを付箋アプリからコピペしています。その付箋に貼っている関数や構文を紹介しました（そもそも付箋アプリじゃなくて、よりよいコピペ用ツールがあれば教えて頂けると幸いです）。

動画中に記載したクエリや補足事項を本ブログに掲載しますので、少しでも皆様のお役に立てたら幸いです。

Youtube

BigQuery クエリ

テーブル保存構文

CREATE OR REPLACE TABLE `project_name.dataset_name.table_name`
PARTITION BY timestamp
CLUSTER BY column
AS

-- 以下SQLクエリを記載

時間系関数：JST換算と型変換

SELECT 
    -- UTCのTIMESTAMPをJSTにする
    FORMAT_TIMESTAMP('%Y-%m-%d %T', '2000-01-01 00:00:00', 'Asia/Tokyo'),

    -- UNIXTIMEをJSTにする
    FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_SECONDS(946684800), 'Asia/Tokyo'),
    
    -- 整数日付をDATE型にする
    PARSE_DATE('%Y%m%d', CAST(20000101 AS STRING))

CROSS JOIN UNNEST SPLIT：セパレーターで行単位に分割（フラット化）

SELECT
    word,
    COUNT(1) AS cnt
FROM
    `bigquery-public-data.hacker_news.comments`,
    UNNEST(SPLIT(text, " ")) AS word
GROUP BY
    1
ORDER BY
    2 DESC

補足

テーブル保存構文は、DDLステートメントの1つです。弊社で利用しているデータ分析基盤構築ツールのtroccoでも、最近BigQueryのDDLが対応されたので嬉しいです。

CROSS JOIN UNNEST SPLITのCROSS JOINは,で省略できるので、その記法を採用しています。ちなみに独自ルールとして、テーブル同士のJOINではCROSS JOINと明示的に記載し、フラット化の場合には,と書き分けています。完全に我流なので、王道記法があれば教えて頂けると嬉しいです。

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