Hive的DDL操作

草莓果冻ovo

已于 2023-05-20 17:54:13 修改

阅读量451

点赞数

文章标签： hadoop hive 大数据

于 2023-04-22 20:23:03 首次发布

本文链接：https://blog.csdn.net/m0_63266054/article/details/130309825

版权

MySQL
DDL 数据定义语言--create alter drop
DML 数据操纵语言--insert delete update select
DCL 数据控制语言--grant revoke deny

Hive的DDL操作
~库操作
~表操作

一、库操作
Hive 的数据库相当于关系型数据库中的命名空间，而从本质上来说，Hive 中的数据库就是 HDFS 上的一个目录。
数据库常用的操作有：创建、查看、修改、删除等。
1.创建库
#完整语法：
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
　[COMMENT database_comment]　　//关于数据库的描述
　[LOCATION hdfs_path]　　　　//指定数据库在HDFS上的存储位置
　[WITH DBPROPERTIES (property_name=property_value, ...)];　//指定数据库属性
1）最简单的用法
create database mydb;
--当名为 mydb 的数据库当前不存在时才能创建成功，否则会报错，需要使用下面的用法。
create database if not exists mydb;
2）创建数据库指定存储位置
创建数据库时，数据库的默认存储位置由参数 hive.metastore.warehouse.dir 指定，默认为 /user/hive/warehouse。
需要注意的是，default 数据库没有自己的目录，是直接存放在参数 hive.metastore.warehouse.dir 指定目录下。另外，可以通过指定 LOCATION 选项指定数据库在 HDFS中的存储位置。
create database mydb location '/db/mydb';
3）创建数据库时指定描述信息
create database mydb comment 'this is mydb database';
4）创建数据库时指定键值对信息
create database mydb with dbproperties ('own'='wei', 'day'='20200402');

例：
（1）创建普通的数据库
create database t1;
（2）创建库的时候检查存与否
create database if not exists t1;
（3）创建库的时候带注释
create database if not exists t2 comment 'learning hive';
（4）创建带属性的库
create database if not exists t3 with dbproperties('creator'='hadoop','date'='2018-04-05');
create database if not exists t4 with dbproperties('a'='1','b'='2');

2.查看库：
（1）查看有哪些数据库
show databases;
（2）显示数据库的详细属性信息
desc database 库名;
desc database extended t3;
show databases like 's*';
（3）查看正在使用哪个库
在 hive命令行显示当前数据库两种方式。
a)、可以在hive命令行执行以下语句显示当前数据库：
select current_database();
b)、可以设置hive属性在命令行显示当前数据库：
set hive.cli.print.current.db=true;
注: 这是当前的session窗口有效；永久修改，向hive-site.xml文件添加一下配置：
<property>
<name>hive.cli.print.current.db</name>
<value>true</value>
</property>
（4）查看创建库的详细语句
show create database t3;

3.切换库：
use database;

4.删除库：
默认情况下，hive 不允许删除包含表的数据库，有两种解决办法：
# 手动删除库下所有表，然后删除库
# 使用 cascade 关键字
drop database if exists dbname cascade;
默认情况下就是 restrict drop database if exists myhive ==== drop database if exists myhive restrict
例:
（1）删除不含表的数据库
语法：
drop database 库名;--只能删除空库里面有没有表格
drop database if not exists 库名;
例：show tables in t1;
drop database t1;
show databases;
（2）删除含有表的数据库
语法：drop database 库名 cascade;--删除库非空库
例：create database t3;
use t3;
create table a1(id int);
drop database if exists t3 cascade;

5.修改库:基本不用,一般只使用对扩展属性的修改
语法：
ALTER DATABASE 数据库名 SET DBPROPERTIES (属性名=属性值, ...);
ALTER DATABASE 数据库名 SET OWNER [USER|ROLE] user_or_role;
例如，修改 mydb 的扩展属性。
alter database mydb set dbproperties ('own'='god', 'day'='2020-2-2');

