职场社交网站怎么做,cms 网站群,网络艺术设计是什么,廊坊网站建设官网1. 后台启动HIVE的JDBC连接 0 表示标准输入 1 表示标准输出 2 表示标准错误输出 nohup 表示挂起 最后的 表示 后台启动 nohup hive -service hiveserver2 /usr/local/soft/hive-3.1.2/log/hiveserver2.log 21 jps 查看 Runj… 1. 后台启动HIVE的JDBC连接 0 表示标准输入 1 表示标准输出 2 表示标准错误输出 nohup 表示挂起 最后的 表示 后台启动 nohup hive -service hiveserver2 /usr/local/soft/hive-3.1.2/log/hiveserver2.log 21 jps 查看 Runjob 关闭后台的JDBC : kill -9 关闭 RunJob对应的PID
1.HIVE WORDCOUNT CREATE TABLE learn3.wordcount( word STRING COMMENT 单词 ) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t STORED AS TEXTFILE;
INSERT INTO TABLE learn3.wordcount (word) VALUES (hello,word),(hello,java),(hive,hello);
-- 统计WORDCOUNT SELECT T1.col , sum(num) FROM ( SELECT col2 as col, count(*) as num FROM (
SELECT split(word,,)[0] as col1, split(word,,)[1] as col2 FROM learn3.wordcount
)T GROUP BY T.col2 UNION ALL SELECT col1 as col, count(*) as num FROM (
SELECT split(word,,)[0] as col1, split(word,,)[1] as col2 FROM learn3.wordcount
)T GROUP BY T.col1 )T1 GROUP BY T1.col
2. WITH AS 用法 格式 WITH table1 AS ( SELECT 查询语句1 ) , table2 AS ( SELECT 查询语句2 ) [INSERT INTO TABLE] SELECT FROM --WORDCOUNT的 WITH AS 用法 WITH split_res AS ( SELECT split(word,,)[0] as col1, split(word,,)[1] as col2 FROM learn3.wordcount ) , col1_count AS( SELECT col1 as col ,count(*) as num FROM split_res GROUP BY col1 )
, col2_count AS( SELECT col2 as col ,count(*) as num FROM split_res GROUP BY col2 )
SELECT T.col, sum(T.num) FROM ( SELECT * FROM col1_count UNION ALL SELECT * FROM col2_count )T GROUP BY T.col 3. 集合函数 COLLECT_LIST(column2) 需要跟 GROUP BY column1 配合使用将column1中相同的组column2数据放至一个集合中 COLLECT_SET() 需要跟GROUP BY column1 配合使用将column1中相同的组column2数据放至一个集合中,并对集合中的数据进行去重操作
--需求 将一列中相同的内容放至一组数据中 将 word列中所有相同的单词对应的num 放至一个数组中
CREATE TABLE learn1.wordcount2( word STRING COMMENT 单词, num int COMMENT 数量 ) STORED AS TEXTFILE;
INSERT INTO TABLE learn1.wordcount2 (word,num) VALUES (hello,1),(hello,2),(hive,3); SELECT word ,COLLECT_LIST(num) FROM learn3.wordcount2 GROUP BY word -------------------- | word | _c1 | -------------------- | hello | [1,2,1,2] | | hive | [3,3] | SELECT word ,COLLECT_SET(num) FROM learn3.wordcount2 GROUP BY word
| hello | [1,2] | | hive | [3] |
CREATE EXTERNAL TABLE IF NOT EXISTS learn3.student20( id STRING COMMENT 学生ID, name STRING COMMENT 学生姓名, age int COMMENT 年龄, gender STRING COMMENT 性别, subject STRING COMMENT 学科 ) ROW FORMAT DELIMITED FIELDS TERMINATED BY , STORED AS TEXTFILE;
load data local inpath /usr/local/soft/hive-3.1.2/data/student_20.txt INTO TABLE learn3.student20;
理科|男 1500100020|杭振凯,1500100019|娄曦之 ① 将两列数据进行拼接 WITH concat_stu AS( SELECT CONCAT(subject,|,gender) as subject_gender ,CONCAT_WS(|,id,name) as id_name FROM learn3.student20 )
SELECT subject_gender ,CONCAT_WS(,,COLLECT_LIST(id_name)) FROM concat_stu GROUP BY subject_gender | 文科|女 | [1500100001|施笑槐,1500100007|尚孤风,1500100016|潘访烟,1500100018|骆怜雪] | | 文科|男 | [1500100002|吕金鹏,1500100013|逯君昊] | | 理科|女 | [1500100003|单乐蕊,1500100005|宣谷芹,1500100008|符半双,1500100012|梁易槐,1500100015|宦怀绿,1500100017|高芷天] | | 理科|男 | [1500100004|葛德曜,1500100006|边昂雄,1500100009|沈德昌,1500100010|羿彦昌,1500100011|宰运华,1500100014|羿旭炎,1500100019|娄曦之,1500100020|杭振凯] |
| concat_ws(separator, [string | array(string)]) - returns the concatenation of the strings separated by the separator. | 4.行列互换 ---------------------------------- | subject_gender | id_name | ---------------------------------- | 文科|女 | 1500100001|施笑槐 | | 文科|男 | 1500100002|吕金鹏 | | 理科|女 | 1500100003|单乐蕊 | | 理科|男 | 1500100004|葛德曜 | | 理科|女 | 1500100005|宣谷芹 | | 理科|男 | 1500100006|边昂雄 | | 文科|女 | 1500100007|尚孤风 | | 理科|女 | 1500100008|符半双 | | 理科|男 | 1500100009|沈德昌 | | 理科|男 | 1500100010|羿彦昌 | | 理科|男 | 1500100011|宰运华 | | 理科|女 | 1500100012|梁易槐 | | 文科|男 | 1500100013|逯君昊 | | 理科|男 | 1500100014|羿旭炎 | | 理科|女 | 1500100015|宦怀绿 | | 文科|女 | 1500100016|潘访烟 | | 理科|女 | 1500100017|高芷天 | | 文科|女 | 1500100018|骆怜雪 | | 理科|男 | 1500100019|娄曦之 | | 理科|男 | 1500100020|杭振凯 |
| 文科|女 | 1500100001|施笑槐,1500100007|尚孤风,1500100016|潘访烟,1500100018|骆怜雪 | | 文科|男 | 1500100002|吕金鹏,1500100013|逯君昊 | | 理科|女 | 1500100003|单乐蕊,1500100005|宣谷芹,1500100008|符半双,1500100012|梁易槐,1500100015|宦怀绿,1500100017|高芷天 | | 理科|男 | 1500100004|葛德曜,1500100006|边昂雄,1500100009|沈德昌,1500100010|羿彦昌,1500100011|宰运华,1500100014|羿旭炎,1500100019|娄曦之,1500100020|杭振凯 |
行转列 将原先多行数据转成一行 转换方式 通过COLLECT_SET() 或者 COLLECT_LIST() 和 GROUP BY 进行配合使用 将GROUP BY 分组的数据进行存放于一个集合当中 列传行 将一行数据转换为多行数据 转换
案例 | wordcount.word | ----------------- | hello,word,hive | | hello,java | | hello,hive | | hello,word | | hello,java | | hive,hello |
结果 ---------- | _u1.col | ---------- | hello | | hive | | java | | word | | hello | | hive | ---------- INSERT INTO TABLE learn3.wordcount (word) VALUES (hello,word,hello,java,hello,spark);
EXPLODE() 函数 将集合中的一行数据转换为多行
SELECT EXPLODE(split(word,,)) as word FROM learn3.wordcount
CREATE TABLE learn3.movie( movie_name STRING COMMENT 电影名, types STRING COMMENT 电影类型 ) ROW FORMAT DELIMITED FIELDS TERMINATED BY , STORED AS TEXTFILE;
load data local inpath /usr/local/soft/hive-3.1.2/data/moive.txt INTO TABLE learn3.movie;
-- 将types列中的电影类型进行分隔并且与电影名进行对应 由 |肖申克的救赎 | 犯罪/剧情 | 转换为 肖申克的救赎 犯罪 肖申克的救赎 剧情
SELECT movie_name,EXPLODE(split(types,/)) as type FROM learn3.movie SELECT movie_name,type FROM learn3.movie LATERAL VIEW EXPLODE(split(types,/)) view as type | movie.movie_name | movie.types | --------------------------------- | 肖申克的救赎 | 犯罪/剧情 | | 霸王别姬 | 剧情/爱情/同性 | | 阿甘正传 | 剧情/爱情 | | 泰坦尼克号 | 剧情/爱情/灾难 | | 这个杀手不太冷 | 剧情/动作/犯罪 | | movie_name | type | -------------------- | 肖申克的救赎 | 犯罪 | | 肖申克的救赎 | 剧情 | | 霸王别姬 | 剧情 | | 霸王别姬 | 爱情 | | 霸王别姬 | 同性 | | 阿甘正传 | 剧情 | | 阿甘正传 | 爱情 | | 泰坦尼克号 | 剧情 | | 泰坦尼克号 | 爱情 | | 泰坦尼克号 | 灾难 | | 这个杀手不太冷 | 剧情 | | 这个杀手不太冷 | 动作 | | 这个杀手不太冷 | 犯罪 | LATERAL VIEW EXPLODE(split(types,/)) view as type 解析 ① 通过split方法将types中的字符串切分为数组 ② 通过EXPLODE方法将数组由一行数据转换为多行 ③ 通过LATERAL VIEW 将EXPLODE转换的结果包装成一个名为view的一个侧写表并且列名为type ④ 通过全连接将侧写表中的数据与原表 learn3.movie 中的一行数据进行全连接 5.
CREATE EXTERNAL TABLE IF NOT EXISTS learn3.student1( id STRING COMMENT 学生ID, name STRING COMMENT 学生姓名, age int COMMENT 年龄, gender STRING COMMENT 性别, clazz STRING COMMENT 班级 ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ,;
load data local inpath /usr/local/soft/hive-3.1.2/data/students.txt INTO TABLE learn3.student1; CREATE EXTERNAL TABLE IF NOT EXISTS learn3.score1( id STRING COMMENT 学生ID, subject_id STRING COMMENT 科目ID, score int COMMENT 成绩 ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ,
load data local inpath /usr/local/soft/hive-3.1.2/data/score.txt INTO TABLE learn3.score1;
需求 1.统计各性别年龄前三 -- HIVE 原先老版本 不支持这种写法 SELECT s1.* FROM learn3.student1 as s1 WHERE 3 (SELECT count(*) FROM learn3.student1 as s2 WHERE s1.gender s2.gender and s1.age s2.age ) 2.统计各班级学生总成绩前三名 ①算出学生总成绩 WITH score_sum AS ( SELECT id ,sum(score) as total_score FROM learn3.score1 GROUP BY id )
, studen_score AS ( SELECT T1.name ,T2.total_score ,T1.clazz FROM learn3.student1 T1 JOIN score_sum T2 ON T1.id T2.id )
--② 排序获取前三名
SELECT TT.* FROM ( SELECT T.name ,T.total_score ,T.clazz , ROW_NUMBER() OVER(PARTITION BY T.clazz ORDER BY T.total_score DESC) as row_pm FROM studen_score T ) TT WHERE TT.row_pm 3 SELECT T.name ,T.total_score ,T.clazz , ROW_NUMBER() OVER(PARTITION BY T.clazz ORDER BY T.total_score DESC) as row_pm , dense_rank() OVER(PARTITION BY T.clazz ORDER BY T.total_score DESC) as dense_pm , rank() OVER(PARTITION BY T.clazz ORDER BY T.total_score DESC) as rank_pm , percent_rank() OVER(PARTITION BY T.clazz ORDER BY T.total_score DESC) as percent_rank_pm , cume_dist() OVER(PARTITION BY T.clazz ORDER BY T.total_score DESC) as cume_dist_pm FROM studen_score T
##### row_number 无并列排名
* 用法 select xxxx, row_number() over(partition by 分组字段 order by 排序字段 desc) as rn from tb group by xxxx
##### dense_rank 有并列排名并且依次递增
* 用法 select xxxx, dense_rank() over(partition by 分组字段 order by 排序字段 desc) as rn from tb group by xxxx ##### rank 有并列排名不依次递增
* 用法 select xxxx, rank() over(partition by 分区字段 order by 排序字段 desc) as rn from tb group by xxxx
##### percent_rank(rank的结果-1)/(分区内数据的个数-1)
* 用法 select xxxx, percent_rank() over(partition by 分组字段 order by 排序字段 desc) as rn from tb group by xxxx
需求1 取每个班级总分最大的同学
CREATE TABLE learn3.student_score( name STRING COMMENT , total_score int COMMENT , clazz STRING COMMENT ); WITH score_sum AS ( SELECT id ,sum(score) as total_score FROM learn3.score1 GROUP BY id )
INSERT INTO TABLE learn3.student_score SELECT T1.name ,T2.total_score ,T1.clazz FROM learn3.student1 T1 JOIN score_sum T2 ON T1.id T2.id -- 通过max方法取出每个班级分区中的学生成绩最大值 SELECT TT.* FROM ( SELECT T1.* ,max(T1.total_score) OVER(PARTITION BY T1.clazz ORDER BY T1.total_score DESC) as max_score FROM learn3.student_score T1 ) TT WHERE TT.total_score TT.max_score -- 如果max() OVER() 中的窗口over()没有给定分区那么当前的窗口表示整个学校,得到的数据是整个学校的最高的分数
SELECT TT.* FROM ( SELECT T1.* ,max(T1.total_score) OVER() as max_score FROM learn3.student_score T1 ) TT WHERE TT.total_score TT.max_score max 用法 ① max(T1.total_score) OVER(PARTITION BY T1.clazz ORDER BY T1.total_score DESC) 基于每个partition分区内数据取最大值 ② max(T1.total_score) OVER() 基于整个数据集取最大值 min、avg、count、sum与max方法使用一致
6. 《《《《 卡口流量需求分析 》》》》
CREATE TABLE learn3.veh_pass( id STRING COMMENT 卡口编号, pass_time STRING COMMENT 进过时间, pass_num int COMMENT 过车数 ) ROW FORMAT DELIMITED FIELDS TERMINATED BY , STORED AS TEXTFILE;
load data local inpath /usr/local/soft/hive-3.1.2/data/veh_pass.txt INTO TABLE learn3.veh_pass;
需求1查询当月的设备及其总数
-- 写法1 SELECT T.id ,count(*) OVER() FROM ( SELECT id ,pass_time FROM learn3.veh_pass WHERE substr(pass_time,1,7) substr(current_date,1,7) ) T GROUP BY T.id -- 错误写法 SELECT DISTINCT id ,count(*) OVER() FROM ( SELECT id ,pass_time FROM learn3.veh_pass WHERE substr(pass_time,1,7) substr(current_date,1,7) )T -- 写法2 SELECT T1.id ,count(*) OVER() FROM ( SELECT DISTINCT id FROM ( SELECT id ,pass_time FROM learn3.veh_pass WHERE substr(pass_time,1,7) substr(current_date,1,7) )T )T1 -------------------------------------- | t1.id | count_window_0 | -------------------------------------- | 451000000000071117 | 5 | | 451000000000071116 | 5 | | 451000000000071115 | 5 | | 451000000000071114 | 5 | | 451000000000071113 | 5 | --------------------------------------
--------------------- | id | --------------------- | 451000000000071113 | | 451000000000071114 | | 451000000000071115 | | 451000000000071116 | | 451000000000071117 | --------------------- -- 需求2查询所有流量明细及所有设备月流量总额 SELECT T1.id ,T1.pass_time ,T1.pass_num ,SUM(T1.pass_num) OVER(PARTITION BY SUBSTRING(T1.pass_time,1,7)) as total_pass FROM learn3.veh_pass T1 需求3按设备编号日期顺序展示明细 并求 1从第一天开始到当前天数 对流量进行累加 2昨天与当前天流量累加 3当前天数的前一天与后一天流量累加 4当前天与下一天的累加和 5当前天数与之后所有天流量累加和
1从第一天开始到当前天数 对流量进行累加 SELECT T1.* ,SUM(T1.pass_num) OVER(ORDER BY T1.pass_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM ( SELECT * FROM learn3.veh_pass ORDER BY pass_time ) T1
2昨天与当前天流量累加
SELECT T1.* ,SUM(T1.pass_num) OVER(ORDER BY T1.pass_time ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM ( SELECT * FROM learn3.veh_pass ORDER BY pass_time ) T1
3当前天数的前一天与后一天流量累加
SELECT T1.* ,SUM(T1.pass_num) OVER(ORDER BY T1.pass_time ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM ( SELECT * FROM learn3.veh_pass ORDER BY pass_time ) T1
4当前天与下一天的累加和 SELECT T1.* ,SUM(T1.pass_num) OVER(ORDER BY T1.pass_time ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM ( SELECT * FROM learn3.veh_pass ORDER BY pass_time ) T1
5当前天数与之后所有天流量累加和 SELECT T1.* ,SUM(T1.pass_num) OVER(ORDER BY T1.pass_time ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM ( SELECT * FROM learn3.veh_pass ORDER BY pass_time ) T1
需求4查询每个设备编号上次有数据日期和下一次有数据日期 SELECT T1.* , LAG(T1.pass_time,1,2022-01-01) OVER(PARTITION BY T1.id ORDER BY T1.pass_time) as before_time , LEAD(T1.pass_time,1,2022-12-31) OVER(PARTITION BY T1.id ORDER BY T1.pass_time) as after_time FROM learn3.veh_pass T1 总结 OVER(): 会为每条数据都开启一个窗口默认窗口大小就是当前数据集的大小 OVER(PARTITION BY) 会按照指定的字段进行分区在获取一条数据时窗口大小为整个分区的大小之后根据分区中的数据进行计算 OVER(PARTITION BY ... ORDER BY ...) 根据给定的分区在获取一条数据时窗口大小为整个分区的大小并且对分区中的数据进行排序
OVER中的取数据格式 (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
OVER()指定分析函数工作的数据窗口大小这个数据窗口大小可能会随着行的改变而变化。 CURRENT ROW当前行 n PRECEDING往前n行数据 n FOLLOWING往后n行数据 UNBOUNDED 起点 UNBOUNDED PRECEDING 表示从前面的起点 UNBOUNDED FOLLOWING 表示到后面的终点 LAG(col,n,default_val)往前第n行数据 LEAD(col,n, default_val)往后第n行数据 NTILE(n)把有序窗口的行分发到指定数据的组中各个组有编号编号从1开始对于每一行NTILE返回此行所属的组的编号。
-- 自定义UDF函数 老版本UDF 不推荐使用 ① 创建自定义类继承UDF 注意 自定义函数名必须使用 evaluate 不然识别不到 public class MyUDFAddString extends UDF { /** * 定义函数名 evaluate * 实现将传入的String 增加后缀 ### * * param col HIVE中使用函数时传入的数据 * return 一行数据 */ public String evaluate(String col) { return col ###; }
} ② 将代码打包添加jar包至HIVE中 add jar /usr/local/soft/test/HiveCode15-1.0-SNAPSHOT.jar; ③ 创建临时自定义函数 CREATE TEMPORARY FUNCTION my_udf as com.shujia.udf.MyUDFAddString; my_udf(col)
