3.HiveQL相关

数据定义

HiveQL是Hive的查询语言，Hive不支持行级插入操作、更新操作和删除操作,并且不支持事务。

Hive中的数据库

Hive中的数据库的概念本质上仅仅是表的一个目录或者命名空间。
如果用户没有显示指定数据库，那么默认的数据库为default

创建数据库

create database hive; 
# 如果不存在创建数据库 
create database if not exists hive; 
# 通配符查看databases 
show databases like 'h*'; 
# 指定数据库存放位置 
create database test location '/usr/test'; 
# 查看数据库详情 
describe database hive; 
# 增加Database相关属性 
create database test with dbproperties('name'='hh','date'='2012-01-02'); 
# 查看相关属性 
describe database extended test;

修改数据库位置

# 修改数据库位置 
set hive.metastore.warehouse.dir=/usr/hive/warehouse;

删除数据库

# 如果存在就删除，但是无法删除存在表的数据库 
DROP DATABASE IF EXISTS test; 
# 加上CASCADE,其对应的hadoop文件目录也会被删除 
DROP DATABASE IF EXISTS test CASCADE;

修改数据库

数据库的其他元数据无法修改，但是可以修改DBPROPERTIES设置的k-v

ALTER DATABASE test SET DBPROPERTIES("name"="h",date="2020-05-19");

Hive中的表

创建表

create table if not exists hive.employesss( 
name string comment 'name', 
salary double  , 
   subordinates array<string>, 
deduction map<string,double>, 
      address struct<stree:string,city:string,state:string,zip:int>) 
     comment '员工表' 
tblproperties('create'='me','created_at'='2020-05-18');

拷贝表模式

# 拷贝表 
CREATE TABLE IF NOT EXISTS employee2 LIKE employees;

查看指定数据库下的表

show  tables in default; 
# 正则查看表 
show tables 'empl.*';

查看表信息

show tables; 
# 查看表详情 
describe employees2; 
# 查看详细表结构信息 
describe extended employees2; 
# 查看格式化的表信息 
describe formatted employees2; 
# 查看某列信息 
describe test.name;

管理表

目前管理的表为内部表，这种表Hive会控制着数据的生命周期，Hive默认情况下将这些表的数据存储在由hive.metastroe.warehouse.dir下。
当我们删除一个管理表时， hive也会删除这些表中的数据。
存在的问题，管理表不方便和其他工作共享数据。此时可以通过创建一个外部表指向这份数据，并不需获得其所有权。

外部表

创建外部表

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

因为表示外部的，所以并非认为其完成拥有这份数据。因此，删除该表并不会删掉这份数据，不过描述表的元数据信息会被删除掉。
管理表和外部表的区别是有些HiveQL语法结构并不适用于外部表。

# 查看是管理表还是外部表 
describe extended employees2; 
tableType:MANAGED_TABLE 
# 复制管理表结构,如果省略external关键字而且源表是外部表的话，那么生成的还是外部表。如果源表是管理表的话，那么生成的还是管理表 
create external table if not exists employees3 like employees2;

分区表

基本概念

创建分区表

create table employees( 
name string,salary float,address struct<city:string,state:string>)partitioned by (country string,state:string); 
# 底层的hadoop还是由一个employees表

employees/country=CA/state=AB 
employees/country=CA/state=AC 
employees/country=CA/state=BC

因为country和state的值已经包含在文件目录名称中了，所以目录下的文件不会再存放这些数据。对数据进行分区，也可以更快的查询。
当使用where子句中增加谓词来按照分区值进行过滤时，这些谓词被成为分区过滤器。
如果表内分区数量众多那么查询所有分区的数据就会导致hive启动一个巨大的任务，可以设置hive为"strict"严格模式，这样如果对分区没有加WHERE子句就禁止提交这个任务。

set hive.mapred.mode=strict;

添加分区

# 添加分区到指定路径 
alter table log_message add partition(year=2021,month=1,day=2) 
LOCATION "hdfs://localhost:8020/data/log_message/2012/01/02";

查看表分区

SHOW PARTITIONS test; 
# 过滤分区查询 
SHOW PARTITIONS test PARTITION(country='CN'); 
# 查询分区键 
DESCRIBE EXTENDED employees;

载入数据的方式创建分区

LOAD DATA LOCAL INPATH '${env:HOME}/employees' 
INTO TABLE employees 
PARTITION(country='US',state='1')

外部分区表

外部分区表是管理大型生产数据集最常见的方法，这种方式提供了一种数据共享以及可以优化查询性能。

