从另一个表(包括配置单元中的分区列)创建临时表 [英] Creating temp table from another table including partition column in hive

问题描述

我正在使用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.

错误:

编译语句时出错:FAILED:NumberFormatException为 输入字符串:"HIVE_DEFAULT_PARTITION"(状态= 42000，代码= 40000)

Error while compiling statement: FAILED: NumberFormatException For input string: "HIVE_DEFAULT_PARTITION" (state=42000,code=40000)

请帮助.

示例:

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.

这篇关于从另一个表(包括配置单元中的分区列)创建临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！