hive create view hive create view 分区

hive-4

• 分区

• 创建分区表
• 插入数据
• 动态分区

• 分桶
• 分区和分桶的区别
• 视图

• 侧视图

分区

分区的目的：通过表分区能够在特定的区域检索数据，减少扫描成本，在一定程度上提高查询效率
单值分区：

创建分区表

# 创建分区表
create table tab_name(
	id int,
	name String,
	dt String
)
partitioned by (month String);

注意事项：

1. 分区字段可以有多个，中间用逗号隔开
2. 分区的字段名不可以和表的字段名重复（查询时可以看到，分区字段列在最后面）

插入数据

insert into table page_view
-- country字段没有给定，即按照最后一个字段动态确定
partition (dt="2008-06-08",country)
-- select语句中，前面的列和要插入的表列一一对应，在这些列之后是分区字段，顺序和上面partition对应
SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, pvs.ip, pvs.cnt

-- 分区字段指定方式
partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

-- 添加分区字段
alter table tab_name add partition partition_spec;

-- 修改分区
ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;

-- 查看分区
show partitions tab_name;

-- 删除分区
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...];

动态分区

区别于静态分区，动态分区字段的值根据查询的结果给出，当有数据产生的分区字段与以往数据都不同时，就会创建新的分区存储

动态分区语法和静态分区基本相同，唯一区别在于静态分区在插入数据时给定了分区字段值，动态分区仅列出分区字段，值由查询结果给出

The dynamic partition columns must be specified last among the columns in the SELECT statement and in the same order in which they appear in the PARTITION() clause.

使用动态分区需要修改几项参数设定：

配置项	默认值	含义
hive.exec.dynamic.partition	true	是否开启insert操作时动态分区
hive.exec.dynamic.partition.mode	strict	严格模式，指用户必须指定至少一个静态的分区字段，防止不小心重写全部分区，设定为nonstrict后，可以将全部分区字段设为动态分区
hive.exec.max.dynamic.partitions.pernode	100	在每个MR结点上，允许创建的最大动态分区个数
hive.exec.max.created.files	100000	在一次job中，允许创建的最大（HDFS）文件个数
hive.error.on.empty.partition	false	在动态分区的insert操作中，如果产生了空的结果，是否报错

范围分区

CREATE [EXTERNAL] TABLE <table_name>
    (<col_name> <data_type>, <col_name> <data_type>, ...)
    PARTITIONED BY RANGE (<partition_key> <data_type>, ...) 
        (PARTITION [<partition_name>] VALUES LESS THAN (<cutoff>), 
            [PARTITION [<partition_name>] VALUES LESS THAN (<cutoff>),
              ...
            ]
            PARTITION [<partition_name>] VALUES LESS THAN (<cutoff>|MAXVALUE) 
        )
    [ROW FORMAT <row_format>] [STORED AS TEXTFILE|ORC|CSVFILE]
    [LOCATION '<file_path>']    
    [TBLPROPERTIES ('<property_name>'='<property_value>', ...)];

分区是按照指定的列划分为多个区，区在形式上可以理解为文件夹

分桶

分桶表举例：

CREATE TABLE page_view(
	viewTime INT,
	userid BIGINT,
    page_url STRING,
    referrer_url STRING,
    ip STRING COMMENT 'IP Address of the User'
)
COMMENT 'This is the page view table'
PARTITIONED BY(dt STRING, country STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\001'
  COLLECTION ITEMS TERMINATED BY '\002'
  MAP KEYS TERMINATED BY '\003'
STORED AS SEQUENCEFILE;

在上面这个例子中，page_view表就是按照userid字段分桶（clustered by指定）的，且指定userid按照递增顺序，使用分桶表可以提高对分桶字段数据（userid）的取样效率

clustered by和sorted by指令并不影响数据插入表中的过程，只会指定数据如何被读取。

使用分桶功能前，需要先进行设置：sethive.enforce.bucketing=true;，允许分桶

如果分桶较多，需要配置map和reduce个数限制：
set hive.mapreduces.job=8; 设置Job数量
set mapred.reduce.tasks=8; 设置reducetask数量

# 创建分桶
CREATE [EXTERNAL] TABLE <table_name>
    (<col_name> <data_type> [, <col_name> <data_type> ...])]
    [PARTITIONED BY ...] 
    CLUSTERED BY (<col_name>) 
        [SORTED BY (<col_name> [ASC|DESC] [, <col_name> [ASC|DESC]...])] 
        INTO <num_buckets> BUCKETS  
    [ROW FORMAT <row_format>] 
    [STORED AS TEXTFILE|ORC|CSVFILE]
    [LOCATION '<file_path>']    
    [TBLPROPERTIES ('<property_name>'='<property_value>', ...)];

Hive分桶表数据分配方式：

一般来说，数据的分配是根据hash_function(bucketing_column)来计算的（类似mapreduce中的HashPartitioner分配策略）。

当然，分桶字段数值类型不同是，分配的方式也会不同。最简单的是int类型，int类型的数值计算后的结果仍和本身相等。例如：以int类型的user_id字段分为10个桶，那么所有userid尾数为0的就会在第一个桶内（对10取余），userid尾数为1的就会在第二个桶内

使用hash来分配分桶可以使数据分配更加的均匀，避免出现负载失衡的情况

分区和分桶的区别

在Hive数仓中也有分区分桶的概念，在逻辑上分区表与未分区表没有区别，在物理上分区表会将数据按照分区键的列值存储在表目录的子目录中，目录名=“分区键=键值”。其中需要注意的是分区键的值不一定要基于表的某一列（字段），它可以指定任意值，只要查询的时候指定相应的分区键来查询即可。我们可以对分区进行添加、删除、重命名、清空等操作。因为分区在特定的区域（子目录）下检索数据，它作用同DNMS分区一样，都是为了减少扫描成本。

分桶则是指定分桶表的某一列，让该列数据按照哈希取模的方式随机、均匀地分发到各个桶文件中。因为分桶操作需要根据某一列具体数据来进行哈希取模操作，故指定的分桶列必须基于表中的某一列（字段）。因为分桶改变了数据的存储方式，它会把哈希取模相同或者在某一区间的数据行放在同一个桶文件中。如此一来便可提高查询效率，如：我们要对两张在同一列上进行了分桶操作的表进行JOIN操作的时候，只需要对保存相同列值的桶进行JOIN操作即可。同时分桶也能让取样（Sampling）更高效。

视图

hive的视图创建和mysql语法相同，作用和特性也相同，将查询的结果临时保存起来，作为视图，视图会实时展示原表的变化，且视图的增删改不会影响原表（前提不是单表形成的视图）；

# 创建视图
create view view_name as select ...

# 删除视图
drop view view_name;

# 更改视图定义
alter view view_name as select ...

侧视图

常与表生成函数结合使用，将函数的输入输出连接
outer关键字：即使output为空也会生成结果

# 单层级侧视图
select col_name,.... from tab_name lateral view outer explode(split(null,',')) a as loc;

# 多层级侧视图
select col_name,... from tab_name
lateral view outer explode(col_name) a as wps
lateral view outer explode(col_name) b as skill