Programming Hive ( Hive编程指南) 四

Chapter 6   HiveQL：查询 

--[root@master chapter5]# cat 4.create_employees.sql 
create table employees (
        name    string,
        salary  float,
        subordinates    Array<string>,
        dedcutions      map<string,float>,
        address struct<street:string,city:string,state:string,zip:int>
)
partitioned by (country string,state string);

--集合字符串元素是加“”的，而基本数据类型String的列值是不加的
select name,subordinates from employees;
Jason    ["Marry","Todd"]
Marry    ["Bill"]
Todd     []
Bill     []

--subordinates是Array，结果用【】表示；deductions是MAP,address是STRUCT，结果用{}表示

引用集合数据类型的元素(下标索引从0开始)，不存在元素返回null，且String类型没有"" 

--引用集合数据类型的元素(下标索引从0开始)
select name , subordinates[0] from employees;
Jason    Marry
Marry    Bill
Bill     null
Todd     null

引用MAP元素

--引用Map元素,需要使用键值，而不是下标索引
select name,deductions["State Taxes"] from employees;
Jason    0.05
Marry    0.05
Todd     0.03
Bill     0.03

引用Struct中的元素

--引用struct中的元素，使用.符号
select name , address.city from employees;
Jason    Chicago
Marry    Chicago
Todd     Oak Park
Bill     Obscuria

6.1.1使用正则表达式来指定列

--创建stocks表
create external table if not exists stocks (
    exchange    string,
    symbol      string,
    ymd         string,
    price_open  float,
    price_high  float,
    price_low   float,
    price_close float,
    volume      int,
    price_adj_close    float)
row format delimited fields terminated by ','
location '/data/stocks';

--选择symbol和所有列名以price为前缀的列(price_open、price_high、price_low、price_close、price_adj_close)
select symbol,'price.*' from stocks;
AAPL    195.69    197.88    194.0    194.12    194.12
AAPL    195.69    197.88    194.0    194.12    194.12
...

 6.1.2 使用列值进行计算

select upper(name),salary,deductions['Federal Taxes'],round(salary * (1-deductions['Federal Taxes'])) from employees;
Jason    10000.0    0.2    80000
Mary     8000.0     0.2    64000
Todd     7000.0     0.15   59500
Bill     6000.0    0.15    51000

6.1.3 算术运算符

运算符	类型	说明
A + B	所有数字类型	A和B相加。结果的与操作数值有共同类型。例如每一个整数是一个浮点数，浮点数包含整数。所以，一个浮点数和一个整数相加结果也是一个浮点数。
A – B	所有数字类型	A和B相减。结果的与操作数值有共同类型。
A * B	所有数字类型	A和B相乘，结果的与操作数值有共同类型。需要说明的是，如果乘法造成溢出，将选择更高的类型。
A / B	所有数字类型	A和B相除，结果是一个double（双精度）类型的结果。
A % B	所有数字类型	A除以B余数与操作数值有共同类型。
A & B	所有数字类型	运算符查看两个参数的二进制表示法的值，并执行按位”与”操作。两个表达式的一位均为1时，则结果的该位为 1。否则，结果的该位为 0。
A|B	所有数字类型	运算符查看两个参数的二进制表示法的值，并执行按位”或”操作。只要任一表达式的一位为 1，则结果的该位为 1。否则，结果的该位为 0。
A ^ B	所有数字类型	运算符查看两个参数的二进制表示法的值，并执行按位”异或”操作。当且仅当只有一个表达式的某位上为 1 时，结果的该位才为 1。否则结果的该位为 0。
~A	所有数字类型	对一个表达式执行按位”非”（取反）。

关系运算符

