Hive数据操作

目录

 

Hive数据操作

DDL数据定义

数据库操作

数据表操作

DML数据操作

数据导入

数据导出

---

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;