Atsushi2022の日記

データエンジニアリングに関連する記事を投稿してます

BigQuery関連で面白かったブログ・動画 4選

1. I spent 3 hours figuring out how BigQuery inserts, deletes and updates data internally. Here’s what I found.

ライトノベルのようなタイトルですね。BigQuery内部でどのようにファイルを管理しているかキレイな図で紹介してくれてます。

データを挿入、削除するときに既存のファイルを書き換えるのではなくて、新しいファイルを作成し、ファイル抽象化層(Storage set)で古いファイルへの参照をやめて、新しく作成されたファイルを参照します。

つまり、一度作成されたファイルは不変で、データを書き換える必要がある場合は常に新しいファイルを作成するということ。古いファイルは、タイムトラベル(過去の時点のデータを参照する機能)でしようしますが、タイムトラベル保持期間を過ぎると、古いファイルはガベージコレクションとしてラベリングされるようです。

2. Lesson learned after reading the BigQuery academic paper: Shuffle operation

Map-ReduceからBigQuery(Dremelエンジン)への進化について説明してくれてます。

Map-Reduceでは、Mapperであるマシンは、ローカルのストレージ(メモリやディスク)を使用していたのに対し、BigQueryで使用されるDremelエンジンではストレージをマシンと分離してます。

3. BigQuery Admin reference guide: Query processing

こちらではBigQueryでどのようにクエリが処理されるかの概要が説明されています。

4. BigQuery Admin reference guide: Query optimization

こちらはGCP公式のBigQueryでの最適なクエリの書き方についての記事です。

  • WHERE句内の式の順序は、レコード数をより削減できる条件を先に持ってくる。
  • 結合では、最初に最大のテーブルを持ってくる。(結合の並び替えは特定の条件下でのみ行われるため、ユーザー側で結合の順序を意識することが重要)
  • 結合前にWHERE句で可能な限りレコード数を少なくする。
  • パターンマッチングの場合、= < LIKE < 正規表現 の順で処理が重くなる。
  • WITH句ではなく、一時テーブルを使用する。WITH句のエイリアスが参照される度に、サブクエリが実行される。

    • こちらについては本当にそうなるのか疑問だったので実行してみた。

      WITH s AS ( 
        SELECT * 
        FROM `dev-gcplab-01.test_dataset.staff` 
      )
      SELECT *
      FROM s AS a 
      JOIN s AS b
      ON a.id = b.id
      JOIN s AS c
      ON a.id = c.id
      

      ↑のようにWITH句のサブクエリをJOINで繰り返し参照すると、確かにInputが2つ+データソース1つで、入力が計3つになった。

  • 同一idで最新のレコードを取り出すといった場合には、ROW_NUMBER関数よりもARRAY_AGGを使用する。

    • ROW_NUMBER sql WITH t AS ( SELECT * ,ROW_NUMBER() OVER( PARTITION BY id ORDER BY updated_at DESC) rn FROM test_dataset.purchase ) SELECT * EXCEPT(rn) FROM t WHERE rn = 1;

    • ARRAY_AGG
      sql SELECT ARRAY_AGG( s ORDER BY updated_at LIMIT 1 )[OFFSET(0)] FROM test_dataset.purchase s GROUP BY id;

  • YouTubeにあげられている動画も参考になりそう

    Leigha Jarett's Playlist

BigQueryでのNULLのソート

BigQueryでORDER BY句を使用する場合、デフォルトのNULLの順序はASCかDESCで異なる。ASCの場合はNULLが最初に来るし、DESCの場合はNULLが最後に来る。

  • NULLS FIRST is applied by default if the sort order is ascending.
  • NULLS LAST is applied by default if the sort order is descending.

デフォルトのNULLの順序で困る場合は、ORDER BY句で、NULLS FIRSTNULLS LASTを使用することでNULLのソート順を制御できる。

参考

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#order_by_clause

MySQLが遅い時に参考にしたサイト一覧

概要

EmbulkでMySQLからデータを抽出しようとしたら遅かったので、関連となるトピックを色々調べた。とりあえず参考リンクだけ貼っておく。

クエリ処理の流れ

(Oracle) SQLの処理

(SQL Server) SELECT ステートメントを処理する

<非公式> MySQL Logical Architecture

<非公式> MySQLの仕組み調べてみた

実行計画を見てSQLの構造を理解しよう

