目录
Hive数据操作
DDL数据定义
数据库模式定义DDL(Data Definition Language),是用于描述数据库中要存储的现实世界实体的语言。
数据库操作
(1)创建数据库,数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db,使用 if not exists来判断数据库是否已经存在。
create database if not exists db_name;
也可以在创建数据库时,指定数据库在HDFS上的存储位置
create database db_name location '/yourpath/db_name';
(2)修改数据库
Hive数据库中的数据是无法修改的,但是我们可以通过ALTER DATABASE命令来修改数据库的属性值。
//比如说我们可以设置数据库的创建时间。
alter database db_name set dbproperties('createtime'='20190920');
//修改数据库的用户
alter database db_name set owner anotherowner;
//修改数据库在HDFS上的存储位置
alter database db_name set location mypath;
(3)查询数据库
显示所有数据库
show databases;
过滤显示查询的数据库
//通过数据库名称过滤
show databases like 'db_name*';
显示数据库信息
//详细信息就加上extended
desc database extended db_hive;
(4)切换和删除数据库
// 使用db_name数据库
use db_name;
删除数据库
//删除空数据库,如果不知道要删除的数据库是否存在,最好加上if exists判断数据库是否存在
drop database if exists db_name;
//如果数据库不为空,可以采用cascade命令,强制删除
drop database db_hive cascade;
数据表操作
(1)创建表
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]
-
CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。
-
EXTERNAL关键字可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION),Hive创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径,不对数据的位置做任何改变。在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
-
COMMENT:为表和列添加注释。
-
PARTITIONED BY创建分区表。
-
CLUSTERED BY创建分桶表。
-
SORTED BY不常用。
-
ROW FORMAT,设置读取数据时的分隔符,如没有自定义的话会使用自带的SerDe。
-
STORED AS指定存储文件类型:常用的存储文件类型:SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、RCFILE(列式存储格式文件,如果数据需要压缩)。
-
LOCATION :指定表在HDFS上的存储位置。
-
LIKE允许用户复制现有的表结构,但是不复制数据。
(2)管理表(内部表)的操作
Hive默认创建的表都是管理表(内部表)。在内部表中,Hive会控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir所定义的目录的子目录下。当我们删除一个管理表时,Hive也会删除这个表中的数据。
//创建普通表
create table if not exists tb_name(
id int, sex string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/yourpath/tb_name';
//根据查询结果创建表(查询的结果会添加到新创建的表中)
create table if not exists tb_name2
as select id, sex from tb_name1;
//根据已经存在的表结构创建表
create table if not exists tb_name3 like tb_name;
//查询表的类型
desc formatted tb_name;
(3)分区表
分区表实际上就是对应一个HDFS文件系统上的独立的文件夹,该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录,把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区,这样的查询效率会提高很多。
此时分区的数据其实都是存储在同一个分区表中,只是在HDFS上分属于各自的文件夹。
//创建分区表
create table tb_partition(
tb_no int, dname string, loc string
)
partitioned by (month string)
row format delimited fields terminated by '\t';
//加载数据到分区表
load data local inpath '/yourpath/data.txt' into table db_name.tb_partition partition(month='201907');
load data local inpath '/yourpath/data.txt' into table db_name.tb_partition partition(month='201908');
load data local inpath '/yourpath/data.txt' into table db_name.tb_partition partition(month='201909');
//查询分区表数据
/// 单分区查询
select * from tb_partition where month='201907';
///多分区联合查询
select * from tb_partition where month='201908'
union
select * from tb_partition where month='201907'
union
select * from tb_partition where month='201909';
//增加分区
alter table tb_partition add partition(month='201905') partition(month='201904');
//删除分区
alter table tb_partition drop partition (month='201805'), partition (month='201806');
//查看分区表有多少个分区
show partitions tb_partition;
//查看分区表结构
desc formatted tb_partition;
当然,如果需要分区的属性有多个,我们也可以创建多级分区表。
//创建二级分区表
create table tb_partition2(
tb_no int, dname string, loc string
)
partitioned by (month string, day string)
row format delimited fields terminated by '\t';
//加载数据到二级分区表
load data local inpath '/yourpath/data.txt' into table db_name.tb_partition2 partition(month='201909', day='13');
//查询数据
select * from tb_partition2 where month='201909' and day='13';
(4)外部表
对于外部表,只在表中存储数据的存储位置,所以并非认为Hive完全拥有这份数据。删除该表并不会删除掉这份数据,不过描述表的元数据信息会被删除掉。
//创建外部表
create external table if not exists tb_name(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';
//向外部表中导入数据
load data local inpath '/yourpath/data.txt' into table db_name.tb_name;
//查看表格式化数据
desc formatted tb_name;
(5)数据表的其他操作
//重命名表
alter table tb_name2 rename to tb_name3;
//更新列信息
alter table tb_name change[column] col_name1 col_name2 column_type[comment col] [first|after column_name]
//增加或替换列(增加是在所有列后面增加,替换是替换掉表中所有的列)
alter table tb_name add|replace columns (col_name,data_type,...)
//删除表
drop table tb_name;
DML数据操作
数据操纵语言(Data Manipulation Language, DML)是SQL语言中,负责对数据库对象运行数据访问工作的指令集,以INSERT、UPDATE、DELETE三种指令为核心,分别代表插入、更新与删除,是开发以数据为中心的应用程序必定会使用到的指令。
数据导入
(1)向表中Load数据
load data [local] inpath '/yourpath/data.txt' [overwrite] into table tb_name [partition (partcol1=val1,…)];
- load data:表示加载数据
- local:表示从本地加载数据到hive表;否则从HDFS加载数据到hive表
- inpath:表示加载数据的路径
- overwrite:表示覆盖表中已有数据,否则表示追加
- into table:表示加载到哪张表
- tb_name:表示具体的表名
- partition:表示上传到指定分区
(2)向表中Insert数据
//insert之前该表必须已经被创建
insert into table tb_name partition(month='201907') values(1,'wangwu');
(3)通过(as select)加载数据
//这种方式不能创建外部表
create table if not exists tb_name as select id, name from tb_name2;
(4)向表中import数据
//该操作常用于copy操作,即先export,再import达到复制数据的目的
import table tb_namefrom '/yourpath/data';
数据导出
(1)insert导出到本地
//导出的数据默认列与列之间不存在间隔,可以人为设置间隔
insert overwrite local directory '/yourpath/data' row format delimited fields terminated by
'\t';
(2)使用Hadoop命令导出到本地
hdfs dfs -get /hdfspath/data /yourpath/data.txt;
(3)Hive Shell导出到本地
hive -e 'select * from db_name.tb_name;' > /yourpath/data.txt;
(4)Export到HDFS
export table db_name.tb_name to '/hdfspath/data';
(5)清除管理表(内部表)中的数据
truncate table tb_name;