第6章 HiveQL:查询
6.1 SELECT…FROM语句
选择的列是集合数据类型时,Hive会使用Json语法应用于输出。
--数组类型
hive >SELECT name,subordinates FROM employees;
John Doe {"Mary Smith","Todd Jones"}
--MAP类型,JSON格式来表达MAP
hive >SELECT name,deductions FROM employees;
John Doe {"Federal Taxes":0.2,"State Taxes":0.05}
--STRUCT类型,JSON map格式
hive >SELECT name,address FROM employees;
John Doe {"street":"1 Michigan Ave","City":"Chicago","state":"IL","zip":"60600"}
如何引用集合数据类型中的元素
--数组第1个元素的查询
hive >SELECT name,subordinates[0] FROM employees;
John Doe Mary Smith
--引用一个MAP元素,ARRAY[..]语法,使用键值
hive >SELECT name,deductions["State Taxes"]FROM employees;
John Doe 0.05
--引用STRUCT中的一个元素,“点”符号
hive >SELECT name,address.city FROM employees;
John Doe Chicago
6.1.1 使用正则表达式来指定列
下面的查询将会从表stocks中选择symbol列和所有列名以price作为前缀的列。
hive >SELECT symbol,'price.*' FROM stocks;
6.1.2 使用列值进行计算
用户不但可以选择表中的列,还可以使用函数调用和算术表达式来操作列值。
例如:我们可以查询到转换为大写的雇员姓名、雇员对应的薪水、缴纳的联邦税收比例以及扣除税收的薪资。
hive >SELECT upper(name),salary,deductions["Federal Taxes"],
>round(salary * (1-deductions["Federal Taxes"])) FROM employees;
round()函数 : 返回一个DOUBLE类型的最近整数。
** 6.1.3 算术运算符**
** 6.1.4 使用函数**
数学函数
返回值类型 | 样式 | 描述 |
---|---|---|
BIGINT | round(DOUBLE d) | 返回DOUBLE型d的BIGINT类型的近似值 |
DOUBLE | round(DOUBLE d,INT n) | 返回DOUBLE型d的保留n位小数的DOUBLE型的近似值 |
BIGINT | floor(DOUBLE d) | 返回<=d的最大BIGINT型值 |
BIGINT | ceil(DOUBLE d) | 返回>=d的最小BIGINT型值 |
DOUBLE | rand() | 每行返回一个DOUBLE型随机数 |
DOUBLE | exp(DOUBLE d) | 返回e的d幂次方 |
DOUBLE | ln(DOUBLE d) | 以自然数为底d的对数 |
DOUBLE | log10(DOUBLE d) | 以10为底d的对数 |
DOUBLE | log(DOUBLE base,DOUBLE d) | 以base为底d的对数 |
DOUBLE | pow(DOUBLE d,DOUBLE p) power(DOUBLE d,DOUBLE p) | 计算d的p次幂 |
DOUBLE | sqrt(DOUBLE d) | 计算d的平方根 |
聚合函数
count(*)
count(expr) 计算提供expr表达式的值非NULL的行数
count(DISTINCT expr[,expr_.]) 计算提供的expr表达式的值排重后非NULL的行数
sum(col)
sum(DISTINCT col)
avg(col)
avg(DISTINCT col)
min(col)
max(col)
variance(col),var_pop(col) 返回集合col中的一组数值的方差
var_samp(col) 返回集合col中一组数值的样本方差
stddev_pop(col) 返回一组数值的标准偏差
stddev_samp(col) 返回一组数值的标准样本偏差
covar_pop(col1,col2) 返回一组数值的协方差
covar_samp(col1,col2) 返回一组数值的样本协方差
corr(col1,col2) 返回两组数值的相关系数
表生成函数
与聚合函数“相反的”一类函数即表生成函数,其可以将单列扩展成多列或者多行。
例子:
(1) employees表中每行记录中的subordinates字段内容转换成0个或者多个新的记录行。如果某行雇员记录subordinates字段内容为空的话,那么将不会产生新的记录;如果不为空的话,那么这个数组的每个元素都会产生一行新记录:
hive > SELECT explode(subordinates) AS sub FROM employees;
(2) url_table表,表中含有一个名为url的列,列中存储有很多网址:
hive > SELECT parse_url_tuple(url,'HOST','PATH','QUERY') as (host,path,query)
> FROM url_table
表生成函数
返回值类型 | 样式 | 描述 |
---|---|---|
N行结果 | explode(ARRAY array) | 返回0到多行结果,每行都对应输入的array数组中的一个元素 |
N行结果 | explode(MAP map) | 返回0到多行结果,,每行都对应每个的map键-值对,其中一个字段是map的键,另一个字段对应map的值 |
数组的类型 | explode(ARRAY a) | 对于a中的每个元素,explode()会生成一行记录包含这个元素 |
结果插入表中 | inline(ARRAY<STRUCT[,STRUCT]>) | 将结构体数组提取出来并插入到表中 |
TUPLE | json_tuple(STRING jsonStr,p1,p2,…,pn) | 通过一次调用可以获得多个键值 |
TUPLE | parse_url_tuple(url,p1,p2,…,pn) | 从URL中解析出N个部分信息。输入参数 URL,以及多个抽取的部分的名称 |
N行结果 | stack(INT n,col1,…,colM) | 把M列转换成N行,每行有M/N个字段 |
其他内置函数
返回值类型 | 样式 | 描述 |
---|---|---|
STRING | ascii(STRING s) | 返回字符串s中首个ASCII字符的整数值 |
STRING | base64(BINARY bin) | 将二进制值bin转换成基于64位的字符串 |
STRING | binary(STRING s) | 输入值转换成二进制 |
返回类型就是type定义的类型 | cast( expr as type) | 将expr类型转换成type类型的 |
STRING | concat(BINARY s1,BINARY s2…) | 将字符串s1 s2等拼接起来 |
STRING | concat_ws(STRING separator,STRING s1,STRING s2,… ) | 使用指定的分隔符拼接字符串 |
其他内置函数见下节详细学习
** 6.1.5 LIMIT 语句**
LIMIT子句用于限制返回的行数
** 6.1.6 列别名**
hive > SELECT upper(name),salary,deductions["Federal Taxes"] as fed_taxes,
> round(salary * (1-deductions["Federal Taxes"])) as salary_minus_fed_taxes
> FROM employees;
** 6.1.7 嵌套SELECT语句**
对于嵌套查询语句,使用别名是非常有用的。
hive > FROM(
> SELECT upper(name),salary,deductions["Federal Taxes"] as fed_taxes,
> round(salary * (1-deductions["Federal Taxes"])) as salary_minus_fed_taxes
> FROM employees
> )e
>SELECTe.name,e.salary_minus_fed_taxes
>WHERE e.salary_minus_fed_taxes>7000
6.1.8 CASE…WHEN…THEN句式
用于处理单个列的查询结果
hive > SELECT name , salary
> CASE
> WHEN salary < 5000 THEN 'low'
> WHEN salary>=5000 AND salary< 7000 THEN 'middle'
> WHEN salary>=7000 AND salary< 10000 THEN 'high'
> ELSE 'very high'
> END AS bracket FROM employees;
6.1.9 什么情况下Hive可以避免进行MapReduce
6.2 WHERE 语句
** 6.2.1 谓词操作符**
** 谓词操作符 **
操作符 | 支持的数据类型 | 描述 |
---|---|---|
A=B | 基本数据类型 | 如果A等于B 则返回TRUE,反之返回FALSE |
A<=>B | 基本数据类型 | 如果A和B都为NULL 则返回TRUE,其他的和等号(=)操作符的结果一致,如果任一为NULL则结构为NULL |
A==B | 没有 | 错误的语法 |
A<>B,A!=B | 基本数据类型 | A或者B为NULL,则返回NULL;如果A不等于B 则返回TRUE,反之返回FALSE |
A<B | 基本数据类型 | A或者B为NULL 则返回NULL;A<B 返回TRUE,反之返回FALSE |
A [NOT] BETWEEN B AND C | 基本数据类型 | 如果A B C任一为NULL,则结果为NULL,如果A的值在B和C之间,则返回TURE 反之返回 FALSE |
A IS NULL | 所有数据类型 | 如果A等于NULL,返回TRUE,反之返回FALSE |
A IS NOT NULL | 所有数据类型 | 如果A不等于NULL,返回TRUE,反之返回FALSE |
A [NOT] LIKE B | STRING类型 | B是一个SQL的正则表达式,如果A与其匹配的话,则返回TRUE;反之返回FALSE.‘%x’ A 必须x结尾;诸如此类;下划线’_'匹配单个字符 |
A RELIKE B, A REGEXP B | STRING类型 | B是一个正则表达式,如果A与其相匹配,则返回TURE;反之返回FALSE.匹配使用的是SDK中的正则表达式接口实现的,正则表达式必须和整个字符串A相匹配,而不是只需要与其子字符串匹配 |
** 6.2.2 关于浮点数比较**
浮点数比较的一个常见陷阱出现在不同类型间作比较的时候(FLOAT 和 DOUBLE)
hive > SELECT name , salary , deductions['Federal Taxes']
> FROM employees WHERE deductions['Federal Taxes'] > 0.2
John Doe 10000.0 0.2
Mary Smith 8000.0 0.2
Fred Finance 15000.0 0.3
问题:deductions[‘Federal Taxes’] = 0.2 的记录也被输出了?
原因:用户在写一个浮点数(比如0.2),Hive会把该值保存成DOUBLE型的。
0.2 对于FLOAT类型是0.2000001,而对于DOUBLE类型是0.200000000001。因为一个8个字节的DOUBLE值具有更多小数位。当表中的FLOAT值通过Hive转换成DOUBLE值时,产生的DOUBLE值为0.200000100000,这个值实际要比0.200000000001大。
解决方法:
- 表模式定义字段时,使用DOUBLE而非FLOAT。
- 显性指出0.2为FLOAT类型。cast(0.2 AS FLOAT)
注意 cast操作符内部语法:数值 AS FLOAT
6.2.3 LIKE 和 RLIKE
RLIKE子句是Hive功能中的一个扩展,可以通过java的正则表达式这个强大的语言来指定匹配条件。
比如:从employees表中查找所有住址的街道名称中含有Chicago或Ontario的雇员名称和街道信息:
hive > SELECT name,address.street
> FROM employees WHERE adress.street RLIKE '.*(Chicago|Ontario).*';
字符串中的点号(.)表示和任意的字符匹配,星号(*)表示重复“左边的字符串”零次到无数次。表达式(x|y)表示和x或者y匹配。
用LIKE子句改写:
hive > SELECT name,address
> FROM employees WHERE adress.street LIKE '%Chicago%' OR adress.street LIKE '%Ontario%';
6.3 GROUP BY 语句
GROUP BY 语句通常会和聚合一起使用,按照一个或者多个列对结果进行分组,然后对每个组执行聚合操作。
** HAVING语句 **
HAVING 子句允许用户通过一个简单的语法完成原本需要子查询才能对GROUP BY 语句产生的分组进行条件过滤的任务。
6.4 JOIN 语句
** 6.4.1 INNER JOIN **
只有进行连接的两个表都存在与连接标准相匹配的数据才会被保留。
** 6.4.2 JOIN 优化 **
用户保证连续查询中的表的大小从左到右依次增加。
** 6.4.3 LEFT OUTER JOIN **
** 6.4.4 OUTER JOIN**
** 6.4.5 RIGHT OUTER JOIN**
右外连接 会返回右边表所有符合WHERE语句的记录。左表匹配不上的字段值用NULL代替。
** 6.4.6 FULL OUTER JOIN **
完全外连接 返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话,那么会使用NULL值代替。
hive > SELECT s.ymd,s.symbol,s.price_close,d.dividend
> FROM dividends d FULL OUTER JOIN stocks s ON d.ymd=s.ymd AND d.symbol=s.symbol
> WHERE s.symbol='AAPL'
1987-05-07 AAPL 80.25 NULL
1987-05-08 AAPL 79.0 NULL
1987-05-11 AAPL 77.0 0.015
1987-05-12 AAPL 75.5 NULL
** 6.4.7 LEFT SEMI-JOIN**
左半开连接 会返回左边表的记录,前提是其记录对于右边表满足ON语句中的判定条件。
--Hive 不支持的查询
SELECT s.ymd,s.symbol,s.price_close FROM stocks s
WHERE s.ymd, s.symbol IN
(SELECT d.ymd,d.symbol FROM dividends d);
--Hive 中 LEFT SEMI JOIN 语法
hive > SELECT s.ymd,s.symbol,s.price_close
> FROM stocks s LEFT SEMI JOIN dividends d ON d.ymd=s.ymd AND d.symbol=s.symbol;
SEMI-JOIN 比 INNER JOIN 更高效。原因如下:对于左表中一条指定的记录,在右表一旦找到匹配的记录,Hive就会停止扫描。
** 6.4.8 笛卡尔积 JOIN**
笛卡尔积是一种连接,表示左边表的行数乘以右边表的行数等于笛卡尔结果集的大小。
6.5 ORDER BY 和 SORT BY
Hive 中 ORDER BY 语句 会对查询结果集执行一个全局排序。也就是说会有一个所有的数据都会通过一个reducer进行处理的过程。
SELECT s.ymd,s.symbol,s.price_close
FROM stocks s
OEDER BY s.ymd ASC,s.symbol DESC;
SELECT s.ymd,s.symbol,s.price_close
FROM stocks s
SORT BY s.ymd ASC,s.symbol DESC;
6.6 含有 SORT BY 的 DISTRIBUTE BY
DISTRIBUTE BY 控制 map 的输出在reducer中是如何划分的。MapReduce job中传输的所有数据都是按照键-值对的方式进行组织的,因此Hive在将用户的查询语句转换成MapReduce job时,其必须在内部使用这个功能。
默认情况下,MapReduce计算框架会依据map输入的键计算相应的哈希值,然后按照得到的哈希值将键-值对均匀分发到多个reducer中去。
假设我们希望具有相同股票交易码的数据在一起处理。那么我们可以使用DISTRIBUTE BY 保证具有相同股票交易码的记录分发到同一个reducer中进行处理,然后使用SORT BY 来按照期望对数据进行排序。
hive > SELECT s.ymd , s.symbol , s.price_close
> FROM stocks s
> DISTRIBUTE BY s.symbol
> SORT BY s.symbol ASC , s.ymd ASC
DISTRIBUTE BY 和 GROUP BY 控制reducer 是如何接受一行行数据进行处理这方面类似,而SORT BY 控制着reducer内的数据是如何进行排序的。
6.7 CLUSTER BY
上面的例子中,s.symbol列被用在了DISTRIBUTE BY语句中,而s.symbol列和s.ymd位于SORT BY 语句中。如果这2个语句中涉及到的列完全相同,而且采用的是升序排序,那么CLUSTER BY 等价于前面的2个语句。
hive > SELECT s.ymd , s.symbol , s.price_close
> FROM stocks s
> CLUSTER BY s.symbol;
DISTRIBUTE BY … SORT BY 语句或简化版的CLUSTER BY 语句会剥夺 SORT BY的并行性,然而可以实现输出文件的数据是全局排序。
6.8 类型转换
cast(value AS TYPE)如果value字段的值不是合法的字符串,hive会返回NULL。如果将浮点数转换成整数 推荐使用round()或者floor()
6.9 抽样查询
对于非常大的数据集,用户需要使用的是一个具有代表性的查询性结果而不是全部结果。Hive可以通过对表进行分桶抽样来满足需求。
分桶管理
hive的分区和分桶
假设numbers表只有number字段,其值是1到10
hive > SELECT * from numbers TABLESAMPLE(BUCKET 3 OUT OF 10 ON numbers)s ;
tablesample是抽样语句,语法:TABLESAMPLE(BUCKET x OUT OF y)
y 必须是table总bucket数的倍数或者因子。hive根据y的大小,决定抽样的比例。例如,table总共分了64份,当y=32时,抽取(64/32)2个bucket的数据,当y=128时,抽取(64/128)1/2个bucket的数据。x表示从哪个bucket开始抽取。例如table总bucket数为32,tablesample(bucket 3 out of 16),表示总共抽取(32/16)2个bucket的数据,分别为第3个bucket和第(3+16)19个bucket的数据。
6.9.1 数据块抽样
Hive提供了另外一种按照抽样百分比进行抽样的方式,这是基于行数的,按照输入路径下的数据块百分比进行抽样。
hive > SELECT * FROM numbersflat TABLESAMPLE(0.1 PERCENT) s;
提示:这种抽样方式不一定适用于所有的文件格式。另外,这种抽样的最小抽样单元是一个HDFS数据块。因此,如果表的大小小于普通的块大小128M的话,那么会返回所有行。
6.9.2 分桶表的输入裁剪
抽样会扫描表中的所有数据,然后在每N行中抽取一行数据。不过如果TABLESAMPLE语句中指定的列和CLUSTERED BY 语句中指定的列相同,那么TABLESAMPLE查询就只会扫描涉及到的表哈斯分区中的数据
6.10 UNION ALL
UNION ALL可以将2个或多个表进行合并。每个union子查询都必须具有相同的列,而且对应的每个字段的字段类型必须一致。