目录
一 DDL
1.1 数据库
1.1.1 创建数据库
1.1.2 查询数据库
1.1.3 修改数据库
1.1.4 删除数据库
1.1.5 切换当前数据库
1.2 表
1.2.1 创建表
1.2.2 查看表
1.2.3 修改表
3.2.4 删除表
3.2.5 清空表
二 DML
2.1 Load
2.2 Insert
2.2.1 将查询结果插入表中
2.2.2 将给定Values插入表中
2.2.3 将查询结果写入目标路径
2.3 Export&Import
在关系型数据库管理系统(RDBMS)中,DDL(Data Definition Language)和DML(Data Manipulation Language)是两类不同类型的SQL语句,用于对数据库进行不同的操作。
DDL(Data Definition Language):DDL语句用于定义数据库的结构,包括创建、修改和删除数据库、表、视图、索引、约束等数据库对象。常见的DDL语句包括:
- CREATE:用于创建数据库对象,例如CREATE DATABASE、CREATE TABLE、CREATE INDEX等。
- ALTER:用于修改数据库对象,例如ALTER TABLE、ALTER INDEX等。
- DROP:用于删除数据库对象,例如DROP DATABASE、DROP TABLE、DROP INDEX等。
- TRUNCATE:用于清空表中的所有数据,并且保留表的结构。
- RENAME:用于重命名数据库对象,例如RENAME TABLE。
DML(Data Manipulation Language):DML语句用于对数据库中的数据进行操作,包括查询、插入、更新和删除数据。常见的DML语句包括:
- SELECT:用于查询数据库中的数据,可以使用多种条件和操作符进行过滤、排序、分组等操作。
- INSERT:用于插入新的数据行到表中。
- UPDATE:用于更新表中的现有数据。
- DELETE:用于删除表中的数据行。
需要注意的是,DDL语句会对数据库的结构进行修改,可能会对数据库的完整性和安全性产生重大影响,因此通常需要具有足够权限的用户来执行。而DML语句只涉及对数据的操作,一般权限较低,更多地用于对数据库中的数据进行日常的查询、插入、更新和删除操作。
一 DDL
1.1 数据库
1.1.1 创建数据库
1)语法文章来源:https://www.toymoban.com/news/detail-415720.html
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
2)案例
(1)创建一个数据库,不指定路径
create database db_hive1;
注:若不指定路径,其默认路径为${hive.metastore.warehouse.dir}/database_name.db
(2)创建一个数据库,指定路径
create database db_hive2 location '/db_hive2';
(2)创建一个数据库,带有dbproperties
create database db_hive3 with dbproperties('create_date'='2023-4-7');
1.1.2 查询数据库
1)展示所有数据库
(1)语法
SHOW DATABASES [LIKE 'identifier_with_wildcards'];
注:like通配表达式说明:*表示任意个任意字符,|表示或的关系。
(2)案例
hive> show databases like 'db_hive*';
OK
db_hive_1
db_hive_2
2)查看数据库信息
(1)语法
DESCRIBE DATABASE [EXTENDED] db_name;
(2)案例
①查看基本信息
hive> desc database db_hive3;
OK
db_hive hdfs://hadoop102:8020/user/hive/warehouse/db_hive.db atguigu USER
②查看更多信息
desc database extended db_hive3;
1.1.3 修改数据库
用户可以使用alter database命令修改数据库某些信息,其中能够修改的信息包括dbproperties、location、owner user。需要注意的是:修改数据库location,不会改变当前已有表的路径信息,而只是改变后续创建的新表的默认的父目录。
1)语法
--修改dbproperties
ALTER DATABASE database_name SET DBPROPERTIES (property_name=property_value, ...);
--修改location
ALTER DATABASE database_name SET LOCATION hdfs_path;
--修改owner user
ALTER DATABASE database_name SET OWNER USER user_name;
2)案例
(1)修改dbproperties
alter database db_hive3 set dbproperties ('author'='mingyu','create-time'='2023-4-7');
如果修改的键值对存在,会更新值,若不存在,会新建键值对
(2)修改location
alter database db_hive3 set location 'hdfs://hadoop102:8020/user/db_hive3';
旧表的绝对位置并未发生变化,新创建的表在新位置
(3)修改owner user
alter database db_hive3 set owner user `mingyu`;
注意用户名要用反引号🕵️♀️
1.1.4 删除数据库
1)语法
DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];
注:RESTRICT:严格模式,若数据库不为空,则会删除失败,默认为该模式。
CASCADE:级联模式,若数据库不为空,则会将库中的表一并删除。
2)案例
(1)删除空数据库
drop database db_hive2;
(2)删除非空数据库
drop database db_hive3 cascade;
1.1.5 切换当前数据库
1)语法
USE database_name;
1.2 表
1.2.1 创建表
Hive有三种建表语法
语法
1)普通建表
(1)完整语法
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]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]
[TBLPROPERTIES (property_name=property_value, ...)]
(2)关键字说明:
① TEMPORARY
临时表,该表只在当前会话可见,会话结束,表会被删除。
② EXTERNAL(重点)
外部表,与之相对应的是内部表(管理表)。管理表意味着Hive会完全接管该表,包括元数据和HDFS中的数据。而外部表则意味着Hive只接管元数据,而不完全接管HDFS中的数据。
③ data_type(重点)
Hive中的字段类型可分为基本数据类型和复杂数据类型。
基本数据类型如下:
Hive |
说明 |
定义 |
---|---|---|
tinyint |
1byte有符号整数 |
|
smallint |
2byte有符号整数 |
|
int |
4byte有符号整数 |
|
bigint |
8byte有符号整数 |
|
boolean |
布尔类型,true或者false |
|
float |
单精度浮点数 |
|
double |
双精度浮点数 |
|
decimal |
十进制精准数字类型 |
decimal(16,2) |
varchar |
字符序列,需指定最大长度,最大长度的范围是[1,65535] |
varchar(32) |
string |
字符串,无需指定最大长度 |
|
timestamp |
时间类型 |
|
binary |
二进制数据 |
复杂数据类型如下;
类型 | 说明 | 定义 | 取值示例 |
---|---|---|---|
array | 数组是一组相同类型的值的集合 | array<string> | arr[0] |
map | map是一组相同类型的键-值对集合 | map<string, int> | map['key'] |
struct | 结构体由多个属性组成,每个属性都有自己的属性名和数据类型 | struct<id:int, name:string> | struct.id |
注:类型转换
Hive的基本数据类型可以做类型转换,转换的方式包括隐式转换以及显示转换。
方式一:隐式转换
具体规则如下:
a. 任何整数类型都可以隐式地转换为一个范围更广的类型,如tinyint可以转换成int,int可以转换成bigint。
b. 所有整数类型、float和string类型都可以隐式地转换成double。
c. tinyint、smallint、int都可以转换为float。
d. boolean类型不可以转换为任何其它的类型。
详情可参考Hive官方说明:Allowed Implicit Conversions
方式二:显示转换
可以借助cast函数完成显示的类型转换
a.语法
cast(expr as <type>)
b.案例
select '1' + 2, cast('1' as int) + 2;
_c0 _c1
3.0 3
④ PARTITIONED BY(重点)
创建分区表
⑤ CLUSTERED BY ... SORTED BY...INTO ... BUCKETS(重点)
创建分桶表
⑥ ROW FORMAT(重点)
指定SERDE,SERDE是Serializer and Deserializer的简写。Hive使用SERDE序列化和反序列化每行数据。详情可参考 Hive-Serde。语法说明如下:
语法一:DELIMITED关键字表示对文件中的每个字段按照特定分割符进行分割,其会使用默认的SERDE对每行数据进行序列化和反序列化。
ROW FORAMT DELIMITED
[FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char]
[NULL DEFINED AS char]
注:
- fields terminated by :列分隔符
- collection items terminated by : map、struct和array中每个元素之间的分隔符
- map keys terminated by :map中的key与value的分隔符
- lines terminated by :行分隔符
语法二:SERDE关键字可用于指定其他内置的SERDE或者用户自定义的SERDE。例如JSON SERDE,可用于处理JSON字符串。
ROW FORMAT SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value, ...)]
⑦ STORED AS(重点)
指定文件格式,常用的文件格式有,textfile(默认值),sequence file,orc file、parquet file等等。
⑧ LOCATION
指定表所对应的HDFS路径,若不指定路径,其默认值为
${hive.metastore.warehouse.dir}/db_name.db/table_name
⑨ TBLPROPERTIES
用于配置表的一些KV键值对参数
2)Create Table As Select(CTAS)建表
该语法允许用户利用select查询语句返回的结果,直接建表,表的结构和查询语句的结构保持一致,且保证包含select查询语句放回的内容。
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
[COMMENT table_comment]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
3)Create Table Like语法
该语法允许用户复刻一张已经存在的表结构,与上述的CTAS语法不同,该语法创建出来的表中不包含数据。
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[LIKE exist_table_name]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
1.2.2 查看表
1)展示所有表
(1)语法
SHOW TABLES [IN database_name] LIKE ['identifier_with_wildcards'];
注:like通配表达式说明:*表示任意个任意字符,|表示或的关系。
(2)案例
show tables like 'stu*';
2)查看表信息
(1)语法
DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name
注:EXTENDED:展示详细信息
FORMATTED:对详细信息进行格式化的展示
(2)案例
1查看基本信息
desc stu;
2查看更多信息
desc formatted stu;
1.2.3 修改表
1)重命名表
(1)语法
ALTER TABLE table_name RENAME TO new_table_name
(2)案例
alter table stu rename to stu1;
2)修改列信息
只修改表中元数据,不修改hdfs中数据
(1)语法
① 增加列
该语句允许用户增加新的列,新增列的位置位于末尾。
ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)
alter table teacher1 add columns (birthday string comment '生日信息' );
② 更新列
该语句允许用户修改指定列的列名、数据类型、注释信息以及在表中的位置。
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 REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
2)案例
(1)查询表结构
hive (default)> desc stu;
(2)添加列
hive (default)> alter table stu add columns(age int);
(3)查询表结构
hive (default)> desc stu;
(4)更新列
hive (default)> alter table stu change column age ages double;
(6)替换列
hive (default)> alter table stu replace columns(id int, name string);
3.2.4 删除表
DROP TABLE [IF EXISTS] table_name;
3.2.5 清空表
1)语法
TRUNCATE [TABLE] table_name
注意:truncate只能清空管理表,不能删除外部表中数据。
2)案例
hive (default)> truncate table student;
二 DML
Hive是用作大数据处理的框架,update和delete用的非常少
2.1 Load
Load语句可将文件导入到Hive表中。
1)语法
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)];
关键字说明:
(1)local:表示从本地加载数据到Hive表;否则从HDFS加载数据到Hive表。
(2)overwrite:表示覆盖表中已有数据,否则表示追加。
(3)partition:表示上传到指定分区,若目标是分区表,需指定分区。
2)实操案例
(0)创建一张表
create table student(
id int,
name string
)
row format delimited fields terminated by '\t';
(1)加载本地文件到hive
load data local inpath '/opt/module/datas/student.txt' into table student;
(2)加载HDFS文件到hive中
①上传文件到HDFS
hdfs dfs -put /opt/module/data/student.txt /user/mingyu
②加载HDFS上数据,导入完成后去HDFS上查看文件是否还存在
load data inpath '/user/mingyu/student.txt'
into table student;
(3)加载数据覆盖表中已有的数据
①上传文件到HDFS
dfs -put /opt/module/data/student.txt /user/mingyu;
②加载数据覆盖表中已有的数据
load data inpath '/user/atguigu/student.txt'
overwrite into table student;
2.2 Insert
2.2.1 将查询结果插入表中
1)语法
INSERT (INTO | OVERWRITE) TABLE tablename
[PARTITION (partcol1=val1, partcol2=val2 ...)]
select_statement;
关键字说明:
(1)INTO:将结果追加到目标表
(2)OVERWRITE:用结果覆盖原有数据
2)案例
(1)新建一张表
create table student1(
id int,
name string
)
row format delimited fields terminated by '\t';
(2)根据查询结果插入数据
insert overwrite table student3
select
id,
name
from student;
2.2.2 将给定Values插入表中
1)语法
INSERT (INTO | OVERWRITE) TABLE tablename
[PARTITION (partcol1[=val1], partcol2[=val2] ...)]
VALUES values_row [, values_row ...]
2)案例
insert into table student1 values(1,'wangwu'),(2,'zhaoliu');
2.2.3 将查询结果写入目标路径
1)语法
INSERT OVERWRITE [LOCAL] DIRECTORY directory
[ROW FORMAT row_format] [STORED AS file_format] select_statement;
2)案例
insert overwrite directory '/user/mingyu/write.json'
row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'
select id,name
from student;
2.3 Export&Import
Export导出语句可将表的数据和元数据信息一并到处的HDFS路径,Import可将Export导出的内容导入Hive,表的数据和元数据信息都会恢复。Export和Import可用于两个Hive实例之间的数据迁移。
1)语法
--导出
EXPORT TABLE tablename
TO 'export_target_path'
--导入
IMPORT [EXTERNAL] TABLE new_or_original_tablename
FROM 'source_path' [LOCATION 'import_target_path']
2)案例文章来源地址https://www.toymoban.com/news/detail-415720.html
--导出
export table default.student
to '/user/hive/warehouse/export/student';
--导入
import table student2
from '/user/hive/warehouse/export/student';
到了这里,关于Hive DDL和DML的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!