{Denodo}スケジューラジョブ(ハンドラー + CSV exporter)

Denodo

 

https://community.denodo.com/docs/html/browse/8.0/jp/scheduler/administration/creating_and_scheduling_jobs/configuring_new_jobs/handler_section

https://learntutorials.net/ja/batch-file/topic/10693/%E7%89%B9%E6%AE%8A%E6%96%87%E5%AD%97%E3%82%92%E3%82%A8%E3%82%B9%E3%82%B1%E3%83%BC%E3%83%97%E3%81%99%E3%82%8B

 

前提:  Schedulerサーバーのメール設定済み

 

-- クエリ
select * from db01.b_tab1 where col2 < 0;


select * from db01.b_tab1 ;
update db01.b_tab1 set col2 = -1*col2 where ABS(col1) = 2;


-- CSVファイル出力先
C:\Denodo\DenodoPlatform8.0\work\scheduler\data\csv

 

-- 1. ジョブ作成

※エラー「指定されたファイルが見つかりません。」発生
col2 > 0 → >を^でエスケープしてOK


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 * from db01.b_tab1 where col2 ^< 0;\", \"sourcesNotChange\" : true } }, \"exportationSection\" : { \"exportAsTransaction\" : false, \"exporters\" : [ { \"type\" : \"CSV\", \"originalIndex\" : 0, \"fileName\" : \"@{projectName}_@{jobName}_@{jobID}_CSVExporter#@{exporterID}.csv\", \"encoding\" : \"UTF-8\", \"overwriteFile\" : false, \"appendFile\" : false, \"createNewFile\" : true, \"allowEmptyFile\" : false, \"exportInternalFields\" : false, \"exportOnlyMappings\" : false, \"filter\" : \"NONE\", \"i18n\" : \"JP\", \"separator\" : \",\", \"includeHeader\" : true, \"quoteAllFields\" : false, \"quoteFieldsOption\" : \"WHEN_REQUIRED\" } ] }, \"handlerSection\" : { \"handlers\" : [ { \"handleOnlyOnLastRetry\" : false, \"type\" : \"mail\", \"originalIndex\" : 0, \"mailAddresses\" : [ \"hoge@example.com\" ], \"send\" : [ \"IF_EXPORTED_MORE_THAN_THRESHOLD\", \"IF_ERRORS\" ], \"thresholdMore\" : 1 } ] }, \"retrySection\" : { }, \"triggerSection\" : { \"triggers\" : [ { \"type\" : \"cron\", \"cronExpression\" : \"0 0/5 * * * ?\" } ] }, \"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/143?uri=%2F%2Flocalhost%3A8000" -H "Authorization: Basic YWRtaW46YWRtaW4="

{
  "type" : "VDP",
  "id" : 143,
  "projectId" : 107,
  "projectName" : "db01",
  "name" : "job01",
  "draft" : false,
  "disabled" : false,
  "description" : "job01",
  "extractionSection" : {
    "type" : "VDP",
    "dataSourceID" : 113,
    "extractionData" : {
      "parameterizedQuery" : "select * from db01.b_tab1 where col2 < 0;",
      "sourcesNotChange" : true
    }
  },
  "exportationSection" : {
    "exportAsTransaction" : false,
    "exporters" : [ {
      "type" : "CSV",
      "originalIndex" : 0,
      "fileName" : "@{projectName}_@{jobName}_@{jobID}_CSVExporter#@{exporterID}.csv",
      "encoding" : "UTF-8",
      "overwriteFile" : false,
      "appendFile" : false,
      "createNewFile" : true,
      "allowEmptyFile" : false,
      "exportInternalFields" : false,
      "exportOnlyMappings" : false,
      "filter" : "NONE",
      "i18n" : "JP",
      "separator" : ",",
      "includeHeader" : true,
      "quoteAllFields" : false,
      "quoteFieldsOption" : "WHEN_REQUIRED"
    } ]
  },
  "handlerSection" : {
    "handlers" : [ {
      "handleOnlyOnLastRetry" : false,
      "type" : "mail",
      "originalIndex" : 0,
      "mailAddresses" : [ "hoge@example.com" ],
      "send" : [ "IF_EXPORTED_MORE_THAN_THRESHOLD", "IF_ERRORS" ],
      "thresholdMore" : 1
    } ]
  },
  "retrySection" : { },
  "triggerSection" : {
    "triggers" : [ {
      "type" : "cron",
      "cronExpression" : "0 0/5 * * * ?"
    } ]
  },
  "reportSection" : {
    "reportConfig" : {
      "maxIndividualReports" : 100,
      "reportOnlyErrors" : true
    }
  }
}


-- 4. ジョブ実行

curl -X PUT "http://localhost:9090/webadmin/denodo-scheduler-admin/public/api/projects/107/jobs/143/status?uri=%2F%2Flocalhost%3A8000" ^
-H  "Authorization: Basic YWRtaW46YWRtaW4=" ^
-H "Content-Type: application/json" ^
-d "{\"action\":\"start\"} "

 


-- 5. 動作確認

対象データがある場合に限り、
5分毎にCSV出力され、メールが届くことを確認