Hive笔记

Hive是什么

同hbase是数据库？还是同impala是sql查询引擎？
06-08理解
基于hdfs的数据库，同时提供hql作为数据库语言，hive由于其稳定性通常作为离线数据库。而impala则更多的只是用来做查询，hive做增删改建表。

数据仓库软件/NOSQL数据库
数仓基础工具，架构在HDFS、HBASE之上。 元存储，HiveQL
Hive是一个数据仓库软件。 使用它，我们可以访问和管理基于Hadoop的大型分布式数据集。

Hive是一个数据仓库基础工具，在Hadoop中用来处理结构化数据。它架构在Hadoop之上，总归为大数据，并使得查询和分析方便。并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。

存储架构在一个数据库中并处理数据到HDFS
提供SQL类型语言查询叫HiveQL或HQL

Hive操作

• Hive分区
  例如，一个名为Tab1表包含雇员数据，如 id, name, dept 和yoj (即加盟年份)。假设需要检索所有在2012年加入，查询搜索整个表所需的信息员工的详细信息。但是，如果用年份分区雇员数据并将其存储在一个单独的文件，它减少了查询处理时间。

使用partioned by (xxx)来创建表的分区，比方说

create table table_name (
  id                int,
  dtDontQuery       string,
  name              string
)
partitioned by (date string)

注意，假如table里有date字段，那么分区的时候不要用date了，不然当查询的时候写where data=xxx时会出错，即下面这种情况：

create table table_name (
  id                int,
  date       string,
  name              string
)
partitioned by (date string)

尽量不用date这个字，根据系统设置不同，可能会触发不同的错误，如FAILED: ParseException line 3:16 Failed to recognize predicate ‘date’. Failed rule: ‘identifier’ in column specification，有的时候又遇不到，换一个词就好了

hive静态分区、动态分区

Hive-显示当前库、显示表的头部信息
hive常用查询命令
hive常用指令
1、进入hive数据库：hive
2、查看hive中的所有数据库：show databases;
3、用default数据库：use default;
4、查看所有的表：show tables;
5、查询表结构：desc mytest（表名）;
6、查询表数据： select * from mytest（表名）；
7、创建数据库：hive> CREATE SCHEMA userdb;
（1）创建数据库，通过location，指定在hdfs的路径。
这样数据库存储位置就是’/locate’，而非默认的’/user/hive/warehouse’

create database if not exists db01_loc LOCATION ‘/locate’;

Hive LOCATION指令

11、创建表
hive> create table if not exists employee (eid int,name String,salary String,destination String)

（1）如果增加分区必须在创建表的时候就创建分区，不然就会报错，创建分区的命令>partition by ‘根据哪个字段分区’，

 hive> create table employee (id int, name String, dept String)
    > PARTITIONED BY (year int)
    > ROW FORMAT delimited
    > fields terminated by '\t'
    > lines terminated by '\n'
    > stored as textfile;

CREATE TABLE IF NOT EXISTS ziwen_test_0 ( eid int, name String, salary String, destination String) 
COMMENT 'Employee details' 
PARTITIONED BY (year int)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
STORED AS TEXTFILE;

stored as textfile文件格式，文件格式在hive中有三种: textfile、Sequencefile（序列化文件，学hadoop的都会知道啦）、Rcfile。

13、增加分区：alter table tables add partition (log_date=’${date}’)
14、删除分区
ALTER TABLE n_log DROP PARTITION (day=‘20140722’);
删除多个分区
ALTER TABLE n_log DROP PARTITION (day>=‘20140722’, day>=‘20140728’);
12、查看所有分区：
show partitions tables;

查看某个表是否存在某个特定分区键
SHOW PARTITIONS tables PARTITION(country=‘US’)
DESCRIBE EXTENDED tables PARTITION(country=‘US’)

查询表信息：
DESCRIBE EXTENDED tables;
查询结构化的表信息：
DESCRIBE FORMATTED tables;
[DESCRIBE命令的其它用法：展示所有表：DESCRIBE FUNCTION substr]

