{"id":1824,"date":"2020-07-01T06:00:22","date_gmt":"2020-06-30T22:00:22","guid":{"rendered":"http:\/\/39.97.231.10\/?p=1824"},"modified":"2022-03-27T01:05:34","modified_gmt":"2022-03-26T17:05:34","slug":"hive%e5%88%86%e5%8c%ba%e8%a1%a8%e7%ae%80%e4%bb%8b","status":"publish","type":"post","link":"http:\/\/www.weijing.co\/?p=1824","title":{"rendered":"Hive\u5206\u533a\u8868\u7b80\u4ecb"},"content":{"rendered":"\t\t<div data-elementor-type=\"wp-post\" data-elementor-id=\"1824\" class=\"elementor elementor-1824\" data-elementor-settings=\"[]\">\n\t\t\t\t\t\t\t<div class=\"elementor-section-wrap\">\n\t\t\t\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-7277f5f7 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"7277f5f7\" data-element_type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-7212ea49\" data-id=\"7212ea49\" data-element_type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t\t\t<div class=\"elementor-element elementor-element-679dc69 elementor-widget elementor-widget-text-editor\" data-id=\"679dc69\" data-element_type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t<div>\n<div>\n<div>\n<div>\u5982\u679c\u4e00\u4e2a\u8868\u4e2d\u6570\u636e\u5f88\u591a\uff0c\u6211\u4eec\u67e5\u8be2\u65f6\u5c31\u5f88\u6162\uff0c\u8017\u8d39\u5927\u91cf\u65f6\u95f4\uff0c\u5982\u679c\u8981\u67e5\u8be2\u5176\u4e2d\u90e8\u5206\u6570\u636e\u8be5\u600e\u4e48\u529e\u5462\uff0c\u8fd9\u65f6\u6211\u4eec\u5f15\u5165\u5206\u533a\u7684\u6982\u5ff5\u3002<\/div>\n<\/div>\n<\/div>\n<\/div>\n<article>Hive\u4e2d\u7684\u5206\u533a\u8868\u5206\u4e3a\u4e24\u79cd\uff1a\u9759\u6001\u5206\u533a\u548c\u52a8\u6001\u5206\u533a\u3002\n<h5>1.\u9759\u6001\u5206\u533a:<\/h5>\n<ul>\n \t<li>\u53ef\u4ee5\u6839\u636ePARTITIONED BY\u521b\u5efa\u5206\u533a\u8868\uff0c\u4e00\u4e2a\u8868\u53ef\u4ee5\u62e5\u6709\u4e00\u4e2a\u6216\u8005\u591a\u4e2a\u5206\u533a\uff0c\u6bcf\u4e2a\u5206\u533a\u4ee5\u6587\u4ef6\u5939\u7684\u5f62\u5f0f\u5355\u72ec\u5b58\u5728\u8868\u6587\u4ef6\u5939\u7684\u76ee\u5f55\u4e0b\u3002<\/li>\n \t<li>\u5206\u533a\u662f\u4ee5\u5b57\u6bb5\u7684\u5f62\u5f0f\u5728\u8868\u7ed3\u6784\u4e2d\u5b58\u5728\uff0c\u901a\u8fc7describe table\u547d\u4ee4\u53ef\u4ee5\u67e5\u770b\u5230\u5b57\u6bb5\u5b58\u5728\uff0c\u4f46\u662f\u8be5\u5b57\u6bb5\u4e0d\u5b58\u653e\u5b9e\u9645\u7684\u6570\u636e\u5185\u5bb9\uff0c\u4ec5\u4ec5\u662f\u5206\u533a\u7684\u8868\u793a\u3002<\/li>\n \t<li>\u5206\u533a\u5efa\u8868\u5206\u4e3a2\u79cd\uff0c\u4e00\u79cd\u662f\u5355\u5206\u533a\uff0c\u4e5f\u5c31\u662f\u8bf4\u5728\u8868\u6587\u4ef6\u5939\u76ee\u5f55\u4e0b\u53ea\u6709\u4e00\u7ea7\u6587\u4ef6\u5939\u76ee\u5f55\u3002\u53e6\u5916\u4e00\u79cd\u662f\u591a\u5206\u533a\uff0c\u8868\u6587\u4ef6\u5939\u4e0b\u51fa\u73b0\u591a\u6587\u4ef6\u5939\u5d4c\u5957\u6a21\u5f0f\u3002<\/li>\n<\/ul>\n\u5355\u7ea7\u5206\u533a\u8868\u6f14\u793a\uff1a\n<div>\n<pre># \u5355\u5206\u533a\u8868\u521b\u5efa\nhive&gt; create table order_partition(\n    &gt; ordernumber string,\n    &gt; eventtime string\n    &gt; )\n    &gt; partitioned by (event_month string)\n    &gt; row format delimited fields terminated by \\'\\\\t\\';\nOK\nTime taken: 0.82 seconds\n# \u5c06order.txt \u6587\u4ef6\u4e2d\u7684\u6570\u636e\u52a0\u8f7d\u5230order_partition\u8868\u4e2d\nhive&gt; load data local inpath \\'\/home\/hadoop\/order.txt\\' overwrite into table order_partition partition (event_month=\\'2014-05\\');\nLoading data to table default.order_partition partition (event_month=2014-05)\nPartition default.order_partition{event_month=2014-05} stats: [numFiles=1, numRows=0, totalSize=208, rawDataSize=0]\nOK\nTime taken: 1.749 seconds\n# \u67e5\u770border_partition\u5206\u533a\u6570\u636e\nhive&gt; select * from order_partition where event_month=\\'2014-05\\';\nOK\n10703007267488  2014-05-01 06:01:12.334+01      2014-05\n10101043505096  2014-05-01 07:28:12.342+01      2014-05\n10103043509747  2014-05-01 07:50:12.33+01       2014-05\n10103043501575  2014-05-01 09:27:12.33+01       2014-05\n10104043514061  2014-05-01 09:03:12.324+01      2014-05\nTime taken: 0.208 seconds, Fetched: 5 row(s)\n# \u5728\u5143\u6570\u636eMySQL\u4e2d\u67e5\u770b\nmysql&gt; select * from partitions;\n+---------+-------------+------------------+---------------------+-------+--------+\n| PART_ID | CREATE_TIME | LAST_ACCESS_TIME | PART_NAME           | SD_ID | TBL_ID |\n+---------+-------------+------------------+---------------------+-------+--------+\n|       1 |  1530498328 |                0 | event_month=2014-05 |    32 |     31 |\n+---------+-------------+------------------+---------------------+-------+--------+\n1 row in set (0.00 sec)\nmysql&gt;  select * from partition_key_vals;\n+---------+--------------+-------------+\n| PART_ID | PART_KEY_VAL | INTEGER_IDX |\n+---------+--------------+-------------+\n|       1 | 2014-05      |           0 |\n+---------+--------------+-------------+\n1 row in set (0.00 sec)\n# HDFS\u4e2d\u67e5\u770b\u76ee\u5f55\n[hadoop@hadoop000 ~]$ hadoop fs -ls \/user\/hive\/warehouse\/order_partition\/\nFound 1 items\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 10:29 \/user\/hive\/warehouse\/order_partition\/event_month=2014-05\n<\/pre>\n<\/div>\n\u6ce8\uff1a\u4f7f\u7528hadoop shell \u52a0\u8f7d\u6570\u636e\u4e5f\u80fd\u52a0\u8f7d\u6570\u636e\uff0c\u4e0b\u9762\u8fdb\u884c\u6f14\u793a\uff1a\n\u521b\u5efa\u5206\u533a\uff0c\u4e5f\u5c31\u662f\u8bf4\u5728HDFS\u6587\u4ef6\u5939\u76ee\u5f55\u4e0b\u4f1a\u6709\u4e00\u4e2a\u5206\u533a\u76ee\u5f55\uff0c\u90a3\u4e48\u6211\u4eec\u662f\u4e0d\u662f\u76f4\u63a5\u53ef\u4ee5\u5728HDFS\u4e0a\u521b\u5efa\u4e00\u4e2a\u76ee\u5f55\uff0c\u518d\u628a\u6570\u636e\u52a0\u8f7d\u8fdb\u53bb\u5462\uff1f\n<div>\n<pre># \u521b\u5efa\u76ee\u5f55\u5e76\u4e0a\u4f20\u6587\u4ef6\n[hadoop@hadoop000 ~]$ hadoop fs -mkdir -p \/user\/hive\/warehouse\/order_partition\/event_month=2014-06\n[hadoop@hadoop000 ~]$ hadoop fs -put \/home\/hadoop\/order.txt \/user\/hive\/warehouse\/order_partition\/event_month=2014-06\n[hadoop@hadoop000 ~]$ hadoop fs -ls \/user\/hive\/warehouse\/order_partition\/\nFound 2 items\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 10:29 \/user\/hive\/warehouse\/order_partition\/event_month=2014-05\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 10:54 \/user\/hive\/warehouse\/order_partition\/event_month=2014-06\n# \u53d1\u73b0\u5206\u533a\u8868\u4e2d\u6ca1\u6709\u6570\u636e\nhive&gt; select * from order_partition where event_month=\\'2014-06\\';\nOK\nTime taken: 0.21 seconds\n# \u539f\u56e0\u662f\u6211\u4eec\u5c06\u6587\u4ef6\u4e0a\u4f20\u5230\u4e86hdfs\uff0chdfs\u662f\u6709\u4e86\u6570\u636e\uff0c\u4f46hive\u4e2d\u7684\u5143\u6570\u636e\u4e2d\u8fd8\u6ca1\u6709\uff0c\u6267\u884c\u5982\u4e0b\u547d\u4ee4\u66f4\u65b0\nhive&gt; msck repair table order_partition;\nOK\nPartitions not in metastore:    order_partition:event_month=2014-06\nRepair: Added partition to metastore order_partition:event_month=2014-06\nTime taken: 0.178 seconds, Fetched: 2 row(s)\n# \u518d\u6b21\u67e5\u770b\u5206\u533a\u6570\u636e\nhive&gt; select * from order_partition where event_month=\\'2014-06\\';\nOK\n10703007267488  2014-05-01 06:01:12.334+01      2014-06\n10101043505096  2014-05-01 07:28:12.342+01      2014-06\n10103043509747  2014-05-01 07:50:12.33+01       2014-06\n10103043501575  2014-05-01 09:27:12.33+01       2014-06\n10104043514061  2014-05-01 09:03:12.324+01      2014-06\nTime taken: 0.257 seconds, Fetched: 5 row(s)\n# \u67e5\u770b\u8868\u5206\u533a\nhive&gt; show partitions order_partition;\nOK\nevent_month=2014-05\nevent_month=2014-06\nTime taken: 0.164 seconds, Fetched: 2 row(s)\n<\/pre>\n<\/div>\n\u6ce8\u610f\uff1a&nbsp;msck repair table\u547d\u4ee4\u6267\u884c\u540eHive\u4f1a\u68c0\u6d4b\u5982\u679cHDFS\u76ee\u5f55\u4e0b\u5b58\u5728 \u4f46\u8868\u7684metastore\u4e2d\u4e0d\u5b58\u5728\u7684partition\u5143\u4fe1\u606f\uff0c\u66f4\u65b0\u5230metastore\u4e2d\u3002\u5982\u679c\u6709\u4e00\u5f20\u8868\u5df2\u7ecf\u5b58\u653e\u597d\u51e0\u5e74\u4e86\uff0c\u7528\u8fd9\u4e2a\u547d\u4ee4\u53bb\u6267\u884c\u7684\u8bdd \u534a\u5929\u90fd\u53cd\u5e94\u4e0d\u4e86\uff0c\u6240\u4ee5\u8fd9\u4e2a\u547d\u4ee4\u592a\u66b4\u529b\u4e86\uff0c\u751f\u4ea7\u4e2d\u4e0d\u63a8\u8350\u4f7f\u7528\u3002\u53ef\u4ee5\u7528Add partition\u6765\u6dfb\u52a0\u5206\u533a\u3002\n<div>\n<pre>[hadoop@hadoop000 ~]$ hadoop fs -mkdir -p \/user\/hive\/warehouse\/order_partition\/event_month=2014-07\n[hadoop@hadoop000 ~]$ hadoop fs -put \/home\/hadoop\/order.txt \/user\/hive\/warehouse\/order_partition\/event_month=2014-07\n[hadoop@hadoop000 ~]$ hadoop fs -ls \/user\/hive\/warehouse\/order_partition\/\nFound 3 items\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 10:29 \/user\/hive\/warehouse\/order_partition\/event_month=2014-05\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 10:54 \/user\/hive\/warehouse\/order_partition\/event_month=2014-06\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 11:09 \/user\/hive\/warehouse\/order_partition\/event_month=2014-07\n# \u67e5\u770b\u65b0\u7684\u5206\u533a\nhive&gt; select * from order_partition where event_month=\\'2014-07\\';\nOK\nTime taken: 0.188 seconds\n# \u6dfb\u52a0\u5206\u533a\nhive&gt; ALTER TABLE order_partition ADD IF NOT EXISTS PARTITION (event_month=\\'2014-07\\');\nOK\nTime taken: 0.22 seconds\n# \u518d\u6b21\u67e5\u770b\nhive&gt; select * from order_partition where event_month=\\'2014-07\\';\nOK\n10703007267488  2014-05-01 06:01:12.334+01      2014-07\n10101043505096  2014-05-01 07:28:12.342+01      2014-07\n10103043509747  2014-05-01 07:50:12.33+01       2014-07\n10103043501575  2014-05-01 09:27:12.33+01       2014-07\n10104043514061  2014-05-01 09:03:12.324+01      2014-07\nTime taken: 0.206 seconds, Fetched: 5 row(s)\nhive&gt; show partitions order_partition;\nOK\nevent_month=2014-05\nevent_month=2014-06\nevent_month=2014-07\nTime taken: 0.151 seconds, Fetched: 3 row(s)\n<\/pre>\n<\/div>\n\u591a\u7ea7\u5206\u533a\u8868\u6f14\u793a\uff1a\n<div>\n<pre># \u521b\u5efa\u591a\u7ea7\u5206\u533a\u8868\nhive&gt; create table order_mulit_partition(\n    &gt; ordernumber string,\n    &gt; eventtime string\n    &gt; )\n    &gt; partitioned by (event_month string,event_day string)\n    &gt; row format delimited fields terminated by \\'\\\\t\\';\nOK\nTime taken: 0.133 seconds\n# \u52a0\u8f7d\u6570\u636e\nhive&gt;  load data local inpath \\'\/home\/hadoop\/order.txt\\' overwrite into table order_mulit_partition partition (event_month=\\'2014-05\\',event_day=01);\n# \u67e5\u770b\u5206\u533a\nhive&gt; select * from order_mulit_partition where event_month=\\'2014-05\\' and event_day=\\'01\\';\nOK\n10703007267488  2014-05-01 06:01:12.334+01      2014-05 01\n10101043505096  2014-05-01 07:28:12.342+01      2014-05 01\n10103043509747  2014-05-01 07:50:12.33+01       2014-05 01\n10103043501575  2014-05-01 09:27:12.33+01       2014-05 01\n10104043514061  2014-05-01 09:03:12.324+01      2014-05 01\nhive&gt; show partitions order_mulit_partition;\nOK\nevent_month=2014-05\/event_day=01\nTime taken: 0.158 seconds, Fetched: 1 row(s)\n# HDFS\u4e2d\u591a\u7ea7\u5206\u533a\u7684\u76ee\u5f55\u7ed3\u6784\n[hadoop@hadoop000 ~]$ hadoop fs -ls \/user\/hive\/warehouse\/order_mulit_partition\/event_month=2014-05\nFound 1 items\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 11:17 \/user\/hive\/warehouse\/order_mulit_partition\/event_month=2014-05\/event_day=01\n<\/pre>\n<\/div>\n\u603b\u7ed3\uff1a\u5355\u7ea7\u5206\u533a\u5728HDFS\u4e0a\u6587\u4ef6\u76ee\u5f55\u4e3a\u5355\u7ea7\uff1b\u591a\u5206\u533a\u5728HDFS\u4e0a\u6587\u4ef6\u76ee\u5f55\u4e3a\u591a\u7ea7\u3002\n<h5>2.\u52a8\u6001\u5206\u533a:<\/h5>\n\u53c2\u8003\uff1a<a href=\"\/\/cwiki.apache.org\/confluence\/display\/Hive\/DynamicPartitions\\&quot;\" target=\"\\&quot;_blank\\&quot;\" rel=\"\\&quot;nofollow noopener\" noopener=\"\">\u5b98\u65b9\u6587\u6863<\/a>\n<ul>\n \t<li>\u5148\u770b\u770b\u5b98\u65b9\u4e3a\u6211\u4eec\u89e3\u91ca\u7684\u4ec0\u4e48\u662f\u52a8\u6001\u5206\u533a\uff1a\nStatic Partition (SP) columns&nbsp;\u9759\u6001\u5206\u533a\uff1b\nDynamic Partition (DP) columns \u52a8\u6001\u5206\u533a\u3002<\/li>\n<\/ul>\n<div>\n<pre>DP columns are specified the same way as it is for SP columns \u2013 in the partition clause. The only difference is that DP columns do not have values, while SP columns do. In the partition clause, we need to specify all partitioning columns, even if all of them are DP columns.\nIn INSERT ... SELECT ... queries, the dynamic partition columns must be specified last among the columns in the SELECT statement and in the same order in which they appear in the PARTITION() clause.\n<\/pre>\n<\/div>\n\u7b80\u5355\u603b\u7ed3\u4e0b\u533a\u522b\uff1a\n<ul>\n \t<li>1.DP\u5217\u7684\u6307\u5b9a\u65b9\u5f0f\u4e0eSP\u5217\u76f8\u540c &#8211; \u5728\u5206\u533a\u5b50\u53e5\u4e2d\uff08 Partition\u5173\u952e\u5b57\u540e\u9762\uff09\uff0c\u552f\u4e00\u7684\u533a\u522b\u662f\uff0cDP\u5217\u6ca1\u6709\u503c\uff0c\u800cSP\u5217\u6709\u503c\uff08 Partition\u5173\u952e\u5b57\u540e\u9762\u53ea\u6709key\u6ca1\u6709value\uff09<\/li>\n \t<li>2.\u5728INSERT \u2026 SELECT \u2026\u67e5\u8be2\u4e2d\uff0c\u5fc5\u987b\u5728SELECT\u8bed\u53e5\u4e2d\u7684\u5217\u4e2d\u6700\u540e\u6307\u5b9a\u52a8\u6001\u5206\u533a\u5217\uff0c\u5e76\u6309PARTITION\uff08\uff09\u5b50\u53e5\u4e2d\u51fa\u73b0\u7684\u987a\u5e8f\u8fdb\u884c\u6392\u5217<\/li>\n \t<li>3.\u6240\u6709DP\u5217 &#8211; \u53ea\u5141\u8bb8\u5728\u975e\u4e25\u683c\u6a21\u5f0f\u4e0b\u4f7f\u7528\u3002 \u5728\u4e25\u683c\u6a21\u5f0f\u4e0b\uff0c\u6211\u4eec\u5e94\u8be5\u629b\u51fa\u4e00\u4e2a\u9519\u8bef<\/li>\n \t<li>4.\u5982\u679c\u52a8\u6001\u5206\u533a\u548c\u9759\u6001\u5206\u533a\u4e00\u8d77\u4f7f\u7528\uff0c\u5fc5\u987b\u662f\u52a8\u6001\u5206\u533a\u7684\u5b57\u6bb5\u5728\u524d\uff0c\u9759\u6001\u5206\u533a\u7684\u5b57\u6bb5\u5728\u540e\u3002<\/li>\n<\/ul>\n\u4e0b\u9762\u4e3e\u51e0\u4e2a\u4f8b\u5b50\u8fdb\u884c\u6f14\u793a\uff1a\n\u6ce8\u610f\uff1a\u4e3a\u4e86\u6f14\u793a\u52a8\u6001\u5206\u533a\u4e0e\u9759\u6001\u5206\u533a\u7684\u533a\u522b \u5e76\u4e14\u5bf9\u6bd4\u51fa\u9759\u6001\u5206\u533a\u7684\u7e41\u7410\uff0c\u6211\u4eec\u5148\u5bf9\u9759\u6001\u5206\u533a\u8fdb\u884c\u64cd\u4f5c \u4e4b\u540e\u518d\u6f14\u793a\u52a8\u6001\u5206\u533a\u3002\n<div>\n<pre># \u521b\u5efa\u5458\u5de5\u9759\u6001\u5206\u533a\u8868\nhive&gt; CREATE TABLE emp_static_partition (\n    &gt; empno int,\n    &gt; ename string,\n    &gt; job string,\n    &gt; mgr int,\n    &gt; hiredate string,\n    &gt; salary double,\n    &gt; comm double\n    &gt; )\n    &gt; PARTITIONED BY (deptno int)\n    &gt; ROW FORMAT DELIMITED FIELDS TERMINATED BY \\'\\\\t\\';\nOK\nTime taken: 0.198 seconds\n# \u5c06emp\u8868\u91cc\u7684\u6570\u636e\u63d2\u5165\u9759\u6001\u5206\u533a\nhive&gt; select * from emp;\nOK\n7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    20\n7499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   30\n7521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   30\n7566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    20\n7654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  30\n7698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    30\n7782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    10\n7788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    20\n7839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    10\n7844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     30\n7876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    20\n7900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    30\n7902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    20\n7934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10\nTime taken: 0.164 seconds, Fetched: 14 row(s)\n# \u6bcf\u4e2a\u5206\u533a\u90fd\u8981\u5199\u4e00\u6761insert\u8bed\u53e5\nhive&gt; insert into table emp_static_partition partition(deptno=10)\n    &gt; select empno,ename ,job ,mgr ,hiredate ,salary ,comm from emp where deptno=10;\nQuery ID = hadoop_20180702100505_f0566585-06b2-4c53-910a-b6a58791fc2d\nTotal jobs = 3\nLaunching Job 1 out of 3\n...\nOK\nTime taken: 15.265 seconds\nhive&gt; insert into table emp_static_partition partition(deptno=20)\n    &gt; select empno,ename ,job ,mgr ,hiredate ,salary ,comm from emp where deptno=20;\nQuery ID = hadoop_20180702100505_f0566585-06b2-4c53-910a-b6a58791fc2d\nTotal jobs = 3\nLaunching Job 1 out of 3\n...\nOK\nTime taken: 18.527 seconds\nhive&gt; insert into table emp_static_partition partition(deptno=30)\n    &gt; select empno,ename ,job ,mgr ,hiredate ,salary ,comm from emp where deptno=30;\nQuery ID = hadoop_20180702100505_f0566585-06b2-4c53-910a-b6a58791fc2d\nTotal jobs = 3\nLaunching Job 1 out of 3\n...\nOK\nTime taken: 14.062 seconds\n# \u67e5\u770b\u5404\u5206\u533a\nhive&gt; select * from emp_static_partition  where deptno=\\'10\\';\nOK\n7782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    10\n7839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    10\n7934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10\nTime taken: 0.219 seconds, Fetched: 3 row(s)\nhive&gt; select * from emp_static_partition  where deptno=\\'20\\';\nOK\n7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    20\n7566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    20\n7788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    20\n7876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    20\n7902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    20\nTime taken: 0.197 seconds, Fetched: 5 row(s)\nhive&gt; select * from emp_static_partition  where deptno=\\'30\\';\nOK\n7499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   30\n7521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   30\n7654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  30\n7698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    30\n7844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     30\n7900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    30\nTime taken: 0.181 seconds, Fetched: 6 row(s)\n<\/pre>\n<\/div>\n\u9759\u6001\u5206\u533a\u8868\u6709\u4e00\u4e2a\u975e\u5e38\u81f4\u547d\u7684\u7f3a\u70b9\uff0c\u6bcf\u6b21\u5206\u533a\u7684\u63d2\u5165\u90fd\u8981\u5355\u72ec\u5199insert\u8bed\u53e5\u3002\n\u4e0b\u9762\u5229\u7528\u52a8\u6001\u5206\u533a\u8fdb\u884c\u6f14\u793a\n\u6f14\u793a\u524d\u5148\u8fdb\u884c\u8bbe\u7f6e\uff1ahive \u4e2d\u9ed8\u8ba4\u662f\u9759\u6001\u5206\u533a\uff0c\u60f3\u8981\u4f7f\u7528\u52a8\u6001\u5206\u533a\uff0c\u9700\u8981\u8bbe\u7f6e\u5982\u4e0b\u53c2\u6570\uff0c\u53ef\u4ee5\u4f7f\u7528\u4e34\u65f6\u8bbe\u7f6e\uff0c\u4f60\u4e5f\u53ef\u4ee5\u5199\u5728\u914d\u7f6e\u6587\u4ef6\uff08hive-site.xml\uff09\u91cc\uff0c\u6c38\u4e45\u751f\u6548\u3002\u4e34\u65f6\u914d\u7f6e\u5982\u4e0b\n<div>\n<pre>set hive.exec.dynamic.partition=true;   --\u5f00\u542f\u52a8\u6001\u5206\u533a \u9ed8\u8ba4\u4e3afalse\uff0c\u4e0d\u5f00\u542f\nset hive.exec.dynamic.partition.mode=nonstrict; --\u6307\u5b9a\u52a8\u6001\u5206\u533a\u6a21\u5f0f\uff0c\u9ed8\u8ba4\u4e3astrict\uff0c\u5373\u5fc5\u987b\u6307\u5b9a\u81f3\u5c11\u4e00\u4e2a\u5206\u533a\u4e3a\u9759\u6001\u5206\u533a\uff0cnonstrict\u6a21\u5f0f\u8868\u793a\u5141\u8bb8\u6240\u6709\u7684\u5206\u533a\u5b57\u6bb5\u90fd\u53ef\u4ee5\u4f7f\u7528\u52a8\u6001\u5206\u533a<\/pre>\n<\/div>\n<div>\n<pre># \u521b\u5efa\u5458\u5de5\u52a8\u6001\u5206\u533a\u8868\uff0c\u5206\u533a\u5b57\u6bb5\u4e3adeptno\nhive&gt; CREATE TABLE emp_dynamic_partition (\n    &gt; empno int,\n    &gt; ename string,\n    &gt; job string,\n    &gt; mgr int,\n    &gt; hiredate string,\n    &gt; salary double,\n    &gt; comm double\n    &gt; )\n    &gt; PARTITIONED BY (deptno int)\n    &gt; ROW FORMAT DELIMITED FIELDS TERMINATED BY \\'\\\\t\\';\nOK\nTime taken: 0.165 seconds\n# insert\u4e00\u6761\u8bed\u53e5\u641e\u5b9a\nhive&gt; insert into table emp_dynamic_partition partition(deptno)\n    &gt; select empno,ename ,job ,mgr ,hiredate ,salary ,comm, deptno from emp;\nQuery ID = hadoop_20180702100505_f0566585-06b2-4c53-910a-b6a58791fc2d\nTotal jobs = 3\nLaunching Job 1 out of 3\n...\nOK\nTime taken: 17.982 seconds\n# \u67e5\u770b\u5404\u5206\u533a\nhive&gt; show partitions emp_dynamic_partition;\nOK\ndeptno=10\ndeptno=20\ndeptno=30\nTime taken: 0.176 seconds, Fetched: 3 row(s)\nhive&gt; select * from emp_dynamic_partition where deptno=\\'10\\';\nOK\n7782    CLARK   MANAGER 7839    1981-6-9        2450.0  NULL    10\n7839    KING    PRESIDENT       NULL    1981-11-17      5000.0  NULL    10\n7934    MILLER  CLERK   7782    1982-1-23       1300.0  NULL    10\nTime taken: 2.662 seconds, Fetched: 3 row(s)\nhive&gt; select * from emp_dynamic_partition where deptno=\\'20\\';\nOK\n7369    SMITH   CLERK   7902    1980-12-17      800.0   NULL    20\n7566    JONES   MANAGER 7839    1981-4-2        2975.0  NULL    20\n7788    SCOTT   ANALYST 7566    1987-4-19       3000.0  NULL    20\n7876    ADAMS   CLERK   7788    1987-5-23       1100.0  NULL    20\n7902    FORD    ANALYST 7566    1981-12-3       3000.0  NULL    20\nTime taken: 0.178 seconds, Fetched: 5 row(s)\nhive&gt; select * from emp_dynamic_partition where deptno=\\'30\\';\nOK\n7499    ALLEN   SALESMAN        7698    1981-2-20       1600.0  300.0   30\n7521    WARD    SALESMAN        7698    1981-2-22       1250.0  500.0   30\n7654    MARTIN  SALESMAN        7698    1981-9-28       1250.0  1400.0  30\n7698    BLAKE   MANAGER 7839    1981-5-1        2850.0  NULL    30\n7844    TURNER  SALESMAN        7698    1981-9-8        1500.0  0.0     30\n7900    JAMES   CLERK   7698    1981-12-3       950.0   NULL    30\nTime taken: 0.146 seconds, Fetched: 6 row(s)\n<\/pre>\n<\/div>\n<ul>\n \t<li>\u67e5\u770bHDFS\u4e0a\u6587\u4ef6\u76ee\u5f55\u7ed3\u6784<\/li>\n<\/ul>\n<div>\n<pre>[hadoop@hadoop000 ~]$ hadoop fs -ls \/user\/hive\/warehouse\nFound 6 items\ndrwxr-xr-x   - hadoop supergroup          0 2018-06-24 15:38 \/user\/hive\/warehouse\/emp\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 13:55 \/user\/hive\/warehouse\/emp_dynamic_partition\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 13:50 \/user\/hive\/warehouse\/emp_static_partition\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 11:17 \/user\/hive\/warehouse\/order_mulit_partition\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 11:09 \/user\/hive\/warehouse\/order_partition\ndrwxr-xr-x   - hadoop supergroup          0 2018-06-24 15:35 \/user\/hive\/warehouse\/stu\n[hadoop@hadoop000 ~]$ hadoop fs -ls \/user\/hive\/warehouse\/emp_static_partition\nFound 3 items\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 13:47 \/user\/hive\/warehouse\/emp_static_partition\/deptno=10\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 13:50 \/user\/hive\/warehouse\/emp_static_partition\/deptno=20\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 13:51 \/user\/hive\/warehouse\/emp_static_partition\/deptno=30\n[hadoop@hadoop000 ~]$ hadoop fs -ls \/user\/hive\/warehouse\/emp_dynamic_partition\nFound 3 items\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 13:55 \/user\/hive\/warehouse\/emp_dynamic_partition\/deptno=10\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 13:55 \/user\/hive\/warehouse\/emp_dynamic_partition\/deptno=20\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 13:55 \/user\/hive\/warehouse\/emp_dynamic_partition\/deptno=30\n<\/pre>\n<\/div>\n\u8865\u5145\uff1a\u4e24\u79cd\u5206\u533a\u8fd8\u53ef\u4ee5\u6df7\u5408\u4f7f\u7528 \u4e0b\u9762\u505a\u7b80\u8981\u4e86\u89e3\uff1a\n<ul>\n \t<li>mixed SP &amp; DP columns\uff08\u6df7\u5408\u4f7f\u7528\u52a8\u6001\u5206\u533a\u548c\u9759\u6001\u5206\u533a\uff09<\/li>\n<\/ul>\n<div>\n<pre>hive&gt; create table student(\n    &gt; id int,\n    &gt; name string,\n    &gt; tel string,\n    &gt; age int\n    &gt; )\n    &gt; row format delimited fields terminated by \\'\\\\t\\';\nOK\nTime taken: 0.125 seconds\nhive&gt; insert into student values(1,\\'zhangsan\\',\\'18311111111\\',20),(2,\\'lisi\\',\\'18222222222\\',30),(3,\\'wangwu\\',\\'15733333333\\',40);\nQuery ID = hadoop_20180702100505_f0566585-06b2-4c53-910a-b6a58791fc2d\nTotal jobs = 3\nLaunching Job 1 out of 3\n...\nOK\nTime taken: 15.375 seconds\nhive&gt; select * from student;\nOK\n1       zhangsan        18311111111     20\n2       lisi    18222222222     30\n3       wangwu  15733333333     40\nTime taken: 0.106 seconds, Fetched: 3 row(s)\n# \u521b\u5efa\u6df7\u5408\u5206\u533a\u8868\nhive&gt; create table stu_mixed_partition(\n    &gt; id int,\n    &gt; name string,\n    &gt; tel string\n    &gt; )\n    &gt; partitioned by (ds string,age int)\n    &gt; row format delimited fields terminated by \\'\\\\t\\';\nOK\nTime taken: 0.171 seconds\n# \u63d2\u5165\u6570\u636e\nhive&gt; insert into stu_mixed_partition partition(ds=\\'2010-03-03\\',age)\n    &gt; select id,name,tel,age from student;\nQuery ID = hadoop_20180702100505_f0566585-06b2-4c53-910a-b6a58791fc2d\nTotal jobs = 3\nLaunching Job 1 out of 3\n...\nOK\nTime taken: 18.887 seconds\n# \u67e5\u770b\u5206\u533a\nhive&gt; show partitions stu_mixed_partition;\nOK\nds=2010-03-03\/age=20\nds=2010-03-03\/age=30\nds=2010-03-03\/age=40\nhive&gt; select * from stu_mixed_partition where ds=\\'2010-03-03\\' and age=20;\nOK\n1       zhangsan        18311111111     2010-03-03      20\nTime taken: 0.184 seconds, Fetched: 1 row(s)\nhive&gt; select * from stu_mixed_partition where ds=\\'2010-03-03\\' and age=30;\nOK\n2       lisi    18222222222     2010-03-03      30\nTime taken: 0.188 seconds, Fetched: 1 row(s)\nhive&gt; select * from stu_mixed_partition where ds=\\'2010-03-03\\' and age=40;\nOK\n3       wangwu  15733333333     2010-03-03      40\nTime taken: 0.186 seconds, Fetched: 1 row(s)\n# \u67e5\u770bHDFS\u76ee\u5f55\n[hadoop@oradb3 ~]$  hadoop fs -ls \/user\/hive\/warehouse\/stu_mixed_partition\/ds=2010-03-03\nFound 3 items\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 14:10 \/user\/hive\/warehouse\/stu_mixed_partition\/ds=2010-03-03\/age=20\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 14:10 \/user\/hive\/warehouse\/stu_mixed_partition\/ds=2010-03-03\/age=30\ndrwxr-xr-x   - hadoop supergroup          0 2018-07-02 14:10 \/user\/hive\/warehouse\/stu_mixed_partition\/ds=2010-03-03\/age=40<\/pre>\n<\/div>\n<\/article>\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"<p>\u5982\u679c\u4e00\u4e2a\u8868\u4e2d\u6570\u636e\u5f88\u591a\uff0c\u6211\u4eec\u67e5\u8be2\u65f6\u5c31\u5f88\u6162\uff0c\u8017\u8d39\u5927\u91cf\u65f6\u95f4\uff0c\u5982\u679c\u8981\u67e5\u8be2\u5176\u4e2d\u90e8\u5206\u6570\u636e\u8be5\u600e\u4e48\u529e\u5462\uff0c\u8fd9\u65f6\u6211\u4eec\u5f15\u5165\u5206\u533a\u7684\u6982\u5ff5 &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"http:\/\/www.weijing.co\/?p=1824\"> <span class=\"screen-reader-text\">Hive\u5206\u533a\u8868\u7b80\u4ecb<\/span> \u67e5\u770b\u5168\u6587 &raquo;<\/a><\/p>\n","protected":false},"author":4,"featured_media":5706,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":""},"categories":[137,118],"tags":[],"uagb_featured_image_src":{"full":["http:\/\/www.weijing.co\/wp-content\/uploads\/2020\/07\/Hive\u5206\u533a\u8868\u7b80\u4ecb.jpg",600,400,false],"thumbnail":["http:\/\/www.weijing.co\/wp-content\/uploads\/2020\/07\/Hive\u5206\u533a\u8868\u7b80\u4ecb-150x150.jpg",150,150,true],"medium":["http:\/\/www.weijing.co\/wp-content\/uploads\/2020\/07\/Hive\u5206\u533a\u8868\u7b80\u4ecb-300x200.jpg",300,200,true],"medium_large":["http:\/\/www.weijing.co\/wp-content\/uploads\/2020\/07\/Hive\u5206\u533a\u8868\u7b80\u4ecb.jpg",600,400,false],"large":["http:\/\/www.weijing.co\/wp-content\/uploads\/2020\/07\/Hive\u5206\u533a\u8868\u7b80\u4ecb.jpg",600,400,false],"1536x1536":["http:\/\/www.weijing.co\/wp-content\/uploads\/2020\/07\/Hive\u5206\u533a\u8868\u7b80\u4ecb.jpg",600,400,false],"2048x2048":["http:\/\/www.weijing.co\/wp-content\/uploads\/2020\/07\/Hive\u5206\u533a\u8868\u7b80\u4ecb.jpg",600,400,false],"mailpoet_newsletter_max":["http:\/\/www.weijing.co\/wp-content\/uploads\/2020\/07\/Hive\u5206\u533a\u8868\u7b80\u4ecb.jpg",600,400,false],"woocommerce_thumbnail":["http:\/\/www.weijing.co\/wp-content\/uploads\/2020\/07\/Hive\u5206\u533a\u8868\u7b80\u4ecb-50x50.jpg",50,50,true],"woocommerce_single":["http:\/\/www.weijing.co\/wp-content\/uploads\/2020\/07\/Hive\u5206\u533a\u8868\u7b80\u4ecb-80x53.jpg",80,53,true],"woocommerce_gallery_thumbnail":["http:\/\/www.weijing.co\/wp-content\/uploads\/2020\/07\/Hive\u5206\u533a\u8868\u7b80\u4ecb-100x100.jpg",100,100,true],"shop_catalog":["http:\/\/www.weijing.co\/wp-content\/uploads\/2020\/07\/Hive\u5206\u533a\u8868\u7b80\u4ecb-50x50.jpg",50,50,true],"shop_single":["http:\/\/www.weijing.co\/wp-content\/uploads\/2020\/07\/Hive\u5206\u533a\u8868\u7b80\u4ecb-80x53.jpg",80,53,true],"shop_thumbnail":["http:\/\/www.weijing.co\/wp-content\/uploads\/2020\/07\/Hive\u5206\u533a\u8868\u7b80\u4ecb-100x100.jpg",100,100,true]},"uagb_author_info":{"display_name":"weijing","author_link":"http:\/\/www.weijing.co\/?author=4"},"uagb_comment_info":0,"uagb_excerpt":"\u5982\u679c\u4e00\u4e2a\u8868\u4e2d\u6570\u636e\u5f88\u591a\uff0c\u6211\u4eec\u67e5\u8be2\u65f6\u5c31\u5f88\u6162\uff0c\u8017\u8d39\u5927\u91cf\u65f6\u95f4\uff0c\u5982\u679c\u8981\u67e5\u8be2\u5176\u4e2d\u90e8\u5206\u6570\u636e\u8be5\u600e\u4e48\u529e\u5462\uff0c\u8fd9\u65f6\u6211\u4eec\u5f15\u5165\u5206\u533a\u7684\u6982\u5ff5&hellip;","_links":{"self":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1824"}],"collection":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1824"}],"version-history":[{"count":4,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1824\/revisions"}],"predecessor-version":[{"id":5724,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1824\/revisions\/5724"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/media\/5706"}],"wp:attachment":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1824"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1824"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1824"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}