BigQuery

Datastreamは max_staleness オプションを指定することでDatastreamが作成するテーブルにその値が反映されます。 https://cloud.google.com/datastream/docs/destination-bigquery?hl=ja#use-max-staleness しかし、Datastreamのmax_stalenessの値を後から変更しても既に作成されたテーブルには反映されません。 以下のようなSQLで手動で変更する必要があります。 1 2 3 4 FOR tables IN (SELECT DISTINCT table_schema || "." || table_name AS table_id FROM `region-asia-northeast1`.INFORMATION_SCHEMA.TABLE_OPTIONS WHERE option_name = 'max_staleness' AND table_schema = 'データセット名') DO EXECUTE IMMEDIATE "ALTER TABLE " || tables.table_id || " SET OPTIONS (max_staleness = INTERVAL 1 DAY);"; END FOR;

1 2 3 4 FOR record IN (SELECT DISTINCT _TABLE_SUFFIX AS table_suffix FROM `dataset.table_*`) DO EXECUTE IMMEDIATE "DROP TABLE `dataset.table_" || record.table_suffix || "`"; END FOR;

背景 Google AnalyticsをBigQueryに連携してSQLで分析しようという時、event_params や user_properties が 1 2 3 4 5 6 7 8 9 10 11 ARRAY< STRUCT< key STRING, value STRUCT< string_value STRING, int_value INT64, float_value FLOAT64, double_value FLOAT64 > > > という大変扱い辛い型のため、以下のようにサブクエリを多用したSQLを度々書いていく必要があります。 1 2 3 4 5 6 7 8 9 SELECT TIMESTAMP_MICROS(event_timestamp) event_timestamp, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_title') AS page_title, ... FROM `analytics_123456789.events_*` WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20240131' このSQLの結果をデータマートにすれば解決かと思いきや、event_paramsにプロパティを追加する度にデータマートをメンテナンスする必要があり運用が非常に面倒です。...

BigQueryに格納されている行動ログを元にマーケティングオートメーションを構築、SendGridでメールを発射したいケースは多々あるかと思います。 MAツールはかなり高価なものが多いため、シュッと自前構築するためのサンプルコードを掲載します。 送信したメールの効果測定・分析方法についても少し触れます。 1. SendGridテンプレートの作成 公式ドキュメントを参考に、Dyamic templateを作成します。 詳細は割愛しますが、今回は以下のようなメールを送りたいとします。 1 2 3 4 5 6 7 foobar様への今週のおすすめ商品です！ 1. ほげほげ (1000円) 2. ふがふが (1500円) 購入はこちらから！ https://example.com テンプレートはこんな感じで記述します。 1 2 3 4 5 6 7 8 {{customer_name}}様への今週のおすすめ商品です！ {{#each products}} {{this.number}}. {{this.product_name}} ({{this.price}}円) {{/each}} 購入はこちらから！ https://example.com このテンプレートに対して、送信対象ごとに以下のようなデータを埋め込む事が目標になります。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 { "customer_name": "foobar", "products": [ { "number": 1, "product_name": "ほげほげ", "price": 1000 }, { "number": 1, "product_name": "ふがふが", "price": 1500 }, ] } 2....

オペミスやat-least-onceセマンティクスによってINSERTされてしまった重複レコードを消すSQLです。 完全に同一な重複レコードを消す やる事は 重複レコードのうち最古のものを一時テーブルに退避 重複レコードを全て削除 一時テーブルから再度INSERT です。 Schema 1 2 3 4 5 6 7 8 9 10 11 12 [ { "mode": "REQUIRED", "name": "id", "type": "INTEGER" }, { "mode": "NULLABLE", "name": "value", "type": "STRING" } ] 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 CREATE TABLE project_name....