HIVE笔记

HIVE笔记基础数据类型基本数据类型复杂数据类型arraymapstruct数据类型转换字符串类型日期类型整型属性设置hive命令行hiveconf参数表和视图创建表查看表视图创建视图查看视图属性修改分区操作显示分区添加分区删除分区修改分区分桶操作字段操作添加列修改列删除列替换列表操作重命名修改表属性删除表查询基本case whencoalesce子查询exists 和in正则元字符正则匹配正则抽取正则替换正则分割连接outer joinleft [outer] joinright [outer] joinfull [outer] joininner joinmap join应用场景进阶muti joinleft semi join注意事项on和where执行顺序on逻辑运算on等值连接on不等值连接集合运算差集交集并集分组grouping setswith cubewith rollup函数库函数字符串字符串分割字符串截取字符串替换字符串抽取字符串拼接字符串包含数字计算日期时间基本操作日期运算数学函数-表和聚合grouping setswith cubewith rolluplateral viewexplode数据函数-分析cume_distrow_numberntilepercentilexx_rank()lag/leadfirst_value/last_value数学函数-累积累积移动平均数学函数-混合reflectUDF编解码类型转换分析函数反射JavaStreaming操作语句transformreduce实现pythonshellperl积累细节运行技巧注释和别名注释别名字段细节order by 字段group by 字段特殊处理NULL处理字典数组数组集合应用场景字典map类型json格式特殊处理http请求头请求url请求UAip处理文件名处理关键词过滤行列转换分列列转行explodelateral view行转列group_concatcollect_set[list]group by sum(if)inner join抽样GTopN抽样间隔抽样随机抽样分布宽格式长格式探索错位间隔优化设置优化参数优化数据倾斜解决方案动态分区合并小文件查询优化合并插入多表插入并行遍历动态分区组合长宽格式单表多表备份迁移结构迁移结构和数据迁移导入文件导入其它表导出本地文件命令行重定向直接写本地文件其它源导出到MySQL导出到ES问题面试hive支持不支持修改查询join时候字段非相等操作count distinct 和sumhive cube rollup分组问题order by,sort by, distribute by, cluster by区别order bysort bydistribute by和sort bycluster byreduce 卡在99%的数据倾斜问题参考

基础

数据类型

基本数据类型:tinyint, smallint, int,bigint, boolean, float, double, string

复杂数据类型:struct,map,array

基本数据类型
数据类型所占字节开始支持版本
TINYINT1byte,-128 ~ 127 
SMALLINT2byte,-32,768 ~ 32,767 
INT4byte,-2,147,483,648 ~ 2,147,483,647 
BIGINT8byte,-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807 
BOOLEAN  
FLOAT4byte单精度 
DOUBLE8byte双精度 
STRING字符串 
BINARY 从Hive0.8.0开始支持
TIMESTAMP 从Hive0.8.0开始支持
DECIMAL 从Hive0.11.0开始支持
CHAR 从Hive0.13.0开始支持
VARCHAR 从Hive0.12.0开始支持
DATE 从Hive0.12.0开始支持

string

num

复杂数据类型
array

数组索引

问题是无法对数据采用-1,-2这样的倒序查(已解决),例子:

注意在shell中要换成8个(在hql脚本中和在hive的命令行中保持一样,都是4个),如下:

例子:

数组统计

map

map数据的插入

map字段插入空:

例子:

注意:

map操作

struct

例子:

综合例子:

数据类型转换

隐式数据类型转换

字符串类型

字符串转整型

日期类型

时间戳转时间戳字符串

时间戳转日期字符串

整型

整型转字符串

进制转换

属性设置

hive命令行
hiveconf参数
功能语法
设置job名称mapred.job.name=complatstat_hive
数据压缩是否开启hive.exec.compress.output=true
中间结果是否压缩hive.exec.compress.intermediate=true
Sequencefile压缩级别io.seqfile.compression.type=BLOCK
输出压缩方式mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec
在map端做部分聚集hive.map.aggr=true
HDFS路径, 存储不同 map/reduce 阶段执行计划和中间输出结果hive.exec.scratchdir=/user/complat/tmp
队列名mapred.job.queue.name=complat
支持动态分区hive.exec.dynamic.partition=true
允许所有的分区列都是动态分区列hive.exec.dynamic.partition.mode=nonstrict
执行map前开启小文件合并hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat
是否根据输入表的大小自动转成map joinhive.auto.convert.join=false
hive.groupby.skewindata当数据出现倾斜时,如果该变量设置为true,那么Hive会自动进行负载均衡。

