{"id":848,"date":"2020-11-05T19:27:15","date_gmt":"2020-11-05T11:27:15","guid":{"rendered":"https:\/\/www.specialwu.com\/?p=848"},"modified":"2021-04-08T13:23:27","modified_gmt":"2021-04-08T05:23:27","slug":"%e6%95%b0%e6%8d%ae%e4%bb%93%e5%ba%93%e4%b9%8b-hive","status":"publish","type":"post","link":"http:\/\/www.specialwu.com\/?p=848","title":{"rendered":"\u6570\u636e\u4ed3\u5e93\u4e4b&#8211;hive"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_61 ez-toc-wrap-center counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >\u76ee\u5f55<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #000000;color:#000000\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #000000;color:#000000\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"http:\/\/www.specialwu.com\/?p=848\/#%E4%B8%80%E3%80%81%E6%98%AF%E4%BB%80%E4%B9%88\" title=\"\u4e00\u3001\u662f\u4ec0\u4e48\">\u4e00\u3001\u662f\u4ec0\u4e48<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"http:\/\/www.specialwu.com\/?p=848\/#%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%E4%B8%8E%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E5%BC%82%E5%90%8C\" title=\"\u6570\u636e\u4ed3\u5e93\u4e0e\u6570\u636e\u5e93\u7684\u5f02\u540c\">\u6570\u636e\u4ed3\u5e93\u4e0e\u6570\u636e\u5e93\u7684\u5f02\u540c<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"http:\/\/www.specialwu.com\/?p=848\/#%E4%BA%8C%E3%80%81%E6%80%8E%E4%B9%88%E7%94%A8\" title=\"\u4e8c\u3001\u600e\u4e48\u7528\">\u4e8c\u3001\u600e\u4e48\u7528<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"http:\/\/www.specialwu.com\/?p=848\/#%E6%80%8E%E4%B9%88%E5%AE%89%E8%A3%85%E5%88%B0centos7%E7%B3%BB%E7%BB%9F%E4%B8%8A\" title=\"\u600e\u4e48\u5b89\u88c5\u5230centos7\u7cfb\u7edf\u4e0a\">\u600e\u4e48\u5b89\u88c5\u5230centos7\u7cfb\u7edf\u4e0a<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"http:\/\/www.specialwu.com\/?p=848\/#hive%E8%84%9A%E6%9C%AC%E7%9A%84%E6%89%A7%E8%A1%8C%E6%96%B9%E5%BC%8F\" title=\"hive\u811a\u672c\u7684\u6267\u884c\u65b9\u5f0f\">hive\u811a\u672c\u7684\u6267\u884c\u65b9\u5f0f<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"http:\/\/www.specialwu.com\/?p=848\/#hive_sql%E8%AF%AD%E5%8F%A5\" title=\"hive sql\u8bed\u53e5\">hive sql\u8bed\u53e5<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"http:\/\/www.specialwu.com\/?p=848\/#%E5%9C%A8%E5%86%99hiveSQL%E8%AF%AD%E5%8F%A5%E5%89%8D%E9%A6%96%E5%85%88%E4%BA%86%E8%A7%A3%E5%85%B6%E7%89%B9%E6%AE%8A%E7%9A%84%E9%83%A8%E5%88%86%E5%9C%A8%E5%93%AA\" title=\"\u5728\u5199hiveSQL\u8bed\u53e5\u524d\u9996\u5148\u4e86\u89e3\u5176\u7279\u6b8a\u7684\u90e8\u5206\u5728\u54ea\">\u5728\u5199hiveSQL\u8bed\u53e5\u524d\u9996\u5148\u4e86\u89e3\u5176\u7279\u6b8a\u7684\u90e8\u5206\u5728\u54ea<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"http:\/\/www.specialwu.com\/?p=848\/#%E5%86%99hive_sql%E8%AF%AD%E5%8F%A5\" title=\"\u5199hive sql\u8bed\u53e5\">\u5199hive sql\u8bed\u53e5<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"http:\/\/www.specialwu.com\/?p=848\/#hive%E7%9A%84%E6%96%87%E4%BB%B6%E6%A0%BC%E5%BC%8F%E5%8F%8A%E9%80%89%E7%94%A8\" title=\"hive\u7684\u6587\u4ef6\u683c\u5f0f\u53ca\u9009\u7528\">hive\u7684\u6587\u4ef6\u683c\u5f0f\u53ca\u9009\u7528<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"http:\/\/www.specialwu.com\/?p=848\/#hive%E4%B8%AD%E7%9A%84%E5%87%A0%E7%A7%8D%E8%A1%A8\" title=\"hive\u4e2d\u7684\u51e0\u79cd\u8868\">hive\u4e2d\u7684\u51e0\u79cd\u8868<\/a><\/li><\/ul><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"http:\/\/www.specialwu.com\/?p=848\/#%E4%B8%89%E3%80%81%E6%A0%B8%E5%BF%83%E9%97%AE%E9%A2%98\" title=\"\u4e09\u3001\u6838\u5fc3\u95ee\u9898\">\u4e09\u3001\u6838\u5fc3\u95ee\u9898<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"http:\/\/www.specialwu.com\/?p=848\/#%E8%BD%AC%E6%8D%A2%E6%97%A5%E6%9C%9F%E7%9A%84%E5%87%BD%E6%95%B0\" title=\"\u8f6c\u6362\u65e5\u671f\u7684\u51fd\u6570\">\u8f6c\u6362\u65e5\u671f\u7684\u51fd\u6570<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"http:\/\/www.specialwu.com\/?p=848\/#Hive_sql%E8%AF%AD%E5%8F%A5%E6%8F%90%E4%BA%A4%E5%90%8E%E6%89%A7%E8%A1%8C%E8%BF%87%E7%A8%8B\" title=\"Hive sql\u8bed\u53e5\u63d0\u4ea4\u540e\u6267\u884c\u8fc7\u7a0b\">Hive sql\u8bed\u53e5\u63d0\u4ea4\u540e\u6267\u884c\u8fc7\u7a0b<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"http:\/\/www.specialwu.com\/?p=848\/#Hive%E7%9A%84HSQL%E8%BD%AC%E6%8D%A2%E4%B8%BAMapReduce%E7%9A%84%E8%BF%87%E7%A8%8B\" title=\"Hive\u7684HSQL\u8f6c\u6362\u4e3aMapReduce\u7684\u8fc7\u7a0b\">Hive\u7684HSQL\u8f6c\u6362\u4e3aMapReduce\u7684\u8fc7\u7a0b<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"http:\/\/www.specialwu.com\/?p=848\/#%E7%AA%97%E5%8F%A3%E5%87%BD%E6%95%B0\" title=\"\u7a97\u53e3\u51fd\u6570\">\u7a97\u53e3\u51fd\u6570<\/a><\/li><\/ul><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E4%B8%80%E3%80%81%E6%98%AF%E4%BB%80%E4%B9%88\"><\/span>\u4e00\u3001\u662f\u4ec0\u4e48<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<ul>\n<li>\u5b98\u7f51\u7ed9\u51fa\u7684\u89e3\u91ca<\/li>\n<\/ul>\n<p>The Apache Hive data warehouse software facilitates reading, writing, and managing large datasets residing in distributed storage using SQL. Structure can be projected onto data already in storage. A command line tool and JDBC driver are provided to connect users to Hive.<br \/>\n<a class=\"wp-editor-md-post-content-link\" href=\"https:\/\/hive.apache.org\/\" title=\"\u70b9\u51fb\u8fdb\u5165Hive\u5b98\u7f51\">\u70b9\u51fb\u8fdb\u5165Hive\u5b98\u7f51<\/a><br \/>\nhive\u662f\u57fa\u4e8eHadoop\u7684\u4e00\u4e2a\u6570\u636e\u4ed3\u5e93\u5de5\u5177\uff0c\u53ef\u4ee5\u5c06\u7ed3\u6784\u5316\u7684\u6570\u636e\u6587\u4ef6\u6620\u5c04\u4e3a\u4e00\u5f20\u6570\u636e\u5e93\u8868\uff0c\u5e76\u63d0\u4f9b\u5b8c\u6574\u7684sql\u67e5\u8be2\u529f\u80fd\uff0c\u53ef\u4ee5\u5c06sql\u8bed\u53e5\u8f6c\u6362\u4e3aMapReduce\u4efb\u52a1\u8fdb\u884c\u8fd0\u884c\u3002\u5176\u4f18\u70b9\u662f\u5b66\u4e60\u6210\u672c\u4f4e\uff0c\u53ef\u4ee5\u901a\u8fc7\u7c7bSQL\u8bed\u53e5\u5feb\u901f\u5b9e\u73b0\u7b80\u5355\u7684MapReduce\u7edf\u8ba1\uff0c\u4e0d\u5fc5\u5f00\u53d1\u4e13\u95e8\u7684MapReduce\u5e94\u7528\uff0c\u5341\u5206\u9002\u5408\u6570\u636e\u4ed3\u5e93\u7684\u7edf\u8ba1\u5206\u6790\uff0c\u4f46\u662fHive\u4e0d\u652f\u6301\u5b9e\u65f6\u67e5\u8be2\u3002<\/p>\n<h2><span class=\"ez-toc-section\" id=\"%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%E4%B8%8E%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E5%BC%82%E5%90%8C\"><\/span>\u6570\u636e\u4ed3\u5e93\u4e0e\u6570\u636e\u5e93\u7684\u5f02\u540c<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<ol>\n<li>\u6570\u636e\u4ed3\u5e93\u662f\u4ec0\u4e48<\/li>\n<\/ol>\n<p>\u6570\u636e\u4ed3\u5e93\uff0c\u662f\u4e3a\u4f01\u4e1a\u6240\u6709\u7ea7\u522b\u7684\u51b3\u7b56\u5236\u5b9a\u8fc7\u7a0b\uff0c\u63d0\u4f9b\u6240\u6709\u7c7b\u578b\u6570\u636e\u652f\u6301\u7684\u6218\u7565\u96c6\u5408\u3002\u5b83\u51fa\u4e8e\u5206\u6790\u6027\u62a5\u544a\u548c\u51b3\u7b56\u652f\u6301\u76ee\u7684\u800c\u521b\u5efa\u3002 \u4e3a\u9700\u8981\u4e1a\u52a1\u667a\u80fd\u7684\u4f01\u4e1a\uff0c\u63d0\u4f9b\u6307\u5bfc\u4e1a\u52a1\u6d41\u7a0b\u6539\u8fdb\u3001\u76d1\u89c6\u65f6\u95f4\u3001\u6210\u672c\u3001\u8d28\u91cf\u4ee5\u53ca\u63a7\u5236\u3002<br \/>\n\u6570\u636e\u4ed3\u5e93\u6807\u51c6\u4e0a\u53ef\u4ee5\u5206\u4e3a\u56db\u5c42\uff1aODS\uff08\u4e34\u65f6\u5b58\u50a8\u5c42\uff09\u3001PDW\uff08\u6570\u636e\u4ed3\u5e93\u5c42\uff09\u3001DM\uff08\u6570\u636e\u96c6\u5e02\u5c42\uff09\u3001APP\uff08\u5e94\u7528\u5c42\uff09\u3002<\/p>\n<ul>\n<li>ODS\u5c42<\/li>\n<\/ul>\n<p>Operate Data Store\u64cd\u4f5c\u6570\u636e\u5b58\u50a8,\u4e3a\u4e34\u65f6\u5b58\u50a8\u5c42\uff0c\u662f\u63a5\u53e3\u6570\u636e\u7684\u4e34\u65f6\u5b58\u50a8\u533a\u57df\uff0c\u4e3a\u540e\u4e00\u6b65\u7684\u6570\u636e\u5904\u7406\u505a\u51c6\u5907\u3002\u4e00\u822c\u6765\u8bf4ODS\u5c42\u7684\u6570\u636e\u548c\u6e90\u7cfb\u7edf\u7684\u6570\u636e\u662f\u540c\u6784\u7684\uff0c\u4e3b\u8981\u76ee\u7684\u662f\u7b80\u5316\u540e\u7eed\u6570\u636e\u52a0\u5de5\u5904\u7406\u7684\u5de5\u4f5c\u3002\u4ece\u6570\u636e\u7c92\u5ea6\u4e0a\u6765\u8bf4ODS\u5c42\u7684\u6570\u636e\u7c92\u5ea6\u662f\u6700\u7ec6\u7684\u3002ODS\u5c42\u7684\u8868\u901a\u5e38\u5305\u62ec\u4e24\u7c7b\uff0c\u4e00\u4e2a\u7528\u4e8e\u5b58\u50a8\u5f53\u524d\u9700\u8981\u52a0\u8f7d\u7684\u6570\u636e\uff0c\u4e00\u4e2a\u7528\u4e8e\u5b58\u50a8\u5904\u7406\u5b8c\u540e\u7684\u5386\u53f2\u6570\u636e\u3002\u5386\u53f2\u6570\u636e\u4e00\u822c\u4fdd\u5b583-6\u4e2a\u6708\u540e\u9700\u8981\u6e05\u9664\uff0c\u4ee5\u8282\u7701\u7a7a\u95f4\u3002\u4f46\u4e0d\u540c\u7684\u9879\u76ee\u8981\u533a\u522b\u5bf9\u5f85\uff0c\u5982\u679c\u6e90\u7cfb\u7edf\u7684\u6570\u636e\u91cf\u4e0d\u5927\uff0c\u53ef\u4ee5\u4fdd\u7559\u66f4\u957f\u7684\u65f6\u95f4\uff0c\u751a\u81f3\u5168\u91cf\u4fdd\u5b58\uff1b<\/p>\n<ul>\n<li>PDW\u5c42<\/li>\n<\/ul>\n<p>\u4e3a\u6570\u636e\u4ed3\u5e93\u5c42\uff0cPDW\u5c42\u7684\u6570\u636e\u5e94\u8be5\u662f\u4e00\u81f4\u7684\u3001\u51c6\u786e\u7684\u3001\u5e72\u51c0\u7684\u6570\u636e\uff0c\u5373\u5bf9\u6e90\u7cfb\u7edf\u6570\u636e\u8fdb\u884c\u4e86\u6e05\u6d17\uff08\u53bb\u9664\u4e86\u6742\u8d28\uff09\u540e\u7684\u6570\u636e\u3002\u8fd9\u4e00\u5c42\u7684\u6570\u636e\u4e00\u822c\u662f\u9075\u5faa\u6570\u636e\u5e93\u7b2c\u4e09\u8303\u5f0f\u7684\uff0c\u5176\u6570\u636e\u7c92\u5ea6\u901a\u5e38\u548cODS\u7684\u7c92\u5ea6\u76f8\u540c\u3002\u5728PDW\u5c42\u4f1a\u4fdd\u5b58BI\u7cfb\u7edf\u4e2d\u6240\u6709\u7684\u5386\u53f2\u6570\u636e\uff0c\u4f8b\u5982\u4fdd\u5b5810\u5e74\u7684\u6570\u636e\u3002<\/p>\n<ul>\n<li>DM\u5c42<\/li>\n<\/ul>\n<p>\u4e3a\u6570\u636e\u96c6\u5e02\u5c42\uff0c\u8fd9\u5c42\u6570\u636e\u662f\u9762\u5411\u4e3b\u9898\u6765\u7ec4\u7ec7\u6570\u636e\u7684\uff0c\u901a\u5e38\u662f\u661f\u5f62\u6216\u96ea\u82b1\u7ed3\u6784\u7684\u6570\u636e\u3002\u4ece\u6570\u636e\u7c92\u5ea6\u6765\u8bf4\uff0c\u8fd9\u5c42\u7684\u6570\u636e\u662f\u8f7b\u5ea6\u6c47\u603b\u7ea7\u7684\u6570\u636e\uff0c\u5df2\u7ecf\u4e0d\u5b58\u5728\u660e\u7ec6\u6570\u636e\u4e86\u3002\u4ece\u6570\u636e\u7684\u65f6\u95f4\u8de8\u5ea6\u6765\u8bf4\uff0c\u901a\u5e38\u662fPDW\u5c42\u7684\u4e00\u90e8\u5206\uff0c\u4e3b\u8981\u7684\u76ee\u7684\u662f\u4e3a\u4e86\u6ee1\u8db3\u7528\u6237\u5206\u6790\u7684\u9700\u6c42\uff0c\u800c\u4ece\u5206\u6790\u7684\u89d2\u5ea6\u6765\u8bf4\uff0c\u7528\u6237\u901a\u5e38\u53ea\u9700\u8981\u5206\u6790\u8fd1\u51e0\u5e74\uff08\u5982\u8fd1\u4e09\u5e74\u7684\u6570\u636e\uff09\u7684\u5373\u53ef\u3002\u4ece\u6570\u636e\u7684\u5e7f\u5ea6\u6765\u8bf4\uff0c\u4ecd\u7136\u8986\u76d6\u4e86\u6240\u6709\u4e1a\u52a1\u6570\u636e\u3002<\/p>\n<ul>\n<li>APP\u5c42<\/li>\n<\/ul>\n<p>\u4e3a\u5e94\u7528\u5c42\uff0c\u8fd9\u5c42\u6570\u636e\u662f\u5b8c\u5168\u4e3a\u4e86\u6ee1\u8db3\u5177\u4f53\u7684\u5206\u6790\u9700\u6c42\u800c\u6784\u5efa\u7684\u6570\u636e\uff0c\u4e5f\u662f\u661f\u5f62\u6216\u96ea\u82b1\u7ed3\u6784\u7684\u6570\u636e\u3002\u4ece\u6570\u636e\u7c92\u5ea6\u6765\u8bf4\u662f\u9ad8\u5ea6\u6c47\u603b\u7684\u6570\u636e\u3002\u4ece\u6570\u636e\u7684\u5e7f\u5ea6\u6765\u8bf4\uff0c\u5219\u5e76\u4e0d\u4e00\u5b9a\u4f1a\u8986\u76d6\u6240\u6709\u4e1a\u52a1\u6570\u636e\uff0c\u800c\u662fDM\u5c42\u6570\u636e\u7684\u4e00\u4e2a\u771f\u5b50\u96c6\uff0c\u4ece\u67d0\u79cd\u610f\u4e49\u4e0a\u6765\u8bf4\u662fDM\u5c42\u6570\u636e\u7684\u4e00\u4e2a\u91cd\u590d\u3002\u4ece\u6781\u7aef\u60c5\u51b5\u6765\u8bf4\uff0c\u53ef\u4ee5\u4e3a\u6bcf\u4e00\u5f20\u62a5\u8868\u5728APP\u5c42\u6784\u5efa\u4e00\u4e2a\u6a21\u578b\u6765\u652f\u6301\uff0c\u8fbe\u5230\u4ee5\u7a7a\u95f4\u6362\u65f6\u95f4\u7684\u76ee\u7684\u6570\u636e\u4ed3\u5e93\u7684\u6807\u51c6\u5206\u5c42\u53ea\u662f\u4e00\u4e2a\u5efa\u8bae\u6027\u8d28\u7684\u6807\u51c6\uff0c\u5b9e\u9645\u5b9e\u65bd\u65f6\u9700\u8981\u6839\u636e\u5b9e\u9645\u60c5\u51b5\u786e\u5b9a\u6570\u636e\u4ed3\u5e93\u7684\u5206\u5c42\uff0c\u4e0d\u540c\u7c7b\u578b\u7684\u6570\u636e\u4e5f\u53ef\u80fd\u91c7\u53d6\u4e0d\u540c\u7684\u5206\u5c42\u65b9\u6cd5\u3002<\/p>\n<ol start=\"2\">\n<li>\u6570\u636e\u4ed3\u5e93\u4e0e\u6570\u636e\u5e93\u7684\u533a\u522b<\/li>\n<\/ol>\n<p><code>\u64cd\u4f5c\u578b\u5904\u7406<\/code>\uff0c\u53eb\u8054\u673a\u4e8b\u52a1\u5904\u7406OLTP\uff08On-Line Transaction Processing\uff0c\uff09\uff0c\u4e5f\u53ef\u4ee5\u79f0\u9762\u5411\u4ea4\u6613\u7684\u5904\u7406\u7cfb\u7edf\uff0c\u5b83\u662f\u9488\u5bf9\u5177\u4f53\u4e1a\u52a1\u5728\u6570\u636e\u5e93\u8054\u673a\u7684\u65e5\u5e38\u64cd\u4f5c\uff0c\u901a\u5e38\u5bf9\u5c11\u6570\u8bb0\u5f55\u8fdb\u884c\u67e5\u8be2\u3001\u4fee\u6539\u3002\u7528\u6237\u8f83\u4e3a\u5173\u5fc3\u64cd\u4f5c\u7684\u54cd\u5e94\u65f6\u95f4\u3001\u6570\u636e\u7684\u5b89\u5168\u6027\u3001\u5b8c\u6574\u6027\u548c\u5e76\u53d1\u652f\u6301\u7684\u7528\u6237\u6570\u7b49\u95ee\u9898\u3002\u4f20\u7edf\u7684\u6570\u636e\u5e93\u7cfb\u7edf\u4f5c\u4e3a\u6570\u636e\u7ba1\u7406\u7684\u4e3b\u8981\u624b\u6bb5\uff0c\u4e3b\u8981\u7528\u4e8e\u64cd\u4f5c\u578b\u5904\u7406\u3002<br \/>\n<code>\u5206\u6790\u578b\u5904\u7406<\/code>\uff0c\u53eb\u8054\u673a\u5206\u6790\u5904\u7406OLAP\uff08On-Line Analytical Processing\uff09\u4e00\u822c\u9488\u5bf9\u67d0\u4e9b\u4e3b\u9898\u7684\u5386\u53f2\u6570\u636e\u8fdb\u884c\u5206\u6790\uff0c\u652f\u6301\u7ba1\u7406\u51b3\u7b56\u3002<\/p>\n<ol start=\"3\">\n<li>\u76f8\u540c\u4e4b\u5904<\/li>\n<\/ol>\n<p>hive\u6570\u4ed3\u5728\u64cd\u4f5c\u65f6\u5199\u7684hive SQL\u8bed\u53e5\u4e0eSQL\u8bed\u53e5\u57fa\u672c\u4e0a\u6ca1\u4ec0\u4e48\u533a\u522b\uff0c\u6240\u4ee5\u53ea\u8981\u5bf9MySQL\u6709\u4e9b\u4e86\u89e3\uff0c\u4f7f\u7528hive\u6570\u4ed3\u65f6\u4e5f\u6ca1\u5565\u969c\u788d\uff0c\u5c31\u662f\u4f60\u4ece\u6982\u5ff5\u4e0a\u6709\u4e2a\u8ba4\u8bc6\u5c31\u884c\u3002<\/p>\n<h1><span class=\"ez-toc-section\" id=\"%E4%BA%8C%E3%80%81%E6%80%8E%E4%B9%88%E7%94%A8\"><\/span>\u4e8c\u3001\u600e\u4e48\u7528<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<h2><span class=\"ez-toc-section\" id=\"%E6%80%8E%E4%B9%88%E5%AE%89%E8%A3%85%E5%88%B0centos7%E7%B3%BB%E7%BB%9F%E4%B8%8A\"><\/span>\u600e\u4e48\u5b89\u88c5\u5230centos7\u7cfb\u7edf\u4e0a<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>\u9996\u5148\u4f60\u8981\u88c5\u4e0aMySQL\uff0chdfs\u4e5f\u542f\u52a8\u4e86<br \/>\n\u6211\u7684hive\u5b89\u88c5\u76ee\u5f55\u4e3a <code>\/opt\/hive-1.2.2\/<\/code><br \/>\n1. <code>vim hive-env.sh<\/code><\/p>\n<pre><code class=\"language-bash line-numbers\">export HADOOP_HOME=\/opt\/hadoop-2.7.7\nexport HIVE_CONF_DIR=\/opt\/hive-1.2.2\/conf\nexport JAVA_HOME=\/opt\/jdk1.8\nexport HIVE_HOME=\/opt\/hive-1.2.2\n<\/code><\/pre>\n<ol start=\"2\">\n<li><code>vim hive-site.xml<\/code><\/li>\n<\/ol>\n<pre data-language=XML><code class=\"language-markup line-numbers\">&lt;?xml  version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?&gt;\n&lt;?xml-stylesheet type=\"text\/xsl\" href=\"configuration.xsl\"?&gt;\n&lt;configuration&gt;\n&lt;!-- \u8bbe\u7f6eHive\u4fdd\u5b58\u5143\u6570\u636e\u4f7f\u7528\u7684Mysql\u6570\u636e\u5e93 --&gt;\n  &lt;property&gt;\n    &lt;name&gt;javax.jdo.option.ConnectionURL&lt;\/name&gt;\n    &lt;!-- \u5b89\u88c5\u6570\u636e\u5e93\u7684\u90a3\u4e2a\u673a\u5668 --&gt;\n    &lt;value&gt;jdbc:mysql:\/\/wq2:3306\/hive?createDatabaseIfNotExist=true&lt;\/value&gt;\n  &lt;\/property&gt;\n  &lt;property&gt;\n    &lt;name&gt;javax.jdo.option.ConnectionDriverName&lt;\/name&gt;\n    &lt;value&gt;com.mysql.jdbc.Driver&lt;\/value&gt;\n  &lt;\/property&gt;\n  &lt;property&gt;\n  &lt;!-- \u7528\u6237 --&gt;\n    &lt;name&gt;javax.jdo.option.ConnectionUserName&lt;\/name&gt;\n    &lt;value&gt;root&lt;\/value&gt;\n  &lt;\/property&gt;\n  &lt;property&gt;\n  &lt;!-- \u5bc6\u7801 --&gt;\n    &lt;name&gt;javax.jdo.option.ConnectionPassword&lt;\/name&gt;\n    &lt;value&gt;specialWu7.&lt;\/value&gt;\n  &lt;\/property&gt;\n  &lt;!-- hive\u5ba2\u6237\u7aef\u6253\u5370\u6807\u9898\u4fe1\u606f --&gt;\n  &lt;property&gt;\n    &lt;name&gt;hive.cli.print.header&lt;\/name&gt;\n    &lt;value&gt;true&lt;\/value&gt;\n  &lt;\/property&gt;\n  &lt;!-- hive\u5ba2\u6237\u7aef\u6253\u5370\u5f53\u524d\u6570\u636e\u5e93\u540d --&gt;\n  &lt;property&gt;\n    &lt;name&gt;hive.cli.print.current.db&lt;\/name&gt;\n    &lt;value&gt;true&lt;\/value&gt;\n  &lt;\/property&gt;\n&lt;\/configuration&gt;\n<\/code><\/pre>\n<ol start=\"3\">\n<li><code>vim \/etc\/profile<\/code><\/li>\n<\/ol>\n<p>PATH\u524d\u5fc5\u987b\u6709\u8fd9\u4e2a\u7f8e\u5143\u7b26\u53f7\uff0cHIVE_HOME\u524d\u4e5f\u5fc5\u987b\u6709\u7f8e\u5143\u7b26\u53f7\uff0c\u4fee\u6539\u7cfb\u7edf\u6587\u4ef6\u7684\u65f6\u5019\u4e00\u5b9a\u8981\u6ce8\u610f\u8fd9\u5757\uff0c\u4e0d\u7136\u5bb9\u6613\u51fa\u95ee\u9898\uff08\u7279\u522b\u8bf4\u660e\u4e0b\u56e0\u4e3a\u8fd9\u4e2adollar\u7b26\u5728\u6211\u6587\u7ae0\u4e2d\u663e\u793a\u4e0d\u51fa\u6765\uff0c\u52a0\u4e0a\u540e\u5c31\u6d88\u5931\u4e86\uff09<\/p>\n<pre><code class=\"language-bash line-numbers\">`export HIVE_HOME = \/opt\/hive-1.2.2\nexport PATH = <span class=\"katex math inline\">PATH:<\/span>HIVE_HOME\/bin \n#\u8bb0\u5f97\u6267\u884c\u8fd9\u4e2a\u4f7f\u4e4b\u751f\u6548\nsource \/etc\/profile`\n<\/code><\/pre>\n<p>\u5982\u679c\u4e0a\u9762\u6ca1\u52a0dollar\u7b26\uff0c\u90a3\u4e48\u6240\u6709\u57fa\u672c\u547d\u4ee4\u65e0\u6cd5\u6267\u884c<\/p>\n<pre><code class=\"language-bash line-numbers\">[root@wq1 etc]# ls\nbash: ls: \u672a\u627e\u5230\u547d\u4ee4...\n\u76f8\u4f3c\u547d\u4ee4\u662f\uff1a 'lz'\n[root@wq1 etc]# lz\nbash: lz: \u672a\u627e\u5230\u547d\u4ee4...\n\u76f8\u4f3c\u547d\u4ee4\u662f\uff1a 'ls'\n[root@wq1 etc]# ss\nbash: ss: \u672a\u627e\u5230\u547d\u4ee4...\n\u76f8\u4f3c\u547d\u4ee4\u662f\uff1a:\n'ss'\n'sz'\n[root@wq1 etc]# ss\nbash: ss: \u672a\u627e\u5230\u547d\u4ee4...\n\u76f8\u4f3c\u547d\u4ee4\u662f\uff1a:\n<\/code><\/pre>\n<p>\u89e3\u51b3\u547d\u4ee4\u5931\u6548\u529e\u6cd5<\/p>\n<pre><code class=\"language-bash line-numbers\">\u5728\u547d\u4ee4\u884c\u4e2d\u8f93\u5165\nPATH=\/bin:\/usr\/bin\n<\/code><\/pre>\n<ol start=\"4\">\n<li>\u5c06\u81ea\u5df1Java\u9879\u76ee\u7684 mysql-connector-java-5.1.35.jar \u653e\u5230hive\u5b89\u88c5\u76ee\u5f55\u4e0b\u7684lib\u6587\u4ef6\u5939\u4e2d<\/li>\n<\/ol>\n<h2><span class=\"ez-toc-section\" id=\"hive%E8%84%9A%E6%9C%AC%E7%9A%84%E6%89%A7%E8%A1%8C%E6%96%B9%E5%BC%8F\"><\/span>hive\u811a\u672c\u7684\u6267\u884c\u65b9\u5f0f<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>hive\u540e\u7ec4\u5408\u7684\u547d\u4ee4\u53ca\u542b\u4e49<\/p>\n<pre><code class=\"language-bash line-numbers\">usage: hive\n -d,--define &lt;key=value&gt;          Variable subsitution to apply to hive\n                                  commands. e.g. -d A=B or --define A=B\n    --database &lt;databasename&gt;     Specify the database to use\n -e &lt;quoted-query-string&gt;         SQL from command line\n -f &lt;filename&gt;                    SQL from files\n -H,--help                        Print help information\n -h &lt;hostname&gt;                    connecting to Hive Server on remote host\n    --hiveconf &lt;property=value&gt;   Use value for given property\n    --hivevar &lt;key=value&gt;         Variable subsitution to apply to hive\n                                  commands. e.g. --hivevar A=B\n -i &lt;filename&gt;                    Initialization SQL file\n -p &lt;port&gt;                        connecting to Hive Server on port number\n -S,--silent                      Silent mode in interactive shell\n -v,--verbose                     Verbose mode (echo executed SQL to the\n                                  console)\n<\/code><\/pre>\n<ol>\n<li>hive\u63a7\u5236\u53f0\u6267\u884c<\/li>\n<\/ol>\n<pre><code class=\"language-bash line-numbers\">hive&gt; set mapred.job.queue.name=pms;\nhive&gt; select page_name, tpa_name from pms.pms_exps_prepro limit 2;\n<\/code><\/pre>\n<ol start=\"2\">\n<li>hive -e &#8220;SQL&#8221;\u6267\u884c<\/li>\n<\/ol>\n<pre><code class=\"language-bash line-numbers\">hive -e \"select page_name, tpa_name from pms.pms_exps_prepro limit 2;\"\n\n<\/code><\/pre>\n<ol start=\"3\">\n<li>hive -f SQL\u6587\u4ef6\u6267\u884c<\/li>\n<\/ol>\n<pre><code class=\"language-sql line-numbers\">date=2015-10-22\nhive -f test.sql --hivevar date=$date\n<\/code><\/pre>\n<blockquote><p>\n  \u628a\u6240\u6709\u6570\u636e\u5e93\u4e0b\u7684\u8868\u5b57\u6bb5\u4fdd\u5b58\u5230\u4e00\u4e2a\u6587\u4ef6\u5939\u4e2d\u7684\u811a\u672c\n<\/p><\/blockquote>\n<pre><code class=\"language-bash line-numbers\">#!\/bin\/bash\nhive -e \"show databases ;\" &gt; databases.txt\ni=1\ncat databases.txt |while read db\ndo\nhive -e \"use <span class=\"katex math inline\">db;show tables;\"><\/span>i.txt\nsleep 3\n\ncat <span class=\"katex math inline\">i.txt |while read eachline\n\ndo\n\nhive -e \"use<\/span>db;select * from <span class=\"katex math inline\">eachline limit\" &gt;&gt;tablesDDL.txt\n\ndone\n\ni=<\/span>(($i+1))\n\ndone \n<\/code><\/pre>\n<p>SQL\u8bed\u53e5\u8303\u4f8b<\/p>\n<pre><code class=\"language-sql line-numbers\">drop table if exists pms.pms_exps_prepro; \ncreate table pms.pms_exps_prepro as \nselect \n  a.provinceid,\n  a.cityid,\n  a.ieversion,\n  a.platform,\n  '${date}' as ds\nfrom track_exps a;\n<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"hive_sql%E8%AF%AD%E5%8F%A5\"><\/span>hive sql\u8bed\u53e5<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<h4><span class=\"ez-toc-section\" id=\"%E5%9C%A8%E5%86%99hiveSQL%E8%AF%AD%E5%8F%A5%E5%89%8D%E9%A6%96%E5%85%88%E4%BA%86%E8%A7%A3%E5%85%B6%E7%89%B9%E6%AE%8A%E7%9A%84%E9%83%A8%E5%88%86%E5%9C%A8%E5%93%AA\"><\/span>\u5728\u5199hiveSQL\u8bed\u53e5\u524d\u9996\u5148\u4e86\u89e3\u5176\u7279\u6b8a\u7684\u90e8\u5206\u5728\u54ea<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<ul>\n<li>Hive\u6ca1\u6709delete\u548cupdate\u3002<\/li>\n<li>Hive\u4e0d\u652f\u6301\u7b49\u503c\u8fde\u63a5<\/li>\n<\/ul>\n<pre><code class=\"language-sql line-numbers\">#SQL\nselect * from dual a,dual b where a.key = b.key\n#hive SQL\nselect * from dual a join dual b on a.key = b.key\n<\/code><\/pre>\n<ul>\n<li>\u5728HiveQL\u4e2d\uff0c\u5bf9\u5206\u53f7\u7684\u8bc6\u522b\u6ca1\u6709\u90a3\u4e48\u667a\u6167<\/li>\n<\/ul>\n<pre><code class=\"language-sql line-numbers\">#sql\nselect concat(';',key) from dual;\n#hive sql\u4f7f\u7528\u5206\u53f7\u7684\u516b\u8fdb\u5236\u7684ASCII\u7801\u8fdb\u884c\u8f6c\u4e49\nselect concat('\\073',key) from dual;\n\n<\/code><\/pre>\n<ul>\n<li>Hive\u4e0d\u652f\u6301\u5c06\u6570\u636e\u63d2\u5165\u73b0\u6709\u7684\u8868\u6216\u5206\u533a\u4e2d<\/li>\n<\/ul>\n<pre><code class=\"language-sql line-numbers\">#\u4ec5\u652f\u6301\u8986\u76d6\u91cd\u5199\u6574\u4e2a\u8868\nINSERT OVERWRITE TABLE t1  ;\n<\/code><\/pre>\n<ul>\n<li>hive\u652f\u6301\u5d4c\u5165mapreduce\u7a0b\u5e8f\uff0c\u6765\u5904\u7406\u590d\u6742\u7684\u903b\u8f91<\/li>\n<\/ul>\n<pre><code class=\"language-sql line-numbers\">FROM (  \nMAP doctext USING 'python wc_mapper.py' AS (word, cnt)  \nFROM docs  \nCLUSTER BY word  \n) a  \nREDUCE word, cnt USING 'python wc_reduce.py';\n<\/code><\/pre>\n<ul>\n<li>hive\u652f\u6301\u5c06\u8f6c\u6362\u540e\u7684\u6570\u636e\u76f4\u63a5\u5199\u5165\u4e0d\u540c\u7684\u8868\uff0c\u8fd8\u80fd\u5199\u5165\u5206\u533a\u3001hdfs\u548c\u672c\u5730\u76ee\u5f55<\/li>\n<\/ul>\n<pre><code class=\"language-sql line-numbers\">INSERT OVERWRITE TABLE t2  \nSELECT t3.c2, count(1)  \nFROM t3  \nWHERE t3.c1 &lt;= 20  \nGROUP BY t3.c2  \n\nINSERT OVERWRITE DIRECTORY '\/output_dir'  \nSELECT t3.c2, avg(t3.c1)  \nFROM t3  \nWHERE t3.c1 &gt; 20 AND t3.c1 &lt;= 30  \nGROUP BY t3.c2  \n<\/code><\/pre>\n<ul>\n<li>HQL\u4e0d\u652f\u6301\u884c\u7ea7\u522b\u7684\u589e\u3001\u6539\u3001\u5220\uff0c\u6240\u6709\u6570\u636e\u5728\u52a0\u8f7d\u65f6\u5c31\u5df2\u7ecf\u786e\u5b9a\uff0c\u4e0d\u53ef\u66f4\u6539<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"%E5%86%99hive_sql%E8%AF%AD%E5%8F%A5\"><\/span>\u5199hive sql\u8bed\u53e5<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<ol>\n<li>\u521b\u5efa\u8868<\/li>\n<\/ol>\n<p>Hive\u9ed8\u8ba4\u7684\u5206\u9694\u7b26\u662f\\001<br \/>\n\u8be5\u8868\u4e3a\u5916\u90e8\u8868\u6309\u7167\u5e74\u6708\u65e5\u8fdb\u884c\u5206\u533a\uff0c\u6570\u636e\u6765\u6e90hdfs\u4e0a\uff0c\u6307\u5b9a\u5b57\u6bb5\u5206\u9694\u7b26\u4e3a\u5236\u8868\u7b26\u2019\\t\u2019<\/p>\n<pre><code class=\"language-sql line-numbers\">CREATE EXTERNAL TABLE IF NOT EXISTS orders_ods_t\n(\norderid INT COMMENT \"\u8ba2\u5355id\",    \ncustomerID INT COMMENT \"\u5ba2\u6237id\",\nemployID INT COMMENT \"\u5458\u5de5id\",\nshipcountry STRING COMMENT \"\u53d1\u8d27\u56fd\u5bb6\" \n)\nCOMMENT \"\u8ba2\u5355\u8868\"\npartitioned by (yt string,mt string,dt string)\nrow format delimited fields terminated by '\\t'\nLOCATION '\/user\/hive\/warehouse\/ods_database.db\/orders_ods_t';\nSTORED AS TEXTFILE;\nMSCK REPAIR TABLE orders_ods_t;\n\n<\/code><\/pre>\n<ol start=\"2\">\n<li>\u521b\u5efa\u5206\u6876\u8868<br \/>\n\u5bf9\u4e8e\u5206\u6876\u8868\u662f\u4e0d\u80fd\u76f4\u63a5\u4f7f\u7528load data\u7684\uff0c\u53ef\u4ee5\u5148\u5efa\u4e2a\u4e2d\u95f4\u8868\u5bfc\u5165\u6570\u636e\u518dload\u5230\u4e2d\u95f4\uff0c\u667a\u6167\u4ece\u8fd9\u4e2a\u4e2d\u95f4\u8868\u62ff\u5230\u5206\u6876\u8868<\/li>\n<\/ol>\n<pre><code class=\"language-sql line-numbers\">set hive.enforce.bucketing = true;\nset mapreduce.job.reduces=4;\nCREATE EXTERNAL TABLE test_bucket (\n  user_num STRING COMMENT '\u7528\u6237\u7f16\u53f7',\n  mobile STRING COMMENT '\u624b\u673a\u53f7\u7801',\n  reg_date STRING COMMENT '\u6ce8\u518c\u65e5\u671f',\n  t_start_date STRING COMMENT '\u751f\u6548\u65f6\u95f4',\n  t_end_date STRING COMMENT '\u6700\u540e\u66f4\u6539\u65f6\u95f4'\n)\nCOMMENT '\u5206\u6876\u8868\u7ec3\u4e60'\nclustered by(user_num) \nsorted by(user_num DESC)\ninto 4 buckets\nLOCATION '\/user\/hive\/warehouse\/test.db\/test_bucket';\n\nset hive.enforce.bucketing = true;\nset mapreduce.job.reduces=4;\ninsert into table test_bucket select user_num,mobile,reg_date,t_start_date,t_end_date from test_bucket_tmp1 distribute by(user_num) sort by(user_num asc);\n\n<\/code><\/pre>\n<ol start=\"3\">\n<li>\u67e5\u770b\u8868\u4fe1\u606f<\/li>\n<\/ol>\n<pre><code class=\"language-sql line-numbers\">describe formatted table_name test;\n<\/code><\/pre>\n<ol start=\"4\">\n<li>\u628ahive\u8868\u6570\u636e\u5bfc\u5165\u5230hdfs\u4e0a<\/li>\n<\/ol>\n<pre><code class=\"language-sql line-numbers\">insert overwrite directory '\/root\/hive_test\/1.txt' select * from test;\n<\/code><\/pre>\n<ol start=\"5\">\n<li>\u628ahdfs\u7684\u6570\u636e\u6620\u5c04\u5230hive\u8868\u4e2d\u4e0a<\/li>\n<\/ol>\n<pre><code class=\"language-sql line-numbers\">LOAD DATA  INPATH 'hdfs:\/\/wq1:9000\/source-six\/categories' into table category_ods_t partition (yt=\"2020\",mt=\"10\",dt=\"26\");;\n<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"hive%E7%9A%84%E6%96%87%E4%BB%B6%E6%A0%BC%E5%BC%8F%E5%8F%8A%E9%80%89%E7%94%A8\"><\/span>hive\u7684\u6587\u4ef6\u683c\u5f0f\u53ca\u9009\u7528<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<ol>\n<li>\u5b58\u50a8\u7c7b\u578b<\/li>\n<\/ol>\n<p><code>\u9762\u5411\u884c<\/code>\uff1a\u540c\u4e00\u884c\u7684\u6570\u636e\u5b58\u50a8\u5728\u4e00\u8d77\uff0c\u5373\u8fde\u7eed\u5b58\u50a8\u3002SequenceFile,TEXTFILE \u3002\u91c7\u7528\u8fd9\u79cd\u65b9\u5f0f\uff0c\u5982\u679c\u53ea\u9700\u8981\u8bbf\u95ee\u884c\u7684\u4e00\u5c0f\u90e8\u5206\u6570\u636e\uff0c\u4ea6\u9700\u8981\u5c06\u6574\u884c\u8bfb\u5165\u5185\u5b58\uff0c\u63a8\u8fdf\u5e8f\u5217\u5316\u4e00\u5b9a\u7a0b\u5ea6\u4e0a\u53ef\u4ee5\u7f13\u89e3\u8fd9\u4e2a\u95ee\u9898\uff0c\u4f46\u662f\u4ece\u78c1\u76d8\u8bfb\u53d6\u6574\u884c\u6570\u636e\u7684\u5f00\u9500\u5374\u65e0\u6cd5\u907f\u514d\u3002\u9762\u5411\u884c\u7684\u5b58\u50a8\u9002\u5408\u4e8e\u6574\u884c\u6570\u636e\u9700\u8981\u540c\u65f6\u5904\u7406\u7684\u60c5\u51b5\u3002<\/p>\n<p><code>\u9762\u5411\u5217<\/code>\uff1a\u6574\u4e2a\u6587\u4ef6\u88ab\u5207\u5272\u4e3a\u82e5\u5e72\u5217\u6570\u636e\uff0c\u6bcf\u4e00\u5217\u6570\u636e\u4e00\u8d77\u5b58\u50a8\u3002Parquet , RCFile,ORCFile\u3002\u9762\u5411\u5217\u7684\u683c\u5f0f\u4f7f\u5f97\u8bfb\u53d6\u6570\u636e\u65f6\uff0c\u53ef\u4ee5\u8df3\u8fc7\u4e0d\u9700\u8981\u7684\u5217\uff0c\u9002\u5408\u4e8e\u53ea\u5904\u4e8e\u884c\u7684\u4e00\u5c0f\u90e8\u5206\u5b57\u6bb5\u7684\u60c5\u51b5\u3002\u4f46\u662f\u8fd9\u79cd\u683c\u5f0f\u7684\u8bfb\u5199\u9700\u8981\u66f4\u591a\u7684\u5185\u5b58\u7a7a\u95f4\uff0c\u56e0\u4e3a\u9700\u8981\u7f13\u5b58\u884c\u5728\u5185\u5b58\u4e2d\uff08\u4e3a\u4e86\u83b7\u53d6\u591a\u884c\u4e2d\u7684\u67d0\u4e00\u5217\uff09\u3002\u540c\u65f6\u4e0d\u9002\u5408\u6d41\u5f0f\u5199\u5165\uff0c\u56e0\u4e3a\u4e00\u65e6\u5199\u5165\u5931\u8d25\uff0c\u5f53\u524d\u6587\u4ef6\u65e0\u6cd5\u6062\u590d\uff0c\u800c\u9762\u5411\u884c\u7684\u6570\u636e\u5728\u5199\u5165\u5931\u8d25\u65f6\u53ef\u4ee5\u91cd\u65b0\u540c\u6b65\u5230\u6700\u540e\u4e00\u4e2a\u540c\u6b65\u70b9\uff0c\u6240\u4ee5Flume\u91c7\u7528\u7684\u662f\u9762\u5411\u884c\u7684\u5b58\u50a8\u683c\u5f0f\u3002<\/p>\n<p>\u5982\u679c\u4e3atextfile\u7684\u6587\u4ef6\u683c\u5f0f\uff0c\u76f4\u63a5load\u5c31OK\uff0c\u4e0d\u9700\u8981\u8d70MapReduce\uff1b\u5982\u679c\u662f\u5176\u4ed6\u7684\u7c7b\u578b\u5c31\u9700\u8981\u8d70MapReduce\u4e86\uff0c\u56e0\u4e3a\u5176\u4ed6\u7684\u7c7b\u578b\u90fd\u6d89\u53ca\u5230\u4e86\u6587\u4ef6\u7684\u538b\u7f29\uff0c\u8fd9\u9700\u8981\u501f\u52a9MapReduce\u7684\u538b\u7f29\u65b9\u5f0f\u6765\u5b9e\u73b0\u3002<br \/>\n\u538b\u7f29\u6bd4\uff1aORC >  Parquet > RCFILE > Sequencefile > textFile\uff08textfile\u6ca1\u6709\u8fdb\u884c\u538b\u7f29\uff09\u6570\u636e\u538b\u7f29\u6bd4\u4f8b\u4e0aORC\u6700\u4f18\uff0c\u76f8\u6bd4textfile\u8282\u7701\u4e8650\u500d\u78c1\u76d8\u7a7a\u95f4<br \/>\n\u67e5\u8be2\u901f\u5ea6\uff1aORC >  Parquet > RCFILE > Sequencefile > textFile<br \/>\n\u5de5\u4f5c\u4e2d\u539f\u59cb\u65e5\u5fd7\u5199\u5165hive\u7684\u5b58\u50a8\u683c\u5f0f\u90fd\u91c7\u7528ORC\u6216\u8005parquet\u683c\u5f0f<\/p>\n<ol>\n<li>TEXTFILE<\/li>\n<\/ol>\n<p>TextFile\u6587\u4ef6\u4e0d\u652f\u6301\u5757\u538b\u7f29\uff0c\u9ed8\u8ba4\u683c\u5f0f\uff0c\u6570\u636e\u4e0d\u505a\u538b\u7f29\uff0c\u78c1\u76d8\u5f00\u9500\u5927\uff0c\u6570\u636e\u89e3\u6790\u5f00\u9500\u5927,\u5c5e\u4e8e\u884c\u5f0f\u5b58\u50a8\u3002\u53ef\u7ed3\u5408Gzip\u3001Bzip2\u4f7f\u7528(\u7cfb\u7edf\u81ea\u52a8\u68c0\u67e5\uff0c\u6267\u884c\u67e5\u8be2\u65f6\u81ea\u52a8\u89e3\u538b)\uff0c\u4f46\u4f7f\u7528\u8fd9\u79cd\u65b9\u5f0f\uff0c\u538b\u7f29\u540e\u7684\u6587\u4ef6\u4e0d\u652f\u6301split\uff0cHive\u4e0d\u4f1a\u5bf9\u6570\u636e\u8fdb\u884c\u5207\u5206\uff0c\u4ece\u800c\u65e0\u6cd5\u5bf9\u6570\u636e\u8fdb\u884c\u5e76\u884c\u64cd\u4f5c\u3002\u5e76\u4e14\u5728\u53cd\u5e8f\u5217\u5316\u8fc7\u7a0b\u4e2d\uff0c\u5fc5\u987b\u9010\u4e2a\u5b57\u7b26\u5224\u65ad\u662f\u4e0d\u662f\u5206\u9694\u7b26\u548c\u884c\u7ed3\u675f\u7b26\uff0c\u56e0\u6b64\u53cd\u5e8f\u5217\u5316\u5f00\u9500\u4f1a\u6bd4SequenceFile\u9ad8\u51e0\u5341\u500d\u3002<\/p>\n<ol start=\"2\">\n<li>SEQUENCEFILE<\/li>\n<\/ol>\n<p><img src=\"http:\/\/static.zybuluo.com\/BrandonLin\/il5vgcjt388skd9el66qmoc1\/Sequence.png\" alt=\"\" \/><\/p>\n<p>\u884c\u5f0f\u5b58\u50a8\uff0cSequenceFile\u662fHadoop API\u63d0\u4f9b\u7684\u4e00\u79cd\u4e8c\u8fdb\u5236\u6587\u4ef6\u652f\u6301\uff0c\uff0c\u5b58\u50a8\u65b9\u5f0f\u4e3a\u884c\u5b58\u50a8\uff0c\u5176\u5177\u6709\u4f7f\u7528\u65b9\u4fbf\u3001\u53ef\u5206\u5272\u3001\u53ef\u538b\u7f29\u7684\u7279\u70b9\u3002SequenceFile\u652f\u6301\u4e09\u79cd\u538b\u7f29\u9009\u62e9\uff1aNONE\uff0cRECORD\uff0cBLOCK\u3002Record\u538b\u7f29\u7387\u4f4e\uff0c\u4e00\u822c\u5efa\u8bae\u4f7f\u7528BLOCK\u538b\u7f29\u3002\u4f18\u52bf\u662f\u6587\u4ef6\u548chadoop api\u4e2d\u7684MapFile\u662f\u76f8\u4e92\u517c\u5bb9\u7684<\/p>\n<ol start=\"3\">\n<li>RCFILE<\/li>\n<\/ol>\n<p>Record Columnar File\uff0c\u5217\u5f0f\u5b58\u50a8\u683c\u5f0f\u6587\u4ef6 Hive0.6\u4ee5\u540e\u5f00\u59cb\u652f\u6301\uff0c\u8fd9\u79cd\u7c7b\u578b\u7684\u6587\u4ef6\u5148\u5c06\u6570\u636e\u6309\u884c\u5212\u5206\u6210Row Group\uff0c\u5728Row Group\u5185\u90e8\uff0c\u518d\u5c06\u6570\u636e\u6309\u5217\u5212\u5206\u5b58\u50a8\u3002\u5b58\u50a8\u65b9\u5f0f\uff1a\u6570\u636e\u6309\u884c\u5206\u5757\uff0c\u6bcf\u5757\u6309\u5217\u5b58\u50a8\u3002\u7ed3\u5408\u4e86\u884c\u5b58\u50a8\u548c\u5217\u5b58\u50a8\u7684\u4f18\u70b9\uff1a<br \/>\n    \u9996\u5148\uff0cRCFile \u4fdd\u8bc1\u540c\u4e00\u884c\u7684\u6570\u636e\u4f4d\u4e8e\u540c\u4e00\u8282\u70b9\uff0c\u56e0\u6b64\u5143\u7ec4\u91cd\u6784\u7684\u5f00\u9500\u5f88\u4f4e\uff1b<br \/>\n    \u5176\u6b21\uff0c\u50cf\u5217\u5b58\u50a8\u4e00\u6837\uff0cRCFile \u80fd\u591f\u5229\u7528\u5217\u7ef4\u5ea6\u7684\u6570\u636e\u538b\u7f29\uff0c\u5e76\u4e14\u80fd\u8df3\u8fc7\u4e0d\u5fc5\u8981\u7684\u5217\u8bfb\u53d6\uff1b<\/p>\n<ol start=\"4\">\n<li>ORC<\/li>\n<\/ol>\n<p>Optimized Record Columnar File\uff0c\u5217\u5f0f\u5b58\u50a8\u683c\u5f0f\u6587\u4ef6\uff0c\u6bd4RCFILE\u6709\u66f4\u9ad8\u7684\u538b\u7f29\u6bd4\u548c\u8bfb\u5199\u6548\u7387\uff0cHive0.11\u4ee5\u540e\u5f00\u59cb\u652f\u6301,\u5176\u5185\u90e8\u5c06\u6570\u636e\u5212\u5206\u4e3a\u9ed8\u8ba4\u5927\u5c0f\u4e3a250M\u7684Stripe\u3002\u6bcf\u4e2aStripe\u5305\u62ec\u7d22\u5f15\u3001\u6570\u636e\u548cFooter\u3002\u7d22\u5f15\u5b58\u50a8\u6bcf\u4e00\u5217\u7684\u6700\u5927\u6700\u5c0f\u503c\uff0c\u4ee5\u53ca\u5217\u4e2d\u6bcf\u4e00\u884c\u7684\u4f4d\u7f6e.<\/p>\n<ul>\n<li>\u4e09\u79cd\u8bbe\u7f6eorcfile\u7684\u547d\u4ee4\uff0c\u540c\u6837\u9002\u7528\u8bbe\u7f6e\u5176\u4ed6\u683c\u5f0f\u7684\u6587\u4ef6<\/li>\n<\/ul>\n<pre><code class=\"language-sql line-numbers\">#\u521b\u8868\u65f6\nCREATE TABLE ... STORED AAS ORC\n#\u4fee\u6539\u8868\u7ed3\u6784\u65f6\nALTER TABLE ... SET FILEFORMAT ORC\n#\u8bbe\u7f6e\u8868\u7684\u53c2\u6570\u65f6\nSET hive.default.fileformat=ORC\n<\/code><\/pre>\n<ol start=\"5\">\n<li>PARQUET<\/li>\n<\/ol>\n<p>\u5217\u51fa\u5b58\u50a8\u683c\u5f0f\u6587\u4ef6\uff0cHive0.13\u4ee5\u540e\u5f00\u59cb\u652f\u6301<\/p>\n<h3><span class=\"ez-toc-section\" id=\"hive%E4%B8%AD%E7%9A%84%E5%87%A0%E7%A7%8D%E8%A1%A8\"><\/span>hive\u4e2d\u7684\u51e0\u79cd\u8868<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<ul>\n<li>\u5185\u90e8\u8868(\u53d7\u63a7\u8868)\uff1a\u5f53\u5220\u9664\u5185\u90e8\u8868\u7684\u65f6\u5019\uff0chdfs\u4e0a\u7684\u6570\u636e\u4ee5\u53ca\u5143\u6570\u636e\u90fd\u4f1a\u88ab\u5220\u9664\u3002<\/li>\n<li>\u5916\u90e8\u8868\uff1a\u5f53\u5220\u9664\u5916\u90e8\u8868\u7684\u65f6\u5019\uff0cHDFS\u4e0a\u7684\u6570\u636e\u4e0d\u4f1a\u88ab\u5220\u9664\uff0c\u4f46\u662f\u5143\u6570\u636e\u4f1a\u88ab\u5220\u9664\u3002<\/li>\n<li>\u4e34\u65f6\u8868(\u6d4b\u8bd5\u73af\u5883)\uff1a\u5728\u5f53\u524d\u4f1a\u8bdd\u671f\u95f4\u5185\u5b58\u5728\uff0c\u4f1a\u8bdd\u7ed3\u675f\u81ea\u52a8\u6d88\u5931\uff0c\u751f\u547d\u5468\u671f\u968f\u4e4bsession\u3002<\/li>\n<li>\u5206\u533a\u8868\uff1a\u5c06\u4e00\u6279\u6570\u636e\u5206\u6210\u591a\u4e2a\u76ee\u5f55\u6765\u5b58\u50a8\u3002<\/li>\n<li>\u5206\u6876\u8868\uff1a\u6876\u7684\u6982\u5ff5\u5c31\u662fMapReduce\u7684\u5206\u533a\u7684\u6982\u5ff5<\/li>\n<\/ul>\n<blockquote><p>\n  hive\u8bef\u5220\u5185\u90e8\u8868\u5982\u4f55\u6062\u590d\n<\/p><\/blockquote>\n<p>\u67e5\u627ehive\u8868\u7684\u5b58\u50a8\u4f4d\u7f6e\u5e76\u67e5\u770b\u8868\u6587\u4ef6\u5927\u5c0f\u53ca\u5206\u533a\u6587\u4ef6\u540d<\/p>\n<pre><code class=\"language-sql line-numbers\">hive (default)&gt; show create table testhive;\nOK\ncreatetab_stmt\nCREATE TABLE `testhive`(\n  `year` string, \n  `id` int, \n  `yearone` int, \n  `c1` int, \n  `c2` int, \n  `c3` int, \n  `c4` int, \n  `c5` int, \n  `c6` int, \n  `c7` int)\nROW FORMAT SERDE \n  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' \nSTORED AS INPUTFORMAT \n  'org.apache.hadoop.mapred.TextInputFormat' \nOUTPUTFORMAT \n  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'\nLOCATION\n  'hdfs:\/\/wq1:9000\/user\/hive\/warehouse\/testhive'\nTBLPROPERTIES (\n  'transient_lastDdlTime'='1603707783')\nTime taken: 0.212 seconds, Fetched: 21 row(s)\n<\/code><\/pre>\n<p>hdfs\u56de\u6536\u7ad9\u65f6\u95f4\u8bbe\u7f6e<\/p>\n<pre data-language=XML><code class=\"language-markup line-numbers\">#\u5355\u4f4d\uff1a\u5206\u949f\n&lt;property&gt;\n    &lt;name&gt;fs.trash.interval&lt;\/name&gt;\n    &lt;value&gt;1440&lt;\/value&gt;\n&lt;\/property&gt;\n<\/code><\/pre>\n<p>\u81ea\u5b9a\u4e49\u56de\u6536\u7ad9\u903b\u8f91<\/p>\n<pre><code class=\"language-java line-numbers\">import java.io.IOException;\nimport org.apache.commons.logging.Log;\nimport org.apache.commons.logging.LogFactory;\nimport org.apache.hadoop.conf.Configuration;\nimport org.apache.hadoop.fs.FileSystem;\nimport org.apache.hadoop.fs.Path;\nimport org.apache.hadoop.fs.Trash;\n\npublic class RMFile {\n    private final static Log log = LogFactory.getLog(RMFile.class);\n    private final static Configuration conf = new Configuration();\n    \/**\n     * Delete a file\/directory on hdfs\n     * @param path\n     * @param recursive\n     * @return\n     * @throws IOException\n     *\/\n    public static boolean rm(FileSystem fs, Path path, boolean recursive)\n            throws IOException {\n        log.info(\"rm: \" + path + \" recursive: \" + recursive);\n        boolean ret = fs.delete(path, recursive);\n        if (ret)\n            log.info(\"rm: \" + path);\n        return ret;\n    }\n    \/**\n     * Delete a file\/directory on hdfs,and move a file\/directory to Trash\n     * @param fs\n     * @param path\n     * @param recursive\n     * @param skipTrash\n     * @return\n     * @throws IOException\n     *\/\n    public static boolean rm(FileSystem fs, Path path, boolean recursive,\n            boolean skipTrash) throws IOException {\n        log.info(\"rm: \" + path + \" recursive: \" + recursive+\" skipTrash:\"+skipTrash);\n        if (!skipTrash) {\n            Trash trashTmp = new Trash(fs, conf);\n            if (trashTmp.moveToTrash(path)) {\n                log.info(\"Moved to trash: \" + path);\n                return true;\n            }\n        }\n        boolean ret = fs.delete(path, recursive);\n        if (ret)\n           log.info(\"rm: \" + path);\n        return ret;\n    }\n    public static void main(String[] args) throws IOException {\n        conf.set(\"fs.default.name\", \"hdfs:\/\/data2.kt:8020\/\");\n        FileSystem fs = FileSystem.get(conf);\n        RMFile.rm(fs,new Path(\"hdfs:\/\/data2.kt:8020\/test\/testrm\"),true,false);\n    }\n}\n<\/code><\/pre>\n<h1><span class=\"ez-toc-section\" id=\"%E4%B8%89%E3%80%81%E6%A0%B8%E5%BF%83%E9%97%AE%E9%A2%98\"><\/span>\u4e09\u3001\u6838\u5fc3\u95ee\u9898<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<blockquote><p>\n  \u62bd\u6837\n<\/p><\/blockquote>\n<pre><code class=\"language-sql line-numbers\"> select * from table_name order by rand() limit 100;\n create table able_name1 as select * from able_name tablesample(10 percent);\n select * from able_name2 tablesample(bucket 1 out of 10 on rand())\n<\/code><\/pre>\n<blockquote><p>\n  \u5b9e\u4f8b\n<\/p><\/blockquote>\n<p>\u67e5\u8be2\u6210\u7ee9\u524d\u4e09\u540d<\/p>\n<pre><code class=\"language-sql line-numbers\">select class, name, score\nfrom test_score a\nwhere (select count(*) from test_score where class = a.class and a.score &lt; score) &lt; 3\norder by a.class, a.score desc;\n#fa2\nselect class, name, score\nfrom (select class, score, name,\n             rank() over (partition by class order by score desc ) as rownum\n      from test_score)\nwhere rownum &lt; 4;\n<\/code><\/pre>\n<p>\u5c55\u793a\u51fa\u6700\u5927\u8fde\u7eed\u767b\u9646\u5929\u6570(\u6bcf\u5929\u53ef\u80fd\u4f1a\u767b\u9646\u591a\u6b21)<\/p>\n<pre><code class=\"language-bash line-numbers\">user_num mobile\nss11    2021-04-01\nss11    2021-04-02\nss11    2021-04-03\nss11    2021-04-05\n<\/code><\/pre>\n<pre><code class=\"language-sql line-numbers\">select\ntongji.user_num,tongji.start_day,tongji.end_day,max(tongji.continue_days)\nfrom\n(\nselect x.user_num,min(x.pt_day) start_day,max(x.pt_day) end_day,count(*) continue_days\nfrom \n(\nselect a.user_num, to_date(a.mobile) pt_day,row_number()over (partition by a.user_num order by to_date(a.mobile)) rn \nfrom(select distinct user_num,mobile from user_login_test) a) x group by x.user_num,date_sub(x.pt_day,x.rn-1)\n) tongji;\n<\/code><\/pre>\n<p>\u62a5\u9519\u5f85\u89e3\u51b3\u4e0a\u9762\u8bed\u53e5<br \/>\n<code>Error while compiling statement: FAILED: SemanticException [Error 10025]: line 2:0 Expression not in GROUP BY key 'user_num'<\/code><\/p>\n<blockquote><p>\n  \u5982\u4f55\u89e3\u51b3\u6570\u636e\u503e\u659c\u7684\u95ee\u9898\uff1f\n<\/p><\/blockquote>\n<ul>\n<li>\u6570\u636e\u503e\u659c\u662fhive\u8c03\u4f18\u9762\u4e34\u7684\u91cd\u8981\u539f\u56e0\uff0c\u8c03\u4f18\u524d\u9996\u5148\u660e\u786e\u4ec0\u4e48\u662f\u6570\u636e\u503e\u659c\uff0c\u6570\u636e\u503e\u659c\u8868\u73b0\u4e3amap\u7aef\u5df2\u7ecf\u5b8c\u6210100%\uff0c\u4f46\u662freduce\u7aef\u4e00\u76f4\u5904\u4e8e99%\u7684\u72b6\u6001\u3002<\/li>\n<\/ul>\n<ol>\n<li>\u5927\u5c0f\u8868join\u65f6\u7684\u6570\u636e\u503e\u659c<\/li>\n<li>group by<\/li>\n<li>count\uff08distinct\uff09<\/li>\n<\/ol>\n<ul>\n<li>\u503e\u659c\u539f\u56e0\uff1a<\/li>\n<\/ul>\n<p>map\u8f93\u51fa\u6570\u636e\u6309key Hash\u7684\u5206\u914d\u5230reduce\u4e2d\uff0c\u7531\u4e8ekey\u5206\u5e03\u4e0d\u5747\u5300\u3001\u4e1a\u52a1\u6570\u636e\u672c\u8eab\u7684\u7279\u3001\u5efa\u8868\u65f6\u8003\u8651\u4e0d\u5468\u3001\u7b49\u539f\u56e0\u9020\u6210\u7684reduce \u4e0a\u7684\u6570\u636e\u91cf\u5dee\u5f02\u8fc7\u5927\u3002<br \/>\n\uff081\uff09key\u5206\u5e03\u4e0d\u5747\u5300;<br \/>\n\uff082\uff09\u4e1a\u52a1\u6570\u636e\u672c\u8eab\u7684\u7279\u6027;<br \/>\n\uff083\uff09\u5efa\u8868\u65f6\u8003\u8651\u4e0d\u5468;<br \/>\n\uff084\uff09\u67d0\u4e9bSQL\u8bed\u53e5\u672c\u8eab\u5c31\u6709\u6570\u636e\u503e\u659c;<br \/>\n\u5982\u4f55\u907f\u514d\uff1a\u5bf9\u4e8ekey\u4e3a\u7a7a\u4ea7\u751f\u7684\u6570\u636e\u503e\u659c\uff0c\u53ef\u4ee5\u5bf9\u5176\u8d4b\u4e88\u4e00\u4e2a\u968f\u673a\u503c\u3002<\/p>\n<ul>\n<li>\u89e3\u51b3\u65b9\u6848<\/li>\n<\/ul>\n<ol>\n<li>\u53c2\u6570\u8c03\u8282\uff1a<\/li>\n<\/ol>\n<pre data-language=XML><code class=\"language-markup line-numbers\">#Map \u7aef\u90e8\u5206\u805a\u5408\uff0c\u76f8\u5f53\u4e8eCombiner\nhive.map.aggr = true\n\u6709\u6570\u636e\u503e\u659c\u7684\u65f6\u5019\u8fdb\u884c\u8d1f\u8f7d\u5747\u8861\uff0c\u5f53\u9009\u9879\u8bbe\u5b9a\u4f4dtrue,\u751f\u6210\u7684\u67e5\u8be2\u8ba1\u5212\u4f1a\u6709\u4e24\u4e2aMR Job\u3002\n\u7b2c\u4e00\u4e2aMR Job\u4e2d\uff0cMap\u7684\u8f93\u51fa\u7ed3\u679c\u96c6\u5408\u4f1a\u968f\u673a\u5206\u5e03\u5230Reduce\u4e2d\uff0c\u6bcf\u4e2aReduce\u505a\u90e8\u5206\u805a\u5408\u64cd\u4f5c\uff0c\u5e76\u8f93\u51fa\u7ed3\u679c\uff0c\u8fd9\u6837\u5904\u7406\u7684\u7ed3\u679c\u662f\u76f8\u540c\u7684Group By Key\u6709\u53ef\u80fd\u88ab\u5206\u53d1\u5230\u4e0d\u540c\u7684Reduce\u4e2d\uff0c\u4ece\u800c\u8fbe\u5230\u8d1f\u8f7d\u5747\u8861\u7684\u76ee\u7684\n\u7b2c\u4e8c\u4e2aMR Job\u518d\u6839\u636e\u9884\u5904\u7406\u7684\u6570\u636e\u7ed3\u679c\u6309\u7167Group By Key \u5206\u5e03\u5230 Reduce \u4e2d\uff08\u8fd9\u4e2a\u8fc7\u7a0b\u53ef\u4ee5\u4fdd\u8bc1\u76f8\u540c\u7684 Group By Key \u88ab\u5206\u5e03\u5230\u540c\u4e00\u4e2aReduce\u4e2d\uff09\uff0c\u6700\u540e\u5b8c\u6210\u6700\u7ec8\u7684\u805a\u5408\u64cd\u4f5c\nhive.groupby.skewindata=true\n<\/code><\/pre>\n<ol start=\"2\">\n<li>SQL \u8bed\u53e5\u8c03\u8282<\/li>\n<\/ol>\n<ul>\n<li>\u5927\u5c0f\u8868Join\uff1a<\/li>\n<\/ul>\n<p>\u4f7f\u7528map join\u8ba9\u5c0f\u7684\u7ef4\u5ea6\u8868\uff081000 \u6761\u4ee5\u4e0b\u7684\u8bb0\u5f55\u6761\u6570\uff09\u5148\u8fdb\u5185\u5b58\u3002\u5728map\u7aef\u5b8c\u6210reduce.<\/p>\n<ul>\n<li>\u5927\u8868Join\u5927\u8868\uff1a<\/li>\n<\/ul>\n<p>\u628a\u7a7a\u503c\u7684key\u53d8\u6210\u4e00\u4e2a\u5b57\u7b26\u4e32\u52a0\u4e0a\u968f\u673a\u6570\uff0c\u628a\u503e\u659c\u7684\u6570\u636e\u5206\u5230\u4e0d\u540c\u7684reduce\u4e0a\uff0c\u7531\u4e8enull \u503c\u5173\u8054\u4e0d\u4e0a\uff0c\u5904\u7406\u540e\u5e76\u4e0d\u5f71\u54cd\u6700\u7ec8\u7ed3\u679c\u3002<\/p>\n<ul>\n<li>count distinct\u5927\u91cf\u76f8\u540c\u7279\u6b8a\u503c:<\/li>\n<\/ul>\n<p>count distinct \u65f6\uff0c\u5c06\u503c\u4e3a\u7a7a\u7684\u60c5\u51b5\u5355\u72ec\u5904\u7406\uff0c\u5982\u679c\u662f\u8ba1\u7b97count distinct\uff0c\u53ef\u4ee5\u4e0d\u7528\u5904\u7406\uff0c\u76f4\u63a5\u8fc7\u6ee4\uff0c\u5728\u6700\u540e\u7ed3\u679c\u4e2d\u52a01\u3002\u5982\u679c\u8fd8\u6709\u5176\u4ed6\u8ba1\u7b97\uff0c\u9700\u8981\u8fdb\u884cgroup by\uff0c\u53ef\u4ee5\u5148\u5c06\u503c\u4e3a\u7a7a\u7684\u8bb0\u5f55\u5355\u72ec\u5904\u7406\uff0c\u518d\u548c\u5176\u4ed6\u8ba1\u7b97\u7ed3\u679c\u8fdb\u884cunion\u3002<\/p>\n<ol start=\"3\">\n<li>\u9047\u5230\u9700\u8981\u8fdb\u884cjoin\u7684\u4f46\u662f\u5173\u8054\u5b57\u6bb5\u6709\u6570\u636e\u4e3anull\uff0c\u5982\u8868\u4e00\u7684id\u9700\u8981\u548c\u8868\u4e8c\u7684id\u8fdb\u884c\u5173\u8054\uff0cnull\u503c\u7684reduce\u5c31\u4f1a\u843d\u5230\u4e00\u4e2a\u8282\u70b9\u4e0a<\/li>\n<\/ol>\n<p>\u89e3\u51b3\u65b9\u6cd51\uff1a\u5b50\u67e5\u8be2\u4e2d\u8fc7\u6ee4\u6389null\u503c\uff0cid\u4e3a\u7a7a\u7684\u4e0d\u53c2\u4e0e\u5173\u8054<\/p>\n<pre><code class=\"language-sql line-numbers\">select a.user_id, a.order_id, b.user_id\nfrom table_a a join table_b b\non (case when a.user_is is null then concat('hive', rand()) else a.user_id end) = b.user_id\n<\/code><\/pre>\n<p>\u89e3\u51b3\u65b9\u6cd52\uff1a\u7528case when\u7ed9\u7a7a\u503c\u5206\u914d\u968f\u673a\u7684key\u503c\uff08\u5b57\u7b26\u4e32+rand()\uff09<\/p>\n<ol start=\"4\">\n<li>\u4e0d\u540c\u6570\u636e\u7c7b\u578b\u5173\u8054\u4ea7\u751f\u6570\u636e\u503e\u659c<\/li>\n<\/ol>\n<p>\u5f20\u8868s8\u7684\u65e5\u5fd7\uff0c\u6bcf\u4e2a\u5546\u54c1\u4e00\u6761\u8bb0\u5f55\uff0c\u8981\u548c\u5546\u54c1\u8868\u5173\u8054\u3002\u4f46\u5173\u8054\u5374\u78b0\u5230\u503e\u659c\u7684\u95ee\u9898\u3002s8\u7684\u65e5\u5fd7\u4e2d\u6709\u5b57\u7b26\u4e32\u5546\u54c1id,\u4e5f\u6709\u6570\u5b57\u7684\u5546\u54c1id,\u7c7b\u578b\u662fstring\u7684\uff0c\u4f46\u5546\u54c1\u4e2d\u7684\u6570\u5b57id\u662fbigint\u7684\u3002\u731c\u6d4b\u95ee\u9898\u7684\u539f\u56e0\u662f\u628as8\u7684\u5546\u54c1id\u8f6c\u6210\u6570\u5b57id\u505ahash\u6765\u5206\u914dreduce\uff0c\u6240\u4ee5\u5b57\u7b26\u4e32id\u7684s8\u65e5\u5fd7\uff0c\u90fd\u5230\u4e00\u4e2areduce\u4e0a\u4e86\uff0c\u89e3\u51b3\u7684\u65b9\u6cd5\u9a8c\u8bc1\u4e86\u8fd9\u4e2a\u731c\u6d4b\u3002<\/p>\n<p>\u89e3\u51b3\u65b9\u6cd5\uff1a\u628a\u6570\u5b57\u7c7b\u578b\u8f6c\u6362\u6210\u5b57\u7b26\u4e32\u7c7b\u578b<\/p>\n<ol start=\"5\">\n<li>\u5f53HiveQL\u4e2d\u5305\u542bcount\uff08distinct\uff09\u65f6<\/li>\n<\/ol>\n<p>\u5982\u679c\u6570\u636e\u91cf\u975e\u5e38\u5927\uff0c\u6267\u884c\u5982select a,count(distinct b) from t group by a;\u7c7b\u578b\u7684SQL\u65f6\uff0c\u4f1a\u51fa\u73b0\u6570\u636e\u503e\u659c\u7684\u95ee\u9898\u3002<\/p>\n<p>\u89e3\u51b3\u65b9\u6cd5\uff1a\u4f7f\u7528sum&#8230;group by\u4ee3\u66ff\u3002\u5982select a,sum(1) from (select a, b from t group by a,b) group by a;<\/p>\n<blockquote><p>\n  \u8bf7\u8bf4\u660ehive\u4e2d Sort By\uff0cOrder By\uff0cCluster By\uff0cDistrbute By\u5404\u4ee3\u8868\u4ec0\u4e48\u610f\u601d\uff1f\n<\/p><\/blockquote>\n<p><strong>order by<\/strong>\uff1a\u4f1a\u5bf9\u8f93\u5165\u505a\u5168\u5c40\u6392\u5e8f\uff0c\u56e0\u6b64\u53ea\u6709\u4e00\u4e2areducer\uff08\u591a\u4e2areducer\u65e0\u6cd5\u4fdd\u8bc1\u5168\u5c40\u6709\u5e8f\uff09\u3002\u53ea\u6709\u4e00\u4e2areducer\uff0c\u4f1a\u5bfc\u81f4\u5f53\u8f93\u5165\u89c4\u6a21\u8f83\u5927\u65f6\uff0c\u9700\u8981\u8f83\u957f\u7684\u8ba1\u7b97\u65f6\u95f4\u3002<br \/>\n<strong>sort by<\/strong>\uff1a\u4e0d\u662f\u5168\u5c40\u6392\u5e8f\uff0c\u5176\u5728\u6570\u636e\u8fdb\u5165reducer\u524d\u5b8c\u6210\u6392\u5e8f\u3002<br \/>\n<strong>distribute by<\/strong>\uff1a\u6309\u7167\u6307\u5b9a\u7684\u5b57\u6bb5\u5bf9\u6570\u636e\u8fdb\u884c\u5212\u5206\u8f93\u51fa\u5230\u4e0d\u540c\u7684reduce\u4e2d\u3002<br \/>\n<strong>cluster by<\/strong>\uff1a\u9664\u4e86\u5177\u6709 distribute by \u7684\u529f\u80fd\u5916\u8fd8\u517c\u5177 sort by \u7684\u529f\u80fd\u3002<\/p>\n<blockquote><p>\n  Hive\u5185\u90e8\u8868\u548c\u5916\u90e8\u8868\u7684\u533a\u522b\uff1f\n<\/p><\/blockquote>\n<p>\u521b\u5efa\u8868\u65f6\uff1a\u521b\u5efa\u5185\u90e8\u8868\u65f6\uff0c\u4f1a\u5c06\u6570\u636e\u79fb\u52a8\u5230\u6570\u636e\u4ed3\u5e93\u6307\u5411\u7684\u8def\u5f84\uff1b\u82e5\u521b\u5efa\u5916\u90e8\u8868\uff0c\u4ec5\u8bb0\u5f55\u6570\u636e\u6240\u5728\u7684\u8def\u5f84\uff0c \u4e0d\u5bf9\u6570\u636e\u7684\u4f4d\u7f6e\u505a\u4efb\u4f55\u6539\u53d8\u3002<br \/>\n\u5220\u9664\u8868\u65f6\uff1a\u5728\u5220\u9664\u8868\u7684\u65f6\u5019\uff0c\u5185\u90e8\u8868\u7684\u5143\u6570\u636e\u548c\u6570\u636e\u4f1a\u88ab\u4e00\u8d77\u5220\u9664\uff0c \u800c\u5916\u90e8\u8868\u53ea\u5220\u9664\u5143\u6570\u636e\uff0c\u4e0d\u5220\u9664\u6570\u636e\u3002\u8fd9\u6837\u5916\u90e8\u8868\u76f8\u5bf9\u6765\u8bf4\u66f4\u52a0\u5b89\u5168\u4e9b\uff0c\u6570\u636e\u7ec4\u7ec7\u4e5f\u66f4\u52a0\u7075\u6d3b\uff0c\u65b9\u4fbf\u5171\u4eab\u6e90\u6570\u636e\u3002<\/p>\n<h3><span class=\"ez-toc-section\" id=\"%E8%BD%AC%E6%8D%A2%E6%97%A5%E6%9C%9F%E7%9A%84%E5%87%BD%E6%95%B0\"><\/span>\u8f6c\u6362\u65e5\u671f\u7684\u51fd\u6570<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<pre><code class=\"language-scala line-numbers\">def dateFormat(date: String) = {\n    val parser = new SimpleDateFormat(\"dd\/MMM\/yyyy:HH:mm:ss\", Locale.US)\n    val formatter = new SimpleDateFormat(\"yyyy-MM-dd HH:mm:ss\")\n    formatter.format(parser.parse(date))\n  }\n<\/code><\/pre>\n<blockquote><p>\n  \u8bf4\u8bf4\u5bf9Hive\u6876\u8868\u7684\u7406\u89e3\n<\/p><\/blockquote>\n<p>\u6876\u8868\u662f\u5bf9\u6570\u636e\u8fdb\u884c\u54c8\u5e0c\u53d6\u503c\uff0c\u7136\u540e\u653e\u5230\u4e0d\u540c\u6587\u4ef6\u4e2d\u5b58\u50a8\u3002<br \/>\n\u6570\u636e\u52a0\u8f7d\u5230\u6876\u8868\u65f6\uff0c\u4f1a\u5bf9\u5b57\u6bb5\u53d6hash\u503c\uff0c\u7136\u540e\u4e0e\u6876\u7684\u6570\u91cf\u53d6\u6a21\u3002\u628a\u6570\u636e\u653e\u5230\u5bf9\u5e94\u7684\u6587\u4ef6\u4e2d\u3002\u7269\u7406\u4e0a\uff0c\u6bcf\u4e2a\u6876\u5c31\u662f\u8868(\u6216\u5206\u533a\uff09\u76ee\u5f55\u91cc\u7684\u4e00\u4e2a\u6587\u4ef6\uff0c\u4e00\u4e2a\u4f5c\u4e1a\u4ea7\u751f\u7684\u6876(\u8f93\u51fa\u6587\u4ef6)\u548creduce\u4efb\u52a1\u4e2a\u6570\u76f8\u540c\u3002<br \/>\n\u6876\u8868\u4e13\u95e8\u7528\u4e8e\u62bd\u6837\u67e5\u8be2\uff0c\u662f\u5f88\u4e13\u4e1a\u6027\u7684\uff0c\u4e0d\u662f\u65e5\u5e38\u7528\u6765\u5b58\u50a8\u6570\u636e\u7684\u8868\uff0c\u9700\u8981\u62bd\u6837\u67e5\u8be2\u65f6\uff0c\u624d\u521b\u5efa\u548c\u4f7f\u7528\u6876\u8868\u3002<\/p>\n<ul>\n<li>\u5206\u533a\u8868\u4e0e\u5206\u6876\u8868\u7684\u533a\u522b<\/li>\n<\/ul>\n<p>\u5206\u533a\u8868\u5b9e\u9645\u4e0a\u5c31\u662f\u5bf9\u5e94\u4e00\u4e2a HDFS \u6587\u4ef6\u7cfb\u7edf\u4e0a\u7684\u72ec\u7acb\u7684\u6587\u4ef6\u5939\uff0c\u8be5\u6587\u4ef6\u5939\u4e0b\u662f\u8be5\u5206\u533a\u6240\u6709\u7684\u6570\u636e\u6587\u4ef6\u3002Hive \u4e2d\u7684\u5206\u533a\u5c31\u662f\u5206\u76ee\u5f55\uff0c\u628a\u4e00\u4e2a\u5927\u7684\u6570\u636e\u96c6\u6839\u636e\u4e1a\u52a1\u9700\u8981\u5206\u5272\u6210\u5c0f\u7684\u6570\u636e\u96c6\u3002\u5728\u67e5\u8be2\u65f6\u901a\u8fc7 WHERE \u5b50\u53e5\u4e2d\u7684\u8868\u8fbe\u5f0f\u9009\u62e9\u67e5\u8be2\u6240\u9700\u8981\u7684\u6307\u5b9a\u7684\u5206\u533a\uff0c\u8fd9\u6837\u7684\u67e5\u8be2\u6548\u7387\u4f1a\u63d0\u9ad8\u5f88\u591a\u3002<br \/>\n\u5e76\u975e\u6240\u6709\u7684\u6570\u636e\u96c6\u90fd\u53ef\u5f62\u6210\u5408\u7406\u7684\u5206\u533a\u3002\u5bf9\u4e8e\u4e00\u5f20\u8868\u6216\u8005\u5206\u533a\uff0cHive \u53ef\u4ee5\u8fdb\u4e00\u6b65\u7ec4\u7ec7\u6210\u6876\uff0c\u4e5f\u5c31\u662f\u66f4\u4e3a\u7ec6\u7c92\u5ea6\u7684\u6570\u636e\u8303\u56f4\u5212\u5206\u3002<\/p>\n<p>\u5206\u6876\u662f\u5c06\u6570\u636e\u96c6\u5206\u89e3\u6210\u66f4\u5bb9\u6613\u7ba1\u7406\u7684\u82e5\u5e72\u90e8\u5206\u7684\u53e6\u4e00\u4e2a\u6280\u672f\u3002<\/p>\n<p>\u5206\u533a\u9488\u5bf9\u7684\u662f\u6570\u636e\u7684\u5b58\u50a8\u8def\u5f84\uff1b\u5206\u6876\u9488\u5bf9\u7684\u662f\u6570\u636e\u6587\u4ef6\u3002<\/p>\n<p>Hive\u7684\u5206\u6876\u91c7\u7528\u5bf9\u5206\u6876\u5b57\u6bb5\u7684\u503c\u8fdb\u884c\u54c8\u5e0c\uff0c\u7136\u540e\u9664\u4ee5\u6876\u7684\u4e2a\u6570\u6c42\u4f59\u7684\u65b9\u5f0f\u51b3\u5b9a\u8be5\u6761\u8bb0\u5f55\u5b58\u653e\u5728\u54ea\u4e2a\u6876\u5f53\u4e2d<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Hive_sql%E8%AF%AD%E5%8F%A5%E6%8F%90%E4%BA%A4%E5%90%8E%E6%89%A7%E8%A1%8C%E8%BF%87%E7%A8%8B\"><\/span>Hive sql\u8bed\u53e5\u63d0\u4ea4\u540e\u6267\u884c\u8fc7\u7a0b<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>\u9996\u5148\u770b\u8f6c\u6362\u8fc7\u7a0b\uff0c\u518d\u5c55\u793a\u6267\u884c\u8fc7\u7a0b<\/p>\n<h4><span class=\"ez-toc-section\" id=\"Hive%E7%9A%84HSQL%E8%BD%AC%E6%8D%A2%E4%B8%BAMapReduce%E7%9A%84%E8%BF%87%E7%A8%8B\"><\/span>Hive\u7684HSQL\u8f6c\u6362\u4e3aMapReduce\u7684\u8fc7\u7a0b<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>SQL Parser\uff1aAntlr\u5b9a\u4e49SQL\u7684\u8bed\u6cd5\u89c4\u5219\uff0c\u5b8c\u6210SQL\u8bcd\u6cd5\uff0c\u8bed\u6cd5\u89e3\u6790\uff0c\u5c06SQL\u8f6c\u5316\u4e3a\u62bd\u8c61 \u8bed\u6cd5\u6811AST Tree\uff1b<br \/>\nSemantic Analyzer\uff1a\u904d\u5386AST Tree\uff0c\u62bd\u8c61\u51fa\u67e5\u8be2\u7684\u57fa\u672c\u7ec4\u6210\u5355\u5143QueryBlock\uff1b<br \/>\nLogical plan\uff1a\u904d\u5386QueryBlock\uff0c\u7ffb\u8bd1\u4e3a\u6267\u884c\u64cd\u4f5c\u6811OperatorTree\uff1b<br \/>\nLogical plan optimizer: \u903b\u8f91\u5c42\u4f18\u5316\u5668\u8fdb\u884cOperatorTree\u53d8\u6362\uff0c\u5408\u5e76\u4e0d\u5fc5\u8981\u7684ReduceSinkOperator\uff0c\u51cf\u5c11shuffle\u6570\u636e\u91cf\uff1b<br \/>\nPhysical plan\uff1a\u904d\u5386OperatorTree\uff0c\u7ffb\u8bd1\u4e3aMapReduce\u4efb\u52a1\uff1b<br \/>\nLogical plan optimizer\uff1a\u7269\u7406\u5c42\u4f18\u5316\u5668\u8fdb\u884cMapReduce\u4efb\u52a1\u7684\u53d8\u6362\uff0c\u751f\u6210\u6700\u7ec8\u7684\u6267\u884c\u8ba1\u5212\uff1b<\/p>\n<pre><code class=\"language-bash line-numbers\">job_1604020633149_0009\nINFO  : Compiling command(queryId=hive_20201116200245_6ca5200e-1ea5-41ef-8048-ab1b47d4e07e): SELECT *,row_number() OVER(PARTITION BY c ORDER BY c DESC)as tn FROM east\nINFO  : Semantic Analysis Completed\nINFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:east.producename, type:string, comment:null), FieldSchema(name:east.c, type:double, comment:null), FieldSchema(name:tn, type:int, comment:null)], properties:null)\nINFO  : Completed compiling command(queryId=hive_20201116200245_6ca5200e-1ea5-41ef-8048-ab1b47d4e07e); Time taken: 0.122 seconds\nINFO  : Executing command(queryId=hive_20201116200245_6ca5200e-1ea5-41ef-8048-ab1b47d4e07e): SELECT *,row_number() OVER(PARTITION BY c ORDER BY c DESC)as tn FROM east\nWARN  : \nINFO  : Query ID = hive_20201116200245_6ca5200e-1ea5-41ef-8048-ab1b47d4e07e\nINFO  : Total jobs = 1\nINFO  : Launching Job 1 out of 1\nINFO  : Starting task [Stage-1:MAPRED] in serial mode\nINFO  : Number of reduce tasks not specified. Estimated from input data size: 1\nINFO  : In order to change the average load for a reducer (in bytes):\nINFO  :   set hive.exec.reducers.bytes.per.reducer=&lt;number&gt;\nINFO  : In order to limit the maximum number of reducers:\nINFO  :   set hive.exec.reducers.max=&lt;number&gt;\nINFO  : In order to set a constant number of reducers:\nINFO  :   set mapreduce.job.reduces=&lt;number&gt;\nINFO  : number of splits:1\nINFO  : Submitting tokens for job: job_1604020633149_0009\nINFO  : Executing with tokens: []\nINFO  : The url to track the job: http:\/\/cdh0052:8088\/proxy\/application_1604020633149_0009\/\nINFO  : Starting Job = job_1604020633149_0009, Tracking URL = http:\/\/cdh0052:8088\/proxy\/application_1604020633149_0009\/\nINFO  : Kill Command = \/opt\/cloudera\/parcels\/CDH-6.3.2-1.cdh6.3.2.p0.1605554\/lib\/hadoop\/bin\/hadoop job  -kill job_1604020633149_0009\nINFO  : Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1\nINFO  : 2020-11-16 20:03:02,634 Stage-1 map = 0%,  reduce = 0%\nINFO  : 2020-11-16 20:03:20,130 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 9.98 sec\nINFO  : 2020-11-16 20:03:31,485 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 19.18 sec\nINFO  : MapReduce Total cumulative CPU time: 19 seconds 180 msec\nINFO  : Ended Job = job_1604020633149_0009\nINFO  : MapReduce Jobs Launched: \nINFO  : Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 19.18 sec   HDFS Read: 9532 HDFS Write: 463 HDFS EC Read: 0 SUCCESS\nINFO  : Total MapReduce CPU Time Spent: 19 seconds 180 msec\nINFO  : Completed executing command(queryId=hive_20201116200245_6ca5200e-1ea5-41ef-8048-ab1b47d4e07e); Time taken: 47.407 seconds\nINFO  : OK\n<\/code><\/pre>\n<blockquote><p>\n  Hive\u662f\u5982\u4f55\u5b9e\u73b0\u5206\u533a\u7684\n<\/p><\/blockquote>\n<ol>\n<li>\u5efa\u8868\u8bed\u53e5<\/li>\n<\/ol>\n<pre><code class=\"language-sql line-numbers\">CREATE EXTERNAL TABLE IF NOT EXISTS category_ods_t\n(\ncategoryID INT COMMENT \"\u4ea7\u54c1\u7c7b\u522bID\",\ncategoryName STRING COMMENT \"\u4ea7\u54c1\u7c7b\u522b\u540d\u79f0\"\n)\nCOMMENT \"\u4ea7\u54c1\u76ee\u5f55\u8868\"\npartitioned by (year string,month string,day string)\n<\/code><\/pre>\n<ol start=\"2\">\n<li>\u589e\u52a0\u5206\u533a<\/li>\n<\/ol>\n<pre><code class=\"language-sql line-numbers\"> alter table tablename add partition(dt='2020-11-16')\n<\/code><\/pre>\n<ol start=\"3\">\n<li>\u5220\u9664\u5206\u533a<\/li>\n<\/ol>\n<pre><code class=\"language-sql line-numbers\">alter table tablename drop partition(dt='2020-11-16')\n<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"%E7%AA%97%E5%8F%A3%E5%87%BD%E6%95%B0\"><\/span>\u7a97\u53e3\u51fd\u6570<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>\u7a97\u53e3\u51fd\u6570\uff0c\u4e5f\u53ebOLAP\u51fd\u6570\uff08Online Anallytical Processing\uff0c\u8054\u673a\u5206\u6790\u5904\u7406\uff09\uff0c\u53ef\u4ee5\u5bf9\u6570\u636e\u5e93\u6570\u636e\u8fdb\u884c\u5b9e\u65f6\u5206\u6790\u5904\u7406\u3002<\/p>\n<pre><code class=\"language-sql line-numbers\">&lt;\u7a97\u53e3\u51fd\u6570&gt; over (partition by &lt;\u7528\u4e8e\u5206\u7ec4\u7684\u5217\u540d&gt; order by &lt;\u7528\u4e8e\u6392\u5e8f\u7684\u5217\u540d&gt;)\nSELECT *,row_number() OVER(PARTITION BY c ORDER BY c DESC)as tn FROM east\n<\/code><\/pre>\n<ul>\n<li>\u4e3a\u4ec0\u4e48\u8981\u7528\u7a97\u53e3\u51fd\u6570\u5462<\/li>\n<\/ul>\n<p>\u7edf\u8ba1\u73ed\u7ea7\u4eba\u6570<\/p>\n<pre><code class=\"language-sql line-numbers\">select classname,count(stuid) as s from student group by classname order by s;\nselect classname,count(stuid) over(PARTITION BY classname ORDER BY classname DESC) from student\n<\/code><\/pre>\n<p>partition by\u5206\u7ec4\u540e\u7684\u7ed3\u679c\u79f0\u4e3a\u201c\u7a97\u53e3\u201d\uff0c\u8fd9\u91cc\u7684\u7a97\u53e3\u4e0d\u662f\u6211\u4eec\u5bb6\u91cc\u7684\u95e8\u7a97\uff0c\u800c\u662f\u8868\u793a\u201c\u8303\u56f4\u201d\u7684\u610f\u601d\u3002\u7b80\u5355\u6765\u8bf4\uff0c\u7a97\u53e3\u51fd\u6570\u6709\u4ee5\u4e0b\u529f\u80fd\uff1a\u540c\u65f6\u5177\u6709\u5206\u7ec4\u548c\u6392\u5e8f\u7684\u529f\u80fd,\u4e0d\u51cf\u5c11\u539f\u8868\u7684\u884c\u6570<\/p>\n<ul>\n<li>\u51e0\u79cd\u4e13\u7528\u7a97\u53e3\u51fd\u6570\u7684\u5bf9\u6bd4<\/li>\n<\/ul>\n<p>\u6bd4\u5982\u7ed9\u51fa\u6392\u5e8f\u540e\u7684\u6570\u636e\u662f100,99,77,77,66<\/p>\n<pre><code class=\"language-sql line-numbers\">#\u6392\u540d\u7ed3\u679c\uff1a1\uff1a100,2:99,3:77,4:77,5:66\nrow_number() over(partition by regionX order by nameX desc) as tn\n#\u6392\u540d\u7ed3\u679c\uff1a1\uff1a100,2:99,3:77,3:77,5:66\nrank() over(partiition by regionX order by nameX desc) as tn\n#\u6392\u540d\u7ed3\u679c\uff1a1\uff1a100,2:99,3:77,3:77,4:66\ndense_rank() over()\n<\/code><\/pre>\n<ul>\n<li>\u805a\u5408\u51fd\u6570\u4f5c\u4e3a\u7a97\u53e3\u51fd\u6570<\/li>\n<\/ul>\n<pre><code class=\"language-sql line-numbers\">select *,\n   sum(\u6210\u7ee9) over (order by \u5b66\u53f7) as current_sum,\n   avg(\u6210\u7ee9) over (order by \u5b66\u53f7) as current_avg,\n   count(\u6210\u7ee9) over (order by \u5b66\u53f7) as current_count,\n   max(\u6210\u7ee9) over (order by \u5b66\u53f7) as current_max,\n   min(\u6210\u7ee9) over (order by \u5b66\u53f7) as current_min\nfrom \u73ed\u7ea7\u8868\n<\/code><\/pre>\n<p>\u805a\u5408\u51fd\u6570sum\u5728\u7a97\u53e3\u51fd\u6570\u4e2d\uff0c\u662f\u5bf9\u81ea\u8eab\u8bb0\u5f55\u3001\u53ca\u4f4d\u4e8e\u81ea\u8eab\u8bb0\u5f55\u4ee5\u4e0a\u7684\u6570\u636e\u8fdb\u884c\u6c42\u548c\u7684\u7ed3\u679c\u3002\u6bd4\u5982\u770b\u7b2c\u4e09\u884c\u7684\u805a\u5408\u51fd\u6570\u8fd0\u7b97\u540e\u7684\u6570\u636e\uff0c\u5728\u4f7f\u7528sum\u7a97\u53e3\u51fd\u6570\u540e\u7684\u7ed3\u679c\uff0c\u662f\u5bf9\u7b2c\u4e00\u884c\uff0c\u7b2c\u4e8c\u884c\u6570\u636e\u6c42\u548c\uff0c\u82e5\u662f\u7b2c10\u884c\uff0c\u5219\u7ed3\u679c\u662f\u7b2c\u4e00\u884c\u7b2c\u5341\u884c\u6570\u636e\u7684\u6c42\u548c\uff1b<br \/>\n\u540c\u7406\uff0c\u5176\u4ed6\u805a\u5408\u51fd\u6570\u90fd\u662f\u9488\u5bf9\u81ea\u8eab\u8bb0\u5f55\u3001\u4ee5\u53ca\u81ea\u8eab\u8bb0\u5f55\u4e4b\u4e0a\u7684\u6240\u6709\u6570\u636e\u8fdb\u884c\u8ba1\u7b97\uff1b<br \/>\n<code>\u805a\u5408\u51fd\u6570\u4f5c\u4e3a\u7a97\u53e3\u51fd\u6570<\/code>\uff0c\u53ef\u4ee5\u5728\u6bcf\u4e00\u884c\u7684\u6570\u636e\u91cc\u76f4\u89c2\u7684\u770b\u5230\uff0c\u622a\u6b62\u5230\u672c\u884c\u6570\u636e\uff0c\u7edf\u8ba1\u6570\u636e\u662f\u591a\u5c11\uff08\u6700\u5927\u503c\u3001\u6700\u5c0f\u503c\u7b49\uff09\u3002\u540c\u65f6\u53ef\u4ee5\u770b\u51fa\u6bcf\u4e00\u884c\u6570\u636e\uff0c\u5bf9\u6574\u4f53\u7edf\u8ba1\u6570\u636e\u7684\u5f71\u54cd\u3002<\/p>\n<blockquote><p>\n  hive\u5bf9\u7a7a\u503c\u7684\u5904\u7406\n<\/p><\/blockquote>\n<p>null\u5728hive\u5e95\u5c42\u9ed8\u8ba4\u662f\u2f64&#8221;\\N&#8221;\u6765\u5b58\u50a8\u7684\uff0c\u6240\u4ee5\u5728sqoop\u5230mysql\u4e4b\u524d\u9700\u8981\u5c06\u4e3anull\u7684\u6570\u636e\u52a0\u2f2f\u6210\u5176\u4ed6\u5b57\u7b26\uff0c\u5426\u5219sqoop\u63d0\u793a\u9519\u8bef\uff0c\u662f\u7531 alter table name SET SERDEPROPERTIES(&#8216;serialization.null.format&#8217; = &#8216;\\N&#8217;); \u53c2\u6570\u63a7\u5236\u7684<\/p>\n<ul>\n<li>\u4e0d\u540c\u6570\u636e\u7c7b\u578b\u5bf9\u7a7a\u503c\u7684\u5b58\u50a8\u89c4\u5219<\/li>\n<\/ul>\n<p>int\u4e0estring\u7c7b\u578b\u6570\u636e\u5b58\u50a8\uff0cnull\u9ed8\u8ba4\u5b58\u50a8\u4e3a \\N\uff1b<\/p>\n<p>string\u7c7b\u578b\u7684\u6570\u636e\u5982\u679c\u4e3a&#8221;&#8221;\uff0c\u5b58\u50a8\u5219\u662f&#8221;&#8221;\uff1b<\/p>\n<p>\u53e6\u5916\u5f80int\u7c7b\u578b\u7684\u5b57\u6bb5\u63d2\u5165\u6570\u636e\u201c\u201d\u65f6\uff0c\u7ed3\u679c\u8fd8\u662f\\N\u3002<\/p>\n<ul>\n<li>\u4e0d\u540c\u6570\u636e\u7c7b\u578b\uff0c\u7a7a\u503c\u7684\u67e5\u8be2<\/li>\n<\/ul>\n<p>\u5bf9\u4e8eint\u53ef\u4ee5\u4f7f\u7528is null\u6765\u5224\u65ad\u7a7a\uff1b<\/p>\n<p>\u800c\u5bf9\u4e8estring\u7c7b\u578b\uff0c\u6761\u4ef6is null \u67e5\u51fa\u6765\u7684\u662f\\N\u7684\u6570\u636e\uff1b\u800c\u6761\u4ef6 =\u2019\u2019\uff0c\u67e5\u8be2\u51fa\u6765\u7684\u662f&#8221;&#8221;\u7684\u6570\u636e<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u4e00\u3001\u662f\u4ec0\u4e48 \u5b98\u7f51\u7ed9\u51fa\u7684\u89e3\u91ca The Apache Hive data warehouse software f [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":624,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7],"tags":[],"_links":{"self":[{"href":"http:\/\/www.specialwu.com\/index.php?rest_route=\/wp\/v2\/posts\/848"}],"collection":[{"href":"http:\/\/www.specialwu.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.specialwu.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.specialwu.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.specialwu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=848"}],"version-history":[{"count":34,"href":"http:\/\/www.specialwu.com\/index.php?rest_route=\/wp\/v2\/posts\/848\/revisions"}],"predecessor-version":[{"id":1925,"href":"http:\/\/www.specialwu.com\/index.php?rest_route=\/wp\/v2\/posts\/848\/revisions\/1925"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.specialwu.com\/index.php?rest_route=\/wp\/v2\/media\/624"}],"wp:attachment":[{"href":"http:\/\/www.specialwu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=848"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.specialwu.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=848"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.specialwu.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=848"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}