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

https://community.denodo.com/docs/html/browse/8.0/jp/vdp/administration/creating_data_sources_and_base_views/excel_sources/excel_sources

前提: 
Denodo Express インストール、起動済み
Cドライブ直下にテスト用エクセルファイル配置済み

1    A    XXX    あ
2    B    YYY    い

-- 1. 事前準備

-- 1.1 データベース作成
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
;

-- 1.2 フォルダ作成
CONNECT DATABASE db01;

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

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

CREATE OR REPLACE DATASOURCE CUSTOM ds01
    FOLDER = '/01ds'
    CLASSNAME='com.denodo.vdb.contrib.wrapper.xls.ExcelWrapper';

-- 3. ラッパー作成

CREATE OR REPLACE WRAPPER CUSTOM ds01
    FOLDER = '/01ds'
    DATASOURCENAME=ds01
    PARAMETERS (
      'Type of file' = 'Excel 2007 or later (*.xlsx)',
      'File location' = ROUTE LOCAL 'LocalConnection' 'C:/a.xlsx',
      'Extract data from all sheets' = false,
      'Extract sheet name as a new column' = false,
      'Has headers' = false,
      'Stream tuples' = false
    )
    OUTPUTSCHEMA (
        col1 = 'Column_0' : 'java.lang.Double' (OPT) (sourcetypeid='8')  SORTABLE,
        col2 = 'Column_1' : 'java.lang.String' (OPT) (sourcetypeid='12')  SORTABLE,
        col3 = 'Column_2' : 'java.lang.String' (OPT) (sourcetypeid='12')  SORTABLE,
        col4 = 'Column_3' : 'java.lang.String' (OPT) (sourcetypeid='12')  SORTABLE
    );

-- 4. 基本ビュー作成

CREATE OR REPLACE TABLE b_tab1 I18N jp (
        col1:double (sourcetypeid = '8'),
        col2:text (sourcetypeid = '12'),
        col3:text (sourcetypeid = '12'),
        col4:text (sourcetypeid = '12')
    )
    FOLDER = '/02bv'
    CACHE OFF
    TIMETOLIVEINCACHE DEFAULT
    ADD SEARCHMETHOD ds01(
        I18N jp
        CONSTRAINTS (
             ADD col1 (any) OPT ANY
             ADD col2 (any) OPT ANY
             ADD col3 (any) OPT ANY
             ADD col4 (any) OPT ANY
        )
        OUTPUTLIST (col1, col2, col3, col4
        )
        WRAPPER (custom ds01)
    );
    

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

CREATE OR REPLACE INTERFACE VIEW i_tab1 (
        col1:double (sourcetypeid = '8'),
        col2:text (sourcetypeid = '12'),
        col3:text (sourcetypeid = '12'),
        col4:text (sourcetypeid = '12')
    )
    SET IMPLEMENTATION b_tab1
    FOLDER = '/03iv';

-- 6. 動作確認

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

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