表和视图

创建表

•CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXIST 选项来忽略这个异常 •EXTERNAL 关键字可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION) •LIKE 允许用户复制现有的表结构,但是不复制数据 •COMMENT可以为表与字段增加描述

•ROW FORMAT DELIMITED [FIELDS TERMINATED BY char][COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char][LINES TERMINATED BY char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)] 用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED,将会使用自带的 SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的 SerDe,Hive 通过 SerDe 确定表的具体的列的数据。 •STORED AS SEQUENCEFILE | TEXTFILE | RCFILE | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname 如果文件数据是纯文本,可以使用 STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCE 。

创建库的时候最好指定下库的路径,建立内部表的时候不建议指定表的location的位置,如下格式:

分割符

文本格式

外部表

内部表

只有文本存储格式才能使用load data local inpath的方法进行数据加载填充,否则则使用load data的时候报错如下:

序列化格式

外部表

内部表

 

查看表

表结构

表容量

视图
创建视图

视图是只读的,不能向视图中插入或是加载数据,视图可以被定义为多个表的连接,也可以被定义为只有部分列可见,也可为部分行可见。

​ 首先,可以简化数据查询语句

​ 其次,可以使用用户能从多角度看待同一数据

​ 然后,通过引入视图可以提高数据的安全性

​ 最后,视图提提供了一定程度的逻辑独立性等。

​ 通过引入视图机制,用户可以将注意力集中在其关心的数据上(而非全部数据),这样就大大提高了用户效率与用户满意度,而且如果这些数据来源于多个基本表结构,或者数据不仅来自于基本表结构,还有一部分数据来源于其他视图,并且搜索条件又比较复杂时,需要编写的查询语句就会比较烦琐,此时定义视图就可以使数据的查询语句变得简单可行。

​ 定义视图可以将表与表之间的复杂的操作连接和搜索条件对用户不可见,用户只需要简单地对一个视图进行查询即可,故增加了数据的安全性,但不能提高查询效率。

查看视图

属性修改

分区操作
显示分区
添加分区

使用insert into插入的时候需要指定所有的分区,不然提示失败,也即不能留空白分区(动态分区除外)

动态分区和静态分区

删除分区

问题:为什么在insert overwrite table之前一般都先使用这个语句删除对应的分区

修改分区

分区综合例子:

动态分区:

但要注意:动态分区必须在静态分区之后,不能先动态分区然后接着静态分区,例如:

会报错:

分桶操作

//分桶操作只在很少的情况下被使用,后面再补充

字段操作
添加列

添加列是添加到表的最后,但在分区之前

注意hive不支持直接添加列到指定位置,解决方案是先添加列,然后再修改列到指定的位置

添加列并设置为分区列:

修改列
删除列

备注:使用此种方法并不能删除列,具体的列删除方法参考,只能使用列替换的方式进行删除

替换列
表操作

参数设置,修改,存储格式,分桶

重命名
修改表属性

修改存储格式

只修改表的存储格式不会影响已有的分区,只会影响新建的分区,如果有需要,已有分区也必须执行修改。

修改字段分割方式

只修改表的字段分隔符不会影响已有分区,只会影响新建的分区,如果有需要,已有分区也必须执行修改。

删除表

对外部表(存储在本地文件系统)和内部表(存储在MetaStore),删除语句相同

删除的时候会连分区和文件(内部表)一起删除

删除表的部分分区

删除分区的时候必须逐层指定,删除顶层分区的话,其子分区也会一并删除

查询

基本

不嵌套,只使用最基本的,无连接,主要是注意积累函数的使用

case when

case when .. then .. else .. end as ..

两种用法

例子1:

例子2:

coalesce