プリペアドステートメント

サーバサイドエンジニアなら分かっておきたいPrepared文についての要点

(MySQL) プリペアドステートメント

(Postgres) PREPARE

(Wiki) Prepared statement

プリペアドステートメントと結果セット

(Postgres) 問い合わせの発行と結果の処理

Fetch size

「カーソル」を理解する

JDBC経由で100万件取得・追加してみた

JDBC setFetchSize() ではまった話

MySQL コマンドラインクライアント

大きな結果セット用のメモリーが足りないことで問題が発生する場合は、--quick オプションを使用します。 これにより mysql は、全結果セットを表示前に取得してメモリー内でバッファリングする代わりに、サーバーから 1 行ずつ結果を取得することを強制されます。

embulk-input-jdbc

  • fetch_rows: number of rows to fetch one time (integer, default: 10000)
    • If this value is set to > 1:
      • It uses a server-side prepared statement and fetches rows by chunks.
    • If this value is set to 1:
      • It uses a client-side built statement and fetches rows one by one.
      • Internally, useCursorFetch=false is used and java.sql.Statement.setFetchSize is set to Integer.MIN_VALUE.
    • If this value is set to -1:
      • It uses a client-side built statement and fetches all rows at once. This may cause OutOfMemoryError.
      • Internally, useCursorFetch=false is used and java.sql.Statement.setFetchSize is not set.

速度改善

https://www.manageengine.jp/products/Applications_Manager/solution_mysql-speed-improvement.html

https://airbyte.com/data-engineering-resources/optimizing-mysql-queries

ページネーション

【SQL】ページング処理用のSQL

Pagination in MySQL

ページネーション vs カーソル

Offset vs Cursor-Based Pagination: Which is the Right Choice for Your Project?

Offset pagination vs Cursor pagination

ResultSetとJVMメモリ

Does jdbc dataset store all rows in jvm memory

ResultSet behavior with MySQL database, does it store all rows in memory?

JVM ヒープ領域

Javaのヒープ・メモリ管理の仕組み

Javaヒープとガーベジコレクション

JVMヒープとコンテナ

JVMのヒープサイズとコンテナ時代のチューニング

コンテナではカーネルの CGroup という機能を使って、コンテナ内のプロセスが利用できるメモリを制限できます。 しかしコンテナ上でメモリサイズを取得しても、見えるのはコンテナホスト側のメモリサイズです。 コンテナ内でfreeコマンドを打つと、なぜかホスト側のメモリサイズが表示されるといった経験をしたことがある人もいるでしょう。 この問題を解決するために、メモリサイズではなく CGroup からヒープサイズを取得するオプションが Java 9 から追加されました。

UseContainerSupport は Java 10 に追加されたオプションです(JDK-8146115)。 また Java 8u191 などにもバックポートされました。 UseContainerSupport は CGroup からメモリ制限を取得するだけでなく、次の機能もあります。

  • CGroup の CPU の制限値も使用する
  • CGropu 上のメモリの利用率も取得できる

UseContainerSupport オプションはデフォルトで有効になっています。 そのため特に何も指定しなくても、コンテナが利用できるメモリ容量の 1/4 がヒープサイズとして割り当てられます。

CGroup

Linuxカーネルのコンテナ機能[2] ─cgroupとは?(その1)

Dataplexで外部キー制約を満たしているかのデータ品質チェック

BigQueryの外部キー制約は強制適用されないため、外部キー参照先の値(またはNULLのみ)しか入らないようにユーザー側で管理する必要がある。

そこでDataplexを使用して定期的に外部キー制約に沿った値になっているか確認できないかと考えた。

調べたところ、Dataplexを使用してSQL行チェックルールを使用して、外部キー参照先の値のみになっているかどうか定期的に確認できた。

以下は、確認結果についてのメモです。

まずはテスト用に2つのテーブルを作成した。

company_listテーブルには、会社IDと会社名(name)の列が存在する。staffテーブルは、スタッフのID、名前、会社名(company_name)の列が存在する。

staffテーブルのcompany_name列が、company_listテーブルのname列を外部参照する。

  • company_listテーブル

  • staffテーブル

以下はデータセット、テーブル作成のTerraformコード。

