Hive常用操作

1．Hive最基本操作

（1）启动hive

   bin/hive

（2）查看数据库

hive> show databases;

（3）打开默认数据库

hive> use default;

（4）显示default数据库中的表

hive> show tables;

（5）创建一张表

hive> create table student(id int, name string);

（6）显示数据库中有几张表

hive> show tables;

（7）查看表的结构

hive> desc student;

（8）向表中插入数据

hive> insert into student values(1000,"ss");

（9）查询表中数据

hive> select * from student;

（10）退出hive

hive> quit;

 

2．数据库相关操作

（1）创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name

[COMMENT database_comment]

[LOCATION hdfs_path]

[WITH DBPROPERTIES (property_name=property_value, ...)];

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

hive (default)> create database db_hive;

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

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上存放的位置

hive (default)> create database db_hive2 location '/db_hive2.db';

（2）查询数据库

--显示数据库

hive> show databases; 

--过滤显示查询的数据库

hive> show databases like 'db_hive*'; 

--显示数据库信息

hive> desc database db_hive; 

--使用extended命令显示数据库详细信息

hive> desc database extended db_hive; 

--切换到指定数据库

hive (default)> use db_hive; 

（3）修改数据库

用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。

数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。

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

（4）删除数据库

--删除数据库

drop database db_hive2; 

--删除数据库前判断数据库是否存在,防止数据库不存在报错

drop database if exists db_hive2; 

--使用cascade命令强制删除非空数据库

drop database db_hive cascade; 

 

3．Hive表操作

（1）Hive建表语法

建表语法:

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]

[TBLPROPERTIES (property_name=property_value, ...)]

[AS select_statement]

参数说明:

-- EXTERNAL指定创建外部表

-- COMMENT：为表和列添加注释

-- PARTITIONED BY创建分区表

-- CLUSTERED BY创建分桶表

-- SORTED BY不常用，对桶中的一个或多个列另外排序

-- ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] 按指定的分隔符对字段进行分割

-- STORED AS指定存储文件类型

-- LOCATION ：指定表在HDFS上的存储位置。

-- AS：后跟查询语句，根据查询结果创建表

-- LIKE允许用户复制现有的表结构，但是不复制数据。

（2）Hive修改表语法

--重命名表

ALTER TABLE table_name RENAME TO new_table_name; 

-- 更新列

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

--增加和替换列，ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。

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

（3）Hive删除表语法

drop table dept_partition;

 

4．内部表和外部表的区别

（1）创建表

内部表:会将数据移动到数据仓库指向的路径。

外部表: 需在建表时使用EXTERNAL 关键字。

创建表时,仅记录数据所在的路径， 不对数据的位置做任何改变。

（2）删除表

内部表: 元数据和数据会被一起删除。

外部表: 只删除元数据，不删除数据。这样外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据。

（3）使用场景

内部表 : 不需要共享的计算结果或中间数据

外部表 : 适用于多部门共享数据

（4）内部表和外部表的转换

-- 修改内部表student2为外部表

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

-- 修改外部表student2为内部表

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

