打怪升级之小白的大数据之旅(六十三)
Hive旅程第四站:DDL操作
上次回顾
上一章，我们学习了Hive的数据类型以及访问方式，本章节我们对数据库与数据表的操作进行学习
DDL操作
数据库操作的CURD
数据库的操作和mysql相同，这里还是把相关的语法写出来，便于日后大家查看
创建数据库
语法格式：
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
- 参数说明：
- [if not exists] : 判断是否存在，如果存在则不创建，不存在就创建
- [COMMENT database_comment] 数据库的说明
- [LOCATION hdfs_path hdfs] hdfs上存储的路径，默认存储路径是
/user/hive/warehouse/*.db - [WITH DBPROPERTIES (property_name=property_value, …)] 数据库的其他参数设置
示例：
-- 创建一个数据库，数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db
create database db_hive;
-- 创建一个数据库，指定数据库在HDFS上存放的位置
create database db_hive2 location '/db_hive2';
一般我们都会使用 if not exites进行判断，这样避免因数据库存在导致报错，所以我们通常创建数据库的标准格式如下:
create database if not exists db_hive;
查询数据库
-
显示数据库
-- 显示数据库 show databases; -- 过滤显示查询的数据库 hive> show databases like 'db_hive*'; -
查看数据库详情
-- 显示数据库信息 desc database db_hive; -- 显示数据库详细信息，extended desc database extended db_hive; -
切换数据库
-- 切换数据库 use db_hive;
修改数据库
-
用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置
alter database db_hive set dbproperties('createtime'='20170830');
删除数据库
-
删除空数据库
drop database db_hive2; -
如果数据库不为空，可以采用cascade命令，强制删除
drop database db_hive cascade; -
通常情况下，会加上参数 if exists 判断数据库是否存在， 我们的标准写法如下
-- 如果删除的数据库不存在，最好采用 if exists判断数据库是否存在 drop database if exists db_hive2;
数据表操作的CURD
在HQL语法中，数据表的创建有些复杂，因为它底层是MR模板，所以有很多的参数大家需要记忆一下
查看表
-
查看当前创建的所有表
show tables; -
查看表的结构
desc test; -
查看表的详细信息
desc formatten test;
我们下面创建表的时候，可以根据这个详细参数来进行理解
创建表
语法格式：
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]
参数说明:
- CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
- EXTERNAL关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
- COMMENT：为表和列添加注释。
- PARTITIONED BY创建分区表
- CLUSTERED BY创建分桶表
- SORTED BY不常用，对桶中的一个或多个列另外排序,它是与分桶表一起使用的
- ROW FORMAT 指定表中数据的格式参数
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)] 用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。SerDe是Serialize/Deserilize的简称， hive使用Serde进行行对象的序列与反序列化。 - STORED AS指定存储文件类型
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件） 如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。 - LOCATION ：指定表在HDFS上的存储位置。
- AS：后跟查询语句，根据查询结果创建表。
- LIKE允许用户复制现有的表结构，但是不复制数据。
我们通常在建表的使用 row format…创建表，如上表，当我们指定了\t为每个字段的分割符之后，那么我们的数据也使用\t进行分割，它自己就会识别出对应的字段
管理表
管理表概述
- 默认创建的表都是所谓的管理表，有时也被称为内部表。
- 因为这种表，Hive会（或多或少地）控制着数据的生命周期。
- Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。
- 当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据
下面通过下面的案例来演示管理表的创建
-
测试数据
-- 创建测试数据，我放到了hive根目录下，并专门创建了一个存储数据的文件夹 vim $HIVE_HOME/dbdata/test2.txt 1001 ss1 1002 ss2 1003 ss3 1004 ss4 1005 ss5 1006 ss6 1007 ss7 1008 ss8 1009 ss9 1010 ss10 1011 ss11 1012 ss12 1013 ss13 1014 ss14 1015 ss15 1016 ss16 -
创建普通表(管理表)
-- 在hdfs中的test数据库中创建一张名为test2的表，并使用`\t`作为分隔符 create table if not exists test2( id int, name string ) row format delimited fields terminated by '\t' stored as textfile location '/user/hive/warehouse/test'; -
将测试数据导入到我们的表中(导入下一章会详细讲)
-- 从本地的测试数据文件夹中加载test2.txt数据到test2表中 load data local inpath "/opt/module/hive/dbdata/test/test2.txt" into table test2; -
根据查询结果创建test3表（查询的结果会添加到新创建的表中）
create table if not exists test3 as select id, name from test; -
根据已经存在的表结构创建表
create table if not exists test4 like student;
外部表
外部表概念
- 因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉
- 当我们重新创建一张表并与数据进行连接，就是建立元数据与数据连接的意思
- 创建外部表的时候，只是多了一个参数
external，其他与创建普通管理表一样
外部表使用场景
- 每天将收集到的网站日志定期流入HDFS文本文件。
- 在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表
- 通俗的来说，就是我们希望创建一张表但不希望改变原数据的时候使用(因为我们通常不会对Hive表进行追加和更新操作)
下面通过下面的案例来演示外部表的创建与使用
- 测试数据
-- 将下面的数据存储在我们测试数据库中，我创建一个文件夹来存放它们 vim /$HIVE_HOME/dbdata/company/dept.txt vim /$HIVE_HOME/dbdata/company/emp.txt -- dept 部门表 10 ACCOUNTING 1700 20 RESEARCH 1800 30 SALES 1900 40 OPERATIONS 1700 -- emp 员工表 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 - 这次我们就不将测试数据放在本地加载了，我们采用hdfs上来模拟一下实际的场景
- 上传数据到HDFS
dfs -mkdir -p /company/dept; dfs -mkdir -p /company/emp; dfs -put /opt/module/hive/dbdata/company/dept.txt /company/dept; dfs -put /opt/module/hive/dbdata/company/emp.txt /company/emp; - 创建部门外部表
create external table if not exists dept(
deptno int,
dname string,
loc int)
row format delimited fields terminated by '\t'
location '/company/dept';
- 创建员工外部表
create external table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
row format delimited fields terminated by '\t'
location '/company/emp';
使用外部表我们就无需进行load data进行导入，它会自动建立连接
管理表与外部表的互相转换
- 管理表与外部表的转换通过使用参数
EXTERNAL=布尔值来进行转换 - (‘EXTERNAL’=‘TRUE’)和(‘EXTERNAL’=‘FALSE’)为固定写法，区分大小写！
- 首先看一下我们test表的类型，可以看到，它是管理表
desc formatted student; Table Type: MANAGED_TABLE - 修改内部表test为外部表
alter table test set tblproperties('EXTERNAL'='TRUE'); - 再次查看类型，发现它确实改变了
desc formatted test; Table Type: EXTERNAL_TABLE - 我们将它改回到管理表
alter table test set tblproperties('EXTERNAL'='FALSE');
修改表
重命名表
语法格式
ALTER TABLE table_name RENAME TO new_table_name
示例
-- 修改test表名称为student
alter table test rename to student ;
增加、删除分区表
分区表我会在分区表与分桶表详细介绍，这里就先大概写一下，有个印象
创建分区表语法
create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string) // 按照指定参数分区
row format delimited fields terminated by '\t';
删除分区
- 删除单个分区
-- 删除单个分区20210530
alter table dept_partition drop partition (day='20210530');
- 同时删除多个分区（分区之间必须有逗号）
-- 同时删除多个分区20210528与20210529
alter table dept_partition drop partition (day='20210528'), partition(day='20210529');
增加/修改/替换列信息
修改列信息比较简单，根据指定参数，然后后面添加对应的字段信息即可
更新列
- 更新列，列名可以随意修改，列的类型只能小改大，不能大改小（遵循自动转换规则）
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name] - 增加列
ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...) - 替换列
ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
- 我比较推荐替换列 REPLACE参数，如果需要进行增删改查，只需要记住这个参数，并且将我们所需要变更后的字段完整写一遍，就可完成增删改查操作
- 当然了，正是因为REPLACE会操作所有字段，所以当我们的字段比较多的时候还是不要使用，避免因为少写或者写错导致整个数据异常
示例：
-- 查询表结构
-- 添加列
alter table dept add columns(deptdesc string);
-- 更新列
alter table dept change column deptdesc desc string;
-- 替换列
alter table dept replace columns(deptno string, dname
string, loc string);
删除表、清空表
删除表
drop table dept;
清空表
truncate table student;
- 注意一下Truncate只能清空管理表，不能清空外部表中数据
总结
- 好了，本章节内容就是这些，本章节的重点就是创建表，因为参数比较多，大家尽量记住一些常用的参数，例如字段分隔符 row format delimited filds terminated by "\t"等
- 分区表和分桶表的操作还是使用创建表中的参数来完成的，因为它们内容较多并且有些不容易理解，所以我单独开一章节来讲解
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