Hive查看表的分区字段

14、加载文本数据

hive -e "LOAD DATA INPATH '/user/dev_bigdata/marketing/tmp/phone-send-data.txt' INTO TABLE dev_linghit_dw_dm_marketing.phone_send_data PARTITION (dt= '${time}')"

flume保存的hdfs离线文件可以能够这个方式新建hive数据库
即用 hive jsonserde可以直接解析json格式的hdfs，load到对应的hive表中

区别：
如果是带有层级的json格式，在新建导入的hive表中可以这么操作

select
 user_id as user_id,
 attr['cookie'] as `$cookie`
from sdk_log

11、添加数据到表中
1.数据加载
hive> LOAD DATA LOCAL INPATH ‘/usr/hadoop/hive/sample.txt’ OVERWRITE INTO TABLE employee;

如果table是个分区表则必须在hql中指定分区

hive> LOAD DATA LOCAL INPATH ‘/usr/hadoop/hive/sample.txt’
> OVERWRITE INTO TABLE employee partition(year=2012);(虽然已经实现了分区，但还未明白为什么分区后所有的数据都根据分区条件发生变化)

LOAD DATA:加载数据； LOCAL：本地数据 INPATH:文件的地址 OVERWRITE:覆盖表中的数据 加overwrite是重写表的数据，不加是追加数据

2.数据插入
插入表数据：insert into employee(eid,name) values (1208,‘jack’)；
hive只支持插入不支持修改和删除 hive不支持删除、修改某一行，如果要做，就要把除了该行的其他行来覆盖整张表

insert overwrite table tmp.apps select * from tmp.apps where id !=38;

Hive 删除行， 表 ，清空类容

8、验证数据库表：hive> SHOW DATABASES;

12、表名重命名： hive> ALTER TABLE employee RENAME TO emp;

13、修改 表中字段：name为ename
hive> ALTER TABLE emp CHANGE name ename String;

14、增加字段：hive表结构的调整，也许会对历史数据造成错位及其他一定影响。
在sqoop同步至hive实例中，mysql表结构的调整需要提前告知hive端。

• HIve 在指定位置添加字段:尽量不要这么做，可能会导致表中数据错位
  alter table table_name add columns (c_time string comment ‘当前时间’); – 正确，添加在最后
  alter table table_name add columns (c_time string comment ‘当前时间’,c_value string comment ‘当前值’); – 正确，添加在最后，插入多个字段
  alter table table_name change c_time c_time string after address ; – 正确，移动到指定位置,address字段的后面
  alter table table_name add column c_time string comment ‘当前时间’ after address ; – 报错

ALTER TABLE 表名 CHANGE column col_old_name col_new_name column_type [COMMENT col_comment] after 列名（放在哪个字段后）；
alter table u_product_relation change register_at register_at timestamp after product_id;
修改字段顺序但字段值不会调整，会造成值错位，需要修复（增加了字段，但是hdfs文件对应没变）
insert overwrite table u_product_relation PARTITION(row_date) select id,user_id,product_id,null,register_at,created_at,updated_at,row_date from u_product_relation where row_date<=‘2020-05-13’;

• 数据表增加列的时候赋默认值
  ALTER TABLE PT_MONTHLYCLOSING ADD TESTCOL VARCHAR(50) DEFAULT ‘2004’

• HIVE 分区表添加字段后的奇怪现象
  https://blog.csdn.net/lxpbs8851/article/details/17118841

15、删除字段(使用新schema替换原有的)
// 删除前schema：id BIGINT, name STRING, age Int
// 删除后schema：id BIGINT, name STRING
ALTER TABLE test REPLACE COLUMNS(id BIGINT, name STRING);

14、修改数据类型：emp表中字段salary的数据类型从float改为double
字段名保持不变
hive> ALTER TABLE emp CHANGE salary salary Double;
alter table linghit_bigdata_ods.u_product_relation change product_id product_id STRING;

9、删除数据库：
hive> DROP DATABASE IF EXISTS userdb;
hive> DROP SCHEMA userdb;
全部删除相应的表在删除数据库之前：hive> DROP DATABASE IF EXISTS userdb CASCADE;
10、删表语句

