Hive是一个数据仓库基础工具在Hadoop中用来处理结构化数据。
它包含两个模块，一个是MapReduce，另外一个是Hadoop分布式文件系统（HDFS）。
Hadoop生态系统包含了用于协助Hadoop的不同的子项目（工具）模块，如Sqoop, Pig 和 Hive。
- Sqoop: 它是用来在HDFS和RDBMS之间来回导入和导出数据。
- Pig: 它是用于开发MapReduce操作的脚本程序语言的平台。
- Hive: 它是用来开发SQL类型脚本用于做MapReduce操作的平台。
Hive的特点
•可扩展
Hive可以自由的扩展集群的规模，一般情况下不需要重启服务
•延展性
Hive支持用户自定义函数，用户可以根据自己的需求来实现自己的函数
•容错
良好的容错性，节点出现问题SQL仍可完成执行
Hive教程http://sishuok.com/forum/blogPost/list/6220.html
第一部分：关系运算
Hive支持的关系运算符
•常见的关系运算符
•等值比较: =
•不等值比较: <>
•小于比较: <
•小于等于比较: <=
•大于比较: >
•大于等于比较: >=
•空值判断: IS NULL
•非空判断: IS NOT NULL
•LIKE比较: LIKE
•JAVA的LIKE操作: RLIKE
•REGEXP操作: REGEXP
•等值比较: =
注意：否定比较时候用 NOT A LIKE B
•JAVA的LIKE操作: RLIKE
语法: A RLIKE B
操作类型: strings
描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合JAVA正则表达式B的正则语法，则为TRUE；否则为FALSE。
举例：
hive> select 1 from dual where 'footbar’ rlike '^f.*r$’;
1
注意：判断一个字符串是否全为数字：
hive>select 1 from dual where '123456' rlike '^\\d+$';
1
hive> select 1 from dual where '123456aa' rlike '^\\d+$';
1
•REGEXP操作: REGEXP
语法: A REGEXP B
操作类型: strings
描述: 功能与RLIKE相同
举例：
hive> select 1 from dual where ‘key' REGEXP '^f.*r$';
1
第二部分：逻辑运算与数学运算
Hive数学运算
•加法操作: +
•减法操作: -
•乘法操作: *
•除法操作: /
•取余操作: %
•位与操作: &
•位或操作: |
•位异或操作: ^
•位取反操作: ~
•加法操作: +
注意： hive 中最高精度的数据类型是 double, 只精确到小数点后 16 位，在做除法运算的时候要 特别注意
Hive逻辑运算
•逻辑与操作: AND
•逻辑或操作: OR
•逻辑非操作: NOT
第三部分：数值运算
•取整函数: round
•指定精度取整函数: round
•向下取整函数: floor
•向上取整函数: ceil
•向上取整函数: ceiling
•取随机数函数: rand
•自然指数函数: exp
•以10为底对数函数: log10
•以2为底对数函数: log2
• 对数函数: log
•幂运算函数: pow
•幂运算函数: power
•开平方函数: sqrt
•二进制函数: bin
•十六进制函数: hex
•反转十六进制函数: unhex
•进制转换函数: conv
•绝对值函数: abs
•正取余函数: pmod
•正弦函数: sin
•反正弦函数: asin
•余弦函数: cos
•反余弦函数: acos
•positive函数: positive
•negative函数: negative
第四部分：日期函数
•UNIX时间戳转日期函数: from_unixtime
• 获取当前UNIX时间戳函数: unix_timestamp
•日期转UNIX时间戳函数: unix_timestamp
• 指定格式日期转UNIX时间戳函数: unix_timestamp
•日期时间转日期函数: to_date
•日期转年函数: year
• 日期转月函数: month
• 日期转天函数: day
• 日期转小时函数: hour
• 日期转分钟函数: minute
• 日期转秒函数: second
• 日期转周函数: weekofyear
• 日期比较函数: datediff
• 日期增加函数: date_add
• 日期减少函数: date_sub
第六部分：字符串函数（较生疏）
•字符串长度函数：length
•字符串反转函数：reverse
•字符串连接函数：concat
• 带分隔符字符串连接函数：concat_ws
• 字符串截取函数：substr,substring
• 字符串截取函数：substr,substring
• 字符串转大写函数：upper,ucase
• 字符串转小写函数：lower,lcase
• 去空格函数：trim
• 左边去空格函数：ltrim
• 右边去空格函数：rtrim
•正则表达式替换函数：regexp_replace
•正则表达式解析函数：regexp_extract
•URL解析函数：parse_url
•json解析函数：get_json_object
•空格字符串函数：space
•重复字符串函数：repeat
•首字符ascii函数：ascii
•左补足函数：lpad
•右补足函数：rpad
•分割字符串函数: split
•集合查找函数: find_in_set
• URL 解析函数： parse_url
语法: parse_url(string urlString, string partToExtract [, string keyToExtract])
返回值: string
说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
举例：
hive> select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') from dual;
facebook.com
hive> select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') from dual;
v1
• json 解析函数： get_json_object
语法: get_json_object(string json_string, string path)
返回值: string
说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。
举例：
hive> select get_json_object('{"store":
> {"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
> "bicycle":{"price":19.95,"color":"red"}
> },
> "email":"amy@only_for_json_udf_test.net",
> "owner":"amy"
> }
> ','$.owner') from dual;
amy
第七部分：集合统计函数
•个数统计函数: count
•总和统计函数: sum
•平均值统计函数: avg
• 最小值统计函数: min
•最大值统计函数: max
• 个数统计函数 : count
第八部分：符合类型构建操作
•Map类型构建: map
•Struct类型构建: struct
•array类型构建: array
• Map 类型构建 : map
语法: map (key1, value1, key2, value2, …)
说明：根据输入的key和value对构建map类型
举例：
hive> Create table alex_test as select map('100','tom','200','mary') as t from dual;
hive> describe alex_test;
t map<string,string>
hive> select t from alex_test;
{"100":"tom","200":"mary"}
• Struct 类型构建 : struct
语法: struct(val1, val2, val3, …)
说明：根据输入的参数构建结构体struct类型
举例：
hive> create table alex_test as select struct('tom','mary','tim') as t from dual;
hive> describe alex_test;
t struct<col1:string,col2:string,col3:string>
hive> select t from alex_test;
{"col1":"tom","col2":"mary","col3":"tim"}
• array 类型构建 : array
语法: array(val1, val2, …)
说明：根据输入的参数构建数组array类型
举例：
hive> create table alex_test as select array("tom","mary","tim") as t from dual;
hive> describe alex_test;
t array<string>
hive> select t from alex_test;
["tom","mary","tim"]
第九部分：复杂类型访问操作
•array类型访问: A[n]
•map类型访问: M[key]
•struct类型访问: S.x
• array 类型访问 : A[n]
语法: A[n]
操作类型: A为array类型，n为int类型
说明：返回数组A中的第n个变量值。数组的起始下标为0。比如，A是个值为['foo', 'bar']的数组类型，那么A[0]将返回'foo',而A[1]将返回'bar'
举例：
hive> create table alex_test as select array("tom","mary","tim") as t from dual;
hive> select t[0],t[1],t[2] from alex_test;
tom mary tim
• map 类型访问 : M[key]
语法: M[key]
操作类型: M为map类型，key为map中的key值
说明：返回map类型M中，key值为指定值的value值。比如，M是值为{'f' -> 'foo', 'b' -> 'bar', 'all' -> 'foobar'}的map类型，那么M['all']将会返回'foobar'
举例：
hive> Create table alex_test as select map('100','tom','200','mary') as t from dual;
hive> select t['200'],t['100'] from alex_test;
mary tom
• struct 类型访问 : S.x
语法: S.x
操作类型: S为struct类型
说明：返回结构体S中的x字段。比如，对于结构体struct foobar {int foo, int bar}，foobar.foo返回结构体中的foo字段
举例：
hive> create table alex_test as select struct('tom','mary','tim') as t from dual;
hive> describe alex_test;
t struct<col1:string,col2:string,col3:string>
hive> select t.col1,t.col3 from alex_test;
tom tim
第十部分：复杂类型长度统计函数
•Map类型长度函数: size(Map<K.V>)
•array类型长度函数: size(Array<T>)
•类型转换函数
• Map 类型长度函数 : size(Map<K.V>)
语法: size(Map<K.V>)
返回值: int
说明: 返回map类型的长度
举例：
hive> select size(map('100','tom','101','mary')) from dual;
2
• array 类型长度函数 : size(Array<T>)
语法: size(Array<T>)
返回值: int
说明: 返回array类型的长度
举例：
hive> select size(array('100','101','102','103')) from dual;
4
• 类型转换函数
类型转换函数: cast
语法: cast(expr as <type>)
返回值: Expected "=" to follow "type"
说明: 返回array类型的长度
举例：
hive> select cast(1 as bigint) from dual;
1
自定义函数
•虽然Hive提供了很多函数，但是有些还是难以满足我们的需求。因此Hive提供了自定义函数开发
•自定义函数包括三种UDF、UADF、UDTF
•UDF(User-Defined-Function)
•UDAF（User- Defined Aggregation Funcation）
•UDTF(User-Defined Table-Generating Functions) 用来解决 输入一行输出多行(On-to-many maping) 的需求。
第一部分：ＤＤＬ
DDL
•建表
•删除表
•修改表结构
•创建／删除视图
•创建数据库
•显示命令
建表
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
•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 。
INSERT INTO
•INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement
•不支持EXIST ,NOT EXIST
ORDER BY与SORT BY的不同
•ORDER BY 全局排序，只有一个Reduce任务
•SORT BY 只在本机做排序
join 查询时，需要注意几个关键点
•只支持等值join
•SELECT a.* FROM a JOIN b ON (a.id = b.id)
•SELECT a.* FROM a JOIN b
ON (a.id = b.id AND a.department = b.department)
•可以 join 多于 2 个表，例如
SELECT a.val, b.val, c.val FROM a JOIN b
ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
Hive易错语法http://blog.csdn.net/javajxz008/article/details/51381163
1.hive在连接中不支持不等值连接，不支持or，where条件后不支持子查询。分别举例如下及实现解决办法。
1.1.不支持不等值连接
错误:select * from a inner join b on a.id<>b.id
替代方法:select * from a inner join b on a.id=b.id and a.id is null;
1.2.不支持or
错误:select * from a inner join b on a.id=b.id or a.name=b.name
替代方法:select * from a inner join b on a.id=b.id
union all
select * from a inner join b on a.name=b.name
1.3.where后不支持子查询
错误：select * from a where a.id in (select id from b)
替代方法:select * from a inner join b on a.id=b.id 或in的另外一种高效实现left-semi join
select a.* from a left-semi join b on a.id=b.id a的id在b的id中，但是只能select a表中的字段，不能把b表中的字段查出来。另外right join 和full join
不支持这种写法。
1.4.两个sql union all的字段名必须一样或者列别名要一样。
2.hive不支持where条件后的列别名
错误:select sum(a.amt) as total from a where a.total>20
替代方法:select total from (select sum(a.amt) as total from a) a where total>20
这种方式下例外:select id,count(*) as amt from a where dt='20160101' group by id having amt>1
3.谓词前推
数据量提前过滤，加分区过滤。
4.计算日期间相隔天数，函数datediff要求日期格式为10位，如2012-10-12
5.为了提高hive sql的执行效率可以在执行hql前设置性能参数，只针对当前会话有效。如
set hive.auto.convert.join=false;
set hive.ignore.mapjoin.hint=false;
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=16;
set hive.groupby.skewindata=true;
set mapred.reduce.tasks=30;
6.按模式匹配字符串取其中的部分。
regexp_extract(a.ed_logo,'(.*)(.0)',1) --字段ed_logo按正则表达式'(.*)(.0)'匹配， 匹配后取第一个部分。如187.0，得187
7.hive排序
row_number() over (distribute by ED_APPLICATION_ID sort by ED_CARD_SEQ desc)，按字段ED_APPLICATION_ID分组，按ED_CARD_SEQ降序排序。distribute也可用partition,sort by也可以用
order by。
三种排序及例子:
row_number() ---
rank() |+ distribute by field1 sort by field2 [desc|asc]
dense_rank()----
表结果及值如下:
hive> desc mid;
OK
id string
value string
hive> select * from mid;
OK
1001 12
1001 13
1001 14
1001 12
1002 13
1003 14
1004 15
select *,row_number() over(distribute by id sort by value) as rank from mid;
1001 12 1
1001 12 2
1001 13 3
1001 14 4
1002 13 1
1003 14 1
1004 15 1
row_number()--重复项依然会递增编号
select *,dense_rank() over(distribute by id sort by value) as rank from mid;
1001 12 1
1001 12 1
1001 13 2
1001 14 3
1002 13 1
1003 14 1
1004 15 1
dense_rank()--重复项编号相同
select *,rank() over(partition by id sort by value) as rank from mid;
1001 12 1
1001 12 1
1001 13 3
1001 14 4
1002 13 1
1003 14 1
1004 15 1
rank()----重复项编号相同，但是后续会跳过一个编号。
8.collect_set(field)[index]--去重
collect_list(field)[index]--不去重
select id,collect_set(value)[0] from mid group by id;
1001 14
1002 13
1003 14
1004 15
目测去重后从大到小放入集合
select id,collect_list(value)[0] from mid group by id;
1001 12
1002 13
1003 14
1004 15
9.在sql中使用udf
定义类继承udf类，重写evaluate方法，该方法支持重载，打成jar包，使用如下:
add jar /home/myudf.jar;
create temporary function as myfunction as 'com.comp.udf.UDFClass';
select myfunction(args) from table_name
参考网址:
2309
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
