How to query the data from Google BigQuery via JDBC connection in Federation Server?

Created by Jun Hui Liu on Mon, 02/01/2021 - 21:22

Published URL:

https://www.ibm.com/support/pages/node/6410770

6410770

How To

Summary

When user wants to query the data from Google BigQuery via JDBC in Db2 Federation Server, please follow the steps:
1. Enable API and create service account for BigQuery in BigQuery console;
2. Check bundled JDBC connection driver for BigQuery in Federation server;
3. Create JDBC Wrapper, Server, User Mapping, and Nickname, and then query the data from BigQuery.

Objective

Query the data from Google BigQuery via JDBC connection in Db2 Federation Server

Environment

Linux,AIX

Steps

When the user wants to query the data from Google BigQuery, it can use bundled JDBC driver in Db2 Federation Server.

The bundled JDBC driver supports two methods of authentication. It takes "serviceaccount" as an example in this article.

1. Review contents in BigQuery guide, and follow the guide to enable API and create a serviceAccount:

a> Enable the API for BigQuery;

b> Go to credentials page to create a serviceAccount for BigQuery, create a private key for account,

save the private key file and serviceAccount mail which are used to connect BigQuery later.

2. Check bundled JDBC driver in Federation.

$ ll /home/db2inst1/sqllib/federation/jdbc/lib/FOgooglebigquery.jar
rwxrwxrwx 1 bin bin 11866139 Apr 10  2020 /home/db2inst1/sqllib/federation/jdbc/lib/FOgooglebigquery.jar

3. Registering JDBC wrapper

db2 => create wrapper jdbc
DB20000I  The SQL command completed successfully.

3. Create Server and User mapping for Google BigQuery

db2 => CREATE SERVER BIGQUERY wrapper JDBC options (DRIVER_PACKAGE '/home/db2inst1/sqllib/federation/jdbc/lib/FOgooglebigquery.jar', driver_class 'com.ibm.fluidquery.jdbc.googlebigquery.GoogleBigQueryDriver', url 'jdbc:ibm:googlebigquery:AuthenticationMethod=serviceaccount;Project=federationjdbc;dataset=jdbc;ServiceAccountEmail=safed-923@federationjdbc.iam.gserviceaccount.com;ServiceAccountPrivateKey=/home/db2inst1/bigquery/key/federationjdbc-6a2e2f1e9059.json', PUSHDOWN 'Y', db2_maximal_pushdown 'Y' )
DB20000I  The SQL command completed successfully.

-- The authentication uses private key which set in url property above statement;
-- The credentials below can set to be any values
db2 => create user mapping for db2inst1 server BIGQUERY options(remote_authid 'userAny', remote_password 'pwdAny')
DB20000I  The SQL command completed successfully.

4. Create nickname for table of Google BigQuery, and query the data from nickname

db2 => create nickname nk_numeric for BIGQUERY."federationjdbc.jdbc"."test_numeric"
DB20000I  The SQL command completed successfully.

db2 => describe table nk_numeric 

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
COL                             SYSIBM    DOUBLE                       8     0 Yes   

  1 record(s) selected.

db2 => select * from nk_numeric

COL                     
------------------------
  +1.23400000000000E+003
  +9.09000000000000E+003

  2 record(s) selected.

db2 =>

Document Location

Worldwide

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PkvAAE","label":"Federation"}],"ARM Case Number":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"}],"Version":"11.5.4"}]