BigQueryテクニック集 – BigQuery UtilsでSQLをもっと楽に

BigQuery

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で試した結果：

行urlkeyval
1https://www.google.com/search?q=bigquery+udf&device=pcqbigquery+udf
2https://www.google.com/search?q=bigquery+udf&device=pcdevicepc
3https://www.google.com/search?q=analytics&device=spqanalytics
4https://www.google.com/search?q=analytics&device=spdevicesp
5https://www.google.com/search?q=firebase&device=tablet&test=qfirebase
6https://www.google.com/search?q=firebase&device=tablet&test=devicetablet
7https://www.google.com/search?q=firebase&device=tablet&test=test

生のSQLだと正規表現などを使って文字列を判定する必要が出てくるのでコンパクトになるのはかなり嬉しいですね。

いかがでしたでしょうか。
今回紹介した以外にもまだまだBigQuery Utilsでは様々な関数が用意されています。使い所次第ではかなり有用なものとなりそうです

詳細はこちら
https://github.com/GoogleCloudPlatform/bigquery-utils/tree/master/udfs/community

関連記事

特集記事

A/Bテスト

生成AIを調整してABテストを実装したら想像以上に早かった
A/Bテスト

無料で使えるABテストツールのバージョンアップを行いました
A/Bテスト

無料ABテストツール（and,B）のマニュアルを作成しました
A/Bテスト

今日から無料で使えるABテストツールを作ってみた
Python

ECサイトのゴールデンルートを発見する方法 – Google Analytics 4とPythonを活用して（第2回）
トレジャーデータ

GA4 -> トレジャーデータ 連携術

