Hive-学习总结(二)

四.HiveQL 查询

4.1select from

注意hive中有特殊的三种集合的形式

查询这三种类型时，hive会使用JSON语法输出，查询其中的元素方式如下

-- 数组
subordiantes[0]
-- map
deduction["State Taxes"]
-- struct
address.city

4.2 算数运算符

+，-，*，/，%，&（位与），|（位或），^（异或），~（取反）

注意：hive进行数据运算时，遵循的是底层Java中数据类型的原则，当两个数据类型不相同时，小的数据类型会自动转成较大的数据类型再进行运算

同时要注意数据溢出或者数据下溢问题，如果不确定，可以选择较大的数据类型，缺点是占用额外的内存。

4.3使用函数

数学函数

floor(DOUBLE d) 向下取整，返回 <= d的最大BIGINT型值

ceil（DOUBLE d)向上取整，返回 >= d的最小BIGINT型值

rand () 每行返回一个DOUBLE型的随机数

abs(DOUBLE d) 绝对值，返回值也是DOUBLE

聚合函数

count(),sum(),avg(),min(),max(),collect_set()(返回集合col元素重排后的数组)

可以设置属性hive.map.aggr值为true来提高聚合的性能。

原因：

-- 提高聚合的性能
SET hive.map.aggr=true 
SELECT count(*),avg(salary) FROM emloyees;
-- 这个设置可以将顶层的聚合操作放在Map阶段执行，从而减轻清洗阶段数据传输和Reduce阶段的执行时间，提升总体性能。
-- 缺点：该设置会消耗更多的内存。

-- 注：顶层的聚合操作（top-levelaggregation operation）,是指在group by语句之前执行的聚合操作

表生成函数

与聚合函数相反，将单列扩展成多列或者多行

explde(xxx)

SELECT explode(students) AS sub FROM employees;
-- 当使用表生成函数时，hive要求使用列别名。 

explode(ARRAY array) 返回0到n行结果，每行都对应输入的array数组中的一个元素  返回值类型 - N 行结果

explode(MAP map) 返回0到n行结果，每行对应每个map中的键值对（0.8版本新增） 返回值类型 - N行结果

explode(ARRAY<TYPE> a) 对于a中的每个元素，explode()会生成一行记录包含这个元素 返回值类型 - 数组类型 

inline(ARRAY<STRUCT[,STRUCT]>)将结构体提取出来并插入到表中（0.10版本新增）结果插入表中

json_tuple(STRING jsonStr,p1,p2,p3) 类似于get_json_object()函数，可以解析json字符串，方法如下

1.get_json_object
get_json_object(STRING jsonStr,"$.p1")

2.json_tuple(STRING jsonStr,p1,p2,p3...pn)
但是这样取出来去要用lateral view 展开

parse_url_tuple(url,p1,p2,...,pn)其中N>=1,从URL解析出N个部分信息，其中需要输入的参数是：URL，以及多个要抽取的部分的名称，所有输入的参数类型都是STRING，部分名称大小写是敏感的，而且不应该包含空格。

其他内置函数

cast(<expr> as <type>) 将expr转换成type类型的，如果转换失败，返回NULL

concat(STRING s1,STRING s2,...) 拼接字符串 hive 0.12.0可以拼接二进制字节码

concat_ws(STRING separator, STRING s1,STRING s2,..) 拼接字符串，按照指定的分隔符进行拼接。0.12可以拼接二进制字节码

in_file(STRING str, STRING filename) 如果文件名为filename的文件中有完整的一行数据和字符串s完全匹配，则返回true

instr(STRING str,STRING substr) 查找字符串str中子字符串substr第一次出现的位置
length(STRING s) 计算字符串s的长度

lower(STRING a) 转成小写

lcase(STRING a) 转成小写

lpad(STRING s,INT len,STRING pad)从左边开始对字符串s使用pad进行填充，最终长度到达len位置。如果字符串s本身长度比len大，多余的本分会杯去掉

ltrim(STRING a) 字符串a左侧的空格会全部被去掉

rpad(STRING s,INT len,STRING pad)同上，右侧

rtrim(STRING a)同上，右侧

size()返回map/array中的元素的个数

split(STRING s,STRING pattern) 按照正则表达式pattern分割字符串s，并将分割后的部分以字符串数组的方式返回
...未完待续 

4.4 开启本地模式，在某些情况下可以避免执行mapreduce

hive.exec.mode.local.auto = true
-- hive在一些情况下会尝试在本地执行
-- 哪些情况？
待补充...

4.5谓词操作符

