hive中字段长度函数_Hive常用函数总结

hive函数的分类

hive的客户端

显示当前会话有多少函数可用

SHOW FUNCTIONS;

如：

hive> show functions ;

OK

!

!=

%

显示函数的描述信息

DESC FUNCTION concat;

如：

hive> DESC FUNCTION concat;

OK

concat(str1, str2, ... strN) - returns the concatenation of str1, str2, ... strN or concat(bin1, bin2, ... binN) - returns the concatenation of bytes in binary data  bin1, bin2, ... binN

Time taken: 0.005 seconds, Fetched: 1 row(s)

显示函数的扩展描述信息

DESC FUNCTION EXTENDED concat;

一.简单函数

1.数学函数

返回对a四舍五入的BIGINT值

1 返回值：

2 hive> select round(2.5);

3 OK

4 3.0

5 Time taken: 0.093 seconds, Fetched: 1 row(s)

返回DOUBLE型d的保留n位小数的DOUBLW型的近似值 round(DOUBLE a, INT d)

1 返回值：double

2 hive> select round(0.5002,2);

3 OK

4 0.5

5 Time taken: 0.074 seconds, Fetched: 1 row(s)

银行家舍入法(1~4：舍，6~9：进，5->前位数是偶：舍，5->前位数是奇：进) bround(DOUBLE a)

返回值：double

bround(2.5) = 2, bround(3.5) = 4.

银行家舍入法,保留d位小数 bround(DOUBLE a, INT d)

1 返回值：double

2 bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4

向下取整，最数轴上最接近要求的值的左边的值  如：6.10->6   -3.4->-4 floor(DOUBLE a)

1 返回值：double

2 hive> select floor(6.10);

3 OK

4 6

5 Time taken: 0.07 seconds, Fetched: 1 row(s)

6 hive> select floor(-3.4);

7 OK

8 -4

9 Time taken: 0.104 seconds, Fetched: 1 row(s)

求其不小于小给定实数的最小整数如：ceil(6) = ceil(6.1)= ceil(6.9) = 6 ceil(DOUBLE a), ceiling(DOUBLE a)

1 返回值：BIGINT

2 hive> select ceil(6);

3 OK

4 6

5 Time taken: 0.2 seconds, Fetched: 1 row(s)

6 hive> select ceil(6.1);

7 OK

8 7

9 Time taken: 0.061 seconds, Fetched: 1 row(s)

10 hive> select ceil(6.9);

11 OK

12 7

13 Time taken: 0.153 seconds, Fetched: 1 row(s)

每行返回一个DOUBLE型随机数seed是随机因子 rand(), rand(INT seed)

1 返回值：DOUBLE

2 hive> select rand(2);

3 OK

4 0.7311469360199058

5 Time taken: 0.068 seconds, Fetched: 1 row(s)

6 hive> select rand();

7 OK

8 0.7859071491095923

9 Time taken: 0.064 seconds, Fetched: 1 row(s)

返回e的a幂次方， a可为小数 exp(DOUBLE a), exp(DECIMAL a)

1 返回值：double

2 hive> select exp(2);

3 OK

4 7.38905609893065

5 Time taken: 0.1 seconds, Fetched: 1 row(s)

以自然数为底d的对数，a可为小数 ln(DOUBLE a), ln(DECIMAL a)

1 返回值：double

2 ln(DOUBLE a), ln(DECIMAL a)

3 > select ln(3);

4 OK

5 1.0986122886681098

6 Time taken: 0.081 seconds, Fetched: 1 row(s)

7 hive> select ln(3.2);

8 OK

9 1.1631508098056809

10 Time taken: 0.067 seconds, Fetched: 1 row(s)

以10为底d的对数，a可为小数 log10(DOUBLE a), log10(DECIMAL a)

1 返回值：double

2 hive> select log10(3.2);

3 OK

4 0.505149978319906

5 Time taken: 0.084 seconds, Fetched: 1 row(s)

6 hive> select log10(3);

7 OK

8 0.47712125471966244

9 Time taken: 0.075 seconds, Fetched: 1 row(s)

以2为底数d的对数，a可为小数  log2(DOUBLE a), log2(DECIMAL a)

1 返回值：double

2 hive>

3 > select log2(3);

4 OK

5 1.5849625007211563

6 Time taken: 0.083 seconds, Fetched: 1 row(s)

7 hive> select log2(3.2);

8 OK

9 1.6780719051126378

10 Time taken: 0.07 seconds, Fetched: 1 row(s)

以base为底的对数，base 与 a都是DOUBLE类型

log(DOUBLE base, DOUBLE a)

log(DECIMAL base, DECIMAL a)

1 返回值：double

2 hive> select log(2,3.2);

3 OK

4 1.6780719051126378

5 Time taken: 0.084 seconds, Fetched: 1 row(s)

6 hive> select log(2,3);

7 OK

8 1.5849625007211563

9 Time taken: 0.066 seconds, Fetched: 1 row(s)

计算a的p次幂 pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p)

1 返回值:double

2 hive> select pow(2,4);

3 OK

4 16.0

5 Time taken: 0.065 seconds, Fetched: 1 row(s)

计算a的平方根 sqrt(DOUBLE a), sqrt(DECIMAL a)

1 返回值：double

2 select sqrt(2);

计算二进制a的STRING类型，a为BIGINT类型 bin(BIGINT a)

返回值：string

