✊🏻 ⏏️ 💧 「GoogleBigQuery」という本。データウェアハウジング、分析、機械学習のすべて " 🕤 🌈 🎎

こんにちは住民！ペタバイトのデータセットを処理する必要性に恐れを感じていますか？企業全体のデータを統合し、インタラクティブな分析を容易にし、機械学習を可能にする情報ストレージシステムであるGoogleBigQueryについて理解します。これで、1つの便利な環境でデータを効率的に保存、クエリ、取得、および探索できます。 WalyappaLakshmananとJordanTaijaniが、スケーラブルでサーバーレスのパブリッククラウドのフルパワーを使用して、最新のデータウェアハウスで作業する方法を説明します。この本では、次のことを行います。-BigQueryの内部を詳しく調べる-Big Queryがサポートするデータの種類、関数、演算子を調べる-クエリを最適化し、スキームを実装してパフォーマンスを向上させたり、コストを削減したりする-GIS、タイムトラベル、DDL / DMLについて学ぶ。カスタム関数とSQLスクリプト-多くの機械学習の問題を解決します-データを保護し、パフォーマンスを追跡し、ユーザーを認証する方法を学びます。

ネットワークコストの最小化

ネットワークコストの最小化BigQueryは、世界中で利用できる地域サービスです。たとえば、EU地域に保存されているデータセットをリクエストする場合、リクエストは欧州連合のデータセンターにあるサーバーで実行されます。クエリ結果をテーブルに保存できるようにするには、EUリージョンにもあるデータセットに含まれている必要があります。ただし、BigQuery REST APIは、GCPの外部のコンピューターからでも、世界中のどこからでも呼び出すことができます（つまり、クエリを実行できます）。 Google CloudStorageやCloudPub / Subなどの他のGCPリソースを使用する場合、それらがデータセットと同じリージョンにあると、最高のパフォーマンスが得られます。したがって、リクエストがComputeEngineインスタンスまたはCloudDataprocクラスターから実行される場合、ネットワークオーバーヘッドは最小限になります。インスタンスまたはクラスターも要求されたデータセットと同じ領域にある場合。 GCPの外部からBigQueryにアクセスする場合は、ネットワークトポロジを考慮し、クライアントコンピューターとデータセットが存在するGCPセンターとの間のホップ数を最小限に抑えるようにしてください。

簡潔で不完全な応答RESTAPIに

直接アクセスすることにより、簡潔で不完全な応答を受け入れることにより、ネットワークのオーバーヘッドを削減できます。圧縮された応答を受け入れるには、HTTPヘッダーで、gzipアーカイブを受け入れる準備ができていることを指定し、「gzip」行がUser-Agentヘッダーに存在することを確認できます。次に例を示します。

Accept-Encoding: gzip
User-Agent: programName (gzip)

この場合、すべての応答はgzipを使用して圧縮されます。デフォルトでは、BigQuery応答には、ドキュメントにリストされているすべてのフィールドが含まれています。ただし、回答のどの部分に関心があるかがわかっている場合は、BigQueryにその部分のみを送信するように依頼できるため、ネットワークのオーバーヘッドが削減されます。たとえば、この章では、JobsAPIを使用してジョブに関する完全な情報を取得する方法を説明しました。完全な回答のサブセットのみに関心がある場合（たとえば、クエリプランのステップのみ）、関心のあるフィールドを指定して、応答のサイズを制限できます。

JOBSURL="https://www.googleapis.com/bigquery/v2/projects/$PROJECT/jobs"
FIELDS="statistics(query(queryPlan(steps)))"
curl --silent \
    -H "Authorization: Bearer $access_token" \
    -H "Accept-Encoding: gzip" \
    -H "User-Agent: get_job_details (gzip)" \
    -X GET \
    "${JOBSURL}/${JOBID}?fields=${FIELDS}" \
| zcat

注意：gzip圧縮データを受け入れることも記載されています。

複数のリクエストをパッケージにまとめる

