cd /kkb/install/hive-1.1.0-cdh5.14.2
bin/hive

cd /kkb/install/hive-1.1.0-cdh5.14.2
bin/hive --service hiveserver2

cd /kkb/install/hive-1.1.0-cdh5.14.2
nohup  bin/hive --service hiveserver2  &

cd /kkb/install/hive-1.1.0-cdh5.14.2
bin/beeline
beeline> !connect jdbc:hive2://node03:10000

cd /kkb/install/hive-1.1.0-cdh5.14.2/
bin/hive -e "show databases"

cd /kkb/install/
vim hive.sql

文件内容如下
create database if not exists myhive;

通过以下命令来执行我们的hive脚本
cd /kkb/install/hive-1.1.0-cdh5.14.2/
bin/hive -f /kkb/install/hive.sql

create table complex(
         col1 array<int>,
         col2 map<string,int>,
         col3 struct<a:string,b:int,c:double>
)

hive > create database db_hive;
# 或者
hive > create database if not exists db_hive;

hive> show databases;

hive> show databases like 'db_hive*';

hive> desc database db_hive;

hive> desc database extended db_hive;

hive > use db_hive;

#删除为空的数据库
hive> drop database db_hive;

#如果删除的数据库不存在，最好采用if exists 判断数据库是否存在
hive> drop database if exists db_hive;

#如果数据库中有表存在，这里需要使用cascade强制删除数据库
hive> drop database if exists db_hive cascade;

hive> load data [local] inpath 'dataPath' overwrite | into table student [partition (partcol1=val1,…)]; 
 
 hive (myhive)> load data local inpath '/kkb/install/hivedatas/score.csv' overwrite into table score3 partition(month='201806');

hive (myhive)> create table score5 like score;
hive (myhive)> insert overwrite table score5 partition(month = '201806') select s_id,c_id,s_score from score;

hive (myhive)> create table score6 as select * from score;

hive (myhive)> create external table score7 (s_id string,c_id string,s_score int) 
row format delimited fields terminated by '\t' 
location '/myscore7';

hive (myhive)>  dfs -mkdir -p /myscore7;
hive (myhive)> dfs -put /kkb/install/hivedatas/score.csv /myscore7;

hive (myhive)> select * from score7;

hive (myhive)> create table teacher2 like teacher;
hive (myhive)> export table teacher to  '/kkb/teacher';
hive (myhive)> import table teacher2 from '/kkb/teacher';

insert overwrite local directory '/kkb/install/hivedatas/stu' select * from stu;

insert overwrite local directory '/kkb/install/hivedatas/stu2' 
row format delimited fields terminated by  ',' 
select * from stu;

insert overwrite  directory '/kkb/hivedatas/stu'  
row format delimited fields terminated by  ','  
select * from stu;

bin/hive -e 'select * from myhive.stu;' > /kkb/install/hivedatas/student1.txt

export table  myhive.stu to '/kkb/install/hivedatas/stuexport';

select * from stu;

select id,name from stu;

select id,name as stuName from stu;

select count(*) cnt from score;

select max(s_score) from score;

select min(s_score) from score;

select sum(s_score) from score;

select avg(s_score) from score;

select  * from score limit 5;

select  * from score where s_score > 60;

select s_id,avg(s_score) from score group by s_id;

select s_id,max(s_score) from score group by s_id;

select s_id,avg(s_score) from score group by s_id;

select s_id,avg(s_score) as avgScore from score group by s_id having avgScore > 60;

#hive当中创建course表并加载数据
create table course (c_id string,c_name string,t_id string) 
row format delimited fields terminated by '\t';
load data local inpath '/kkb/install/hivedatas/course.csv' overwrite into table course;

select * from teacher t join course c on t.t_id = c.t_id;

select * from teacher t inner join course c  on t.t_id = c.t_id;

select * from teacher t left outer join course c  on t.t_id = c.t_id;

select * from teacher t right outer join course c  on t.t_id = c.t_id;

select * from teacher t full outer join course c  on t.t_id = c.t_id;

select * from teacher t left join course c on t.t_id = c.t_id 
left join score s on c.c_id = s.c_id 
left join stu on s.s_id = stu.id;

select * from score  s order by s_score desc ;

select s_id,avg(s_score) avgscore  from score group by s_id order by avgscore desc;

set mapreduce.job.reduces=3;

set mapreduce.job.reduces;

