{Denodo}データソース・ラッパー・基本ビュー・インターフェイスビューの作成(BigQuery)

https://community.denodo.com/kb/en/view/document/Integrating%20Google%20BigQuery%20with%20Denodo?category=Data%20Sources
https://www.isoroot.jp/blog/1651/
https://dev.classmethod.jp/articles/service-account-bigquery-roles/

前提:
Denodo Express インストール、起動済み

-------- BigQuery側作業

-- 1. 前作業

gcloud init
gcloud auth list

gcloud --version

gcloud projects create project01-9999999 \
--name="project01"

gcloud config list
gcloud config set project project01-9999999
gcloud config set compute/region asia-northeast1 --quiet
gcloud config set compute/zone asia-northeast1-a --quiet

gcloud beta billing accounts list
gcloud beta billing projects link project01-9999999 --billing-account=111111-111111-111111

gcloud services enable compute.googleapis.com --project project01-9999999

gcloud components update

-- 2. BigQuery API有効化

gcloud services list --enabled

gcloud services enable bigquery.googleapis.com \
--project project01-9999999

-- 3. BigQueryデータセットを作成する

bq mk ds01

bq ls

-- 4. テスト用テーブル、データの作成(コンソールから実行)

drop table ds01.tab1;

create table ds01.tab1
(
col1 INT NOT NULL
, col2 NUMERIC NOT NULL
, col3 DATETIME NOT NULL
, col4 TIMESTAMP
, col5 STRING
, col6 BYTES
);

insert into ds01.tab1 values(1,2,current_datetime(),current_timestamp(),'a',b'a' );

select * from ds01.tab1;

select * from ds01.INFORMATION_SCHEMA.TABLES;

※INFORMATION_SCHEMA.TABLESは大文字で指定が必要

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

gcloud iam service-accounts create sa99999999 \
--description="sa99999999" \
--display-name="sa99999999"

gcloud iam service-accounts list

gcloud projects add-iam-policy-binding project01-9999999 \
--member="serviceAccount:sa99999999@project01-9999999.iam.gserviceaccount.com" \
--role="roles/bigquery.admin"

gcloud projects get-iam-policy project01-9999999

gcloud iam service-accounts keys create ~/key01.json \
--iam-account=sa99999999@project01-9999999.iam.gserviceaccount.com

cat ~/key01.json

gcloud iam service-accounts keys list \
--iam-account=sa99999999@project01-9999999.iam.gserviceaccount.com

key01.jsonをDenodo Express端末のCドライブ直下へコピー

-------- Denodo側作業

-- 6. BigQuery用JDBCドライバーのインポート

Design Studio で 'File -> Extension management'

Librariesタブを選択
下記URLからダウンロードした「SimbaBigQueryJDBC42-1.3.2.1003.zip」の全jarファイルをImport
https://cloud.google.com/bigquery/providers/simba-drivers

Resource type: jdbc
Version: bigquery

-- 7. データベース作成
DROP DATABASE db01 cascade;

CREATE OR REPLACE DATABASE db01 'description of db01'
VCS OFF
CHARSET UNICODE
AUTHENTICATION LOCAL
ODBC AUTHENTICATION NORMAL
CHECK_VIEW_RESTRICTIONS ALWAYS
;

-- 8. フォルダ作成
CONNECT DATABASE db01;

CREATE FOLDER '/01ds' DESCRIPTION 'description of folder01';
CREATE FOLDER '/02bv' DESCRIPTION 'description of folder02';
CREATE FOLDER '/03iv' DESCRIPTION 'description of folder03';

-- 9. データソース作成

Name -> ds01

Database adapter -> Google BigQuery

Database URL -> jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=project01-9999999;OAuthType=0;OAuthServiceAcctEmail=sa99999999@project01-9999999.iam.gserviceaccount.com;OAuthPvtKeyPath=c:\key01.json;Timeout=45

Transaction isolation -> Database Default

Authentication -> Service-based OAuth Authentication

Project name -> project01-9999999

Account email -> sa99999999@project01-9999999.iam.gserviceaccount.com

Private key -> 「c:\key01.json」を選択

