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
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
/opt/mapr/drill/drill-<version>/bin/sqlline -u jdbc:drill:drillbit=drillnode1:31010
Configuration Options
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 theUSE <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
For additional configuration options, refer to the Driver Configuration Options section in the JDBC Installation and Configuration Guide.auth
option in the connection string. If Drill is installed on a cluster secured by default security, setauth=MAPRSASL
.
Connecting to a Specific Drill Node
jdbc:drill:drillbit=<host>:<port>
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 theauth
type tomaprsasl
:/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.
jdbc:drill:zk=<zk-server-list>/drill/<clustername>
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. /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
.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
NOTEThe default port for ZooKeeper nodes in a data-fabric cluster is 5181.If you installed Drill on a secure cluster, set theauth
type tomaprsasl
:/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.
- 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>
- 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
!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
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
.