1. 内置运算符和函数
hive 内置一些函数，
咱们能够通过 show functions 来查看
0: jdbc:hive2://hadoop10:10000> show functions;
+------------------------------+
| tab_name |
+------------------------------+
| ! |
| != |
......
| udftoshort |
| udftostring |
| unbase64 |
| xpath_number |
| xpath_short |
| xpath_string |
| year |
| | |
| ~ |
+------------------------------+
貌似很多的样子，那么怎么晓得这个函数怎么用呢？
— 查看函数帮忙
这么多函数也不能总是查手册，有没有便捷的形式，给咱们提供一些应用帮忙呢？当然有
显示函数帮忙的办法
0: jdbc:hive2://hadoop10:10000> desc function abs;
+-------------------------------------------+
| tab_name |
+-------------------------------------------+
| abs(x) - returns the absolute value of x |
+-------------------------------------------+
貌似太少了，有没有更具体的呢？那看上面的
具体显示函数的用法
0: jdbc:hive2://hadoop10:10000> desc function extended abs;
+----------------------------------------------------+
| tab_name |
+----------------------------------------------------+
| abs(x) - returns the absolute value of x |
| Example: |
| > SELECT abs(0) FROM src LIMIT 1; |
| 0 |
| > SELECT abs(-5) FROM src LIMIT 1; |
| 5 |
| Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFAbs |
| Function type:BUILTIN |
+----------------------------------------------------+
这个就更具体了，还有应用的示例，很是不便。
下边对内置运算符和函数归了下来，能够作为参考
我大抵是一目十行扫一眼，有个印象即可。
1.1 内置运算符
1.1.1 关系运算符
| 运算符 | 类型 | 阐明 |
|---|---|---|
| A = B | 所有原始类型 | 如果 A 与 B 相等, 返回 TRUE, 否则返回 FALSE |
| A == B | 无 | 失败，因为有效的语法。SQL 应用”=”，不应用”==”。 |
| A <> B | 所有原始类型 | 如果 A 不等于 B 返回 TRUE, 否则返回 FALSE。如果 A 或 B 值为”NULL”，后果返回”NULL”。 |
| A < B | 所有原始类型 | 如果 A 小于 B 返回 TRUE, 否则返回 FALSE。如果 A 或 B 值为”NULL”，后果返回”NULL”。 |
| A <= B | 所有原始类型 | 如果 A 小于等于 B 返回 TRUE, 否则返回 FALSE。如果 A 或 B 值为”NULL”，后果返回”NULL”。 |
| A > B | 所有原始类型 | 如果 A 大于 B 返回 TRUE, 否则返回 FALSE。如果 A 或 B 值为”NULL”，后果返回”NULL”。 |
| A >= B | 所有原始类型 | 如果 A 大于等于 B 返回 TRUE, 否则返回 FALSE。如果 A 或 B 值为”NULL”，后果返回”NULL”。 |
| A IS NULL | 所有类型 | 如果 A 值为”NULL”，返回 TRUE, 否则返回 FALSE |
| A IS NOT NULL | 所有类型 | 如果 A 值不为”NULL”，返回 TRUE, 否则返回 FALSE |
| A LIKE B | 字符串 | 如果 A 或 B 值为”NULL”，后果返回”NULL”。字符串 A 与 B 通过 sql 进行匹配，如果相符返回 TRUE，不符返回 FALSE。B 字符串中的”_”代表任一字符，”%”则代表多个任意字符。例如：(‘foobar’like‘foo’)返回 FALSE，（‘foobar’like‘foo_ _ _’或者‘foobar’like‘foo%’)则返回 TURE |
| A RLIKE B | 字符串 | 如果 A 或 B 值为”NULL”，后果返回”NULL”。字符串 A 与 B 通过 java 进行匹配，如果相符返回 TRUE，不符返回 FALSE。例如：（‘foobar’rlike‘foo’）返回 FALSE，（’foobar’rlike‘^f.*r$’）返回 TRUE。 |
| A REGEXP B | 字符串 | 与 RLIKE 雷同。 |
1.1.2 算术运算符
| 运算符 | 类型 | 阐明 | |
|---|---|---|---|
| A + B | 所有数字类型 | A 和 B 相加。后果的与操作数值有独特类型。例如每一个整数是一个浮点数，浮点数蕴含整数。所以，一个浮点数和一个整数相加后果也是一个浮点数。 | |
| A – B | 所有数字类型 | A 和 B 相减。后果的与操作数值有独特类型。 | |
| A * B | 所有数字类型 | A 和 B 相乘，后果的与操作数值有独特类型。须要阐明的是，如果乘法造成溢出，将抉择更高的类型。 | |
| A / B | 所有数字类型 | A 和 B 相除，后果是一个 double（双精度）类型的后果。 | |
| A % B | 所有数字类型 | A 除以 B 余数与操作数值有独特类型。 | |
| A & B | 所有数字类型 | 运算符查看两个参数的二进制表示法的值，并执行按位”与”操作。两个表达式的一位均为 1 时，则后果的该位为 1。否则，后果的该位为 0。 | |
| A | B | 所有数字类型 | 运算符查看两个参数的二进制表示法的值，并执行按位”或”操作。只有任一表达式的一位为 1，则后果的该位为 1。否则，后果的该位为 0。 |
| A ^ B | 所有数字类型 | 运算符查看两个参数的二进制表示法的值，并执行按位”异或”操作。当且仅当只有一个表达式的某位上为 1 时，后果的该位才为 1。否则后果的该位为 0。 | |
| ~A | 所有数字类型 | 对一个表达式执行按位”非”（取反）。 |
1.1.3 逻辑运算符
| 运算符 | 类型 | 阐明 | |
|---|---|---|---|
| A AND B | 布尔值 | A 和 B 同时正确时, 返回 TRUE, 否则 FALSE。如果 A 或 B 值为 NULL，返回 NULL。 | |
| A && B | 布尔值 | 与”A AND B”雷同 | |
| A OR B | 布尔值 | A 或 B 正确, 或两者同时正确返返回 TRUE, 否则 FALSE。如果 A 和 B 值同时为 NULL，返回 NULL。 | |
| A | B | 布尔值 | 与”A OR B”雷同 |
| NOT A | 布尔值 | 如果 A 为 NULL 或谬误的时候返回 TURE，否则返回 FALSE。 | |
| ! A | 布尔值 | 与”NOT A”雷同 |
1.1.4 map、struct、array 简单类型
| 函数 | 类型 | 阐明 |
|---|---|---|
| map | (key1, value1, key2, value2, …) | 通过指定的键 / 值对，创立一个 map。 |
| struct | (val1, val2, val3, …) | 通过指定的字段值，创立一个构造。构造字段名称将 COL1，COL2，… |
| array | (val1, val2, …) | 通过指定的元素，创立一个数组。 |
1.1.5 对简单类型函数的操作
| 函数 | 类型 | 阐明 |
|---|---|---|
| A[n] | A 是一个数组，n 为 int 型 | 返回数组 A 的第 n 个元素，第一个元素的索引为 0。如果 A 数组为 [‘foo’,’bar’]，则 A[0] 返回’foo’和 A[1]返回”bar”。 |
| M[key] | M 是 Map，要害 K 型 | 返回要害值对应的值，例如 mapM 为 {‘f’->‘foo’,‘b’->‘bar’,‘all’->‘foobar’}，则 M[‘all’] 返回’foobar’。 |
| S.x | S 为 struct | 返回构造 x 字符串在构造 S 中的存储地位。如 foobar {int foo, int bar} foobar.foo 的畛域中存储的整数。 |
1.2 内置函数
接下来就是内置函数的分类
1.2.1 数学函数
| 返回类型 | 函数 | 阐明 |
|---|---|---|
| BIGINT | round(double a) | 四舍五入 |
| DOUBLE | round(double a, int d) | 小数局部 d 位之后数字四舍五入，例如 round(21.263,2), 返回 21.26 |
| BIGINT | floor(double a) | 对给定数据进行向下舍入最靠近的整数。例如 floor(21.2), 返回 21。 |
| BIGINT | ceil(double a), ceiling(double a) | 将参数向上舍入为最靠近的整数。例如 ceil(21.2), 返回 23. |
| double | rand(), rand(int seed) | 返回大于或等于 0 且小于 1 的均匀散布随机数（依从新计算而变） |
| double | exp(double a) | 返回 e 的 n 次方 |
| double | ln(double a) | 返回给定数值的自然对数 |
| double | log10(double a) | 返回给定数值的以 10 为底自然对数 |
| double | log2(double a) | 返回给定数值的以 2 为底自然对数 |
| double | log(double base, double a) | 返回给定底数及指数返回自然对数 |
| double | pow(double a, double p) power(double a, double p) | 返回某数的乘幂 |
| double | sqrt(double a) | 返回数值的平方根 |
| string | bin(BIGINT a) | 返回二进制格局，参考：http://dev.mysql.com/doc/refm… |
| string | hex(BIGINT a) hex(string a) | 将整数或字符转换为十六进制格局。参考：http://dev.mysql.com/doc/refm… |
| string | unhex(string a) | 十六进制字符转换由数字示意的字符。 |
| string | conv(BIGINT num, int from_base, int to_base) | 将指定数值，由原来的度量体系转换为指定的试题体系。例如 CONV(‘a’,16,2), 返回。参考：’1010′ http://dev.mysql.com/doc/refm… |
| double | abs(double a) | 取绝对值 |
| int double | pmod(int a, int b) pmod(double a, double b) | 返回 a 除 b 的余数的绝对值 |
| double | sin(double a) | 返回给定角度的正弦值 |
| double | asin(double a) | 返回 x 的反正弦，即是 X。如果 X 是在 - 1 到 1 的正弦值，返回 NULL。 |
| double | cos(double a) | 返回余弦 |
| double | acos(double a) | 返回 X 的反余弦，即余弦是 X，，如果 -1<= A <= 1，否则返回 null. |
| int double | positive(int a) positive(double a) | 返回 A 的值，例如 positive(2)，返回 2。 |
| int double | negative(int a) negative(double a) | 返回 A 的相反数，例如 negative(2), 返回 -2。 |
1.2.2 汇合函数
| 返回类型 | 函数 | 阐明 |
|---|---|---|
| int | size(Map<K.V>) | 求 map 的长度 |
| int | size(Array<T>) | 求数组的长度 |
| array<K> | map_keys(Map<K.V>) | 返回 map 中的所有 key |
| array<V> | map_values(Map<K.V>) | 返回 map 中的所有 value |
| boolean | array_contains(Array<T>, value) | 如该数组 Array<T> 蕴含 value 返回 true。，否则返回 false |
| array | sort_array(Array<T>) | 按天然程序对数组进行排序并返回 |
1.2.3 类型转换函数
| 返回类型 | 函数 | 阐明 | |
|---|---|---|---|
| binary | binary(string | binary) | 将输出的值转换成二进制 |
| Expected “=” to follow “type” | cast(expr as <type>) | 将 expr 转换成 type 类型 如：cast(“1” as BIGINT) 将字符串 1 转换成了 BIGINT 类型，如果转换失败将返回 NULL |
1.2.4 日期函数
| 返回类型 | 函数 | 阐明 |
|---|---|---|
| string | from_unixtime(bigint unixtime[, string format]) | UNIX_TIMESTAMP 参数示意返回一个值’YYYY- MM – DD HH：MM：SS’或 YYYYMMDDHHMMSS.uuuuuu 格局，这取决于是否是在一个字符串或数字语境中应用的性能。该值示意在以后的时区。 |
| bigint | unix_timestamp() | 如果不带参数的调用，返回一个 Unix 工夫戳（从’1970- 01 – 0100:00:00′到当初的 UTC 秒数）为无符号整数。 |
| bigint | unix_timestamp(string date) | 指定日期参数调用 UNIX_TIMESTAMP（），它返回参数值’1970- 01 – 0100:00:00′到指定日期的秒数。 |
| bigint | unix_timestamp(string date, string pattern) | 指定工夫输出格局，返回到 1970 年秒数：unix_timestamp(’2009-03-20′,‘yyyy-MM-dd’) = 1237532400 参考：http://java.sun.com/j2se/1.4…. |
| string | to_date(string timestamp) | 返回工夫中的年月日：to_date(“1970-01-01 00:00:00″) =“1970-01-01″ |
| string | to_dates(string date) | 给定一个日期 date，返回一个天数（0 年以来的天数） |
| int | year(string date) | 返回指定工夫的年份，范畴在 1000 到 9999，或为”零”日期的 0。 |
| int | month(string date) | 返回指定工夫的月份，范畴为 1 至 12 月，或 0 一个月的一部分，如’0000-00-00′或’2008-00-00′的日期。 |
| int | day(string date) dayofmonth(date) | 返回指定工夫的日期 |
| int | hour(string date) | 返回指定工夫的小时，范畴为 0 到 23。 |
| int | minute(string date) | 返回指定工夫的分钟，范畴为 0 到 59。 |
| int | second(string date) | 返回指定工夫的秒，范畴为 0 到 59。 |
| int | weekofyear(string date) | 返回指定日期所在一年中的星期号，范畴为 0 到 53。 |
| int | datediff(string enddate, string startdate) | 两个工夫参数的日期之差。 |
| int | date_add(string startdate, int days) | 给定工夫，在此基础上加上指定的时间段。 |
| int | date_sub(string startdate, int days) | 给定工夫，在此基础上减去指定的时间段。 |
1.2.5 条件函数
| 返回类型 | 函数 | 阐明 |
|---|---|---|
| T | if(boolean testCondition, T valueTrue, T valueFalseOrNull) | 如果 testCondition 为 true 就返回 valueTrue, 否则返回 valueFalseOrNull，（valueTrue，valueFalseOrNull 为泛型） |
| T | nvl(T value, T default_value) | 如果 value 值为 NULL 就返回 default_value, 否则返回 value |
| T | COALESCE(T v1, T v2, …) | 返回第一非 null 的值，如果全副都 NULL 就返回 NULL 如：COALESCE (NULL,44,55)=44/strong> |
| T | CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END | 如果 a = b 就返回 c,a= d 就返回 e，否则返回 f，如 CASE 4 WHEN 5 THEN 5 WHEN 4 THEN 4 ELSE 3 END 将返回 4 |
| T | CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END | 如果 a =ture 就返回 b,c= ture 就返回 d，否则返回 e，如：CASE WHEN 5>0 THEN 5 WHEN 4>0 THEN 4 ELSE 0 END 将返回 5；CASE WHEN 5<0 THEN 5 WHEN 4<0 THEN 4 ELSE 0 END 将返回 0 |
| boolean | isnull(a) | 如果 a 为 null 就返回 true，否则返回 false |
| boolean | isnotnull (a) | 如果 a 为非 null 就返回 true，否则返回 false |
1.2.6 字符函数
| 返回类型 | 函数 | 阐明 |
|---|---|---|
| int | length(string A) | 返回字符串的长度 |
| string | reverse(string A) | 返回倒序字符串 |
| string | concat(string A, string B…) | 连贯多个字符串，合并为一个字符串，能够承受任意数量的输出字符串 |
| string | concat_ws(string SEP, string A, string B…) | 链接多个字符串，字符串之间以指定的分隔符离开。 |
| string | substr(string A, int start) substring(string A, int start) | 从文本字符串中指定的起始地位后的字符。 |
| string | substr(string A, int start, int len) substring(string A, int start, int len) | 从文本字符串中指定的地位指定长度的字符。 |
| string | upper(string A) ucase(string A) | 将文本字符串转换成字母全副大写模式 |
| string | lower(string A) lcase(string A) | 将文本字符串转换成字母全副小写模式 |
| string | trim(string A) | 删除字符串两端的空格，字符之间的空格保留 |
| string | ltrim(string A) | 删除字符串右边的空格，其余的空格保留 |
| string | rtrim(string A) | 删除字符串左边的空格，其余的空格保留 |
| string | regexp_replace(string A, string B, string C) | 字符串 A 中的 B 字符被 C 字符代替 |
| string | regexp_extract(string subject, string pattern, int index) | 通过下标返回正则表达式指定的局部。regexp_extract(‘foothebar’,‘foo(.*?)(bar)’, 2) returns‘bar.’ |
| string | parse_url(string urlString, string partToExtract [, string keyToExtract]) | 返回 URL 指定的局部。parse_url(‘http://facebook.com/path1/p.p…′,‘HOST’) 返回：’facebook.com’ |
| string | get_json_object(string json_string, string path) | select a.timestamp, get_json_object(a.appevents,‘$.eventid’), get_json_object(a.appenvets,‘$.eventname’) from log a; |
| string | space(int n) | 返回指定数量的空格 |
| string | repeat(string str, int n) | 反复 N 次字符串 |
| int | ascii(string str) | 返回字符串中首字符的数字值 |
| string | lpad(string str, int len, string pad) | 返回指定长度的字符串，给定字符串长度小于指定长度时，由指定字符从左侧填补。 |
| string | rpad(string str, int len, string pad) | 返回指定长度的字符串，给定字符串长度小于指定长度时，由指定字符从右侧填补。 |
| array | split(string str, string pat) | 将字符串转换为数组。 |
| int | find_in_set(string str, string strList) | 返回字符串 str 第一次在 strlist 呈现的地位。如果任一参数为 NULL, 返回 NULL；如果第一个参数蕴含逗号，返回 0。 |
| array> | sentences(string str, string lang, string locale) | 将字符串中内容按语句分组，每个单词间以逗号分隔，最初返回数组。例如 sentences(‘Hello there! How are you?’) 返回：((“Hello”,“there”), (“How”,“are”,“you”) ) |
| array> | ngrams(array>, int N, int K, int pf) | SELECT ngrams(sentences(lower(tweet)), 2, 100 [, 1000]) FROM twitter; |
| array> | context_ngrams(array>, array, int K, int pf) | SELECT context_ngrams(sentences(lower(tweet)), array(null,null), 100, [, 1000]) FROM twitter; |
1.2.7 内置聚合函数
| 返回类型 | 函数 | 阐明 |
|---|---|---|
| bigint | count(*) , count(expr), count(DISTINCT expr[, expr_., expr_.]) | 返回记录条数。 |
| double | sum(col), sum(DISTINCT col) | 求和 |
| double | avg(col), avg(DISTINCT col) | 求平均值 |
| double | min(col) | 返回指定列中最小值 |
| double | max(col) | 返回指定列中最大值 |
| double | var_pop(col) | 返回指定列的方差 |
| double | var_samp(col) | 返回指定列的样本方差 |
| double | stddev_pop(col) | 返回指定列的偏差 |
| double | stddev_samp(col) | 返回指定列的样本偏差 |
| double | covar_pop(col1, col2) | 两列数值协方差 |
| double | covar_samp(col1, col2) | 两列数值样本协方差 |
| double | corr(col1, col2) | 返回两列数值的相关系数 |
| double | percentile(col, p) | 返回数值区域的百分比数值点。0<=P<=1, 否则返回 NULL, 不反对浮点型数值。 |
| array | percentile(col, array(p~1,, [, p,,2,,]…)) | 返回数值区域的一组百分比值别离对应的数值点。0<=P<=1, 否则返回 NULL, 不反对浮点型数值。 |
| double | percentile_approx(col, p[, B]) | Returns an approximate p^th^ percentile of a numeric column (including floating point types) in the group. The B parameter controls approximation accuracy at the cost of memory. Higher values yield better approximations, and the default is 10,000. When the number of distinct values in col is smaller than B, this gives an exact percentile value. |
| array | percentile_approx(col, array(p~1,, [, p,,2_]…) [, B]) | Same as above, but accepts and returns an array of percentile values instead of a single one. |
| array | histogram_numeric(col, b) | Computes a histogram of a numeric column in the group using b non-uniformly spaced bins. The output is an array of size b of double-valued (x,y) coordinates that represent the bin centers and heights |
| array | collect_set(col) | 返回无重复记录 |