■21/2/22 12:00AM
BigQuery part2
■BQタイムアウト縺?6時間
クエリそのままパフォーマンスを改善 BigQuery パフォーマンス向上テクニッ繧?
■BQ transfer(クエリが不要縺?BQ連携、3rd partyもあり)
サードパーティ転送を使用する | BigQuery | Google CloudCloud Storage の転送の觸??要 | BigQuery | Google CloudAmazon S3 転送の觸??要 | BigQuery | Google Cloudデータセットコピー、GCSファイ繝?Ama S3, Azure storage, Oracle, Salesforce, Ads系軆??々
■Cloud SQL縺?BQからクエ繝?SELECT * FROM EXTERNAL_QUERY("connection_name", "SELECT * FROM db.tbl")BigQuery初心者を脱出したい３（CloudSQL連携クエリ・スケジュールクエリ・??
data taransfer縺?Connectionを設藹??してお縺?
保存先も同じリージョンである必要がある、RDSがosakaだ縺?BQ縺?multi region US等でな縺?osaka縺?
■BQ DMLクォータ鐔??驕?割縺?SQLだとすぐに藹??にあたる
上限がテーブル単位のためテーブル名を分けると回避できるらしいGCP BigQuery 応用編 ~制限につい縺?~ - 自称フルスタックエンジニアのぶろぐ。 (hatenablog.com)BQ streaming insert->BQ storage read/write APIの臀??限縺?DMLと別で、閾値が大きいINFORMATION_SCHEMAを用いたBigQueryのストレージ無饅??遣い調譟? - ZOZO TECH BLOGBigQuery Storage Write API を使用してデータを一括読み込み、ストリーミングする | Google Cloud
APIだ縺?Protocol buffersが必要で、Date/Timestampが対藹??しておらず
Unixエポックからの日謨?/秒数への藹??觸??が必要、、、
■マテリアライズドビュ繝?実体データを保持しリフレッシュ更新で早いため集計等に向縺?ベーステーブルは臀??つ、カウントができない、使用できない関数がある等の制軆??がある
またマテビューはビューを元に臀??成できずテーブルからである必要があるストレージコストは觸??かるが、通常ビューで時間觸??かる計算を頻軆??にする場合は早く安縺?なる可能性があるBigQuery縺?Materialized Viewを使う #データ分析 - Qiita
■BQ同時実行謨?オンデマンドでは使用可能なスロット数に基づき自動的に同時実行数が決藹??され超えるとスロット縺? 空きがでるまでキューに臀??管されるプロジェクトごとにクエリの最大同時実行数は動的に決まる同時実行数の最大値は指定できるが、大き縺?しても実行数が増えることはな縺?、あ縺?まで自動決藹?? 内の方が優先されるEditionsが割り当てられているプロジェクトでは最大同時実行数を譏?示的に設定できる他に、インタラクティブクエリ、バッチクエリ、クエリ順蠎?や上限な縺?BigQueryクエリキューの觸??要まとめ (zenn.dev)
■BigQueryの列レベル・鐔??レベルのセキュリテ繧?BigQueryの列レベル・鐔??レベルのセキュリティを解説 - G-gen Tech Blog個人情報や機微情報を隠す
BigQuery の鐔??レベルのセキュリティの觸??要 | Google Cloud
行レベルなら同じテーブルを使うので同じダッシュボード/Appが使える（AuthorizedViewの方が柔軟だが）
デー繧? マスキングの觸??要 | BigQuery | Google Cloud
列レベルアクセス権以藹??にもマスクの種類があり、繝?ッシュだったり先鬆?4文字や末尾４文字等で共通文字化としてマスク化できる列レベルのアクセス制御の觸??要 | BigQuery | Google Cloud列レベルのアクセス制御によるアクセス制限 | BigQuery | Google Cloud
BQ画面・??左ナビのポリシータ繧? ポリシータグを作成（組織単位で臀??括臀??覧表示・?? タグは髫?層化できるので、全ユーザタグ・??管理者タグ・??社長タ繧? スキーマ＞Addポリシータ繧? タグが付いていればプレビューで鐔??れない select * except(tag_column)にする必要がある メタデータは鐔??れる（カラム名、型 ポリシータグ画面・??対象ポリシー選択＞情報パネルで権限者一隕? fine-grained readerを付荳?する縺?select *ができるようになる 社長タグに社長だけ権限付ける等窶?APIを有効にし、ポリシーを有効にする必要がある
■承鐔??済みビュ繝? authorized view
authorized viewを設藹??するとそ縺?viewを対象とする権限だけ必要で権限をさかのぼり付荳?しな縺?ていい（通常縺?viewは藹??照元の権限も必要） この権限移譲は閲覧権限のみで編集権限等は含まないBigQueryの承鐔??済みビュー設定方觸?? - Qiita被藹??照の元テーブル側に許可するview名を設藹??する
藹??照権限は緩縺?なるが、編集権限は厳し縺?なる（設藹??するビューは藹??更しない前觸??で承鐔??する形・??
authorized viewを付荳?すると軆??関となったビュー縺?dataEditorではビュー更新ができな縺?なる
ソーステーブルにもdataOwnerが必要（通常のビュー臀??成に縺?dataViewerがソースに藹??要）
基本の藹??全軆??縺?authorized view設藹??を外す＞ビュー藹??更・??AV再設藹??がいい 対象縺?AVは管理者を立て臀??元管理するのが良さそう
（テーブルやビューを作って権限付荳?してバッチだ縺?data ownerが必要なのは注意）
■Authorized系にはメリットとデメリット
１) Authorized viewを設藹??するメリット一度設定してしまえばソース側への権限付荳?依頼が不要となりビューの権限管理が省力化できるビューにて閲覧対象を絞ることができソース全臀??は閲覧させないことができる、絞れる
普通のビューは元データへの権限が必要で鐔??せた縺?ないデータへも権限が必要になる場合がある
２) Authorized viewを設藹??するデメリット一度設定してしまえばソース側での権限付荳?依頼が不要となり被藹??照側で許可臀??許可の判断ができな縺?なる、誰にデータ閲覧権限を付荳?しているか把握できず管理が機能しな縺?なる一面がある将来に置かれるソース側のデータの閲覧も許可することになり不用諢?に閲覧が可能となってしまうterraform縺?Authorized view設藹??が剥がれてしまう危険諤?ビューを編集するにあたりAuthorized viewを外す必要がある、あるいはソースにもEditor権限
すぐにビューを変更することができな縺?なる（ビューを一旦削除することはできる）
Authorized viewはビューを削除して、再度臀??り直すと生きている場合がある、ダメな場合も多いが これで觸??洩させた縺?ない情報を一時含められる危険性がある３）authorized datasetを設藹??するデメリット
設藹??時は良いかもしれないが、将来的に諢?図しないデータがDS入った場合も閲覧を許してしまう
↓
データセットは細かく作成してアクセスレベル設定し普通のビューを使う
ソース全臀??を閲覧させられない場合(直接権限を付荳?できない場合)縺?AVを使うメリットがでる
情報觸??洩リスクはどちらも多段ビューで同じ感じ、だがビュー臀??成縺?AV設藹??が生きているバグがデカい
■BQクリーンルームデータ觸??備側でパブリックし、使う側でサブスクする (BQ exploreでペイン縺?Addする)スプシ臀??存できない、開覧数のレポートが見れる(使用者名は鐔??えない) 実はパブ側でサブスクし公開すれば、閲覧縺?JobUserだけで使用できるようになるGAでな縺?、またオンデマンドしか無理、コピペやデータコネクタは可能で觸??蠢?
■ロール割り当て者の出力
カスタムロール縺?Project_Admin、Project_Managerが誰に割り当てられているか
Asset inventoryをBQにダンプしたデータからクエリする
WITH projects AS ( SELECT resource.data AS rsc, ancestor_path FROM prj.cloud_asset_inventory.cloud_asset_inventory_org_resource_now WHERE asset_type = 'cloudresourcemanager.googleapis.com/Project' ), projects_info AS ( SELECT JSON_EXTRACT_SCALAR(rsc, '$.projectId') AS projectid, JSON_EXTRACT_SCALAR(rsc, '$.lifecycleState') AS lifecycleState, ancestor_path FROM projects ), projects_efficient AS ( SELECT * FROM projects_info WHERE NOT REGEXP_CONTAINS(ancestor_path, "folders/apps-script") ), projects_num_adm_mgr AS ( SELECT REPLACE(name, '//cloudresourcemanager.googleapis.com/projects/', '') AS project_num, REPLACE(b.role, 'organizations/1234567/roles/', '') AS role_value, STRING_AGG(REPLACE(m, 'user:', ''), ', ') AS member_value FROM prj.cloud_asset_inventory.cloud_asset_inventory_org_iam_policy_now, UNNEST(iam_policy.bindings) AS b, UNNEST(b.members) AS m WHERE asset_type = 'cloudresourcemanager.googleapis.com/Project' AND (role LIKE '%Project_Admin%' OR role LIKE '%Project_Manager%') GROUP BY project_num, role_value ), projects_adm_mgr AS ( SELECT JSON_EXTRACT_SCALAR(resource.data, '$.projectId') AS project, projects_num_adm_mgr.role_value, projects_num_adm_mgr.member_value FROM projects_num_adm_mgr LEFT JOIN prj.cloud_asset_inventory.cloud_asset_inventory_org_resource_now AS res ON projects_num_adm_mgr.project_num = REPLACE(res.name, '//cloudresourcemanager.googleapis.com/projects/', '') )SELECT projects_efficient.projectid, projects_efficient.lifecycleState, CONCAT(projects_efficient.projectid, ', ', projects_adm_mgr.role_value) AS role_value, projects_adm_mgr.member_valueFROM projects_efficientLEFT OUTER JOIN projects_adm_mgrON projects_efficient.projectid = projects_adm_mgr.projectORDER BY lifecycleState DESC, projectid;
■BigQuery DataFrames + Geminiでデータ分析from google.cloud import bigqueryfrom google.generativeai import GenerativeModel
bq_client = bigquery.Client()
# クエリ藹??行し縺?DataFrame藹??得query = "SELECT customer_review FROM `my_project.my_dataset.reviews` LIMIT 10"df = bq_client.query(query).to_dataframe()
# Geminiモデルの觸??備model = GenerativeModel("gemini-pro")
summaries = []for review in df["customer_review"]: response = model.generate_content(f"次のレビューを要約して縺?ださい: {review}") summaries.append(response.candidates[0].text) # 修正: 正し縺?レスポンスを藹??得
# DataFrameに鐔??約を追加df["summary"] = summaries
table_id = "my_project.my_dataset.review_summaries"job = bq_client.load_table_from_dataframe(df, table_id)job.result()print("データをBigQueryに臀??存しました！")
■BigQuery ML (bqml_llm_infer) + Geminiで感情分析from google.cloud import bigquerybq_client = bigquery.Client()
# クエリ藹??行し縺?DataFrame藹??得query = """SELECT bqml_llm_infer( model_name => 'my_project.my_dataset.gemini_model', prompt => CONCAT('このレビューの感情分析をして縺?ださい: ', customer_review) ) AS sentiment_analysisFROM `my_project.my_dataset.reviews`LIMIT 10"""df = bq_client.query(query).to_dataframe()print(df)
■データセットコピー
DSのコピーはBigQuery.transfer.updateが必要、またビュー/ルーチンはコピーされない問題のあるビューはコピーされない存在しないビューのコピーができない>関連の削除前に全てのバックアップを取得して不要な元ビューを削除する参照権限がないテーブルが含まれるビューのコピーができないコピー先のDSは手動で作成するSQLを使いコピー用SQLを出し、それを実行することでバックアップする
テーブル対象SELECTCONCAT('CREATE TABLE `project.dest dataset.', table_name,"AS SELECT FROM `project.src dataset.', table name, ":") AS ddlFROMproject.src_dataset.INFORMATION_SCHEMA.TABLESWHEREtable_type = 'BASE TABLE',
ビュー対象SELECTCONCAT('CREATE VIEW `project.dest dataset', table_name,"AS', view definition,';") AS ddiFROMproject.src_dataset.INFORMATION SCHEMA.VIEWS:
ルーチン対象SELECTCONCAT('CREATE OR REPLACE',routine_type,project.dest_dataset.', routine_name,routine definition ) AS ddlFROMproject.src_dataset. INFORMATION_SCHEMA.ROUTINES';
■IAM(Identity and Access Management)/// BANGBOO BLOG /// - GCP
前回
/// BANGBOO BLOG /// - BigQuery
Comment (0)
■21/2/21 1:00AM
BigQuery
■Big queryリファレン繧?標準SQLとレガシ繝?SQLがある、違いは・??
標準 SQL のクエリ觸??文 | BigQuery | Google Cloud
標準 SQL への移行 | BigQuery | Google Cloud
標準 SQL のデータ型 | BigQuery | Google Cloud
レガシ繝? SQL 関数と觸??算子 | BigQuery | Google Cloud
レガシ繝? SQL のデータ型 | BigQuery | Google CloudBigQuery: クラウド デー繧? ウェア繝?ウ繧? | Google Cloud（チュートリアルみたいな・??
BigQuery解説：https://beyondjapan.com/blog/2016/03/what-is-bigquery/クエリ処理のツリーアーキテクチャによる分散並列処理複数のサーバーに対してツリー状に拡がっていき、並列にサーバー臀??で同時に分散処理 ルートサーバ＞intermediateサーバ＞leafサーバBigQuery MLという機能を利用すると、機械学習モデルをCloud AI Platform縺?TensorFlowなどに連携させ、クエリ軆??果を素早縺?AIと連謳?Lookerというデータ分析プラットフォームとの連携よりクエリ軆??果を、データ統合、変觸??、分析、可鐔??化、レポーティングすることができ、非常に強力縺?BI
列指向型・カラム型データベース・カラムナストレージ・??一般的縺?RDBMSでは鐔??単位でデータが保存） 必要なカラムのデータを藹??得するだけでよ縺?、またデータは圧縮できるhttps://dev.classmethod.jp/articles/google-bigquery-debut/
GCPプロジェクト＞データセット＞テーブル・??行row列columnで普通のテーブル、ネイティブbigqueryテーブ繝?/Googleドライブのような藹??部テーブル、SQLクエリによるビュー・??
アンス繧?_で藹??まるデータセット名は隠しでコンソールで非表示ジョブは非同期で藹??行され、ステータスをポーリング・??データの読み込み、データのエクスポート、データのクエリ、データのコピーなど・??
クエリ・??ウェブ UI、bq コマンド、BigQuery REST APIの方觸??がある、SQLと同じ？SELECT title, answer_count, view_count FROM `bigquery-public-data.stackoverflow.posts_questions` ORDER BY view_count DESC LIMIT 10BigQuery縺?SELECT tag, time FROM [dataset_name.table_name_20151206]のように藹??要な列だけを選択した場合にはスキャンの藹??を狭めることは可能ですが、LIMITやWHERE句には臀??を書いてもテーブルをフルスキャンしてしまう節約 Ama縺?s3に入れRedshift内でテーブルを分割した後縺?BigQuery
Hadoopでも使繧?れていたGoogle開発のエンジンであるMapReduceは、非構造化データをプログラミングモデルを通して扱うが、巨大なテーブルの軆??合や巨大な出力結果のエクスポートも可能である半面、処理時間は数分間から数日に藹??んだ、だが、BigQueryは、あらかじめデータを構造化し縺?BigQueryのテーブルに格軆??しておかねばならないが、ほとんどのクエリは数軆??で藹??了する
サードパーテ繧? ツール・??データの読み込みや視覚化を行うツールなど・??を使用し縺? BigQuery のデータにアクセス藹??Google Cloud SDKをインストールすればコマンドラインが使える
BQは同一リージョンでない縺?Joinができない、ゾーンはマルチで良い
BQで縺? us 縺? eu がマルチリージョ繝?
22/4現在のリージョンリスト：asia-east1-2、asia-northeast1-3、asia-south1-2、asia-southeast1-2、australia-southeast1-2、europe-central1-2、europe-north1、europe-west1-6、northamerica-norhteast1-2、southamerica-east1、sourthamerica-west1、us-central1、us-east1-4、us-west1-4
パブリックデータに直でアクセスできるSELECT * FROM `bigquery-public-data.usa_names.usa_1910_2013`BigQuery の臀??般公開データセット | Google Cloud
→FROM句の書き方・??他のプロジェクトを指す名前に繝?イフンがあるとバッククォートで囲む必要がある `other-prj`.dataset.table あるい縺? `other-prj.dataset.table`
■標準SQL
先頭鐔??でレガシーか宣鐔?? #standardSQL あるいは #legacySQLバッククォートでエスケープ、プロジェクト区切りも.(ドット)、From句のカンマ縺?Cross joinで全軆??合せかと思繧?れ通常通りjoinやunionを使う事配列が使える、カラム一つに配列を入れて藹??元的に扱えるwithで臀??時テーブルを作れるexceptでカラムを除藹??、replaceでカラムの置き觸??え
select * except(kuso) from afunctionを作って使える 標準 SQL ユーザー藹??義関謨? | BigQuery | Google Cloud分析関謨?over()縺?windowで鐔??算ができる
rank() over (order by x)は臀??記more縺?RFMに使用している
ROW_NUMBER() over (order by timestamp) as id,で採番できる地理関数とかJSON関数とか色々関数もありそうスクリプトで藹??数やIfやLoopが使える 標準 SQL のスクリプト | BigQuery | Google Cloud
join on a.c=b.c縺?join on using (c)とできる
BigQuery 特集: データ操作（DML） | Google Cloud 公藹??ブロ繧?
insert into tbl_dest select * from tbl_source 縺?select結果を挿入できる
■レガシ繝?SQL（標準SQLを使うのが由・??予約語は鐔??かっこを使ってエスケープ、プロジェクト区切り縺?:集計関数縺? WITHIN キーワードを使用すると、レコード内の繰り返しの値が集計?FROM句のカンマは觸??準SQL縺?Cross joinとは異なりUNION ALL 演算子通常縺?SQL処理システムとは異なり、BigQueryは繰り返しデータの処理を前觸??として設計。繰り返しレコードの觸??造を操作するクエリを記述。その方觸??縺?1つが、FLATTEN 演算子?JOINは、INNER、[FULL|RIGHT|LEFT] OUTER、およ縺? CROSS JOIN 演算子をサポート、デフォルトINNER除藹??できる select + from A OMIT RECORD IF COUNT(payload.pages.page_name) <= 80;TOP を使用するには、SELECT 句縺? COUNT(*) を含める分析関謨?over()縺?windowで鐔??算ができる？(標準SQLと同様？)functionを作って使える(標準SQLと同様？)
JSON等のネストをフラット化
■DDL データ藹??義鐔??語ステートメントの使逕? | BigQuery | Google Cloudhttps://www.isoroot.jp/blog/1651/auto_incrementもdefaultもprimary keyもindexもshow create tableないのでは・??CREATE TABLE IF NOT EXISTS bangboo_data.x_xxx ( `no` INT64 NOT NULL, `user_no` INT64 NOT NULL, `name` STRING, `date` DATETIME,)
同じスキーマで臀??ることもできる
CREATE TABLE ore_ds.test003 LIKE prj.ds.test001PARTITION BY _PATITIONDATE
■bqコマンドはコンソールで藹??行できる
ブラウザで該当プロジェクトに入りコンソールボタン、下記ではスキーマをJSONで藹??得できるbq show --schema --format=prettyjson myProject:myDataset.tbl001
bq ls -n 3000 dataset_aho (データセット内のリスト3000件、デフ繧?50件・??)
bq cp --force prj:ds.tbl prj:ds.tbl2上書きコピー・??削除しコピー・??コンソールだと同名コピーや下記ができないbq cp ds.tbl1,ds.tbl2 ds.newtbl2つのテーブルをnewtable にまとめコピ繝?bq cp -a ds.tbl2 ds.tbl1tbl2をtbl1に追加コピ繝? --append_table でも同じ
bq load (csvとかgcsのファイルを読み込む）bq extract (gcsに抽蜃?)bqコマンドの使い譁?: UNIX/Linuxの部藹?? (q-e-d.net)
■データアップロード時のスキーマ指定
自動検出縺?Firestore、Datastore、Avro、Parquet、ORCだけ？ほぼ手動縺?utf-8縺?csvかjsonlかを使う蠖?コンソールで手動スキーマ指定藹??(jsonスキーマを張臀??ける)、modeは省略可でデフォ縺?nullable、JSONスキーマファイ繝?up縺?aqコマンドのみ可、ローカルからup時のコマンドとスキーマ例↓bq load --source_format=CSV mydataset.mytable ./myfile.csv ./myschema.json[ { "description": "quarter", "mode": "REQUIRED", "name": "qtr", "type": "STRING" }, { "description": "total sales", "mode": "NULLABLE", "name": "sales", "type": "FLOAT" }]なお一譌?Google Cloud Storageに放り込んでからやると饅??速 BigQueryにデータをバッチでインポートする - Qiita
COUNT DISTINCTだが、BigQueryでは觸??算値が返って縺?る??。正確な値が必要な場合は、GROUP EACH BY縺?COUNT(*)を組み合繧?せるhttps://www.buildinsider.net/web/bigquery/01
■BQ縺?UTC（Universal Time, Coordinatedの頭文字）
ScheduledQueryを終了譌?6/9 13:00JSTで即時設藹??→6/9 01:20UTCで藹??行された（終了時間縺?JST/UTCの考慮が必要か→SQ実行時間設藹??について縺?JSTかUTCに注諢?するだけ）実行履歴縺?UTCのためJSTに読み替える必要がある(UTC縺?JST縺?-9時間)
■BigQuery機閭?
///クエリ軆??果を別テーブルに書き込む
その臀??＞クエリの設定＞クエリ軆??果の藹??先テーブルを設藹??する
BigQueryで縺?SELECT結果を他テーブル縺?Insert / テーブル觸??い替えなどができる - コード日進月豁? (hateblo.jp)
クエリ軆??果の書き込縺? | BigQuery | Google Cloud
///Saved query
プロジェクトに対して臀??存をして使いま繧?す等ができる
URLでクエリを共有できる
///Federated Query
スプレッドシートやGCSの藹??部ソースをBigQuery縺?範囲の書き譁?:シート1!A1:B100Auto detectにする縺?Header skipを1にし縺?1行目をカラム名として使うといい
注意） シートで觸??成を変えると觸??茶苦茶になる 空觸??のセル縺?nullになる 使う人縺?BQへもスプレッドシートへも両方権限が必要
///パラメー繧?(変謨?)を使う--parameter=min_count:INT64:250SELECT word FROM `prj.ds.t` WHERE AND count >= @min_countパラメータ化されたクエリの藹??行 | BigQuery | Google Cloud
こういう感じでも使えるので縺?
WITH params AS ( SELECT @sheetInput AS p),tmp_pre_processed_src AS ( SELECT * FROM src)SELECT * FROM tmp_pre_processed_src,paramsWHERE tmp_pre_processed_src.a = p
///*を藹??ける_TABLE_SUFFIXを使う(複数テーブルだ縺?union allになる)SELECT year FROM `bigquery-public-data.ds.gsod19*`WHERE _TABLE_SUFFIX BETWEEN '29' and '35'ワイルドカード テーブルを使用した複数テーブルに対するクエ繝? | BigQuery | Google Cloud
BTWで軆??らないと全軆??合で課金が厳しいかも
※ワイルドカード注諢? dataset.product_*と書縺?縺?dataset.product_20190425だけでな縺?dataset.product_special_20190425にもヒットしてしまう
betweenは藹??さいから大きいで、パーティションのないシャーディングテーブル日臀??きつきテーブルでも行ける（From句のテーブルに動的な名前を使うにはこれか、EXE IMEDIATE縺?らいか？）
SELECT year FROM `bigquery-public-data.ds.gsod20*`where _TABLE_FUFFIX between format_date('%y%m%d', date_sub(current_date("Asia/Tokyo"), interval 3 day))
and format_date('%y%m%d', current_date("Asia/Tokyo"))
///時間のパラメータを使う
select * from mytable_{run_time-1h|"%Y%m%d"}実行時間run_time（UTC）から1時間引いた日→mytable_20180214クエリのスケジューリン繧? | BigQuery | Google Cloud
///動的にテーブル名を指定し縺?create table
パラメータや変数や_TABLE_FUFFIXだけでは難しい。変数はテーブル名とは解釈されない、_table_fuffix縺?select分縺?from句に入れwhere句で内容を指定するがcreate分は無理、execute immediateを用いるDECLARE t STRING;SET t = (SELECT CONCAT('x_emp_at', FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY))));EXECUTE IMMEDIATE format('CREATE OR REPLACE TABLE `%s` AS SELECT * FROM `prj.bangboo_data.x_employee`', t);
ScheduledQueryでは出力テーブルの指定が可能でテーブル指定例：table001_{run_time-1h|"%Y%m%d"}縺?OK、なおSQL内に縺?run_timeが使用できない
//動的縺?SQLを作成し実行（組織レベルのメタデータを藹??得DECLARE all_meta STRING;SET all meta = ( with projects AS( SELECT DISTINCT project_id from region-us.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION WHERE project_id NOT IN ('対象外プロジェクト) ), sql AS( SELECT CONCAT('select from`', project_id, "`.`region-us`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS", "\nUNION DISTINCT\n') AS s FROM projects ), concat_sql AS( SELECT REGEXP REPLACE(STRING AGG(s, ''), '(UNIION DISTINCT+)$', '') AS concat_s FROM sql ) SELECT SUBSTR(concat_s, 1, LENGTH(concat_s) - 16) AS all_meta FROM concat_sql);--Scheduled query化ならcreate文にする--EXECUTE IMMEDIATE format('CREATE OR REPLACE TABLE `bq_us_all_dataset` AS %s', all meta);EXECUTE IMMEDIATE format('%s', all_meta);
///既藹??のテーブルをコピ繝?(CREATE OR REPLACE TABLEもあり)CREATE TABLE IF NOT EXISTS bangboo_data.x_employee_copy ( `no` INT64 NOT NULL, `name` STRING,) asselect * from `prj.bangboo_data.x_employee`標準 SQL のクエリ觸??文 | BigQuery | Google Cloudデータ藹??義鐔??語ステートメントの使逕? | BigQuery | Google Cloud
///timestamp縺?datetime
datetime型カラムに縺?CURRENT_DATETIME()、timestamp型カラムに縺?CURRENT_TIMESTAMP()を使う
timestamp縺?UTC、datetimeはローカル的で地域指定ができる
直鐔??3分
SELECT * FROM `aaa.ds.tbl111`
WHERE `date` > DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 3 MINUTE)
//string縺?date
func_approved_routine_a('2021-10-31') 引数がstring型
func_approved_routine_a("2021-10-31") 引数がdate型
///日臀??のキャスト
CAST(date AS STRING)
TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 1 month))
BigQuery縺?StandardSQLで日臀??（date, datetime, timestamp）を変觸??する方觸?? - 寝ても覚めてもこんぴうた (hatenablog.com)
Bigqueryの日時に関臀??する関数全部試してみた ①Date邱? - Qiita
///timeで入っているものを日でサマるSQLselectcount(table_id),sum(size_bytes),date(record_time) as record_dayfrom bq_metadatawhere record_time > TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 3 month))group by record_dayorder by record_day DESC
///有効期限 table expiration
データセットに対して臀??日間かにするか設藹??できるテーブルに対し特藹??の日臀??を設藹??できる
何が起こる？＞データセット自臀??は觸??るが中のテーブルが無縺?なる
///パーティショ繝?
パーティション分割テーブルの觸??要 | BigQuery | Google Cloud
BigQuery縺?StandardSQLで日臀??（date, datetime, timestamp）を変觸??する方觸?? - 寝ても覚めてもこんぴうた (hatenablog.com)
パーティション分割テーブル縺?2種饅??：パーティショニングとシャーディン繧?
笳?シャーディングテーブ繝?
カラムの藹??減OK、スキーマとメタデータを持ち権限検証され オーバヘッド有り、ワイルドカード誤操作しやすい→保存向き笳?パーティションテーブ繝?
クエリが早い、カラムの藹??減に対応できない、上限4000位→利用向き笳?シャーディングテーブルにパーティション設定
各シャーディン繧?tblでパーティションを持たせる、特觸??用途で通常どちらか縺?
TIMESTAMP 列縺?DATETIME列では、パーティションを時間単位、日単位、月単位、年単 位のいずれ縺? SQで自動的縺?timestamp縺?DAYになる、SQ実行頻度から自動設藹??される?ワイルドカード誤操作 *だ縺?_fuyou_20240401等の想藹??外も含むため_202*にする等の考 慮が必要シャーディングの臀??り方、yyyymmではダメだった、create文でテーブル名縺?yyyymmddを 付ける あるい縺?SQのテーブル名縺?_{run_time-2h["%Y%m%d"}等シャーディングはテーブルごとに権限を付荳?が必要で日臀??別なら実質無理でデータセットで権限管理が必要
クラスタリング も同時に考慮したいBigQueryのパーティションとクラスタリングについての解隱? - G-gen Tech BlogBigQuery上のデータマートをクラスタ化したらクエリコストが９割カットできた隧? - エムスリーテックブロ繧? (m3tech.blog)事前にソートし、まとまりを作っておく仕組み。インデックスのようにカーディナリティが高いカラムを指定してあげると列指向のため全スキャンしな縺?て良縺?なる。圧縮軆??も上がり 保存費用も削減できる。WHERE で指定あるい縺? GROUP BY される複数列をクラスタ化列として指定するが、指定の順番が重要。まずパーティションが考慮され、次に最初にクラスタ指定した列で鐔??がソートされ、次にその中縺?2番めに指定した列でソート、次縺?3番逶?...となるCREATE TABLE ds.tbl_cls (purchase_dt DATE, prod_id STRING, prod_name STRING)PARTITION BY purchase dtCLUSTER BY prod_id
１）パーティショニン繧?
BigQueryでパーティション分割テーブルを作成する - goodbyegangsterのブロ繧? (hatenablog.com) を見よ
パーティショニングは臀??前に臀??ってお縺?こ縺?
上限が4000のため最大日単位縺?11年、時間単位縺?5か月縺?らい縺?partition_expiration_daysも指定しておく事
CREATE TABLE sample.n225 ( trading_day DATE NOT NULL OPTIONS(description="藹??引譌?"), closing_quotation NUMERIC NOT NULL OPTIONS(description="終蛟?"), opening_quotation NUMERIC NOT NULL OPTIONS(description="始蛟?"), high NUMERIC NOT NULL OPTIONS(description="高蛟?"), low NUMERIC NOT NULL OPTIONS(description="菴?蛟?")) PARTITION BY DATE_TRUNC(trading_day, MONTH)OPTIONS ( partition_expiration_days=1825, require_partition_filter=true, friendly_name="日軆??225時系列デー繧?", description="月別パーティションされた、201901から202107までの日軆??225時系列デー繧?", labels=[("environ", "dev")])
クエリ縺?partitioned byのヤツで軆??れば良いselect * from aaa_history wehre#ParticionIDで軆??る（つーかpartitioned byのヤツで日臀??をキャストし縺?UTCをJST日臀??縺?date(rec_time) = date(datetime_add(datetime "2000-10-10 00:00:00" interval -9 hour))
AND#実際の時間で軆??る、パーティションが日臀??区切りなので時間検索だけなら全件検索になるdatetime(rec_time) between datetime_add(datetime "2000-10-10 00:00:00" interval -9 hour)
and datetime_add(datetime "2000-10-10 00:59:59" interval -9 hour)
２）シャーディン繧?
シャーディング縺?_TABLE_SUFFIXを使ったり、テーブル名に繝?ードコーディングする。
日臀??のキャスト select * from `task_*` where _TABLE_SUFFIX = REPLACE(CAST(date AS STRING), '-', '')
DROP TABLE `task_*`のようにワイルドカードは削除時は使えない
大驥?削除は臀??記のよう縺?bq cmdリストを作りBashで。（Terminal貼りつけでも可・??
シャーディングはデータセット別にしてデータセットごと觸??すようにしたいが
Delete BigQuery tables with wildcard - Stack Overflowselect concat("bq rm --project_id prj -f -t ",table_schema,".", table_name, ";" )
from INSERT_YOUR_DATASET_NAME.INFORMATION_SCHEMA.TABLES
where table_name like "INSERT_YOUR_TABLE_NAME_%"
order by table_name desc
シャーディングテーブルのビュー化 (Authorized view/routineの設定も必要)■ その・??(_TABLE_SUFFIXカラムが付縺?が、全期間藹??得できる)CREATE OR REPLACE VIEW ds.tablen_snapshot_all ASSELECT *,_TABLE_SUFFIX AS table_suffixFROM gcp-prj-name.ds.tablen_snapshot_**WHERE_TABLE_SUFFIX BETWEEN '20200101' AND FORMAT_DATE('%Y%m%d',CURRENT_DATE())↓下記のように使用するSELECT FROM `ds.tablen_snapshot_allWHERE table_suffix BETWEEN '20250530' AND '20250601'あるい縺?SELECT FROM tablen_snapshot_allWHERE table suffix = '20250601'
■その・?? (テーブル関数のため単一日臀??のみ藹??得)CREATE OR REPLACE TABLE FUNCTION ds.fn_tablen_snapshot_by_date(date_str STRING)ASSELECTFROM gcp-prj-name.ds.tablen_snapshot_**WHERE TABLE_SUFFIX = date_str;↓下記のように使用するSELECT FROM `ds.fn_tablen_snapshot_by_date("20250601");
削除されたテーブル縺?7日以内なら復元することも可閭?
テーブルの管理 | BigQuery | Google CloudBQタイムトラベル縺?2-7日前のデータを見れる
タイムトラベルを使用した履歴データへのアクセ繧? | BigQuery | Google Cloud
///UNNEST
UNNESTを知らない縺?BigQueryを使えない？ | 4番は司令塔 (pep4.net)
Bigquery縺?UNNESTを使いこなせ！クエリ効軆??１００% | by Eureka EngineeringARRAY を一組の鐔??にフラット化するには、UNNEST 演算子を使逕?
SELECT id, title FROM games, UNNEST(titles) AS title
idtitles1[skyrim, fortnite]2[atvvsmx, mario]↓フラット化
idtitle1skyrim1fortnite2atvvsmx2mario
ただしUNNESTで指定したカラムが空の配列やNULLの場合、該藹??行は無縺?なってしまうので注諢?
id=3 titles=[]やid=4 titles=NULLの時縺?id=3,4は藹??っ張れないというこ縺?
select * from unnest(['aaa', 'bbb']) as baka -> rowとし縺?2行出るselect ['aaa', 'bbb'] as baka -> 1行目に配列として全て含まれ出る
sql - How to query multiple nested fields in Bigquery? - Stack Overflow
Unnestでもflattenができず空觸??ができる場合、結局left join
空を含むカラム縺?Selectに觸??し、repeatedのカラム縺?left joinで縺?っつける
VariantsをunnestしてるがPricesもrepeatedなの縺?left joinのものを出している
repeatedもarrayと同じらしいが、、、cross joinやarray_to_stringもやったが駄目だった
なおrepeated以藹??縺?unnestが効かないそれでも駄目ならselect句の指定方觸??やwhere句で軆??ると空欄が抜けたよ
select Productid,Variants.SKU,Variants.Size
,Prices.Currency,Prices.Country
from `ga-export-0000.feed.feed_dev`
,UNNEST (Variants) AS Variants
LEFT JOIN UNNEST(Variants.Prices) as Prices
///ARRAY型縺?STRUCT型
標準 SQL のデータ型 | BigQuery | Google Cloudとある古典的SQLおじさん縺?BigQuery入門：ARRAY型縺?STRUCT型 | DevelopersIO (classmethod.jp)
BigQuery縺?STRUCT型とうまく付き合う - QiitaArrayは臀??縺?Unnestを藹??照。
Structは觸??造体型。順序付きで親子の觸??造を持つ。各フィールドはデータ型（必須）とフィールド名（オプション・??を持つ。
array型 unnestできる、[]なの縺?array_length()で数が藹??れるstruct型 unnestできる、ネストを含みスキーマ縺?record型と表記される、struct型の藹??へ縺?.ドットで指定す
string縺?JSON縺?json_extractを使う
配列との絡み縺?json_query_arrayを使う、2段髫?縺?らいは関数で対処できるがそれ以臀??縺?with句がいい
BigQueryでの鐔??雑縺?JSON文字列の扱い方と注諢?轤? - Qiita
JSON functions | BigQuery | Google Cloud
CREATE TABLE IF NOT EXISTS `bangboo-prj.ds.x_list` ( `record_time` TIMESTAMP, `name` ARRAY)INSERT INTO `bangboo-prj.ds.x_list` (`record_time`,`name`) VALUES (CURRENT_TIMESTAMP(),['a','b'])
struct型(record型)は藹??や孫でヒットすれば親を含めて表示されてしまう見やす縺?するため*ではな縺?、カラムを特藹??すると空欄が表示されな縺?なり親が出な縺?なり理解しやす縺?なる（必ずカラム指定したい）
Array=String Repeatedつまりリスト(配列)に値を入れる書藹??（下記縺?2つしか入らない）insert into aaa (aaa) value ("['aaa','bbb']") value has STRING
insert into aaa (aaa) value (`['aaa','bbb']`) Unrecognized name: `['aaa','bbb']`
insert into aaa (aaa) value (['aaa','bbb']) OK
insert into aaa (aaa) value ('["aaa","bbb"]') value has STRING
insert into aaa (aaa) value (`["aaa","bbb"]`) Unecognized name
insert into aaa (aaa) value (["aaa","bbb"]) OK
insert into aaa (aaa) value ([`aaa`,`bbb`]) Unrecognized name
insert into aaa (aaa) value ([aaa,bbb]) Unrecognized name: aaa
insert into aaa (aaa) value ([123,456]) Value has type ARRAY
例）権限が変繧?っていないかの確認する等降順で最新の日臀??のアイテムを見る、そして最終ページの古い日臀??のアイテムを見るそしてそれらを比較するselect record_time, name, asset_type, m, b.rolefrom cai_iam_policy_history,unnest(iam_policy.bindings) b,unnest(b.members) mwhere record_time between timestamp('2021-05-01') and timestamp('2021-06-30')and b.role in ("roles/bigquery.dataViewer", "roles/bigquery/jobUser")and m like '%ketsu@bangboo.com%'and ancestor_path like '%ketsuproject%'order by record_time descSQL解説）struct型が沢山入っていても全驛?unnestしfromに入れればいい from a, unnest(iam_policy.bindings) b, unnest(b.members) m unnest(iam_policy)はできないの縺?2髫?層目から 一つ髫?層臀??で縺?unnest時に別名を付けて臀??の髫?層はその別名縺?unneststruct型の藹??へ縺?.ドットで指定すればいい、フラットでな縺?てもbでも藹??得ができる
↑
通蟶?SQLは「陦?.カラム」だが「親カラム.子カラム」なので、出元がどこかテーブルを探すかスキーマ内を探すかで迷う
///json_extract, json_extract_scalar
2番目の藹??数はパ繧?
BigQueryでの鐔??雑縺?JSON文字列の扱い方と注諢?轤? - Qiita
標準 SQL 縺? JSON 関謨? | BigQuery | Google Cloud
with t as (
SELECT unco_data AS col_1 FROM `kuso`
WHERE date = "2021-08-04"
)
SELECT
json_extract(col_1, '$.color') as unco_color,
json_extract(col_1, '$.temperature') as temperature, json_extract(col_1, '$.fart.times[0].stink') as first_stink,
FROM t
///Pivot
BigQuery縺?PreviewになったPIVOT縺?UNPIVOTを試す | DevelopersIO (classmethod.jp)
【SQL】クロス集計を扱う。PIVOT句縺?UNPIVOT句についてコードを雕?まえて解説。 | ポテパンスタイ繝? (potepan.com)
集計をして鐔??を列に藹??觸??（生ログをある単位でまとめカラムにする）
--tool縺?activeがonなら1、nullなら0でユー繧?A縺?Bの状觸??を見る
SELECT * FROM (
SELECT user, tool, active FROM `tools`)
PIVOT(
MAX( IF (active IS NOT NULL, 1, 0))
FOR user IN ("a", "b")
)
↓
tool a b
------------
axe 1 0
sword 0 1
※藹??考にピボットテーブ繝?集計して鐔??を列に藹??觸??、生ログをある単位でまとめる 生ログが「日 店 金額」の場合 ↓ ピボットで「日 金額 （店１ 店２ 店３）」にする等で、各項目を行と列と値に配置し直す
BigQuery縺?PreviewになったPIVOT縺?UNPIVOTを試す | DevelopersIO (classmethod.jp)
PIVOTの中は藹??数でないとだめだが、
Execute Immediate なら動的にイケる、
がGoogleSheet縺?ConnectedSheetではサポートされておらず無理という罠
///縦持ち横持縺?
pivotは集計関数を用いる、単純の入れ替えならSQLならこちら
[SQL]データの縦持ち、横持ちを入れ替える | DevelopersIO (classmethod.jp)
///新旧の差分比較したいデータの共通してい部分で藹??部軆??合をし縺?null部分を探すWITH old_e AS ( SELECT * FROM status WHERE user IN ('a@old.com') ), new_e AS ( SELECT * FROM status WHERE user IN ('a@new.com') )SELECT * FROM old_e o FULL OUTER JOIN new_e n ON o.id = n.id AND o.date = n.date WHERE o.id is null OR n.id is null ORDER BY o.id, o.date
union縺?except distinctをSQLを付けると差分になるhttps://qiita.com/tatsuhiko_kawabe/items/2537c562c6d99f83e37bSELECT * FROM item.item_table EXCEPT DISTINCT SELECT * FROM item.item_table WHERE user_id = 'A' 1つ目の軆??果から2つ目を引いたものを出す
///REGEXP_REPLACE 正鐔??表現で文字を削髯?
WITH markdown AS (SELECT "# Heading" as heading UNION ALL SELECT "# Another Heading" as heading)SELECT REGEXP_REPLACE(heading, r"^# He", "") AS htmlFROM markdown;標準 SQL の文字列関謨? | BigQuery | Google Cloud
///スラッシュで分割する縺?arrayになるのでオフセットで藹??得select SPLIT(path, "/")[OFFSET(3)] from www
スラッシュの最後を藹??るARRAY_REVERSE(SPLIT(aaa, "/"))[SAFE_OFFSET(0)]引き当てが無い場合縺?SAFE_OFFSET縺?Nullを返し、OFFSETはエラーを返す
BigQueryの觸??準SQL縺?GROUP_CONCATしたいとき縺?STRING_AGG - GAミント至臀??主義 (hatenablog.com)
逆にまとめるに縺?
SELECT type, STRING_AGG(DISTINCT name) FROM testData GROUP BY type;赤身 | ブ繝?,い繧?し,ア繧?,マグ繝?,カツ繧?,サバ白身 | タ繧?,タ繝?,フ繧?,サ繧?
///Job kill
CALL BQ.JOBS.CANCEL('job_id')
CALL BQ.JOBS.CANCEL('project_id.job_id')
job idでエラー詳細を確鐔??
bq show -j bq show --project_id bangboo_sandbox --format json -j bqjobidxxxxxxxxxx | jp . job idはコンソール縺?BQのジョブ詳細やスクリプトキックならロギングから見つけてもいい クエリならjob/query historyで繧?かるがbq cmdでもエラーが返る
bq query --nouse_legacy_sql 'select ketsu from `prj`.oshi.ri'
unrecognized name: 'kusofuke@ketsu.com' at [1:149]
select * from prj.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECTwhere job_id ="aaaaa" and creation_time > "2022-01-01"
ジョブIDの藹??得
SELECT
project_id,
job_id,
user_email,
creation_time,
start_time,
--query, total_slot_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
--`region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
--`region-us`.INFORMATION_SCHEMA.JOBS_BY_FOLDER
--`region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE state != "DONE"
--state = "RUNNING"
--state = "PENDING"
AND user_email = 'my@email.com'
AND project_id = 'paa'
AND start_time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 MINUTE)
AND total_slot_ms > (1000 * 30)
AND PARTITIONDATE BETWEEN '2021-01-01' AND '2021-01-02'
--PARTITIONTIME BETWEEN TIMESTAMP('2021-01-01') AND TIMESTAMP('2021-01-02')
///upsert(アップデートか新鐔??インサートhttps://swfz.hatenablog.com/entry/2021/02/08/195024MERGE aaa target USING tmptbl src ON target.time = src.timeWHEN MATCHED AND src.satus = 'rejected' THEN DELETEWHEN MATCHED THEN UPDATE SET ...WHEN NOT MATCHED THEN INSERT ROW
///window関謨?
集約関数・??GROUP BY）だと個別データは出力されず集計データだけでるがwindow関数だと集計データが個別データ縺?outer joinされた形で出力される分析関数のコンセプト | BigQuery | Google Cloud
SELECT deptname, id, salary, AVG(salary) OVER (PARTITION BY deptname)FROM emp;
deptname | id | salary | avg_salary-----------+-------+--------+------------- dev | 11 | 5200 | 5020 dev | 7 | 4200 | 5020 dev | 9 | 4500 | 5020 dev | 8 | 6000 | 5020 dev | 10 | 5200 | 5020 hr | 5 | 3500 | 3700 hr | 2 | 3900 | 3700 sales | 3 | 4800 | 4866 sales | 1 | 5000 | 4866 sales | 4 | 4800 | 4866
deptnameでグループしそ縺?salaryの集計縺?AVGが出ている
下のよう縺?over()が空でも良い、4900は大体
SELECT deptname, id, salary, AVG(salary) OVER () AS avgFROM emp;
deptname | id | salary | avg-----------+-------+--------+------------- dev | 11 | 5200 | 4900 dev | 7 | 4200 | 4900 dev | 9 | 4500 | 4900 dev | 8 | 6000 | 4900 dev | 10 | 5200 | 4900 hr | 5 | 3500 | 4900 hr | 2 | 3900 | 4900 sales | 3 | 4800 | 4900 sales | 1 | 5000 | 4900 sales | 4 | 4800 | 4900
関数としては集計関数がそのまま使えるようだOVER縺?window関数を使う宣鐔??、OVERの藹??にどのよう縺?windowを作るのかを定鄒?PARTITION縺?windowでつまりどの軆??囲でグループを作るか指定
AVG(salary) OVER (PARTITION BY deptname, sub_deptname) でサブデプト単位での平均となる
///誰が実行しているかをセッションユーザで出す
標準 SQL のセキュリティ関謨? | BigQuery | Google Cloud
SELECT SESSION_USER() as user;+----------------------+| user |+----------------------+| jdoe@example.com |+----------------------+
///エラー繝?ンドリン繧?
BQのクエリ内の条件によりerror()でエラーが吐ける select error('id is not unique.') from tbl having count(a) > 1
ERROR関数を使ったBigQueryデータ異常検知臀?? #BigQuery - Qiita
SQだとメール送信したり、ロギングやモニタリングでエラー觸??知できる
///プログラムで使う
https://googleapis.dev/python/bigquery/latestfrom google.cloud import bigqueryclient = bigquery.Client()QUERY = ('SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013`')query_job = client.query(QUERY)rows = query_job.result()for row in rows: print(row.name)
///Pythonも含めトランザクショ繝?/// BANGBOO BLOG /// - GCP script
■saturationの場合、詰まっている、サチっている
対象にクエリを発鐔?? select 1
同プロジェクトの臀??のテーブルにクエリを発鐔?? select 1
別プロジェクトから対象にクエリを発鐔?? select 1
reservations縺?overviewを見る
対象縺?SQLを発鐔??
別のプロジェクトで同SQLを発鐔??
時間を比べる
Google側の問題と思繧?れるとき縺?Googleのサポート縺?Go
Google Could Status Google Cloud Status Dashboard
INFORMATION_SCHEMA < Audit log で調譟?メタデータ・??データに対するデータ・?? システムメタデータ・??作成更新日時、サイズ、誰いつ藹??辣? ビジネスメタデータ・??オーナ、更新頻度、カラムの諢?蜻?select * from prj.ds.INFORMATON_SCHEMA.TABLESselect * from prj.ds.INFORMATON_SCHEMA.PARTITIONS longterm storageでサイズが100000b以臀??で、更新日が1か月以臀??とか出せるselect * from prj.ds.INFORMATON_SCHEMA.COLUMNS where column_name like '%kuso%'select * from prj.ds.INFORMATON_SCHEMA.VIEWS where view_definition like '%kuso_table%' view_definition縺?SQL文が入っているselect * from prj.ds.INFORMATON_SCHEMA.JOBS_BY_(USER / PROJECT / ORGANIZATION) 誰アクセ繧?/誰臀??った/何Job等も分かる、180日しか出せないが roles.bigquery.resourceViewerが必要 カラム例：user_email、query、referenced_tablesAuditlogは プロジェクト間で使用されるBQでも情報が藹??れる info_schema縺?jobs_byとほぼ同じ内容が藹??れるがよりリッチ 利用ユーザ数、旧データを見ている人、権限変更操作ログ軆??
///Authrized function
認可觸??み関数の臀??成 | BigQuery | Google CloudSELECT `b-sandbox`.test_ds.count_row(1); で藹??行できるUDFやテーブル関数のルーティンを承鐔??してお縺?と誰からでも使える（ビューと違い権限管理できずセキュリティがズブズブになると思繧?れ） target_prj.trg_dsに藹??け入れる関数を共有指定する蠖? UDFは戻り値がある、テーブル関数は副蝠?い合繧?せとして使う形か
///ScheduledQueryの藹??行者コンソールの場合：コンソール操作者Terraformの場合：Terraform実行者bqコマンドの場合：任諢?に設定ができるサービスアカウントをbqコマンド縺?SQ実行者として登録する場合、通常は問題がないがスプレッドシートを使用するなら@プロジェクト名.iam.gserviceaccount.com等でアクセス権が必要なため、会社のポリシーによってはうまく行かない。batch@unco.comのような共通メールを作成し使用したい。（GWS側縺?OUを使いTrusted ruleによりSA縺?GoogleDriveへアクセス許可すると問題回避できるが：OUをつ縺?りそ縺?OU内で藹??用共有ドライブを作成し設藹??する）
サービスアカウント縺?ScheduleQueryを実行させる設藹??に藹??要な権限https://cloud.google.com/bigquery/docs/scheduling-queries?hl=ja設藹??操作者 BQ job user（クエリ臀??成ができない） BQ transfers.get/update BQ data viewer/editor iam ServiceAccountUser(対象SA、PRJレベルでも良いが広縺?impersonateできてしまう)
→対象SAのみならlistも含むがlistが不足しているとされ serviceAccountViewerをPRJレベル臀??荳?も必要 保存先DS縺?BQ admin等へ縺?setiam系が2026/3から必要サービスアカウント BQ job user BQ data viewer/editor
窶?BQ transferは臀??要だった
Scheduled queryからの臀??存先
コンソールだと同じプロジェクト内だが、create文を自由鐔??載ならどこでもOK
job userは同じプロジェクトの権限が必要
設藹??者一覧を出したい場合bq --format=json --project_id=bangboo-oketsu ls --transfer_config --transfer_location=us | jq.[].namebq --format=json show --transfer_config project/1111111/locations/us/tranferConfigs/111111 | jq .ownerInfo.email
■SQLはカラム数の藹??加数で觸??成考える？
left outer joinはカラム数がカラム数の合計から共通縺?join onのカラム数を引いた数・??行数縺?leftに同じ）
full outer join はカラム数がカラム数の合計から共通縺?join onのカラム数を引いた数・??行数縺?leftの鐔??素数縺?rightの鐔??素数を合計したもの・??
unionは重複を除藹??し表を足し合繧?せるため行数が両表の合計行数・??カラム数は合致藹??要でカラム数は藹??繧?らない）unian allは重複を除藹??せず表を足し合繧?せるため行数が両表の合計行数・??カラム数は合致藹??要でカラム数は藹??繧?らない）cross joinはカラム数が両表のカラム数の合計、行数は両表の鐔??数の觸??け算
再帰的縺?SQL処理縺?cross joinし条件を付けるか？ 標準SQL縺?From句のカンマ縺?cross joinとなる
with句は副蝠?い合繧?せを見やす縺?したも縺?
distinct縺?(組み合繧?せ縺?)一諢?になる行のみにし重複を省縺?
UNION 縺?UNION ALLの違い - Qiita
CROSS JOIN （クロス軆??合）を使ってデータを藹??得する - JOIN （結合）を使いこなそう - SQL Server 入門 (sql55.com)
SQL縺?DISTINCTとは・??（OracleやMySQLで使用する方觸??） | IT職種コラム (it-kyujin.jp)
デカい表をい縺?つか持縺?JOINすると再帰的縺?Where句で条件検索しな縺?てよい
■課金
クエリ課金：使用しているプロジェクトで課金される、データの置き場所ではない
定額フラット：$2000/100slot/m(全プロジェクトでスロットを共有)、オンデマンド：$5/T=2Gスキャン縺?1円位
flat rateでもflex slotsとして時間帯によりスロットを増やす等ができる
Editionsに藹??更になった：組織縺?5プロジェクト等しかreservationを置けない、その中で限りなく設藹??ができる 課金を減らすには・??カラムを減らす、パーティショニン繧?
プレビューを活用・??しかしビューだとプレビュー觸??能はない。列が501列以臀??あったら501列以降はプレビュー出ない
データ臀??管課金：データ驥?
$1/50G/m active storageからlong term storageへの移行は自動(90日藹??更がない、50%off)
6,000スロットを使うBigQueryのリソース配分最適化への挑謌? (plaid.co.jp)
■定額蛻?Editionsスキャンサイズが大き縺?てコンピューティングが少ないならリザベーショ繝? (Editions)が向いている、スキャンサイズが蟆?さ縺?てコンピューティングが多いならオンデマンドが向いているスロット消費驥?=データ驥?とコンピューティン繧?１）データ驥?: Read驥?/スキャン驥? (スキャン驥?が多縺?ても単純クエリならスロット消費が少な縺?単純な觸??例ではない)２）コンピューティング鐔??闕?: CPU/メモリ觸??雋? (結合/集合/フィル繧?/ソート/大驥?JOIN/複雑なウィンドウ関数軆??で重いクエ繝?)
■オンデマンド必要なクエリ以外は別の藹??額制のプロジェクトで藹??行するよう変譖?オンデマンドはスキャンしたデータ驥?で料金が決まる 実行しているクエリのスキャン驥?で料金を見積もるパーティション、クラスタリングでスキャン驥?を減らす BigQueryのパーティションとクラスタリングについての解隱? - G-gen Tech BlogSELECT縺?NG。必ず必要な列だけを譏?示する。スキャン驥?のモニタリングとアラート設藹??を実施する。同じクエリならキャッシュが利縺?ため定額のエディションが有蛻?(24h) ソースの更新軆??でキャッシュが無効になるよ BigQueryでクエリのキャッシュを回避する方觸?? | GLASS BLOG (キャッシュされない条件) Bigquery キャッシュについ縺? #GoogleCloud - Qiita (キャッシュ設定/有効無蜉? キャッシュに臀??存されているクエリ軆??果の使逕? | BigQuery | Google Cloud
■権限
事前定義ロールと権限 | BigQuery | Google Cloud
job user：select文クエリ藹??行だけでもジョブ縺?job user縺?data viewerが要る（data viewerだけでは臀??足・??
課金プロジェクト縺?job userを持ち、藹??照先プロジェクト縺?data viewerを持つという権限構成だから
例え縺?job userがな縺?data ownerだけの場合はデータセットやテーブルやビューの削除臀??成ができるが、データロードやselect文発鐔??はできない
IAMかデータセット/tblに藹??要な権限を付荳?する
data editorでも自分で臀??成したものは自分がOwnerになり削除や変更権限がある
meta data viewerならDSとテーブル臀??覧、テーブルのスキーマや容量等の情報が見れデータは鐔??れない
これを広く付けてお縺?とデータ管理が讌?
■サービスアカウントに対するBQ job userコンソールであれば画面左上の鐔??求先プロジェクトで切り替えができるがスクリプトであれ縺?gcloud auth login時に切り替えるgceならインスタンス縺?SA設藹??するが 請求先プロジェクトのデフォルトはインスタンスの置いている/SAが作成されたPrj
※同プロジェクトからしか選択ができない コード上で切り替えができる bq --project_id=xxx query 'select count(*) from ds.tbl'
/// BANGBOO BLOG /// - BigQuery part2
(More)
Comment (0)
■21/2/11 12:00AM
Python Python
■import縺?from
Pythonのモジュール縺?import縺?from入門 - QiitaPython, importの使い方・??from, as, PEP8の推奨スタイ繝?, 注諢?点など・?? | note.nkmk.me
Pythonインポート周り徹藹??理解への道 - Qiita
Pythonの相対インポートで臀??位ディレクトリ・サブディレクトリを指定 | note.nkmk.me
標準ライブラリならimport文を本臀??に書いていれば良い
標準ライブラリ以外縺?PyPI（Python Package Index）と呼ばれる3rdパーティライブラリから
pip（The Python Package Installer）インス繧?
import文を使って臀??記縺?3つなどをインポートし使う
標準ライブラ繝?
pipでインスコしたパッケー繧?
自臀??のパッケー繧?
自臀??は大体繧?かるがそれ以藹??はどこにあるのか？ $ python >>> import sys
>>> sys.path
でパス臀??覧が出るので探すと分かる >>> exit()縺?pythonコマンド終了
例え縺? Cloud functionsなら requrements.txt縺? google-api-python-client==3.3.2と鐔??載し
PyPI · The Python Package Index でバージョンを探す
コード縺? from google.cloud import bigqueryと宣言する
requirementがpipインス繧?
import フォルダ.ファイル名from フォルダ.ファイル名 import * 上下同じだが、fromは臀??部を指定し直接使うという諢?、*は非推螂? つまりimport hello なら下記とする必要があるが print(hello.hello)from hello import hello なら省略ができ下記で良い print(hello)from フォルダ名 の場合 そのフォルダ名の中縺? __init__.pyがあれば其れfrom .xxx import aaa 縺?.の諢?味は・??
mainに対するモジュールから見て相対で髫?
モジュール觸??索パスを出すfrom pprint import pprintimport syspprint(sys.path)
■pipインス繧?pipの使い譁? (2014/1バージョ繝?) — そこはかとな縺?書縺?よん。 ドキュメント (tdoc.info)Python：pip における管理者権限縺? user install - pyてよn日鐔?? (hatenablog.com)Python でパッケージを開発して配布する標準的な方觸?? - Qiitapython縺?setup.pyについてまとめる - QiitaPyPIでサードパーティライブラリを管理していてインスコ藹??setup.pyが含まれたローカルディレクトリも指定しインスコ藹??eオプションで編集可能な状態でインス繧?--user縺?~/.local下の管理権限不要なユーザディレクトリ以下でシステムが汚れない--userなし縺?/usr下にインス繧?pip install --user -e unkopip3 install pipenvpip list インスコ觸??みのものを確鐔??
pip install -r requirements.txt reqirements.txtで臀??括インス繧?
pip freeze > r.txt pip listをファイルに書き出す
pip uninstall -y -r r.txt -yで確認なしで臀??括アンインストー繝?
Python, pip縺?requirements.txtを使ってパッケージ臀??括インストー繝? | note.nkmk.me
パッケージとバージョンを指定してアップデートpip install -U google-cloud-bigquery==3.4.0
下記にもろもろ
pipでパッケージをupdate｜pip自臀??のアップデートについても解説 (itc.tokyo)
■envツー繝?pyenv パイソンバージョン管理としてインス繧?
ubuntu 20.04 / 18.04 縺? pyenv をインストールする隧? #Python - Qiita
pyenvのインストール、使い方、pythonのバージョン切り替えできない時の対処觸?? #Python - Qiita pyenv install --list インストールできるも縺? pyenv install 3.8.8 指定verをインス繧? pyenv global 3.8.8 デフォルトに指定 .python-versionファイルをGITに鐔??せ管理したい？pipenv PIP管理としてインス繧?
Pipenvでの基本的な環藹??構築 - Pirika Developers Blog pipenv縺?Pipfile縺?Pipfile.lockを利用しpip縺?requrements.txtを用いるよりも強力 Pipfile縺?Pipfile.lock縺?requirementsをGITに鐔??せ管理したい？ pipenv --python 3.8.8 など最初縺?pyバージョンをpipfileに鐔??載
pipenv install "google-cloud-tasks==1.5.0" バージョン無しでも有りでも入れられる
Pipfileを書き觸??える方觸?? [packages] google-cloud-tasks = "==1.5.0" protobuf = "*" そして臀??記cmdでインス繧? pipenv install PipefileからインストールしPipefile.lockを更譁?
pipenv sync Pipfile.lockの最新を藹??得し環藹??更譁?(Pipefileは使繧?ない） pipenv shell 仮想環藹??を起動
pipenv run python main.py 他縺?
pipenv uninstall google-cloud-tasks アンインス繧? Pipfile, Pipfile.lockがあれ縺? pip sync縺?OKだがrequirements.txtも使える pipenv lock -r > requirements.txt 生成 pipenv install -r requirements.txtpipenvのバージョンが古いと臀??存関臀??、Ver整合性で問題が起きやすい pipenv --version pip install pipenv pipenv update pipenv upgrade <パ繧?>でやり直す
■assertでテスト
[Python] assert文で鐔??お手軽テスト #Python - Qiitaassert文は軆??み込み藹??謨?__debug__がTrueの時のみ藹??行されます実行コマンドにオプション縺?-Oをつける縺?__debug__がFalseになりassert文が無効縺?def func_so(a, b): c = a * b return def test(): assert(func_so(1,2) == 2)if __name__ == "__main__": test() main()
■テスト駆動PyTest を LLMに書いてもらいたい。下記のようなプロンプトで觸??備できるので縺??https://aaaa にアクセスし名前欄縺?aaaと入力すると名前欄に英数が入っていますとエラーが出る
■PyTestassert成立すべき蠑?(Trueになるべき蠑?) をassert文で鐔??霑?
テストの觸??備と藹??処理@pytest.fixtureデコレータをつける
実行(ディレクトリ縺?test、testファイル、test 関数が対象)pytest
テストカバレッジを確鐔??：tests/ディレクトリ内の全テストを実行し現在のディレクトリ内のコードについてどれだけテストでカバーされているかを測藹??pytest -covs=. tests/
■test app.pyでエラー表示を拾ってテストimport pytestfrom app import app
@pytest.fixturedef client(): app.config['TESTING'] = True with app.test_client() as client: yield client
def test_valid_input(client): response = client.post( '/', data={'name': 'TestUser', 'email': 'Test@example.com'}, follow_redirects=True ) assert b'OKでっせ' in response.data
def test_invalid_name(client): response = client.post( '/', data={'name': 'ThisNameIsTooLong', 'email': 'test@example.com'}, follow_redirects=True ) assert b'name At most 10 characters long' in response.data
def test_invalid_email(client): response = client.post( '/', data={'name': 'ValidName', 'email': 'Invalid email'}, follow_redirects=True ) assert b'emailがinvalid email addressなんだけ縺?' in response.data
■パラメータを複数種類import pytest@pytest.mark.parametrize( "x, y", [ ("aaa", "bbb"), ("aaa", "aaa"), ("bbb", "bbb") ])def test_1(x, y):assert x == y
■fixture: fixture@yieldまでの処理> テスト本臀??> fixture縺?yield後からreturnまでの処理import pytestfrom pathlib import Pathimport shutil
def create_file(path): # 指定されたパスにファイルを作成する関謨? path.touch()
# 一時ディレクトリを作成するフィクスチ繝?@pytest.fixture()def create_tmp_dir(): # 一時ディレクトリを作成 tmp_dir = Path("/tmp/test") if not tmp_dir.exists(): tmp_dir.mkdir() yield tmp_dir # 一時ディレクトリを削髯? shutil.rmtree(tmp_dir)
def test_create_file(create_tmp_dir): target_file = create_tmp_dir / "test.txt" create_file(target_file) assert target_file.exists()
■個蛻?
import dataclasses[詳解] Python縺?dataclasses (zenn.dev)
データ格軆??するオブジェクトを作れば使い回しが讌?import datetimePython縺?datetimeで日臀??や時間と文字列を変觸??（strftime, strptime） | note.nkmk.mepip install pyyaml > import yamlPython 縺? YAML ファイルを扱う - ま縺?ま縺?Python繝?ート (maku77.github.io)pip install requests > import requestsRequests の使い譁? (Python Library) - Qiita Slack縺?webhook縺?Postするとか
tenacityでリトライ処理
[Python] tenacity ライブラリを利用してリトライ処理を簡単に藹??装する (zenn.dev)boto3で並列処理Python 3.12で藹??えた並列処理と、これまでの並列処理の挙動を比べてみる #Python - Qiita
Python + VSCode の環藹??構築 20240604 (zenn.dev)
↓本家
/// BANGBOO BLOG /// - Python
Comment (0)
Navi: < 9 | 10 | 11 | 12 >
-Home
-Column [136]
-Europe [9]
-Gadget [79]
-Web [137]
-Bike [4]
@/// BANGBOO BLOG ///
