Hive SQL

一、DDL（数据定义语言，Data Definition Language） 1、对数据库的操作：创建、修改数据库 2、对数据表的操作：内部表、外部表、分区表、分桶表

二、DQL（数据查询语言，Data Query Language） 1、单表查询、多表查询 2、常用函数：聚合函数、条件函数、日期函数、字符串函数等 3、高级函数：多行合并为一行，一行拆分为多行 4、窗口函数、增强聚合函数

三、DML（数据操纵语言，Data Manipulation Language） 1、insert into 2、insert overwrite 3、update，不支持 4、delete，不支持

四、DCL（数据控制语言，Data Control Language） 1、grant，授权 2、revoke，撤销

一. hive的DDL语法

1. 数据库的操作

创建数据库

--创建库并且添加注释
create databases if not exists test2 comment '这是一个测试库';

--查看数据库的基本信息
describe database test2;

修改数据库

--使用test库
use test;
--展示所有库
show databases;
--展示当前库下的所有表
show tables;​alter database test2 set dbproperties('createtime'='20221011')
--不支持修改库名

查看数据库的详细信息

--查看数据库的基本信息
describe database test2;
--查看数据库的详细信息
dexcribe database extended test2;

删除数据库

--删除数据库(库下无表,如果库下有表则会报错)
drop database test2;
--删除数据库(无论库下是否有表,谨慎使用,执行前记得备份)
drop database test2 cascade;

2. 数据表的操作

内部表的操作

创建内部表

--创建一个student表
create table if not exists text.student(id int,name string);

-- 插入一条数据
insert into test.student values(1,'张飞');
-- 插入多条数据
insert into test.student values(2,'二爷'),(3,'皇叔');

-- 创建student_new表
create table if not exists text.student_new(id int,name string);
insert into test.student_new select * from test.student;

--通过查询语句创建表
select table test.student_select as select as * from test.student;

--通过现有表的结构来创建表(复制现有表的结构,但是没有数据)
create table test.student_like like test.student;

--查看基本信息
describe test.student

--查看详细信息
describe formatted test.student

--查看建表语句
show create table test.student

外部表的操作

创建外部表

--创建外部表
create external table if not exists test .person(    
    s_id int,
    s_name string,
    s_birth string,
    s_sex string,
    s_class int)
row format delimitedfields terminated by ',';

 从本地文件系统加载数据

-- 插入数据(从本地加载数据,追加操作)
load data local inpath '/data/import/edu_student.csv' 
into table test.student_external;

--覆盖数据(原来表的数据将会被覆盖,通过overwrite覆盖)
load data local inpath '/data/import/edu_student.csv' 
overwrite table test.student_external;

 从hdfs文件系统加载数据

--将本地文上传至hdfs(linux 操作命令)
hadoop fs -put/data/import/edu_student.csv/user/hive/warehouse/import
-- 修改文件夹的所有者
hadoop fs -chown -R root

-- 从hdfs加载数据,追加操作
load data inpath 'user/hive/warehouse/import/edu_student.csv/edu_student.csv' into table test.student_external;

-- 从hdfs加载数据,覆盖操作
load data inpath 'user/hive/warehouse/import/edu_student.csv/edu_student.csv' overwrite table test.student_external;

分区表的操作

创建分区表，一个分区

create table test.teacher_p1(
	t_id int,
    t_name string)
partitioned by(p_year string)
row format delimited
fields terminated by ',';

--从本地加载数据, 并指定分区(文件夹)
load data local inpath '/data/import/edu_teacher.csv'
into table test.teacher_p1 partition(p_year='2021');

创建分区表，多个分区

create table test.teacher_p2(
	t_id int,
    t_name string)
partitioned by(p_year string)
row format delimited
fields terminated by ',';
--从本地加载数据, 并指定分区(文件夹)
--(p_year='分区条件一',p_moth='分区条件二')
load data local inpath '/data/import/edu_teacher.csv'
into table test.teacher_p2 partition(p_year='2021',p_moth='12');

查看分区/添加分区/

--查看分区
show partitions test.teacher_p2;
--添加一个分区(表下新增一个文件夹)
alter table test.teacher_p2 add partition(p_year='2021',p_month='11')
--添加多个分区(表下新增多个文件夹)
alter table test.teacher_p2 add partition(p_year='2021',p_month='10')
partition(p_year='2021',p_month='09')

--删除分区
alter table test.teacher_p2 drop partition(p_year='2021',p_month='09')

分桶表的操作

--开启hive的桶表功能
set hive.enforce.bucketing=true;

ceate table test.course_b(
	c_id int,
    c_name string,
    t_id int)
clustered by (c_id) into 5 buckets;

-- 分桶表的数据记载
-- 通过hadoop fs -put 或 load data加载,没有分桶效果
-- 所以只能通过insert overwrite加载
-- 必须先创建普通表,再通过insert overwrite将普通表的数据加载到分桶表
ceate table test.course(
	c_id int,
    c_name string,
    t_id int)
row format delimited
fields terminated by ',';

load data local inpath '/data/import/edu_score.csv'
overwrite table test.course;

insert overwrite table test.course_b
select * from test.course cluster by (c_id);