Hive手写SQL案例

时间：2021-09-04 06:05:00

1-请详细描述将一个有结构的文本文件student.txt导入到一个hive表中的步骤，及其关键字

  • 假设student.txt 有以下几列：id,name,gender三列
  • 1-创建数据库 create database student_info;
  • 2-创建hive表 student
create external table student_info.student( id string comment ‘学生id‘, name string comment ‘学生姓名‘, gender string comment ‘学生性别‘ ) comment "学生信息表" row format delimited fields terminated by ‘t‘ line terminated by ‘n‘ stored as textfile location "/user/root/student";
  • 3-加载数据
load data local inpath ‘/root/student.txt‘ into table student_info.student location "/user/root/student" ;
  • 4- 进入hive-cli，查看相应的表结构
    select * from student_info.student limit 10；

划重点：要回手写代码

2-利用HQL实现以下功能

2-1-创建表

  • 创建员工基本信息表(EmployeeInfo)，字段包括(员工 ID，员工姓名，员工身份证号，性别，年龄，所属部门，岗位，入职公司时间，离职公司时间)，分区字段为入职公司时间，其行分隔符为”n “，字段分隔符为”t “。其中所属部门包括行政部、财务部、研发部、教学部，其对应岗位包括行政经理、行政专员、财务经理、财务专员、研发工程师、测试工程师、实施工程师、讲师、助教、班主任等，时间类型值如：2018-05-10 11:00:00

  • 创建员工收入表(IncomeInfo)，字段包括(员工 ID，员工姓名，收入金额，收入所属
    月份，收入类型，收入薪水的时间)，分区字段为发放薪水的时间，其中收入类型包括薪资、奖金、公司福利、罚款四种情况 ; 时间类型值如：2018-05-10 11:00:00。

注意：时间类型是2018-05-10 11:00:00，需要对字段进行处理

  • 创建员工基本信息表
create external table test.employee_info( id string comment ‘员工id‘, name string comment ‘员工姓名‘, indentity_card string comment ‘身份证号‘, gender string comment ‘性别‘, department string comment ‘所属部门‘, post string comment ‘岗位‘, hire_date string comment ‘入职时间‘, departure_date string comment ‘离职时间‘ ) comment "员工基本信息表" partitioned by (day string comment "员工入职时间") row format delimited fields terminated by ‘t‘ lines terminated by ‘n‘ stored as textfile location ‘/user/root/employee‘;
  • 创建员工收入表
create external table test.income_info( id string comment ‘员工id‘, name string comment ‘员工姓名‘, income_data string comment ‘收入‘, income_month string comment ‘收入所属月份‘, income_type string comment ‘收入类型‘, income_datetime string comment ‘收入薪水时间‘ ) comment ‘员工收入表‘ partitioned by (day string comment "员工发放薪水时间") row format delimited fields terminated by ‘t‘ lines terminated by ‘n‘ stored as textfile location ‘/user/root/income‘;

2-2用 HQL 实现，求公司每年的员工费用总支出各是多少，并按年份降序排列?

  • 重点对时间类型 2018-05-10 11:00:00 进行内置函数处理
  • 需要读取income_info全量表，按照分区时间进行聚合，因为收入类型里面有罚款一项，所以需要在员工发放的钱中扣除罚款的钱。
  • 不采用join、对数据一次遍历输出结果，
  • 对于大数据量的情况下，要考虑对数据进行一次遍历求出结果
select income_year,(income_data-(nvl(penalty_data,0))) as company_cost from ( -- 统计员工收入金额和罚款金额，输出 2019 500 10 select income_year, sum(case when income_type!=‘罚款‘ then data_total else 0 end) as income_data, sum(case when income_type=‘罚款‘ then data_total else 0 end) as penalty_data from ( -- 按照年份、收入类型求收入金额 select year(to_date(income_datetime)) as income_year, income_type, sum(income_data) as data_total from test.income_info group by year(to_date(income_datetime)) ,income_type ) tmp_a group by tmp_a.income_year ) as temp order by income_year desc;

2-3用 HQL 实现，求各部门每年的员工费用总支出各是多少，并按年份降序，按部门的支出升序排列？

  • 保证对数据的一次遍历