• 仅删除表中数据，保留表结构
  truncate table table_name;
  truncate操作用于删除指定表中的所有行，相当于delete from table where 1=1.表达的是一个意思。
  truncate 不能删除外部表！因为外部表里的数据并不是存放在Hive Meta store中。创建表的时候指定了EXTERNAL，外部表在删除分区后，hdfs中的数据还存在，不会被删除。因此要想删除外部表数据，可以把外部表转成内部表或者删除hdfs文件。

• 清空表，第二种方式
  insert overwrite table employee select * from employee where 1=0;

• 删除表结构和数据
  drop table if exists table_name;
  drop table if exists zxcs_userflow_off_channelanalysis;

• 删除数据库
  drop database if exists database_name;
  如果我们的数据库xpu123中，还存在iot_deviceenergytype表，因此，如果直接删除，会报以下错误。Hive会提醒你，将要执行删除操作的xpu123的库里面还存在tables。
  解决这个错误有两种方法：

1. 就是很简单的将所有表先删除完，再删除库。
2. 使用cascade关键字执行强制删库。drop database if exists xpu123 cascade;

15、创建hbase外部表
hbase外部表和hbase表是互通的，不管插入到hbase和hive，对应的hive和hbase都会更新

# 创建用户中心创建索引hbase表的hive映射外部关联表
CREATE EXTERNAL TABLE sit_linghit_ondw_dwd.user_create_index_hbase_external(
rowkey string,
user_create_time timestamp
) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
 WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,info:user_create_time","serialization.format"="1")
 TBLPROPERTIES ("hbase.table.name" = "user_create_index");

# 插入数据到外部表
insert into table sit_linghit_ondw_dwd.user_create_index_hbase_external SELECT CONCAT(user_id,'',cast(product_id as STRING)) AS rowkey,created_at as user_create_time FROM linghit_bigdata_ods.u_product_relation LIMIT 10;

• Hive外部表、内部表

1. 外部表：
2. 内部表

hive内部表、外部表、分区
Hive映射HBase表的几种方式
hive创建hbase外部表

15、Hive数据类型转换 cast，时间戳处理函数，concat链接函数

• 数据类型转换

SELECT cast(product_id as STRING) FROM linghit_bigdata_ods.u_product_relation LIMIT 10;
SELECT cast('1' as INT) FROM linghit_bigdata_ods.u_product_relation LIMIT 10;

• 时间戳处理

# 时间戳 -> 日期
select distinct from_unixtime(1441565203,'yyyy/MM/dd HH:mm:ss') from test_date;
select from_unixtime(1441565203,'yyyy-MM-dd');
# 日期 -> 时间戳
create_at >= unix_timestamp('2020-05-20 00:00:00') AND create_at <= unix_timestamp('2020-05-20 23:59:59')

• concat链接函数

#concat: 用于将多个字符串连接成一个字符串
SELECT CONCAT(id, ‘，’, name) AS con FROM info LIMIT 1;

+----------+
| con      |
+----------+
| 1,BioCyc |
+----------+

#CONCAT_WS函数: CONCAT_WS(separator,str1,str2,…)
SELECT CONCAT_WS('_',id,name) AS con_ws FROM info LIMIT 1;

+----------+
| con_ws   |
+----------+
| 1_BioCyc |
+----------+

#函数会忽略任何分隔符参数后的 NULL 值
SELECT CONCAT_WS(',','First name',NULL,'Last Name');

+----------------------------------------------+
| CONCAT_WS(',','First name',NULL,'Last Name') |
+----------------------------------------------+
| First name,Last Name                         |
+----------------------------------------------+

#GROUP_CONCAT函数: 

16、创建视图
hive> create view empview as
> select * from emp where salary>40000;

17、hive之条件筛选
常用的条件筛选函数为：if函数，coalesce函数，case when函数

