数据仓库Hive
Hive运算符和函数
Hive所有函数参考官网地址：cwiki.apache.org/confluence/…
内置运算符
关系运算符(where子句中)
以下运算符比较传递的操作数，并根据操作数之间的比较是否成立生成TRUE或FALSE值
| 运算符 | 类型 | 说明 | |
|---|---|---|---|
| A = B | 所有原始类型 | 如果表达式A等于表达式B，则为TRUE，否则为FALSE | |
| A == B | 所有原始类型 | 同"=" | |
| A <=> B | 所有原始类型 | 对于非null操作数，与使用EQUAL(=) 运算符返回相同的结果，但若两者都为null，则返回TRUE；若其中一个为null，返回FALSE（自0.9.0版本起） | |
| A <> B | 所有原始类型 | 如果A或B为NULL，则为NULL；如果表达式A不等于表达式B，则为TRUE；否则为FALSE | |
| A < B | 所有原始类型 | 如果A或B为NULL，则为NULL；如果表达式A小于表达式B，则为TRUE；否则为FALSE | |
| A <= B | 所有原始类型 | 如果A或B为NULL，则为NULL；如果表达式A小于或等于表达式B，则为TRUE；否则为FALSE | |
| A > B | 所有原始类型 | 如果A或B为NULL，则为NULL；如果表达式A大于表达式B，则为TRUE；否则为FALSE | |
| A >= B | 所有原始类型 | 如果A或B为NULL，则为NULL；如果表达式A大于或等于表达式B，则为TRUE；否则为FALSE | |
| A [NOT] BETWEEN B AND C | 所有原始类型 | 如果A、B或C为NULL，则为NULL；如果A大于或等于B且A小于或等于C，则为TRUE；否则为FALSE。这可以通过使用NOT关键字来反转（自0.9.0版本起） | |
| A IS NULL | 所有类型 | 如果表达式A的计算结果为NULL，则为TRUE，否则为FALSE | |
| A IS NOT NULL | 所有类型 | 如果表达式A的计算结果为NULL，则为FALSE，否则为TRUE | |
| `A IS [NOT] (TRUE | FALSE)` | 布尔类型 | 只有当A满足条件时才求值为TRUE（自：3.0.0起）。注意：NULL是未知的，因为(UNKNOWN is TRUE)和(UNKNOWN is FALSE)都计算为FALSE |
| A [NOT] LIKE B | 字符串 | 如果A或B为NULL则为NULL，如果字符串A匹配SQL简单正则表达式B则为TRUE，否则为FALSE。比较是逐个字符进行的。B中的''匹配A中的任意字符(类似于posix正则表达式中的'.')，而B中的'%'匹配A中的任意数目的字符(类似于posix正则表达式中的'.*')。例如，'foobar' like 'foo' 计算结果为FALSE，而 'foobar' like 'foo__' 的计算结果为TRUE，'foobar' like 'foo%' 的计算结果也为TRUE | |
| A RLIKE B | 字符串 | 如果A或B为NULL则为NULL，如果A的任何子字符串(可能为空)匹配Java正则表达式B则为TRUE，否则为FALSE。例如，'foobar' RLIKE 'foo' 计算结果为TRUE， 'foobar' RLIKE '^f.*r$' 计算结果也为TRUE | |
| A REGEXP B | 字符串 | 与RLIKE相同 |
算术运算符
以下操作符支持对操作数进行各种常见的算术运算。所有都返回number类型；如果任何操作数为NULL，则结果也是NULL
| 运算符 | 类型 | 说明 | |
|---|---|---|---|
| A + B | 所有数字类型 | 给出A和B相加的结果。结果的类型与操作数类型的共同父类型(在类型层次结构中)相同。例如，由于每个整数都是浮点数，因此浮点数是包含整数类型的，因此浮点数和int的+运算符将产生浮点数 | |
| A – B | 所有数字类型 | 给出A减去B的结果。结果的类型与操作数类型的共同父类型(在类型层次结构中)相同 | |
| A * B | 所有数字类型 | 给出A与B相乘的结果。结果的类型与操作数类型的共同父类型(在类型层次结构中)相同。注意，如果乘法导致溢出，则你必须将其中一个操作符强制转换为类型层次结构中更高的类型 | |
| A / B | 所有数字类型 | 给出A除以B的结果，结果在大多数情况下是double类型。当A和B都是整数时，结果是double类型，除非hive.compat配置参数被设置为"0.13"或"latest"，在这种情况下，结果是十进制类型 | |
| A DIV B | 整数类型 | 给出A除以B的整数部分，例如17除以3得到5 | |
| A % B | 所有数字类型 | 给出A除以B得到的余数。结果的类型与操作数类型的共同父类型(在类型层次结构中)相同 | |
| A & B | 所有数字类型 | 给出A和B的按位与运算的结果。结果的类型与操作数类型的共同父类型(在类型层次结构中)相同 | |
| `A | B` | 所有数字类型 | 给出A和B按位或的结果。结果的类型与操作数类型的共同父类型(在类型层次结构中)相同 |
| A ^ B | 所有数字类型 | 给出A和B按位异或的结果。结果的类型与操作数类型的共同父类型(在类型层次结构中)相同 | |
| ~A | 所有数字类型 | 给出A的按位NOT(非或否)的结果，结果的类型与A的类型相同 |
逻辑运算符
以下操作符提供了创建逻辑表达式的支持。它们都根据操作数的布尔值返回布尔值TRUE、FALSE或NULL。NULL作为“未知”标志，因此如果结果取决于未知的状态，则结果本身是未知的
| 运算符 | 类型 | 说明 |
|---|---|---|
| A AND B | 布尔类型 | 如果A和B都为TRUE，则为TRUE，否则为FALSE。如果A或B为NULL，则为NULL |
| A OR B | 布尔类型 | 如果A或B或两者都为TRUE，则为TRUE，FALSE OR NULL是NULL，否则为FALSE |
| NOT A | 布尔类型 | 如果A为FALSE则为TRUE，如果A为NULL则为NULL。否则为FALSE |
| ! A | 布尔类型 | 与NOT A相同 |
| A IN (val1, val2, ...) | 布尔类型 | 如果A等于其中任何一个值，则为TRUE。从Hive 0.13开始，在IN语句中支持子查询 |
| A NOT IN (val1, val2, ...) | 布尔类型 | 如果A不等于其中任何值，则为TRUE。从Hive 0.13开始，在NOT IN语句中支持子查询 |
| [NOT] EXISTS (subquery) | 如果子查询返回至少一行，则返回TRUE。从Hive 0.13开始支持 |
字符串运算符
| 运算符 | 类型 | 说明 | ||
|---|---|---|---|---|
| `A | B` | strings | 连接操作数-concat(A,B)的简写。Hive 2.2.0起支持 |
复杂类型构造函数
下面的函数构造复杂类型的实例
| 构造函数 | 操作数 | 说明 |
|---|---|---|
| map | (key1, value1, key2, value2, ...) | 使用给定的键/值对创建map |
| struct | (val1, val2, val3, ...) | 用给定的字段值创建一个结构体。结构字段名将是col1, col2, .... |
| named_struct | (name1, val1, name2, val2, ...) | 用给定的字段名和值创建一个结构体(从Hive 0.8.0开始) |
| array | (val1, val2, ...) | 用给定的元素创建一个数组 |
| create_union | (tag, val1, val2, ...) | 使用标记参数所指向的值创建联合类型 |
复杂类型上的运算符
| 函数 | 操作数类型 | 说明 |
|---|---|---|
| A[n] | A是Array, n是int | 返回数组a中的第n个元素，第一个元素的索引为0。例如，如果A是一个由['foo', 'bar'] 组成的数组，那么A[0]返回'foo'， A[1]返回'bar' |
| M[key] | M是Map<K, V>，键的类型为K | 返回map中键对应的值。例如，如果M是由{'f' -> 'foo', 'b' -> 'bar', 'all' -> 'foobar'} 组成的map，则M['all']返回'foobar' |
| S.x | S是一个结构体 | 返回S的x字段。例如，对于struct foobar {int foo, int bar},，foobar.foo返回存储在该结构体的foo字段中的整数 |
内置函数
数学函数
Hive支持以下内置数学函数：当参数为NULL时，大多数返回NULL
| 返回类型 | 函数名称 | 说明 |
|---|---|---|
| DOUBLE | round(DOUBLE a) | 返回a的四舍五入BIGINT值 |
| DOUBLE | round(DOUBLE a, INT d) | 返回a四舍五入到d位小数，例如round(21.263,2)返回21.26 |
| DOUBLE | bround(DOUBLE a) | 使用HALF_EVEN舍入模式返回a的四舍五入的BIGINT值(如从Hive 1.3.0, 2.0.0开始)。也称为高斯四舍五入或银行家四舍五入。例如：bround(2.5) = 2, bround(3.5) = 4 |
| DOUBLE | bround(DOUBLE a, INT d) | 使用HALF_EVEN舍入模式返回四舍五入到小数点后d位(从Hive 1.3.0, 2.0.0开始)。例如：bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4 |
| BIGINT | floor(DOUBLE a) | 返回等于或小于a的最大BIGINT值 |
| BIGINT | ceil(DOUBLE a), ceiling(DOUBLE a) | 返回等于或大于a的最小BIGINT值 |
| DOUBLE | rand(), rand(INT seed) | 返回从0到1均匀分布的随机数(逐行变化)。指定种子将确保生成的随机数序列是确定的 |
| DOUBLE | exp(DOUBLE a), exp(DECIMAL a) | 返回ea，其中e是自然对数的底数。Hive 0.13.0添加十进制版本 |
| DOUBLE | ln(DOUBLE a), ln(DECIMAL a) | 返回参数a的自然对数。Hive 0.13.0添加十进制版本 |
| DOUBLE | log10(DOUBLE a), log10(DECIMAL a) | 返回参数a的以10为底的对数。Hive 0.13.0添加十进制版本 |
| DOUBLE | log2(DOUBLE a), log2(DECIMAL a) | 返回参数a的以2为底的对数。Hive 0.13.0添加十进制版本 |
| DOUBLE | log(DOUBLE base, DOUBLE a)log(DECIMAL base, DECIMAL a) | 返回参数a的以base为底的对数。Hive 0.13.0添加了十进制版本 |
| DOUBLE | pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p) | 返回a的p次方 |
| DOUBLE | sqrt(DOUBLE a), sqrt(DECIMAL a) | 返回a的平方根。Hive 0.13.0添加十进制版本 |
| STRING | bin(BIGINT a) | 返回数字的二进制格式 |
| STRING | hex(BIGINT a) hex(STRING a) hex(BINARY a) | 如果参数是INT或二进制，hex返回该参数十六进制格式的字符串。否则，如果参数是STRING，则将每个字符转换为其十六进制表示并返回最终的STRING |
| BINARY | unhex(STRING a) | 十六进制的反转操作。将每对字符解释为十六进制数，并转换为该数字的字节表示形式。(Hive 0.12.0的二进制版本，用于返回字符串) |
| STRING | conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base) | 将一个数字从给定的进制转换为另一个进制 |
| DOUBLE | abs(DOUBLE a) | 返回绝对值 |
| INT or DOUBLE | pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b) | 返回a对b取模后的正值 |
| DOUBLE | sin(DOUBLE a), sin(DECIMAL a) | 返回a的正弦值(a的单位是弧度) |
| DOUBLE | asin(DOUBLE a), asin(DECIMAL a) | 如果-1<=a<=1，返回a的反正弦值，否则返回NULL |
| DOUBLE | cos(DOUBLE a), cos(DECIMAL a) | 返回a的余弦值(a以弧度为单位) |
| DOUBLE | acos(DOUBLE a), acos(DECIMAL a) | 如果-1<=a<=1，则返回a的反余弦值，否则返回NULL |
| DOUBLE | tan(DOUBLE a), tan(DECIMAL a) | 返回a的正切值(a的单位是弧度) |
| DOUBLE | atan(DOUBLE a), atan(DECIMAL a) | 返回a的反正切值 |
| DOUBLE | degrees(DOUBLE a), degrees(DECIMAL a) | 将a的值从弧度转换为度数 |
| DOUBLE | radians(DOUBLE a), radians(DOUBLE a) | 将a的值从度数转换为弧度 |
| INT or DOUBLE | positive(INT a), positive(DOUBLE a) | 返回a |
| INT or DOUBLE | negative(INT a), negative(DOUBLE a) | 返回-a |
| DOUBLE or INT | sign(DOUBLE a), sign(DECIMAL a) | 将a的符号返回为'1.0'(如果a是正数)或'-1.0'(如果a是负数)，否则返回'0.0'。十进制版本返回INT而不是DOUBLE |
| DOUBLE | e() | 返回e的值 |
| DOUBLE | pi() | 返回pi的值 |
| BIGINT | factorial(INT a) | 返回a的阶乘 |
| DOUBLE | cbrt(DOUBLE a) | 返回double值的立方根 |
| INTBIGINT | shiftleft(TINYINT|SMALLINT|INT a, INT b)shiftleft(BIGINT a, INT b) | 按位左移(从Hive 1.2.0开始)。将a b位置向左移动。对于tinyint, smallint和int a返回int。对于bigint a返回bigint |
| INTBIGINT | shiftright(TINYINT|SMALLINT|INT a, INT b)shiftright(BIGINT a, INT b) | 按位右移(从Hive 1.2.0开始)。将a b位置向右移动。对于tinyint, smallint和int a返回int。对于bigint a返回bigint |
| INTBIGINT | shiftrightunsigned(TINYINT|SMALLINT|INT a, INT b),shiftrightunsigned(BIGINT a, INT b) | 按位无符号右移(从Hive 1.2.0开始)。将a b位置向右移动。对于tinyint, smallint和int a返回int。对于bigint a返回bigint。 |
| T | greatest(T v1, T v2, ...) | 返回值列表中的最大值(从Hive 1.1.0开始)。修复了当一个或多个参数为NULL时返回NULL的问题，并放宽了严格的类型限制，与">"操作符一致(从Hive 2.0.0开始) |
| T | least(T v1, T v2, ...) | 返回值列表中最小的值(从Hive 1.1.0开始)。修复了当一个或多个参数为NULL时返回NULL的问题，并放宽了严格的类型限制，与"<"操作符一致(从Hive 2.0.0开始) |
| INT | width_bucket(NUMERIC expr, NUMERIC min_value, NUMERIC max_value, INT num_buckets) | 通过将expr映射到第i个大小相等的桶，返回一个介于0和num_buckets+1之间的整数。通过将[min_value, max_value] 划分为大小相等的区域来创建桶。如果expr < min_value，返回1，如果expr > max_value返回num_buckets+1 |
集合函数
| 返回类型 | 函数名称 | 说明 |
|---|---|---|
| int | size(Map<K.V>) | 返回map类型中元素的个数 |
| int | size(Array) | 返回数组类型中元素的个数 |
| array | map_keys(Map<K.V>) | 返回包含输入map键的无序数组 |
| array | map_values(Map<K.V>) | 返回包含输入map值的无序数组 |
| boolean | array_contains(Array, value) | 如果数组包含value则返回TRUE |
| array | sort_array(Array) | 根据数组元素的自然顺序将输入数组按升序排序并返回 |
类型转换函数
int->bigint自动转换，bigint->int需要强制类型转换
| 返回类型 | 函数名称 | 说明 | |
|---|---|---|---|
| binary | `binary(string | binary)` | 将参数强制转换为二进制 |
| Expected "=" to follow "type" | cast(expr as <type>) | 将表达式expr的结果转换为type。例如，cast('1' as BIGINT)将把字符串'1'转换为整数形式。如果转换不成功，则返回null。如果cast(expr as boolean)，Hive对非空字符串返回true |
日期函数
| 返回类型 | 函数名称 | 说明 |
|---|---|---|
| string | from_unixtime(bigint unixtime[, string pattern]) | 使用指定的模式将自epoch(1970-01-01 00:00:00 UTC)以来的秒数转换为表示当前时区(使用"hive.local.time.zone"配置)中该时刻的时间戳的字符串。如果缺少模式，则使用默认模式值('uuuu-MM-dd HH:mm:ss'或'yyyy-MM-dd HH:mm:ss')。示例: from_unixtime(0)=1970-01-01 00:00:00 (hive.local.time.zone=Etc/GMT)。从Hive 4.0.0 (HIVE-25576)开始，"hive.datetime. formatter"属性可用于控制底层格式化程序实现，从而控制可接受的模式及其行为。以前的版本总是使用Java中的SimpleDateFormat作为底层格式化程序 |
| bigint | unix_timestamp() | 获取以秒为单位的当前Unix时间戳。这个函数不是确定的，它的值对于查询执行的范围来说不是固定的，因此妨碍了查询的适当优化——自2.0以来，这已经被弃用了，转而使用CURRENT_TIMESTAMP常量 |
| bigint | unix_timestamp(string date) | 使用默认模式将datetime字符串转换为unix时间(从epoch开始的秒数)。默认接受的模式取决于底层格式化程序实现。datetime字符串不包含时区，因此转换使用"hive.local.time. zone"属性指定的本地时区。转换失败时返回null。例如: unix_timestamp('2009-03-20 11:30:01') = 1237573801。（同上，"hive.datetime. formatter"属性控制底层格式化程序实现） |
| bigint | unix_timestamp(string date, string pattern) | 使用指定的模式将datetime字符串转换为unix时间(从epoch开始的秒数)。可接受的模式及其行为取决于底层格式化程序的实现。转换失败时返回null。例如:unix_timestamp('2009-03-20', 'uuuu-MM-dd') = 1237532400。（同上，"hive.datetime. formatter"属性控制底层格式化程序实现） |
| 2.1.0之前是string，之后是date | to_date(string timestamp) | 返回时间戳字符串的日期部分(Hive 2.1.0之前):to_date("1970-01-01 00:00:00") = "1970-01-01"。从Hive 2.1.0开始，返回一个日期对象。在Hive 2.1.0 (HIVE-13248) 之前，返回类型是String，因为在创建方法时不存在Date类型 |
| int | year(string date) | 返回日期或时间戳字符串的年份部分:year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970 |
| int | quarter(date/timestamp/string) | 返回日期、时间戳或字符串的一年中的季度在1到4之间(从Hive 1.3.0开始)。例如:quarter('2015-04-08') = 2. |
| int | month(string date) | 返回日期或时间戳字符串的月份部分:month("1970-11-01 00:00:00") = 11, month("1970-11-01") = 11 |
| int | day(string date) dayofmonth(date) | 返回日期或时间戳字符天部分:day("1970-11-01 00:00:00") = 1, day("1970-11-01") = 1 |
| int | hour(string date) | 返回时间戳的小时:hour('2009-07-30 12:58:59') = 12, hour('12:58:59') = 12 |
| int | minute(string date) | 返回时间戳的分钟 |
| int | second(string date) | 返回时间戳的秒值 |
| int | weekofyear(string date) | 返回时间戳字符串的周数:weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44 |
| int | extract(field FROM source) | 从source中检索字段，如天数或小时(从Hive 2.2.0始)。source必须是日期、时间戳、时间间隔或可以转换为日期或时间戳的字符串。支持的字段包括:day, dayofweek, hour, minute, month, quarter, second, week and year。例如：select extract(month from "2016-10-20") 结果是10，select extract(hour from "2016-10-20 05:06:07") 结果是5，select extract(dayofweek from "2016-10-20 05:06:07") 结果是5，select extract(month from interval '1-3' year to month)结果是3，select extract(minute from interval '3 12:20:30' day to second)结果是20 |
| int | datediff(string enddate, string startdate) | 返回从startdate到enddate的天数:datediff('2009-03-01', '2009-02-27') = 2 |
| 2.1.0之前是string，之后是date | date_add(date/timestamp/string startdate, tinyint/smallint/int days) | 向startdate添加天数:date_add('2008-12-31', 1) = '2009-01-01'。在Hive 2.1.0 (HIVE-13248)之前，返回类型是String，因为在创建方法时不存在Date类型 |
| 2.1.0之前是string，之后是date | date_sub(date/timestamp/string startdate, tinyint/smallint/int days) | 向startdate减少天数:date_sub('2008-12-31', 1) = '2008-12-30'。在Hive 2.1.0 (HIVE-13248)之前，返回类型是String，因为在创建方法时不存在Date类型 |
| timestamp | from_utc_timestamp({any primitive type} ts, string timezone) | 将UTC中的timestamp*转换为给定的时区(从Hive 0.8.0开始)。* timestamp是一个基本类型，包括timestamp/date, tinyint/smallint/int/bigint, float/double 和decimal。小数值被认为是秒，整数值被认为是毫秒。例如，from_utc_timestamp(2592000.0,'PST'), from_utc_timestamp(2592000000,'PST') 和 from_utc_timestamp(timestamp '1970-01-30 16:00:00','PST') 都返回时间戳1970-01-30 08:00:00. |
| timestamp | to_utc_timestamp({any primitive type} ts, string timezone) | 将给定时区中的timestamp*转换为UTC(从Hive 0.8.0开始)。* timestamp是一个基本类型，包括timestamp/date, tinyint/smallint/int/bigint, float/double 和decimal。小数值被认为是秒，整数值被认为是毫秒。例如，to_utc_timestamp(2592000.0,'PST'), to_utc_timestamp(2592000000,'PST') 和 to_utc_timestamp(timestamp '1970-01-30 16:00:00','PST') 都返回时间戳 1970-01-31 00:00:00. |
| date | current_date | 返回查询计算开始时的当前日期(从Hive 1.2.0开始)。同一查询中对current_date的所有调用返回相同的值 |
| timestamp | current_timestamp | 返回查询计算开始时的当前时间戳(从Hive 1.2.0开始)。同一查询中对current_timestamp的所有调用返回相同的值 |
| string | add_months(string start_date, int num_months, output_date_format) | 在指定的日期上加上几个月并返回，返回在start_date之后num_months的日期(从Hive 1.1.0开始)。start_date是一个字符串、日期或时间戳。num_months是一个整数。如果start_date是该月的最后一天，或者结果月份的天数少于start_date的day部分，则结果为结果月份的最后一天。否则，结果与start_date具有相同的day部分。默认输出格式为'yyyy-MM-dd'。在Hive 4.0.0之前，日期的时间部分被忽略。从Hive 4.0.0开始，add_months支持一个可选参数output_date_format，它接受一个字符串，表示输出的有效日期格式。这允许在输出中保留时间格式。例如：add_months('2009-08-31', 1) 返回 '2009-09-30'，add_months('2017-12-31 14:15:16', 2, 'YYYY-MM-dd HH:mm:ss') 返回 '2018-02-28 14:15:16' |
| string | last_day(string date) | 返回日期所属月份的最后一天(从Hive 1.1.0开始)。date为字符串，格式为'yyyy-MM-dd HH:mm:ss' 或 'yyyy-MM-dd'。date的时间部分被忽略 |
| string | next_day(string start_date, string day_of_week) | 返回晚于start_date的第一个日期并命名为day_of_week(从Hive 1.2.0开始)。start_date是一个字符串/日期/时间戳。day_of_week是2个字母，3个字母或星期的全名(例如Mo, tue, FRIDAY)。start_date的时间部分被忽略。例如：next_day('2015-01-14', 'TU') = 2015-01-20. |
| string | trunc(string date, string format) | 返回被截断的日期，以格式指定的单位返回(从Hive 1.2.0开始)。支持的格式有：MONTH/MON/MM, YEAR/YYYY/YY，例如：trunc('2015-03-17', 'MM') = 2015-03-01 |
| double | months_between(date1, date2) | 返回日期date1和date2之间的月数(从Hive 1.2.0开始)。如果date1晚于date2，则结果为正。如果date1早于date2，则结果为负。如果date1和date2是同一个月的同一天，或者都是同一个月的最后一天，那么结果总是一个整数。否则，UDF根据31天的月份计算结果的小数部分，并考虑date1和date2时间部分的差异。date1和date2类型可以是date, timestamp或格式为'yyyy-MM-dd' 或 'yyyy-MM-dd HH:mm:ss'的字符串。结果四舍五入到小数点后8位。例如：months_between('1997-02-28 10:30:00', '1996-10-30') = 3.94959677 |
| string | date_format(date/timestamp/string ts, string pattern) | 使用指定的模式(从Hive 1.2.0开始)将date/timestamp/string转换为字符串值。可接受的模式及其行为取决于底层格式化程序的实现。pattern参数应该是常量。例如：date_format('2015-04-08', 'y') = '2015' |
条件函数
| 返回类型 | 函数名称 | 说明 |
|---|---|---|
| T | if(boolean testCondition, T valueTrue, T valueFalseOrNull) | 当testCondition为true时返回valueTrue，否则返回valueFalseOrNull |
| boolean | isnull( a ) | 如果a为NULL返回true，否则返回false |
| boolean | isnotnull ( a ) | 如果a不为NULL则返回true，否则返回false |
| T | nvl(T value, T default_value) | 如果value为null则返回默认值，否则返回value(从HIve 0.11开始) |
| T | COALESCE(T v1, T v2, ...) | 返回第一个不为NULL的v，如果所有v都为NULL则返回NULL |
| T | CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END | 当a = b，返回c；当a = d时，返回e；否则返回f |
| T | CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END | 当a = true时，返回b；当c = true时，返回d；否则返回e |
| T | nullif( a, b ) | 如果a=b，返回NULL；否则返回a(从Hive 2.3.0开始)。简写为：CASE WHEN a = b then NULL else a |
| void | assert_true(boolean condition) | 如果'condition'不为true，抛出异常，否则返回null(从Hive 0.8.0开始)。例如，select assert_true (2<1) |
字符串函数
| 返回类型 | 函数名称 | 说明 | |
|---|---|---|---|
| int | ascii(string str) | 返回str的第一个字符的数值 | |
| string | base64(binary bin) | 将参数从二进制转换为base64字符串(从Hive 0.12.0开始) | |
| int | character_length(string str) | 返回字符串中包含的UTF-8字符的数量(从Hive 2.2.0开始)。char_length函数是这个函数的简写 | |
| string | chr(bigint|double A) | 返回二进制相当于A的ASCII字符(Hive 1.3.0和2.1.0)。如果A大于256，则结果等于chr(A % 256)。例如：select chr(88); 返回"X" | |
| string | concat(string|binary A, string|binary B...) | 按顺序连接作为参数传入的字符串或字节，并返回。例如，concat('foo', 'bar')的结果是'foobar'。注意，这个函数可以接受任意数量的输入字符串 | |
| array<struct<string,double>> | context_ngrams(array<array>, array, int K, int pf) | 给定一个"context"字符串（上下文），从一组分词化的句子中返回前k个上下文相关的n元语法，可选的第四个参数pf控制启发式使用的内存，较大的值将产生更好的准确性，但会使用更多的内存。更多信息请参考StatisticsAndDataMining。从一组标记化的句子中返回前 k 个文本，例如：select context_ngrams(sentences('hello word!hello hive,hi hive,hello hive'),array('hello',null),4) from A会返回[{"ngram":["hive"],"estfrequency":7141046.0},{"ngram":["word"],"estfrequency":3570523.0}]，即最经常在"hello"后出现的单词，并统计其频次，有统计聚合作用 | |
| string | concat_ws(string SEP, string A, string B...) | 与上面的concat()类似，但使用自定义分隔符SEP | |
| string | concat_ws(string SEP, array) | 与上面的concat_ws()类似，但采用字符串数组。(从Hive 0.9.0开始) | |
| string | decode(binary bin, string charset) | 使用提供的字符集('US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'之一)将第一个参数解码为字符串。如果任何一个参数是null，结果也是null。(Hive 0.12.0版本) | |
| string | elt(N int,str1 string,str2 string,str3 string,...) | 返回指定索引处的字符串。例如，elt(2,'hello','world') 返回 'world'。如果N小于1或大于参数数量，则返回NULL | |
| binary | encode(string src, string charset) | 使用提供的字符集('US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'之一)将第一个参数编码为二进制。如果任何一个参数是null，结果也是null。(Hive 0.12.0版本) | |
| int | field(val T,val1 T,val2 T,val3 T,...) | 返回val在val1,val2,val3,... 列表中的索引，如果未找到则为0。例如：field('world','say','hello','world') 返回 3。支持所有基本类型，使用str.equals(x)比较参数。如果val为NULL，则返回值为0 | |
| int | find_in_set(string str, string strList) | 返回strList中第一次出现的str，其中strList是一个逗号分隔的字符串。如果参数为null，则返回null。如果第一个参数包含任何逗号，则返回0。例如，find_in_set('ab', 'abc,b,ab,c,def') 返回3 | |
| string | format_number(number x, int d) | 将数字X格式化为类似'#,###,###.##'的格式，四舍五入到小数点后D位，并以字符串形式返回结果。如果D为0，则结果没有小数点或小数部分。(从Hive 0.10.0开始;Hive 0.14.0修正了float类型的错误，Hive 0.14.0增加了decimal类型的支持 | |
| string | get_json_object(string json_string, string path) | 根据指定的json路径从json字符串中提取json对象，并返回提取的json对象的json字符串。如果输入的json字符串无效，它将返回null。注意:json路径只能包含字符[0-9a-z_]，即不能包含大写或特殊字符。此外，键不能以数字开头。这是由于Hive列名称的限制 | |
| boolean | in_file(string str, string filename) | 如果字符串str在filename中作为整行出现，则返回true | |
| int | instr(string str, string substr) | 返回substr在str中第一次出现的位置。如果两个参数中的任何一个为null则返回null，如果在str中找不到substr则返回0。注意，这不是基于零的，str中的第一个字符索引为1 | |
| int | length(string A) | 返回字符串的长度 | |
| int | locate(string substr, string str[, int pos]) | 在位置pos之后返回substr在str中第一次出现的位置 | |
| string | lower(string A) lcase(string A) | 返回将B的所有字符转换为小写后的字符串。例如，lower('fOoBaR') 结果是 'foobar' | |
| string | lpad(string str, int len, string pad) | 返回str，用pad左填充，长度为len。如果str比len长，返回值会缩短为len个字符。如果pad字符串为空，则返回值为null | |
| string | ltrim(string A) | 返回从a的开头(左侧)去掉空格后的字符串。例如，ltrim(' foobar ') 结果是 'foobar ' | |
| array<struct<string,double>> | ngrams(array<array>, int N, int K, int pf) | 从一组分词化的句子中返回前k个n元语法，例如sentences() UDAF返回的那些。更多信息请参考 StatisticsAndDataMining | |
| int | octet_length(string str) | 返回保存UTF-8编码的字符串str所需的字节数(从Hive 2.2.0开始)。请注意，octet_length(str)可以大于character_length(str) | |
| string | parse_url(string urlString, string partToExtract [, string keyToExtract]) | 从URL返回指定的部分。partToExtract提取的有效值包括HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, 和USERINFO。例如，parse_url('facebook.com/path1/p.php…', 'HOST') 返回 'facebook.com'。同样，QUERY中特定键的值可以通过提供第三个参数来提取，例如，parse_url('facebook.com/path1/p.php…', 'QUERY', 'k1') 返回'v1' | |
| string | printf(String format, Obj... args) | 将输入的字符串根据printf样式格式化并返回(在Hive 0.9.0中) | |
| string | quote(String text) | 返回带引号的字符串（包括任何单引号的转义符HIVE-4.0.0），例如：NULL->NULL，DONT->'DONT',DON'T->'DON\'T' | |
| string | regexp_extract(string subject, string pattern, int index) | 返回使用模式提取的字符串。例如，regexp_extract('foothebar', 'foo(.*?)(bar)', 2)返回'bar.'。请注意，在使用预定义的字符类时需要小心：使用'\s'作为第二个参数将匹配字母s；'\s'是匹配空格等所必需的 | |
| string | regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT) | 用REPLACEMENT替换INITIAL_STRING中与PATTERN中定义的java正则表达式语法匹配的所有子字符串，返回结果字符串（就是将原始字符串中与正则匹配的所有子字符串替换成指定的字符串，并返回）。例如：`regexp_replace("foobar", "oo | ar", "")` 返回'fb.'。请注意，在使用预定义的字符类时需要小心：使用'\s'作为第二个参数将匹配字母s；'\s'是匹配空格等所必需的 |
| string | repeat(string str, int n) | 重复字符串n次 | |
| string | replace(string A, string OLD, string NEW) | 返回字符串A，并将所有不重叠的OLD替换为NEW(从Hive 1.3.0和2.1.0开始)。例如：select replace("ababab", "abab", "Z"); 返回 "Zab" | |
| string | reverse(string A) | 返回反转后的字符串 | |
| string | rpad(string str, int len, string pad) | 返回str，用pad右填充，长度为len。如果str比len长，返回值会缩短为len个字符。如果pad字符串为空，则返回值为null | |
| string | rtrim(string A) | 返回修剪a末尾(右侧)的空格所产生的字符串。例如，rtrim(' foobar ') 结果是 ' foobar'. | |
array<array<string>> | sentences(string str, string lang, string locale) | 将自然语言文本字符串标记为单词和句子，其中每个句子在适当的句子边界处断开，并作为单词数组返回。 'lang'和'locale' 是可选参数。例如，sentences('Hello there! How are you?') 返回 ( ("Hello", "there"), ("How", "are", "you") ) | |
| string | space(int n) | 返回一个包含n个空格的字符串 | |
| array | split(string str, string pat) | 围绕pat拆分str (pat是正则表达式) | |
| map<string,string> | str_to_map(text[, delimiter1, delimiter2]) | 使用两个分隔符将文本分割为键值对。Delimiter1将文本分割为K-V对，而Delimiter2将每个K-V对分割。对于delimiter1，默认分隔符是',' ；对于delimiter2，默认分隔符是 ':' | |
| string | substr(string|binary A, int start) substring(string|binary A, int start) | 返回A的字节数组的子字符串或切片，从开始位置到字符串A的末尾。例如，substr('foobar', 4)的结果为'bar' | |
| string | substr(string|binary A, int start, int len) substring(string|binary A, int start, int len) | 返回从起始位置开始、长度为len的A字节数组的子字符串或切片。例如，substr('foobar', 4, 1) 结果是 'b' | |
| string | substring_index(string A, string delim, int count) | 在分隔符delim出现指定数量之前，返回字符串A中的子字符串(从Hive 1.3.0开始)。如果count为正数，则返回最后一个分隔符(从左边开始计数)左边的所有内容。如果count为负值，则返回最后一个分隔符(从右侧开始计数)右侧的所有内容。substring_index在搜索delim时执行区分大小写的匹配。例如，substring_index('www.apache.org', '. ', 2) = 'www.apache' | |
| string | translate(string|char|varchar input, string|char|varchar from, string|char|varchar to) | 通过将from字符串中的字符替换为to字符串中的相应字符来转换输入字符串。这类似于PostgreSQL中的translate函数。如果此UDF的任何参数为NULL，则结果也为NULL。(从Hive 0.10.0开始提供，用于字符串类型)。Hive 0.14.0添加Char/varchar支持 | |
| string | trim(string A) | 返回修剪a两端空格所产生的字符串。例如，trim(' foobar ') 结果是 'foobar' | |
| binary | unbase64(string str) | 将参数从base 64字符串转换为BINARY。(从Hive 0.12.0开始) | |
| string | upper(string A) ucase(string A) | 返回将A的所有字符转换为大写后的字符串。例如，upper('fOoBaR') 结果是 'FOOBAR'. | |
| string | initcap(string A) | 返回字符串，其中每个单词的第一个字母为大写，其他所有字母为小写。单词由空格分隔。(从Hive 1.1.0开始) | |
| int | levenshtein(string A, string B) | 返回两个字符串之间的Levenshtein距离(在Hive 1.2.0中)。例如，levenshtein('kitten', 'sitting') 结果是 3 | |
| string | soundex(string A) | 返回字符串的soundex编码(从Hive 1.2.0开始)。例如，soundex('Miller') 结果是 M460 |
数据屏蔽函数
| 返回类型 | 函数名称 | 说明 |
|---|---|---|
| string | mask(string str[, string upper[, string lower[, string number]]]) | 返回str的一个掩码版本(从Hive 2.1.0开始)。默认情况下，大写字母转换为"X"，小写字母转换为"x"，数字转换为"n"。例如mask("abcd-EFGH-8765-4321") 结果是 xxxx-XXXX-nnnn-nnnn。您可以通过提供附加参数来覆盖掩码中使用的字符：第二个参数控制大写字母的掩码字符，第三个参数控制小写字母的掩码字符，第四个参数控制数字的掩码字符。例如：mask("abcd-EFGH-8765-4321", "U", "l", "#") 结果是 llll-UUUU-####-####. |
| string | mask_first_n(string str[, int n]) | 返回str的一个掩码版本，其中前n个值被掩码(从Hive 2.1.0开始)。大写字母转换为"X"，小写字母转换为"x"，数字转换为"n"。例如，mask_first_n("1234-5678-8765-4321", 4) 结果为 nnnn-5678-8765-4321 |
| string | mask_last_n(string str[, int n]) | 返回str的一个掩码版本，其中最后n个值被掩码(自Hive 2.1.0起)。大写字母转换为"X"，小写字母转换为"x"，数字转换为"n"。例如，mask_last_n("1234-5678-8765-4321", 4) 结果为 1234-5678-8765-nnnn |
| string | mask_show_first_n(string str[, int n]) | 返回str的一个掩码版本，显示不掩码的前n个字符(从Hive 2.1.0开始)。大写字母转换为"X"，小写字母转换为"x"，数字转换为"n"。例如，mask_show_first_n("1234-5678-8765-4321", 4) 结果为 1234-nnnn-nnnn-nnnn |
| string | mask_show_last_n(string str[, int n]) | 返回str的一个掩码版本，显示不掩码的最后n个字符(从Hive 2.1.0开始)。大写字母转换为"X"，小写字母转换为"x"，数字转换为"n"。例如，mask_show_last_n("1234-5678-8765-4321", 4) 结果为 nnnn-nnnn-nnnn-4321 |
| string | mask_hash(string|char|varchar str) | 返回基于str的哈希值(从Hive 2.1.0开始)。哈希是一致的，可用于跨表连接掩码值。对于非字符串类型，此函数返回null |
其它函数
| 返回类型 | 函数名称 | 说明 |
|---|---|---|
| varies | java_method(class, method[, arg1[, arg2..]]) | 反射的同义词 (从Hive 0.9.0开始) |
| varies | reflect(class, method[, arg1[, arg2..]]) | 通过匹配参数签名，使用反射调用Java方法。(从Hive 0.7.0开始)参考Reflect (Generic) UDF的示例 |
| int | hash(a1[, a2...]) | 返回参数的哈希值(从Hive 0.4开始) |
| string | current_user() | 从配置的认证管理器中返回当前的用户名(从Hive 1.2.0开始)。可能与用户在连接时提供的相同，但对于某些身份验证管理器(例如HadoopDefaultAuthenticator)，它可能不同 |
| string | logged_in_user() | 从会话状态返回当前用户名(从Hive 2.2.0开始)。这是连接到Hive时提供的用户名 |
| string | current_database() | 返回当前数据库名称(从Hive 0.13.0开始) |
| string | md5(string/binary) | 计算字符串或二进制的MD5 128位校验和(从Hive 1.3.0开始)。该值作为32个十六进制数字的字符串返回，如果参数为NULL，则返回NULL。示例:md5('ABC') = '902fbdd2b1df0c4f70b4a5d23525e932'. |
| string | sha1(string/binary)sha(string/binary) | 计算字符串或二进制的SHA-1摘要并以十六进制字符串的形式返回值(从Hive 1.3.0开始)。示例:sha1('ABC') = '3c01bdbb26f358bab27f267924aa2c9a03fcfdb8' |
| bigint | crc32(string/binary) | 计算字符串或二进制参数的循环冗余校验值并返回bigint值(从Hive 1.3.0开始)。示例:crc32('ABC') = 2743272264 |
| string | sha2(string/binary, int) | 计算SHA-2系列哈希函数(SHA-224, SHA-256, SHA-384, and SHA-512) (从Hive 1.3.0开始)。第一个参数是要哈希的字符串或二进制。第二个参数表示结果所需的位长度，其值必须为224, 256, 384, 512, 或0 (相当于256)。从Java 8开始支持SHA-224。如果参数为NULL或哈希长度不是允许的值之一，则返回值为NULL。例如：sha2('ABC', 256) = 'b5d4045c3f466fa91fe2cc6abe79232a1a57cdf104f7a26e716e0a1e2789df78' |
| binary | aes_encrypt(input string/binary, key string/binary) | 使用AES对输入进行加密(从Hive 1.3.0开始)。可以使用128, 192 或 256位的密钥长度。如果安装了Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files，则可以使用192位和256位的密钥。如果参数为NULL或键长度不是允许的值之一，则返回值为NULL |
| binary | aes_decrypt(input binary, key string/binary) | 使用AES对输入进行解密(从Hive 1.3.0开始)。可以使用128, 192 或 256位的密钥长度。如果安装了Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files，则可以使用192位和256位的密钥。如果参数为NULL或键长度不是允许的值之一，则返回值为NULL |
| string | version() | 返回Hive版本(从Hive 2.1.0开始)。该字符串包含2个字段，第一个是构建号，第二个是构建散列。例如:"select version();"可能会返回"2.1.0.2.5.0.0-1245 r027527b9c5ce1a3d7d0b6d2e6de2378fb0c39232"。实际结果将取决于您的构建 |
| bigint | surrogate_key([write_id_bits, task_id_bits]) | 在向表中输入数据时自动为行生成数字id。只能用作acid或只插入表的默认值 |
xpath
以下函数在LanguageManual XPathUDF中描述:
xpath, xpath_short, xpath_int, xpath_long, xpath_float, xpath_double, xpath_number, xpath_string
get_json_object
JSONPath支持一个限制版本：
$: Root object.: Child operator[]: 数组的下标操作符*: 用于[]通配符
不支持的语法值得注意：
- : Zero length string as key
..: Recursive descent@: Current object/element(): Script expression?(): Filter (script) expression.[,]: Union operator[start:end.step]: array slice operator
内置聚合函数(UDAF)
| 返回类型 | 返回类型 | 说明 |
|---|---|---|
| BIGINT | count(*), count(expr), count(DISTINCT expr[, expr...]) | count(*) - 返回检索行的总数，包括包含NULL值的行。count(expr) - 返回所提供表达式为非NULL的行数。count(DISTINCT expr[, expr]) - 返回所提供表达式唯一且非NULL的行数。可以使用hive.optimize.distinct.rewrite来优化执行 |
| DOUBLE | sum(col), sum(DISTINCT col) | 返回组中元素的和或组中列的不同值的和 |
| DOUBLE | avg(col), avg(DISTINCT col) | 返回组中元素的平均值或组中列的不同值的平均值 |
| DOUBLE | min(col) | 返回组中列的最小值 |
| DOUBLE | max(col) | 返回组中列的最大值 |
| DOUBLE | variance(col), 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(BIGINT col, p) | 返回组中某列的确切百分位数p(不适用于浮点类型)。p必须在0到1之间。注意:一个真正的百分位数只能计算整数值。如果输入是非整数，则使用PERCENTILE_APPROX。（p指定得到的分位数数值，取值范围为[0,1]，若为0.5则为中位数，若为0.75则为四分之三分位数，依此类推） |
| array | percentile(BIGINT col, array(p1 [, p2]...)) | 返回组中某列的确切百分位数p1, p2, ...(不适用于浮点类型)。pi必须在0到1之间。注意:一个真正的百分位数只能计算整数值。如果输入是非整数，则使用PERCENTILE_APPROX |
| DOUBLE | percentile_approx(DOUBLE col, p [, B]) | 返回组中数值列(包括浮点类型)的近似百分位数p。参数B以内存为代价控制近似精度。值越大，近似值越好，默认值为10000。当col中不同值的个数小于B时，会得到一个精确的百分位数 |
| array | percentile_approx(DOUBLE col, array(p1 [, p2]...) [, B]) | 与上面相同，但接受并返回一个百分位数数组，而不是一个 |
| double | regr_avgx(independent, dependent) | 相当于avg(dependent)。从Hive 2.2.0开始 |
| double | regr_avgy(independent, dependent) | 相当于avg(independent)。从Hive 2.2.0开始 |
| double | regr_count(independent, dependent) | 返回用于拟合线性回归线的非空对的个数。从Hive 2.2.0开始 |
| double | regr_intercept(independent, dependent) | 返回线性回归线的y截距，即等式中b的值dependent = a * independent + b。从Hive 2.2.0开始 |
| double | regr_r2(independent, dependent) | 返回回归的确定系数。从Hive 2.2.0开始 |
| double | regr_slope(independent, dependent) | 返回线性回归线的斜率，即等式中a的值dependent = a * independent + b。从Hive 2.2.0开始 |
| double | regr_sxx(independent, dependent) | 相当于regr_count(independent, dependent) * var_pop(dependent)。从Hive 2.2.0开始 |
| double | regr_sxy(independent, dependent) | 相当于regr_count(independent, dependent) * covar_pop(independent, dependent). 从Hive 2.2.0开始 |
| double | regr_syy(independent, dependent) | 相当于regr_count(independent, dependent) * var_pop(independent). 从Hive 2.2.0开始 |
array<struct {'x','y'}> | histogram_numeric(col, b) | 使用b个非均匀间隔的容器计算组中数字列的直方图。输出是一个大小为b的双值(x,y)坐标数组，表示容器的中心和高度 |
| array | collect_set(col) | 返回删除重复元素的对象集合 |
| array | collect_list(col) | 返回包含重复对象的列表（从Hive 0.13.0开始） |
| INTEGER | ntile(INTEGER x) | 将有序分区划分为x个组，称为桶，并为分区中的每一行分配一个桶号。这使得计算四分位数、十分位数、百分位数和其他常见汇总统计数据变得容易。(从Hive 0.11.0开始) |
内置表生成函数(UDTF)
| 行集列类型 | 返回类型 | 说明 |
|---|---|---|
| T | explode(ARRAY a) | 将数组分解为多行。返回一个单列(col)的行集，数组中的每个元素对应一行 |
| Tkey,Tvalue | explode(MAP<Tkey,Tvalue> m) | 将map拆分为多行。返回一个包含两列(key,value)的行集，来自输入map的每个键值对对应一行(从Hive 0.8.0开始) |
| int,T | posexplode(ARRAY a) | 将一个数组分解为多行，其中包含int类型的附加位置列(原始数组中元素的位置，从0开始)。返回一个包含两列(pos,val)的行集，数组中的每个元素对应一行 |
| T1,...,Tn | inline(ARRAY<STRUCT<f1:T1,...,fn:Tn>> a) | 将结构体数组拆分为多行。返回一个有N列的行集(N等于结构体中顶层元素的数量)，每一行对应数组中的一个结构体(从Hive 0.10开始) |
| T1,...,Tn/r | stack(int r,T1 V1,...,Tn/r Vn) | 分解n个值V1,... ,Vn成r行。每行有n/r列。r必须是常数 |
| string1,...,stringn | json_tuple(string jsonStr,string k1,...,string kn) | 接受一个JSON字符串和一组n个键，并返回一个包含n个值的元组。这是get_json_object UDF的一个更有效的版本，因为它只需调用一次就可以获得多个键 |
| string 1,...,stringn | parse_url_tuple(string urlStr,string p1,...,string pn) | 接受URL字符串和一组n个URL部分，并返回一个包含n个值的元组。这类似于parse_url() UDF，但可以一次从URL中提取多个部分。有效的部分名称是:HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY: |
Hive 自定义函数
自定义函数包括三种UDF、UDAF、UDTF
- UDF(User-Defined-Function) ：一进一出
- UDAF(User- Defined Aggregation Funcation) ：聚集函数，多进一出，如count/max/min
- UDTF(User-Defined Table-Generating Functions)：一进多出，如explore
UDF 开发
UDF函数可以直接应用于select语句，对查询结果做格式化处理后，再输出内容。自定义UDF函数可以通过java或python实现，使用java实现时，自定义UDF需要继承org.apache.hadoop.hive.ql.exec.UDF，并实现evaluate方法
下面操作是在node03节点，bigdata用户下操作
准备
score.txt数据如下
1 zs 100
2 ls 200
3 ww 300
4 ml 400
5 tq 500
-- 建表
create table person_score(id int,name string,score int) row format delimited fields terminated by '\t';
-- 加载数据
load data local inpath '/home/bigdata/score.txt' into table person_score;
使用python自定义UDF
使用python自定义Hive UDF函数，实现判断分数好坏，分数大于等于200 返回good，否则返回bad
myudf.py
# -*- coding: utf-8 -*-
import sys
for s in sys.stdin:
if int(s) > 300:
print("good")
else:
print("bad")
hive中操作如下
-- 将python udf文件加入到hive
add file /home/bigdata/myudf.py;
-- 查看添加的自定义脚本文件
list files;
/home/bigdata/myudf.py
-- 使用udf函数
-- 不支持，直接返回其它列，自定义函数时可以输入多列，然后输出多列，这样可以实现查询其它列
select id,name, transform(score) using 'python myudf.py' from person_score;
FAILED: ParseException line 1:14 missing EOF at ',' near 'name'
-- 查询时使用自定义函数，会调用MR执行，自定义函数输入是1列，输出也是1列
select transform(score) using 'python myudf.py' from person_score;
bad NULL
bad NULL
bad NULL
good NULL
good NULL
修改myudf.py脚本，接收一整行（输入），并返回自定义的多列（输出），实现查询其它列
# -*- coding: utf-8 -*-
import sys
for line in sys.stdin:
detail = line.strip().split("\t")
if len(detail) < 3:
continue
id = detail[0]
name = detail[1]
score = detail[2]
if int(score) >= 200:
print("\t".join([id,name,score,"good"]))
else:
print("\t".join([id,name,score,"bad"]))
-- 在hdfs中创建脚本目录，并上传脚本到该目录
dfs -mkdir -p /data/script/;
dfs -put /home/bigdata/myudf.py /data/script/;
-- 重新上传脚本，指定hdfs路径
add file hdfs://mycluster/data/script/myudf.py;
-- 使用自定义函数查询，自定义函数输入是多列，输出也是多列
select transform(id,name,score) using 'python myudf.py' as (id,name,age,xxx) from person_score;
1 zs 100 bad
2 ls 200 good
3 ww 300 good
4 ml 400 good
5 tq 500 good
注意add file只在当前会话中有效
使用java自定义UDF
参考本文档中"JDBC方式运行"中的"java"项目，这里使用maven打包，打包时依赖hive-exec和hadoop-common包，打成hive-test.jar
public class MyUdf1 extends UDF {
public Text evaluate(final Text text) {
if (text == null) {
return null;
}
int score = Integer.parseInt(text.toString());
if (score > 300) {
return new Text("good");
} else {
return new Text("bad");
}
}
}
public class MyUdf2 extends UDF {
public Text evaluate(final Text text) {
if (text == null) {
return null;
}
String line = text.toString();
String[] items = line.split("\t");
String id = items[0];
String name = items[1];
int score = Integer.parseInt(items[2]);
String scoreText;
if (score > 300) {
scoreText = "good";
} else {
scoreText = "bad";
}
return new Text(id + "\t" + name + "\t" + score + "\t" + scoreText);
}
}
将hive-test.jar上传至node03节点，通过bigdata用户进入hive cli操作
-- 添加jar包
add jar /home/bigdata/hive-test.jar;
Added [/home/bigdata/hive-test.jar] to class path
Added resources: [/home/bigdata/hive-test.jar]
-- 创建临时函数my_udf1
create temporary function my_udf1 AS 'org.example.hive.MyUdf1';
-- 与python自定义函数不一样，这个支持直接返回其它列
select id,name,my_udf1(score) from person_score;
1 zs bad
2 ls bad
3 ww bad
4 ml good
5 tq good
-- 创建临时函数my_udf2
create temporary function my_udf2 AS 'org.example.hive.MyUdf2';
-- 传入一整行数据时，与python中不一样，需要将非字符串字段转为字符串字段然后再拼接
-- java自定义UDF中evaluate方法可以接收多个参数，因此可以依次传入每个字段，这里不演示了
-- java自定义UDF可以与内置的UDF一样使用，支持直接返回其它列，因此没有必要传入所有的列，只需要传入需要的列即可
select my_udf2(concat_ws('\t',cast(id as string),name,cast(score as string))) from person_score;
1 zs 100 bad
2 ls 200 bad
3 ww 300 bad
4 ml 400 good
5 tq 500 good
上面这种方式创建的函数属于临时函数，当关闭了当前会话之后，函数会无法使用，因为jar的引用没有了，无法找到对应的java文件进行处理，因此不推荐使用。应该将jar包上传到hdfs，然后创建对应函数
-- 先要退出hive会话，重新进一下，使上面创建的临时函数my_udf1失效
-- 临时函数，在会话结束后就没有了
select id,name,my_udf1(score) from person_score;
FAILED: SemanticException [Error 10011]: Invalid function my_udf1
-- 在hdfs中创建目录，并上传jar包
dfs -mkdir -p /data/jar;
dfs -put /home/bigdata/hive-test.jar /data/jar/;
-- 创建my_udf1函数
create function my_udf1 AS 'org.example.hive.MyUdf1' using jar "hdfs://mycluster/data/jar/hive-test.jar";
-- 再退出hive会话，重新进一下
-- 查询正常，创建的函数在会话结束后依然存在
select id,name,my_udf1(score) from person_score;
1 zs bad
2 ls bad
3 ww bad
4 ml good
5 tq good
-- 删除函数，临时函数不能被drop删除，会话结束会自动删除
drop function my_udf1;
-- 创建函数之后可以在hive的元数据表中看到（node02节点mysql的hive库中）
select * from FUNCS;
+---------+-------------------------+-------------+-------+-----------+-----------+------------+------------+
| FUNC_ID | CLASS_NAME | CREATE_TIME | DB_ID | FUNC_NAME | FUNC_TYPE | OWNER_NAME | OWNER_TYPE |
+---------+-------------------------+-------------+-------+-----------+-----------+------------+------------+
| 3 | org.example.hive.MyUdf1 | 1711692602 | 1 | my_udf1 | 1 | bigdata | USER |
+---------+-------------------------+-------------+-------+-----------+-----------+------------+------------+
总结
hive自定义函数，可以使用java和python，二者使用上有很大的差异。hive的python自定义函数，需要通过add file添加，只在当前会话有效（一个会话中每次使用都需要执行add file），一般用于hql脚本文件，hql脚本文件中，在前面加载自定义函数，在后面的hql语句中使用自定义函数，hive的python自定义函数和内置函数的使用方式有很大的差异。hive的java自定义函数，可以永久添加到hive环境中，任何会话都可以使用，且使用方式与内置函数一样，一般非常通用的自定义函数才会使用java编写，用的没有python自定义函数多
实战案例
Struct案例
创建Struct结构体 student表，将如下数据(struct.txt)加载进表中，主要练习SQL语法与分割符号指定
1,zhangsan:18
2,lisi:19
3,wangwu:20
4,maliu:21
5,tianqi:22
-- 创建student表
Create table student(
id int,
info struct<name:String,age:int>
)
row format delimited fields terminated by ','
collection items terminated by ':';
-- 加载数据
load data local inpath '/home/bigdata/struct.txt' into table student
-- 查询
select id,info,info.name,info.age from student;
1 {"name":"zhangsan","age":18} zhangsan 18
2 {"name":"lisi","age":19} lisi 19
3 {"name":"wangwu","age":20} wangwu 20
4 {"name":"maliu","age":21} maliu 21
5 {"name":"tianqi","age":22} tianqi 22
基站信息统计
有如下数据(cell_info.csv)，各列分别表示，通话时间、基站编号、手机编号、掉话次数、掉话总秒长 ....
2023-07-13 00:00:00+08,356966,29448-37062,0,0,0,0,0,G,0
2023-07-13 00:00:00+08,352024,29448-51331,0,0,0,0,0,G,0
2023-07-13 00:00:00+08,353736,29448-51331,0,0,0,0,0,G,0
2023-07-13 00:00:00+08,353736,29448-51333,0,0,0,0,0,G,0
2023-07-13 00:00:00+08,351545,29448-51333,0,0,0,0,0,G,0
2023-07-13 00:00:00+08,353736,29448-51343,1,0,0,8,0,G,0
创建表jizhan，加载数据，统计对应的结果写入新表 jizhan_result , jizhan_result 表中只含有基站编号、掉话次数、掉话总时长
-- 创建jizhan表
create table jizhan(
record_time string,
imei int,
cell string,
ph_num int,
call_num int,
drop_num int,
duration int,
drop_rate double,
net_type string,
erl int)
row format delimited fields terminated by ',';
-- 加载数据
load data local inpath '/home/bigdata/cell_info.csv' into table jizhan;
-- 创建jizhan_result表
create table jizhan_result(
imei string,
total_drop_num int,
total_duration int
);
-- 查询数据并写入新表
insert into jizhan_result select imei,sum(drop_num) as total_dropnum,sum(duration) as total_dur from jizhan group by imei;
-- 查询新表数据
select * from jizhan_result;
351545 0 0
352024 0 0
353736 0 8
356966 0 0
使用Hive统计WordCount
准备如下数据(test01.txt)
hello zs
hello ls
hello ww
hello zs
hello ml
hello ml
hello ml
-- 创建表，并加载数据
create table test01(line string);
load data local inpath '/home/bigdata/test01.txt' into table test01;
-- 统计WordCount，将test01表中的字段line分割成数组，然后将该数组分解为多行；这样处理后的数据就是一行一个单词，使用group by就可以统计了
select word,count(*) as cnt from (select explode(split(line, ' ')) as word from test01) t group by word;
hello 7
ls 1
ml 3
ww 1
zs 2
-- 将WordCount统计结果写到结果表中
create table wc_result(word string,count int);
from (select explode(split(line, ' ')) word from test01) t insert into wc_result select word,count(*) group by word;
select * from wc_result;
hello 7
ls 1
ml 3
ww 1
zs 2
HQL统计不相交的结果集
使用SQL查询a，b表中不相交的数据集
a表数据(a.txt)
1 zhangsan
2 lisi
3 wangwu
4 maliu
5 tianqi
b表数据(b.txt)
1 zhangsan
2 lisi
3 wangwu
6 zhaoqi
7 gaoba
Hive 默认执行底层转换成MR执行，分布式执行，如果我们的数据量小，我们也可以设置Hive 本地模式，速度相对分布式执行要快
-- 创建表并加载数据
create table a (id int, name string) row format delimited fields terminated by '\t';
create table b like a;
load data local inpath '/home/bigdata/a.txt' into table a;
load data local inpath '/home/bigdata/b.txt' into table b;
-- 设置本地执行
set hive.exec.mode.local.auto=true;
-- 统计不相交的结果集
select a.id,a.name from a full outer join b on a.id = b.id where b.id is null
union
select b.id,b.name from a full outer join b on a.id = b.id where a.id is null;
4 maliu
5 tianqi
6 zhaoqi
7 gaoba
使用HQL统计如下结果
使用SQL根据表a1，表b1 计算出表c1
a1表数据(a1.txt)，date,v1
2023/1/1 74
2023/1/2 54
2023/1/3 19
2023/1/6 84
2023/1/7 2
2023/1/8 78
2023/1/9 19
2023/1/10 95
b1表数据(b1.txt)，date,v2
2023/1/1 13
2023/1/2 3
2023/1/3 29
2023/1/4 81
2023/1/5 37
2023/1/8 56
2023/1/9 53
2023/1/10 50
| 表c1 | ||
|---|---|---|
| date | v1 | v2 |
| 2023/1/1 | 74 | 13 |
| 2023/1/10 | 95 | 50 |
| 2023/1/2 | 54 | 3 |
| 2023/1/3 | 19 | 29 |
| 2023/1/4 | 0 | 81 |
| 2023/1/5 | 0 | 37 |
| 2023/1/6 | 84 | 0 |
| 2023/1/7 | 2 | 0 |
| 2023/1/8 | 78 | 56 |
| 2023/1/9 | 19 | 53 |
-- 创建表并加载数据
create table a1(`date` string,v1 int) row format delimited fields terminated by '\t';
create table b1(`date` string,v2 int) row format delimited fields terminated by '\t';
load data local inpath '/home/bigdata/a1.txt' into table a1;
load data local inpath '/home/bigdata/b1.txt' into table b1;
-- 查询类似于上面c1的结果，下面两个查询逻辑是一样的，只不过一个使用了if判断，一个使用了nvl函数
select if(a1.`date` is null, b1.`date`, a1.`date`) as dt, if(a1.v1 is null, 0, a1.v1) as v1, if(b1.v2 is null, 0, b1.v2) as v2 from a1 full join b1 on a1.`date` = b1.`date`;
select nvl(a1.`date`, b1.`date`) as dt, nvl(a1.v1, 0) as v1, nvl(b1.v2, 0) as v2 from a1 full join b1 on a1.`date` = b1.`date`;
2023/1/1 74 13
2023/1/10 95 50
2023/1/2 54 3
2023/1/3 19 29
2023/1/4 0 81
2023/1/5 0 37
2023/1/6 84 0
2023/1/7 2 0
2023/1/8 78 56
2023/1/9 19 53
