Unable to execute statement INSERT INTO SELECT

[#1]

Hi,

I'm trying to integrate the ODBC connectivity to Hortonworks plateform into our marketing data management software

I'm having an issue runing a query INSERT INTO TableX (Col1) SELECT col1 from Table1 UNION SELECT col2 FROM Table2

using the ODBC connection where as the exact same statement works fine when run through Hive View

Here are some details :

Structure of tables used as sources:

CREATE TABLE wkf107422_10_1( sextkey STRING, 
                             sinfosfirstname STRING, 
                             sinfosname STRING, 
                             spersid STRING); 
ALTER TABLE wkf107422_10_1 SET SERDEPROPERTIES  ('serialization.encoding'='UTF8')

CREATE TABLE wkf107422_11_1( sextkey STRING, 
                             sinfosfirstname STRING, 
                             sinfosname STRING, 
                             spersid STRING);
ALTER TABLE wkf107422_11_1 SET SERDEPROPERTIES  ('serialization.encoding'='UTF8') ;

Structure of the table used as destination

CREATE TABLE wkf107422_12_1_0( sextkey STRING, 
                               sinfosfirstname STRING, 
                               sinfosname STRING, 
                               spersid STRING) ; 
ALTER TABLE wkf107422_12_1_0 SET SERDEPROPERTIES ('serialization.encoding'='UTF8') ; 

Statements that is failing :

INSERT INTO wkf107422_12_1_0 ( spersid ) 
  SELECT spersid FROM wkf107422_11_1 UNION SELECT spersid FROM wkf107422_10_1 

Any advice on what I'm doing wrong there ?

Best regards

[#2]

As a complementary information I've tried to replace the INSERT INTO by a CREATE TABLE AS SELECT

CREATE TABLE wkf107422_12_1_1  AS 
    SELECT spersid FROM wkf107422_11_1 UNION SELECT spersid FROM wkf107422_10_1

and this one works through the same ODBC connection

[#3]

The column list specification "INSERT INTO wkf107422_12_1_0 ( spersid )" is available starting with Hive-1.2 but you are most likely using an older version of Hive which doesn't support this feature (added by HIVE-9481). In old versions your SELECT statement has to provide all schema columns, in your case all 4. Regarding your "complementary information", it works but the resulting table wkf107422_12_1_1 contains only one column corresponding to spersid. To confirm try "describe wkf107422_12_1_1".

[#4]

Indeed the table as only one column (I knew this but just wanted to add more info to the description)

The issue might be realted to Hive odbc driver from Hortonworks since it indeed works with Hive view (web page)

ODBC driver version is version 2.1.2.1002 (32 bits) 

the server versions are from the ps -auxwww | grep -i HIve of the test VM I'm using from hortonworks :

hive-service-1.2.1000.2.4.0.0-169.jar
hive-webhcat-1.2.1000.2.4.0.0-169.jar

I've tested that if I change the CREATE TABLE for wkf107422_12_1_0 to have only one column it work in both in ODBC and Hive view

[#5 · Accepted solution]

Just so that every body knows - using the "use native query" in the ODBC driver make it work in my case.