BigQueryからDr.Sumにデータをインポートするにはどのようにしたらいいのでしょうか?
CSV/Excelファイルのインポートはできるのは知っているが、クラウドのデータベースからDr.Sumにインポートすることが想像がつかない…。そんな方に今回はクラウドのデータベースであるBigQueryからDr.Sumにデータをインポートする方法をお伝えします。

BigQueryとDr.Sumの位置付け

企業によりデータ基盤の仕組みは異なりますが今回の記事ではデータレイクにBigQuery、データウェアハウスにDr.Sum、のようにDr.Sumをエンドユーザーのフロントに置くようなデータ分析基盤として使用しているユーザーを対象としてご説明します。

BigQueryをデータレイクとして扱う

クラウドのデータベースであるBigQueryはデータウェアハウスにもデータレイクにもなりえます。今回は社内のデータが全てBigQueryに蓄積されていくがエンドユーザー向けに公開するには加工が必要、というようなシナリオを想定してデータレイクの位置付けとします。

Dr.Sumをデータウェアハウスとして扱う

データを可視化するならDr.Sumを見ればよい!というように全社的なデータの分析可視化基盤としてDr.Sumを利用している、というようなシナリオを想定してデータウェアハウスの位置付けとします。

前提:BigQueryにテーブルを持っている。

前提として今回はBigQuery上に下記テーブルを保持していることを前提とします。
データセット名:SAMPLE
テーブル名　　:SAMPLE

BigQueryにサービスアカウントを作成し必要情報を確認する

BigQueryに接続する際はサービスアカウントによる接続とユーザーアカウントを使用した接続の2通りあります。今回は発行されたキーファイルを使用して簡易的に接続できるサービスアカウントによる接続でご説明します。

BigQuery APIが有効になっているか確認する

BigQueryに接続する際はAPIで接続するため、まずBigQuery APIが有効になっているか確認します。

まずGoogle Cloud Platformに管理者でログインします。
https://console.cloud.google.com

①ログイン後、画面左上のメニューを選択します。
②メニューから「APIとサービス」を選択します。
③「ライブラリ」を選択します。
④「ビッグデータ」を選択します。
⑤「BigQuery API」を選択します。
⑥「APIが有効です」と記載されていることを確認します。
　※有効ではない場合は有効にします。

サービスアカウントを作成し「メールアドレス」を控えておく

続いてサービスアカウントを作成します。サービスアカウントはDr.SumからBigQueryに接続する際の接続アカウントになります。

①画面左上のメニューを選択します。
②「APIとサービス」を選択します。
③「認証情報」を選択します。
④「認証情報を作成」を選択します。
⑤「サービスアカウント」を選択します。
⑥「サービスアカウント名」を入力します。
　今回は techblog とします。
※ここで自動生成されるメールアドレスをコピーして控えておきます。
⑦「完了」を選択します。

秘密鍵のファイル(JSONファイル)を作成する

ODBCデータソースの設定時に使用する秘密鍵のファイル(JSONファイル)をあらかじめダウンロードしておきます。

①サービスアカウントを作成した後に表示される画面から
　正しくサービスアカウントが作成されたことを確認し、鉛筆マークから編集に入ります。
②「キー」タブを選択します。
③「鍵を追加」を選択します。
④「新しい鍵を作成」を選択します。
⑤キーのタイプに「JSON」を選択します。
⑥「作成」を選択します。
ここでダウンロードされたJSONファイルをデスクトップなど、分かりやすいところに置いておきます。

プロジェクトIDを確認する

Google Cloud上の「プロジェクト」とは「データセット」の上位の管理対象です。
プロジェクト>データセット>テーブル の順番で管理されます。
Dr.Sumで例えると
Dr.Sumサーバー > データベース > テーブル の順番で管理しますよね…。
今回はその上位層となるプロジェクトIDを確認します。

①画面左上のメニューを選択します。
②「IAMと管理」を選択します。
③「設定」を選択します。
④「プロジェクトID」を確認します。
ここでプロジェクトIDを控えておきます。

サービスアカウントにBigQueryジョブユーザーのロールを割り当てる

作成したサービスアカウントに「BigQueryジョブユーザー」というロールを割り当てます。ジョブとはデータの読み込み、データのエクスポート、データのクエリ、データのコピーなど、BigQuery がユーザーに代わって実行するアクションのことです。

①画面左上のメニューを選択します。
②「IAMと管理」を選択します。
③「IAM」を選択します。
④「アクセス権を付与」を選択します。
⑤「新しいプリンシパル」に作成したサービスアカウントのメールアドレスを入力します。
⑥「ロール」に BigQueryジョブユーザー を選択します。
⑦「保存」を選択します。
保存を選択した後に「ロール」BigQueryジョブユーザー が付与されていることが確認できたら完了です。

