Start the Drill Shell (SQLLine)

SQLLine is a JDBC application packaged with Drill that serves as the Drill shell. When you issue queries from the SQLLine, the SQLLine client sends the queries to the connected Drillbit (Drill node).

You can connect to Drill through SQLLine directly or through a connection-property file. If want to avoid exposing credentials, connecting through the connection-property file is recommended.

A JDBC connection string supplies the connection information to a Drill node or ZooKeeper cluster. When connecting to a ZooKeeper cluster, ZooKeeper selects the Drillbit that SQLLine connects to.

JDBC Connection String

This is an example of a JDBC connection string that connects SQLLine to drillnode1:
jdbc:drill:drillbit=drillnode1:31010

The default port on any Drill node is 31010.

Starting SQLLine

You start SQLLine from the Drill installation directory, as shown in the following example where SQLLine connects directly to a Drill node named drillnode1:
/opt/mapr/drill/drill-<version>/bin/sqlline -u jdbc:drill:drillbit=drillnode1:31010

Connection Parameters

You can include SQLLine connection parameters in the connection string and run various shell commands, as described in Configuring the Drill Shell.

In the following example, -u is the connection parameter for the JDBC connection string, -n is the parameter for the username, and –p is the parameter for the password: 
/opt/mapr/drill/drill-<version>/bin/sqlline -u "jdbc:drill:drillbit=drillnode1:31010" -n mapr -p mapr
In the following example, the !connect shell command is used to hide the password when making an authenticated connection:
//From /opt/mapr/drill/drill-<version>/, run:

bin/sqlline

//The sqlline prompt appears. At the prompt, provide the connection string with the !connect property:

sqlline> !connect jdbc:drill:drillbit=drillnode1:31010
//The system prompts you for the username and password.
Enter username for jdbc:drill:drillbit=drillnode1:31010: mapr
Enter password for jdbc:drill:drillbit=drillnode1:31010: ********
NOTICE
In Drill 1.15, the SQLLine !connect command incorrectly requests a username and password when connecting to a secure cluster via MAPRSASL or KERBEROS authentication:
sqlline> !connect jdbc:drill:drillbit=drillnode1:31010;auth=MAPRSASL

//!connect usage: connect <url> <username> <password> [driver]
//Driver is optional. Driver is the Apache Drill driver class, org.apache.drill.jdbc.Driver.
To workaround this issue, provide your username when you connect and press Enter when prompted for the password: 
sqlline> !connect jdbc:drill:drillbit=drillnode1:31010;auth=MAPRSASL mapr
Enter password for jdbc:drill:drillbit=drillnode1:31010;auth=MAPRSASL:
Alternatively, you can use an empty quote in place of a username:
sqlline> !connect jdbc:drill:drillbit=drillnode1:31010;auth=MAPRSASL ""

Configuration Options

You can also include configuration options, such as schema and auth (if authentication is enabled): 
/opt/mapr/drill/drill-<version>/bin/sqlline -u "jdbc:drill:drillbit drillnode1:31010;schema=dfs;auth=MAPRSASL"
Schema
The schema is the name of a storage plugin configuration to use as the default for queries. If you indicate the schema in the connection string, you do not have to run the USE <schema>; query to switch to the schema you want to use. All queries run against the schema indicated in the JDBC connection string.
Authentication
If authentication is enabled (Plain, MAPRSASL, Kerberos), include the auth option in the connection string. If Drill is installed on a cluster secured by the default security, set auth=MAPRSASL. If using Plain authentication, include the username and password, as shown:
/opt/mapr/drill/drill-<version>/bin/sqlline -u "jdbc:drill:drillbit drillnode1:31010;schema=dfs;auth=MAPRSASL"

Connecting to a Specific Drill Node

Indicate which Drill node you want SQLLine to connect to in the JDBC connection string, using the following JDBC connection string format: 
jdbc:drill:drillbit=<host>:<port>

Note that properties are case-sensitive. The host is the DNS or IP address of the server (Drill node). The default connection port is 31010.

Example

The following example shows you how to start SQLLine with a JDBC connection string that includes the username, password, and auth parameters to authenticate to the server with Plain authentication:
/opt/mapr/drill/drill-<version>/bin/sqlline -u "jdbc:drill:drillbit=<ip-address>:<port>;auth=PLAIN" -n <username> -p <password>
If you installed Drill on a a cluster secured by default security, set the auth type to maprsasl:
/opt/mapr/drill/drill-<version>/bin/sqlline -u "jdbc:drill:drillbit=<ip-address>:<port>;auth=MAPRSASL"

Connecting to ZooKeeper

When you include the ZooKeeper nodes in the JDBC connection string, ZooKeeper selects an available Drill node for SQLLine to connect to.

