{"id":6669,"date":"2021-09-03T10:58:35","date_gmt":"2021-09-03T02:58:35","guid":{"rendered":"https:\/\/nj.transwarp.cn:8180\/?p=6669"},"modified":"2021-09-03T10:58:34","modified_gmt":"2021-09-03T02:58:34","slug":"%e5%9c%a8morphing%e6%a8%a1%e5%bc%8f%e4%b8%8b%ef%bc%8c%e5%af%b9%e4%b8%80%e4%b8%aawindow-stream%e5%81%9amapjoin%e4%b9%8b%e5%90%8e%ef%bc%8c%e5%86%8dgroup-by%e6%97%a0%e6%b3%95%e4%ba%a7%e7%94%9f%e7%bb%93","status":"publish","type":"post","link":"https:\/\/kbwp.transwarp.cn\/?p=6669","title":{"rendered":"\u5728Morphing\u6a21\u5f0f\u4e0b\uff0c\u5bf9\u4e00\u4e2awindow stream\u505aMapJoin\u4e4b\u540e\uff0c\u518dgroup by\u65e0\u6cd5\u4ea7\u751f\u7ed3\u679c"},"content":{"rendered":"<h3>\u6982\u8981\u63cf\u8ff0<\/h3>\n<hr \/>\n<p>Morphling\u6a21\u5f0f\u4e0bMapJoin\u540e\u5728\u505agroup by\u6ca1\u6709\u7ed3\u679c<\/p>\n<h3>\u8be6\u7ec6\u8bf4\u660e<\/h3>\n<hr \/>\n<p>\u5177\u4f53\u6d4b\u8bd5SQL\u5982\u4e0b\uff1a<\/p>\n<pre><code class=\"language-sql\">\ndrop stream s2;\nCREATE STREAM s2(\nhits int,\nurl string,\ndtime TIMESTAMP\n) ROW FORMAT DELIMITED FIELDS TERMINATED BY \",\"\nTBLPROPERTIES(\n\"topic\"=\"testjoin\",\n\"timefield\"=\"dtime\",\n\"timeformat\"=\"yyyy-MM-dd HH:mm:ss\",\n\"use.lowlevel.consumer\"=\"true\", \n\"kafka.zookeeper\"=\"stream371:2188\",\n\"kafka.broker.list\"=\"stream371:9098\"\n);\n\n--\u5efa\u7acb\u7ed3\u679c\u8868\ndrop table s2_out;\nCREATE table s2_out(\nkey string,\nhits int,\nurl string,\nrealtime string,\nmintime string,\nmaxtime string\n);\n\n--\u5efa\u7acb\u7a97\u53e3\ndrop stream wstream;\nCREATE stream wstream AS\nSELECT *\nFROM s2 STREAMWINDOW w1 AS (SEPARATED BY dtime INTERVAL '5' SECOND); \u2013 \u7a97\u53e3\u65f6\u95f4\u4e3a5\n\nSET streamsql.use.eventtime=true; \nset streamsql.use.eventmode=true; --\u53ef\u9009,\u4f7f\u7528eventmode\u5ef6\u65f6\u4f1a\u66f4\u4f4e\nset morphling.result.auto.flush=true;\n\n--\u6d41\u8868\u8fdb\u884cJoin \u2013 \u65e0\u7ed3\u679c s2_out\u4e3a\u7ed3\u679c\u8868\nstop streamjobs;\ndrop stream s_result;\ncreate stream s_result as select\/+MAPJOIN(dict_table)\/ wstream.url\nfrom wstream join dict_table on wstream.url = dict_table.url group by wstream.url;\n\ninsert into s2_out(hits,url,realtime,mintime,maxtime) select '-',url,'','-','end' from s_result;<\/code><\/pre>\n<p>\u95ee\u9898\u539f\u56e0\uff1a<\/p>\n<p>\u57285.1\u4e4b\u524d\u7684\u7248\u672c\uff0c\u7531\u4e8e\u4f7f\u7528\/<em> + MAPJOIN <\/em>\/ hint\uff0c \u5982\u679chive.ignore.mapjoin.hint\u8bbe\u7f6e\u6210false\u7684\u8bdd\uff0chive\u4f1a\u751f\u6210MapJoinOperator,\u4f46\u662f\u5728\u6784\u5efamapjoindesc\u7684\u65f6\u5019\uff0c\u6ca1\u6709\u8bbe\u7f6estreamwindow\u914d\u7f6e\uff0c\u6240\u4ee5\u5bfc\u81f4\u6ca1\u6709\u7ed3\u679c;<\/p>\n<p>\u5982\u679chive.ignore.mapjoin.hint\u8bbe\u7f6e\u6210true\uff0chive\u751f\u6210\u7684JoinOperator\uff0cinceptor\u4f1a\u5728\u540e\u7eed\u81ea\u52a8convert\u6210MapJoinOperator\uff0c\u5728\u8fd9\u4e2a\u8fc7\u7a0b\u4e2d\u662f\u8bbe\u7f6e\u8fc7StreamWindowDesc\u7684\uff0c\u56e0\u6b64\u4f1a\u4ea7\u751f\u6b63\u786e\u7ed3\u679c\u3002<\/p>\n<p>\u89e3\u51b3\u65b9\u6cd5:<\/p>\n<p>\u6709\u4e24\u79cd\u89e3\u51b3\u65b9\u6cd5\uff1a<\/p>\n<p>set hive.ignore.mapjoin.hint = true; \u5f3a\u884c\u5728inceptor\u53bb\u505a\u4f18\u5316<br \/>\n\u5148\u505aMapJoin\uff0c\u5728\u6784\u5efa\u7a97\u53e3\uff0c\u5e76\u8fdb\u884cgroup by\u64cd\u4f5c; \u8fd9\u6837\u7684\u597d\u5904\u53ef\u4ee5\u907f\u514d\u6ed1\u52a8\u7a97\u53e3\u4e2d\u540c\u4e00\u6761\u6570\u636e\u8981join\u591a\u6b21\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u6982\u8981\u63cf\u8ff0 Morphling\u6a21\u5f0f\u4e0bMapJoin\u540e\u5728\u505agroup by\u6ca1\u6709\u7ed3\u679c \u8be6\u7ec6\u8bf4\u660e \u5177\u4f53\u6d4b\u8bd5SQL\u5982\u4e0b\uff1a ..<\/p>\n<div class=\"clear-fix\"><\/div>\n<p><a href=\"https:\/\/kbwp.transwarp.cn\/?p=6669\" title=\"read more...\">Read more<\/a><\/p>\n","protected":false},"author":9,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-6669","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"acf":[],"_links":{"self":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/6669","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\/9"}],"replies":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6669"}],"version-history":[{"count":0,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/6669\/revisions"}],"wp:attachment":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6669"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6669"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6669"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}