hive中partition如何使用

时间:2022-05-16 07:01:09

1.背景

1、在Hive Select查询中一般会扫描整个表内容,会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据,因此建表时引入了partition概念。

2、分区表指的是在创建表时指定的partition的分区空间。

3、如果需要创建有分区的表,需要在create表的时候调用可选参数partitioned by,详见表创建的语法结构。

2.细节

1.一个表可以拥有一个或者多个分区,每个分区以文件夹的形式单独存在表文件夹的目录下。

show partitions stage_shake_prize_yyyymmdd;

结果:

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAhQAAABgCAIAAADKNHxMAAAQPUlEQVR4nO2dvZWzOhCGOYctxOG24A4cOHcFDm5sF7BtkH2FuBa34huAQZpfSfwYw3vOk+xiYDQazUgjIVX//v3777//fg6/AAAAQCIVggcAAIBcEDwAAABkg+ABAAAgGwQPAAAA2dDgUT/+qte1dm471a+/6nWrz58vgEtaibbHmDr6pvr9RkbZZHNbcdXAckqV1hyLrn4SBI8v5Vg//6rnRf+BUUdj7l0nbonWBYIHoErbTfD4JrZXop/D7zh3+WWudnsl2m7wAAUgeIzlWD9n0RGCx6T3rpMvKxGCBwjYWvA41s+/6vVXUbnbYWlHfQ+e1dzofw6/P/dr/M/wdtYAmlv1+qsep6KSBALHT/ZKpBVK9Uf1I8lPvX8WPJwU7XypNGWeL10pWp2EhRr+Q+idkVNHhfd6P/DLq9dRef36JTL1zK6y35gWO59NGjKT4NFpIHx+ocxODRo26VrO/SrUUWQeRTLbPme+q50qOpn733duwb4qqct2s6p5fLq3JAaPoPqp92d6JAWO/UXcw4p/IHagWiPLVsqpju461o9LLINeovaNfd201dwcf9QgcayfSRFOfG9gBKf6SYUcpOp9R/ciHskS+tpa3RXfq+sqwXKsOhpTv16JEvTcFaF15cwh2hY7h03aModitDXCvXCRzI7F+jZpWx15UTjwKpaZKLmvxPbhs109X6rXrXre6nOrhFv1OJHQol7tNTnUWjy2sK8OKirozUyMHDxom+eDJslE6ofU9iLnElqM4oU7G80Z1EcugGKWSGgAg5zNrRcjCCSpo0j2XtNlkyJQA+ItavngYepK03NfBLOOxtRvaomkR1GbjEudarH5Mqe2MvFRvSUIkWOUzI7F+jZpW536g9EyBzII9TvD1UAVw8/OlyB46FeFXinzk/rVSJhVjjz0ToFtIpqVK8/R8z+5azbeozyp+VklElt41DhbGdrnB12S1OARhUAz0y30Pszu2+LBw9aVbzlWHY2p39QSSXo2g0eWxebJnNrKRNtodd4IkWOkzI7F+japWI4j4Tg9Cz6HZSwnv0oiwTAcGYKHelXTQPci++q6mDR4RG2YdB9YFu/FctPkpVZz4rj5ZalEYnvoY0Z/9X6tntf6SS3DRggVxDp5vn71wUPVVZLlzDDnkVIiT89x2kqe/vEsdjqbdGUOL5mzd7kyOxY7RfCQosJIPRs+Z7ar44MHn+EIg4d6dV1MGzwkz2s/h1OQthJuN5IPySOPw6nu57ua409zqx6nYdrQk8Tpx9EZuc2PPKw6GlO/TolsPdOJ3PTRwCiZE5L+usxvnbMs01iZZx95BF2NqWSOHiu2zTmuIngcfn+mDx59UZubkB93h6KFE+Yxdl47KpGQXQ3kbFcPn+pnb0DX+n4t7ceF7yKdXGYiKwwejq4yUzETT4FoJbL17OTWc5JUeTL7NmnYxhCwu5GNtOatREhn7mFs8FCd4Gg9d9pTnjPD1VHBg1ldtPDEvhqb0yonzMcEj9+f5tYmeeQCG12M4qW696sxj+eUSFpBFPcQ41iSHNvIe8n8W/Rnv44lI3goqwnT66jgXl9Xpp7dudai+rVLZOvZ+d7Ctdh5bNKxDT6VyOd1i2S2LXZk8LAixBg9B7VvW/uUV0cFj7gGube0rwZKHqu00WQGDzrMl3LQVlQ01i+P+0gwyhFn5pejQvGcL0s356xdGbBSve9VfVnBg5b6LXlKHRXfq+sqM4+f2WlKuVcska1nKeEuJIJki53PJk2ZSewU8niFMjsWa9ukbTnyVaaBMj0PSlD6AZNfHRk8QlU/Ttz12Vd/Dr9DR3ZVwQNMxUa/bN8S4SK6DrbcfEd8scXaY5f5rn4QMZ21LAgec/HFTXEniH7BTehtly+2WDsLOt/Vj8G/YfwACB5z8cVNcS+wkcfIlX5fzrdaLJmKW+zqZxjzvdTEIHjMxbc2xX0RfoqRnkTWPk0oytcvhC/z+izWk1mepuqgu61MdxW0IHgAAADIBsEDAABANggeAAAAskHwAAAAkA2CBwAAgGwQPAAAAGSD4AEAACAbMXio5wnL+25iNTQAAOwMFjyE87P0nVN3vJcDAADsGRI8Us6pjndgXePHtAAAAOYlDh7qUaPyDsNr3GwSAADA/MTBQzwnMkhkvYNHwlF0AAAAtkscPOT9h4fzI7vgsboN1AAAACxKQfDgp4ABAADYFwnBQ0hbreIoEgAAAJ8iccK8iyiYMAcAAPDDluqeahYS6scwyBCW6iJ+AADA/mAfCZKvN+JElviRID71AACAvSFtT9Kd5CxMjGvbkyB+AADArsDGiAAAALJB8AAAAJANggcAAIBsEDwAAABkg+ABAAAgGwQPAAAA2SB4AAAAyAbBAwAAQDYIHgAAALJB8AAAAJDNWoLHFAdMnerXn3QeifSzHe2nYh/7uLA2UEcAbISFg4fqyNYbPNrNH9UjsNqXtkivfm8URje6DxUi3649Ofx/hPwKcZv9Ym2MBXVUILNRfQB8jC0Fj0RyHFNzC6RqnUgoZLSDff2QPMjzUjc3uf1be9rbT87QXrij/lhtLMfW68iWmcR7stE1AOsAwSOHuBkzmUNvcqyf7/YvOyZLDPPJjOCox/zCfn/w+Po6kmSO2oideATgM/CTBG/1uRsy1/f34DoyXHVA/Tb64Ad9W4oG6X98wP5uikGKIMN3uGN8lkYoc0zR+YnMWbQugIdA0TFFfU9J2pQnD6pT+rzyW0xtRBvy0zSLdMpkYlIFdaSPS+JzOeWDDxA8wMoQgkf1vNXn1l5v1eMUOQjhqCjSy/sjP44bpD3ysO9NQLyLnpg7Ua+W9CXPl+p1rcVD4AXHdKyfyb1U48l9ceRHSW9xtHGqg9ppK+UtOT+4nueIUEe5dcRkPrAz1pC2AqtEOMO8tfKhrzRkYAXXH/aSYl/zK7UZJ3jQe3MbjOSYWBIgfHI4F+pOPnc/7i6FieluxBad9z5gOKZwQNYrKv3JB3rUI9MGvWRqgxG7SNp9tlMxqKOUOuIyh0Vujp2SMewA60MIHq0RD1Gh/6foLIKGkZAFTp/z8DprIoJj4s8p7dW2ToS7j953HNId0ztFQ3qX7cPTn+wOO6iqc7URXyUGYGV1UEcpdSTJTP5f0AoAWITs4CFlvVcfPCI3VOSY+NxPq43mEvmL1JSIUDqq8NQnK11aeYVugjb47NRwNaw+M6uDOkqpI1Hm/tWhnrFaF6yPfYw8VMeUlhKR50KFTxbkNWOJ+fShI5/4ZD4J4UniumkqKvt931c4X8xUDOrIryNFZt5GiuaWAJiZnOAhNacwW73K4MFkJrORac8Um66cqVfyD+5KHqpJ98napwn974Uy2tpQPpLgbv1x8j4fQR15daTKPNHCBABmJit4yMtC+raRFACU5jRb8IjzBs2tel7rZ7Jj6tatKu3WWWFpFpmsLyKS+082u7RGqsTURjQl3q/ZFcaaIxIpqCNP5m7lIVl8hZkPsDIyg8eh7zH9cetPDQDSxgyjgkckUs/gPft1wNXj1PXj0hzTcKOaMAm/Toh9gfxpCysjk9Z/8sEedjizEaY2iEiSrmxnjTpKqCNXZvoDrNMF62MtGyOCydAWjE4F/SYDALBHEDw2xvxfIztfLQAAdgGCB8gBXzsDAA6/P98QPNTdrZEOXhK6fwzqCIB9s/7gAQAAYHUgeAAAAMgGwQMAAEA2CB4AAACyQfAAAACQDYIHAGAfNLe8j1ubGxYKGpQFD2FL0fWCj9r2xTpPZZ8MdXsu6MrG2tFHJDiS6+PCr5I4eKgnjYvn5CzskUu/nV5v8LD3TRpzr3lgeHhK+QaPqFvKIcaHvQcbcyU+odCeVx083jpZ3ekjZadeuofP7xtt5DH/LhfZbCx4RDs/Ztqoea+4kWV8HJ6+EfIGWDZ4lL9oY8Gj20K4zu7gL8AYb7ZCT7gWEDw+w5hdhO172TkTod6mO/B1vSB4LK+rY/18B4wVBg/xFIDFbt8uucFDz4d0R8t1P6jv4hGbRYkaNZkWNKE4hxAJRoJH97Tw7apUb0cc/EDw7+bRGjLMievHGeXey5xLUJWCv8s9TTY8189Ol0n1G1WTeOqfUIMJtcD2R+FlLEwP6vjBQ9FGij3runrXb1AoKkNGSlO9vbjjWBA8+lMpB80QsUXbME+WC/5vHlyWYhtFZwvtgNKRB4/G50v1ulXP9yEQz1v1OMU/i+sge0BgiHSqia2EsoUvav0s73orUtENncQ+SOe7c8pC/M75Ur2udaJC3HvJMXxR2kroaeacCUjcyrF+6Efp8eLQ8wFPdXzOEj3+KDqiSq8FukU8KaMt8wic4OFau9nEdF15Nmm+19FVqT0zyfODR9gniDWj24ZiuqQ/ZLj+VNvYYnZ3AqYNHl0lDVl48XQpuY5dkof5pFX37UeIHI5UNA7JhpjfUwunJfpuV+I5HP69wSqRVhU0srJecKLwprt06te6V6jZ8Gl2LcjHwUbBdZ4s1hhtKKVOfrJ6zrxvz4auwn8uPfLg5xC3VmraRnBOcFA0wQBkedJtY+4zcr6TiYOHfgqh4HYzz8FOzxHH1tMaYiNEDleqaQ7H5fRq6b3/IT942Pf2GQAmbXRKXXPKyby/swdCe/Pq1xhXiW04+L1ZC+4sjiHzBJVICUd4jrXr9myOQTO1wbzq3DNepcEjw79H3cFWUW0tt2qJC2XNWCTbBqY9JJYNHm6e1yJhmC9mcsNLVnZYkGre4NFc2CAgOXhY94Y9x24Uotv9VHMeuZqULSc2sOTgEVUr/8/ycx4p1m6m7PV24WtDfW+KrkYzQ/BQbaO/er+2596/O1V0taFj/65tIHhIfHLkkYndU1MH8r1jZeN9X6q5gof0oUxyXtW+l2upbR6W4y5xH3RFv6eZWUceiQ5x2q8Q3ODh2MlCIw9TM98RPHTbONX92pzm2H1Afo77VcbD020DaSuJxYJHbpKKo4nEnaMcPMRuuC1VWvAoyRHL2Wfy5M6gqdWa93JhrOAxaiYwbthO/Vp9N2H0k5w8ZPfSqWZLZlvPWcX36lcptfgbs5+buVDb1LOsq8/MeRTZxrF+tqnXfiByre/Xwg6fXqGYMBdZLngI02K5KHYZTQL3mWghePwK3XZTqqTgUbY6JX6vaJ3D5ITo7JR7u7tI1k5bYZwl9v2qzGom1a8w02uuttK/ZNQXFDW3Nn0RpbwNmaMazDROe9CWYu26nzV05WjDfq+tq0gbS6+2Um/xbYMmaVM7Tym20T8BS3UZmcGjb2YR+sIqahbuGnOPaG6jNwiysP0YNQnRx6VJNd/Ig71XMm6rR2zdG02JEyWHNVjQHiL9539DIFcfN63oklsLQ3kfp3dv9Jgs8+/gdCYMHinasBWiXEqwSeu9jq6K7Vn+eCWtt+5mlnTboIMnsatkjOR828CEhwp21QVgld9Fg8nA9iSzgOABdg/7GgZsjeKc+So3N1oJCB5gz8z2FQhYG/ZKChEy3QJiEDwAAPsg73AncUIIDCB4AAAAyAbBAwAAQDZfHzyqV2XwcfEAAGCTIHgAAADIBsEDAABANv8D6ZNL8SE5G4gAAAAASUVORK5CYII=" alt="" />

