{"id":2137,"date":"2020-10-02T17:04:19","date_gmt":"2020-10-02T09:04:19","guid":{"rendered":"https:\/\/nj.transwarp.cn:8180\/?p=2137"},"modified":"2020-10-02T17:04:18","modified_gmt":"2020-10-02T09:04:18","slug":"%e6%b5%81%e8%a1%a8join%e4%b9%8b%e4%b8%80%e8%88%acjoin","status":"publish","type":"post","link":"https:\/\/kbwp.transwarp.cn\/?p=2137","title":{"rendered":"\u6d41\u8868join\u4e4b\u4e00\u822cjoin"},"content":{"rendered":"

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

\u672c\u6587\u65e8\u5728\u4ecb\u7ecd\u6d41\u8868join\u4e4b\u4e00\u822cjoin\u7684\u7528\u6cd5<\/p>\n

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

\u4e00\u822cjoin\uff08\u53ea\u5728mapred\u4e0b\u652f\u6301\uff09
\n\u8d85\u8fc7hive.mapjoin.smalltable.filesize\u53c2\u6570\u6307\u5b9a\u7684mapjoin\uff0c\u4f1a\u81ea\u52a8\u8f6c\u6362\u6210common join<\/p>\n

\u4e00\u822cjoin\u652f\u6301\u7684\u8868\uff1a<\/strong>
\norc\u5206\u533a\u8868
\ntext\u8868
\norc\u8868
\ntext\u5206\u533a\u8868
\nholodesk\u8868
\n\u4e0d\u652f\u6301\u7684\u8868\uff1a<\/strong>
\norc\u4e8b\u52a1\u5206\u533a\u8868
\norc\u4e8b\u52a1\u8868<\/p>\n

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

\n\u5f00\u542f\u52a8\u6001\u5206\u533a\u529f\u80fd\nSET hive.exec.dynamic.partition=true;\n\n\u5efa\u6d41s1\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  );\n\ntext\u8868\uff1a\n--CREATE TABLE tb1(id INT, name STRING);\n--CREATE TABLE tb3(id INT, name STRING);\n\ntext\u5206\u533a\u8868\uff1a\n--CREATE TABLE tb1(id INT, name STRING) PARTITIONED BY (sex string);\n--CREATE TABLE tb3(id INT, name STRING) PARTITIONED BY (sex string);\nINSERT INTO tb3 SELECT 1,'a' FROM system.dual;\nINSERT INTO tb3 SELECT 1,'b' FROM system.dual;\n\norc\u5206\u533a\u8868\uff1a\n--CREATE TABLE tb4(id int, name STRING) partitioned by(sex string) STORED AS orc;\n--CREATE TABLE tb3(id int, name STRING) partitioned by(sex string) STORED AS orc;\nINSERT INTO tb3 PARTITION (sex='b') SELECT 1,'a' FROM system.dual;\nINSERT INTO tb3 PARTITION (sex='b') SELECT 1,'b' FROM system.dual;\nSELECT * FROM tb3;\nSHOW CREATE TABLE tb1;\nSHOW PARTITIONS tb3;\n\norc\u8868\uff1a\n--CREATE TABLE tb3(id int, name STRING) STORED AS orc;\n--CREATE TABLE tb4(id int, name STRING) STORED AS orc;\n\norc\u4e8b\u52a1\u5206\u533a\u8868\uff1a\n--CREATE TABLE tb3(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\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--Holodesk\u8868\uff08\u4e8b\u4ef6\u9a71\u52a8\u6a21\u5f0f\u4e0b\u4e0d\u652f\u6301\uff09(holodesk\u4e0d\u652f\u6301\u5206\u533a\u64cd\u4f5c\uff09\n\nholodesk\u8868\uff1a\n--CREATE TABLE tb4(id INT, name STRING) STORED AS holodesk;\n--CREATE TABLE tb3(id INT, name STRING) STORED AS holodesk;\n\n\u5efa\u884d\u751f\u6d41s3\uff0c\u5b9e\u73b0\u6d41\u8868join\nCREATE STREAM s3 AS\nSELECT a.id,b.name\nFROM s1 a\nLEFT join tb3 b \nON a.id = b.id;\n\n\u628a\u884d\u751f\u6d41s3\u6570\u636e\u5165\u8868\nINSERT INTO tb1 partition(sex='b') select * from s3;\nSELECT * FROM tb1;\n<\/code><\/pre>\n
\u7ed3\u679c\u663e\u793a\u6d41\u8868\u53ef\u4ee5\u6210\u529fjoin\uff1a
\n\"file\"<\/p>\n
\u6ce8\uff1a\u96c6\u7fa4\u5f00\u5b89\u5168\u60c5\u51b5\u4e0bkafka\u5bf9\u63a5slipstream\u53c2\u8003\u6587\u6863\uff1a
\nkafka\u5bf9\u63a5slipstream<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
\u6982\u8981\u63cf\u8ff0 \u672c\u6587\u65e8\u5728\u4ecb\u7ecd\u6d41\u8868join\u4e4b\u4e00\u822cjoin\u7684\u7528\u6cd5 \u8be6\u7ec6\u8bf4\u660e \u4e00\u822cjoin\uff08\u53ea\u5728mapred\u4e0b\u652f\u6301\uff09 \u8d85\u8fc7 ..<\/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-2137","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\/2137","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=2137"}],"version-history":[{"count":1,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/2137\/revisions"}],"predecessor-version":[{"id":3304,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/2137\/revisions\/3304"}],"wp:attachment":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2137"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2137"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2137"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}