【系统学习Hive常用知识】

前言

Hive是由Facebook开源，基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL查询功能。

一、Hive是什么？

Hive是一个Hadoop客户端，用于将HQL（Hive SQL）转化成MapReduce程序。
（1）Hive中每张表的数据存储在HDFS
（2）Hive分析数据底层的实现是MapReduce（也可配置为Spark或者Tez）
（3）执行程序运行在Yarn上

1）用户接口：Client
CLI（command-line interface）、JDBC/ODBC。
说明：JDBC和ODBC的区别。
（1）JDBC的移植性比ODBC好；（通常情况下，安装完ODBC驱动程序之后，还需要经过确定的配置才能够应用。而不相同的配置在不相同数据库服务器之间不能够通用。所以，安装一次就需要再配置一次。JDBC只需要选取适当的JDBC数据库驱动程序，就不需要额外的配置。在安装过程中，JDBC数据库驱动程序会自己完成有关的配置。）
（2）两者使用的语言不同，JDBC在Java编程时使用，ODBC一般在C/C++编程时使用。

2）元数据：Metastore
元数据包括：数据库（默认是default）、表名、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等。
默认存储在自带的derby数据库中，由于derby数据库只支持单客户端访问，生产环境中为了多人开发，推荐使用MySQL存储Metastore。

二、Hive安装配置

1.hive包安装

1）把apache-hive-3.1.3-bin.tar.gz上传到Linux的/opt/software目录下
2）解压apache-hive-3.1.3-bin.tar.gz到/opt/module/目录下面

 tar -zxvf /opt/software/apache-hive-3.1.3-bin.tar.gz -C /opt/module/

3）修改apache-hive-3.1.3-bin.tar.gz的名称为hive

 mv /opt/module/apache-hive-3.1.3-bin/ /opt/module/hive

4）修改/etc/profile.d/my_env.sh，添加环境变量

 sudo vim /etc/profile.d/my_env.sh

（1）添加内容

#HIVE_HOME
export HIVE_HOME=/opt/module/hive
export PATH=$PATH:$HIVE_HOME/bin

（2）source一下

source /etc/profile.d/my_env.sh

5）初始化元数据库（默认是derby数据库）

bin/schematool -dbType derby -initSchema

2、配置Hive元数据存储到MySQL

1、mysql创建metastore数据库

#登录MySQL
mysql -uroot -p****

#创建Hive元数据库
mysql> create database metastore;
mysql> quit;

2）将MySQL的JDBC驱动拷贝到Hive的lib目录下。

 cp /opt/software/mysql-connector-java-5.1.37.jar $HIVE_HOME/lib

3）在$HIVE_HOME/conf目录下新建hive-site.xml文件

 vim $HIVE_HOME/conf/hive-site.xml

4）添加以下内容

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<configuration>
    <!-- jdbc连接的URL -->
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://hadoop102:3306/metastore?useSSL=false</value>
    </property>
    
    <!-- jdbc连接的Driver-->
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
    </property>
    
	<!-- jdbc连接的username-->
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
    </property>

    <!-- jdbc连接的password -->
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>****</value>
    </property>

    <!-- Hive默认在HDFS的工作目录 -->
    <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/user/hive/warehouse</value>
    </property>
</configuration>

5）初始化Hive元数据库（修改为采用MySQL存储元数据）

 bin/schematool -dbType mysql -initSchema -verbose

3、Hive服务部署

在远程访问Hive数据时，客户端并未直接访问Hadoop集群，而是由Hivesever2代理访问。由于Hadoop集群中的数据具备访问权限控制，所以此时需考虑一个问题：那就是访问Hadoop集群的用户身份是谁？是Hiveserver2的启动用户？还是客户端的登录用户？答案是都有可能，具体是谁，由Hiveserver2的hive.server2.enable.doAs参数决定，该参数的含义是是否启用Hiveserver2用户模拟的功能。若启用，则Hiveserver2会模拟成客户端的登录用户去访问Hadoop集群的数据，不启用，则Hivesever2会直接使用启动用户访问Hadoop集群数据。模拟用户的功能，默认是开启的。生产环境，推荐开启用户模拟功能，因为开启后才能保证各用户之间的权限隔离。hivesever2的模拟用户功能，依赖于Hadoop提供的proxy user（代理用户功能），只有Hadoop中的代理用户才能模拟其他用户的身份访问Hadoop集群。因此，需要将hiveserver2的启动用户设置为Hadoop的代理用户，配置方式如下：

