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

https://community.denodo.com/docs/html/browse/8.0/jp/vdp/administration/creating_data_sources_and_base_views/json_sources/json_sources
https://medium.com/veltra-engineering/dummy-json-8d21e35f6e45

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

-- 1. JSONモックサーバ作成

npm install -g dummy-json

vim a.hbs

[
  {{#repeat min=3 max=3}}
  {
    "col1": {{add @index 1}},
    "col2": {{int 1 100}},
    "col3": "{{date '2010' '2020' 'YYYY-MM-DD'}}",
    "col4": "{{firstName}}",
    "col5": "{{add (step 10) 0}}.png",
    "col6": "{{random 'foge' 'fuga' 'piyo' 'foo' 'bar' 'baz'}}",
    "col7": "{{color}}",
    "col8": "{{guid}}",
    "col9": "{{ipv4}}"
  }
  {{/repeat}}
]

dummyjson a.hbs
dummyjson a.hbs | jq '.[]' -cr

vim a.sh

#!/bin/bash

PORT=4000

function response() {
  echo "HTTP/1.0 200 OK"
  echo "Content-Type: text/plain"
  echo ""
  dummyjson a.hbs
}

# main
# ctrl+cで無限ループを抜けれるようにする
trap exit INT
# 無限ループでリクエストを受け取る
while true; do
  response | nc -l "$PORT" -w 1 
done

chmod +x a.sh
./a.sh

curl http://localhost:4000

-- 2. 事前準備

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

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

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

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 JSON ds01
    FOLDER = '/01ds'
    ROUTE HTTP 'http.CommonsHttpClientConnection,120000' GET 'http://192.168.137.6:4000'
    AUTHENTICATION OFF
    PROXY OFF;

-- 4. ラッパー作成

CREATE OR REPLACE WRAPPER JSON ds01
    FOLDER = '/02bv'
    DATASOURCENAME=ds01
    TUPLEROOT '/JSONFile'
    ROUTE HTTP 'http.CommonsHttpClientConnection,120000' GET '/'
    OUTPUTSCHEMA ("JSONFile" = 'JSONFile' : REGISTER OF (
        "JSONArray" = 'JSONArray' : ARRAY OF (
            "JSONArray" = 'JSONArray' : REGISTER OF (
                col1 = 'col1' : 'java.lang.Integer',
                col2 = 'col2' : 'java.lang.Integer',
                col3 = 'col3' : 'java.lang.String',
                col4 = 'col4' : 'java.lang.String',
                col5 = 'col5' : 'java.lang.String',
                col6 = 'col6' : 'java.lang.String',
                col7 = 'col7' : 'java.lang.String',
                col8 = 'col8' : 'java.lang.String',
                col9 = 'col9' : 'java.lang.String'
            )
        )
    )
    );

-- 5. 基本ビュー作成

CREATE OR REPLACE TYPE "ds01_JSONArray_JSONArray" AS REGISTER OF (col1:int, col2:int, col3:text, col4:text, col5:text, col6:text, col7:text, col8:text, col9:text);

CREATE OR REPLACE TYPE "ds01_JSONArray" AS ARRAY OF "ds01_JSONArray_JSONArray";

CREATE OR REPLACE TABLE b_tab1 I18N jp (
        "JSONArray":"ds01_JSONArray"
    )
    FOLDER = '/02bv'
    CACHE OFF
    TIMETOLIVEINCACHE DEFAULT
    ADD SEARCHMETHOD ds01(
        I18N jp
        CONSTRAINTS (
             ADD "JSONArray" NOS ZERO ()
             ADD "JSONArray" NOS ZERO ()
             ADD "JSONArray".col1 NOS ZERO ()
             ADD "JSONArray".col2 NOS ZERO ()
             ADD "JSONArray".col3 NOS ZERO ()
             ADD "JSONArray".col4 NOS ZERO ()
             ADD "JSONArray".col5 NOS ZERO ()
             ADD "JSONArray".col6 NOS ZERO ()
             ADD "JSONArray".col7 NOS ZERO ()
             ADD "JSONArray".col8 NOS ZERO ()
             ADD "JSONArray".col9 NOS ZERO ()
        )
        OUTPUTLIST ("JSONArray"
        )
        WRAPPER (json ds01)
    );

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

CREATE OR REPLACE INTERFACE VIEW i_tab1 (
        "JSONArray":"ds01_JSONArray"
    )
    SET IMPLEMENTATION b_tab1
    FOLDER = '/03iv';

-- 7. 動作確認

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

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