COALESCE( value1,value2,... )

The COALESCE function returns the fist not NULL value from the list of values. If all the values in the list are NULL, then it returns NULL.

如何返回数组中的第一个非NULL元素,配合collect_list使用?取聚合后第一个非空元素

子查询

子查询要和连接一起学习

exists 和in

hive中支持exists和in,但不支持exists和in的子查询,代替方案是left semi join

例子2:

正则

元字符

hive中的正则转义使用两个反斜杠, 即‘//’,

基础元字符1:

元字符含义备注
.任何字符 
?a?表示a出现一次或一次也没有扩展正则
*a*表示a出现零次或多次 
+a+表示a出现一次或多次扩展正则
{n}a{n}表示a恰好出现n次 
a{n,}a{n,}表示a至少出现n次 
a{n,m}a{n,m}表示a至少出现n次,不超过m次 
|两者取其一:Java|Hello 表示 Java或Hello扩展正则
[][abc]表示a或b或c 
[^][^abc]表示除了a、b、c、以外的字符 
[a-zA-Z]表示任何大小写字符 
[a-d[m-p]]表示a到d或m-p的字符;并集 
[a-z&&[def]]d、e 或 f(交集) 
[a-z&&[^bc]]a 到 z,除了 b 和 c:[ad-z](减去) 
[a-z&&[^m-p]]a 到 z,而非 m 到 p:[a-lq-z](减去) 
^行的开头 
$行的结尾 

基础元字符2:

元字符含义备注
\d数字:[0-9] 
\D非数字: [^0-9] 
\s空白字符 
\S非空白字符:[^\\s] 
\w单词字符:[a-zA-Z_0-9] 
\W非单词字符:[^\w] 
(?i)这个标志能让表达式忽略大小写进行匹配 
(?x)在这种模式下,匹配时会忽略(正则表达式里的)空格字符,例如空格,tab,回车之类 

例子:

扩展字符

正则匹配
正则抽取

版本号

网址

电话号码

文件和路径

提取中文

提取指定位置

等效的另外方法:

 

注:正则抽取中的贪婪匹配

正则替换

例子:

正则分割

一个综合正则例子:

连接

连接是在查询中最广泛使用的,但要注意数据倾斜问题,而且join中on的字段不能放在where中

outer join
left [outer] join

left join(左连接):返回两个表中连结字段相等的行和左表中的行;

左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID),B表记录不足的地方均为NULL.

right [outer] join

right join(右连接):返回包括右表中的所有记录和左表中连接字段相等的记录。

right outer join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.

full [outer] join

全外连接

inner join

inner join(等值连接):只返回两个表中连接字段相等的行, inner join 有时简写为join

例子:求交集

map join

mapjoin会把小表全部读入内存中,在map阶段直接拿另外一个表的数据和内存中表的数据做匹配,而普通的equality join则是类似于mapreduce中的filejoin,需要先分组,然后在reduce端进行连接,由于mapjoin是在map是进行了join操作,省去了reduce的运行,效率也会高很多

mapjoin还有一个很大的好处是能够进行不等连接的join操作,如果将不等条件写在where中,那么mapreduce过程中会进行笛卡尔积,运行效率特别低,这是由于equality join (不等值join操作有 >、<、like等如:a.x < b.y 或者 a.x like b.y) 需要在reduce端进行不等值判断,map端只能过滤掉where中等值连接时候的条件,如果使用mapjoin操作,在map的过程中就完成了不等值的join操作,效率会高很多。

应用场景

例子:

该语句中t2表有30亿行记录,t1表只有100行记录,而且t2表中数据倾斜特别严重,有一个key上有15亿行记录,在运行过程中特别的慢,而且在reduece的过程中遇有内存不够而报错

进阶

连接小表的时候,在内存中操作,省去reduce过程,和common join存在着差别

使用样例:

muti join

多表join , 优化代码结构

关联条件相同多表join会优化成一个job

left semi join

left semi join是可以高效实现in/exists子查询的语义

例子:

例子2:

注意事项

主要处理on的多条件和where顺序

on和where执行顺序

on和where顺序

on逻辑运算

