02常用操作

本文最后更新于 2021-08-05 11:42:59

HIVE常用操作

hive hql

显示数据库

show databases;

使用默认数据库

use default;

删除创建的表

drop table student;
truncate table student;

创建表并指定分隔符

create table student(id int,name string) 
row format delimited fields terminated by ','   --字段分割符
collection items terminated by '_'              --集合分割符 map array struct
map keys terminated by ':'                      --map key分隔符
lines terminated by '\n';                       --行分隔符

查询表结构

show create table student;

查询库信息

desc database xxx;
desc database extended xxx; --详细信息

查询表信息

desc 表名;
desc formatted 表名;
desc extended 表名;

创建表

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] --表中的数据要以哪种文件格式来存储，默认为TEXTFILE（文本文件）
					    --可以设置为SequnceFile或 Paquret,ORC等
[LOCATION hdfs_path] --表在hdfs上的位置


create table xxx like xxx1;

EXTERNAL:

不带EXTERNAL 是一个MANAGED_TABLE （管理表，内部表）
带EXTERNAL 是一个外部表
内部表(管理表)在执行删除操作时，会将表的元数据(schema)和表位置的数据一起删除！
外部表在执行删除表操作时，只删除表的元数据(schema)
转换：alter table p1 set tblproperties('EXTERNAL'='TRUE');

PARTITIONED BY：

在MR中分区概念：在MapTask输出key-value时，为每一个key-value计算一个分区号，同一个分区的数据，会被同一个reduceTask处理，每个分区的数据最终会生成一个结果文件
HIVE中：将表中的数据，分散到表目录下的多个子目录（分区目录）中
意义：为了将数据分散到多个子目录中，在执行查询时，可以只选择某些子目录中的数据，加快查询效率
分区查询 show partitions 表名；
一级分区
- 创建分区：alter table 表名 add partition(分区字段名=分区字段值) partition(分区字段名=分区字段值)；
- 创建后在hdfs上会生成分区字段名=分区字段值的路径
- 创建后在mysql中 partitions表中生成分区数据
- 直接使用load向分区加载数据，如果分区不存在，load时会自动生成
- load data loacl inpath '/xx/xx/xx' into datble 表名 partition(area='xxx');
- 删除分区：alter table xxx drop partition(area='xxx') , partition(area='xxx') 管理表会删除数据
多级分区：
- PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)
- alter table 表名 add partition(分区字段名=分区字段值,分区字段名=分区字段值) partition(分区字段名=分区字段值,分区字段名=分区字段值)；
- load data loacl inpath '/xx/xx/xx' into datble 表名 partition(area='xxx',pp='xxx');

LOCATION：

指定数据位置
如果数据已经按照规范格式上传到了hdfs 可以使用分区修复命令 msck repair table 表名

CLUSTERED BY && SORTED BY

指定了 CLUSTERED BY 称为分桶表，把数据分到多个文件中
本质也是为了分散数据，在分桶后可以结合hive提供的抽样查询，只查询指定桶的数据
如果需要排序可以加上SORTED BY
clustered by (id) sorted by (id desc) into 4 buckets
分桶表导入数据需要运行MR 分桶排序
不能使用load 必须执行insert into
insert into 表名 select xxxx
导入数据前要设置强制分桶 set hive.enforce.bucketing=true
设置强制排序 set hive.enforce.sirting=true
抽样查询 select * from 分桶表 tablesample(bucket x of y on 分桶表分桶字段)
抽样查询必须时分桶表
假设分了z个桶 x：标识从第几桶开始抽 y: z/y表示抽多少桶
y必须是z的因子或倍数 0<x<=y
从x桶开始抽，每隔y桶抽一桶直到抽满 z/y桶

SERDE：hive中序列化器和反序列化器

表中的数据是什么样的格式，就必须使用什么样的SerDe!
纯文本： row format delimited ，默认使用LazySimpleSerDe
JSON格式：使用JsonSerde
ORC：使用读取ORC的SerDe
Paquet: 使用读取PaquetSerDe
HDFS files –> InputFileFormat –> <key, value> –> Deserializer –> Row object
Row object –> Serializer –> <key, value> –> OutputFileFormat –> HDFS files
调用InputFormat，将文件切成不同的文档。每篇文档即一行(Row)。
调用SerDe的Deserializer，将一行(Row)，切分为各个字段。

