问题描述
我正在使用AS子句从另一个表创建一个临时表，其中包括另一个表的分区列也属于临时表，然后出现以下错误.下面是表create语句，其中col4是表xyz的分区列.
I am creating a temp table from another table using AS clause where I am including the partition column of another table also be part of temp table and then I am getting the below error. Below is the table create statement where col4 is the partition column of table xyz.
在运行create语句时，出现以下错误.当我从create语句中删除col4时，它运行正常.
And while running the create statement i am getting the below error. And when I am removing the col4 from the create statement its running fine.
错误:
请帮助.
示例:
CREATE TEMPORARY TABLE abc STORED AS PARQUET AS SELECT
col1 AS col1,
col2 AS col2,
col3 AS col3,
col4 AS col4
FROM xyz;
推荐答案
这是源表xyz的问题，因为它包含分区__HIVE_DEFAULT_PARTITION__
This is a problem with source table xyz because it contains partition __HIVE_DEFAULT_PARTITION__
在动态分区模式下，插入的分区值为NULL时，Hive创建一个值为__HIVE_DEFAULT_PARTITION__的分区.
Hive creates a partition with value __HIVE_DEFAULT_PARTITION__ when in dynamic partition mode inserted partition value is NULL.
分区__HIVE_DEFAULT_PARTITION__与数字类型不兼容，这会导致错误，因为无法将其强制转换为数字类型.
Partition __HIVE_DEFAULT_PARTITION__ is not compatible with numeric type and this causing error because it cannot be cast to numeric type.
要删除或查询该分区，您需要先将列类型更改为字符串:
To remove or query this partition, you need to change the column type to string first:
ALTER TABLE xyz PARTITION COLUMN (col4 string);
当然，您可能需要备份表并检查数据，然后再删除并决定如何处理此数据.
Of course you may want to backup table and check the data before removing and decide what to do with this data.
要删除分区:
ALTER TABLE xyz DROP PARTITION (col4 = '__HIVE_DEFAULT_PARTITION__');
删除分区后，您可以将分区列的类型改回数字类型.
After removing partition you can change the type of partition column back to numeric type.
这篇关于从另一个表(包括配置单元中的分区列)创建临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！