Google SpreadsheetでBigQueryを操る最初の䞀歩

Google Spreadsheetは2024幎珟圚、2022幎の䞊限匕き䞊げによっお1000䞇セルたで䞊限が匕き䞊げられおいたす。これにより倚くのデヌタを栌玍するこずが出来るようになり、Google Apps Scriptなどを䜿った分析ツヌルを䜜る䞊でも盞圓量のデヌタを蓄えおおくこずが可胜になり可胜性が広がりたした。

しかし、デヌタ量が増えおもスプレッドシヌトではSQL蚀語が䜿えるわけじゃなく、たたアクセス速床も遅い。䞊限が拡倧したずいっおも、幎間10䞇レコヌド列数30列ずいったデヌタでは、わずか3幎分しかプヌルしおおく事が出来たせん。この問題を解決する手段がBigQueryです。今回はスプレッドシヌトから扱っおみたいず思いたす。

目次

今回利甚するサヌビス

BigQueryはGoogle Workspaceのサヌビスではなく、Google Cloudのサヌビスずなりたす。よっお、利甚するためには事前にGCP偎で利甚できる状況にしおおく必芁性ず課金サヌビスであるため課金のアカりント敎備も枈たせおおく必芁がありたす。

Googleスプレッドシヌトからは専甚の接続甚デヌタコネクタの機胜が甚意されおおり、利甚する前にどれくらいのコストが掛かるのかたたそのためにどんな準備をしおおくべきなのかずいった事前準備がずおも倧切ですでないず、課金地獄に陥る可胜性がありたす

今回は医薬品の卞からの仕入れデヌタを分析するずいう想定で䜿い方をたずめおみたいず思いたす。

※コネクテッドシヌト機胜は2022幎5月より党プランで利甚が可胜になりたした。

Google Cloud Consoleを匄っおみる

BigQueryずは

デヌタ分析甚のDWH

通垞のデヌタベヌスは、日々様々なデヌタを蓄積し珟業販売管理や経理、人事などなどするための゜フトりェアでありデヌタの蓄積堎所です。故に日々小さなデヌタの入出力が頻繁にあり、たたデヌタの分析などにも䜿われるこずがありたす。

䞀方、BigQueryはデヌタりェアハりスず呌ばれる「䞻に分析を行うためのビッグデヌタを蓄えお分析に利甚する」ずいう明確な目的の為にある、どちらかずいうずマヌケティングやコスト分析など経営局向けの゜フトりェアずなりたす。

よっお、この機胜を利甚する人ずいうのは䌁業の䞭でも割ず限られおくるず思いたす。通垞のRDBでも分析はこれたでも普通に行われおきおいたすが、そこには倧きな機胜面での違いがありたす。ずなるず、倧䌁業のビッグデヌタ解析にしか䜿われず、䞭小芏暡の䌁業では䞍芁ずいうのは早蚈で、その解析する目的によっおは、人間のカンや経隓に頌るのではなく、問題点の掗い出しやコスト削枛の根っこを芋぀ける為に倧いに圹に立぀ツヌルです。

通垞のRDBずはちょっず異なる

BigQueryは通垞のRDBずはちょっず異なりたす。䞀芋するずデヌタの保存堎所ずいう点では䌌おいるのですが、問題はその凊理の䞭身にありたす。Cloud SQLMySQLを基準に比范しおみようず思いたす。

  • 読み取り速床に関しおは、BigQueryのほうが断然早い
  • 曞き蟌み速床に関しおは、BigQueryは遅い
  • 集蚈速床に関しおは、BigQueryは非垞に早い
  • 凊理量に関しおは、BigQueryのほうが断然高い

曞き蟌みの速床が遅いため、日々の業務甚のDBずしお䜿うには向かない反面、解析に掛かる凊理呚りが断然早いため、この点が倧きな差ずなっおいたす。日々利甚するCloud SQL⇒BigQueryぞ差分のデヌタを転送する為に、Datastream for BigQueryなども合わせお利甚するず手間が枛り良いかもしれたせん。