create table testSerde2(
name string,
friends array<string>
)
ROW FORMAT SERDE 
'org.apache.hive.hcatalog.data.JsonSerDe' 
STORED AS TEXTFILE

查询

select * from student


Hive的本质是MR，MR中如何排序的！
				全排序：  结果只有一个(只有一个分区)，所有的数据整体有序！
				部分排序：  结果有多个(有多个分区)，每个分区内部有序！
				二次排序：  在排序时，比较的条件有多个！
				
				排序： 在reduce之前就已经排好序了，排序是shuffle阶段的主要工作！
				
		排序？ 
		分区：使用Partitioner来进行分区！
					当reduceTaskNum>1，设置用户自己定义的分区器，如果没有使用HashParitioner!
					HashParitioner只根据key的hashcode来分区！
				
ORDER BY col_list ：  全排序！ 
SORT BY col_list ： 部分排序！ 设置reduceTaskNum>1。 只写sort by是随机分区！
						如果希望自定定义使用哪个字段分区，需要使用DISTRIBUTE BY
						
DISTRIBUTE BY  col_list：指定按照哪个字段分区！结合sort by 使用！
CLUSTER BY col_list  ： 如果分区的字段和排序的字段一致，可以简写为CLUSTER BY 
							DISTRIBUTE BY sal sort by sal asc  等价于  CLUSTER BY  sal
							
						要求： CLUSTER BY  后不能写排序方式，只能按照asc排序！
SORT BY: SORT BY 是一个部分排序方案， 其只会在每个reducer 中对数据进行排序，也就是执行一个局部排序过程。

插入

insert ...

导入数据

# 1. 直接上传到hdfs对应表目录下
# 2. load
load data local inpath '/xxxx/xxx' into table student


1.load : 作用将数据直接加载到表目录中
	语法：  load  data [local] inpath 'xx' into table 表名 [partition()]
	
				local:  如果导入的文件在本地文件系统，需要加上local，使用put将本地上传到hdfs
						不加local默认导入的文件是在hdfs，使用mv将源文件移动到目标目录
						
2. insert： insert方式运行MR程序，通过程序将数据输出到表目录！
		在某些场景，必须使用insert方式来导入数据：
				①向分桶表插入数据
				②如果指定表中的数据，不是以纯文本形式存储，需要使用insert方式导入
				
	语法： insert into|overwrite table 表名 select xxx | values(),(),() 
				insert into: 向表中追加新的数据
				insert overwrite： 先清空表中所有的数据，再向表中添加新的数据
				
	特殊情况： 多插入模式(从一张源表查询，向多个目标表插入)
				from 源表
				insert xxxx  目标表  select xxx
				insert xxxx  目标表  select xxx
				insert xxxx  目标表  select xxx
	
	举例： from deptpart2
           insert into table deptpart1 partition(area='huaxi') select deptno,dname,loc
            insert into table deptpart1 partition(area='huaxinan') select deptno,dname,loc 
            
3. 建表时指定location
4.import: 导入数据和元数据 导入 export的内容
IMPORT [[EXTERNAL] TABLE 表名(新表或已经存在的表) [PARTITION (part_column="value"[, ...])]]
  FROM 'source_path'
  [LOCATION 'import_target_path']
				①如果向一个新表中导入数据，hive会根据要导入表的元数据自动创建表
				②如果向一个已经存在的表导入数据，在导入之前会先检查表的结构和属性是否一致
						只有在表的结构和属性一致时，才会执行导入
				③不管表是否为空，要导入的分区必须是不存在的
				
  import external table importtable1  from '/export1'

动态分区

往hive分区表中插入数据时，如果需要创建的分区很多，比如以表中某个字段进行分区存储，则需要复制粘贴修改很多sql去执行，效率低。因为hive是批处理系统，所以hive提供了一个动态分区功能，其可以基于查询参数的位置去推断分区的名称，从而建立分区。

create table dpartition(id int ,name string )
   partitioned by(ct string  );
   