# 外部分区表 
CREATE EXTERNAL TABLE IF NOT EXISTS log_messages ( 
hms 　　　　 INT, 
severity 　　STRING, 
server 　　　STRING, 
process_id 　INT, 
message 　　 STRING) 
PARTITIONED BY (year INT, month INT, day INT) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

对于非分区外部表需要使用LOCATION子句，因为数据并非由Hive来管理而是由其他数据管理，对于外部分区表则不需要指定LOCATION子句。

分区管理

# 添加分区到指定路径 
alter table log_message add partition(year=2021,month=1,day=2) 
LOCATION "hdfs://localhost:8020/data/log_message/2012/01/02"; 
# 修改分区路径 
alter table log_message add partition(year=2021,month=1,day=2)set location "file://localhost:8020/data/log_message/2012/01/02"; 
# 查看分区实际存放路径 
describe extended log_message partition(year=2021,month=1,day=2);

自定义表的存储格式

STORED AS XXX；
- TEXTFILE表示所有字段都使用字母、数字、字符编码，包括那些国际字符集，使用TextFile代表每一行是一个单独记录。使用TextInputFormat对象来控制，使用HiveIgnoreKeyTextOutputFormat来控制输出。
- SEQUENCEFILE:使用二进制编码和压缩(可选)，优化磁盘空间使用以及IO带宽性能。
- RCFILE:使用二进制编码和压缩(可选)
记录编码通过InputFormat对象来控制，通过outputformat对象来将查询输出写入到文件中或输出到控制台中。
记录的解析是有序列化/反序列化器控制的(SerDe)。Hive使用的为LazySimpleSerDe的Java类。

使用自定义SerDe

create table kst partitioned by(ds string) row format serde 'com.linkedin.haivvreo.AvroSerde' 
With serdeproperties('schema.url'='http://test/kst.avsc') 
stored as inputformat 'com.linked.haivvreo.AvroContainerInputFormat' 
outputformat 'com.linedin.haivvero.AvroContrainerOutputFormat'

row format serde指定一个SerDe，serdeproperties传递配置给指定的SerDe。
store as inputformat...outputformat指定相关输入流和输出流格式

删除表

# 删除表，如果存在删除 
Drop table if exists test;

HDFS回收站

如果开启Hadoop回收站，删除的表将会移动到/user/$USER/.Trash目录下。

fs.trash.interval设置正整数,表示回收站检查点间的时间间隔，单位是分钟。如果设置值为1440表示24小时。如果删除数据后可以先重建表在重建分区然后从.Trash目录将误删除数据移动到正确的目录下。

修改表

Alter Table只能修改元数据，不会修改数据本身。

表重命名

Alter table log_message RENAME TO logmsg;

增加、修改和删除表分区

ALTER TABLE table ADD PARTITION .. 用于为表增加一个新分区。

ALTER TABLE test ADD IF NOT EXiSTS 
PARTITION(ds=1) LOCATION '/log/1'  
PARTITION(ds=2) LOCATION '/log/2' 
PARTITION(ds=3) LOCATION '/log/3';

修改分区路径

# 移动数据至另一个位置但是不会删除旧数据 
ALTER TABLE test PARTITION(ds=1) 
SET LOCATION 'file://log/1';

删除某个分区

ALTER TABLE test DROP IF NOT EXISTS PARTITION(ds=1);

修改列信息

# 表示在第一个位置 
alter table log 
change column name user_name string comment 'user name' first age; 
comment注释可选，after/first表示在之后或之前。

增加列

alter table log add colunms( 
app_name string, 
session_id int)

删除或者替换列

alter table log replace colunms( 
user_name string comment 'test', 
app_name string comment 'app name'); 
将log变替换为user_name和app_name俩列。

REPLACE语句只能用于2中内置SerDe模块的表:DynamicSerDe和MetadataTypedColumnsetSerDe。

修改表属性

alter table log set tblproperties( 
'test'='this is test'); 
# 查看表属性 
show  tblproperties log;

修改存储属性

# 修改分区表的fileformat 
alter table log 
partition(ds=1) 
set fileformat sequencefile; 
# 设置新的serde 
ALTER TABLE table_using_JSON_storage 
SET SERDE 'com.example.JSONSerDe' 
WITH SERDEPROPERTIES ( 
　'prop1' = 'value1', 
　'prop2' = 'value2'); 
# 设置新的serdeproperties属性 
alter table test 
set serdeproperties('test'='name') 
# 按照字段分桶 
alter table log 
clustered by(name) 
sorted by (ordered) 
into 48 buckets;

众多的修改表语句

