# 常用DDL操作

# 一、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, ...)]; --指定额外属性
1
2
3
4

示例:

CREATE DATABASE IF NOT EXISTS hive_test
  COMMENT 'hive database for test'
  WITH DBPROPERTIES ('create'='heibaiying');

-- 创建数据库的时候附带注释
CREATE DATABASE <database name> COMMENT
'Hold all secret information';

-- 创建数据库的时候附带属性值
CREATE DATABASE bihell WITH DBPROPERTIES
('creator'='haseo','Date'='2016-07-10');

1
2
3
4
5
6
7
8
9
10
11
12

# 1.2 查看数据库

-- 显示数据库列表
show databases;

-- 显示数据库信息
DESCRIBE DataBase <database_name>;

-- 显示数据库的扩展信息
DESCRIBE DataBase extended <database_name>;

-- 查看数据库列表
-- LIKE 子句允许使用正则表达式进行过滤,但是 SHOW 语句当中的 LIKE 子句只支持 `*`(通配符)和 `|`(条件或)两个符号。例如 `employees`,`emp *`,`emp * | * ees`,所有这些都将匹配名为 `employees` 的数据库。
SHOW DATABASES like 'hive*';

# 设置命令行显示当前使用的数据库
set hive.cli.print.current.db=true;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 1.3 使用数据库

USE database_name;
1

# 1.4 修改数据库属性

ALTER DataBase bihell set DBPROPERTIES
('edited-by'='Haseo','Date'='2016-07-10');
1
2

# 1.5 删除数据库

语法:

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
1
  • 默认行为是 RESTRICT,如果数据库中存在表则删除失败。要想删除库及其中的表,可以使用 CASCADE 级联删除。

示例:

  DROP DATABASE IF EXISTS hive_test CASCADE;
1

# 二、创建表

内部表和外部表

内部表又叫做管理表 (Managed/Internal Table),创建表时不做任何指定,默认创建的就是内部表。想要创建外部表 (External Table),则需要使用 External 进行修饰。 内部表和外部表主要区别如下:

内部表 外部表
数据存储位置 内部表数据存储的位置由 hive.metastore.warehouse.dir 参数指定,默认情况下表的数据存储在 HDFS 的 /user/hive/warehouse/数据库名.db/表名/ 目录下 外部表数据的存储位置创建表时由 Location 参数指定;
导入数据 在导入数据到内部表,内部表将数据移动到自己的数据仓库目录下,数据的生命周期由 Hive 来进行管理 外部表不会将数据移动到自己的数据仓库目录下,只是在元数据中存储了数据的位置
删除表 删除元数据(metadata)和文件 只删除元数据(metadata)

# 2.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]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
1
2
3
4
5
6
7
8
9
10
11

字段解释说明

  1. CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。
  2. EXTERNAL 关键字可以让用户创建一个外部表,在建表的同时可以指定一个指向实际数据的路径(LOCATION),在删除表的时候,内部表的元数据和数据会被一起删除,而外 部表只删除元数据,不删除数据。
  3. COMMENT:为表和列添加注释。
  4. PARTITIONED BY 创建分区表
  5. CLUSTERED BY 创建分桶表
  6. SORTED BY 不常用,对桶中的一个或多个列另外排序
  7. ROW FORMAT 行格式
  8. STORED AS 指定存储文件类型
  9. LOCATION :指定表在 HDFS 上的存储位置。
  10. AS:后跟查询语句,根据查询结果创建表。
  11. LIKE 允许用户复制现有的表结构,但是不复制数据。

# 2.2 建表数据分隔符

  • 制作table的输入文件,有时候我们需要输入一些特殊的分隔符
  • 把hive表格导出到本地时,系统默认的分隔符是^A,这个是特殊字符,直接cat或者vim是看不到的
分隔符 描述
\n 对于文本文件来说,每行都是一条记录,因此换行符可以分隔记录
^A(Ctrl+A) 用于分隔字段(列)。在CREATE TABLE语句中可以使用八进制编码\001表示
^B(Ctrl+B) 用于分隔ARRAY或者STRUCT中的元素,或用于MAP中键-值对之间的分隔。在CREATE TABLE语句中可以使用八进制编码\002表示
^C(Ctrl+C) 用于MAP中键和值之间的分隔。在CREATE TABLE语句中可以使用八进制编码\003表示

# 2.3 创建内部表

CREATE TABLE IF NOT EXISTS mydb.employee(
  Name STRING COMMENT 'Employee name',
  Salary FLOAT COMMENT 'Employee salary',
  Subordinates ARRAY<STRING> COMMENT 'Names of subordinates',
  deductions MAP<STRING,FLOAT> COMMENT 'deductions',
  address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT> COMMENT 'Address')
)
COMMENT 'Description of the table'
PARTITIONED BY (dt STRING, country STRING);
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
COLLECTION ITEMS TERMINATED BY '|' --指定数组类型的数据用什么符号分割
MAP KEYS TERMINATED BY '\t'
STORED AS TEXTFILE;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 2.4 创建外部表

CREATE EXTERNAL TABLE IF NOT EXISTS mydb.employee (
  Name STRING COMMENT 'Employee name',
  Salary FLOAT COMMENT 'Employee salary',
  Address STRING COMMENT 'Address')
COMMENT 'Description of the table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
Location '/user/cloudera/hdfs_dir';
1
2
3
4
5
6
7

# 2.5 创建视图

CREATE VIEW IF NOT EXISTS shipments(firstname,lastname)
COMMENT 'firstname and lastname'.
TBLPROPERTIES('creator'='Haseo')
AS SELECT...;
1
2
3
4

# 2.6 创建CSV表