目前在hive的join中只支持and操作,不支持or操作,提示如下:

on等值连接

//待补充

on不等值连接

两个表join的时候,不支持两个表的字段的非相等操作.

例1:

例2:

集合运算

集合运算是通过join运算实现的

差集
交集
并集

分组

grouping sets
with cube

with cube是所有组合方式,及全组合

with rollup

以最左侧的维度为准,进行层级聚合

withe rollup是with cube的子集

函数

库函数

字符串

字符串处理函数一览

返回类型函数名描述
intascii(string str)返回str第一个字符串的数值
stringbase64(binary bin)将二进制参数转换为base64字符串
stringconcat(string|binary A, string|binary B...)返回将A和B按顺序连接在一起的字符串,如:concat('foo', 'bar') 返回'foobar'
array<struct<string,double>>context_ngrams(array<array>, array, int K, int pf)从一组标记化的句子中返回前k个文本
stringconcat_ws(string SEP, string A, string B...)类似concat() ,但使用自定义的分隔符SEP
stringconcat_ws(string SEP, array)类似concat_ws() ,但参数为字符串数组
stringdecode(binary bin, string charset)使用指定的字符集将第一个参数解码为字符串,如果任何一个参数为null,返回null。可选字符集为: 'US_ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'
binaryencode(string src, string charset)使用指定的字符集将第一个参数编码为binary ,如果任一参数为null,返回null
intfind_in_set(string str, string strList)返回str在strList中第一次出现的位置,strList为用逗号分隔的字符串,如果str包含逗号则返回0,若任何参数为null,返回null。如: find_in_set('ab', 'abc,b,ab,c,def') 返回3
stringformat_number(number x, int d)将数字x格式化为'#,###,###.##',四舍五入为d位小数位,将结果做为字符串返回。如果d=0,结果不包含小数点或小数部分
stringget_json_object(string json_string, string path)从基于json path的json字符串中提取json对象,返回json对象的json字符串,如果输入的json字符串无效返回null。Json 路径只能有数字、字母和下划线,不允许大写和其它特殊字符
booleanin_file(string str, string filename)如果str在filename中以正行的方式出现,返回true
intinstr(string str, string substr)返回substr在str中第一次出现的位置。若任何参数为null返回null,若substr不在str中返回0。Str中第一个字符的位置为1
intlength(string A)返回A的长度
intlocate(string substr, string str[, int pos])返回substr在str的位置pos后第一次出现的位置
stringlower(string A) lcase(string A)返回字符串的小写形式
stringlpad(string str, int len, string pad)将str左侧用字符串pad填充,长度为len
stringltrim(string A)去掉字符串A左侧的空格,如:ltrim(' foobar ')的结果为'foobar '
array<struct<string,double>>ngrams(array<array>, int N, int K, int pf)从一组标记化的Returns the top-k 句子中返回前K个N-grams
stringparse_url(string urlString, string partToExtract [, string keyToExtract])返回给定URL的指定部分,partToExtract的有效值包括HOST,PATH, QUERY, REF, PROTOCOL, AUTHORITY,FILE和USERINFO。例如: parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') 返回 'facebook.com'.。当第二个参数为QUERY时,可以使用第三个参数提取特定参数的值,例如: parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1','QUERY', 'k1') 返回'v1'
stringprintf(String format, Obj... args)将输入参数进行格式化输出
stringregexp_extract(string subject, string pattern, int index)使用pattern从给定字符串中提取字符串。如: regexp_extract('foothebar', 'foo(.*?)(bar)', 2) 返回'bar' 有时需要使用预定义的字符类:使用'\s' 做为第二个参数将匹配s,'s'匹配空格等。参数index是Java正则匹配器方法group()方法中的索引
stringregexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)使用REPLACEMENT替换字符串INITIAL_STRING中匹配PATTERN的子串,例如: regexp_replace("foobar", "oo|ar", "") 返回'fb'
stringrepeat(string str, int n)将str重复n次
stringreverse(string A)将字符串A翻转
stringrpad(string str, int len, string pad)在str的右侧使用pad填充至长度len
stringrtrim(string A)去掉字符串A右侧的空格,如: rtrim(' foobar ') 返回 ' foobar'
array<array>sentences(string str, string lang, string locale)将自然语言文本处理为单词和句子,每个句子在适当的边界分割,返回单词的数组。参数lang和local为可选参数,例如: sentences('Hello there! How are you?') 返回( ("Hello", "there"), ("How", "are", "you") )
stringspace(int n)返回n个空格的字符串
arraysplit(string str, string pat)用pat分割字符串str,pat为正则表达式
map<string,string>str_to_map(text[, delimiter1, delimiter2])使用两个分隔符将文本分割为键值对。第一个分隔符将文本分割为K-V 对,第二个分隔符分隔每个K-V 对。默认第一个分隔符为““,第二个分隔符为=
stringsubstr(string|binary A, int start) substring(string|binary A, int start)返回A从位置start直到结尾的子串
stringsubstr(string|binary A, int start, int len) substring(string|binary A, int start, int len)返回A中从位置start开始,长度为len的子串,如: substr('foobar', 4, 1) 返回 'b'
stringtranslate(string input, string from, string to)将input中出现在from中的字符替换为to中的字符串,如果任何参数为null,结果为null
stringtrim(string A)去掉字符串A两端的空格
binaryunbase64(string str)将base64字符串转换为二进制
stringupper(string A) ucase(string A)返回字符串A的大写形式