# 当某个表被修改时，会触发钩子执行 
ALTER TABLE log TOUCH 
POARTITION(ds=1); 
# 压缩 
ALTER TABLE ... ARCHIVE PARTITION会将分区内的文件打包成一个Hadoop压缩包(HAR)文件 
# 解压HAR文件 
ALTER TABLE ... UNARCHIVE PARTITION会将分区内的文件打包成一个Hadoop压缩包(HAR)文件 
# 防止分区被删除和被查询 
ALTER TABLE log PARTITION(ds=1) ENABLE NO_DROP; 
ALTER TABLE log PARTITION(ds=1) ENABLE OFFLINE; 
# 使用DISABLE可以关闭保护限制

数据操作

向管理表中装载数据

# 加载HOME目录下的test目录下的文件，然后将数据放入分区ds=1的test表下，如果分区不存在会去创建分区，然后在拷贝到该目录下。 
LOAD DATA LOCAL INPUT '${env:HOME}'/test' 
OVERWRITE INTO TABLE test 
PARTITION(ds=1);

Local代表本地文件系统路径，如果没有Local的话是从分布式文件系统中拉取数据。
指定Overwrite将会把之前的数据都删除掉
hive不会验证数据与表模式是否一致，但是会验证文件格式与表定义的存储格式是否一致。

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

向指定分区插入数据

insert into table test1 partition(ds=1) 
select * from test;

多种方式划分数据

FROM test 
insert overwrite table test1 
partition(ds=1) 
select * where test.ds=1 
insert overwrite table test1 
partition(ds=2) 
select * test.ds=2;

动态分区插入

INSERT OVERWRITE TABLE employees 
PARTITION (country, state) 
SELECT ..., se.cnty, se.st 
FROM staged_employees se;

Hive根据SELECT语句中最后2列来确定分区字段country和state的值。

动态、静态分区混合使用

INSERT OVERWRITE TABLE test 
PARTITION(ds=1,state) 
select ...,se.cnty,se.st 
FROM test2 se 
where se.cnty='US';

静态分区必须出现了动态分区之前，动态分区功能默认情况下没有开启。开启后，默认是以“严格”模式执行的，在这种模式下要求至少有一列分区字段是静态的。这有助于阻止因设计错误导致查询产生大量的分区。例如，用户可能错误地使用时间戳作为分区字段，然后导致每秒都对应一个分区！而用户也许是期望按照天或者按照小时进行划分的。还有一些其他相关属性值用于限制资源利用。

动态分区属性

单个查询语句中创建表并加载数据

CREATE TABLE test AS SELECT name,salary,address FROM 
test1 where state='CA';

常见情况是从一个大的宽表中选取部分需要的数据集，此功能不能用于外部表。
无法对外部表进行此操作，类似与Alter table修改外部表也仅仅是修改外部表的Location

导出数据

如果数据的格式是用户需要的,通过hadoop shell即可

hdfs dfs -cp source_path target_path 
# 如下 
hdfs dfs -cp /user/hive/warehouse/test2/ds=3/test.txt file:///Users/babywang/Desktop

如果数据格式不同可以通过INSERT...DIRECTORY

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/ca_employees' 
SELECT name, salary, address 
FROM employees 
WHERE se.state = 'CA'; 
# 查看目录 
! ls /test 
! cat /test/0000_0 
# 将一张表的多个分区迁移 
from test 
insert overwrite directory '/test/1' 
select * where ds=1 
insert overwrite directory '/test/2' 
select * where ds=2 
insert overwrite directory '/test/3' 
select * where ds=3

查询

Select ...From语句

查询数组

select name[0] from test; 
# 如果数据不存在返回Null

查询Map元素

select order["name"] from test;

查询STRUCT数据

select detail.color from test;

使用正则表达式来指定列

select  name,`detail.*` from test;

使用列值进行计算

select upper(name),slary,deductions["test"], round(slary*(1-deductions["test"])) from test;

算数运算符

支持 常见算数运算符如:+、-、x、/、%、&、｜、^、～A取反

当int和bigint运行时，int会将类型转换为Bigint，int和float运算int会被转换为float

函数

内置数学函数

聚合函数

设置hive.map.aggr提高聚合的效率

表生成函数

嵌套Select语句

From(select name from test) as e 
select e.name where e.age>1000;

case...when..then句式

select name case  
    when salary<5000 then 'low' 
    when salary>=5000 and salary<7000 then 'middle' 
    else 'high' 
end as bracket from test;

避免Hive进行MapReduce

本地模式

查询目录的时候可以直接输出格式化到控制台无需走MR