REST APIを使用する場合、マルチパート/混合コンテンツタイプと各パートのネストされたHTTP要求を使用して、複数のBigQueryAPI呼び出しを組み合わせることができます。各部分の本文は、HTTP操作（GET、PUTなど）、URLへのパス、ヘッダー、および本文を指定します。応答として、サーバーはマルチパート/混合コンテンツタイプの単一のHTTP応答を送信します。各パートには、バッチ要求の対応する要求への応答が（順番に）含まれます。応答は特定の順序で返されますが、サーバーは任意の順序で呼び出しを処理できます。したがって、バッチリクエストは、並行して実行されるリクエストのグループと考えることができます。これは、バッチリクエストを送信して、プロジェクトの最後の5つのリクエストの実行計画から詳細を取得する例です。まず、BigQueryコマンドラインツールを使用します。最後の5つの成功したクエストを取得するには：

# 5   
JOBS=$(bq ls -j -n 50 | grep SUCCESS | head -5 | awk '{print $1}')

リクエストは、バッチ処理のためにBigQueryエンドポイントに送信されます。

BATCHURL="https://www.googleapis.com/batch/bigquery/v2"
JOBSPATH="/projects/$PROJECT/jobs"
FIELDS="statistics(query(queryPlan(steps)))"

個々のリクエストは、URLパスで定義できます。

request=""
for JOBID in $JOBS; do
read -d '' part << EOF
--batch_part_starts_here
GET ${JOBSPATH}/${JOBID}?fields=${FIELDS}
EOF
request=$(echo "$request"; echo "$part")
done

次に、リクエストを複合リクエストとして送信できます。

curl --silent \
   -H "Authorization: Bearer $access_token" \
   -H "Content-Type: multipart/mixed; boundary=batch_part_starts_here" \
   -X POST \
   -d "$request" \
   "${BATCHURL}"

BigQuery StorageAPIを使用した一括読み取り

第5章では、BigQuery REST APIとクライアントライブラリを使用してテーブルを列挙し、クエリ結果を取得する方法について説明しました。 REST APIは、比較的小さな結果セットにより適したページ化されたレコードとしてデータを返します。ただし、機械学習および分散抽出、変換、およびロード（ETL）ツールの出現により、外部ツールでは、BigQuery管理リポジトリへの高速かつ効率的な一括アクセスが必要になりました。この一括読み取りアクセスは、リモートプロシージャコール（RPC）プロトコルを介してBigQuery StorageAPIで提供されます。 BigQuery Storage APIを使用すると、構造化データは、列データストレージ形式により厳密に一致するバイナリシリアル化形式でネットワークを介して送信されます。これにより、複数のコンシューマー間で結果セットの追加の並列化が提供されます。

エンドユーザーはBigQueryStorageAPIを直接使用しません。代わりに、Cloud Dataflow、Cloud Dataproc、TensorFlow、AutoML、およびStorage APIを使用して、BigQuery APIを介さずに、管理対象ストレージから直接データを読み取るその他のツールを使用します。

Storage APIは保存されたデータに直接アクセスするため、BigQuery StorageAPIにアクセスするためのアクセス許可は既存のBigQueryAPIとは異なります。 BigQuery Storage APIのアクセス許可は、BigQueryのアクセス許可とは別に構成する必要があります。

BigQuery Storage APIは、BigQueryマネージドストレージから直接データを読み取るツールにいくつかの利点を提供します。たとえば、消費者は、複数のスレッドを使用してテーブルから重複しないレコードセットを読み取ることができ（たとえば、Cloud Dataprocの異なる本番サーバーからの分散読み取りを許可することにより）、これらのスレッドを動的にセグメント化します（したがって、テールレイテンシを削減します。これは、MapReduceジョブにとって深刻な問題になる可能性があります）。、読み取る列のサブセットを選択し（モデルで使用される機能のみを機械学習構造に渡すため）、列の値をフィルタリングし（ネットワークを介して送信されるデータの量を減らします）、同時にスナップショットの一貫性を確保します（つまり、特定の時点からデータを読み取ります）。

第5章では、JupyterNotebookの%% bigquery拡張機能を使用してクエリ結果をDataFrameにロードする方法について説明しました。ただし、例では、数十から数百のレコードなど、比較的小さなデータセットを使用しました。london_bicyclesデータセット全体（2400万レコード）をDataFrameにロードすることは可能ですか？はい、できますが、この場合、BigQueryAPIではなくStorageAPIを使用してデータをDataFrameにロードする必要があります。まず、AvroとpandasをサポートするPython StorageAPIクライアントライブラリをインストールする必要があります。これは、コマンドで実行できます

