2025-04-11

BigQueryの出力フォーマットをprettyjsonに変更する手順

BigQuery

BigQuery の bq コマンドでは、デフォルトの出力フォーマットはテーブル形式が使われます。このデフォルトの出力形式を.bigqueryrc ファイルを使って、件数や行数が多くなっても見やすいprettyjson形式に変更します。

.bigqueryrc ファイルを作成する

ホームディレクトリに .bigqueryrc ファイルがなければ作成します。

touch ~/.bigqueryrc

エディタでbigqueryrcファイルを開きます

vim ~/.bigqueryrc

query セクションに設定を追加

--format=prettyjson

設定が反映されているかの確認

設定前

$ bq query "SELECT CURRENT_DATE()

+------------+
|    f0_     |
+------------+
| 2025-04-11 |
+------------+

設定後

$ bq query "SELECT CURRENT_DATE()"

[
  {
    "f0_": "2025-04-11"
  }
]

数が少ないとあまり効果を実感できませんが、行や列の数が多いと...

$ bq query 'SELECT * FROM bigquery-public-data.covid19_open_data.covid19_open_data LIMIT 10'
[
  {
    "adult_female_mortality_rate": "73.319", 
    "adult_male_mortality_rate": "171.82", 
    "age_bin_0": "00-09", 
    "age_bin_1": "10-19", 
    "age_bin_2": "20-29", 
    "age_bin_3": "30-39", 
    "age_bin_4": "40-49", 
    "age_bin_5": "50-59", 
    "age_bin_6": "60-69", 
    "age_bin_7": "70-79", 
    "age_bin_8": "80-", 
    "age_bin_9": null, 
    "aggregation_level": "0", 
    "area_rural_sq_km": "219047", 
    "area_sq_km": "238400", 
    "area_urban_sq_km": "15594", 
    "average_temperature_celsius": "5.044444", 
    "cancel_public_events": "1", 
    "comorbidity_mortality_rate": "21.4", 
    "contact_tracing": "1", 
    "country_code": "RO", 
    "country_name": "Romania", 
    "cumulative_confirmed": "1346240", 
    "cumulative_confirmed_age_0": "36379", 
    "cumulative_confirmed_age_1": "74690", 
    "cumulative_confirmed_age_2": "133053", 
    "cumulative_confirmed_age_3": "224942", 
    "cumulative_confirmed_age_4": "260809", 
    "cumulative_confirmed_age_5": "233996", 
    "cumulative_confirmed_age_6": "194750", 
    "cumulative_confirmed_age_7": "124220", 
    "cumulative_confirmed_age_8": "63227", 
    "cumulative_confirmed_age_9": null, 
    "cumulative_confirmed_female": null, 
    "cumulative_confirmed_male": null, 
    "cumulative_deceased": "39209", 
    "cumulative_deceased_age_0": null, 

    ...(中略)

このようにとても見やすくなります

2025-02-02

2024年の振り返り

個人的なふりかえり

今更だけど2024年を備忘録として振り返っておく。

私生活

2024年は、結婚式とその準備が私生活のすべてになった。奥様とは2023年10月に結婚し、年が明けた2024年1月に「そろそろ結婚式に向けて動き出したほうがいいかもね」と会話し、結婚式関連の相談カウンターへ行った。すると、あっという間に話が進み、気づけば前撮りの日程、結婚式場、挙式の日取りまで決まっていた。

そこからは、ダイエットを継続する日々と、節目ごとに訪れる結婚式関連のイベントをこなしていく日々だった。和装と洋装での前撮り、遠方での結婚式に向けた現地での打ち合わせ……と、気づけばあっという間に時間が過ぎていた。準備も大変だったが、それ以上に大変だったのは体重を5〜6キロ落とすことだった。仕事がほぼフルリモート（週1回出社）のため、普段は家から出ることが少ない。そのため、毎朝早起きして公園を散歩したり、ジムに通ったりと、意識的に運動する時間を作った。結果として、前撮りも結婚式本番も納得のいく形で写真を残すことができ、本当に良かった。式が終わった後は、好きな食べ物やお酒を自由に楽しめて最高だった。

お仕事

エンタメ企業で引き続きデータエンジニアとして働いている。既存のデータ基盤の運用をしつつ、新しいプロダクトのデータ基盤をゼロから構築するという大きな仕事もこなした。このプロジェクトでは、ETLツールの選定や、それを動かす基盤の設計・構築を担当し、さまざまなデータ基盤を参考にしながら、一から作り上げた。転職後は、すでに存在しているデータ基盤の改修が主な業務であり、前職はSIer・SESだったため、完全にゼロから構築する経験は初めてだった。とても貴重な経験になったと思う。また、メインのデータ基盤以外の部分でも、ドキュメントを整備することの重要性を改めて実感した。長期休暇を取る際や業務の引き継ぎ時に、ドキュメントがなければ他のメンバーに作業を依頼することが難しくなる。そのため、プロダクト外の部分にも気を配ることが大切だと改めて学んだ。もちろん、現在も引き続きデータ基盤の運用・改善に取り組んでおり、より良くしたい点や修正が必要な箇所を見つけながら、楽しく（ときには大変な思いをしながら）日々の業務に取り組んでいる。

その他

2023年と2024年、趣味で集まっているフットサルチームの年間ゴール集動画を作成した。編集には DaVinci Resolve を使用している。動画は公開しないが、個人的には一昨年より去年と、動画のクオリティと作業効率が向上しているのを実感している。2024年は、テロップやサムネイルにもこだわり、より完成度を高めた。幸いなことに、YouTubeには動画編集の教材が山ほどあり、参考になる表現をすぐに学ぶことができる。そして「これいいな」と思ったら、すぐ自分の動画に取り入れることができる。この学びのサイクルが楽しい。最近では、この動画編集の経験を活かして、旅行のVlogも撮ってみようかと考えている。

という文章を書いて、生成AIに添削を依頼したら、とても読みやすい文章になった。生成AIで遊ぶのも楽しいです。

追伸

この広告は、90日以上更新していないブログに表示しています。

という広告を見て悲しくなった。ブログを書いていこうと思った

2024-05-14

【Dataform】pre_operationsを使ってdelete insertを実現する方法

Dataform

Dataform では incremental モードを使ってデータを積み上げていくことができます。しかし、データを積み上げていくことのみしか出来ず、仮に一度実行した処理を再度実行すると同じデータがシンク先に書き込まれます。要はDELETE INSERT には対応していないということになります。

現時点の Dataform の機能としては対応していないということで工夫が必要になり、それを実現するためにpre_operationsを使います。

pre_operations

pre_operationsは SQLX ファイル内に記述し、テーブルの作成前に実行できる SQL ステートメントです。 Dataform の docs の例では、一時的なファンクションを作っています。

  pre_operations {
    CREATE TEMP FUNCTION AddFourAndDivide(x INT64, y INT64)
      RETURNS FLOAT64
      AS ((x + 4) / y);
  }

ref: https://cloud.google.com/dataform/docs/table-settings?hl=ja#execute-sql-before-table

ちなみにテーブルの作成後に実行できる SQL ステートメントpost_operationsも存在します。

pre_operationsの中に DELETE 文を記述することで、同じデータが追加されず冪等性のある処理にすることが出来ます。

次は、WITH のサブクエリ内で作ったソースデータを、sample_table に入れている処理です。 pre_operations の中で、2024-05-13 の日付のデータを DELETE する処理を実行しています。その後、WITH のサブクエリのデータを sink先のtable に insert しています。

ソース元は実際のテーブルへと、また日付は動的に変更できるようにする必要はありますが、これで date 単位でのDELETE INSERTの洗替処理は実行できます。

config {
  type: "incremental",
  schema: "sample_dataset",
  name: "sink_table",
}

pre_operations {
    DELETE FROM ${self()} WHERE date = '2024-05-13'
}

WITH source_table AS (
  SELECT
    DATE('2024-05-13') AS date,
    1 as user_id,
    100 as value,
  UNION ALL
  SELECT
    DATE('2024-05-13') AS date,
    2 as user_id,
    200 as value,
)

SELECT
  date,
  user_id,
  value
FROM source_table
WHERE date = '2024-05-13'

Dataform の公式 GitHub の issue にて質問中...

返事が返ってきませんが、公式 GitHub の issue にて質問をしています。

github.com

現状色々調べた中で pre_operations を使って DELETE INSERT の洗替処理をする方法しかなさそうというところに行き着きました。公式が提供する機能で同じ処理と結果が得られるのであれば、そちらのほうが嬉しいので実現を待ちたいです。

英語の翻訳はChatGPTがよしなにやってくれるので便利ですね。

2024-04-22

DataformCLIの2系を使ってBQに接続するまでの手順

Dataform

2024 現在の Dataform は、Dataform 3 系の開発とttps://docs.dataform.co/（存在しない URL）にあった Docs の移行がおこなわれている。最近 2 系の Docs にすぐ当たれない体験が増えてきたので、備忘録として残しておくー。

今回は Dataform CLI を使って、BQ に接続するまでの手順になる。

バージョン

$ node --version
v20.11.1

$ dataform --version
2.9.0

DataformCLI をインストールする

npm i -g @dataform/cli@^2.9.0

Dataform プロジェクトを初期化する

docs には、dataform initを実行すると dataform プロジェクトを初期化できると書いてあったが、BigQuery を使った Dataform プロジェクトを初期化するためには、下記のコマンドとオプションが必要になる。今回は sandbox-dataform ディレクトリの下で実行していくことにする。

$ pwd
/Users/yoshitaku/workspace/sandbox-dataform

$ dataform init bigquery <YOUR_DIRECTORY> --default-database <YOUR_BIGWUERY_PROJECT> --default-location <YOUR_BQ_LOCATION>

コマンドが成功すると、下記のディレクトリとファイルが作成される。

Writing project files...

Directories successfully created:
  /Users/yoshitaku/workspace/sandbox-dataform/dataform
  /Users/yoshitaku/workspace/sandbox-dataform/dataform/definitions
  /Users/yoshitaku/workspace/sandbox-dataform/dataform/includes
Files successfully written:
  /Users/yoshitaku/workspace/sandbox-dataform/dataform/dataform.json
  /Users/yoshitaku/workspace/sandbox-dataform/dataform/package.json
  /Users/yoshitaku/workspace/sandbox-dataform/dataform/.gitignore
NPM packages successfully installed.

また、@dataform/core もインストールされている。

$ cat dataform/package.json
{
    "dependencies": {
        "@dataform/core": "2.9.0"
    }
}

クレデンシャルファイルの生成

dataform init-credsコマンドを実行すると、実行環境へ接続するためのクレデンシャルファイルを生成することができる。しかしここでも、このコマンドのままでは実行できず、末尾に bigquery を付ける必要があるdataform init-creds bigquery

実行すると対話式で必要情報を入力していく。聞かれることは下記の 2 つになる。

接続する BigQuery が存在している location（おそらく asia-northeast1 を選択することが多い）
Google Cloud のプロジェクト ID

無事に完了すると Dataform CLI から、BigQuery に接続する準備ができたことになる。

サンプルファイルの作成と実行

次のコマンドを実行し、サンプルの SQL を作成する。なお、このコマンドは Dataform の公式 Docs に載っていたコマンドになる。

echo "config { type: 'view' } SELECT 1 AS test" > definitions/example.sqlx

dataform には dry-run のコマンドがあるので実行する。無事に設定が完了しているとコンパイルが実行され、プレビュー実行される。

$ dataform run --dry-run
Compiling...

Compiled successfully.

Dry run (--dry-run) mode is turned on; not running the following actions against your warehouse:

1 dataset(s):
  dataform.example [table]

ちなみに、生成したクレデンシャルファイルを適当なディレクトリへ移動させると dry-run は失敗する。

$ dataform run --dry-run
Compiling...

Compiled successfully.

Dataform encountered an error: Missing credentials JSON file; not found at path '/Users/yoshitaku/workspace/sandbox-dataform/dataform/.df-credentials.json'

2024-03-06

supabaseで全件削除するための小技

Python Supabase

supabase の Python ライブラリであるsupabase-pyでは、2024-03-06 時点で全件削除できるような実装はないらしい。そこで擬似的に全件削除のようなことをしたいときにはひと工夫する必要がある。

supabase.com

削除するときのフィルター条件でneqを使って、idが0では無い行を全て削除するようにすれば実現できる。

from supabase import create_client, Client

url = "YOUR_URL"
key = "YOUR_KEY"
supabase = create_client(url, key)

data, count = supabase.table('YOUR_TABLE').delete().neq('id', 0).execute()

2023-11-24

Google Cloud Next Tokyo ’23 Day2に行った📝

GCP 勉強会・イベントレポート

DMM における AWS から BigQuery へのデータ基盤移行 / 合同会社 DMM.com

背景

1500テーブルほぼ日時処理 biから投げられる30000クエリ/ week 1500mau 新規テーブル・カラムの別作業が5回/week

課題

オペの省力化ができていないため、活用拡大にリソースを割きにくい
ガバナンス
データ活用の拡大
- 今まではデータは社内利用に留まっていたが、広告システムなどの社外利用に拡大したかった

狙い

サーバレスにすること
Google製品に連携が用意できること
- サーチコンソール
- ADs

基盤移行の流れとポイント

データ同期
- S3toGCSはSTSを使って同期している
- GCStoBQはGCSの変更検知を使ってイベントドリブンに対象範囲を絞って更新している
データの差分検知
- AWSとBQで全レコードのmd5を見て差分チェックしていた

information schemaでみたら全体で3.5兆レコードあったらしい

質問

過去分のデータはどうしたか。一時的に二つの環境にデータがあることになるはず？
- 現時点で、もうS3はサポートしない旨を言っていていずれは消す。
- サービスからはデータをGCSに直接送ってもらっている
GCSに置いてあるデータのコスト削減とかは何をしているか
- 外部テーブル群はデータセット単位で見せないようにしてる
- また、日にちで区切ってライフサイクル化してる
  - bqに取り込んだ後はストレージクラスを下げたり、消してしまったり

BigQuery のデータ品質やデータ活用を高める Dataplex 等の活用 / GO株式会社

基盤利用者は100人 / week

課題
- 利用者がすぐにデータ活用できない
- 障害や仕様変更の調査が大変
- いつのまにかデータが壊れている
DataplexとDatahubで課題解決
- Dataplex
  - データ品質管理
- Datahub
  - リネージ
  - メタデータ
Dataplex
- データ周りをまとめた色々製品
  - この中にデータ品質管理がある
  - テスト定義をSQLに変換してテストできたりする
  - レポート配信などで使われる少数の手ブルに導入した
Datahub
- Data CatalogがBQとLookerに対応していないから採用したが、バックエンド技術などがオーバースペックで微妙らしい

質問: - データのテストはどうやってるか - 辞書テストや範囲テスト、鮮度テストのやり方？ - また、この時のSQLファイルはどうしているのか - Dataplexでできちゃうよ - データマートのメタデータの管理はどうしているか - データソース側はinformationからとってきていることはわかったけど、手動でつけてる？ - DataHubでできちゃうよ - 発表にはなかったが、権限管理はされてる？どうしてる？ - 今は全部見せる運用。これからやるならDataplexのデータメッシュで切ってやりたい

Dataform で BigQuery データパイプラインをより効率的に / Google Cloud

Goさんの発表で出てきたDataformが気になったので、追加で見てきた。機能紹介と簡単な事例紹介だった。

2023-11-22

Google Cloud Next Tokyo ’23 Day1に行った📝

勉強会・イベントレポート GCP

基調講演

ハルシレーションを抑えるグラウンディングをし、事実に基づく生成AIにする
Vertexがより協力になっていく話
- Garden
- Codey
  - 企業独自のコードベースから、出力をチューニングしてくれる
  - https://zenn.dev/cloud_ace/articles/20230609codeyapispreview
data residency （今日サービス発表）（drz）
vertex ai searchの日本語提供
Googleワークスペースのduet aiが強力になっていく話
- Meetでの自動翻訳
- 会議の途中経過を要約して教えてくれる
  - 質問して、詳細部分を回答をしてくれたりもする
duet ai in google cloud
- cloud station
- log explorerに自然言語でエラー内容を説明してくれる
- どう治したらいいかも教えてくれる
- Looker studio pro
  - 自然言語でグラフを生成してくれる
    - そこからスライド作成もできる
- Database migration
  - oracleからAlloy dbへ
  - duet aiで自動で変換してくれたり、ダメなところは確認が必要だと提供してくれたりする
- Memorystore for redis clusterがnew

生成AIの話が多く、Google製品とのコラボレーションが熱くなっていきそうだった。知らない製品多めでVertex AIもあまり知らなかったので、色々できること増えていきそうだなと思ったけど、自社に組み込むのはまだまだ先な感じがあるかなぁ

任天堂のデータ分析基盤 / ニンテンドーシステムズ株式会社

データ規模アカウント3.3億ほど 900テーブル

他プロジェクトのBigQueryを見るときは、承認済みデータセットを経由して参照やコピーをしている。
- 承認済みデータセットは2段階に分けて承認してる
元々はdatafusionをつかっていたが、今はdata transformはbatchを
変換はcomposerでdbtを使っている。
データセットごとにdbtを用意してる。プロジェクト間は別で定義
bqは20000スロット
bigquery data transfer service
開発中のデータソースとbqを統計値を使ってデータ比較検証
data visialasionを使っている
フォルダ単位でプロジェクトを分けている
terraformとGHEをつかって権限管理している
- GHAで定期的に実環境とのドリフトを検出
- 構成情報を社内wikiに連携
- 利用者の権限洗い出しをおこなっている
インフォメーションスキーマで利用状況をチェックしてる
- スロークエリを見つけて対処している
- ex, 集計済みテーブルがあるのに生テーブル見てたり、ジョインするテーブルがデカすぎる

質問したかった内容: terraformでの権限管理はどうしている？スロークエリを気付ける仕組みは？今後の展望でのデータカタログの充実をどのようにやっていこうと思っているか今後の展望でのデータ制度の向上をどのようにやろうと思っているか

いろいろ質問事項あったのに、Ask The Spreakerはなかった...

—-

製造業での Cloud Run を中心としたシステム開発コラボレーション事例 / 株式会社LIXIL

cloud shellのteachmeコマンド知らなかった
デモ動画でのハンズオンであった

—-

ゴーゴーカレーのデジタル進化: Google の技術を中心とした新たな航路 / 株式会社ゴーゴーカレーグループ

ゴーゴーカレーのビジネスについてのトーク
- いろんな販路があってすごかった
SmartHR、MoneyForward、OKRや1on1も実施しているらしいこれからアプリの開発もしていく
デジタル方針はGoogle中心主義になることを掲げている。
- Google Cloudのみをデータセンターとして活用
- AppSheet
Google以外はSaaSを使う
- バックオフィスはMoneyForward

2023-11-05

gcloud auth loginとapplication-default loginの違いを整理した

記事の概要

Google Cloud SDKの認証コマンド、`gcloud auth login` と `gcloud auth application-default login` の違いについてまとめた。

目的

`gcloud` コマンドラインツールを使用するユーザー自身を認証するためのコマンドになる。

用途

個人の開発マシンでの作業
手動での `gcloud` コマンドの実行

具体例

Google Cloud Platform上で仮想マシンを手動で作成したい場合、以下のコマンドを使用して自分自身を認証する。

gcloud auth login

その後、以下のようなコマンドで仮想マシンを作成することができる。

gcloud compute instances create INSTANCE_NAME

概要

特定のアプリケーションがGoogle Cloudのリソースにアクセスできるかどうかを確認するための認証。アプリケーションは、バックエンドのサービスやスクリプトなど、人の介入なしに動作するものを指す。

具体例

WebアプリケーションがCloud Storageから画像を取得: ユーザーにプロフィール画像を表示するWebアプリケーションが、Cloud Storageのバケットから画像を自動的に取得する場合。
ログ処理スクリプト: 一日の終わりに、Compute EngineのインスタンスからログファイルをBigQueryに自動的にアップロードするスクリプト。
定期的なデータバックアップ: Cloud Schedulerを使用して、Cloud SQLのデータベースを定期的にバックアップするスクリプト。
自動的なリソースのスケーリング: Cloud Pub/Subのメッセージキューのサイズに基づいて、App Engineのインスタンス数を自動的にスケーリングするアプリケーション。

具体的なコマンド

gcloud auth application-default login

違い

`gcloud auth login` はユーザーの認証のため、`gcloud auth application-default login` はアプリケーションの認証のために使用される。
`gcloud auth login` は主に手動の操作のため、`gcloud auth application-default login` はアプリケーションの開発の際に使用される。

2023-10-02

転職して1年を振り返る

個人的なふりかえり

はじめに

2022の振り返りと称した、半年の振り返りはこれになる。

yoshitaku-jp.hatenablog.com

1年間での主な業務と成果

主に - ETL処理を組む - バッチ処理の面倒をみる - 集計依頼に対応する - データ基盤の速度改善 - データ基盤のコスト削減を実施している。

定常業務的なものは慣れてきてこなせるようになってきた。また自動化出来そうな部分があれば、自動化にも着手していて改善活動にも着手できている。

学びと成長

ドメイン知識以外で新しく吸収できたことはGCPとTerraform周りになる。以前はAzureのクラウドコンソール画面からポチポチと選択してお仕事をしていたが、GCPとTerraformが導入されていることもあり、そちらを0からキャッチアップしていった。自分で0から作る経験と、すでにあるものを改善する経験もし、慣れてきた実感がある。

Pythonで簡単なアプリケーションを作る経験もした。今まで個人的なスクリプトを書くことしかしてこなかったので、指摘される部分が多く、念入りにレビューしてもらった。チームメンバには感謝している。このあたりは次回以降生かしていく。

挑戦と困難

なれることに精一杯で挑戦という挑戦はしてこなかったことは認識している。このあたりは次の年の課題だと思っている。日々出会うことが新しいことだったので、こんなんだとは思っていないが日々困難では合ったかもしれない。プレッシャーに強いタイプではないので、期間が短かったり、先が見通せないタスクだと尻込みをしてしまう面は合ったと自覚している。このあたりも課題である。

1年後の自分への評価

データエンジニアとしてもっと成長していたいので、OKRでいい感じの目標を立ててやっていくつもり。今の段階ではそれぐらいのことしか思っていない。

今後の目標と展望

転職してなれることに精一杯だったので、最近ふとした時に目標や展望がないことに不安になっている（まだまだ出来ないことが多いので、それらのキャッチアップはやっていくとして）。考えても始まらないのでとりあえず手を出すかと考えていて、プライベートの時間を使って新しく学ぶ習慣は崩れてしまっているので、まずはこれを取り戻したいと思っている。そのための振り返りブログだったりもする。

また、世界も落ち着いてきたので、エンジニア仲間とと直接の交流を増やして、エネルギーを吸収したいと思っている日々である。

おわりに

2023-07-11

one secアプリがSNSの時間消費抑えに良かった

小ネタ

SNSの時間を抑えたいなと思っていたところone secにたどり着き、使ってみたところとても良かったので紹介します。

tl, dr

SNSの時間消費にone secがよかった
対象のアプリが起動すると、one secの妨害が始まる
妨害のカスタマイズもできる

one sec とは

one sec
長期的な、ソーシャルメディアの妨害から自由になる
ソーシャルメディアアプリの起動時にone secはあなたに深呼吸を吐かせます。これはシンプルかつ効果的で、摩擦が邪魔なアプリの魅力を削ぎます。

one-sec.app

紹介文にもあるように、SNSの使いすぎ防止を回避する目的で作られました。アプリはApp StoreとGoogle Playで提供されています。無課金でも1個のアプリに妨害設定できるので、まずはインストールしてみるのがいいんじゃないでしょうか。

妨害の設定 - iOS

自分がiPhoneを使っているのでiOSでの設定を紹介します。 iOSでは、one secとショートカットアプリを使って妨害の設定をすることになります。設定の解説は動画があるので、わかりやすく設定することができるかと思います。

www.youtube.com

one secの妨害の種類

妨害の種類は6つあって、

呼吸エクササイズ
呼吸（ミニマム）
反転
- AppleWatchの呼吸のような動きをして、開く前に一度間を置かせる
- 深呼吸が終わったあとに、今まで何回開こうとしたかが画面に出て萎える
- 反転は呼吸と同じ動きをしているので、このカテゴリに入れておく
  - よくわかっていない
ドットを目で追う
- 目で追うとかいてあるけど、実際には輪っかをタップし続けて一定時間追いかけ続けると解除できる
ブラックスクリーン
- 画面が一定時間真っ暗になって、その後開ける
- 真っ黒になると自分の顔が打つづので冷静になる
スマホの画面を回転させる
- デフォルトだと3回スマホ本体を回転させると開くことができる
- 公共の場だとスマホを回転させる変な人に見られたくないので、結果的に開かなくなる