Indicate the ZooKeeper cluster you want SQLLine to connect to in the JDBC connection string, using the following JDBC connection string format:
jdbc:drill:zk=<zk-server-list>/drill/<clustername>

The zk-server-list is a comma-separated list of the ZooKeeper nodes in the cluster. The clustername is the unique name of the Drillbit cluster that you want to connect to.

You can locate the name of the Drillbit cluster in /opt/mapr/drill/drill-<version>/conf/drill-distrib.conf. The default name of the Drillbit cluster is drillbits1. The name is set by the cluster-id property. If you have multiple Drill clusters, you may want to override the Drillbit cluster name in drill-override.conf. However, first back-up your storage plugin configurations, as they may reset to the defaults when you change the cluster name. Restart Drill after you edit drill-override.conf.

Example

The following example shows you how to configure the JDBC connection string to connect SQLLine to the ZooKeeper cluster:
/opt/mapr/drill/drill-<version>/bin/sqlline jdbc:drill:zk=<node-ip>:<port>,<node-ip>:<port>,=<node-ip>:<port>/drill/drillbits1;auth=PLAIN -n <username> -p <password>

The default port for ZooKeeper nodes is 5181.

If you installed Drill on a secure cluster, set the auth type to MAPRSASL:
/opt/mapr/drill/drill-<version>/bin/sqlline jdbc:drill:zk=<node-ip>:<port>,<node-ip>:<port>,=<node-ip>:<port>/drill/drillbits1;auth=MAPRSASL

Using a Connection-Property File with SQLLine

Make sure you restrict access to the connection-property file to specific users.

Create a connection-property file named login.properties, as shown:
url:<jdbc-connection-url>
user:<username>
password:<password>

//Example
cat login.properties
url:jdbc:drill:schema=dfs;drillbit=drill-lab-node01
user:drilluser
password:letsdrill
To connect to Drill, run SQLLine as shown: 
sqlline <sqlline args> <path/to/login.properties file>
The following examples show you how you can use the connection-property file to connect to Drill:
Example 1: Connecting to Drill via the connection-property file
Run SQLLine from /opt/mapr/drill/drill-<version>/bin:
sqlline login.properties

//List the active connection:
0: jdbc:drill:schema=dfs> !list
1 active connection:
 #0  open     jdbc:drill:schema=dfs;drillbit=drill-lab-node01

//Exit SQLLine:
0: jdbc:drill:schema=dfs>!q
Example 2: Submitting a query when connecting to Drill via the connection-property file
Run SQLLine from /opt/mapr/drill/drill-<version>/bin:
sqlline -q "SELECT version FROM sys.version" login.properties

//Run query:
0: jdbc:drill:schema=dfs> select version from sys.version;
+----------+
| version  |
+----------+
| 1.16.0   |
+----------+
1 row selected (0.295 seconds)
Example 3: Use the properties command to connect to Drill via the connection-property
Run SQLLine from /opt/mapr/drill/drill-<version>/bin:
sqlline 

//At sqlline the prompt, run:
sqlline> !properties /home/drilluser/login.properties
0: jdbc:drill:schema=dfs>
0: jdbc:drill:schema=dfs> !list
1 active connection:
 #0  open     jdbc:drill:schema=dfs;drillbit=drill-lab-node01
0: jdbc:drill:schema=dfs>
Verify that Login Details are Secure
Run the following command to verify that login details are not exposed to other users:
ps -ef | grep sqlline

drilluser     18938 21924 99 14:14 pts/0    00:00:03 /opt/jdk1.8.0_141/bin/java -XX:MaxPermSize=512M -Djava.security.auth.login.config=/opt/mapr/conf/mapr.login.conf \
-Dzookeeper.sasl.client=false -Dhadoop.login=simple -Dlog.path=/opt/mapr/drill/drill-1.10.0/logs/sqlline.log -Dlog.query.path=/opt/mapr/drill/drill-1.16.0/logs/sqlline_queries.json \
-cp /opt/mapr/drill/drill-1.10.0/conf:/opt/mapr/drill/drill-1.16.0/jars/*:/opt/mapr/drill/drill-1.16.0/jars/ext/*:/opt/mapr/drill/drill-1.16.0/jars/3rdparty/*:/opt/mapr/drill/drill-1.16.0/jars/classb/* 
sqlline.SqlLine -d org.apache.drill.jdbc.Driver --maxWidth=10000 --color=true login.properties
drilluser     20119  1691  0 14:14 pts/1    00:00:00 grep sqlline

Exit SQLLine

To exit SQLLine, run !quit.

Start|Stop the Drill Process

You can start|stop|restart the Drill process on one or more nodes using the Control System or the following command:
maprcli node services -name drill-bits -action start|restart|stop -nodes <node host names separated by a space>

Use the host name if possible. Using host names instead of IP addresses is a best practice.