%pip install google-cloud-bigquery-storage[fastavro,pandas]

次に、前と同じように%% bigquery拡張機能を使用するだけですが、StorageAPIの使用を必要とするパラメーターを追加します。

%%bigquery df --use_bqstorage_api --project $PROJECT
SELECT 
   start_station_name 
   , end_station_name 
   , start_date 
   , duration
FROM `bigquery-public-data`.london_bicycles.cycle_hire

ここでは、個々の列への直接アクセスを提供するStorageAPIの機能を利用していることに注意してください。BigQueryテーブル全体をDataFrameに読み込む必要はありません。リクエストが少量のデータを返す場合、拡張機能は自動的にBigQueryAPIを使用します。したがって、ノートブックのセルに常にこのフラグを表示しても、怖くはありません。ノートブックのすべてのセルで--usebqstorageapiフラグを有効にするには、コンテキストフラグを設定します。

import google.cloud.bigquery.magics
google.cloud.bigquery.magics.context.use_bqstorage_api = True

効率的なストレージ形式の選択

クエリのパフォーマンスは、テーブルを構成するデータがどこにどの形式で保存されているかによって異なります。一般に、クエリでルックアップや型変換を実行する必要が少ないほど、パフォーマンスは向上します。

内部および外部データソース

BigQueryは、Google Cloud Storage、Cloud Bigtable、Google Sheetsなどの外部ソースのクエリをサポートしていますが、最高のクエリパフォーマンスは、独自のテーブルを使用する場合にのみ可能です。

すべての構造化および半構造化データの分析データのリポジトリとしてBigQueryを使用することをお勧めします。外部データソースは、ステージングストレージ（Google Cloud Storage）、ライブアップロード（Cloud Pub / Sub、Cloud Bigtable）、または定期的な更新（Cloud SQL、Cloud Spanner）に最適です。次に、これらの外部ソースからBigQueryにスケジュールに従ってデータをロードするようにデータパイプラインを設定します（第4章を参照）。

Google Cloud Storageにデータをリクエストする必要がある場合は、可能であれば、圧縮された列形式（Parquetなど）で保存してください。最後の手段として、JSONやCSVなどのレコードベースの形式を使用します。

ステージングバケットライフサイクル管理

データをGoogleCloud Storageに配置した後でBigQueryにアップロードする場合は、アップロード後に必ずクラウドから削除してください。ETLパイプラインを使用してデータをBigQueryにロードする場合（データを大幅に変換するか、途中でデータの一部のみを残すため）、元のデータをGoogle CloudStorageに保存することをお勧めします。このような場合、Google Cloud Storageのストレージをダウングレードするバケットライフサイクル管理ルールを定義することで、コストを削減できます。

バケットライフサイクル管理をオンにして、30日以上経過したフェデレーションリージョンまたは標準クラスからNearline Storageにデータを自動移動し、NearlineStorageに90日以上保存されたデータをColdlineStorageに自動移動する方法を次に示します。

gsutil lifecycle set lifecycle.yaml gs://some_bucket/

この例では、lifecycle.yamlファイルに次のコードが含まれています。

{
"lifecycle": {
  "rule": [
  {
   "action": {
    "type": "SetStorageClass",
    "storageClass": "NEARLINE"
   },
   "condition": {
    "age": 30,
    "matchesStorageClass": ["MULTI_REGIONAL", "STANDARD"]
   }
 },
 {
  "action": {
   "type": "SetStorageClass",
   "storageClass": "COLDLINE"
  },
  "condition": {
   "age": 90,
   "matchesStorageClass": ["NEARLINE"]
  }
 }
]}}

ライフサイクル管理を使用して、オブジェクトのクラスを変更するだけでなく、特定のしきい値より古いオブジェクトを削除することもできます。

配列および構造としてのデータの保存

BigQueryには、他の公開されているデータセットに加えて、世界中の気象サービスによって取得されたサイクロンストーム（ハリケーン、台風、サイクロンなど）に関する情報を含むデータセットがあります。サイクロンストームは最大数週間続く可能性があり、その気象パラメータは約3時間ごとに測定されます。このデータセットで、2018年に発生したすべての嵐、各嵐が到達した最大風速、およびその最大速度に到達したときの嵐の時間と場所を見つけることにしたとします。次のクエリは、パブリックデータセットからこのすべての情報を取得します。