A [NOT] LIKE B 支持的数据类型：STRING
B 是一个SQL下的简单的正则表达式，如果A与其匹配的话，则返回TRUE，否则返回FALSE，B的表达式说明如下："x%"表示必须以x字母开头，"%x"表示必须以x字母结尾，"%x%"表示包含x，'_'表示匹配单个字符，B必须要整个字符串A匹配才可以。

A RLIKE B, A REGEXP B
B是一个正则表达式，如果A与之相匹配，则返回TRUE，反之，返回FALSE。
匹配使用的是JDK中的正则表达式实现的，正因为正则表达式也依旧其中的规则。正则表达式必须和整个字符串A相匹配，而不是与其子符串相匹配
SELECT name,address.street
FROM employees WHERE address.street RLIKE '.*(Chicago|Ontario).*'
字符串中的点表示和任意的字符匹配，星号表示重复"左边的字符串"零次到无数次。表达式(x|y)表示和x或者y匹配

 4.6 GROUP BY语句

通常和聚合函数一起使用，按照一个或者多个列对结果进行分组，然后对每个组执行聚合操作。

　Having语句

该语句可以直接对Group b语句产生的分组进行条件过滤，而无需通过子查询。

4.7 JOIN语句

Hive支持通常的SQL JOIN语句，但是只支持等值连接

4.7.1 INNER JOIN

只有进行连接的两个表中都存在与连接标准相匹配的数据才会被保留下来。

-- 这里想说明一个问题
/*
对于where的使用，之前有群里问到一个问题，自己写了where条件如下，但是却报错显示未指定分区，其实原因在与在一条sql语句中，where是在join后执行，
所以where并没有起到指定分区的作用，只起到了join后做条件过滤，相反这样会进行全表扫描，一旦hive开启设置必须指定分区，此时就会报错，所以要想指定分区，要用子查询。
*/
SELECT 
    a.name,
    b.salary 
FROM a INNER JOIN b
ON a.name = b.name
WHERE a.event_day = '20190520'  and b.event_day =  '20190520' 

hive不支持不等值连接，原因在于mr难以实现。Pig可以实现，尽管我不清楚Pig怎么用。。。

-- 来个sql
SELECT
    a.name,
    b.age,
    c.salary
FROM a JOIN b ON a.id = b.id
            JOIN c ON a.id = c.id
WHERE a.symbol = 'APPL' AND b.symbol = 'IBM' AND c.symbol = 'GE'
/*
1.这是我在书上看到的，这样写sql，我没写过，是否可以待验证，有点别扭
一般我会把分区放在子查询，然后两次JOIN
2.Hive会对每对JOIN连接对象启动一个MapReduce任务，该sql，会先启动一个mr job对表a,b进行连接操作，然后会再启动一个mr将第一个mr job的输出和c进行连接操作。
3.hive总是按照从左向右进行执行
*/

LEFT OUTER JOIN 略

OUTER JOIN 略

RIGHT OUTER JOIN 略

4.7.2 LEFT SEMI-JOIN

左半开连接会返回左边表的记录，前提是其记录对于右边表满足ON语句中的判定条件

注意：

1.hive不支持右半开连接

2.SEMI-JOIN比通常的INNER-JOIN要高效，因为对于左表中指定的一条记录，在右边表中一旦找到匹配的记录，就会停止扫描。

4.7.3 笛卡尔积JOIN 

设置属性：

hive.mapred.mode = strict 会禁止用户进行笛卡尔积查询。

 

4.8 JOIN 优化

当对3个或者更多个表进行JOIN连接时，如果每个ON子句都使用相同的键时，只会产生一个mr job。

streamtable 

hive对每行记录进行连接操作时，它会尝试把其他表缓存起来，然后扫描最后的那个表进行计算，因此我们最好小表在前大表在后。(也就是说在多表及联时，写在最后的表默认是stream形式加载，其他表的结果存在缓存中)，但是我们也可以通过指定哪张为大表从而采用stream的形式加载，而无需放在最后。这样可以避免大表加载占用过多内存而产生问题。

SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

mapjoin 

当遇到小表join大表时，正常的操作，会导致在reduce阶段导致数据倾斜。

我们可以使得join操作得map阶段进行，两种方式：1.增加/*+ MAPJOIN(b) */标示 哪张为小表  2.设置参数hive.optimize.bucketmapjoin = true 由于hive 0.8v版本不支持方式二，所以采用哪种方式要看公司使用的hive的版本而定。

SELECT /*+ MAPJOIN(b) */ a.key, a.value
FROM a JOIN b ON a.key = b.key

 注意：

以下的操作是不支持mapjoin的

Union Followed by a MapJoin   
Lateral View Followed by a MapJoin
Reduce Sink (Group By/Join/Sort By/Cluster By/Distribute By) Followed by MapJoin
MapJoin Followed by Union 
MapJoin Followed by Join
MapJoin Followed by MapJoin