select * from test;

对于Where语句中过滤条件只有分区字段的情况下也无需MapReduce过程。
如果hive.exec.mode.local.auto设置为true，Hive还会尝试用本地模式来执行其他操作，否则其他操作都用MR。

Where语句

where语句中不能使用列别名，可以使用select嵌套语句

select e.* from(select concat(name,'oo') as test1 from test2) as e where e.test1 like '10%';

谓词操作符

浮点数比较

select * from test where price >cast(0.2 as FLOAT);

Group By语句

select year(ymd),avg(price_closs) from test where ds='2020/01/02' group by year(ymd);

Having语句

select year(ymd),avg(price_closs) from test where ds='2020/01/02' group by year(ymd) 
Having avg(price_closs) >50.0;

JOIN语句

Hive支持JOIN语句，但只支持等值连接

INNER JOIN

内连接中，只有进行连接的两个表中都存在与连接标准匹配的数据才会被保留。
对于非等值连接，Hive是不支持的，因为对于MapRduce很难实现这种类型的连接。Pig中提供了交叉生产功能，Pig可以实现这种连接。
大部分情况下Hive会对每个Join连接对象启动一个MapReduce任务。

JOIN优化

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

每个ON子句都使用了a.ymd作为其中一个JOIN连接键。在这种情况下，Hive通过一个优化可以在同一个MapReduce job中连接3张表。同样，如果b.ymd也用于ON子句中的话，那么也会应用到这个优化。当多个表进行JOIN连接时，如果每个ON子句都使用相同的连接键的话，只会产生一个MapReduce job。
Hive同时假定查询中最后一个表是最大的那个表。在对每行记录进行连接操作时，它会尝试将其他表缓存起来，然后扫描最后那个表进行计算。用户需要保证连续查询中的表的大小从左到右是依次增加的。

标志机制

标志那个表是大表告诉Hive /+STREAMTABLE（s）/

SELECT /*+STREAMTABLE（s）*/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'；

LEFT OUTER JOIN

JOIN操作符左边表中符合Where子句的所有记录都将被返回，JOIN操作符右边表中如果没有符合ON后面连接条件的记录是，那么从右边表指定选择的列的值都将会是NULL 。

OUTER JOIN

在大多数SQL使用左连接后查询出来的NULL值，通过Where条件后都会被过滤掉，如果想要NULL限制可以通过OUTER JOIN。

SELECT s.ymd, s.symbol, s.price_close, d.dividend FROM 
(SELECT * FROM stocks WHERE symbol = 'AAPL' AND exchange = 'NASDAQ') s 
LEFT OUTER JOIN 
(SELECT * FROM dividends WHERE symbol = 'AAPL' AND exchange = 'NASDAQ') d 
ON s.ymd = d.ymd;

FULL OUTER JOIN

返回全部符合Where语句连接条件的所有记录，如果任一表的指定字段没有符合返回NULL

LEFT SEMI-JOIN

左半开连接会返回左边表记录，前提是其记录对于右边表满足ON语句中的判定条件。
效果如下

# 查询A表的ymd，symbol在B表也存在的记录 
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中方法

# select和where中不能引用右边表的字段 
select * from test t left semi join test2 on t.name=test2.name and  t.age=test2.age;

SEMI-JOIN比通常的INNER JOIN要高效，因为对于左边表中的一条指定的记录，在右边表一旦找到记录，HIve就会立即停止扫描。

笛卡尔积JOIN

select * from test1 join test2;

hive.mapred.mode值为strict的话，Hive会阻止用户执行笛卡尔积查询。

map-side JOIN

如果表中只有一张表是小表，那么可以在最大的表通过mapper的时候将小表完全放到内存中。Hive可以在map端执行连接过程(称为map-side JOIN)，这何地因为Hive可以和内存中的小表进行逐一匹配，从而省略掉产生的连接所需的reduce过程。
通过设置hive.auto.convert.join的值为true
通过hive.mapjoin.smalltable.filesize的大小，来决定多大的表可以放到内存中
对于右外和全外连接并不支持这个优化，如果表的数据是分桶的，那么对于大表，在特定的情况下同样可以使用这个优化。set hive.optimize.bucketmapJOIN=true;开启分桶优化

Order BY和Sort BY

Order BY会在reducer阶段来排序数据
SORT BY只会在每个reducer中对数据进行排序，也就是执行一个局部排序的过程，可以保证每个reducer的输出数据是有序的。可以提供后续全局排序的效率。如果reduce任务个数大于1，可能会出现重叠数据。

