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

CData のトライアル版JDBCドライバーを使用
→ DB接続はできたが、基本ビュー作成時に下記エラー発生
Not supported yet.

Unity のトライアル版JDBCドライバーを使用
→ OK

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

-- 1. 事前準備(MongoDB)

yum update -y

vim /etc/yum.repos.d/mongodb-org-6.0.repo

[mongodb-org-6.0]
name=MongoDB Repository
baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/6.0/x86_64/
gpgcheck=1
enabled=1
gpgkey=https://www.mongodb.org/static/pgp/server-6.0.asc

yum install -y mongodb-org

systemctl enable mongod
systemctl restart mongod
systemctl status mongod

mongod --version

vim /etc/mongod.conf

bindIp: 0.0.0.0

systemctl restart mongod
ss -ano | grep 27017

mongosh

use testdb
db.createCollection('tab1')

show dbs
show collections

db.tab1.insertMany([
{ _id: 1, col1: "val11", col2 : 21, col3 : ["val311","val321"], col4: { col41: 411, col42: "val421" } },
{ _id: 2, col1: "val12", col2 : 22, col3 : ["val312","val322"], col4: { col41: 412, col42: "val422" } },
])

db.tab1.find()

-- 2. 事前準備(Denodo)

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

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

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

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のコピー

フォルダ「unity-mongodb」を作成し、
UnityJDBC_Trial_Install.jarをダブルクリックで展開した中にある、mongodb_unityjdbc_full.jarを下記にコピーする

C:\Program Files\UnityJDBC
mongodb_unityjdbc_full.jar
↓
C:\Denodo\DenodoPlatform8.0\lib\extensions\jdbc-drivers-external\unity-mongodb
mongodb_unityjdbc_full.jar

VDP再起動

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

CREATE OR REPLACE DATASOURCE JDBC mongodb
FOLDER = '/01ds'
DRIVERCLASSNAME = 'mongodb.jdbc.MongoDriver'
DATABASEURI = 'jdbc:mongo://192.168.137.127:27017/testdb'
USERNAME = ''
USERPASSWORD = ''
CLASSPATH = 'unity-mongodb'
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_tab1
FOLDER = '/02bv'
DATASOURCENAME=mongodb
SCHEMANAME='testdb'
RELATIONNAME='tab1'
OUTPUTSCHEMA (
_id = '_id' :'java.lang.Integer' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='10', description='', sourcetypeid='4', sourcetypename='INTEGER') ESCAPE NOT NULL SORTABLE,
col1 = 'col1' :'java.lang.String' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='16793600', description='', sourcetypeid='12', sourcetypename='VARCHAR') NOT NULL SORTABLE,
col2 = 'col2' :'java.lang.Integer' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='10', description='', sourcetypeid='4', sourcetypename='INTEGER') NOT NULL SORTABLE,
col3 = 'col3' :'java.lang.String' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='16793600', description='', sourcetypeid='2003', sourcetypename='ARRAYLIST') NOT NULL NOT SORTABLE,
"col3.[0-2147483647]" = 'col3.[0-2147483647]' :'java.lang.String' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='16793600', description='', sourcetypeid='12', sourcetypename='VARCHAR') ESCAPE NOT NULL SORTABLE,
col4 = 'col4' :'java.lang.String' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='16793600', description='', sourcetypeid='-2000', sourcetypename='HASHMAP/JSON') NOT NULL NOT SORTABLE,
"col4.col41" = 'col4.col41' :'java.lang.Integer' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='10', description='', sourcetypeid='4', sourcetypename='INTEGER') ESCAPE NOT NULL SORTABLE,
"col4.col42" = 'col4.col42' :'java.lang.String' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='16793600', description='', sourcetypeid='12', sourcetypename='VARCHAR') ESCAPE NOT NULL SORTABLE
)
CONSTRAINT 'pk_tab1' PRIMARY KEY ( '_id' )
INDEX '_id_' CLUSTER PRIMARY ( '_id' );

-- 5. 基本ビュー作成

CREATE OR REPLACE TABLE b_tab1 I18N jp (
_id:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '10', description = ''),
col1:text (notnull, sourcetypeid = '12', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '16793600', description = ''),
col2:int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '10', description = ''),
col3:text,
"col3.[0-2147483647]":text (notnull, sourcetypeid = '12', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '16793600', description = ''),
col4:text,
"col4.col41":int (notnull, sourcetypeid = '4', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '10', description = ''),
"col4.col42":text (notnull, sourcetypeid = '12', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '16793600', description = '')
)
FOLDER = '/02bv'
CONSTRAINT 'pk_tab1' PRIMARY KEY ( '_id' )
CACHE OFF
TIMETOLIVEINCACHE DEFAULT
ADD SEARCHMETHOD b_tab1(
I18N jp
CONSTRAINTS (
ADD _id (any) OPT ANY
ADD col1 (any) OPT ANY
ADD col2 (any) OPT ANY
ADD col3 (any) OPT ANY
ADD "col3.[0-2147483647]" (any) OPT ANY
ADD col4 (any) OPT ANY
ADD "col4.col41" (any) OPT ANY
ADD "col4.col42" (any) OPT ANY
)
OUTPUTLIST (_id, col1, col2, col3, "col3.[0-2147483647]", col4, "col4.col41", "col4.col42"
)
WRAPPER (jdbc b_tab1)
);

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

CREATE OR REPLACE INTERFACE VIEW i_tab1 (
_id:int (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '4'),
col1:text (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '16793600', sourcetypeid = '12'),
col2:int (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '4'),
col3:text,
"col3.[0-2147483647]":text (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '16793600', sourcetypeid = '12'),
col4:text,
"col4.col41":int (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '10', sourcetypeid = '4'),
"col4.col42":text (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '16793600', sourcetypeid = '12')
)
SET IMPLEMENTATION b_tab1
FOLDER = '/03iv';

-- 7. 動作確認

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

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

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