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

Denodo

 


https://www.cdata.com/kb/tech/redis-jdbc-denodo-vdp.rst
https://redis.io/docs/getting-started/installation/install-redis-from-source/
https://qiita.com/morrr/items/26dbdd87df03315fa680
https://arkgame.com/2022/05/01/post-307658/

CData のトライアル版JDBCドライバーを使用

 

前提: 
Denodo Express インストール、起動済み
CData JDBC Driver for Redisインストール済み

 

-- 1. 事前準備(Redis)

yum update -y
yum -y install gcc*

wget https://download.redis.io/redis-stable.tar.gz
tar -xzvf redis-stable.tar.gz
cd redis-stable
make
make install

vim redis.conf

bind 127.0.0.1 -::1
↓
bind 0.0.0.0

protected-mode yes
↓
protected-mode no

# requirepass foobared
↓
requirepass password

 

cd src
./redis-server --version
./redis-server ../redis.conf &

./redis-cli -h 192.168.137.127 -p 6379

auth password

set key01 val01
set key02 val02

get key01
get key02

 


-- 2. 事前準備(Denodo)

-- 2.1 データベース作成
DROP DATABASE db02 cascade;

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

-- 2.2 フォルダ作成
CONNECT DATABASE db02;

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


-- 2.3 JAR Fileとライセンスファイルのコピー
フォルダ「cdata-redis」を作成し、インストールしたJARファイルとライセンスファイルをコピーする

C:\Program Files\CData\CData JDBC Driver for Redis 2022\lib
cdata.jdbc.redis.jar
cdata.jdbc.redis.lic
↓
C:\Denodo\DenodoPlatform8.0\lib\extensions\jdbc-drivers-external\cdata-redis
cdata.jdbc.redis.jar
cdata.jdbc.redis.lic


VDP再起動

 

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


CREATE OR REPLACE DATASOURCE JDBC redis
    FOLDER = '/01ds'
    DRIVERCLASSNAME = 'cdata.jdbc.redis.RedisDriver'
    DATABASEURI = 'jdbc:redis:Server=192.168.137.127;Port=6379;Password=password;'
    USERNAME = ''
    USERPASSWORD = ''
    CLASSPATH = 'cdata-redis'
    DATABASENAME = 'Generic'
    DATABASEVERSION = ''
    FETCHSIZE = 1000
    VALIDATIONQUERY = ''
    INITIALSIZE = 4
    MAXIDLE = 0
    MINIDLE = 0
    MAXACTIVE = 20
    EXHAUSTEDACTION = 0
    TESTONBORROW = true
    TESTONRETURN = false
    TESTWHILEIDLE = false
    TIMEBETWEENEVICTION = 0
    NUMTESTPEREVICTION = 0
    MINEVICTABLETIME = 0
    POOLPREPAREDSTATEMENTS = false
    MAXOPENPREPAREDSTATEMENTS = 0
    DATA_LOAD_CONFIGURATION (
        BATCHINSERTSIZE = 200
    );

-- 4. ラッパー作成


CREATE OR REPLACE WRAPPER JDBC "b_Keys"
    FOLDER = '/02bv'
    DATASOURCENAME=redis
    SCHEMANAME='Redis' 
    RELATIONNAME='Keys' 
    OUTPUTSCHEMA (
        "RedisKey" = 'RedisKey' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='255', description='', sourcetypeid='12', sourcetypename='VARCHAR')  NOT NULL SORTABLE,
        "ValueIndex" = 'ValueIndex' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='2000', description='', sourcetypeid='12', sourcetypename='VARCHAR')  SORTABLE,
        "Value" = 'Value' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='2000', description='', sourcetypeid='12', sourcetypename='VARCHAR')  ESCAPE NOT NULL SORTABLE,
        "RedisType" = 'RedisType' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='2000', description='', sourcetypeid='12', sourcetypename='VARCHAR')  NOT NULL SORTABLE,
        "ValueScore" = 'ValueScore' :'java.lang.Double' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='15', description='', sourcetypeid='8', sourcetypename='FLOAT')  SORTABLE
    )
    CONSTRAINT 'CData_Redis_Keys_PRIMARYKEY' PRIMARY KEY ( 'RedisKey' )
    INDEX 'CDATA_REDIS_KEYS_PRIMARYKEY_INDEX' OTHER UNIQUE PRIMARY ( 'RedisKey' );

 

-- 5. 基本ビュー作成

CREATE OR REPLACE TABLE "b_Keys" I18N jp (
        "RedisKey":text (notnull, sourcetypeid = '12', sourcetyperadix = '10', sourcetypesize = '255', description = ''),
        "ValueIndex":text (sourcetypeid = '12', sourcetyperadix = '10', sourcetypesize = '2000', description = ''),
        "Value":text (notnull, sourcetypeid = '12', sourcetyperadix = '10', sourcetypesize = '2000', description = ''),
        "RedisType":text (notnull, sourcetypeid = '12', sourcetyperadix = '10', sourcetypesize = '2000', description = ''),
        "ValueScore":double (sourcetypeid = '8', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '15', description = '')
    )
    FOLDER = '/02bv'
    CONSTRAINT 'CData_Redis_Keys_PRIMARYKEY' PRIMARY KEY ( 'RedisKey' )
    CACHE OFF
    TIMETOLIVEINCACHE DEFAULT
    ADD SEARCHMETHOD "b_Keys"(
        I18N jp
        CONSTRAINTS (
             ADD "RedisKey" (any) OPT ANY
             ADD "ValueIndex" (any) OPT ANY
             ADD "Value" (any) OPT ANY
             ADD "RedisType" (any) OPT ANY
             ADD "ValueScore" (any) OPT ANY
        )
        OUTPUTLIST ("RedisKey", "RedisType", "Value", "ValueIndex", "ValueScore"
        )
        WRAPPER (jdbc "b_Keys")
    );


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

CREATE OR REPLACE INTERFACE VIEW "i_Keys" (
        "RedisKey":text (sourcetyperadix = '10', sourcetypesize = '255', sourcetypeid = '12'),
        "ValueIndex":text (sourcetyperadix = '10', sourcetypesize = '2000', sourcetypeid = '12'),
        "Value":text (sourcetyperadix = '10', sourcetypesize = '2000', sourcetypeid = '12'),
        "RedisType":text (sourcetyperadix = '10', sourcetypesize = '2000', sourcetypeid = '12'),
        "ValueScore":double (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '15', sourcetypeid = '8')
    )
    SET IMPLEMENTATION "b_Keys"
    FOLDER = '/03iv';

 

-- 7. 動作確認

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

SELECT * FROM db02."b_Keys" CONTEXT('cache_wait_for_load' = 'true') TRACE

SELECT * FROM db02."i_Keys" CONTEXT('cache_wait_for_load' = 'true') TRACE