学习大数据DAY40 基于 hive 的数据处理

Hive 复合数据查询方法

with open(r"D:\智云大数据\数据源
\user\user_info.txt",'r',encoding="utf-8") as f:
LineRead=f.readlines()
# print(LineRead)
for i in LineRead:
list1=[]
list_i=i.split('\t')
# print(list_i)
# 把字典的值复制加到每行的末尾
for j in eval(list_i[7]).values():
list1.append(j)
str1='|'.join(list1)
list_i[7]=str1
list_i.append(str1)
# print(list_i)
list_i[8]='|'.join(list_i[8].split(',')).strip('\n') #
把第九列转化成可以导入 struct 的形式
str_i=','.join(list_i) # 每行每个数据用逗号隔开
# print(str_i)
with open(r"D:\智云大数据
\user_info_disposal_new.txt",'a',encoding='utf-8') as h:
h.writelines(str_i+"\n")
h.close()
f.close()

drop table if exists user_info;
create table if not exists user_info
(
id int,
name string,
sex string,
age tinyint,
address string,
date_info string,
lev tinyint,
a_info array<string>,
m_info map<string,string>,
s_info
struct<systemtype:string,education:string,marriage_status:string,phon
ebrand:string>
)
row format delimited
fields terminated by ','
collection items terminated by '|'
MAP keys terminated by ':'
lines terminated by '\n'
load data local inpath '/root/user_info_disposal_new.txt'
into table user_info;

select date_format(date_info,'YYYY-MM'),address,sex,count(1) from
user_info
group by date_format(date_info,'YYYY-MM'),address,sex3.2 统计各地区的不同手机型号使用人数,并按照老中青(35 以下青年 男 65 岁
以下女 55 岁以下中年 男 65 岁以上女 55 岁以上老年)年龄 划分
地区 安卓使用人数{老 中 青} ios 使用人数{老 中 青}
with SystemtypeAndAge as
(
select
case when m_info["systemtype"]='android' then 'android'
when m_info["systemtype"]='ios' then 'ios'end as systemtype,
case when age<35 then '青年'
when sex='male' and age<65 or sex='female' and age<55 then '中年'
else '老年'
end as AgeBracket
from user_info
)
select systemtype,AgeBracket,count(1) from SystemtypeAndAge
group by systemtype,AgeBracket

with SystemtypeAndAge as
(
select
case when m_info["systemtype"]='android' then 'android'
when m_info["systemtype"]='ios' then 'ios'end as systemtype,
case when age<35 then '青年'
when sex='male' and age<65 or sex='female' and age<55 then '中年'
else '老年'
end as AgeBracket
from user_info
)
select systemtype,AgeBracket,count(1) from SystemtypeAndAge
group by systemtype,AgeBracket

select user_info.address,education
,collect_list(distinct m_info["phonebrand"])
from user_info
left join
(
select address,
case m_info["education"] when "bachelor" then 'bachelor'
when "doctor" then 'doctor'when "master" then 'master'
end as education
from user_info
) EducationInAddress on user_info.address=EducationInAddress.address
group by user_info.address,education