山西省网站建设哪家好,开发公司对物业公司的补贴怎么开票,请人建网站需要多少钱,招标网站都有哪些一、Hive基本概念1.1 hive是什么hive是基于hadoop的一个数仓分析工具#xff0c;hive可以将hdfs上存储的结构化的数据#xff0c;映射成一张表#xff0c;然后让用户写HQL(类SQL)来分析数据tel up down
1383838438 1345 1567
138383…一、Hive基本概念1.1 hive是什么hive是基于hadoop的一个数仓分析工具hive可以将hdfs上存储的结构化的数据映射成一张表然后让用户写HQL(类SQL)来分析数据 tel up down
1383838438 1345 1567
1383838439 5345 1567
1383838440 1241 16577
1383838441 3453 15757
1383838434 35355 1567567按照手机号 分组统计每个手机号的总流量select tel,updown from test; hive的本质其实就是hadoop的一个客户端hive底层不存储任何数据hive表的数据存在hdfs上hive表的元数据存在关系型数据库中默认是derby我们不一般不用默认的derby来存一般都会修改为mysql。元数据描述数据的数据Hive其实就是将用户写的HQL给翻译成对应的mr模板然后执行这些mr程序hive底层执行引擎其实就是MapReducemr运行在yarn上1.2 hive的优缺点优点操作简单采用类sql的语法分析数据门槛低大大的降低了大数据分析的难度通用性高缺点不够灵活机翻粒度比较粗调优困难。因为底层执行引擎还是mr所以延迟较高不能像关系型数据库那样立马返回结果并且底层存储是hdfs不支持随机写只能追加所以hive不支持行级别的更新和删除delete 和 update1.3 hive的架构原理客户端命令行客户端jdbc客户端数据存储hdfs底层执行引擎mr元数据库hive将元数据默认存在derby中我们一般在安装hive的时候会修改成mysqldirver四个器解析器将hql语句转换成AST抽象语法树解析sql是否有误编译器将解析后的hql编译成逻辑执行计划暂时不执行优化器对逻辑计划进行优化调优执行器将优化后的逻辑计划执行其实就是翻译成对应的mr程序在yarn上运行1.4 hive和关系型数据库对比hive不是数据库不是数据库不是数据库hive除了查询语言HQL跟SQL很像之外别的跟数据库再也没有半点相似可言数据更新 数据规模 执行延迟 底层引擎 数据存储二、Hive安装2.1 hive访问1通过hive自带的beeline客户端访问beeline -u jdbc:hive2://hadoop102:10000 -n logcathive脚本访问hive2.2 hive交互命令交互命令使用场景在shell脚本里面不能人为的进入hive客户端交互写sql所以要通过hive -e或者-f两个交互参数进行写入1hive -ehive -e select * from student2hive -fhive -f stu.sql2.3 hive参数设置方式1 通过配置文件设置 永久生效在hive的家目录下面的conf文件夹下的hive-site.xml hive-env.sh hive-log4j2.properties2 通过命令行参数来设置 临时生效只针对当前客户端连接hive -hiveconf 参数名参数值beeline -u jdbc:hive2://hadoop102:10000 -n logcat -hiveconf 参数名参数值3 通过set命令设置临时生效只针对当前客户端连接我们连接到hive的客户端以后可以通过set语句来设置参数查看所有参数设置set;查看单个参数的值set 参数名;设置单个参数的值set 参数名参数值;参数设置优先级hive-default.xml hive-site.xml -hiveconf 参数名参数值 set 参数名参数值三、数据类型3.1 基本数据类型HIVEMySQLJAVA长度例子TINYINTTINYINTbyte1byte有符号整数2SMALINTSMALINTshort2byte有符号整数20INTINTint4byte有符号整数20BIGINTBIGINTlong8byte有符号整数20BOOLEAN无boolean布尔类型true或者falseTRUE FALSEFLOATFLOATfloat单精度浮点数3.14159DOUBLEDOUBLEdouble双精度浮点数3.14159STRINGVARCHARstring字符系列。可以指定字符集。可以使用单引号或者双引号。‘now is the time’ “for all good men”TIMESTAMPTIMESTAMP时间类型BINARYBINARY字节数组利用基本数据类型建表测试create table test(id int,weight double,name string,money bigint);利用insert语句按照指定的数据类型插入一条数据insert into test values(1001,75,zhangsan,1000000000000);3.2 集合数据类型数据类型描述语法示例STRUCT和c语言中的struct类似都可以通过“点”符号访问元素内容。例如如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。2struct() 例如 structstreet:string, city:stringMAPMAP是一组键-值对元组集合使用数组表示法可以访问数据。例如如果某个列的数据类型是MAP其中键-值对是’first’-’John’和’last’-’Doe’那么可以通过字段名[‘last’]获取最后一个元素map() 例如mapstring, intARRAY数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素每个数组元素都有一个编号编号从零开始。例如数组值为[‘John’, ‘Doe’]那么第2个元素可以通过数组名[1]进行引用。Array() 例如array测试数据songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing_10010
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing_10011在hive中建表描述上述数据create table person(
name string,
friends arraystring,
children mapstring,int,
address structstreet:string,city:string,email:int
)
row format delimited fields terminated by ,
collection items terminated by _
map keys terminated by :
lines terminated by \n;加载数据load data local inpath /opt/module/hive/datas/person.txt into table person;查询数据select * from person;查出来songsong 这个人的姓名第一个朋友孩子xiaoxiao song的年龄和他的邮编select name, friends[0],children[xiaoxiao song],address.email from person where name songsong;select name, friends[1],children[xiao song],address.street from person;3.3 类型转换1隐式自动类型转换1任何整数类型都可以隐式地转换为一个范围更广的类型如TINYINT可以转换成INTINT可以转换成BIGINT。2所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。3TINYINT、SMALLINT、INT都可以转换为FLOAT。4BOOLEAN类型不可以转换为任何其它的类型。2显示强制类型转换 CAST( vlaue AS type)例如select * from cast(1 as int) 2;四、DDL数据定义语言4.1 库的DDL1 创建数据库CREATE DATABASE [IF NOT EXISTS] database_name --指定数据库名称
[COMMENT database_comment] --指定数据库描述
[LOCATION hdfs_path] --指定创建的数据库在hdfs上存储的路径
[WITH DBPROPERTIES (property_nameproperty_value, ...)]; --指定库的一些属性案例实操create database if not exists db_hive
comment this is my first db
with dbproperties (namedb_hive,ownerlogcat);create database if not exists db_hive;--创建数据库并指定在hdfs上的路径
create database if not exists db_hive2
location /db_hive2;
2 查询数据库show databases3 查看数据库详情--简单查看
desc database 数据库名;
--详细查看 详细查看可以看到库的属性信息简单查看看不到
desc database extended 数据库名;4 切换数据库use 数据库名;5修改数据库只能修改数据库的属性信息别的都无法更改。例如库名库的存储位置等元数据信息无法更改alter database db_hive set dbproperties(createtime20200624);6删除数据库注意删除掉数据库以后hdfs上对应的目录也会删除谨慎操作。drop database 数据库名 cascade;--如果数据库不为空可以在最后加上cascade强制删除
drop database 数据库名 cascade;--为了更严谨我们可以在删除之前判断数据库是否存在
drop database if exists 数据库名 cascade;4.2表的DDL1创建表CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name --指定表名 【external 外部表/内部表】
[(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 --指定hive表在hdfs上存储的原始数据的格式[FIELDS TERMINATED BY char] --每行数据中字段的分隔符 ascII码表的第一个字符 ^A [COLLECTION ITEMS TERMINATED BY char] --集合元素分隔符 ascII码表的第二个字符 ^B[MAP KEYS TERMINATED BY char] --map集合中 key 和 value 的分隔符 ascII码表的第三个字符 ^C[LINES TERMINATED BY char] --每行数据的分隔符 默认值\n
]
[STORED AS file_format] --指定hive的数据在hdfs上存储的格式
[LOCATION hdfs_path] --指定hive数据在hdfs上存储的路径 默认值 /user/hive/warehouse/数据库名
[TBLPROPERTIES (property_nameproperty_value, ...)] --指定表的属性
[AS select_statement] --按照as后面的查询语句的结果来创建表复制表结构以及表数据
[LIKE table_name] --按照like后面的表结构来创建表只复制表结构不复制表数据2 管理表和外部表管理表内部表hive掌控者这个数据的生命周期如果删除一个管理表hdfs上存储的数据也跟着一起删除。所以一般我们创建管理表时一般不会再location表的存储路径就默认放在/user/hive/warehouse下外部表hive不完全掌控外部表的数据的生命周期删除外部表只删除hive表的元数据不会删除掉hdfs上存储的数据一般外部表都是先有的hdfs上的数据然后我们创建一个外部表手动指定这个外部表的存储路径3 创建管理表create table student(
id int,name string
)
row format delimited fields terminated by \t;create table student2(
id int,name string
);
--根据AS select语句查询结构创建表,复制表结构复制表数据
create table student3 as select * from student;--根据like 创建表只复制表结构不复制表数据
create table student4 like student;
查看表信息--简单查看表信息
desc 表名;--详细查看表信息
desc formatted 表名;删除管理表注意删除管理表同时会删除hdfs上对应目录的数据谨慎操作数据无价drop table student3;4 创建外部表注意外部表创建时要加external外部表的好处就是删除表的时候只删除表的元数据信息不删hdfs上存储的数据更安全。建表语句create external table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by \t
location /company/dept;create external table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
row format delimited fields terminated by \t
location /company/emp;5 外部表和内部表的转换通过设置表属性“EXTERNAL”来控制表是外部表还是内部表TRUE 是 外部表 FALSE 是内部表alter table student set tblproperties(EXTERNALTRUE/FALSE);6修改表重名表 注意重命名表会一起修改hdfs上对应的目录名前提这个表必须是管理表并且创建这个管理表时没有自定义locationALTER TABLE table_name RENAME TO new_table_name更新列更新列列名可以随意修改列的类型只能小改大不能大改小遵循自动转换规则ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]增加列ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...) 替换列ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 7清空表注意只能truncate 管理表外部表不能truncate。truncate的本质其实就是删除hdfs上对应路径的数据。truncate table 表名;五 DML数据操作5.1 导入5.1.1 load1 通过load命令加载数据load data [local] inpath 数据的path [overwrite] into table student [partition (partcol1val1,…)];2 通过本地方式导入注意通过本地路径load本质上其实就是将本地的文件put到hdfs对应的表目录load data local inpath /opt/module/hive/datas/student.txt overwrite into table student;3通过hdfs路径导入注意通过hdfs路径导入本质是将hdfs上路径的数据剪切到对应的表的hdfs路径load data inpath /student.txt into table student;4 覆盖导入注意:加上关键字overwrite 就是覆盖导入不加的话默认是追加导入load data local inpath /opt/module/hive/datas/student.txt overwrite into table student;5.1.2 insert1) 基本模式插入into是追加插入overwrite是覆盖插入此方式一般没人用insert into/overwrite table student values(1018,ss18),(1019,ss19);2通过查询结果插入此方式用的比较多一般都是查询原始表的数据到临时表注意select之前不能加as跟创建表时as select区分开insert into table student2 select * from student where id 1006;
insert overwrite table student2 select * from student where id 1006;注意通过inset插入数据数据格式和列的数量要一致才可以。5.1.3 建表语句后 as select注意建表语句后跟select语句时as不能省略跟上面的insert into刚好相反create table student3 as select * from student;5.1.4 建表指定location位置提前把数据传到hdfs上然后创建表的时候指定表的位置为数据的路径注意 location后面一定要给一个目录不能直接给文件路径。create table student5(
id int,name string
)
row format delimited fields terminated by \t
location /student;5.1.5 import导入注意使用import导入之前得使用export导出数据并且因为export会把数据和元数据一起导出所以我们使用import导入的时候表不能存在否则会元数据冲突报错。import table student6 from /stu;5.2 数据导出5.2.1 insert 导出无格式导出insert overwrite local directory /opt/module/hive/datas/export/student
select * from student;格式化导出insert overwrite local directory /opt/module/hive/datas/export/student2
ROW FORMAT DELIMITED FIELDS TERMINATED BY \t
select * from student;注意因为insert导出后面只能跟overwrite所以我们给路径的时候一定要具体防止hive误删重要文件、这个导出路径可以不存在hive会帮我们创建路径5.2.2hadoop命令导出hadoop dfs -get /user/hive/warehouse/student/student.txt
/opt/module/hive/datas/export/student3.txt;5.2.3hive shell 命令导出hive -e select * from db_hive.student /opt/module/hive/datas/stu.txt5.2.4 export 导出注意;export导出只能导出到hdfs上并且会将元数据和数据一起导出。export table db_hive.student to /stu;六 查询DQLSELECT [ALL | DISTINCT] select_expr, select_expr, ... --指定查询字段FROM table_reference --从哪个表查询[WHERE where_condition] --指定where过滤条件[GROUP BY col_list] --指定分组条件[ORDER BY col_list] --指定排序条件[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] --hive排序四个by][LIMIT number] --限制输出结果条数6.1 基本查询6.2 分组查询6.3 join关联查询6.4 排序1 全局排序 order byOrder By全局排序只有一个Reducer把所有数据都放在一个分区里面进行排序这样才能保证输出的结果集是全局有序的。ASCascend: 升序默认DESCdescend: 降序按照多个列双重排序--按照部门降序和工资升序排序
select deptno,sal,empno,ename,job from emp order by deptno DESC ,sal asc ;2 单个reducer排序 sort by注意因为单独使用sort by的话只能给每个分区内的数据排序但是不能指定分区内的数据都有哪些属于随机给数据分配分区因此没人会单独使用sort by来排序因为出来的结果集没有意义3 分区 distribute by指定分区字段一般在sort by之前都会加上 distribute by4 分区排序 Cluster bycluster by属于 distribute by sort by的结合但是前提是 分区字段和排序字段是同一个的时候才可以替换。并且排序只能升序排不能再指定desc和ascselect * from emp distribute by deptno sort by deptno;
--上面的sql可以简写成下面的
select * from emp cluster by deptno;七 分区表 分桶表7.1分区表hive存在问题hive里面没有索引机制每次查询的时候hive会暴力扫描整张表。分区表的本质就是分目录按照业务需求把数据分成多个目录存储然后查询的时候就可以通过where条件指定对应的分区创建分区表语法create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string)
row format delimited fields terminated by \t;分区字段属于分区表的一个伪列数据里面并没有记录这列的值分区字段的值体现在分区目录名上面。往分区表里正常load数据一定要指定分区load data local inpath /opt/module/hive/datas/dept_20200401.log into table dept_partition partition(day20200401);分区表查询数据select * from dept_partition where day 20200401;select * from dept_partition where day20200401
union
select * from dept_partition where day20200402
union
select * from dept_partition where day20200403;select * from dept_partition where day 20200401 or day 20200402;查看分区表有多少分区show partitions dept_partition;删除分区注意删除分区会一起删除掉分区内的数据alter table dept_partition drop partition (day__HIVE_DEFAULT_PARTITION__);
--删除多个分区 注意多个分区间必须有逗号没有会报错
alter table dept_partition drop partition(day20200405),partition(day20200406);增加分区 alter table dept_partition add partition(day20200404) ;--增加多个分区 注意多个分区间不能逗号有会报错alter table dept_partition add partition(day20200405) partition(day20200406);查看分区表信息desc formatted dept_partition;7.2 二级分区表创建二级分区表create table dept_partition2(
deptno int, dname string, loc string
)
partitioned by (day string, hour string)
row format delimited fields terminated by \t;给二级分区正常加载数据load data local inpath /opt/module/hive/datas/dept_20200401.log
into table dept_partition2
partition(day20200401,hour12);给二级分区增加分区alter table dept_partition2 add partition(day20200403,hour01) partition(day20200403,hour 02);给二级分区删除分区alter table dept_partition2 drop partition(day20200403,hour01),partition(day20200403,hour 02);分区表和元数据对应三种方式1 先上传 再修复表msck repair table dept_partition2;2 先上传数据然后手动添加分区3 直接load load数据的时候直接指定分区字段的值这个时候不仅会上传数据还会创建对应的分区7.3 分桶表创建分桶表create table stu_buck(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by \t;查看分桶表信息desc formatted stu_buck;八 函数查看系统自带函数show functions;查看函数具体用法desc function extended upper;1 空字段赋值 NVL将null值转换成我们想要的值select sal,comm,sal NVL(comm,0) money from emp;select ename,job,sal,mgr,comm,NVL(comm,mgr) from emp;2 CASE WHENnamedept_idsex悟空A男大海A男宋宋B男凤姐A女婷姐B女婷婷B女期望结果 求出不同部门男女各多少人dept_Id 男 女
A 2 1
B 1 2分析过程第一步先求出每个部门有多少人selectdept_id,sum(1)
from emp_sex
group by dept_id ;第二步求出每个部门男女各多少人selectdept_id,sum(case sex when 男 then 1 else 0 end) man,sum(case sex when 女 then 1 else 0 end) woman
from emp_sex
group by dept_id ;3 行转列 多行转一列相关函数CONCAT(string A/col, string B/col…)返回输入字符串连接后的结果支持任意个输入字符串;CONCAT_WS(separator, str1, str2,...)它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;注意CONCAT_WS must be string or array COLLECT_SET(col)函数只接受基本数据类型它的主要作用是将某字段的值进行去重汇总产生array类型字段。COLLECT_LIST(col)函数只接受基本数据类型它的主要作用是将某字段的值进行不去重汇总产生array类型字段。原始数据nameconstellationblood_type孙悟空白羊座A大海射手座A宋宋白羊座B猪八戒白羊座A凤姐射手座A苍老师白羊座B需求 把星座和血型一样的人归类到一起射手座,A 大海|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋|苍老师实现SELECT t1.c_b,concat_ws(|,COLLECT_LIST(t1.name))
FROM
(select name,concat_ws(,,constellation,blood_type) c_b from person_info
) t1
group by t1.c_b;4 列转行 一列转多行相关函数Split(str, separator)将字符串按照后面的分隔符切割转换成字符array。EXPLODE(col)将hive一列中复杂的array或者map结构拆分成多行。LATERAL VIEW用法LATERAL VIEW udtf(expression) tableAlias AS columnAlias解释lateral view用于和split, explode等UDTF一起使用它能够将一行数据拆成多行数据在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTFUTDF会把一行拆分成一或者多行lateral view再把结果组合产生一个支持别名表的虚拟表。原始数据moviecategory《疑犯追踪》悬疑,动作,科幻,剧情《Lie to me》悬疑,警匪,动作,心理,剧情《战狼2》战争,动作,灾难需求《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难实现 第一步炸开类型selectexplode(split(category,,))
from movie_info;实现第二步尝试使用join进行连接但是不行没有办法写join条件造成了笛卡尔积
SELECT t1.movie,t2.category_name
from
(select movie from movie_info
) t1
left join
(selectexplode(split(category,,)) category_namefrom movie_info
) t2;实现第三步我们的需求是·想让炸开后的临时表和炸开之前表所在行的其他字段进行joinhive帮我们实现了这个需求 lateral view 侧写视图selectmovie,category_name
from movie_info
LATERAL VIEW explode(split(category,,)) tmp as category_name;5 窗口函数1定义窗口函数属于sql中比较高级的函数mysql从8.0版本才支持窗口函数我们学的5.6,5.7都有窗口函数oracle 里面一直支持窗口函数hive也支持窗口函数以下函数才是窗口函数窗口函数LEAD LEAD(col,n, default_val)往后第n行数据 col 列名 n 往后第几行 默认为1 默认值 默认nullLAG LAG(col,n,default_val)往前第n行数据 col 列名 n 往前第几行 默认为1 默认值 默认nullFIRST_VALUE 在当前窗口下的第一个值 FIRST_VALUE col,true/false 如果设置为true则跳过空值。LAST_VALUE 在当前窗口下的最后一个值 LAST_VALUE col,true/false如果设置为true则跳过空值。标准聚合函数COUNTSUMMINMAXAVG分析排名函数RANKROW_NUMBERDENSE_RANKNTILE窗口函数函数窗口窗口函数在运算时我们可以指定函数运算的数据范围2语法窗口函数 over([partition by 字段] [order by 字段] [ 窗口语句])partition by 给查出来的结果集按照某个字段分区分区以后开窗的大小最大不会超过分区数据的大小一旦分区之后我们必须在单个分区内指定窗口。order by 给分区内的数据按照某个字段排序3) 窗口语句(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING两种特殊情况当指定ORDER BY缺少WINDOW子句时WINDOW规范默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。如果同时缺少ORDER BY和WINDOW子句则WINDOW规范默认为ROW BETWEENUND UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING。以下函数在over里面只能分区和排序不能自定义窗口大小了也就是不能再写window字句排序分析函数 都不能写 例如 Rank, NTile, DenseRank, CumeDist, PercentRank.Lead 和 Lag不能写窗口需求需求1 查询在2017年4月份购买过的顾客及总人数select name,count(1) over(rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING)
from business
where month(orderdate) 4
group by name;由于窗口语句有两种特殊情况我们这种刚好符合第二种因此可以省略掉窗口语句select name,count(1) over()
from business
where month(orderdate) 4
group by name;需求2 查询顾客的购买明细及月购买总额SELECT name,orderdate,cost,sum(cost ) over(partition by name,month(orderdate) )
from business;需求3 上述的场景, 将每个顾客的cost按照日期进行累加SELECT name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW) cost1,sum(cost) over(partition by name order by orderdate) cost2
from business;需求4 查询顾客购买明细以及上次的购买时间和下次购买时间selectname,orderdate,cost,LAG(orderdate,1,无) over(partition by name order by orderdate) prev_time,LEAD(orderdate,1,无) over(partition by name order by orderdate) next_time
from business;需求5 查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间注意LAST_VALUE和FIRST_VALUE 需要自定义windows字句否则出现错误selectname,orderdate,cost,FIRST_VALUE(orderdate) over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) first_time,LAST_VALUE(orderdate) over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) last_time
from business;需求6 查询前20%时间的订单信息selectt1.*
FROM
(selectname,orderdate,cost,ntile(5) over(order by orderdate ) nsortfrom business
) t1
where t1.nsort 1;6 排名行数RANK() 排序相同时会重复会跳号DENSE_RANK() 排序相同时会重复不会跳号ROW_NUMBER() 会根据顺序计算SELECT name,subject,score,rank() over(PARTITION by subject order by score desc) rp,DENSE_RANK() over(PARTITION by subject order by score desc) drp,ROW_NUMBER() over(PARTITION by subject order by score desc) rowp
from score;几个关键字总结1 建表 PARTITIONED BY(分区表) CLUSTERED BY分桶表2 查询ORDER BY(全局排序) SORT BY(区内排序)DITRIBUTE BY分区 CLUSTER BY分区排序3 窗口函数PARTITION BY(对数据分区) ORDER BY排序7自定义udf1 编写代码继承genericUDF类。实现里面的三个方法2 创建临时函数添加jar包的类路径给hive注意是临时生效add jar /opt/module/hive/datas/myudf.jar;创建临时函数create temporary function my_len as com.logcat.hive.udf.MyStringLength;删除临时函数drop temporary function my_len;注意临时函数只跟会话有关系只要会话不断在当前会话下任意一个库都可以使用。其他会话全都不能使用。3 创建永久函数注意因为永久函数是永久生效的我们推出当前会话以后其他会话也要使用永久函数因此我们就不能简单的使用add jar来添加hive的类路径了创建永久函数注意此时要使用USING JAR的方式来添加函数的jar包类路径并且这个路径必须是hdfs路径create function my_len2 as com.logcat.hive.udf.MyStringLength USING JAR hdfs://hadoop102:9820/hivejar/myudf.jar;删除永久函数drop function my_len2;注意永久函数创建的时候在函数名之前需要自己加上库名如果不指定库名的话会默认把当前库的库名给加上。然后使用永久函数的时候需要在指定的库里面操作,或者在其他库里面使用的话得加上 库名.函数名
