hive之路7-hive之DDL操作

皮大大

发布于 2021-03-02 16:11:20

5220

发布于 2021-03-02 16:11:20

文章被收录于专栏：机器学习/数据可视化机器学习/数据可视化

Hive的DDL操作

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

LanguageManual DDL

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

库

创建库
查看库
删除库
切换库

表

创建表
查看表
修改表
删除表
清空表

库操作

一、创建库create database

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

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/…

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

三、创建带属性的库

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

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         |

四、查看库

查看有哪些库

0: jdbc:hive2://localhost:10000/default> show databases;
+----------------+
| database_name  |
+----------------+
| default        |
| student        |
| test           |
| worker         |

查看某个库的具体属性信息 desc database databasename;

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)

查看正在使用的库

select current_database();

0: jdbc:hive2://localhost:10000/default> select current_database();
+----------+
|   _c0    |
+----------+
| default  |
+----------+
1 row selected (1.255 seconds)

查看创建库的具体hive SQL语句

show create database databasename;

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)

五、删除库

drop database dbname;
drop database if exists dbname;

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

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

2、使用cascade关键字

delete database if exists dbname cascade;

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

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

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

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)

删除带有表的库

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

使用worker库
创建userinfo表
显示表的信息

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库，则会报错：

使用cascade关键字解决

六、使用表

`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.

七、选择数据库

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

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 后面接的是某个查询语句的结果

方式2

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
LIKE existing_table_or_view_name   # 通过某个现有的表或者视图来进行创建
[LOCATION hdfs_path]; # 指定存放位置

创建表demo

在hive中创建好表user

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       wyp     25      13188888888888
2       test    30      13888888888888
3       zs      34      899314121

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

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

各种表

一、内部表和外部表

内部表managed table

内部表也称之为管理表，其数据文件、元数据及统计信息全部由hive进程自身管理
存储位置：/usr/hive/warehouse/dbname.db/tablename
删除内部表，则其全部数据会一同删除
创建临时表时，推荐内部表

外部表external table

通过元信息或者schema描述外部文件的结构
外部文件能够被hive之外的进程访问和管理，例如HDFS等
如果一份数据已经存储在HDFS上，并且被多个客户端进行使用，最好创建外部表

3、内部表和外部表demo

创建默认的内部表

 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)

创建外部表

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

查看某个表的详细信息

desc formatted student_ext;

二、分区表和分桶表

1、分区表

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

partitioned by (par_col, par_type)   -- 指定字段和类型

静态分区：分区的值是确定的
动态分区：分区的值是不确定的，默认开始

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

2、两种模式

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

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

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

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)

每个分区值都会形成一个具体的分区目录，数据文件一定要存储在某个分区中，而不能直接存储在表中。
分区字段是伪字段，不能与表定义字段重名

# 假设某个表中存在3个字段
Id int,
Date date,
Name varchar

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

分桶表bucketed sorted tables

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

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

表的其他操作

查看当前使用的数据库中的表

show tables;

查看数据库中以xxx开头的表

show tables like 'student*'   -- 以student开头的表

查看表的详细信息

desc student;
desc extended student;
desc formatted student;

查看建表的语句

show create tabel student;

修改表名

alter table student rename to new_student;

删除表

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

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

drop table [if exists] student [pruge];

查看分区信息

show partitions student_ptn;

删除分区

alter table student_ptn drop partition (city="shenzhen");

其他命令

Hive表的多种导入数据方式

从本地系统上导入数据（假设有数据worker.txt）
- 先在hive中创建表
- 将本地的数据导入

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

从HDFS中导入数据

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

hive>load data inpath '/home/worker/test.txt' into table user;

从其他的表中查询出相应的数据并且导入到hive表

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

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

导入语句如下

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

CTAS导入方式

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

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

hive> create table test1
    > as
    > select id, name
    > from user;

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2019-11-22，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度