运算符	类型	说明
A = B	所有原始类型	如果A与B相等,返回TRUE,否则返回FALSE
A == B	无	失败，因为无效的语法。 SQL使用”=”，不使用”==”。
A <> B	所有原始类型	如果A不等于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”，结果返回”NULL”。
A < B	所有原始类型	如果A小于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”，结果返回”NULL”。
A <= B	所有原始类型	如果A小于等于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”，结果返回”NULL”。
A > B	所有原始类型	如果A大于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”，结果返回”NULL”。
A >= B	所有原始类型	如果A大于等于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”，结果返回”NULL”。
A IS NULL	所有类型	如果A值为”NULL”，返回TRUE,否则返回FALSE
A IS NOT NULL	所有类型	如果A值不为”NULL”，返回TRUE,否则返回FALSE
A LIKE B	字符串	如果A或B值为”NULL”，结果返回”NULL”。字符串A与B通过sql进行匹配，如果相符返回TRUE，不符返回FALSE。B字符串中 的”_”代表任一字符，”%”则代表多个任意字符。例如： (‘foobar’ like ‘foo’)返回FALSE，（ ‘foobar’ like ‘foo_ _ _’或者 ‘foobar’ like ‘foo%’)则返回TURE
A RLIKE B	字符串	如果A或B值为”NULL”，结果返回”NULL”。字符串A与B通过java进行匹配，如果相符返回TRUE，不符返回FALSE。例如：（ ‘foobar’ rlike ‘foo’）返回FALSE，（’foobar’ rlike ‘^f.*r$’ ）返回TRUE。
A REGEXP B	字符串	与RLIKE相同。

逻辑运算符

运算符	类型	说明
A AND B	布尔值	A和B同时正确时,返回TRUE,否则FALSE。如果A或B值为NULL，返回NULL。
A && B	布尔值	与”A AND B”相同
A OR B	布尔值	A或B正确,或两者同时正确返返回TRUE,否则FALSE。如果A和B值同时为NULL，返回NULL。
A | B	布尔值	与”A OR B”相同
NOT A	布尔值	如果A为NULL或错误的时候返回TURE，否则返回FALSE。
! A	布尔值	与”NOT A”相同

1.4复杂类型函数

函数	类型	说明
map	(key1, value1, key2, value2, …)	通过指定的键/值对，创建一个map。
struct	(val1, val2, val3, …)	通过指定的字段值，创建一个结构。结构字段名称将COL1，COL2，…
array	(val1, val2, …)	通过指定的元素，创建一个数组。

1.5对复杂类型函数操作

函数	类型	说明
A[n]	A是一个数组，n为int型	返回数组A的第n个元素，第一个元素的索引为0。如果A数组为['foo','bar']，则A[0]返回’foo’和A[1]返回”bar”。
M[key]	M是Map，关键K型	返回关键值对应的值，例如mapM为 \{‘f’ -> ‘foo’, ‘b’ -> ‘bar’, ‘all’ -> ‘foobar’\}，则M['all'] 返回’foobar’。
S.x	S为struct	返回结构x字符串在结构S中的存储位置。如 foobar \{int foo, int bar\} foobar.foo的领域中存储的整数。

2.内置函数
2.1数学函数

返回类型	函数	说明
BIGINT	round(double a)	四舍五入
DOUBLE	round(double a, int d)	小数部分d位之后数字四舍五入，例如round(21.263,2),返回21.26
BIGINT	floor(double a)	对给定数据进行向下舍入最接近的整数。例如floor(21.2),返回21。
BIGINT	ceil(double a), ceiling(double a)	将参数向上舍入为最接近的整数。例如ceil(21.2),返回22.
double	rand(), rand(int seed)	返回大于或等于0且小于1的平均分布随机数（依重新计算而变）
double	exp(double a)	返回e的n次方
double	ln(double a)	返回给定数值的自然对数
double	log10(double a)	返回给定数值的以10为底自然对数
double	log2(double a)	返回给定数值的以2为底自然对数
double	log(double base, double a)	返回给定底数及指数返回自然对数
double	pow(double a, double p) power(double a, double p)	返回某数的乘幂
double	sqrt(double a)	返回数值的平方根
string	bin(BIGINT a)	返回二进制格式，参考：http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex
string	hex(BIGINT a) hex(string a)	将整数或字符转换为十六进制格式。参考：http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex
string	unhex(string a)	十六进制字符转换由数字表示的字符。
string	conv(BIGINT num, int from_base, int to_base)	将指定数值，由原来的度量体系转换为指定的试题体系。例如CONV(‘a’,16,2),返回。参考：’1010′ http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv
double	abs(double a)	取绝对值
int double	pmod(int a, int b) pmod(double a, double b)	返回a除b的余数的绝对值
double	sin(double a)	返回给定角度的正弦值
double	asin(double a)	返回x的反正弦，即是X。如果X是在-1到1的正弦值，返回NULL。
double	cos(double a)	返回余弦
double	acos(double a)	返回X的反余弦，即余弦是X，，如果-1<= A <= 1，否则返回null.
int double	positive(int a) positive(double a)	返回A的值，例如positive(2)，返回2。
int double	negative(int a) negative(double a)	返回A的相反数，例如negative(2),返回-2。