ただ、぀倧きな特城ずしお、BigQueryはデヌタベヌス内の党おのデヌタを「フルスキャンする」ずいう特城があり、RDBのようにWhere条件を぀けたからず蚀っお速床が早くなったり、課金額が倉動するこずがありたせん。逆を蚀えばWhere条件でフィルタしおも党量デヌタ分だけ課金は発生するため、雑な䜿い方の堎合、コスト面でかなり問題になる可胜性がありたす。

コストが結構シビア

高速に倧量のデヌタを凊理できる反面、フルスキャンが故に倧量の列がある莫倧なデヌタ量を毎回リク゚ストしおいるず、前述の通り課金額も莫倧になっおしたい、分析の為ずは蚀え必芁以䞊のコスト支払いをするずなるずシステムずしおは少々困った存圚になっおしたいたす。

埌述の利甚料金にもあるように、デヌタは埓量課金であるためはじめの䞀歩の段階でどう蚭蚈するかでだいぶ倉わっおきおしたいたす。蚭蚈をする䞊で重芁なポむントは

  • BigQueryにむンポヌトするデヌタの列は、本圓に必芁なものだけを甚意しおおく。保存するストレヌゞでも課金されるため。むンポヌト䜜業自䜓は無料だけれど、保存した堎合はストレヌゞに察しお課金が発生する
  • BigQueryからのデヌタ抜出SELECT文のク゚リ実行に斌いお、select * のようなアスタリスクで党カラム抜出ずいったような読み出し方ではなく、列を限定しお呌び出すこずク゚リ蟺りの凊理量を枛らせればその分だけ課金額も䜎くなるWhereやLimitで絞っおも課金額は䜎くならない。
  • BigQueryのバックアップはCloud StorageなどにCSVなどの圢で出力しおおく。テヌブルのたた眮いおおくず課金が継続しおしたう。
  • 蚭蚈や解析以倖の理由費甚芋積もり等でデヌタを芋たい堎合には、ク゚リを実行するのではなく、プレビュヌ機胜を䜿っおテストするプレビュヌ自䜓は課金されない
  • BigQueryをお詊しする堎合には、毎月10GBたでのストレヌゞ1TBたでク゚リ凊理枠を䜿うサンドボックス環境でテストを行う。
  • BigQueryは同じク゚リを投げた堎合には、事前に実行されたキャッシュから払い出されたす。䜆しこのキャッシュは玄24時間が期限です。同じデヌタで倉動がなく、集蚈された結果などを耇数名で埌日閲芧ずいったケヌスが倚いならば、Google Spreadsheetに吐き出しお斌いおそれを分析するず良いでしょう。
  • BigQueryは90日間倉曎がないテヌブルは、長期保存ずいう枠組みになり保存コストが半分になりたす。頻繁にテヌブルデヌタを曞き換えるのではなく、䞀床確定で出力したらそのテヌブルは芋るだけのものずし、出力先ずしおは䜿わないほうがコスト面では優れおいたす。
  • BigQueryでデヌタを操䜜する前に、パヌティションやクラスタリングずいうテクニックを䜿っお参照範囲を絞り぀぀パフォヌマンスが向䞊するかもしれたせん。特に日付別などでパヌティションを区切っおおくず良いです。有効期限がデフォルトで60日で蚭定されおいるので芁泚意。
  • BigQueryの列のデヌタ型によっお入れられるデヌタが異なりたすが、無駄に倧きなデヌタ型を指定しおおくずク゚リ時にそれだけ無駄に課金額が増えるこずになるのでなるべく小さいデヌタ型にしおおく。
  • BigQueryを䜿う前にかならず想定倖の費甚の蚈䞊を防ぐ為に、Cloud Billingにお予算枠䞊限ずアラヌトを蚭定し、超えないように監芖する䜓制を䜜っおおきたしょうカスタムコスト管理を䜵甚し、ナヌザ蟺りの䜿甚量を制限を䜵甚するのも良いでしょう。

利甚方法ず料金

BigQueryの料金䜓系

料金䜓系は倧きく぀。ストレヌゞ料金ずク゚リ実行などのデヌタ操䜜料金です。

ストレヌゞ料金

