bigquery

date: 2020-06-26 excerpt: bigqueryのチートシート

tag: bq bigquery gcp

bigqueryのチートシート

データセット

bigqueryのエクスプローラでbigquery-public-dataと検索すると取得できる
様々なデータセットがあり、実験的な分析であれば足りることがある

UNNEST

/bigquery-unnest/

TIMESTAMP

/bigquery-timestamp/

各種関数

IF

IF(expr, true_result, false_result)

REGEX; 正規表現

SELECT REGEXP_CONTAINS("ポケモンゲットだぜ", "(ゲットだぜ)") 	-- true

ARRAY_AGG; groupbyしたvalueをARRAYに変換する

SELECT 
    name,
    TO_JSON_STRING(ARRAY_AGG(id))
FROM UNNEST([
  STRUCT("abc" AS name, 1 AS id),
  ("abc", 2),
  ("例子", 4),
  ("例子", 10),
  ("例子", 15)
])
GROUP BY
    name

+----+-------+-------------+
| 行 | name	 | f0_	       |
+----+-------+-------------+
| 1	 | abc   | [1,2]       |
| 2	 | 例子  | [4,10,15]   |
+----+-------+-------------+

APPROXIMATE FUNCTION; アプロキシメート関数

巨大なデータセットに対して厳密時でないがおおよそ正しい値を大幅に高速に得る
代表例
- APPROX_COUNT_DISTINCT
- APPROX_QUANTILES
- APPROX_TOP_SUM
- APPROX_TOP_COUNT

SELECT
  APPROX_COUNT_DISTINCT(repo_name) AS num_repos
FROM
  `bigquery-public-data`.github_repos.commits,
  UNNEST(repo_name) AS repo_name

create temporary table within sql, sqlで中間テーブルに書き込むようにする

e.g.

CREATE OR REPLACE TABLE
  mydataset.typical_trip AS # ここに書き込むテーブル名を入れる
SELECT
  start_station_name,
  end_station_name
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire

data partitioning, パーティショニングする

/bigquery-partition/

ランダムサンプル

/bigquery-random-sample/

bigquryのメタデータ

/bigquery-meta/

bigquery cli

/bigquery-cli/

pythonバインディング

/bigquery-python/

pandas_gbqでpandasのIFでbigqueryを実行する

/pandas-gbq/

cheat sheet

arrayに対してインデックスアクセスする

あるarray, xsがあるとき、要素0番目にアクセスするには OFFSET を使う
OFFSET はindex 0から、 ORDINAL は index 1から

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list, list[OFFSET(1)] as offset_1, list[ORDINAL(1)] as ordinal_1
FROM items;

+----------------------------------+-----------+-----------+
| list                             | offset_1  | ordinal_1 |
+----------------------------------+-----------+-----------+
| [apples, bananas, pears, grapes] | bananas   | apples    |
| [coffee, tea, milk]              | tea       | coffee    |
| [cake, pie]                      | pie       | cake      |
+----------------------------------+-----------+-----------+

formatを利用して文字列をパッティング、スライスする

SELECT t, len, FORMAT("%T", LPAD(t, len, "_")) AS LPAD 
FROM UNNEST([
  STRUCT("abc" AS t, 5 AS len),
  ("abc", 2),
  ("例子", 4)
]);

+--------------------------+
| t   | len   | PAD        |
+-----+-------+------------+
| abc | 5     | "__abc"    |
| ab  | 2     | "ab"       |
| 例子| 4     | "__例子"   |
+-----+-------+------------+

最瀕値を出す

SELECT 
  key, 
  AVG(fare) as mean_fare, 
FROM
( 
  SELECT 
    key, 
    PERCENTILE_CONT(fare, 0.5) OVER(PARTITION BY key) as median_fare,
  FROM `{SOME_PROJECT_NAME}.{BUCKET_NAME}.{TABLE}`
)
GROUP BY key 

カテゴリの最瀕値を出す

SELECT 
  key, 
  APPROX_TOP_COUNT(category_value, 1)[OFFSET(0)].value AS most_frequent_category_value
GROUP BY key

参考