{"id":5844,"date":"2021-05-14T16:26:35","date_gmt":"2021-05-14T08:26:35","guid":{"rendered":"https:\/\/nj.transwarp.cn:8180\/?p=5844"},"modified":"2021-05-14T17:54:22","modified_gmt":"2021-05-14T09:54:22","slug":"workflow%e8%a1%a5%e6%95%b0%e4%b9%8bsql%e4%b8%ad%e7%9a%84sysdate%e6%97%b6%e9%97%b4%e5%87%bd%e6%95%b0%e9%97%ae%e9%a2%98","status":"publish","type":"post","link":"https:\/\/kbwp.transwarp.cn\/?p=5844","title":{"rendered":"workflow\u8865\u6570\u4e4bsql\u4e2d\u7684sysdate\u65f6\u95f4\u51fd\u6570\u95ee\u9898"},"content":{"rendered":"<h3>\u6982\u8981\u63cf\u8ff0<\/h3>\n<hr \/>\n<p><strong>\u8865\u6570\u7684\u5b9a\u4e49<\/strong>\uff1a\u6839\u636e\u5f53\u524d\u5de5\u4f5c\u6d41\u7684\u8c03\u5ea6\u5468\u671f\uff0c\u5728\u6307\u5b9a\u65f6\u95f4\u6bb5\u5185\uff0c\u91cd\u65b0\u8c03\u5ea6\u6267\u884c\u4e00\u904d\u3002<\/p>\n<p>\u6bd4\u5982\uff0c\u5de5\u4f5c\u6d41A\u7684\u8c03\u5ea6\u5468\u671f\u662f\u6bcf\u5929\u65e9\u4e0a1\u70b9\u949f\u8c03\u5ea6\u3002\u6211\u4eec\u9009\u62e9A\u5728\u65f6\u95f4\u6bb52019\/01\/01~2019\/12\/31\u4e0a\u8fdb\u884c\u8865\u6570\uff0c\u90a3\u4e48workflow\u5c31\u4f1a\u7acb\u9a6c\u628aA\u8c03\u5ea6\u8d77\u6765365\u6761\u6267\u884c\u8bb0\u5f55\uff0c\u8c03\u5ea6\u65f6\u95f4\u662f2019\/01\/01-01:00:00\uff0c2019\/01\/02-01:00:00 &#8230; 2019\/12\/31-01:00:00\u3002<\/p>\n<p>\u67d0\u79cd\u573a\u666f\u4e0b\uff0c\u5ba2\u6237\u5728sql\u8bed\u53e5\u4e2d\u4f7f\u7528\u4e86<code>sysdate<\/code>\u51fd\u6570\uff0c\u800c\u8865\u6570\u8fc7\u7a0b\u4e2d\u65e0\u6cd5\u901a\u8fc7<code>sysdate<\/code>\u83b7\u53d6\u8c03\u5ea6\u65e5\u671f\uff0c\u53ea\u80fd\u591f\u901a\u8fc7workflow\u8c03\u5ea6\u65f6\u95f4\u51fd\u6570<code>schedule.date()<\/code>\u6765\u83b7\u53d6\u5f53\u5929\u65e5\u671f\uff08<code>yyyy-MM-dd<\/code>\u683c\u5f0f\uff09\u3002<\/p>\n<p>\u4e0b\u9762\u7684\u5185\u5bb9\uff0c\u4e3b\u8981\u4ecb\u7ecd\uff0c\u4e00\u4e2a\u6bcf\u5929\u6839\u636esysdate\u589e\u91cf\u4eceoracle\u62bd\u53d6\u6570\u636e\u7684sql\u7b97\u5b50\u5de5\u4f5c\u6d41\uff0c\u5982\u4f55\u6539\u9020\u5e76\u652f\u6301workflow\u7684\u8865\u6570\u529f\u80fd\uff0c\u53ef\u4f9b\u53c2\u8003\u3002<\/p>\n<p><strong>\u652f\u6301\u7248\u672c\uff1a&gt;=studio 1.4<\/strong><\/p>\n<h3>\u8be6\u7ec6\u8bf4\u660e<\/h3>\n<hr \/>\n<h4>Step1: \u5b9e\u9a8c\u51c6\u5907<\/h4>\n<p><strong>ORACLE\u4fa7\uff0c\u6784\u5efa\u6837\u4f8b\u8868\uff0c\u63d2\u5165\u6837\u4f8b\u6570\u636e<\/strong><\/p>\n<pre><code class=\"language-sql\">create table EMP\n(\n  empno    INTEGER,\n  ename    VARCHAR2(255),\n  job      VARCHAR2(255),\n  mgr      INTEGER,\n  hiredate DATE,\n  sal      INTEGER,\n  comm     INTEGER,\n  deptno   INTEGER\n);<\/code><\/pre>\n<pre><code class=\"language-sql\">insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) \nvalues ('8001', 'SMITH', 'CLERK', '7902', to_date('12-05-2021 12:07:05', 'dd-mm-yyyy hh24:mi:ss'), '800', null, '20');\ninsert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) \nvalues ('8002', 'SMITH', 'CLERK', '7902', to_date('13-05-2021 12:07:05', 'dd-mm-yyyy hh24:mi:ss'), '800', null, '20');\ninsert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) \nvalues ('8003', 'SMITH', 'CLERK', '7902', to_date('14-05-2021 12:07:05', 'dd-mm-yyyy hh24:mi:ss'), '800', null, '20');<\/code><\/pre>\n<p><img decoding=\"async\" src=\"https:\/\/nj.transwarp.cn:8180\/wp-content\/uploads\/2021\/05\/image-1620985376259.png\" alt=\"file\" \/><\/p>\n<p><strong>INCEPTOR\u4fa7\uff0c\u6784\u5efa\u6837\u4f8b\u8868<\/strong><\/p>\n<pre><code class=\"language-sql\">CREATE  TABLE <code>emp_sp<\/code>(\n  <code>empno<\/code> int DEFAULT NULL,\n  <code>ename<\/code> string DEFAULT NULL,\n  <code>job<\/code> string DEFAULT NULL,\n  <code>mgr<\/code> int DEFAULT NULL,\n  <code>hiredate<\/code> date DEFAULT NULL,\n  <code>sal<\/code> int DEFAULT NULL,\n  <code>comm<\/code> int DEFAULT NULL,\n  <code>deptno<\/code> int DEFAULT NULL\n)\nCLUSTERED BY (\n  <code>empno<\/code>)\nINTO 3 BUCKETS\nSTORED AS ORC_TRANSACTION;<\/code><\/pre>\n<h4>Step2:workflow\u5de5\u4f5c\u6d41\u914d\u7f6e<\/h4>\n<p><strong>\u4f7f\u7528SQL\u7b97\u5b50<\/strong><br \/>\n<img decoding=\"async\" src=\"\/wp-content\/uploads\/2021\/05\/image-1620985508639.png\" alt=\"file\" \/><\/p>\n<p><code>insert into emp_sp select * from emp@oracledblink where STR_TO_DATE(HIREDATE,&quot;yyyy-MM-dd&quot;)=STR_TO_DATE(&quot;${xxxyyy}&quot;,&quot;yyyy-MM-dd&quot;);<\/code><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2021\/05\/image-1620986032015.png\" alt=\"file\" \/><\/p>\n<p>\u53c2\u6570\u914d\u7f6e\uff0c\u9009\u62e9<strong>\u53d8\u91cf<\/strong>\u7c7b\u578b\u6dfb\u52a0<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2021\/05\/image-1620985558736.png\" alt=\"file\" \/><\/p>\n<h4>Step3: \u9a8c\u8bc13\u5929\u7684\u8865\u6570<\/h4>\n<p><strong>\u70b9\u51fbworkflow\u9875\u9762\u53f3\u4e0a\u89d2\u7684\u8865\u6570\u6309\u94ae<\/strong><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2021\/05\/image-1620981303254.png\" alt=\"file\" \/><\/p>\n<p><strong>\u52fe\u9009\u5bf9\u5e94\u7684\u5de5\u4f5c\u6d41\uff0c\u9009\u62e9\u9700\u8981\u8865\u6570\u7684\u65f6\u95f4\u8303\u56f4<\/strong><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2021\/05\/image-1620985653312.png\" alt=\"file\" \/><\/p>\n<p><strong>\u5728\u4e3b\u9875\u9762\uff0c\u9009\u62e9\u65f6\u95f4\u8de8\u5ea6\uff0c\u89c2\u5bdf\u8865\u6570\u751f\u6210\u7684\u5de5\u4f5c\u4efb\u52a1\u662f\u5426\u6267\u884c\u6210\u529f<\/strong><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2021\/05\/image-1620985605340.png\" alt=\"file\" \/><\/p>\n<p><strong>\u6700\u7ec8inceptor\u7ed3\u679c\uff1a<\/strong><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2021\/05\/image-1620985699176.png\" alt=\"file\" \/><\/p>\n<h4>\u9644beeline\u811a\u672c\u7b97\u5b50\u8865\u6570\u65b9\u5f0f\uff1a<\/h4>\n<p><strong>\u4f7f\u7528\u811a\u672c\u7b97\u5b50<\/strong><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2021\/05\/image-1620980307166.png\" alt=\"file\" \/><\/p>\n<p><code>source \/bin\/tdh_client_import.sh<\/code><br \/>\n<code>beeline -u jdbc:hive2:\/\/172.22.23.71:10000\/default -n hive -p 123456  --maxWidth=10000 --maxColumnWidth=10000 --hivevar ddate=${xxxyyy} -e &#039;set hive.exec.dynamic.partition=true; insert into emp_sp select * from emp@oracledblink where STR_TO_DATE(HIREDATE,&quot;yyyy-MM-dd&quot;)=STR_TO_DATE(&quot;${hivevar:ddate}&quot;,&quot;yyyy-MM-dd&quot;);&#039;<\/code><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2021\/05\/image-1620980384935.png\" alt=\"file\" \/><\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2021\/05\/image-1620980398308.png\" alt=\"file\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u6982\u8981\u63cf\u8ff0 \u8865\u6570\u7684\u5b9a\u4e49\uff1a\u6839\u636e\u5f53\u524d\u5de5\u4f5c\u6d41\u7684\u8c03\u5ea6\u5468\u671f\uff0c\u5728\u6307\u5b9a\u65f6\u95f4\u6bb5\u5185\uff0c\u91cd\u65b0\u8c03\u5ea6\u6267\u884c\u4e00\u904d\u3002 \u6bd4\u5982\uff0c\u5de5\u4f5c\u6d41A\u7684\u8c03\u5ea6\u5468\u671f\u662f ..<\/p>\n<div class=\"clear-fix\"><\/div>\n<p><a href=\"https:\/\/kbwp.transwarp.cn\/?p=5844\" title=\"read more...\">Read more<\/a><\/p>\n","protected":false},"author":12,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38],"tags":[],"class_list":["post-5844","post","type-post","status-publish","format-standard","hentry","category-configuration"],"acf":[],"_links":{"self":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/5844","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5844"}],"version-history":[{"count":1,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/5844\/revisions"}],"predecessor-version":[{"id":5856,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/5844\/revisions\/5856"}],"wp:attachment":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5844"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5844"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5844"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}