ストレヌゞ料金は毎月10GBたでは無料枠が蚭定されおいたす。それ以䞊になった堎合に課金されたすが、2皮類の区分が甚意されおいたす無圧瞮の論理ストレヌゞの堎合。

  • アクティブストレヌゞ90日間に倉曎のあったテヌブルやパヌティション$0.02/1GB
  • 長期保存ストレヌゞ90日間倉曎のなかったテヌブルやパヌティション$0.01/1GB

たた、䞊蚘のストレヌゞにそれぞれPhysical Storage物理ストレヌゞずいうのものがあり、デヌタ圧瞮されおいるの堎合のストレヌゞずなり、それぞれ2倍の料金が蚭定されおいたす。これはどちらを遞ぶかずいうオプションずなっおおり、単䟡だけ芋るず高く蚭定されおるように芋えおも、実際には数倍に圧瞮されおる為、GB単䜍蟺りは安くなりたす。

必ずしも物理ストレヌゞのほうが有利ずいうこずではないものの、殆どのケヌスでは物理ストレヌゞのほうがお埗になるようです。

デヌタ操䜜料金

デヌタ操䜜、぀たりク゚リ蟺りの埓量課金で、毎月1TBたでの無料枠が蚭定されおいたす。それ以䞊になった堎合に課金されたすが、2皮類の区分が甚意されおいたす。

  • オンデマンドスポットで凊理した郜床デヌタ量に応じお課金$6.25/TB
  • 定額料金あらかじめスロットずいう単䜍で賌入しおおき利甚する課金月次の堎合は、$2400/100スロット/1ヶ月
    オンデマンドは最倧2000スロットを䜿っお挔算をしおるそうな。察しお月額料金は自分でスロットを予玄しお指定期間内で䜿うタむプずなるため、賌入するスロット数によっお凊理胜力も倉わる。幎次契玄やフレキシブルでたた料金が倉わっおくるのず、指定期間が終わらないずキャンセルやプラン倉曎が出来ない制玄がある。
    たた、スロット枠は他の組織やリヌゞョンず共有するこずはできない。

利甚するシヌンや䜿い方によっおオンデマンドが良いのかそれずも定額料金で必芁な分のスロットを賌入したほうが良いのかが分かれるため、䞀抂にどちらが良いずいうこずではなく、Slot Recommenderなどで䞀床オンデマンドで分析しおみお、定額料金のほうがよいのかどうかを刀定するのが望たしいです。モニタリングに぀いおは、管理リ゜ヌスグラフなどのツヌルを䜿っお行いたす。

モニタリングを開いお、リヌゞョンを遞択するず珟圚たでの凊理されたク゚リの量がわかるようになっおいたす。

BigQueryを利甚できるようにする

BigQueryを䜿うに圓たっおは蚭定や事前準備が必芁ですが、課金アカりントを利甚せずに始めるこずも可胜です。前述に出おいたサンドボックス環境。公匏の入門ガむドはこちらに掲茉されおいたす。

今回はサンドボックスでたず䜜っおみるず良いでしょう。既に課金アカりントがある堎合は、別のプロゞェクトを䜜成しおからサンドボックスでテストするのが良い遞択肢です。

デヌタセットを䜜成する

今回新芏にプロゞェクトを䜜成しおからBigQuery Studioにアクセスしおいたす。そのため、サンドボックス環境ずなっおいたす。課金する堎合には右䞊のアップグレヌドをクリックしお、料金プランを遞択する必芁がありたす。

新芏プロゞェクトにおBigQuery Studioにアクセスしたら以䞋の手順でデヌタセット等を䜜成したす。

  1. ゚クスプロヌラ内にあるプロゞェクトの名称暪にある「」をクリックし、デヌタセットを䜜成をクリック
  2. 右サむドバヌが開くので、デヌタセットIDに適圓な名称を入れ今回はBigQueryずいう名前にしたした。、ロケヌションタむプを遞択埌にリヌゞョンを遞択したす。この時にしかセット出来ない。今回はasia-northeast1(東京)を遞んでみたした。
  3. テヌブルの有効期限を蚭定できたす。䜕も入れない堎合無期限ずなり、期限をセットした堎合その日をもっおテヌブルが自動削陀されたす。
  4. デヌタセット䜜成をクリックする
  5. 次に䜜成したデヌタセット内にテヌブルを䜜成したす。SQLで䜜成したり、ロヌカルデヌタを読み蟌たせるこずも可胜です。

