Connecting to Drill via the Drill Shell (SQLLine)

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

You can connect to Drill through Sqlline directly or through a connection-property file. To avoid exposing credentials, connect through the connection-property file.

A JDBC connection string supplies the connection information to a Drill node or ZooKeeper cluster. When you connect to a ZooKeeper cluster, ZooKeeper selects the Drillbit for SQLLine to connect to.

JDBC Connection String Example

Here is an example of a JDBC connection string that connects SQLLine to drillnode1:

jdbc:drill:drillbit=drillnode1:31010

The default port on a Drill node is 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

Starting SQLLine

Start SQLLine from the Drill installation directory, as shown:

/opt/mapr/drill/drill-<version>/bin/sqlline -u jdbc:drill:drillbit=drillnode1:31010

Configuration Options

You can also include configuration options, such as schema:

/opt/mapr/drill/drill-<version>/bin/sqlline -u "jdbc:drill:drillbit drillnode1:31010;schema=dfs" -n <username> -p <password>

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 URL, 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 (Plain, MAPRSASL, or Kerberos) is enabled, include the auth option in the connection string. If Drill is installed on a cluster secured by default security, set auth=MAPRSASL.
For additional configuration options, refer to the Driver Configuration Options section in the JDBC Installation and Configuration Guide.

Connecting to a Specific Drill Node

Indicate which Drill node you want SQLLine to connect to in the JDBC connection string by 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). By default, the driver connects to port 31010.

Example

The following example shows how to run SQLLine with the JDBC connection string and 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 cluster with default security enabled, 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 use.

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.

IMPORTANT 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 might want to override the Drillbit cluster name in drill-override.conf. However, first back-up your storage plugin configurations, as they might reset to the defaults when you change the cluster name. Restart Drill after you edit drill-override.conf.

Note that properties are case-sensitive. The host is the DNS or IP address of the server (ZooKeeper node).

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

NOTE The default port for ZooKeeper nodes in a data-fabric cluster 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

If you use a connection-property file, make sure you restrict user permission on the file to only those users you want to have access.

Complete the following steps to create a connection-property file and connect to Drill:

Create a connection-property file named login.properties with the following information:

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 to connect to Drill through the connection-property file and how to verify that log in details are safe:

Example 1: Connecting to Drill via the connection-property file

sqlline login.properties

Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
apache drill 1.16.0
"drill baby drill"
0: jdbc:drill:schema=dfs> !list
1 active connection:
 #0  open     jdbc:drill:schema=dfs;drillbit=drill-lab-node01
0: jdbc:drill:schema=dfs>!q

Example 2 : Submitting a query when connecting to Drill via the connection-property file

sqlline -q "SELECT version FROM sys.version" login.properties
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
apache drill 1.16.0
"the only truly happy people are children, the creative minority and drill users"
0: jdbc:drill:schema=dfs> select version from sys.version
. . . . . . . . . . . . > +----------+
| version  |
+----------+
| 1.16.0   |
+----------+
1 row selected (0.295 seconds)
0: jdbc:drill:schema=dfs> Closing: org.apache.drill.jdbc.impl.DrillConnectionImpl
$

Example 3: Use the properties command to connect to Drill via the connection-property file

Run sqlline from /opt/mapr/drill/drill-<version>/bin sqlline

Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
apache drill 1.16.0
"a little sql for your nosql"

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>

Example 4: Verify that Login Details are Safe

You can verify sqlline process information to confirm 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

How to Protect the Password

Use the !connect command to mask and protect the password, as shown in the following example:

sqlline> !connect jdbc:drill:drillbit=ip-10-0-0-33.eu-west-2.compute.internal:31010

Enter username for jdbc:drill:drillbit=ip-10-0-0-33.eu-west-2.compute.internal:31010: alice
Enter password for jdbc:drill:drillbit=ip-10-0-0-33.eu-west-2.compute.internal:31010: ********

Start|Stop the Drill Service

You can start|stop|restart the Drillbit service on one or more nodes by 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.

Drill Log Files

You can access the Drill log files in /opt/mapr/drill/drill-<version>/logs/drillbit.log.