hive简介(二)

阅读数：1155次 2022-01-18

字数统计: 11.2k字 | 阅读时长≈ 48分

白衣执甲，逆行而上，以勇气和辛劳诠释了医者仁心，用担当和奉献换来了山河无恙。新时代中国女性可亲、可敬、可爱，她们在热血奋斗中怒放生命，在应对挑战中成就不凡。 ——人民日报

blog: www.xubatian.cn

DDL数据定义(创建库,创建表都属于他)

创建数据库

1）创建一个数据库，数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db。

1	hive (default)> create database db_hive;

blog: www.xubatian.cn

2）避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法）

1
2
3

hive (default)> create database db_hive;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Database db_hive already exists
hive (default)> create database if not exists db_hive;

3）创建一个数据库，指定数据库在HDFS上存放的位置,/db-hive2.db是名字

1	hive (default)> create database db_hive2 location '/db_hive2.db'; 放在根目录下

blog: www.xubatian.cn

查询数据库

显示数据库

1．显示数据库

1	hive> show databases;

2．过滤显示查询的数据库

hive> show databases like 'db_hive*';
OK
db_hive
db_hive_1

4.2.2 查看数据库详情
1．显示数据库信息

1
2
3

hive> desc database db_hive;
OK
db_hive		hdfs://hadoop102:9000/user/hive/warehouse/db_hive.db	shangbaishuyaoUSER

上白书妖补充:

blog: www.xubatian.cn

2．显示数据库详细信息，extended

hive> desc database extended db_hive;
OK
db_hive		hdfs://hadoop102:9000/user/hive/warehouse/db_hive.db	shangbaishuyaoUSER	
40.3.3 切换当前数据库
hive (default)> use db_hive;

切换当前数据库

1	hive (default)> use db_hive;

修改数据库

用户可以使用ALTER DATABASE (alter database )命令为某个数据库的DBPROPERTIES(dbproperties)设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

1	hive (default)> alter database db_hive set dbproperties('createtime'='20170830');

在hive中查看修改结果

1
2
3

hive> desc database extended db_hive;
db_name comment location        owner_name      owner_type      parameters
db_hive         hdfs://hadoop102:8020/user/hive/warehouse/db_hive.db    shangbaishuyao USER    {createtime=20170830}

blog: www.xubatian.cn

删除数据库

1．删除空数据库

1	hive>drop database db_hive2;

2．如果删除的数据库不存在，最好采用 if exists判断数据库是否存在

1
2
3

hive> drop database db_hive;
FAILED: SemanticException [Error 10072]: Database does not exist: db_hive
hive> drop database if exists db_hive2;

3．如果数据库不为空，里面有表的话，可以采用cascade命令，强制删除

1
2
3

hive> drop database db_hive;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database db_hive is not empty. One or more tables exist.)
hive> drop database db_hive cascade;

创建表

1．建表语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
[CLUSTERED BY (col_name, col_name, ...) 
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]

blog: www.xubatian.cn

2．字段解释说明
（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
（2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
（3）COMMENT：为表和列添加注释。
（4）PARTITIONED BY创建分区表
（5）CLUSTERED BY创建分桶表
（6）SORTED BY不常用
（7）ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。
SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。
（8）STORED AS指定存储文件类型
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
（9）LOCATION ：指定表在HDFS上的存储位置。
（10）LIKE允许用户复制现有的表结构，但是不复制数据。

管理表

也叫内部表,将来删除表中数据会把对应HDFS中存的也删掉,在HDFS上的文件,目录也会删除掉

1．理论
我们默认创建的表都是内部表,外部表的创建需要单独去指定
默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。

2．案例实操
（1）普通创建表

create table if not exists student2(
id int, name string
) 
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student2';

blog: www.xubatian.cn

（2）根据查询结果创建表（查询的结果会添加到新创建的表中）

1	create table if not exists student3 as select id, name from student;

（3）根据已经存在的表结构创建表

1	create table if not exists student4 like student;

补充效果图: 说明这种方式只是按照表结构来创建一张表,里面并没有数据

blog: www.xubatian.cn

（4）查询表的类型,更详细的信息

1
2
3

hive (default)> desc formatted student2;
Table Type:             MANAGED_TABLE  
由这个就可以看出他是管理表,或者说就是内部表,注意，只要表删了，在hdfs中对应的目录就没了

blog: www.xubatian.cn

外部表

hive认为这个表不归我管，所以叫外部表

1．理论
因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。就是在所对应的hdfs上的文件不会被删除
特点就是：删除数据时，如果你是内部表，我就把你数据删了，如果你是外部表，我就不删除你数据
2．管理表和外部表的使用场景
每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。
外部表的创建就只需要在创建表的语法上加上external 就是创建的外部表

案例实操
分别创建部门和员工外部表，并向表中导入数据。