-- 注意:('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

 

5．数据导入和导出

（1）数据导入

① 向表中装载数据(Load):

语法:

load data [local] inpath '/opt/module/datas/xxx.txt' [overwrite] into table 表名[partition (partcol1=val1,…)];

-- load data:表示加载数据

-- local : 从本地加载数据到hive表,否则从hdfs加载数据到hive表

-- inpath: 表示加载数据的路径

-- overwrite:表示覆盖已有数据,否则追加

-- into table:加载到哪张表

-- 表名 : 表示具体的表名

-- partition:上传到指定分区

 

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

--insert into: 以追加数据的方式插入到表或分区,原有数据不会删除  insert不支持插入部分字段

insert into table  student partition(month='202007') values(1,'xmr'),(2,’rxct’); 

--insert overwrite:会覆盖表或分区中已存在的数据

insert overwrite table student partition(month='202006') select id, name from student where month='202005';

 

③ 使用import,将数据导入到指定Hive表中

import table student2 partition(month='202006') from '/user/hive/warehouse/export/student';

 

（2）数据导出

① 常规数据导出方式

--使用insert命令将查询结果导出(本地 有local)或HDFS(没有Local)

insert overwrite (local) directory '/opt/module/datas/export/student' select * from student; 

--使用hdfs命令将数据导出到本地

dfs -get /user/hive/warehouse/student/month=202007/000000_0 /opt/module/datas/export/student3.txt; 

--使用hive -f/-e 执行语句或者脚本

bin/hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt;  

--使用export命令导出到HDFS上

export table default.student to '/user/hive/warehouse/export/student'; 

 

② 使用sqoop导出数据

# 首先设置关系数据库相关配置

database_connection="jdbc:postgresql://192.168.0.2:8080/db_name" #数据库连接uri

database_username="user" #用户名

database_password="pass" #密码

databaseTableName="test2" #表名

databaseColums="a1,a2,a3" #需要的列

 

#将数据写到一个临时表中<br>hive -v -e "

set mapred.job.queue.name=${queueName};

 

USE hive_db_name;

INSERT OVERWRITE TABLE test3

select a1,a2,a3 from test1 where y='2020' and m='07' and d='25';

";

 

if [[ $? != 0 ]]; then

print_log "hive执行失败!"

exit 1

fi

 

# 设置hdfs路径

exportHdfsPath="hdfs:///user/hive/warehouse/sqoop/test3"

 

sqoop export -Dmapred.job.queue.name=$queueName \   //mr的队列

--connect $database_connection \        //关系型数据库的连接

--username $database_username \      //关系型数据库的用户名

--password $database_password \      //关系型数据的密码

--export-dir $exportHdfsPath \              //hive表的hdfs路径

--verbose \

--table $databaseTableName \          //要写到的表名

--columns $databaseColums \          //要写过去的字段名

--input-fields-terminated-by '\001' \   //hive表的分割符，注意表一定是行式存储的表才能导出，一般是text后缀的表

--input-lines-terminated-by '\n' \       //每行的分割符

--input-null-string '\\N' \                    //空值处理

--input-null-non-string '\\N'               //空值处理

 

if [[ $? != 0 ]]; then

print_log "sqoop导出失败!"

exit 1

fi

 

6．Hive分区

分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。

Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。

在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，通过减少查询的数据量来提高查询效率。

Hive静态分区和动态分区公用操作如下:

# 创建分区表

create table dept_partition(

deptno int, dname string, loc string

)

partitioned by (month string)

row format delimited fields terminated by '\t';

-- 注意:根据创建的表中某个字段进行分区操作时,创建表时不可添加这个字段,例如:以month分区,建表的字段不能包含month

 

# 操作分区表

select * from dept_partition where month='202007'; -- 查询分区表中数据

alter table dept_partition add partition(month='202006') ; -- 增加单个分区

alter table dept_partition add partition(month='202005') partition(month='202004'); -- 增加多个分区

 alter table dept_partition drop partition (month='202007'); -- 删除单个分区

 alter table dept_partition drop partition (month='202005'), partition (month='202006'); -- 删除多个分区

 show partitions dept_partition; -- 查看分区表有多少分区

 desc formatted dept_partition; -- 查看分区表结构

 

（1）Hive静态分区

# 静态分区使用load方式导入数据

load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='202007'); --加载数据到分区表

 

# 静态分区使用insert方式导入数据

insert into default.dept_partition partition(month='202007') select deptno, dname , loc from partition_test where month = '202007';

-- 注意查询的字段要与创建分区表时的字段一致

-- 产生一级子目录，需手动创建每一个分区，当分区较多时，操作比较麻烦

 

# 产生二级子目录

insert into empar partition (year=2015,month=11) select name,id,number from employee_hr where year(start_date)=2015 and month(start_date)=11;

load data local inpath '/opt/datas/dept.txt' into table dept_partition2 partition(year='2019', month='12');

 

（2）Hive动态分区

关系型数据库中，对分区表Insert数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用Hive的动态分区，需要进行相应的配置。

# 开启动态分区功能（默认true，开启）

hive.exec.dynamic.partition=true

# 设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）

hive.exec.dynamic.partition.mode=nonstrict

# 在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000

hive.exec.max.dynamic.partitions=1000

# 在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。

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

# 整个MR Job中，最大可以创建多少个HDFS文件。默认100000

hive.exec.max.created.files=100000

# 当有空分区生成时，是否抛出异常。一般不需要设置。默认false

hive.error.on.empty.partition=false

# 不需要手动一个一个分区，可以根据关键字自动分区。

# 此时select中的字段要和创建表时的字段加上partition的关键字的一致。根据一个字段分区

insert into dept_partition partition(month) select * from partition_test;

# 根据两个字段分区

insert into empar partition(year,month) select name,id,number,year(start_date),month(start_date) from employee_hr;

 

（3）Hive动态分区和静态分区的区别

Hive是默认开启动态分区的,但是想要更好的利用动态分区特性,需要进行必要的设置

静态分区是手动指定，而动态分区是通过数据来进行判断。详细来说，静态分区的列是在编译时期，通过用户传递来决定的；动态分区只有在SQL执行时才能决定。

静态分区支持load和insert两种插入方式,动态分区只支持insert方式