2.2收集函数

返回类型	函数	说明
int	size(Map)	返回的map类型的元素的数量
int	size(Array)	返回数组类型的元素数量

2.3类型转换函数

返回类型	函数	说明
指定 “type”	cast(expr as )	类型转换。例如将字符”1″转换为整数:cast(’1′ as bigint)，如果转换失败返回NULL。

2.4日期函数

返回类型	函数	说明
string	from_unixtime(bigint unixtime[, string format])	UNIX_TIMESTAMP参数表示返回一个值’YYYY- MM – DD HH：MM：SS’或YYYYMMDDHHMMSS.uuuuuu格式，这取决于是否是在一个字符串或数字语境中使用的功能。该值表示在当前的时区。
bigint	unix_timestamp()	如果不带参数的调用，返回一个Unix时间戳（从’1970- 01 – 0100:00:00′到现在的UTC秒数）为无符号整数。
bigint	unix_timestamp(string date)	指定日期参数调用UNIX_TIMESTAMP（），它返回参数值’1970- 01 – 0100:00:00′到指定日期的秒数。
bigint	unix_timestamp(string date, string pattern)	指定时间输入格式，返回到1970年秒数：unix_timestamp(’2009-03-20′, ‘yyyy-MM-dd’) = 1237532400 参考：http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html
string	to_date(string timestamp)	返回时间中的年月日： to_date(“1970-01-01 00:00:00″) = “1970-01-01″
string	to_dates(string date)	给定一个日期date，返回一个天数（0年以来的天数）
int	year(string date)	返回指定时间的年份，范围在1000到9999，或为”零”日期的0。
int	month(string date)	返回指定时间的月份，范围为1至12月，或0一个月的一部分，如’0000-00-00′或’2008-00-00′的日期。
int	day(string date) dayofmonth(date)	返回指定时间的日期
int	hour(string date)	返回指定时间的小时，范围为0到23。
int	minute(string date)	返回指定时间的分钟，范围为0到59。
int	second(string date)	返回指定时间的秒，范围为0到59。
int	weekofyear(string date)	返回指定日期所在一年中的星期号，范围为0到53。
int	datediff(string enddate, string startdate)	两个时间参数的日期之差。
int	date_add(string startdate, int days)	给定时间，在此基础上加上指定的时间段。
int	date_sub(string startdate, int days)	给定时间，在此基础上减去指定的时间段。

2.5条件函数

返回类型	函数	说明
T	if(boolean testCondition, T valueTrue, T valueFalseOrNull)	判断是否满足条件，如果满足返回一个值，如果不满足则返回另一个值。
T	COALESCE(T v1, T v2, …)	返回一组数据中，第一个不为NULL的值，如果均为NULL,返回NULL。
T	CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END	当a=b时,返回c；当a=d时，返回e，否则返回f。
T	CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END	当值为a时返回b,当值为c时返回d。否则返回e。

2.6字符函数