二、表的操作：
1、Hive的数据类型
Hive 的数据类型分为两大类：基本数据类型和集合数据类型。
1）．基本数据类型
基本数据类型包括数值类型、日期/时间型、字符串类型和其他类型等。
（1）数值类型
                   表 3-1   基本数据类型

           数据类型           长度           取值范围
           TINYINT           1 字节       -128 to 127
           SMALLINT       2 字节       -32,768 to 32,767
           INT/INTEGER       4 字节       -231 to 231-1
           BIGINT           8 字节       -263 to 263-1
           FLOAT           4 字节       单精度浮点数，6-7 位有效数字
           DOUBLE           8 字节       双精度浮点数，15 位有效数字
           DOUBLE PRECISION       DOUBLE 的别名，Hive 2.2.0 引入
           DECIMAL                   精确小数，38 位精度的数字
           NUMERIC                   与 DECIMAL 等价，Hive 3.0 引入

（2）日期/时间型
日期/时间型包括 TIMESTAMP、DATE 和 INTERVAL。
TIMESTAMP，支持传统的 UNIX 时间戳数据，可精确到纳秒。
DATE，以 YYYY-MM-DD 格式存放日期数据。日期范围：0000-1-1 到 9999-12-31。
INTERVAL，新增的类型，表示时间间隔。
（3）字符串类型
字符串类型包括 String、Varchar 和 char。
String，字符串，可以用单引号或双引号括起来，如’hive’或”hive”，可存储多达 2GB 字符。
Varchar，指定宽度的字符串，宽度为 1-65535。
char，固定宽度的字符串，最大宽度 255。
（4）其他类型
BOOLEAN，布尔型，true 或 false。
BINARY，字节数组。
2）．集合数据类型
Hive 的集合数据类型包括 STRUCT（结构）、MAP（映射）、ARRAY（数组）等。

       数据类型           释义           示例
   STRUCT       与 C/C++中的结构体类似，可通过“字段名.域名”形式访问数据   addr.street
   MAP           存储键值对数据，可以通过[‘key’]获取 key 对应的值       dedu[‘k1’]
   ARRAY       同类型数据的集合，通过从 0 开始的索引访问数据       sub[0]

示例：
create table emp (
name string,
salary float,
sub array<string>,
dedu map<string, float>,
addr struct<street:string, city:string, state:string, zip:int>
);

2.创建表：
管理表（内部表）删除表时：hive和hdsf上的数据全删
外部表 EXTERNAL 删除表时：hive上的数据全删，hdsf上的数据没有删除
分区表（手动分区、动态分区）PARTITIONED BY （字段名（!!!!用于分区的字段，一定不是表中字段）数据类型 [COMMENT col_comment]）
分桶表 CLUSTERED BY

复制表
查询表

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 EXIST 选项来忽略这个异常
--EXTERNAL 关键字：是否创建外部表，不加是内部表。在建表的同时指定一个指向实际数据的路径（LOCATION）
--LIKE 允许用户复制现有的表结构，但是不复制数据
--COMMENT可以为表与字段增加描述
--PARTITIONED BY 指定分区字段分区字段一定不是建表字段中的某一个或多个，是一个全新的字段
--CLUSTERED BY 指定分桶字段分桶字段一定是建表字段中的某一个字段或多个字段
--INTO num_buckets BUCKETS 指定分桶个数
--SORTED BY 指定排序字段排序字段一定是建表字段中的某一个字段或多个字段指定的是同一个分桶内的排序规则
--ROW FORMAT 指定分隔符
　　DELIMITED [FIELDS|LINES TERMINATED BY--指定列|行分隔符 char] [COLLECTION ITEMS TERMINATED BY char]
　　　　MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
　　　　| SERDE serde_name [WITH SERDEPROPERTIES
　　　　(property_name=property_value, property_name=property_value, ...)]
　　用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。
　　在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。
--STORED AS 指定表中最终数据的存储格式
　　SEQUENCEFILE //序列化文件
　　| TEXTFILE //普通的文本文件格式默认格式
　　| RCFILE　　//行列存储相结合的文件行列结合格式
　　| PARQUET　　//压缩格式
　　| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname //自定义文件格式
　　如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。
--LOCATION指定hive上的表的存储路径，此路径是表在HDFS上的存储路径，若不指定则在默认的存储路径下存储，默认地址：/user/hive/warehouse
例:创建一个数据库stu，创建一个表，包含id,name,sex,age,department字段
create database stu;
use stu;
create table student(id int,name string,sex string,age int,department string);--不执行
1)内部表（管理表）
管理表的整个生命周期由 Hive 控制，在 Hive 中删除表将删除 HDFS 上的数据文件。在实际应用中，当需要 Hive 管理表的生命周期或生成临时表时，应考虑使用管理表。
在 CREATE TABLE 命令中省略 EXTERNAL 即表示创建的是管理表。管理表默认存储在 hive.metastore.warehouse.dir 属性指定的路径下，而在 CREATE TABLE 命令中使用 location 选项可以指定存储位置。