resource "google_bigquery_dataset" "default" {
  dataset_id                  = "test_dataset"
  friendly_name               = "test"
  description                 = "This is a test description"
  location                    = "US"
  # default_table_expiration_ms = 3600000
}

resource "google_bigquery_table" "company_list" {
  dataset_id = google_bigquery_dataset.default.dataset_id
  table_id   = "company_list"

  schema = <<EOF
  [
    {
      "name": "id",
      "type": "INT64",
      "mode": "NULLABLE"
    },
    {
      "name": "name",
      "type": "STRING",
      "mode": "NULLABLE"
    }
  ]
  EOF

  table_constraints {
    primary_key {
      columns = ["name"]
    }
  }
}

resource "google_bigquery_table" "staff" {
  dataset_id = google_bigquery_dataset.default.dataset_id
  table_id   = "staff"

  schema = <<EOF
  [
    {
      "name": "id",
      "type": "INT64",
      "mode": "NULLABLE"
    },
    {
      "name": "person_name",
      "type": "STRING",
      "mode": "NULLABLE"
    },
    {
      "name": "company_name",
      "type": "STRING",
      "mode": "NULLABLE"
    }
  ]
  EOF

  table_constraints {
    foreign_keys {
      referenced_table {
        project_id  = data.google_project.project.project_id
        dataset_id  = google_bigquery_table.company_list.dataset_id
        table_id    = google_bigquery_table.company_list.table_id
      }
      column_references {
        referencing_column = "company_name"
        referenced_column = "name"
      }
    }
  }
}

さらに、Dataplexのデータ品質チェックを作成する。SQLでは、DISTINCTで会社名を参照先のテーブルを取り出して、それらの会社名のいずれかに含まれていることを確認する。

resource "google_dataplex_datascan" "check_foreign_key_staff_table" {
  location     = "us-central1"
  display_name = "Check if satisfy foreign key"
  data_scan_id = "check-foreign-key-staff-table"

  data {
    resource = "//bigquery.googleapis.com/${google_bigquery_table.staff.id}"
  }

  execution_spec {
    trigger {
      on_demand {}
    }
  }

  data_quality_spec {
    sampling_percent = 100

    rules {
      name = "check-foreign-key"
      column = "company_name"
      dimension = "CONSISTENCY"
      row_condition_expectation {
        sql_expression = <<EOL
          company_name IN (
            SELECT
              DISTINCT name
              FROM ${google_bigquery_table.company_list.project}.${google_bigquery_table.company_list.dataset_id}.${google_bigquery_table.company_list.table_id}
              -- FROM句でプロジェクト名を指定しないと、table not found的なエラーになるので要注意
          )
        EOL
      }
    }
  }
}

では、実際にデータ品質スキャンを実行して確かめる。

それぞれのテーブルには次のようなデータを入れた。

この状態でデータ品質スキャンを実行すると成功した。

次に失敗するパターンも試してみた。staffテーブルにQuad.Incというcompnany_listテーブルに存在しない会社名のレコードを挿入した。

この状態でデータ品質スキャンを実行すると想定通り失敗となった。

ちなみにstaffテーブルにレコードが0件の状態も試してみたが、想定通り成功になった。

このようにしてDataplexのデータ品質スキャンを使えば、外部キー制約を満たしていることを定期的に確認できそう。

他に外部キー制約を満たしていることを確認するための良い方法があれば、教えてください。

以上

BigQueryのPRIMARY KEYは強制適用されない

まったく知らなかったけど、BigQueryの主キー/外部キー制約は強制適用されない。。。

これは知らないと割と事故りそう。


わい)主キー設定したし、NULLだったらエラーになってくれるよねー。NULLが入らないから安心だな~

BigQuery)NULLでもオレ、全然いいっすよ。気にしないっす


という感じですもんね。。。

まあ試しにCREATE文で主キー制約と外部キー制約を使ってみます。

CREATE OR REPLACE TABLE test_dataset.company_list (
  id INT64,
  company_name STRING,

  PRIMARY KEY (company_name) NOT ENFORCED
);

CREATE TABLE test_dataset.staff (
  id INT64,
  person_name STRING,
  company_name STRING REFERENCES test_dataset.company_list(company_name) NOT ENFORCED
);

PKFKと表示される。

ALTER TABLE ADD/DROPで制約を追加/削除することもできます。


で、結局いったい何のために主キー制約作ったの!?と思ったら、ブログ記事を用意してくれてますね。

