mysql hive 内置函数_hive官方文档摘录

DDL Operations

1. 建表操作

hive> CREATE TABLE pokes (foo INT, bar STRING);

hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);

hive建表有个表分区的概念，PARTITIONED BY 为创建分区列，分区列不是数据本身，是虚拟的列。在hive的存储结构中，分区为数据路径的文件夹。

CREATE TABLE u_data (

userid INT,

movieid INT,

rating INT,

unixtime STRING)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

STORED AS TEXTFILE;

2.浏览表

hive> SHOW TABLES;

hive> SHOW TABLES '.*s';

hive> DESCRIBE invites;

3.修改删除表

ALTER TABLE invites REPLACE COLUMNS (foo INT COMMENT 'only keep the first column');

DROP TABLE pokes;

DML Operations

load加载数据

hive> LOAD DATA LOCAL INPATH './examples/files/kv1.txt' OVERWRITE INTO TABLE pokes;

在指定路径下，将数据加载到指定table中。

需要注意的是：

overwrite关键字，当table存在时，删除旧表并新建， 当没有写overwrite时，为追加写入数据表

在路径下的文件数据，应该与导入目标表的表结构一致，包括字段的数量、分隔符等，当overwrite时，可以忽略。

当表有分区时，需要加载路径时指定分区，使用partition()制定。

hive> LOAD DATA LOCAL INPATH './examples/files/kv3.txt' OVERWRITE INTO TABLE invites PARTITION (ds='2008-08-08');

与mysql相同的sql查询操作

1.通用sql查询数据，和mysql一样

hive> SELECT a.foo FROM invites a WHERE a.ds='2008-08-15';

2.将select的结果建表 insert … select

hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a;

hive> INSERT OVERWRITE TABLE events SELECT a.* FROM profiles a WHERE a.key < 100;

GROUP BY / JOIN，使用上和mysq上基本无差别。

hive内置文档手册

1.运算符

关系运算符 算数运算符 逻辑运算符

关系预算符用于比较，有 = , !=, ==, <= , is null, is not null, like ,rlike

算术运算符，+-*/ DIV，% ，位运算等

逻辑运算符 and or not in 等

2. 复杂类型

构造函数

构造函数操作数描述map(key1，value1，key2，value2，…)使用给定的键/值对创建一个映射。

struct(val1，val2，val3，…)用给定的字段值创建一个结构。结构字段名称将为col1，col2，…。

named_struct(name1, val1, name2, val2, …)用给定的字段名称和值创建一个结构。(从Hive 0.8.0开始。)

array(val1，val2，…)用给定的元素创建一个数组。

create_union(tag, val1, val2, …)使用tag参数指向的值创建联合类型。

复杂类型上的运算符

操作员操作数类型描述A[n]A是一个数组，n是一个整数返回数组A中的第n个元素。第一个元素的索引为0。例如，如果A是包含[‘foo’，’bar’]的数组，则A [0]返回’foo’，而A [1]返回’酒吧’。

M[key]M是Map 并且键的类型为K返回与映射中的键对应的值。例如，如果M是包含{‘f’->’foo’，’b’->’bar’，’all’->’foobar’}的映射，则M [‘all’]返回’foobar’。

S.xS是一个struct返回S的x字段。例如，对于结构foobar {int foo，int bar}，foobar.foo返回存储在结构的foo字段中的整数。

内置函数

1.数学函数

针对数字的计算，四舍五入，向上向下取整等

取对数、指数、进制计算等

round(), floor(), ceil()

exp(),ln(),log(),sqrt(),hex()

2.搜集函数

返回类型姓名(签名)描述intsize(Map)返回地图类型中的元素数。

intsize(Array)返回数组类型中的元素数。

arraymap_keys(Map)返回包含输入映射键的无序数组。

arraymap_values(Map)返回包含输入映射值的无序数组。

booleanarray_contains(Array, value)如果数组包含值，则返回TRUE。

arraysort_array(Array)根据数组元素的自然顺序对输入数组进行升序排序并返回(从0.9.0版本开始)。

array_contains(list, value) 函数，检测一个数组中，是否包含指定的value值，返回Ture False，用于if条件过滤。和split嵌套使用： if(array_contains(split(col_name,’, ‘), value) ) ，常用场景为一个字段中记录多条数据时，将字段先使用 split分割成数组，然后在用 array_contains函数判断是否包含目标value。和正则匹配的区别再也array_contains用于等值查询。

hive 中json处理

1.json内容提取，可以使用get_json_object 解析hive中json字符串的数据

get_json_object(data, ‘$.owner’) 解析json格式的字段， data字段为json数据， $.key 取key的值

2. str_to_map

json在hive中以string类型存储，用str_to_map 将string类型的json 转化成 map类型字段

3. map类型字段,可以使用搜集函数等，map_values 方式提取出字段的内容，map_keys提取key

3.类型转换函数

返回类型姓名(签名)描述binarybinary(string|binary)将参数转换为二进制。

Expected “=” to follow “type”cast(expr as )将表达式expr的结果转换为。例如，cast(’1’as BIGINT)会将字符串’1’转换为其整数表示。如果转换不成功，则返回null。如果cast(expr为boolean)，则Hive对于非空字符串返回true。