建表语句

创建部门表

create external  table if not exists default.dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

创建员工表

create external table if not exists default.emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t';

查看创建的表

hive (default)> show tables;
OK
tab_name
dept
emp

导入数据

blog: www.xubatian.cn

向外部表中导入数据

1 2	hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept; hive (default)> load data local inpath '/opt/module/datas/emp.txt' into table default.emp;

blog: www.xubatian.cn

查询结果

1 2	hive (default)> select * from emp; hive (default)> select * from dept;

blog: www.xubatian.cn

上面看的结构比较乱,如果想看的更清楚可以使用hiveserver2查看:如下图

blog: www.xubatian.cn

查看表格式化数据

1 2	hive (default)> desc formatted dept; Table Type: EXTERNAL_TABLE

管理表(内部表)与外部表的互相转换

（1）查询表的类型

1 2	hive (default)> desc formatted student2; Table Type: MANAGED_TABLE

（2）修改内部表student2为外部表

1	alter table student2 set tblproperties('EXTERNAL'='TRUE');

（3）查询表的类型

1 2	hive (default)> desc formatted student2; Table Type: EXTERNAL_TABLE

（4）修改外部表student2为内部表

1	alter table student2 set tblproperties('EXTERNAL'='FALSE');

（5）查询表的类型

1 2	hive (default)> desc formatted student2; Table Type: MANAGED_TABLE

注意：(‘EXTERNAL’=’TRUE’)和(‘EXTERNAL’=’FALSE’)为固定写法，区分大小写！

分区表

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

分区表基本操作

1．引入分区表（需要根据日期对日志进行管理,按月存储,按月分区）

1
2
3

/user/hive/warehouse/log_partition/20170702/20170702.log
/user/hive/warehouse/log_partition/20170703/20170703.log
/user/hive/warehouse/log_partition/20170704/20170704.log

2．创建分区表语法

hive (default)> create table dept_partition(
deptno int, dname string, loc string )
partitioned by (month string)
row format delimited fields terminated by '\t';

3．加载数据到分区表中

1
2
3

hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201709');
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201708');
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201707’);

blog: www.xubatian.cn

4．查询分区表中数据
单分区查询

1	hive (default)> select * from dept_partition where month='201709';

多分区联合查询

hive (default)> select * from dept_partition where month='201709'
              union
              select * from dept_partition where month='201708'
              union
              select * from dept_partition where month='201707';

_u3.deptno      _u3.dname       _u3.loc _u3.month
10      ACCOUNTING      NEW YORK        201707
10      ACCOUNTING      NEW YORK        201708
10      ACCOUNTING      NEW YORK        201709
20      RESEARCH        DALLAS  201707
20      RESEARCH        DALLAS  201708
20      RESEARCH        DALLAS  201709
30      SALES   CHICAGO 201707
30      SALES   CHICAGO 201708
30      SALES   CHICAGO 201709
40      OPERATIONS      BOSTON  201707
40      OPERATIONS      BOSTON  201708
40      OPERATIONS      BOSTON  201709

5．单独增加分区
创建单个分区

1	hive (default)> alter table dept_partition add partition(month='201706') ;

同时创建多个分区

1	hive (default)> alter table dept_partition add partition(month='201705') partition(month='201704');

6．删除分区
删除单个分区

1	hive (default)> alter table dept_partition drop partition (month='201704');

同时删除多个分区

1	hive (default)> alter table dept_partition drop partition (month='201705'), partition (month='201706');

7．查看分区表有多少分区

1	hive> show partitions dept_partition;

8．查看分区表结构

hive> desc formatted dept_partition;
#Partition Information          
#col_name              data_type               comment             
month                   string

分区表注意事项

1．创建二级分区表

hive (default)> create table dept_partition2(
                 deptno int, dname string, loc string )
                 partitioned by (month string, day string)
                 row format delimited fields terminated by '\t';

2．正常的加载数据
（1）加载数据到二级分区表中

1 2	hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition2 partition(month='201709', day='13');

（2）查询分区数据

1	hive (default)> select * from dept_partition2 where month='201709' and day='13';

3．把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

blog: www.xubatian.cn

（1）方式一：上传数据后修复
上传数据

hive (default)> dfs -mkdir -p
 /user/hive/warehouse/dept_partition2/month=201709/day=12;  我自己创建分区目录,和分区如何关联?
 
 
 hive (default)> dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=12;
把文件上传到自己创建的的分区目录下

查询数据（查询不到刚上传的数据）

1	hive (default)> select * from dept_partition2 where month='201709' and day='12';

执行修复命令

1	hive> msck repair table dept_partition2;

再次查询数据

1	hive (default)> select * from dept_partition2 where month='201709' and day='12';

（2）方式二：上传数据后添加分区
上传数据

1
2
3

