CData Google BigQuery Driverのサービスアカウント認証をJSONキーで行う方法

こんにちは。CData Software Japan リードエンジニアの杉本です。

以前、CData Driverでサービスアカウント認証を行う方法を紹介しました。

www.cdatablog.jp

上記記事ではP12形式のキーを用いた方法を紹介していますが、GCPではJSON・P12の2種類の方法でサービスアカウント用キーが提供されていて、現在はJSONキーが推奨となっています。

そこで今回はこのJSONキーを用いた方法を紹介したいと思います。

なお、サービスアカウントの作成方法は先の記事と共通なので、そこまでは以下を参照してみてください。

サービスアカウントの作成

秘密鍵の作成

サービスアカウントを作成したら、秘密鍵も作成しましょう。

以下の「鍵を作成」をクリックし

今回はキーのタイプを「JSON」で選択して、生成します。

ダウンロードしたファイルを開くと、以下のようなJSONファイルが出力されるので、この値を使って認証を行います。

{
  "type": "service_account",
  "project_id": "meta-coyote-270802",
  "private_key_id": "9ec1f4b8147bf714a18d8426cfb37d58e6b074ed",
  "private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQDHSHJh66IvkbjA\nnIzOidyIUXImDuML0PSC+mauZeDd4tMeB+1md1khI6WUV3EQu+f/fSlCEMNbPFAB\nTHCdsRFB44tnQeFEEcjtFrzWq9bZElLMYoni3hrcTYc0PRgeYZYrpYPNDg6b7eIh\nAPy6h1SMbLXHHbArTwkosGqMMQyJiIdgfDf7IbDCL+rHG2ns/CoAOtDeOyGLBMx9\n+4Xv2IAmgmAUN6sZHwtnico3QQheoRL1S9cTJkNxurC33GrvAvWkTL9A0vvctBcg\nP+/VQ8tmHU2OUZEayLPK1qKmVbcVxSMXR2NvblxQSKpE9nD1piMIQv1DhU84M8SU\nJfmYUV8vAgMBAAECggEAEQEmuCH1+WQlpSiBQAB11RtKB7hTzwbPTmZ/y4LS9has\nQrmUxskJTcbFErbcECb7NrWfMc13YKTGrjhaZazzFtxhNqLho2rManqlthWnTrzT\nBttOU7xLA3W0GJTb/CpXOOCLMhHwf4VZZc037idXuLxv+GPD9rYW8dC35BWCsdt8\nxzPgccmnAz1pytrLZw1LrGvi4xbM8Muj+N/fcbuC3ckxrc8DtyVpksD+5g3UrL6n\ny/lQD3ahGwuyUxQ6gKwNEy9QNbVRWPd7nkmmSxInNMUHrJeVVO/94zngxFe1rjox\nIuhw4qGkuDbfstLKnxX9muWdfTKDh7q6SajN+sO0aQKBgQDsmJi2q7ZjToxyJTTV\n5S7HBsCaeypV/GxnAUBD3hRtCfkEXPkbFOhV7Spcw4/CIuFY3bjDL3zMpBV4oOR0\nchp1DKN8RSLSq7jBs8daIzzAW2mKjueTur06TIdoYeSW5t0AGhRzUZhM775BTUAx\nfrDwGlefToRWgHM/hIjv5pueKQKBgQDXoHOCZM0Cn9VShHA4zSLMc/4hpojFaSMl\nWeLRoBmyz5CvNSvz2/f9fMH5QbMeLnkuPbPuALMa2XsCgkNPardsnVm2HVlkpugo\nA7XZAo3VFJByQSHcopWE/THgWzPR4d4h+jRfyhlDB3mDodElK698LgmAg1QWQa2N\nLyaE4WcNlwKBgDgwFTRsmygCY4BILcAOaYmCpQMK9Qbup5/ewDBd8WPj1mue2NoB\noRum80SMfPuR5QqBvXs9L9o5rY+0Rg0RfA8ILVs54AdAGOSHmXRioe7cxrfNMFn5\nIy9RNWcouGzN7vYzO2AXpJ/A3+IKQpB+ke6zktOWhf81CimvhAXVMmfhAoGBAL4P\n8SEDhu2EQz1r/ZsjvkOnDl9M8ovHo7Idq9+Iln5VlTvKy4Y04z+twzDZJ3A4GJqY\nysvEFBtbjaDlfz02XJ3BiL0M26nrsQDsXFL/fFNQwA3luVpIeTDzGQ6gdWnQBMPA\nToAMnP3HTeWoMbjo69Kypa+AkxLXK4oiAHrqT7LbAoGAEz/8+YmZRHmo6UNOZG5C\nHk4dWU6y7q7plzqkcvi/XN3Ms9BBQgVNVMfB9/9qGWp2eOcBqkgwFX1Wh2ZruOgx\ndQYDlrv21q/Gm7oh7FXseBvu3bihoPgHwDpDVm43NnBySzvvSspCY8zV4lCFhL3r\nTDrwtc4PmCsXyfuN4SpjZhI=\n-----END PRIVATE KEY-----\n",
  "client_email": "admin-826@meta-coyote-270802.iam.gserviceaccount.com",
  "client_id": "102763102223596559839",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/admin-826%40meta-coyote-270802.iam.gserviceaccount.com"
}

