一、利用搭好的环境完成简单Hive复杂数据类型创建查询

前置准备

在cdh01上执行以下操作，确保HDFS上存在/user/root目录（只需执行一次）

# 以 hdfs 超级用户身份创建目录并授权

sudo -u hdfs hadoop fs -mkdir -p /user/root

sudo -u hdfs hadoop fs -chown root:root /user/root

# 验证权限

hadoop fs -ls /user/ | grep root

Array类型

创建本地数据文件array.txt

cat > /tmp/array.txt <<EOF

yaya        beijing,shanghai,tianjin,hangzhou

lucy        shanghai,chengdu,wuhan,shenzhen

EOF

上传至HDFS

hadoop fs -mkdir -p /user/root/array

hadoop fs -put -f /tmp/array.txt /user/root/array/

输入hive进入hive环境，接着进行建表，

CREATE EXTERNAL TABLE array_tab(

        name STRING,

        work_locations ARRAY<STRING>

)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

COLLECTION ITEMS TERMINATED BY ','

LOCATION '/user/root/array';

查询示例

查看全部数据

SELECT * FROM array_tab;

查询在天津工作过的用户

SELECT * FROM array_tab WHERE ARRAY_CONTAINS(work_locations, 'tianjin');

查询每个人的第一工作城市

SELECT name, work_locations[0] FROM array_tab;

以上就是所有代码和图片展示。接下来的Map和Struct是一样的操作，我这里就只展示代码了。

Map类型

创建本地数据文件map.txt

cat > /tmp/map.txt <<EOF

1,yaya,father:yababa#mother:yamama#brother:daya#sister:lussy,28 2,pandas,father:panbaba#mother:panmama#brother:dapan,25 3,ai,father:aibaba#mother:aimama#brother:daai,30 4,ds,father:dsbaba#mother:dsmama#brother:dads,29

EOF

上传至 HDFS

hadoop fs -mkdir -p /user/root/map

hadoop fs -put -f /tmp/map.txt /user/root/map/

Hive 建表

CREATE EXTERNAL TABLE map_tab(

        id INT, name STRING,

        members MAP<STRING,

        STRING>,

        age INT

)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

COLLECTION ITEMS TERMINATED BY '#'

MAP KEYS TERMINATED BY ':'

LOCATION '/user/root/map';

查询示例

查看全部数据

SELECT * FROM map_tab;

查询 yaya 的爸爸

SELECT members['father'] FROM map_tab WHERE name = 'yaya';

Struct 类型

创建本地数据文件struct.txt

cat > /tmp/struct.txt <<EOF

192.168.1.1#yaya:30

192.168.1.2#pandas:50

192.168.1.3#tiger:60

192.168.1.4#lion:70

EOF

上传至 HDFS

hadoop fs -mkdir -p /user/root/struct

hadoop fs -put -f /tmp/struct.txt /user/root/struct/

Hive 建表

CREATE EXTERNAL TABLE struct_tab(

        ip STRING,

        userinfo STRUCT<name:STRING, age:INT>

)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '#'

COLLECTION ITEMS TERMINATED BY ':'

LOCATION '/user/root/struct';

查询示例

查看全部数据

SELECT * FROM struct_tab;

查询所有用户的名字

SELECT userinfo.name FROM struct_tab;

查询访问过 192.168.1.1 的用户名字

SELECT userinfo.name FROM struct_tab WHERE ip = '192.168.1.1';