hive之路7-hive之DDL操作

2021-03-02 16:11:20 浏览数 (1)

Hive的DDL操作

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

LanguageManual DDL

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

库

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

表

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

库操作

一、创建库create database

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

代码语言：javascript复制
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/…
代码语言：javascript复制
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表

代码语言：javascript复制
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. 查看有哪些库
代码语言：javascript复制
0: jdbc:hive2://localhost:10000/default> show databases;
 ---------------- 
| database_name  |
 ---------------- 
| default        |
| student        |
| test           |
| worker         |
  1. 查看某个库的具体属性信息 desc database databasename;
代码语言：javascript复制
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)

  1. 查看正在使用的库

select current_database();

代码语言：javascript复制
0: jdbc:hive2://localhost:10000/default> select current_database();
 ---------- 
|   _c0    |
 ---------- 
| default  |
 ---------- 
1 row selected (1.255 seconds)
  1. 查看创建库的具体hive SQL语句

show create database databasename;

代码语言：javascript复制
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)

五、删除库
代码语言：javascript复制
drop database dbname;
drop database if exists dbname;

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

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

2、 使用cascade关键字

代码语言：javascript复制
delete database if exists dbname cascade;

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

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

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

代码语言：javascript复制
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)
  1. 删除带有表的库

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

  • 使用worker库
  • 创建userinfo表
  • 显示表的信息
代码语言：javascript复制
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关键字解决

六、 使用表
代码语言：javascript复制
`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.

七、选择数据库
代码语言：javascript复制
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
代码语言：javascript复制
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
代码语言：javascript复制
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
LIKE existing_table_or_view_name   # 通过某个现有的表或者视图来进行创建
[LOCATION hdfs_path]; # 指定存放位置
创建表demo
  • 在hive中创建好表user
代码语言：javascript复制
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
代码语言：javascript复制
1       wyp     25      13188888888888
2       test    30      13888888888888
3       zs      34      899314121

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

代码语言：javascript复制
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

  • 创建默认的内部表
代码语言：javascript复制
 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)
  • 创建外部表
代码语言：javascript复制
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";

  • 查看某个表的详细信息
代码语言：javascript复制
desc formatted student_ext;

二、分区表和分桶表

1、 分区表

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

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

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

2、两种模式

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

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

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

代码语言：javascript复制
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)
  • 每个分区值都会形成一个具体的分区目录，数据文件一定要存储在某个分区中，而不能直接存储在表中。
  • 分区字段是伪字段，不能与表定义字段重名
代码语言：javascript复制
# 假设某个表中存在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：

代码语言：javascript复制
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";

表的其他操作

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

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

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

代码语言：javascript复制
drop table [if exists] student [pruge];
  2. 查看分区信息
代码语言：javascript复制
show partitions student_ptn;
  1. 删除分区
代码语言：javascript复制
alter table student_ptn drop partition (city="shenzhen");
  1. 其他命令

Hive表的多种导入数据方式

  1. 从本地系统上导入数据（假设有数据worker.txt）
    • 先在hive中创建表
    • 将本地的数据导入
代码语言：javascript复制
-- 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

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

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

代码语言：javascript复制
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

导入语句如下

代码语言：javascript复制
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。

代码语言：javascript复制
hive> create table test1
    > as
    > select id, name
    > from user;
数据库 sql hive 大数据 存储

0 人点赞