1. if函数（处理单个列的查询结果）
   语法：if(condition, value_if_true, value_if_false)
   说明：当条件Condition为TRUE时，返回value_True；否则返回value_False Or Null

select if(user_create_time>"2020-03-05",user_create_time,null) from sit_linghit_ondw_dwd.user_create_index_hbase_external;

2. coalesce函数（非空查找）
   语法：coalesce(value1,value2)
   说明：返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL, 在数据清洗中，null显示的地方想替换为0，即可用该函数

select coalesce(null,1,0) from table;

3. case when
   语法1：case a when b then c when d then e else f end
   说明：如果a=b返回c,a=d返回e,其他的返回f
   语法2：case when a then b when c then d else e end
   说明：如果a满足返回b,如果c满足返回d,其他的返回e

select case when user_create_time>"2020-03-07" then "future"
when user_create_time>"2020-03-05" and user_create_time<"2020-03-07" then "now"
when user_create_time>"2020-03-02" and user_create_time<"2020-03-05" then "history"
else "others"
end from sit_linghit_ondw_dwd.user_create_index_hbase_external;

hive之条件筛选

17、Hive中and和or的执行顺序
hive中and的执行优先级比or高，
select 1 from student where 1=0 or 1=1 and 1 = 0;
执行结果为空

select 1 from student where 1=0 or 1=1 and 1 =1;
执行结果为1
除了可以用and 和 or来区分优先级，也可以给语句加上括号()来区分优先级

18、不同类型的连接 JOIN 、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN

19、hive高级

• hive 子查询

(1)把table1和table2符合条件的数据进行连接

select t1.id as id1,t2.id as id2 from table1 t1 left join(
　　select id from table2 where col1 = ‘a’
) t2 on t1.id = t2.id

(2)或者使用left semi-join来实现(不支持right semi-join)

left-semi join 返回左边表满足 on 条件的记录

select id from table1 t1 left semi join table2 t2
on t1.id = t2.id and t2.col1 != ‘a’

left-semin join 要比join更高效,因为对于左表中一条制定的记录在右边表一旦匹配到就停止右边表的匹配

hive子查询

场景一：单表子查询没有指定表别名

语句：select * from (select id,devid,job_time from tb_in_base) ;

执行过程：

提示需要指定子查询源。

加上表别名：

语句：select * from (select id,devid,job_time from tb_in_base) a;

执行过程：

加了表别名后可以正常输出子查询中的数据。

结果分析：在hive中若有子查询必须指定子查询的表别名

场景二：单表查询外围字段比子查询少一个

语句： select id,devid from (select id,devid,job_time from tb_in_base) a;

执行过程：

结果分析：输出外围指定字段的数据可以输出 。

场景三：两张表进行union all

语句：

select a.id,a.devid from (select a.id,a.devid,a.job_time from tb_in_base a union all select b.id,b.devid,b.job_time from tb_in_up b) a;

执行过程：

结果分析：两张表进行union all 取相同的字段名称，可正常输出指定数据内容，且结果为两张表的结果集

场景四：两张表在子查询中进行union

语句：

select a.id,a.devid from (select a.id,a.devid,a.job_time from tb_in_base a union select b.id,b.devid,b.job_time from tb_in_up b) a;

执行过程：

结果分析：hive 不支持union

场景五：外围使用count、sum 统计id

语句：

select count(a.id),sum(a.id) from (select a.id,a.devid,a.job_time from tb_in_base a union all select b.id,b.devid,b.job_time from tb_in_up b) a;

执行过程：

结果分析：两表在子查询中直接进行union all ，外围查询可以使用count、sum 等聚合函数

场景六：使用union all关联两张表，同时使用count、sum 、max等 聚合函数

结果分析：union all 时不能使用count、sum 、max等 聚合函数，单表可以进行聚合函数使用，如下图：

场景七：left join 是否可以使用max、count、sum 等函数

语句：

select max(a.id),min(b.id),sum(a.job_time),count(a.id) from tb_in_base a join tb_in_up b on (a.id=b.id);

执行过程：

结果分析：在left join 中可以使用max、count等聚合函数。

总结分析

