Druid.io的SQL功能雖然在試驗階段,但是也支援了大部分的功能,而且還可以通過 Avatica JDBC檢視請求的json,有助於我們理解Druid.io的語法。Druid.io有個比較坑的是,如果沒有啟用SQL功能,你怎麼訪問也是沒有data返回的,但也不會提示你錯誤。
1、修改配置檔案
為了支援Druid SQL,需要修改common.runtime.properties或者 runtime.properties的druid.sql.enable為true就行。
To enable Druid SQL, make sure you have set druid.sql.enable = true either in your common.runtime.properties or your broker's runtime.properties.
注意啟動Druid時,使用的命令是官網提供的命令的話
java `cat conf-quickstart/druid/historical/jvm.config | xargs` -cp "conf-quickstart/druid/_common:conf-quickstart/druid/historical:lib/*" io.druid.cli.Main server historical
java `cat conf-quickstart/druid/broker/jvm.config | xargs` -cp "conf-quickstart/druid/_common:conf-quickstart/druid/broker:lib/*" io.druid.cli.Main server broker
java `cat conf-quickstart/druid/coordinator/jvm.config | xargs` -cp "conf-quickstart/druid/_common:conf-quickstart/druid/coordinator:lib/*" io.druid.cli.Main server coordinator
java `cat conf-quickstart/druid/overlord/jvm.config | xargs` -cp "conf-quickstart/druid/_common:conf-quickstart/druid/overlord:lib/*" io.druid.cli.Main server overlord
java `cat conf-quickstart/druid/middleManager/jvm.config | xargs` -cp "conf-quickstart/druid/_common:conf-quickstart/druid/middleManager:lib/*" io.druid.cli.Main server middleManager
那麼,你應該修改conf-quickstart/druid/broker/runtime.properties或者conf-quickstart/druid/_common/common.runtime.properties,增加下面幾個配置:
druid.sql.enable=true
druid.sql.avatica.enable=true
druid.sql.http.enable=true
2、測試HTTP SQL
測試前,需要匯入測試的資料,這裡直接使用quickstart的資料:http://druid.io/docs/0.12.0/tutorials/quickstart.html
$ cat query.json
{"query":"SELECT COUNT(*) AS TheCount FROM wikiticker"}
$ curl -XPOST -H'Content-Type: application/json' http://localhost:8082/druid/v2/sql/ -d @query.json
[{"TheCount":39244}]
使用EXPLAIN PLAN FOR
新增到任何查詢的開頭,可以檢視通過SQL生成的原生json查詢。
3、測試Jdbc
增加maven的依賴:
<dependency>
<groupId>org.apache.calcite.avatica</groupId>
<artifactId>avatica-core</artifactId>
<version>1.11.0</version>
</dependency>
<dependency>
<groupId>com.google.protobuf</groupId>
<artifactId>protobuf-java</artifactId>
<version>3.5.1</version>
</dependency>
// Connect to /druid/v2/sql/avatica/ on your broker.
String url = "jdbc:avatica:remote:url=http://localhost:8082/druid/v2/sql/avatica/";
// Set any connection context parameters you need here (see "Connection context" below).
// Or leave empty for default behavior.
Properties connectionProperties = new Properties();
try (Connection connection = DriverManager.getConnection(url, connectionProperties)) {
try (
final Statement statement = client.createStatement();
final ResultSet resultSet = statement.executeQuery(query)
) {
while (resultSet.next()) {
// Do something
}
}
}
暫時測試出不支援jdbc遊標移動absolute的方法。
網上關於Druid的東西特別難找,都是阿里的那個連線池-。-