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 (
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
"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) :