-- 往表里装载数据，并且动态建立分区，以city建立动态分区
hive.exec.dynamici.partition=true;  #开启动态分区，默认是false
set hive.exec.dynamic.partition.mode=nonstrict; #开启允许所有分区都是动态的，否则必须要有静态分区才能使用。
insert overwrite table dpartition
partition(ct)
select id ,name,city from  mytest_tmp2_p;
-- 因为dpartition表中只有两个字段，所以当我们查询了三个字段时（多了city字段），所以系统默认以最后一个字段city为分区名，因为分区表的分区字段默认也是该表中的字段，且依次排在表中字段的最后面。所以分区需要分区的字段只能放在后面，不能把顺序弄错。如果我们查询了四个字段的话，则会报错，因为该表加上分区字段也才三个。要注意系统是根据查询字段的位置推断分区名的，而不是字段名称。
show partitions dpartition;
-- partition
-- ct=beijing
-- ct=beijing1
-- insert...select 往表中导入数据时，查询的字段个数必须和目标的字段个数相同，不能多，也不能少,否则会报错。但是如果字段的类型不一致的话，则会使用null值填充，不会报错。而使用load data形式往hive表中装载数据时，则不会检查。如果字段多了则会丢弃，少了则会null值填充。同样如果字段类型不一致，也是使用null值填充。

多个分区字段时，实现半自动分区（部分字段静态分区，注意静态分区字段要在动态前面）

-- 创建一个只有一个字段，两个分区字段的分区表
create table ds_parttion(id int ) 
partitioned by (state string ,ct string );

set hive.exec.dynamici.partition=true;
 set hive.exec.dynamic.partition.mode=nonstrict;
 insert overwrite table ds_parttion
 partition(state='china',ct)  #state分区为静态，ct为动态分区，以查询的city字段为分区名
 select id ,city from  mytest_tmp2_p;

多个分区字段时，全部实现动态分区插入数据

set hive.exec.dynamici.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ds_parttion
 partition(state,ct)
 select id ,country,city from  mytest_tmp2_p; 
-- 注意：字段的个数和顺序不能弄错。

调优参数

set  hive.exec.max.dynamic.partitions.pernode=100 -- （默认100，一般可以设置大一点，比如1000）
     -- 表示每个maper或reducer可以允许创建的最大动态分区个数，默认是100，超出则会报错。

 set hive.exec.max.dynamic.partitions =1000 -- (默认值) 
     -- 表示一个动态分区语句可以创建的最大动态分区个数，超出报错

 set hive.exec.max.created.files =10000-(默认) 全局可以创建的最大文件个数，超出报错。

导出数据

1.insert 
insert :  将一条sql运算的结果，插入到指定的路径  local输出到本地文件系统
		语法： insert overwrite [local] directory '/opt/module/datas/export/student'
			   row format xxxx
               			select * from student;
			   
2. export ：  既能导出数据，还可以导出元数据(表结构)！
			  export会在hdfs的导出目录中，生成数据和元数据！
			  导出的元数据是和RDMS无关！ 
			  如果是分区表，可以选择将分区表的部分分区进行导出！
			  
		语法：  export table 表名 [partiton(分区信息) ] to 'hdfspath'
3.直接用hdfs命令将文件下载下来
4.hiveShell:hive -e 'select * from ttt;'>/opt/xxx/xxx.txt

设置本地模式

HIVE可以通过本地模式在单台机器上吹所有任务，对于小数据集，执行时间可以明显缩短

只适用于reduceTask=1

# 设置HVE在合适的时候自动启动优化
set hive.exec.mode.local.auto=true;
# 当输入数据据量小于这个值的时候采用local,默认是128M  134217728 
set hive.exec.mode.local.auto.inputbytes.max=5000000;
#当输入文件数小于这个数的时候采用local 默认是4
set hive.exec.mode.local.auto.input.files.max=10;

在HIVE CLI模式下

可以直接操作hdfs

hive> dfs -ls /

操作linux

hive>!ls /

bigdata > 02hive

#bigdata #02hive

02常用操作

https://jiajun.xyz/2020/10/13/bigdata/02hive/02常用操作/

作者

Lambda

发布于

2020年10月13日

更新于

2021年8月5日

许可协议

09vim操作上一篇

03数据类型下一篇