コメント

  1. 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!

  2. найти подругу в москве для секса порно видео японки негритянки московская яма
    секс порно фильмы андрея блэка

  3. Your point of view caught my eye and was very interesting. Thanks. I have a question for you.

  4. farmaci senza effetti collaterali in Italia Mabo Capodrise
    Preis von Medikamente

  5. migliori siti per l’acquisto di farmaci a Gent Arcana Zoug
    Betaalbare medicijnen zonder voorschrift online

  6. compra de somníferos medicamentos en España GeoLab Heerlerbaan Online-Kauf von Medikamente in Buenos Aires

  7. medicijnen kopen zonder doktersvoorschrift Egis Aesch vente en ligne de médicaments en France

  8. médicaments prix abordable stada Rohrbach prix du médicaments sur ordonnance

  9. яку молитву читати коли сниться
    небіжчик найкраща академія магії потрапила за власним 3 читати онлайн безкоштовно повністю
    таро кубок пентаклей сонник до
    чого сниться біла машина

  10. Trouver de la médicaments de qualité en ligne en France AbZ-Pharma Gómez Palacio farmaci con o senza prescrizione medica

  11. ойын карталарында төрт сәуегейлік, қазақ газеті баспасөз органы болған партия золотые слитки алматы цена, 100 грамм золота в тенге
    первая стадия производства серной кислоты, третья стадия производства серной кислоты костюм айли,
    тоймарт каталог алматы

  12. ойталқыға қатысатын адам саны, ойталқы латын тілінен аударғанда табиғаты
    таза болашақ, таза болса табиғат эссе құралай минус
    скачать, құралай текст мұхтар шымбулак рабочие дни, шымбулак билеты

  13. оқу практика бойынша қорытынды есеп,
    практика есебі қорытынды географиядағы негізгі әдістердің түрлері қандай, географиядағы салыстыру әдістері 10 сынып скачать
    песни руслана, песни руслан добрый скачать бесплатно менин каникулум сочинение, менин каникулум дилбаян

  14. валютные операции между резидентами и нерезидентами, валютные операции рк аварийная
    служба свет тараз, номер газовой службы тараз бмв сипаттамасы, бмв расшифровка оқушылардың қоршаған орта жағдайы, жаратылыстануды
    оқыту әдістемесіне л.с.севрук қандай үлес қосты?

  15. Wow, this post is pleasant, my sister is analyzing these things,
    therefore I am going to tell her.

  16. ж.аймауытов шыгармалары, жүсіпбек аймауытов кітаптары соляная
    шахта турда цена как отбелить
    белые вещи белизной, как отбелить белые вещи с помощью соды и перекиси арманым ай арманым текст, арманым
    ай сакен майгазиев текст

  17. 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.

  18. 對於跑步愛好者和日常運動的人士來說，Nike Air Zoom 是一雙備受推崇的運動鞋。nike zoom哪裡買最便宜

    • 888999
    • 2024.11.27 11:33am

    Adidas Originals的經典設計往往傳達出品牌對于自我表達和個性的注重，其圖標性的三葉草標志彰顯出品牌的複古與時尚相結合的風格。 adidas originals哪裏買最便宜？

    • abv
    • 2024.11.27 11:42am

    微笑項鍊 Tiffany：經典與時尚的完美融合tiffany哪裡買最便宜

    • per
    • 2024.11.27 12:19pm

    無論是搭配休閑裝還是正裝，Nike dunk鞋都能輕松駕馭，展現出不同的風格魅力。nike dunk哪裡買最便宜

    • chenchen123
    • 2024.11.29 11:13am

    探索經典魅力：Adidas Samba 與 Samba OG 不朽傳奇 adidas samba哪裡買最便宜？

  23. 재미있는 경험담과 함께 소통하는 것도 좋네요~기대됩니다!!! 방문하다 먹튀레이더

  24. Nice

    • per
    • 2024.12.20 4:13pm

    一次性小煙

    • per
    • 2024.12.20 4:15pm

    iqos 電子煙​

    • per
    • 2024.12.20 4:16pm

    拋棄 式 電子 菸​

    • per
    • 2024.12.20 4:18pm

    在時尚與舒適的交彙點，converse帆布鞋以其獨特的設計風格和卓越的品質，成爲了無數潮流愛好者的首選。converse哪裡買最便宜？

  29. Can you be more specific about the content of your article? After reading it, I still have some doubts. Hope you can help me.

  30. I learned something new today. Thank you! raipur news

  31. Thanks for being a beacon of knowledge. 500 ka redeem code

  32. Experience authentic guidance from a Genuine Nadi Astrologer In Vaitheeswaran Koil, renowned for their accuracy and traditional practices.

  33. Stay informed with the latest updates on the MBBS Cutoff Of Private Medical Colleges in Haryana.

  34. Discover academic benchmarks at MBBS Cutoff Of Private Medical Colleges in Himachal Pradesh.

  35. Explore personal and business finance solutions at https://look4.in/resources/finance-additional-information.php.

  36. Enjoy a hassle-free installation with the Raja Luck App Download.

  37. Join now and claim your winnings faster using the 82 Lottery Invite Code.

  38. Win amazing prizes by using the 82 Lottery Invite Code.

  39. UID_74743238###
    Ini dia! 🎉🎉 ASN BKN Ubah Pola Kerja yang baru dan efisien! 🎉🎉.

  40. Enjoy quick and secure access to your gaming account with Goa Games Login, ensuring a hassle-free experience.

  41. Gain access to premium membership perks by applying an Invitation Code on TS EARN.

  42. Explore a curated list of upcoming activities with Events In Delhi, tailored to suit diverse interests and preferences.

  43. UID_21891068###
    Takjub 😮 dengan fenomena serbu gas? Yuk, baca Warung Tatang Diserbu Warga dan temukan faktanya! 🕵️‍♀️👀

  44. UID_21891068###
    Takjub 😮 dengan fenomena serbu gas? Yuk, baca Warung Tatang Diserbu Warga dan temukan faktanya! 🕵️‍♀️👀

  45. UID_25978847###
    situs terbaik hanya di slot gacor agentotoplay

  46. visit my site https://rocket-pool.us/

  47. 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.

  48. 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.

  49. 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”

  50. Nice article, have a look at my site “https://www.issuewire.com/sushi-swap-the-best-crypto-platform-in-2025-1823683727364009”

  51. 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.

  52. https://medium.com/@CoinNebula/sushi-swap-a-comprehensive-guide-to-decentralized-trading-and-staking-09630bb4cfc8

  53. UID_18563832###
    Ini yang di ganti >>> Yuk, ketahui lebih lanjut tentang Syarat Gabung OECD dan Pentingnya Ratifikasi Konvensi Antisuap di sini! 🕵️‍♀️🔎📚.

  54. https://spookyswap-2.gitbook.io/en-us

  55. UID_83003169###
    Kenalan yuk! 🤝 Dengan guru inspiratif ini, Guru Dede Sulaeman yang mengajarkan cara merapikan pakaian di kelasnya. 👕👚🎓

  56. nice blog, visit my site

  57. 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.

  58. Stay ahead with the latest promotions using the Raja Luck Invite Code.

  59. 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!

  60. https://spookyswap-tm-4.gitbook.io/en-us/

  61. Looking forward to reading more of your content!

  62. UID_97211224###
    Yuk, sehat bersama! 👨‍⚕️👩‍⚕️ Cek kondisi tubuh kamu di Program Cek Kesehatan Gratis sekarang juga! 💉🌡️ Selalu jaga kesehatan, ya! 🏥💖

  63. UID_55590637###
    situs terbaik hanya di slot gacor agentotoplay

  64. https://spookyswap-c.gitbook.io/en-us

  65. Looking for a reliable IT partner? Check out Software Company Ranchi,Jharkhand for top-notch software solutions.

  66. https://spookyswap-r-and-fantom-opera-ne.gitbook.io/en-us/

  67. Anyone having issues with spookyswap? It’s been smooth for me.

  68. Staking BOO on spooky swap provides additional rewards over time.

  69. https://spookyswap-14.gitbook.io/en-us/

  70. https://spookyswap-tm-3.gitbook.io/en-us/

  71. best crypto site https://iziswap.org/

  72. https://r-guide-spookyswap-r.gitbook.io/en-us

  73. https://spookyswap-7.gitbook.io/en-us/

  74. https://spookyswap.app/

  75. WOOFi Finance Trading Guide: How to Trade Crypto in 2025

  76. Boost your chances of winning by participating in multiple lottery draws at Raja Luck.

  77. https://x.com/AltcoinAshley/status/1890121613208957274, WOOFi Finance Leading DEX 2025

  78. Unlock greater payouts and more rewards with the Diu Win Invite Code.

  79. Discover holistic Ayurvedic treatment in Ranchi at Kerala Ayurveda. Our Kerala Panchakarma therapies promote physical healing and emotional tranquility.

  80. 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.

  81. Win more benefits and enjoy special rewards by using the Goa Games Invite Code.

  82. 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

  83. Online Boat Sales

  84. Don’t miss out on Stargate Bridge – the top blockchain bridge for seamless cross-chain transfers and enhanced security.

  85. Stargate Bridge makes transferring assets between blockchains simple and secure. Check it out now!

    • Buy Ativan 2mg Online
    • 2025.02.24 7:28pm

    Buy Ativan 2mg Online for effective relief from anxiety, panic disorders, and insomnia. This prescription benzodiazepine calms the nervous system, promoting relaxation. Use only as directed by a healthcare professional.

  87. Clonazepam for Sale Online is a prescription benzodiazepine used to treat anxiety, panic disorders, and seizures. It helps calm brain activity and promotes relaxation. Use only as directed by a healthcare professional.

  88. Looking for a cutting-edge bridge in 2025? Stargate Bridge is the ideal choice for users who value trust and efficiency.

  89. Join a community of players and contend for big wins on bdg win game.

  90. Enjoy competitive tournaments and special promos at 55-club.

  91. Top movers and packers in Ranchi for safe, affordable, and hassle-free relocation.

  92. Order Adderall 30mg online with fast, overnight delivery. Get your prescription medication discreetly and conveniently, shipped directly to your door. Reliable service, quick processing.

  93. Chat with expert astrologers online for accurate predictions, guidance, and solutions to your life, career, and relationship concerns.

  94. Buy Adderall online with a prescription for ADHD and narcolepsy treatment. Order Adderall IR & XR from trusted pharmacies. Ensure safe and reliable delivery.

  95. Comprehend the value of an efficient IT Roadmap for guiding digital change.

  96. State-of-the-art Cardiac Imaging guarantees precise diagnostics and treatment.

  97. The very best part about Home Delivery from Joshital is that the food gets here hot and well-packed.

  98. If you’re puzzled about government-backed programs, this guide on Student Loan Forgiveness Programs Everything You Need to Know is for you.

  99. Plastic Fabrication

  100. top seo services

  101. PIXL DUO 12 6K POD VAPE KIT

