Hive 表复杂类型字段使用

create table test.employee(
              name StriNG,
              salary FLOAT,
              subordinates ARRAY<String>,
              deductions MAP<String,String>,
              address ARRAY<STRUCT<stree:string,city:string,state:string,zip:int>>
            ) ROW FORMAT DELIMITED
                  FIELDS TERMINATED BY 't'
                  COLLECTION ITEMS TERMINATED BY ','
                  MAP KEYS TERMINATED BY ':'
            STORED AS TEXTFILE;
        create table test.emp(
              name StriNG,
              salary FLOAT,
              subord String,
              dedkey String,
              dedval FLOAT,
              stree String,
              city String,
              state String,
              zip int
            ) ROW FORMAT DELIMITED
                  FIELDS TERMINATED BY 't'
            STORED AS TEXTFILE;
         insert into test.emp (name,salary,subord,dedkey,dedval,stree,city,state,zip) values ('u001',25000,'sub001','ded-k01',10.01,'china','beijing','use','100000');
         insert into test.emp (name,salary,subord,dedkey,dedval,stree,city,state,zip) values ('u001',25000,'sub002','ded-k02',20.02,'china-02','shanghai','use','100001');
         insert into test.emp (name,salary,subord,dedkey,dedval,stree,city,state,zip) values ('u001',25000,'sub003','ded-k03',30.03,'china-03','lanzhou','use','100002');

　　　　 1). 使用COllect_list()组装 ARRAY<String>字段
             SELEct name,collect_list(subord) subordinates  from   test.emp  group by name;
                 name    subordinates
                 u001    ["sub002","sub003","sub001"]
        2). 使用COllect_list()组装 ARRAY<STRUCT<stree:string,city:string,state:string,zip:int>>字段
            SELEct name,collect_set(named_struct('stree',stree,'city',city,'state',state,'zip',zip)) address from  test.emp group by name;
                name    address
                u001    [{"stree":"china-02","city":"shanghai","state":"use","zip":100001},{"stree":"china-03","city":"lanzhou","state":"use","zip":100002},{"stree":"china","city":"beijing","state":"use","zip":100000}]

        3). 组装Map类型字段
            SELEct name,collect_set(named_struct('dedkey',dedkey,'dedval',dedval)) page_stats from  test.emp group by name;
                u001    [{"dedkey":"ded-k02","dedval":20.02},{"dedkey":"ded-k03","dedval":30.03},{"dedkey":"ded-k01","dedval":10.01}]

            SELEct name,collect_set(concat_ws('=',dedkey,cast(dedval as String))) page_stats from  test.emp group by name;
                u001    ["ded-k02=20.02","ded-k03=30.03","ded-k01=10.01"]

            --  第一步： 将key-value字段组装成一个字符串，借助于concat_ws
             SELEct name,concat_ws(':',dedkey,cast(dedval as String)) kvs from  test.emp ;
                     name    kvs
                    u001    ded-k03:30.03
                    u001    ded-k01:10.01
                    u001    ded-k02:20.02
            --  第二步: 将所有属于同一个人的数据组合在一起,，借助于collect_set
             SELEct name,collect_set(concat_ws(':',dedkey,cast(dedval as String))) kvs from  test.emp group by name;
                    name    kvs
                    u001    ["ded-k02:20.02","ded-k03:30.03","ded-k01:10.01"]

            --  第三步： 将数组变成一个字符串，借助于concat_ws
             SELEct name,concat_ws(',',collect_set(concat_ws(':',dedkey,cast(dedval as String)))) kvs from  test.emp group by name;
                    name    kvs
                    u001    ded-k02:20.02,ded-k03:30.03,ded-k01:10.01

            --  第四步:将字符串转成map 使用函数str_to_map(text, delimiter1, delimiter2)
                --  text：是字符串
                --  delimiter1：多个键值对之间的分隔符
                --  delimiter2：key和value之间的分隔符
             SELEct name,str_to_map(concat_ws(',',collect_set(concat_ws(':',dedkey,cast(dedval as String)))),",",":") from test.emp group by name;
                map     deductions
                u001    {"ded-k02":"20.02","ded-k03":"30.03","ded-k01":"10.01"}

with deds as (
             SELEct name,str_to_map(concat_ws(',',collect_set(concat_ws(':',dedkey,cast(dedval as String)))),",",":") deductions from test.emp group by name
            ),
            adds  as(
             SELEct name,collect_set(named_struct('stree',stree,'city',city,'state',state,'zip',zip)) address from  test.emp group by name
            ),
            subs as(
                SELEct name,collect_list(subord) subordinates  from   test.emp  group by name
            )
            insert  into table test.employee
            SELEct
                coalesce(adds.name,deds.name,subs.Name) name,
                25000,
                subs.subordinates,
                deds.deductions,
                adds.address
            from deds
            full join adds on deds.name=adds.name
            full join subs on deds.name=subs.name;

SELECT * from  test.employee;
  employee.name    employee.salary        employee.subordinates            employee.deductions
   u001                25000            ["sub002","sub003","sub001"]    {"ded-k02":"20.02","ded-k03":"30.03","ded-k01":"10.01"}
  employee.address
  [{"stree":"china-02","city":"shanghai","state":"use","zip":100001},{"stree":"china-03","city":"lanzhou","state":"use","zip":100002},{"stree":"china","city":"beijing","state":"use","zip":100000}]