2、表和列名不区分大小写。

3、分区是以字段的形式在表结构中存在,通过describe table命令可以查看到字段存在,但是该字段不存放实际的数据内容,仅仅是分区的表示

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAY8AAAEJCAIAAADjETx1AAAVq0lEQVR4nO2dzXGzPBRGNYMLyTIlvOmAhfepwBXYBaQN9+Ja3Mi38LcAg/4lEBhdODNnkxgIQZeDEFiPOn19AwDUj9p8DwAAcsBWACADbAUAMsBWACADbDWDtnn9qde1OWcsdv/Zem+PRfP4U69Lk1gsswUXXxeKwFYzwFb1UrGtfprnn3r+bn6I5IKt1gNbbUCerTYBW5WCrdYDW20Attoxqnl0R7Dr3/6p1596tOMS59++03u/9p/afWBtRW/3+PyrxgWsMtLX/Wtu40dOzbWNtmOJfU5uObFuhOBmvQsobPVx3pXz0zy97ZtqQaNcrcVi68bqajx3LOq0ar2o5mEe/dvFOMeGxusPvXV9MCRyul9tYXXtNJ6xbTMsbP0hc8m0rSL7nN6yva7PO1G8a3XHajw36FttQKI2BiIt2C/c+c53Afatm1FX9K1K6W2lHVbzmNpnoKEkxyk/zVNb2Gh7C0/L6VvLsVVgn3O2rK9rCjeTUL0afxdbbUCingeCxtGqLnQlC9sqWlfYqhTl3ucbvzn/hrsenvPcOGPdrtaAV2QxD7q2Cuxz2ZZz8dSrux1stQGJeh5YxVbxusJWpXhsdbpfJ9gqfDceG+/0bvZ2mW2rcZ/LtpxLyFaGm7DVBiTqeSD3TtBXwNhqIxbuWxkcsG+FrbampG/VD3IlR8Gx1Ua4tvKMPYUGoZ2RGpPYALb5V9ytWdfDroyCttK3ltjyarZy/q79hAE+QW775rRgCGy1Ebatmof5HCRqK88YvIln6DH6THBcUi8I+7mkXRn2Pke3vJqtzL7k/aqel+aJrT5NojYGsowTYKatnCKHiYxvMPRY7o/bamiVyBtGxssmZikYHW+nSsYV36/POG8w+Pc5uuUiW9l3Ch3jyTDu2KPtr6XY6sNYL0xZtZFoQc9QrHUFDa2bW1eR0wFS+EbZq0fiPkP1dKpyr218gbkWsBXA9+krMKzBvVtNYCuADqdv1d9UUmm1gK0GQq+PBcbjYIdo3y40H+xADTAHAwDIAFsBgAywFQDIAFsBgAywFQDIAFsBgAwStqr+/Qbv5LPJ+YgBZhCvq5yqI92rCNG2ahvvlwQH5s1iDBAnXlexT0n3KkKyrZIywlawBvNtNRts9X2Sb6voZQpbwRpgq41Q47Fw5j85efOOrC+gmBN0GI1Unu4VZ7atIvvsfMrgl0AC9dwXZF91ze1dmboF4rXRMcdWpHstgHJGf36ax9h4qdyhtnnaC4+fFqZ7hfBPM5Sba5K1z8mMJqiXcD2ff9Xrqp7X5tyV4lU9WrNIorUxUNK3It2rABXN0QrkDoW+32ttqiTdK4dF7gTNfc5NPYFqidSzVpDjxFWR+SZDm1rHVqR7JVFj/9PXwBNn2jRdVpLulcMy41bGPmMr+YTrWStIK3YkVqUfshVzuqfJHLcKH0f3vjrfVoV347NtldrndEYTVE1k3Cplq0htDGCrjTCfCb5HmnIzF+yZFYv6VpOZZ6v4PmdmNIEIrHpO2ipeGwPYaiOcNxji4zjGcXS7Hvm2mn7f5zLHVvF9nj52BpWj13PCVql6HsBWG6FOt0toIDx5HI059ocngHm2SqZ7pZnVt4rvc9Xvl0EOkXpO9a0S9az/iU/bihniv09938q4V580bmW9MGVmUpWne8UJ2WpCCpOzz/GMJhBBqJ7T41bR2ojXVcGnpHtlwhwMOmQ0AdQLttIgowmgYrCVDhlNAPVSoa22TcoiowmgUiq0FQCAB2wFADLAVgAgA2wFADLAVgAgA2wFADJY1lbRufrqgqwkmAeVsxnb2mq9b5Ynt0zNHZbCqqNyNuOwtoLDQm1IBVvB0aA2pKLSM1iFsoP05UPfjAnlHWXlDs3K70pvOZyVlJPgVJgqBp8gMNNxsjYSmXJzU7bGjTMZUREZttIPazdNTzDVxupbJfOOIle5ufld6S2/cedFy0lwKtor+ACxxLn+N6HaSGTKvZmRskXy2xJk2UprGKP9nNmKp+R3OVvTKc7vmm2rWILTAqlisDbRxLnT13faVuGpdHump2yRpbQIObZyZ6frfuPOcBgft3I/DdVNeX7XfFuF55ZcIlUMVieWOHf6+k7aKi2R6bMVY6tFmGyr0/1q2MqoCec3ibyjqK0So1pxVrNV0V7BZwgmzo2fftZWJL8tQnHfKmKrdN7RhL7VRD7Ut4KqcRLnTl/f29iK5LclUFpfSTusQVvpIzXOqE3XkwpeQPJtVX6HtYatuO8TyJTR0tVsxfjmMijj0NvPROw2sCMV9DHI+1U9L3pGSFbeUWjW8/L8ruR86tNttcBewdpEE+e0Xwar7hPjVjALK6Hr0pjXgeG5bI9zRRoXeLT9NcSfduVmYb0J5g6V5XeFthzJSspJHi/fK1ibcOJcYBn9fatEFKC/cqZl2fGy1UwS77JzTQAohuS3ZcBWACtD8ttCYCuAtSH5bRnE2Wrb/C6AeZD8tgDMHQoAMsBWACADbAUAMsBWACADbAUAMsBWACCDzWy1xLsRmekjgnLDoH7IvNmMtW2VPzvoDLAVzICELqmItlUm2Ap0yLyRCraCo4GtpKImZlIZ38OMpRKl0pDettK+kTBBKLGsJHcBvpFzMEjo2icqkUllRXIZs4OmUomGugn2reLrZuBdK5EbBvuGhK7dooa28WVSeVpLv4OLpxLF2ju07lSnhOomPzcMdgYJXftFxWbL9Da81n6p+RK/p4xbzcpo8LT61Nww2BkkdO2WtK3sY3q7CLBVPDcMdg4JXftkp30rbAVfJHTtjaitfMlC+qhQlbaK54bBwSCha0fEbeV/Jji0U5ZxAvNPr2arRG4Y7BwSuvZLylZfVic2lNvsaaERXxpSka2iWUnvjQ+PogPJYLBXSOjaKczBALA2JHQtA7YCWBkSuhYCWwGsDQldy1Chrcjggv1BQtcCVGgrAAAP2AoAZICtAEAG2AoAZICtAEAG2AoAZODa6sMzFpBbA7Ig82YzsBUcDRK6pLK5rTKpc69AImTeSAVbwdHAVlLpbBXNszKThcbvYXq/ljlhwmlStmAlSOjaJyqVZ9U22lXIDPawopC+Z044TcoWLAkJXbtFTcuzMueNtefoSYcj+SBlCxaEhK79oibmWZmfWpVxv86ZBIOULVgSErp2i0onxLh3++OnesPPnSqf3BpYGBK69knKVvZQumONIV5wGKGcuhPYClaChK59oaJ5Vu5FIOCRR+uMNGVDyhasBwldO0LF86yMcfThiYknD7XgcSwpW7AgJHTtF3VK5Fnp75hcm7Mv7WreJNOkbMFKkNC1U5aYg8Eb6QEAPSR0LcMStvJ2tgGgg4SuhSi2lRU9DwA2JHQtw3xb2V818DQPKVsAHSR0LQBzhwKADLAVAMgAWwGADLAVAMgAWwGADLAVAMgAWwGADLAVAMgAWwGADLAVAMgAWwGADLAVAMgAWwGADLAVAMgAWwGADLAVAMgAWwGADLAVAMgAWwGADLAVAMgAWwGADLAVAMgAWwGADLAVAMgAWwGADLAVAMgAWwGADLAVAMgAWwGADETbqm1ef+p1bc4fXnfzf7mnuW2+P3s9zlAjna1+mue79G8X9bo0eSs3j/HMUcsX5U/z/FPP3/ACkTOhZN1VcI7Vn3r9qfvPlI20zSv+T32e6o4z7JjOVm0z1NP9OtFW48L9CTntDIyQPBNWWndVCnbsdqmvP1XtcYYdok5f36fzr9JtlV18lq362s2WXQpsZXK71NdJqfY4ww5RjXt7kn1b59iq+4224vlXhcZZBkXer/ZfHH9jMfyt8PBNybqpBZpHd2ZqCzzaiUfcf3pnbTlmq/A/FTnO/Uf9us3tvYy+e6EWrP04ww5Rpy9jrKp5TKiMlK3aRqv77j5xLMrhNOj/nHsaZ1y3gzdHc9e9XYyb2e6cfP84jD1pY3xTb83Ctgptudsll2Ej0X2OHefzr3pd1fPanLtfXtWjNf+paAtWfZxhh7xt1RfcT/OcMPDkvxMM1W532lhnkW7G+9XsO3zeVp619P/ROV3bZvJlP2arxJb9favEPseOs/bReJk5/wbVYLVg1ccZdog6feljVUW2sm8DbdrGsVX0gvlxW3nORsOhjp2XtVVqy15bpfY5aZ/uo3EHYu1itmDVxxl2iGWrtpnS5bafyrtV645uVG8r3z1L7baK7HOprSItWPVxhh2iAmOlWcXhjlsZ3K+e/nz1tqJvNS4fb8GqjzPsEGXc/emvMmQQtZX7NkP1turWMs+K9/Mp7/9bga1S+1xgq1QLVn2cYYeYtpryIvsp1bcyhrGGJ1MTbOVe2x0iT4vmret7VjUsU6WtEvtc0rdKtGDVxxl2iDLGqha1lflCzfsx+SRbfVnjJu+/5X+o75zJ89Y1FnDfz5h5FsW/eVNkq+g+l41bRVuwyuMMO0b0t5oB4EBgKwCQAbYCABlgKwCQAbYCABlgKwCQAbYCABlgKwCQAbYCABlgKwCQwYa2Ehc2BRJxv4kNUilK6FoG5rGFFZlqq/VyMUjcKKUooWsZsBWsCLbaD0UJXcuArWBFsNV+KEro6jFCnKx+Wcbg1HRbpROcQrlSOZlU9jQp2zcSTKS1q1q31fzMsWjiXIT4lr0ThGknRUZe2VEqtiiha2yJsRraZlg9nhzla5hMUglO4VypnEwqvRrsGB6oHjvjx+pblWSOJdeNE9py29jXS2PWVm+1G//RYSq2KKHLP8F2uG38s/fNtVVugpO+k6lMKn/mGPPAycGY5fnrO3EnOC9zLLhunOCW/ZnBZrqiVu3Gdg5VsUUJXTGRJ9MNBmbaKn9uSa1eE7NlerbjVD/UjNuC8XGrWTPNZ23ZJbxl62Qxn3QFMoa73xyrYssTugIPEJPJUcYvl7ZVKFcqx1bxkQuoGtcgzm9KMscS68aJbFn/yO4cec6yUWfHqlgVGP/L60zW2beK5EpN71uBKFK2KskcS68bJ+rB4ULu5E5N7VvtmKKErqhoUslRWRvxE7VVNFcqJ+Vlp73oY+BUnfFspyRzLGfdjH0LVldfw24FxkemDlWxRQldJ++Ad/SZoMdKS/etYrlSyZQX+4kSSEPvv9+v6nnRh2JLMsey1k3vW7jU37eZ1gJWtfuH5I9RsUUJXdZR9twwR5KjMlO2fKTGrcK5UjlZ6vG3daB6xjC0R+s8OCrIHMtcN07kZOkNaJ+AdrZb6B2IA1QsczAA1EGgl5RK7TwQ2AqgDgLPrLDVALYCqAD7DfURbDVQoa1Cr5Ds/J4cxFJUsfYXa/wLYKvvU5W2AgDwgK0AQAbYCgBkgK0AQAbYCgBkgK0AQAbYCgBkgK0AQAbYCgBkgK0AQAbYCgBkgK0AQAbYCgBkgK0AQAbYCgBkgK0AQAbYCgBkgK0AQAbqv3//AEA6m6sEWwFAFpurBFsBQBabq+Sjttp8VwBgKoc6f7EVgGAOdf5iKwDBHOr8xVYAgjnU+YutAARzqPMXWwEI5lDnL7YCEMyhzl9sBSCYQ52/m9mqefyp16Up2kjbvP7U69qcMxa7/2x+rGEXZFbdh8BWC/LTPP/U89f9CFvBRgRrMg9stRmibZUJtgKdQlvVBbZaEGwFtYGtpDLaSr3+mtv36X5VL6stu65vT3MbV24e3ZLaAo+2/7TbjofeUG9b/TTP90cThBLcJe8CEzcO0tGKSr9lS9Xk6fzbLz8uqd/xxaoudi6MG7f/tK90p3FQWzXPP/W8qkd7ul3G43i7GKd614rvH5uHedD1FfW6Cfat4utm4F2rK4uxVuhbHYq2MS63P83DKr9w32oQSl88gSV9VZeo527LfRF2Ml1m5Ouotnq8j+D5932gPa2l38F1LaQ1W9vYl5SErex1pzolVDdu3xBbHQTDC15SttIL+H71aCVsq9C5YI97zLs2+ziyrS7Nl2Yrb8Nr7eeMPU21VXzdDDyt7m4HWx2K971YsMUTtkpLJGirYD1jq0VI28o+preLAFsZlYqtjkZg3Er/9LO28t0JLvOICVvJ71thK/gax6Eyx1JXtFU3/uuO6xeDrcxxK9Mg+qhQlbZy9tl8MgDHwnPF/bytPOfRUmArrc18zwSHdsoyjrlKXutm473/10dG71f1vDRPbHUYbpf0MHmgJj83brUc2MpsM6MTaxzxXOMY77lY71ul1vVi96vtEYrhcbJ6tP2VDVsdB6PeAi8K+GoyYato1aXq2XkBkFH26TAHA8DadKpyr/QLvHJ1qPMXWwGsjPsa11f4bnQihzp/sRXA2jh9q/5h5QIjWYc6fyu0lecO34ARKJCH/gqY71uEc6nv/F2RCm0FALkc6vzFVgCCOdT5i60ABHOo8xdbAQjmUOcvtgIQzKHOX2wFIJhDnb+urT48YwG5NSALMm82A1vB0SChSyqb2yqTOvcKJELmjVSwFRwNbCUVI6HL/+0WM1lo/B6m92uZEyacJmULVoKErn2S7Fu1jXYVMoM9rCik75kTTpOyBUtCQtdumXgnaM4ba8/Rkw5H8kHKFiwICV37Zeq4lfmpVRn365xJMEjZgiUhoWu3ZNjKvdsfP9Ubfu5U+eTWwMKQ0LVPUrayh9IdawzxgsMI5dSdwFawEiR07QvHVkaelXsRCHjk0TojTdmQsgXrQULXjjBt5eRZGePowxMTTx5qweNYUrZgQUjo2i/m+1aePCv9HZNrc/alXc2bZJqULVgJErp2yhJzMHgjPQCgh4SuZVjCVt7ONgB0kNC1EMW2sqLnAcCGhK5lmG8r+6sGnuYhZQugg4SuBWDuUADBHOr8xVYAgjnU+YutAARzqPMXWwEI5lDnL7YCEMyhzl9sBSCYQ52/mbaqK+cjAW+rHoudz88R/47hRFvJPlajrQBALhva6v3qpaXUZPACtgI4JJvZavyyt/2y/miohb7xgq0A9sBGttKmt9Ns5cx2t0wqmkptLtyd6ycL7Rdobu9ZONz0h/jcHS7JJKWvcG7YlzNu1W8tFLVk7FU6Z2lYfdqhd2aa769IOd8US6zrjGt4mnJYZvpES/FZg1PtazRT7p1CRivE09vi+zyXdD5F4Gjk1HP4WL2bTPun7H2It0Iy6W5GPSfqKqcmo8fZn5OWtNUbdway8696XdXzPenV86oerbmYeZpNHvyO7FIkN8z8Q92J7QZSBPYqkbOkHYpp/4tV6Odf9bo0mQckua41sarb69b+i4m2iqddpdrXnvG1bayZf80Jtc05IMOtkEhvSyZ0zSVhq2S1R0+x8LFK1WT07+Yk3c2o52RdJWsyeZxfnpy0Qlv1Wxwn69HuV/1+nfBlzmwfe6J3rs3Zq6rEXsVzloxfTroW6bfxw6VjmNK+dN33Vff+0x8Kt3s7bxLL6PmZaN/YurHeX7IVEult82Li1j4agf86e8vBbIR0PaeT7ub1reJ1Fa/JjOPsmwD2f+TcRFu6NrvQAAAAAElFTkSuQmCC" alt="" />

