1 创建表 hive命令行操作

　　

CREATE TABLE IF NOT EXISTS emp(name STRING,salary FLOAT,subordinates ARRAY
     
      ,deductions MAP
      
       ,address STRUCT
       
        )PARTITIONED BY (province STRING,city STRING)ROW FORMAT DELIMITEDFIELDS TERMINATED BY '\t'COLLECTION ITEMS TERMINATED BY ','MAP KEYS TERMINATED BY ':’;
       
      
     

　　

2 造数据

　　shanxi.txt

　　

zj1	10000	james,datacloase	jim:1.2,james:2.1,lilly:3.8	huaxing,xian,shanxi,1zj2	10000	james,datacloase	jim:1.2,james:2.1,lilly:3.8	huaxing,xian,shanxi,2zj3	10000	james,datacloase	jim:1.2,james:2.1,lilly:3.8	huaxing,xian,shanxi,3zj4	10000	james,datacloase	jim:1.2,james:2.1,lilly:3.8	huaxing,xian,shanxi,4zj5	10000	james,datacloase	jim:1.2,james:2.1,lilly:3.8	huaxing,xian,shanxi,5zj6	10000	james,datacloase	jim:1.2,james:2.1,lilly:3.8	huaxing,xian,shanxi,6

　　hunan.txt

zbq1	10000	james,datacloase	jim:1.2,james:2.1,lilly:3.8	huaxing,zhangjiajie,hunan,1zbq2	10000	james,datacloase	jim:1.2,james:2.1,lilly:3.8	huaxing,zhangjiajie,hunan,2zbq3	10000	james,datacloase	jim:1.2,james:2.1,lilly:3.8	huaxing,zhangjiajie,hunan,3zbq4	10000	james,datacloase	jim:1.2,james:2.1,lilly:3.8	huaxing,zhangjiajie,hunan,4zbq5	10000	james,datacloase	jim:1.2,james:2.1,lilly:3.8	huaxing,zhangjiajie,hunan,5

　　

3 导入数据 hive命令行操作

　　

LOAD DATA LOCAL INPATH '/tmp/logs/shanxi.txt' OVERWRITE INTO TABLE empPARTITION(province='shanxi',city='xian’);LOAD DATA LOCAL INPATH '/tmp/logs/hunan.txt' OVERWRITE INTO TABLE empPARTITION(province='hunan',city='zhangjiajie’);

　

4 查询hive数据

hive表结构　　

 hive> describe extended emp;　

查询hive分区数据

hive> select * from emp where province='shanxi' and city = 'xian';

 

 

5 查看HDFS的hive目录

[root@hdp1 /tmp/logs]#hdfs dfs -ls /user/hive/warehouse/emp

 

6 删除hive中hunan的分区

　　

A 进入hive使用的MySQL 

B 切换为hive数据库

mysql> use hive;

C 查询相关表

mysql> SELECT * FROM TBLS WHERE TBL_NAME='emp';

 

发现出现1条记录，所以使用TBLS表的SD_ID字段去SDS表查询LOCATION字段的值，通过LOCATION字段，就可以知道emp这张表的数据库，TBLS中TBL_ID为6的这行记录就是我要查询的表的ID

mysql> select * from SDS where SD_ID=6;

 

接下来，要根据TBLS表的TBL_ID和hive表分区字段的值（模糊查询）去PARTITIONS表查询，需要得到PARTITIONS表的PART_ID的值（2）

mysql> select * from PARTITIONS t where t.tbl_id=6 and PART_NAME like '%hunan%';

 

D开始删除

　　最后，通过TBLS表的TBL_ID（70）和PARTITIONS表的PART_ID（202354）就可以删除hive的分区了

mysql> mysql> delete from PARTITION_KEY_VALS where part;

Query OK, 0 rows affected (0.00 sec)

mysql> delete from PARTITION_KEY_VALS where part_id=2;

Query OK, 2 rows affected (0.00 sec)

mysql> delete from PARTITION_PARAMS where part_id=2;

Query OK, 6 rows affected (0.01 sec)

mysql> delete from PARTITIONS where tbl_id=6 and part_id=2;

Query OK, 1 row affected (0.00 sec)

删除hdfs相关分区目录

[root@hdp1 /root]#hdfs dfs -rm -r "/user/hive/warehouse/emp/province=hunan"

Deleted /user/hive/warehouse/emp/province=hunan

7 查询分区是否删除

已经查询不出来hunan的分区数据了

hive> select * from emp where province='hunan';

OK

Time taken: 0.073 seconds