1. 子查询相当于表名，使用 from 关键字需要指定真实表名或表别名。

2. hive 不支持union ，只支持union all

3. 子查询中使用union all 时，在子查询里不能使用count、sum 等 聚合函数

4. 两表直接进行union all 可以使用count、sum 等聚合函数

5. 两张表进行union all 取相同的字段名称，可正常输出指定数据内容，且结果为两张表的结果集

hive中子查询实例

hive中空值判断基本分两种
（1）NULL 与 \N
hive在底层数据中如何保存和标识NULL，是由 alter table name SET SERDEPROPERTIES(‘serialization.null.format’ = ‘\N’); 参数控制的
比如：
1.设置 alter table name SET SERDEPROPERTIES(‘serialization.null.format’ = ‘\N’);
则：底层数据保存的是’\N’,通过查询显示的是’NULL’
这时如果查询为空值的字段可通过 语句：a is null 或者 a=’\N’

2.设置 alter tablename SET SERDEPROPERTIES(‘serialization.null.format’ = ‘NULL’);
则：底层数据保存的是’NULL’,通过查询显示的是’NULL’
这时如果查询为空值的字段可通过 语句：a is null 或者 a=‘NULL’

（2）’’ 与 length（xx）=0
‘’ 表示的是字段不为null且为空字符串，此时用 a is null 是无法查询这种值的，必须通过 a=’’ 或者 length(a)=0 查询

Hive高级操作
1.使用LIKE、AS创建表,表重命名，添加、修改、删除列
表结构数据复制

根据已存在的表结构，使用like关键字，复制一个表结构一模一样的新表

hive> create table student_info2 like student_info;
OK
Time taken: 0.73 seconds
hive> show tables;
OK
employee
student_info
student_info2
student_school_info
student_school_info_external_partition
student_school_info_partition
Time taken: 0.15 seconds, Fetched: 6 row(s)

根据已经存在的表，使用as关键字，创建一个与查询结果字段一致的表，同时将查询结果数据插入到新表
create table student_info3 as select * from student_info;

只有student_id,name两个字段的表
create table student_info4 as select student_id,name from student_info;

表重命名

student_info4表重命名为student_id_name
alter table student_info4 rename to student_id_name;

添加列

给student_info3表添加性别列,新添加的字段会在所有列最后，分区列之前，在添加新列之前已经存在的数据文件中。

如果没有新添加列对应的数据，在查询的时候显示为空。添加多个列用逗号隔开。
hive> alter table student_info3 add columns(gender string comment ‘性别’);
OK
Time taken: 0.185 seconds
删除列或修改列

修改列，将继续存在的列再定义一遍，需要替换的列重新定义
hive> alter table student_info3 replace columns(student_id string,name string,age int,origin string,gender2 int);
OK
Time taken: 0.422 seconds
删除列,将继续存在的列再定义一遍，需要删除的列不再定义

hive> alter table student_info3 replace columns(student_id string,name string,age int,origin string);
OK
Time taken: 0.529 seconds
2.分桶表使用
创建分桶表

按照指定字段取它的hash散列值分桶,创建学生入学信息分桶表
字段名称 类型 注释 分桶字段
student_id string 学生ID 是
name string 姓名 否
age int 年龄 否
origin string 学院ID 否

create table rel.student_info_bucket(
student_id string,
name string,
age int,
origin string
)
clustered by (student_id) sorted by (student_id asc) into 4 buckets
row format delimited
fields terminated by ‘\t’
lines terminated by ‘\n’
stored as textfile;
分桶表插入数据

向student_info_bucket分桶表插入数据
set hive.enforce.bucketing = true;
set mapreduce.job.reduces=4;
insert overwrite table student_info_bucket
select student_id,name,age,origin
from student_info
cluster by(student_id);
查看hdfs分桶文件