4、建表的语法(建分区可参见PARTITIONED BY参数):

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], ...)] [COMMENT table_comment] [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] [ROW FORMAT row_format] [STORED AS file_format] [LOCATION hdfs_path]

5、分区建表分为2种,一种是单分区,也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区,表文件夹下出现多文件夹嵌套模式。

a、单分区建表语句:单分区表,按天分区,在表结构中存在id,content,dt三列。

create table day_table (id int, content string) partitioned by (dt string);

b、双分区建表语句:双分区表,按天和小时分区,在表结构中新增加了dt和hour两列。

表文件夹目录示意图(多分区表):

create table day_hour_table (id int, content string) partitioned by (dt string, hour string);

6、添加分区表语法(表已创建,在此基础上添加分区):

ALTER TABLE table_name ADD partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ... partition_spec: : PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)

用户可以用 ALTER TABLE ADD PARTITION 来向一个表中增加分区。当分区名是字符串时加引号。例:

ALTER TABLE day_table ADD PARTITION (dt='2008-08-08', hour='') location '/path/pv1.txt' PARTITION (dt='2008-08-08', hour='') location '/path/pv2.txt';

7、删除分区语法:

ALTER TABLE table_name DROP partition_spec, partition_spec,...

用户可以用 ALTER TABLE DROP PARTITION 来删除分区。分区的元数据和数据将被一并删除。例:

ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='');

8、数据加载进分区表中语法:

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

例:

LOAD DATA INPATH '/user/pv.txt' INTO TABLE day_hour_table PARTITION(dt='2008-08- 08', hour=''); LOAD DATA local INPATH '/user/hua/*' INTO TABLE day_hour partition(dt='2010-07- 07');

当数据被加载至表中时,不会对数据进行任何转换。Load操作只是将数据复制至Hive表对应的位置。数据加载时在表下自动创建一个目录,文件存放在该分区下。

9、基于分区的查询的语句:

SELECT day_table.* FROM day_table WHERE day_table.dt>= '2008-08-08';

10、查看分区语句:

show partitions day_hour_table; OK dt=2008-08-08/hour=08 dt=2008-08-08/hour=09 dt=2008-08-09/hour=09

3.总结

1、在 Hive 中,表中的一个 Partition 对应于表下的一个目录,所有的 Partition 的数据都存储在最字集的目录中。

2、总的说来partition就是辅助查询,缩小查询范围,加快数据的检索速度和对数据按照一定的规格和条件进行管理。