hive> select bin(2);

OK

10

Time taken: 0.194 seconds, Fetched: 1 row(s)

计算十六进制a的STRING类型，如果a为STRING类型就转换成字符相对应的十六进制 hex(BIGINT a) hex(STRING a) hex(BINARY a)

1 返回值：STRING

2 hive> select hex(2);

3 OK

4 2

5 Time taken: 0.097 seconds, Fetched: 1 row(s)

hex的逆方法

unhex(STRING a)

1 返回值：BINARY

2 hive> select unhex(2);

3 OK

4

5 Time taken: 0.077 seconds, Fetched: 1 row(s)

将GIGINT/STRING类型的num从from_base进制转换成to_base进制 conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base)

1 返回值：STRING

2 hive> select conv(2,10,2);

3 OK

4 10

5 Time taken: 0.075 seconds, Fetched: 1 row(s)

计算a的绝对值 abs(DOUBLE a)

1 返回值：DOUBLE

2 hive> select abs(-2);

3 OK

4 2

5 Time taken: 0.077 seconds, Fetched: 1 row(s)

a对b取模 pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b)

1 返回值：double

2 hive> select pmod(4,2);

3 OK

4 0

5 Time taken: 0.077 seconds, Fetched: 1 row(s)

求a的正弦值 sin(DOUBLE a), sin(DECIMAL a)

1 返回值：double

2 hive> select sin(2.5);

3 OK

4 0.5984721441039564

5 Time taken: 0.092 seconds, Fetched: 1 row(s)

求d的反正弦值 asin(DOUBLE a), asin(DECIMAL a)

1 返回值：double

2 hive> select asin(2.5);

3 OK

4 NaN

5 Time taken: 0.097 seconds, Fetched: 1 row(s)

求余弦值 cos(DOUBLE a), cos(DECIMAL a)

1 返回值：double

2 hive> select cos(2.5);

3 OK

4 -0.8011436155469337

5 Time taken: 0.087 seconds, Fetched: 1 row(s)

求反余弦值 acos(DOUBLE a), acos(DECIMAL a)

1 返回值：double

2 hive> select acos(2.5);

3 OK

4 NaN

5 Time taken: 0.091 seconds, Fetched: 1 row(s)

求正切值 tan(DOUBLE a), tan(DECIMAL a)

1 返回值：double

2 hive> select tan(2.5);

3 OK

4 -0.7470222972386603

5 Time taken: 0.076 seconds, Fetched: 1 row(s)

求反正切值 atan(DOUBLE a), atan(DECIMAL a)

1 返回值：double

2 hive> select atan(2.5);

3 OK

4 1.1902899496825317

5 Time taken: 0.074 seconds, Fetched: 1 row(s)

奖弧度值转换角度值 degrees(DOUBLE a), degrees(DECIMAL a)

1 返回值：DOUBLE

2 hive> select degrees(30);

3 OK

4 1718.8733853924698

5 Time taken: 0.114 seconds, Fetched: 1 row(s)

将角度值转换成弧度值 radians(DOUBLE a), radians(DOUBLE a)

1 返回值：double

2 hive> select radians(30);

3 OK

4 0.5235987755982988

5 Time taken: 0.093 seconds, Fetched: 1 row(s)

返回a positive(INT a), positive(DOUBLE a)

1 返回值：INT or DOUBLE

2 hive> select positive(2);

3 OK

4 2

5 Time taken: 0.124 seconds, Fetched: 1 row(s)

返回a的相反数 negative(INT a), negative(DOUBLE a)

1 返回值：double

2 hive> select negative(2);

3 OK

4 -2

5 Time taken: 0.066 seconds, Fetched: 1 row(s)

如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0 sign(DOUBLE a), sign(DECIMAL a)

1 返回值：DOUBLE or INT

2 hive> select sign(2);

3 OK

4 1.0

5 Time taken: 0.091 seconds, Fetched: 1 row(s)

数学常数e e()

1 返回值：double

2 hive> select e();

3 OK

4 2.718281828459045

5 Time taken: 0.07 seconds, Fetched: 1 row(s)

数学常数pi pi()

1 返回值：double

2 hive> select pi();

3 OK

4 3.141592653589793

5 Time taken: 0.082 seconds, Fetched: 1 row(s)

求a的阶乘 factorial(INT a)

1 返回值：BIGINT

2 select factorial(2);

求a的立方根 cbrt(DOUBLE a)

1 返回值：DOUBLE

2 select cbrt(2);

按位左移

shiftleft(TINYINT|SMALLINT|INT a, INT b)

shiftleft(BIGINT a, INT b)

1 返回值：int bigint

2 hive> select shiftleft(2,3);

按拉右移

shiftright(TINYINT|SMALLINT|INT a, INTb)

shiftright(BIGINT a, INT b)

1 返回值：INT BIGINT

2 hive> select shiftrigth(2,3);

无符号按位右移(<<

shiftrightunsigned(TINYINT|SMALLINT|INTa, INT b),

shiftrightunsigned(BIGINT a, INT b)

1 返回值：INT BIGINT

2 select shiftrightunsigned(2,3);

求最大值 greatest(T v1, T v2, ...)

1 返回值：T

2 hive> select greatest(2,3,6,7);

3 OK

4 7

5 Time taken: 0.072 seconds, Fetched: 1 row(s)

求最小值 least(T v1, T v2, ...)