[root@hadoop01 ~]# hadoop fs -ls /user/hive/warehouse/rel.db/student_info_bucket
Found 4 items
-rwxr-xr-x 3 hadoop supergroup 78 2018-01-24 19:33 /user/hive/warehouse/rel.db/student_info_bucket/000000_0
-rwxr-xr-x 3 hadoop supergroup 84 2018-01-24 19:33 /user/hive/warehouse/rel.db/student_info_bucket/000001_0
-rwxr-xr-x 3 hadoop supergroup 80 2018-01-24 19:33 /user/hive/warehouse/rel.db/student_info_bucket/000002_0
-rwxr-xr-x 3 hadoop supergroup 81 2018-01-24 19:33 /user/hive/warehouse/rel.db/student_info_bucket/000003_0
说明：

分桶表一般不使用load向分桶表中导入数据，因为load导入数据只是将数据复制到表的数据存储目录下，hive并不会在load的时候对数据进行分析然后按照分桶字段分桶，load只会将一个文件全部导入到分桶表中，并没有分桶。一般采用insert从其他表向分桶表插入数据。
分桶表在创建表的时候只是定义表的模型，插入的时候需要做如下操作：
在每次执行分桶插入的时候在当前执行的session会话中要设置

hive.enforce.bucketing=true;
声明本次执行的是一次分桶操作。需要指定reduce个数与分桶的数量相同

set mapreduce.job.reduces=4，
这样才能保证有多少桶就生成多少个文件。
如果定义了按照分桶字段排序，需要在从其他表查询数据过程中将数据按照分区字段排序之后插入各个桶中，分桶表并不会将各分桶中的数据排序。排序和分桶的字段相同的时候使用Cluster by(字段),cluster by 默认按照分桶字段在桶内升序排列，如果需要在桶内降序排列，使用distribute by (col) sort by (col desc)组合实现。

3.导出数据
使用insert将student_info表数据导出到本地指定路径
insert overwrite local directory ‘/home/hadoop/apps/hive_test_data/export_data’
row format delimited fields terminated by ‘\t’ select * from student_info;
[root@hadoop01 export_data]# cat 000000_0
1 xiaoming 20 11
2 xiaobai 21 31
3 zhangfei 22 44
4 likui 19 44
5 zhaoyun 21 13
6 zhangsan 20 11
7 lisi 19 11
8 wangwu 23 31
9 zhaofei 19 21
10 zhangyan 20 21
11 lihe 20 22
12 caoyang 17 32
13 lihao 19 32
14 zhaoming 21 50
15 zhouhong 18 51
16 yangshuo 23 33
17 xiaofei 24 13
18 liman 23 13
19 qianbao 20 13
20 sunce 21 41
导出数据到本地的常用方法

[hadoop@hadoop01 export_data]$ hive -e"select * from rel.student_info"> /home/hadoop/student_info_data.txt
[hadoop@hadoop01 ~]$ cat student_info_data.txt
1 xiaoming 20 11
2 xiaobai 21 31
3 zhangfei 22 44
4 likui 19 44
5 zhaoyun 21 13
6 zhangsan 20 11
7 lisi 19 11
8 wangwu 23 31
9 zhaofei 19 21
10 zhangyan 20 21
11 lihe 20 22
12 caoyang 17 32
13 lihao 19 32
14 zhaoming 21 50
15 zhouhong 18 51
16 yangshuo 23 33
17 xiaofei 24 13
18 liman 23 13
19 qianbao 20 13
20 sunce 21 41
默认结果分隔符：’\t’

4.关联查询
创建2张表

create table rel.a(
id int,
name string
)
row format delimited
fields terminated by ‘\t’
lines terminated by ‘\n’
stored as textfile;

create table rel.b(
id int,
name string
)
row format delimited
fields terminated by ‘\t’
lines terminated by ‘\n’
stored as textfile;
导入数据

hive> load data local inpath ‘/home/hadoop/apps/hive_test_data/a_join_data’ into table a;
Loading data to table rel.a
Table rel.a stats: [numFiles=1, totalSize=61]
OK
Time taken: 1.79 seconds
hive> load data local inpath ‘/home/hadoop/apps/hive_test_data/b_join_data’ into table b;
Loading data to table rel.b
Table rel.b stats: [numFiles=1, totalSize=38]
OK
Time taken: 0.562 seconds
inner或inner join