hive (default)> dfs -mkdir -p
 /user/hive/warehouse/dept_partition2/month=201709/day=11;
hive (default)> dfs -put /opt/module/datas/dept.txt  /user/hive/warehouse/dept_partition2/month=201709/day=11;

执行添加分区

1	hive (default)> alter table dept_partition2 add partition(month='201709', day='11');

查询数据

1	hive (default)> select * from dept_partition2 where month='201709' and day='11';

（3）方式三：创建文件夹后load数据到分区
创建目录

1 2	hive (default)> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=10;

上传数据

1 2	hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table dept_partition2 partition(month='201709',day='10');

查询数据

1	hive (default)> select * from dept_partition2 where month='201709' and day='10';

修改表

重命名表
1．语法

1	ALTER TABLE table_name RENAME TO new_table_name

2．实操案例

1	hive (default)> alter table dept_partition2 rename to dept_partition3;

实操案例

（1）查询表结构

1	hive> desc dept_partition;

（2）添加列

1	hive (default)> alter table dept_partition add columns(deptdesc string);

（3）查询表结构

1	hive> desc dept_partition;

（4）更新列

1	hive (default)> alter table dept_partition change column deptdesc desc int;

（5）查询表结构

1	hive> desc dept_partition;

（6）替换列

1	hive (default)> alter table dept_partition replace columns(deptno string, dname, string, loc string);

（7）查询表结构

1	hive> desc dept_partition;

删除表

1	hive (default)> drop table dept_partition;

DML数据操作

数据导入

向表中装载数据（Load）

1．语法

1	hive> load data [local] inpath '/opt/module/datas/student.txt' overwrite \| into table student [partition (partcol1=val1,…)];

（1）load data:表示加载数据
（2）local:表示从本地加载数据到hive表；没有写Local则从HDFS加载数据到hive表
（3）inpath:表示加载数据的路径
（4）overwrite:表示覆盖表中已有数据，没有写overwrite则表示追加
（5）into table:表示加载到哪张表
（6）student:表示具体的表
（7）partition:表示上传到指定分区
注意:从本地文件系统中取load数据,他其实是copy的操作.如果你从HDFS系统中去load数据,他其实是剪切的操作.
2．实操案例
（0）创建一张表

1	hive (default)> create table student(id string, name string) row format delimited fields terminated by '\t';

（1）加载本地文件到hive

1	hive (default)> load data local inpath '/opt/module/datas/student.txt' into table default.student;

（2）加载HDFS文件到hive中
上传文件到HDFS

1	hive (default)> dfs -put /opt/module/datas/student.txt /user/shangbaishuyao/hive;

加载HDFS上数据

1	hive (default)> load data inpath '/user/shangbaishuyao/hive/student.txt' into table default.student;

（3）加载数据覆盖表中已有的数据
上传文件到HDFS

1	hive (default)> dfs -put /opt/module/datas/student.txt /user/shangbaishuyao/hive;

加载数据覆盖表中已有的数据

1	hive (default)> load data inpath '/user/shangbaishuyao/hive/student.txt' overwrite into table default.student;

上传的方式就是不能加””号

通过查询语句向表中插入数据（Insert）

1．创建一张分区表

1	hive (default)> create table student(id int, name string) partitioned by (month string) row format delimited fields terminated by '\t';

2．基本插入数据

1	hive (default)> insert into table student partition(month='201709') values(1,'wangwu');

3．基本模式插入（根据单张表查询结果）

1 2	hive (default)> insert overwrite table student partition(month='201708') select id, name from student where month='201709';

4．多插入模式（根据多张表查询结果）

hive (default)> from student
              	 insert overwrite table student partition(month='201707')
              	 select id, name where month='201709'
              	 insert overwrite table student partition(month='201706')
             		 select id, name where month='201709';

查询语句中创建表并加载数据（As Select）

根据查询结果创建表（查询的结果会添加到新创建的表中）

1 2	create table if not exists student3 as select id, name from student;

创建表时通过Location指定加载数据路径

1．创建表，并指定在hdfs上的位置

hive (default)> create table if not exists student5(
                 id int, name string  )
                 row format delimited fields terminated by '\t'
                 location '/user/hive/warehouse/student5';

2．上传数据到hdfs上

1	hive (default)> dfs -put /opt/module/datas/student.txt /user/hive/warehouse/student5;

3．查询数据

1	hive (default)> select * from student5;

Import数据到指定Hive表中

注意：先用export导出后，再将数据导入。

1 2	hive (default)> import table student2 partition(month='201709') from '/user/hive/warehouse/export/student';

数据导出

Insert导出

1．将查询的结果导出到本地

1 2	hive (default)> insert overwrite local directory '/opt/module/datas/export/student' select * from student;

2．将查询的结果格式化导出到本地

