{"id":967,"date":"2018-11-09T18:15:00","date_gmt":"2018-11-09T10:15:00","guid":{"rendered":"https:\/\/199604.com\/?p=967"},"modified":"2019-09-06T19:20:11","modified_gmt":"2019-09-06T11:20:11","slug":"hive%e8%b8%a9%e8%bf%87%e7%9a%84%e5%9d%91-hive%e7%9a%84insert%e4%b8%8ewith-as-%e8%af%ad%e5%8f%a5%e6%90%ad%e9%85%8d","status":"publish","type":"post","link":"https:\/\/199604.com\/967","title":{"rendered":"Hive\u8e29\u8fc7\u7684\u5751-Hive\u7684insert\u4e0ewith as \u8bed\u53e5\u642d\u914d"},"content":{"rendered":"

\u4eba\u751f\u5c31\u662f\u4e0d\u65ad\u7684\u6316\u5751;\u800c\u6211\u4e5f\u4e0d\u4f8b\u5916\u3002<\/p>\n

\n

\u5199\u60ef\u4e86ORACLE\u8bed\u53e5\u7684\u7ae5\u978b\uff0c\u5751\u7239\u5bf9with as\u8bed\u53e5\u4e0d\u964c\u751f\u5427\u3002\u7b80\u5355\u7684\u8bf4with as \u8bed\u53e5\u4e5f\u53ef\u4ee5\u8bf4\u662f\u5b50\u67e5\u8be2\u8bed\u53e5\u3002<\/p>\n

\u9996\u5148\uff0c\u6211\u4eec\u5148\u6765\u4e00\u4e2a\u6817\u5b50(\u4f8b\u5b50)\u5427\u3002<\/p>\n

CREATE TABLE TABLE_A AS\r\n\tWITH TEMP_A AS (\r\n\t\tSELECT TIME,IOT_ID,NAME FROM IOT_XX_A\r\n\t),TEMP_B AS (\r\n\t\tSELECT TIME,IOT_ID,NAME,COUNT(DISTINCT IOT_ID) AS TIMES FROM TEMP_A\r\n\t\t\tGROUP BY TIME,IOT_ID,NAME\r\n\t)\r\nSELECT TIME,IOT_ID,NAME,TIMES FROM TEMP_B;<\/pre>\n
\u6309\u7167\u4e0a\u9762\u7684SQL\u8bed\u53e5\uff0c\u5728ORACLE\u4f1a\u6267\u884c\uff0c\u800c\u5728hive\u5462\uff0c\u4e0d\u7136\u4e5f\u4f1a\u6267\u884c..\u524d\u63d0\u6700\u540e\u538b\u7f29\u4e00\u4e0bsql\u683c\u5f0f\u3002<\/p>\n
\u56e0\u4e3a\u628a\u67e5\u8be2\u7ed3\u679c\u521b\u5efa\u6210\u8868\uff0c\u4e8c\u8005\u90fd\u662f\u652f\u6301\u7684\u3002<\/p>\n
\u4f46\u662f\u5982\u679c\u662fINSERT (\u63d2\u5165)\u8868\u5462?<\/p>\n
\u518d\u4e3e\u4e2a\u6817\u5b50(\u4f8b\u5b50)<\/p>\n
INSERT INTO TABLE_B\r\n\tWITH TEMP_A AS (\r\n\t\tSELECT TIME,IOT_ID,NAME FROM IOT_XX_A\r\n\t),TEMP_B AS (\r\n\t\tSELECT TIME,IOT_ID,NAME,COUNT(DISTINCT IOT_ID) AS TIMES FROM TEMP_A\r\n\t\t\tGROUP BY TIME,IOT_ID,NAME\r\n\t)\r\nSELECT TIME,IOT_ID,NAME,TIMES FROM TEMP_B;<\/pre>\n
\u6309\u7167\u4e0a\u9762\u7684SQL\u8bed\u53e5\uff0c\u5728ORACLE\u4f1a\u6267\u884c\uff0c\u800c\u5728hive\u5462?\u4ed6\u5c31\u4f1a\u62a5\u9519\uff0c\u5177\u4f53\u7684\u9519\u662f\uff1aError: Error while compiling statement: FAILED: ParseException line 2:0 cannot recognize input near ‘WITH’ ‘TABLE_B’ ‘AS’ in statement<\/p>\n
\u9519\u8bef\u7684\u539f\u56e0\u5c31\u662fINSERT INTO \u4f4d\u7f6e\u653e\u5f97\u4e0d\u5408\u9002\u3002\u4e0d\u4e5f\u8fd8\u662fhive\u8bed\u53e5\u4e0esql\u8bed\u53e5\u89c4\u5219\u4e0d\u4e00\u6837\u5bfc\u81f4\u7684?\u628aINSERT INTO\u8bed\u53e5\u6362\u4e00\u4e0b\u4f4d\u7f6e\u5c31\u53ef\u4ee5\u6267\u884c\u4e86<\/p>\n
\tWITH TEMP_A AS (\r\n\t\tSELECT TIME,IOT_ID,NAME FROM IOT_XX_A\r\n\t),TEMP_B AS (\r\n\t\tSELECT TIME,IOT_ID,NAME,COUNT(DISTINCT IOT_ID) AS TIMES FROM TEMP_A\r\n\t\t\tGROUP BY TIME,IOT_ID,NAME\r\n\t)\r\nINSERT INTO TABLE_B\t\r\n\tSELECT TIME,IOT_ID,NAME,TIMES FROM TEMP_B;<\/pre>\n
\u533a\u5206\uff1a<\/p>\n
insert into \u662f\u76f4\u63a5\u8ffd\u52a0\u5230table<\/p>\n
insert\u00a0overwrite\u00a0\u662f\u8986\u76d6table\u6570\u636e\u3002<\/p>\n
\u597d\u4e86\uff0c\u5751\u57cb\u4e0a\u4e86\u3002\u4ee5\u540e\u7684\u81ea\u5df1\u8bb0\u5f97\u770b\u54c8.<\/p>\n
\n
\"\"<\/div> 
\"\"<\/div>
<\/div>
<\/div><\/p>\n","protected":false},"excerpt":{"rendered":"
\u4eba\u751f\u5c31\u662f\u4e0d\u65ad\u7684\u6316\u5751;\u800c\u6211\u4e5f\u4e0d\u4f8b\u5916\u3002 \u5199\u60ef\u4e86ORACLE\u8bed\u53e5\u7684\u7ae5\u978b\uff0c\u5751\u7239\u5bf9with as\u8bed\u53e5\u4e0d\u964c\u751f\u5427\u3002\u7b80\u5355\u7684\u8bf4w […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[179,180,184],"class_list":["post-967","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-hive","tag-180","tag-184"],"_links":{"self":[{"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/posts\/967","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/comments?post=967"}],"version-history":[{"count":4,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/posts\/967\/revisions"}],"predecessor-version":[{"id":1300,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/posts\/967\/revisions\/1300"}],"wp:attachment":[{"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/media?parent=967"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/categories?post=967"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/199604.com\/wp-json\/wp\/v2\/tags?post=967"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}