返回类型	函数	说明
int	length(string A)	返回字符串的长度
string	reverse(string A)	返回倒序字符串
string	concat(string A, string B…)	连接多个字符串，合并为一个字符串，可以接受任意数量的输入字符串
string	concat_ws(string SEP, string A, string B…)	链接多个字符串，字符串之间以指定的分隔符分开。
string	substr(string A, int start) substring(string A, int start)	从文本字符串中指定的起始位置后的字符。
string	substr(string A, int start, int len) substring(string A, int start, int len)	从文本字符串中指定的位置指定长度的字符。
string	upper(string A) ucase(string A)	将文本字符串转换成字母全部大写形式
string	lower(string A) lcase(string A)	将文本字符串转换成字母全部小写形式
string	trim(string A)	删除字符串两端的空格，字符之间的空格保留
string	ltrim(string A)	删除字符串左边的空格，其他的空格保留
string	rtrim(string A)	删除字符串右边的空格，其他的空格保留
string	regexp_replace(string A, string B, string C)	字符串A中的B字符被C字符替代
string	regexp_extract(string subject, string pattern, int index)	通过下标返回正则表达式指定的部分。regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2) returns ‘bar.’
string	parse_url(string urlString, string partToExtract [, string keyToExtract])	返回URL指定的部分。parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1′, ‘HOST’) 返回：’facebook.com’
string	get_json_object(string json_string, string path)	select a.timestamp, get_json_object(a.appevents, ‘$.eventid’), get_json_object(a.appenvets, ‘$.eventname’) from log a;
string	space(int n)	返回指定数量的空格
string	repeat(string str, int n)	重复N次字符串
int	ascii(string str)	返回字符串中首字符的数字值
string	lpad(string str, int len, string pad)	返回指定长度的字符串，给定字符串长度小于指定长度时，由指定字符从左侧填补。
string	rpad(string str, int len, string pad)	返回指定长度的字符串，给定字符串长度小于指定长度时，由指定字符从右侧填补。
array	split(string str, string pat)	将字符串转换为数组。
int	find_in_set(string str, string strList)	返回字符串str第一次在strlist出现的位置。如果任一参数为NULL,返回NULL；如果第一个参数包含逗号，返回0。
array>	sentences(string str, string lang, string locale)	将字符串中内容按语句分组，每个单词间以逗号分隔，最后返回数组。 例如sentences(‘Hello there! How are you?’) 返回：( (“Hello”, “there”), (“How”, “are”, “you”) )
array>	ngrams(array>, int N, int K, int pf)	SELECT ngrams(sentences(lower(tweet)), 2, 100 [, 1000]) FROM twitter;
array>	context_ngrams(array>, array, int K, int pf)	SELECT context_ngrams(sentences(lower(tweet)), array(null,null), 100, [, 1000]) FROM twitter;

3.内置的聚合函数（UDAF）

返回类型	函数	说明
bigint	count(*) , count(expr), count(DISTINCT expr[, expr_., expr_.])	返回记录条数。
double	sum(col), sum(DISTINCT col)	求和
double	avg(col), avg(DISTINCT col)	求平均值
double	min(col)	返回指定列中最小值
double	max(col)	返回指定列中最大值
double	var_pop(col)	返回指定列的方差
double	var_samp(col)	返回指定列的样本方差
double	stddev_pop(col)	返回指定列的偏差
double	stddev_samp(col)	返回指定列的样本偏差
double	covar_pop(col1, col2)	两列数值协方差
double	covar_samp(col1, col2)	两列数值样本协方差
double	corr(col1, col2)	返回两列数值的相关系数
double	percentile(col, p)	返回数值区域的百分比数值点。0<=P<=1,否则返回NULL,不支持浮点型数值。
array	percentile(col, array(p~1,,\ [, p,,2,,]…))	返回数值区域的一组百分比值分别对应的数值点。0<=P<=1,否则返回NULL,不支持浮点型数值。
double	percentile_approx(col, p[, B])	Returns an approximate p^th^ percentile of a numeric column (including floating point types) in the group. The B parameter controls approximation accuracy at the cost of memory. Higher values yield better approximations, and the default is 10,000. When the number of distinct values in col is smaller than B, this gives an exact percentile value.
array	percentile_approx(col, array(p~1,, [, p,,2_]…) [, B])	Same as above, but accepts and returns an array of percentile values instead of a single one.
array	histogram_numeric(col, b)	Computes a histogram of a numeric column in the group using b non-uniformly spaced bins. The output is an array of size b of double-valued (x,y) coordinates that represent the bin centers and heights
array	collect_set(col)	返回无重复记录

4.内置表生成函数（UDTF）

返回类型	函数	说明
数组	explode(array a)	数组一条记录中有多个参数，将参数拆分，每个参数生成一列。
	json_tuple	get_json_object语句：select a.timestamp, get_json_object(a.appevents, ‘$.eventid’), get_json_object(a.appenvets, ‘$.eventname’) from log a; json_tuple语句: select a.timestamp, b.* from log a lateral view json_tuple(a.appevent, ‘eventid’, ‘eventname’) b as f1, f2

explode示例：

数组	SQL	返回
myCol [1,2] [3,4]	SELECT explode(myCol) AS myNewCol FROM myTable	myNewCol 1 2 3 4

运算符表格来自：Hive 运算符 & 内置函数详解 -- 适合关键词查找

算术运算符中，可以接受任意数据类型，如果数据类型不同，那么两种类型中值范围小的那个数据类型将转换为其他范围更广的数据类型。

6.1.4聚合函数

select count(*) , avg(salary) from employees;
4    77500

通过设置hive.map.aggr=true来提高聚合性能