(1)修改配置文件core-site.xml

cd $HADOOP_HOME/etc/hadoop
vim core-site.xml

增加如下配置：

<!--配置所有节点的zqw用户都可作为代理用户-->
<property>
    <name>hadoop.proxyuser.zqw.hosts</name>
    <value>*</value>
</property>

<!--配置zqw用户能够代理的用户组为任意组-->
<property>
    <name>hadoop.proxyuser.zqw.groups</name>
    <value>*</value>
</property>

<!--配置zqw用户能够代理的用户为任意用户-->
<property>
    <name>hadoop.proxyuser.zqw.users</name>
    <value>*</value>
</property>

（2）Hive端配置
在hive-site.xml文件中添加如下配置信息

vim hive-site.xml

<!-- 指定hiveserver2连接的host -->
<property>
	<name>hive.server2.thrift.bind.host</name>
	<value>hadoop102</value>
</property>

<!-- 指定hiveserver2连接的端口号 -->
<property>
	<name>hive.server2.thrift.port</name>
	<value>10000</value>
</property>

三、Hive数据操作

1、DDL数据定义

创建数据库

创建表
语法：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]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]
[TBLPROPERTIES (property_name=property_value, ...)]

（1）内部表与外部表

Hive中默认创建的表都是的内部表，有时也被称为管理表。对于内部表，Hive会完全管理表的元数据和数据文件。
创建内部表

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

创建外部表

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

删除表时，内部表会将hdfs的数据文件一并删除，外部表不会。
(1)

(2)以json数据格式创建，使用专门负责JSON文件的JSON Serde，设计表字段时，表的字段与JSON字符串中的一级字段保持一致，对于具有嵌套结构的JSON字符串，考虑使用合适复杂数据类型保存其内容。

{
    "name": "laoshi",
    "friends": [
        "xiaoming",
        "xiaogang"
    ],
    "students": {
        "xiaoli": 18,
        "xiaozhang": 16
    },
    "address": {
        "street": "shang hai nan lu",
        "city": "beijing",
        "postal_code": 10010
    }
}

create table if not exists teacher(
    name string,
    friends array<string>,
    students map<string,int>,
    address struct<city:string,street:string,postal_code:int>
) row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'
location '/teacher';

(3)as select 与 create table like 创建表

create table teacher1 as select * from teacher;
create table teacher2 like teacher;

修改表

（1）表重命名

ALTER TABLE table_name RENAME TO new_table_name

（2）增加列

ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)

（3）修改列

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

（4）替换列

ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

删除表

DROP TABLE [IF EXISTS] table_name;

清空表

TRUNCATE [TABLE] table_name

truncate只能清空管理表，不能删除外部表中数据。

2、DML数据操作

Load
Load语句可将文件导入到Hive表中。
语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)];

（1）local：表示从本地加载数据到Hive表；否则从HDFS加载数据到Hive表。
（2）overwrite：表示覆盖表中已有数据，否则表示追加。
（3）partition：表示上传到指定分区，若目标是分区表，需指定分区。
案例：

create table student(
    id int, 
    name string
) 
row format delimited fields terminated by '\t';

1）加载本地文件到hive

 load data local inpath '/opt/module/datas/student.txt' into table student;

（2）加载HDFS文件到hive中

load data inpath '/user/zqw/student.txt' into table student;
#注意：加载之后HDFS数据文件将删除

（3）加载数据覆盖表中已有的数据

load data inpath '/user/zqw/student.txt' overwrite into table student;

Insert
（1）查询结果插入到表中
语法：

INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement;

1）INTO：将结果追加到目标表
2）OVERWRITE：用结果覆盖原有数据
案例：

create table student1(
    id int, 
    name string
) 
row format delimited fields terminated by '\t';

insert overwrite table student3 select id, name from student;

（2）将给定Values插入表中

insert into table  student1 values(1,'zhangsan'),(2,'lisi');

（3）将查询结果写入目标路径
语法：