SELECT
  sid, number, basin, name,
  ARRAY_AGG(STRUCT(iso_time, usa_latitude, usa_longitude, usa_wind) ORDER BY
usa_wind DESC LIMIT 1)[OFFSET(0)].*
FROM
  `bigquery-public-data`.noaa_hurricanes.hurricanes
WHERE
  season = '2018'
GROUP BY
  sid, number, basin, name
ORDER BY number ASC

クエリは、ストームID（sid）、その季節、プール、およびストーム名（割り当てられている場合）を取得し、そのストームに対して行われた観測の配列を見つけ、観測を風速の降順でランク付けし、各ストームの最大速度を選択します..。嵐自体は連番順に並べられています。結果には88レコードが含まれ、次のようになります。

リクエストには1.4秒かかり、41.7MBを処理しました。最初のエントリは、2018年1月2日の18：00UTCに最高速度29m / sに達した嵐Bolavenについて説明しています。

観測は複数の気象サービスによって行われるため、このデータは、以下に示すように、ネストされたフィールドを使用して標準化し、BigQueryに格納できます。

CREATE OR REPLACE TABLE ch07.hurricanes_nested AS

SELECT sid, season, number, basin, name, iso_time, nature, usa_sshs,
    STRUCT(usa_latitude AS latitude, usa_longitude AS longitude, usa_wind AS
wind, usa_pressure AS pressure) AS usa,
    STRUCT(tokyo_latitude AS latitude, tokyo_longitude AS longitude,
tokyo_wind AS wind, tokyo_pressure AS pressure) AS tokyo,
    ... AS cma,
    ... AS hko,
    ... AS newdelhi,
    ... AS reunion,
    ... bom,
    ... AS wellington,
    ... nadi
FROM `bigquery-public-data`.noaa_hurricanes.hurricanes

このテーブルのクエリは、元のテーブルのクエリと同じように見えますが、列名が少し変更されています（usa_latitudeではなくusa.latitude）。

SELECT
  sid, number, basin, name,
  ARRAY_AGG(STRUCT(iso_time, usa.latitude, usa.longitude, usa.wind) ORDER BY
usa.wind DESC LIMIT 1)[OFFSET(0)].*
FROM
  ch07.hurricanes_nested
WHERE
  season = '2018'
GROUP BY
  sid, number, basin, name
ORDER BY number ASC

このリクエストは、パブリックデータセットを使用して、元のリクエストと同じ量のデータを処理し、同じ時間で実行されます。ネストされたフィールド（構造）を使用しても、クエリの速度やコストは変わりませんが、クエリが読みやすくなります。その期間中に同じ嵐の観測が多数あるため、ストレージを変更して、各嵐の観測の配列全体を1つのレコードに収めることができます。

CREATE OR REPLACE TABLE ch07.hurricanes_nested_track AS

SELECT sid, season, number, basin, name,
 ARRAY_AGG(
   STRUCT(
    iso_time,
    nature,
    usa_sshs,
    STRUCT(usa_latitude AS latitude, usa_longitude AS longitude, usa_wind AS
wind, usa_pressure AS pressure) AS usa,
    STRUCT(tokyo_latitude AS latitude, tokyo_longitude AS longitude,
      tokyo_wind AS wind, tokyo_pressure AS pressure) AS tokyo,
    ... AS cma,
    ... AS hko,
    ... AS newdelhi,
    ... AS reunion,
    ... bom,
    ... AS wellington,
    ... nadi
  ) ORDER BY iso_time ASC ) AS obs
FROM `bigquery-public-data`.noaa_hurricanes.hurricanes
GROUP BY sid, season, number, basin, name

嵐のsid、season、およびその他の特性は、その期間に応じて変化しないため、スカラー列として保存することに注意してください。

観測ごとに変化する残りのデータは、構造の配列として保存されます。新しいテーブルのクエリは次のようになります。

SELECT
  number, name, basin,
  (SELECT AS STRUCT iso_time, usa.latitude, usa.longitude, usa.wind
     FROM UNNEST(obs) ORDER BY usa.wind DESC LIMIT 1).*