create table student(id int,name string,sex string,age int,department string)
row format delimited fields terminated by ',' lines terminated by '\n';

create external table student(id int,name string,sex string,age int,department string)
row format delimited fields terminated by ',' lines terminated by '\n';

创建内部表:
create table student(id int,name string,sex string,age int,department string) row format delimited fields terminated by ',';
load data local inpath '/home/zkpk/student.txt' into table student;
格式化显示表的详细信息:desc formatted student;
!hadoop fs -put /home/zkpk/student.txt /user/hive/warehouse/stu.db/student;
dfs -put /home/zkpk/student.txt /user/hive/warehouse/stu.db/student;

2)外部表
外部表适用于，先有数据在 HDFS 上，或者位于远程位置上，通过创建外部表和数据存储位置关连起来，从而实现使用 Hive 来管理数据的业务场景。在生产环境中，外部表很常见，这是因为企业的大数据常会被多个项目共享使用。
通过在 CREATE TABLE 语句中指定 EXTERNAL 选项来创建外部表。可以使用 DESCRIBE FORMATTED
命令来查看表是管理表还是外部表，该命令将根据表类型显示 MANAGED_TABLE 或 EXTERNAL_TABLE。

创建外部表:
create external table student_ext
(id int,name string,sex string,age int,department string) row format delimited fields terminated by ',';
格式化显示表的详细信息:desc formatted student_ext;
--验证：将hdfs上的文件student.txt直接上传至student_ext表目录下，查看表内容。
create external table student
(id int,name string,sex string,age int,department string) row format delimited fields terminated by ',';

3)分区表--存的是不同的目录
为了对表进行合理的管理以及提高查询效率，Hive 可以将表组织成“分区”。一个分区实际上就是表下的一个目录，一个表可以在多个维度上进行分区，分区之间的关系就是目录树的关系。
分区表一般在数据量比较大，且有明确的分区字段时使用，这样用分区字段作为查询条件查询效率会比较高。
Hive分区分为静态分区和动态分区。
在CREATE TABLE 语句中，通过 PARTITIONED BY 子句指定分区列的定义。
创建分区表:
create table student_ptn(id int,name string,sex string,age int,department string) partitioned by (city string) row format delimited fields terminated by ',';
分区表在添加数据之前，一定要添加分区
add partition--添加分区
alter table student_ptn add if not exists partition(city='beijing');
alter table student_ptn add if not exists partition(city='wuhan');
格式化显示表的详细信息:desc formatted student_ptn;
--验证

insert into student_ptn
partition (city='beijing')
select id,name,sex,age,department
from student_ext
where name like '王%';

此外，Hive 提供了一个动态分区功能，可以基于查询参数的位置去推断分区的名称，从而动态建立分区。动态分区涉及到以下几个参数：
#启动动态分区功能
set hive.exec.dynamic.partition=true;
#strick 模式：指定分区时至少有一个静态分区
# nonstrick 模式：允许全部分区都是动态分区 set hive.exec.dynamic.partition.mode=nonstrick;
#设置每个节点可以创建的最大动态分区个数，默认是 100，超出则会报错 set hive.exec.max.dynamic.partitions.pernode=100;
#设置最大可以创建的动态分区个数，默认是 1000，超出则会报错 set hive.exec.max.dynamic.partitions =1000;
#全局可以创建的最大文件个数，默认是 10000，超出报错 set hive.exec.max.created.files =10000;