额外函数

函数用法备注
xl_md5('aacw')Returns the md5 value of str 
   
   

一般函数

字符串分割

注意:

字符串截取
字符串替换
字符串抽取
字符串拼接
字符串包含

判断一个字符串是否包含另一个字符串,也可以归结为字符串匹配问题

hive join实现的字符串匹配

这个方法可以用来实现多条件筛选哈

数字计算
Return TypeName (Signature)Description
DOUBLEround(DOUBLE a)Returns the rounded BIGINT value of a.返回对a四舍五入的BIGINT值
DOUBLEround(DOUBLE a, INT d)Returns a rounded to d decimal places.返回DOUBLE型d的保留n位小数的DOUBLW型的近似值
DOUBLEbround(DOUBLE a)Returns the rounded BIGINT value of a using HALF_EVEN rounding mode (as of Hive 1.3.0, 2.0.0). Also known as Gaussian rounding or bankers' rounding. Example: bround(2.5) = 2, bround(3.5) = 4.银行家舍入法(14:舍,69:进,5->前位数是偶:舍,5->前位数是奇:进)
DOUBLEbround(DOUBLE a, INT d)Returns a rounded to d decimal places using HALF_EVEN rounding mode (as of Hive 1.3.0, 2.0.0). Example: bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4.银行家舍入法,保留d位小数
BIGINTfloor(DOUBLE a)Returns the maximum BIGINT value that is equal to or less than a向下取整,最数轴上最接近要求的值的左边的值 如:6.10->6 -3.4->-4
BIGINTceil(DOUBLE a), ceiling(DOUBLE a)Returns the minimum BIGINT value that is equal to or greater than a.求其不小于小给定实数的最小整数如:ceil(6) = ceil(6.1)= ceil(6.9) = 6
DOUBLErand(), rand(INT seed)Returns a random number (that changes from row to row) that is distributed uniformly from 0 to 1. Specifying the seed will make sure the generated random number sequence is deterministic.每行返回一个DOUBLE型随机数seed是随机因子
DOUBLEexp(DOUBLE a), exp(DECIMAL a)Returns ea where e is the base of the natural logarithm. Decimal version added in Hive 0.13.0.返回e的a幂次方, a可为小数
DOUBLEln(DOUBLE a), ln(DECIMAL a)Returns the natural logarithm of the argument a. Decimal version added in Hive 0.13.0.以自然数为底d的对数,a可为小数
DOUBLElog10(DOUBLE a), log10(DECIMAL a)Returns the base-10 logarithm of the argument a. Decimal version added in Hive 0.13.0.以10为底d的对数,a可为小数
DOUBLElog2(DOUBLE a), log2(DECIMAL a)Returns the base-2 logarithm of the argument a. Decimal version added in Hive 0.13.0.以2为底数d的对数,a可为小数
DOUBLElog(DOUBLE base, DOUBLE a)log(DECIMAL base, DECIMAL a)Returns the base-base logarithm of the argument a. Decimal versions added in Hive 0.13.0.以base为底的对数,base 与 a都是DOUBLE类型
DOUBLEpow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p)Returns ap.计算a的p次幂
DOUBLEsqrt(DOUBLE a), sqrt(DECIMAL a)Returns the square root of a. Decimal version added in Hive 0.13.0.计算a的平方根
STRINGbin(BIGINT a)Returns the number in binary format (see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_bin).计算二进制a的STRING类型,a为BIGINT类型
STRINGhex(BIGINT a) hex(STRING a) hex(BINARY a)If the argument is an INT or binary, hex returns the number as a STRING in hexadecimal format. Otherwise if the number is a STRING, it converts each character into its hexadecimal representation and returns the resulting STRING. (Seehttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex, BINARY version as of Hive 0.12.0.)计算十六进制a的STRING类型,如果a为STRING类型就转换成字符相对应的十六进制
BINARYunhex(STRING a)Inverse of hex. Interprets each pair of characters as a hexadecimal number and converts to the byte representation of the number. (BINARY version as of Hive 0.12.0, used to return a string.)hex的逆方法
STRINGconv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base)Converts a number from a given base to another (see http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv).将GIGINT/STRING类型的num从from_base进制转换成to_base进制
DOUBLEabs(DOUBLE a)Returns the absolute value.计算a的绝对值
INT or DOUBLEpmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b)Returns the positive value of a mod b.a对b取模
DOUBLEsin(DOUBLE a), sin(DECIMAL a)Returns the sine of a (a is in radians). Decimal version added in Hive 0.13.0.求a的正弦值
DOUBLEasin(DOUBLE a), asin(DECIMAL a)Returns the arc sin of a if -1<=a<=1 or NULL otherwise. Decimal version added in Hive 0.13.0.求d的反正弦值
DOUBLEcos(DOUBLE a), cos(DECIMAL a)Returns the cosine of a (a is in radians). Decimal version added in Hive 0.13.0.求余弦值
DOUBLEacos(DOUBLE a), acos(DECIMAL a)Returns the arccosine of a if -1<=a<=1 or NULL otherwise. Decimal version added in Hive 0.13.0.求反余弦值
DOUBLEtan(DOUBLE a), tan(DECIMAL a)Returns the tangent of a (a is in radians). Decimal version added in Hive 0.13.0.求正切值
DOUBLEatan(DOUBLE a), atan(DECIMAL a)Returns the arctangent of a. Decimal version added in Hive 0.13.0.求反正切值
DOUBLEdegrees(DOUBLE a), degrees(DECIMAL a)Converts value of a from radians to degrees. Decimal version added in Hive 0.13.0.奖弧度值转换角度值
DOUBLEradians(DOUBLE a), radians(DOUBLE a)Converts value of a from degrees to radians. Decimal version added in Hive 0.13.0.将角度值转换成弧度值
INT or DOUBLEpositive(INT a), positive(DOUBLE a)Returns a.返回a
INT or DOUBLEnegative(INT a), negative(DOUBLE a)Returns -a.返回a的相反数
DOUBLE or INTsign(DOUBLE a), sign(DECIMAL a)Returns the sign of a as '1.0' (if a is positive) or '-1.0' (if a is negative), '0.0' otherwise. The decimal version returns INT instead of DOUBLE. Decimal version added in Hive 0.13.0.如果a是正数则返回1.0,是负数则返回-1.0,否则返回0.0
DOUBLEe()Returns the value of e.数学常数e
DOUBLEpi()Returns the value of pi.数学常数pi
BIGINTfactorial(INT a)Returns the factorial of a (as of Hive 1.2.0). Valid a is [0..20].求a的阶乘
DOUBLEcbrt(DOUBLE a)Returns the cube root of a double value (as of Hive 1.2.0).求a的立方根
INT BIGINTshiftleft(TINYINT|SMALLINT|INT a, INT b)shiftleft(BIGINT a, INT b)Bitwise left shift (as of Hive 1.2.0). Shifts a b positions to the left.Returns int for tinyint, smallint and int a. Returns bigint for bigint a.按位左移
INTBIGINTshiftright(TINYINT|SMALLINT|INT a, INTb)shiftright(BIGINT a, INT b)Bitwise right shift (as of Hive 1.2.0). Shifts a b positions to the right.Returns int for tinyint, smallint and int a. Returns bigint for bigint a.按拉右移
INTBIGINTshiftrightunsigned(TINYINT|SMALLINT|INTa, INT b),shiftrightunsigned(BIGINT a, INT b)Bitwise unsigned right shift (as of Hive 1.2.0). Shifts a b positions to the right.Returns int for tinyint, smallint and int a. Returns bigint for bigint a.无符号按位右移(<<<)
Tgreatest(T v1, T v2, ...)Returns the greatest value of the list of values (as of Hive 1.1.0). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with ">" operator (as of Hive 2.0.0).求最大值
Tleast(T v1, T v2, ...)Returns the least value of the list of values (as of Hive 1.1.0). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with "<" operator (as of Hive 2.0.0).求最小值
日期时间
基本操作