4.日期函数

日期时间提取函数

to_date(string timestamp) 返回字符串 时间戳的 日期部分，返回值string类型。

year(string date)、 month() 、 day() 、 hour() 、 minute() 、 second()、 分别返回日期/时间戳字符串的年月日时分秒数据 ，返回为int类型。

current_timestamp() 返回当前时间戳 ，current_date() 当前所在日期

last_day(string date)，返回string， 日前所在月份的最后一天

datediff(string enddate, string startdate) ， 返回int，两个日期时间差

date_add() date_sub() 日期加减

(UNIX_TIMESTAMP(dt,’ddMMyyyy’) 时间戳字符串提取日期

to_date(from_unixtime(UNIX_TIMESTAMP(dt,’ddMMyyyy’)))

5.条件函数

返回类型姓名(签名)描述Tif(boolean testCondition, T valueTrue, T valueFalseOrNull)当testCondition为true时返回valueTrue，否则返回valueFalseOrNull。

booleanisnull( a )如果a为NULL，则返回true，否则返回false。

booleanisnotnull ( a )如果a不为NULL，则返回true，否则返回false。

Tnvl(T value, T default_value)如果value为null，则返回默认值，否则返回值(从HIve 0.11开始)。

TCOALESCE(T v1, T v2, …)返回第一个不为NULL的v，如果所有v均为NULL，则返回NULL。

TCASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END当a = b时，返回c; 当a = d时，返回e; 否则返回f。

TCASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END当a = true时，返回b; 当c = true时，返回d; 否则返回e。

Tnullif( a, b )如果a = b，则返回NULL。否则返回a (从Hive 2.3.0开始)。简写：CASE，当a = b时为NULL，否则为a

voidassert_true(boolean condition)如果’condition’不为true，则引发异常，否则返回null(从Hive 0.8.0开始)。例如，选择assert_true(2 <1)。

6.字符串函数

concat() concat_ws() 字符串拼接

char_length() utf8字符长度； length() 返回字符长度

get_json_object() 根据指定的json路径从json字符串中提取json对象，并返回提取的json对象的json字符串。如果输入的json字符串无效，它将返回null。注意：json路径只能包含字符[0-9a-z_]，即不能包含大写或特殊字符。另外，键*不能以数字开头。*这是由于对Hive列名的限制

lower(string A)  返回将B的所有字符都转换为小写形式的字符串

lpad(string str, int len, string pad) 返回str，在其左边填充pad，长度为len。如果str大于len，则返回值缩短为len个字符。如果填充字符串为空，则返回值为null。

ltrim(string A) 、rtrim(string A) 、trim() 去除左右空格

substr(string, start, length) 截取字符串，起始位置设置。 -1，从后开始截取

7.加密函数

mask(string) 将字符串加密, 默认情况下，大写字母转换为“ X”，小写字母转换为“ x”，数字转换为“ n”。

mask(“ abcd-EFGH-8765-4321”)的结果为xxxx-XXXX-nnnn-nnnn

mask_first_n() \ mask_last_n() 对前n 后n项加密

8.聚合函数

如count、sum、min、max、avg、

percentile(BIGINT col, p)返回组中列的精确第p 个百分位数(不适用于浮点类型)。p必须在0到1之间。注意：只能为整数值计算真实百分位数。

percentile_approx()返回组中列的精确百分位数p 1，p 2，…(不适用于浮点类型)。p i必须在0到1之间。注意：只能为整数值计算真实百分位数。如果您输入的内容不是整数，请使用PERCENTILE_APPROX。

9.表函数

生成函数将单个输入行转换为多个输出行。

行集列类型姓名(签名)描述Texplode(ARRAY a)将数组分解为多行。返回带有单列(col)的行集，该数组代表数组中每个元素的一行。

Tkey,Tvalueexplode(MAP m)将地图分解为多行。返回一个行集合与两列(键，值)，一个行从输入图中的每个键-值对。(从Hive 0.8.0开始。)。

int,Tposexplode(ARRAY a)使用附加的int类型位置列将数组分解为多行(原始数组中项的位置，从0开始)。返回具有两列(pos，val)的行集，该数组中的每个元素一行。

T1,…,Tninline(ARRAY> a)将结构数组分解为多行。返回具有N列的行集(N =结构中顶级元素的数量)，数组中每个结构一行一行。(从Hive0.10开始。)

T1,…,Tn/rstack(int r,T1 V1,…,Tn/r Vn)将n个值V 1，…，V n分解为r行。每行将有n / r列。r必须是常数。

string1,…,stringnjson_tuple(string jsonStr,string k1,…,string kn)接收JSON字符串和一组n个键，并返回n个值的元组。这是get_json_objectUDF的一种更有效的版本，因为它只需一次调用就可以获取多个密钥。

string 1,…,stringnparse_url_tuple(string urlStr,string p1,…,string pn)接受URL字符串和一组n个URL部分，并返回n个值的元组。这类似于parse_url()UDF，但可以一次从URL中提取多个部分。有效的部件名称是：主机，路径，查询，参考，协议，授权，文件，用户信息，查询：。

10窗口函数