{Denodo}リモートテーブル - HTN20190109の日記

https://community.denodo.com/docs/html/browse/8.0/jp/vdp/vql/remote_tables/remote_tables

https://community.denodo.com/docs/html/browse/8.0/jp/vdp/administration/bulk_data_load/bigquery

クエリの結果を使用して外部データベースにテーブルを作成しデータを挿入する機能

※VQL シェルから CREATE REMOTE TABLE を実行する場合は、[Retrieve all rows] チェックボックスをチェックします。
チェックしないと、VQL シェルの [Display rows] ボックスで設定した数の行のみが実行エンジンによって挿入されます。

※データ一括読み込みの設定も必要。設定しないとリモートテーブル作成時下記のようなエラーとなる

Operation not allowed. Please check the configuration of the data source 'ds02'.
Data sources based on HDFS (Hive, Impala, Presto, Spark) require Bulk Data Load enabled.

前提:
Denodo Express インストール、起動済
Oracle19cインストール、起動済
BigQuery用JDBCドライバーのインポート済

Oracle19c -> ds01,tab1,b_tab1,i_tab1
BigQuery -> ds02,tab1

Oracleのデータをクエリして、リモートテーブル機能により
BigQueryのテーブルdataset01.tab1を作成する

-------- Oracle側作業

-- 1. テスト用テーブル、データの作成

drop table tab1 purge;
create table tab1(
col1 number not null
, col2 number(33,30) not null
, col3 timestamp not null
, col4 char(10)
, col5 varchar2(10)
, col6 nchar(10)
, col7 nvarchar2(10)
, col8 blob
, col9 clob
);
alter table tab1 add constraint tab1pk primary key(col1);
create index ind11 on tab1(col2);
create index ind12 on tab1(col3);

desc tab1

insert into tab1 values(1,123.012345,systimestamp,'a','b','c','d','e','f');
commit;

col col2 for 999.999999999999999999999999999999
select * from tab1;

-------- GCP側作業

-- 2. 前作業

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

-- 3. BigQuery API有効化

gcloud services list --enabled

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

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

bq mk dataset01

bq ls

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

※データ一括読み込みの場合、Google Cloud Storageへの権限も必要

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

gcloud iam service-accounts list

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

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

gcloud projects get-iam-policy project01-9999999

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

cat ~/key01.json

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

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

-- 6. バケットの作成

gcloud storage buckets create gs://bucket123 \
--default-storage-class=Standard \
--no-enable-autoclass \
--location=asia-northeast1 \
--public-access-prevention \
--uniform-bucket-level-access

gcloud storage ls

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

-- 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. データソース作成(Oracle)

CREATE OR REPLACE DATASOURCE JDBC ds01
FOLDER = '/01ds'
DRIVERCLASSNAME = 'oracle.jdbc.OracleDriver'
DATABASEURI = 'jdbc:oracle:thin:@192.168.137.65:1521/pdb1.example.com'
USERNAME = 'test'
USERPASSWORD = 'xxxxx' ENCRYPTED
CLASSPATH = 'oracle-21c'
DATABASENAME = 'oracle'
DATABASEVERSION = '19c'
FETCHSIZE = 1000
VALIDATIONQUERY = 'SELECT COUNT(*) FROM SYS.DUAL'
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
PROPERTIES ('remarksReporting'='false', 'includeSynonyms'='false')
KERBEROSPROPERTIES ('oracle.net.authentication_services'='KERBEROS5')
DATA_LOAD_CONFIGURATION (
BATCHINSERTSIZE = 200
);

-- 10. ラッパー作成(Oracle)

CREATE OR REPLACE WRAPPER JDBC "b_TAB1"
FOLDER = '/02bv'
DATASOURCENAME=ds01
SCHEMANAME='TEST'
RELATIONNAME='TAB1'
OUTPUTSCHEMA (
"COL1" = 'COL1' :'java.math.BigDecimal' (OPT) (sourcetyperadix='10', sourcetypeid='2', sourcetypename='NUMBER') NOT NULL SORTABLE,
"COL2" = 'COL2' :'java.math.BigDecimal' (OPT) (sourcetypedecimals='30', sourcetyperadix='10', sourcetypesize='33', sourcetypeid='2', sourcetypename='NUMBER') NOT NULL SORTABLE,
"COL3" = 'COL3' :'java.time.LocalDateTime' (OPT) (sourcetypedecimals='6', sourcetyperadix='10', sourcetypesize='11', sourcetypeid='93', sourcetypename='TIMESTAMP(6)') NOT NULL SORTABLE,
"COL4" = 'COL4' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='10', sourcetypeid='1', sourcetypename='CHAR') SORTABLE,
"COL5" = 'COL5' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='10', sourcetypeid='12', sourcetypename='VARCHAR2') SORTABLE,
"COL6" = 'COL6' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='10', sourcetypeid='-15', sourcetypename='NCHAR') SORTABLE,
"COL7" = 'COL7' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='10', sourcetypeid='-9', sourcetypename='NVARCHAR2') SORTABLE,
"COL8" = 'COL8' :'java.sql.Blob' (OPT) (sourcetyperadix='10', sourcetypeid='2004', sourcetypename='BLOB') NOT SORTABLE,
"COL9" = 'COL9' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypeid='2005', sourcetypename='CLOB') NOT SORTABLE
)
CONSTRAINT 'TAB1PK' PRIMARY KEY ( 'COL1' )
INDEX 'TAB1PK' CLUSTER UNIQUE PRIMARY ( 'COL1' )
INDEX 'IND11' CLUSTER ( 'COL2' )
INDEX 'IND12' CLUSTER ( 'COL3' );