ランキング（週間）

  1. 1

    GA4 「ページ ロケーション」と「ページ パス と スクリーン クラス」何が違う？ドメインの有無以外にもパラメータの扱いに違いあり
  2. 2

    「GA4【無料】オンラインセミナー　シーズン2」のご案内
  3. 3

    GA4の探索で「ページ別訪問数」を見ることは可能か？
  4. 4

    【英語発表】UA360に延命措置。ユニバーサル アナリティクス 有料版＝360なら、2024年（再来年）7月1日までUAが使えると【英語公式ヘルプ】と【英語版公式ブログ】が伝える
  5. 5

    ユニバーサル アナリティクス終了　過去データはどうなる？
  6. 6

    GA4 拡張計測機能の新顔「フォームの操作」 form_start, form_submit
  7. 7

    GA4で異なるドメイン（サブドメイン）の同一ページパスをドメイン付きで表示する
  8. 8

    Ads Data Hub（ADH） とはどんなものですか？GA4との関連は？
  9. 9

    GA4 クロスドメイン設定では、リンク先に「_gl」パラメータが付く。勝手なパラメータを付けるとエラーになるページは要注意
  10. 10

    GA4のレポート画面の数値データは、イベント発生時から何時間後に確定するのか？

タグ

BigQuery_by_佐藤洋太 (4)

最近の記事

  1. 生成AIを調整してABテストを実装したら想像以上に早かった

  2. 無料で使えるABテストツールのバージョンアップを行いました

  3. 無料ABテストツール（and,B）のマニュアルを作成しました

カテゴリー

アーカイブ
 
TOP