1 2	hive(default)>insert overwrite local directory '/opt/module/datas/export/student1' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;

3．将查询的结果导出到HDFS上(没有local)

1
2
3

hive (default)> insert overwrite directory '/user/shangbaishuyao/student2'
             ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
             select * from student;

Hadoop命令导出到本地

get就是从hdfs上去下载文件

1 2	hive (default)> dfs -get /user/hive/warehouse/student/month=201709/000000_0 /opt/module/datas/export/student3.txt;

Hive Shell 命令导出

基本语法：（hive -f/-e 执行语句或者脚本 > file）
注意: > 这个是追加号,将他追加到我们的文件里面

1 2	[shangbaishuyao@hadoop102 hive]$ bin/hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt;

Export导出到HDFS上

1 2	(defahiveult)> export table default.student to '/user/hive/warehouse/export/student';

说明他不仅把你数据导出来,还把你的元数据也导出来了,说明,也就是只有这两者都具备的的文件才能够导入

导出的东西有元数据的,但是你student.txt导入的东西也是student.txt,是没有元数据的

清除表中数据（Truncate）

注意：Truncate只能删除管理表，不能删除外部表中数据

1	hive (default)> truncate table student;

查询

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

查询语句语法

[WITH CommonTableExpression (, CommonTableExpression)*]    (Note: Only available
 starting with Hive 0.13.0)

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]

基本查询（Select…From）

全表和特定列查询

1．全表查询

1	hive (default)> select * from emp;

2．选择特定列查询

1	hive (default)> select empno, ename from emp;

注意：
（1）SQL 语言大小写不敏感。
（2）SQL 可以写在一行或者多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。

列别名

1．重命名一个列
2．便于计算
3．紧跟列名，也可以在列名和别名之间加入关键字‘AS’
4．案例实操
查询名称和部门

1	hive (default)> select ename AS name, deptno dn from emp;

算术运算符

运算符	描述
A+B	A和B 相加
A-B	A减去B
A*B	A和B 相乘
A/B	A除以B
A%B	A对B取余
A&B	A和B按位取与
A\|B	A和B按位取或
A^B	A和B按位取异或
~A	A按位取反

常用函数

1．求总行数（count）

1	hive (default)> select count(*) cnt from emp;

2．求工资的最大值（max）

1	hive (default)> select max(sal) max_sal from emp;

3．求工资的最小值（min）

1	hive (default)> select min(sal) min_sal from emp;

4．求工资的总和（sum）

1	hive (default)> select sum(sal) sum_sal from emp;

5．求工资的平均值（avg）

1	hive (default)> select avg(sal) avg_sal from emp;

Limit语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

1	hive (default)> select * from emp limit 5;

Where语句

1．使用WHERE子句，将不满足条件的行过滤掉
2．WHERE子句紧随FROM子句
3．案例实操
查询出薪水大于1000的所有员工

1	hive (default)> select * from emp where sal >1000;

比较运算符（Between/In/ Is Null）

1）下面表中描述了谓词操作符，这些操作符同样可以用于JOIN…ON和HAVING语句中

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回TRUE，反之返回FALSE
A<=>B	基本数据类型	如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL
A<>B, A!=B	基本数据类型	A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE
A<B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE
A<=B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE
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，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL	所有数据类型	如果A等于NULL，则返回TRUE，反之返回FALSE
A IS NOT NULL	所有数据类型	如果A不等于NULL，则返回TRUE，反之返回FALSE
IN(数值1, 数值2)	所有数据类型	使用 IN运算显示列表中的值
A [NOT] LIKE B	STRING 类型	B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP B	STRING 类型	B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

2）案例实操
（1）查询出薪水等于5000的所有员工

1	hive (default)> select * from emp where sal =5000;

（2）查询工资在500到1000的员工信息

1	hive (default)> select * from emp where sal between 500 and 1000;

（3）查询comm为空的所有员工信息

1	hive (default)> select * from emp where comm is null;

（4）查询工资是1500或5000的员工信息

1	hive (default)> select * from emp where sal IN (1500, 5000);

Like和RLike

1）使用LIKE运算选择类似的值
2）选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
2）RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

blog: www.xubatian.cn

这个就是匹配我这里面有2和3的数字

blog: www.xubatian.cn

这个表示我要严格要求以2开头以3结束的匹配数据

4）案例实操
（1）查找以2开头薪水的员工信息

1	hive (default)> select * from emp where sal LIKE '2%';

（2）查找第二个数值为2的薪水的员工信息

1	hive (default)> select * from emp where sal LIKE '_2%';

（3）查找薪水中含有2的员工信息

1	hive (default)> select * from emp where sal RLIKE '[2]';

逻辑运算符（And/Or/Not）

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

案例实操
（1）查询薪水大于1000，部门是30

