{"id":2140,"date":"2020-10-02T17:01:56","date_gmt":"2020-10-02T09:01:56","guid":{"rendered":"https:\/\/nj.transwarp.cn:8180\/?p=2140"},"modified":"2020-10-02T17:01:55","modified_gmt":"2020-10-02T09:01:55","slug":"%e6%b5%81%e8%a1%a8join%e4%b9%8bmapjoin","status":"publish","type":"post","link":"https:\/\/kbwp.transwarp.cn\/?p=2140","title":{"rendered":"\u6d41\u8868join\u4e4bmapjoin"},"content":{"rendered":"

\u6982\u8981\u63cf\u8ff0<\/h3>\n
\n

\u6d41\u8868join\u4e4bmapjoin<\/p>\n

\u8be6\u7ec6\u8bf4\u660e<\/h3>\n
\n

mapjoin\uff08mapred\u548cmorphling\u90fd\u652f\u6301\uff09
\n\u4e00\u822c\u662f\u5c0f\u8868\u6216\u8005\u8fc7\u6ee4\u7387\u9ad8\u7684\u8868
\n\u63a8\u8350\u663e\u5f0f\u6307\u5b9a\u5173\u8054\u65b9\u5f0f
\n\u5efa\u8baeJoin\u5355\u72ec\u653e\u4e00\u4e2aDerived Stream
\n5.1.x\u4ee5\u524d\u7248\u672cMapJoin\u524d\u9700\u8981\u5148\u6307\u5b9a\u7a97\u53e3
\n\u56e0\u4e3aMapjoin\u5f00\u5173\u9ed8\u8ba4\u5f00\u542f(ngmr.mapjoin.autoconvert = TRUE),\u5f53\u8868\u6bd4\u8f83\u5c0f,\u7b26\u5408mapjoin\u7684\u5c0f\u8868\u5b9a\u4e49\u65f6(\u7531\u53c2\u6570hive.mapjoin.smalltable.filesize\u6307\u5b9a), \u6d41\u4e0e\u8868\u7684\u5173\u8054\u9ed8\u8ba4\u5c06\u88ab\u8f6c\u6210mapjoin\u6a21\u5f0f.<\/p>\n

\u5fae\u6279mapjoin\u6d4b\u8bd5\u7ed3\u679c\uff1a
\n\u652f\u6301\u7684\u8868\uff1a
\n\u666e\u901atext\uff0corc\u8868\uff0corc\u4e8b\u7269\u8868\uff0corc\u5206\u533a\u8868\uff0c\u5206\u6876\u8868,holodesk\u8868
\n\u4e0d\u652f\u6301\u7684\u8868\uff1a
\ntext\u5206\u533a\u8868\uff0corc\u4e8b\u7269\u5206\u533a\u8868<\/p>\n

\u4e8b\u4ef6\u9a71\u52a8mapjoin\u6d4b\u8bd5\u7ed3\u679c\uff1a
\n\u652f\u6301\u7684\u8868\uff1a
\n\u666e\u901atext,orc\u8868\uff0corc\u4e8b\u52a1\u8868
\n\u4e0d\u652f\u6301\u7684\u8868\uff1a
\ntext\u5206\u533a\u8868\uff0corc\u5206\u533a\u8868\uff0corc\u4e8b\u52a1\u5206\u533a\u8868<\/p>\n

\u6d4b\u8bd5\u8bed\u53e5\uff1a<\/p>\n

set streamsql.enable.hdfs.batchflush=FALSE;\nset streamsql.hdfs.batchflush.size=1;\nSET streamsql.hdfs.batchflush.interval.ms=6;\nSET stream.batch.duration.ms=2;\nset character.literal.as.string=true;\nSET streamsql.use.eventmode=false;\nSET morphling.result.auto.flush=true;\nSET hive.exec.dynamic.partition=true;\nDROP STREAM s1;\nCREATE STREAM s1(id INT, name STRING)\n  ROW FORMAT DELIMITED FIELDS TERMINATED BY ','\n  TBLPROPERTIES(\"topic\"=\"demoo\",\n  \"kafka.zookeeper\"=\"172.22.22.1:2181\",\n  \"kafka.broker.list\"=\"172.22.22.1:9092\",\n  \"transwarp.consumer.security.protocol\"=\"SASL_PLAINTEXT\",\n  \"transwarp.consumer.sasl.kerberos.service.name\"=\"kafka\",\n  \"transwarp.consumer.sasl.jaas.config\"=\"com.sun.security.auth.module.Krb5LoginModule required useKeyTab=true storeKey=true keyTab=\\\"\/etc\/slipstream1\/conf\/xmyh.keytab\\\" principal=\\\"xmyh@TDH\\\"\"\n  );\nDROP STREAM s1_join;\nCREATE STREAM s1_join as SELECT * FROM s1 STREAMWINDOW w1 AS(LENGTH '2' SECOND SLIDE '1' SECOND);\nDROP STREAMJOB 2job;\nCREATE STREAMJOB 2job AS (\"insert into tb1 select * from s1_join\");\n\nDROP TABLE tb1;\nDROP TABLE tb2;\n\ntext\u8868\uff1a\nCREATE TABLE tb1(id INT, name STRING);\nCREATE TABLE tb2(id INT, name STRING);\nINSERT INTO tb2 SELECT 1,'a' FROM system.dual;\nINSERT INTO tb2 SELECT 1,'b' FROM system.dual;\nSELECT * FROM tb2;\n\ntext\u5206\u533a\u8868\uff1a\nCREATE TABLE tb1(id INT, name STRING) PARTITIONED BY (sex string);\nCREATE TABLE tb2(id INT, name STRING) PARTITIONED BY (sex string);\nINSERT INTO tb2 PARTITION (sex='a')SELECT 1,'a' FROM system.dual;\nINSERT INTO tb2 PARTITION (sex='a')SELECT 1,'b' FROM system.dual;\n\norc\u5206\u533a\u8868\n\nCREATE TABLE tb2(id INT, name STRING) PARTITIONED BY (sex string) STORED AS orc;\nCREATE TABLE tb1(id INT, name STRING)  PARTITIONED BY (sex string)STORED AS orc ;\n\norc\u8868\uff1a\n--CREATE TABLE tb2(id INT, name STRING) STORED AS orc;\n--CREATE TABLE tb1(id INT, name STRING) STORED AS orc;\n\norc\u4e8b\u52a1\u8868\uff1a\n--CREATE TABLE tb3(id INT, name STRING)   CLUSTERED BY (id) INTO 2 BUCKETS STORED AS ORC TBLPROPERTIES('transactional'='true');\n--CREATE TABLE tb4(id INT, name STRING)  CLUSTERED BY (id) INTO 2 BUCKETS STORED AS ORC TBLPROPERTIES('transactional'='true');\n\norc\u4e8b\u7269\u5206\u533a\u8868\n--CREATE TABLE tb2(id INT, name STRING)PARTITIONED BY (sex string) CLUSTERED BY (id) INTO 2 BUCKETS STORED AS ORC   TBLPROPERTIES('transactional'='true');\n--CREATE TABLE tb1(id INT, name STRING)PARTITIONED BY (sex string)  CLUSTERED BY (id) INTO 2 BUCKETS STORED AS ORC TBLPROPERTIES('transactional'='true');\n\nholodesk\u8868\uff08\u9700\u5f00search\uff09\uff08\u5fae\u6279mapjoin\uff09\nHolodesk\u8868\uff08\u4e8b\u4ef6\u9a71\u52a8\u6a21\u5f0f\u4e0b\u4e0d\u652f\u6301\uff09\nholodesk\u4e0d\u652f\u6301partition by\u5206\u533a\u64cd\u4f5c\nCREATE TABLE tb1(id INT, name STRING) STORED AS holodesk;\nCREATE TABLE tb3(id INT, name STRING) STORED AS holodesk;\n\nDROP STREAM s3;\nCREATE STREAM s3 AS\nSELECT \/*+MAPJOIN(b)*\/ a.id,b.name\nFROM s1 a\nLEFT join tb2 b \nON a.id = b.id;\n\nINSERT INTO tb1  select * from s3\uff1b   ---mapred\u6a21\u5f0f\u4e0b\u521b\u5efajob\u65b9\u5f0f\nINSERT INTO tb1 partition(sex='a') select * from s3; ---mapred\u6a21\u5f0f\u4e0b\u521b\u5efajob\u65b9\u5f0f\nCREATE STREAMJOB sb4 as(\"INSERT INTO tb4  select * from s3\");   ---morphling\u6a21\u5f0f\u4e0b\u521b\u5efajob\u65b9\u5f0f\nCREATE STREAMJOB sb5 as(\"INSERT INTO tb4 partition(sex='a') select * from s3\");  ---morphling\u6a21\u5f0f\u4e0b\u521b\u5efajob\u65b9\u5f0f\n\nlist STREAMJOBS;\nSELECT * FROM tb1;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
\u6982\u8981\u63cf\u8ff0 \u6d41\u8868join\u4e4bmapjoin \u8be6\u7ec6\u8bf4\u660e mapjoin\uff08mapred\u548cmorphling\u90fd\u652f\u6301\uff09 \u4e00 ..<\/p>\n
<\/div>\n
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-2140","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\/2140","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=2140"}],"version-history":[{"count":1,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/2140\/revisions"}],"predecessor-version":[{"id":3335,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/2140\/revisions\/3335"}],"wp:attachment":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2140"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2140"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2140"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}