hive的map类型处理

--首先进行一些基础配置设置：

SET mapred.job.queue.name=queue3;

SET hbase.client.scanner.caching=5000;

SET hbase.zookeeper.quorum=datanode06,datanode07,datanode08;

SET zookeeper.znode.parent=/hbase;

--然后对hbase的表建立外部表：

CREATE EXTERNAL TABLE wizad_test_geely_20141017 (

rowkey string,

fixeddim_map map<STRING,STRING>,

social_map map<STRING,STRING>

) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'

WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,fixeddim:,social:")

TBLPROPERTIES ("hbase.table.name" = "wizad_mdm_task_geely_hk_20141017");

--查询时，需要用Lateral View语法将（map）值展开为一个新的虚拟表：

--lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。

SELECT social_key,count(DISTINCT rowkey) FROM wizad_test_geely_20141017

LATERAL VIEW explode(fixeddim_map) myTable1 AS fixeddim_key,fixeddim_value

LATERAL VIEW explode(social_map) myTable2 AS social_key,social_value

WHERE (social_key='sex_f' or social_key='sex_m') and (social_value>50)

GROUP BY social_key;

=========================================================================

CREATE EXTERNAL TABLE lxw2 

(

key string,

value map<STRING,STRING>

) 

STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'

WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,fixeddim:")

TBLPROPERTIES ("hbase.table.name" = "wizad_mdm_task_geely_hk_20141014"); 

 

SELECT KEY,dim_name,dim_value FROM lxw2 

LATERAL VIEW explode(VALUE) myTable1 AS dim_name,dim_value

--WHERE KEY = '000000054153796'

hive支持8个层级的分隔符，依次是\001,\002,\003...\008（HIVE nested ARRAY in MAP data type）

map嵌套使用

map的key分隔符指定

hive> explain

    > create table t3(foo int,bar MAP<STRING, MAP<STRING,STRING>>)

    > row format delimited fields terminated by '\t' 

    > collection items terminated by ','

    > MAP KEYS TERMINATED BY ':'

> STORED AS TEXTFILE;

map元素用分隔符\004（就是','），key通过分隔符\005（就是':'）指示；

map只有一个元素数据：\tkey_out0:key_in01\005val_in01\004key_in02\005val_in02

实际结构:{"key_out0":{"key_in01":"val_in01","key_in02":"val_in02"}}

外层map两个元素：1\tkey_out0:key_in01\005val_in01\004key_in02\005val_in02,key_out1:key_in01\005val_in01\004key_in02\005val_in02

结构： {"key_out0":{"key_in01":"val_in01","key_in02":"val_in02"},"key_out1":{"key_in01":"val_in01","key_in02":"val_in02"}}

map的使用

1建表:

hive> CREATE TABLE t3 (foo STRING, bar MAP<STRING,INT>)

    > ROW FORMAT DELIMITED

    > FIELDS TERMINATED BY '/t'

    > COLLECTION ITEMS TERMINATED BY ','

    > MAP KEYS TERMINATED BY ':'

    > STORED AS TEXTFILE;

2,load本地数据：路径默认

LOAD DATA LOCAL INPATH 'test.txt' OVERWRITE INTO TABLE t3;

3使用：

select foo,bar['a'] from t3 where id='1'; --使用下标访问map

select foo,size(bar) from t3 where id='1'; #查看map长度

select ip,uid from t3 where dt='20130101'  where array_contains(map_keys(gameinfo),'wow');#查看map的key，找出有玩wow游戏的记录