and,a株式会社 デジタルマーケティングエンジニア 佐藤洋太
GA4＋BigQueryを利用していてこのようなエラーに出くわすことはありませんか？
エラーメッセージ：
Quota exceeded: Your project exceeded quota for free query bytes scanned. For more information, see https://cloud.google.com/bigquery/docs/troubleshoot-quotas
このエラーはサンドボックスモードを利用していて、プロジェクトの無料クエリ使用分の限界に達した際に発生するエラーです。
SQLを試したい時にこのエラーが出ると非常に厄介です。
有料にすれば解決では？と思いますが、Billing情報を登録してサンドボックスモードを解除したとしても、大きなクエリはお金がかかってしまうため、結局のところクエリコストを如何に減らせるかがキモになってきます。結局はコストを減らす点、クエリの効率化を行うという点からは逃れられません。
このような背景から、今回は開発時に簡単に使えるクエリコストの軽減テクニックを考えてみます。また、このテクニックは無料使用分でも使えるテクニックですのでご参考にどうぞ。
一時テーブルとは？
そこで登場するのが一時テーブルという機能です。
公式のアナウンス：
https://cloud.google.com/bigquery/docs/multi-statement-queries?hl=ja#temporary_tables
一時テーブルを使用すると、中間結果をテーブルに保存できます。この一時テーブルはセッション レベルで存在するため、データセット内での保存や管理の必要はありません。
複数ステートメント クエリで一時テーブルを作成して参照できます。一時テーブルが不要になったら、手動で削除するか、BigQuery が 24 時間後に削除されるのを待ちます。
https://cloud.google.com/bigquery/docs/multi-statement-queries?hl=ja#temporary_tables
一時テーブルはコピー＆ペーストの際のクリップボードのような一時的な保存領域と覚えておきましょう。コスト低減以外にも様々なメリットがあるので必須のテクニックかと個人的には考えています。
公式のドキュメントにもSQLソースの記載はありますが、正直めんどくさい・・・という方向けに楽に作る方法をご紹介します。
実践
工程
１：準備
まずは、クエリエディタを開きます。
２：設定
クエリ設定から「一時テーブルにクエリ結果を保存」を選択し、保存します。
３：SQLの実行
任意のSQLをペーストし、実行します。
今回はごく簡単なSQLを実行します。
４：テーブルが作成されたかの確認
先ほどのクエリ結果画面からジョブ詳細を選択すると宛先テーブルの行にTemporary table という文字がハイライトされています。
Temporary table をクリックすると一時テーブルのスキーマ画面が開きます
ここからクエリを選択して開くとSQLが発行されます
このSQLを実行すると先ほどの結果と同じものが保存されていることが確認できます
画像内のFROM句の文字列が一時テーブルの所在になっています。
下記が一時テーブルの住所です。
`project_id._af0a1e64d3998259dfc43b27b34d1c5d90d1bdcd.anonfd8da7be_cd28_49e0_9c05_8e0f0be12a60`以上で一時テーブルを作成する操作は終わりです。
画面の操作だけで完結するのでほぼ問題ないと思います。
実際にどれぐらい変わるの？
では実際にどれぐらいコスト低減効果があるのかを試してみます。
今回は140GBを超えるGA4のテーブルに直接アクセスして比較しました
結果：
・経過時間が10秒ほど短くなっている
・消費したスロット時間が大幅に減少
この2点が大きな違いです。
スロット時間の消費は料金に対して直接影響がある部分ですので減少は大きなメリットですね。
最後に
重いテーブルを何回もスキャンするなら一時テーブルに保存してからクエリを開発する形がベストと思います。もちろん中間テーブルを利用するのも有用だと思います。特に有料版ではスケジュールクエリなどを利用して中間テーブルを作成することを強くお勧めします。
Really informative article post.Really thank you!
wow, awesome blog.Much thanks again. Keep writing.
This is one awesome article post.Much thanks again.
Really appreciate you sharing this post.Really looking forward to read more. Much obliged.
Great article post. Will read on…
ivermectin walgreens ivermectin egg withdrawal
Thanks for the blog post. Really Cool.
Very good blog.Really thank you! Really Cool.
A big thank you for your article.Thanks Again. Cool.
I value the post. Much obliged.
I really enjoy the article post.Much thanks again. Awesome.
What’s Going down i am new to this, I stumbled upon this I’ve found It absolutely useful and it has helped me out loads. I’m hoping to give a contribution & aid other users like its aided me. Great job.
fountain pointe apartments grandview heights apartments plantation apartments
After being escorted to the room, I turned on the air conditioner, then I went to the car.
Fantastic blog post.Really thank you! Want more.
Thank you, I’ve recently been looking for information approximately this subject for a while and yours is the greatest I’ve came upon so far. But, what concerning the bottom line? Are you certain concerning the source?
what is atorvastatin] generic for lipitor atorvastatin medication
An intriguing discussion is worth comment. There’s no doubt that that you need to publish more about this subject matter, it might not be a taboo subject but usually people do not speak about these topics. To the next! Cheers!!
Specifically the sort of material that enriches the internet. Thanks!
I really like your writing style, excellent information, regards for putting up : D.
It’s difficult to find educated people about this topic, but youseem like you know what you’re talking about! Thanks
yasmin uk online – yasmin 28 birth control yasmin brand coupon
I really enjoy the blog post.Really thank you! Really Great.
Great, thanks for sharing this blog.Really looking forward to read more. Really Great.
Thanks for the blog.Thanks Again. Want more.
best erectile dysfunction treatmenterectile sprayerectile dysfunction injections
At this moment I am going to do my breakfast, once having my breakfast coming over again to read additionalnews.
Hit me up! I enjoyed your post. Thank you. You’ve made my day! Thx again. Great post!
Great blog post. Want more.
Hey, thanks for the article post.Much thanks again. Really Cool.
pretty beneficial material, overall I believe this is worthy of a bookmark, thanks
Enjoyed every bit of your post.Really looking forward to read more. Great.
I really like and appreciate your blog article.Really looking forward to read more. Want more.
Itís hard to come by experienced people on this topic, however, you seem like you know what youíre talking about! Thanks
Hi there, I enjoy reading all of your post.I like to write a little comment to support you.
You could definitely see your skills in the work you write. The arena hopes for more passionate writers such as you who aren’t afraid to mention how they believe. Always follow your heart.
I have been examinating out some of your stories and i can state pretty clever stuff. I will make sure to bookmark your blog.
Simply a smiling visitant here to share the love (:, btw outstanding style and design .
I couldn’t refrain from commenting. Perfectly written! kokosvatten ica maxi swere.sewomenpriz.com/advice-girlfriends/kokosvatten-ica-maxi.php
I value the article.Thanks Again. Awesome.
Great post.Much thanks again. Will read on…
Enjoyed every bit of your article post.Thanks Again. Fantastic.
Say, you got a nice blog post.Really thank you! Awesome.
I think this is a real great blog.Thanks Again. Cool.
Appreciate you sharing, great blog.Really thank you! Really Great.
I am curious to find out what blog system you have been working with? I’m having some small security problems with my latest blog and I would like to find something more safe. Do you have any recommendations?
This is one awesome article post.Thanks Again. Really Cool.
mexican online mail order pharmacy cvs pharmacy
best replica watches review is normally dedicated producing the more leading-edge an element of the watch.
Is There A Way A Minor Can Make Money Online For Free?: I am looking for any legitimate sites that i can make a li
Nice post. I understand some thing additional challenging on different blogs everyday. It is going to usually be stimulating to read content from other writers and practice somewhat something from their store. I’d prefer to use some with the content on my blog whether or not you do not mind. Natually I’ll provide you with a link on your internet weblog. Thanks for sharing.
I like the valuable info you provide in your articles.
I’ll bookmark your blog and check again here
frequently. I’m quite sure I’ll learn a lot of new stuff
right here! Best of luck for the next!
When I originally commented I clicked the “Notify me when new comments are added” checkbox and now each time a comment is added I get four e-mails
with the same comment. Is there any way you can remove people from that service?
Appreciate it!
Hey! Do you use Twitter? I’d like to follow you if that would
be ok. I’m undoubtedly enjoying your blog and look forward to
new posts.
I have to thank you for the efforts you’ve put in writing this site.
I really hope to check out the same high-grade blog posts from you later on as well.
In truth, your creative writing abilities has inspired me to get my own,
personal blog now 😉
Hi there just wanted to give you a quick heads up. The
words in your article seem to be running off the screen in Safari.
I’m not sure if this is a format issue or something to do with web browser compatibility but I figured
I’d post to let you know. The design and style look
great though! Hope you get the issue resolved soon.
Cheers
Great blog! Is your theme custom made or did you download it from somewhere?
A design like yours with a few simple adjustements would really make my blog shine.
Please let me know where you got your design. Appreciate it
What a stuff of un-ambiguity and preserveness of precious experience concerning unpredicted emotions.