修邦建设网站,网页设计师培训费,wordpress同步微信素材,网络营销的概念是谁提出来的回顾总结 hive表的类型 #xff1a;内部表和外部表 删除内部表会删除表的所有数据 删除外部表只会删除表的元数据#xff0c;hdfs上的行数据会保留 表的分区和分桶 本质都是对表数据的拆分存储 分区的方式 是通过创建不同的目录来拆分数据 #xff0c;根据数据本身的内容最为…回顾总结 hive表的类型 内部表和外部表 删除内部表会删除表的所有数据 删除外部表只会删除表的元数据hdfs上的行数据会保留 表的分区和分桶 本质都是对表数据的拆分存储 分区的方式 是通过创建不同的目录来拆分数据 根据数据本身的内容最为目录名 分桶的方式 是通过创建不同的文件来拆分数据 文件名时hash取余的名字 数据拆分后可以提升数据的查询效率 分桶还有特殊使用场景 分桶关联多张表 分桶随机采样 序列化 本质就是对hdfs上的文件数据进行读取和写入 可以通过row format delimited fields terminated by , 指定如何读取和写入hdfs上的字段数据 CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name(col_name data_type [COMMENT col_comment])[COMMENT table_comment]hive中的独有语法-- 分区字段指定分区字段是不能表字段重复[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]-- 分桶字段需要指定表中存在的字段[CLUSTERED BY (col_name, col_name, ...) INTO num_buckets BUCKETS] -- 指定分割符 默认的字段分割符\001[ROW FORMAT row_format] -- 指定表的存储目录位置 不指定默认是在对应的数据库目录下创建表目录[LOCATION hdfs_path]
一、内置函数 内置函数时hive中自带的函数方法用来对不同类型的字段数据进行操作 字符串数值条件判断 字符串方法 length
concat
concat_ws
substr
split
regexp_replace %
round()
ceil()
floor() if()
case when 条件1 then 结果when 条件2 then 结果when 条件3 then 结果else前面条件都不成返回的结果
end 字段名称 -- 查看hive中的所有内置函数
show functions;
-- 查看函数的具体用户
desc function extended 函数名; 1-1 日期类型操作
-- 获取当前日期
select current_date();
-- 获取当前日期时间
select current_timestamp();
-- 获取unix时间时间戳 从1970年1月1号0时0分0秒 到现在过去了多少秒
select unix_timestamp();
-- unix时间 和日期时间的转化
-- 日期时间转为unix
select unix_timestamp(2023-10-01 15:30:28);
-- 将unix时间转为日期时间
select from_unixtime(12390886789);
-- 年月日的取值
select year(2023-10-01 15:30:28);
select month(2023-10-01 15:30:28);
select day(2023-10-01 15:30:28);
select dayofmonth(2023-10-12 15:30:28);
select dayofweek(2023-10-12 15:30:28);
select hour(2023-10-12 15:30:28);
select minute(2023-10-12 15:30:28);
select second(2023-10-12 15:30:28);
-- 时间加减
select date_add(2023-10-12 15:30:28,5);
select date_add(2023-10-12 15:30:28,-5);
-- 比较时间相差多少天
select datediff(current_date(),2023-10-12);
1-2 类型转化
-- 字段类型不符合计算需求可以进行类型转化
-- 隐式转化 hive会自动判断进行转化数据然后计算
select 123456;
-- 手动指定转化
select cast(123 as int) cast(456 as int);
select * from itcast.tb_hero;
desc itcast.tb_hero;
-- 转化只是在计算时进行并不会改变字段本身类型
select cast(blood as bigint) from itcast.tb_hero;
1-3 字符串数据转jsonarraymap操作 josn字符串操作 数据是一个 {key:value} 格式 使用方法取值value
create table tb_order_detail(json_field string
);
select * from tb_order_detail;
-- 对字段中的json字符串数据进行取值按照key取value值
-- 方法一 get_json_object 每次只能取一个字段数据 可以向下一直取值
selectget_json_object(json_field,$.orderid) as orderid,get_json_object(json_field,$.total_price) as total_price,get_json_object(json_field,$.total_num) as total_num,get_json_object(json_field,$.goods) as goods,get_json_object(json_field,$.goods[0]) as good1,get_json_object(json_field,$.goods[0].name) as good1_name,get_json_object(json_field,$.goods[1]) as good2
from tb_order_detail;
-- json_tuple 一次取多个字段值,不能对嵌套数据往下取值
select json_tuple(json_field,orderid,total_price,total_num,goods) as(orderid,total_price,total_num,goods) from tb_order_detail
将字符串数据切割转为数组数据 create table tb_user(id int,name string,hobby string
)row format delimited fields terminated by ,;
select id,name,split(hobby,-) from tb_user; 将字符串数据切割转为map数据 -- 使用map方法
select map(name,张三,age,29);
create table tb_hero(id int,name string,blood int,skin string
)row format delimited fields terminated by ,;
-- 西部大镖客:288-大圣娶亲:888-全息碎片:0-至尊宝:888-地狱火:1688 -- {西部大镖客:288,大圣娶亲:88}
select id,name,blood,map(split(split(skin,-)[0],:)[0], cast(split(split(skin,-)[0],:)[1] as int),split(split(skin,-)[1],:)[0],cast(split(split(skin,-)[1],:)[1] as int))from tb_hero; mysql中的内置函数操作
select concat(name,sex) from member;
select concat_ws(,,name,sex) from member;
select substr(s_birth,1,4) from student;
select replace(s_birth,-,/) from student;
select round(3.1421,2);
select round(3.123);
select pow(2,3);
select current_timestamp;
select current_date;
select unix_timestamp();
select unix_timestamp(2023-10-10 10:10:10);
select from_unixtime(127381923);
select date_add(2023-10-10 10:10:10,interval 1 day);
select date_add(2023-10-10 10:10:10,interval 1 month );
select date_add(2023-10-10 10:10:10,interval 1 year );
select date_add(2023-10-10 10:10:10,interval -1 year );
select date_add(2023-10-10 10:10:10,interval -1 hour );
select timestampdiff(year ,2020-02-02,2023-02-02);
select timestampdiff(month ,2020-02-02,2023-02-02);
select timestampdiff(day ,2020-02-02,2023-02-02);
select timestampdiff(hour ,2020-02-02 16:23:12,2023-02-02 15:12:12);
select if(s_sex男,1,2),s_sex from student;
select *,casewhen year(s_birth) between 1980 and 1989 then 80后when year(s_birth) between 1990 and 1999 then 90后end
from student; 二、DQL的查询计算(掌握) 对表进行查询计算 select 字段 from 表;
select 字段1,字段2,字段3,常量值,内置函数计算 from tb
2-1 单表查询计算
where 的条件过滤
select 字段1,字段2,字段3,常量值,内置函数计算 from tb where 过滤条件 过滤条件条件成立的将对的行数据返回 比较大小 字段 数值 判断字段和数值是否相等 字段 数值 字段 数值 字段 数值 字段 数值 字段 ! 数值
-- 大小比较
-- 年龄大于19岁
select * from tb_stu where age 19;
-- 查询性别为女性的学生信息
select * from tb_stu where gender女;
-- 查询学科不是IS的学生信息
select * from tb_stu where cls !IS; 判断空值 字段 is null 字段为空 字段 is not null
-- 空值判断
insert into tb_stu values(9023,null,男,20,MA);
select * from tb_stu where name is not null;
select * from tb_stu where name is null;
select * from tb_stu where name !; -- 空字符过滤是会将null值一起过滤掉
select * from tb_stu where name ; -- 相等判断是,空字符是不会过滤出null值的 范围判断 字段 between 数值1 and 数值2 字段 数值 and 字段 数值 字段 in (数值1,数值2....) 字段的值等于任意一个值就返回结果
-- 范围判断
select * from tb_stu where age between 20 and 25;
select * from tb_stu where age in(19,22);
select * from tb_stu where age not in(19,22); 模糊查询 字段 like % _ 数据 % 可以匹配任意多个 _ 匹配任意一个字符 字段 rlink 正则表达式
create table tb_stu2(id int,name string,gender string,age int,cls string,email string
)row format delimited fields terminated by ,;
select * from tb_stu2;
-- like的模糊查询
-- 查询姓名为刘的学生
select * from tb_stu where name like 刘%; -- % 代表任意多个字符
-- 查询姓名为刘的学生 名字个数时2个字的
select * from tb_stu where name like 刘_;
select * from tb_stu where name like 刘__; -- 查询三个字的
-- rlike 的正则表达式
-- 表的是就是通过不同的符号来表示不同的数据进行匹配
-- \\d 匹配数据的表达式 \\w 匹配字符字母 \\s 匹配空格
select * from tb_stu2;
-- ^ 表是什么开头
select * from tb_stu2 where email rlike ^\\d; -- 表是以数字开头
select * from tb_stu2 where email rlike ^\\w;
select * from tb_stu2 where email rlike ^\\S;
-- ^[a-zA-Z0-9._%-][a-zA-Z0-9.-]\\.[a-zA-Z]{2,}$
select email,split(email,)[1] from tb_stu2;
select email,split(split(email,)[1],\\.)[0] from tb_stu2; 与或非 条件1 and 条件2 and 条件3 ... 多个条件都成立返回对应的行数据 条件1 or 条件2 or 条件3 ... 多个条件满足任意一个返回对应的行数据
-- 与 多个条件都成立
select * from tb_stu;
-- 查询性别为男性学科是is的
select * from tb_stu where gender男 and cls IS;
-- 查询性别为男性或学科是is的
select * from tb_stu where gender男 or cls IS;
聚合计算 sumcount
select * from tb_stu;
select sum(age) from tb_stu2;
select count(*) from tb_stu where name is not null;
select avg(age) from tb_stu2;
select max(age) from tb_stu;
select min(age) from tb_stu;
分组聚合 group by
select sum(age) from tb_stu group by gender;
select sum(age),gender from tb_stu group by gender; 注意分组后select 中不能出现非分组字段 分组后过滤 having
select sum(age),gender from tb_stu group by gender having sum(age) 200;
排序 order by 全局排序 select * from tb_stu order by age; -- 默认是升序 从小到大
select * from tb_stu order by age desc ; -- 降序 从大到小
分页 limit
-- 分页
select * from tb_stu limit 5;
select * from tb_stu limit 10,5; -- 页数 m 每页数量是n (m-1)*n,n 2-2 多表关联查询
join的列关联 内关联 找关联字段相同的数据 左关联 展示保留左边表的所有数据右边表有相同数据显示没有相同数据则为null 右关联 展示保留右边表的所有数据左边表有相同数据显示没有相同数据则为null
-- table1: 员工表
CREATE TABLE employee(id int,name string,deg string,salary int,dept string) row format delimited
fields terminated by ,;
-- table2:员工家庭住址信息表
CREATE TABLE employee_address (id int,hno string,street string,city string
) row format delimited
fields terminated by ,;
-- table3:员工联系方式信息表
CREATE TABLE employee_connection (id int,phno string,email string
) row format delimited
fields terminated by ,;
-- on 当成where使用进行条件顾虑
select * from employee t1 join employee_address t2 on t1.id t2.id and salary 30000;
select * from employee t1 left join employee_address t2 on t1.id t2.id;
select * from employee t1 right join employee_address t2 on t1.id t2.id;
-- 实现内关联的效果
select * from employee,employee_address where employee.id employee_address.id;
union的行关联 将select查询计算后的结果表合并 -- union合并
select tb_stu,count(*) from tb_stu where name is not null
union
select tb_stu2, count(*) from tb_stu2 where name is not null;-- 保留重复数据
select id,name from tb_stu
union all
select id,name from tb_stu2;三、窗口聚合 默认没有窗口函数进行计算时全表数据获取计算 根据指定的窗口范围计算数据将计算结果单独呈现一列展示不会因为聚合改变行数 聚合使用取值函数排序函数 over(partition by 分组字段 order by 排序字段 rows between 起始行 and 结束行)
rows 指定计算的行范围3-1 聚合窗口
-- 按照性别分组统计年龄和
select sid,sname,age,gander,province,tuition,sum(age) over(partition by gander) from stu;
select sid,sname,age,gander,province,tuition,sum(age) over(partition by province,gander) from stu;
-- order by 排序后会将前面的数据进行累加
select sid,sname,age,gander,province,tuition,sum(tuition) over(partition by gander order by age) from stu;
select sid,sname,age,gander,province,tuition,avg(tuition) over(partition by gander order by age) from stu;3-2 取值窗口
-- 向上一行取值
select empno,ename,lag(ename) over() from emp;
-- 向上两行行取值
select empno,ename,lag(ename,2) over() from emp;
-- 去不到值给默认值
select empno,ename,lag(ename,2,itcast) over() from emp;
-- 向下一行取值
select empno,ename,lead(ename) over() from emp;
-- 向下两行行取值
select empno,ename,lead(ename,2) over() from emp;
-- 去不到值给默认值
select empno,ename,lead(ename,2,itcast) over() from emp;-- 分组后只进行组内查找
select *,lag(ename) over(partition by job) from emp;-- 取第一个值
select *,first_value(ename)over(partition by job order by sal desc ) from emp;3-3 排序窗口 按照指定字段排序后生成序号 -- 排序
select *,rank() over (order by sal) as rnk, -- 如果有并列生成序号是不连续的dense_rank() over (order by sal) as dernk, -- 生成连续序号row_number() over (order by sal) as rw -- 生成行号
from emp;-- 查找薪资前三
select * from (
select *,dense_rank() over (order by sal desc ) as dernk -- 生成连续序号
from emp) tb1
where dernk 3;
