- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Unable to execute statement INSERT INTO SELECT
- Labels:
-
Apache Hive
Created ‎09-08-2016 08:47 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Created ‎09-09-2016 11:18 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Just so that every body knows - using the "use native query" in the ODBC driver make it work in my case.
Created ‎09-08-2016 08:57 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Created ‎09-08-2016 09:21 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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".
Created ‎09-08-2016 11:52 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Created ‎09-09-2016 11:18 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Just so that every body knows - using the "use native query" in the ODBC driver make it work in my case.
