Hive语法
与关系型数据库的SQL 略有不同,但支持了绝大多数的语句如DDL、DML 以及常见的聚合函数、连接查询、条件查询
。HIVE不适合用于联机,也不提供实时查询功能。它最适合应用在基于大量不可变数据的批处理作业。
HIVE的特点:可伸缩(在Hadoop的集群上动态的添加设备),可扩展,容错,输入格式的松散耦合。
hive 默认分隔符
\n :分隔行【lines terminated by ‘\n’】
^A :分隔字段,显示编码用\001【fields terminated by ‘\001’】
^B :分隔复合类型的元素,显示编码用\002【collectionitems terminated by ‘\002’】
^C :分隔map元素的key和value,显示编码用\003【map keys terminated by ‘\003’】
DDL
Create/Drop/Alter/Use Database
创建数据库【Create Database】
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
实例
hive> create database if not exists wujiadong
> comment 'the first database'//说明信息
> location '/hive_test' //数据库在HDFS上存放的位置
> with dbproperties('creator'='wujiadong','data'='2016-11-13')
查看所有数据库【show databases】
hive> show databases;
hive> show databases like "w.*"; #正则匹配
删除数据库【Drop Database】
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
实例
hive> drop database wujiadong1;
hive> drop database if exists wujiadong1 cascade; #删除代表的数据库
修改数据库【Alter Database】
用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值,来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的,包括数据库名和数据库所在的目录位置。
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;
实例
hive> alter database wujiadong set dbproperties ('edit_by'='wjd'); 修改数据库属性,但不能删除或重置
使用数据库【Use Database】
USE database_name;
USE DEFAULT;
实例
hive> use wujiadong;
hive> use default;
Create/Drop/Truncate Table
创建表【create table】
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 DELIMITED row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
注释:
- CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常
- EXTERNAL关键字可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION),Hive 创建内部表时,会将数据 移动 到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径,不对数据的位置做任何改变。在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据
- COMMENT:为表和列添加注释。
- PARTITIONED BY创建分区表(此处定义的字段不能和定义表中的字段重复)
- CLUSTERED BY创建分桶表
- LIKE 允许用户复制现有的表结构,但是不复制数据
- ROW FORMAT DELIMITED :指定行列数据格式
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是Serialize/Deserilize的简称,目的是用于序列化和反序列化)或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED,将会使用自带的 SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的 SerDe,Hive通过 SerDe 确定表的具体的列的数据
- STORED AS 指定存储文件类型
SEQUENCEFILE|TEXTFILE|RCFILE
如果文件数据是纯文本,可以使用 STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCEFILE。
实例
hive> create table student(id int,name string) row format delimited fields terminated by '\t'; 创建表
hive> create table if not exists student1 like student; 创建一个和表一样模式的表
hive> create table if not exists mytable(sid int,sname string)
> row format delimited fields terminated by '\005'
> stored as textfile; 创建内部表
hive> create external table if not exists pageview(
> pageid int,
> page_url string comment 'the page url'
> )
> row format delimited fields terminated by ','
> location 'hdfs://192.168.220.144:9000/user/hive/warehouse'; 创建外部表
hive> create table student_p(id int,name string,sexex string,age int,dept string)
> partitioned by(part string)
> row format delimited fields terminated by ','
> stored as textfile; 创建分区表
查看表信息【desc】
hive> show tables;
hive> desc student;
hive> desc pageview.pageid; 查看表某列的信息
删除表【drop table】
hive> drop table if exists student;
Alter Table/Partition/Column
修改表格都用Alter
-
重命名
-
增加、修改、删除分区
-
修改列
-
修改列信息
-
增加列
-
删除或替换列
ALTER TABLE table_name RENAME TO new_table_name; 重命名 ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...) [ LOCATION 'location2/partition_col_value/partiton_col_value' ] //增加分区,创建外部表需要单独为外部表的分区键指定值和存储的位置 ALTER TABLE table_name DROP partition_spec, partition_spec,... 删除分区 ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION "new location"; 修改表或分区路径 ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) //增加列 ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name] //更新列
实例
hive> alter table student1 rename to student; 重命名
hive> alter table student_p add if not exists
> partition(part='a')
> partition(part='b'); 增加分区
hive> alter table student add if not exists
> partition(year=2011,month=1,day=1) location 'logs/2011/01/01'
> partition(year=2011,month=1,day=2) location 'logs/2011/01/02'; 增加分区
hive> alter table student_p drop partition(part='b'); 删除分区
hive> ALTER TABLE log_messages DROP IF EXISTS PARTITION (year=2011,month=1,day=4); 删除分区
hive> show partitions student_p; #显示表分区
hive> alter table student add columns (gender string comment 'student gender'); 增加列
hive> alter table student replace columns(id int, age int, name string); 替换所有字段
修改表属性
hive> ALTER TABLE log_messages SET TBLPROPERTIES(
> 'wujiadong');
修改存储属性
hive> ALTER TABLE log_messages
> PARTITION(year=2011,month=1,day=1)
> SET FILEFORMAT SEQUENCEFILE;
例:外部表
hive> CREATE EXTERNAL TABLE IF NOT EXISTS stocks5(
> home string,
> symbol string,
> ymd string,
> price_open float,
> price_high float,
> price_low float,
> price_close float,
> volume int,
> price_adj_close float
> )
> ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
> LOCATION '/data/stocks';
例:分区表
分区表实际上就是对应一个HDFS文件系统上的独立的文件夹,该文件夹下是该分区所有的数据文件。**Hive中的分区就是分目录,**把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区,这样的查询效率会提高很多。
1.引入分区表(需要根据日期对日志进行管理)
/user/hive/warehouse/log_partition/20170702/20170702.log
/user/hive/warehouse/log_partition/20170703/20170703.log
/user/hive/warehouse/log_partition/20170704/20170704.log
2.创建分区表语法
hive (default)> create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (month string)
row format delimited fields terminated by '\t';
3.加载数据到分区表中
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201709');
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201708');
hive (default)> load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201707’);
4.查询分区表中数据
单分区查询
hive (default)> select * from dept_partition where month=‘201709’;
多分区联合查询
hive (default)> select * from dept_partition where month='201709'
union
select * from dept_partition where month='201708'
union
select * from dept_partition where month='201707';
5.增加分区
创建单个分区
hive (default)> alter table dept_partition add partition(month='201706') ;
同时创建多个分区
hive (default)> alter table dept_partition add partition(month='201705') partition(month='201704');
6.删除分区
删除单个分区
hive (default)> alter table dept_partition drop partition (month='201704');
同时删除多个分区
hive (default)> alter table dept_partition drop partition (month='201705'), partition (month='201706');
7.查看分区表有多少分区
hive> show partitions dept_partition;
8.查看分区表结构
hive> desc formatted dept_partition;
# Partition Information
# col_name data_type comment
month string
自定义存储格式
hive> CREATE TABLE employees(
> name STRING,
> salary FLOAT,
> subordinates ARRAY<string>,
> deductions MAP<STRING,FLOAT>,
> address STRUCT<street:STRING,city:STRING,state:STRING,zip:INT>)
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '\001'
> COLLECTION ITEMS TERMINATED BY '\002'
> MAP KEYS TERMINATED BY '\003'
> LINES TERMINATED BY '\n'
> STORED AS TEXTFILE;
DML数据操作
Loading files into tables
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
注释
-
Load 操作只是单纯的复制/移动操作,将数据文件移动到 Hive 表对应的位置
-
filepath
相对路径,例如:project/data1
绝对路径,例如:/user/hive/project/data1
包含模式的完整 URI,列如:hdfs://namenode:9000/user/hive/project/data1 -
LOCAL关键字
如果指定了 LOCAL, load 命令会去查找本地文件系统
中的 filepath。(复制文件)
如果没有指定 LOCAL 关键字,则根据inpath中的uri 在HDFS
查找文件。(移动文件) -
OVERWRITE关键字
如果使用了 OVERWRITE关键字,则目标表(或者分区)中的内容会被删除,然后再将filepath指向的文件/目录中的内容添加到表/分区中
如果目标表(分区)已经有一个文件,并且文件名和 filepath 中的文件名冲突,那么现有的文件会被新文件所替代。
创建内部表students
hive> create table students(id int,name string,gender string)
> row format delimited fields terminated by ','
> stored as textfile;
创建外部表students1
hive> create external table students1(id int,name string,gender string)
> row format delimited fields terminated by ','
> stored as textfile;
本地导入数据
hive> load data local inpath "/root/hive_test/students.txt" into table students;
加入overwrite 会先删除之前存在的数据在插入数据
hive> load data local inpath "/root/hive_test/students.txt" overwrite into table students;
HDFS导入数据
[root@spark1 hive_test]# hadoop fs -put /root/hive_test/students.txt /hive_test/ #先将将文件上传到hdfs
hive> load data inpath '/hive_test/students.txt' into table students; 数据导入hive表中
hive> load data inpath '/hive_test/students.txt' into table students1; 数据导入外部表中
查询是否导入成功
hive> select * from students;
通过查询语句向表中插入数据(Insert)
1.创建一张分区表
hive (default)> create table student(id int, name string)
partitioned by (month string)
row format delimited fields terminated by '\t';
2.基本插入数据
hive (default)> insert into table student partition (month='201709') values(1,'wangwu');
3.基本模式插入(根据单张表查询结果)
hive (default)> insert overwrite table student partition(month='201708')
select id, name from student where month='201709';
4.多插入模式(根据多张表查询结果)
对source表一次查询产生多个不相交输出
hive (default)> from source
insert overwrite table student partition(month='201707')
select id, name where month='201709'
insert overwrite table student partition(month='201706')
select id, name where month='201709';
查询语句中创建表并加载数据(CTAS)
根据查询结果创建表(查询的结果会添加到新创建的表中)
create table if not exists student3
as select id, name from student;
创建表时通过Location指定加载数据路径
1.创建表,并指定在hdfs上的位置
hive (default)> create table if not exists student5(
id int, name string
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student5';
2.上传数据到hdfs上
hive (default)> dfs -put /opt/module/datas/student.txt
/user/hive/warehouse/student5;
3.查询数据
hive (default)> select * from student5;
数据导出
Insert导出
1.将查询的结果导出到本地
hive (default)> insert overwrite local directory '/opt/module/datas/export/student'
select * from student;
2.将查询的结果格式化导出到本地
hive(default)>insert overwrite local directory '/opt/module/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
3.将查询的结果导出到HDFS上(没有local)
hive (default)> insert overwrite directory '/user/atguigu/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
4 . Hadoop命令导出到本地
hive (default)> dfs -get /user/hive/warehouse/student/month=201709/000000_0
/opt/module/datas/export/student3.txt;
5 Hive Shell 命令导出
基本语法:(hive -f/-e 执行语句或者脚本 > file)
[atguigu@hadoop102 hive]$ bin/hive -e 'select * from default.student;' >
/opt/module/datas/export/student4.txt;
6 . Export导出到HDFS上
(defahiveult)> export table default.student to
'/user/hive/warehouse/export/student';
数据查询
[WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available
starting with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]
基本查询(Select…From)
全表和特定列查询
1.全表查询
hive (default)> select * from emp;
2.选择特定列查询
hive (default)> select empno, ename from emp;
注意:
(1)SQL 语言大小写不敏感。
(2)SQL 可以写在一行或者多行
(3)关键字不能被缩写也不能分行
(4)各子句一般要分行写。
(5)使用缩进提高语句的可读性。
列别名
1.重命名一个列
2.便于计算
3.紧跟列名,也可以在列名和别名之间加入关键字‘AS’
4.案例实操
查询名称和部门
hive (default)> select ename AS name, deptno dn from emp;
Limit语句
典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。
hive (default)> select * from emp limit 5;
Where语句
1.使用WHERE子句,将不满足条件的行过滤掉
2.WHERE子句紧随FROM子句
3.案例实操
查询出薪水大于1000的所有员工
hive (default)> select * from emp where sal >1000;
(1)查询出薪水等于5000的所有员工
hive (default)> select * from emp where sal =5000;
(2)查询工资在500到1000的员工信息
hive (default)> select * from emp where sal between 500 and 1000;
(3)查询comm为空的所有员工信息
hive (default)> select * from emp where comm is null;
(4)查询工资是1500或5000的员工信息
hive (default)> select * from emp where sal IN (1500, 5000);
Like和RLike
1)使用LIKE运算选择类似的值
2)选择条件可以包含字符或数字:
% 代表零个或多个字符(任意个字符)。
_ 代表一个字符。
3)RLIKE子句是Hive中这个功能的一个扩展,其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。
4)案例实操
(1)查找以2开头薪水的员工信息
hive (default)> select * from emp where sal LIKE '2%';
(2)查找第二个数值为2的薪水的员工信息
hive (default)> select * from emp where sal LIKE '_2%';
(3)查找薪水中含有2的员工信息
hive (default)> select * from emp where sal RLIKE '[2]';
分组
Group By语句
GROUP BY语句通常会和聚合函数一起使用,先按照一个或者多个列队结果进行分组,然后对每个组执行聚合操作。
案例实操:
(1)计算emp表每个部门的平均工资
hive (default)> select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
(2)计算emp每个部门中每个岗位的最高薪水
hive (default)> select t.deptno, t.job, max(t.sal) max_sal from emp t group by
t.deptno, t.job;
Having语句
1.having与where不同点
(1)where针对表中的列发挥作用,查询数据;having针对查询结果中的列发挥作用,筛选数据。
(2)where后面不能写分组函数,而having后面可以使用分组函数。
(3)having只用于group by分组统计语句。
2.案例实操
求每个部门的平均工资
hive (default)> select deptno, avg(sal) from emp group by deptno;
求每个部门的平均薪水大于2000的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having
avg_sal > 2000;
Join语句
等值Join
Hive支持通常的SQL JOIN语句,但是只支持等值连接,不支持非等值连接。
案例实操
(1)根据员工表和部门表中的部门编号相等,查询员工编号、员工名称和部门名称;
hive (default)> select e.empno, e.ename, d.deptno, d.dname from emp e join dept d
on e.deptno = d.deptno;
表的别名
1.好处
(1)使用别名可以简化查询。
(2)使用表名前缀可以提高执行效率。
2.案例实操
合并员工表和部门表
hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno
= d.deptno;
内连接
内连接:只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
hive (default)> select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno
= d.deptno;
左外连接
JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。
hive (default)> select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno
= d.deptno;
右外连接
右外连接:JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。
hive (default)> select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno
= d.deptno;
满外连接
满外连接:将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话,那么就使用NULL值替代。
hive (default)> select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno
= d.deptno;
多表连接
注意:连接 n个表,至少需要n-1个连接条件。例如:连接三个表,至少需要两个连接条件。
排序
全局排序(Order By)
Order By:全局排序,一个Reducer
1.使用 ORDER BY 子句排序
ASC(ascend): 升序(默认)
DESC(descend): 降序
2.ORDER BY 子句在SELECT语句的结尾
3.案例实操
(1)查询员工信息按工资升序排列
hive (default)> select * from emp order by sal;
(2)查询员工信息按工资降序排列
hive (default)> select * from emp order by sal desc;
按照别名排序
按照员工薪水的2倍排序
hive (default)> select ename, sal*2 twosal from emp order by twosal;
多个列排序
按照部门和工资升序排序
hive (default)> select ename, deptno, sal from emp order by deptno, sal ;
每个MapReduce内部排序(Sort By)
Sort By:每个Reducer内部进行排序,对全局结果集来说不是排序。
1.设置reduce个数
hive (default)> set mapreduce.job.reduces=3;
2.查看设置reduce个数
hive (default)> set mapreduce.job.reduces;
3.根据部门编号降序查看员工信息
hive (default)> select * from emp sort by empno desc;
4.将查询结果导入到文件中(按照部门编号降序排序)
hive (default)> insert overwrite local directory '/opt/module/datas/sortby-result'
select * from emp sort by deptno desc;
分区排序(Distribute By)
Distribute By:类似MR中partition,进行分区,结合sort by使用。
注意,Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
对于distribute by进行测试,一定要分配多reduce进行处理,否则无法看到distribute by的效果。
案例实操:
(1)先按照部门编号分区,再按照员工编号降序排序。(使部门编号相同的数据进入同一个reducer)
hive (default)> set mapreduce.job.reduces=3;
hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;
Cluster By
当distribute by和sorts by字段相同时,可以使用cluster by方式。
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序,不能指定排序规则为ASC或者DESC。
1)以下两种写法等价
hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;
注意:按照部门编号分区,不一定就是固定死的数值,可以是20号和30号部门分到一个分区里面去。
分桶及抽样查询
select * from test tablesample (bucket 3 out of 10 on id)
表示根据id分10个桶取第三个桶。
分桶表数据存储
**分区针对的是数据的存储路径;分桶针对的是数据文件。**
分区提供一个隔离数据和优化查询的便利方式。不过,并非所有的数据集都可形成合理的分区,特别是之前所提到过的要确定合适的划分大小这个疑虑。
UNION ALL
主要用于多表合并,要求各select的字段一致
select r.id,r.price
from (
select m.id,m.price from monday m
union all
select t.id,t.price from tuesday t) r
Hive 函数
标准函数
聚合函数
1.求总行数(count)
hive (default)> select count(*) cnt from emp;
2.求工资的最大值(max)
hive (default)> select max(sal) max_sal from emp;
3.求工资的最小值(min)
hive (default)> select min(sal) min_sal from emp;
4.求工资的总和(sum)
hive (default)> select sum(sal) sum_sal from emp;
5.求工资的平均值(avg)
hive (default)> select avg(sal) avg_sal from emp;
表生成函数
用户自定义函数
UDF标准函数
作用于单个数据行,产生一个数据行输出。
继承UDF类,添加evaluate()方法
UDAF聚合函数
接受多个输入数据行,产生一个数据输出行
继承UDAF类,必须包含一个或多个实现了UDAFEvaluate接口的静态内部类,实现init、iterater、merge、terminatePartial、terminate 这5个方法
- init() 初始化函数,重设内部状态
- iterater:对每个新值聚集时候调用,接受的参数与Hive中被调用函数的参数一致
- terminatePartial:生成部分聚集结果,Map的输出
- merge:接受Map的输出进行聚集,reduce的输入
- terminate:最终输出结果
UDTF表生成函数
作用于单个数据行,产生多个数据行(一个表)输出
参考
https://blog.csdn.net/luanpeng825485697/article/details/80510916
https://www.cnblogs.com/wujiadong2014/p/6090372.html