CData Driverでサービスアカウント認証を行う

それでは、サービスアカウントを用いて、CData Driverで認証を行ってみましょう。

接続に使用するドライバーはCData ODBC Driverを例にしていますが、どのテクノロジーのドライバーでも同様の値で接続できます。

ODBCデータソースアドミニストレータツールを立ち上げて、BigQueryのDSNの編集画面を表示します。

接続構成画面が表示されるので、取得した情報を元に下記のように設定を行います。

• InitiateOAuth=GETANDREFRESH;：GETANDREFRESH固定です。
• ProjectId=meta-coyote-270802;：接続先のProjectIdを指定します。
• DatasetId=sample;：接続先のDatasetIdを指定します。
• OAuthJWTCertType=PEMKEY_BLOB;：P12ファイルの場合はPFXFILEでしたが、ここではPEMKEY_BLOBを指定します。
• OAuthJWTCertPassword=notasecret;：予め取得した秘密鍵パスワードを指定します。
• OAuthJWTCertSubject=*;：固定でアスタリスクを指定します。
• OAuthJWTIssuer=admin-826@meta-coyote-270802.iam.gserviceaccount.com;：作成したサービスアカウントのメールアドレスを指定します。
• OAuthJWTCert=-----BEGIN PRIVATE KEY-----MIIEv（省略）+f+A==-----END PRIVATE KEY-----\n;：取得したJSONファイルのprivate_key部分を指定します。

P12ファイルと異なる点は「OAuthJWTCertType」と「OAuthJWTCert」の部分です。

「OAuthJWTCertType」は秘密鍵の文字列を直接指定するので「PEMKEY_BLOB」を指定します。

そして、「OAuthJWTCert」には取得したJSONファイルに出ている「private_key」の値を指定します。ただし、設定する際に注意したいのは「改行コード」の部分です。初期状態では、「\n」で文字列指定されていますが、これを実際の改行コードに置き換えます。

例えば、VS Codeですと、以下のように一括置換が可能です。（正規表現を使う、にチェックを入れるのを忘れずに）

これで構成した値を、以下のように貼り付けることで認証が通るようになります。

ちょっと長いですが、JDBC の文字列の場合は以下のようになります。

