hive中操作struct与map三例

Myth丶恋晨 2023-01-01 11:53 58阅读 0赞

### 例子一 ###

### **beeline中进行:** ###

create table test (id int,course struct<course:string,score:int>)
    row format delimited
    fields terminated by ','
    collection items terminated by ':';

/home/appleyuchi/test.txt

**中加入以下内容:**

1,english:80  
2,math:89

**beeline中继续:**

load data local inpath '/home/appleyuchi/test.txt' overwrite into table test;

select \* from test;

得到:

+----------+----------------------------------+
    | test.id  |           test.course            |
    +----------+----------------------------------+
    | 1        | {"course":"english","score":80}  |
    | 2        | {"course":"math","score":89}     |
    +----------+----------------------------------+

### **例子二** ###

create table tb_test2 (name string,score_list array<map<string,int>>);
    
    insert into tb_test2 select "A", array(map({"math",100},{"history",85})) from (select 1) x;
    
    insert into tb_test2 select "A", array(map("math",100,"english",90,"history",85)) from (select 1) x;

插入效果如下:

0: jdbc:hive2://Desktop:10000> select * from tb_test2;
    +----------------+-----------------------------------------------+
    | tb_test2.name  |              tb_test2.score_list              |
    +----------------+-----------------------------------------------+
    | A              | [{"math":100},{"english":90},{"history":85}]  |
    | A              | [{"history":85,"english":90,"math":100}]      |
    +----------------+-----------------------------------------------+

### **例子三** ###

create table sales_info_new(
    sku_id string comment '商品id',
    sku_name string comment '商品名称',
    state_map map<string,string> comment '商品状态信息',
    id_array array<string> comment '商品相关id列表'
    )
    partitioned by(
    dt  string comment '年-月-日'
    )
    row format delimited
      fields terminated by '|'
      collection items terminated by ','
      map keys terminated by ':';

**~/test.txt中写入:**

123|华为Mate10|id:1111,token:2222,user_name:zhangsan1|1235,345
    456|华为Mate30|id:1113,token:2224,user_name:zhangsan3|89,635
    789|小米5|id:1114,token:2225,user_name:zhangsan4|452,63
    1235|小米6|id:1115,token:2226,user_name:zhangsan5|785,36
    4562|OPPO Findx|id:1116,token:2227,user_name:zhangsan6|7875,3563

load data local inpath '/home/appleyuchi/test.txt' overwrite into table sales_info_new partition(dt='2019-04-26')

导入效果如下:

0: jdbc:hive2://Desktop:10000> select * from sales_info_new;
    +------------------------+--------------------------+----------------------------------------------------+--------------------------+--------------------+
    | sales_info_new.sku_id  | sales_info_new.sku_name  |              sales_info_new.state_map              | sales_info_new.id_array  | sales_info_new.dt  |
    +------------------------+--------------------------+----------------------------------------------------+--------------------------+--------------------+
    | 123                    | 华为Mate10                | {"id":"1111","token":"2222","user_name":"zhangsan1"} | ["1235","345"]           | 2019-04-26         |
    | 456                    | 华为Mate30                | {"id":"1113","token":"2224","user_name":"zhangsan3"} | ["89","635"]             | 2019-04-26         |
    | 789                    | 小米5                     | {"id":"1114","token":"2225","user_name":"zhangsan4"} | ["452","63"]             | 2019-04-26         |
    | 1235                   | 小米6                     | {"id":"1115","token":"2226","user_name":"zhangsan5"} | ["785","36"]             | 2019-04-26         |
    | 4562                   | OPPO Findx               | {"id":"1116","token":"2227","user_name":"zhangsan6"} | ["7875","3563"]          | 2019-04-26         |
    +------------------------+--------------------------+----------------------------------------------------+--------------------------+--------------------+

**Reference:**

**\[1\][How to insert array<map<string,int>> into hive table?][How to insert array_map_string_int_ into hive table]**

**\[2\][Hive 导入array,map，要定义好分隔符，不用带双引号][Hive _array_map]**

[How to insert array_map_string_int_ into hive table]: https://stackoverflow.com/questions/65537116/how-to-insert-arraymapstring-int-into-hive-table/65537127?noredirect=1#comment115869484_65537127
[Hive _array_map]: https://blog.csdn.net/u010002184/article/details/89640949