and,a株式会社 デジタルマーケティングエンジニア 佐藤洋太
BigQueryを触っていて配列の操作などの取り回しが入ってくると途端に記述量が増え、データの扱いが大変になって来ることが多々あります。今回はBigQueryに用意されているBigQuery Utilsを使ってSQLをもっと楽に開発してみましょう。
BigQuery Utilsとは？
BigQuery UtilsとはBig Query上で使用できる便利な関数を組み込んだライブラリのようなものです。
EXCELでいう数式をもっと簡単に扱えるようにしたコードの集合体の認識でOKです。
Githubリポジトリ
https://github.com/GoogleCloudPlatform/bigquery-utils
導入する手間は一切なく、
bqutil.fnとBigQueryのエディタに記述するだけですぐに任意の関数を呼び出すことができます。
SELECT
bqutil.fn.typeof("アイウエオ"), /* 結果は STRING */
bqutil.fn.typeof(0), /* 結果は INT64 */
bqutil.fn.typeof(DATE("2022-01-01")), /* 結果は DATE */上記は引数の型を読み取る例です。
特に難しいこともなくスムーズに理解頂けると思います。
※注意事項
BigQuery UtilsはBigQueryがサンドボックスモードだと、下記のようなエラーが出てしまい動作しないのでBillingを設定し、サンドボックスモードを解除してお使いください。
Not found: Dataset ga4-quick:analytics_302974976 was not found in location US早速使ってみる
GA4でevent_paramsの配列内を取得して操作する際のよくあるこの形。
SELECT
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS hitdate,
CONCAT(user_pseudo_id, CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') as STRING)) AS sid,
FROM `ga4-quick.analytics_302974976.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20220901' AND '20220901'これをBigQueryUtilsで書くと
SELECT
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS hitdate,
CONCAT(
user_pseudo_id,
CAST(bqutils.fn.get_value('ga_session_id', event_params).int_value as STRING)
) AS sid,
FROM `ga4-quick.analytics_302974976.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20220901' AND '20220901'サブクエリが減って少しスッキリしました。
event_params, user_propertiesの配列で頻出するクエリで使えそうです。
他にも使い所を考えてみましょう。
例えば、サイト上の全てのpage_locationから、クエリパラメーターをURL毎にkey/valueで出力するとこのようになります。
WITH locations AS (
/* GAからpage_locationsを取得する処理 */
)
SELECT url, key, bqutil.fn.url_param(t1.url, key) AS val
FROM (
SELECT url, bqutil.fn.url_keys(url) AS keys
FROM locations
) AS t1, UNNEST(t1.keys) AS keyサンプルのURLで試した結果：
| 行 | url | key | val |
|---|---|---|---|
| 1 | https://www.google.com/search?q=bigquery+udf&device=pc | q | bigquery+udf |
| 2 | https://www.google.com/search?q=bigquery+udf&device=pc | device | pc |
| 3 | https://www.google.com/search?q=analytics&device=sp | q | analytics |
| 4 | https://www.google.com/search?q=analytics&device=sp | device | sp |
| 5 | https://www.google.com/search?q=firebase&device=tablet&test= | q | firebase |
| 6 | https://www.google.com/search?q=firebase&device=tablet&test= | device | tablet |
| 7 | https://www.google.com/search?q=firebase&device=tablet&test= | test |
生のSQLだと正規表現などを使って文字列を判定する必要が出てくるのでコンパクトになるのはかなり嬉しいですね。
いかがでしたでしょうか。
今回紹介した以外にもまだまだBigQuery Utilsでは様々な関数が用意されています。使い所次第ではかなり有用なものとなりそうです
詳細はこちら
https://github.com/GoogleCloudPlatform/bigquery-utils/tree/master/udfs/community
I seriously love your blog.. Pleasant colors & theme.
Did you make this amazing site yourself? Please reply back as I’m hoping to create my own personal site and want to find out where you got this
from or just what the theme is named. Cheers!
найти подругу в москве для секса порно видео японки негритянки московская яма
секс порно фильмы андрея блэка
Your point of view caught my eye and was very interesting. Thanks. I have a question for you.
farmaci senza effetti collaterali in Italia Mabo Capodrise
Preis von Medikamente
migliori siti per l’acquisto di farmaci a Gent Arcana Zoug
Betaalbare medicijnen zonder voorschrift online
compra de somníferos medicamentos en España GeoLab Heerlerbaan Online-Kauf von Medikamente in Buenos Aires
medicijnen kopen zonder doktersvoorschrift Egis Aesch vente en ligne de médicaments en France
médicaments prix abordable stada Rohrbach prix du médicaments sur ordonnance
яку молитву читати коли сниться
небіжчик найкраща академія магії потрапила за власним 3 читати онлайн безкоштовно повністю
таро кубок пентаклей сонник до
чого сниться біла машина
Trouver de la médicaments de qualité en ligne en France AbZ-Pharma Gómez Palacio farmaci con o senza prescrizione medica
ойын карталарында төрт сәуегейлік, қазақ газеті баспасөз органы болған партия золотые слитки алматы цена, 100 грамм золота в тенге
первая стадия производства серной кислоты, третья стадия производства серной кислоты костюм айли,
тоймарт каталог алматы
ойталқыға қатысатын адам саны, ойталқы латын тілінен аударғанда табиғаты
таза болашақ, таза болса табиғат эссе құралай минус
скачать, құралай текст мұхтар шымбулак рабочие дни, шымбулак билеты
оқу практика бойынша қорытынды есеп,
практика есебі қорытынды географиядағы негізгі әдістердің түрлері қандай, географиядағы салыстыру әдістері 10 сынып скачать
песни руслана, песни руслан добрый скачать бесплатно менин каникулум сочинение, менин каникулум дилбаян
валютные операции между резидентами и нерезидентами, валютные операции рк аварийная
служба свет тараз, номер газовой службы тараз бмв сипаттамасы, бмв расшифровка оқушылардың қоршаған орта жағдайы, жаратылыстануды
оқыту әдістемесіне л.с.севрук қандай үлес қосты?
Wow, this post is pleasant, my sister is analyzing these things,
therefore I am going to tell her.
ж.аймауытов шыгармалары, жүсіпбек аймауытов кітаптары соляная
шахта турда цена как отбелить
белые вещи белизной, как отбелить белые вещи с помощью соды и перекиси арманым ай арманым текст, арманым
ай сакен майгазиев текст