set hive.map.aggr=true;
select count(*),avg(salary) from employees;
--触发map阶段的“顶级”聚合过程（非顶级聚合过程会在执行一个groupby后进行），同时顶级聚合需要更多内存

通过排重后计算孤僻交易码个数：

select count(distinct symbol) from stocks;

--当使用count(distinct col)而同时col是分区列时查询结果会是0，这是一个bug

表生成函数：（一到多）

6.1.6列别名

select upper(name),salary,deductions["Federal Taxes"] as fed_taxes, 
round(salary * (1 - deductions["Federal Taxes"])) as salary_minus_fed_taxes  from employees limit 2;

--用as给列设置别名（有的是源表已存在的列，有的是新产生的列，取名的就会很有意义）

6.1.7嵌套select语句

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
select e.name,e.salary_minus_fed_taxes > 70000;

JASON    10000.0    0.2    80000

--第一个select结果集起了别名e，然后从e里面查询name和salary_minus_fed_taxes，并约定后者的值>7000

 

6.1.8 CASE...WHEN...THEN句式（类似于if条件语句）：处理单个列的查询结果

select name,salary,
    case
        when salary < 50000.0 then 'low'
        when salary >= 50000.0 and salary < 70000.0 then 'middle'
        when salary >= 70000.0 and salary < 100000.0 then 'high'
        else 'very high'
    end as bracket from employees;

--end as bracket????

Jason    100000.0    very high
Mary     80000.0     high
Bill     70000.0     high
Boss     200000.0    very high
...

6.1.9 Hive可以避免MapReduce的情况

1.select           2.where

select * from employees;
where country = 'US' and state = 'CA' limit 10;
--此语句可以避免触发MR

最好将下面设置语句增加到$HOME/.hiverc配置文件中（可以尝试使用本地模式执行其他操作）

set hive.exec.mode.local.auto=true;

6.2 where语句

select：选取；where：过滤

select * from employees
where country = 'US' and state = 'CA';

求税后工资> 70，000的查询结果：

select name,salary,deductions["Federal Taxes"],salary * (1-deductions["Federal Taxes"])
from employees
where round(salary * (1-deductions["Federal Taxes"])) > 70000;
Jason    100000.0    0.2    80000.0
--查找税后工资>7000的结果

--我们想为salary * (1-deductions["Federal Taxes"])设置一个别名，但不幸的是，在where语句中不支持别名，但我们可以改为select嵌套语句

使用select嵌套语句：

select e.* from 
    ( select name,salary,deductions["Federal Taxes"] as ded,
    salary * (1 - deductions["Federal Taxes"]) as salasry_minus_fed_taxes
    from employees ) e 
where round(e.salary_minus_fed_taxes) > 70000;
Jason    100000.0    0.2    80000.0
...

6.2.2 关于浮点数比较

select name,salary,deductions["Federal Taxes"]
from employees where deductions["Federal Taxes"] > 0.2;
Jason    100000.0    0.2
Mary     80000.0     0.2
Boss     200000.0    0.3
Fred     150000.0    0.3

--为什么把0.2的值也输出了？？

解析：

hive会默认把float数0.2保存为double类型。0.2的float类型实际上是0.200001，而对于double类型是0.2000000001（8字节的double值具有更多的小数位），表中float通过Hive转换为double时，其产生的double值是0.20000100000，这个值实际要比0.2000000001，这就是为什么查询结果像是使用了>=而不是>l了。

解决办法：

1.TEXTFILE文件读取数据，Hive会从数据文件中读取字符串0.2，然后将其转换为真实的数字。我们可以在表模式中定义对应的字段类型为double而不是float，这样就可以对deductions['fEDERAL tAXES']这个double值和0.2这个double值进行比较，但是会增加我们查询时所需的内存消耗。

2.显示地指出0.2为float类型的。（Java只需在末尾加加上字母F或f即可，0.2f，但Hive并不支持）,Hive需要用cast操作符

select name,salary,deductions["Federal Taxes"]
--from employees where deductions["Federal Taxes"] > 0.2;
from employees where deductions["Federal Taxes"] > cast(0.2 as float);
Boss     200000.0    0.3
Fred     150000.0    0.3

--0.2的结果不见了

3.和钱相关的都避免使用浮点数

 

6.2.3  LIKE 和 RLIKE

