Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Hive JdbcStorageHandler query pushdown not working on custom databases

Hive JdbcStorageHandler query pushdown not working on custom databases

New Contributor

I try to use JdbcStorageHandler based external tables, that point to IBM Netezza database or to Apache Kylin. Unfortunately, I cannot make SQL pushdown to work. I get the query working, but only "SELECT * FROM table;" and all aggregation or filtering happens in hive.

Can someone write me what to do to make Hive support pushdown to a custom data store? Shouldn't it work out of the box? (In the end, I want whole query to be forwarded to the datastore).

If it is required to code some plugin, I'm willing to do it too, just need instructions how. It would be great if it could be done on top of existing distribution.


Here is an example of what I'm doing:

add jar hdfs:///apps/kylin/kylin-jdbc-2.6.1.jar;
CREATE EXTERNAL TABLE tickets_kylin_jdbc (
  tran_id bigint
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
    "hive.sql.database.type" = "H2", -- I have tried all the possible options like H2, DERBY, ORACLE, MYSQL, MSSQL, POSTGRES
    "hive.sql.jdbc.driver" = "org.apache.kylin.jdbc.Driver",
    "hive.sql.jdbc.url" = "jdbc:kylin://edge-01.example.com.pl:7070/Project",
    "hive.sql.dbcp.username" = "user.name",
    "hive.sql.dbcp.password" = "*****",
    "hive.sql.table" = "KYLIN_TICKETS",
    "hive.sql.numPartitions" = "1",
    "hive.sql.dbcp.maxActive" = "1"
);


I'm using HDP 3.1.0, with hive in version ("select version();" output) :

3.1.0.3.1.0.0-78 r56673b027117d8cb3400675b1680a4d992360808