select * from score s sort by s.s_score;

insert overwrite local directory '/kkb/install/hivedatas/sort' 
select * from score s sort by s.s_score;

set mapreduce.job.reduces=3;

insert overwrite local directory '/kkb/install/hivedatas/distribute' 
select * from score distribute by s_id sort by s_score;

--以下两种写法等价

insert overwrite local directory '/kkb/install/hivedatas/distribute_sort' select * from score distribute  by s_score sort  by s_score;


insert overwrite local directory '/kkb/install/hivedatas/cluster' select * from score  cluster by s_score;

cd /kkb/install/hive-1.1.0-cdh5.14.2/
nohup bin/hive --service hiveserver2 2>&1 &

配置文件  hive-site.xml

命令行参数  启动hive客户端的时候可以设置参数

参数声明   进入客户端以后设置的一些参数  set

bin/hive -hiveconf hive.root.logger=INFO,console

set mapred.reduce.tasks=100;

参数声明  >   命令行参数   >  配置文件参数（hive）

${hiveconf:key} 
bin/hive --hiveconf "mapred.job.queue.name=root.default"

使用前缀:
 ${hivevar:key}
不使用前缀:
 ${key}
--hivevar  name=zhangsan    ${hivevar:name}  
也可以这样取值  ${name}

define与hivevar用途完全一样，还有一种简写“-d
bin/hive --hiveconf "mapred.job.queue.name=root.default" -d my="201809" --database mydb
执行SQL
select * from mydb where concat(year, month) = ${my} limit 10;

select * from student 
left join score on student.s_id = score.s_id 
where score.month = '201807' and score.s_score > 80 and score.c_id = 03;

hive (myhive)> create external table student
(s_id string,s_name string,s_birth string , s_sex string ) 
row format delimited fields terminated by '\t';

hive (myhive)> load data local inpath '/kkb/install/hivedatas/student.csv' overwrite into table student;

cd /kkb/instal/hivedatas

vim hivevariable.hql
use myhive;
select * from student 
left join score on student.s_id = score.s_id where score.month = ${hiveconf:month} 
and score.s_score > ${hivevar:s_score} and score.c_id = ${c_id};

[root@node03 hive-1.1.0-cdh5.14.2]# bin/hive --hiveconf month=201807 --hivevar s_score=80 --hivevar c_id=03  -f /kkb/install/hivedatas/hivevariable.hql

1．查看系统自带的函数
hive> show functions;
2．显示自带的函数的用法
hive> desc function upper;
3．详细显示自带的函数的用法
hive> desc function extended upper;

hive> select round(3.1415926) from tableName;
3
hive> select round(3.5) from tableName;
4
hive> create table tableName as select round(9542.158) from tableName;

hive> select round(3.1415926,4) from tableName;
3.1416

hive> select floor(3.1415926) from tableName;
3
hive> select floor(25) from tableName;
25

hive> select ceil(3.1415926) from tableName;
4
hive> select ceil(46) from tableName;
46

hive> select ceiling(3.1415926) from tableName;
4
hive> select ceiling(46) from tableName;
46

hive> select rand() from tableName;
0.5577432776034763
hive> select rand() from tableName;
0.6638336467363424
hive> select rand(100) from tableName;
0.7220096548596434
hive> select rand(100) from tableName;
0.7220096548596434

hive> select from_unixtime(1323308943,'yyyyMMdd') from tableName;
20111208

hive> select unix_timestamp() from tableName;
1323309615

hive> select unix_timestamp('2011-12-07 13:01:03') from tableName;
1323234063

hive> select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from tableName;
1323234063

hive> select to_date('2011-12-08 10:03:01') from tableName;
2011-12-08

hive> select year('2011-12-08 10:03:01') from tableName;
2011
hive> select year('2012-12-08') from tableName;
2012

hive> select month('2011-12-08 10:03:01') from tableName;
12
hive> select month('2011-08-08') from tableName;
8

hive> select day('2011-12-08 10:03:01') from tableName;
8
hive> select day('2011-12-24') from tableName;
24

hive> select hour('2011-12-08 10:03:01') from tableName;
10

hive> select minute('2011-12-08 10:03:01') from tableName;
3

hive> select second('2011-12-08 10:03:01') from tableName;
1

hive> select datediff('2012-12-08','2012-05-09') from tableName;
213

hive> select date_add('2012-12-08',10) from tableName;
2012-12-18