INSERT OVERWRITE [LOCAL] DIRECTORY directory
  [ROW FORMAT row_format] [STORED AS file_format] select_statement;

案例：

insert overwrite local directory '/opt/module/datas/student' ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
select id,name from student;

3、Export&Import

Export导出语句可将表的数据和元数据信息一并到处的HDFS路径，Import可将Export导出的内容导入Hive，表的数据和元数据信息都会恢复。Export和Import可用于两个Hive实例之间的数据迁移。

语法

--导出
EXPORT TABLE tablename TO 'export_target_path'
--导入
IMPORT [EXTERNAL] TABLE new_or_original_tablename FROM 'source_path' [LOCATION 'import_target_path']

案例

--导出
export table default.student to '/user/hive/warehouse/export/student';
--导入，无需提前创建表
import table student2 from '/user/hive/warehouse/export/student';

四、查询

语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference       -- 从什么表查
  [WHERE where_condition]   -- 过滤
  [GROUP BY col_list]        -- 分组查询
   [HAVING col_list]          -- 分组后过滤
  [ORDER BY col_list]        -- 排序
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]                -- 限制输出的行数

基本语法与关系数据库相同，在此不再记录

1、Sort By

每个Reduce内部排序
Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用Sort by。
Sort by为每个reduce产生一个排序文件。每个Reduce内部进行排序，对全局结果集来说不是排序。

set mapreduce.job.reduces=3;

select * from emp sort by deptno desc;

2、分区（Distribute By）

Distribute By：在有些情况下，我们需要控制某个特定行应该到哪个Reducer，通常是为了进行后续的聚集操作。distribute by子句可以做这件事。distribute by类似MapReduce中partition（自定义分区），进行分区，结合sort by使用。 对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。
案例

--distribute by的分区规则是根据分区字段的hash码与reduce的个数进行相除后，余数相同的分到一个区。
--Hive要求distribute by语句要写在sort by语句之前。
select * from emp distribute by deptno sort by sal desc;

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

3、分区排序（Cluster By）

当distribute by和sort by字段相同时，可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为asc或者desc。

select * from emp cluster by deptno;

五、函数

1、数值函数

1）round：四舍五入

select round(3.3);   
--3

2）ceil：向上取整

select ceil(3.1) ;   
--4

3）floor：向下取整

select floor(4.8);  
--4

2、字符串函数

1）substring：截取字符串
语法一：substring(string A, int start)
返回值：string
说明：返回字符串A从start位置到结尾的字符串
语法二：substring(string A, int start, int len)
返回值：string
说明：返回字符串A从start位置开始，长度为len的字符串

2）replace ：替换
语法：replace(string A, string B, string C)
返回值：string
说明：将字符串A中的子字符串B替换为C。

3）repeat：重复字符串
语法：repeat(string A, int n)
返回值：string
说明：将字符串A重复n遍。

4）split ：字符串切割
语法：split(string str, string pat)
返回值：array
说明：按照正则表达式pat匹配到的内容分割str，分割后的字符串，以数组的形式返回。

5）nvl ：替换null值
语法：nvl(A,B)
说明：若A的值不为null，则返回A，否则返回B。

6）concat ：拼接字符串
语法：concat(string A, string B, string C, ……)
返回：string
说明：将A,B,C……等字符拼接为一个字符串

7）concat_ws：以指定分隔符拼接字符串或者字符串数组
语法：concat_ws(string A, string…| array(string))
返回值：string
说明：使用分隔符A拼接多个字符串，或者一个数组的所有元素。

8）get_json_object：解析json字符串
语法：get_json_object(string json_string, string path)
返回值：string
说明：解析json的字符串json_string，返回path指定的内容。如果输入的json字符串无效，那么返回NULL。
案例：
案例实操：
（1）获取json数组里面的json具体数据

select get_json_object('[{"name":"张三","sex":"男","age":"25"},{"name":"李四","sex":"男","age":"47"}]','$.[0].name');
--张三

（2）获取json数组里面的数据

select get_json_object('[{"name":"张三","sex":"男","age":"25"},{"name":"李四","sex":"男","age":"47"}]','$.[0]');
--{"name":"张三","sex":"男","age":"25"}

