包含storedasorc的词条
hive复合数据类型array
create table temp.array_test(
name string,
active_date string,
active_num arraystring)
partitioned by (
dt string)
stored as orc;
create table temp.actvice_array_test(
name string,
active_date string,
active_num arraystring)
partitioned by (
dt string)
stored as orc;
insert overwrite table temp.array_test partition(dt)
select 'wdx','20180312',array('0','0','0','0'),'20180312';
hive select name,array(active_num[0],active_num[1],active_num[2],active_num[3]) from temp.array_test;
OK
wdx ["0","0","0","0"]
Time taken: 0.06 seconds, Fetched: 1 row(s)
insert overwrite table temp.actvice_array_test partition(dt)
select
name, active_date,
case when active_date = '20180312' then
array(active_num[1],active_num[2],active_num[3],'1')
else array(active_num[1],active_num[2],active_num[3],'0') end,
'20180312'
from temp.array_test
where dt = '20180312';
hive select * from temp.actvice_array_test;
OK
wdx 20180312 ["0","0","0","1"] 20180312
深入理解Hive分区与分桶
目前，在利用hive建设数据仓库的过程中，总会遇见分区分桶的，跟传统的DBMS系统一样，通过表分区能够在特定的区域检索数据，减少扫描成本，在一定程度上提高查询效率。比如我们要收集某个大型网站的日志数据，一个网站每天的日志数据存在同一张表上，由于每天会生成大量的日志，导致数据表的内容巨大，在查询时进行全表扫描耗费的资源非常多。那其实这个情况下，我们可以按照日期对数据表进行分区，不同日期的数据存放在不同的分区，在查询时只要指定分区字段的值就可以直接从该分区查找。在物理上分区表会将数据按照分区键的列值存储在表目录的子目录中，目录名=“分区键=键值”。其中需要注意的是分区键的值不一定要基于表的某一列（字段），它可以指定任意值，只要查询的时候指定相应的分区键来查询即可。
分桶与分区有所不同，它指定分桶表的某一列，让该列数据按照哈希取模的方式随机、均匀地分发到各个桶文件中。因为分桶操作需要根据某一列具体数据来进行哈希取模操作，故指定的分桶列必须基于表中的某一列（字段）。因为分桶改变了数据的存储方式，它会把哈希取模相同或者在某一区间的数据行放在同一个桶文件中。如此一来便可提高查询效率，比如我们要对两张在同一列上进行了分桶操作的表进行JOIN操作的时候，只需要对保存相同列值的桶进行JOIN操作即可。同时分桶也可以提高采样率。
分区是为了对表进行合理的管理以及提高查询效率，Hive可以将表组织成“分区”。一个分区实际上就是表下的一个目录，一个表可以在多个维度上进行分区，分区之间的关系就是目录树的关系。Hive(Inceptor)分区分为静态分区跟动态分区，详细介绍如下：
静态分区在插入或者导入的时候需要指定具体的分区
[if !supportLists]1、? [endif]静态分区创建
需要在PARTITIONED BY后面跟上分区键，类型。例如：
CREATE?TABLE?p_table1(
id int
,name
string
)
PARTITIONED BY(date_day string)
stored as orc
;
这是一级分区，当然也可以创建多级分区。例如：
CREATE?TABLE? p_table1(
id int
,name string
)
PARTITIONED BY(date_day string, company
string,emp_no string)
stored as orc
;??????????????????????????????????????????????????????????????????????????
下面的实例都是以一级分区为例。?????????????????????
[if !supportLists]2、? [endif]静态分区插入数据
insert overwrite table p_table1 partition(date_day='2019-07-14')
values(1,'lucy');
或者insert overwrite table p_table1 partition(date_day='2019-07-15')
select 2 as id,'lily' as name;
上面两个例子都是覆盖的形式，也就是插入这个分区之前，会将该分区数据删除，再插入新的数据，也可以写成追加的形式：
insert into p_table1
partition(date_day='2019-07-14') values(1,'lucy');
或者insert into e p_table1 partition(date_day='2019-07-15') select 2 as
id,'lily' as name;
[if !supportLists]3、? [endif]静态分区查看分区
查看所有分区show partitions p_table1
结果如下：
date_day=2019-07-14
date_day=2019-07-15
查看某个分区show partitions p_table1 partition(date_day='2019-07-14');
结果如下：
date_day=2019-07-14
[if !supportLists]4、? [endif]静态分区删除分区
删除某个分区alter table p_table1 drop partition(date_day='2019-07-14');
或者删除范围内的分区alter table p_table1 drop partition(date_day='2019-07-14');
1、动态分区创建
创建方式与静态分区表完全一样，一张表可同时被静态和动态分区键分区，只是动态分区键需要放在静态分区建的后面（因为HDFS上的动态分区目录下不能包含静态分区的子目录）。
CREATE TABLE p_table2(
id int
,name string
)
PARTITIONED BY(date_day string,emp_no
string)
stored as orc
;
这是创建了二级分区表。
2、动态分区插入数据
插入数据时需要开启动态数据支持：
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nostrict;
插入数据（覆盖）insert overwrite table p_table2 partition(date_day，emp_no)
select 2 as id,'lily' as name,'2019-07-14' as date_day, ‘a’ as emp_no;
分区并没有写死，而是根据查询到的值动态创建的两级分区。
3、动态分区查看分区、删除分区与静态分区操作完全一致不再重述。
分桶字段是表内字段，默认是对分桶的字段进行hash值，然后模总的桶数，得到的值则是分区桶数，主要有以下两点好处：
（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。
创建分桶表
先看一下创建分桶表的创建，分桶表的建表有三种方式：直接建表，CREATE TABLE LIKE 和 CREATE TABLE AS SELECT ，单值分区表不能用 CREATETABLE
AS SELECT 建表。这里以直接建表为例：
create table b_table1(id int,name string)
clustered by (id) sorted by(id) into 4 buckets stored as textfile;
使用CLUSTERED BY 子句来指定划分桶所用的列和要划分的桶的个数，当表分区时，每个分区下都会有4个桶。对于map端连接的情况，两个表以相同方式划分桶。处理左边表内某个桶的 mapper知道右边表内相匹配的行在对应的桶内。因此，mapper只需要获取那个桶 (这只是右边表内存储数据的一小部分)即可进行连接。这一优化方法并不一定要求两个表必须桶的个数相同，两个表的桶个数是倍数关系也可以。用HiveQL对两个划分了桶的表进行连接。
桶中的数据可以根据一个或多个列另外进行排序。由于这样对每个桶的连接变成了高效的归并排序(merge-sort), 因此可以进一步提升map端连接的效率。
向分桶表写入数据
如何保证表中的数据都划分成桶了呢？把在Hive外生成的数据加载到划分成桶的表中，当然是可以的。其实让Hive来划分桶更容易。这一操作通常针对已有的表。
Hive并不检查数据文件中的桶是否和表定义中的桶一致(无论是对于桶的数量或用于划分桶的列）。如果两者不匹配，在査询时可能会碰到错误或未定义的结果。因此，建议让Hive来进行划分桶的操作。
要向分桶表中填充成员，需要将 hive.enforce.bucketing 属性设置为 true。这样Hive 就知道用表定义中声明的数量来创建桶。
下面有个未分桶的用户表b_user_test，数据如下：
1??????a
2??????b
3??????c
4??????d
5??????e
6??????f
7??????g
插入语句
INSERT OVERWRITE TABLE b_table1 SELECT *
FROM b_user_test;
查看文件结构
dfs -ls/user/hive/warehouse/bucketed_users;
文件结构如下所示：
?/user/hive/warehouse/b_table1/000000_0
?/user/hive/warehouse/b_table1/000001_0
?/user/hive/warehouse/b_table1/000002_0
?/user/hive/warehouse/b_table1/000003_0
查看文件000000_0
dfs -cat /user/hive/warehouse/bucketed_users/000000_0;
值为4 d说明文件000000_0存的是对分桶数求余等于0的那部分数据。
对桶中的数据进行采样
对分桶进行查询 tablesample(bucket x out of y on id)：
x:表示查询那个桶
y:表示建表指定的桶的总数,如果不是建表时指定的桶的总数，则会重新分桶。
x不能大于y。
取第一个桶的数据：
Sql：SELECT * FROM
b_table1 TABLESAMPLE(BUCKET 2 OUT OF 4 ON id);
结果：
5??????e
[if !supportLists]1???????????????????????[endif]a
当桶数不等于建表指定的桶的总数时
Sql：SELECT * FROM
b_table1 TABLESAMPLE(BUCKET 2 OUT OF 3 ON id);
结果：
4??????d
1??????a
7??????g
由结果可知，进行了重新分桶，分成了三个桶，取出第二个桶的数据，也就是hash值对3求余等于1的那部分数据。
分桶比分区粒度更细，在每个分区了可以将数据进行分桶操作。
Hive实战之Youtube数据集
本次实战的数据来自于"YouTube视频统计与社交网络"的数据集，是西蒙弗雷泽大学计算机学院在2008年所爬取的数据
数据集地址
数据之间采用"\t"作为分隔符
具体数据如下：
数据量大小为1G，条数为500万+
使用环境为
hive-1.1.0-cdh5.4.5
hadoop-2.6.0-cdh5.4.5
演示形式为使用hive shell
我们一起来看看数据
主要的问题在于category和relatedIDs处理，由于Hive是支持array格式的，所以我们想到的是使用array来存储category和relatedIDs，但是我们发现category的分割符是""而realatedIDs的分隔符是"\t"，我们在创建表格的时候能够指定array的分隔符，但是只能指定一个，所以再将数据导入到Hive表格之前我们需要对数据进行一定转换和清洗
并且数据中肯定会存在一些不完整数据和一些奇怪的格式，所以数据的清洗是必要的，我在这里所使用的数据清洗方式是使用Spark进行清洗，也可以使用自定义UDF函数来进行清洗
数据清洗注意点
1）我们可以看到每行数据以"\t"作为分隔符，每行有十列数据，最后一列关联ID可以为空，那么我们对数据进行split之后数组的大小要大于8
2）数据中存在 "uNiKXDA8eyQ KRQE 1035 News amp; Politics 107" 这样格式的数据，所以在处理category时需要注意 News Politics中间的 amp;
处理后的数据如下：
下面的实战都是基于数据清洗后的数据进行的
1）youtube1的创建，文件格式为textfile
create table youtube1(videoId string, uploader string, age int, category arraystring, length int, views int, rate float, ratings int, comments int,relatedId arraystring)
row format delimited
fields terminated by "\t"
collection items terminated by ""
stored as textfile;
2）youtube2的创建，文件格式为orc
create table youtube2(videoId string, uploader string, age int, category arraystring, length int, views int, rate float, ratings int, comments int,relatedId arraystring)
row format delimited
fields terminated by "\t"
collection items terminated by ""
stored as orc;
3）youtube3的创建，文件格式为orc，进行桶分区
create table youtube3(videoId string, uploader string, age int, category arraystring, length int, views int, rate float, ratings int, comments int,relatedId arraystring)
clustered by (uploader) into 8 buckets
row format delimited
fields terminated by "\t"
collection items terminated by ""
stored as orc;
数据导入：
1）load data inpath "path" into table youtube1;
2）由于无法将textfile格式的数据导入到orc格式的表格，所以数据需要从youtube1导入到youtube2和youtube3：
insert into table youtube2 select * from youtube1;
insert into table youtube3 select * from youtube1;
1）user_tmp的创建，文件格式textfile，24buckets
create table user_tmp(uploader string,videos int,friends int)
clustered by (uploader) into 24 buckets
row format delimited
fields terminated by "\t"
stored as textfile;
2）user的创建，文件格式orc，24buckets
create table user(uploader string,videos int,friends int)
clustered by (uploader) into 24 buckets
row format delimited
fields terminated by "\t"
stored as orc;
user表的数据导入也是同理
数据导入：
1）load data inpath "path" into table user_tmp;
2）由于无法将textfile格式的数据导入到orc格式的表格，所以数据需要从user_tmp导入到user：
insert into table user select * from user_tmp;
1）统计出观看数最多的10个视频
2）统计出视频类别热度的前10个类型
3）统计出视频观看数最高的50个视频的所属类别
4）统计出观看数最多的前N个视频所关联的视频的所属类别排行
5）筛选出每个类别中热度最高的前10个视频
6）筛选出每个类别中评分最高的前10个视频
7）找出用户中上传视频最多的10个用户的所有视频
8）筛选出每个类别中观看数Top10
select * from youtube3 order by views desc limit 10;
结果如下：
select tagId, count(a.videoid) as sum from (select videoid,tagId from youtube3 lateral view explode(category) catetory as tagId) a group by a.tagId order by sum desc limit 10;
结果：
select tagId, count(a.videoid) as sum from (select videoid,tagId from (select * from youtube3 order by views desc limit 20) e lateral view explode(category) catetory as tagId) a group by a.tagId order by sum desc;
结果：
思路：
结果：
思路：
结果如下：
select * from youtube_category where categoryId="Music" order by ratings desc limit 10;
结果如下：
思路：
结果如下：
hive查询时间复杂度
1、使用Tez引擎
Apache Tez Engine是一个可扩展的框架，用于构建高性能批处理和交互式数据处理。它由YARN在Hadoop中 调度。Tez通过提高处理速度和保持MapReduce扩展到数PB数据的能力来改进MapReduce job。
通过设置hive.execution.engine 为tez：可以在环境中启用Tez引擎：
set hive.execution.engine=tez;
2、使用向量化
向量化通过在单个操作中获取 1024 行而不是 每次只获取单行来改善 scans, aggregations, filters 和 join 这类操作的性能。
我们可以通过执行以下命令在环境中启用向量化：
set hive.vectorized.execution.enabled=true;
set hive.vectorized.execution.reduce.enabled=true;
3、使用ORCFile
Hive 支持 ORCfile，这是一种新的表存储格式，在读取，写入和处理数据时，ORCFile格式优于Hive文件格式，它通过 predicate push-down, compression 等技术来提高查询速度。
在 HIVE 表中使用 ORCFile，将有益于获得 HIVE 快速响应的查询。
ORCFile 格式通过对原始数据存储量压缩75％，提供了高效的存储 Hive 数据的方法。
举例，考虑两个大表 A 和 B（存储为 TextFIle，这里没有指定一些列），使用一个简单的查询，如：
SELECT A.customerID,
A.name,
A.age,
A.address
JOIN B.role,
B.department,
B.salary ON A.customerID=B.customerID;
由于表 A 和表 B 都存储为 TextFile，因此执行此查询可能需要很长时间。
将这些表存储格式转换为 ORCFile 格式通常会明显减少查询时间：
CREATE TABLE A_ORC (
customerID int,
name string,
age int,
address string
) STORED AS ORC tblproperties (“orc.compress" = “SNAPPY”)
;
INSERT INTO TABLE A_ORC
SELECT *
FROM A
;
CREATE TABLE B_ORC (
customerID int,
ROLE string,
salary float,
department string
) STORED AS ORC tblproperties (“orc.compress" = “SNAPPY”)
;
INSERT INTO TABLE B_ORC
SELECT *
FROM B
;
SELECT A_ORC.customerID,
A_ORC.name,
A_ORC.age,
A_ORC.address
JOIN B_ORC.role,
B_ORC.department,
B_ORC.salary ON A_ORC.customerID=B_ORC.customerID
;
ORC 支持压缩存储（使用 ZLIB 或如上所示使用 SNAPPY），但也支持不压缩存储。
4、使用分区
通过分区，数据存储在 HDFS 上的单独单个文件夹中。Hive 将查询分区数据集，而不是 扫描表的所有数据集。
创建临时表并将数据加载到临时表中
CREATE TABLE Employee_Temp(
EmloyeeID int,
EmployeeName Varchar(100),
Address Varchar(100),
STATE Varchar(100),
City Varchar(100),
Zipcode Varchar(100)
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
LOAD DATA INPATH '/home/hadoop/hive' INTO TABLE Employee_Temp;
创建分区表
Create Table Employee_Part(
EmloyeeID int,
EmployeeName Varchar(100),
Address Varchar(100),
State Varchar(100),
Zipcode Varchar(100))
PARTITIONED BY (City Varchar(100))
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
启用动态分区的命令
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;
从临时表导入数据到分区表
INSERT Overwrite TABLE Employee_Part Partition(City)
SELECT EmployeeID,
EmployeeName,
Address,
STATE,
City,
Zipcode
FROM Emloyee_Temp;
5、使用 分桶
桶表介绍：
举例：
Hive 表被划分为多个分区，称为 Hive分区。Hive分区进一步细分为集群或桶，称为 bucket 或 Cluster。
Create Table Employee_Part(
EmloyeeID int,
EmployeeName Varchar(100),
Address Varchar(100),
State Varchar(100),
Zipcode Varchar(100))
PARTITIONED BY (City Varchar(100))
Clustered By (EmployeeID) into 20 Buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
6、CBO 查询优化器
Hive CBO 是使用 Apache calcite 来处理的。
早期的 Hive 版本中（Hive-0.14 之前），在提交最终执行之前，Hive 会优化每个查询的逻辑和物理执行计划。 这些优化不是基于查询的成本优化(Cost-based Optimizer) 。
直到 Hive-0.14 时才添加了 Cost-based optimization ，这时已经根据查询的成本进行优化（例如要执行的连接类型，如何排序连接，并行度等）。
要使用基于成本的优化，需要在查询开头设置以下参数
set hive.cbo.enable=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
set hive.stats.fetch.partition.stats=true;
如果要收集表信息，请使用 Analyze 命令。
7、写好的 SQL
SQL是一种强大的声明性语言。 与其他声明性语言一样，编写SQL语句的方法不止一种。
尽管每个语句的功能都相同，但它可能具有截然不同的性能特征。
我们来看一个例子。 考虑点击流事件表：
CREATE TABLE clicks (
timestamp date, sessionID string, url string, source_ip string
) STORED as ORC tblproperties (“orc.compress” = “SNAPPY”);
每条记录代表一次点击事件，我们希望找到每个sessionID的最新网址。
有人可能会考虑以下方法：
SELECT clicks.* FROM clicks inner join
(select sessionID, max(timestamp) as max_ts from clicks
group by sessionID) latest
ON clicks.sessionID = latest.sessionID and
clicks.timestamp = latest.max_ts;
在上面的查询中，我们构建一个子查询来收集每个会话中最新事件的时间戳，然后使用 内联接 来过滤掉其余的事件。
虽然查询是一个合理的解决方案， 但是从功能的角度来看 ，有一种更好的方法来重写这个查询，如下所示：
SELECT * FROM
(SELECT *, RANK() over (partition by sessionID,
order by timestamp desc) as rank
FROM clicks) ranked_clicks
WHERE ranked_clicks.rank=1;
在这里，我们使用 Hive 的 OLAP 窗口功能（OVER 和 RANK）来实现相同的功能。
显然，删除不必要的连接几乎总能带来更好的性能，而且当使用大数据时，这比以往任何时候都更重要。
我发现很多情况下查询不是最优的 - 所以仔细查看每个查询并考虑重写是否可以使它更好更快。
————————————————
版权声明：本文为CSDN博主「highfei2011」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：