FROM ch07.hurricanes_nested_track
WHERE season = '2018'
ORDER BY number ASC

このリクエストは同じ結果を返しますが、今回は14.7 MB（3分の1のコスト削減）しか処理せず、1秒で完了します（速度が30％向上）。このパフォーマンスの向上の原因は何ですか？データが配列として保存されると、テーブル内のレコード数は劇的に減少します（682,000から14,000）2。これは、ストームごとに1つのレコードしかなく、多くのレコードがないためです。次に、季節で行をフィルタリングすると、図1に示すように、BigQueryは関連する多くのケースを同時に削除できます。 7.13。

もう1つの利点は、詳細レベルが異なるケースが同じテーブルに格納されている場合に、データレコードを複製する必要がないことです。 1つのテーブルに、嵐の緯度と経度の両方の変化データと、嵐の名前や季節などの高レベルのデータを格納できます。また、BigQueryは圧縮を使用して表形式のデータを列に格納するため、詳細なデータを処理するコストを心配することなく、高レベルのデータをクエリして処理できます。これで、ストームごとに個別の値の配列として格納されます。

たとえば、年ごとの嵐の数を調べるには、必要な列のみをクエリできます。

WITH hurricane_detail AS (
SELECT sid, season, number, basin, name,
 ARRAY_AGG(
  STRUCT(
    iso_time,
    nature,
    usa_sshs,
    STRUCT(usa_latitude AS latitude, usa_longitude AS longitude, usa_wind AS
wind, usa_pressure AS pressure) AS usa,
    STRUCT(tokyo_latitude AS latitude, tokyo_longitude AS longitude,
        tokyo_wind
AS wind, tokyo_pressure AS pressure) AS tokyo
  ) ORDER BY iso_time ASC ) AS obs
FROM `bigquery-public-data`.noaa_hurricanes.hurricanes
GROUP BY sid, season, number, basin, name
)
SELECT
  COUNT(sid) AS count_of_storms,
  season
FROM hurricane_detail
GROUP BY season
ORDER BY season DESC

前のリクエストは27MBを処理しました。これは、ネストされた繰り返しフィールドが使用されなかった場合に処理する必要がある56MBの半分です。

ネストされたフィールドだけではパフォーマンスは向上しませんが、他の関連テーブルへの結合を実際に実行することで読みやすさを向上させることができます。さらに、ネストされた繰り返しフィールドは、パフォーマンスの観点から非常に役立ちます。ネストされていない列または繰り返しの列（この場合はシーズン）でフィルタリングするクエリの速度を劇的に向上させ、コストを削減できるため、スキーマでネストされた繰り返しフィールドの使用を検討してください。

ネストされた繰り返しフィールドの主な欠点は、ストリーミングの更新に既存の配列へのアイテムの追加が含まれる場合、そのようなテーブルにストリーミングを実装するのが難しいことです。これは、新しいレコードを追加するよりも実装がはるかに困難です。既存のレコードを変更する必要があります。これは、新しい観測値が絶えず追加されるため、ストーム情報テーブルの重大な欠点です。これが、この公開データセットがネストされた複製を使用しない理由を説明しています。田畑。

アレイの使用方法

経験によれば、ネストされた繰り返しフィールドを正常に使用するには、ある程度の練習が必要です。 BigQueryのGoogleAnalyticsサンプルデータセットは、この目的に最適です。スキーマ内のネストされたデータを識別する最も簡単な方法は、以下に示すように、データタイプSTRUCTに対応するType列でRECORDという単語を見つけ、Mode列でREPEATEDという単語を見つけることです。

この例では、TOTALSフィールドはSTRUCT（ただし、繰り返されません）であり、HITSフィールドはSTRUCTであり、繰り返されます。 Google Analyticsは、訪問者のセッションデータを集計レベル（totals.hitsの1つのセッション値）と粒度レベル（各ページとサイトから取得した画像の個別のhit.time値）で追跡するため、これはある程度意味があります。 ..。レコードにvisitorIdを複製せずに、これらのさまざまな詳細レベルでデータを保存するには、配列を使用する必要があります。配列を使用して繰り返し形式でデータを保存した後、UNNESTを使用してそのデータをリクエストにデプロイすることを検討する必要があります。次に例を示します。

