Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

INSERT to hive partitioned table fails when the target table is created during run-time with error: "Need to specify partition columns because the destination table is partitioned."
Problem Description
Hive mapping is configured to CREATE hive partitioned table by specifying the DDL query and then load the data.


When the mapping runs, the partitioned table is successfully created but INSERT fails with the following error.

Even the execution plan for insert does not include ‘partition by’ clause in the 'insert into' statement and fails with the following error:

 

2017-07-17 17:45:39.311 <pool-9-thread-10> SEVERE: [HIVE_1054] The Integration Service failed to run the Hive task [exec1]. See the additional error messages for more information.

Exception Class: [com.informatica.sdk.dtm.ExecutionException] Exception Message: [[HIVE_1070] The Integration Service failed to run Hive query [exec1_query_1] for task [exec1] due to following error: Hive error code [40,000], Hive message [FAILED: SemanticException 0:0 Need to specify partition columns because the destination table is partitioned. Error encountered near token 'partition_test2'], Hive SQL state [42000].].​

Cause

This issue occurs when the query specified to CREATE the hive partitioned table is not as expected by Informatica.

Solution

The syntax to create DDL of partitioned hive table should be:


CREATE TABLE {INFA_TABLE_NAME}

( {INFA_COLUMN_LIST} )

PARTITIONED BY ({INFA_PORT_SELECTOR: PortSelector_partition})

Note


Our DDL code generation logic is intelligent enough to know which columns are part of the partitioned by clause and then it removes them from the main column list of the table internally. It also adds data type within partitioned by clause; as expected by Hive syntax.


Example

CREATE TABLE {partition_test4}({a1}) PARTITIONED BY ({INFA_PORT_SELECTOR: PortSelector});


where PortSelector is the port selector defined in the target object to define the partition columns.

Here is the screenshot of port selector defined at target:


port_selector.png
After this, the query execution plan is created as follows:


 kb_partition.png
 

Attached is the mapping xml from 10.1.1 environment for reference.

More Information
Applies To
Product: Data Engineering Integration(Big Data Management)
Problem Type: Configuration
User Type: Architect; Developer
Project Phase: Configure; Implement
Product Version:
Database:
Operating System:
Other Software:

Reference
Attachments
​​CopyOf_map_partition_test.xmlCopyOf_map_partition_test.xml

Mapping xml from 10.1.1 MRS.
Last Modified Date:7/30/2019 3:02 AMID:516266
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)