前提: ソースデータベースで下記テーブルおよび基本ビュー作成済
drop table if exists input ;
create table input( tabname varchar(100), bvname varchar(100) );
insert into input values
('tab1','b_tab1'),
('tab2','b_tab2'),
('tab3','b_tab3');
select * from input order by tabname;
select * from b_input order by tabname;
-- パラメータ化クエリ
select creation_vql from GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW ()
where data_source_name = 'ds01'
and catalog_name ='test'
and table_name = '@TABNAME'
and base_view_name = '@BVMAME'
and folder ='/02bv'
and database_name = 'db01'
;
-- CSVファイル出力先
C:\Denodo\DenodoPlatform8.0\work\scheduler\data\csv
-- 1. ジョブ作成
※入力SQLに改行があると改行部分がエスケープされ不正なJSONとみなされる
curl -X POST "http://localhost:9090/webadmin/denodo-scheduler-admin/public/api/projects/107/jobs?uri=%2F%2Flocalhost%3A8000" -H "accept: application/json" -H "authorization: Basic YWRtaW46YWRtaW4=" -H "Content-Type: application/json" -d "{ \"type\" : \"VDP\", \"name\" : \"job01\", \"draft\" : false, \"disabled\" : false, \"description\" : \"job01\", \"extractionSection\" : { \"type\" : \"VDP\", \"dataSourceID\" : 113, \"extractionData\" : { \"parameterizedQuery\" : \"select creation_vql from GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW () where data_source_name = 'ds01' and catalog_name ='test' and table_name = '@TABNAME' and base_view_name = '@BVMAME' and folder ='/02bv' and database_name = 'db01' ;\", \"fields\" : [ { \"fields\" : [ { \"queryParamName\" : \"TABNAME\", \"sourceParamName\" : \"tabname\" }, { \"queryParamName\" : \"BVMAME\", \"sourceParamName\" : \"bvname\" } ], \"source\" : { \"type\" : \"vdp\", \"dataSourceID\" : 113, \"nonParameterizedQuery\" : \"select * from b_input order by tabname;\" } } ], \"sourcesNotChange\" : true } }, \"exportationSection\" : { \"exportAsTransaction\" : false, \"exporters\" : [ { \"type\" : \"CSV\", \"originalIndex\" : 0, \"fileName\" : \"output.csv\", \"encoding\" : \"UTF-8\", \"overwriteFile\" : true, \"appendFile\" : false, \"createNewFile\" : false, \"allowEmptyFile\" : true, \"exportInternalFields\" : false, \"exportOnlyMappings\" : false, \"filter\" : \"NONE\", \"separator\" : \",\", \"includeHeader\" : false, \"quoteAllFields\" : false, \"quoteFieldsOption\" : \"WHEN_REQUIRED\" } ] }, \"handlerSection\" : { }, \"retrySection\" : { }, \"triggerSection\" : { \"triggers\" : [ { \"type\" : \"cron\", \"cronExpression\" : \"0 0 23 * * ?\" } ] }, \"reportSection\" : { \"reportConfig\" : { \"maxIndividualReports\" : 100, \"reportOnlyErrors\" : true } }}"
-- 2. 特定プロジェクトのジョブ一覧
curl -X GET "http://localhost:9090/webadmin/denodo-scheduler-admin/public/api/projects/107/jobs?uri=%2F%2Flocalhost%3A8000" -H "Authorization: Basic YWRtaW46YWRtaW4="
curl -s -X GET "http://localhost:9090/webadmin/denodo-scheduler-admin/public/api/projects/107/jobs?uri=%2F%2Flocalhost%3A8000" -H "Authorization: Basic YWRtaW46YWRtaW4=" | findstr " \"id\" \"name\" "
-- 3. 特定プロジェクトの特定ジョブ確認
curl -X GET "http://localhost:9090/webadmin/denodo-scheduler-admin/public/api/projects/107/jobs/118?uri=%2F%2Flocalhost%3A8000" -H "Authorization: Basic YWRtaW46YWRtaW4="
{
"type" : "VDP",
"id" : 118,
"projectId" : 107,
"projectName" : "db01",
"name" : "job01",
"draft" : false,
"disabled" : false,
"description" : "job01",
"extractionSection" : {
"type" : "VDP",
"dataSourceID" : 113,
"extractionData" : {
"parameterizedQuery" : "select creation_vql from GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW () where data_source_name = 'ds01' and catalog_name ='test' and table_name = '@TABNAME' and base_view_name = '@BVMAME' and folder ='/02bv' and database_name = 'db01' ;",
"fields" : [ {
"fields" : [ {
"queryParamName" : "TABNAME",
"sourceParamName" : "tabname"
}, {
"queryParamName" : "BVMAME",
"sourceParamName" : "bvname"
} ],
"source" : {
"type" : "vdp",
"dataSourceID" : 113,
"nonParameterizedQuery" : "select * from b_input order by tabname;"
}
} ],
"sourcesNotChange" : true
}
},
"exportationSection" : {
"exportAsTransaction" : false,
"exporters" : [ {
"type" : "CSV",
"originalIndex" : 0,
"fileName" : "output.csv",
"encoding" : "UTF-8",
"overwriteFile" : true,
"appendFile" : false,
"createNewFile" : false,
"allowEmptyFile" : true,
"exportInternalFields" : false,
"exportOnlyMappings" : false,
"filter" : "NONE",
"separator" : ",",
"includeHeader" : false,
"quoteAllFields" : false,
"quoteFieldsOption" : "WHEN_REQUIRED"
} ]
},
"handlerSection" : { },
"retrySection" : { },
"triggerSection" : {
"triggers" : [ {
"type" : "cron",
"cronExpression" : "0 0 23 * * ?"
} ]
},
"reportSection" : {
"reportConfig" : {
"maxIndividualReports" : 100,
"reportOnlyErrors" : true
}
}
}
-- 4. ジョブ実行
curl -X PUT "http://localhost:9090/webadmin/denodo-scheduler-admin/public/api/projects/107/jobs/118/status?uri=%2F%2Flocalhost%3A8000" ^
-H "Authorization: Basic YWRtaW46YWRtaW4=" ^
-H "Content-Type: application/json" ^
-d "{\"action\":\"start\"} "