3、日期函数

1）unix_timestamp：返回当前或指定时间的时间戳
语法：unix_timestamp()
返回值：bigint

案例：

select unix_timestamp('2022/08/08 08-08-08','yyyy/MM/dd HH-mm-ss');  
--前面是日期，后面是指日期传进来的具体格式 

3）current_date：当前日期

select current_date;

4）current_timestamp：当前的日期加时间，并且精确的毫秒

select current_timestamp;   

5）获取日期中的年、月、日、小时

--年
select year('2022-08-08 08:08:08');
--月
select month('2022-08-08 08:08:08');
--日
select day('2022-08-08 08:08:08');
--小时
select hour('2022-08-08 08:08:08');

6）datediff：两个日期相差的天数（结束日期减去开始日期的天数）
语法：datediff(string enddate, string startdate)
返回值：int
案例：

select datediff('2021-08-08','2022-10-09');    

7）date_add：日期加天数
语法：date_add(string startdate, int days)
返回值：string
说明：返回开始日期 startdate 增加 days 天后的日期
案例实操：

select date_add('2022-08-08',2);   

8）date_sub：日期减天数
语法：date_sub (string startdate, int days)
返回值：string
说明：返回开始日期startdate减少days天后的日期。
案例实操：

select date_sub('2022-08-08',2);    

9）date_format:将标准日期解析成指定格式字符串

select date_format('2022-08-08','yyyy年-MM月-dd日')   

4、控制函数

1）case when：条件判断函数
语法一：case when a then b [when c then d]* [else e] end
返回值：T
说明：如果a为true，则返回b；如果c为true，则返回d；否则返回 e

select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from tableName; 

语法二： case a when b then c [when d then e]* [else f] end
返回值: T
说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f

select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from tableName; 

2）if: 条件判断，类似于Java中三元运算符
语法：if（boolean testCondition, T valueTrue, T valueFalseOrNull）
返回值：T
说明：当条件testCondition为true时，返回valueTrue；否则返回valueFalseOrNull
（1）条件满足，输出正确

select if(10 > 5,'正确','错误'); 

5、集合函数

1）size：集合中元素的个数

select size(friends) from test;  
--2/2  每一行数据中的friends集合里的个数

2）map：创建map集合
语法：map (key1, value1, key2, value2, …)
说明：根据输入的key和value对构建map类型

select map('xiaozhang',1,'xiaoli',2);

3）map_keys： 返回map中的key

select map_keys(map('xiaozhang',1,'xiaoli',2));
--["xiaozhang","xiaoli"]

4）map_values: 返回map中的value

select map_values(map('xiaozhang',1,'xiaoli',2));
--[1,2]

5）array 声明array集合
语法：array(val1, val2, …)
说明：根据输入的参数构建数组array类

select array('1','2','3','4');
--["1","2","3","4"]

6）array_contains: 判断array中是否包含某个元素

select array_contains(array('a','b','c','d'),'a');
--true

7）sort_array：将array中的元素排序

select sort_array(array('a','d','c'));
--["a","c","d"]

8）struct声明struct中的各属性
语法：struct(val1, val2, val3, …)
说明：根据输入的参数构建结构体struct类

select struct('name','age','weight');
--{"col1":"name","col2":"age","col3":"weight"}

9）named_struct声明struct的属性和值

select named_struct('name','xiaosong','age',18,'weight',80);
--{"name":"xiaosong","age":18,"weight":80}

6、聚合函数

多进一出 （多行传入，一个行输出）
（1）collect_list 收集并形成list集合，结果不去重

select deptno,collect_list(ename) from emp group by deptno;

（2）collect_set 收集并形成set集合，结果去重

select deptno,collect_set(job) from emp group by deptno;

7、炸裂函数

一进多出（一行传入，多行输出）

select movie,cate 
from
(select movie, split(category,',') as cates 
from movie_info)t lateral view explode(cates) tmp as cate

8、窗口函数

窗口函数，能为每行数据划分一个窗口，然后对窗口范围内的数据进行计算，最后将计算结果返回给该行数据。
按照功能，常用窗口可划分为如下几类：聚合函数、跨行取值函数、排名函数。
1）跨行取值函数
（1）lead和lag
获取当前行的上/下边某行、某个字段的值。
案例

