跳至主要內容

常用DDL操作

常用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, ...)]; --指定额外属性

示例：

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 查看数据库

-- 显示数据库列表
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.3 使用数据库

USE database_name;

1.4 修改数据库属性

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

1.5 删除数据库

语法：

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

示例：

  DROP DATABASE IF EXISTS hive_test CASCADE;

二、创建表

内部表和外部表

内部表又叫做管理表 (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. 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;

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';

2.5 创建视图

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

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;

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';

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";

2.9 CTAS创建表

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

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

2.10 复制表结构

语法：

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.11 加载数据到表

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

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

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”);

2.13 内表和外表的互相转换

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

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

三、查看表

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

3.1 显示单个字段的Schema信息

Describe <table name>.<column name>;

3.2 显示表的详细信息

Describe FORMATTED <table name>;

3.3 查看表的列表

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

-- 示例
SHOW TABLES IN default;

3.4 查看视图列表

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

3.5 查看表的分区列表

SHOW PARTITIONS table_name;

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

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

四、修改表

4.1 重命名表

语法：

ALTER TABLE table_name RENAME TO new_table_name;

示例：

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

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];

示例：

-- 修改字段名和类型
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');

4.3 新增字段

示例：

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

4.4 修改表属性

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

4.5 添加表注释

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

4.6 修改表格式

ALTER TABLE employee SET FILEFORMAT SEQUENCEFILE;

4.7 修改视图

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

4.8 修改表存储格式

alter table utrack.adl_visit_daily_v2_utrack set fileformat orc;

五、清空表/删除表

5.1 清空表

语法：

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

示例：

TRUNCATE TABLE emp_mgt_ptn PARTITION (deptno=20);

5.2 删除表

语法：

DROP TABLE [IF EXISTS] table_name [PURGE];
  • 内部表：不仅会删除表的元数据，同时会删除 HDFS 上的数据；
  • 外部表：只会删除表的元数据，不会删除 HDFS 上的数据；
  • 删除视图引用的表时，不会给出警告（但视图已经无效了，必须由用户删除或重新创建）。
上次编辑于:
Copyright © 2019-present 数码乱炖