--根据id关联得出department,和消费类型 select income_year,department, (sum(case when income_type!=‘罚款‘ then income_data else 0 end) - sum(case when income_type=‘罚款‘ then income_data else 0 end) ) as department_cost from ( -- 先对员工进行薪资类别的聚合统计 select id,year(to_date(income_datetime)) as income_year,income_type,sum(income_data) as income_data from test.income_info group by year(to_date(income_datetime)),id,income_type ) temp_a inner join test.employee_info b on temp_a.id=b.id group by department,income_year order by income_year desc , department_cost asc;

2-4用 HQL 实现，求各部门历史所有员工费用总支出各是多少，按总支出多少排名降序，遇到值相等情况，不留空位。

  • 根据2-3中的中间结果进行修改
  • 注意历史上所有的数据
select department,department_cost,dense_rank() over(order by department_cost desc) as cost_rank from ( --根据id关联得出department,和消费类型 select department, (sum(case when income_type!=‘罚款‘ then income_data else 0 end) - sum(case when income_type=‘罚款‘ then income_data else 0 end) ) as department_cost from ( -- 先对员工进行薪资类别的聚合统计 select id,income_type,sum(income_data) as income_data from test.income_info group by id,income_type ) temp_a inner join test.employee_info b on temp_a.id=b.id group by department ) tmp_c ;

2-5 用 HQL 实现，创建并生成员工薪资收入动态变化表，即员工 ID，员工姓名，员工本月薪资，本月薪资发放时间，员工上月薪资，上月薪资发放时间。分区字段为本月薪资发放时间。

  • 感觉应该使用动态分区插入的特性？-但是不知道该怎么写
  • 先创建表，再采用insert into table **** select ***
  • 要考虑到离职和入职的员工，这一点需要考虑到，full join
  • 两张表进行full join，过滤day is null
  • 需要concat year month to_date内置函数处理
  • 这个题需要考虑的比较多
create external table test.income_dynamic( id string comment ‘员工id‘, name string comment ‘员工姓名‘, income_data_current string comment ‘本月收入‘, income_datetime_current string comment 本月‘收入薪水时间‘, income_data_last string comment ‘上月收入‘, income_datetime_last string comment ‘上月收入薪水时间‘, ) comment ‘员工收入动态表‘ partitioned by (day string comment "员工本月发放薪水时间") row format delimited fields terminated by ‘t‘ lines terminated by ‘n‘ stored as textfile location ‘/user/root/income‘; -- ------------------------------------------------------------------------------ -- 动态分区插入 -- 插入语句 -- 采用full join insert into table test.income_dynamic partition(day) select (case when id_a is not null then id_a else id_b end ) as id, (case when name_a is not null then name_a else name_b end ) as name , income_data,income_datetime,income_data_b,income_datetime_b,day from ( -- 选出表中所有的数据 select id as id_a,name as name_a,income_data,income_datetime,day,concat(year(to_date(day)),month(to_date(day))) as day_flag from test.income_info where income_type=‘薪资‘ ) tmp_a full outer join ( -- 将表中的收到薪水的日期整体加一个月 select id as id_b,name as name_b,income_data as income_data_b,income_datetime as income_datetime_b,concat(year(add_months(to_date(day),1)),month(add_months(to_date(day),1))) as month_flag from test.income_info where income_type=‘薪资‘ ) tmp_b on tmp_a.day_flag=tmp_b.month_flag and tmp_a.id_a=tmp_b.id_b where day is not null ;

2-6 用 HQL 实现，薪资涨幅方面，2018 年 5 月份谁的工资涨的最多，谁的涨幅最大？

  • 再2-5的基础上做比较简单，仅仅利用select部分即可；或者是再2-5的基础上做就行

Hive行列转换