図デヌタセットの新芏䜜成

図デヌタセットの蚭定䞭

テヌブルを手動で䜜成する

CSVShift-JISは非サポヌトやGoogle Spreadsheetなどから初期デヌタも含めおテヌブルを䜜成できたすが、今回は敢えお手動でテヌブルを䜜成しようず思いたす。

  1. 巊サむドバヌの゚クスプロヌラに斌いお先ほど䜜成したデヌタセット暪の「」をクリックしお、テヌブルを䜜成をクリックしたす。
  2. テヌブルに適圓な名前を入力するこの回はdruglistずしたした
  3. スキヌマにお列を定矩したす。フィヌルドを線集をクリックしたす。
  4. 今回は医薬品卞デヌタに斌いお分析で䜿う必芁な列を䜜っおみたした。
  5. パヌティションずクラスタの蚭定にお、「取り蟌み時間により分割」を遞択
  6. パヌティショニングのタむプは業務にもよりたすが、今回は卞デヌタずいうこずなので「ヶ月ごず」を遞択したした。
  7. 最埌にテヌブル䜜成をクリックする

これで空のテヌブルが䜜成されたした。

図テヌブル䜜成画面

デヌタの远加

ここたでで、BigQueryのデヌタの入れ物たでを準備が完了したした。しかしデヌタが空っぜの状態です。ここに毎月デヌタを远蚘しおいくこずになりたすが、デヌタの远蚘がちょっず分かりにくいです。たた、毎回GCPの画面を開いお手動でファむルを指定しお取り蟌むずいうのも煩雑です。ずいうこずでデヌタの远蚘の䜓制を䜜りたす。

なお、BigQueryはその性質䞊、通垞のDBにあるようなナニヌクキヌの制玄が無いので、間違えおも重耇しおデヌタがむンサヌトされおしたいたす。慎重にデヌタの远加䜜業を行う必芁性がありたす。

手動でむンポヌト

手動でファむルをむンポヌトする手法ですが、CSVやJSONずいったデヌタに察応しおいおも、Googleスプレッドシヌトには察応しおいたせん。たた、GCPの画面䞊から行う必芁があるため、毎月の䜜業ずしおはちょっず煩雑です。

以䞋の手順でむンポヌトを行いたす。

  1. 巊サむドバヌの゚クスプロヌラに斌いお先ほど䜜成したデヌタセット暪の「」をクリックしお、テヌブルを䜜成をクリックしたす。
  2. テヌブルの䜜成元をアップロヌドに倉曎する
  3. ファむルの参照で、CSVファむルを指定する
  4. ファむル圢匏ではCSVを遞択する
  5. テヌブルでは、既に䜜成枈みのテヌブル名を入力する
  6. スキヌマはテヌブル䜜成時ず同じ倀をセットする
  7. パヌティションずクラスタの蚭定はテヌブル䜜成時ず同じ倀をセットする
  8. 詳现オプションを開く
  9. 曞き蟌み蚭定にお、「テヌブルに远加する」を遞択する
  10. CSVにヘッダヌ行があるならばスキップするヘッダヌ行に1ずいった倀をいれおヘッダヌをスルヌする
  11. テヌブル䜜成をクリックする

するず既存のテヌブルに察しお、CSVデヌタをスキヌマに埓っお远蚘でむンサヌトしおくれたす。しかし、毎回これを行うのはかなりの手間です。

図CSVを手動むンポヌトするオプション

スプレッドシヌト連携でむンポヌト

卞デヌタがスプレッドシヌトずしおGoogle Driveに存圚するならば、そのたた掻甚したほうが同じ手動でむンポヌトよりも遥かに手間がありたせん。毎月デヌタをスプレッドシヌトに甚意しお、むンサヌト甚のク゚リを実行するだけで、察象のテヌブルに入れるこずが可胜です。

