Hive集合类型Array,Map,Struct的使用

1. Array

   创建测试表

   CREATE TABLE IF NOT EXISTS TEST.test1( 
       id int,
   	name string,
       hobby ARRAY<String>
   ) COMMENT '用户测试表1' 
   ROW FORMAT delimited fields terminated BY '\t' 
   collection items terminated BY ',' 
   STORED AS TEXTFILE;
   

   插入数据

   新建文本数据

   1	zhangsan	唱歌,跳舞,游泳
   2	lisi	打游戏,篮球
   

   LOAD DATA [LOCAL] inpath '/root/array.txt' INTO TABLE test.test1;
   

   SQL插入单条数据

   INSERT INTO TABLE test.test1 
   select 3,'wangwu',array('唱歌','跳舞') as hooby;
   

   查询结果

   SELECT id,name,hobby,hobby[0],hobby[1],hobby[2] FROM test.test1;	
   

   
   beeline方式查询

   !connect jdbc:hive2://192.168.1.101:10000 -n root
   

   [root@node00 ~]# beeline 
   Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
   Java HotSpot(TM) 64-Bit Server VM warning: Using incremental CMS is deprecated and will likely be removed in a future release
   Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
   Beeline version 1.1.0-cdh5.12.1 by Apache Hive
   beeline> !connect jdbc:hive2://192.168.1.101:10000 -n root
   scan complete in 1ms
   Connecting to jdbc:hive2://192.168.1.101:10000
   Connected to: Apache Hive (version 1.1.0-cdh5.12.1)
   Driver: Hive JDBC (version 1.1.0-cdh5.12.1)
   Transaction isolation: TRANSACTION_REPEATABLE_READ
   0: jdbc:hive2://192.168.1.101:10000>  select id,name,hobby[0],hobby[1],hobby[2] FROM test.test1;
   INFO  : Compiling command(queryId=hive_20190509150303_6235e025-3df8-45c7-9624-5ead2f0ec4e1): select id,name,hobby[0],hobby[1],hobby[2] FROM test.test1
   INFO  : Semantic Analysis Completed
   INFO  : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:id, type:int, comment:null), FieldSchema(name:name, type:string, comment:null), FieldSchema(name:_c2, type:string, comment:null), FieldSchema(name:_c3, type:string, comment:null), FieldSchema(name:_c4, type:string, comment:null)], properties:null)
   INFO  : Completed compiling command(queryId=hive_20190509150303_6235e025-3df8-45c7-9624-5ead2f0ec4e1); Time taken: 0.213 seconds
   INFO  : Executing command(queryId=hive_20190509150303_6235e025-3df8-45c7-9624-5ead2f0ec4e1): select id,name,hobby[0],hobby[1],hobby[2] FROM test.test1
   INFO  : Query ID = hive_20190509150303_6235e025-3df8-45c7-9624-5ead2f0ec4e1
   INFO  : Total jobs = 1
   INFO  : Launching Job 1 out of 1
   INFO  : Starting task [Stage-1:MAPRED] in serial mode
   INFO  : Number of reduce tasks is set to 0 since there's no reduce operator
   INFO  : number of splits:1
   INFO  : Submitting tokens for job: job_1557221110563_0412
   INFO  : The url to track the job: http://bigdata-dev-41:8088/proxy/application_1557221110563_0412/
   INFO  : Starting Job = job_1557221110563_0412, Tracking URL = http://bigdata-dev-41:8088/proxy/application_1557221110563_0412/
   INFO  : Kill Command = /opt/cloudera/parcels/CDH-5.12.1-1.cdh5.12.1.p0.3/lib/hadoop/bin/hadoop job  -kill job_1557221110563_0412
   INFO  : Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
   INFO  : 2019-05-09 15:03:13,210 Stage-1 map = 0%,  reduce = 0%
   INFO  : 2019-05-09 15:03:18,405 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.91 sec
   INFO  : MapReduce Total cumulative CPU time: 1 seconds 910 msec
   INFO  : Ended Job = job_1557221110563_0412
   INFO  : MapReduce Jobs Launched: 
   INFO  : Stage-Stage-1: Map: 1   Cumulative CPU: 1.91 sec   HDFS Read: 4385 HDFS Write: 92 SUCCESS
   INFO  : Total MapReduce CPU Time Spent: 1 seconds 910 msec
   INFO  : Completed executing command(queryId=hive_20190509150303_6235e025-3df8-45c7-9624-5ead2f0ec4e1); Time taken: 11.179 seconds
   INFO  : OK
   +-----+-----------+-------------------+------+------+-------+--+
   | id  |   name    |       hobby       | _c3  | _c4  |  _c5  |
   +-----+-----------+-------------------+------+------+-------+--+
   | 1   | zhangsan  | ["唱歌","跳舞","游泳"]  | 唱歌   | 跳舞   | 游泳    |
   | 2   | lisi      | ["打游戏","篮球"]      | 打游戏  | 篮球   | NULL  |
   | 3   | wangwu    | ["唱歌","游泳"]       | 唱歌   | 游泳   | NULL  |
   +-----+-----------+-------------------+------+------+-------+--+
   3 rows selected (11.803 seconds)
   0: jdbc:hive2://192.168.1.101:10000> 
   