１、问题
hive如何将
a       b       1 a b 2 a b 3 c d 4 c d 5 c d 6 变为： a b 1,2,3 c d 4,5,6 ------------------------------------------------------------------------------------------- ２、数据 test.txt a b 1 a b 2 a b 3 c d 4 c d 5 c d 6 ------------------------------------------------------------------------------------------- ３、答案 1.建表 drop table tmp_jiangzl_test; create table tmp_jiangzl_test ( col1 string, col2 string, col3 string ) row format delimited fields terminated by ‘t‘ stored as textfile; -- 加载数据 load data local inpath ‘/home/jiangzl/shell/test.txt‘ into table tmp_jiangzl_test; 2.处理 select col1,col2,concat_ws(‘,‘,collect_set(col3)) from tmp_jiangzl_test group by col1,col2; --------------------------------------------------------------------------------------- collect_set/concat_ws语法参考链接：https://blog.csdn.net/waiwai3/article/details/79071544 https://blog.csdn.net/yeweiouyang/article/details/41286469 [Hive]用concat_w实现将多行记录合并成一行 --------------------------------------------------------------------------------------- 二、列转行 １、问题 hive如何将 a b 1,2,3 c d 4,5,6 变为： a b 1 a b 2 a b 3 c d 4 c d 5 c d 6 --------------------------------------------------------------------------------------------- 2、答案 1.建表 drop table tmp_jiangzl_test; create table tmp_jiangzl_test ( col1 string, col2 string, col3 string ) row format delimited fields terminated by ‘t‘ stored as textfile; 处理： select col1, col2, col5 from tmp_jiangzl_test a lateral view explode(split(col3,‘,‘)) b AS col5; --------------------------------------------------------------------------------------- lateral view 语法参考链接： https://blog.csdn.net/clerk0324/article/details/58600284

Hive实现wordcount

1.创建数据库 create database wordcount; 2.创建外部表 create external table word_data(line string) row format delimited fields terminated by ‘,‘ location ‘/home/hadoop/worddata‘; 3.映射数据表 load data inpath ‘/home/hadoop/worddata‘ into table word_data; 4.这里假设我们的数据存放在hadoop下，路径为：/home/hadoop/worddata，里面主要是一些单词文件，内容大概为： hello man what are you doing now my running hello kevin hi man 执行了上述hql就会创建一张表src_wordcount，内容是这些文件的每行数据，每行数据存在字段line中，select * from word_data;就可以看到这些数据 5.根据MapReduce的规则，我们需要进行拆分，把每行数据拆分成单词，这里需要用到一个hive的内置表生成函数（UDTF）：explode(array)，参数是array，其实就是行变多列： create table words(word string); insert into table words select explode(split(line, " ")) as word from word_data; 6.查看words表内容 OK hello man what are you doing now my running hello kevin hi man split是拆分函数，跟java的split功能一样，这里是按照空格拆分，所以执行完hql语句，words表里面就全部保存的单个单词 7.group by统计单词 select word, count(*) from wordcount.words group by word; wordcount.words 库名称.表名称，group by word这个word是create table words(word string) 命令创建的word string 结果： are 1 doing 1 hello 2 hi 1 kevin 1 man 2 my 1 now 1 running 1 what 1 you 1

Hive取TopN

  • rank() over()
  • dense_rank() over()
  • row_number() over()

求取指定状态下的订单id

  • 给一张订单表，统计只购买过面粉的用户；（重点在于仅仅购买过面粉的客户）
    eg：order:order_id,buyer_id,order_time.....

在保证一次遍历的情况下,重点是O(1)复杂度

select buyer_id from ( select buyer_id,sum(case when order_id=‘面粉‘ then 0 else 1 end) as flag from order ) as tmp where flag=0;

微博体系中互粉的有多少组

  • 在微博粉丝表中，互相关注的人有多少组，例如：A-->B;B-->A；A和B互粉，称为一组。
    表结构：id,keep_id,time.... (id,keep_id可作为联合主键)
  • 借助Hive进行实现
select count(*)/2 as weibo_relation_number from ( (select concat(id,keep_id) as flag from weibo_relation) union all --全部合并到一起，不能提前去重 (select concat(keep_id,id) as flag from weibo_relation) ) as tmp having count(flag) =2 group by flag;

购买了香蕉的人买了多少东西

  • 这个是一个很经典的问题，购买了香蕉的人买了多少东西
  • 数据还是延用上一个问题的数据和表结构，即理解为关注C的人总共关注了多少人
  • 仔细理解是需要对关注的人进行去重统计
select count(distinct keep_id) as total_keep_id from weibo_relation where id in (select id from weibo_relation where keep_id=‘c‘)

相关文章