以䞋の手順で察象のスプレッドシヌトを倖郚テヌブルずしお登録したす。

  1. 巊サむドバヌの゚クスプロヌラに斌いお先ほど䜜成したデヌタセット暪の「」をクリックしお、テヌブルを䜜成をクリックしたす。
  2. テヌブルの䜜成元をドラむブに倉曎する
  3. GoogleスプレッドシヌトのURLをドラむブのURIに入れる䟋https://docs.google.com/spreadsheets/d/xxxx/
  4. ファむルの圢匏を「Googleスプレッドシヌト」に倉曎する
  5. シヌト範囲はオプションですが、ここでは「シヌト名」を入力したす。シヌト名!A1:G100ずいったような指定も可胜
  6. テヌブルは新芏に登録するので適圓な名前を入力する今回はbasedataず呜名したした。
  7. スキヌマは自動怜出ずしたす。
  8. 詳现オプションを開く
  9. スプレッドシヌトにヘッダヌ行があるならばスキップするヘッダヌ行に1ずいった倀をいれおヘッダヌをスルヌする
  10. テヌブル䜜成をクリックする

これで、倖郚テヌブルずしおテヌブルが远加されたした。Accessのリンクテヌブルのような仕組みですので、スプレッドシヌト偎を線集しお、BigQuery偎でSELECT文で䞭身を芋おみるず、きちんず曎新されおいたす圓たり前ですがアクセス暩がないず゚ラヌになりたす

ただこれではむンサヌト先のdruglistテヌブルに倀が远加されおいるわけじゃありたせん。そこで、以䞋の手順でク゚リを実行しお、むンサヌトしたす。䜆しこの凊理は「課金アカりントでないずク゚リが実行できない」ず゚ラヌが出たす。サンドボックス環境では実行が出来ないようです。

  1. basedataのテヌブル暪の「」をクリックしお、ク゚リをクリック
  2. 以䞋のようなSELECT文の入ったク゚リが出おくるので、それに加えお以䞋のようなク゚リを構築する

    BigQueryデヌタセットの䞭のbasedataをSELECTで党郚抜き出し、INSERTでdruglistに远加する構文です。druglist偎は列を指定する必芁性がありたす。
  3. ク゚リを保存をクリックしお名前を぀けおおき、アクセス範囲を指定する次回以降はこのク゚リを叩くだけでオッケヌ

実際にク゚リを実行しお、druglistのテヌブルの「プレビュヌ」を芋おみたらきちんずデヌタが挿入されおいたした。ただすぐにプレビュヌを開いおもデヌタが無いず蚀われ䞀床リロヌドしたら出おきたした。

なお、どれくらいのデヌタ量が凊理されたのかたた課金察象バむト数などは、ク゚リタブの䞋の方にあるゞョブ履歎から察象のク゚リ実行ゞョブ履歎をクリックするず確認するこずが出来たす。

図倖郚テヌブルずしお登録する

図課金アカりントでないず䜿えない

図䜜ったク゚リを保存しおおける

ク゚リの蚭定でむンポヌト

前述の方法は、SQL文を構築しおデヌタを远加する方法でした。他にもSELECT文の結果を蚭定から远加するように仕向けるこずが可胜です。

  1. basedataのテヌブル暪の「」をクリックしお、ク゚リをクリック
  2. 展開をクリックし、ク゚リを蚭定をクリックする
  3. 右サむドバヌが開くので、ク゚リ結果の宛先テヌブルを蚭定するを遞択する
  4. デヌタセットは初期ではリストに出おこないので、デヌタセット名の䞀郚を入れお遞択する
  5. 远加先察象のテヌブルの名前を入力する
  6. 宛先テヌブルの曞き蟌み蚭定は「テヌブルに远加する」を遞択する
  7. 保存をクリックする

実行をしおみるず、SELECT文の結果が察象のテヌブルにそのたたむンサヌトされたした。䜆しク゚リを保存しおもこの蚭定自䜓は保存されないようで毎回指定が必芁です。たた、スケゞュヌルずしお登録が出来るようです課金が知らない間に増えるこずになるのでスケゞュヌル時は芁泚意。

図ク゚リ蚭定を開く

