最近在hive学习时遇到drop table时卡死,删除失败的情况。
先看一下表的情况:
0: jdbc:hive2://hadoop102:10000> desc dy_name_table
. . . . . . . . . . . . . . . .> ;
+--------------------------+-----------------------+-----------------------+
| col_name | data_type | comment |
+--------------------------+-----------------------+-----------------------+
| sno | int | |
| sex | string | |
| | NULL | NULL |
| # Partition Information | NULL | NULL |
| # col_name | data_type | comment |
| | NULL | NULL |
| sex | string | |
+--------------------------+-----------------------+-----------------------+
dy_name_table表的分区情况
0: jdbc:hive2://hadoop102:10000> show partitions dy_name_table;
+---------------------------------+
| partition |
+---------------------------------+
| sex=? |
| sex=__HIVE_DEFAULT_PARTITION__ |
+---------------------------------+
hdfs上的分区信息如下:
可以看出分区字段值是中文,大体判断这就是导致删不掉的原因。
解决方法:
删掉分区字段就行。删掉分区就行。
这时候如果用hive的语句的话会报错。
0: jdbc:hive2://hadoop102:10000> alter table dy_name_table drop partition=(sex='男');
Error: Error while compiling statement: FAILED: ParseException line 1:40 extraneous input '=' expecting ( near '<EOF>' (state=42000,code=40000)
所以我们要去mysql中删掉对应的元数据信息。因为hive的元数据都存在mysql里。在mysql里选择hive数据库。
mysql> select * from TBLS;
+--------+-------------+-------+------------------+-------+-----------+-------+---------------+---------------+--------------------+--------------------+--------------------+
| TBL_ID | CREATE_TIME | DB_ID | LAST_ACCESS_TIME | OWNER | RETENTION | SD_ID | TBL_NAME | TBL_TYPE | VIEW_EXPANDED_TEXT | VIEW_ORIGINAL_TEXT | IS_REWRITE_ENABLED |
+--------+-------------+-------+------------------+-------+-----------+-------+---------------+---------------+--------------------+--------------------+--------------------+
| 1 | 1605202643 | 2 | 0 | root | 0 | 1 | student | MANAGED_TABLE | NULL | NULL | |
| 6 | 1605208007 | 2 | 0 | root | 0 | 6 | friend | MANAGED_TABLE | NULL | NULL | |
| 11 | 1605604870 | 1 | 0 | root | 0 | 11 | test | MANAGED_TABLE | NULL | NULL | |
| 12 | 1605606030 | 1 | 0 | root | 0 | 12 | ip_table | MANAGED_TABLE | NULL | NULL | |
| 13 | 1605606059 | 1 | 0 | root | 0 | 13 | day_table | MANAGED_TABLE | NULL | NULL | |
| 16 | 1605613385 | 1 | 0 | root | 0 | 16 | student | MANAGED_TABLE | NULL | NULL | |
| 17 | 1605626713 | 1 | 0 | root | 0 | 17 | dy_name_table | MANAGED_TABLE | NULL | NULL | |
| 23 | 1605637503 | 1 | 0 | root | 0 | 23 | part_stu | MANAGED_TABLE | NULL | NULL | |
| 26 | 1605650736 | 1 | 0 | root | 0 | 26 | f_name | MANAGED_TABLE | NULL | NULL | |
| 32 | 1605663970 | 1 | 0 | root | 0 | 32 | stu_part | MANAGED_TABLE | NULL | NULL | |
+--------+-------------+-------+------------------+-------+-----------+-------+---------------+---------------+--------------------+--------------------+--------------------+
TBLS表里是所有HIVE表的信息,找到目标表的TBL_ID.
mysql> select * from PARTITIONS;
+---------+-------------+------------------+--------------------------------+-------+--------+
| PART_ID | CREATE_TIME | LAST_ACCESS_TIME | PART_NAME | SD_ID | TBL_ID |
+---------+-------------+------------------+--------------------------------+-------+--------+
| 1 | 1605627480 | 0 | sex=? | 18 | 17 |
| 2 | 1605627480 | 0 | sex=__HIVE_DEFAULT_PARTITION__ | 19 | 17 |
+---------+-------------+------------------+--------------------------------+-------+--------+
##可以看到PART_ID=1的分区sex=?是有问题的。
mysql> delete from PARTITIONS WHERE TBL_ID=17 AND PART_ID=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`hive`.`PARTITION_KEY_VALS`, CONSTRAINT `PARTITION_KEY_VALS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`))
##删除失败是有外键约束,从报错信息可以看出级联表是PARTITION_KEY_VALS,所以我们要去PARTITION_KEY_VALS中删除数据
##查看PARTITION_KEY_VALS的数据。
mysql> select * from PARTITION_KEY_VALS;
+---------+----------------------------+-------------+
| PART_ID | PART_KEY_VAL | INTEGER_IDX |
+---------+----------------------------+-------------+
| 1 | ? | 0 |
| 2 | __HIVE_DEFAULT_PARTITION__ | 0 |
+---------+----------------------------+-------------+
2 rows in set (0.00 sec)
###删除PART_ID=1的数据
mysql> delete from PARTITION_KEY_VALS where PART_ID=1;
Query OK, 1 row affected (0.00 sec)
###再次删除PARTITIONS表中的数据
mysql> delete from PARTITIONS WHERE TBL_ID=17 AND PART_ID=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`hive`.`PARTITION_PARAMS`, CONSTRAINT `PARTITION_PARAMS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`))
###删除失败是有外键约束,从报错信息可以看出级联表是PARTITION_PARAMS,所以我们要去PARTITION_PARAMS中删除数据
###查看PARTITION_PARAMS的数据
mysql> select * from PARTITION_PARAMS;
+---------+-----------------------+------------------------+
| PART_ID | PARAM_KEY | PARAM_VALUE |
+---------+-----------------------+------------------------+
| 1 | COLUMN_STATS_ACCURATE | {"BASIC_STATS":"true"} |
| 1 | numFiles | 1 |
| 1 | numRows | 0 |
| 1 | rawDataSize | 0 |
| 1 | totalSize | 4 |
| 1 | transient_lastDdlTime | 1605627480 |
| 2 | COLUMN_STATS_ACCURATE | {"BASIC_STATS":"true"} |
| 2 | numFiles | 1 |
| 2 | numRows | 0 |
| 2 | rawDataSize | 0 |
| 2 | totalSize | 3 |
| 2 | transient_lastDdlTime | 1605627480 |
+---------+-----------------------+------------------------+
###删除PART_ID=1的数据
mysql> delete from PARTITION_PARAMS where PART_ID=1;
Query OK, 6 rows affected (0.01 sec)
###再次删除PARTITIONS表中的数据
mysql> delete from PARTITIONS WHERE TBL_ID=17 AND PART_ID=1;
Query OK, 1 row affected (0.09 sec)
去hivel里删除表
查看表的信息
0: jdbc:hive2://hadoop102:10000> desc dy_name_table;
+--------------------------+-----------------------+-----------------------+
| col_name | data_type | comment |
+--------------------------+-----------------------+-----------------------+
| sno | int | |
| sex | string | |
| | NULL | NULL |
| # Partition Information | NULL | NULL |
| # col_name | data_type | comment |
| | NULL | NULL |
| sex | string | |
+--------------------------+-----------------------+-----------------------+
7 rows selected (0.35 seconds)
###删除表
0: jdbc:hive2://hadoop102:10000> drop table dy_name_table;
No rows affected (3.662 seconds)
###查看结果
0: jdbc:hive2://hadoop102:10000> show tables;
+------------+
| tab_name |
+------------+
| day_table |
| f_name |
| ip_table |
| part_stu |
| stu_part |
| student |
| test |
+------------+
###dy_name_table已经不在数据库里了,删除成功。
下面是找出mysql中与一个表外键关联的所有表的方法,不需要等到报错才知道:
mysql> select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME='PARTITIONS';
###+--------------------+-------------------+------------------------+---------------+--------------+--------------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------------+-------------------+------------------------+---------------+--------------+--------------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| NULL | hive | PARTITION_KEY_VALS_FK1 | NULL | hive | PARTITION_KEY_VALS | PART_ID | 1 | 1 | hive | PARTITIONS | PART_ID |
| NULL | hive | PARTITION_PARAMS_FK1 | NULL | hive | PARTITION_PARAMS | PART_ID | 1 | 1 | hive | PARTITIONS | PART_ID |
| NULL | hive | PART_COL_PRIVS_FK1 | NULL | hive | PART_COL_PRIVS | PART_ID | 1 | 1 | hive | PARTITIONS | PART_ID |
| NULL | hive | PART_COL_STATS_FK | NULL | hive | PART_COL_STATS | PART_ID | 1 | 1 | hive | PARTITIONS | PART_ID |
| NULL | hive | PART_PRIVS_FK1 | NULL | hive | PART_PRIVS | PART_ID | 1 | 1 | hive | PARTITIONS | PART_ID |
+--------------------+-------------------+------------------------+---------------+--------------+--------------------+-------------+------------------+--------
参考:https://blog.csdn.net/qq1226317595/article/details/84306519