「キー制約は強制適用されないのに、なぜ作成する必要があるのか」という疑問が生じると思います。 その答えは、「クエリ オプティマイザ―がより効果的にクエリを最適化するためにこの情報を使用する可能性があるから」です。 次に、キー制約を利用するクエリ最適化である内部結合解除、外部結合解除、結合順序変更の 3 つについて説明します。

回答ありましたね。

簡単に言うと、主キーと外部キー制約を適用しておくと、制約が設定された列で内部/外部結合するときに一致する行を探さなくて済むようになる(=結合解除と呼称)。だからクエリのパフォーマンスが上がる、ということらしいです。

必ずしも結合解除されるわけではないっぽいですが、その場合でも制約があることでカーディナリティの情報を使えて、パフォーマンスが上がる模様。

結合にめっちゃ時間かかって大変! という場合は使用を検討してもいいかもしれないですね。

ただ、やはりちゃんと主キー/外部キー制約を適用した列に不正な値が入らないようユーザー側で管理しないといけないらしいです。

ユーザーの責任

優れた性能には、大きな責任が伴います。BigQuery ではキー制約は強制適用されないため、ユーザーには常に制約を管理する責任があります。 主キー列の値はすべての行で一意でなければならず、NULL 値であってはいけません。 各外部キーは、NULL 値であるか、参照先テーブルの対応する主キー行である必要があります。 これらの制約のいずれかに違反があると、違反している制約のあるテーブルに対するクエリは誤った結果を返すことがあります。

優れた性能には、大きな責任が伴います。

す、すみません、甘えすぎてました。。。

とりあえず主キー制約を設定する列にはNOT NULL制約を設定しといたほうがいいですかね。BigQueryにはUNIQUE制約がないので、Dataplexとかで定期的に値をチェックするとかですかね。

外部キーについてもDataplexを使用して、参照先の列のDISTINCTの値のいずれかになってる、みたいなことがチェックできるのかな。

BigQueryで全行削除するときはDELETEを使う

BigQueryでは全行削除するときはDELETEを使った方が良い。 Timetravelで復元もできるし、DELETEより早いし、お金もかからない。

BigQuery公式

When performing a DELETE operation to remove all the rows from a table, use TRUNCATE TABLE statement instead. The TRUNCATE TABLE statement is a DDL (Data Definition Language) operation that removes all rows from a table but leaves the table metadata intact, including the table schema, description, and labels. Since TRUNCATE is a metadata operation it does not incur a charge.

TRUNCATEだとトランザクションログが残らず、復元できない、という認識だった。例えば、Oracle 12.1だと以下の通りの記述がある。

Orable公式

TRUNCATE TABLE文はロールバックできません。

BigQueryだとTRUNCATEしてもTimetravelで復元できる。一応、復元できることを確かめておく。

データの入ったテーブルを用意。

TRUNCATEする。

「課金されるバイト数」が0になっているので、課金されないことも確認できる。

ちゃんとTimetravelで復元できる。

BigQueryで全行削除ならTRUNCATEです。

以上

SQLでの計算量(Big O)

O(1) オーダー

  • テーブルから任意の1行を取り出す
  SELECT TOP 1 * FROM table;

O(log N) オーダー

  • インデックスが張られた列に対してのWHERE句による抽出

O(log N)なので、テーブルサイズが大きくなっても、あまり時間計算量は増えない

O(N) オーダー

以下の場合は、行数に対して線形的に時間計算量が増加する

  • 全行SELECTする
  • WHERE句を使用して抽出する。※インデックスがない場合にO(N)
  • count(*)でテーブルの行数をカウントする

O(N log N) オーダー

  • ORDER BY句によるソート

O(N2) オーダー

以下の場合は、行数に対して多項式的に時間計算量が増加する(激増する)。

  • テーブルの結合
    • テーブルAの各行に対して、テーブルBの各行を結合させるため

但し、結合のアルゴリズムによっては以下の計算量になる。

  • ハッシュ結合
    • O(M+N)
  • マージ結合
    • O(M+N)
    • O(M log M + N log N)
    • O(M + N log N)
  • ネストされた結合
    • O(N2)

参考

How To Write Better SQL Queries: The Definitive Guide – Part 2

Understanding Algorithmic Time Efficiency in SQL Queries

Big-O Cheat Sheet