--查询住址中街道以Ave结尾
select name,address.street from employees where address.street like '%Ave.';
Jason    1 Michigan Ave.
Todd     200 Chicago Ave.

--查询住址、城市O开头
select name,address.city from employees where address.city like 'O%';
Todd    Oak Park
Bill    Obscuria

--查询住址和街道名称中包含Chicago的雇员
select name,address.street from employees where address.street like '%Chi%';
Todd    200 Chicago Ave.

Rlike

--从表中查询所有住址街道名称中含有单词Chicago或Ontario的雇员名称和街道信息：
select name,address.street from employees where address.street RLIKE '.*(Chicago|Ontario).*';
Mary    100 Ontario St.
Todd    200 Chicago Ave.

--  . 表示任意字符匹配
--  * 表示重复左边的字符串(本例中为 .)0到无数次，
--  (x|y)  表示匹配x或者y 

可以通过LIKE子句来改写这个例子

select name,address from employees
where address.street like '%Chicago%' or address.street like 'Ontario';

6.3 GROUP BY语句

通常与聚合函数一起使用

--按照苹果公司的年份对股票记录进行分组，然后计算每年的平均年收盘价
select year(ymd), avg(price_close) from stocks
where exchange = 'NASDAQ' and symbol = 'AAPL'
group bby year(ymd);
1984    25.57...
1985    20.19...
...

HAVING语句：

完成原本需要子查询才能对group by语句产生的分组进行条件过滤的任务

--在上个结果中查询平均收盘价>50.0的结果；
select year(ymd),avg(price_close) from stocks
where exchange = 'NASDAQ' and symbol = 'AAPL'
group by year(ymd)
    having avg(price_close) > 50.0;
1987    53.8896...
1991    52.4955...
...

--不使用having
select s2.year,s2.avg from
(select year(ymd) as year , avg(price_close) as avg from stocks
where exchange = 'NASDAQ' and symbol = 'APPL'
group by year(ymd) ) s2
where s2.avg > 50.0;
1987    53.8896...
1991    52.4955...
...

6.4 JOIN语句    只支持等值连接

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

--查询同一天的苹果公司和IBM的收盘价
select a.ymd,a.price_close,b.price_close
from stocks a join stocks b on a.ymd = b.ymd
where a.symbol = 'APPL' and b.symbol = 'IBM'; 
2010-01-04    214.01    132.45
2010-01-05    214.38    130.85
...
    --on子句指定了连接条件on a.ymd = b.ymd ymd为关键字；where子句限制了左边表是苹果公司，右边表是IBM
--内连接需要on条件中两个列都有值（假如APPLE的时间是从1984年9月7--，IBM是从1900年1月1，name输出应该从1984-9-7开始）

创建dividends表：

create external table if not exists dividends(
    ymd    string,
    diviidend    float,
)
partitioned by (exchange string,symbol string)
row format delimated fields terminated by ',';

select s.ymd,s.symbol,s.price_close,d.dividend
from stocks s JOIN dividends d on s.ymd = d.ymd and s.symbol = d.symbol
where s.symbol = 'AAPL';
1987-05-11    AAPL    77.0    0.015
1987-08-10    APPL    48.25   0.015
...

--对多于两张表的多张表进行连接操作
select a.ymd,a.price_close,b.price_close,c.price_close
from stocks a JOIN stocks b on a.ymd = b.ymd
              JOIN stocks c on a.ymd = c.ymd
where a.symbol = 'AAPL' and b.symbol = 'IBM' and c.symbol = 'GE';
2010-01-04    214.01    132.45    15.45
...

--Hive会对每对JOIN连接对象启动一个MR任务，（a join b）启动一个MR，这个结果join c再启动一个MR，Hive执行顺序是从左到右的

6.4.2 JOIN优化

Hive查询时默认最后一个表是大表，会尝试将其他的表缓存起来，然后扫描最后那个表进行计算

--错误的将小表dividends放在了最后面：
select s.ymd,s.symbol,s.price_close,d.dividend
from stocks s JOIN dividends d on s.ymd = d.ymd and s.symbol = d.symbol
where s.symbol = 'AAPL';

--修改：交换stocks和dividends的位置
select s.ymd,s.symbol,s.price_close,d.dividend
from divdends d join stocks s on s.ymd = d.ymd and s.symbol = d.symbol
where s.symbol = 'AAPL';

通过标记告诉Hive哪张表是大表      /*+STREANMTABLE(s)*/