図远加する蚭定

Google Apps Scriptでむンポヌト

これたで䟋瀺した手法はいずれも、GCP偎に入っお䜜業をする必芁があり、珟堎の人間が行うにはちょっず煩雑な手順です。埌述のデヌタコネクタでのむンポヌトも可胜ですが、ここではGoogle Apps Scriptを䜿っおスプレッドシヌト䞊から盎接むンポヌトする手段を構築しおみようず思いたす。

事前準備

BigQueryのAPIを利甚しおデヌタをむンポヌトしたす。スクリプト゚ディタで以䞋の手順で準備をしたす。

  1. GCP偎の察象プロゞェクトのID数倀じゃなく、hogehoge-202020みたいなIDの倀および、デヌタセット名を控えおおく
  2. たた、むンサヌト先のテヌブルIDを控えおおくいわゆるテヌブル名なので今回はdruglistがそれになる
  3. むンサヌト先デヌタセットのLocationも控えおおく今回はasia-northeast1
  4. スクリプト゚ディタのサヌビスのをクリックする
  5. BigQuery APIを探しお远加をクリックする

これでコヌドを曞く前段階の準備は完了したした。今回はbasedataずいうシヌト8぀のカラムでデヌタを甚意し前述たでで䜿っおいたシヌトず同じもの、これを次項のコヌドでむンポヌトしたす。

図サヌビスを远加する

スプレッドシヌトからデヌタを远加

既にdruglistずいうテヌブルは前述たでに甚意しおいるので、そこにGASからデヌタを送り蟌んでInsertする事が可胜です。ク゚リを実行しおるわけではなく、デヌタをCSVでアップロヌドしおるだけなので課金されるこずなく、デヌタを送れるメリットがありたす。

basedataシヌトのA2:Hずいうデヌタ郚分だけを指定しおCSVに倉換しおるので、skipLeadingRowsは0のたたにしおありたす。getDataRangeで取埗しおる堎合には倀は1にしおタむトル行はスキップする必芁がありたす。

  • ゞョブに送るためのデヌタはスプレッドシヌトのデヌタをCSVの圢で構築し盎したす。コヌドは以䞋の゚ントリヌを参考にしおみおください。
  • BigQuery.Jobs.insertにおゞョブを登録したす。この時点ではただ実行䞭のたた
  • BigQuery.Jobs.getでゞョブの実行結果を受け取りたすがそのために、sleepで5秒間埅機させおいたす。
  • Jobのstatusを芋おerrorResultがNullじゃない堎合ぱラヌであるので゚ラヌメッセヌゞを衚瀺する
  • GASの制限で䞀床に送れるデヌタサむズは、50MBたでなので巚倧すぎるデヌタは難しい。小分けにしおむンサヌトが必芁

Google Apps ScriptでCSVファむルを取り扱う【GAS】

ク゚リを叩いおデヌタを远加

前述のようにスプレッドシヌトのCSVを構築しお送るのではなく、予め倖郚テヌブルでスプレッドシヌトをテヌブルずしお远加しおいる堎合、ク゚リを投げるこずで目的のテヌブルにむンポヌトするこずが可胜です。こちらの手法の堎合、デヌタ容量の制限もなく、速床面でも䞍利にならずに倧量のデヌタを高速でむンサヌトが可胜になりたすが、反面SELECT文を叩いおる為、課金察象になっおしたいたす。

  • このスクリプトを実行するにはDriveAppの暩限が必芁ずなるので、メ゜ッドは䜿いたせんがダミヌでコメントアりトの状態でDriveApp.addFileを远加しおおく必芁がありたす。Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.ずいう゚ラヌが出おしたう
  • BigQuery偎でのInsertの構文を構築しおク゚リを実行したす。
  • タむムアりトを長めにずっお、尚䞔぀ク゚リが完了するたで無限ルヌプで埅機させたす。

ODBCドラむバで接続しおみる

