[size=medium]
--screen
insert overwrite ana_fx_goal.mc_daily_kv partition(hdfs_par='20161221')
select * from
(
select
cast(
concat(substr('20161221', 1, 4), '-', substr('20161221', 5, 2), '-', substr('20161221', 7, 2))
as timestamp
)
as day_begin,
NOW() as generate_time,
'screen_distribution' as json_key,
json_output as json_value
from
(
select
concat(
'{',
group_concat(
concat(wrapped_column_key, ':', 0),
','
),
'}'
) as json_output
from
(
select
concat('"', screen, '"') as wrapped_column_key,
case
when cnt is null then 'null'
when 'int' = 'string' then concat('"', cast(cnt as string), '"')
else cast(cnt as string)
end as wrapped_column_value
from
(
with analyse_table as(
with device_profile as(
select *,ana_fx_middle.get_device_info(model) as readablename from src_huidu_mc.device_profile1
)
select
deviceid,
screen
from device_profile
)
select screen,count(distinct deviceid) as cnt from analyse_table group by screen
) as tmp1_B
) as tmp2_B
) as tmp
limit 1
) as t1
union all
select * from
(
select
day_begin,
generate_time,
json_key,
json_value
from ana_fx_goal.mc_daily_kv
where hdfs_par = '20161221'
and json_key != 'screen_distribution'
) as t2
;
--model
insert overwrite ana_fx_goal.mc_daily_kv partition(hdfs_par='20161221')
select * from
(
select
cast(
concat(substr('20161221', 1, 4), '-', substr('20161221', 5, 2), '-', substr('20161221', 7, 2))
as timestamp
)
as day_begin,
NOW() as generate_time,
'model_distribution' as json_key,
json_output as json_value
from
(
select
concat(
'{',
group_concat(
concat(wrapped_column_key, ':', wrapped_column_value),
','
),
'}'
) as json_output
from
(
select
concat('"', dev_name, '"') as wrapped_column_key,
case
when cnt is null then 'null'
when 'int' = 'string' then concat('"', cast(cnt as string), '"')
else cast(cnt as string)
end as wrapped_column_value
from
(
with analyse_table as(
with device_profile as(
select *,ana_fx_middle.get_device_info(model) as readablename from src_huidu_mc.device_profile1
)
select
deviceid,
regexp_extract(readablename, '^([^\\t]*)\\t([^\\t]*)\\t*(.*)$', 2) as dev_name
from device_profile
)
select dev_name,count(distinct deviceid) as cnt from analyse_table group by dev_name
) as tmp1_E
) as tmp2_E
) as tmp
limit 1
) as t1
union all
select * from
(
select
day_begin,
generate_time,
json_key,
json_value
from ana_fx_goal.mc_daily_kv
where hdfs_par = '20161221'
and json_key != 'model_distribution'
) as t2
;
--brand
insert overwrite ana_fx_goal.mc_daily_kv partition(hdfs_par='20161221')
select * from
(
select
cast(
concat(substr('20161221', 1, 4), '-', substr('20161221', 5, 2), '-', substr('20161221', 7, 2))
as timestamp
)
as day_begin,
NOW() as generate_time,
'brand_distribution' as json_key,
json_output as json_value
from
(
select
concat(
'{',
group_concat(
concat(wrapped_column_key, ':', wrapped_column_value),
','
),
'}'
) as json_output
from
(
select
concat('"', dev_brand, '"') as wrapped_column_key,
case
when cnt is null then 'null'
when 'int' = 'string' then concat('"', cast(cnt as string), '"')
else cast(cnt as string)
end as wrapped_column_value
from
(
with analyse_table as(
with device_profile as(
select *,ana_fx_middle.get_device_info(model) as readablename from src_huidu_mc.device_profile1
)
select
deviceid,
regexp_extract(readablename, '^([^\\t]*)\\t([^\\t]*)\\t*(.*)$', 1) as dev_brand
from device_profile
)
select dev_brand,count(distinct deviceid) as cnt from analyse_table group by dev_brand
) as tmp1_H
) as tmp2_H
) as tmp
limit 1
) as t1
union all
select * from
(
select
day_begin,
generate_time,
json_key,
json_value
from ana_fx_goal.mc_daily_kv
where hdfs_par = '20161221'
and json_key != 'brand_distribution'
) as t2
;
[/size]
分享到:
相关推荐
主要给大家介绍了关于Spark SQL操作JSON字段的小技巧,文中通过示例代码介绍的非常详细,对大家学习或者使用spark sql具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧。
把oracle数据库中字段类型为clob的字段值以字符串的形式读取出来
主要介绍了Oracle截取JSON字符串内容 ,本文通过实例代码给大家介绍的非常详细,具有一定的参考借鉴价值,需要的朋友可以参考下
5.方法二:随机生成字符串(数字和字母混和) 6.从字符串里随机得到,规定个数的字符串. 复制代码 22.条形码 BarCodeToHTML 本类是个条码生成类,大家可根据需要自己设置,非常好用 23.图片 ImageClass 主要功能...
使用C#实现对Sql数据库的操作,执行存储过程,Sql语句,返回影响行数,返回DateTable,DataSet,DataReader,以及表集等方法。实现多个数据库的切换功能。功能强大,希望大家喜欢 2.一个操作Sql2005数据库的类(备份...
简单留言板 Simple Guest Book == 开发环境:Win7 32bit + ...如何实现对象转json字符串? 如何实现$选择器和Ajax封装? 源码注释挺详细的,主要是给新同学看的,大牛请无视. 感兴趣的话更多内容请下载后请行查看....
35、MySQL、SqlServer、oracle写出字符存储、字符串转时间 52 36、update语句可以修改结果集中的数据吗? 53 37、oracle如何设置主键自动增长? 53 38、表连接、子查询的区别是什么?它们可以相互转化吗?你倾向于用...
11.2.3 使用特定查询字符串参数的缓存 11.2.4 自定义缓存控制 11.2.5 使用HttpCachePolicy类进行缓存 11.2.6 缓存后替换和部分页缓存 11.2.7 缓存用户配置 11.2.8 缓存配置 11.2.9 输出缓存扩展 11.3...