select order_id,user_id,user_name,order_date,
		lag(order_date,1,null) over (partition by user_id order by order_date)as last_date,
		lead(order_date,1,null) over (partition by user_id order by order_date) as next_date
from order_info

（2）first_value和last_value
获取窗口内某列第一个和最后一个值

select order_id,user_id,user_name,order_date,
       first_value(order_date,false) over (partition by user_id order by order_date) as fir,
       last_value(order_date,false) over (partition by user_id order by order_date) as las
from order_info;

2）排名函数
常用窗口函数——rank、dense_rank、row_number
rank：重复值排名一样，后续排名会不连续，比如：1，2，2，4，5，5，7
dense_rank：重复值排名一样，后续排名会连续，比如：1，2，2，3，4，4，5
row_number：行号
案例

select ename,sal,deptno,rank() over (partition by deptno order by sal desc) as rk,
       dense_rank() over (partition by deptno order by sal desc) as dk,
       row_number() over (partition by deptno order by sal desc) as rn
       from emp;

结果：

3）指定行
PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点（一般结合PRECEDING，FOLLOWING使用）
UNBOUNDED PRECEDING 表示该窗口最前面的行（起点）
UNBOUNDED FOLLOWING：表示该窗口最后面的行（终点）
比如说：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示从起点到当前行）
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING（表示往前2行到往后1行）
ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW（表示往前2行到当前行）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING（表示当前行到终点）

select order_id,order_amount,user_id,user_name,
       sum(order_amount) over (partition by user_id order by order_date rows between unbounded preceding and current row ) as sum_so_far
from order_info;

六、分区表和分桶表

1、分区表

Hive中的分区就是把一张大表的数据按照业务需要分散的存储到多个目录，每个目录就称为该表的一个分区。在查询时通过where子句中的表达式选择查询所需要的分区，这样的查询效率会提高很多。
创建分区表

hive (default)> 
create table dept_partition
(
    deptno int,    --部门编号
    dname  string, --部门名称
    loc    string  --部门位置
)
    partitioned by (day string)
    row format delimited fields terminated by '\t';

装载数据

load data local inpath '/opt/module/hive/datas/dept_20220401.log' 
into table dept_partition 
partition(day='20220401');

将day='20220401’分区的数据插入到day='20220402’分区，可执行如下装载语句

insert overwrite table dept_partition partition (day = '20220402')
select deptno, dname, loc
from dept_partition
where day = '2020-04-01';

读数据
查询分区表数据时，可以将分区字段看作表的伪列，可像使用其他字段一样使用分区字段。

select deptno, dname, loc ,day
from dept_partition
where day = '2020-04-01';

增加分区

alter table dept_partition add partition(day='20220403');

同时创建多个分区

alter table dept_partition add partition(day='20220404') partition(day='20220405');

删除分区

alter table dept_partition drop partition (day='20220403');

同时删除多个分区

alter table dept_partition drop partition (day='20220404'), partition(day='20220405');

2、修复分区

Hive将分区表的所有分区信息都保存在了元数据中，只有元数据与HDFS上的分区路径一致时，分区表才能正常读写数据。若用户手动创建/删除分区路径，Hive都是感知不到的，这样就会导致Hive的元数据和HDFS的分区路径不一致。除了使用add partition、drop partition手动修复分区之外，使用msck命令进行修复，以下是该命令的用法说明。

msck repair table table_name [add/drop/sync partitions];

说明：

msck repair table table_name add partitions
--该命令会增加HDFS路径存在但元数据缺失的分区信息。
msck repair table table_name drop partitions
--该命令会删除HDFS路径已经删除但元数据仍然存在的分区信息。
msck repair table table_name sync partitions
--该命令会同步HDFS路径和元数据分区信息，相当于同时执行上述的两个命令。
msck repair table table_name
--等价于msck repair table table_name add partitions命令。

3、二级分区

可以在按天分区的基础上，再对每天的数据按小时进行分区

hive (default)>
create table dept_partition2(
    deptno int,    -- 部门编号
    dname string, -- 部门名称
    loc string     -- 部门位置
)
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';

