大数据

grouping sets 测试数据

文 / sptk 来源 / 原创 阅读 / 174 5月前

1. 上传数据到服务器或者hdfs

数据链接: 链接:https://pan.baidu.com/s/19jTPxpxuVHpPWKyKsUaglg?pwd=mr1k 提取码:mr1k

2. hive 上创建表数据 加载数据

-- 创建表语句
drop table if exists tb_grouping;
create table tb_grouping
(
    dt         string,
    brand      string,
    city       string,
    sales_sum  int,
    order_num  int,
    group_type string
)
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
        WITH SERDEPROPERTIES (
        'serialization.null.format' = '',
        'field.delim' = '\t'
        );
-- 加载数据 注意替换成你自己的数据路径      
load data LOCAL inpath '/root/data/data.txt' into table tb_grouping;

3. 测试hive sql, 也可以使用preoto 测试

select dt,
       brand,
       city,
       case
           when grouping(dt, brand, city) = 3 and group_type = 'ALL' then sum(sales_sum)
           when grouping(dt, brand, city) = 1 and group_type = 'brand' then sum(sales_sum)
           when grouping(dt, brand, city) = 2 and group_type = 'city' then sum(sales_sum)
           else null
           end as s_total

from tb_grouping
group by dt, brand, city, group_type
    grouping sets (
    ( dt, group_type),
    ( dt, brand, group_type),
    ( dt, city, group_type )
    );

org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

Constant Expanded Default Description
COLUMN_NAME_DELIMITER column.name.delimiter ,
FIELD_DELIM field.delim
COLLECTION_DELIM collection.delim
MAPKEY_DELIM mapkey.delim
SERIALIZATION_FORMAT serialization.format
SERIALIZATION_NULL_FORMAT serialization.null.format
SERIALIZATION_ESCAPE_CRLF serialization.escape.crlf false
SERIALIZATION_LAST_COLUMN_TAKES_REST serialization.last.column.takes.rest true
ESCAPE_CHAR escape.delim \\
SERIALIZATION_ENCODING serialization.encoding
SERIALIZATION_EXTEND_NESTING_LEVELS hive.serialization.extend.nesting.levels
SERIALIZATION_EXTEND_ADDITIONAL_NESTING_LEVELS hive.serialization.extend.additional.nesting.levels

hive 关于 seder的官方文档

https://cwiki.apache.org/confluence/display/Hive/DeveloperGuide#DeveloperGuide-HiveSerDe https://cwiki.apache.org/confluence/display/Hive/SerDe

0

站点声明:站点主要用于个人技术文章。

冀ICP备19037883号
相关侵权、举报、投诉及建议等,请发E-mail:804330969@qq.com