前提:
Denodo Express インストール、起動済み
-- 1. コマンド等のインストール
-- 1.1 aws cli version 2 インストール
curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
unzip awscliv2.zip
sudo ./aws/install
aws --version
-- 1.2 jqインストール
sudo yum -y install jq
-- 1.3 psqlインストール
amazon-linux-extras list | grep postgresql
sudo amazon-linux-extras install postgresql14
psql --version
-- 2. クラスターの作成
aws redshift create-cluster \
--db-name test \
--cluster-identifier redshift01 \
--cluster-type single-node \
--node-type dc2.large \
--master-username test \
--master-user-password 'password' \
--no-allow-version-upgrade \
--publicly-accessible \
--automated-snapshot-retention-period 0
aws redshift describe-clusters
aws redshift describe-clusters --cluster-identifier redshift01
-- 3. 動作確認
PGPASSWORD='password' psql -h redshift01.xxxxxxxxxxxx.ap-northeast-1.redshift.amazonaws.com -p 5439 -d test -U test
create database denodo;
\l
\c denodo
drop table tab1;
create table tab1(col1 int, col2 int);
insert into tab1 values(1,2);
insert into tab1 values(3,4);
select * from tab1;
PGPASSWORD='password' psql -h redshift01.xxxxxxxxxxxx.ap-northeast-1.redshift.amazonaws.com -p 5439 -d denodo -U test -c "select * from tab1;"
-- 4. データベースとフォルダの作成
-- 4.1 データベース作成
DROP DATABASE db06 cascade;
CREATE OR REPLACE DATABASE db06 'description of db06'
VCS OFF
CHARSET UNICODE
AUTHENTICATION LOCAL
ODBC AUTHENTICATION NORMAL
CHECK_VIEW_RESTRICTIONS ALWAYS
;
-- 4.2 フォルダ作成
CONNECT DATABASE db06;
CREATE FOLDER '/01ds' DESCRIPTION 'description of folder01';
CREATE FOLDER '/02bv' DESCRIPTION 'description of folder02';
CREATE FOLDER '/03iv' DESCRIPTION 'description of folder03';
-- 5. データソース作成
CREATE OR REPLACE DATASOURCE JDBC ds01
FOLDER = '/01ds'
DRIVERCLASSNAME = 'com.amazon.redshift.jdbc.Driver'
DATABASEURI = 'jdbc:redshift://redshift01.xxxxxxxxxxxx.ap-northeast-1.redshift.amazonaws.com:5439/denodo'
USERNAME = 'test'
USERPASSWORD = 'XXXXX' ENCRYPTED
CLASSPATH = 'amazon-redshift-2.x'
DATABASENAME = 'redshift'
DATABASEVERSION = '2015'
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
PROPERTIES ('enableFetchRingBuffer'='false')
DATA_LOAD_CONFIGURATION (
BATCHINSERTSIZE = 50000
);
-- 6. ラッパー作成
CREATE OR REPLACE WRAPPER JDBC tab1
FOLDER = '/02bv'
DATASOURCENAME=ds01
SCHEMANAME='public' ESCAPE
RELATIONNAME='tab1'
OUTPUTSCHEMA (
col1 = 'col1' :'java.lang.Integer' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='10', sourcetypeid='4', sourcetypename='int4') SORTABLE,
col2 = 'col2' :'java.lang.Integer' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='10', sourcetypeid='4', sourcetypename='int4') SORTABLE
);
-- 7. 基本ビュー作成
CREATE OR REPLACE TABLE b_tab1 I18N jp (
col1:int (sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '10'),
col2:int (sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '10')
)
FOLDER = '/02bv'
CACHE OFF
TIMETOLIVEINCACHE DEFAULT
ADD SEARCHMETHOD tab1(
I18N jp
CONSTRAINTS (
ADD col1 (any) OPT ANY
ADD col2 (any) OPT ANY
)
OUTPUTLIST (col1, col2
)
WRAPPER (jdbc tab1)
);
-- 8. インターフェイスビュー作成
CREATE OR REPLACE INTERFACE VIEW i_tab1 (
col1:int (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '4'),
col2:int (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '4')
)
SET IMPLEMENTATION b_tab1
FOLDER = '/03iv';
-- 9. 動作確認
SELECT * FROM db06.b_tab1 CONTEXT('cache_wait_for_load' = 'true') TRACE
SELECT * FROM db06.i_tab1 CONTEXT('cache_wait_for_load' = 'true') TRACE
-- 10. クリーンアップ
-- クラスターの削除
aws redshift delete-cluster \
--cluster-identifier redshift01 \
--skip-final-cluster-snapshot
