=Start=<\/p>\n\n\n\n
\u7ee7\u7eed\u6574\u7406\u6700\u8fd1\u5b66\u5230\u6216\u662f\u7528\u5230\u7684Hive SQL\u77e5\u8bc6\uff0c\u65b9\u4fbf\u4ee5\u540e\u53c2\u8003\u3002<\/p>\n\n\n\n
IF( Test Condition, True Value, False Value )\n\/\/ \u5982\u679c\u300cTest Condition\u300d\u4e3a\u771f\uff0c\u5219\u53d6\u300cTrue Value\u300d\u7684\u503c\uff0c\u5426\u5219\u53d6\u300cFalse Value\u300d\u7684\u503c\nselect IF(1=1,'TRUE','FALSE') as IF_CONDITION_TEST;\n-- TRUE\n\nCOALESCE( value1,value2,\u2026 )\n\/\/ \u83b7\u53d6\u53c2\u6570\u5217\u8868\u4e2d\u7684\u9996\u4e2a\u975e\u7a7a\u503c\uff0c\u82e5\u5747\u4e3aNULL\uff0c\u5219\u8fd4\u56deNULL\nselect coalesce(null,'a',null,'b');\n-- a<\/code><\/pre>\n\n\n\nCASE Statement\n\/\/ \u4e0e\u5176\u4ed6\u8bed\u8a00\u4e2dcase\u8bed\u6cd5\u76f8\u4f3c\uff0c\u6839\u636e\u5b9e\u9645\u60c5\u51b5\u53d6\u5b9e\u9645\u7684\u503c\uff0c\u9002\u7528\u4e8e\u591a\u6761\u4ef6\u6bd4\u8f83\u7684\u60c5\u51b5<\/code>\nselect case x\u00a0\n when 1 then 'one'\n when 2 then 'two'\n when 0 then 'zero'\n else 'out of range'\nend\nfrom\n(select 3 as x\n)t\n;\n-- out of range<\/code><\/pre>\n\n\n\n2\u3001Hive\u4e2d\u5982\u4f55\u83b7\u53d6\u4eca\u5929\u7684\u65e5\u671f\uff1f\u662f\u661f\u671f\u51e0\uff1f\u662f\u4e00\u5e74\u4e2d\u7684\u7b2c\u51e0\u5468\uff1f<\/h6>\n\n\n\nselect current_timestamp,\nfrom_unixtime(unix_timestamp()) as `from_unixtime(unix_timestamp())`,\n\ncurrent_date,\nto_date(from_unixtime(unix_timestamp())) as `to_date(from_unixtime(unix_timestamp()))`\n;\n-- 2019-05-21 20:30:05.591 2019-05-21 20:30:06 2019-05-21 2019-05-21\n-- \u5373\uff0c\u4e00\u822c\u60c5\u51b5\u4e0b\u4f7f\u7528 current_timestamp \u548c current_date \u5373\u53ef<\/code><\/pre>\n\n\n\nSELECT current_date AS `date`,\n\u00a0 \u00a0 \u00a0 \u00a0CASE date_format(current_date,'u')\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0WHEN 1 THEN 'Mon'\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0WHEN 2 THEN 'Tues'\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0WHEN 3 THEN 'Wed'\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0WHEN 4 THEN 'Thu'\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0WHEN 5 THEN 'Fri'\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0WHEN 6 THEN 'Sat'\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0WHEN 7 THEN 'Sun'\nEND AS day_of_week\n;\n-- 2019-05-21 Tues<\/code><\/pre>\n\n\n\n&<\/p>\n\n\n\n
select weekofyear(current_timestamp); -- 21<\/code><\/pre>\n\n\n\n3\u3001Hive\u4e2d\u7684\u6570\u636e\u7c7b\u578b\u8f6c\u6362<\/h6>\n\n\n\n
\u540cJava\u8bed\u8a00\u4e00\u6837\uff0cHive\u4e5f\u5305\u62ec\u9690\u5f0f\u8f6c\u6362\uff08implicit conversions\uff09\u548c\u663e\u5f0f\u8f6c\u6362\uff08explicitly conversions\uff09\u3002<\/p>\n\n\n\n
\u4efb\u4f55\u6574\u6570\u7c7b\u578b\u90fd\u53ef\u4ee5\u9690\u5f0f\u5730\u8f6c\u6362\u6210\u4e00\u4e2a\u8303\u56f4\u66f4\u5927\u7684\u7c7b\u578b\u3002TINYINT,SMALLINT,INT,BIGINT,FLOAT\u548cSTRING\u90fd\u53ef\u4ee5\u9690\u5f0f\u5730\u8f6c\u6362\u6210DOUBLE\uff1b\u662f\u7684\u4f60\u6ca1\u770b\u9519\uff0cSTRING\u4e5f\u53ef\u4ee5\u9690\u5f0f\u5730\u8f6c\u6362\u6210DOUBLE\uff01\u4f46\u662f\u4f60\u8981\u8bb0\u4f4f\uff0cBOOLEAN\u7c7b\u578b\u4e0d\u80fd\u8f6c\u6362\u4e3a\u5176\u4ed6\u4efb\u4f55\u6570\u636e\u7c7b\u578b\uff01<\/p>\n\n\n\n
CAST\u7684\u8bed\u6cd5\u4e3a\uff1a<\/strong><\/p>\n\n\n\ncast(value AS TYPE)<\/code><\/pre>\n\n\n\n\u5982\u679c\u8f6c\u6362\u5931\u8d25\uff0c\u7ed3\u679c\u5219\u4f1a\u8fd4\u56deNULL\u3002<\/strong><\/p>\n\n\n\n4\u3001Hive\u4e2d\u5982\u4f55\u83b7\u53d6\u672c\u5468\u3001\u672c\u6708\u7684\u7b2c\u4e00\u5929\uff1f<\/strong><\/h6>\n\n\n\n\u65b9\u6cd5\u4e00\uff1a\u5199 UDF \u5b9e\u73b0\u4e00\u4e2a function \u4f9b\u8c03\u7528\uff0c\u8fd9\u79cd\u6700\u7b80\u5355\u76f4\u63a5\u5feb\u901f
\u65b9\u6cd5\u4e8c\uff1a\u7528 SQL \u8fdb\u884c\u65e5\u671f\u51fd\u6570\u7684\u589e\u51cf
\u5728Hive SQL\u4e2d\u5982\u4f55\u83b7\u53d6\u672c\u6708\u7684\u7b2c\u4e00\u5929\uff1f(date_format\u51fd\u6570\u7684\u7b2c\u4e8c\u4e2a\u53c2\u6570\u4e3a’d’\u8868\u793aDay in month\uff0c\u5373\u5f53\u6708\u7684\u7b2c\u51e0\u5929<\/strong>)
hive> select date_sub(current_date, cast(date_format(current_date,'d') as INT)) as month_first;<\/code>
\u5728Hive SQL\u4e2d\u5982\u4f55\u83b7\u53d6\u672c\u5468\u7684\u7b2c\u4e00\u5929\uff1f(date_format\u51fd\u6570\u7684\u7b2c\u4e8c\u4e2a\u53c2\u6570\u4e3a’u’\u8868\u793aDay number of week\uff0c\u5373\u5f53\u5468\u7684\u7b2c\u51e0\u5929<\/strong>)
hive> select date_sub(current_date, cast(date_format(current_date,'u') as INT)) as week_first;<\/code><\/p>\n\n\n\n5\u3001Hive\u4e2d\u5e38\u7528\u7684\u6b63\u5219\u8868\u8fbe\u5f0f<\/h6>\n\n\n\n
1. \u6821\u9a8c\u5bc6\u7801\u5f3a\u5ea6
\u5bc6\u7801\u7684\u5f3a\u5ea6\u5fc5\u987b\u662f\u5305\u542b\u5927\u5c0f\u5199\u5b57\u6bcd\u548c\u6570\u5b57\u7684\u7ec4\u5408\uff0c\u4e0d\u80fd\u4f7f\u7528\u7279\u6b8a\u5b57\u7b26\uff0c\u957f\u5ea6\u57288-10\u4e4b\u95f4\u3002<\/p>\n\n\n\n
^(?=.*\\\\d)(?=.*[a-z])(?=.*[A-Z]).{8,10}$<\/code><\/pre>\n\n\n\n2. \u6821\u9a8c\u4e2d\u6587<\/strong>
\u5b57\u7b26\u4e32\u4ec5\u80fd\u662f\u4e2d\u6587\u3002<\/p>\n\n\n\n^[\\\\u4e00-\\\\u9fa5]{0,}$<\/code><\/pre>\n\n\n\n3. \u7531\u6570\u5b57\u300126\u4e2a\u82f1\u6587\u5b57\u6bcd\u6216\u4e0b\u5212\u7ebf\u7ec4\u6210\u7684\u5b57\u7b26\u4e32<\/p>\n\n\n\n
^\\\\w+$<\/code><\/pre>\n\n\n\n4. \u6821\u9a8cE-Mail \u5730\u5740
\u540c\u5bc6\u7801\u4e00\u6837\uff0c\u4e0b\u9762\u662fE-mail\u5730\u5740\u5408\u89c4\u6027\u7684\u6b63\u5219\u68c0\u67e5\u8bed\u53e5\u3002<\/p>\n\n\n\n
[\\\\w!#$%&'*+\/=?^_`{|}~-]+(?:\\\\.[\\\\w!#$%&'*+\/=?^_`{|}~-]+)*@(?:[\\\\w](?:[\\\\w-]*[\\\\w])?\\\\.)<\/code><\/pre>\n\n\n\n5. \u6821\u9a8c\u8eab\u4efd\u8bc1\u53f7\u7801<\/strong><\/p>\n\n\n\n\u4e0b\u9762\u662f\u8eab\u4efd\u8bc1\u53f7\u7801\u7684\u6b63\u5219\u6821\u9a8c\u300215 \u6216 18\u4f4d\u3002\uff08\u6839\u636e\u5b9e\u9645\u60c5\u51b5\u6765\u770b\uff0c\u7b26\u5408\u8eab\u4efd\u8bc1\u53f7\u7801\u683c\u5f0f\u7684\u53ef\u80fd\u4f1a\u5f88\u591a\uff0c\u4f46\u662f\uff0c\u80fd\u901a\u8fc7\u8eab\u4efd\u8bc1\u683c\u5f0f\u6821\u9a8c\u7684\u5c31\u5f88\u5c11\uff0c\u6240\u4ee5\u8fd8\u9700\u8981\u501f\u52a9\u4e0b\u9762\u7684\u8eab\u4efd\u8bc1\u683c\u5f0f\u6821\u9a8c\u529f\u80fd\u8fdb\u884c\u8fdb\u4e00\u6b65\u7684\u9a8c\u8bc1\u624d\u884c\uff09<\/p>\n\n\n\n
15\u4f4d\uff1a<\/p>\n\n\n\n
^[1-9]\\\\d{7}((0\\\\d)|(1[0-2]))(([0|1|2]\\\\d)|3[0-1])\\\\d{3}$<\/code><\/pre>\n\n\n\n18\u4f4d\uff1a<\/p>\n\n\n\n
^[1-9]\\\\d{5}[1-9]\\\\d{3}((0\\\\d)|(1[0-2]))(([0|1|2]\\\\d)|3[0-1])\\\\d{3}([0-9]|X)$<\/code><\/pre>\n\n\n\n6. \u6821\u9a8c\u65e5\u671f
\u201cyyyy-mm-dd\u201c \u683c\u5f0f\u7684\u65e5\u671f\u6821\u9a8c\uff0c\u5df2\u8003\u8651\u5e73\u95f0\u5e74\u3002<\/p>\n\n\n\n
^(?:(?!0000)[0-9]{4}-(?:(?:0[1-9]|1[0-2])-(?:0[1-9]|1[0-9]|2[0-8])|(?:0[13-9]|1[0-2])<\/code><\/pre>\n\n\n\n7. \u6821\u9a8c\u624b\u673a\u53f7<\/strong>
\u4e0b\u9762\u662f\u56fd\u5185 13\u300115\u300118\u5f00\u5934\u7684\u624b\u673a\u53f7\u6b63\u5219\u8868\u8fbe\u5f0f\u3002<\/p>\n\n\n\n^(13[0-9]|14[5|7]|15[0|1|2|3|5|6|7|8|9]|18[0|1|2|3|5|6|7|8|9])\\\\d{8}$<\/code><\/pre>\n\n\n\n6\u3001Hive\u4e2d\u5982\u4f55\u8fdb\u884c\u8eab\u4efd\u8bc1\u5b57\u7b26\u4e32\u683c\u5f0f\u7684\u5408\u6cd5\u6027\u6821\u9a8c<\/h6>\n\n\n\n-- Hive 18\u4f4d\u8eab\u4efd\u8bc1\u53f7\u7801\u9a8c\u8bc1\uff08\u9700\u8981\u7528\u300c\u4e0d\u7b26\u5408\u89c4\u8303\u300d\u7684\u8eab\u4efd\u8bc1\u53f7\u7801\u8fdb\u884c\u6d4b\u8bd5\u624d\u4f1a\u6709\u7ed3\u679c\uff0c\u6b63\u786e\u7684\u53f7\u7801\u67e5\u8be2\u7ed3\u679c\u4e3a\u7a7a\uff09\n\nselect * from\n(select trim(upper('440102198001021231')) idcard) t1\nwhere\n-- \u53f7\u7801\u4f4d\u6570\u4e0d\u6b63\u786e\nlength(idcard) <> 18\u00a0\n\n-- \u7701\u4efd\u4ee3\u7801\u4e0d\u6b63\u786e\nor substr(idcard,1,2) not in \n('11','12','13','14','15','21','22','23','31',\n'32','33','34','35','36','37','41','42','43',\n'44','45','46','50','51','52','53','54','61',\n'62','63','64','65','71','81','82','91')\u00a0\n\n-- \u8eab\u4efd\u8bc1\u53f7\u7801\u7684\u6b63\u5219\u8868\u8fbe\u5f0f\u5224\u65ad\nor (if(pmod(cast(substr(idcard, 7, 4) as int),400) = 0 or (pmod(cast(substr(idcard, 7, 4) as int),100) <> 0 and pmod(cast(substr(idcard, 7, 4) as int),4) = 0), -- \u95f0\u5e74\nif(idcard regexp '^[1-9][0-9]{5}19[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}[0-9X]$',1,0),\nif(idcard regexp '^[1-9][0-9]{5}19[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}[0-9X]$',1,0)\n)) = 0\n\n-- \u6821\u9a8c\u4f4d\u4e0d\u6b63\u786e\nor substr('10X98765432',pmod(\n(cast(substr(idcard,1,1) as int)+cast(substr(idcard,11,1) as int))*7\n+(cast(substr(idcard,2,1) as int)+cast(substr(idcard,12,1) as int))*9\n+(cast(substr(idcard,3,1) as int)+cast(substr(idcard,13,1) as int))*10\n+(cast(substr(idcard,4,1) as int)+cast(substr(idcard,14,1) as int))*5\n+(cast(substr(idcard,5,1) as int)+cast(substr(idcard,15,1) as int))*8\n+(cast(substr(idcard,6,1) as int)+cast(substr(idcard,16,1) as int))*4\n+(cast(substr(idcard,7,1) as int)+cast(substr(idcard,17,1) as int))*2\n+cast(substr(idcard, 8,1) as int)*1\n+cast(substr(idcard, 9,1) as int)*6\n+cast(substr(idcard,10,1) as int)*3,11)+1,1)\u00a0\n<> cast(substr(idcard,18,1) as int)\n;<\/code><\/pre>\n\n\n\n\u53c2\u8003\u94fe\u63a5\uff1a<\/h5>\n\n\n\n- Hive\u6761\u4ef6\u5224\u65ad<\/strong>
https:\/\/blog.csdn.net\/u012378570\/article\/details\/62216722<\/a><\/li>- What’s the best way to write if\/else if\/else if\/else in HIVE?
https:\/\/stackoverflow.com\/questions\/32472801\/whats-the-best-way-to-write-if-else-if-else-if-else-in-hive<\/a><\/li>- Hadoop Hive Conditional Functions: IF,CASE,COALESCE,NVL,DECODE
http:\/\/dwgeek.com\/hadoop-hive-conditional-functions-if-case-coalesce-nvl-decode.html\/<\/a><\/li>- =<\/li>
- Hive\u8fdb\u884c\u8eab\u4efd\u8bc1\u5408\u6cd5\u6027\u6821\u9a8c<\/strong>
https:\/\/blog.csdn.net\/wzy0623\/article\/details\/53893238<\/a><\/li>- \u5e38\u7528\u7684\u6b63\u5219\u8868\u8fbe\u5f0f<\/strong>
http:\/\/lxw1234.com\/archives\/2016\/04\/640.htm<\/a><\/li>- hive\u6b63\u5219<\/strong>
https:\/\/blog.csdn.net\/changzoe\/article\/details\/80251700<\/a><\/li>- Hive \u6b63\u5219\u63d0\u53d6\u82f1\u6587\u540d\u79f0\u548c\u4e2d\u6587\u540d\u79f0
https:\/\/cloud.tencent.com\/developer\/article\/1403321<\/a><\/li>- =<\/li>
- How to select current date in Hive SQL<\/strong>
https:\/\/stackoverflow.com\/questions\/17905873\/how-to-select-current-date-in-hive-sql<\/a><\/li>- Hive date function to achieve day of week<\/strong>
https:\/\/stackoverflow.com\/questions\/22982904\/hive-date-function-to-achieve-day-of-week\/55320077#55320077<\/a><\/li>- Hive\u548csparksql\u4e2d\u7684dayofweek
https:\/\/blog.csdn.net\/hjw199089\/article\/details\/79526362<\/a><\/li>- =<\/li>
- Hive\u6570\u636e\u7c7b\u578b\u8f6c\u6362<\/strong>
https:\/\/www.iteblog.com\/archives\/892.html<\/a><\/li>- =<\/li>
- Hive\u5e38\u7528\u51fd\u6570\u5927\u5168\u4e00\u89c8<\/strong>
https:\/\/www.iteblog.com\/archives\/2258.html#i-5<\/a><\/li>- Apache Hive \u5185\u7f6e\u51fd\u6570(Builtin Function)\u5217\u8868<\/strong>
https:\/\/www.iteblog.com\/archives\/2032.html#date_format<\/a><\/li><\/ul>\n\n\n\n=END=<\/p>\n","protected":false},"excerpt":{"rendered":"
=Start= \u7f18\u7531\uff1a \u7ee7\u7eed\u6574\u7406\u6700\u8fd1\u5b66\u5230\u6216\u662f\u7528\u5230\u7684Hive SQL\u77e5\u8bc6\uff0c\u65b9\u4fbf\u4ee5\u540e\u53c2\u8003\u3002 \u6b63\u6587\uff1a \u53c2\u8003\u89e3\u7b54\uff1a 1 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,23,7],"tags":[1284,1413,1411,1412,690,1261,1262,1044,1414],"class_list":["post-4453","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","category-programing","tag-case","tag-cast","tag-current_date","tag-current_timestamp","tag-date_format","tag-hive","tag-sql","tag-1044","tag-1414"],"views":10233,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/4453","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=4453"}],"version-history":[{"count":3,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/4453\/revisions"}],"predecessor-version":[{"id":4485,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/4453\/revisions\/4485"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=4453"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=4453"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=4453"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}