Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。
创建数据表

  1. create table test(
  2. name string,
  3. friends array<string>,
  4. children map<string, int>,
  5. address struct<street:string, city:string>
  6. )
  7. row format delimited fields terminated by ','
  8. collection items terminated by '_'
  9. map keys terminated by ':'
  10. lines terminated by '\n';

查看数据结构

  1. hive (default)> desc test;
  2. OK
  3. col_name    data_type    comment
  4. name                    string                                      
  5. friends                 array<string>                               
  6. children                map<string,int>                             
  7. address                 struct<street:string,city:string>                        
  8. Time taken: 0.053 seconds, Fetched: 4 row(s)

测试数据

  1. liming,zhangsan_lisi,xiao ming:12_xiaoxiao ming:3,haidian_beijing
  2. wangwu,zhaoliu_sunba_qianer,xiao wang:18_xiaoxiao wang:9,chao yang_beijing

加载测试数据

  1. hive (default)> load data local inpath '/opt/module/data/people.txt' into table test;
  2. Loading data to table default.test
  3. Table default.test stats: [numFiles=1, numRows=0, totalSize=141, rawDataSize=0]
  4. OK
  5. Time taken: 0.354 seconds

查看数据内容

  1. hive (default)> select *from test;
  2. OK
  3. test.name    test.friends    test.children    test.address
  4. liming    ["zhangsan","lisi"]    {"xiao ming":12,"xiaoxiao ming":3}    {"street":"haidian","city":"beijing"}
  5. wangwu    ["zhaoliu","sunba","qianer"]    {"xiao wang":18,"xiaoxiao wang":9}    {"street":"chao yang","city":"beijing"}
  6. Time taken: 0.069 seconds, Fetched: 2 row(s)

Mysql 查看hive表结构

  1. mysql root@localhost:(none)> use hive;
  2. You are now connected to database "hive" as user "root"
  3. Time: 0.001s
  4. mysql root@localhost:hive> select *from `TBLS`;
  5. +--------+-------------+-------+------------------+-------+-----------+-------+------------+---------------+--------------------+--------------------+
  6. | TBL_ID | CREATE_TIME | DB_ID | LAST_ACCESS_TIME | OWNER | RETENTION | SD_ID | TBL_NAME   | TBL_TYPE      | VIEW_EXPANDED_TEXT | VIEW_ORIGINAL_TEXT |
  7. +--------+-------------+-------+------------------+-------+-----------+-------+------------+---------------+--------------------+--------------------+
  8. | 1      | 1566392699  | 6     | 0                | root  | 0         | 1     | docs       | MANAGED_TABLE | <null>             | <null>             |
  9. | 2      | 1566392821  | 6     | 0                | root  | 0         | 2     | word_count | MANAGED_TABLE | <null>             | <null>             |
  10. +--------+-------------+-------+------------------+-------+-----------+-------+------------+---------------+--------------------+--------------------+
  12. 2 rows in set
  13. Time: 0.012s

查看表的字段

  1. mysql root@localhost:hive> select *from `COLUMNS_V2`
  2. +-------+---------+-------------+-----------+-------------+
  3. | CD_ID | COMMENT | COLUMN_NAME | TYPE_NAME | INTEGER_IDX |
  4. +-------+---------+-------------+-----------+-------------+
  5. | 1     | <null>  | line        | string    | 0           |
  6. | 2     | <null>  | count       | bigint    | 1           |
  7. | 2     | <null>  | word        | string    | 0           |
  8. +-------+---------+-------------+-----------+-------------+
  10. 3 rows in set
  11. Time: 0.012s

加载数据到hive中

LOAD DATA LOCAL（本地文件需要添加LOCAL） INPATH ‘文件路径’ OVERWRITE INTO TABLE 表名称;