两个表通过id关联，只把id值相等的数据查询出来。join的查询结果与inner join的查询结果相同。
select * from a join b on a.id=b.id;
等同于

select * from a inner join b on a.id=b.id;
…
OK
1 a 1 AA
2 b 2 BB
3 c 3 CC
6 f 6 FF
Time taken: 44.337 seconds, Fetched: 4 row(s)
full outer join或full join
两个表通过id关联，把两个表的数据全部查询出来

OK
1 a 1 AA
2 b 2 BB
3 c 3 CC
4 d NULL NULL
5 e NULL NULL
6 f 6 FF
7 g NULL NULL
8 h NULL NULL
9 i NULL NULL
10 j NULL NULL
11 k NULL NULL
12 l NULL NULL
13 m NULL NULL
14 n NULL NULL
NULL NULL 20 TT
NULL NULL 21 UU
NULL NULL 22 vv
left join
左连接时，左表中出现的join字段都保留，右表没有连接上的都为空

OK
1 a 1 AA
2 b 2 BB
3 c 3 CC
4 d NULL NULL
5 e NULL NULL
6 f 6 FF
7 g NULL NULL
8 h NULL NULL
9 i NULL NULL
10 j NULL NULL
11 k NULL NULL
12 l NULL NULL
13 m NULL NULL
14 n NULL NULL
right join
右连接时，右表中出现的join字段都保留，左表没有连接上的都是空

select * from a right join b on a.id=b.id;
OK
1 a 1 AA
2 b 2 BB
3 c 3 CC
6 f 6 FF
NULL NULL 20 TT
NULL NULL 21 UU
NULL NULL 22 vv
Time taken: 25.188 seconds, Fetched: 7 row(s)
left semi join
左半连接实现了类似IN/EXISTS的查询语义，输出符合条件的左表内容。
hive不支持in …exists这种关系型数据库中的子查询结构，hive暂时不支持右半连接。
例如：

select a.id, a.name from a where a.id in (select b.id from b);
使用Hive对应于如下语句：

select a.id,a.name from a left semi join b on a.id = b.id;
OK
1 a
2 b
3 c
6 f
Time taken: 27.42 seconds, Fetched: 4 row(s)
map side join
使用分布式缓存将小表数据加载都各个map任务中，在map端完成join，map任务输出后，不需要将数据拷贝到reducer阶段再进行join，
降低的数据在网络节点之间传输的开销。多表关联数据倾斜优化的一种手段。多表连接，如果只有一个表比较大，其他表都很小，
则join操作会转换成一个只包含map的Job。运行日志中会出现Number of reduce tasks is set to 0 since there’s no reduce operator
没有reduce的提示。
例如：

select /*+ mapjoin(b) */ a.id, a.name from a join b on a.id = b.id
Total MapReduce CPU Time Spent: 1 seconds 320 msec
OK
1 a
2 b
3 c
6 f
Time taken: 25.538 seconds, Fetched: 4 row(s)
5.Hive内置函数
创建用户评分表

create table rel.user_core_info(
user_id string,
age int,
gender string,
core int
)
row format delimited fields terminated by ‘\t’
lines terminated by ‘\n’
stored as textfile;
导入数据

load data local inpath ‘/home/hadoop/apps/hive_test_data/user_core.txt’ into table rel.user_core_info;
条件函数 case when

语法1：CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f
例如：
hive> select case 1 when 2 then ‘two’ when 1 then ‘one’ else ‘zero’ end;
OK
one
Time taken: 0.152 seconds, Fetched: 1 row(s)
语法2：CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END

说明：如果a为TRUE，则返回b；如果c为TRUE，则返回d；否则返回e
例如：

hive> select case when 1=2 then ‘two’ when 1=1 then ‘one’ else ‘zero’ end;
OK
one
Time taken: 0.33 seconds, Fetched: 1 row(s)
查询用户评分表，每个年龄段的最大评分值

