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.
ж.аймауытов шыгармалары, жүсіпбек аймауытов кітаптары соляная
шахта турда цена как отбелить
белые вещи белизной, как отбелить белые вещи с помощью соды и перекиси арманым ай арманым текст, арманым
ай сакен майгазиев текст
I don’t think the title of your article matches the content lol. Just kidding, mainly because I had some doubts after reading the article.
對於跑步愛好者和日常運動的人士來說，Nike Air Zoom 是一雙備受推崇的運動鞋。nike zoom哪裡買最便宜
Adidas Originals的經典設計往往傳達出品牌對于自我表達和個性的注重，其圖標性的三葉草標志彰顯出品牌的複古與時尚相結合的風格。 adidas originals哪裏買最便宜？
微笑項鍊 Tiffany：經典與時尚的完美融合tiffany哪裡買最便宜
無論是搭配休閑裝還是正裝，Nike dunk鞋都能輕松駕馭，展現出不同的風格魅力。nike dunk哪裡買最便宜
探索經典魅力：Adidas Samba 與 Samba OG 不朽傳奇 adidas samba哪裡買最便宜？
재미있는 경험담과 함께 소통하는 것도 좋네요~기대됩니다!!! 방문하다 먹튀레이더
Nice
一次性小煙
iqos 電子煙
拋棄 式 電子 菸
在時尚與舒適的交彙點，converse帆布鞋以其獨特的設計風格和卓越的品質，成爲了無數潮流愛好者的首選。converse哪裡買最便宜？
Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.
I learned something new today. Thank you! raipur news
Thanks for being a beacon of knowledge. 500 ka redeem code
Experience authentic guidance from a Genuine Nadi Astrologer In Vaitheeswaran Koil, renowned for their accuracy and traditional practices.
Stay informed with the latest updates on the MBBS Cutoff Of Private Medical Colleges in Haryana.
Discover academic benchmarks at MBBS Cutoff Of Private Medical Colleges in Himachal Pradesh.
Explore personal and business finance solutions at https://look4.in/resources/finance-additional-information.php.
Enjoy a hassle-free installation with the Raja Luck App Download.
Join now and claim your winnings faster using the 82 Lottery Invite Code.
Win amazing prizes by using the 82 Lottery Invite Code.
UID_74743238###
Ini dia! 🎉🎉 ASN BKN Ubah Pola Kerja yang baru dan efisien! 🎉🎉.
Enjoy quick and secure access to your gaming account with Goa Games Login, ensuring a hassle-free experience.
Gain access to premium membership perks by applying an Invitation Code on TS EARN.
Explore a curated list of upcoming activities with Events In Delhi, tailored to suit diverse interests and preferences.
UID_21891068###
Takjub 😮 dengan fenomena serbu gas? Yuk, baca Warung Tatang Diserbu Warga dan temukan faktanya! 🕵️♀️👀
UID_21891068###
Takjub 😮 dengan fenomena serbu gas? Yuk, baca Warung Tatang Diserbu Warga dan temukan faktanya! 🕵️♀️👀
UID_25978847###
situs terbaik hanya di slot gacor agentotoplay
visit my site https://rocket-pool.us/
UID_95859078###
Baru-baru ini, para pemain Mahjong Wins 3 dikejutkan dengan bocoran RTP yang diklaim bisa meningkatkan peluang kemenangan secara signifikan. Banyak yang percaya bahwa informasi ini membantu mereka mendapatkan hasil yang lebih konsisten dalam permainan. Jika Anda ingin tahu lebih lanjut, cek bocoran RTP Mahjong Wins 3 hari ini dan lihat apakah strategi ini benar-benar efektif.
UID_57331425###
Skandal terbaru mengguncang komunitas pecinta game slot setelah seorang admin terkenal, Rachel, dikabarkan tertangkap membocorkan pola kemenangan Gate of Olympus. Banyak yang bertanya-tanya apakah informasi ini benar-benar akurat atau hanya sekadar rumor yang beredar di kalangan pemain. Simak lebih lanjut detailnya dalam artikel admin Rachel tertangkap bocorkan pola Gate of Olympus.
nice article ave a look at my site “https://www.newsbreak.com/crypto-space-hub-313321940/3799652652916-top-crypto-investments-in-2025-bitcoin-ai-projects-tokenized-assets”
Nice article, have a look at my site “https://www.issuewire.com/sushi-swap-the-best-crypto-platform-in-2025-1823683727364009”
UID_17136916###
Kisah inspiratif datang dari seorang tukang ojek online asal Tangerang yang berhasil membawa pulang hadiah fantastis setelah bermain Mahjong Ways 2. Dengan modal kecil, ia sukses mengubah nasibnya dan meraih kemenangan besar senilai 200 juta rupiah. Penasaran bagaimana caranya? Simak kisah lengkapnya di artikel tukang ojek online menang 200 juta dari Mahjong Ways 2.
https://medium.com/@CoinNebula/sushi-swap-a-comprehensive-guide-to-decentralized-trading-and-staking-09630bb4cfc8
UID_18563832###
Ini yang di ganti >>> Yuk, ketahui lebih lanjut tentang Syarat Gabung OECD dan Pentingnya Ratifikasi Konvensi Antisuap di sini! 🕵️♀️🔎📚.
https://spookyswap-2.gitbook.io/en-us
UID_83003169###
Kenalan yuk! 🤝 Dengan guru inspiratif ini, Guru Dede Sulaeman yang mengajarkan cara merapikan pakaian di kelasnya. 👕👚🎓
nice blog, visit my site
As the decentralized finance (DeFi) space continues to evolve in 2025, SpookySwap has established itself as one of the leading decentralized exchanges (DEXs) on the Fantom Opera blockchain. Known for fast, low-cost token swaps, yield farming, and liquidity rewards, SpookySwap is a powerful tool for traders and DeFi enthusiasts looking to maximize their returns while maintaining full control of their funds.
Stay ahead with the latest promotions using the Raja Luck Invite Code.
Totally agree! Understanding finances can be life-changing. If anyone’s looking for more tips, we covered similar topics at Woofi Finance. Check it out!
https://spookyswap-tm-4.gitbook.io/en-us/
Looking forward to reading more of your content!
UID_97211224###
Yuk, sehat bersama! 👨⚕️👩⚕️ Cek kondisi tubuh kamu di Program Cek Kesehatan Gratis sekarang juga! 💉🌡️ Selalu jaga kesehatan, ya! 🏥💖
UID_55590637###
situs terbaik hanya di slot gacor agentotoplay
https://spookyswap-c.gitbook.io/en-us
Looking for a reliable IT partner? Check out Software Company Ranchi,Jharkhand for top-notch software solutions.
https://spookyswap-r-and-fantom-opera-ne.gitbook.io/en-us/
Anyone having issues with spookyswap? It’s been smooth for me.
Staking BOO on spooky swap provides additional rewards over time.
https://spookyswap-14.gitbook.io/en-us/
https://spookyswap-tm-3.gitbook.io/en-us/
best crypto site https://iziswap.org/
https://r-guide-spookyswap-r.gitbook.io/en-us
https://spookyswap-7.gitbook.io/en-us/
https://spookyswap.app/
WOOFi Finance Trading Guide: How to Trade Crypto in 2025
Boost your chances of winning by participating in multiple lottery draws at Raja Luck.
https://x.com/AltcoinAshley/status/1890121613208957274, WOOFi Finance Leading DEX 2025
Unlock greater payouts and more rewards with the Diu Win Invite Code.
Discover holistic Ayurvedic treatment in Ranchi at Kerala Ayurveda. Our Kerala Panchakarma therapies promote physical healing and emotional tranquility.
In need of a web design company Ranchi? Modern websites, e-commerce solutions, and digital growth tactics are provided by Jharkhand IT Services for your company.
Win more benefits and enjoy special rewards by using the Goa Games Invite Code.
Discover Woofi Finance, the most trusted decentralized exchange for 2025, offering low fees, secure transactions, and seamless crypto swaps. Whether you’re staking or trading, Woofi Finance is the top platform for DeFi users worldwide
Online Boat Sales