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

Denodo

 

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

https://note.com/bunsekiya_tech/n/nd1b8c2e31a1f


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

 

-- 1. Snowflakeのセットアップ


brew install --cask snowflake-snowsql

vi .bash_profile
alias snowsql=/Applications/SnowSQL.app/Contents/MacOS/snowsql

. .bash_profile

vi a.csv
111,AAAA
222,BBBB


snowsql -a xxxxxxx.ap-northeast-1.aws -u sf123

 

create database test;

use database test;

create stage stage01;

create or replace TABLE tab1 (
  col1 INT,
  col2 VARCHAR(30)
);

put file://a.csv @stage01;

list @stage01;

create warehouse warehouse01;

use warehouse warehouse01;

copy into tab1 from @stage01;

select * from tab1;


!quit;

 

 

 

-- 2. データベースとフォルダの作成

-- 2.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
;

-- 2.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';

 

 

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

CREATE OR REPLACE DATASOURCE JDBC ds01
    FOLDER = '/01ds'
    DRIVERCLASSNAME = 'com.snowflake.client.jdbc.SnowflakeDriver'
    DATABASEURI = 'jdbc:snowflake://xxxxxxx.ap-northeast-1.aws.snowflakecomputing.com/?db=test&schema=public&warehouse=warehouse01'
    USERNAME = 'sf123'
    USERPASSWORD = 'XXXXX' ENCRYPTED
    CLASSPATH = 'snowflake-1.x'
    DATABASENAME = 'snowflake'
    DATABASEVERSION = '1_x'
    ISOLATIONLEVEL = TRANSACTION_READ_COMMITTED
    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 ('application'='Denodo')
    DATA_LOAD_CONFIGURATION (
        BATCHINSERTSIZE = 500000
        USEEXTERNALTABLES ( 
            ONMOVEREAD = false,
            ONMOVEWRITE = true
        )
    );

 

 

-- 4. ラッパー作成


CREATE OR REPLACE WRAPPER JDBC "TAB1"
    FOLDER = '/02bv'
    DATASOURCENAME=ds01
    CATALOGNAME='TEST' 
    SCHEMANAME='PUBLIC' ESCAPE
    RELATIONNAME='TAB1' 
    OUTPUTSCHEMA (
        "COL1" = 'COL1' :'java.math.BigDecimal' (OPT) (sourcetypedecimals='0', sourcetypesize='38', description='', sourcetypeid='-5', sourcetypename='NUMBER')  SORTABLE,
        "COL2" = 'COL2' :'java.lang.String' (OPT) (sourcetypedecimals='0', sourcetypesize='30', description='', sourcetypeid='12', sourcetypename='VARCHAR')  SORTABLE
    );


-- 5. 基本ビュー作成

CREATE OR REPLACE TABLE b_tab1 I18N jp (
        "COL1":decimal,
        "COL2":text (sourcetypeid = '12', sourcetypedecimals = '0', sourcetypesize = '30', description = '')
    )
    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")
    );


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

CREATE OR REPLACE INTERFACE VIEW i_tab1 (
        "COL1":decimal,
        "COL2":text (sourcetypedecimals = '0', sourcetypesize = '30', sourcetypeid = '12')
    )
    SET IMPLEMENTATION b_tab1
    FOLDER = '/03iv';

 


-- 7. 動作確認

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

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