2. Map

   创建测试表

   CREATE TABLE IF NOT EXISTS TEST.t_map( 
       id int,
   	name string,
       hobby MAP<String,String>
   ) COMMENT 'Map测试表' 
   ROW FORMAT delimited fields terminated BY '\t' 
   collection items terminated BY ',' 
   MAP keys terminated BY ':' 
   STORED AS TEXTFILE;
   

   插入数据

   新建文本数据

   1	张三	唱歌:非常喜欢,跳舞:喜欢,游泳:一般般
   2	李四	打游戏:非常喜欢,篮球:不喜欢
   

   LOAD DATA [LOCAL] inpath '/root/map.txt' INTO TABLE test.t_map;
   

   SQL插入单条数据

   INSERT INTO TABLE test.t_map 
   SELECT 3,'王五',map('唱歌','非常喜欢','打游戏','喜欢') AS hooby;
   

   查询结果

   SELECT id,name,hobby,hobby['唱歌'],hobby['跳舞'],hobby['游泳'],hobby['打游戏'],hobby['篮球'] FROM test.t_map;	
   

   

3. Struct

   创建测试表

   CREATE TABLE IF NOT EXISTS TEST.t_struct( 
       id int,
   	name string,
       address STRUCT<country:String,city:String>
   ) COMMENT 'Struct测试表' 
   ROW FORMAT delimited fields terminated BY '\t' 
   collection items terminated BY ',' 
   STORED AS TEXTFILE;
   

   插入数据

   新建文本数据

   1	张三	China,BeiJing
   2	李四	USA,NewYork
   

   LOAD DATA [LOCAL] inpath '/root/struct.txt' INTO TABLE test.t_struct;
   

   SQL插入单条数据

   INSERT INTO TABLE test.t_struct 
   SELECT 3,'王五',named_struct('country','China','city','ShangHai') AS address;
   

   查询结果

   SELECT id,name,address.country,address.city FROM test.t_struct;	
   

   

4. 集合类型组合

   创建测试表

   CREATE TABLE IF NOT EXISTS TEST.array_struct ( 
       id string,
       values ARRAY<STRUCT<k1:String,
                           k2:String,
                           k3:String
                          >
                   > 
   ) COMMENT '组合类型测试表' 
   ROW FORMAT delimited fields terminated BY '\t' 
   collection items terminated BY ',' 
   MAP KEYS TERMINATED BY '-' 
   STORED AS TEXTFILE;
   

   插入数据

   新建文本数据

   1	a1-a2-a3,a11-a22-a33,a111-a222-a333
   2	a1-a2-a3,a11-a22-a33
   3	a1-a2,a11--a33,-a222-
   

   LOAD DATA [LOCAL] inpath '/root/arr_struct.txt' INTO TABLE test.array_struct;
   

   SQL插入单条数据
   4 a1--,-a22-,--

   INSERT INTO TABLE test.array_struct 
   SELECT '4',array(named_struct('k1','a1','k2','','k3',''),named_struct('k1','','k2','a22','k3',''),named_struct('k1','','k2','','k3','')) AS values;
   

   查询结果

   SELECT id,values[0],values[0].k1,values[1],values[1].k2,values[2],values[2].k3,values FROM test.array_struct;