{"id":5096,"date":"2021-03-20T21:44:08","date_gmt":"2021-03-20T13:44:08","guid":{"rendered":"https:\/\/ixyzero.com\/blog\/?p=5096"},"modified":"2021-04-01T22:51:03","modified_gmt":"2021-04-01T14:51:03","slug":"readhive-sql%e5%ad%a6%e4%b9%a0%e6%95%b4%e7%90%86_6","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/5096.html","title":{"rendered":"Hive SQL\u5b66\u4e60\u6574\u7406_6"},"content":{"rendered":"\n<p>=Start=<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u7f18\u7531\uff1a<\/h4>\n\n\n\n<p>\u6574\u7406\u4e00\u4e0b\u6700\u8fd1\u5728\u5de5\u4f5c\u4e2d\u7528\u5230\u7684Hive SQL\u7684\u77e5\u8bc6\u70b9\uff0c\u65b9\u4fbf\u4ee5\u540e\u53c2\u8003\u3002<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u6b63\u6587\uff1a<\/h4>\n\n\n\n<h5 class=\"wp-block-heading\">\u53c2\u8003\u89e3\u7b54\uff1a<\/h5>\n\n\n\n<ul class=\"wp-block-list\"><li>\u5728Hive SQL\u4e2d\u8fdb\u884cIP\u5b57\u7b26\u4e32\u548c\u6570\u5b57\u4e4b\u95f4\u7684\u8f6c\u6362<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>-- \u6570\u5b57\u683c\u5f0f\u7684IP\u8f6c\u6210\u70b9\u5206\u5341\u8fdb\u5236\u5b57\u7b26\u4e32\ncreate temporary macro IPint2str (x bigint) concat_ws('.',CONV(substr(hex(x),1,2),16,10),CONV(substr(hex(x),3,2),16,10),CONV(substr(hex(x),5,2),16,10),CONV(substr(hex(x),7,2),16,10));\n\nselect\nconcat_ws('.',\nconv(substr(lpad(conv(3232267034, 10, 2), 32, 0), 1, 8), 2, 10),\nconv(substr(lpad(conv(3232267034, 10, 2), 32, 0), 9, 8), 2, 10),\nconv(substr(lpad(conv(3232267034, 10, 2), 32, 0), 17, 8), 2, 10),\nconv(substr(lpad(conv(3232267034, 10, 2), 32, 0), 25, 8), 2, 10)\n)\n, concat_ws('.',\nCONV(substr(hex(3232267034),1,2),16,10),\nCONV(substr(hex(3232267034),3,2),16,10),\nCONV(substr(hex(3232267034),5,2),16,10),\nCONV(substr(hex(3232267034),7,2),16,10)\n)\n, IPint2str(3232267034)\n;\n-- 192.168.123.26\t192.168.123.26\t192.168.123.26\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<pre class=\"wp-block-code\"><code>-- \u70b9\u5206\u5341\u8fdb\u5236\u683c\u5f0f\u7684IP\u5b57\u7b26\u4e32\u8f6c\u6362\u6210\u6574\u6570\ncreate temporary macro IPstr2int (x string) cast(split(x, '\\\\.') &#91;0] * 256 * 256 * 256 + split(x, '\\\\.') &#91;1] * 256 * 256 + split(x, '\\\\.') &#91;2] * 256 + split(x, '\\\\.') &#91;3] AS bigint);\n\nSELECT\n  cast(\n    split('192.168.123.26', '\\\\.') &#91;0] * 256 * 256 * 256 + split('192.168.123.26', '\\\\.') &#91;1] * 256 * 256 + split('192.168.123.26', '\\\\.') &#91;2] * 256 + split('192.168.123.26', '\\\\.') &#91;3] AS bigint\n  ) AS ip_bigint\n, IPstr2int('192.168.123.26')\n;\n-- 3232267034\t3232267034\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<pre class=\"wp-block-code\"><code># \u5341\u516d\u8fdb\u5236\u51fd\u6570: hex\n\u8bed\u6cd5: hex(BIGINT\/BINARY\/STRING a)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e: \u5982\u679c\u53d8\u91cf\u662fbigint\/binary\u7c7b\u578b\uff0c\u90a3\u4e48\u8fd4\u56dea\u7684\u5341\u516d\u8fdb\u5236\u8868\u793a\uff1b\u5982\u679c\u53d8\u91cf\u662fstring\u7c7b\u578b\uff0c\u5219\u8fd4\u56de\u8be5\u5b57\u7b26\u4e32\u4e2d\u6bcf\u4e2a\u5b57\u7b26\u7684\u5341\u516d\u8fdb\u5236\u8868\u793a\n\nhive> select hex(17);\n11\n\nhive> select hex('abc');\n616263\n\nIf the argument is an INT or binary, hex returns the number as a STRING in hexadecimal format. Otherwise if the number is a STRING, it converts each character into its hexadecimal representation and returns the resulting STRING. (BINARY version as of Hive 0.12.0.)\n\n\n# \u8fdb\u5236\u8f6c\u6362\u51fd\u6570: conv\n\u8bed\u6cd5: conv(BIGINT\/STRING num, int from_base, int to_base)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e: \u5c06\u6570\u503cnum\u4ecefrom_base\u8fdb\u5236\u8f6c\u5316\u5230to_base\u8fdb\u5236\n\nhive> select conv(17,10,16) from tableName;\n11\n\nhive> select conv(17,10,2) from tableName;\n10001\n\nconv(BIGINT num, INT from_base, INT to_base),\nconv(STRING num, INT from_base, INT to_base)\n\nConverts a number from a given base to another (see http:\/\/dev.mysql.com\/doc\/refman\/5.0\/en\/mathematical-functions.html#function_conv).\n\n\n# \u5de6\u8865\u8db3\u51fd\u6570: lpad\n\u8bed\u6cd5: lpad(string str, int len, string pad)\n\u8fd4\u56de\u503c: string\n\u8bf4\u660e\uff1a\u5c06 str \u8fdb\u884c\u7528 pad \u8fdb\u884c\u5de6\u8865\u8db3\u5230 len \u4f4d\n\nhive> select lpad('abc',10,'td');\ntdtdtdtabc\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\"><li>\u5728Hive\u4e2d\u7528SQL\u751f\u6210\u65e5\u671f\u5e8f\u5217<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>-- \u901a\u7528\u65b9\u6cd5\nwith t as (\n  select '2020-12-28' as dt_start, '2021-01-12' as dt_end\n)\nselect\nidx, date_add(to_date(dt_start), idx) as dt\nfrom t\nlateral view posexplode(split(space(datediff(t.dt_end,t.dt_start)),' ')) pi as idx, empty\n;\n\n\/*\n\u601d\u8def\u89e3\u8bfb\uff1a\n\u5148\u7528 datediff \u51fd\u6570\u8ba1\u7b97\u4e24\u4e2a\u65e5\u671f\u4e4b\u95f4\u7684\u5929\u6570\uff08\u7b2c\u4e8c\u4e2a\u53c2\u6570\u5e94\u8be5\u6bd4\u7b2c\u4e00\u4e2a\u53c2\u6570\u8981\u66f4\u300c\u5927\u300d\uff0c\u5426\u5219\u8fd4\u56de\u7684\u4f1a\u662f\u8d1f\u6570\uff09\uff1b\n\u7136\u540e\u7528 space \u51fd\u6570\u628a\u5929\u6570\u8fd9\u4e2a\u6570\u503c\uff08\u5728\u6b64\u5047\u8bbe\u4e3an\uff09\u8f6c\u6362\u6210 n \u4e2a\u7a7a\u683c\uff1b\n\u518d\u7528 split \u51fd\u6570\u5bf9\u7a7a\u683c\u8fdb\u884c\u5207\u5206\uff0c\u8f6c\u6362\u6210\u4e00\u4e2aarray\u6570\u7ec4\uff1b\n\u518d\u7528 lateral view posexplode \u5bf9\u4e0a\u4e00\u6b65\u9aa4\u4ea7\u751f\u7684\u6570\u7ec4\u62c6\u5206\u6210\u591a\u884c\uff08\u7d22\u5f15\u548c\u5185\u5bb9\u7684\u683c\u5f0f\uff0c\u4f46\u6b64\u5904\u7684\u5185\u5bb9\u4e3a\u7a7a\u4e32\uff09\uff1b\n\u6700\u540e\u5728 select \u7684\u65f6\u5019\u7528 date_add \u51fd\u6570\u5728\u6700\u521d\u7684\u57fa\u7840\u65e5\u671f\u4e0a\u5faa\u73af\u4e0d\u65ad\u589e\u52a0 \u7d22\u5f15\u7684\u503c \u4ece\u800c\u4ea7\u751f\u65b0\u7684\u8fde\u7eed\u65e5\u671f\u3002\n\ndatediff(date1, date2) - Returns the number of days between date1 and date2\ndate1 and date2 are strings in the format 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'. The time parts are ignored.If date1 is earlier than date2, the result is negative.\n\nspace(n) - returns n spaces\n\nsplit(str, regex) - Splits str around occurances that match regex\nFunction class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFSplit\n\nposexplode(a) - behaves like explode for arrays, but includes the position of items in the original array\nFunction class:org.apache.hadoop.hive.ql.udf.generic.GenericUDTFPosExplode\nFunction type:BUILTIN\n\n\nidx\tdt\n0\t2020-12-28\n1\t2020-12-29\n2\t2020-12-30\n3\t2020-12-31\n4\t2021-01-01\n...\n15\t2021-01-12\n*\/\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>-- \u6ce8\uff1a\u5728Hive SQL\u4e2d\uff0c\u51fd\u6570\u63a5\u6536\u65e5\u671f\u7684\u683c\u5f0f\u9ed8\u8ba4\u4e3a 'yyyy-mm-dd' \u7684\uff0c\u800c\u975e 'yyyymmdd' \u8981\u6ce8\u610f\uff01\n\nSELECT\ndefault.date_range('20201228','20210112',1)\n;\n\n\/*\ndate\tindex\n20201228\t0\n20201229\t1\n20201230\t2\n20201231\t3\n20210101\t4\n...\n20210112\t15\n*\/\n\ndesc function extended default.date_range;\n\/*\ndefault.date_range(a,b,c) - Generates a range of integers from a to b incremented by c or the elements of a map into multiple rows and columns\nFunction class:brickhouse.udf.date.DateRangeUDTF\nFunction type:PERSISTENT\nResource:viewfs:\/\/hadoop-lt-cluster\/home\/system\/hive\/resources\/brickhouse-0.7.1-SNAPSHOT-jar-with-dependencies.jar\n*\/\n<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">\u53c2\u8003\u94fe\u63a5\uff1a<\/h5>\n\n\n\n<p>Hive Sql\u4e2dIP\u5730\u5740\u548c\u6570\u5b57\u4e4b\u95f4\u7684\u8f6c\u6362<br><a href=\"https:\/\/blog.csdn.net\/weixin_44034508\/article\/details\/105813769\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/blog.csdn.net\/weixin_44034508\/article\/details\/105813769<\/a><\/p>\n\n\n\n<p>hive \u6570\u5b57IP\u4e0e\u5b57\u7b26\u4e32IP\u4e4b\u95f4\u8f6c\u6362<br><a href=\"https:\/\/blog.csdn.net\/cxy1991xm\/article\/details\/102505150\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/blog.csdn.net\/cxy1991xm\/article\/details\/102505150<\/a><\/p>\n\n\n\n<p>Hive SQL\u4e2dIP\u5730\u5740\u4e0e\u6570\u5b57\u4e4b\u95f4\u7684\u8f6c\u6362<br><a href=\"https:\/\/www.vzhima.com\/2020\/06\/24\/hive-sql-convert-ip-address-to-integer.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.vzhima.com\/2020\/06\/24\/hive-sql-convert-ip-address-to-integer.html<\/a><\/p>\n\n\n\n<p>How to generate Date Series in HIVE? (Creating table)<br><a rel=\"noreferrer noopener\" href=\"https:\/\/stackoverflow.com\/questions\/45278300\/how-to-generate-date-series-in-hive-creating-table\/45279528\" target=\"_blank\">https:\/\/stackoverflow.com\/questions\/45278300\/how-to-generate-date-series-in-hive-creating-table\/45279528<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/www.gairuo.com\/p\/hive-sql-mathematical-functions\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.gairuo.com\/p\/hive-sql-mathematical-functions<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/string-functions.html#function_hex\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/string-functions.html#function_hex<\/a><br><a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/mathematical-functions.html#function_conv\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/mathematical-functions.html#function_conv<\/a><\/p>\n\n\n\n<p>=END=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>=Start= \u7f18\u7531\uff1a \u6574\u7406\u4e00\u4e0b\u6700\u8fd1\u5728\u5de5\u4f5c\u4e2d\u7528\u5230\u7684Hive SQL\u7684\u77e5\u8bc6\u70b9\uff0c\u65b9\u4fbf\u4ee5\u540e\u53c2\u8003\u3002 \u6b63\u6587\uff1a \u53c2\u8003\u89e3\u7b54\uff1a [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,7,12],"tags":[1722,1724,1721,1261,882,1723,1262],"class_list":["post-5096","post","type-post","status-publish","format-standard","hentry","category-database","category-programing","category-tools","tag-conv","tag-datediff","tag-hex","tag-hive","tag-macro","tag-posexplode","tag-sql"],"views":4787,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5096","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=5096"}],"version-history":[{"count":2,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5096\/revisions"}],"predecessor-version":[{"id":5098,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5096\/revisions\/5098"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=5096"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=5096"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=5096"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}