Pentaho Data IntegrationでGoogle BigQueryと接続に成功した事例

背景

• Pentaho Data Integration 6.1を利用して、日々のKPI算出を行っています。
• BigQueryとの接続を検証をするため、環境構築を行いました。
• 日本語での情報はほとんどなく、その情報ソースに沿って実施しても、エラーになってしまい、試行錯誤のすえ、接続できたので情報をシェアします。
• Pentaho Data Integration（以下、PDI）のバージョンは9.4にあげました。
• JDBCドライバーは最新バージョンだと動かず、公式ドキュメントに沿ってやると動きました。

前提

• PDI 6.1はすでに稼働している環境で実施しています。
  • PDIを起動するためにはJavaのインストールと環境変数への設定が必要です。
  • 環境ファイルの設定は多少ノウハウが必要です。
• BigQueryとの接続・認証はサービスアカウントを利用します。
  • BigQuery側ではあらかじめサービスアカウントをつくり、p12形式のキーファイルを用意しておきます。サービスアカウント側に必要な権限を付与しておきます。

Pentaho Data Integrationのダウンロード＆展開

• PentahoのCommunity Editionの方を開く

• 左側の［Download］ボタンを押下
  

• 「Click here to acknowledge the above open source licenses.」のチェックボックスにチェックを入れる

• ［Proceed to Download］をクリック
  

• 一覧のなかで、
  pdi-ce-9.4.0.0-343.zip　Pentaho Data Integration (Base Install)
  を選び［Download］をクリック
  

• ZIPファイルを展開する
  ※パスの長いものもあるため、c:\TEMPなどの浅い階層での解凍を推奨
  　解凍は結構時間がかかります。（20分程度）

• 都合のいいフォルダに格納しなおす

JDBCドライバのダウンロード＆展開

• 公式ドキュメントを参照

• JDBCドライバのダウンロードページを開く
  

• Simba version 1.2.2.1004 JDBC 4.2 ドライバをダウンロードする
  類似のURL文字列を参考に直打ちする。
  https://storage.googleapis.com/simba-bq-release/jdbc/SimbaJDBCDriverforGoogleBigQuery42_1.2.2.1004.zip

• ZIPファイルを展開する

※ドライバのバージョンは古すぎても新しくてもダメ（たぶん）

JDBCドライバから必要なファイルの抽出

• 公式ドキュメントによると、必要なファイルをコピーするとなっているが、ファイル数が多いので、不要なファイルを消した方が早い

• 不要なファイルを削除する（16個）

  • commons-codec-1.10.jar
  • commons-compress-1.18.jar
  • commons-lang3-3.5.jar
  • commons-logging-1.2.jar
  • filelist.txt
  • grpc-grpclb-1.18.0.jar
  • guava-26.0-android.jar
  • httpclient-4.5.5.jar
  • httpcore-4.4.9.jar
  • jackson-annotations-2.9.0.jar
  • jackson-core-2.9.8.jar
  • jackson-databind-2.9.8.jar
  • release-notes.txt
  • Simba JDBC Driver for Google BigQuery Install and Configuration Guide.pdf
  • slf4j-api-1.7.25.jar
  • third-party-licenses.txt

• なお、必要なファイルは下記の通り（42個）

  • animal-sniffer-annotations-1.14.jar
  • api-common-1.7.0.jar
  • avro-1.9.0.jar
  • checker-compat-qual-2.5.2.jar
  • error_prone_annotations-2.1.3.jar
  • gax-1.42.0.jar
  • gax-grpc-1.42.0.jar
  • google-api-client-1.28.0.jar
  • google-api-services-bigquery-v2-rev426-1.25.0.jar
  • google-auth-library-credentials-0.15.0.jar
  • google-auth-library-oauth2-http-0.13.0.jar
  • GoogleBigQueryJDBC42.jar
  • google-cloud-bigquerystorage-0.85.0-alpha.jar
  • google-cloud-core-1.67.0.jar
  • google-cloud-core-grpc-1.67.0.jar
  • google-http-client-1.29.0.jar
  • google-http-client-apache-2.0.0.jar
  • google-http-client-jackson2-1.28.0.jar
  • google-oauth-client-1.28.0.jar
  • grpc-alts-1.18.0.jar
  • grpc-auth-1.18.0.jar
  • grpc-context-1.18.0.jar
  • grpc-core-1.18.0.jar
  • grpc-google-cloud-bigquerystorage-v1beta1-0.50.0.jar
  • grpc-grpclb-1.18.0.jar
  • grpc-netty-shaded-1.18.0.jar
  • grpc-protobuf-1.18.0.jar
  • grpc-protobuf-lite-1.18.0.jar
  • grpc-stub-1.18.0.jar
  • gson-2.7.jar
  • j2objc-annotations-1.1.jar
  • javax.annotation-api-1.3.2.jar
  • jsr305-3.0.2.jar
  • opencensus-api-0.18.0.jar
  • opencensus-contrib-grpc-metrics-0.18.0.jar
  • opencensus-contrib-http-util-0.18.0.jar
  • protobuf-java-3.7.0.jar
  • protobuf-java-util-3.7.0.jar
  • proto-google-cloud-bigquerystorage-v1beta1-0.50.0.jar
  • proto-google-common-protos-1.15.0.jar
  • proto-google-iam-v1-0.12.0.jar
  • threetenbp-1.3.3.jar

• 抽出したファイルをpentaho-dataintegration配下のlibフォルダ内に追加する

PDIの起動とデータベース設定

• Windowsの場合は、Spoon.batをダブルクリックして起動

• 左カラムデザイン＞入力＞テーブル入力
  

• データソースの新規作成＞一般＞Google BigQueryを選択

• 一般設定は下記の通り

  • ホスト名＞https://www.googleapis.com/bigquery/v2
  • Project ID＞BigQueryのプロジェクト名
  • ポート番号＞443
    

• オプション設定は下記の通り
  

パラメータ	値
OAuthType	0
OAuthServiceAcctEmail	サービスアカウントのメールアドレス
OAuthPvtKeyPath	サービスアカウントのキーファイルパス
Timeout	120

• サービスアカウントのメールアドレスは下記のような感じで設定
  [e-mail]
• サービスアカウントのキーファイルパスは下記のような感じで設定
  C:\pentaho-di\xxx\pentahoxxx.p12

*［テスト］接続する