hive> select date_sub('2012-12-08',10) from tableName;
2012-11-28

hive> select if(1=2,100,200) from tableName;
200
hive> select if(1=1,100,200) from tableName;
100

hive> select COALESCE(null,'100','50') from tableName;
100

hive> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;
mary
hive> Select case 200 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName;
tim

hive> select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;
mary
hive> select case when 1=1 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName;
tom

#创建表
create table employee(
empid int, 
deptid int,
sex string,
salary double
)row format delimited 
fields terminated by ' ';

# employee.txt 数据文件
1 10 female 5500.0
2 10 male 4500.0
3 20 female 1900.0
4 20 male 4800.0
5 30 female 6500.0
6 30 female 14500.0
7 30 male 44500.0
8 40 male 6500.0
9 40 male 7500.0


#表结构
# desc employee;
empid                   int                                         
deptid                  int                                         
sex                     string                                      
salary                  double  
#表记录
select * from employee;
1       10      female  5500.0
2       10      male    4500.0
3       20      female  1900.0
4       20      male    4800.0
5       40      female  6500.0
6       40      female  14500.0
7       40      male    44500.0
8       50      male    6500.0
9       50      male    7500.0

################### 将员工按照薪资待遇划分等级  薪水小于五千的，打上低等收入的标签，收入在5000到10000打上中等收入标签，收入大于10000打上高等收入标签 ###################
################### 将员工按照性别打上标识       ###################


select *,
case 
when salary < 5000 then "低等收入" 
when salary>= 5000 and salary < 10000 then "中等收入"
when salary > 10000 then "高等收入"  
end  as level,
case sex
when "female" then 1 
when "male" then 0
end as flag 
from employee;
#统计结果
1       10      female  5500.0  中等收入        1
2       10      male    4500.0  低等收入        0
3       20      female  1900.0  低等收入        1
4       20      male    4800.0  低等收入        0
5       40      female  6500.0  中等收入        1
6       40      female  14500.0 高等收入        1
7       40      male    44500.0 高等收入        0
8       50      male    6500.0  中等收入        0
9       50      male    7500.0  中等收入        0

hive> select length('abcedfg') from tableName;

hive> select reverse('abcedfg') from tableName;
gfdecba

hive> select concat('abc','def','gh') from tableName;
abcdefgh

hive> select concat_ws(',','abc','def','gh')from tableName;
abc,def,gh

hive> select substr('abcde',3) from tableName;
cde
hive> select substring('abcde',3) from tableName;
cde
hive>  select substr('abcde',-1) from tableName;  （和ORACLE相同）
e

hive> select substr('abcde',3,2) from tableName;
cd
hive> select substring('abcde',3,2) from tableName;
cd
hive>select substring('abcde',-2,2) from tableName;
de

hive> select upper('abSEd') from tableName;
ABSED
hive> select ucase('abSEd') from tableName;
ABSED

hive> select lower('abSEd') from tableName;
absed
hive> select lcase('abSEd') from tableName;
absed

hive> select trim(' abc ') from tableName;
abc

hive> select parse_url
('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') 
from tableName;
www.tableName.com 
hive> select parse_url
('https://www.tableName.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1')
 from tableName;
v1

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 tableName;

hive> select repeat('abc',5) from tableName;
abcabcabcabcabc

hive> select split('abtcdtef','t') from tableName;
["ab","cd","ef"]

hive> select count(*) from tableName;
20
hive> select count(distinct t) from tableName;
10

hive> select sum(t) from tableName;
100
hive> select sum(distinct t) from tableName;
70

hive> select avg(t) from tableName;
50
hive> select avg (distinct t) from tableName;
30

hive> select min(t) from tableName;
20

hive> select max(t) from tableName;
120

create table score_map(name string, score map<string,int>)
row format delimited fields terminated by '\t' 
collection items terminated by ',' map keys terminated by ':';

创建数据内容如下并加载数据
cd /kkb/install/hivedatas/
vim score_map.txt

zhangsan	数学:80,语文:89,英语:95
lisi	语文:60,数学:80,英语:99

加载数据到hive表当中去
load data local inpath '/kkb/install/hivedatas/score_map.txt' overwrite into table score_map;

map结构数据访问：
获取所有的value：
select name,map_values(score) from score_map;

获取所有的key：
select name,map_keys(score) from score_map;