load data local inpath '/opt/module/hive/datas/dept_20220401.log' 
into table dept_partition2 
partition(day='20220401', hour='12');

select * 
from dept_partition2 
where day='20220401' and hour='12';

4、动态分区

动态分区是指向分区表insert数据时，被写往的分区不由用户指定，而是由每行数据的最后一个字段的值来动态的决定。使用动态分区，可只用一个insert语句将数据写入多个分区。
（1）动态分区功能总开关（默认true，开启）

set hive.exec.dynamic.partition=true

（2）严格模式和非严格模式
动态分区的模式，默认strict（严格模式），要求必须指定至少一个分区为静态分区，nonstrict（非严格模式）允许所有的分区字段都使用动态分区。

set hive.exec.dynamic.partition.mode=nonstrict

（3）一条insert语句可同时创建的最大的分区个数，默认为1000。

set hive.exec.max.dynamic.partitions=1000

（4）单个Mapper或者Reducer可同时创建的最大的分区个数，默认为100。

set hive.exec.max.dynamic.partitions.pernode=100

（5）一条insert语句可以创建的最大的文件个数，默认100000。

set hive.exec.max.created.files=100000

（6）当查询结果为空时且进行动态分区时，是否抛出异常，默认false。

set hive.error.on.empty.partition=false

案例

create table dept_partition_dynamic(
    id int, 
    name string
) 
partitioned by (loc int) 
row format delimited fields terminated by '\t';

set hive.exec.dynamic.partition.mode = nonstrict;
insert into table dept_partition_dynamic 
partition(loc) 
select 
    deptno, 
    dname, 
    loc 
from dept;

5、分桶表

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分，分区针对的是数据的存储路径，分桶针对的是数据文件。
分桶表的基本原理是，首先为每行数据计算一个指定字段的数据的hash值，然后模以一个指定的分桶数，最后将取模运算结果相同的行，写入同一个文件中，这个文件就称为一个分桶（bucket）。
1）语法

create table stu_buck(
    id int, 
    name string
)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

分桶排序表
1）语法

create table stu_buck_sort(
    id int, 
    name string
)
clustered by(id) sorted by(id)
into 4 buckets
row format delimited fields terminated by '\t';

七、Hive文件格式

为Hive表中的数据选择一个合适的文件格式，对提高查询性能的提高是十分有益的。Hive表数据的存储格式，可以选择text file、orc、parquet、sequence file等。

1、Text File

文本文件是Hive默认使用的文件格式，文本文件中的一行内容，就对应Hive表中的一行记录。

2、ORC

ORC（Optimized Row Columnar）file format是Hive 0.11版里引入的一种列式存储的文件格式。ORC文件能够提高Hive读写数据和处理数据的性能。
（1）行存储的特点
查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。
（2）列存储的特点
因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。
前文提到的text file和sequence file都是基于行存储的，orc和parquet是基于列式存储的。

每个Orc文件由Header、Body和Tail三部分组成。
其中Header内容为ORC，用于表示文件类型。
Body由1个或多个stripe组成，每个stripe一般为HDFS的块大小，每一个stripe包含多条记录，这些记录按照列进行独立存储，每个stripe里有三部分组成，分别是Index Data，Row Data，Stripe Footer。
Index Data：一个轻量级的index，默认是为各列每隔1W行做一个索引。每个索引会记录第n万行的位置，和最近一万行的最大值和最小值等信息。
Row Data：存的是具体的数据，按列进行存储，并对每个列进行编码，分成多个Stream来存储。
Stripe Footer：存放的是各个Stream的位置以及各column的编码信息。
Tail由File Footer和PostScript组成。File Footer中保存了各Stripe的其实位置、索引长度、数据长度等信息，各Column的统计信息等；PostScript记录了整个文件的压缩类型以及File Footer的长度信息等。
在读取ORC文件时，会先从最后一个字节读取PostScript长度，进而读取到PostScript，从里面解析到File Footer长度，进而读取FileFooter，从中解析到各个Stripe信息，再读各个Stripe，即从后往前读。

建表语句文章来源地址https://www.toymoban.com/news/detail-828993.html

create table orc_table
(column_specs)
stored as orc
tblproperties (property_name=property_value, ...);

到了这里，关于【系统学习Hive常用知识】的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！