hive日期计算精确到毫秒:

其中t1是10位的时间戳值,即1970-1-1至今的秒,而13位的所谓毫秒的是不可以的。

对于13位时间戳,需要截取,然后转换成bigint类型,因为from_unixtime类第一个参数只接受bigint类型。例如

秒和毫秒组合:

时间区间

日期运算
数学函数-表和聚合
grouping sets

在一个GROUP BY查询中,根据不同的维度组合进行聚合,等价于将不同维度GROUP BY结果集进行UNION ALL

注:

with cube

根据GROUP BY的维度的所有组合进行聚合。

注意:

所有组合进行聚合的时候,存在都为null的情况,强烈建议在事实表层队null值进行集中处理

with rollup

是CUBE的子集,以最左侧的维度为主,从该维度进行层级聚合

注意:

曾经聚合的时候第一行为null

lateral view

explode将复杂结构一行拆成多行,然后再用lateral view做各种聚合

原始数据

转行数据

展开之后聚合

explode

explode就是将hive一行中复杂的array或者map结构拆分成多行

explode不能和其它列混合使用,例如

若要混合,请使用类似map的操作方式:

将一个map类型按k,v展开并和其它列进行组合成行

例子解析:(还有问题)

数据函数-分析
cume_dist

小于等于当前值的行数/分组内的总行数,也就是分组百分比