4)分桶表--hadoop中的分区默认分区按照hash分区
Hive 提供一种数据分桶机制，可以将数据分解为更容易管理的若干部分。在表进行分桶时，Hive对分桶字段值进行哈希，哈希值除以桶的个数求余，余数决定了该条记录在哪个桶中，也就是余数相同的在一个桶中。通过分桶，Hive 可以将表的大数据文件散列为多个小文件。
通过数据分桶，一方面可以使数据取样更为高效，尤其是在开发阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。另一方面，使用分桶可以获得更高的查询处理效率，桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。如连接两个在（包含连接列的）相同列上划分了桶的表，可以使 Map 端连接（Map-side join）更为高效地实现。
在CREATE TABLE 语句中，通过 CLUSTERED BY 子句创建分桶表，
tablesample(bucket n out of 10 on 分桶字段)--select 中数据取样的子句
创建分桶表语法:
create external table student_bck(id int,name string,sex string,age int,department string) clustered by (id) sorted by (id asc,name desc) into 4 buckets row format delimited fields terminated by ',';
格式化显示表的详细信息:desc formatted student_bck;
clustered by相当于map端发出的key, id.hashCode()
into 4 buckets相当于指定numreducetasks, id.hashCode() %4

验证分桶：添加数据成功后，查看student_bck表对应的 HDFS 目录，是否被分为4个文件。
insert into student_bck
select *
from student_ext;

在 SELECT 查询中，通过指定 TABLESAMPLE 子句可以对表数据进行取样，该子句的语法如下：
TABLESAMPLE (BUCKET x OUT OF y [ON colname])
下面的查询将返回 tab_buc 表的第 1 个分桶的前 2 行数据。
select *
from student_bck
tablesample (bucket 1 out of 4)
limit 2;

5)复制表--like关键字
在 Hive 中，可以参照已有表的结构创建新表，相应语法如下：
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [数据库名.]表名
LIKE 已存在表或视图名
[LOCATION hdfs 路径];
创建复制表:
create table student_copy like student;
create external table student_copy1 like student;
格式化显示表的详细信息:desc formatted student_copy;
注意：
在复制表的时候，复制出来的表示内部表还是外部表，取决于复制时是否指定了external关键字。
复制表的时候只复制表的结构，不会复制表的数据

6)查询表
与大多数关系型数据库一样，在 Hive 中可以基于查询结果创建新表，相应语法如下：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [数据库名.]表名 [AS select 查询语句];
这一种用法，常被称为 CTAS。该方法创建出的表结构与查询结果集列表相同（列名和数据类型都相同），并且包含查询结果的数据集。需要注意的是，select 查询中不可有无列名的列。

create table as select .... from table;

create table t1
as select * from student;
3.查看表
（1）查看表列表
a)查看当前使用的数据库中有哪些表
show tables;
b)查看非当前使用的数据库中有哪些表
show tables in 库名;
c)查看数据库中以xxx开头的表
show tables like 'student_c*';

（2）查看表的详细信息
a)查看表的信息
desc 表名--显示表的字段信息
desc student;
b)查看表的详细信息（格式不友好）
desc extended 表名
desc extended student;
c)查看表的详细信息（格式友好）
desc formatted 表名--格式化显示表的详细信息
desc formatted student;
c)查看分区信息
show partitions 表名;
show partitions student_ptn;
（3）查看表的详细建表语句
show create table 表名;
show create table student_ptn;