SELECT DISTINCT
  visitId
  , totals.pageviews
  , totals.timeOnsite
  , trafficSource.source
  , device.browser
  , device.isMobile
  , h.page.pageTitle
FROM
  `bigquery-public-data`.google_analytics_sample.ga_sessions_20170801,
  UNNEST(hits) AS h
WHERE
  totals.timeOnSite IS NOT NULL AND h.page.pageTitle =
'Shopping Cart'
ORDER BY pageviews DESC
LIMIT 10
     ,   [1,2,3,4,5]   :
[1,
2
3
4
5]

次に、WHEREなどの通常のSQL操作を実行して、ShoppingCartなどのタイトルのあるページのヒットをフィルタリングできます。それを試してみてください！

一方、GitHubパブリックコミット情報データセット（bigquery-publicdata.githubrepos.commits）は、ネストされた繰り返しフィールド（reponame）を使用して、コミット操作の影響を受けるリポジトリのリストを格納します。時間の経過とともに変化することはなく、他のフィールドでフィルタリングするより高速なクエリを提供します。

地理的タイプとしてのデータの保存

BigQueryパブリックデータセットには、米国の郵便番号エリア境界のテーブル（bigquery-public-data.utilityus.zipcodearea）と、米国の都市の境界を説明するポリゴンを含む別のテーブル（bigquery-publicdata.utilityus.uscitiesarea）が含まれています。zipcodegeom列は文字列であり、city_geom列は地理タイプです。

これらの2つの表から、以下に示すように、ニューメキシコのサンタフェのすべてのZIPコードのリストを取得できます。

SELECT name, zipcode
FROM `bigquery-public-data`.utility_us.zipcode_area
JOIN `bigquery-public-data`.utility_us.us_cities_area
ON ST_INTERSECTS(ST_GeogFromText(zipcode_geom), city_geom)
WHERE name LIKE '%Santa Fe%'

このクエリは51.9秒かかり、305.5 MBのデータを処理し、次の結果を返します。

このリクエストに時間がかかるのはなぜですか？これは、STINTERSECTS操作によるものではなく、主にSTGeogFromText関数がS2セルを評価し、各zipコードに対応するGEOGRAPHYタイプを構築する必要があるためです。

事前にこれを行ってポストコードテーブルを変更し、ジオメトリをGEOGRAPHY値として保存してみてください。

CREATE OR REPLACE TABLE ch07.zipcode_area AS
SELECT 
  * REPLACE(ST_GeogFromText(zipcode_geom) AS zipcode_geom)
FROM 
  `bigquery-public-data`.utility_us.zipcode_area

REPLACE（前のクエリを参照）は、SELECT *式の列を置き換える便利な方法です。

新しいデータセットのサイズは131.8MBで、元のテーブルの116.5MBよりも大幅に大きくなっています。ただし、このテーブルに対するクエリはS2カバレッジを使用でき、はるかに高速です。たとえば、次のクエリは5.3秒（速度が10倍に増加）かかり、320.8 MB（「オンデマンド」料金プランを使用するとコストがわずかに増加）を処理します。

SELECT name, zipcode
FROM ch07.zipcode_area
JOIN `bigquery-public-data`.utility_us.us_cities_area
ON ST_INTERSECTS(zipcode_geom, city_geom)
WHERE name LIKE '%Santa Fe%'

地理データをGEOGRAPHY列に保存することによるパフォーマンス上の利点は、魅力的なものではありません。これが、utilityusデータセットが非推奨になっている理由です（既に書き込まれたクエリを保持するために引き続き使用できます）。bigquery-public-data.geousboundaries.uszip_codesテーブルを使用することをお勧めします。このテーブルは、地理情報をGEOGRAPHY列に格納し、常に更新されます。

この本についての詳細は、上で見つけることができます»出版社のウェブサイト

»目次

»抜粋

については居住者クーポンで25％割引- Googleの

書籍の紙のバージョンの支払いの際に、電子書籍を電子メールで送信されます。

「GoogleBigQuery」という本。データウェアハウジング、分析、機械学習のすべて "

ネットワークコストの最小化

効率的なストレージ形式の選択

More articles: