Fork me on GitHub

hive之路7-hive之DDL操作

Posted on | In , |
Words count in article: 2.7k | Reading time ≈ 12

Hive的DDL操作

Hive是一种数据库技术，可以定义数据库和表来分析结构化数据。主题结构化数据分析是以表方式存储数据，并通过查询来分析。

LanguageManual DDL

数据库定义语言DDL主要是对库和表进行各种操作，具体包含：

库

  1. 创建库
  2. 查看库
  3. 删除库
  4. 切换库

表

  1. 创建表
  2. 查看表
  3. 修改表
  4. 删除表
  5. 清空表

库操作

一、创建库create database

在Hive中，数据库是一个命名空间或表的集合。创建了数据库之后，在HDFS上的存储路径默认为/usr/hive/warehouse/*.db具体语法为：

1
2
3
4
hive> create database|schema [if not exists] database_name  # 1创建数据库
		[comment database_comment]  # 2 描述
		[location hdfs_path]  # 存放路径
		[with dbproperties(property_name=property_value,...)];  # 3. 指定数据库的属性
  • if not exists是可选的，如果数据库存在则会报错；
  • 可以对数据库进行描述和添加属性
  • 指定数据库的存放路径。默认地址是/user/hive/warehouse/db_name.db/table_name/partition_name/…
1
2
3
4
5
6
7
8
9
10
11
0: jdbc:hive2://localhost:10000/default> create database if not exists worker comment 'learning hive';  # 创建worker数据库，并且戴上了注释
No rows affected (0.111 seconds)
0: jdbc:hive2://localhost:10000/default> show databases;  # 结果显示正常创建
+----------------+
| database_name  |
+----------------+
| default        |
| student        |
| worker         |
+----------------+
3 rows selected (0.118 seconds)

二、查询库信息

desc database databasename

Moflw9.png

三、创建带属性的库

创建一个带上注释和属性的test表

1
2
3
4
5
6
7
8
9
10
0: jdbc:hive2://localhost:10000/default> create database if not exists test comment 'test database' with dbproperties('creator'='peter','date'='2019-11-21');
No rows affected (0.343 seconds)
0: jdbc:hive2://localhost:10000/default> show databases;
+----------------+
| database_name  |
+----------------+
| default        |
| student        |
| test           |
| worker         |

四、查看库

  1. 查看有哪些库
1
2
3
4
5
6
7
8
0: jdbc:hive2://localhost:10000/default> show databases;
+----------------+
| database_name  |
+----------------+
| default        |
| student        |
| test           |
| worker         |

  1. 查看某个库的具体属性信息

    desc database databasename;

1
2
3
4
5
6
7
0: jdbc:hive2://localhost:10000/default> desc database test;
+----------+----------------+----------------------------------------------------+------------------+-------------+-------------+
| db_name  |    comment     |                      location                      |    owner_name    | owner_type  | parameters  |
+----------+----------------+----------------------------------------------------+------------------+-------------+-------------+
| test     | test database  | hdfs://localhost:9000/user/hive/warehouse/test.db  | show databases;  | USER        |             |
+----------+----------------+----------------------------------------------------+------------------+-------------+-------------+
1 row selected (0.126 seconds)

Mo4tqe.png

  1. 查看正在使用的库

select current_database();

1
2
3
4
5
6
7
0: jdbc:hive2://localhost:10000/default> select current_database();
+----------+
|   _c0    |
+----------+
| default  |
+----------+
1 row selected (1.255 seconds)
  1. 查看创建库的具体hive SQL语句

show create database databasename;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
0: jdbc:hive2://localhost:10000/default> show create database test;
+----------------------------------------------------+
|                   createdb_stmt                    |
+----------------------------------------------------+
| CREATE DATABASE `test`                             |
| COMMENT                                            |
|   'test database'                                  |
| LOCATION                                           |
|   'hdfs://localhost:9000/user/hive/warehouse/test.db' |
| WITH DBPROPERTIES (                                |
|   'creator'='peter',                               |
|   'date'='2019-11-21')                             |
+----------------------------------------------------+
8 rows selected (0.276 seconds)

Mo5sYR.png

五、删除库

1
2
drop database dbname;
drop database if exists dbname;

默认情况下，hive 不允许删除包含表的数据库，有两种解决办法：

1、 手动删除库下所有表，然后删除库

2、 使用cascade关键字

1
delete database if exists dbname cascade;

相当于是采用的严格restrict模式

1、删除不带有表的数据库test

直接通过drop关键字进行删除

1
2
3
4
5
6
7
8
9
10
11
0: jdbc:hive2://localhost:10000/default> drop database test;
No rows affected (0.327 seconds)
0: jdbc:hive2://localhost:10000/default> show databases;
+----------------+
| database_name  |
+----------------+
| default        |
| student        |
| worker         |
+----------------+
3 rows selected (0.099 seconds)

MoIljK.png

  1. 删除带有表的库

选择先前worker数据库，进入库中创建一个usrinfo表

  • 使用worker库
  • 创建userinfo表
  • 显示表的信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
0: jdbc:hive2://localhost:10000/default> use worker;
No rows affected (0.264 seconds)

0: jdbc:hive2://localhost:10000/default> create table userinfo (id int, name string);  # 创建表
No rows affected (0.719 seconds)
0: jdbc:hive2://localhost:10000/default> show tables;
+-----------+
| tab_name  |
+-----------+
| userinfo  |
+-----------+

0: jdbc:hive2://localhost:10000/default> desc userinfo;  # 显示表的信息
+-----------+------------+----------+
| col_name  | data_type  | comment  |
+-----------+------------+----------+
| id        | int        |          |
| name      | string     |          |
+-----------+------------+----------+
2 rows selected (0.145 seconds)

如果此时删除worker库，则会报错：

MooLdg.png

使用cascade关键字解决

MoT00S.png

六、 使用表

1
`USE database_name;``USE DEFAULT;`

USE sets the current database for all subsequent HiveQL statements. To revert to the default database, use the keyword “default” instead of a database name.

七、选择数据库

1
2
3
4
5
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);

ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER | ROLE] user_or_role;

ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;
  • 选择数据库，改变数据库的属性
  • 选择数据库，改变数据库的用户
  • 选择数据库，改变数据库的存放目录

表操作

一、创建表

  • 方式1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS]   [db_name.]table_name   # 建表
[(col_name data_type [column_constraint_specification,...)] # 指定字段
[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 # 数据存储格式format  'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]
[LOCATION hdfs_path]  # 在HDFS中的存储目录
[TBLPROPERTIES (property_name=property_value, ...)]  # 表的属性
[AS select_statement];  # AS 后面接的是某个查询语句的结果

  • create table：创建一个表，指定名字；表若存在，则通过if not exists进行忽略；

  • Temporary：临时表

  • external：让用户创建一个外表

  • comment：用于表或者字段的注释

  • Partitioned by ：指定分区字段

  • Clustered by：distributed by + sorted by的功能叠加

  • sorted by：保证同一个reduce中的数据是按照指定字段进行排序，同时指定执行的reduce 的个数（有序）

  • distributed by：按照那个指定的字段将数据划分到不同的输出reduce中，保证数据不重叠，但是数据是无序的（不重叠，无序）

  • row_format：指定字段之间的分隔符

  • file_format：

    1
    2
    3
    4
    5
    6
    7
    8
    file_format:
     |SEQUENCEFILE   # 序列化文件
     | TEXTFILE   # 文本文件
     | RCFILE    # 行列存储结合的文件格式
     | ORC     # 按照块的列存储
     | PARQUET    # 行存储格式文件
     | AVRO    # 序列化存储方式
     | JSONFILE  # json格式
  • 方式2
1
2
3
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
LIKE existing_table_or_view_name   # 通过某个现有的表或者视图来进行创建
[LOCATION hdfs_path]; # 指定存放位置

创建表demo

  • 在hive中创建好表user
1
2
3
4
5
6
7
8
9
hive> create table user
    > (id int, name string,   # 注意字符串不是char/varchar而是string
    > age int, tel string)
    > ROW FORMAT DELIMITED  # 指定行和列按照什么进行分割，行默认是一行行的数据
    > FIELDS TERMINATED BY '\t'
    > STORED AS TEXTFILE
    > LOCATION '/user/hive/warehouse/user.db/users/';
OK
Time taken: 2.832 seconds
  • 假设本地有个文件/user/local/user.txt
1
2
3
1       wyp     25      13188888888888
2       test    30      13888888888888
3       zs      34      899314121

通过如下方式进行导入并查看

1
2
hive> load data local inpath 'user.txt' into table user;   # 查看
hive> dfs -ls /user/hive/warehouse/user;   # /usre/hive/warehouse 是默认路径

各种表

一、内部表和外部表

  1. 内部表managed table
  • 内部表也称之为管理表，其数据文件、元数据及统计信息全部由hive进程自身管理
  • 存储位置：/usr/hive/warehouse/dbname.db/tablename
  • 删除内部表，则其全部数据会一同删除
  • 创建临时表时，推荐内部表
  1. 外部表external table
  • 通过元信息或者schema描述外部文件的结构
  • 外部文件能够被hive之外的进程访问和管理，例如HDFS等
  • 如果一份数据已经存储在HDFS上，并且被多个客户端进行使用，最好创建外部表

3、内部表和外部表demo

  • 创建默认的内部表
1
2
3
4
5
6
7
8
9
 0: jdbc:hive2://localhost:10000/default> create table workerinfo (id int, name string, sex string,  age int) row format delimited fields terminated by ",";
No rows affected (0.163 seconds)
0: jdbc:hive2://localhost:10000/default> show tables;
+-------------+
|  tab_name   |
+-------------+
| workerinfo  |
+-------------+
1 row selected (0.123 seconds)
  • 创建外部表
1
2
3
4
0: jdbc:hive2://localhost:10000/default> create external table student_ext(id int, name string, sex string, age int, department string)
row format delimited fields
terminated by ","
location "/hive/student";

MToMtJ.png

  • 查看某个表的详细信息
1
desc formatted student_ext;

MTTvdS.png

二、分区表和分桶表

1、 分区表

Hive select 查询时候，一般会扫描整个表，开销大，消耗时间。分区表指的是在创建表的时候，指定partition的分区空间，语法为：

1
partitioned by (par_col, par_type)   -- 指定字段和类型
  • 静态分区：分区的值是确定的
  • 动态分区：分区的值是不确定的，默认开始

hive中默认的是动态分区开启状态

MTLf4U.png

2、两种模式

严格模式：strict，必须有一个字段是静态分区字段。

非严格模式：non strict；允许所有的字段是动态分区字段。

设置方法：hive.exec.dynamic.partition.mode=strict/nonstrict

1
2
3
4
5
6
7
0: jdbc:hive2://localhost:10000/default> set hive.exec.dynamic.partition.mode;   # 默认是strict模式
+------------------------------------------+
|                   set                    |
+------------------------------------------+
| hive.exec.dynamic.partition.mode=strict  |
+------------------------------------------+
1 row selected (0.01 seconds)

  • 每个分区值都会形成一个具体的分区目录，数据文件一定要存储在某个分区中，而不能直接存储在表中。

  • 分区字段是伪字段，不能与表定义字段重名

1
2
3
4
5
6
7
8
9
10
11
12
# 假设某个表中存在3个字段
Id int,
Date date,
Name varchar

# 创建分区表
create table partable(
  Id int,
  dtDontQuery string,  # 不能重名，用dtDontQuery代替
  Name string
)
partitioned by (date string)  # 指定分区字段
  1. 分桶表bucketed sorted tables

将已经执行了聚类和排序之后的表放入桶中，一个demo：

1
2
3
4
5
0: jdbc:hive2://localhost:10000/default> 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 ","
location "/hive/student_bck";

M7mV00.png

表的其他操作

  1. 查看当前使用的数据库中的表
1
show tables;
  1. 查看数据库中以xxx开头的表
1
show tables like 'student*'   -- 以student开头的表
  1. 查看表的详细信息
1
2
3
desc student;
desc extended student;
desc formatted student;
  1. 查看建表的语句
1
show create tabel student;
  1. 修改表名
1
alter table student rename to new_student;
  1. 删除表

删除内表：将元数据信息和数据一起删除，实际上将数据移动到.Transh/Current目录，而元数据则是完全删除的；加了purge之后，数据删除干净！

删除外表：仅删除元数据，诗句仍在建表时指定的目录下

1
drop table [if exists] student [pruge];

  2. 查看分区信息

1
show partitions student_ptn;
  1. 删除分区
1
alter table student_ptn drop partition (city="shenzhen");
  1. 其他命令

M73CyF.png

Hive表的多种导入数据方式

  1. 从本地系统上导入数据（假设有数据worker.txt）
    • 先在hive中创建表
    • 将本地的数据导入
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- hive中创建表
hive> create table user    # 创建user表
    > (id int, name string,
    > age int, tel string)
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY '\t'
    > STORED AS TEXTFILE;
OK
Time taken: 2.832 seconds

# 导入本地数据
hive> load data local inpath 'user.txt' into table user;

# 查询结果，显示咋HDFS中的
hive> dfs -ls /user/hive/warehouse/worker;
  1. 从HDFS中导入数据

假设HDFS上有一个文件/home/worker/test.txt

1
hive>load data inpath '/home/worker/test.txt' into table user;
  1. 从其他的表中查询出相应的数据并且导入到hive表

假设有一张test表，以age作为分区字段：

1
2
3
4
5
6
7
8
9
hive> create table test(
    > id int, name string
    > ,tel string)
    > partitioned by   -- 分区字段
    > (age int)
    > ROW FORMAT DELIMITED
    > FIELDS TERMINATED BY '\t'
    > STORED AS TEXTFILE;
OK

导入语句如下

1
2
3
4
hive> insert into table test
    > partition (age='25')
    > select id, name, tel
    > from user;   # 从user表中导出3个字段，同时id=25
  1. CTAS导入方式

在实际情况中，表的输出结果可能太多，不适于显示在控制台上，此时将Hive的查询输出结果直接存在一个新的表中，非常方便

CTAS指的是create table….. as select。

1
2
3
4
hive> create table test1
    > as
    > select id, name
    > from user;

本文标题:hive之路7-hive之DDL操作

发布时间:2019年11月22日 - 18:11

原始链接:http://www.renpeter.cn/2019/11/22/hive%E4%B9%8B%E8%B7%AF7-hive%E4%B9%8BDDL%E6%93%8D%E4%BD%9C.html

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

Coffee or Tea