一、Hive SQL DML语句
1、加载数据
官方推荐加载数据方式:清洗数据 成为结构化文件,在使用LOAD语法加载数据到表中。
注意:使用前先到目标数据库下,不知道自己在哪个数据库下,可以使用
select current_database(); -- 查询当前数据库
1)load加载(官方推荐)
将数据文件移动到与Hive表对应的位置,移动时是复制、移动操作。
纯复制、移动指在数据load加载到表中时,HIve不会对表中的数据内容进行任何转换、任何操作。
语法规则:
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename;
其中LOCAL是指:Hiveserver2服务所在机器的本地Linux文件系统,不是Hive客户端所在的本地文件系统。 不写则默认是HDFS中加载
示例:加载student.txt
本地加载数据
create table student_local(
num int,
name string,
sex string,
age int,
dept string)
row format delimated fields terminated by ",";
加载本地数据(日志中可以看到)
LOAD DATA LOCAL INPATH '/root/hivedata/student.txt' INTO TABLE student_local;
HDFS加载数据
create table student_HDFS(
num int,
name string,
sex string,
age int,
dept string)
row format delimated fields terminated by ",";
加载HDFS数据(日志中可以看到)
LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS;
2)insert插入数据(比较慢,会涉及MR)
常用:将查询返回结果插入到另一张表中(insert+select)
常用要求:保证查询结果列的数目和需要插入数据表格的列数目一致
查询出来的数据类型和插入表格对应的列数据类型不一致,将会进行转换,但是不能保证转换一定成功,转换失败的数据将会为Null。
语法:INSERT INTO TABLE tablename select_statement1 FROM from_statement;
示例:创建一张目标表,只有俩个字段
create table student-from_insert(sno int,sname strring);
使用insert+select插入数据到新表中
insert into table student_from_insert select num,name from student;
查看新表
select * from student_from_insert;
3)直接在HDFS页面Web UI上上传
4)使用hadoop fs -put |-mv等翁氏直接将数据移动到表文件夹下
2、查询数据
1)select 语法介绍
(表名列名不区分大小写)
示例:有一个感染情况的数据文件。
创表
加载
简单查询
2)select_expr、ALL、 DISTINCT结果返回与去重
select_expr:介于select和from之间,也就是需要查的对象
select */all from 表名; --查询所有字段或指定字段
select 字段1,字段n from 表名; --查询字段。
ALL、 DISTINCT
去重结果:(上部分原数据,下部分为去重结果)
外加一个 a WWW
3)where 过滤
where后面是一个布尔表达式,(结果要么为TRUE,要么为FALSE),用于查询过滤,当布尔表达式为true时,返回select后面expr表达式的结果,否则返回空。
在where表达式中,可以使用Hive支持的任何函数和运算符,当聚合函数除外。
4)聚合操作
sql中拥有很多可用于计数和计算的内建函数,其中使用的语法是:
select function(列) from 表
聚合操作函数:如:count、sum、max、min、avg等函数。
聚合函数特点:不管原数据有多少,经过聚合后只返回一条数据,返回的数据就是聚合结果。
示例:
5)GROUP BY 分组
根据一个或多个列对结果集进行分组,没有group by 语法,则表中的所有行数据当成一组
group by 语法限制:
出现在group by 中的select_expr字段,要么是group by分组的字段,要么是被聚合函数应用的字段。
原因:避免出现一个字段多个值的歧义。
select_expr中出现的:因为就是基于该字段分组的,同一组中必相同
被聚合函数应用的字段,也没歧义,因为聚合函数的本质就是多进溢出,最终返回一个结果。
示例:
错误示例:
select count(score)as count_score, age from student where class=3 group by score;
原因:多了age,既不是group by分组字段,也不是聚合函数字段(没人在背后支持它,所以它不能被选中)
正确示范:本处是做聚合函数,也可使用group by中添加。
select count(score)as count_score, avg(age) from student where class=3 group by score;
6)HAVING 分组后过滤
HAVING 子句可以让我们筛选分组后的各组数据,并且可以在Having中使用聚合函数,因此此时where,group by已经执行结束,结果集已经确定。
在sql中增加having子句原因是,where关键字无法与聚合函数一起使用。
7)ORDER BY 排序
order by :根据指定的列对结果集进行排序,默认asc(升序,也就是越来越大),desc(降序 越来越小)
8)LIMIT 返回条数限制
limit用于限制select语句返回的行数。接受1-2个数据参数,必须是非负整数常量。
第一个参数是返回第一行的偏移量,第二个参数指定要返回的最大行数。一个数字时,代表最大行数,并且偏移量默认为0。
执行优先级:
from>where>group(包含聚合)>having>order>select
聚合语句>having子句
where子句在查询过程中>聚合函数
注意事项:where 执行在聚合函数之前,having在聚合函数之后,group by 执行顺序是在select之前,所以group by中不能有select后面的字段名
3、Join关联查询
背景:当不止一张表时,但表之间又有关联字段时。
join语法出现:根据两个或多个表中的列之间的关系,从这些表中共同组合查询数据
1)内连接:
inner join (交集),inner可以不写,默认为inner join。也就是都匹配部分才能保留。
(两表中相同的字段为id,故on e.id =e_a.id)
等价于:
2)左连接:
left outer join 左外连接或者左连接,,以左边数据为准,关联右边,有则返回,无则显示null。
示例:
二、函数使用
1、函数概述和分类标准
为了提高sql编写率,有内置函数。分内置函数、用户自定义函数UDF
内置函数可分为:数值类型函数、日期类型函数、字符串类型函数、几何函数、条件函数等;
用户自定义函数根据输入输出的函数可分为3类:UDF、UDAF、UDTF。
show functions可以查看可用的所有函数,
describe function extended funcname 查看函数的使用方式
2、常用内置函数
hive开发实现好,
字符串函数:
>>>6
>>>tsacti
>>>anglela baby
>>>www.itcast.cn
>>>by (倒着为-1开始,取右边的)
>>>ng (正着来是1开始,取俩个)
>>>apache
>>>
>>>
日期函数
时间戳:1970-01-01 00:00:00开始,每过一秒,加一。
注意时间戳的格式
数学函数:
条件函数:
if条件判断:if(boolean testCondition, T valueTrue,T valueFalseOrNull)
select if(1=2,100,200) --如果1=2,返回100,否则返回200
select if(sex = ’男‘,’M‘,’W') from student limit 3; --如果性别为男,则返回M,反之则W
空值转换函数:nvl(T value,T default_value)
select nvl(”allen“,”itcast“); --如果不为空,则返回自己的,也就是allen,否则返回指定值itcast。
select nvl(null,”itcast“);
条件转换函数:CASE a when b THEN c [WHEN d THEN e] * [ELSE f] END
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end; --100是50则返回为tom,是100则返回mary,都不是则返回tim
select case sex when '男‘ then ’male‘ else ’female‘ end from student limit 3;
3、用户自定义函数UDF分类标准
UDF:普通函数,一进一出 (扩大到hive所有函数中,包含内置函数和用户自定义函数)
UDAF:聚合函数,多进一出(count)
UDTF:表生成函数,一进多出