select /*+STREANMTABLE(s)*/ s.ymd,s.symbol,s.price_close,d.dividend
from stocks s join dividends s on s.ymd = d.ymd and s.symbol = d.symbol
where s.symbol = 'AAPL';
--此时Hive会尝试将表stocks作为驱动表，缓存dividends表到内存中

还有一个优化：map-side  JOIN，参考6.4.9

6.4.3 LEFT OUTER JOIN（左外连接通过关键字left outer进行标识）

select s.ymd,s.symbol,s.price_close,d.dividend
from stocks s LEFT OUTER JOIN dividends d on s.ymd = d.ymd AND s.symbol = d.symbol
where s.symbol = 'AAPL';
1987-05-01    AAPL    80.0    NULL
1987-05-04    APPL    79.75   NULL
...
1987-05-11    AAPL    77.0    0.015
...
--join操作符左表中符合where子句的所有记录都会被返回，右表中没有符合on后面的连续条件记录，返回NULL(dividends是股息)

6.4.4  OUTER JOIN

select s.ymd,s.symbol,s.price_close,d.dividend
from stocks s LEFT OUTER JOIN dividends d on s.ymd = d.ymd AND s.symbol = d.symbol
where s.symbol = 'AAPL' AND s.exchange = 'NASDAQ' AND d.exchange = 'NASDAQ';
1987-05-11    APPL    77.0    0.015
1987-08-10    APPL    48.25   0.015
1987-11-17    APPL    35.0    0.02
...
    --先执行JOIN语句，然后再将结果通过where语句中进行过滤
--因为d.exchange字段中大多数为NULL，所以此语句优化实际是过滤掉了非股息支付日的所有记录，这个效果与INNER JOIN是一样的 

select s.ymd,s.symbol,s.price_close,d.dividend
from stocks s LEFT OUTER JOIN dividends d on s.ymd = d.ymd AND s.symbol = d.symbol
where s.symbol = 'AAPL' AND s.exchange = 'NASDAQ' ;
1987-05-07    APPL    80.25   NULL
1987-05-08    APPL    79.0    NULL
1987-05-11    APPL    77.0    0.015
1987-08-12    APPL    75.5    NULL
...

外连接不能将where语句中的内容放到on语句中，但是内连接是可以的，但是有一个解决办法让外连接也能达到这样的效果，就是使用select嵌套语句。

select s.ymd,s.symbol,s.price_close,d.dividend
from stocks s LEFT OUTER JOIN dividends d on s.ymd = d.ymd AND s.symbol = d.symbol AND s.symbol = 'AAPL' AND s.exchange = 'NASDAQ' ;
...
1962-01-02    GE    74.75    NULL
1962-01-02    IBM   572.0    NULL
1962-01-02    GE    74.0     NULL
...
--将外连接的where语句放到on语句中，外连接会忽略掉分区过滤条件，内连接可以这样使用

解决方案：select嵌套

select s.ymd,s.symbol,s.price_close,d.dividend from
(select * from stocks where symbol = 'APPL' AND exchange = 'NASDAQ') s
LEFT OUTER JOIN
(select * from dividends where symbol = 'AAPL' AND exchange = 'NASDAQ') d
ON s.ymd = d.ymd;
...
1988-02-10    AAPL    41.0    NULL
1988-02-11    APPL    40.63   NULL
1988-02-12    AAPL    41.0    0.02
...
--在数据进行连接之前会先进行分区过滤

6.4.5 RIGHT OUTER JOIN

    --右外连接 会返回右边表所有符合where语句的记录，左表中匹配不上的字段值用NULL代替
select s.ymd,s.symbol,s.price_close,d.dividend
from dividends d RIGHT 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    APPL    79.0     NULL
1987-05-11    AAPL    77.0     0.015
...
--与之前的LEFT OOUTER JOIN相比，将from语句中dividends与stocks变换位置，与LEFT OUTER JOIN结果一致

6.4.6 FULL OUTER JOIN：返回所有符合where语句条件的所有记录，任一表的指定字段没有符合条件的值的话，返回NULL

select s.ymd,s.symbol,s.price_close,d.dividend
from dividends d LEFT OUTER JOIN stocks s on s.ymd = d.ymd AND s.symbol = d.symbol
where s.symbol = 'AAPL' ;
1987-05-07    APPL    80.25   NULL
1987-05-08    APPL    79.0    NULL
1987-05-11    APPL    77.0    0.015
1987-08-12    APPL    75.5    NULL
...

