{"id":2212,"date":"2023-07-27T23:08:35","date_gmt":"2023-07-27T15:08:35","guid":{"rendered":"http:\/\/viplao.com\/?p=2212"},"modified":"2023-07-27T23:08:39","modified_gmt":"2023-07-27T15:08:39","slug":"hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80","status":"publish","type":"post","link":"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/","title":{"rendered":"Hive Sql\uff1a\u5de5\u4f5c\u4e2d\u5e38\u7528HSQL\u8bed\u53e5\u5927\u5168 \u4e00"},"content":{"rendered":"\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_71 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">\u6587\u7ae0\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: #999;color:#999\" 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: #999;color:#999\" 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 eztoc-toggle-hide-by-default' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#Hive_Sql_%E5%A4%A7%E5%85%A8\" title=\"Hive Sql \u5927\u5168\">Hive Sql \u5927\u5168<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#hive%E7%9A%84DDL%E8%AF%AD%E6%B3%95\" title=\"hive\u7684DDL\u8bed\u6cd5\">hive\u7684DDL\u8bed\u6cd5<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E5%AF%B9%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E6%93%8D%E4%BD%9C\" title=\"\u5bf9\u6570\u636e\u5e93\u7684\u64cd\u4f5c\">\u5bf9\u6570\u636e\u5e93\u7684\u64cd\u4f5c<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E5%AF%B9%E6%95%B0%E6%8D%AE%E8%A1%A8%E7%9A%84%E6%93%8D%E4%BD%9C\" title=\"\u5bf9\u6570\u636e\u8868\u7684\u64cd\u4f5c\">\u5bf9\u6570\u636e\u8868\u7684\u64cd\u4f5c<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E5%AF%B9%E7%AE%A1%E7%90%86%E8%A1%A8%E5%86%85%E9%83%A8%E8%A1%A8%E7%9A%84%E6%93%8D%E4%BD%9C\" title=\"\u5bf9\u7ba1\u7406\u8868(\u5185\u90e8\u8868)\u7684\u64cd\u4f5c\">\u5bf9\u7ba1\u7406\u8868(\u5185\u90e8\u8868)\u7684\u64cd\u4f5c<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E5%AF%B9%E5%A4%96%E9%83%A8%E8%A1%A8%E6%93%8D%E4%BD%9C\" title=\"\u5bf9\u5916\u90e8\u8868\u64cd\u4f5c\">\u5bf9\u5916\u90e8\u8868\u64cd\u4f5c<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E5%AF%B9%E5%88%86%E5%8C%BA%E8%A1%A8%E7%9A%84%E6%93%8D%E4%BD%9C\" title=\"\u5bf9\u5206\u533a\u8868\u7684\u64cd\u4f5c\">\u5bf9\u5206\u533a\u8868\u7684\u64cd\u4f5c<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E5%AF%B9%E5%88%86%E6%A1%B6%E8%A1%A8%E6%93%8D%E4%BD%9C\" title=\"\u5bf9\u5206\u6876\u8868\u64cd\u4f5c\">\u5bf9\u5206\u6876\u8868\u64cd\u4f5c<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E4%BF%AE%E6%94%B9%E8%A1%A8%E5%92%8C%E5%88%A0%E9%99%A4%E8%A1%A8\" title=\"\u4fee\u6539\u8868\u548c\u5220\u9664\u8868\">\u4fee\u6539\u8868\u548c\u5220\u9664\u8868<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E5%90%91hive%E8%A1%A8%E4%B8%AD%E5%8A%A0%E8%BD%BD%E6%95%B0%E6%8D%AE\" title=\"\u5411hive\u8868\u4e2d\u52a0\u8f7d\u6570\u636e\">\u5411hive\u8868\u4e2d\u52a0\u8f7d\u6570\u636e<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#hive%E8%A1%A8%E4%B8%AD%E6%95%B0%E6%8D%AE%E5%AF%BC%E5%87%BA\" title=\"hive\u8868\u4e2d\u6570\u636e\u5bfc\u51fa\">hive\u8868\u4e2d\u6570\u636e\u5bfc\u51fa<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#hive%E7%9A%84DQL%E6%9F%A5%E8%AF%A2%E8%AF%AD%E6%B3%95\" title=\"hive\u7684DQL\u67e5\u8be2\u8bed\u6cd5\">hive\u7684DQL\u67e5\u8be2\u8bed\u6cd5<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E5%8D%95%E8%A1%A8%E6%9F%A5%E8%AF%A2\" title=\"\u5355\u8868\u67e5\u8be2\">\u5355\u8868\u67e5\u8be2<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#Hive%E5%87%BD%E6%95%B0\" title=\"Hive\u51fd\u6570\">Hive\u51fd\u6570<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E8%81%9A%E5%90%88%E5%87%BD%E6%95%B0\" title=\"\u805a\u5408\u51fd\u6570\">\u805a\u5408\u51fd\u6570<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-16\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E5%85%B3%E7%B3%BB%E8%BF%90%E7%AE%97\" title=\"\u5173\u7cfb\u8fd0\u7b97\">\u5173\u7cfb\u8fd0\u7b97<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-17\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E6%95%B0%E5%AD%A6%E8%BF%90%E7%AE%97\" title=\"\u6570\u5b66\u8fd0\u7b97\">\u6570\u5b66\u8fd0\u7b97<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-18\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E9%80%BB%E8%BE%91%E8%BF%90%E7%AE%97\" title=\"\u903b\u8f91\u8fd0\u7b97\">\u903b\u8f91\u8fd0\u7b97<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-19\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E6%95%B0%E5%80%BC%E8%BF%90%E7%AE%97\" title=\"\u6570\u503c\u8fd0\u7b97\">\u6570\u503c\u8fd0\u7b97<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-20\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E6%9D%A1%E4%BB%B6%E5%87%BD%E6%95%B0\" title=\"\u6761\u4ef6\u51fd\u6570\">\u6761\u4ef6\u51fd\u6570<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-21\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0\" title=\"\u65e5\u671f\u51fd\u6570\">\u65e5\u671f\u51fd\u6570<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-22\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E5%AD%97%E7%AC%A6%E4%B8%B2%E5%87%BD%E6%95%B0\" title=\"\u5b57\u7b26\u4e32\u51fd\u6570\">\u5b57\u7b26\u4e32\u51fd\u6570<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-23\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E5%A4%8D%E5%90%88%E7%B1%BB%E5%9E%8B%E6%9E%84%E5%BB%BA%E6%93%8D%E4%BD%9C\" title=\"\u590d\u5408\u7c7b\u578b\u6784\u5efa\u64cd\u4f5c\">\u590d\u5408\u7c7b\u578b\u6784\u5efa\u64cd\u4f5c<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-24\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E5%A4%8D%E6%9D%82%E7%B1%BB%E5%9E%8B%E8%AE%BF%E9%97%AE%E6%93%8D%E4%BD%9C\" title=\"\u590d\u6742\u7c7b\u578b\u8bbf\u95ee\u64cd\u4f5c\">\u590d\u6742\u7c7b\u578b\u8bbf\u95ee\u64cd\u4f5c<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-25\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E5%A4%8D%E6%9D%82%E7%B1%BB%E5%9E%8B%E9%95%BF%E5%BA%A6%E7%BB%9F%E8%AE%A1%E5%87%BD%E6%95%B0\" title=\"\u590d\u6742\u7c7b\u578b\u957f\u5ea6\u7edf\u8ba1\u51fd\u6570\">\u590d\u6742\u7c7b\u578b\u957f\u5ea6\u7edf\u8ba1\u51fd\u6570<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-26\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#hive%E5%BD%93%E4%B8%AD%E7%9A%84lateral_view_%E4%B8%8E_explode%E4%BB%A5%E5%8F%8Areflect%E5%92%8C%E7%AA%97%E5%8F%A3%E5%87%BD%E6%95%B0\" title=\"hive\u5f53\u4e2d\u7684lateral view \u4e0e explode\u4ee5\u53careflect\u548c\u7a97\u53e3\u51fd\u6570\">hive\u5f53\u4e2d\u7684lateral view \u4e0e explode\u4ee5\u53careflect\u548c\u7a97\u53e3\u51fd\u6570<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-27\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E4%BD%BF%E7%94%A8explode%E5%87%BD%E6%95%B0%E5%B0%86hive%E8%A1%A8%E4%B8%AD%E7%9A%84Map%E5%92%8CArray%E5%AD%97%E6%AE%B5%E6%95%B0%E6%8D%AE%E8%BF%9B%E8%A1%8C%E6%8B%86%E5%88%86\" title=\"\u4f7f\u7528explode\u51fd\u6570\u5c06hive\u8868\u4e2d\u7684Map\u548cArray\u5b57\u6bb5\u6570\u636e\u8fdb\u884c\u62c6\u5206\">\u4f7f\u7528explode\u51fd\u6570\u5c06hive\u8868\u4e2d\u7684Map\u548cArray\u5b57\u6bb5\u6570\u636e\u8fdb\u884c\u62c6\u5206<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-28\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E4%BD%BF%E7%94%A8explode%E6%8B%86%E5%88%86json%E5%AD%97%E7%AC%A6%E4%B8%B2\" title=\"\u4f7f\u7528explode\u62c6\u5206json\u5b57\u7b26\u4e32\">\u4f7f\u7528explode\u62c6\u5206json\u5b57\u7b26\u4e32<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-29\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E9%85%8D%E5%90%88LATERAL_VIEW%E4%BD%BF%E7%94%A8\" title=\"\u914d\u5408LATERAL VIEW\u4f7f\u7528\">\u914d\u5408LATERAL VIEW\u4f7f\u7528<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-30\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E8%A1%8C%E8%BD%AC%E5%88%97\" title=\"\u884c\u8f6c\u5217\">\u884c\u8f6c\u5217<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-31\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E5%88%97%E8%BD%AC%E8%A1%8C\" title=\"\u5217\u8f6c\u884c\">\u5217\u8f6c\u884c<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-32\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#reflect%E5%87%BD%E6%95%B0\" title=\"reflect\u51fd\u6570\">reflect\u51fd\u6570<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-33\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E7%AA%97%E5%8F%A3%E5%87%BD%E6%95%B0%E4%B8%8E%E5%88%86%E6%9E%90%E5%87%BD%E6%95%B0\" title=\"\u7a97\u53e3\u51fd\u6570\u4e0e\u5206\u6790\u51fd\u6570\">\u7a97\u53e3\u51fd\u6570\u4e0e\u5206\u6790\u51fd\u6570<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-34\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#sum%E3%80%81avg%E3%80%81min%E3%80%81max\" title=\"sum\u3001avg\u3001min\u3001max\">sum\u3001avg\u3001min\u3001max<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-35\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#row_number%E3%80%81rank%E3%80%81dense_rank%E3%80%81ntile\" title=\"row_number\u3001rank\u3001dense_rank\u3001ntile\">row_number\u3001rank\u3001dense_rank\u3001ntile<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-36\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#%E5%85%B6%E4%BB%96%E4%B8%80%E4%BA%9B%E7%AA%97%E5%8F%A3%E5%87%BD%E6%95%B0\" title=\"\u5176\u4ed6\u4e00\u4e9b\u7a97\u53e3\u51fd\u6570\">\u5176\u4ed6\u4e00\u4e9b\u7a97\u53e3\u51fd\u6570<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-37\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#lagleadfirst_valuelast_value\" title=\"lag,lead,first_value,last_value\">lag,lead,first_value,last_value<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-38\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#cume_distpercent_rank\" title=\"cume_dist,percent_rank\">cume_dist,percent_rank<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-39\" href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/#grouping_setsgrouping_idcuberollup\" title=\"grouping sets,grouping__id,cube,rollup\">grouping sets,grouping__id,cube,rollup<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Hive_Sql_%E5%A4%A7%E5%85%A8\"><\/span><strong>Hive Sql \u5927\u5168<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u672c\u6587\u57fa\u672c\u6db5\u76d6\u4e86Hive\u65e5\u5e38\u4f7f\u7528\u7684\u6240\u6709SQL\uff0c\u56e0\u4e3aSQL\u592a\u591a\uff0c\u6240\u4ee5\u5c06SQL\u8fdb\u884c\u4e86\u5982\u4e0b\u5206\u7c7b\uff1a \u4e00\u3001DDL\u8bed\u53e5\uff08\u6570\u636e\u5b9a\u4e49\u8bed\u53e5\uff09\uff1a<br>\u5bf9\u6570\u636e\u5e93\u7684\u64cd\u4f5c\uff1a\u5305\u542b\u521b\u5efa\u3001\u4fee\u6539\u6570\u636e\u5e93<br>\u5bf9\u6570\u636e\u8868\u7684\u64cd\u4f5c\uff1a\u5206\u4e3a\u5185\u90e8\u8868\u53ca\u5916\u90e8\u8868\uff0c\u5206\u533a\u8868\u548c\u5206\u6876\u8868<br>\u4e8c\u3001DQL\u8bed\u53e5\uff08\u6570\u636e\u67e5\u8be2\u8bed\u53e5\uff09\uff1a<br>\u5355\u8868\u67e5\u8be2\u3001\u5173\u8054\u67e5\u8be2<br>hive\u51fd\u6570\uff1a\u5305\u542b\u805a\u5408\u51fd\u6570\uff0c\u6761\u4ef6\u51fd\u6570\uff0c\u65e5\u671f\u51fd\u6570\uff0c\u5b57\u7b26\u4e32\u51fd\u6570\u7b49<br>\u884c\u8f6c\u5217\u53ca\u5217\u8f6c\u884c\uff1alateral view \u4e0e explode \u4ee5\u53ca reflect<br>\u7a97\u53e3\u51fd\u6570\u4e0e\u5206\u6790\u51fd\u6570<br>\u5176\u4ed6\u4e00\u4e9b\u7a97\u53e3\u51fd\u6570<\/p>\n<\/blockquote>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"hive%E7%9A%84DDL%E8%AF%AD%E6%B3%95\"><\/span><strong>hive\u7684DDL\u8bed\u6cd5<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%AF%B9%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E6%93%8D%E4%BD%9C\"><\/span><strong>\u5bf9\u6570\u636e\u5e93\u7684\u64cd\u4f5c<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<ul>\n<li>\u521b\u5efa\u6570\u636e\u5e93:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>create database if not exists myhive;\n\u8bf4\u660e\uff1ahive\u7684\u8868\u5b58\u653e\u4f4d\u7f6e\u6a21\u5f0f\u662f\u7531hive-site.xml\u5f53\u4e2d\u7684\u4e00\u4e2a\u5c5e\u6027\u6307\u5b9a\u7684 :hive.metastore.warehouse.dir\n\n\u521b\u5efa\u6570\u636e\u5e93\u5e76\u6307\u5b9ahdfs\u5b58\u50a8\u4f4d\u7f6e :\ncreate database myhive2 location '\/myhive2';\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u4fee\u6539\u6570\u636e\u5e93:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>alter  database  myhive2  set  dbproperties('createtime'='20210329');\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u8bf4\u660e\uff1a\u53ef\u4ee5\u4f7f\u7528alter database \u547d\u4ee4\u6765\u4fee\u6539\u6570\u636e\u5e93\u7684\u4e00\u4e9b\u5c5e\u6027\u3002\u4f46\u662f\u6570\u636e\u5e93\u7684\u5143\u6570\u636e\u4fe1\u606f\u662f\u4e0d\u53ef\u66f4\u6539\u7684\uff0c\u5305\u62ec\u6570\u636e\u5e93\u7684\u540d\u79f0\u4ee5\u53ca\u6570\u636e\u5e93\u6240\u5728\u7684\u4f4d\u7f6e<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>\u67e5\u770b\u6570\u636e\u5e93\u8be6\u7ec6\u4fe1\u606f<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u67e5\u770b\u6570\u636e\u5e93\u57fa\u672c\u4fe1\u606f\nhive (myhive)&gt; desc  database  myhive2;\n\n\u67e5\u770b\u6570\u636e\u5e93\u66f4\u591a\u8be6\u7ec6\u4fe1\u606f\nhive (myhive)&gt; desc database extended  myhive2;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u5220\u9664\u6570\u636e\u5e93<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u5220\u9664\u4e00\u4e2a\u7a7a\u6570\u636e\u5e93\uff0c\u5982\u679c\u6570\u636e\u5e93\u4e0b\u9762\u6709\u6570\u636e\u8868\uff0c\u90a3\u4e48\u5c31\u4f1a\u62a5\u9519\ndrop  database  myhive2;\n\n\u5f3a\u5236\u5220\u9664\u6570\u636e\u5e93\uff0c\u5305\u542b\u6570\u636e\u5e93\u4e0b\u9762\u7684\u8868\u4e00\u8d77\u5220\u9664\ndrop  database  myhive  cascade; \n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%AF%B9%E6%95%B0%E6%8D%AE%E8%A1%A8%E7%9A%84%E6%93%8D%E4%BD%9C\"><\/span><strong>\u5bf9\u6570\u636e\u8868\u7684\u64cd\u4f5c<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%AF%B9%E7%AE%A1%E7%90%86%E8%A1%A8%E5%86%85%E9%83%A8%E8%A1%A8%E7%9A%84%E6%93%8D%E4%BD%9C\"><\/span><strong>\u5bf9\u7ba1\u7406\u8868(\u5185\u90e8\u8868)\u7684\u64cd\u4f5c:<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u6587\u7ae0\u9996\u53d1\u4e8e\u516c\u4f17\u53f7\u3010\u4e94\u5206\u949f\u5b66\u5927\u6570\u636e\u3011\uff0c\u5927\u6570\u636e\u9886\u57df\u539f\u521b\u6280\u672f\u53f7\uff0c\u6bcf\u5468\u66f4\u65b0\u5927\u6570\u636e\u6280\u672f\u6587\u53ca\u9762\u8bd5\u771f\u9898\u89e3\u6790\uff0c\u5173\u6ce8\u540e\u53ef\u9886\u53d6\u7cbe\u5fc3\u5236\u4f5c\u5927\u6570\u636e\u9762\u8bd5\u5b9d\u5178\uff01<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>\u5efa\u5185\u90e8\u8868:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>hive (myhive)&gt; use myhive; -- \u4f7f\u7528myhive\u6570\u636e\u5e93\nhive (myhive)&gt; create table stu(id int,name string);\nhive (myhive)&gt; insert into stu values (1,\"zhangsan\");\nhive (myhive)&gt; insert into stu values (1,\"zhangsan\"),(2,\"lisi\");  -- \u4e00\u6b21\u63d2\u5165\u591a\u6761\u6570\u636e\nhive (myhive)&gt; select * from stu;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>hive\u5efa\u8868\u65f6\u5019\u7684\u5b57\u6bb5\u7c7b\u578b:<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><th>\u5206\u7c7b<\/th><th>\u7c7b\u578b<\/th><th>\u63cf\u8ff0<\/th><th>\u5b57\u9762\u91cf\u793a\u4f8b<\/th><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p><strong>\u5bf9decimal\u7c7b\u578b\u7b80\u5355\u89e3\u91ca\u4e0b<\/strong>\uff1a<br>\u7528\u6cd5\uff1adecimal(11,2) \u4ee3\u8868\u6700\u591a\u670911\u4f4d\u6570\u5b57\uff0c\u5176\u4e2d\u540e2\u4f4d\u662f\u5c0f\u6570\uff0c\u6574\u6570\u90e8\u5206\u662f9\u4f4d\uff1b\u5982\u679c\u6574\u6570\u90e8\u5206\u8d85\u8fc79\u4f4d\uff0c\u5219\u8fd9\u4e2a\u5b57\u6bb5\u5c31\u4f1a\u53d8\u6210null\uff1b\u5982\u679c\u5c0f\u6570\u90e8\u5206\u4e0d\u8db32\u4f4d\uff0c\u5219\u540e\u9762\u75280\u8865\u9f50\u4e24\u4f4d\uff0c\u5982\u679c\u5c0f\u6570\u90e8\u5206\u8d85\u8fc7\u4e24\u4f4d\uff0c\u5219\u8d85\u51fa\u90e8\u5206\u56db\u820d\u4e94\u5165<br>\u4e5f\u53ef\u76f4\u63a5\u5199 decimal\uff0c\u540e\u9762\u4e0d\u6307\u5b9a\u4f4d\u6570\uff0c\u9ed8\u8ba4\u662f decimal(10,0) \u6574\u657010\u4f4d\uff0c\u6ca1\u6709\u5c0f\u6570<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>\u521b\u5efa\u8868\u5e76\u6307\u5b9a\u5b57\u6bb5\u4e4b\u95f4\u7684\u5206\u9694\u7b26<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>create  table if not exists stu2(id int ,name string) row format delimited fields terminated by '\\t' stored as textfile location '\/user\/stu2';\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>row format delimited fields terminated by &#8216;\\t&#8217; \u6307\u5b9a\u5b57\u6bb5\u5206\u9694\u7b26\uff0c\u9ed8\u8ba4\u5206\u9694\u7b26\u4e3a &#8216;\\001&#8217;<br>stored as \u6307\u5b9a\u5b58\u50a8\u683c\u5f0f<br>location \u6307\u5b9a\u5b58\u50a8\u4f4d\u7f6e<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>\u6839\u636e\u67e5\u8be2\u7ed3\u679c\u521b\u5efa\u8868<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>create table stu3 as select * from stu2;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u6839\u636e\u5df2\u7ecf\u5b58\u5728\u7684\u8868\u7ed3\u6784\u521b\u5efa\u8868<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>create table stu4 like stu2;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u67e5\u8be2\u8868\u7684\u7ed3\u6784<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u53ea\u67e5\u8be2\u8868\u5185\u5b57\u6bb5\u53ca\u5c5e\u6027\ndesc stu2;\n\n\u8be6\u7ec6\u67e5\u8be2\ndesc formatted  stu2;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u67e5\u8be2\u521b\u5efa\u8868\u7684\u8bed\u53e5<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>show create table stu2;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%AF%B9%E5%A4%96%E9%83%A8%E8%A1%A8%E6%93%8D%E4%BD%9C\"><\/span><strong>\u5bf9\u5916\u90e8\u8868\u64cd\u4f5c<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u5916\u90e8\u8868\u56e0\u4e3a\u662f\u6307\u5b9a\u5176\u4ed6\u7684hdfs\u8def\u5f84\u7684\u6570\u636e\u52a0\u8f7d\u5230\u8868\u5f53\u4e2d\u6765\uff0c\u6240\u4ee5hive\u8868\u4f1a\u8ba4\u4e3a\u81ea\u5df1\u4e0d\u5b8c\u5168\u72ec\u5360\u8fd9\u4efd\u6570\u636e\uff0c\u6240\u4ee5\u5220\u9664hive\u8868\u7684\u65f6\u5019\uff0c\u6570\u636e\u4ecd\u7136\u5b58\u653e\u5728hdfs\u5f53\u4e2d\uff0c\u4e0d\u4f1a\u5220\u6389\uff0c\u53ea\u4f1a\u5220\u9664\u8868\u7684\u5143\u6570\u636e<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>\u6784\u5efa\u5916\u90e8\u8868<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>create external table student (s_id string,s_name string) row format delimited fields terminated by '\\t';\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u4ece\u672c\u5730\u6587\u4ef6\u7cfb\u7edf\u5411\u8868\u4e2d\u52a0\u8f7d\u6570\u636e<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8ffd\u52a0\u64cd\u4f5c\nload data local inpath '\/export\/servers\/hivedatas\/student.csv' into table student;\n\n\u8986\u76d6\u64cd\u4f5c\nload data local inpath '\/export\/servers\/hivedatas\/student.csv' overwrite  into table student;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u4ecehdfs\u6587\u4ef6\u7cfb\u7edf\u5411\u8868\u4e2d\u52a0\u8f7d\u6570\u636e<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>load data inpath '\/hivedatas\/techer.csv' into table techer;\n\n\u52a0\u8f7d\u6570\u636e\u5230\u6307\u5b9a\u5206\u533a\nload data inpath '\/hivedatas\/techer.csv' into table techer partition(cur_date=20201210);\n<\/code><\/pre>\n\n\n\n<ul>\n<li><strong>\u6ce8\u610f<\/strong>\uff1a<br>1.\u4f7f\u7528 load data local \u8868\u793a\u4ece\u672c\u5730\u6587\u4ef6\u7cfb\u7edf\u52a0\u8f7d\uff0c\u6587\u4ef6\u4f1a\u62f7\u8d1d\u5230hdfs\u4e0a<br>2.\u4f7f\u7528 load data \u8868\u793a\u4ecehdfs\u6587\u4ef6\u7cfb\u7edf\u52a0\u8f7d\uff0c\u6587\u4ef6\u4f1a\u76f4\u63a5\u79fb\u52a8\u5230hive\u76f8\u5173\u76ee\u5f55\u4e0b\uff0c\u6ce8\u610f\u4e0d\u662f\u62f7\u8d1d\u8fc7\u53bb\uff0c\u56e0\u4e3ahive\u8ba4\u4e3ahdfs\u6587\u4ef6\u5df2\u7ecf\u67093\u526f\u672c\u4e86\uff0c\u6ca1\u5fc5\u8981\u518d\u6b21\u62f7\u8d1d\u4e86<br>3.\u5982\u679c\u8868\u662f\u5206\u533a\u8868\uff0cload \u65f6\u4e0d\u6307\u5b9a\u5206\u533a\u4f1a\u62a5\u9519<br>4.\u5982\u679c\u52a0\u8f7d\u76f8\u540c\u6587\u4ef6\u540d\u7684\u6587\u4ef6\uff0c\u4f1a\u88ab\u81ea\u52a8\u91cd\u547d\u540d<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%AF%B9%E5%88%86%E5%8C%BA%E8%A1%A8%E7%9A%84%E6%93%8D%E4%BD%9C\"><\/span><strong>\u5bf9\u5206\u533a\u8868\u7684\u64cd\u4f5c<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<ul>\n<li>\u521b\u5efa\u5206\u533a\u8868\u7684\u8bed\u6cd5<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>create table score(s_id string, s_score int) partitioned by (month string);\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u521b\u5efa\u4e00\u4e2a\u8868\u5e26\u591a\u4e2a\u5206\u533a<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>create table score2 (s_id string, s_score int) partitioned by (year string,month string,day string);\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p><strong>\u6ce8\u610f\uff1a<\/strong><br><strong>hive\u8868\u521b\u5efa\u7684\u65f6\u5019\u53ef\u4ee5\u7528 location \u6307\u5b9a\u4e00\u4e2a\u6587\u4ef6\u6216\u8005\u6587\u4ef6\u5939\uff0c\u5f53\u6307\u5b9a\u6587\u4ef6\u5939\u65f6\uff0chive\u4f1a\u52a0\u8f7d\u6587\u4ef6\u5939\u4e0b\u7684\u6240\u6709\u6587\u4ef6\uff0c\u5f53\u8868\u4e2d\u65e0\u5206\u533a\u65f6\uff0c\u8fd9\u4e2a\u6587\u4ef6\u5939\u4e0b\u4e0d\u80fd\u518d\u6709\u6587\u4ef6\u5939\uff0c\u5426\u5219\u62a5\u9519<\/strong><br><strong>\u5f53\u8868\u662f\u5206\u533a\u8868\u65f6\uff0c\u6bd4\u5982 partitioned by (day string)\uff0c \u5219\u8fd9\u4e2a\u6587\u4ef6\u5939\u4e0b\u7684\u6bcf\u4e00\u4e2a\u6587\u4ef6\u5939\u5c31\u662f\u4e00\u4e2a\u5206\u533a\uff0c\u4e14\u6587\u4ef6\u5939\u540d\u4e3a day=20201123 \u8fd9\u79cd\u683c\u5f0f\uff0c\u7136\u540e\u4f7f\u7528\uff1amsck repair table score; \u4fee\u590d\u8868\u7ed3\u6784\uff0c\u6210\u529f\u4e4b\u540e\u5373\u53ef\u770b\u5230\u6570\u636e\u5df2\u7ecf\u5168\u90e8\u52a0\u8f7d\u5230\u8868\u5f53\u4e2d\u53bb\u4e86<\/strong><\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>\u52a0\u8f7d\u6570\u636e\u5230\u4e00\u4e2a\u5206\u533a\u7684\u8868\u4e2d<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>load data local inpath '\/export\/servers\/hivedatas\/score.csv' into table score partition (month='201806');\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u52a0\u8f7d\u6570\u636e\u5230\u4e00\u4e2a\u591a\u5206\u533a\u7684\u8868\u4e2d\u53bb<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>load data local inpath '\/export\/servers\/hivedatas\/score.csv' into table score2 partition(year='2018',month='06',day='01');\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u67e5\u770b\u5206\u533a<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>show  partitions  score;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u6dfb\u52a0\u4e00\u4e2a\u5206\u533a<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>alter table score add partition(month='201805');\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u540c\u65f6\u6dfb\u52a0\u591a\u4e2a\u5206\u533a<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code> alter table score add partition(month='201804') partition(month = '201803');\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u6ce8\u610f\uff1a\u6dfb\u52a0\u5206\u533a\u4e4b\u540e\u5c31\u53ef\u4ee5\u5728hdfs\u6587\u4ef6\u7cfb\u7edf\u5f53\u4e2d\u770b\u5230\u8868\u4e0b\u9762\u591a\u4e86\u4e00\u4e2a\u6587\u4ef6\u5939<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>\u5220\u9664\u5206\u533a<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code> alter table score drop partition(month = '201806');\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%AF%B9%E5%88%86%E6%A1%B6%E8%A1%A8%E6%93%8D%E4%BD%9C\"><\/span><strong>\u5bf9\u5206\u6876\u8868\u64cd\u4f5c<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u5c06\u6570\u636e\u6309\u7167\u6307\u5b9a\u7684\u5b57\u6bb5\u8fdb\u884c\u5206\u6210\u591a\u4e2a\u6876\u4e2d\u53bb\uff0c\u5c31\u662f\u6309\u7167\u5206\u6876\u5b57\u6bb5\u8fdb\u884c\u54c8\u5e0c\u5212\u5206\u5230\u591a\u4e2a\u6587\u4ef6\u5f53\u4e2d\u53bb<br>\u5206\u533a\u5c31\u662f\u5206\u6587\u4ef6\u5939\uff0c\u5206\u6876\u5c31\u662f\u5206\u6587\u4ef6<\/p>\n\n\n\n<p>\u5206\u6876\u4f18\u70b9\uff1a<br>1. \u63d0\u9ad8join\u67e5\u8be2\u6548\u7387<br>2. \u63d0\u9ad8\u62bd\u6837\u6548\u7387<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>\u5f00\u542fhive\u7684\u6345\u8868\u529f\u80fd<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>set hive.enforce.bucketing=true;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u8bbe\u7f6ereduce\u7684\u4e2a\u6570<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>set mapreduce.job.reduces=3;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u521b\u5efa\u6876\u8868<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>create table course (c_id string,c_name string) clustered by(c_id) into 3 buckets;\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u6876\u8868\u7684\u6570\u636e\u52a0\u8f7d\uff1a\u7531\u4e8e\u6876\u8868\u7684\u6570\u636e\u52a0\u8f7d\u901a\u8fc7hdfs dfs -put\u6587\u4ef6\u6216\u8005\u901a\u8fc7load data\u5747\u4e0d\u53ef\u4ee5\uff0c\u53ea\u80fd\u901a\u8fc7insert overwrite \u8fdb\u884c\u52a0\u8f7d<br>\u6240\u4ee5\u628a\u6587\u4ef6\u52a0\u8f7d\u5230\u6876\u8868\u4e2d\uff0c\u9700\u8981\u5148\u521b\u5efa\u666e\u901a\u8868\uff0c\u5e76\u901a\u8fc7insert overwrite\u7684\u65b9\u5f0f\u5c06\u666e\u901a\u8868\u7684\u6570\u636e\u901a\u8fc7\u67e5\u8be2\u7684\u65b9\u5f0f\u52a0\u8f7d\u5230\u6876\u8868\u5f53\u4e2d\u53bb<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>\u901a\u8fc7insert overwrite\u7ed9\u6876\u8868\u4e2d\u52a0\u8f7d\u6570\u636e<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>insert overwrite table course select * from course_common cluster by(c_id);  -- \u6700\u540e\u6307\u5b9a\u6876\u5b57\u6bb5\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E4%BF%AE%E6%94%B9%E8%A1%A8%E5%92%8C%E5%88%A0%E9%99%A4%E8%A1%A8\"><\/span><strong>\u4fee\u6539\u8868\u548c\u5220\u9664\u8868<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<ul>\n<li>\u4fee\u6539\u8868\u540d\u79f0<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>alter  table  old_table_name  rename  to  new_table_name;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u589e\u52a0\/\u4fee\u6539\u5217\u4fe1\u606f<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u67e5\u8be2\u8868\u7ed3\u6784\ndesc score5;\n\n\u6dfb\u52a0\u5217\nalter table score5 add columns (mycol string, mysco string);\n\n\u66f4\u65b0\u5217\nalter table score5 change column mysco mysconew int;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u5220\u9664\u8868\u64cd\u4f5c<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>drop table score5;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u6e05\u7a7a\u8868\u64cd\u4f5c<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>truncate table score6;\n\n\u8bf4\u660e\uff1a\u53ea\u80fd\u6e05\u7a7a\u7ba1\u7406\u8868\uff0c\u4e5f\u5c31\u662f\u5185\u90e8\u8868\uff1b\u6e05\u7a7a\u5916\u90e8\u8868\uff0c\u4f1a\u4ea7\u751f\u9519\u8bef\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p><strong>\u6ce8\u610f\uff1atruncate \u548c drop\uff1a<\/strong><br><strong>\u5982\u679c hdfs \u5f00\u542f\u4e86\u56de\u6536\u7ad9\uff0cdrop \u5220\u9664\u7684\u8868\u6570\u636e\u662f\u53ef\u4ee5\u4ece\u56de\u6536\u7ad9\u6062\u590d\u7684\uff0c\u8868\u7ed3\u6784\u6062\u590d\u4e0d\u4e86\uff0c\u9700\u8981\u81ea\u5df1\u91cd\u65b0\u521b\u5efa\uff1btruncate \u6e05\u7a7a\u7684\u8868\u662f\u4e0d\u8fdb\u56de\u6536\u7ad9\u7684\uff0c\u6240\u4ee5\u65e0\u6cd5\u6062\u590dtruncate\u6e05\u7a7a\u7684\u8868<\/strong><br><strong>\u6240\u4ee5 truncate \u4e00\u5b9a\u614e\u7528\uff0c\u4e00\u65e6\u6e05\u7a7a\u5c06\u65e0\u529b\u56de\u5929<\/strong><\/p>\n<\/blockquote>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%90%91hive%E8%A1%A8%E4%B8%AD%E5%8A%A0%E8%BD%BD%E6%95%B0%E6%8D%AE\"><\/span><strong>\u5411hive\u8868\u4e2d\u52a0\u8f7d\u6570\u636e<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<ul>\n<li>\u76f4\u63a5\u5411\u5206\u533a\u8868\u4e2d\u63d2\u5165\u6570\u636e<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>insert into table score partition(month ='201807') values ('001','002','100');\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u901a\u8fc7load\u65b9\u5f0f\u52a0\u8f7d\u6570\u636e<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code> load data local inpath '\/export\/servers\/hivedatas\/score.csv' overwrite into table score partition(month='201806');\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u901a\u8fc7\u67e5\u8be2\u65b9\u5f0f\u52a0\u8f7d\u6570\u636e<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>insert overwrite table score2 partition(month = '201806') select s_id,c_id,s_score from score1;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u67e5\u8be2\u8bed\u53e5\u4e2d\u521b\u5efa\u8868\u5e76\u52a0\u8f7d\u6570\u636e<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>create table score2 as select * from score1;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u5728\u521b\u5efa\u8868\u662f\u901a\u8fc7location\u6307\u5b9a\u52a0\u8f7d\u6570\u636e\u7684\u8def\u5f84<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>create external table score6 (s_id string,c_id string,s_score int) row format delimited fields terminated by ',' location '\/myscore';\n<\/code><\/pre>\n\n\n\n<ul>\n<li>export\u5bfc\u51fa\u4e0eimport \u5bfc\u5165 hive\u8868\u6570\u636e\uff08\u5185\u90e8\u8868\u64cd\u4f5c\uff09<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>create table techer2 like techer; --\u4f9d\u636e\u5df2\u6709\u8868\u7ed3\u6784\u521b\u5efa\u8868\n\nexport table techer to  '\/export\/techer';\n\nimport table techer2 from '\/export\/techer';\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"hive%E8%A1%A8%E4%B8%AD%E6%95%B0%E6%8D%AE%E5%AF%BC%E5%87%BA\"><\/span><strong>hive\u8868\u4e2d\u6570\u636e\u5bfc\u51fa<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<ul>\n<li>insert\u5bfc\u51fa<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u5c06\u67e5\u8be2\u7684\u7ed3\u679c\u5bfc\u51fa\u5230\u672c\u5730\ninsert overwrite local directory '\/export\/servers\/exporthive' select * from score;\n\n\u5c06\u67e5\u8be2\u7684\u7ed3\u679c\u683c\u5f0f\u5316\u5bfc\u51fa\u5230\u672c\u5730\ninsert overwrite local directory '\/export\/servers\/exporthive' row format delimited fields terminated by '\\t' collection items terminated by '#' select * from student;\n\n\u5c06\u67e5\u8be2\u7684\u7ed3\u679c\u5bfc\u51fa\u5230HDFS\u4e0a(\u6ca1\u6709local)\ninsert overwrite directory '\/export\/servers\/exporthive' row format delimited fields terminated by '\\t' collection items terminated by '#' select * from score;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>Hadoop\u547d\u4ee4\u5bfc\u51fa\u5230\u672c\u5730<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>dfs -get \/export\/servers\/exporthive\/000000_0 \/export\/servers\/exporthive\/local.txt;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>hive shell \u547d\u4ee4\u5bfc\u51fa<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u57fa\u672c\u8bed\u6cd5\uff1a\uff08hive -f\/-e \u6267\u884c\u8bed\u53e5\u6216\u8005\u811a\u672c &gt; file\uff09\n\nhive -e \"select * from myhive.score;\" &gt; \/export\/servers\/exporthive\/score.txt\n\nhive -f export.sh &gt; \/export\/servers\/exporthive\/score.txt\n<\/code><\/pre>\n\n\n\n<ul>\n<li>export\u5bfc\u51fa\u5230HDFS\u4e0a<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>export table score to '\/export\/exporthive\/score';\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"hive%E7%9A%84DQL%E6%9F%A5%E8%AF%A2%E8%AF%AD%E6%B3%95\"><\/span><strong>hive\u7684DQL\u67e5\u8be2\u8bed\u6cd5<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%8D%95%E8%A1%A8%E6%9F%A5%E8%AF%A2\"><\/span><strong>\u5355\u8868\u67e5\u8be2<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT &#91;ALL | DISTINCT] select_expr, select_expr, ... \nFROM table_reference\n&#91;WHERE where_condition] \n&#91;GROUP BY col_list &#91;HAVING condition]] \n&#91;CLUSTER BY col_list \n  | &#91;DISTRIBUTE BY col_list] &#91;SORT BY| ORDER BY col_list] \n] \n&#91;LIMIT number]\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u6ce8\u610f\uff1a<br>1\u3001order by \u4f1a\u5bf9\u8f93\u5165\u505a\u5168\u5c40\u6392\u5e8f\uff0c\u56e0\u6b64\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>2\u3001sort by\u4e0d\u662f\u5168\u5c40\u6392\u5e8f\uff0c\u5176\u5728\u6570\u636e\u8fdb\u5165reducer\u524d\u5b8c\u6210\u6392\u5e8f\u3002\u56e0\u6b64\uff0c\u5982\u679c\u7528sort by\u8fdb\u884c\u6392\u5e8f\uff0c\u5e76\u4e14\u8bbe\u7f6emapred.reduce.tasks&gt;1\uff0c\u5219sort by\u53ea\u4fdd\u8bc1\u6bcf\u4e2areducer\u7684\u8f93\u51fa\u6709\u5e8f\uff0c\u4e0d\u4fdd\u8bc1\u5168\u5c40\u6709\u5e8f\u3002<br>3\u3001distribute by(\u5b57\u6bb5)\u6839\u636e\u6307\u5b9a\u7684\u5b57\u6bb5\u5c06\u6570\u636e\u5206\u5230\u4e0d\u540c\u7684reducer\uff0c\u4e14\u5206\u53d1\u7b97\u6cd5\u662fhash\u6563\u5217\u3002<br>4\u3001Cluster by(\u5b57\u6bb5) \u9664\u4e86\u5177\u6709Distribute by\u7684\u529f\u80fd\u5916\uff0c\u8fd8\u4f1a\u5bf9\u8be5\u5b57\u6bb5\u8fdb\u884c\u6392\u5e8f\u3002<br>\u56e0\u6b64\uff0c\u5982\u679c\u5206\u6876\u548csort\u5b57\u6bb5\u662f\u540c\u4e00\u4e2a\u65f6\uff0c\u6b64\u65f6\uff0ccluster by = distribute by + sort by<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>WHERE\u8bed\u53e5<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>select * from score where s_score &lt; 60;\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u6ce8\u610f\uff1a<br>\u5c0f\u4e8e\u67d0\u4e2a\u503c\u662f\u4e0d\u5305\u542bnull\u7684\uff0c\u5982\u4e0a\u67e5\u8be2\u7ed3\u679c\u662f\u628a s_score \u4e3a null \u7684\u884c\u5254\u9664\u7684<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>GROUP BY \u5206\u7ec4<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>select s_id ,avg(s_score) from score group by s_id;\n\n\u5206\u7ec4\u540e\u5bf9\u6570\u636e\u8fdb\u884c\u7b5b\u9009\uff0c\u4f7f\u7528having\n select s_id ,avg(s_score) avgscore from score group by s_id having avgscore &gt; 85;\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u6ce8\u610f\uff1a<br>\u5982\u679c\u4f7f\u7528 group by \u5206\u7ec4\uff0c\u5219 select \u540e\u9762\u53ea\u80fd\u5199\u5206\u7ec4\u7684\u5b57\u6bb5\u6216\u8005\u805a\u5408\u51fd\u6570<br>where\u548chaving\u533a\u522b\uff1a<br>1 having\u662f\u5728 group by \u5206\u5b8c\u7ec4\u4e4b\u540e\u518d\u5bf9\u6570\u636e\u8fdb\u884c\u7b5b\u9009\uff0c\u6240\u4ee5having \u8981\u7b5b\u9009\u7684\u5b57\u6bb5\u53ea\u80fd\u662f\u5206\u7ec4\u5b57\u6bb5\u6216\u8005\u805a\u5408\u51fd\u6570<br>2 where \u662f\u4ece\u6570\u636e\u8868\u4e2d\u7684\u5b57\u6bb5\u76f4\u63a5\u8fdb\u884c\u7684\u7b5b\u9009\u7684\uff0c\u6240\u4ee5\u4e0d\u80fd\u8ddf\u5728gruop by\u540e\u9762\uff0c\u4e5f\u4e0d\u80fd\u4f7f\u7528\u805a\u5408\u51fd\u6570<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>join \u8fde\u63a5<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>INNER JOIN \u5185\u8fde\u63a5\uff1a\u53ea\u6709\u8fdb\u884c\u8fde\u63a5\u7684\u4e24\u4e2a\u8868\u4e2d\u90fd\u5b58\u5728\u4e0e\u8fde\u63a5\u6761\u4ef6\u76f8\u5339\u914d\u7684\u6570\u636e\u624d\u4f1a\u88ab\u4fdd\u7559\u4e0b\u6765\nselect * from techer t &#91;inner] join course c on t.t_id = c.t_id; -- inner \u53ef\u7701\u7565\n\nLEFT OUTER JOIN \u5de6\u5916\u8fde\u63a5\uff1a\u5de6\u8fb9\u6240\u6709\u6570\u636e\u4f1a\u88ab\u8fd4\u56de\uff0c\u53f3\u8fb9\u7b26\u5408\u6761\u4ef6\u7684\u88ab\u8fd4\u56de\nselect * from techer t left join course c on t.t_id = c.t_id; -- outer\u53ef\u7701\u7565\n\nRIGHT OUTER JOIN \u53f3\u5916\u8fde\u63a5\uff1a\u53f3\u8fb9\u6240\u6709\u6570\u636e\u4f1a\u88ab\u8fd4\u56de\uff0c\u5de6\u8fb9\u7b26\u5408\u6761\u4ef6\u7684\u88ab\u8fd4\u56de\u3001\nselect * from techer t right join course c on t.t_id = c.t_id;\n\nFULL OUTER JOIN \u6ee1\u5916(\u5168\u5916)\u8fde\u63a5: \u5c06\u4f1a\u8fd4\u56de\u6240\u6709\u8868\u4e2d\u7b26\u5408\u6761\u4ef6\u7684\u6240\u6709\u8bb0\u5f55\u3002\u5982\u679c\u4efb\u4e00\u8868\u7684\u6307\u5b9a\u5b57\u6bb5\u6ca1\u6709\u7b26\u5408\u6761\u4ef6\u7684\u503c\u7684\u8bdd\uff0c\u90a3\u4e48\u5c31\u4f7f\u7528NULL\u503c\u66ff\u4ee3\u3002\nSELECT * FROM techer t FULL JOIN course c ON t.t_id = c.t_id ;\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u6ce8\uff1a1. hive2\u7248\u672c\u5df2\u7ecf\u652f\u6301\u4e0d\u7b49\u503c\u8fde\u63a5\uff0c\u5c31\u662f join on\u6761\u4ef6\u540e\u9762\u53ef\u4ee5\u4f7f\u7528\u5927\u4e8e\u5c0f\u4e8e\u7b26\u53f7\u4e86;\u5e76\u4e14\u4e5f\u652f\u6301 join on \u6761\u4ef6\u540e\u8ddfor (\u65e9\u524d\u7248\u672c on \u540e\u53ea\u652f\u6301 = \u548c and\uff0c\u4e0d\u652f\u6301 &gt; &lt; \u548c or)<br>2.\u5982hive\u6267\u884c\u5f15\u64ce\u4f7f\u7528MapReduce\uff0c\u4e00\u4e2ajoin\u5c31\u4f1a\u542f\u52a8\u4e00\u4e2ajob\uff0c\u4e00\u6761sql\u8bed\u53e5\u4e2d\u5982\u6709\u591a\u4e2ajoin\uff0c\u5219\u4f1a\u542f\u52a8\u591a\u4e2ajob<\/p>\n\n\n\n<p>\u6ce8\u610f\uff1a\u8868\u4e4b\u95f4\u7528\u9017\u53f7(,)\u8fde\u63a5\u548c inner join \u662f\u4e00\u6837\u7684<br>select * from table_a,table_b where table_a.id=table_b.id;<br>\u5b83\u4eec\u7684\u6267\u884c\u6548\u7387\u6ca1\u6709\u533a\u522b\uff0c\u53ea\u662f\u4e66\u5199\u65b9\u5f0f\u4e0d\u540c\uff0c\u7528\u9017\u53f7\u662fsql 89\u6807\u51c6\uff0cjoin \u662fsql 92\u6807\u51c6\u3002\u7528\u9017\u53f7\u8fde\u63a5\u540e\u9762\u8fc7\u6ee4\u6761\u4ef6\u7528 where \uff0c\u7528 join \u8fde\u63a5\u540e\u9762\u8fc7\u6ee4\u6761\u4ef6\u662f on\u3002<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>order by \u6392\u5e8f<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u5168\u5c40\u6392\u5e8f\uff0c\u53ea\u4f1a\u6709\u4e00\u4e2areduce\nASC\uff08ascend\uff09: \u5347\u5e8f\uff08\u9ed8\u8ba4\uff09 DESC\uff08descend\uff09: \u964d\u5e8f\nSELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC;\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u6ce8\u610f\uff1aorder by \u662f\u5168\u5c40\u6392\u5e8f\uff0c\u6240\u4ee5\u6700\u540e\u53ea\u6709\u4e00\u4e2areduce\uff0c\u4e5f\u5c31\u662f\u5728\u4e00\u4e2a\u8282\u70b9\u6267\u884c\uff0c\u5982\u679c\u6570\u636e\u91cf\u592a\u5927\uff0c\u5c31\u4f1a\u8017\u8d39\u8f83\u957f\u65f6\u95f4<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>sort by \u5c40\u90e8\u6392\u5e8f<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u6bcf\u4e2aMapReduce\u5185\u90e8\u8fdb\u884c\u6392\u5e8f\uff0c\u5bf9\u5168\u5c40\u7ed3\u679c\u96c6\u6765\u8bf4\u4e0d\u662f\u6392\u5e8f\u3002\n\n\u8bbe\u7f6ereduce\u4e2a\u6570\nset mapreduce.job.reduces=3;\n\n\u67e5\u770b\u8bbe\u7f6ereduce\u4e2a\u6570\nset mapreduce.job.reduces;\n\n\u67e5\u8be2\u6210\u7ee9\u6309\u7167\u6210\u7ee9\u964d\u5e8f\u6392\u5217\nselect * from score sort by s_score;\n \n\u5c06\u67e5\u8be2\u7ed3\u679c\u5bfc\u5165\u5230\u6587\u4ef6\u4e2d\uff08\u6309\u7167\u6210\u7ee9\u964d\u5e8f\u6392\u5217\uff09\ninsert overwrite local directory '\/export\/servers\/hivedatas\/sort' select * from score sort by s_score;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>distribute by \u5206\u533a\u6392\u5e8f<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>distribute by\uff1a\u7c7b\u4f3cMR\u4e2dpartition\uff0c\u8fdb\u884c\u5206\u533a\uff0c\u7ed3\u5408sort by\u4f7f\u7528\n\n\u8bbe\u7f6ereduce\u7684\u4e2a\u6570\uff0c\u5c06\u6211\u4eec\u5bf9\u5e94\u7684s_id\u5212\u5206\u5230\u5bf9\u5e94\u7684reduce\u5f53\u4e2d\u53bb\nset mapreduce.job.reduces=7;\n\n\u901a\u8fc7distribute by  \u8fdb\u884c\u6570\u636e\u7684\u5206\u533a\nselect * from score distribute by s_id sort by s_score;\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u6ce8\u610f\uff1aHive\u8981\u6c42 distribute by \u8bed\u53e5\u8981\u5199\u5728 sort by \u8bed\u53e5\u4e4b\u524d<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>cluster by<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u5f53distribute by\u548csort by\u5b57\u6bb5\u76f8\u540c\u65f6\uff0c\u53ef\u4ee5\u4f7f\u7528cluster by\u65b9\u5f0f.\ncluster by\u9664\u4e86\u5177\u6709distribute by\u7684\u529f\u80fd\u5916\u8fd8\u517c\u5177sort by\u7684\u529f\u80fd\u3002\u4f46\u662f\u6392\u5e8f\u53ea\u80fd\u662f\u6b63\u5e8f\u6392\u5e8f\uff0c\u4e0d\u80fd\u6307\u5b9a\u6392\u5e8f\u89c4\u5219\u4e3aASC\u6216\u8005DESC\u3002\n\n\u4ee5\u4e0b\u4e24\u79cd\u5199\u6cd5\u7b49\u4ef7\nselect * from score cluster by s_id;\nselect * from score distribute by s_id sort by s_id;\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Hive%E5%87%BD%E6%95%B0\"><\/span><strong>Hive\u51fd\u6570<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E8%81%9A%E5%90%88%E5%87%BD%E6%95%B0\"><\/span><strong>\u805a\u5408\u51fd\u6570<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>hive\u652f\u6301 count(),max(),min(),sum(),avg() \u7b49\u5e38\u7528\u7684\u805a\u5408\u51fd\u6570\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u6ce8\u610f\uff1a<br>\u805a\u5408\u64cd\u4f5c\u65f6\u8981\u6ce8\u610fnull\u503c<br>count(*) \u5305\u542bnull\u503c\uff0c\u7edf\u8ba1\u6240\u6709\u884c\u6570<br>count(id) \u4e0d\u5305\u542bnull\u503c<br>min \u6c42\u6700\u5c0f\u503c\u662f\u4e0d\u5305\u542bnull\uff0c\u9664\u975e\u6240\u6709\u503c\u90fd\u662fnull<br>avg \u6c42\u5e73\u5747\u503c\u4e5f\u662f\u4e0d\u5305\u542bnull<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>\u975e\u7a7a\u96c6\u5408\u603b\u4f53\u53d8\u91cf\u51fd\u6570: var_pop<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: var_pop(col)\n\u8fd4\u56de\u503c: double\n\u8bf4\u660e: \u7edf\u8ba1\u7ed3\u679c\u96c6\u4e2dcol\u975e\u7a7a\u96c6\u5408\u7684\u603b\u4f53\u53d8\u91cf\uff08\u5ffd\u7565null\uff09\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u975e\u7a7a\u96c6\u5408\u6837\u672c\u53d8\u91cf\u51fd\u6570: var_samp<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: var_samp (col)\n\u8fd4\u56de\u503c: double\n\u8bf4\u660e: \u7edf\u8ba1\u7ed3\u679c\u96c6\u4e2dcol\u975e\u7a7a\u96c6\u5408\u7684\u6837\u672c\u53d8\u91cf\uff08\u5ffd\u7565null\uff09\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u603b\u4f53\u6807\u51c6\u504f\u79bb\u51fd\u6570: stddev_pop<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: stddev_pop(col)\n\u8fd4\u56de\u503c: double\n\u8bf4\u660e: \u8be5\u51fd\u6570\u8ba1\u7b97\u603b\u4f53\u6807\u51c6\u504f\u79bb\uff0c\u5e76\u8fd4\u56de\u603b\u4f53\u53d8\u91cf\u7684\u5e73\u65b9\u6839\uff0c\u5176\u8fd4\u56de\u503c\u4e0eVAR_POP\u51fd\u6570\u7684\u5e73\u65b9\u6839\u76f8\u540c\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u4e2d\u4f4d\u6570\u51fd\u6570: percentile<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: percentile(BIGINT col, p)\n\u8fd4\u56de\u503c: double\n\u8bf4\u660e: \u6c42\u51c6\u786e\u7684\u7b2cpth\u4e2a\u767e\u5206\u4f4d\u6570\uff0cp\u5fc5\u987b\u4ecb\u4e8e0\u548c1\u4e4b\u95f4\uff0c\u4f46\u662fcol\u5b57\u6bb5\u76ee\u524d\u53ea\u652f\u6301\u6574\u6570\uff0c\u4e0d\u652f\u6301\u6d6e\u70b9\u6570\u7c7b\u578b\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%85%B3%E7%B3%BB%E8%BF%90%E7%AE%97\"><\/span><strong>\u5173\u7cfb\u8fd0\u7b97<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>\u652f\u6301\uff1a\u7b49\u503c(=)\u3001\u4e0d\u7b49\u503c(!= \u6216 &lt;&gt;)\u3001\u5c0f\u4e8e(&lt;)\u3001\u5c0f\u4e8e\u7b49\u4e8e(&lt;=)\u3001\u5927\u4e8e(&gt;)\u3001\u5927\u4e8e\u7b49\u4e8e(&gt;=)\n\n\u7a7a\u503c\u5224\u65ad(is null)\u3001\u975e\u7a7a\u5224\u65ad(is not null)\n<\/code><\/pre>\n\n\n\n<ul>\n<li>LIKE\u6bd4\u8f83: LIKE<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: A LIKE B\n\u64cd\u4f5c\u7c7b\u578b: strings\n\u63cf\u8ff0: \u5982\u679c\u5b57\u7b26\u4e32A\u6216\u8005\u5b57\u7b26\u4e32B\u4e3aNULL\uff0c\u5219\u8fd4\u56deNULL\uff1b\u5982\u679c\u5b57\u7b26\u4e32A\u7b26\u5408\u8868\u8fbe\u5f0fB \u7684\u6b63\u5219\u8bed\u6cd5\uff0c\u5219\u4e3aTRUE\uff1b\u5426\u5219\u4e3aFALSE\u3002B\u4e2d\u5b57\u7b26\u201d_\u201d\u8868\u793a\u4efb\u610f\u5355\u4e2a\u5b57\u7b26\uff0c\u800c\u5b57\u7b26\u201d%\u201d\u8868\u793a\u4efb\u610f\u6570\u91cf\u7684\u5b57\u7b26\u3002\n<\/code><\/pre>\n\n\n\n<ul>\n<li>JAVA\u7684LIKE\u64cd\u4f5c: RLIKE<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: A RLIKE B\n\u64cd\u4f5c\u7c7b\u578b: strings\n\u63cf\u8ff0: \u5982\u679c\u5b57\u7b26\u4e32A\u6216\u8005\u5b57\u7b26\u4e32B\u4e3aNULL\uff0c\u5219\u8fd4\u56deNULL\uff1b\u5982\u679c\u5b57\u7b26\u4e32A\u7b26\u5408JAVA\u6b63\u5219\u8868\u8fbe\u5f0fB\u7684\u6b63\u5219\u8bed\u6cd5\uff0c\u5219\u4e3aTRUE\uff1b\u5426\u5219\u4e3aFALSE\u3002\n<\/code><\/pre>\n\n\n\n<ul>\n<li>REGEXP\u64cd\u4f5c: REGEXP<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: A REGEXP B\n\u64cd\u4f5c\u7c7b\u578b: strings\n\u63cf\u8ff0: \u529f\u80fd\u4e0eRLIKE\u76f8\u540c\n\u793a\u4f8b\uff1aselect 1 from tableName where 'footbar' REGEXP '^f.*r$';\n\u7ed3\u679c\uff1a1\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%95%B0%E5%AD%A6%E8%BF%90%E7%AE%97\"><\/span><strong>\u6570\u5b66\u8fd0\u7b97<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>\u652f\u6301\u6240\u6709\u6570\u503c\u7c7b\u578b\uff1a\u52a0(+)\u3001\u51cf(-)\u3001\u4e58(*)\u3001\u9664(\/)\u3001\u53d6\u4f59(%)\u3001\u4f4d\u4e0e(&amp;)\u3001\u4f4d\u6216(|)\u3001\u4f4d\u5f02\u6216(^)\u3001\u4f4d\u53d6\u53cd(~)\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E9%80%BB%E8%BE%91%E8%BF%90%E7%AE%97\"><\/span><strong>\u903b\u8f91\u8fd0\u7b97<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>\u652f\u6301\uff1a\u903b\u8f91\u4e0e(and)\u3001\u903b\u8f91\u6216(or)\u3001\u903b\u8f91\u975e(not)\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%95%B0%E5%80%BC%E8%BF%90%E7%AE%97\"><\/span><strong>\u6570\u503c\u8fd0\u7b97<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<ul>\n<li>\u53d6\u6574\u51fd\u6570: round<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: round(double a)\n\u8fd4\u56de\u503c: BIGINT\n\u8bf4\u660e: \u8fd4\u56dedouble\u7c7b\u578b\u7684\u6574\u6570\u503c\u90e8\u5206 \uff08\u9075\u5faa\u56db\u820d\u4e94\u5165\uff09\n\u793a\u4f8b\uff1aselect round(3.1415926) from tableName;\n\u7ed3\u679c\uff1a3\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u6307\u5b9a\u7cbe\u5ea6\u53d6\u6574\u51fd\u6570: round<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: round(double a, int d)\n\u8fd4\u56de\u503c: DOUBLE\n\u8bf4\u660e: \u8fd4\u56de\u6307\u5b9a\u7cbe\u5ea6d\u7684double\u7c7b\u578b\nhive&gt; select round(3.1415926,4) from tableName;\n3.1416\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u5411\u4e0b\u53d6\u6574\u51fd\u6570: floor<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: floor(double a)\n\u8fd4\u56de\u503c: BIGINT\n\u8bf4\u660e: \u8fd4\u56de\u7b49\u4e8e\u6216\u8005\u5c0f\u4e8e\u8be5double\u53d8\u91cf\u7684\u6700\u5927\u7684\u6574\u6570\nhive&gt; select floor(3.641) from tableName;\n3\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u5411\u4e0a\u53d6\u6574\u51fd\u6570: ceil<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: ceil(double a)\n\u8fd4\u56de\u503c: BIGINT\n\u8bf4\u660e: \u8fd4\u56de\u7b49\u4e8e\u6216\u8005\u5927\u4e8e\u8be5double\u53d8\u91cf\u7684\u6700\u5c0f\u7684\u6574\u6570\nhive&gt; select ceil(3.1415926) from tableName;\n4\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u53d6\u968f\u673a\u6570\u51fd\u6570: rand<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: rand(),rand(int seed)\n\u8fd4\u56de\u503c: double\n\u8bf4\u660e: \u8fd4\u56de\u4e00\u4e2a0\u52301\u8303\u56f4\u5185\u7684\u968f\u673a\u6570\u3002\u5982\u679c\u6307\u5b9a\u79cd\u5b50seed\uff0c\u5219\u4f1a\u7b49\u5230\u4e00\u4e2a\u7a33\u5b9a\u7684\u968f\u673a\u6570\u5e8f\u5217\nhive&gt; select rand() from tableName; -- \u6bcf\u6b21\u6267\u884c\u6b64\u8bed\u53e5\u5f97\u5230\u7684\u7ed3\u679c\u90fd\u4e0d\u540c\n0.5577432776034763\n\nhive&gt; select rand(100) ;  -- \u53ea\u8981\u6307\u5b9a\u79cd\u5b50\uff0c\u6bcf\u6b21\u6267\u884c\u6b64\u8bed\u53e5\u5f97\u5230\u7684\u7ed3\u679c\u4e00\u6837\u7684\n0.7220096548596434\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u81ea\u7136\u6307\u6570\u51fd\u6570: exp<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: exp(double a)\n\u8fd4\u56de\u503c: double\n\u8bf4\u660e: \u8fd4\u56de\u81ea\u7136\u5bf9\u6570e\u7684a\u6b21\u65b9\nhive&gt; select exp(2) ;\n7.38905609893065\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u4ee510\u4e3a\u5e95\u5bf9\u6570\u51fd\u6570: log10<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: log10(double a)\n\u8fd4\u56de\u503c: double\n\u8bf4\u660e: \u8fd4\u56de\u4ee510\u4e3a\u5e95\u7684a\u7684\u5bf9\u6570\nhive&gt; select log10(100) ;\n2.0\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u6b64\u5916\u8fd8\u6709\uff1a\u4ee52\u4e3a\u5e95\u5bf9\u6570\u51fd\u6570: log2()\u3001\u5bf9\u6570\u51fd\u6570: log()<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li>\u5e42\u8fd0\u7b97\u51fd\u6570: pow<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: pow(double a, double p)\n\u8fd4\u56de\u503c: double\n\u8bf4\u660e: \u8fd4\u56dea\u7684p\u6b21\u5e42\nhive&gt; select pow(2,4) ;\n16.0\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u5f00\u5e73\u65b9\u51fd\u6570: sqrt<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: sqrt(double a)\n\u8fd4\u56de\u503c: double\n\u8bf4\u660e: \u8fd4\u56dea\u7684\u5e73\u65b9\u6839\nhive&gt; select sqrt(16) ;\n4.0\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u4e8c\u8fdb\u5236\u51fd\u6570: bin<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: bin(BIGINT a)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e: \u8fd4\u56dea\u7684\u4e8c\u8fdb\u5236\u4ee3\u7801\u8868\u793a\nhive&gt; select bin(7) ;\n111\n<\/code><\/pre>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u5341\u516d\u8fdb\u5236\u51fd\u6570: hex()\u3001\u5c06\u5341\u516d\u8fdb\u5236\u8f6c\u5316\u4e3a\u5b57\u7b26\u4e32\u51fd\u6570: unhex()<br>\u8fdb\u5236\u8f6c\u6362\u51fd\u6570: conv(bigint num, int from_base, int to_base) \u8bf4\u660e: \u5c06\u6570\u503cnum\u4ecefrom_base\u8fdb\u5236\u8f6c\u5316\u5230to_base\u8fdb\u5236<\/p>\n\n\n\n<p>\u6b64\u5916\u8fd8\u6709\u5f88\u591a\u6570\u5b66\u51fd\u6570\uff1a \u7edd\u5bf9\u503c\u51fd\u6570: abs()\u3001\u6b63\u53d6\u4f59\u51fd\u6570: pmod()\u3001\u6b63\u5f26\u51fd\u6570: sin()\u3001\u53cd\u6b63\u5f26\u51fd\u6570: asin()\u3001\u4f59\u5f26\u51fd\u6570: cos()\u3001\u53cd\u4f59\u5f26\u51fd\u6570: acos()\u3001positive\u51fd\u6570: positive()\u3001negative\u51fd\u6570: negative()<\/p>\n<\/blockquote>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%9D%A1%E4%BB%B6%E5%87%BD%E6%95%B0\"><\/span><strong>\u6761\u4ef6\u51fd\u6570<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<ul>\n<li>If\u51fd\u6570: if<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: if(boolean testCondition, T valueTrue, T valueFalseOrNull)\n\u8fd4\u56de\u503c: T\n\u8bf4\u660e: \u5f53\u6761\u4ef6testCondition\u4e3aTRUE\u65f6\uff0c\u8fd4\u56devalueTrue\uff1b\u5426\u5219\u8fd4\u56devalueFalseOrNull\nhive&gt; select if(1=2,100,200) ;\n200\nhive&gt; select if(1=1,100,200) ;\n100\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u975e\u7a7a\u67e5\u627e\u51fd\u6570: coalesce<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: coalesce(T v1, T v2, \u2026)\n\u8fd4\u56de\u503c: T\n\u8bf4\u660e: \u8fd4\u56de\u53c2\u6570\u4e2d\u7684\u7b2c\u4e00\u4e2a\u975e\u7a7a\u503c\uff1b\u5982\u679c\u6240\u6709\u503c\u90fd\u4e3aNULL\uff0c\u90a3\u4e48\u8fd4\u56deNULL\nhive&gt; select coalesce(null,'100','50') ;\n100\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u6761\u4ef6\u5224\u65ad\u51fd\u6570\uff1acase when (\u4e24\u79cd\u5199\u6cd5\uff0c\u5176\u4e00)<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: case when a then b &#91;when c then d]* &#91;else e] end\n\u8fd4\u56de\u503c: T\n\u8bf4\u660e\uff1a\u5982\u679ca\u4e3aTRUE,\u5219\u8fd4\u56deb\uff1b\u5982\u679cc\u4e3aTRUE\uff0c\u5219\u8fd4\u56ded\uff1b\u5426\u5219\u8fd4\u56dee\nhive&gt; select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;\nmary\n<\/code><\/pre>\n\n\n\n<ul>\n<li>\u6761\u4ef6\u5224\u65ad\u51fd\u6570\uff1acase when (\u4e24\u79cd\u5199\u6cd5\uff0c\u5176\u4e8c)<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: case a when b then c &#91;when d then e]* &#91;else f] end\n\u8fd4\u56de\u503c: T\n\u8bf4\u660e\uff1a\u5982\u679ca\u7b49\u4e8eb\uff0c\u90a3\u4e48\u8fd4\u56dec\uff1b\u5982\u679ca\u7b49\u4e8ed\uff0c\u90a3\u4e48\u8fd4\u56dee\uff1b\u5426\u5219\u8fd4\u56def\nhive&gt; Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;\nmary\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0\"><\/span><strong>\u65e5\u671f\u51fd\u6570<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u6ce8\uff1a\u4ee5\u4e0bSQL\u8bed\u53e5\u4e2d\u7684 from tableName \u53ef\u53bb\u6389\uff0c\u4e0d\u5f71\u54cd\u67e5\u8be2\u7ed3\u679c<\/p>\n<\/blockquote>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u83b7\u53d6\u5f53\u524dUNIX\u65f6\u95f4\u6233\u51fd\u6570: unix_timestamp<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: unix_timestamp()\n\u8fd4\u56de\u503c: bigint\n\u8bf4\u660e: \u83b7\u5f97\u5f53\u524d\u65f6\u533a\u7684UNIX\u65f6\u95f4\u6233\nhive&gt; select unix_timestamp() from tableName;\n1616906976\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>UNIX\u65f6\u95f4\u6233\u8f6c\u65e5\u671f\u51fd\u6570: from_unixtime<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: from_unixtime(bigint unixtime&#91;, string format])\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e: \u8f6c\u5316UNIX\u65f6\u95f4\u6233\uff08\u4ece1970-01-01 00:00:00 UTC\u5230\u6307\u5b9a\u65f6\u95f4\u7684\u79d2\u6570\uff09\u5230\u5f53\u524d\u65f6\u533a\u7684\u65f6\u95f4\u683c\u5f0f\nhive&gt; select from_unixtime(1616906976,'yyyyMMdd') from tableName;\n20210328\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u65e5\u671f\u8f6cUNIX\u65f6\u95f4\u6233\u51fd\u6570: unix_timestamp<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: unix_timestamp(string date)\n\u8fd4\u56de\u503c: bigint\n\u8bf4\u660e: \u8f6c\u6362\u683c\u5f0f\u4e3a\"yyyy-MM-dd HH:mm:ss\"\u7684\u65e5\u671f\u5230UNIX\u65f6\u95f4\u6233\u3002\u5982\u679c\u8f6c\u5316\u5931\u8d25\uff0c\u5219\u8fd4\u56de0\u3002\nhive&gt;  select unix_timestamp('2021-03-08 14:21:15') from tableName;\n1615184475\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u6307\u5b9a\u683c\u5f0f\u65e5\u671f\u8f6cUNIX\u65f6\u95f4\u6233\u51fd\u6570: unix_timestamp<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: unix_timestamp(string date, string pattern)\n\u8fd4\u56de\u503c: bigint\n\u8bf4\u660e: \u8f6c\u6362pattern\u683c\u5f0f\u7684\u65e5\u671f\u5230UNIX\u65f6\u95f4\u6233\u3002\u5982\u679c\u8f6c\u5316\u5931\u8d25\uff0c\u5219\u8fd4\u56de0\u3002\nhive&gt;  select unix_timestamp('2021-03-08 14:21:15','yyyyMMdd HH:mm:ss') from tableName;\n1615184475\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u65e5\u671f\u65f6\u95f4\u8f6c\u65e5\u671f\u51fd\u6570: to_date<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: to_date(string timestamp)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e: \u8fd4\u56de\u65e5\u671f\u65f6\u95f4\u5b57\u6bb5\u4e2d\u7684\u65e5\u671f\u90e8\u5206\u3002\nhive&gt; select to_date('2021-03-28 14:03:01') from tableName;\n2021-03-28\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u65e5\u671f\u8f6c\u5e74\u51fd\u6570: year<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: year(string date)\n\u8fd4\u56de\u503c: int\n\u8bf4\u660e: \u8fd4\u56de\u65e5\u671f\u4e2d\u7684\u5e74\u3002\nhive&gt; select year('2021-03-28 10:03:01') from tableName;\n2021\nhive&gt; select year('2021-03-28') from tableName;\n2021\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u65e5\u671f\u8f6c\u6708\u51fd\u6570: month<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: month (string date)\n\u8fd4\u56de\u503c: int\n\u8bf4\u660e: \u8fd4\u56de\u65e5\u671f\u4e2d\u7684\u6708\u4efd\u3002\nhive&gt; select month('2020-12-28 12:03:01') from tableName;\n12\nhive&gt; select month('2021-03-08') from tableName;\n8\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u65e5\u671f\u8f6c\u5929\u51fd\u6570: day<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: day (string date)\n\u8fd4\u56de\u503c: int\n\u8bf4\u660e: \u8fd4\u56de\u65e5\u671f\u4e2d\u7684\u5929\u3002\nhive&gt; select day('2020-12-08 10:03:01') from tableName;\n8\nhive&gt; select day('2020-12-24') from tableName;\n24\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u65e5\u671f\u8f6c\u5c0f\u65f6\u51fd\u6570: hour<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: hour (string date)\n\u8fd4\u56de\u503c: int\n\u8bf4\u660e: \u8fd4\u56de\u65e5\u671f\u4e2d\u7684\u5c0f\u65f6\u3002\nhive&gt; select hour('2020-12-08 10:03:01') from tableName;\n10\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u65e5\u671f\u8f6c\u5206\u949f\u51fd\u6570: minute<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: minute (string date)\n\u8fd4\u56de\u503c: int\n\u8bf4\u660e: \u8fd4\u56de\u65e5\u671f\u4e2d\u7684\u5206\u949f\u3002\nhive&gt; select minute('2020-12-08 10:03:01') from tableName;\n3\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u65e5\u671f\u8f6c\u79d2\u51fd\u6570: second<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: second (string date)\n\u8fd4\u56de\u503c: int\n\u8bf4\u660e: \u8fd4\u56de\u65e5\u671f\u4e2d\u7684\u79d2\u3002\nhive&gt; select second('2020-12-08 10:03:01') from tableName;\n1\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u65e5\u671f\u8f6c\u5468\u51fd\u6570: weekofyear<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: weekofyear (string date)\n\u8fd4\u56de\u503c: int\n\u8bf4\u660e: \u8fd4\u56de\u65e5\u671f\u5728\u5f53\u524d\u7684\u5468\u6570\u3002\nhive&gt; select weekofyear('2020-12-08 10:03:01') from tableName;\n49\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u65e5\u671f\u6bd4\u8f83\u51fd\u6570: datediff<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: datediff(string enddate, string startdate)\n\u8fd4\u56de\u503c: int\n\u8bf4\u660e: \u8fd4\u56de\u7ed3\u675f\u65e5\u671f\u51cf\u53bb\u5f00\u59cb\u65e5\u671f\u7684\u5929\u6570\u3002\nhive&gt; select datediff('2020-12-08','2012-05-09') from tableName;\n213\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u65e5\u671f\u589e\u52a0\u51fd\u6570: date_add<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: date_add(string startdate, int days)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e: \u8fd4\u56de\u5f00\u59cb\u65e5\u671fstartdate\u589e\u52a0days\u5929\u540e\u7684\u65e5\u671f\u3002\nhive&gt; select date_add('2020-12-08',10) from tableName;\n2020-12-18\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u65e5\u671f\u51cf\u5c11\u51fd\u6570: date_sub<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: date_sub (string startdate, int days)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e: \u8fd4\u56de\u5f00\u59cb\u65e5\u671fstartdate\u51cf\u5c11days\u5929\u540e\u7684\u65e5\u671f\u3002\nhive&gt; select date_sub('2020-12-08',10) from tableName;\n2020-11-28\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%AD%97%E7%AC%A6%E4%B8%B2%E5%87%BD%E6%95%B0\"><\/span><strong>\u5b57\u7b26\u4e32\u51fd\u6570<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u5b57\u7b26\u4e32\u957f\u5ea6\u51fd\u6570\uff1alength<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: length(string A)\n\u8fd4\u56de\u503c: int\n\u8bf4\u660e\uff1a\u8fd4\u56de\u5b57\u7b26\u4e32A\u7684\u957f\u5ea6\nhive&gt; select length('abcedfg') from tableName;\n7\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u5b57\u7b26\u4e32\u53cd\u8f6c\u51fd\u6570\uff1areverse<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: reverse(string A)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u8fd4\u56de\u5b57\u7b26\u4e32A\u7684\u53cd\u8f6c\u7ed3\u679c\nhive&gt; select reverse('abcedfg') from tableName;\ngfdecba\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u5b57\u7b26\u4e32\u8fde\u63a5\u51fd\u6570\uff1aconcat<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: concat(string A, string B\u2026)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u8fd4\u56de\u8f93\u5165\u5b57\u7b26\u4e32\u8fde\u63a5\u540e\u7684\u7ed3\u679c\uff0c\u652f\u6301\u4efb\u610f\u4e2a\u8f93\u5165\u5b57\u7b26\u4e32\nhive&gt; select concat('abc','def\u2019,'gh')from tableName;\nabcdefgh\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u5e26\u5206\u9694\u7b26\u5b57\u7b26\u4e32\u8fde\u63a5\u51fd\u6570\uff1aconcat_ws<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: concat_ws(string SEP, string A, string B\u2026)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u8fd4\u56de\u8f93\u5165\u5b57\u7b26\u4e32\u8fde\u63a5\u540e\u7684\u7ed3\u679c\uff0cSEP\u8868\u793a\u5404\u4e2a\u5b57\u7b26\u4e32\u95f4\u7684\u5206\u9694\u7b26\nhive&gt; select concat_ws(',','abc','def','gh')from tableName;\nabc,def,gh\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u5b57\u7b26\u4e32\u622a\u53d6\u51fd\u6570\uff1asubstr,substring<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: substr(string A, int start),substring(string A, int start)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u8fd4\u56de\u5b57\u7b26\u4e32A\u4ecestart\u4f4d\u7f6e\u5230\u7ed3\u5c3e\u7684\u5b57\u7b26\u4e32\nhive&gt; select substr('abcde',3) from tableName;\ncde\nhive&gt; select substring('abcde',3) from tableName;\ncde\nhive&gt; select substr('abcde',-1) from tableName; \uff08\u548cORACLE\u76f8\u540c\uff09\ne\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u5b57\u7b26\u4e32\u622a\u53d6\u51fd\u6570\uff1asubstr,substring<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: substr(string A, int start, int len),substring(string A, int start, int len)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u8fd4\u56de\u5b57\u7b26\u4e32A\u4ecestart\u4f4d\u7f6e\u5f00\u59cb\uff0c\u957f\u5ea6\u4e3alen\u7684\u5b57\u7b26\u4e32\nhive&gt; select substr('abcde',3,2) from tableName;\ncd\nhive&gt; select substring('abcde',3,2) from tableName;\ncd\nhive&gt;select substring('abcde',-2,2) from tableName;\nde\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u5b57\u7b26\u4e32\u8f6c\u5927\u5199\u51fd\u6570\uff1aupper,ucase<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: upper(string A) ucase(string A)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u8fd4\u56de\u5b57\u7b26\u4e32A\u7684\u5927\u5199\u683c\u5f0f\nhive&gt; select upper('abSEd') from tableName;\nABSED\nhive&gt; select ucase('abSEd') from tableName;\nABSED\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u5b57\u7b26\u4e32\u8f6c\u5c0f\u5199\u51fd\u6570\uff1alower,lcase<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: lower(string A) lcase(string A)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u8fd4\u56de\u5b57\u7b26\u4e32A\u7684\u5c0f\u5199\u683c\u5f0f\nhive&gt; select lower('abSEd') from tableName;\nabsed\nhive&gt; select lcase('abSEd') from tableName;\nabsed\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u53bb\u7a7a\u683c\u51fd\u6570\uff1atrim<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: trim(string A)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u53bb\u9664\u5b57\u7b26\u4e32\u4e24\u8fb9\u7684\u7a7a\u683c\nhive&gt; select trim(' abc ') from tableName;\nabc\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u5de6\u8fb9\u53bb\u7a7a\u683c\u51fd\u6570\uff1altrim<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: ltrim(string A)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u53bb\u9664\u5b57\u7b26\u4e32\u5de6\u8fb9\u7684\u7a7a\u683c\nhive&gt; select ltrim(' abc ') from tableName;\nabc\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u53f3\u8fb9\u53bb\u7a7a\u683c\u51fd\u6570\uff1artrim<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: rtrim(string A)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u53bb\u9664\u5b57\u7b26\u4e32\u53f3\u8fb9\u7684\u7a7a\u683c\nhive&gt; select rtrim(' abc ') from tableName;\nabc\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u6b63\u5219\u8868\u8fbe\u5f0f\u66ff\u6362\u51fd\u6570\uff1aregexp_replace<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: regexp_replace(string A, string B, string C)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u5c06\u5b57\u7b26\u4e32A\u4e2d\u7684\u7b26\u5408java\u6b63\u5219\u8868\u8fbe\u5f0fB\u7684\u90e8\u5206\u66ff\u6362\u4e3aC\u3002\u6ce8\u610f\uff0c\u5728\u6709\u4e9b\u60c5\u51b5\u4e0b\u8981\u4f7f\u7528\u8f6c\u4e49\u5b57\u7b26,\u7c7b\u4f3coracle\u4e2d\u7684regexp_replace\u51fd\u6570\u3002\nhive&gt; select regexp_replace('foobar', 'oo|ar', '') from tableName;\nfb\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u6b63\u5219\u8868\u8fbe\u5f0f\u89e3\u6790\u51fd\u6570\uff1aregexp_extract<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: regexp_extract(string subject, string pattern, int index)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u5c06\u5b57\u7b26\u4e32subject\u6309\u7167pattern\u6b63\u5219\u8868\u8fbe\u5f0f\u7684\u89c4\u5219\u62c6\u5206\uff0c\u8fd4\u56deindex\u6307\u5b9a\u7684\u5b57\u7b26\u3002\nhive&gt; select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from tableName;\nthe\nhive&gt; select regexp_extract('foothebar', 'foo(.*?)(bar)', 2) from tableName;\nbar\nhive&gt; select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) from tableName;\nfoothebar\nstrong&gt;\u6ce8\u610f\uff0c\u5728\u6709\u4e9b\u60c5\u51b5\u4e0b\u8981\u4f7f\u7528\u8f6c\u4e49\u5b57\u7b26\uff0c\u4e0b\u9762\u7684\u7b49\u53f7\u8981\u7528\u53cc\u7ad6\u7ebf\u8f6c\u4e49\uff0c\u8fd9\u662fjava\u6b63\u5219\u8868\u8fbe\u5f0f\u7684\u89c4\u5219\u3002\nselect data_field,\nregexp_extract(data_field,'.*?bgStart\\\\=(&#91;^&amp;]+)',1) as aaa,\nregexp_extract(data_field,'.*?contentLoaded_headStart\\\\=(&#91;^&amp;]+)',1) as bbb,\nregexp_extract(data_field,'.*?AppLoad2Req\\\\=(&#91;^&amp;]+)',1) as ccc \nfrom pt_nginx_loginlog_st \nwhere pt = '2021-03-28' limit 2;\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>URL\u89e3\u6790\u51fd\u6570\uff1aparse_url<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: parse_url(string urlString, string partToExtract &#91;, string keyToExtract])\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u8fd4\u56deURL\u4e2d\u6307\u5b9a\u7684\u90e8\u5206\u3002partToExtract\u7684\u6709\u6548\u503c\u4e3a\uff1aHOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.\nhive&gt; select parse_url\n('https:\/\/www.tableName.com\/path1\/p.php?k1=v1&amp;k2=v2#Ref1', 'HOST') \nfrom tableName;\nwww.tableName.com \nhive&gt; select parse_url\n('https:\/\/www.tableName.com\/path1\/p.php?k1=v1&amp;k2=v2#Ref1', 'QUERY', 'k1')\n from tableName;\nv1\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>json\u89e3\u6790\u51fd\u6570\uff1aget_json_object<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: get_json_object(string json_string, string path)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u89e3\u6790json\u7684\u5b57\u7b26\u4e32json_string,\u8fd4\u56depath\u6307\u5b9a\u7684\u5185\u5bb9\u3002\u5982\u679c\u8f93\u5165\u7684json\u5b57\u7b26\u4e32\u65e0\u6548\uff0c\u90a3\u4e48\u8fd4\u56deNULL\u3002\nhive&gt; select  get_json_object('{\"store\":{\"fruit\":\\&#91;{\"weight\":8,\"type\":\"apple\"},{\"weight\":9,\"type\":\"pear\"}], \"bicycle\":{\"price\":19.95,\"color\":\"red\"} },\"email\":\"amy@only_for_json_udf_test.net\",\"owner\":\"amy\"}','$.owner') from tableName;\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u7a7a\u683c\u5b57\u7b26\u4e32\u51fd\u6570\uff1aspace<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: space(int n)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u8fd4\u56de\u957f\u5ea6\u4e3an\u7684\u5b57\u7b26\u4e32\nhive&gt; select space(10) from tableName;\nhive&gt; select length(space(10)) from tableName;\n10\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u91cd\u590d\u5b57\u7b26\u4e32\u51fd\u6570\uff1arepeat<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: repeat(string str, int n)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u8fd4\u56de\u91cd\u590dn\u6b21\u540e\u7684str\u5b57\u7b26\u4e32\nhive&gt; select repeat('abc',5) from tableName;\nabcabcabcabcabc\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u9996\u5b57\u7b26ascii\u51fd\u6570\uff1aascii<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: ascii(string str)\n\u8fd4\u56de\u503c: int\n\u8bf4\u660e\uff1a\u8fd4\u56de\u5b57\u7b26\u4e32str\u7b2c\u4e00\u4e2a\u5b57\u7b26\u7684ascii\u7801\nhive&gt; select ascii('abcde') from tableName;\n97\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u5de6\u8865\u8db3\u51fd\u6570\uff1alpad<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: lpad(string str, int len, string pad)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u5c06str\u8fdb\u884c\u7528pad\u8fdb\u884c\u5de6\u8865\u8db3\u5230len\u4f4d\nhive&gt; select lpad('abc',10,'td') from tableName;\ntdtdtdtabc\n\u6ce8\u610f\uff1a\u4e0eGP\uff0cORACLE\u4e0d\u540c\uff0cpad \u4e0d\u80fd\u9ed8\u8ba4\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u53f3\u8865\u8db3\u51fd\u6570\uff1arpad<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: rpad(string str, int len, string pad)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u5c06str\u8fdb\u884c\u7528pad\u8fdb\u884c\u53f3\u8865\u8db3\u5230len\u4f4d\nhive&gt; select rpad('abc',10,'td') from tableName;\nabctdtdtdt\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u5206\u5272\u5b57\u7b26\u4e32\u51fd\u6570: split<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: split(string str, string pat)\n\u8fd4\u56de\u503c: array\n\u8bf4\u660e: \u6309\u7167pat\u5b57\u7b26\u4e32\u5206\u5272str\uff0c\u4f1a\u8fd4\u56de\u5206\u5272\u540e\u7684\u5b57\u7b26\u4e32\u6570\u7ec4\nhive&gt; select split('abtcdtef','t') from tableName;\n&#91;\"ab\",\"cd\",\"ef\"]\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u96c6\u5408\u67e5\u627e\u51fd\u6570: find_in_set<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: find_in_set(string str, string strList)\n\u8fd4\u56de\u503c: int\n\u8bf4\u660e: \u8fd4\u56destr\u5728strlist\u7b2c\u4e00\u6b21\u51fa\u73b0\u7684\u4f4d\u7f6e\uff0cstrlist\u662f\u7528\u9017\u53f7\u5206\u5272\u7684\u5b57\u7b26\u4e32\u3002\u5982\u679c\u6ca1\u6709\u627e\u8be5str\u5b57\u7b26\uff0c\u5219\u8fd4\u56de0\nhive&gt; select find_in_set('ab','ef,ab,de') from tableName;\n2\nhive&gt; select find_in_set('at','ef,ab,de') from tableName;\n0\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%A4%8D%E5%90%88%E7%B1%BB%E5%9E%8B%E6%9E%84%E5%BB%BA%E6%93%8D%E4%BD%9C\"><\/span><strong>\u590d\u5408\u7c7b\u578b\u6784\u5efa\u64cd\u4f5c<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<ul>\n<li>Map\u7c7b\u578b\u6784\u5efa: map<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: map (key1, value1, key2, value2, \u2026)\n\u8bf4\u660e\uff1a\u6839\u636e\u8f93\u5165\u7684key\u548cvalue\u5bf9\u6784\u5efamap\u7c7b\u578b\nhive&gt; Create table mapTable as select map('100','tom','200','mary') as t from tableName;\nhive&gt; describe mapTable;\nt       map&lt;string ,string&gt;\nhive&gt; select t from tableName;\n{\"100\":\"tom\",\"200\":\"mary\"}\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>Struct\u7c7b\u578b\u6784\u5efa: struct<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: struct(val1, val2, val3, \u2026)\n\u8bf4\u660e\uff1a\u6839\u636e\u8f93\u5165\u7684\u53c2\u6570\u6784\u5efa\u7ed3\u6784\u4f53struct\u7c7b\u578b\nhive&gt; create table struct_table as select struct('tom','mary','tim') as t from tableName;\nhive&gt; describe struct_table;\nt       struct&lt;col1:string ,col2:string,col3:string&gt;\nhive&gt; select t from tableName;\n{\"col1\":\"tom\",\"col2\":\"mary\",\"col3\":\"tim\"}\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>array\u7c7b\u578b\u6784\u5efa: array<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: array(val1, val2, \u2026)\n\u8bf4\u660e\uff1a\u6839\u636e\u8f93\u5165\u7684\u53c2\u6570\u6784\u5efa\u6570\u7ec4array\u7c7b\u578b\nhive&gt; create table arr_table as select array(\"tom\",\"mary\",\"tim\") as t from tableName;\nhive&gt; describe tableName;\nt       array&lt;string&gt;\nhive&gt; select t from tableName;\n&#91;\"tom\",\"mary\",\"tim\"]\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%A4%8D%E6%9D%82%E7%B1%BB%E5%9E%8B%E8%AE%BF%E9%97%AE%E6%93%8D%E4%BD%9C\"><\/span><strong>\u590d\u6742\u7c7b\u578b\u8bbf\u95ee\u64cd\u4f5c<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>array\u7c7b\u578b\u8bbf\u95ee: A[n]<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: A&#91;n]\n\u64cd\u4f5c\u7c7b\u578b: A\u4e3aarray\u7c7b\u578b\uff0cn\u4e3aint\u7c7b\u578b\n\u8bf4\u660e\uff1a\u8fd4\u56de\u6570\u7ec4A\u4e2d\u7684\u7b2cn\u4e2a\u53d8\u91cf\u503c\u3002\u6570\u7ec4\u7684\u8d77\u59cb\u4e0b\u6807\u4e3a0\u3002\u6bd4\u5982\uff0cA\u662f\u4e2a\u503c\u4e3a&#91;'foo', 'bar']\u7684\u6570\u7ec4\u7c7b\u578b\uff0c\u90a3\u4e48A&#91;0]\u5c06\u8fd4\u56de'foo',\u800cA&#91;1]\u5c06\u8fd4\u56de'bar'\nhive&gt; create table arr_table2 as select array(\"tom\",\"mary\",\"tim\") as t\n from tableName;\nhive&gt; select t&#91;0],t&#91;1] from arr_table2;\ntom     mary    tim\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>map\u7c7b\u578b\u8bbf\u95ee: M[key]<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: M&#91;key]\n\u64cd\u4f5c\u7c7b\u578b: M\u4e3amap\u7c7b\u578b\uff0ckey\u4e3amap\u4e2d\u7684key\u503c\n\u8bf4\u660e\uff1a\u8fd4\u56demap\u7c7b\u578bM\u4e2d\uff0ckey\u503c\u4e3a\u6307\u5b9a\u503c\u7684value\u503c\u3002\u6bd4\u5982\uff0cM\u662f\u503c\u4e3a{'f' -&gt; 'foo', 'b' -&gt; 'bar', 'all' -&gt; 'foobar'}\u7684map\u7c7b\u578b\uff0c\u90a3\u4e48M&#91;'all']\u5c06\u4f1a\u8fd4\u56de'foobar'\nhive&gt; Create table map_table2 as select map('100','tom','200','mary') as t from tableName;\nhive&gt; select t&#91;'200'],t&#91;'100'] from map_table2;\nmary    tom\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>struct\u7c7b\u578b\u8bbf\u95ee: S.x<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: S.x\n\u64cd\u4f5c\u7c7b\u578b: S\u4e3astruct\u7c7b\u578b\n\u8bf4\u660e\uff1a\u8fd4\u56de\u7ed3\u6784\u4f53S\u4e2d\u7684x\u5b57\u6bb5\u3002\u6bd4\u5982\uff0c\u5bf9\u4e8e\u7ed3\u6784\u4f53struct foobar {int foo, int bar}\uff0cfoobar.foo\u8fd4\u56de\u7ed3\u6784\u4f53\u4e2d\u7684foo\u5b57\u6bb5\nhive&gt; create table str_table2 as select struct('tom','mary','tim') as t from tableName;\nhive&gt; describe tableName;\nt       struct&lt;col1:string ,col2:string,col3:string&gt;\nhive&gt; select t.col1,t.col3 from str_table2;\ntom     tim\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%A4%8D%E6%9D%82%E7%B1%BB%E5%9E%8B%E9%95%BF%E5%BA%A6%E7%BB%9F%E8%AE%A1%E5%87%BD%E6%95%B0\"><\/span><strong>\u590d\u6742\u7c7b\u578b\u957f\u5ea6\u7edf\u8ba1\u51fd\u6570<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>Map\u7c7b\u578b\u957f\u5ea6\u51fd\u6570: size(Map&lt;k .V&gt;)<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: size(Map&lt;k .V&gt;)\n\u8fd4\u56de\u503c: int\n\u8bf4\u660e: \u8fd4\u56demap\u7c7b\u578b\u7684\u957f\u5ea6\nhive&gt; select size(t) from map_table2;\n2\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>array\u7c7b\u578b\u957f\u5ea6\u51fd\u6570: size(Array)<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u8bed\u6cd5: size(Array&lt;T&gt;)\n\u8fd4\u56de\u503c: int\n\u8bf4\u660e: \u8fd4\u56dearray\u7c7b\u578b\u7684\u957f\u5ea6\nhive&gt; select size(t) from arr_table2;\n4\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u7c7b\u578b\u8f6c\u6362\u51fd\u6570 ***<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u7c7b\u578b\u8f6c\u6362\u51fd\u6570: cast\n\u8bed\u6cd5: cast(expr as &lt;type&gt;)\n\u8fd4\u56de\u503c: Expected \"=\" to follow \"type\"\n\u8bf4\u660e: \u8fd4\u56de\u8f6c\u6362\u540e\u7684\u6570\u636e\u7c7b\u578b\nhive&gt; select cast('1' as bigint) from tableName;\n1\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"hive%E5%BD%93%E4%B8%AD%E7%9A%84lateral_view_%E4%B8%8E_explode%E4%BB%A5%E5%8F%8Areflect%E5%92%8C%E7%AA%97%E5%8F%A3%E5%87%BD%E6%95%B0\"><\/span><strong>hive\u5f53\u4e2d\u7684lateral view \u4e0e explode\u4ee5\u53careflect\u548c\u7a97\u53e3\u51fd\u6570<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E4%BD%BF%E7%94%A8explode%E5%87%BD%E6%95%B0%E5%B0%86hive%E8%A1%A8%E4%B8%AD%E7%9A%84Map%E5%92%8CArray%E5%AD%97%E6%AE%B5%E6%95%B0%E6%8D%AE%E8%BF%9B%E8%A1%8C%E6%8B%86%E5%88%86\"><\/span><strong>\u4f7f\u7528explode\u51fd\u6570\u5c06hive\u8868\u4e2d\u7684Map\u548cArray\u5b57\u6bb5\u6570\u636e\u8fdb\u884c\u62c6\u5206<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>\u200b lateral view\u7528\u4e8e\u548csplit\u3001explode\u7b49UDTF\u4e00\u8d77\u4f7f\u7528\u7684\uff0c\u80fd\u5c06\u4e00\u884c\u6570\u636e\u62c6\u5206\u6210\u591a\u884c\u6570\u636e\uff0c\u5728\u6b64\u57fa\u7840\u4e0a\u53ef\u4ee5\u5bf9\u62c6\u5206\u7684\u6570\u636e\u8fdb\u884c\u805a\u5408\uff0clateral view\u9996\u5148\u4e3a\u539f\u59cb\u8868\u7684\u6bcf\u884c\u8c03\u7528UDTF\uff0cUDTF\u4f1a\u628a\u4e00\u884c\u62c6\u5206\u6210\u4e00\u884c\u6216\u8005\u591a\u884c\uff0clateral view\u5728\u628a\u7ed3\u679c\u7ec4\u5408\uff0c\u4ea7\u751f\u4e00\u4e2a\u652f\u6301\u522b\u540d\u8868\u7684\u865a\u62df\u8868\u3002<\/p>\n\n\n\n<p>\u200b \u5176\u4e2dexplode\u8fd8\u53ef\u4ee5\u7528\u4e8e\u5c06hive\u4e00\u5217\u4e2d\u590d\u6742\u7684array\u6216\u8005map\u7ed3\u6784\u62c6\u5206\u6210\u591a\u884c<\/p>\n\n\n\n<p>\u9700\u6c42\uff1a\u73b0\u5728\u6709\u6570\u636e\u683c\u5f0f\u5982\u4e0b<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>zhangsan child1,child2,child3,child4 k1:v1,k2:v2\n\nlisi child5,child6,child7,child8 k3:v3,k4:v4\n<\/code><\/pre>\n\n\n\n<p>\u200b \u5b57\u6bb5\u4e4b\u95f4\u4f7f\u7528\\t\u5206\u5272\uff0c\u9700\u6c42\u5c06\u6240\u6709\u7684child\u8fdb\u884c\u62c6\u5f00\u6210\u4e3a\u4e00\u5217<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+----------+--+\n| mychild  |\n+----------+--+\n| child1   |\n| child2   |\n| child3   |\n| child4   |\n| child5   |\n| child6   |\n| child7   |\n| child8   |\n+----------+--+\n<\/code><\/pre>\n\n\n\n<p>\u200b \u5c06map\u7684key\u548cvalue\u4e5f\u8fdb\u884c\u62c6\u5f00\uff0c\u6210\u4e3a\u5982\u4e0b\u7ed3\u679c<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+-----------+-------------+--+\n| mymapkey  | mymapvalue  |\n+-----------+-------------+--+\n| k1        | v1          |\n| k2        | v2          |\n| k3        | v3          |\n| k4        | v4          |\n+-----------+-------------+--+\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u521b\u5efahive\u6570\u636e\u5e93<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u521b\u5efahive\u6570\u636e\u5e93\nhive (default)&gt; create database hive_explode;\nhive (default)&gt; use hive_explode;\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u521b\u5efahive\u8868\uff0c\u7136\u540e\u4f7f\u7528explode\u62c6\u5206map\u548carray<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>hive (hive_explode)&gt; create  table t3(name string,children array&lt;string&gt;,address Map&lt;string,string&gt;) row format delimited fields terminated by '\\t'  collection items terminated by ',' map keys terminated by ':' stored as textFile;\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u52a0\u8f7d\u6570\u636e<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>node03\u6267\u884c\u4ee5\u4e0b\u547d\u4ee4\u521b\u5efa\u8868\u6570\u636e\u6587\u4ef6\n mkdir -p \/export\/servers\/hivedatas\/\n cd \/export\/servers\/hivedatas\/\n vim maparray\n\u5185\u5bb9\u5982\u4e0b:\nzhangsan child1,child2,child3,child4 k1:v1,k2:v2\nlisi child5,child6,child7,child8 k3:v3,k4:v4\n\nhive\u8868\u5f53\u4e2d\u52a0\u8f7d\u6570\u636e\nhive (hive_explode)&gt; load data local inpath '\/export\/servers\/hivedatas\/maparray' into table t3;\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u4f7f\u7528explode\u5c06hive\u5f53\u4e2d\u6570\u636e\u62c6\u5f00<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u5c06array\u5f53\u4e2d\u7684\u6570\u636e\u62c6\u5206\u5f00\nhive (hive_explode)&gt; SELECT explode(children) AS myChild FROM t3;\n\n\u5c06map\u5f53\u4e2d\u7684\u6570\u636e\u62c6\u5206\u5f00\n\nhive (hive_explode)&gt; SELECT explode(address) AS (myMapKey, myMapValue) FROM t3;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E4%BD%BF%E7%94%A8explode%E6%8B%86%E5%88%86json%E5%AD%97%E7%AC%A6%E4%B8%B2\"><\/span><strong>\u4f7f\u7528explode\u62c6\u5206json\u5b57\u7b26\u4e32<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>\u9700\u6c42: \u9700\u6c42\uff1a\u73b0\u5728\u6709\u4e00\u4e9b\u6570\u636e\u683c\u5f0f\u5982\u4e0b\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|&#91;{\"source\":\"7fresh\",\"monthSales\":4900,\"userCount\":1900,\"score\":\"9.9\"},{\"source\":\"jd\",\"monthSales\":2090,\"userCount\":78981,\"score\":\"9.8\"},{\"source\":\"jdmart\",\"monthSales\":6987,\"userCount\":1600,\"score\":\"9.0\"}]\n<\/code><\/pre>\n\n\n\n<p>\u5176\u4e2d\u5b57\u6bb5\u4e0e\u5b57\u6bb5\u4e4b\u95f4\u7684\u5206\u9694\u7b26\u662f |<\/p>\n\n\n\n<p>\u6211\u4eec\u8981\u89e3\u6790\u5f97\u5230\u6240\u6709\u7684monthSales\u5bf9\u5e94\u7684\u503c\u4e3a\u4ee5\u4e0b\u8fd9\u4e00\u5217\uff08\u884c\u8f6c\u5217\uff09<\/p>\n\n\n\n<p>4900<\/p>\n\n\n\n<p>2090<\/p>\n\n\n\n<p>6987<\/p>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u521b\u5efahive\u8868<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>hive (hive_explode)&gt; create table explode_lateral_view\n                   &gt; (`area` string,\n                   &gt; `goods_id` string,\n                   &gt; `sale_info` string)\n                   &gt; ROW FORMAT DELIMITED\n                   &gt; FIELDS TERMINATED BY '|'\n                   &gt; STORED AS textfile;\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u51c6\u5907\u6570\u636e\u5e76\u52a0\u8f7d\u6570\u636e<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u51c6\u5907\u6570\u636e\u5982\u4e0b\ncd \/export\/servers\/hivedatas\nvim explode_json\n\na:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|&#91;{\"source\":\"7fresh\",\"monthSales\":4900,\"userCount\":1900,\"score\":\"9.9\"},{\"source\":\"jd\",\"monthSales\":2090,\"userCount\":78981,\"score\":\"9.8\"},{\"source\":\"jdmart\",\"monthSales\":6987,\"userCount\":1600,\"score\":\"9.0\"}]\n\n\u52a0\u8f7d\u6570\u636e\u5230hive\u8868\u5f53\u4e2d\u53bb\nhive (hive_explode)&gt; load data local inpath '\/export\/servers\/hivedatas\/explode_json' overwrite into table explode_lateral_view;\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u4f7f\u7528explode\u62c6\u5206Array<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>hive (hive_explode)&gt; select explode(split(goods_id,',')) as goods_id from explode_lateral_view;\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u4f7f\u7528explode\u62c6\u89e3Map<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>hive (hive_explode)&gt; select explode(split(area,',')) as area from explode_lateral_view;\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u62c6\u89e3json\u5b57\u6bb5<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>hive (hive_explode)&gt; select explode(split(regexp_replace(regexp_replace(sale_info,'\\\\&#91;\\\\{',''),'}]',''),'},\\\\{')) as  sale_info from explode_lateral_view;\n\n\u7136\u540e\u6211\u4eec\u60f3\u7528get_json_object\u6765\u83b7\u53d6key\u4e3amonthSales\u7684\u6570\u636e\uff1a\n\nhive (hive_explode)&gt; select get_json_object(explode(split(regexp_replace(regexp_replace(sale_info,'\\\\&#91;\\\\{',''),'}]',''),'},\\\\{')),'$.monthSales') as  sale_info from explode_lateral_view;\n\n\n\u7136\u540e\u6302\u4e86FAILED: SemanticException &#91;Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions\nUDTF explode\u4e0d\u80fd\u5199\u5728\u522b\u7684\u51fd\u6570\u5185\n\u5982\u679c\u4f60\u8fd9\u4e48\u5199\uff0c\u60f3\u67e5\u4e24\u4e2a\u5b57\u6bb5\uff0cselect explode(split(area,',')) as area,good_id from explode_lateral_view;\n\u4f1a\u62a5\u9519FAILED: SemanticException 1:40 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'good_id'\n\u4f7f\u7528UDTF\u7684\u65f6\u5019\uff0c\u53ea\u652f\u6301\u4e00\u4e2a\u5b57\u6bb5\uff0c\u8fd9\u65f6\u5019\u5c31\u9700\u8981LATERAL VIEW\u51fa\u573a\u4e86\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E9%85%8D%E5%90%88LATERAL_VIEW%E4%BD%BF%E7%94%A8\"><\/span><strong>\u914d\u5408LATERAL VIEW\u4f7f\u7528<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>\u200b \u914d\u5408lateral view\u67e5\u8be2\u591a\u4e2a\u5b57\u6bb5<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>hive (hive_explode)&gt; select goods_id2,sale_info from explode_lateral_view LATERAL VIEW explode(split(goods_id,','))goods as goods_id2;\n\n\u5176\u4e2dLATERAL VIEW explode(split(goods_id,','))goods\u76f8\u5f53\u4e8e\u4e00\u4e2a\u865a\u62df\u8868\uff0c\u4e0e\u539f\u8868explode_lateral_view\u7b1b\u5361\u5c14\u79ef\u5173\u8054\n<\/code><\/pre>\n\n\n\n<p>\u200b \u4e5f\u53ef\u4ee5\u591a\u91cd\u4f7f\u7528<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>hive (hive_explode)&gt; select goods_id2,sale_info,area2\n                    from explode_lateral_view \n                    LATERAL VIEW explode(split(goods_id,','))goods as goods_id2 \n                    LATERAL VIEW explode(split(area,','))area as area2;\u4e5f\u662f\u4e09\u4e2a\u8868\u7b1b\u5361\u5c14\u79ef\u7684\u7ed3\u679c\n<\/code><\/pre>\n\n\n\n<p>\u6700\u7ec8\uff0c\u6211\u4eec\u53ef\u4ee5\u901a\u8fc7\u4e0b\u9762\u7684\u53e5\u5b50\uff0c\u628a\u8fd9\u4e2ajson\u683c\u5f0f\u7684\u4e00\u884c\u6570\u636e\uff0c\u5b8c\u5168\u8f6c\u6362\u6210\u4e8c\u7ef4\u8868\u7684\u65b9\u5f0f\u5c55\u73b0<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>hive (hive_explode)&gt; select get_json_object(concat('{',sale_info_1,'}'),'$.source') as source,get_json_object(concat('{',sale_info_1,'}'),'$.monthSales') as monthSales,get_json_object(concat('{',sale_info_1,'}'),'$.userCount') as monthSales,get_json_object(concat('{',sale_info_1,'}'),'$.score') as monthSales from explode_lateral_view LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\\\\&#91;\\\\{',''),'}]',''),'},\\\\{'))sale_info as sale_info_1;\n<\/code><\/pre>\n\n\n\n<p>\u603b\u7ed3\uff1a<\/p>\n\n\n\n<p>Lateral View\u901a\u5e38\u548cUDTF\u4e00\u8d77\u51fa\u73b0\uff0c\u4e3a\u4e86\u89e3\u51b3UDTF\u4e0d\u5141\u8bb8\u5728select\u5b57\u6bb5\u7684\u95ee\u9898\u3002 Multiple Lateral View\u53ef\u4ee5\u5b9e\u73b0\u7c7b\u4f3c\u7b1b\u5361\u5c14\u4e58\u79ef\u3002 Outer\u5173\u952e\u5b57\u53ef\u4ee5\u628a\u4e0d\u8f93\u51fa\u7684UDTF\u7684\u7a7a\u7ed3\u679c\uff0c\u8f93\u51fa\u6210NULL\uff0c\u9632\u6b62\u4e22\u5931\u6570\u636e\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E8%A1%8C%E8%BD%AC%E5%88%97\"><\/span><strong>\u884c\u8f6c\u5217<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>\u76f8\u5173\u53c2\u6570\u8bf4\u660e:<\/p>\n\n\n\n<p>\u200b CONCAT(string A\/col, string B\/col\u2026)\uff1a\u8fd4\u56de\u8f93\u5165\u5b57\u7b26\u4e32\u8fde\u63a5\u540e\u7684\u7ed3\u679c\uff0c\u652f\u6301\u4efb\u610f\u4e2a\u8f93\u5165\u5b57\u7b26\u4e32;<\/p>\n\n\n\n<p>\u200b CONCAT_WS(separator, str1, str2,&#8230;)\uff1a\u5b83\u662f\u4e00\u4e2a\u7279\u6b8a\u5f62\u5f0f\u7684 CONCAT()\u3002\u7b2c\u4e00\u4e2a\u53c2\u6570\u5269\u4f59\u53c2\u6570\u95f4\u7684\u5206\u9694\u7b26\u3002\u5206\u9694\u7b26\u53ef\u4ee5\u662f\u4e0e\u5269\u4f59\u53c2\u6570\u4e00\u6837\u7684\u5b57\u7b26\u4e32\u3002\u5982\u679c\u5206\u9694\u7b26\u662f NULL\uff0c\u8fd4\u56de\u503c\u4e5f\u5c06\u4e3a NULL\u3002\u8fd9\u4e2a\u51fd\u6570\u4f1a\u8df3\u8fc7\u5206\u9694\u7b26\u53c2\u6570\u540e\u7684\u4efb\u4f55 NULL \u548c\u7a7a\u5b57\u7b26\u4e32\u3002\u5206\u9694\u7b26\u5c06\u88ab\u52a0\u5230\u88ab\u8fde\u63a5\u7684\u5b57\u7b26\u4e32\u4e4b\u95f4;<\/p>\n\n\n\n<p>\u200b COLLECT_SET(col)\uff1a\u51fd\u6570\u53ea\u63a5\u53d7\u57fa\u672c\u6570\u636e\u7c7b\u578b\uff0c\u5b83\u7684\u4e3b\u8981\u4f5c\u7528\u662f\u5c06\u67d0\u5b57\u6bb5\u7684\u503c\u8fdb\u884c\u53bb\u91cd\u6c47\u603b\uff0c\u4ea7\u751farray\u7c7b\u578b\u5b57\u6bb5\u3002<\/p>\n\n\n\n<p>\u6570\u636e\u51c6\u5907:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><th>name<\/th><th>constellation<\/th><th>blood_type<\/th><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>\u9700\u6c42: \u628a\u661f\u5ea7\u548c\u8840\u578b\u4e00\u6837\u7684\u4eba\u5f52\u7c7b\u5230\u4e00\u8d77\u3002\u7ed3\u679c\u5982\u4e0b\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u5c04\u624b\u5ea7,A            \u8001\u738b|\u51e4\u59d0\n\u767d\u7f8a\u5ea7,A            \u5b59\u609f\u7a7a|\u732a\u516b\u6212\n\u767d\u7f8a\u5ea7,B            \u5b8b\u5b8b\n<\/code><\/pre>\n\n\n\n<p>\u5b9e\u73b0\u6b65\u9aa4:<\/p>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u521b\u5efa\u672c\u5730constellation.txt\uff0c\u5bfc\u5165\u6570\u636e<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>node03\u670d\u52a1\u5668\u6267\u884c\u4ee5\u4e0b\u547d\u4ee4\u521b\u5efa\u6587\u4ef6\uff0c\u6ce8\u610f\u6570\u636e\u4f7f\u7528\\t\u8fdb\u884c\u5206\u5272\ncd \/export\/servers\/hivedatas\nvim constellation.txt\n\n\u6570\u636e\u5982\u4e0b: \n\u5b59\u609f\u7a7a \u767d\u7f8a\u5ea7 A\n\u8001\u738b \u5c04\u624b\u5ea7 A\n\u5b8b\u5b8b \u767d\u7f8a\u5ea7 B       \n\u732a\u516b\u6212 \u767d\u7f8a\u5ea7 A\n\u51e4\u59d0 \u5c04\u624b\u5ea7 A\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u521b\u5efahive\u8868\u5e76\u5bfc\u5165\u6570\u636e<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>\u521b\u5efahive\u8868\u5e76\u52a0\u8f7d\u6570\u636e\nhive (hive_explode)&gt; create table person_info(\n                    name string, \n                    constellation string, \n                    blood_type string) \n                    row format delimited fields terminated by \"\\t\";\n                    \n\u52a0\u8f7d\u6570\u636e\nhive (hive_explode)&gt; load data local inpath '\/export\/servers\/hivedatas\/constellation.txt' into table person_info;\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u6309\u9700\u6c42\u67e5\u8be2\u6570\u636e<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>hive (hive_explode)&gt; select\n                        t1.base,\n                        concat_ws('|', collect_set(t1.name)) name\n                    from\n                        (select\n                            name,\n                            concat(constellation, \",\" , blood_type) base\n                        from\n                            person_info) t1\n                    group by\n                        t1.base;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%88%97%E8%BD%AC%E8%A1%8C\"><\/span><strong>\u5217\u8f6c\u884c<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>\u6240\u9700\u51fd\u6570:<\/p>\n\n\n\n<p>\u200b EXPLODE(col)\uff1a\u5c06hive\u4e00\u5217\u4e2d\u590d\u6742\u7684array\u6216\u8005map\u7ed3\u6784\u62c6\u5206\u6210\u591a\u884c\u3002<\/p>\n\n\n\n<p>\u200b LATERAL VIEW<\/p>\n\n\n\n<p>\u200b \u7528\u6cd5\uff1aLATERAL VIEW udtf(expression) tableAlias AS columnAlias<\/p>\n\n\n\n<p>\u200b \u89e3\u91ca\uff1a\u7528\u4e8e\u548csplit, explode\u7b49UDTF\u4e00\u8d77\u4f7f\u7528\uff0c\u5b83\u80fd\u591f\u5c06\u4e00\u5217\u6570\u636e\u62c6\u6210\u591a\u884c\u6570\u636e\uff0c\u5728\u6b64\u57fa\u7840\u4e0a\u53ef\u4ee5\u5bf9\u62c6\u5206\u540e\u7684\u6570\u636e\u8fdb\u884c\u805a\u5408\u3002<\/p>\n\n\n\n<p>\u6570\u636e\u51c6\u5907:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>cd \/export\/servers\/hivedatas\nvim movie.txt\n\u6587\u4ef6\u5185\u5bb9\u5982\u4e0b:  \u6570\u636e\u5b57\u6bb5\u4e4b\u95f4\u4f7f\u7528\\t\u8fdb\u884c\u5206\u5272\n\u300a\u7591\u72af\u8ffd\u8e2a\u300b \u60ac\u7591,\u52a8\u4f5c,\u79d1\u5e7b,\u5267\u60c5\n\u300aLie to me\u300b \u60ac\u7591,\u8b66\u532a,\u52a8\u4f5c,\u5fc3\u7406,\u5267\u60c5\n\u300a\u6218\u72fc2\u300b \u6218\u4e89,\u52a8\u4f5c,\u707e\u96be\n<\/code><\/pre>\n\n\n\n<p>\u9700\u6c42: \u5c06\u7535\u5f71\u5206\u7c7b\u4e2d\u7684\u6570\u7ec4\u6570\u636e\u5c55\u5f00\u3002\u7ed3\u679c\u5982\u4e0b\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u300a\u7591\u72af\u8ffd\u8e2a\u300b \u60ac\u7591\n\u300a\u7591\u72af\u8ffd\u8e2a\u300b \u52a8\u4f5c\n\u300a\u7591\u72af\u8ffd\u8e2a\u300b \u79d1\u5e7b\n\u300a\u7591\u72af\u8ffd\u8e2a\u300b \u5267\u60c5\n\u300aLie to me\u300b \u60ac\u7591\n\u300aLie to me\u300b \u8b66\u532a\n\u300aLie to me\u300b \u52a8\u4f5c\n\u300aLie to me\u300b \u5fc3\u7406\n\u300aLie to me\u300b \u5267\u60c5\n\u300a\u6218\u72fc2\u300b \u6218\u4e89\n\u300a\u6218\u72fc2\u300b \u52a8\u4f5c\n\u300a\u6218\u72fc2\u300b \u707e\u96be\n<\/code><\/pre>\n\n\n\n<p>\u5b9e\u73b0\u6b65\u9aa4:<\/p>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u521b\u5efahive\u8868<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>create table movie_info(\n    movie string, \n    category array&lt;string&gt;) \nrow format delimited fields terminated by \"\\t\"\ncollection items terminated by \",\";\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u52a0\u8f7d\u6570\u636e<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>load data local inpath \"\/export\/servers\/hivedatas\/movie.txt\" into table movie_info;\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u6309\u9700\u6c42\u67e5\u8be2\u6570\u636e<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>select\n    movie,\n    category_name\nfrom \n    movie_info lateral view explode(category) table_tmp as category_name;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"reflect%E5%87%BD%E6%95%B0\"><\/span><strong>reflect\u51fd\u6570<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>\u200b reflect\u51fd\u6570\u53ef\u4ee5\u652f\u6301\u5728sql\u4e2d\u8c03\u7528java\u4e2d\u7684\u81ea\u5e26\u51fd\u6570\uff0c\u79d2\u6740\u4e00\u5207udf\u51fd\u6570\u3002<\/p>\n\n\n\n<p>\u9700\u6c421: \u4f7f\u7528java.lang.Math\u5f53\u4e2d\u7684Max\u6c42\u4e24\u5217\u4e2d\u6700\u5927\u503c<\/p>\n\n\n\n<p>\u5b9e\u73b0\u6b65\u9aa4:<\/p>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u521b\u5efahive\u8868<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>create table test_udf(col1 int,col2 int) row format delimited fields terminated by ',';\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u51c6\u5907\u6570\u636e\u5e76\u52a0\u8f7d\u6570\u636e<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>cd \/export\/servers\/hivedatas\nvim test_udf \n\n\u6587\u4ef6\u5185\u5bb9\u5982\u4e0b:\n1,2\n4,3\n6,4\n7,5\n5,6\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u52a0\u8f7d\u6570\u636e<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>hive (hive_explode)&gt; load data local inpath '\/export\/servers\/hivedatas\/test_udf' overwrite into table test_udf;\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u4f7f\u7528java.lang.Math\u5f53\u4e2d\u7684Max\u6c42\u4e24\u5217\u5f53\u4e2d\u7684\u6700\u5927\u503c<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>hive (hive_explode)&gt; select reflect(\"java.lang.Math\",\"max\",col1,col2) from test_udf;\n<\/code><\/pre>\n\n\n\n<p>\u9700\u6c422: \u6587\u4ef6\u4e2d\u4e0d\u540c\u7684\u8bb0\u5f55\u6765\u6267\u884c\u4e0d\u540c\u7684java\u7684\u5185\u7f6e\u51fd\u6570<\/p>\n\n\n\n<p>\u5b9e\u73b0\u6b65\u9aa4:<\/p>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u521b\u5efahive\u8868<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>hive (hive_explode)&gt; create table test_udf2(class_name string,method_name string,col1 int , col2 int) row format delimited fields terminated by ',';\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u51c6\u5907\u6570\u636e<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>cd \/export\/servers\/hivedatas\nvim test_udf2\n\n\u6587\u4ef6\u5185\u5bb9\u5982\u4e0b:\njava.lang.Math,min,1,2\njava.lang.Math,max,2,3\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u52a0\u8f7d\u6570\u636e<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>hive (hive_explode)&gt; load data local inpath '\/export\/servers\/hivedatas\/test_udf2' overwrite into table test_udf2;\n<\/code><\/pre>\n\n\n\n<ul>\n<li><\/li>\n<\/ul>\n\n\n\n<ol>\n<li>\u6267\u884c\u67e5\u8be2<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>hive (hive_explode)&gt; select reflect(class_name,method_name,col1,col2) from test_udf2;\n<\/code><\/pre>\n\n\n\n<p>\u9700\u6c423: \u5224\u65ad\u662f\u5426\u4e3a\u6570\u5b57<\/p>\n\n\n\n<p>\u5b9e\u73b0\u65b9\u5f0f:<\/p>\n\n\n\n<p>\u200b \u4f7f\u7528apache commons\u4e2d\u7684\u51fd\u6570\uff0ccommons\u4e0b\u7684jar\u5df2\u7ecf\u5305\u542b\u5728hadoop\u7684classpath\u4e2d\uff0c\u6240\u4ee5\u53ef\u4ee5\u76f4\u63a5\u4f7f\u7528\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select reflect(\"org.apache.commons.lang.math.NumberUtils\",\"isNumber\",\"123\")\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E7%AA%97%E5%8F%A3%E5%87%BD%E6%95%B0%E4%B8%8E%E5%88%86%E6%9E%90%E5%87%BD%E6%95%B0\"><\/span><strong>\u7a97\u53e3\u51fd\u6570\u4e0e\u5206\u6790\u51fd\u6570<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>\u5728sql\u4e2d\u6709\u4e00\u7c7b\u51fd\u6570\u53eb\u505a\u805a\u5408\u51fd\u6570,\u4f8b\u5982sum()\u3001avg()\u3001max()\u7b49\u7b49,\u8fd9\u7c7b\u51fd\u6570\u53ef\u4ee5\u5c06\u591a\u884c\u6570\u636e\u6309\u7167\u89c4\u5219\u805a\u96c6\u4e3a\u4e00\u884c,\u4e00\u822c\u6765\u8bb2\u805a\u96c6\u540e\u7684\u884c\u6570\u662f\u8981\u5c11\u4e8e\u805a\u96c6\u524d\u7684\u884c\u6570\u7684\u3002\u4f46\u662f\u6709\u65f6\u6211\u4eec\u60f3\u8981\u65e2\u663e\u793a\u805a\u96c6\u524d\u7684\u6570\u636e,\u53c8\u8981\u663e\u793a\u805a\u96c6\u540e\u7684\u6570\u636e,\u8fd9\u65f6\u6211\u4eec\u4fbf\u5f15\u5165\u4e86\u7a97\u53e3\u51fd\u6570\u3002\u7a97\u53e3\u51fd\u6570\u53c8\u53ebOLAP\u51fd\u6570\/\u5206\u6790\u51fd\u6570\uff0c\u7a97\u53e3\u51fd\u6570\u517c\u5177\u5206\u7ec4\u548c\u6392\u5e8f\u529f\u80fd\u3002<\/p>\n\n\n\n<p>\u7a97\u53e3\u51fd\u6570\u6700\u91cd\u8981\u7684\u5173\u952e\u5b57\u662f&nbsp;<strong>partition by<\/strong>&nbsp;\u548c&nbsp;<strong>order by\u3002<\/strong><\/p>\n\n\n\n<p>\u5177\u4f53\u8bed\u6cd5\u5982\u4e0b\uff1a<strong>over (partition by xxx order by xxx)<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"sum%E3%80%81avg%E3%80%81min%E3%80%81max\"><\/span><strong>sum\u3001avg\u3001min\u3001max<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>\u51c6\u5907\u6570\u636e<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u5efa\u8868\u8bed\u53e5:\ncreate table test_t1(\ncookieid string,\ncreatetime string,   --day \npv int\n) row format delimited \nfields terminated by ',';\n\n\u52a0\u8f7d\u6570\u636e\uff1a\nload data local inpath '\/root\/hivedata\/test_t1.dat' into table test_t1;\n\ncookie1,2020-04-10,1\ncookie1,2020-04-11,5\ncookie1,2020-04-12,7\ncookie1,2020-04-13,3\ncookie1,2020-04-14,2\ncookie1,2020-04-15,4\ncookie1,2020-04-16,4\n\n\u5f00\u542f\u667a\u80fd\u672c\u5730\u6a21\u5f0f\nSET hive.exec.mode.local.auto=true;\n<\/code><\/pre>\n\n\n\n<p>SUM\u51fd\u6570\u548c\u7a97\u53e3\u51fd\u6570\u7684\u914d\u5408\u4f7f\u7528\uff1a\u7ed3\u679c\u548cORDER BY\u76f8\u5173,\u9ed8\u8ba4\u4e3a\u5347\u5e8f\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select cookieid,createtime,pv,\nsum(pv) over(partition by cookieid order by createtime) as pv1 \nfrom test_t1;\n\nselect cookieid,createtime,pv,\nsum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2\nfrom test_t1;\n\nselect cookieid,createtime,pv,\nsum(pv) over(partition by cookieid) as pv3\nfrom test_t1;\n\nselect cookieid,createtime,pv,\nsum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4\nfrom test_t1;\n\nselect cookieid,createtime,pv,\nsum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5\nfrom test_t1;\n\nselect cookieid,createtime,pv,\nsum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6\nfrom test_t1;\n\n\npv1: \u5206\u7ec4\u5185\u4ece\u8d77\u70b9\u5230\u5f53\u524d\u884c\u7684pv\u7d2f\u79ef\uff0c\u5982\uff0c11\u53f7\u7684pv1=10\u53f7\u7684pv+11\u53f7\u7684pv, 12\u53f7=10\u53f7+11\u53f7+12\u53f7\npv2: \u540cpv1\npv3: \u5206\u7ec4\u5185(cookie1)\u6240\u6709\u7684pv\u7d2f\u52a0\npv4: \u5206\u7ec4\u5185\u5f53\u524d\u884c+\u5f80\u524d3\u884c\uff0c\u5982\uff0c11\u53f7=10\u53f7+11\u53f7\uff0c 12\u53f7=10\u53f7+11\u53f7+12\u53f7\uff0c\n                        13\u53f7=10\u53f7+11\u53f7+12\u53f7+13\u53f7\uff0c 14\u53f7=11\u53f7+12\u53f7+13\u53f7+14\u53f7\npv5: \u5206\u7ec4\u5185\u5f53\u524d\u884c+\u5f80\u524d3\u884c+\u5f80\u540e1\u884c\uff0c\u5982\uff0c14\u53f7=11\u53f7+12\u53f7+13\u53f7+14\u53f7+15\u53f7=5+7+3+2+4=21\npv6: \u5206\u7ec4\u5185\u5f53\u524d\u884c+\u5f80\u540e\u6240\u6709\u884c\uff0c\u5982\uff0c13\u53f7=13\u53f7+14\u53f7+15\u53f7+16\u53f7=3+2+4+4=13\uff0c\n        14\u53f7=14\u53f7+15\u53f7+16\u53f7=2+4+4=10\n<\/code><\/pre>\n\n\n\n<p>\u200b \u5982\u679c\u4e0d\u6307\u5b9arows between,\u9ed8\u8ba4\u4e3a\u4ece\u8d77\u70b9\u5230\u5f53\u524d\u884c;<\/p>\n\n\n\n<p>\u200b \u5982\u679c\u4e0d\u6307\u5b9aorder by\uff0c\u5219\u5c06\u5206\u7ec4\u5185\u6240\u6709\u503c\u7d2f\u52a0;<\/p>\n\n\n\n<p>\u200b \u5173\u952e\u662f\u7406\u89e3rows between\u542b\u4e49,\u4e5f\u53eb\u505awindow\u5b50\u53e5\uff1a<\/p>\n\n\n\n<p>\u200b preceding\uff1a\u5f80\u524d<\/p>\n\n\n\n<p>\u200b following\uff1a\u5f80\u540e<\/p>\n\n\n\n<p>\u200b current row\uff1a\u5f53\u524d\u884c<\/p>\n\n\n\n<p>\u200b unbounded\uff1a\u8d77\u70b9<\/p>\n\n\n\n<p>\u200b unbounded preceding \u8868\u793a\u4ece\u524d\u9762\u7684\u8d77\u70b9<\/p>\n\n\n\n<p>\u200b unbounded following\uff1a\u8868\u793a\u5230\u540e\u9762\u7684\u7ec8\u70b9<\/p>\n\n\n\n<p>\u200b AVG\uff0cMIN\uff0cMAX\uff0c\u548cSUM\u7528\u6cd5\u4e00\u6837\u3002<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"row_number%E3%80%81rank%E3%80%81dense_rank%E3%80%81ntile\"><\/span><strong>row_number\u3001rank\u3001dense_rank\u3001ntile<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>\u51c6\u5907\u6570\u636e<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>cookie1,2020-04-10,1\ncookie1,2020-04-11,5\ncookie1,2020-04-12,7\ncookie1,2020-04-13,3\ncookie1,2020-04-14,2\ncookie1,2020-04-15,4\ncookie1,2020-04-16,4\ncookie2,2020-04-10,2\ncookie2,2020-04-11,3\ncookie2,2020-04-12,5\ncookie2,2020-04-13,6\ncookie2,2020-04-14,3\ncookie2,2020-04-15,9\ncookie2,2020-04-16,7\n \nCREATE TABLE test_t2 (\ncookieid string,\ncreatetime string,   --day \npv INT\n) ROW FORMAT DELIMITED \nFIELDS TERMINATED BY ',' \nstored as textfile;\n  \n\u52a0\u8f7d\u6570\u636e\uff1a\nload data local inpath '\/root\/hivedata\/test_t2.dat' into table test_t2;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>ROW_NUMBER()\u4f7f\u7528<br>ROW_NUMBER()\u4ece1\u5f00\u59cb\uff0c\u6309\u7167\u987a\u5e8f\uff0c\u751f\u6210\u5206\u7ec4\u5185\u8bb0\u5f55\u7684\u5e8f\u5217\u3002<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \ncookieid,\ncreatetime,\npv,\nROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn \nFROM test_t2;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>RANK \u548c DENSE_RANK\u4f7f\u7528<br>RANK() \u751f\u6210\u6570\u636e\u9879\u5728\u5206\u7ec4\u4e2d\u7684\u6392\u540d\uff0c\u6392\u540d\u76f8\u7b49\u4f1a\u5728\u540d\u6b21\u4e2d\u7559\u4e0b\u7a7a\u4f4d \u3002<br>DENSE_RANK()\u751f\u6210\u6570\u636e\u9879\u5728\u5206\u7ec4\u4e2d\u7684\u6392\u540d\uff0c\u6392\u540d\u76f8\u7b49\u4f1a\u5728\u540d\u6b21\u4e2d\u4e0d\u4f1a\u7559\u4e0b\u7a7a\u4f4d\u3002<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \ncookieid,\ncreatetime,\npv,\nRANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,\nDENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,\nROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 \nFROM test_t2 \nWHERE cookieid = 'cookie1';\n<\/code><\/pre>\n\n\n\n<ul>\n<li>NTILE<br>\u6709\u65f6\u4f1a\u6709\u8fd9\u6837\u7684\u9700\u6c42:\u5982\u679c\u6570\u636e\u6392\u5e8f\u540e\u5206\u4e3a\u4e09\u90e8\u5206\uff0c\u4e1a\u52a1\u4eba\u5458\u53ea\u5173\u5fc3\u5176\u4e2d\u7684\u4e00\u90e8\u5206\uff0c\u5982\u4f55\u5c06\u8fd9\u4e2d\u95f4\u7684\u4e09\u5206\u4e4b\u4e00\u6570\u636e\u62ff\u51fa\u6765\u5462?NTILE\u51fd\u6570\u5373\u53ef\u4ee5\u6ee1\u8db3\u3002<br>ntile\u53ef\u4ee5\u770b\u6210\u662f\uff1a\u628a\u6709\u5e8f\u7684\u6570\u636e\u96c6\u5408\u5e73\u5747\u5206\u914d\u5230\u6307\u5b9a\u7684\u6570\u91cf\uff08num\uff09\u4e2a\u6876\u4e2d, \u5c06\u6876\u53f7\u5206\u914d\u7ed9\u6bcf\u4e00\u884c\u3002\u5982\u679c\u4e0d\u80fd\u5e73\u5747\u5206\u914d\uff0c\u5219\u4f18\u5148\u5206\u914d\u8f83\u5c0f\u7f16\u53f7\u7684\u6876\uff0c\u5e76\u4e14\u5404\u4e2a\u6876\u4e2d\u80fd\u653e\u7684\u884c\u6570\u6700\u591a\u76f8\u5dee1\u3002<br>\u7136\u540e\u53ef\u4ee5\u6839\u636e\u6876\u53f7\uff0c\u9009\u53d6\u524d\u6216\u540e n\u5206\u4e4b\u51e0\u7684\u6570\u636e\u3002\u6570\u636e\u4f1a\u5b8c\u6574\u5c55\u793a\u51fa\u6765\uff0c\u53ea\u662f\u7ed9\u76f8\u5e94\u7684\u6570\u636e\u6253\u6807\u7b7e\uff1b\u5177\u4f53\u8981\u53d6\u51e0\u5206\u4e4b\u51e0\u7684\u6570\u636e\uff0c\u9700\u8981\u518d\u5d4c\u5957\u4e00\u5c42\u6839\u636e\u6807\u7b7e\u53d6\u51fa\u3002<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \ncookieid,\ncreatetime,\npv,\nNTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,\nNTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,\nNTILE(4) OVER(ORDER BY createtime) AS rn3\nFROM test_t2 \nORDER BY cookieid,createtime;\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%85%B6%E4%BB%96%E4%B8%80%E4%BA%9B%E7%AA%97%E5%8F%A3%E5%87%BD%E6%95%B0\"><\/span><strong>\u5176\u4ed6\u4e00\u4e9b\u7a97\u53e3\u51fd\u6570<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"lagleadfirst_valuelast_value\"><\/span><strong>lag,lead,first_value,last_value<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<ul>\n<li>LAG<br><strong>LAG(col,n,DEFAULT) \u7528\u4e8e\u7edf\u8ba1\u7a97\u53e3\u5185\u5f80\u4e0a\u7b2cn\u884c\u503c<\/strong>\u7b2c\u4e00\u4e2a\u53c2\u6570\u4e3a\u5217\u540d\uff0c\u7b2c\u4e8c\u4e2a\u53c2\u6570\u4e3a\u5f80\u4e0a\u7b2cn\u884c\uff08\u53ef\u9009\uff0c\u9ed8\u8ba4\u4e3a1\uff09\uff0c\u7b2c\u4e09\u4e2a\u53c2\u6570\u4e3a\u9ed8\u8ba4\u503c\uff08\u5f53\u5f80\u4e0a\u7b2cn\u884c\u4e3aNULL\u65f6\u5019\uff0c\u53d6\u9ed8\u8ba4\u503c\uff0c\u5982\u4e0d\u6307\u5b9a\uff0c\u5219\u4e3aNULL\uff09<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT cookieid,\ncreatetime,\nurl,\nROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,\nLAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,\nLAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time \nFROM test_t4;\n\n\nlast_1_time: \u6307\u5b9a\u4e86\u5f80\u4e0a\u7b2c1\u884c\u7684\u503c\uff0cdefault\u4e3a'1970-01-01 00:00:00'  \n                 cookie1\u7b2c\u4e00\u884c\uff0c\u5f80\u4e0a1\u884c\u4e3aNULL,\u56e0\u6b64\u53d6\u9ed8\u8ba4\u503c 1970-01-01 00:00:00\n                 cookie1\u7b2c\u4e09\u884c\uff0c\u5f80\u4e0a1\u884c\u503c\u4e3a\u7b2c\u4e8c\u884c\u503c\uff0c2015-04-10 10:00:02\n                 cookie1\u7b2c\u516d\u884c\uff0c\u5f80\u4e0a1\u884c\u503c\u4e3a\u7b2c\u4e94\u884c\u503c\uff0c2015-04-10 10:50:01\nlast_2_time: \u6307\u5b9a\u4e86\u5f80\u4e0a\u7b2c2\u884c\u7684\u503c\uff0c\u4e3a\u6307\u5b9a\u9ed8\u8ba4\u503c\n         cookie1\u7b2c\u4e00\u884c\uff0c\u5f80\u4e0a2\u884c\u4e3aNULL\n         cookie1\u7b2c\u4e8c\u884c\uff0c\u5f80\u4e0a2\u884c\u4e3aNULL\n         cookie1\u7b2c\u56db\u884c\uff0c\u5f80\u4e0a2\u884c\u4e3a\u7b2c\u4e8c\u884c\u503c\uff0c2015-04-10 10:00:02\n         cookie1\u7b2c\u4e03\u884c\uff0c\u5f80\u4e0a2\u884c\u4e3a\u7b2c\u4e94\u884c\u503c\uff0c2015-04-10 10:50:01\n<\/code><\/pre>\n\n\n\n<ul>\n<li>LEAD<\/li>\n<\/ul>\n\n\n\n<p>\u4e0eLAG\u76f8\u53cd&nbsp;<strong>LEAD(col,n,DEFAULT) \u7528\u4e8e\u7edf\u8ba1\u7a97\u53e3\u5185\u5f80\u4e0b\u7b2cn\u884c\u503c<\/strong>&nbsp;\u7b2c\u4e00\u4e2a\u53c2\u6570\u4e3a\u5217\u540d\uff0c\u7b2c\u4e8c\u4e2a\u53c2\u6570\u4e3a\u5f80\u4e0b\u7b2cn\u884c\uff08\u53ef\u9009\uff0c\u9ed8\u8ba4\u4e3a1\uff09\uff0c\u7b2c\u4e09\u4e2a\u53c2\u6570\u4e3a\u9ed8\u8ba4\u503c\uff08\u5f53\u5f80\u4e0b\u7b2cn\u884c\u4e3aNULL\u65f6\u5019\uff0c\u53d6\u9ed8\u8ba4\u503c\uff0c\u5982\u4e0d\u6307\u5b9a\uff0c\u5219\u4e3aNULL\uff09<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT cookieid,\ncreatetime,\nurl,\nROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,\nLEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,\nLEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time \nFROM test_t4;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>FIRST_VALUE<br>\u53d6\u5206\u7ec4\u5185\u6392\u5e8f\u540e\uff0c\u622a\u6b62\u5230\u5f53\u524d\u884c\uff0c\u7b2c\u4e00\u4e2a\u503c<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code> SELECT cookieid,\n createtime,\n url,\n ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,\n FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1 \n FROM test_t4;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>LAST_VALUE<\/li>\n<\/ul>\n\n\n\n<p>\u53d6\u5206\u7ec4\u5185\u6392\u5e8f\u540e\uff0c\u622a\u6b62\u5230\u5f53\u524d\u884c\uff0c\u6700\u540e\u4e00\u4e2a\u503c<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT cookieid,\ncreatetime,\nurl,\nROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,\nLAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1 \nFROM test_t4;\n<\/code><\/pre>\n\n\n\n<p>\u5982\u679c\u60f3\u8981\u53d6\u5206\u7ec4\u5185\u6392\u5e8f\u540e\u6700\u540e\u4e00\u4e2a\u503c\uff0c\u5219\u9700\u8981\u53d8\u901a\u4e00\u4e0b\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT cookieid,\ncreatetime,\nurl,\nROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,\nLAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,\nFIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2 \nFROM test_t4 \nORDER BY cookieid,createtime;\n<\/code><\/pre>\n\n\n\n<p><strong>\u7279\u522b\u6ce8\u610forder by<\/strong><\/p>\n\n\n\n<p>\u5982\u679c\u4e0d\u6307\u5b9aORDER BY\uff0c\u5219\u8fdb\u884c\u6392\u5e8f\u6df7\u4e71\uff0c\u4f1a\u51fa\u73b0\u9519\u8bef\u7684\u7ed3\u679c<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT cookieid,\ncreatetime,\nurl,\nFIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2  \nFROM test_t4;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"cume_distpercent_rank\"><\/span><strong>cume_dist,percent_rank<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>\u8fd9\u4e24\u4e2a\u5e8f\u5217\u5206\u6790\u51fd\u6570\u4e0d\u662f\u5f88\u5e38\u7528\uff0c<strong>\u6ce8\u610f\uff1a \u5e8f\u5217\u51fd\u6570\u4e0d\u652f\u6301WINDOW\u5b50\u53e5<\/strong><\/p>\n\n\n\n<ul>\n<li>\u6570\u636e\u51c6\u5907<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>d1,user1,1000\nd1,user2,2000\nd1,user3,3000\nd2,user4,4000\nd2,user5,5000\n \nCREATE EXTERNAL TABLE test_t3 (\ndept STRING,\nuserid string,\nsal INT\n) ROW FORMAT DELIMITED \nFIELDS TERMINATED BY ',' \nstored as textfile;\n\n\u52a0\u8f7d\u6570\u636e\uff1a\nload data local inpath '\/root\/hivedata\/test_t3.dat' into table test_t3;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<ul>\n<li>CUME_DIST \u548corder byd\u7684\u6392\u5e8f\u987a\u5e8f\u6709\u5173\u7cfb<br>CUME_DIST \u5c0f\u4e8e\u7b49\u4e8e\u5f53\u524d\u503c\u7684\u884c\u6570\/\u5206\u7ec4\u5185\u603b\u884c\u6570 order \u9ed8\u8ba4\u987a\u5e8f \u6b63\u5e8f \u5347\u5e8f \u6bd4\u5982\uff0c\u7edf\u8ba1\u5c0f\u4e8e\u7b49\u4e8e\u5f53\u524d\u85aa\u6c34\u7684\u4eba\u6570\uff0c\u6240\u5360\u603b\u4eba\u6570\u7684\u6bd4\u4f8b<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code> SELECT \n dept,\n userid,\n sal,\n CUME_DIST() OVER(ORDER BY sal) AS rn1,\n CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2 \n FROM test_t3;\n \n rn1: \u6ca1\u6709partition,\u6240\u6709\u6570\u636e\u5747\u4e3a1\u7ec4\uff0c\u603b\u884c\u6570\u4e3a5\uff0c\n      \u7b2c\u4e00\u884c\uff1a\u5c0f\u4e8e\u7b49\u4e8e1000\u7684\u884c\u6570\u4e3a1\uff0c\u56e0\u6b64\uff0c1\/5=0.2\n      \u7b2c\u4e09\u884c\uff1a\u5c0f\u4e8e\u7b49\u4e8e3000\u7684\u884c\u6570\u4e3a3\uff0c\u56e0\u6b64\uff0c3\/5=0.6\n rn2: \u6309\u7167\u90e8\u95e8\u5206\u7ec4\uff0cdpet=d1\u7684\u884c\u6570\u4e3a3,\n      \u7b2c\u4e8c\u884c\uff1a\u5c0f\u4e8e\u7b49\u4e8e2000\u7684\u884c\u6570\u4e3a2\uff0c\u56e0\u6b64\uff0c2\/3=0.6666666666666666\n<\/code><\/pre>\n\n\n\n<ul>\n<li>PERCENT_RANK<br>PERCENT_RANK \u5206\u7ec4\u5185\u5f53\u524d\u884c\u7684RANK\u503c-1\/\u5206\u7ec4\u5185\u603b\u884c\u6570-1<br>\u7ecf\u8c03\u7814 \u8be5\u51fd\u6570\u663e\u793a\u73b0\u5b9e\u610f\u4e49\u4e0d\u660e\u6717 \u6709\u5f85\u4e8e\u7ee7\u7eed\u8003\u8bc1<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>  SELECT \n  dept,\n  userid,\n  sal,\n  PERCENT_RANK() OVER(ORDER BY sal) AS rn1,   --\u5206\u7ec4\u5185\n  RANK() OVER(ORDER BY sal) AS rn11,          --\u5206\u7ec4\u5185RANK\u503c\n  SUM(1) OVER(PARTITION BY NULL) AS rn12,     --\u5206\u7ec4\u5185\u603b\u884c\u6570\n  PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2 \n  FROM test_t3;\n  \n  rn1: rn1 = (rn11-1) \/ (rn12-1) \n      \u7b2c\u4e00\u884c,(1-1)\/(5-1)=0\/4=0\n      \u7b2c\u4e8c\u884c,(2-1)\/(5-1)=1\/4=0.25\n      \u7b2c\u56db\u884c,(4-1)\/(5-1)=3\/4=0.75\n  rn2: \u6309\u7167dept\u5206\u7ec4\uff0c\n       dept=d1\u7684\u603b\u884c\u6570\u4e3a3\n       \u7b2c\u4e00\u884c\uff0c(1-1)\/(3-1)=0\n       \u7b2c\u4e09\u884c\uff0c(3-1)\/(3-1)=1\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"grouping_setsgrouping_idcuberollup\"><\/span><strong>grouping sets,grouping__id,cube,rollup<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>\u200b \u8fd9\u51e0\u4e2a\u5206\u6790\u51fd\u6570\u901a\u5e38\u7528\u4e8eOLAP\u4e2d\uff0c\u4e0d\u80fd\u7d2f\u52a0\uff0c\u800c\u4e14\u9700\u8981\u6839\u636e\u4e0d\u540c\u7ef4\u5ea6\u4e0a\u94bb\u548c\u4e0b\u94bb\u7684\u6307\u6807\u7edf\u8ba1\uff0c\u6bd4\u5982\uff0c\u5206\u5c0f\u65f6\u3001\u5929\u3001\u6708\u7684UV\u6570\u3002<\/p>\n\n\n\n<ul>\n<li>\u6570\u636e\u51c6\u5907<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>2020-03,2020-03-10,cookie1\n2020-03,2020-03-10,cookie5\n2020-03,2020-03-12,cookie7\n2020-04,2020-04-12,cookie3\n2020-04,2020-04-13,cookie2\n2020-04,2020-04-13,cookie4\n2020-04,2020-04-16,cookie4\n2020-03,2020-03-10,cookie2\n2020-03,2020-03-10,cookie3\n2020-04,2020-04-12,cookie5\n2020-04,2020-04-13,cookie6\n2020-04,2020-04-15,cookie3\n2020-04,2020-04-15,cookie2\n2020-04,2020-04-16,cookie1\n \nCREATE TABLE test_t5 (\nmonth STRING,\nday STRING, \ncookieid STRING \n) ROW FORMAT DELIMITED \nFIELDS TERMINATED BY ',' \nstored as textfile;\n\n\u52a0\u8f7d\u6570\u636e\uff1a\nload data local inpath '\/root\/hivedata\/test_t5.dat' into table test_t5;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<ul>\n<li>GROUPING SETS<\/li>\n<\/ul>\n\n\n\n<p>grouping sets\u662f\u4e00\u79cd\u5c06\u591a\u4e2agroup by \u903b\u8f91\u5199\u5728\u4e00\u4e2asql\u8bed\u53e5\u4e2d\u7684\u4fbf\u5229\u5199\u6cd5\u3002<\/p>\n\n\n\n<p>\u7b49\u4ef7\u4e8e\u5c06\u4e0d\u540c\u7ef4\u5ea6\u7684GROUP BY\u7ed3\u679c\u96c6\u8fdb\u884cUNION ALL\u3002<\/p>\n\n\n\n<p><strong>GROUPING__ID<\/strong>\uff0c\u8868\u793a\u7ed3\u679c\u5c5e\u4e8e\u54ea\u4e00\u4e2a\u5206\u7ec4\u96c6\u5408\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \nmonth,\nday,\nCOUNT(DISTINCT cookieid) AS uv,\nGROUPING__ID \nFROM test_t5 \nGROUP BY month,day \nGROUPING SETS (month,day) \nORDER BY GROUPING__ID;\n\ngrouping_id\u8868\u793a\u8fd9\u4e00\u7ec4\u7ed3\u679c\u5c5e\u4e8e\u54ea\u4e2a\u5206\u7ec4\u96c6\u5408\uff0c\n\u6839\u636egrouping sets\u4e2d\u7684\u5206\u7ec4\u6761\u4ef6month\uff0cday\uff0c1\u662f\u4ee3\u8868month\uff0c2\u662f\u4ee3\u8868day\n\n\u7b49\u4ef7\u4e8e \nSELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM test_t5 GROUP BY month UNION ALL \nSELECT NULL as month,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM test_t5 GROUP BY day;\n<\/code><\/pre>\n\n\n\n<p>\u518d\u5982\uff1a<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \nmonth,\nday,\nCOUNT(DISTINCT cookieid) AS uv,\nGROUPING__ID \nFROM test_t5 \nGROUP BY month,day \nGROUPING SETS (month,day,(month,day)) \nORDER BY GROUPING__ID;\n\n\u7b49\u4ef7\u4e8e\nSELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM test_t5 GROUP BY month \nUNION ALL \nSELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM test_t5 GROUP BY day\nUNION ALL \nSELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM test_t5 GROUP BY month,day;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>CUBE<\/li>\n<\/ul>\n\n\n\n<p>\u6839\u636eGROUP BY\u7684\u7ef4\u5ea6\u7684\u6240\u6709\u7ec4\u5408\u8fdb\u884c\u805a\u5408\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \nmonth,\nday,\nCOUNT(DISTINCT cookieid) AS uv,\nGROUPING__ID \nFROM test_t5 \nGROUP BY month,day \nWITH CUBE \nORDER BY GROUPING__ID;\n\n\u7b49\u4ef7\u4e8e\nSELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM test_t5\nUNION ALL \nSELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM test_t5 GROUP BY month \nUNION ALL \nSELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM test_t5 GROUP BY day\nUNION ALL \nSELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM test_t5 GROUP BY month,day;\n<\/code><\/pre>\n\n\n\n<ul>\n<li>ROLLUP<\/li>\n<\/ul>\n\n\n\n<p>\u662fCUBE\u7684\u5b50\u96c6\uff0c\u4ee5\u6700\u5de6\u4fa7\u7684\u7ef4\u5ea6\u4e3a\u4e3b\uff0c\u4ece\u8be5\u7ef4\u5ea6\u8fdb\u884c\u5c42\u7ea7\u805a\u5408\u3002<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\u6bd4\u5982\uff0c\u4ee5month\u7ef4\u5ea6\u8fdb\u884c\u5c42\u7ea7\u805a\u5408\uff1a\nSELECT \nmonth,\nday,\nCOUNT(DISTINCT cookieid) AS uv,\nGROUPING__ID  \nFROM test_t5 \nGROUP BY month,day\nWITH ROLLUP \nORDER BY GROUPING__ID;\n\n--\u628amonth\u548cday\u8c03\u6362\u987a\u5e8f\uff0c\u5219\u4ee5day\u7ef4\u5ea6\u8fdb\u884c\u5c42\u7ea7\u805a\u5408\uff1a\n \nSELECT \nday,\nmonth,\nCOUNT(DISTINCT cookieid) AS uv,\nGROUPING__ID  \nFROM test_t5 \nGROUP BY day,month \nWITH ROLLUP \nORDER BY GROUPING__ID;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Hive Sql \u5927\u5168 \u672c\u6587\u57fa\u672c\u6db5\u76d6\u4e86Hive\u65e5\u5e38\u4f7f\u7528\u7684\u6240\u6709SQL\uff0c\u56e0\u4e3aSQL\u592a\u591a\uff0c\u6240\u4ee5\u5c06SQL\u8fdb\u884c&hellip; <a href=\"http:\/\/viplao.com\/index.php\/2023\/07\/27\/hive-sql%ef%bc%9a%e5%b7%a5%e4%bd%9c%e4%b8%ad%e5%b8%b8%e7%94%a8hsql%e8%af%ad%e5%8f%a5%e5%a4%a7%e5%85%a8-%e4%b8%80\/\" class=\"more-link read-more\" rel=\"bookmark\">\u7ee7\u7eed\u9605\u8bfb <span class=\"screen-reader-text\">Hive Sql\uff1a\u5de5\u4f5c\u4e2d\u5e38\u7528HSQL\u8bed\u53e5\u5927\u5168 \u4e00<\/span><i class=\"fa fa-arrow-right\"><\/i><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[26],"views":1281,"_links":{"self":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/2212"}],"collection":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/comments?post=2212"}],"version-history":[{"count":1,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/2212\/revisions"}],"predecessor-version":[{"id":2213,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/posts\/2212\/revisions\/2213"}],"wp:attachment":[{"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/media?parent=2212"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/categories?post=2212"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/viplao.com\/index.php\/wp-json\/wp\/v2\/tags?post=2212"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}