DROP TABLE IF EXISTS default.customphone;
CREATE TABLE customphone(EMpID string,EMPName string,showedPhone string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
STORED AS TEXTFILE
TBLPROPERTIES  ('skip.header.line.count'='1');

LOAD DATA LOCAL INPATH 'customPhone.csv' OVERWRITE  INTO TABLE default.customphone;
1
2
3
4
5
6
7

# 2.7 创建倾斜表

通过指定一个或者多个列经常出现的值(严重偏斜),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';   
1
2
3
4
5
6
7
8
9
10
11
12

# 2.8 创建临时表

临时表仅对当前 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";
1
2
3
4
5
6
7
8
9
10

# 2.9 CTAS创建表

支持从查询语句的结果创建表:

CREATE TABLE emp_copy AS SELECT * FROM emp WHERE deptno='20';
1

# 2.10 复制表结构

语法:

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name  --创建表表名
   LIKE existing_table_or_view_name  --被复制表的表名
   [LOCATION hdfs_path]; --存储位置
1
2
3

示例:

CREATE TEMPORARY EXTERNAL TABLE  IF NOT EXISTS  emp_co  LIKE emp
1

# 2.11 加载数据到表

加载数据到表中属于 DML 操作,这里为了方便大家测试,先简单介绍一下加载本地数据到表中:

-- 加载数据到 emp 表中
load data local inpath "/usr/file/emp.txt" into table emp;
1
2

# 2.12 创建表的时候忽略表头

CREATE TABLE Employee (Emp_Number Int,Emp_Name String,Emp_sal Int) row format delimited fields terminated BY,lines terminated BY ‘\n’ tblproperties(“skip.header.line.count”=1);
1

# 2.13 内表和外表的互相转换

  1. 查询表的类型
hive (default)> desc formatted student2;
Table Type: MANAGED_TABLE
1
2
  1. 修改内部表 student2 为外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
1
  1. 查询表的类型
hive (default)> desc formatted student2;
Table Type: EXTERNAL_TABLE
1
2
  1. 修改外部表 student2 为内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');
1
  1. 查询表的类型
hive (default)> desc formatted student2;
Table Type: MANAGED_TABLE
1
2

注意:('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法,区分大小写!

# 三、查看表

使用 desc format emp_external 命令可以查看表的详细信息

# 3.1 显示单个字段的Schema信息

Describe <table name>.<column name>;
1

# 3.2 显示表的详细信息

Describe FORMATTED <table name>;
1

# 3.3 查看表的列表

-- 语法
SHOW TABLES [IN database_name] ['identifier_with_wildcards'];

-- 示例
SHOW TABLES IN default;
1
2
3
4
5

# 3.4 查看视图列表

SHOW VIEWS [IN/FROM database_name] [LIKE 'pattern_with_wildcards'];   --仅支持 Hive 2.2.0 +
1

# 3.5 查看表的分区列表

SHOW PARTITIONS table_name;
1

# 3.6 查看表/视图的创建语句

SHOW CREATE TABLE ([db_name.]table_name|view_name);
1

# 四、修改表

# 4.1 重命名表

语法:

ALTER TABLE table_name RENAME TO new_table_name;
1

示例:

ALTER TABLE emp_temp RENAME TO new_emp; --把 emp_temp 表重命名为 new_emp
1

# 4.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];
1
2

示例:

-- 修改字段名和类型
ALTER TABLE emp_temp CHANGE empno empno_new INT;
 
-- 修改字段 sal 的名称 并将其放置到 empno 字段后
ALTER TABLE emp_temp CHANGE sal sal_new decimal(7,2)  AFTER ename;

-- 为字段增加注释
ALTER TABLE emp_temp CHANGE mgr mgr_new INT COMMENT 'this is column mgr';

-- 替换字段
ALTER TABLE employee REPLACE COLUMNS (app_name STRING COMMENT 'old application name',app_id String COMMENT 'new application id');
1
2
3
4
5
6
7
8
9
10
11

# 4.3 新增字段

示例:

ALTER TABLE emp_temp ADD COLUMNS (address STRING COMMENT 'home address');
ALTER TABLE employee ADD COLUMNS (app_name STRING,session_id LONG);
1
2

# 4.4 修改表属性

ALTER TABLE employee SET TBLPROPERTIES(
'notes'='The process id is no longer captured');
)
1
2
3

# 4.5 添加表注释

Alter Table Hive_Test_table SET TBLPROPERTIES ('comment' = 'This is a new comment');
1

# 4.6 修改表格式

ALTER TABLE employee SET FILEFORMAT SEQUENCEFILE;
1

# 4.7 修改视图

ALTER VIEW shipments
SET TBLPROPERTIES
('created_at' = 'some_timestamp');
1
2
3

# 4.8 修改表存储格式

alter table utrack.adl_visit_daily_v2_utrack set fileformat orc;
1

# 五、清空表/删除表

# 5.1 清空表

语法:

-- 清空整个表或表指定分区中的数据
TRUNCATE TABLE table_name [PARTITION (partition_column = partition_col_value,  ...)];
1
2
  • 目前只有内部表才能执行 TRUNCATE 操作,外部表执行时会抛出异常 Cannot truncate non-managed table XXXX

示例:

TRUNCATE TABLE emp_mgt_ptn PARTITION (deptno=20);
1

# 5.2 删除表

语法:

DROP TABLE [IF EXISTS] table_name [PURGE]; 
1
  • 内部表:不仅会删除表的元数据,同时会删除 HDFS 上的数据;
  • 外部表:只会删除表的元数据,不会删除 HDFS 上的数据;
  • 删除视图引用的表时,不会给出警告(但视图已经无效了,必须由用户删除或重新创建)。
更新时间: 2/11/2022, 11:55:16 AM