Hive--DDL与DML与DQL

0、Hive官网学习语法

https://cwiki.apache.org/confluence/display/Hive/Home#Home-UserDocumentation

1、DDL：数据定义语言

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

1.1数据库管理

• 列举

show databases;

• 创建

CREATE DATABASE [IF NOT EXISTS] database_name [LOCATION hdfs_path];

LOCATION hdfs_path：手动指定数据库目录在HDFS上的地址

• 所有的数据库在创建时，都会在HDFS上默认创建一个同名的目录

• 如果不指定，就在/user/hive/warehouse下

• 这个选项可以手动指定一个HDFS的目录作为这个数据库目录
  测试

create database if not exists db_word;
create database if not exists db_test location '/test';

• 删除

DROP DATABASE  [IF EXISTS] database_name [CASCADE];

CASCADE：强制递归删除，如果数据库中有表存在，就需要加上这个关键字 测试

drop database db_test;--只能删除空的数据库
drop database db_test cascade;--允许数据库中有表存在

• 切换

use db_name;

• 描述

desc database db_name;

1.2表的管理

• 列举

show tables;

• 描述

desc [extended | formatted] tbname;

• 测试

desc  tb_lianjia;

desc extended tb_lianjia;

显示更加详细的信息，但是没有做排版，不易观察

desc formatted tb_lianjia;

显示更加详细的信息，同时做了排版，可读性更高

• 删除

DROP TABLE [IF EXISTS] table_name;

• 测试

drop table tests;

• 创建

规则：必须根据文件的内容来创建表，创建表的时候必须执行文件的分割符
官方给定的创建表的模板

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, ...)     -- (Note: Available in Hive 0.10.0 and later)]
    ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
[
  [ROW FORMAT row_format] 
  [STORED AS file_format] | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
[AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
 LIKE existing_table_or_view_name
[LOCATION hdfs_path];

• 第一种方式：普通的创建方式

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name (
col1 typ1,
col2 type2,
col3 type3,
……
colN typeN
)
[PARTITIONED BY (col_name data_type]        --分区表
[CLUSTERED BY (col_name, col_name, ...)     --分桶表
[ROW FORMAT row_format]                     --指定分隔符
[STORED AS file_format]                     --存储文件格式
[LOCATION hdfs_path]                        --指定表所在的HDFS目录

• 创建员工表

create database db_emp;
use db_emp;

create table tb_emp(
empno string,
ename string,
job string,
managerno string,
hiredate string,
salary double,
jiangjin double,
deptno string
)
row format delimited fields terminated by '\t'
;
-- 创建表时：要根据文件内容来设定字段，要根据文件的行和列的分隔符来指定创建表

lines termianted by ‘\n’：指定行的分隔符，默认为\n，一般不用指定
row format delimited fields terminated by ’\001‘ ：指定文件中列的分隔符，默认为\001
-- 加载数据
load data local inpath '/export/datas/emp.txt' into table tb_emp;

• 创建部门表

create table tb_dept(
    deptno string, 
    dname string,
    loc string )
    row format delimited fields terminated by ',';
-- 加载数据
load data local inpath '/export/datas/dept.txt' into table tb_dept;

• 第二种方式：子查询方式
  应用场景：用于保存SQL语句的结果

create table tbname as select ……

功能：可以将一条SQL语句的结果保存到一张不存在的表中
需求：统计每个部门的平均薪资、最高薪资、最低薪资，将结果进行保存
实现：

create table deptsal 
as select deptno,round(avg(salary),2) as avgsal ,
max(salary) as maxsal ,
min(salary) as minsal 
from tb_emp 
group by deptno;

• 第三种方式：复制表结构不会复制数据，只复制了表结构

create  table tb1  like tb2;

创建tb1这张新表，表的结构与tb2一致tb2必须已存在
这种方式一般很少用
测试

create table deptsalary like deptsal;

2、DML：数据操作语言

2.1load：用于将文件关联到表中

语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;

• LOCAL：表示加载本地文件到表中

  • 它会将本地文件，复制一份到HDFS对应的表的目录下
  • 不加LOCAL：加载一个HDFS文件，移动到表的目录下

• INSERT OVERWRITE：表示覆盖原来表中所有的数据

• 第一种用法：将Select语句的结果写入一张已存在的表中

-- 将SQL语句的结果覆盖到已存在的表中
INSERT OVERWRITE TABLE tablename1 select statement1 FROM from_statement;

--#将SQL语句的结果追加到已存在的表中
INSERT INTO TABLE tablename1 select statement1 FROM from_statement;

与create table tbname as 的区别，将SQL语句的结果保存到一张不存在的表中
测试

insert into table deptsalary select * from deptsal limit 2;
insert overwrite table deptsalary select * from deptsal limit 2;

• 第二种用法：将Select语句的结果保存为文件

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select statement1 FROM from_statement;

LOCAL：将结果保存为本地文件
测试：将SQL语句结果保存到本地文件

insert overwrite local directory '/export/datas/insert/local' select * from deptsal;
insert overwrite local directory '/export/datas/insert/local-split' row format delimited fields terminated by '\t' select * from deptsal;

将SQL语句结果保存到HDFS

insert overwrite directory  select * from deptsal;

2.2update：更新

Hive支持类似于Mysql中的操作
语法：

UPDATE tablename SET column = value [, column = value ...] [WHERE expression]

2.3delete：删除

Hive支持类似于Mysql中的操作
语法：

DELETE FROM tablename [WHERE expression]

2.4一般不用update和delete、insert的行级插入

• update：在大数据中不会对数据更新，数据的价值是真实的
• delete：在大数据中数据只要产生就代表存在，一般不会删除
• insert行级插入：大数据当中不会用这种方式

3、DQL：数据查询语言

Select语句的语法与Mysql基本一致

select 1 from 2 where 3 group by 4 having 5 order by 6 limit 7

• 1-决定了结果有哪些列：要么是已存在的列，要么是函数生成的列，列的过滤
• 2-决定了读取数据的数据源
• 3-决定了对哪些行进行过滤
• 4-按照什么条件进行分组
• 5-分组以后对哪些行进行过滤
• 6-按照什么条件进行排序
• 7-限制输出

拿到任何一个SQL需求，分析的步骤

• step1：看结果，结果应该长什么样？
  • 决定了1这个位置会有哪些字段
• step2：数据从哪来
  • 结果是从单表还是多表获取
  • 决定了2的位置
• step3：有没有行的过滤的条件
  • 决定了3的位置
  • 分组之前就有的数据，直接用where过滤
• step4：有没有分组
  • 一般会出现每个、不同、每、各个这些关键字
• step5：分析分组以后的数据要不要过滤
  • 分组之后的数据有过滤就用having
  • 用于分组以后产生的数据过滤
• step6：分析有没有排序
  • 排序的字段是什么
  • 排序方式
• step7：是否限制输出的条数
  • 子查询：与Mysql一致