DDL（data definition language）数据库定义语言：**库表结构**
DDL主要是用在定义或改变库表的结构，数据类型，表之间的链接和约束等初始化工作上，比如说：CREATE、ALTER、DROP等。
一、Database
1.1 增
语法：CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name --DATABASE|SCHEMA 是等价的[COMMENT database_comment] --数据库注释[LOCATION hdfs_path] --存储在 HDFS 上的位置[WITH DBPROPERTIES (property_name=property_value, ...)]; --指定额外属性示例：CREATE DATABASE IF NOT EXISTS hive_testCOMMENT 'hive database for test'LOCATION '/user/hive';-- 不指定location，则使用${hive.metastore.warehouse.dir}CREATE DATABASE IF NOT EXISTS hive_testCOMMENT 'hive database for test'WITH DBPROPERTIES ('create'='heibaiying');
1.2 删
语法：
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
示例：
DROP DATABASE IF EXISTS hive_test;
DROP DATABASE IF EXISTS hive_test CASCADE; (谨慎操作)
注：默认行为是 RESTRICT，如果数据库中存在表则删除失败。要想删除库及其中的表，可以使用 CASCADE 级联删除。
1.3 改
用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。
alter database db_hive set dbproperties('createtime'='20170830');
alter database myhive set dbproperties(edited-by'='Joe');
1.4 查
语法：
DESCRIBE|Desc DATABASE [EXTENDED] db_name; --EXTENDED 是否显示额外属性
示例:
DESC DATABASE EXTENDED hive_test;
语法：
SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];
--查看所有
show databases;
-- 使用正则
SHOW DATABASES like 'hive*';
-- LIKE 子句允许使用正则表达式进行过滤，但是 SHOW 语句当中的 LIKE 子句只支持 *（通配符）和 |（条件或）两个符号。例如 employees，emp *，emp * | * ees，所有这些都将匹配名为 employees 的数据库。
1.5 其他
USE database_name;
二、创建表
2.1 建表语法
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name --表名
[(col_name data_type [COMMENT col_comment],
... [constraint_specification])] --列名 列数据类型
[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
] --分桶表分桶规则
[SKEWED BY (col_name, col_name, ...) ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
] --指定倾斜列和值
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
] -- 指定行分隔符、存储文件格式或采用自定义存储格式
[LOCATION hdfs_path] -- 指定表的存储位置
[TBLPROPERTIES (property_name=property_value, ...)] --指定表的属性
[AS select_statement]; --从查询结果创建表
2.2 内部表
CREATE TABLE emp(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
2.3 外部表
CREATE EXTERNAL TABLE emp_external(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOCATION '/hive/emp_external';
管理表与外部表的区别
外部表：
a) 共享数据
b) 删除表时，仅删除表的元数据，不删除对应数据。
管理表：
a)删除表时，表的元数据和对应数据都删除。
删除外部表异常(Table metadata not deleted)
问题原因：这是因为 Hive 在删除表的时候会检查当前用户是否对数据目录有写权限。一般外部共享给咱们的数据只有读权限，并没有写权限。
解决方法：
1.修改hive源码；
2.修改外部表的数据目录；
alter table order_info set location 'hdfs://iteblogcluster/user/iteblog/tmp/';
2.4 分区表
CREATE EXTERNAL TABLE emp_partition(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2)
)
PARTITIONED BY (deptno INT) -- 按照部门编号进行分区
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOCATION '/hive/emp_partition';
建AVRO格式数据表
Avro（读音类似于[ævrə]）是Hadoop的一个子项目，由Hadoop的创始人Doug Cutting牵头开发。Avro是一个数据序列化系统，设计用于支持大批量数据交换的应用。它的主要特点有：支持二进制序列化方式，可以便捷，快速地处理大量数据；动态语言友好，Avro提供的机制使动态语言可以方便地处理Avro数据。
CREATE EXTERNAL TABLE dw_marketing.credit_card_user
PARTITIONED BY (p_product STRING,p_date STRING)
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
LOCATION
'hdfs://nameservice/data/hive/dw_marketing.db/credit_card/user/'
TBLPROPERTIES (
'avro.schema.literal'=
'{
"namespace": "dw.syncer.model",
"type": "record",
"name": "CreditCardUser",
"fields": [
{"name": "phone_number", "type": "string", "doc": "手机号码"},
{"name": "applied_time", "type": "long", "doc": "时间"},
{"name": "bank", "type": "string", "doc": "银行"},
{"name": "uid", "type": ["null", "string"], "default": null, "doc": "密文"},
{"name": "eid", "type": ["null", "string"], "default": null, "doc": "外部密文"}
]
}'
);
补充：
1.Hive分区修复命令MSCK
命令: MSCK REPAIR TABLE table_name;
注意：为了让 MSCK 命令工作，分区的目录名必须是 /partition_name=partition_value/结构的，否则将无法添加分区。这时候你必须使用add partition命令了。
2.5 AS建表
支持从查询语句的结果创建表：
CREATE TABLE emp_copy AS SELECT * FROM emp WHERE deptno='20';
2.6 LIKE建表
语法：
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name --创建表表名
LIKE existing_table_or_view_name --被复制表的表名
[LOCATION hdfs_path]; --存储位置
示例：
CREATE TEMPORARY EXTERNAL TABLE IF NOT EXISTS emp_co LIKE emp；
2.7 分桶表
CREATE EXTERNAL TABLE emp_bucket(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT)
CLUSTERED BY(empno) SORTED BY(empno ASC) INTO 4 BUCKETS --按照员工编号散列到四个 bucket 中
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOCATION '/hive/emp_bucket';
2.8 倾斜表
通过指定一个或者多个列经常出现的值（严重偏斜），Hive 会自动将涉及到这些值的数据拆分为单独的文件。在查询时，如果涉及到倾斜值，它就直接从独立文件中获取数据，而不是扫描所有文件，这使得性能得到提升。
CREATE EXTERNAL TABLE emp_skewed(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2)
)
SKEWED BY (empno) ON (66,88,100) --指定 empno 的倾斜值 66,88,100
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
LOCATION '/hive/emp_skewed';
2.9 临时表
临时表仅对当前 session 可见，临时表的数据将存储在用户的暂存目录中，并在会话结束后删除。如果临时表与永久表表名相同，则对该表名的任何引用都将解析为临时表，而不是永久表。临时表还具有以下两个限制：
- 不支持分区列；
- 不支持创建索引。
CREATE TEMPORARY TABLE emp_temp( empno INT, ename STRING, job STRING, mgr INT, hiredate TIMESTAMP, sal DECIMAL(7,2), comm DECIMAL(7,2) ) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";三、修改表
3.1 改表名
```plsql 语法： ALTER TABLE table_name RENAME TO new_table_name;
示例： ALTER TABLE emp_temp RENAME TO new_emp; —把 emp_temp 表重命名为 new_emp;
<a name="I9fSu"></a>
### 3.2 修改表结构
```plsql
修改模板：
alter table tablename set TBLPROPERTIES(
property_name=property_value,property_name=property_value,…
);
eg1:改表类型
alter table iteblog set TBLPROPERTIES ('EXTERNAL'='TRUE');//改为外部表
alter table iteblog set TBLPROPERTIES ('EXTERNAL'='FALSE');
eg2:avro表改schema
ALTER TABLE bigdata_dev_dashuju.credit_card_user
SET TBLPROPERTIES(
'avro.schema.literal'=
'{
"namespace": "dw.syncer.model",
"type": "record",
"name": "CreditCardUser",
"fields": [
{"name": "phone_number", "type": "string", "doc": "手机号码"},
{"name": "applied_time", "type": "long", "doc": "时间"},
{"name": "bank", "type": "string", "doc": "银行"},
{"name": "uid", "type": ["null", "string"], "default": null, "doc": "密文"},
{"name": "eid", "type": ["null", "string"], "default": null, "doc": "外部密文"}
]
}');
3.3 修改表属性
1.无分区
alter table tablename set serdeproperties(
'field.delim'='\t'
);
2.有分区
alter table tablename partition(dt='xxxx') set serdeproperties
('field.delim'='\t');
四、清空表/删除表
4.1 清空表
目前只有内部表才能执行 TRUNCATE 操作，外部表执行时会抛出异常 Cannot truncate non-managed table XXXX。
语法：
TRUNCATE TABLE table_name [PARTITION (partition_column = partition_col_value, ...)];
-- 清空整个表或表指定分区中的数据
TRUNCATE TABLE emp_mgt_ptn PARTITION (deptno=20);
4.2 删除表
语法：
DROP TABLE [IF EXISTS] table_name [PURGE];
示例：
drop table tablename;
- 内部表：不仅会删除表的元数据，同时会删除 HDFS 上的数据；
- 外部表：只会删除表的元数据，不会删除 HDFS 上的数据；
- 删除视图引用的表时，不会给出警告（但视图已经无效了，必须由用户删除或重新创建）。
五、查看表或视图
5.1 查看表
--查看表详情
DESCRIBE|Desc [EXTENDED|FORMATTED] table_name --FORMATTED 以友好的展现方式查看表详情
-- 示例
desc FORMATTED table_namep;
-- 查看指定库的所有表
SHOW TABLES [IN database_name] ['identifier_with_wildcards'];
-- 示例
SHOW TABLES IN default;
5.2 查看视图
-- 查看指定库的所有视图
SHOW VIEWS [IN/FROM database_name] [LIKE 'pattern_with_wildcards']; --仅支持 Hive 2.2.0 +
5.3 查看表/视图的创建语句
SHOW CREATE TABLE ([db_name.]table_name|view_name);
-- 示例
show create table table_name;
六、分区操作
6.1.查看分区列表
show partitions table_name;
6.2.修改分区
a.add(可自定义分区路径)
ALTER TABLE bigdata_dev_dashuju.e_dttag ADD IF NOT EXISTS PARTITION(day='20180416', province_code=32) LOCATION '/group/bigdata_dev_dashuju/db/e_dttag/20180416/32';
b.drop
ALTER TABLE bigdata_dev_dashuju.e_dttag DROP IF EXISTS PARTITION (day='20180418');
c.update
ALTER TABLE iteblog PARTITION (dt='2008-08-08') SET LOCATION "new location";
ALTER TABLE iteblog PARTITION (dt='2008-08-08') RENAME TO PARTITION (dt='20080808');
d.分区修复
为了让 MSCK 命令工作，分区的目录名必须是 partition_name=partition_value(key=value结构)，否则将无法添加分区。这时候你必须使用add partition命令了。
MSCK REPAIR TABLE table_name;
注：分区操作，只是表结构层面的调整，不会实际影响到hdfs上的数据文件。
七、字段操作
7.1 新增列
ADD COLUMNS 允许用户在当前列的末尾增加新的列，但是在分区列之前。
--这种方式，新增的列在旧分区中不可见，查询数据时为NULL，重新刷数据时仍为NULL
ALTER TABLE emp_temp ADD COLUMNS (address STRING COMMENT 'home address');
--解决办法：新增列时，DDL语句最后添加CASCADE
ALTER TABLE iteblog ADD COLUMNS (col_name STRING) CASCADE;(分区，推荐)
7.2 修改列
语法：
ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type
[COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];
示例：
-- 修改字段名和类型
ALTER TABLE emp_temp CHANGE empno empno_new INT;
-- 修改字段 sal 的名称 并将其放置到 empno 字段后
ALTER TABLE emp_temp CHANGE sal sal_new decimal(7,2) AFTER ename;
--将 b 列的名字修改为 b1, 并将它放在第一列。新表的结构为： b1 int, a string, c int.
ALTER TABLE test_change CHANGE b b1 INT FIRST;
-- 为字段增加注释
ALTER TABLE emp_temp CHANGE mgr mgr_new INT COMMENT 'this is column mgr';
-- 替换列
CREATE TABLE test_change (a int, b int, c int);
--REPLACE COLUMNS先删除现存列，然后再增加新列。替换列只能在表使用自带SerDe（DynamicSerDe,MetadataTypedColumnsetSerDe, LazySimpleSerDe and ColumnarSerDe）时使用。REPLACE COLUMNS也可以用于删除列
alter table test_change replace columns (a1 string, b1 string, c1 string);
【注】需要注意的是，hive表、分区、字段的调整仅是针对hive元数据层面的修改，不会相应的修改数据。hive中的数据本质是hdfs中的文件
若有收获，就点个赞吧
0 人点赞
