📌

BigQuery Scriptingでcolumnのcardinalityを調べる

2024/12/21に公開

はじめに

BigQuery Scriptingで任意のtableのすべてのcolumnのcardinalityを調べる方法をご紹介します。このscriptでは、各columnに対して COUNT(DISTINCT) を実行するqueryを動的に生成します。

cardinalityとは

Databaseにおけるcardinalityとは、あるtableのcolumnに存在するuniqueな値の数を指します。これはデータの分布や多様性を把握する上で重要な指標です。また、Relational Databaseではcardinalityが高い（unique値が多い）columnにindexを貼ることで検索パフォーマンスを向上させます。一方、BigQueryでは従来のRelational Databaseで使用されるindex（B-treeやHash indexなど）はありませんが、高いcardinalityのcolumnをfilter条件に利用することでqueryのパフォーマンスを向上させることができます。

個人的にBigQueryでcardinalityを調べるタイミングとしては、以下のような場面が挙げられます。

初めて扱うtableのデータ分布を把握したいとき。
複雑なqueryで作成されたdatamartの一意性が正しいか確認したいとき。
特定の識別子やID列が一意であるかを検証したいとき。

script

方法は、INFORMATION_SCHEMA からcolumnを取得し、各columnについて COUNT(DISTINCT) を実行し、その結果を UNION ALL で結合するqueryを組み立てます。

DECLARE project_id STRING DEFAULT 'project_id';
DECLARE dataset_id STRING DEFAULT 'dataset_id';
DECLARE table_id STRING DEFAULT 'table_id';
DECLARE cardinality_query STRING;

SET cardinality_query = '';

FOR column_names IN (
  SELECT column_name
  FROM `project_id.dataset_id.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'table_id'
)
DO
  IF cardinality_query = '' THEN
    SET cardinality_query = 
    FORMAT(
      "SELECT '%s' AS column, COUNT(DISTINCT `%s`) AS cardinality FROM `%s.%s.%s`",
      column_names.column_name,
      column_names.column_name,
      project_id,
      dataset_id,
      table_id
    );
  ELSE
    SET cardinality_query = 
    FORMAT(
      "%s UNION ALL SELECT '%s' AS column, COUNT(DISTINCT `%s`) AS cardinality FROM `%s.%s.%s`",
      cardinality_query,
      column_names.column_name,
      column_names.column_name,
      project_id,
      dataset_id,
      table_id
    );
  END IF;
END FOR;

EXECUTE IMMEDIATE cardinality_query;

このscriptで以下のようなqueryが生成され実行されます。

SELECT
  'id' AS column,
  COUNT(DISTINCT `id`) AS cardinality
FROM
  `project_id.dataset_id.table_id`
UNION ALL
SELECT
  'name' AS column,
  COUNT(DISTINCT `name`) AS cardinality
FROM
  `project_id.dataset_id.table_id`
UNION ALL
SELECT
  'age' AS column,
  COUNT(DISTINCT `age`) AS cardinality
FROM
  `project_id.dataset_id.table_id`
UNION ALL
SELECT
  'sex' AS column,
  COUNT(DISTINCT `sex`) AS cardinality
FROM
  `project_id.dataset_id.table_id`
UNION ALL
SELECT
  'email' AS column,
  COUNT(DISTINCT `email`) AS cardinality
FROM
  `project_id.dataset_id.table_id`
UNION ALL
SELECT
  'address' AS column,
  COUNT(DISTINCT `address`) AS cardinality
FROM
  `project_id.dataset_id.table_id`

[
  { "column": "address", "cardinality": "100" },
  { "column": "email", "cardinality": "100" },
  { "column": "sex", "cardinality": "3" },
  { "column": "age", "cardinality": "41" },
  { "column": "name", "cardinality": "100" },
  { "column": "id", "cardinality": "100" }
]

table sizeが大きい場合や、高いcardinalityを持つcolumnに対して COUNT(DISTINCT) を実行する場合は計算コストが増加します。その場合は APPROX_COUNT_DISTINCT を使用することでパフォーマンスを犠牲にせずに概算値を取得できます。

はじめに

cardinalityとは

script

Discussion