row_number

ROW_NUMBER() 从1开始,按照顺序,生成分组内记录的序列,比如,按照pv降序排列,生成分组内每天的pv名次

row number可以用来进行分组去重

ntile

NTILE(n),用于将分组数据按照顺序切分成n片,返回当前切片值

percentile

计算分位数的函数percentile和percentile_approx,percentile要求输入的字段必须是int类型的,而percentile_approx则是数值类似型的都可以 ,返回分位点对应的记录值(该记录值不一定在记录中)。

格式

方法

注意使用前要先对要操作的列进行排序,然后可以根据排序确定分位数。

xx_rank()

rank系列:rank()/dense_rank()/percent_rank()

rank()

扩展1:不分区排序(整体排序)

扩展2:多层分组的组内排序(有问题)

比如1~3号,每天按app按城市取top10,即每个app的前十个城市

dense_rank()

percent_rank()

lag/lead

lag

lead

first_value/last_value

first_value取分组内排序后,截止到当前行,第一个值,last_value取分组内排序后,截止到当前行,最后一个值

数学函数-累积

计算一定范围内、一定值域内或者一段时间内的累积和以及移动平均值等

累积

累积条数

注意:select的非移动窗口计算列必须在group by中,否则报错:

移动平均

结果如下:

小时数量累积和前后一小时和累积均值前3小时均值
0064166416102006416.006416.00
01378410200125505100.005100.00
0223501255077784183.334183.33
0316441419454273548.503548.50
0414331562741653125.402302.75
0510881671539372785.831628.75
0614161813151672590.141395.25
0726632079498822599.251650.00
08580326597172252955.222742.50
09875935356254783535.604660.25
101091646272313534206.557035.25
111167857950354504829.179289.00
121285670806376335446.6211052.25
131309983905388885993.2112137.25
141293396838395576455.8712641.50
1513525110363406386897.6913103.25
1614180124543412377326.0613434.25
1713532138075417697670.8313542.50
1814057152132433438006.9513823.50
1915754167886477808394.3014380.75
2017969185855518138850.2415328.00
2118090203945513959270.2316467.50
2215336219281440339533.9616787.25
2310607229888259439578.6715500.50
数学函数-混合
reflect