jdbc:googlebigquery:InitiateOAuth=GETANDREFRESH;ProjectId=meta-coyote-270802;DatasetId=sample;OAuthJWTCertType=PEMKEY_BLOB;OAuthJWTCertPassword=notasecret;OAuthJWTCertSubject=*;OAuthJWTIssuer=admin-826@meta-coyote-270802.iam.gserviceaccount.com;OAuthJWTCert=-----BEGIN PRIVATE KEY-----
MIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQDHSHJh66IvkbjA
nIzOidyIUXImDuML0PSC+mauZeDd4tMeB+1md1khI6WUV3EQu+f/fSlCEMNbPFAB
THCdsRFB44tnQeFEEcjtFrzWq9bZElLMYoni3hrcTYc0PRgeYZYrpYPNDg6b7eIh
APy6h1SMbLXHHbArTwkosGqMMQyJiIdgfDf7IbDCL+rHG2ns/CoAOtDeOyGLBMx9
+4Xv2IAmgmAUN6sZHwtnico3QQheoRL1S9cTJkNxurC33GrvAvWkTL9A0vvctBcg
P+/VQ8tmHU2OUZEayLPK1qKmVbcVxSMXR2NvblxQSKpE9nD1piMIQv1DhU84M8SU
JfmYUV8vAgMBAAECggEAEQEmuCH1+WQlpSiBQAB11RtKB7hTzwbPTmZ/y4LS9has
QrmUxskJTcbFErbcECb7NrWfMc13YKTGrjhaZazzFtxhNqLho2rManqlthWnTrzT
BttOU7xLA3W0GJTb/CpXOOCLMhHwf4VZZc037idXuLxv+GPD9rYW8dC35BWCsdt8
xzPgccmnAz1pytrLZw1LrGvi4xbM8Muj+N/fcbuC3ckxrc8DtyVpksD+5g3UrL6n
y/lQD3ahGwuyUxQ6gKwNEy9QNbVRWPd7nkmmSxInNMUHrJeVVO/94zngxFe1rjox
Iuhw4qGkuDbfstLKnxX9muWdfTKDh7q6SajN+sO0aQKBgQDsmJi2q7ZjToxyJTTV
5S7HBsCaeypV/GxnAUBD3hRtCfkEXPkbFOhV7Spcw4/CIuFY3bjDL3zMpBV4oOR0
chp1DKN8RSLSq7jBs8daIzzAW2mKjueTur06TIdoYeSW5t0AGhRzUZhM775BTUAx
frDwGlefToRWgHM/hIjv5pueKQKBgQDXoHOCZM0Cn9VShHA4zSLMc/4hpojFaSMl
WeLRoBmyz5CvNSvz2/f9fMH5QbMeLnkuPbPuALMa2XsCgkNPardsnVm2HVlkpugo
A7XZAo3VFJByQSHcopWE/THgWzPR4d4h+jRfyhlDB3mDodElK698LgmAg1QWQa2N
LyaE4WcNlwKBgDgwFTRsmygCY4BILcAOaYmCpQMK9Qbup5/ewDBd8WPj1mue2NoB
oRum80SMfPuR5QqBvXs9L9o5rY+0Rg0RfA8ILVs54AdAGOSHmXRioe7cxrfNMFn5
Iy9RNWcouGzN7vYzO2AXpJ/A3+IKQpB+ke6zktOWhf81CimvhAXVMmfhAoGBAL4P
8SEDhu2EQz1r/ZsjvkOnDl9M8ovHo7Idq9+Iln5VlTvKy4Y04z+twzDZJ3A4GJqY
ysvEFBtbjaDlfz02XJ3BiL0M26nrsQDsXFL/fFNQwA3luVpIeTDzGQ6gdWnQBMPA
ToAMnP3HTeWoMbjo69Kypa+AkxLXK4oiAHrqT7LbAoGAEz/8+YmZRHmo6UNOZG5C
Hk4dWU6y7q7plzqkcvi/XN3Ms9BBQgVNVMfB9/9qGWp2eOcBqkgwFX1Wh2ZruOgx
dQYDlrv21q/Gm7oh7FXseBvu3bihoPgHwDpDVm43NnBySzvvSspCY8zV4lCFhL3r
TDrwtc4PmCsXyfuN4SpjZhI=
-----END PRIVATE KEY-----;

これで、接続テストが通ればサービスアカウントによる認証は成功です。

あとは、PowerBIなどお好みなツールでアクセスできます。