按照key来进行获取value值
select name,score["数学"]  from score_map;

查看map元素个数
select name,size(score) from score_map;

创建struct表
hive> create table movie_score( name string,  info struct<number:int,score:float> )row format delimited fields terminated by "\t"  collection items terminated by ":"; 

加载数据
cd /kkb/install/hivedatas/
vim struct.txt

ABC	1254:7.4  
DEF	256:4.9  
XYZ	456:5.4

加载数据
load data local inpath '/kkb/install/hivedatas/struct.txt' overwrite into table movie_score;


hive当中查询数据
hive> select * from movie_score;  
hive> select info.number,info.score from movie_score;  
OK  
1254    7.4  
256     4.9  
456     5.4

hive> create table  person(name string,work_locations array<string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';

加载数据到person表当中去
cd /kkb/install/hivedatas/
vim person.txt

数据内容格式如下
biansutao	beijing,shanghai,tianjin,hangzhou
linan	changchu,chengdu,wuhan

加载数据
hive > load  data local inpath '/kkb/install/hivedatas/person.txt' overwrite into table person;

查询所有数据数据
hive > select * from person;

按照下表索引进行查询
hive > select work_locations[0] from person;

查询所有集合数据
hive  > select work_locations from person; 

查询元素个数
hive >  select size(work_locations) from person;

hive> select size(t) from map_table2;
2

hive> select size(t) from arr_table2;
4

hive> select cast('1' as bigint) from tableName;
1

需求：现在有数据格式如下
zhangsan	child1,child2,child3,child4	k1:v1,k2:v2
lisi	child5,child6,child7,child8	 k3:v3,k4:v4

字段之间使用\t分割，需求将所有的child进行拆开成为一列
 
+----------+--+
| mychild  |
+----------+--+
| child1   |
| child2   |
| child3   |
| child4   |
| child5   |
| child6   |
| child7   |
| child8   |
+----------+--+

将map的key和value也进行拆开，成为如下结果

+-----------+-------------+--+
| mymapkey  | mymapvalue  |
+-----------+-------------+--+
| k1        | v1          |
| k2        | v2          |
| k3        | v3          |
| k4        | v4          |
+-----------+-------------+--+

hive (default)> create database hive_explode;
hive (default)> use hive_explode;

hive (hive_explode)> create  table hive_explode.t3(name string,children array<string>,address Map<string,string>) row format delimited fields terminated by '\t'  collection items    terminated by ','  map keys terminated by ':' stored as textFile;

cd  /kkb/install/hivedatas/

vim maparray
数据内容格式如下

zhangsan	child1,child2,child3,child4	k1:v1,k2:v2
lisi	child5,child6,child7,child8	k3:v3,k4:v4

hive (hive_explode)> load data local inpath '/kkb/install/hivedatas/maparray' into table hive_explode.t3;

hive (hive_explode)> SELECT explode(children) AS myChild FROM hive_explode.t3;

hive (hive_explode)> SELECT explode(address) AS (myMapKey, myMapValue) FROM hive_explode.t3;

a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]

4900
2090
6987

hive (hive_explode)> create table hive_explode.explode_lateral_view  (area string, goods_id string, sale_info string)  
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' STORED AS textfile;

cd /kkb/install/hivedatas
vim explode_json

a:shandong,b:beijing,c:hebei|1,2,3,4,5,6,7,8,9|[{"source":"7fresh","monthSales":4900,"userCount":1900,"score":"9.9"},{"source":"jd","monthSales":2090,"userCount":78981,"score":"9.8"},{"source":"jdmart","monthSales":6987,"userCount":1600,"score":"9.0"}]

hive (hive_explode)> load data local inpath '/kkb/install/hivedatas/explode_json' overwrite into table hive_explode.explode_lateral_view;

hive (hive_explode)> select explode(split(goods_id,',')) as goods_id from hive_explode.explode_lateral_view;

hive (hive_explode)> select explode(split(area,',')) as area from hive_explode.explode_lateral_view;

hive (hive_explode)> select explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')) as  sale_info from hive_explode.explode_lateral_view;