反射java库的函数

UDF

udf和streaming的区别在于udf必须是在hadoop平台上的文件,而streaming要求的则是本地文件

编解码

原生

平台提供

自定义

类型转换

str->map

str_to_map(strings ,delim1,delim2), delin1键值对分隔符,delim2键值分隔符

str->array

split(strings,pattern)

array->str

concat_ws(delim,ARRAY arr)

以上均是自带的,以下是扩展:

array->map

分析函数
反射Java

hive中提供了reflect函数来调用Java现有库中的方法,调用方法如下:

Streaming操作

hadoop streaming api为外部进程开始I/O管道,数据被传输给外部进程,外部进程从标准输入中读数据,然后将结果数据写入到标准输出。

注意:

streaming过程使用到的文件都是本地文件,不需要上传到hadoop集群上

优点:

缺点:

语句
transform

结合insert overwrite 使用transform

可以直接将经过处理后的文件进行处理后导出到本地

reduce

例子1:

例子2:

处理后插入到新表中

实现
python
shell

shell脚本:hive_streaming.sh

hive语句:hive_streaming.hql

perl

积累

细节

运行技巧

命令行和重定向

注释和别名
注释

hql脚本注释

对比mysql的注释xxx.sql

别名

中文别名

使用位置

字段细节
order by 字段

order by 是最后执行的,若对列(包含计算列)没有起别名,则_c0,_c1,_c2分别对应相应的列

在orderby中可以使用别名

group by 字段

group by中字段要严格和select部分的字段一致,包括substr,if,case等的处理,都要在group by中体现出来

在group by中不能使用别名

特殊处理
NULL处理

字典数组

数组集合

函数一览

Return TypeName(Signature)Description
intsize(Map<K.V>)Returns the number of elements in the map type.
intsize(Array)Returns the number of elements in the array type.
arraymap_keys(Map<K.V>)Returns an unordered array containing the keys of the input map.
arraymap_values(Map<K.V>)Returns an unordered array containing the values of the input map.
booleanarray_contains(Array, value)Returns TRUE if the array contains value.
arraysort_array(Array)Sorts the input array in ascending order according to the natural ordering of the array elements and returns it (as of version 0.9.0).
arraycollect_set(col)Returns a set of objects with duplicate elements eliminated.
arraycollect_list(col)Returns a list of objects with duplicates. (As of Hive 0.13.0.)
intfind_in_set(string str,string strlist)返回str在strlist第一次出现的位置,strlist是用逗号分割的字符串。如果没有找该str字符,则返回0

例子:

应用场景

数组元素拼接

数组中的每个元素转换成字符串

数组排序

数组最大值和最小值(不展开)

数组均值、中位数、最大值、最小值

经过explode拆分的列可以直接参与select部分的计算,方法2主要用于验证

数组中元素出现的个数

字典
map类型

函数一览:

函数名用法备注
map_keysmap_keys(xx)返回map所有key
map_valuesmap_values(yy)返回map所有value
   

额外函数:

函数名用法备注
xl_map_get(map,key)select nvl(m2["k1"],''),nvl(xl_map_get(m2,"k1"),'') from high_test;获取map中指定的key值
xl_map_tag(v,map())select xl_map_tag('push_new',map('^1ps232','1','.*push.*','2')); --2根据字段的值进行匹配,翻译成不同的值,类似维表翻译
xl_str_tag(x,x)还有问题待升级 

取值

转化

合并和分解

从map类型中选出指定的key,组成一个新的map

插入

json格式

取值