SQL Line

You can run SQLLine on Windows, Mac-OS-X, Linux.

Prerequisite

Make sure JAVA is already installed in your environment.

Download & deploy SQLLine

Download here

copy the jar and the dependence files into a machine that will access Indexima.

Use SQLLine (command line)

On Windows execute indexima.bat

On Linux execute indexima.sh in the sqlline directory.

An Example

TEXT

 ./sqlline.sh -u "jdbc:hive2://localhost:10000/default" -n admin -p password -d io.galactica.jdbc.Driver -f script.sql

Here is an example of sql script

SQL

SELECT * from table where id1 = 1 ;
SELECT * .... ;

You can also use variables in your sql script that will be substituted at runtime.

SQL

SELECT * from table where id1 = ${yourparameter} ;
SELECT * .... ;

Now call SQLLine with the value of your variable in the connection URL

ACTIONSCRIPT3

 ./sqlline.sh -u "jdbc:hive2://localhost:10000/default;#yourparameter=2" -n admin -p password -d io.galactica.jdbc.Driver -f script.sql

Parameters

Property	Description	Comments
-u	The database URL to which to connect	include a variable by completing the URL with parameters starting with # parameters would be separated by a semicolon (;) Example: #Param1=toto;Param2=XX
-n	Username to connect to the database. If unspecified, SQLLine will prompt for it.
-p	Password to connect to the database. If unspecified, SQLLine will prompt for it.
-f	script file that contains SQL statements

Configure SQLLine (with property file)

Once unzipped, copy indexima.conf from indexima.conf.template when this file does not exists.

Edit the properties file indexima.conf :

url = jdbc:hive2://first_data_node:10000/
driver = io.galactica.jdbc.Driver

Refer to JDBC connection strings Examples for connections using other transport and security setup.

Editing this properties file allows to automatically connect to a database with credentials if defined. The properties file is expected to have the following values:

Property	Description
url	The database URL to which to connect
driver	The driver class that handles the URL. If unspecified, SQLLine with automatically scan the CLASSPATH for an appropriate driver
user	Username to connect to the database. If unspecified, SQLLine will prompt for it.
password	Password to connect to the database. If unspecified, SQLLine will prompt for it.

Example of property file:

url = jdbc:hive2://192.168.41.14:10000/
user = hadoop
password =
driver = io.galactica.jdbc.Driver

Connecting

... a Kerberized INDEXIMA cluster

An additional file needs to be set up in order to manage the Kerberos ticket

Copy the file galactica-jdbc.conf.template if galactica-jdbc.conf do not yet exist.

Then edit the galactica-jdbc.conf file

cat  galactica-jdbc.conf
# Kerberos configuration
kerberos.user = indexima-user/ns3615.co@INDEXIMA.COM
kerberos.keytab = /etc/security/keytabs/hive.service.keytab

Type the Kerberos principal as kerberos.user and specify the key tab as kerberos.keytab

... using Zookeeper

The connection must be set up in indexima.conf as shown below:

url = jdbc:hive2://ns3615.co:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace= indexima
driver = io.galactica.jdbc.Driver
user=
password=

Specify user and password in the configuration file to avoid being prompted by SQLLine at the connection time.

When Zookeeper is used, galactica-jdbc.conf file must be set with principal and keytab used to connect to INDEXIMA. A valid ticket must be claimed and can be verified with klist utility before starting INDEXIMA.

To avoid being prompted for user and password credential, which is the case when using Kerberos authentication, you can set blank user and blank password in the configuration file indexima.conf

Exit SQLLine

To exit SQLLine, enter !quit