hive (hive_explode)> select get_json_object(explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')),'$.monthSales') as  sale_info from hive_explode.explode_lateral_view;


然后出现异常FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions
UDTF explode不能写在别的函数内
如果你这么写，想查两个字段，select explode(split(area,',')) as area,good_id from explode_lateral_view;
会报错FAILED: SemanticException 1:40 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'good_id'
使用UDTF的时候，只支持一个字段，这时候就需要LATERAL VIEW出场了

hive (hive_explode)> select goods_id2,sale_info from explode_lateral_view LATERAL VIEW explode(split(goods_id,','))goods as goods_id2;

hive (hive_explode)> select goods_id2,sale_info,area2 from explode_lateral_view  LATERAL VIEW explode(split(goods_id,','))goods as goods_id2 LATERAL VIEW explode(split(area,','))area as area2;

hive (hive_explode)> select get_json_object(concat('{',sale_info_1,'}'),'$.source') as source, get_json_object(concat('{',sale_info_1,'}'),'$.monthSales') as monthSales, get_json_object(concat('{',sale_info_1,'}'),'$.userCount') as monthSales,  get_json_object(concat('{',sale_info_1,'}'),'$.score') as monthSales from explode_lateral_view   LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{'))sale_info as sale_info_1;

射手座,A            老王|冰冰
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋

cd /kkb/install/hivedatas
vim constellation.txt

孙悟空	白羊座	A
老王	射手座	A
宋宋	白羊座	B       
猪八戒	白羊座	A
凤姐	射手座	A

hive (hive_explode)> create table person_info(  name string,  constellation string,  blood_type string)  row format delimited fields terminated by "\t";


hive (hive_explode)> load data local inpath '/kkb/install/hivedatas/constellation.txt' into table person_info;

hive (hive_explode)> select t1.base, concat_ws('|', collect_set(t1.name)) name from    (select name, concat(constellation, "," , blood_type) base from person_info) t1 group by  t1.base;

cd /kkb/install/hivedatas

vim movie.txt
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼2》	战争,动作,灾难

《疑犯追踪》	悬疑
《疑犯追踪》	动作
《疑犯追踪》	科幻
《疑犯追踪》	剧情
《Lie to me》	悬疑
《Lie to me》	警匪
《Lie to me》	动作
《Lie to me》	心理
《Lie to me》	剧情
《战狼2》	战争
《战狼2》	动作
《战狼2》	灾难

hive (hive_explode)> create table movie_info(movie string, category array<string>) row format delimited fields terminated by "\t" collection items terminated by ",";


load data local inpath "/kkb/install/hivedatas/movie.txt" into table movie_info;

hive (hive_explode)>  select movie, category_name  from  movie_info lateral view explode(category) table_tmp as category_name;

hive (hive_explode)>  create table test_udf(col1 int,col2 int) row format delimited fields terminated by ',';

cd /kkb/install/hivedatas

vim test_udf

1,2
4,3
6,4
7,5
5,6

hive (hive_explode)> load data local inpath '/kkb/install/hivedatas/test_udf' overwrite into table test_udf;

hive (hive_explode)> select reflect("java.lang.Math","max",col1,col2) from test_udf;

hive (hive_explode)> create table test_udf2(class_name string,method_name string,col1 int , col2 int) row format delimited fields terminated by ',';

cd /export/servers/hivedatas

vim test_udf2

java.lang.Math,min,1,2
java.lang.Math,max,2,3

hive (hive_explode)> load data local inpath '/kkb/install/hivedatas/test_udf2' overwrite into table test_udf2;

hive (hive_explode)> select reflect(class_name,method_name,col1,col2) from test_udf2;

hive (hive_explode)> select reflect("org.apache.commons.lang.math.NumberUtils","isNumber","123");

1）开启hive中间传输数据压缩功能
hive (default)>set hive.exec.compress.intermediate=true;

2）开启mapreduce中map输出压缩功能
hive (default)>set mapreduce.map.output.compress=true;

3）设置mapreduce中map输出数据的压缩方式
hive (default)>set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec;

4）执行查询语句
   select count(1) from score;

1）开启hive最终输出数据压缩功能
hive (default)>set hive.exec.compress.output=true;

2）开启mapreduce最终输出数据压缩
hive (default)>set mapreduce.output.fileoutputformat.compress=true;

3）设置mapreduce最终数据输出压缩方式
hive (default)> set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;

4）设置mapreduce最终数据输出压缩为块压缩
hive (default)>set mapreduce.output.fileoutputformat.compress.type=BLOCK;

5）测试一下输出结果是否是压缩文件
insert overwrite local directory '/kkb/install/hivedatas/snappy' select * from score distribute by s_id sort by s_id desc;

use myhive;
create table log_text (
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE ;

load data local inpath '/kkb/install/hivedatas/log.data' into table log_text ;

dfs -du -h /user/hive/warehouse/myhive.db/log_text;
18.1 M  /user/hive/warehouse/log_text/log.data

create table log_orc(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc ;

insert into table log_orc select * from log_text ;

dfs -du -h /user/hive/warehouse/myhive.db/log_orc;

2.8 M  /user/hive/warehouse/log_orc/123456_0

create table log_parquet(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS PARQUET ;

insert into table log_parquet select * from log_text ;

dfs -du -h /user/hive/warehouse/myhive.db/log_parquet;

13.1 M  /user/hive/warehouse/log_parquet/123456_0

1）TextFile
hive (default)> select count(*) from log_text;
_c0
100000
Time taken: 21.54 seconds, Fetched: 1 row(s)  

2）ORC
hive (default)> select count(*) from log_orc;
_c0
100000
Time taken: 20.867 seconds, Fetched: 1 row(s)  

3）Parquet
hive (default)> select count(*) from log_parquet; 
_c0
100000
Time taken: 22.922 seconds, Fetched: 1 row(s)

存储文件的查询速度总结：
ORC > TextFile > Parquet

create table log_orc_none(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc tblproperties ("orc.compress"="NONE");

insert into table log_orc_none select * from log_text ;

dfs -du -h /user/hive/warehouse/myhive.db/log_orc_none;

7.7 M  /user/hive/warehouse/log_orc_none/123456_0

create table log_orc_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc tblproperties ("orc.compress"="SNAPPY");

insert into table log_orc_snappy select * from log_text ;

dfs -du -h /user/hive/warehouse/myhive.db/log_orc_snappy ;
3.8 M  /user/hive/warehouse/log_orc_snappy/123456_0

2.8 M  /user/hive/warehouse/log_orc/123456_0

set hive.fetch.task.conversion=none;
select * from score;
select s_id from score;
select s_id from score limit 3;

set hive.fetch.task.conversion=more;
select * from score;
select s_id from score;
select s_id from score limit 3;

--开启本地模式，并执行查询语句
set hive.exec.mode.local.auto=true;  //开启本地mr

--设置local mr的最大输入数据量，当输入数据量小于这个值时采用local  mr的方式，
--默认为134217728，即128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;

--设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，
--默认为4
set hive.exec.mode.local.auto.input.files.max=5;

--执行查询的sql语句
select * from student cluster by s_id;

--关闭本地运行模式
set hive.exec.mode.local.auto=false;
select * from student cluster by s_id;

select  count(distinct s_id)  from score;

select count(s_id) from score group by s_id; 在map端进行聚合，效率更高

use myhive;
create table ori(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

create table nullidtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

create table jointable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

load data local inpath '/kkb/install/hivedatas/hive_big_table/*' into table ori; 
load data local inpath '/kkb/install/hivedatas/hive_have_null_id/*' into table nullidtable;

不过滤：
INSERT OVERWRITE TABLE jointable
SELECT a.* FROM nullidtable a JOIN ori b ON a.id = b.id;
结果：
No rows affected (152.135 seconds)

过滤：
INSERT OVERWRITE TABLE jointable
SELECT a.* FROM (SELECT * FROM nullidtable WHERE id IS NOT NULL ) a JOIN ori b ON a.id = b.id;
结果：
No rows affected (141.585 seconds)

set hive.exec.reducers.bytes.per.reducer=32123456;
set mapreduce.job.reduces=7;
INSERT OVERWRITE TABLE jointable
SELECT a.*
FROM nullidtable a
LEFT JOIN ori b ON CASE WHEN a.id IS NULL THEN 'hive' ELSE a.id END = b.id;
No rows affected (41.668 seconds)

set hive.exec.reducers.bytes.per.reducer=32123456;
set mapreduce.job.reduces=7;
INSERT OVERWRITE TABLE jointable
SELECT a.*
FROM nullidtable a
LEFT JOIN ori b ON CASE WHEN a.id IS NULL THEN concat('hive', rand()) ELSE a.id END = b.id;

No rows affected (42.594 seconds)

create table bigtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

create table smalltable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

create table jointable2(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

hive (default)> load data local inpath '/kkb/install/hivedatas/big_data' into table bigtable;

hive (default)>load data local inpath '/kkb/install/hivedatas/small_data' into table smalltable;

--默认为true
set hive.auto.convert.join = true;

set hive.mapjoin.smalltable.filesize=26214400;

set hive.auto.convert.join = true; 默认为true

INSERT OVERWRITE TABLE jointable2
SELECT b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
FROM smalltable s
JOIN bigtable  b
ON s.id = b.id;

Time taken: 31.814 seconds

INSERT OVERWRITE TABLE jointable2
SELECT b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
FROM bigtable  b
JOIN smalltable  s
ON s.id = b.id;

Time taken: 28.46 seconds

--是否在Map端进行聚合，默认为True
set hive.map.aggr = true;
--在Map端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval = 100000;
--有数据倾斜的时候进行负载均衡（默认是false）
set hive.groupby.skewindata = true;

当选项设定为 true，生成的查询计划会有两个MR Job。第一个MR Job中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MR Job再根据预处理的数据结果按照Group By Key分布到Reduce中（这个过程可以保证相同的Group By Key被分布到同一个Reduce中），最后完成最终的聚合操作。

create table bigtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

load data local inpath '/kkb/install/hivedatas/data/100万条大表数据（id除以10取整）/bigtable' into table bigtable;


--每个reduce任务处理的数据量 默认256000000（256M）
 set hive.exec.reducers.bytes.per.reducer=32123456;
 
 select  count(distinct ip )  from log_text;
 
 转换成
 set hive.exec.reducers.bytes.per.reducer=32123456;
 select count(ip) from (select ip from log_text group by ip) t;
 
 
 虽然会多用一个Job来完成，但在数据量大的情况下，这个绝对是值得的。

create table ori(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

create table bigtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

load data local inpath '/home/admin/softwares/data/加递增id的原始数据/ori' into table ori;

load data local inpath '/home/admin/softwares/data/100万条大表数据（id除以10取整）/bigtable' into table bigtable;

SELECT a.id
FROM bigtable a
LEFT JOIN ori b ON a.id = b.id
WHERE b.id <= 10;

SELECT a.id
FROM ori a
LEFT JOIN bigtable b ON (a.id <= 10 AND a.id = b.id);

SELECT a.id
FROM bigtable a
RIGHT JOIN (SELECT id
FROM ori
WHERE id <= 10
) b ON a.id = b.id;

--开启并行执行
set hive.exec.parallel=true;
--同一个sql允许最大并行度，默认为8。
set hive.exec.parallel.thread.number=16;

--设置非严格模式（默认）
set hive.mapred.mode=nonstrict;

--设置严格模式
set hive.mapred.mode=strict;

--设置严格模式下 执行sql语句报错； 非严格模式下是可以的
select * from order_partition；

异常信息：Error: Error while compiling statement: FAILED: SemanticException [Error 10041]: No partition predicate found for Alias "order_partition" Table "order_partition"

--设置严格模式下 执行sql语句报错； 非严格模式下是可以的
select * from order_partition where month='2019-03' order by order_price; 

异常信息：Error: Error while compiling statement: FAILED: SemanticException 1:61 In strict mode, if ORDER BY is specified, LIMIT must also be specified. Error encountered near token 'order_price'

<property>
  <name>mapreduce.job.jvm.numtasks</name>
  <value>10</value>
  <description>How many tasks to run per jvm. If set to -1, there is
  no limit. 
  </description>
</property>

set  mapred.job.reuse.jvm.num.tasks=10;

<property>
  <name>mapreduce.map.speculative</name>
  <value>true</value>
  <description>If true, then multiple instances of some map tasks 
               may be executed in parallel.</description>
</property>

<property>
  <name>mapreduce.reduce.speculative</name>
  <value>true</value>
  <description>If true, then multiple instances of some reduce tasks 
               may be executed in parallel.</description>
</property>

<property>
    <name>hive.mapred.reduce.tasks.speculative.execution</name>
    <value>true</value>
    <description>Whether speculative execution for reducers should be turned on. </description>
  </property>

#设置为true为激活中间数据压缩功能，默认是false，没有开启
set hive.exec.compress.intermediate=true;
#设置中间数据的压缩算法
set mapred.map.output.compression.codec= org.apache.hadoop.io.compress.SnappyCodec;

set hive.exec.compress.output=true;
set mapred.output.compression.codec= 
org.apache.hadoop.io.compress.SnappyCodec;