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
./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
SELECT * from table where id1 = 1 ;
SELECT * .... ;
You can also use variables in your sql script that will be substituted at runtime.
SELECT * from table where id1 = ${yourparameter} ;
SELECT * .... ;
Now call SQLLine with the value of your variable in the connection URL
./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