question Re: Difference in Hive query execution via Beeline & Hortonworks ODBC Driver in Support Questions

Difference in Hive query execution via Beeline & Hortonworks ODBC Driver

siddarth_wardha — Fri, 16 Sep 2022 13:07:52 GMT

I have two tables in Hive.
    
    CREATE TABLE Target_table(
    col_1 timestamp,
    col_2 int,
    col_3 int) CLUSTERED BY (col_1) INTO 50 BUCKETS STORED AS ORC 
    TBLPROPERTIES('transactional'='true')

    CREATE TABLE Source_table(
    col_1 timestamp,
    col_2 int)

I am trying to execute this query 

    INSERT INTO Target_table (col_1, col_2) 
    SELECT col_1, col_2 FROM Source_table;


Query runs successfully in Beeline. 

Same query fails when executed via Hortonworks ODBC Driver with the error
    
    ERROR [HY000] [Hortonworks][Hardy] (80) Syntax or semantic analysis error 
    thrown in server while executing query. 
    Error message from server: Error while compiling statement: FAILED: 
    SemanticException [Error 10044]: Line 1:18 Cannot insert into target table 
    because column number/types are different 'Targer': Table insclause-0 has 3 
    columns, but query has 2 columns.

Looks like Hive is completely ignoring the column list in the Insert clause.

Other Details

    Cluster: Azure HDInsight Cluster
    Hortonworkds Data Platform: HDP-2.6.2.25
    OS: Windows 10
    Language: C#


Any help is appreciated.

Re: Difference in Hive query execution via Beeline & Hortonworks ODBC Driver

Shu_ashu — Sat, 21 Apr 2018 22:26:56 GMT

@Siddarth Wardhan

Probably it's a bug in Hive ODBC driver, As a workaround you can use the below query, this query will run in beeline and in ODBC driver also.

INSERT INTO Target_table(col_1, col_2, col_3) SELECT col_1, col_2,int(null) col_3 FROM Source_table;

(or)

INSERT INTO Target_table SELECT col_1, col_2,int(null) col_3 FROM Source_table; //if the order of columns matches between select statement and target_table

These both query will insert same results as your query does (i.e . INSERT INTO Target_table(col_1, col_2) SELECT col_1, col_2 FROM Source_table;)

Re: Difference in Hive query execution via Beeline & Hortonworks ODBC Driver

siddarth_wardha — Sat, 21 Apr 2018 23:02:41 GMT

@ShuThank you so much. First approach worked.

INSERT INTO Target_table(col_1, col_2, col_3) SELECT col_1, col_2,int(null) col_3 FROM Source_table;