1	hive (default)> select * from emp where sal>1000 and dep tno=30;

（2）查询薪水大于1000，或者部门是30

1	hive (default)> select * from emp where sal>1000 or deptno=30;

（3）查询除了20部门和30部门以外的员工信息

1	hive (default)> select * from emp where deptno not IN(30, 20);

分组

Group By语句

分组?什么时候下会用到分组?
把整个一张表拆分成多个数据
GROUP BY(分组)语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。
案例实操：
（1）计算emp表每个部门的平均工资

1	hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;

2）计算emp每个部门中每个岗位的最高薪水

1	hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by t.deptno, t.job; 主标识

Having语句

1．having与where不同点

（1） where针对表中的列发挥作用，查询数据；
having针对查询结果中的列发挥作用，筛选数据。
（2）where后面不能写分组函数，而having后面可以使用分组函数。
（3）having只用于group by分组统计语句。

2．案例实操
（1）求每个部门的平均薪水大于2000的部门
求每个部门的平均工资

1	hive (default)> select deptno, avg(sal) from emp group by deptno;

求每个部门的平均薪水大于2000的部门

1 2	hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

Join语句

等值Join

Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。等值连接就是A表里面的某某字段等于B表里面的某某字段.
案例实操
（1）根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；

1 2	hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;

表的别名

1．好处
（1）使用别名可以简化查询。
（2）使用表名前缀可以提高执行效率。
2．案例实操
合并员工表和部门表

1
2
3

hive (default)> select e.empno, e.ename, d.deptno 
from emp e join dept d 
on e.deptno = d.deptno;

内连接

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

1 2	hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;

左外连接

左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。

1 2	hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;

右外连接

右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

1 2	hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;

满外连接

满外连接：将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。

1 2	hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;

多表连接

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。

1．创建位置表

create table if not exists default.location(
loc int,
loc_name string
)
row format delimited fields terminated by '\t';

2．导入数据

1	hive (default)> load data local inpath '/opt/module/datas/location.txt' into table default.location;

3．多表连接查询

hive (default)>SELECT e.ename, d.deptno, l.loc_name
FROM   emp e 
JOIN   dept d
ON     d.deptno = e.deptno 
JOIN   location l
ON     d.loc = l.loc;

大多数情况下，Hive会对每对JOIN连接对象启动一个MapReduce任务。本例中会首先启动一个MapReduce job对表e和表d进行连接操作，然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l;进行连接操作。
注意：为什么不是表d和表l先进行连接操作呢？这是因为Hive总是按照从左到右的顺序执行的。

笛卡尔积

1．笛卡尔集会在下面条件下产生
（1）省略连接条件
（2）连接条件无效
（3）所有表中的所有行互相连接
2．案例实操

1	hive (default)> select empno, dname from emp, dept;

连接谓词中不支持or 但是and是支持的

1 2	hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno or e.ename=d.ename; 错误的

排序

全局排序（Order By）

Order By：全局排序，会进入到一个Reducer里面,最终会生成一个结果文件
1．使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序
2．ORDER BY 子句在SELECT语句的结尾
3．案例实操
（1）查询员工信息按工资升序排列

1	hive (default)> select * from emp order by sal;

（2）查询员工信息按工资降序排列

1	hive (default)> select * from emp order by sal desc;

按照别名排序

按照员工薪水的2倍排序

1	hive (default)> select ename, sal*2 twosal from emp order by twosal;

多个列排序

按照部门和工资升序排序

1	hive (default)> select ename, deptno, sal from emp order by deptno, sal ;

每个MapReduce内部排序（Sort By）

什么情况下map进入多个reduce呢? 分区.

Sort By：对每个Reducer里面的数据进行排序，对全局结果集来说不是排序。
Order by : 是对整个数据进行排序
1．设置reduce个数

1	hive (default)> set mapreduce.job.reduces=3;

2．查看设置reduce个数

1	hive (default)> set mapreduce.job.reduces;

3．根据部门编号降序查看员工信息

1	hive (default)> select * from emp sort by empno desc;

4．将查询结果导入到文件中（按照部门编号降序排序）

1 2	hive (default)> insert overwrite local directory '/opt/module/datas/sortby-result' select * from emp sort by deptno desc;

MapReduce里面是按照hash来进行分区的

blog: www.xubatian.cn

可以看到,这是随机分配的,是没有规律的,我们一般排序肯定会先指定分区然后在排序,分区的话,一般我们会指定按照什么来分区,然而这里没有,说明这种排序方式不提供指定分区,从而出现了随机分配的现象

分区排序（Distribute By）

这种方式确确实实分区了,分区之后你在哪个区就用sort by了

Distribute By：类似MR中partition，进行分区,就是先分好区,分好之后在每个partition里面进行sort by一下，结合sort by使用。

注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

