Running SQL statements from a file or command line in Drill

Document created by vgonzalez on May 27, 2016
Version 1Show Document
  • View in full screen mode

I recently had someone ask me something like the following in an email:

 

With Hive I can create a shell script like this:

 

#!/bin/bash

hive -e 'select * from tablename';

 

How can I do the same with Drill?

 

The goal is to send a query intro Drill using the command line. There's two ways I'm aware of to do this with sqlline.

 

One way is to put the SQL statements in a script and use the --run option for sqlline:

 

$ echo 'select count(*) from cp.`employee.json`;' > count_employees.sql

$ sqlline -u jdbc:drill:zk=localhost:2181 --run=count_employees.sql

1/1          select count(*) from cp.`employee.json`;

+---------+

| EXPR$0  |

+---------+

| 1155    |

+---------+

1 row selected (0.256 seconds)

 

This is probably the best approach if you're going to have a lot of statements together.

 

If you just want to run a statement from the command line without creating a script file, you can just pipe the statement into sqlline. This is probably best for one or two short SQL statements:

 

$ echo 'select count(*) from cp.`employee.json`;' |\
    sqlline -u jdbc:drill:zk=localhost:2181

1/1          select count(*) from cp.`employee.json`;

+---------+

| EXPR$0  |

+---------+

| 1155    |

+---------+

1 row selected (0.739 seconds)

Closing: org.apache.drill.jdbc.impl.DrillConnectionImpl

 

In these examples, I'm running a single drillbit in distributed mode (e.g., with a standalone zookeeper) on my laptop, and the query executes against the cp storage plugin, which refers to files in Drill's classpath.

 

You may also want to take a look at the --force option, which will allow the script to continue to run if one statement fails - might be useful in cases where you are using CREATE TABLE AS, and the table might already exist, but you don't want that to stop execution of the script.

2 people found this helpful

Attachments

    Outcomes