6.4.7 LEFT SEMI-JOIN

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 LEFT改写


select s.ymd,s.symbol,s.price_close 
from stocks s LEFT SEMT JOIN dividends d ON s.ymd = s.ymd AND s.symbol = d.symbol;
...
1962-11-05    IBM    361.5
1962-08-07    IBM    373.25
1962-05-08    IBM    459.5
...
--只返回左边表的记录，前提是其记录满足右表ON语句中的判定条件
        --此语法select和where语句中不能引用右边的字段
--同时,Hive不支持右半开连接（RIGHT SEMI JOIN）


--SEMI JOIN比通常的INNER JOIN要高效，原因如下：
--左表中一条指定的记录，在右表中一旦找到匹配的记录，Hive就会立即停止扫描。从这点来看，左边表中选择的列是可以预测的。

6.4.8 笛卡尔积  JOIN(笛卡尔结果集 = 左边行数*右边行数)

select * from stocks join dividents;
--左表有5行，右表有6行，结果就有5*6=30行

如果错误的使用了笛卡尔积连接，会导致一个执行时间长，运行缓慢的笛卡尔积查询，下面这个查询在很多数据库中会被优化成INNER JOIN,但在Hive中没有优化：

select * from stocks JOIN dividends
where stocks.symbol = dividends.symbol and stock.symbol='AAPL'；
--Hive中，在where语句执行前会先进行完全笛卡尔积运算，通过下面设置，Hive会阻止笛卡尔积查询

set hive.mapred.mode=strict;
--此语句禁止笛卡尔查询（JOIN）

6.4.9 map side join

在最大的表通过mapper时将小表完全放到内存中。Hive可以在map端执行过程（简称为map-side join）因为Hive可以和内存中的小表进行逐一匹配，从而省略掉常规操作所需的reduce过程，有时还可以减少map过程的执行步骤

    --    Hive 0.7版本以前
select /*+ MAPJOIN(D) */ s.ymd,s.symbol,s.price_close,d.dividend
from stocks s JOIN dividends d ON s.ymd = d.ymd AND s.symbol = d.symbol
where s.symbol = 'AAPL';

    --0.7版本以后,需要先开启set hive.auto.convert.join=true;才会开启优化，上面的方式，0.7版本以后也是支持的
set hive.auto.convert.join=true;

select s.ymd,s.symbol,s.price_close,d.dividend
from stocks s JOIN dividends d ON s.ymd = d.ymd AND s.symbol = d.symbol
where s.symbol = 'AAPL';



    --用户还可以配置能够使用这个优化的小表的大小，单位是字节
hive.mapjoin.smalltable.filesize=25000000;

--Tip:右外连接和全外连接不支持这个优化

6.5 ORDER BY 和 SORT BY    ORDER BY会对查询结果集进行全局排序，所有的数据都通过一个rducer进行处理，耗时长

Hive增加了一个可供选择的方式，即sort by，只在每个reducer中对数据进行排序，局部排序。

    --ORDER BY
select s.ymd,s.symbol,s.price_close
from stocks s
order by s.ymd ASC,s.symbol DESC;

--ASC表示升序排序，（默认就是升序排序，可以不加），DESC表示逆序排序

    --SORT BY
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中是如何划分的)

MRjob中传输数据都是按照键值对进行组织的，因此Hive在将用户查询语句转换为MRjob时，其必须在内部使用这个功能

    --Tips：Hive 要求 DISTRIBUTE BY 要在SORT BY之前
select s.ymd,s.symbol,s.price_close
from stocks s 
distribute by s.symbol
sort by s.symbol ASC, s.ymd ASC;
1984-09-07    AAPL    26.5
1984-09-10    AAPL    26.37
1984-09-11    AAPL    26.87
...
    --DISTRIBUTE BY和GROUP BY 在其控制着reducer如何接受这一行行数据进行处理这方面是类似的，SORT BY控制reducer如何排序
--DISTRIBUTE BY保证相同股票交易码的记录会分发到同一个reducer中进行处理，然后SORT BY按照我们的期望对数据进行排序

6.7 CLUSTER BY

select s.ymd,s.symbol,s.price_close
from stocks s 
distribute by s.symbol
cluster by s.symbol ASC;