How to use REST API to submit queries to Drill

Goal

How to use REST API to submit queries to Drill

Env

Drill 1.4

Solution

1. When authentication is not enabled(By default)

Example:

curl -v -X POST -H "Content-Type: application/json" -d '{"queryType":"SQL", "query": "select * from sys.version"}' http://localhost:8047/query.json

Sample output:

* About to connect() to localhost port 8047 (#0)
*   Trying ::1... connected
* Connected to localhost (::1) port 8047 (#0)
> POST /query.json HTTP/1.1
> User-Agent: curl/7.19.7 (x86_64-redhat-linux-gnu) libcurl/7.19.7 NSS/3.14.0.0 zlib/1.2.3 libidn/1.18 libssh2/1.4.2
> Host: localhost:8047
> Accept: */*
> Content-Type: application/json
> Content-Length: 57
>
< HTTP/1.1 200 OK
< Set-Cookie: JSESSIONID=5066jbscw9ii5hdxd9cb5k3n;Path=/
< Expires: Thu, 01 Jan 1970 00:00:00 GMT
< Content-Type: application/json
< Content-Length: 444
< Server: Jetty(9.1.5.v20140505)
<
{
  "columns" : [ "version", "commit_id", "commit_message", "commit_time", "build_email", "build_time" ],
  "rows" : [ {
    "build_email" : "Unknown",
    "commit_id" : "2756d168daded5ac4cd8813593ec0270598d04fc",
    "build_time" : "20.01.2016 @ 17:56:05 UTC",
    "commit_time" : "20.01.2016 @ 03:53:38 UTC",
    "commit_message" : "MD-669: Set the hasLimit0 flag in PlannerSettings (continuation of MD-649).",
    "version" : "1.4.0"
  } ]
* Connection #0 to host localhost left intact
* Closing connection #0

2. When authentication is enabled(with default HTTP)

Above curl command will fail with error message "HTTP 405 Method Not Allowed" which is expected. Here are 2 steps to make it work:
Step 1: Create a cookie file by providing the username and password

curl -X POST -H "Content-Type: application/x-www-form-urlencoded" -k -c cookies.txt -s -d "j_username=mapr" -d "j_password=mapr" http://localhost:8047/j_security_check

After that, you can see a cookies.txt file created:

$ cat cookies.txt
# Netscape HTTP Cookie File
# http://curl.haxx.se/rfc/cookie_spec.html
# This file was generated by libcurl! Edit at your own risk.

localhost FALSE / FALSE 0 JSESSIONID 9e81s29cdyv51iiys6zh821tm

Step 2: Using that cookie file to run the query

curl -k -b cookies.txt -v -X POST -H "Content-Type: application/json" -d '{"queryType":"SQL", "query": "select * from sys.version"}' http://localhost:8047/query.json

3. When both authentication and HTTPS are enabled.

HTTPS should be used together with authentication if you want to use REST API.
One sample drill-override.conf is:

drill.exec: {
  cluster-id: "my_cluster_com-drillbits",
  zk.connect: "v1.poc.com:5181,v2.poc.com:5181,v3.poc.com:5181",
  impersonation.enabled: true,
  http.ssl_enabled: true,
  security.user.auth {
         enabled: true,
         packages += "org.apache.drill.exec.rpc.user.security",
         impl: "pam",
         pam_profiles: [ "sudo", "login" ]
   }
}

The command are basically the same as above #2. The only thing different is to change "http" to "https".

curl -X POST -H "Content-Type: application/x-www-form-urlencoded" -k -c cookies.txt -s -d "j_username=mapr" -d "j_password=mapr" https://localhost:8047/j_security_check

curl -k -b cookies.txt -v -X POST -H "Content-Type: application/json" -d '{"queryType":"SQL", "query": "select * from sys.version"}' https://localhost:8047/query.json