4.修改表
1)修改表名
alter table 旧表名 rename to 新表名;
alter table student rename to new_student;
2)修改表字段信息
a.添加字段
alter table 表名 add columns(字段名数据类型)
所有的新的字段都要指定类型
alter table new_student add columns (score int);
alter table t1 add columns (score int);
b.修改字段定义
修改字段类型
alter table 表名 change 原始字段新字段新字段类型;
注意:在进行修改表字段定义时，注意表字段类型的匹配，只能小类型转大类型。
但在hive1.2.2中无此限制，各种类型之间都可以修改。
alter table new_student change name new_name string;
alter table t1 change score score1 int;
c. 删除一个字段
--不支持
d.替换所有字段
alter table new_student replace columns (id int, name string, address string);
3)修改表分区信息
a.添加分区
#添加静态分区
添加分区的过程中，没有指定分区的存储路径，则存储在默认情况下
　　 ~添加一个
alter table student_ptn add partition(city="chongqing");
添加分区的过程中，指定分区的存储路径
alter table student_ptn add partition(city="shenzhen") location '/user/shenzhen';
　　 ~添加多个
alter table student_ptn add partition(city="chongqing2") partition(city="chongqing3") partition(city="chongqing4");
----------------------------------------------------------------------
#动态分区
先向student_ptn表中插入数据，数据格式如下图
load data local inpath '/home/zkpk/student.txt' into table student_ptn partition(city='beijing');
现在我把这张表的内容直接插入到另一张表student_ptn_age中，并实现sex为动态分区（不指定到底是哪种性别，让系统自己分配决定）
首先创建student_ptn_age并指定分区为age
create table student_ptn_age(id int,name string,sex string,department string) partitioned by (age int);
从student_ptn表中查询数据并插入student_ptn_age表中
insert overwrite table student_ptn_age partition(age)
select id,name,sex,department,age from student_ptn;
---------------------------------------------------------------------------
b.修改分区
//添加分区
ALTER TABLE 表名 ADD [IF NOT EXISTS] PARTITION 分区定义 [LOCATION '存储位置'][, PARTITION 分区定义 [LOCATION '存储位置'], ...]; //重命名分区
ALTER TABLE 表名 PARTITION 分区定义 RENAME TO PARTITION 分区定义;
//修复表分区，使表分区定义反映当前数据的物理分区
MSCK [REPAIR] TABLE 表名 [ADD/DROP/SYNC PARTITIONS]; //删除分区
ALTER TABLE 表名 DROP [IF EXISTS] PARTITION 分区定义[, PARTITION 分区定义, ...] [PURGE];
需要注意的是，MSCK 命令可以实现分区表的分区定义（元数据）与 HDFS 中的目录结构（物理数据）同步。
Hadoop 的 HDFS 有一个回收站（Trash）功能，用户可以通过在 core-site.xml 文件中设置 fs.trash.interval 参数来使用这一功能（默认是关闭的）。在启用 HDFS 的 Trash 后，删除的 HDFS 目录或文件，将被移动到 Trash 的 Current 目录中(/user/${username}/.Trash/current)。而在删除分区语句中的 PURGE 表示，直接清理删除的分区且不放入回收站。

修改分区，一般来说，都是指修改分区的数据存储目录
~在添加分区的时候，直接指定当前分区的数据存储目录
alter table student_ptn add if not exists partition(city='beijing') location '/student_ptn_beijing' partition(city='cc') location 'student_cc';
~修改已经指定好的分区的数据存储目录
alter table student_ptn partition (city='beijing') set location '/student_ptn_beijing';
此时原先的分区文件夹仍存在，但是在往分区添加数据时，只会添加到新的分区目录

c.删除分区
alter table student_ptn drop partition (city='beijing');

5.删除表
1)删除表:
DROP TABLE [IF EXISTS] 表名 [PURGE];
对于管理表，DROP TABLE 将删除表的元数据和 HDFS 中的物理数据。
对于外部表，仅删除表的元数据，HDFS 中的物理数据不受影响。该语句中的 PURGE 表示，对于管理表将直接清理物理数据且不放入 HDFS 的回收站。
drop table if exists 表名;
drop table new_student;
2)截断表，清空表:
TRUNCATE [TABLE] 表名 [PARTITION 分区定义];
TRUNCATE TABLE 语句可以截断指定表或分区，即删除表或分区的所有数据，保留元数据定义。
truncate table 表名;
--仅仅是清空表中的数据，表结构依然保留
truncate table student_ptn;

注意:
删除表时，hdfs上的表数据和元数据表中表数据是否同时删除取决于表的类型（内部表、外部表）
内部表:删除表时，该表的元数据和hdfs上的表数据同时删除；
外部表:删除表时，该表的元数据被删除，但hdfs上的该表数据不会被删除。
故：hdfs上原始存放的数据（公共数据）加载至hive时，一定建立外部表

其他辅助命令：
查看分区表：--一定针对分区表查看
show partitions 表名;
show partitions 表名 partition(city='beijing');