Programmatic PlyQL via HTTP, ODBC, and JDBC

May 04, 2016
Vadim Ogievetsky

Some time ago at Imply, we launched PlyQL, a command line utility that provides an SQL-like interface to Druid via Plywood.We heard a lot of positive feedback as many people prefer to use SQL over Druid’s native JSON-over-HTTP interface.The most common question we hear about PlyQL is how one can interface to it programmatically either from user created apps or from existing SQL based BI tools.We are pleased to announce we just released PlyQL 0.7 with two brand new programmatic interface options: SQL-over-HTTP and MySQL Gateway.


When you run:

plyql -h --json-server 8083

PlyQL will start listening on port 8083 where you can POST some SQL like so:

curl -X POST 'http://localhost:8083/plyql' \
  -H 'content-type: application/json' \
  -d '{
  "sql": "SELECT COUNT(*) FROM wikipedia WHERE \"2015-09-12T01\" <= __time AND __time < \"2015-09-12T02\""

Check out the json-server docs for more info about possible parameters.

MySQL Gateway

In this experimental feature we use the excellent node-mysql2 library to provide a MySQL server interface.

When you run:

plyql -h --experimental-mysql-gateway 3307

PlyQL will start in a MySQL-like server mode and will be queryable from a MySQL client, ODBC, or JDBC driver.

PlyQL will attempt to respond faithfully to SET, SHOW, DESCRIBE and any other meaningful query so that it appears to the client as a real MySQL server with read-only permissions.

While not all of MySQL is supported, the goal is to provide a way for new and existing tools that already communicate via ODBC or JDBC interfaces to talk to Druid using PlyQL.

Here is an example of how you could use the existing MySQL JDBC driver to query the above PlyQL gateway.

import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;

class DruidQuery
  public static void main(String[] args) throws SQLException
    Connection con = DriverManager.getConnection("jdbc:mysql://");
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(
      "SELECT page, count(*) AS cnt FROM wikipedia GROUP BY page ORDER BY cnt DESC LIMIT 15"

    while ( {
      String page = rs.getString("page");
      long count = rs.getLong("cnt");
      System.out.println(String.format("page[%s] count[%d]", page, count));

We are looking for your help to make this tool better. Whenever a query cannot be handled it will throw an error like so:

If this happens, please file an issue, if one does not already exist, and tell us what client software generated the relevant query.

For more information please look through the PlyQL Readme or run plyql --help

Other blogs you might find interesting

No records found...
Jul 03, 2024

Using Upserts in Imply Polaris

Transform your data management with upserts in Imply Polaris! Ensure data consistency and supercharge efficiency by seamlessly combining insert and update operations into one powerful action. Discover how Polaris’s...

Learn More
Jul 01, 2024

Make Imply Polaris the New Home for your Rockset Data

Rockset is deprecating its services—so where should you go? Try Imply Polaris, the database built for speed, scale, and streaming data.

Learn More
Jun 26, 2024

Announcing Imply Polaris on Microsoft Azure: Elevating Real-Time Analytics for Developers

We are excited to announce that Imply Polaris, our Database-as-a-Service (DBaaS) solution built from Apache Druid, is now available on Microsoft Azure. Azure customers worldwide can now take advantage of a...

Learn More

Let us help with your analytics apps

Request a Demo