1-Hive--DDL操作
hql语句形式
-
DDL
-
库、表、视图 创建修改删除
-
create、alter、drop
-
-
DML
-
表的具体数据的事务操作
-
数据写入
-
数据的修改
-
数据的删除
-
-
-
DQL
-
数据查询
-
from、where、group by、having 、聚合、select、order by，limit
-
-
DCL
-
权限控制
-
grant
-
-
用户创建修改
-
重点 创建数据操作和查询数据操作
HQL建表
-
基本建表语句
create table tb_name( id int comment '订单id', total_price int )
hive表的字段类型
-
原生数据类型
-
数值型
-
int
-
float
-
-
字符型
-
string
-
-
日期型
-
date
-
-
布尔型
-
bool
-
-
-
复杂数据数据类型
-
array 数组 []
-
map key-value
-
struct 结构体 { int ，string array}
-
联合体
-
数据类型转换
-
隐式转换
-
显示转换 cast()
hive文件的读写
hadoo中的文件数据和hvie的表之间的关系
读文件 -- 将文件数据映射到表上---反序列化
zhangsan,lisi,wangwu
-
将sql语句转化为mp程序，找到对应的数据文件，按照映射表指定的格式去切割获取数据，然后将数据安装指定的字段形式进行返回
写文件--将表上的数据写入文件 -- 序列化
-
按照指定的的格式将数据写入文件
数据格式规范指定
规范在读写数据过程中按照指定格式操作数据
-
字段间格式
-
张三 王五
-
-
集合元素之间的
-
zhangsan,gender:boy-age:18
-
-
map映射
Hive使用流程
先创建表，指定数据的处理形式后才能进行相应的数据操作
-
创建表之后，在hdfs上会表名作为目录方便后续上传数据到表中
-
表的数据是在HDFS上存储，表的元数据实在MySQL在中存储 hive-site.xml
保存数据库名
| ID | NAME | |
|---|---|---|
| 1 | itcast | |
| 2 | python | |
保存所有表的名字
| NAME | ID_PK | |
|---|---|---|
| tb_name | 1 | |
| tb_archer | 1 | |
hive表的类型
-
内部表
-
一般是数据文件还没有上传仓库，需要提前创建表目录，让后将数据传入对应的表目录中
-
默认情况下在没有指定external关键词情况下创建的都是内部表
-
内部表管理元数据和表数据--一旦删除表之后，元数据和表数据全部清空
-
-
外部表
-
数据已经存在，对存在数仓上的数据建表后进行操作，由于存储位置不一致默认的存储位置，需要使用location指定数据存储位置路径
-
外部表创建的关键词是external
-
外部表只管理元数据，删除外部表不会把hdfs上的数据删除，只是把元数据删除
-
-- 内部表 create table student( id int, name string, sex string, age int , dept string )row format delimited fields terminated by ','; -- 查询 select * from student; -- 删除 drop table student; -- 外部表创建 location指定数据的位置 external创建外部表的关键 create external table student_ext( id int, name string, sex string, age int , dept string )row format delimited fields terminated by ',' location '/stu'; select * from student_ext; -- 删除 drop table student_ext;
分区表
分区可以将多个文件划分成不同文件目录，在进行查询是可以指定对应目录，直接到对应目录下完成数据的查询操做
-
partition by(分区字段，字段类型) 创建分区的关键词
-
分区字段是虚拟字段不能和已经定义字段重复
分区表导入数据，根据导入数据方式不同，分区表可以分为静态表和动态表
-
静态表数据导入
-
load add local inpath '路径--服务器上文件路径' into table 表名 partition(分区字段=‘名称’)
-
静态导入需要手动的对数据进行分类导入
-
-
动态表导入数据
-
inster into table 表名字 partition(分区字段) select * from tmp_table
-
hive根据指定的数据自动进行分区，生成对应的分区目录和数据
-
分桶表
字段层面对数据划分，划分的结果比分区表更加平均
根据字段的hash值除以指定分桶数量，然后对结果取余数，把余数相同的数据放在一起
-
CLUSTERED BY(state) sorted by (cases desc) INTO 5 BUCKETS;
-Hive--DQL
内置运算符
-
查看函数
show functions; desc functions extended +;
-
关系运算符
-
大小比较
select 1>1; select 1=1; select 1>=1;
-
空值判断
select 'asd' is null; select 'asd' is not null;
-
模糊查询
select 'itcast' like 'it____'; _代表匹配一个字符 select 'itcast' like 'it%'; _代表匹配任意个数字符
-
正则查询
select 'itcast' rlike 'it.'; select 'itcast' regexp 'it.';
-
-
算数运算符
-
加减乘除
select 1+1; select 1/2; select 1/0;
-
取整取余
select 3 div 2; select 4 % 2;
-
位运算
0 0000 1 0001 2 0010 3 0011 4 0100 5 0101 6 0110 select 4 & 6; 0100 * 0110 0100 select 4 | 6 0100 | 0110 只要有一个位数是1 该位置上的数据就是1
-
-
逻辑运算符
-
与或非
select 1>1 and 1=1; and 所有条件成立 select 1>1 or 1=1; or 一个条件成则返回true select !1>1; 取反
-
在范围内
select 1 in (2,3); select 1 not in (2,3);
-
内置函数
-
字符串函数
-- 字符串长度函数
select length('asd');
-- 字符串反转函数
select reverse('asd');
-- 字符串连接函数
select concat('asd','zxc');
-- 分隔符字符串连接函数
select concat_ws('-','asd','zxc','aaa')
-- 字符串截取函数
select substr('itcast',2,2); -- 第一个截取字符的位置 第二个截取字符的个数
select substr('itcast',-2)
-- 字符串转大写函数
select upper('asd');
select lower('ASD');
-- 去空格函数
select trim(' aaa ');
select ltrim(' aaaa ');
select rtrim(' aaaa ');
-- 正则表达式替换函数
select regexp_replace('asd123','(\\w+)\d','aaa')
-- URL解析函数
select parse_url('http://www.baidu.com','HOST');
-- 割字符串函数
select split('asd,aaa',',')
-
时间日期函数
-- 获取当前日期
select `current_date`();
-- 获取当前时间戳
select `current_timestamp`();
-- UNIX时间戳转日期函数
select from_unixtime(1634528834);
-- 获取当前UNIX时间戳函数
select unix_timestamp();
-- 日期转UNIX时间戳函数
select unix_timestamp('2021-10-10 10:10:10');
-- 抽取日期函数
select to_date('2021-10-10 10:10:10');
-- 日期转年函数
select year('2021-10-10 10:10:10');
-- 日期比较函数
select datediff('2021-10-10 10:10:10','2021-10-12 10:10:10');
-- 日期增加函数
select date_add('2021-10-10 10:10:10',5);
select date_sub('2021-10-10 10:10:10',5);
-
数学函数
-- 取整函数 select round(3.1415); -- 指定精度取整函数 select round(3.1415，2); -- 向下取整函数 select `floor`(-1.1); -- 向上取整函数 select ceil(1.1); -- 进制转换函数 select conv() -- 取随机数函数 select round(rand(3)*100); -- 绝对值函数 select abs(-10)
-
集合函数
-- 集合元素数
select size(`array`('a','n','b'));
-- 取map集合keys函数
select map_keys(`map`('name','python','age',19));
-- 取map集合values函数
select map_values(`map`('name','python','age',19));
-- 判断数组是否包含指定元素
select array_contains(`array`(1,2,3),4);
select 1 in (1,2,3);
-- 数组排序函数
select sort_array(`array`(1,5,3,2));
select sort_array(`array`('g','a','f','c'));
-
条件函数
-- if条件判断 -- 空判断函数 -- 非空判断函数 -- 空值转换函数 -- 非空查找函数 select coalesce(null,null,2); -- 条件转换函数
-
类型转换
select cast('12' as int);
-
数据脱敏
select mask('asd123ZXC');
select mask('asd123ZXC','*','@','#');
-- 对前n个进行脱敏替换
select mask_first_n('asd123',3);
select mask_last_n('itcastpython',3);
-- 除了前n个字符，其余进行掩码处理
select mask_show_first_n('asdzxc',2);
select mask_show_last_n('qwertyu',2);
-- 返回字符串的hash编码。xxxxxxxxxx select mask('asd123ZXC');select mask('asd123ZXC','*','@','#');--对前n个进行脱敏替换select mask_first_n('asd123',3);select mask_last_n('itcastpython',3);--除了前n个字符，其余进行掩码处理select mask_show_first_n('asdzxc',2);select mask_show_last_n('qwertyu',2);--返回字符串的hash编码。-- 脱敏--对前n个进行脱敏替换--除了前n个字符，其余进行掩码处理--返回字符串的hash编码。
-
其他
-- 反射函数
select reflect("java.lang.Math","max",11,22);
-- 取哈希值函数
-- SHA-1加密
-- MD5加密
自定义函数（UDF）（了解）
-
UDF 普通函数 输入一行输出一行
-
UDAF 聚合函数 输入多行输出一行
-
UDTF 输入一行输出多行
Hive函数进阶
-
explode（UDTF）
-
NBA总冠军球队
create table the_nba_championship( team_name string, champion_year array<string> ) row format delimited fields terminated by ',' collection items terminated by '|'; -- 使用explode函数对champion_year进行拆分
-
-
lateral View
-
行列转换 concat
列比较
select count(case when gender='男' then id end) as '男'，count(case when gender='女' then id end) as '女' from students
男 女 30 40 行比较
select gender,count(id) as count from student group by gender
GENDER COUNT 男 30 女 40 -
行转列
create table row2col2( col1 string, col2 string, col3 int )row format delimited fields terminated by '\t';
-
列转行
create table col2row2( col1 string, col2 string, col3 string )row format delimited fields terminated by '\t';
-
-
json数据处理
-
单个字段
create table tb_json_test1 ( json string ); get_json_object(json,'$.deviceType')
-
多个字段
create table tb_json_test2 ( device string, deviceType string, signal double, `time` string ) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' STORED AS TEXTFILE; json_tuple(json,'device','deviceType')
-
serder
create table tb_json_test2 ( device string, deviceType string, signal double, `time` string ) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' STORED AS TEXTFILE;
-- 拓展--xml数据处理 desc function extended xpath; SELECT xpath('<a><b>b1</b><b>b2</b><b>b3</b><c>c1</c><c>c2</c></a>', 'a/b/text()') -
窗口函数
聚合函数、分析函数、排序函数 over(partition by 字段1... order by 字段1... rows 范围)
-
建表
---建表并且加载数据
create table website_pv_info(
cookieid string,
createtime string, --day
pv int
) row format delimited
fields terminated by ',';
create table website_url_info (
cookieid string,
createtime string, --访问时间
url string --访问页面
) row format delimited
fields terminated by ','
-
求出每个用户总pv数sum+group by普通常规聚合操作
select cookieid,sum(pv) from website_pv_info group by cookieid;
-
求出网站总的pv数 所有用户所有访问加起来
-
求出每个用户总pv数
select cookieid,sum(pv) over(partition by cookieid) from website_pv_info;
-
求出每个用户截止到当天，累积的总pv数
-- 求出每个用户截止到当天，累积的总pv数 between unbounded preceding and current row 等价 unbounded preceding -- order by 默认有一个计算范围 unbounded preceding select cookieid,sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row ) from website_pv_info; select cookieid,sum(pv) over(partition by cookieid order by createtime rows unbounded preceding) from website_pv_info; select cookieid,sum(pv) over(partition by cookieid order by createtime ) from website_pv_info;
-
自定义窗口范围
unbounded preceding
-
找出每个用户访问pv最多的Top3重复并列的不考虑
-- 排序 rank 遇到重复会生成不连续的序号 dense_rank连续序号 row_number生成行号 select *,rank() over (partition by cookieid order by pv desc ) as rank_num from website_pv_info; select *,dense_rank() over (partition by cookieid order by pv desc ) as rank_num from website_pv_info; select *,row_number() over (partition by cookieid order by pv desc ) as rank_num from website_pv_info; select *,ntile(3) over (partition by cookieid order by pv desc ) as rank_num from website_pv_info; with tb1 as ( select *,dense_rank() over (partition by cookieid order by pv desc ) as rank_num from website_pv_info ) from tb1 select * where rank_num <= 3;
-
统计每个用户pv数最多的前3分之1天
with tb1 as (select *,ntile(3) over (partition by cookieid order by pv desc ) as rank_num from website_pv_info) from tb1 select * where rank_num = 1;
-
分析函数
select *,lag(pv) over (partition by cookieid order by pv desc ) as rank_num from website_pv_info; -- 向下取值 select *,lead(pv) over (partition by cookieid order by pv desc ) as rank_num from website_pv_info; -- 取第一个值 select *,first_value(pv) over (partition by cookieid order by pv desc ) as rank_num from website_pv_info; -- 去最后一个值 注意取值范围 因为orderby 所用取值范围需要增加向下的范围 select *,last_value(pv) over (partition by cookieid order by pv desc rows between unbounded preceding and unbounded following) as rank_num from website_pv_info; select sum(pv) sum(sum(pv)) over() from website_pv_info
数据压缩
-
压缩算法
-
map压缩
1）开启hive中间传输数据压缩功能 set hive.exec.compress.intermediate=true; 2）开启mapreduce中map输出压缩功能 set mapreduce.map.output.compress=true; 3）设置mapreduce中map输出数据的压缩方式 Set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec;
-
reduce压缩
1）开启hive最终输出数据压缩功能 set hive.exec.compress.output=true; 2）开启mapreduce最终输出数据压缩 set mapreduce.output.fileoutputformat.compress=true; 3）设置mapreduce最终数据输出压缩方式 set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec; 4）设置mapreduce最终数据输出压缩为块压缩 set mapreduce.output.fileoutputformat.compress.type=BLOCK;
数据存储格式
-
行存储和列存储
-
TextFILE
-
ORC
-
PAROUET
-
格式对比
TEXTfile
create table log_text ( track_time string, url string, session_id string, referer string, ip string, end_user_id string, city_id string )ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE ;
orc
create table log_orc( track_time string, url string, session_id string, referer string, ip string, end_user_id string, city_id string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS orc ;
PARQUET
create table log_parquet( track_time string, url string, session_id string, referer string, ip string, end_user_id string, city_id string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS PARQUET ;
-
查询速度对比
扫一扫
1157
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