-- 11. 基本ビュー作成(Oracle)

CREATE OR REPLACE TABLE "b_TAB1" I18N jp (
"COL1":decimal (notnull, sourcetypeid = '2', sourcetyperadix = '10'),
"COL2":decimal (notnull, sourcetypeid = '2', sourcetyperadix = '10', sourcetypedecimals = '30', sourcetypesize = '33'),
"COL3":timestamp (notnull, sourcetypeid = '93', sourcetyperadix = '10', sourcetypedecimals = '6', sourcetypesize = '11'),
"COL4":text (sourcetypeid = '1', sourcetyperadix = '10', sourcetypesize = '10'),
"COL5":text (sourcetypeid = '12', sourcetyperadix = '10', sourcetypesize = '10'),
"COL6":text (sourcetypeid = '-15', sourcetyperadix = '10', sourcetypesize = '10'),
"COL7":text (sourcetypeid = '-9', sourcetyperadix = '10', sourcetypesize = '10'),
"COL8":blob (sourcetypeid = '2004', sourcetyperadix = '10'),
"COL9":text (sourcetypeid = '2005', sourcetyperadix = '10')
)
FOLDER = '/02bv'
CONSTRAINT 'TAB1PK' PRIMARY KEY ( 'COL1' )
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" (any) OPT ANY
ADD "COL7" (any) OPT ANY
ADD "COL9" (any) OPT ANY
ADD "COL8" NOS ZERO ()
)
OUTPUTLIST ("COL1", "COL2", "COL3", "COL4", "COL5", "COL6", "COL7", "COL8", "COL9"
)
WRAPPER (jdbc "b_TAB1")
);

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

CREATE OR REPLACE INTERFACE VIEW "i_TAB1" (
"COL1":decimal (sourcetyperadix = '10', sourcetypeid = '2'),
"COL2":decimal (sourcetypedecimals = '30', sourcetyperadix = '10', sourcetypesize = '33', sourcetypeid = '2'),
"COL3":timestamp (sourcetypedecimals = '6', sourcetyperadix = '10', sourcetypesize = '11', sourcetypeid = '93'),
"COL4":text (sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '1'),
"COL5":text (sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '12'),
"COL6":text (sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '-15'),
"COL7":text (sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '-9'),
"COL8":blob (sourcetyperadix = '10', sourcetypeid = '2004', contenttype = 'autodetect'),
"COL9":text (sourcetyperadix = '10', sourcetypeid = '2005')
)
SET IMPLEMENTATION "b_TAB1"
FOLDER = '/03iv';

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

-- 13. データソース作成(BigQuery)

Name -> ds02

Database adapter -> Google BigQuery

Database URL -> jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=project01-9999999;OAuthType=0;OAuthServiceAcctEmail=sa123@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 -> sa123@project01-9999999.iam.gserviceaccount.com

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

-- 14. データ一括読み込みの設定(BigQuery)

データソースのRerad Writeタブ

Use bulk data load APIs -> On

GS URI -> gs://bucket123/denodo

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

CREATE OR REPLACE DATASOURCE JDBC ds02
FOLDER = '/01ds'
DRIVERCLASSNAME = 'com.simba.googlebigquery.jdbc.Driver'
DATABASEURI = 'jdbc:bigquery://https://www.googleapis.com/bigquery/v2:443;ProjectId=project01-9999999;OAuthType=0;OAuthServiceAcctEmail=sa123@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 = 'sa123@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 = 10000
BULK_LOAD_CONFIGURATION (
GCP (
URI = 'gs://bucket123/denodo'
PRIVATE_KEY = 'xxxxx' ENCRYPTED
)
)
USEEXTERNALTABLES (
ONMOVEREAD = false,
ONMOVEWRITE = true
)
);

-- 15. リモートテーブル作成

CREATE OR REPLACE REMOTE TABLE tab1
INTO ds02
SCHEMA = 'dataset01'
AS
SELECT * FROM db01."b_TAB1";

-------- GCP側作業

-- 16. BigQueryにテーブルが作成されていることを確認

select * from dataset01.INFORMATION_SCHEMA.TABLES
order by table_name
;

SELECT *
FROM dataset01.INFORMATION_SCHEMA.COLUMNS
order by table_name,column_name
;

select * from dataset01.tab1;

-- 17. GCSにファイルが作成されていることを確認

gcloud storage ls --long gs://bucket123/*

-- 18. クリーンアップ

gcloud storage rm gs://bucket123 --recursive
gcloud storage ls

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