Distribute指定正常的分区字段,指定之后就可以正常的分区操作了

Cluster By

当distribute by和sort by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。
Sort by可以指定是升序还是降序,但是用cluster by之后就不能指定了,只能是升序
1）以下两种写法等价

1 2	hive (default)> select * from emp cluster by deptno; hive (default)> select * from emp distribute by deptno sort by deptno;

注意：按照部门编号分区，不一定就是固定死的数值，可以是30号和60号部门分到一个分区里面去

其他常用查询函数

空字段赋值

1.函数说明
NVL：给值为NULL的数据赋值，它的格式是NVL( string1, replace_with)。
它的功能是如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。
2.数据准备：采用员工表
3.查询：如果员工的comm为NULL，则用-1代替

hive (default)> select nvl(comm,-1) from emp;
OK
_c0
20.0
300.0
500.0
-1.0
1400.0
-1.0
-1.0
-1.0
-1.0
0.0
-1.0
-1.0
-1.0
-1.0

4.查询：如果员工的comm为NULL，则用领导id代替

hive (default)> select nvl(comm,mgr) from emp;
OK
_c0
20.0
300.0
500.0
7839.0
1400.0
7839.0
7839.0
7566.0
NULL
0.0
7788.0
7698.0
7566.0

CASE WHEN THEN ELSE END

窗口函数

1．相关函数说明
OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n)：往前第n行数据
LEAD(col,n)：往后第n行数据
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

Rank

1．函数说明
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算

函数

系统内置函数

1．查看系统自带的函数

1	hive> show functions;

2．显示自带的函数的用法

1	hive> desc function upper;

3．详细显示自带的函数的用法

1	hive> desc function extended upper;

自定义函数

1）Hive 自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。
2）当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
3）根据用户自定义函数类别分为以下三种：
（1）UDF（User-Defined-Function）
一进一出
（2）UDAF（User-Defined Aggregation Function）
聚集函数，多进一出
类似于：count/max/min
（3）UDTF（User-Defined Table-Generating Functions）
一进多出
如lateral view explode()
4）官方文档地址
https://cwiki.apache.org/confluence/display/Hive/HivePlugins
5）编程步骤：
（1）继承org.apache.hadoop.hive.ql.UDF
（2）需要实现evaluate函数；evaluate函数支持重载；
（3）在hive的命令行窗口创建函数
a）添加jar

1	add jar linux_jar_path

b）创建function，

1	create [temporary] function [dbname.]function_name AS class_name;

（4）在hive的命令行窗口删除函数

1	Drop [temporary] function [if exists] [dbname.]function_name;

6）注意事项
（1）UDF必须要有返回类型，可以返回null，但是返回类型不能为void；

自定义UDF函数

1．创建一个Maven工程Hive
2．导入依赖

<dependencies>
		<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->
		<dependency>
			<groupId>org.apache.hive</groupId>
			<artifactId>hive-exec</artifactId>
			<version>1.2.1</version>
		</dependency>
</dependencies>

3．创建一个类

package com.shangbaishuyao.hive;
import org.apache.hadoop.hive.ql.exec.UDF;

public class Lower extends UDF {

	public String evaluate (final String s) {
		
		if (s == null) {
			return null;
		}
		
		return s.toLowerCase();
	}
}

打成jar包上传到服务器/opt/module/jars/udf.jar

5．将jar包添加到hive的classpath,

1	hive (default)> add jar /opt/module/datas/udf.jar;

6.创建临时函数与开发好的java class关联

1	create [temporary] function [dbname.]function_name AS class_name;

注意:如果你加了这个temporary的话,创建的函数就是临时的,窗口关闭就没了
如果你不加这个temporary的话,创建的就是永久的函数;function后面指定那个库,as后面关联的是你idea写的类的全类名

1	hive (default)> create temporary function mylower as "com.shangbaishuyao.hive.Lower";

blog: www.xubatian.cn

7．即可在hql中使用自定义的函数strip

1	hive (default)> select ename, mylower(ename) lowername from emp;

自定义UDTF函数

1）需求说明
自定义一个UDTF实现将一个任意分割符的字符串切割成独立的单词，例如：

Line:"hello,world,hadoop,hive"

Myudtf(line, ",")

hello
world
hadoop
hive

2）代码实现

package com.shangbaishuyao.udtf;

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

import java.util.ArrayList;
import java.util.List;

public class MyUDTF extends GenericUDTF {
private ArrayList<String> outList = new ArrayList<>();

@Override
public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
  //1.定义输出数据的列名和类型
    List<String> fieldNames = new ArrayList<>();
    List<ObjectInspector> fieldOIs = new ArrayList<>();

    //2.添加输出数据的列名和类型
    fieldNames.add("lineToWord");
    fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);

    return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
}