select gender,
case when age<=20 then ‘p0’ when age>20 and age<=50 then ‘p1’ when age>=50 then ‘p3’ else ‘p0’ end,
max(core) max_core
from rel.user_core_info
group by gender,
case when age<=20 then ‘p0’ when age>20 and age<=50 then ‘p1’ when age>=50 then ‘p3’ else ‘p0’ end;
结果为：

OK
female p0 90
female p1 95
female p3 90
male p0 80
male p1 80
male p3 80
Time taken: 28.461 seconds, Fetched: 6 row(s)
自定义UDF函数
当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
UDF 作用于单个数据行，产生一个数据行作为输出。
步骤：

先开发一个java类，继承UDF，并重载evaluate方法
打成jar包上传到服务器
在使用的时候将jar包添加到hive的classpath
hive>add jar /home/hadoop/apps/hive_test_data/HiveUdfPro-1.0-SNAPSHOT.jar;
创建临时函数与开发好的java class关联
hive>create temporary function age_partition as ‘cn.chinahadoop.udf.AgePartitionFunction’;
即可在hql中使用自定义的函数
新建Maven 项目
Pom 信息如下：

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.yongliang.udf</groupId>
  <artifactId>HiveUdfPro</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>HiveUdfPro</name>
  <url>http://maven.apache.org</url>
  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>
  <dependencies>
    <dependency>
      <groupId>org.apache.hive</groupId>
      <artifactId>hive-exec</artifactId>
      <version>1.2.2</version>
    </dependency>
    <dependency>
      <groupId>org.apache.hadoop</groupId>
      <artifactId>hadoop-common</artifactId>
      <version>2.7.0</version>
    </dependency>
  </dependencies>
  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>2.3.2</version>
        <executions>
          <execution>
            <id>default-compile</id>
            <phase>compile</phase>
            <goals>
              <goal>compile</goal>
            </goals>
            <configuration>
              <encoding>UTF-8</encoding>
            </configuration>
          </execution>
        </executions>
      </plugin>
    </plugins>
  </build>
</project>

新建类继承UDF

package com.yongliang.udf;

import org.apache.hadoop.hive.ql.exec.UDF;
/**
 * 创建时间 : 2018/1/27 15:35
 * 类描述 :  Hive UDF自定义函数，作用于单个数据行，产生一个数据行作为输出
 * @author  zhangyonglaing
 */
public class AgePartitionFunction extends UDF {
    public String evaluate(int age) {
        String partition = "p0";
        if(age <=20){
            partition = "p0";
        }else if(age > 20 && age <=50){
            partition = "p1";
        }else if(age > 50){
            partition = "p2";
        }
        return partition;
    }
}

将项目进行打包

参考链接

Hive教程 - 易百教程
Hive教程
从一个锁表问题了解hive锁机制
hive 锁机制
Hive Lock 那些事儿
hive中的distribute by
hive 创建表、加载数据 load data
shell中判断hive表分区是否存在
Hive partition的使用
hive中partition如何使用
hive从查询中获取数据插入到表或动态分区
hive创建hbase外部表
hue——hbase、hive的使用
Hive（三）Hive元数据信息对应MySQL数据库表
数据仓库(七)—hive的性能优化—hive的分区和分桶
大数据：Hive常用参数调优：把这一套优化吃完了，hive及涉及到的其他大数据只是估计就学的差不多啦。
hive优化之------控制hive任务中的map数和reduce数
Hive中的各种join关系和使用
hive之left join详解
hive 的 left semi join 讲解
Hive中HSQL中left semi join和INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN区别
left semi join 与 inner join 相同点与区别
Hive(三)hive的高级操作
Hive高级查询操作
50 个高级 sql 语句
Hibernate——Hql实例+详解
HQL中使用case when
hive常用时间转换
关于hibernate中hql语句 case when的写法
hive常用字符串函数
HiveSQL解析原理：包括SQL转化为MapReduce过程及MapReduce如何实现基本SQL操作
数仓应用工具Hive：从底层设计窥见其优化策略
Hive分区、分桶操作及其比较