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

Denodo

 


https://community.denodo.com/kb/en/view/document/How%20to%20connect%20to%20Amazon%20Athena%20from%20Denodo?category=Data%20Sources

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

 

-------- AWS側作業

-- 1. S3 バケットを作成する

aws s3 ls

aws s3 mb s3://bucket123


-- 2. S3にテストデータアップロード

vim tab1_0001.txt
1,10,AAA
2,20,BBB
3,30,CCC


aws s3 cp tab1_0001.txt s3://bucket123/tab1/tab1_0001.txt


aws s3 ls s3://bucket123 --recursive

 


-- 3. クエリ結果の場所の設定


aws athena list-work-groups

aws athena get-work-group \
--work-group primary


aws athena update-work-group \
--work-group primary \
--configuration-updates '{
  "EnforceWorkGroupConfiguration": false,
  "ResultConfigurationUpdates": {
    "OutputLocation": "s3://bucket123/result"
  },
  "PublishCloudWatchMetricsEnabled": false,
  "RequesterPaysEnabled": false,
  "EngineVersion": {
    "SelectedEngineVersion": "AUTO",
    "EffectiveEngineVersion": "Athena engine version 2"
  }
}'

-- 4. データベースを作成する

aws glue create-database \
--database-input '{"Name": "test"}'

aws glue get-databases

aws glue get-database \
--name test


-- 5. テーブルを作成する


create external table tab1 (
  col1 int
, col2 int
, col3 string
)
row format delimited
  fields terminated by ','
  escaped by '\\'
  lines terminated by '\n'
location 's3://bucket123/tab1/';

 


select col1,col2,col3 from test.tab1 
order by col1;


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

 

-- 6. データベース作成
DROP DATABASE db04 cascade;

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

-- 7. フォルダ作成
CONNECT DATABASE db04;

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

 

 

 

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


CREATE OR REPLACE DATASOURCE JDBC athena
    FOLDER = '/01ds'
    DRIVERCLASSNAME = 'com.simba.athena.jdbc.Driver'
    DATABASEURI = 'jdbc:awsathena://AwsRegion=ap-northeast-1;S3OutputLocation=s3://bucket123/output/;Schema=test;'
    USERNAME = 'xxxxxxxxxxxxxxxxxxxx'
    USERPASSWORD = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' ENCRYPTED
    CLASSPATH = 'amazon-athena-1.0'
    DATABASENAME = 'athena'
    DATABASEVERSION = '1_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
    );

 

-- 9. ラッパー作成


CREATE OR REPLACE WRAPPER JDBC b_tab1
    FOLDER = '/02bv'
    DATASOURCENAME=athena
    SCHEMANAME='test' 
    RELATIONNAME='tab1' 
    OUTPUTSCHEMA (
        col1 = 'col1' :'java.lang.Integer' (OPT) (sourcetyperadix='10', sourcetypesize='10', sourcetypeid='4', sourcetypename='integer')  SORTABLE,
        col2 = 'col2' :'java.lang.Integer' (OPT) (sourcetyperadix='10', sourcetypesize='10', sourcetypeid='4', sourcetypename='integer')  SORTABLE,
        col3 = 'col3' :'java.lang.String' (OPT) (sourcetypesize='255', sourcetypeid='12', sourcetypename='string')  SORTABLE
    );

 


-- 10. 基本ビュー作成

CREATE OR REPLACE TABLE b_tab1 I18N jp (
        col1:int (sourcetypeid = '4', sourcetyperadix = '10', sourcetypesize = '10'),
        col2:int (sourcetypeid = '4', sourcetyperadix = '10', sourcetypesize = '10'),
        col3:text (sourcetypeid = '12', sourcetypesize = '255')
    )
    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
        )
        OUTPUTLIST (col1, col2, col3
        )
        WRAPPER (jdbc b_tab1)
    );

 


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

CREATE OR REPLACE INTERFACE VIEW i_tab1 (
        col1:int (sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '4'),
        col2:int (sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '4'),
        col3:text (sourcetypesize = '255', sourcetypeid = '12')
    )
    SET IMPLEMENTATION b_tab1
    FOLDER = '/03iv';

 

 

-- 12. 動作確認

SELECT * FROM GET_ELEMENTS()
where input_database_name = 'db04'
;


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


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

 


-------- AWS側作業

-- 13. クリーンアップ


-- データベースの削除

aws glue get-databases


aws glue delete-database \
--name test

aws glue delete-database \
--name default

 


-- バケットの削除

aws s3 ls

aws s3 rb s3://bucket123 --force