@Override
public void process(Object[] args) throws HiveException {
    
    //1.获取原始数据
    String arg = args[0].toString();

    //2.获取数据传入的第二个参数，此处为分隔符
    String splitKey = args[1].toString();

    //3.将原始数据按照传入的分隔符进行切分
    String[] fields = arg.split(splitKey);

    //4.遍历切分后的结果，并写出
    for (String field : fields) {

        //集合为复用的，首先清空集合
        outList.clear();

        //将每一个单词添加至集合
        outList.add(field);

        //将集合内容写出
        forward(outList);
    }
}

@Override
public void close() throws HiveException {

}
}

3）打成jar包上传到服务器/opt/module/data/udtf.jar
4）将jar包添加到hive的classpath下

1	hive (default)> add jar /opt/module/data/udtf.jar;

5）创建临时函数与开发好的java class关联

1	hive (default)> create temporary function myudtf as "com.shangbaishuyao.hive.MyUDTF";

6）即可在hql中使用自定义的函数

1	hive (default)> select myudtf(line, ",") word from words;

blog: www.xubatian.cn

压缩和存储

snappy压缩

blog: www.xubatian.cn

所以hadoop本身是不支持的

不支持Snappy压缩的,如图,东西很少

blog: www.xubatian.cn

而支持Snapp压缩的东西就比较多了

blog: www.xubatian.cn

所以我们要把这些东西直接拷贝到我正在使用的hadoop下面把他覆盖一下就可以了

blog: www.xubatian.cn

拷贝完查看一下就ok了

blog: www.xubatian.cn

然后如果是集群的话,就分发一下

blog: www.xubatian.cn

Hadoop压缩配置

MR支持的压缩编码

压缩格式	工具	算法	文件扩展名	是否可切分
DEFAULT	无	DEFAULT	.deflate	否
Gzip	gzip	DEFAULT	.gz	否
bzip2	bzip2	bzip2	.bz2	是
LZO	lzop	LZO	.lzo	是
Snappy	无	Snappy	.snappy	否

为了支持多种压缩/解压缩算法，Hadoop引入了编码/解码器，如下表所示:

压缩格式	对应的编码/解码器
DEFLATE	org.apache.hadoop.io.compress.DefaultCodec
gzip	org.apache.hadoop.io.compress.GzipCodec
bzip2	org.apache.hadoop.io.compress.BZip2Codec
LZO	com.hadoop.compression.lzo.LzopCodec
Snappy	org.apache.hadoop.io.compress.SnappyCodec

压缩性能的比较:

压缩算法	原始文件大小	压缩文件大小	压缩速度	解压速度
gzip	8.3GB	1.8GB	17.5MB/s	58MB/s
bzip2	8.3GB	1.1GB	2.4MB/s	9.5MB/s
LZO	8.3GB	2.9GB	49.3MB/s	74.6MB/s

http://google.github.io/snappy/
On a single core of a Core i7 processor in 64-bit mode, Snappy compresses at about 250 MB/sec or more and decompresses at about 500 MB/sec or more.

压缩参数配置

要在Hadoop中启用压缩，可以配置如下参数（mapred-site.xml文件中）

参数	默认值	阶段	建议
io.compression.codecs （在core-site.xml中配置）	org.apache.hadoop.io.compress.DefaultCodec, org.apache.hadoop.io.compress.GzipCodec, org.apache.hadoop.io.compress.BZip2Codec,org.apache.hadoop.io.compress.Lz4Codec	输入压缩	Hadoop使用文件扩展名判断是否支持某种编解码器
mapreduce.map.output.compress	false	mapper输出	这个参数设为true启用压缩
mapreduce.map.output.compress.codec	org.apache.hadoop.io.compress.DefaultCodec	mapper输出	使用LZO、LZ4或snappy编解码器在此阶段压缩数据
mapreduce.output.fileoutputformat.compress	false	reducer输出	这个参数设为true启用压缩
mapreduce.output.fileoutputformat.compress.codec	org.apache.hadoop.io.compress. DefaultCodec	reducer输出	使用标准工具或者编解码器，如gzip和bzip2
mapreduce.output.fileoutputformat.compress.type	RECORD	reducer输出	SequenceFile输出使用的压缩类型：NONE和BLOCK

开启Map输出阶段压缩

开启map输出阶段压缩可以减少job中map和Reduce task间数据传输量。具体配置如下：
案例实操：
1．开启hive中间传输数据压缩功能

1	hive (default)>set hive.exec.compress.intermediate=true;

2．开启mapreduce中map输出压缩功能

1	hive (default)>set mapreduce.map.output.compress=true;

3．设置mapreduce中map输出数据的压缩方式

1	hive (default)>set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

4．执行查询语句

1	hive (default)> select count(ename) name from emp;

结果展示:

blog: www.xubatian.cn

开启Reduce输出阶段压缩

