BigQuery

知っておくと運用で役に立つbq loadのTips

今日もbq loadが失敗して涙を流していたデータエンジニアのid:syou6162です*1。このエントリではbq loadを使ったデータ取り込みで泣かないで済む、あるいは泣いても致命傷まではいかないようにするための色々なTipsを書きます。 bq loadをベースに書いていま…

クエリを書くときは確率的な挙動にならないように注意しよう

自分用のメモです。以下のエントリで便利なスクリプトを作りました。 不可解な現象に遭遇 このスクリプトを使って、いくつかのデータを調べていましたが、ぱっと見不思議な現象に遭遇しました。 比較対象のテーブルAとB(クエリは同一のものを仕様。作成元がp…

テーブル比較のクエリを型化するスクリプトを作った

前提 DWHやデータマートのリファクタリング、あるいは軽微なカラム追加をした際、意図せず差分が起きていないかを確認したい場面は多いでしょう 「一個カラムを追加するために一個JOINを増すだけ」と思ったら、JOIN先のテーブルに重複があってfan-outしてい…

BigQueryにexportしたGA4のシャーディングテーブルのスキーマに関するハマリ所

前提: GA4とシャーディングテーブル GA4のイベントデータを仕事で取り扱う人は多いと思う。BigQueryにexportする場合、GA4のイベントデータは[PREFIX]_YYYYMMDDのシャーディングテーブルになっており、さらにその中でevents_YYYYMMDDとevents_intraday_YYYYM…

Google Cloud Champion Innovatorに選ばれました

先日のdbtのCommunity spotlightに引き続き、めでたいことが続いてありがたい限りですね。Google Cloud Champion Innovatorに選ばれました。 https://cloud.google.com/innovators/champions/directory?hl=ja&text=yasuhisa より 「そもそもChampion Innovat…

BigQueryを補完する技術: DuckDBとDataflowでのデータ処理入門

背景 & Disclaimer DuckDB 概念や代表的なユースケース 使ってみる 1週間〜一ヶ月などある程度の期間、分析で使いたい場合 便利なCLIツールとして使う 所感 参考 Dataflow 代表的なユースケース 具体例 参考 背景 & Disclaimer BigQueryは非常に便利で、BigQ…

elementaryを使ってデータの正確性に関わるデータ品質の実装状況を可視化する

背景: データ品質を可視化したい 実装: elementaryを使って、正確性のデータ指標を可視化する 実例: ダミーデータを使った可視化 まとめ 背景: データ品質を可視化したい 運用しているDWHでデータ品質にまつわる問題で苦労したことがない人は少ないと思いま…

dbtのモデルとLooker Studioのレポートの依存関係をexposureで表現して、データ管理を効率的に行なおう

シリーズの第三弾です。読者の宿題にしてたけど、誰も書いてくれなさそうだったので結局自分で書きました。 背景 Looker StudioはGoogle Workspaceを使っていれば基本的に無料で使えますし*1、権限管理にGoogle Groupとも連携できるので、人気のBIの一つだと…

dbtのモデルとConnected Sheetsの依存関係をexposureで表現して、データ管理を効率的に行なおう

以下のConnected Sheets版です。これはかなり便利なものができたと、自画自賛してます。 背景 Connected Sheetsをdbtのexposureとして取り込む 見所 Connected Sheetsからのクエリか判断する BigQuery Scripting経由で発行されたクエリでもreferenced_tables…

Terraform経由でBigQueryのデータセットを管理する際に必要なメタデータが入力されていることをConftestで担保する

背景 ConftestによるTerraformのポリシーテスト 例: ConftestでBigQueryのデータセットのlabelにownerが設定されていることをテストする 実際の業務への取り込み方 背景 権限管理を含め、BigQueryのデータセットの管理をTerraformで行なっている人は多いと思…

大量のBigQueryの日付別テーブルから特定の条件でレコードを削除する

この記事はdatatech-jp Advent Calendar 2023の12日目の投稿です。本日は12/18ですが、Advent Calendarの空きがあったのでねじこみました。 背景 困ること: 実行時間が長い 脱線: レコードの削除時に考慮したいこと dry-runモードで何が実行されるかを分かる…

派生先テーブルの参照回数も考慮して安全にテーブルを撤退する

3行まとめ テーブルの撤退時にはテーブルの参照回数を見ることが多いと思いますが、テーブル単独の参照回数を見るだけだと不十分なことが多いです 派生先のテーブルの参照回数まで考慮すると、テーブルが撤退できるか安全に判断することができます リネージ…

BigQueryの列レベルのアクセス制御とポリシータグの調査メモ

なぜ列レベルのアクセス制御とポリシータグが必要か Terraformでポリシータグの作成および権限付与 ポリシータグの付与の仕方 dbt経由の場合 bq loadを使う場合 運用上の注意点 まとめ なぜ列レベルのアクセス制御とポリシータグが必要か 「テーブルの全ての…

Cloud Shell経由で手元のデータをBigQueryにアップロードする

小ネタです。割と便利だったので、エントリに書き起しておきます。 背景: エンジニア職種でなくてもでかいデータをBigQueryにアップロードできるようにしたい BigQueryはWebコンソールから手元のcsvなどをアップロードすることができます しかし、これにはフ…

Dataplex(旧Data Catalog)によるデータカタログの調査

Dataplex(旧Data Catalog)によるデータカタログについてあれやこれやれやをまとめておいたポインタが欲しくなってきたので、とりとめもなくつらつらと書きます。 注意点: BigQuery on GCPの運用を前提に書いてます Dataplexはデータカタログ以外の機能もたく…

BigQueryのシャーディングテーブル(日付別テーブル)でフルスキャンを避ける方法 with BigQuery Scripting & dbt

SQLをレビューしていて、シャーディングテーブル(日付別テーブル)をサブクエリを使ってフィルタしているものがあった。BigQureyのシャーディングテーブルはWHERE句で日付の条件を書いてやるとスキャン範囲を限定することができるので便利ではあるが、サブク…

BigQuery Editions環境でのスロット使用量を調べられるSQLを書いた

BigQueryの新プランの登場でBigQueryをOnDemandからEditionsに切り替える人も多いと思います。OnDemand環境下ではスキャンするデータ量を見ておけばよかったですが、Editionsではスロット消費量がベースになり課金額が決まります。 「課金額がどれくらいか」…

BigQueryで自分が発行したjobをまとめてキャンセルする

小ネタです。特にLooker Studioでよく起きますが、カスタムクエリや選択するフィールドをぽちぽちいじっていると、その都度クエリが発行されます。探索的にあれこれいじっていると、段々クエリが詰まってくることもあります(Looker Studio側がうまいことjob…

Terraform経由でBigQuery Editionsの設定を行なう

背景: BigQuery Editionsの登場およびOnDemandの価格変更 注意(Disclaimer) BigQuery Editionsの設定を行なう providerのバージョンを上げる Reservationの作成およびAssignmentの設定 脱線: BigQuery Editionsを選択して、OnDemandよりコストが上がってしま…

JSONの配列がある集合の要素を含んでいるかをSQLで判定する

前提 SQLを書いているとき、元データが[2,3,5,7]のようなJSONで入ってくることがある。データがJSONで入ってくると色々考慮しないといけないことが増えるので、あんまりぽんぽん出てきて欲しくはない。が、まあ現実には相手をしないといけない場合もある。世…

BigQueryのビューをコマンドラインでコピーする

BigQueryのビューのコピーは普通だとWeb画面からのみ可能 あるビューを別のデータセットに複数コピーをしないといけない場面に遭遇した。Webの画面からコピーできるのは知っているが、数がちょっとあった & 何回もやって入力ミスがあると面倒だったのでコマ…

BigQueryのデータセットの権限設定を別のデータセットにコピーする

背景 BigQueryの権限設定、Terraformなどを使ってIaCとして管理することが多い しかし、様々な事情で手動で管理しなければいけないケースもたまにある 個別のデータセットに対する権限付与の場合、Web Consoleからぽちぽちとやればよい 「データセットAに対…

無駄打ちになっているBigQueryのスケジュールクエリを列挙する

背景: 無駄なスケジュールクエリを撲滅したい スケジュールクエリは便利 エンジニアでない方でもクエリを手軽に定期的に実行できるようになる 一方で、設定したけどしばらくすると生成したテーブルを全然見てない(=無駄打ちになっている)...というクエリが徐…

BigQuery管理リソースグラフと同等のものをSQLで作る

背景 BigQuery管理リソースグラフって何?って人も多いと思いますが、BigQuery Reservationsを使っている場合に見える管理者用のBigQueryのリソース利用率の遷移が見れるグラフです。こういうやつです、主にスロット関係の統計量をよく見ます。 リアルタイム…

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

背景 SQLのみで様々な要約統計量を出す 応用事例: 定常的な監視への発展 参考: 先行研究 背景 pandasのデータフレームに対するdescribe、ざっとテーブルの要約を知りたい場合に便利です 値域がどうなっているか、数値の分布(パーセンタイル)がどうなっている…

BigQuery Scriptingの便利な使い方をまとめてみた

背景 & Disclaimer 自分自身はこれまでBigQuery Scriptingをほぼ使っていませんでした BigQuery自体は3年くらいの利用歴 SQL単発で済ませるのが苦しそうな場合は、Pythonなどのプログラミング言語 + ワークフローエンジンの組み合わせで戦っており、自分とし…

BigQuery MLでスロット使用量が急増しているプロジェクトやユーザーを異常検知する

背景 どうやって異常を検知するか BigQuery MLでの異常検知 検知できるモデルの種類 共通設定 データの前準備 モデルの学習 モデルを元にスロット使用量が異常に増加していないか予測する 所感 背景 BigQueryはオンデマンドとフラットレート(定額料金)がある…

使われていないにも関わらず定期実行されてしまっているクエリ(in BigQuery)を検知するツールを作りました

TL;DR 背景 実装時の問題点 作った TL;DR データ活用が進んでくると、使われていないにも関わらず定期実行されているクエリが増えてきます 無駄な利用料金、スロットを使っていることになるので、こういったクエリはなるべく減らしていきたいです こうしたク…

BigQueryのビューにパラメータを渡せない欠点をテーブル関数で補おう

背景 (BigQueryに限らず)ビューは便利 テーブル化する必要がないので、ビュー内で参照しているテーブルが更新されたらビューの結果も新しいものを参照できる 物理テーブルと違って、保存料金を気にする必要がない 一方で、ビューにはパラメータを渡すことが…

BigQueryのクエリ結果をMackerelのメトリックとして取得するプラグインを作りました

背景 GCP単独でサービスを構築している場合、Cloud Monitoringはとても便利な監視のためのツールです しかし、サービスをマルチクラウドで構築している場合、複数のクラウドサービスに監視の設定やダッシュボードを作っていく必要があり、面倒です 自分が趣…