含有SORT BY的DISTRIBUTE BY

distribute by控制map的输出在reducer中如何划分，mapReduce job中传输的所有数据都是按照k-v方式进行组织的，因此Hive在将用户的查询语句转换成MR job时，其必须在内部使用这个功能。
保证相同的id的数据发送到同一个reducer任务中进行局部排序

select * from stocks distribute by id sort by id;

distrubute by和group by控制reducer是如何接受处理一行行数据进行处理，而Sort by则控制一个reducer内的数据是如何进行排序的。

CLUSTER BY

cluster by等价于distribute by和sort by组合使用的是相同的列进行排序和路由的情况下

# 上述语句可以修改为 
select * from stocket cluster by id;

使用distribute by...sort by语句或cluster by会剥夺sort by的并行性，然后可以实现输出文件的数据全局排序。

类型转换

cast语句将指定数据转换指定类型，如果转换失败会返回NULL

BINARY值类型转换

将BINARY类型转换为String

select (2.0*cast(cast(b as string) as double)) from test;

也可以将string转换为binary类型

抽样查询

使用rand()函数抽样查询

select * from numbers tablesample(bucket 3 out of 10 on rand(10))s;

数据块抽样

select * from numbers tablesample(0.1 percent)s;

这种抽样正对的最小但愿快是一个HDFS的数据block，如果表数据小于blockSize，将返回所有行。

分桶表的输出裁剪

# 查询2的倍数 
select * from number where number%2=0;

大数据类型的表抽样会扫描表中的所有数据，然后在每N行抽取一行数据。TBLESAMPLE语句中指定的列和CLUSTER BY语句中指定的列相同，那么TABLESAMPLE查询只会扫描涉及到表的hash分区下的数据。

UNION ALL

UNION ALL可以将2个或多个表进行合并，每个union子查询都必须具有相同的列，而且对应的类型必须相同。

SELECT log.ymd, log.level, log.message 
　FROM ( 
　　SELECT l1.ymd, l1.level, 
　　　l1.message, 'Log1' AS source 
　　FROM log1 l1 
　UNION ALL 
　　SELECT l2.ymd, l2.level, 
　　　l2.message, 'Log2' AS source 
　FROM log1 l2 
　) log 
SORT BY log.ymd ASC;

同一张表的合并

FROM ( 
　FROM src SELECT src.key, src.value WHERE src.key < 100 
　UNION ALL 
　FROM src SELECT src.* WHERE src.key > 110 
) unioninput 
INSERT OVERWRITE DIRECTORY '/tmp/union.out' SELECT unioninput.*'

视图

视图可以运行保存一个查询并像对待表一样对这个查询进行操作。这只是一个逻辑结构不会存储数据，Hive目前不支持物化视图。

使用试图来降低查询复杂度

当查询变得长或复杂的时候，通过使用试图将这个查询语句分割成多个小的、可控的片段来降低查询复杂度。
创建视图

# 原始嵌套查询 
from( 
select * from test join test1 on test.name=test1.name 
where test.name='zhangsan') s  
select s.* where s.age>10; 
# 视图方式 
create view test_view as select * from test join test1 on test.name=test1.name where test.name='zhangsan'; 
select * from test_view where age>10;

使用视图来限制基于条件过滤的数据

# 源表 
create table userinfo( 
firstname string,lastname string,ssn string,password string); 
# 限制视图 
create view userinfo_view as 
select firstname,lastname from userinfo;

动态分区中的视图和map类型

# map数据格式^B kv分隔符，^A元素分隔符 
time^B1298598398404^Atype^Brequest^Astate^Bny^Acity^Bwhite 
plains^Apart\^Bmuffler 
time^B1298598398432^Atype^Bresponse^Astate^Bny^Acity^Btarrytown^ 
Apart\^Bmuffler 
time^B1298598399404^Atype^Brequest^Astate^Btx^Acity^Baustin^ 
Apart^Bheadlight 
# 创建表 
CREATE EXTERNAL TABLE dynamictable(cols map<string,string>) 
ROW FORMAT DELIMITED 
　FIELDS TERMINATED BY '\004' 
　COLLECTION ITEMS TERMINATED BY '\001' 
　MAP KEYS TERMINATED BY '\002' 
STORED AS TEXTFILE; 
# 创建视图,包含state和city 
create view order(state,city) as 
select cols["state"],cols["city"] from dynamictable; 
# 创建视图包含time,part 
CREATE VIEW shipments(time, part) AS 
SELECT cols["time"], cols["parts"] 
FROM dynamictable 
WHERE cols["type"] = "response";