当Hive将输出写入到表中时，输出内容同样可以进行压缩。属性hive.exec.compress.output控制着这个功能。用户可能需要保持默认设置文件中的默认值false，这样默认的输出就是非压缩的纯文本文件了。用户可以通过在查询语句或执行脚本中设置这个值为true，来开启输出结果压缩功能。
案例实操：
1．开启hive最终输出数据压缩功能

1	hive (default)>set hive.exec.compress.output=true;

2．开启mapreduce最终输出数据压缩

1	hive (default)>set mapreduce.output.fileoutputformat.compress=true;

3．设置mapreduce最终数据输出压缩方式

1	hive (default)> set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;

4．设置mapreduce最终数据输出压缩为块压缩,以为他默认的是record,所以我们改成块

1	hive (default)> set mapreduce.output.fileoutputformat.compress.type=BLOCK;

5．测试一下输出结果是否是压缩文件;如果我直接写在hive里面是看不到的
所以我们写在文件里面,用下面insert这种方式

1 2	hive (default)> insert overwrite local directory '/opt/modules/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

结果展示:

blog: www.xubatian.cn

补充:

blog: www.xubatian.cn

结果图示:

blog: www.xubatian.cn

文件存储格式

Hive支持的存储数据的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET。

列式存储和行式存储

blog: www.xubatian.cn

如图所示左边为逻辑表，右边第一个为行式存储，第二个为列式存储。

1．行存储的特点

查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。

2．列存储的特点

因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。
TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的；
ORC和PARQUET是基于列式存储的。

TextFile格式

TextFile格式,本身这种格式是不带压缩的,必须要借助其他的压缩来压缩他,压缩时要注意,如果我将来数据要切分的话,我就要用可切分的压缩格式来压缩他
默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用，但使用Gzip这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。

Orc格式

Orc (Optimized Row Columnar)是Hive 0.11版里引入的新的存储格式。
如图所示可以看到每个Orc文件由1个或多个stripe组成，每个stripe250MB大小，这个Stripe实际相当于RowGroup概念，不过大小由4MB->250MB，这样应该能提升顺序读的吞吐率。每个Stripe里有三部分组成，分别是Index Data(索引)，Row Data(具体的每一行数据)，Stripe Footer：

blog: www.xubatian.cn

1）Index Data：一个轻量级的index，默认是每隔1W行做一个索引。这里做的索引应该只是记录某行的各字段在Row Data中的offset。
2）Row Data：存的是具体的数据，先取部分行，然后对这些行按列进行存储。对每个列进行了编码，分成多个Stream来存储。
3）Stripe Footer：存的是各个Stream的类型，长度等信息。
每个文件有一个File Footer，这里面存的是每个Stripe的行数，每个Column的数据类型信息等；每个文件的尾部是一个PostScript，这里面记录了整个文件的压缩类型以及FileFooter的长度信息等。在读取文件时，会seek到文件尾部读PostScript，从里面解析到File Footer长度，再读FileFooter，从里面解析到各个Stripe信息，再读各个Stripe，即从后往前读。

Parquet格式

Parquet是面向分析型业务的列式存储格式，由Twitter和Cloudera合作开发，2015年5月从Apache的孵化器里毕业成为Apache顶级项目。
Parquet文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的。
通常情况下，在存储Parquet数据的时候会按照Block大小设置行组的大小，由于一般情况下每一个Mapper任务处理数据的最小单位是一个Block，这样可以把每一个行组由一个Mapper任务处理，增大任务执行并行度。Parquet文件的格式如图所示。

blog: www.xubatian.cn

上图展示了一个Parquet文件的内容，一个文件中可以存储多个行组，文件的首位都是该文件的Magic Code，用于校验它是否是一个Parquet文件，Footer length记录了文件元数据的大小，通过该值和文件长度可以计算出元数据的偏移量，文件的元数据中包括每一个行组的元数据信息和该文件存储数据的Schema信息。除了文件中每一个行组的元数据，每一页的开始都会存储该页的元数据，在Parquet中，有三种类型的页：数据页、字典页和索引页。数据页用于存储当前行组中该列的值，字典页存储该列值的编码字典，每一个列块中最多包含一个字典页，索引页用来存储当前行组下该列的索引，目前Parquet中还不支持索引页。

存储文件的压缩比总结

ORC > Parquet > textFile

注意orc是以256兆为单位来存的,其他方式就是按正常的128兆为一个块来存的
在实际的项目开发当中，hive表的数据存储格式一般选择：orc或parquet。压缩方式一般选择snappy，lzo。

本文作者： xubatian
本文链接： http://xubatian.cn/hive简介(二)/
版权声明： 本博客所有文章除特别声明外均为原创，采用 CC BY 4.0 CN协议许可协议。转载请注明出处:https://www.xubatian.cn/