本文详细介绍了Hive的各种操作，包括创建表的三种方式（普通、CTAS、LIKE）、数据库和表的DDL操作，如查看、描述、删除、修改等。此外，还讲解了数据加载（LOAD DATA、INSERT）和查询操作，强调了分区表、分桶表的创建和管理，以及动态分区插入的注意事项。内容涵盖了Hive的常用命令和操作技巧，对于理解和使用Hive进行大数据处理非常有帮助。
1、创建表的方式
1、普通方式:(默认字段分割符是ascii码的控制符\001,默认存储方式是textfile)；
语法:create [external] table [if not exists] table_name( col_name data_type comment col_comment,...)
[comment table_comment] -- 表的描述
[partitioned by (col_name data_type,...)] -- 分区表(分区字段一定不要在表字段中出现)
[
clustered by(col_name) -- 指定分桶字段（分桶字段必须是表字段中的一部分）
[sorted by(col_name [ASC|DESC]),...] -- 是否分桶有序，如有序，则按照哪个字段排序
into num_buckets buckets -- 指定分桶个数
] 分桶
[row format delimited fields terminated by '\t' lines terminated by '\n'] -- 行列分隔符
[stored as file_format] -- file_format（textfile：普通的文本文件格式(默认)；sequenceFIle:序列化文件；...）
[location hdfs_path] -- 创建表(不管内部表或外部表)的时候可以指定表的路径，不管是内部表还是外部 表，都可以指定hdfs的存储路径；最佳实战:如果一份数据已经存储在hdfs上，并且要被多个用户或客户端使用，最好创建外部表，反之创建内部表;如果不指定则存储在默认的目录默认的仓库中
[tblproperties (name=value)]
2、CTAS(CREATE TABLE AS SELECT)；
create table page_view_ctas stored by rcfile
as
select * from page_view sort by viewTime,userid;
注意:创建的表不能是外部表、分区表、桶表；sql会执行MR作业。
3、LIKE（与ctas的区别，like只是拷贝表结构不复制表的数据，ctas拷贝结构和数据执行mr）；
create table page_view_like like page_view;
2、例子
a、数据库和表相关操作(DDL)
1、库：
show databases; # 查看某个数据库
show databases like 'hive*';
use 数据库;# 进入某个数据库
select current_database(); # 查看当前使用的库
desc database [extended] 数据库;# 查询库的详细信息
desc database db_name;# 查询库的详细信息
drop database 数据库; # 删除数据库(前提:里面有表时删除不掉的)
drop database 数据库 restrict；//默认方式(不急连删除)
drop database 数据库 cascade；//级联删除一个非空的数据库
2、表：
show tables;# 展示所有表
desc table_name;# 显示表结构
desc extended table_name;# 显示表结构
desc formatted table_name;
show create table table_name; # 显示创建表的结构
show partitions 表名; show partitions 表名 partition(city="beijing"); # 查看hive表的分区
show functions; 查看hive函数列表
desc function function_name; 查看hive函数使用
desc function extended function_name; 查看hive函数使用
truncate table table_name; # 清空表数据
show tables in hive; # 查看库中表
drop table table_name; #删除表
3、修改表:
修改表名: alter table page_view rename to page_view_new;
修改表字段定义:
添加一个字段：alter table page_view add columns(sex string, age int);
修改一个字段的定义:alter table page_view change age new_age string;
删除一个字段: 不支持
替换所有字段: atler table page_view replace columns(view_time string, user_id int);-- 如果有需要要去更加某一表的所有字段的定义。那么一定要注意类型是否匹配。如果是hive1.2.2某一任何阻碍，不管几列都，不管任何数据类型都可以替换。
4、修改分区信息:
添加分区:
静态分区:
alter table page_view_partition add partition(city="hubei");
alter table page_view_partition add partition(city="hubei") partition(city="shenzheng") ;
动态分区:
修改分区:修改分区一般来说都是指修改分区的数据存储目录
//在添加分区的时候，直接指定当前分区的数据存储目录
alter table page_view_partition add if not exists partition(city="beijing") location '/page_view'
partition(city="cc") location '/page_view_cc';
//修改已经指定好的分区的数据存储目录(修改后没有创建，加载数据的时候会触发创建，原来的目录还有但是已经不存储数据)
alter table page_view_partition partition(city="beijing") set location '/page_view_beijing';
删除分区: alter table page_view_partition drop partition(city="beijing")
b、创建数据和表相关操作
1、创建数据库db
create database hive comment 'hive测试数据库' with dbproperties('creator'='root','create_time'='2019-03-03');
查看mysql的metastore数据库信息:1、dbs:数据库主要信息；2、database_params：数据库参数信息；
2、创建一个内部表(没有external 关键字都是内部表)
create table page_view(view_time string, user_id int) row format delimited fields terminated by '\t' ;
加载数据:
load data local inpath '/usr/local/big_data/hive/page_view.txt' into table page_view;
3、创建一个外部表
create external table page_view_external(view_time string, user_id int) row format delimited fields terminated by '\t' location '/hive/page_view';
4、创建分区表(可内部/外部表，主要使用外部表，在hdfs中表现为table目录下的子目录，分区字段一定不要在表字段中出现)
create external table page_view_partition(view_time string, user_id int) partitioned by(city string)
row format delimited fields terminated by '\t' location '/hive/page_view_partition';
创建完后/hive/page_view_partition目录下什么都没有；
添加分区:
alter table page_view_partition add partition(city="beijing")；
alter table page_view_partition add partition(city="tianjin")；
如果某一张表时分区表，那么每个分区的定义，其实就表现了这张表的数据存储目录下的一个子目录；如果是分区表，那么数据文件一定要存储在某一个分区中，而不能直接存储在表中。
加载数据（静态分区）:
load data local inpath '/usr/local/big_data/hive/page_view.txt' into table page_view_partition partition(city ='china');
5、分桶表
create table page_view_bucket(view_time string, user_id int)
clustered by(user_id) sorted by(view_time asc) into 3 buckets
row format delimited fields terminated by '\t' ;
6、使用CTAS创建表(作用: 从一个查询sql的结果来创建一个表进行存储),执行MR作业。
在实际情况中，表的输出结果可能太多，不适于显示在控制台，这时候，将hive的查询输出结果直接存在一个新的表中是非常方便的，我们称这种情况为CTAS。注意:CTAS操作是原子的，因此如果select查询由于某种原因而失败，新表是不会创建的。
设置本地模式： set hive.exec.mode.local.auto=true; 会快一些
create table page_view_ctas as select * from page_view where user_id < 5;
7、复制表结构
create table page_view_copy like page_view;只复制表结构没有数据，table前没有external 是内部表
c、导入数据load和insert(DML)
1、导入数据 set hive.exec.mode.local.auto=true 设置成本地模式快些
load方式装载数据(hive读模式不是写模式，导入数据的时候，完全不会做数据校验的)
//从linux本地导入数据到表中，去掉local就是从hdfs导,overwrite覆盖数据
load data local inpath '/usr/local/big_data/hive/page_view.txt' [overwrite] into table page_view;
如果数据本身就已经存储在hdfs上了，那么还创建一个内部表。合适吗？
因为假如从hdfs数据导入到hive表的话，那么原来的hdfs目录中的数据文件就没有了。 由于删除内部表的时候，会删除数据。
hive的权限控制(HDFS)
最暴力的一种上传数据到hive表的方式：hadoop dfs -put /user/hive/warehouse/hive.db/page_view
insert into table page_view(view_time,user_id) values('2019-03-06','3'); -- 这句话的原理:首先创建一张临时表来保存insert语句的结果，然后把这张临时表的数据文件移动到指定的表table_name中；
insert into table page_view select * from page_view_a where user_id < 10; -- 单重插入
需求：
从page_view 表中，把数据分成三类，插入到page_view_partition这张表的三个分区中；
导入数据到分区表中的分区时，这个分区可以不存在，会自动创建。
insert into table page_view_partition partition(age=10) select view_time,user_id from page_view where age<= 10;
insert into table page_view_partition partition(age=20) select view_time,user_id from page_view where age<= 20;
使用多重插入来降低整体任务的复杂度，主要减少就是原表的数据扫描次数
from page_view
insert into table page_view_partition partition(age=10) select view_time,user_id where age<= 10
insert into table page_view_partition partition(age=20) select view_time,user_id where age<= 20 ;
在hive的使用中，分区的普通字段没有任何区别，是完全一样的。只是在定义的时候有区别。
如果真实的需求要求每一个年龄一个分区，那怎么做？
动态分区插入:
create table page_view_partition_1(view_time string, user_id int)
partitioned by(city string)
row format delimited fields terminated by '\t' ;
实现动态分区插入的sql编写:
insert into table page_view_partition_1 partition(city) select view_time ,city ,user_id from page_view;-- 错误
insert into table page_view_partition_1 partition(city) select view_time ,user_id,city from page_view;
查分区数据: select * from page_view_partition_1 where city = "beijing";
如果一张分区表有多个分区字段，那么在进行动态分区插入的时候，一定要有一列是静态分区，
create table page_view_partition_2(view_time string)
partitioned by(city string,user_id int)
row format delimited fields terminated by '\t' ;
insert into table page_view_partition_1 partition(city="beijing", user_id) select view_time ,city,user_id from page_view;
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
注意:动态分区默认情况下是开启的，但是却以默认strict模式执行的，在这种模式下要求至少有一列分区字段是静态的。这有助于阻止因设计错误导致查询产生大量的分区。但是此处我们不需要静态分区字段，故将其设为nonstrict。
如果往分区表中插入数据，千万不要使用load方式，除非在非常确定的情况。hadoop dfs 也不行。
2、insert方式导出数据
单模式导出本地/HDFS: insert overwrite local directory "/usr/local/big_data/hive/page_view/page_view_18" select * from page_view;-- 注意目录不要覆盖了原来的东西.
注意:数据写入到文件系统时进行文本序列化，且每列用^A来区分，\n为换行符。用more命令查看时不容易看出分隔符，可以使用:sed -e 's/\x01/\t/g' filename来查看。more 000000_0很难看
3、查询
1、select * from table;
2、select count(disinct uid) from table;
3、支持select、union all、join(left、right、full join)、like、where、having、各种聚合函数、支持json解析。
4、UDF(User Defined function用户自定义函数):普通的单行函数(输入参数1输出1)
UDAF(多对一函数，输入n，输出1)
UDTF(一对多函数，输入1，输出n)
5、不支持update和delete;
6、hive虽然支持in/exists（老版本不支持的），但是hive推荐使用semi join(半连接)的方式来代替实现，而且效率更高。
select * from page_view where user_id in(18,19);
select * from page_view where user_id in(select user_id from page_view where user_id = 18 or user_id = 19);
7、支持case....when...
语法结构:
select [all | distinct] select_condition
from table_name a
[join table_other b on a.id = b.id]
[where where_condition]
[group by col_list [having condition]]
[cluster by col_list | [distribute by col_list] [sort by col_list | order by col_list] ]
[limit number];
说明:
1、select_condition查询字段
2、table_name表名
3、order by(字段)全局排序，因此只有一个reducer，只有一个reduce task的结果，比如文件000000_0，会导致当输入规模比较大时，需要较长的计算时间。
4、sort by(字段) 局部排序,不是全局排序，其在数据进入reduce前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.task>1，则sort by只保证每个reducer的输出有序，不保证全局有序。那万一，我要对我的所有处理结果进行一个综合排序，而且数据量又非常大，那么怎么解决？我们不适用order by进行全数据排序，我们使用sort by对数据进行局部排序完了之后，再对所有的局部排序结果做一个归并排序。
5、distribute by(字段) 根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
6、cluster by(字段) 除了具有distribute by功能外，还会对该字段进行排序。
------------------------------------------------------------------------------------------------------------------------------------------------------------------
排序说明:
cluster by col_list :既分桶又局部排序太麻烦
cluster by user_id = distribute by user_id sort by user_id ;
distribute by view_time sort by view_time, user_id == cluster by view_time sort by user_id;-- 不能这样写 cluster by和sort by不能同时使用，只能采用前面的方式
distribute by col_list ：分桶操作
select * from page_view distribute by user_id sort by user_id desc; -- 既分桶又局部排序
在分桶查询中，分桶的个数就是reduceTask的个数，sort by用来进行局部排序，每个桶中的数据是有序的。
sort by col_list：（局部排序）
首先一个sql语句应该要被看做一个MapReduce程序。所谓的局部排序，就是指如果有多个reducetask执行的话，那么最终每个reduceTask的结果是有序的。
如果只有一个reduceTask那么，sort by = order by；
order by col_list
------------------------------------------------------------------------------------------------------------------------------------------------------------------
操作:select * from page_view sort by user_id desc;
观察:
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=<number> 256000000 256M 每个MapReduce能够加载多少数据
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=<number> 1009 最大的reduces个数
In order to set a constant number of reducers:
set mapreduce.job.reduces=<number> -1没有设置默认1 设置reduceTask数量， set mapreduce.job.reduces=3，为了设置当前reduceTask数量为3；分发的机制完全随机。
------------------------------------------------------------------------------------------------------------------------------------------------------------------
扫一扫
2048
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
