sql读取数据库中的字段生成json字符串再存储

[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]