Enable Query Pushdown
Introduction
If a query can not be answered by any cube, Kylin supports pushing down such query to backup query engines like Hive, SparkSQL, Impala through JDBC.
Query Pushdown config
Pushdown to single engine
Take hive as an example to explain the opening steps:
-
In Kylin's installation directory, uncomment configuration item
kylin.query.pushdown.runner-class-name
of config filekylin.properties
, and set it toorg.apache.kylin.query.adhoc.PushDownRunnerJdbcImpl
, If you need to pushdown the query to presto, please set this configuration toorg.apache.kylin.query.pushdown.PushdownRunnerSDKImpl
. For other configurations, please refer to Pushdown to Presto at the end of this page. -
Add configuration items below in config file
kylin.properties
.-
kylin.query.pushdown.jdbc.url: Hive JDBC's URL.
-
kylin.query.pushdown.jdbc.driver: Hive Jdbc's driver class name.
-
kylin.query.pushdown.jdbc.username: Hive Jdbc's user name.
-
kylin.query.pushdown.jdbc.password: Hive Jdbc's password.
-
kylin.query.pushdown.jdbc.pool-max-total: Hive Jdbc's connection pool's max connected connection number, default value is 8
-
kylin.query.pushdown.jdbc.pool-max-idle: Hive Jdbc's connection pool's max waiting connection number, default value is 8
-
kylin.query.pushdown.jdbc.pool-min-idle: Hive Jdbc's connection pool's min connected connection number, default value is 0
-
Here is a sample configuration; remember to change host "hiveserver" and port "10000" with your cluster configuraitons.
- When the query engine pushdown to is not
Hive
, please put the jdbc driver of the corresponding query engine in $KYLIN_HOME/ext directory, if there is no such directory, please create it yourself.
Then, restart Kylin.
kylin.query.pushdown.runner-class-name=org.apache.kylin.query.adhoc.PushDownRunnerJdbcImpl
kylin.query.pushdown.jdbc.url=jdbc:hive2://hiveserver:10000/default
kylin.query.pushdown.jdbc.driver=org.apache.hive.jdbc.HiveDriver
kylin.query.pushdown.jdbc.username=hive
kylin.query.pushdown.jdbc.password=
kylin.query.pushdown.jdbc.pool-max-total=8
kylin.query.pushdown.jdbc.pool-max-idle=8
kylin.query.pushdown.jdbc.pool-min-idle=0
Pushdown to multi engines
Since v3.0.0, Kylin supports pushdown query to multiple engines through JDBC.
You can specify multiple engine ids by configuring kylin.query.pushdown.runner.ids
, separated by ,
, such as:
kylin.query.pushdown.runner.ids=id1,id2,id3
Three depression engines are specified. These three engines can be the same type or different types.
Multi-engine pushdown also supports specifying specific jdbc parameters. The meaning of the parameters is the same as the single engine pushdown described above. Please see the configuration below:
kylin.query.pushdown.{id}.jdbc.url
kylin.query.pushdown.{id}.jdbc.driver
kylin.query.pushdown.{id}.jdbc.username
kylin.query.pushdown.{id}.jdbc.password
kylin.query.pushdown.{id}.jdbc.pool-max-total
kylin.query.pushdown.{id}.jdbc.pool-max-idle
kylin.query.pushdown.{id}.jdbc.pool-min-idle
When specifying a specific jdbc parameter for an engine, please replace the above {id}
with the real engine id, such as the configuration of id1
:
kylin.query.pushdown.id1.jdbc.url
kylin.query.pushdown.id1.jdbc.driver
kylin.query.pushdown.id1.jdbc.username
kylin.query.pushdown.id1.jdbc.password
kylin.query.pushdown.id1.jdbc.pool-max-total
kylin.query.pushdown.id1.jdbc.pool-max-idle
kylin.query.pushdown.id1.jdbc.pool-min-idle
Do Query Pushdown
After Query Pushdown is configured, user is allowed to do flexible queries to the imported tables without available cubes.
If query is answered by backup engine, Is Query Push-Down
is set to true
in the log.
Pushdown to Presto
If you want your query be push down to Presto, you can set following configuration in Project level properties (Introduced in KYLIN-4491).
kylin.query.pushdown.runner-class-name=org.apache.kylin.query.pushdown.PushdownRunnerSDKImpl
kylin.source.jdbc.dialect=presto
kylin.source.jdbc.adaptor=org.apache.kylin.sdk.datasource.adaptor.PrestoAdaptor
kylin.query.pushdown.jdbc.url={YOUR_URL}
kylin.query.pushdown.jdbc.driver=com.facebook.presto.jdbc.PrestoDriver
kylin.query.pushdown.jdbc.username={USER_NAME}
kylin.query.pushdown.jdbc.password={PASSWORD}
In addition, please download Presto jdbc driver presto-jdbc-xxx.jar and put it in $KYLIN_HOME/ext directory, if there is no such directory, please create it yourself.