トップ > BigQuery > BigQueryのカラム毎の要約統計量をSQLのみで出す

BigQueryのカラム毎の要約統計量をSQLのみで出す

BigQuery SQL

背景

  • pandasのデータフレームに対するdescribe、ざっとテーブルの要約を知りたい場合に便利です
    • 値域がどうなっているか、数値の分布(パーセンタイル)がどうなっているか、異なりがどれくらいあるか
  • DWHの構築やアドホックなデータ分析をする際にさっとこの辺を知りたい場合が結構あります
    • もちろんSQLで調べられるけど、カラムが大量にあるので、一個一個SQLを書くのは大変
  • 素朴にやるなら、BigQueryのデータをメモリに持ってきてpandasで分析すればよいが、できないケースもある
    • データが巨大でそもそもpandasを持ち出したくない場合
      • 集計自体もSQLを使ってBigQueryにやらせるのが基本的には処理は早い
      • しかし、SQLで要約統計量用の集計コードを書くのはダルい
    • 自分が分析するのではなく、他人に使ってもらいたい場合
      • エンジニアでない方も想定する場合があり、その時はpandasやpythonを使ってもらうのはtoo much感
      • SQLで完結しているとうれしい
  • SQL単独でdf.describeができるようにしたい

SQLのみで様々な要約統計量を出す

  • 要約統計量はSQLではaggregate関数で色々できるので、それを使う
  • 毎回手で書いていると大変なので、BigQuery Scriptingを使ってSQLを自動生成させる
  • 以下のようなテーブルとしてカラム毎の統計量をざっくり把握することができます
    • 数値のカラムについては分位点(パーセンタイル)が分かるように
    • top_countで登場頻度が多い代表的な値をさっと把握できるように
    • カラムのdescriptionも一緒に表示できるので、統計値を見ながら間違ったdescriptionを書いていないか確認できます

f:id:syou6162:20220319090457p:plain
カラム毎の統計量を見やすく出す

以下のように実行します。統計量を出したい元のテーブルをsourceに、要約した統計量を出力するのをtargetに指定します。

% cat main.sql | \
  bq query --nouse_legacy_sql \
    --parameter="source_project:STRING:my-project" \
    --parameter="source_dataset:STRING:my_dataset" \
    --parameter="source_table:STRING:my_table" \
    --parameter="target_project:STRING:target-project" \
    --parameter="target_dataset:STRING:target_dataset" \
    --parameter="target_table:STRING:target_table"

BigQuery Scriptingとして保存します(main.sql)。肝は以下の通りです。

  • カラムの型の情報をINFORMATION_SCHEMA.COLUMNSで取得
  • 数値型やDATE型など型によってaggregateする関数を出し分ける

SQLを表示

DECLARE source_project STRING DEFAULT @source_project;
DECLARE source_dataset STRING DEFAULT @source_dataset;
DECLARE source_table STRING DEFAULT @source_table;

DECLARE target_project STRING DEFAULT @target_project;
DECLARE target_dataset STRING DEFAULT @target_dataset;
DECLARE target_table STRING DEFAULT @target_table;

DECLARE select_clause_numeric STRING;
DECLARE select_clause_date_stats STRING;

EXECUTE IMMEDIATE FORMAT("""
  CREATE OR REPLACE TABLE `%s.%s.%s`(
    project STRING,
    dataset STRING,
    table STRING,
    column_name STRING,
    data_type STRING,
    description STRING,
    unique_count INT64,
    null_count INT64,
    null_count_ratio FLOAT64,
    top_count ARRAY<STRUCT<key STRING, value INT64>>,
    avg FLOAT64,
    std FLOAT64,
    percentile ARRAY<STRUCT<key STRING, value FLOAT64>>,
    min_date DATE,
    max_date DATE
  );
""", target_project, target_dataset, target_table);

EXECUTE IMMEDIATE FORMAT("""
  CREATE TEMP TABLE table_metadata AS
  SELECT
    columns.column_name,
    columns.data_type,
    columns.ordinal_position,
    field_paths.description,
  FROM
    `%s`.%s.INFORMATION_SCHEMA.COLUMNS AS columns
  INNER JOIN
    `%s`.%s.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS AS field_paths
  ON
    columns.table_catalog = field_paths.table_catalog
    AND columns.table_schema = field_paths.table_schema
    AND columns.table_name = field_paths.table_name
    AND columns.column_name = field_paths.column_name
    AND columns.column_name = field_paths.field_path
  WHERE
    columns.table_name = "%s"
    AND columns.data_type IN (
      "STRING",
      "INT64", "FLOAT64", "NUMERIC", "BIGNUMERIC",
      "DATE", "DATETIME", "TIMESTAMP"
    );
""", source_project, source_dataset, source_project, source_dataset, source_table);

CREATE TEMP FUNCTION SelectClauseForNumericEmpty() RETURNS STRING AS (
  """
    NULL AS avg,
    NULL AS std,
    NULL AS percentile,
  """
);

