进入Hive表-非分区表到具有多个分区的分区表-无法插入目标表，因为列号/类型 [英] into Hive table - Non Partitioned table to Partitioned table having multiple partitions - Cannot insert into target table because column number/types

问题描述

当我尝试插入分区表时，出现以下错误:

When I tried to insert into a Partiotioned table I am getting the bellow error:

SemanticException [错误10044]:行1:23无法插入目标表，因为列号/类型不同:表insclause-0有6列，并且3列已分区，我们不需要任何必须转储的过滤器/存储从非分区表到分区表.

SemanticException [Error 10044]: Line 1:23 Cannot insert into target table because column number/types are different : Table insclause-0 has 6 columns, and the 3 columns are partitioned and we not required any filters we have to dump/store from non partitioned table to partitioned table.

我的桌子:

来源:

id name   salary dep
1  sai    1000   sales
2  syam   2000   hr
3  sundar 3000   bank

目标:

id name   salary dep
1  sai    1000   sales
2  syam   2000   hr
3  sundar 3000   bank

分区(名称字符串，dep字符串)

partition (name string, dep string)

请让我了解如何从源复制到目标

Please let me how to copy from source to target

尝试以下方式.

insert into target_partitioned_table partition(name,dep) select id from source_table;

推荐答案

您应列出select中的所有列，分区列应排在最后并且顺序相同.列的顺序很重要.

You should list all columns in the select, partition columns should be the last and in the same order. Order of columns matters.

检查表DDL.如果按名称和部门进行分区，则分区列应为最后一列: id，salary，name，dep .如果列按您的问题中的顺序排序，则该表看起来不像按(Name，Dep)分区，或者文件或数据示例中的列顺序错误.按DESCRIBE命令返回的顺序插入列.

Check the table DDL. If it is partitioned by Name and Dep, then partition columns should be the last: id, salary, name, dep. If columns ordered like in your question, it does not look like the table is partitioned by (Name, Dep), or the order of columns is wrong in the file or in your data example. Insert columns in the same order which DESCRIBE command returns.

查询应以完全相同的顺序包含所有列.

Query should contain all columns in exactly the same order.

对于静态分区加载，您不需要在select中使用分区列，分区规范中的值是静态的:

For static partition load you do not need partition columns in the select, values are static in the partition spec:

insert into table target_partitioned_table partition(name='Some Name',dep='Sales')
select id, salary from source_table;

对于动态分区加载(分区是从数据集中获取的，并且应该以相同的顺序在选择中):

For dynamic partition load (partitions are taken from the dataset and should be in the select in the same order):

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

insert into table target_partitioned_table partition(name,dep)
select id, salary, name, dep from source_table;

这篇关于进入Hive表-非分区表到具有多个分区的分区表-无法插入目标表，因为列号/类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！