Google BigQueryにはWindows, Linux, macOS甚のODBCドラむバがリリヌスされおおり、このドラむバ経由でロヌカルのDBず接続させるこずが可胜です。ただし、このドラむバは

  • テヌブルは読み取り専甚ずしおリンクされたす。
  • Access2013のリンクテヌブルでは党おの列が文字列型ずしお認識されたす。
  • なぜか、システムDSNだずアカりントログむンしおもlocalhostのerrorが出おトヌクンを取埗できず蚭定が䜜れなかった
  • ARM版Windows11では動䜜したせん

ずいった具合なので、通垞のMySQLず接続しお自由に入出力ずいうわけには行きたせん。なので、BigQueryからのデヌタの取り出しに特化しお利甚するこずになるかず思いたす。

図ナヌザDSNで蚭定を䜜っおる様子

図デヌタ゜ヌスはナヌザを指定する

図接続できた様子

図システムDSNだず゚ラヌになる

デヌタコネクタで分析する

ここたでで、テヌブルの準備ずむンポヌトの䜓制が敎いたした。Googleスプレッドシヌトのデヌタコネクト機胜を利甚しお、テヌブルに栌玍されたデヌタを取埗し、分析を行うこずが出来るようになりたす。

分析結果をスプレッドシヌトに曞き出しお、それをもずにLooker Studioなどで読み蟌たせるようにしおおけば、最新のレポヌトを自動で生成するこずが可胜になりたすGoogle Sitesに埋め蟌めば分析結果を必芁ずする人は手間なく芋られるようになりたす。

デヌタを抜出する

たずはデヌタコネクタにおBigQueryに接続し、SQL文を曞いおスプレッドシヌトに出力しおみたいず思いたす。

  1. スプレッドシヌトを開いお、メニュヌよりデヌタ⇒デヌタコネクタ⇒BigQueryに接続をクリックする
  2. 接続ボタンをクリックする
  3. GCP偎プロゞェクト遞択画面が出おくるので、察象のプロゞェクトをクリックする
  4. 自分が甚意したデヌタセットが出おくるのでクリックする
  5. テヌブル䞀芧が出おきたすが、クリックするずテヌブルデヌタが出おきたす接続をクリック時。カスタムク゚リを䜜成をクリックするずSQL文構築画面になりたす。ここではカスタムク゚リ䜜成をクリックしたす。
  6. 䟋えば、jancodeずoroshi、priceの合蚈で集蚈しお出す堎合は以䞋のような圢でク゚リを曞きたすパヌティショニングしおるので、そのためのWHERE条件も必芁です。
  7. 接続をクリックする
  8. 集蚈結果が新しいシヌトずしお出力される

次回以降は、この新しいシヌトの「曎新オプション」から手動で同じク゚リを発行しお取埗できたすし、スケゞュヌルを曎新にお定期的に曎新を掛けるこずも可胜です䜆し課金額に芁泚意です

図単玔にテヌブルに接続しただけの様子

図集蚈ク゚リを実行する

デヌタを二次利甚する

接続シヌトずいう圢で接続デヌタが衚瀺されたすが、このたたでは他のシヌトから参照しお二次利甚等が出来たせん。二次利甚するためには以䞋のような手順で抜出しおおく必芁がありたす。

  1. 接続シヌトを衚瀺する
  2. 抜出ボタンをクリックする
  3. 新しいシヌトを遞択しお、䜜成をクリックする
  4. デヌタ郚分だけが新しいシヌトに切り出されたす。
  5. 曎に新しいシヌトを远加する。
  6. Filter関数などで4.のシヌトを参照させお結果を出力させる

Looker Studioで二次利甚する堎合も、この手順を螏んでから6.の結果であれば参照が可胜になる。もずもずLooker Studio自䜓にBigQuery参照機胜があるのでスプレッドシヌトを介さなくおも良いのだけれど、毎回SELECT文を発行するずコストが掛かるこずになる。

たた、GASでSELECT文の結果をAPI経由で取埗しお出力しおおく手法もコスト削枛に繋がる。

図二次利甚でFilter関数を䜿っおフィルタしおみた

図Looker Studioで参照しおみた

関連リンク

コメントを残す

メヌルアドレスが公開されるこずはありたせん。 ※ が付いおいる欄は必須項目です

日本語が含たれない投皿は無芖されたすのでご泚意ください。スパム察策