CREATE TEMP FUNCTION SelectClauseForNumeric(column_name STRING) RETURNS STRING AS (
  REPLACE("""
    CAST(AVG(__COLUMN_NAME__) AS FLOAT64) AS avg,
    CAST(STDDEV_POP(__COLUMN_NAME__) AS FLOAT64) AS std,
    [
      STRUCT("min" AS key, MIN(CAST(__COLUMN_NAME__ AS FLOAT64)) AS value),
      STRUCT("percentile_1" AS key, APPROX_QUANTILES(CAST(__COLUMN_NAME__ AS FLOAT64), 100)[OFFSET(1)] AS value),
      STRUCT("percentile_5" AS key, APPROX_QUANTILES(CAST(__COLUMN_NAME__ AS FLOAT64), 100)[OFFSET(5)] AS value),
      STRUCT("percentile_25" AS key, APPROX_QUANTILES(CAST(__COLUMN_NAME__ AS FLOAT64), 100)[OFFSET(25)] AS value),
      STRUCT("median" AS key, APPROX_QUANTILES(CAST(__COLUMN_NAME__ AS FLOAT64), 100)[OFFSET(50)] AS value),
      STRUCT("percentile_75" AS key, APPROX_QUANTILES(CAST(__COLUMN_NAME__ AS FLOAT64), 100)[OFFSET(75)] AS value),
      STRUCT("percentile_95" AS key, APPROX_QUANTILES(CAST(__COLUMN_NAME__ AS FLOAT64), 100)[OFFSET(95)] AS value),
      STRUCT("percentile_99" AS key, APPROX_QUANTILES(CAST(__COLUMN_NAME__ AS FLOAT64), 100)[OFFSET(99)] AS value),
      STRUCT("max" AS key, MAX(CAST(__COLUMN_NAME__ AS FLOAT64)) AS value)
    ] AS percentile,
  """, "__COLUMN_NAME__", column_name)
);

CREATE TEMP FUNCTION SelectClauseEmptyDateStats() RETURNS STRING AS (
  """
    CAST(NULL AS DATE) AS min_date,
    CAST(NULL AS DATE) AS max_date,
  """
);

CREATE TEMP FUNCTION SelectClauseDateStats(column_name STRING) RETURNS STRING AS (
  REPLACE("""
    MIN(CAST(__COLUMN_NAME__ AS DATE)) AS min_date,
    MAX(CAST(__COLUMN_NAME__ AS DATE)) AS max_date,
  """, "__COLUMN_NAME__", column_name)
);

CREATE TEMP FUNCTION SelectClauseCommonStats(column_name STRING, data_type STRING) RETURNS STRING AS (
  REPLACE(REPLACE("""
    COUNT(DISTINCT(__COLUMN_NAME__)) AS unique_count,
    SUM(IF(__COLUMN_NAME__ IS NULL, 1, 0)) AS null_count,
    SUM(IF(__COLUMN_NAME__ IS NULL, 1, 0)) / COUNT(*) AS null_count_ratio,
    APPROX_TOP_COUNT(CAST(__COLUMN_NAME__ AS STRING), 5) AS top_count,
  """, "__COLUMN_NAME__", column_name), "__DATA_TYPE__", data_type)
);

FOR column IN (SELECT column_name, data_type, description FROM table_metadata ORDER BY ordinal_position) DO
  IF column.data_type IN ("INT64", "FLOAT64", "NUMERIC", "BIGNUMERIC") THEN
    SET  select_clause_numeric = SelectClauseForNumeric(column.column_name);
    SET select_clause_date_stats = SelectClauseEmptyDateStats();
  ELSEIF column.data_type IN ("DATE", "DATETIME", "TIMESTAMP") THEN
    SET select_clause_numeric = SelectClauseForNumericEmpty();
    SET select_clause_date_stats = SelectClauseDateStats(column.column_name);
  ELSE
    SET select_clause_numeric = SelectClauseForNumericEmpty();
    SET select_clause_date_stats = SelectClauseEmptyDateStats();
  END IF;
  EXECUTE IMMEDIATE FORMAT("""
    INSERT INTO `%s.%s.%s`
    SELECT
      "%s" AS project,
      "%s" AS dataset,
      "%s" AS table,
      "%s" AS column_name,
      "%s" AS data_type,
      \"\"\"%s\"\"\" AS description,
      %s
      %s
      %s
    FROM
      `%s.%s.%s`;
    """,
    target_project, target_dataset, target_table,
    source_project, source_dataset, source_table,
    column.column_name,
    column.data_type,
    column.description,
    SelectClauseCommonStats(column.column_name, column.data_type),
    select_clause_numeric,
    select_clause_date_stats,
    source_project, source_dataset, source_table
  );
END FOR;

EXECUTE IMMEDIATE FORMAT("""
  SELECT * FROM `%s.%s.%s`
""", target_project, target_dataset, target_table);

応用事例: 定常的な監視への発展

  • 静的なルールでテーブルを監視したい場合、SQLでルールを書けばよい
    • 例: {dbt,dataform}でnon_nullのテストをする
  • しかし、静的なルールで書き切れないケースも世の中にはある
    • 例: nullが一部入ることが知られている(ほぼない)が、カラム中のnullの割合が急に変化していないか知りたい
      • 特にMLの特徴量に使う場合、この辺りの傾向が急に変わると本番で事故る可能性が高い
    • 例: nullでない数値が入るカラムだが、分布が変化していないか知りたい
      • 平均値、中央値etc
  • こうしたケースは機械学習を使った異常検知が有用
  • 今回作ったSQLのみで要約統計量を出すのは、異常検知用のデータ生成にそのまま使える
    • スケジュールクエリで今回のBigQuery ScriptingのSQLを定期的に動かして、BigQuery MLの異常検知に食わせればデータの分布が変わったり、nullの割合が急に増えた、などを簡単に検知できる
  • 仕事でDWHを使っているので、そこで生成しているテーブルの品質チェックに使えるかなと思っています

参考: 先行研究

集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析 できるDigital Camp

集中演習 SQL入門 Google BigQueryではじめるビジネスデータ分析 できるDigital Camp

Amazon