DDL操作
简介:
DDL(Data Definition Language)数据定义语言,它通过creating、deleting、or altering模式对象(数据库、表、分区、视图、Buckets)来描述Hive的数据结构
大。
部分的DDL关键词为CREATE、 DROP或者ALTER。
1,数据库
-
(1)创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名;
HDFS默认位置:/user/hive/warehouse/数据库名.db
-
(2)查看数据库
SHOW DATABASES; 正则表达式:SHOW DATABASES LIKE 's*';
-
(3)查看数据库信息
DESCRIBE DATABASE 数据库名;
-
(4)删除数据库
DROP DATABASE [IF EXISTS] 数据库名;
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]
CREATE TABLE:创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。
EXTERNAL:创建外部表。在建表的同时指定一个指向实际数据的路径(LOCATION)。
内部表和外部表区别:Hive创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径,不对数据的位置做任何改变。在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
PARTITIONED:
1,不用扫描整个表,只需扫描关心的一部分数据,因此引入 partition 概念
2,每个表可以拥有一个或多个分区,每个分区以文件夹的形式单独存在表文件夹的目录下,分区是以字段的形式在表结构中存在,通过 desc table 命令可以查看到字段存在,但是该字段不存放实际的数据内容,仅仅是分区的表示。
3,分区建表分为 2 种,一种是单分区,也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区,表文件夹下出现多文件夹嵌套模式。
CLUSTERED BY
1,对于每一个表(table)或者分区, Hive可以进一步组织成桶,也就是说桶是更为细粒度的数据范围划分。
2,Hive也是针对某一列进行桶的组织。
3,Hive采用对列值哈希,然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
4,把表(或者分区)组织成桶(Bucket)有两个理由:
(1)获得更高的查询处理效率
(2)使取样(sampling)更高效
ROW FORMAT:指定列分隔符,确定表的具体的列的数据
STORED AS SEQUENCEFILE|TEXTFILE|RCFILE:文件数据是纯文本:STORED AS TEXTFILE
如果数据需要压缩:STORED AS SEQUENCEFILE
LOCATION:指定数据文件存放的 HDFS 目录
LIKE:允许用户复制现有的表结构,但是不复制数据
COMMENT:可以为表与字段增加描述 -
2,创建内部表:
具体示例:CREATE TABLE student( id INT, name STRING, age INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;
-
3,创建外部表:
具体示例:CREATE EXTERNAL TABLE student2( id INT, name STRING, age INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/user/hive/warehouse/shiny.db/student2';
-
4,创建分区表
-
具体示例:
CREATE TABLE student3( id INT, name STRING, age INT) PARTITIONED BY(sex STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;
-
准备数据:
student1.txt的数据为:cat student1.txt 1001 shiny 23 1002 cendy 22 1003 angel 23 1009 ella 21 1012 eva 24
student2.txt中的数据为:
cat student2.txt 1005 bob 24 1006 mark 23 1007 leo 22 1011 JACK 23 1014 JAMES 24
-
插入数据:
插入女分区数据:LOAD DATA LOCAL INPATH '/home/shiny/Desktop/test/student1.txt' INTO TABLE student3 PARTITION(SEX='female');
插入男分区数据:
LOAD DATA LOCAL INPATH '/home/shiny/Desktop/test/student2.txt' INTO TABLE student3 PARTITION(SEX='male');
-
-
查询student3.txt的内容:
select * from student3;
-
查询表分区:
SHOW PARTITIONS student3;
-
HDFS上的显示结果:
-
-
创建分桶表
具体示例:CREATE TABLE student4( id INT, name STRING, age INT) CLUSTERED BY(id) SORTED BY(id DESC) INTO 3 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
3,修改表
-
重命名表
语法结构:ALTER TABLE table_name RENAME TO new_table_name;
-
增加/删除/改变/替换列
增加列
语法结构:
ALTER TABLE table_name ADD COLUMNS (col_spec[, col_spec …])
示例:ALTER TABLE student add columns(address string);
DD 是代表新增一字段,字段位置在所有列后面(PARTITION 列前)
-
删除列
DROP 操作不支持,使用REPLACE操作代替 -
改变列
语法结构:
ALTER TABLE table_name CHANGE COLUMN c_name new_name new_type [FIRST|AFTER c_name]
示例:ALTER TABLE student CHANGE COLUMN id number INT;
-
替换列
语法结构:
ALTER TABLE table_name REPLACE COLUMNS (col_spec[, col_spec …]
示例:ALTER TABLE student REPLACE COLUMNS (id INT,name string,age INT);
REPLACE 则是表示替换表中所有字段
-
增加/删除分区
-
增加分区
语法结构:
ALTER TABLE table_name ADD [IF NOT EXISTS]
partition_spec [ LOCATION ‘location1’ ]
partition_spec [ LOCATION ‘location2’ ]
…
partition_spec:
: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, …)
示例:ALTER TABLE student3 ADD PARTITION(sex='weizhi') LOCATION '/user/hive/warehouse/shiny.db/student3/sex=weizhi';
注意:只有在存在分区列的表上执行增加分区的操作,否则产生异常。
-
删除分区
语法结构:
ALTER TABLE table_name DROP partition_spec, partition_spec,…
示例:ALTER TABLE student3 DROP PARTITION(sex='weizhi');
-
-
4,删除表
语法结构:
DROP TABLE [IF EXISTS] table_name;
示例:
DROP TABLE IF EXISTS student3;
5,常用显示命令
显示命令 | 释义 |
---|---|
SHOW TABLES; | 显示当前数据库中所有的表 |
SHOW DATABASES; | 显示所有的数据库 |
SHOW PARTITIONS table_name; | 显示分区信息 |
SHOW FUNCTIONS; | 显示所有函数 |
DESC FORMATTED table_name; | 显示表结构信息 |
DML操作
简介:
DML(Data Manipulation Language)数据操纵语言
使用户能够查询数据库以及操作已有数据库中的数据
DML主要是对Hive 表中的数据进行操作,如INSERT、DELETE、UPDATE、SELECT(插入、删除、修改、检索)等都是DML
LOAD装载数据
LOAD:复制/移动操作,将数据文件移动到 Hive 表对应的位置
语法结构:
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename
[PARTITION(partcol1=val1, partcol2=val2 …)]
-
加载本地数据
LOAD DATA LOCAL INPATH '/home/shiny/Desktop/test/student1.txt' INTO TABLE student3 PARTITION(SEX='female');
filepath: 本地文件系统中的路径
LOCAL: LOAD 命令会去查找本地文件系统中的 filepathLOAD DATA LOCAL INPATH '/home/shiny/Desktop/test/student2.txt' OVERWRITE INTO TABLE student3 PARTITION(SEX='male');
OVERWRITE: 覆盖表中原来内容
-
加载HDFS数据
LOAD DATA INPATH '/user/hive/warehouse/shiny.db/student3/sex=female/student1.txt' INTO TABLE student3 PARTITION(SEX='male');
filepath: HDFS中的路径
简单模式:/user/hive/project/data1
完整模式:hdfs://namenode_host:9000/user/hive/project/data1
LOCAL: 没有指定 LOCAL 关键字,则根据 inpath 中的 URI 查找文件,URI 是指 HDFS 上的路径 -
INSERT插入数据
-
1,单条插入(一般不使用)
语法结构:
INSERT INTO TABLE table_name VALUES(XX,YY,ZZ); -
2,利用查询语句将结果导入新表
语法结构:
INSERT OVERWRITE [INTO] TABLE table_name [PARTITION (partcol1=val1, partcol2=val2 …)]
select_statement1 FROM from_statement -
3,多重插入
语法结构:
FROM from_statement
INSERT OVERWRITE TABLE table_name1 [PARTITION (partcol1=val1, partcol2=val2 …)]
select_statement1
INSERT OVERWRITE TABLE table_name2 [PARTITION (partcol1=val1, partcol2=val2 …)]
select_statement2]
…
示例 :FROM student3
-
INSERT INTO TABLE student3_1 SELECT id,name
INSERT INTO TABLE student3_2 SELECT age;
- 4,CTAS(create table … as select …)
在实际情况中,表的输出结果可能太多,不适于显示在控制台上,这时候,将 Hive 的查询输出结果直接存在一个新的表中是非常方便的,我们称这种情况为 CTAS。
具体示例:
CREATE TABLE student3_3 AS SELECT id,name,age FROM student3;
若 select 查询失败,新表不会创建
INSERT导出数据
- 单模式导出
-
导出数据到本地
INSERT OVERWRITE LOCAL DIRECTORY'/home/shiny/Desktop/test/students.txt' SELECT * FROM student3;
-
注意:数据写入到文件系统时进行文本序列化,且每列用^A 来区分, \n 为换行符。
- 导出数据到HDFS
INSERT OVERWRITE DIRECTORY '/students' SELECT * FROM student3;
- 多模式导出
语法结构:
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]
…
SELECT查询数据
-
1,GROUP BY:按学生姓名汇总学生年龄
SELECT name,SUM(age) FROM student_buck GROUP BY name;
-
2,ORDER BY: 获取班级年龄最大的五个学生
SELECT * FROM student3 ORDER BY age DESC LIMIT 5;
全局排序,因此只有一个 reducer,只有一个 reduce task 的结果,比如文件名是 000000_0,会导致当输入规模较大时,需要较长的计算时间。
-
3,SORT BY: 查询学生信息,按照age降序排序
SET mapreduce.job.reduces=2; #设置reduce的个数为2 SELECT * FROM STUDENT3 SORT BY age DESC;
局部排序,其在数据进入 reducer 前完成排序。因此,如果用 SORT BY 进行排序,并且设置 mapreduce.job.reduces > 1,则 SORT BY 只保证每个 reducer 的输出有序,不保证全局有序。
-
4,DISTRIBUTE BY + SORT BY: 分桶和排序的组合操作,对 id 进行分桶,对 age, id 进行降序排序(先对 age 进行降序排序,age 相同的情况下对 id 进行降序排序)
SET mapreduce.job.reduces; #查看reduce的个数 INSERT OVERWRITE LOCAL DIRECTORY '/home/shiny/Desktop/test/students_buck' SELECT * FROM student3 DISTRIBUTE BY id SORT BY age DESC,id DESC;
-
5,CLUSTER BY: 分桶和排序的组合操作,等于DISTRIBUTE BY + SORT BY(前提:分桶和 SORT 字段是同一个)。对 id 进行分桶,对 id 进行升序排序
INSERT OVERWRITE LOCAL DIRECTORY '/home/shiny/Desktop/test/students_buck' SELECT * FROM student3 CLUSTER BY id ;
-
6,补充
-
指定 reducetask 数量,也就是指定桶的数量
SET mapreduce.job.reduces=2;
-
指定开启分桶
SET hive.enforce.bucketing = true;
-