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:

1. In Kylin's installation directory, uncomment configuration item kylin.query.pushdown.runner-class-name of config file kylin.properties, and set it to org.apache.kylin.query.adhoc.PushDownRunnerJdbcImpl, If you need to pushdown the query to presto, please set this configuration to org.apache.kylin.query.pushdown.PushdownRunnerSDKImpl. For other configurations, please refer to Pushdown to Presto at the end of this page.

2. 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.

3. 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.