参照するデータセットに対してBigQueryデータオーナーのロールを割り当てる

プロジェクト単位の権限として「BigQueryジョブユーザー」、
データセット単位の権限として「BigQueryデータオーナー」があります。
今回はデータセット単位の権限として「BigQueryデータオーナー」の権限を付与します。

①画面左上のメニューを選択します。
②「BigQuery」を選択します。
③「BigQuery Studio」 を選択します。
④データセットアイコンの「SAMPLE」を選択します。
ここでデータセット名を控えておきます。
⑤「共有」を選択します。
⑥「権限」を選択します。
⑦「プリンシパルを追加」を選択します。
⑧「新しいプリンシパル」にサービスアカウントのメールアドレスを入力します。
⑨「ロール」に BigQuery データオーナー を選択します。
⑩「保存」を選択します。
⑪BigQuery データオーナーにサービスアカウントが追加されたことを確認したら
　「閉じる」を選択します。

接続に必要な情報が揃ったらインポートの手順に進みます。

BigQueryのデータをDr.Sumにインポートする

BigQueryへの接続には専用のODBCドライバーを使用します。

ODBCドライバーをインストールする

下記サイトにアクセスし、ODBCドライバーのインストーラーをダウンロードします。https://cloud.google.com/bigquery/docs/reference/odbc-jdbc-drivers?hl=ja
①「Windows64ビット(msi)」を選択します。
②「SimbaODBCDriverforGoogleBigQuery64_3.0.2.1005.msi」がダウンロードされます。
　※バージョンは時期により異なります。
③ダウンロードしたインストーラーをダブルクリックで起動します。
④「Next」を選択します。
⑤「I accept the terms in the License Agreement」にチェックを入れます。
⑥「Next」を選択します。
⑦「Next」を選択します。
⑧「Install」を選択します。
⑨「Finish」を選択します。

ODBCデータソース(64ビット)を設定する

①Windowsメニューから「ODBCデータソース(64ビット)」を開きます。
②「システムDSN」のタブを選択します。
③「追加」を選択します。
④「Simba ODBC Driver for Google BigQuery」を選択します。
⑤「完了」を選択します。

赤文字が事前に整理した情報です。
①「Data Source Name」を任意で入力します。
今回は BigQuery とします。
②「Encrypt Sensitive Data」を 「For All Users」 と指定します。
③「OAuth Mechanism」を「Service Authentication」と指定します。
④「Email」にサービスアカウントのメールアドレスを入力します。
⑤「Browse」からダウンロードしたJSONファイルを指定します。
⑥「Catalog(Project)」を任意で指定します。
今回はデータセット「SAMPLE」を含むプロジェクトを指定します。
⑦Datasetを任意で指定します。
今回は「SAMPLE」を指定します。
⑧「Test」を選択します。
⑨SUCCESS! と表示されたら「OK」を選択します。
⑩「OK」を選択します。

※
「For All Users」に変更するところを忘れがちです。
また、入力情報が間違っている場合でもSUCCESS!と表示される場合があるため
実際にDr.Sumにインポートできるまで気が抜けません!

VTBを設定する

VTBを設定する際に注意点としては以下二つになります。
・引用符を「[ ` ] バッククォーテーション」にする
・ユーザーIDとパスワードは不要

①「種類」をODBC(64Bit)を選択します。
②「名前」を任意で入力します。
③「グループ」を選択します。
今回は Administrators とします。
④「データソース」にODBCデータソースで作成したデータソースを選びます。
⑤「SQL文の名前引用符」を「 ` 」バッククォーテーションに変更します。
⑥「テスト」を選択します。
⑦「OK」を選択します。
⑧「OK」を選択します。

ODBCでインポートする

下記の別の記事を参考にしてODBCでインポートします。

SQL Server のデータをODBCでデータインポートしよう

https://navi.wingarc.com/product/drsum/9300

SQL Server のデータをDr.SumにODBCでインポートする手順をご紹介します。他にも上位のデータベースのPostgreSQLやMySQLなどのデータも同じ方法でデータを移行することができます。CSVのデータをインポートする場合は下記のリンクをご覧ください。参照:【自動で取り込み】データベースにCSVデータをインポートしよう～CSV自動編～参照:【手動で取り込み】データベースにCSVデータをインポートしよう～CSV手動編～ 他のデータベースからインポートする時の設定手順は、以下の3つだけです!　　(事前準備):ODBC接続の作成　　手順1　...

WingArc TECH BLOG

2020.02.18

無事にBigQueryからDr.Sumにインポートできました!
BigQuery側で必要な情報を集めるところが大変ですがDr.Sum側はいつものようなODBC接続になります。ちなみにBigQuery側の項目名を日本語表記にしておくとデータ参照時にエラーが出たので項目名を英語表記にしたほうが無難な気がします。