Why is my Hive QL Query that I run in SSMS via Openquery through the Hortonworks ODBC Driver producing an error?

Question

I set up a connection to a Hive server using the Hortonworks ODBC Driver for Apache Hive. Version info is below:

OS: Windows Server 2012 R2 Standard
Hive: 1.2.1000.2.6.5.4-1
Hadoop: 2.7.3.2.6.5.4-1
Hortonworks ODBC Driver for Apache Hive
ODBC Version: 03.80
Driver Version: 2.1.12.1017
Bitness: 64-bit
Locale: en_US

I can run the queries below using the connector that I configured in Teradata SQL Assistant with no issues. I set up my DSN as a linked server in SSMS. However, when I attempt to run the queries in SSMS using openquery, I have some issues. Info on my SQL Server is below:

Microsoft SQL Server 2016 (SP2-CU3) (KB4458871) - 13.0.5216.0 (X64) Sep 13 2018 22:16:01 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

Here is some info on the table that I am querying:

Table Name: instrumentapps_event

Using OPENQUERY, I am capable of querying the Hive DB through SSMS with the following query:

SELECT * FROM OPENQUERY(KMhivehttp, 'select * from dmfwk_gold.instrumentapps_event')

The above query returns the contents of the desired table. However, the query below produces an error:

SELECT * FROM OPENQUERY(KMhivehttp, 'select * from dmfwk_gold.instrumentapps_event WHERE to_date(from_unixtime(UNIX_TIMESTAMP(load_ts,''yyyy/MM/dd''))) >= to_date(''2019-03-01'')')

The error is as follows:

Msg 7355, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "KMhivehttp" supplied inconsistent metadata for a column. The name was changed at execution time.

How can I fix this?

Answer 1

While I'm unsure how to fix the problem that I previously faced (I suspect it to be a bug with the Hortonworks ODBC driver), I did discover a workaround.

Instead of running:

SELECT * FROM OPENQUERY(KMhivehttp, 
    'SELECT * 
     FROM dmfwk_gold.instrumentapps_event 
     WHERE to_date(from_unixtime(UNIX_TIMESTAMP(load_ts,''yyyy/MM/dd''))) >= 
     to_date(''2019-03-01'')
    ')

I now use:

SELECT * FROM OPENQUERY(KMhivehttp, 'select * from dmfwk_gold.instrumentapps_event')
WHERE load_ts >= CAST('2019-03-01' AS DATE);

This allows me to avoid any metadata errors.