CREATE DATASOURCE JDBC ds01
FOLDER = '/01ds'
DRIVERCLASSNAME = 'com.simba.googlebigquery.jdbc.Driver'
DATABASEURI = 'jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=project01-9999999;OAuthType=0;OAuthServiceAcctEmail=sa99999999@project01-9999999.iam.gserviceaccount.com;OAuthPvtKeyPath=c:\key01.json;Timeout=45'
GCP (
OAUTH_TYPE = '0'
PRIVATE_KEY = 'xxxxx' ENCRYPTED
PROJECT_ID = 'project01-9999999'
SERVICE_ACCOUNT_EMAIL = 'sa99999999@project01-9999999.iam.gserviceaccount.com'
)
CLASSPATH = 'bigquery'
DATABASENAME = 'bigquery'
DATABASEVERSION = '2_0'
FETCHSIZE = 1000
VALIDATIONQUERY = 'Select 1'
INITIALSIZE = 4
MAXIDLE = -1
MINIDLE = 0
MAXACTIVE = 20
EXHAUSTEDACTION = 1
TESTONBORROW = true
TESTONRETURN = false
TESTWHILEIDLE = false
TIMEBETWEENEVICTION = -1
NUMTESTPEREVICTION = 3
MINEVICTABLETIME = 1800000
POOLPREPAREDSTATEMENTS = false
MAXOPENPREPAREDSTATEMENTS = -1
DATA_LOAD_CONFIGURATION (
BATCHINSERTSIZE = 200
);

-- 10. ラッパー作成

CREATE WRAPPER JDBC b_tab1
FOLDER = '/02bv'
DATASOURCENAME=ds01
SCHEMANAME='ds01'
RELATIONNAME='tab1'
OUTPUTSCHEMA (
col1 = 'col1' :'java.lang.Long' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='19', sourcetypeid='-5', sourcetypename='INT64') NOT NULL SORTABLE,
col2 = 'col2' :'java.math.BigDecimal' (OPT) (sourcetypedecimals='9', sourcetyperadix='10', sourcetypesize='38', sourcetypeid='2', sourcetypename='NUMERIC') NOT NULL SORTABLE,
col3 = 'col3' :'java.time.LocalDateTime' (OPT) (sourcetypedecimals='6', sourcetypesize='26', sourcetypeid='93', sourcetypename='DATETIME') NOT NULL SORTABLE,
col4 = 'col4' :'java.time.OffsetDateTime' (OPT) (sourcetypedecimals='6', sourcetypesize='26', sourcetypeid='93', sourcetypename='TIMESTAMP') NOT NULL SORTABLE,
col5 = 'col5' :'java.lang.String' (OPT) (sourcetypesize='65535', sourcetypeid='12', sourcetypename='STRING') NOT NULL SORTABLE,
col6 = 'col6' :'java.sql.Blob' (OPT) (sourcetypesize='65535', sourcetypeid='-3', sourcetypename='BYTES') NOT NULL NOT SORTABLE
);

-- 11. 基本ビュー作成

CREATE TABLE b_tab1 I18N jp (
col1:long (notnull, sourcetypeid = '-5', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '19'),
col2:decimal (notnull, sourcetypeid = '2', sourcetyperadix = '10', sourcetypedecimals = '9', sourcetypesize = '38'),
col3:timestamp (notnull, sourcetypeid = '93', sourcetypedecimals = '6', sourcetypesize = '26'),
col4:timestamptz (notnull, sourcetypeid = '93', sourcetypedecimals = '6', sourcetypesize = '26'),
col5:text (notnull, sourcetypeid = '12', sourcetypesize = '65535'),
col6:blob (notnull, sourcetypeid = '-3', sourcetypesize = '65535')
)
FOLDER = '/02bv'
CACHE OFF
TIMETOLIVEINCACHE DEFAULT
ADD SEARCHMETHOD b_tab1(
I18N jp
CONSTRAINTS (
ADD col1 (any) OPT ANY
ADD col2 (any) OPT ANY
ADD col3 (any) OPT ANY
ADD col4 (any) OPT ANY
ADD col5 (any) OPT ANY
ADD col6 NOS ZERO ()
)
OUTPUTLIST (col1, col2, col3, col4, col5, col6
)
WRAPPER (jdbc b_tab1)
);

-- 12. インターフェイスビュー作成

CREATE INTERFACE VIEW i_tab1 (
col1:long (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '19', sourcetypeid = '-5'),
col2:decimal (sourcetypedecimals = '9', sourcetyperadix = '10', sourcetypesize = '38', sourcetypeid = '2'),
col3:timestamp (sourcetypedecimals = '6', sourcetypesize = '26', sourcetypeid = '93'),
col4:timestamptz (sourcetypedecimals = '6', sourcetypesize = '26', sourcetypeid = '93'),
col5:text (sourcetypesize = '65535', sourcetypeid = '12'),
col6:blob (sourcetypesize = '65535', sourcetypeid = '-3', contenttype = 'autodetect')
)
SET IMPLEMENTATION b_tab1
FOLDER = '/03iv';

-- 13. 動作確認

SELECT * FROM db01.b_tab1 CONTEXT('cache_wait_for_load' = 'true') TRACE;

SELECT * FROM db01.i_tab1 CONTEXT('cache_wait_for_load' = 'true') TRACE;

-------- BigQuery側作業

-- 14. クリーンアップ

gcloud projects list
gcloud projects delete project01-9999999 \
--quiet