本文目录
DDL (Data Definition Language) 数据定义语言。DDL主要是用在定义
或改变
表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用。建库建表并不会经常建,所以 DML 比 DDL 更重要。
1.创建数据库
Ⅰ.建库语句
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
字段介绍: ([ ] 中括号中内容为创建库时的可选项)
1.COMMENT:数据库描述
2.LOCATION:指定数据库存放在 HDFS 的目录,默认存储在 hive 所指定的地址 /user/hive/warehouse/目录下,HDFS中数据库通常统一为 *.db。比如创建了一个 test库,在HDFS中默认地址为:/user/hive/warehouse/test.db
3.WITH DBPROPERTIES:DB数据库属性,设置数据库属性信息,以使用 key=value 格式添加,比如:创建时间、创建人、修改人 等信息。不常用
注意:默认创建的库,存储在 Hive 默认路径下,如果通过 LOCATION 属性修改库所在的位置。如果指定库存储在 HDFS 根目录下。建议是
location '/库名.db'
,而不是location '/'
。此处存储在根目录下,如果不指定库名.db
,那么在 HDFS 根目录下是看不到这个库名的文件夹的,如果在该库中创建表,以表名命名的文件夹会直接在 根目录下,导致数据比较混乱,一般不建议这么干!!!
Ⅱ.查询数据库
先创建一个 testdb 数据库
建库语句:create database if not exists testdb;
1.查看已有的数据库
show databases; (查询所有的数据库)
show databaseslike 'test*';
(过滤test开头的数据库)
2.使用数据库
use testdb;
3.显示数据库信息
desc database testdb; (显示数据库基本信息,不包括dbproperties内容)
desc database extended testdb; (显示数据库详细信息,包括dbproperties内容)
Ⅲ.修改数据库dbproperties属性
用户可以使用 alter database 库名
命令,为某个数据库的 DBPROPERTIES
设置键-值对属性值,来描述这个数据库的属性信息。(数据库的其他元数据信息都是不可更改的,包括数据库名和数据库所在的目录位置。hive 暂不支持数据库重命名操作,仅支持修改dbproperties和数据库的用户或权限;)
Ⅲ.删除数据库
1.删除空数据库
drop database testdb;
drop database if exists testdb; (如果删除的数据库不确定存在与否,最好采用 if exists 判断一下数据库是否存在)
2.删除非空数据库,采用 cascade 命令强制删除
drop database testdb; cascade;
注意:在手工操作删除库后,MySQL元数据中的元数据信息会被删除,HDFS中相对应的文件夹也会被删除。
2.创建表
Ⅰ.建表语句
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]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
[LIKE table_name]
字段介绍:([ ] 中括号中内容为创建表时的可选项)
1.EXTERNAL:
external 关键字,可以允许用户创建一个外部表,在建表的同时可以指定一个指向实际数据的路径(LOCATION),在删除表的时候,内部表的元数据和HDFS数据会被一起删除;而外部表只删除元数据,不删除HDFS中实际的数据。(Hive中的表,分为外部表和内部表)
2.COMMENT col_comment:
为表中的每一列属性字段备注,即:列备注
3.COMMENT table_comment:
为表添加备注,即:表备注
4.PARTITIONED BY:
创建分区表,请参考:分区表介绍
5.CLUSTERED BY:
创建分桶表,请参考:分桶表介绍
6.SORTED BY (col_name [ASC|DESC], …)] INTO num_buckets BUCKETS:
对桶中的一个或多个列另外排序,这个也和分桶表有关系,分桶表会指定按照哪个字段进行分桶,以及分几个桶(INTO num_buckets BUCKETS),不常用。在 分桶表中有介绍。请参考:分桶表介绍
7.ROW FORMAT:
用于定义行的格式,如:ROW FORMAT DELIMITED [FIELDS 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。如果没有指定 ROWFORMAT 或者 ROW FORMAT DELIMITED,将会使用自带的 SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的 SerDe,Hive 通过 SerDe 确定表的具体的列的数据。
SerDe 是 Serialize/Deserilize 的简称, hive 使用 Serde 进行行对象的序列与反序列化。
8.STORED AS file_format:
指定存储文件类型。file_format 就类似于 text,lzo,snappy 压缩方式,就是文件格式,按什么存储。Hive对应的是MR,MR可以使用压缩,那么Hive就可以指定压缩格式。默认的是text文本文件
常用的存储文件类型:SEQUENCEFILE
(二进制序列文件)、TEXTFILE
(文本)、ORCFILE
(列式存储格式文件) 如果文件数据是纯文本,可以使用STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCEFILE
7.LOCATION :
指定表在 HDFS 上的存储位置。
8.TBLPROPERTIES :
对表加一些属性,类似于DBPROPERTIES
,对表加一些属性。
9.AS select_statement:
select_statement 意为 select 筛选语句。Hive 建表时,可以根据另外一张表查询出来的结果来建表。比如A表有10个字段,现在我需要建个表,只要5个字段,但是5个字段都是A表中的,可以根据create table xx as select id,name,age,address,mobile from user
; 用这种方式建表,通过查询的方式建表,同时还附带查询出来的数据哦!!!
10.LIKE table_name:
LIKE 允许用户复制现有的表结构,但是不复制数据。
eg:create table test1 like test;
Ⅱ.内部表 & 外部表
Hive 中的表分为内部表
和外部表
。内部表又称为管理表(MANAGED_TABLE
),没有添加 EXTERNAL 关键字的表都是内部表。真正的类型应该叫做管理表
1.内部表(管理表)和外部表区别
- 管理表:删除时,元数据和 HDFS 数据都会被删除;
- 外部表:删除时,只有元数据会被删除,HDFS 数据不会被删除
2.何时用内部表(管理表)?何时用外部表?
由于管理表在删除时,会同时将HDFS数据一并删除,所以不适合和其他工具(Spark、Flink、其他系统等)共享使用数据。如果数据除了 Hive 使用,还有其他系统使用,建议建成外部表。内部表利用场景相对较少,只有中间表、临时表等会用到内部表,其他表都会用外部表。
外部表就更安全一些,防止误操作。表删了,数据还在,我们就可以通过建表语句的方式,创建一个元数据,然后 Hive 就又能操作数据,然后再去查询,就又能找到了。
3.内部表和外部表的转换
如果建表时忘记指定,写入一段时间数据后发现了问题,本应该是外部表,结果建的却是管理表,该怎么办呢?这种情况下,我们也可以实现内部表和外部表之间的相互转换,省去了建表、拷贝数据等繁琐操作。
1.查询表的类型
可通过命令查询,也可以直接在元数据库中的TBLS表中查看。命令如下:
命令:
hive (default)> desc formatted test;
Table Type: MANAGED_TABLE
2.管理表 & 外部表转换
1.管理表修改为外部表:alter table test set tblproperties('EXTERNAL'='TRUE');
2.外部表修改为管理表:alter table test set tblproperties('EXTERNAL'='FALSE');
注意:('EXTERNAL'='TRUE')
和('EXTERNAL'='FALSE')
为固定写法,区分大小写!
Ⅲ.show 查看已有表的建表语句
在Hive中建表,我们可以通过一个简单的语句建表,比如create table test(id string);
,我们就可以创建一个 test 表,非常的简单,但是其他需要我们定义的项,我们没有定义则默认使用的都是默认值。
可以使用命令show create table 表名;
来查看默认的建表语句。如果没指定,使用的都是默认配置。
Ⅳ.修改表
1.重命名
语法:
ALTER TABLE table_name RENAME TO new_table_name;
2.增加、修改、删除分区
创建分区表,请参考:分区表介绍
3.增加、修改、替换列信息
修改列信息,包括:1.修改列名 2.修改列类型
1.更新列
语法:
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
注意:
在修改列类型时,①int 修改为 String 是允许的;②String 修改为 int 如果 String字段为汉字等,修改提示失败,如果修改成功,查询出来的就有NULL了,所以修改会失败;③如果只需要改列名,而不需要改类型,也需要根据语法,将原来的类型加上
2.增加列 & 替换列
语法:
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], …)
注意:
ADD 是代表新增一字段,字段位置在所有列后面(partition分区列之前),
REPLACE 则是表示替换表中的所有字段。
更新列使用的 CHANGE ,也可以看做是一种替换,CHANGE 是指定列名的方式修改,只对当前一个指定列生效;(即:如果表中有id和name两个字段,使用 change 将 name 字段 修改为 name1字段,则该表中有 id 和 name1 共2个字段了)
替换列使用的 REPLACE,是以表为维度,对整个表生效,所有字段全部改掉。(即:如果表中有id和name两个字段,使用 replace 替换为 address字段,则该表中就只有一个address字段了)
3.删除列
Hive 并没有提供删除列语法支持,那么如何删除列呢??
可以使用 REPLACE 来实现。比如表 test 中有 id(int)、name(string)、address(string) 三列,需要删除 address 列,使用 REPLACE 即可。语句:alter table test REPLACE columns (id int,name string);
。
在修改表时,操作的都是元数据,HDFS文件的数据是不会变化的。如果由2个字段再改为3个字段,address列依然还是会有数据的。
下一篇:Hive DML数据操纵语言
博主写作不易,加个关注呗
求关注、求点赞,加个关注不迷路 ヾ(◍°∇°◍)ノ゙
我不能保证所写的内容都正确,但是可以保证不复制、不粘贴。保证每一句话、每一行代码都是亲手敲过的,错误也请指出,望轻喷 Thanks♪(・ω・)ノ