docs/_docs/tools/sqlline.adoc

// Licensed to the Apache Software Foundation (ASF) under one or more
// contributor license agreements.  See the NOTICE file distributed with
// this work for additional information regarding copyright ownership.
// The ASF licenses this file to You under the Apache License, Version 2.0
// (the "License"); you may not use this file except in compliance with
// the License.  You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
= Using SQLLine With Apache Ignite


Command line tool for SQL connectivity.

== Overview
Apache Ignite is shipped with the SQLLine tool – a console-based utility for connecting to relational databases and executing SQL commands.
This documentation describes how to connect SQLLine to your cluster, as well as various supported SQLLine commands.

== Connecting to Ignite Cluster
From your {IGNITE_HOME}/bin directory, run `sqlline.sh -u jdbc:ignite:thin:[host]` to connect SQLLine to the cluster. Substitute [host] with your actual value. For example:

[tabs]
--
tab:Unix[]
[source,shell]
----
./sqlline.sh --verbose=true -u jdbc:ignite:thin://127.0.0.1/
----

tab:Windows[]
[source,shell]
----
sqlline.bat --verbose=true -u jdbc:ignite:thin://127.0.0.1/
----

--



Use the `-h` or `help` option to see the various options available with SQLLine:

[tabs]
--
tab:Unix[]
[source,shell]
----
./sqlline.sh -h
./sqlline.sh --help
----

tab:Windows[]
[source,shell]
----
sqlline.bat -h
sqlline.bat --help
----
--


=== Authentication
If you have authentication enabled for your cluster, then from your `{IGNITE_HOME}/bin' directory, run `jdbc:ignite:thin://[address]:[port];user=[username];password=[password]` to connect SQLLine to the cluster. Substitute `[address]`, `[port]`, `[username]` and `[password]` with your actual values. For example:


[tabs]
--
tab:Unix[]
[source,shell]
----
./sqlline.sh --verbose=true -u "jdbc:ignite:thin://127.0.0.1:10800;user=ignite;password=ignite"
----

tab:Windows[]
[source,shell]
----
sqlline.bat --verbose=true -u "jdbc:ignite:thin://127.0.0.1:10800;user=ignite;password=ignite"
----
--

If you do not have authentication set, omit `[username]` and `[password]`.

[NOTE]
====
[discrete]
=== Put JDBC URL in Quotes When Connecting from bash
Make sure to put the connection URL in " " quotes when connecting from a bash environment, as follows: "jdbc:ignite:thin://[address]:[port];user=[username];password=[password]"
====

== Commands
Here is the list of supported link:http://sqlline.sourceforge.net#commands[SQLLine commands, window=_blank]:

[width="100%", cols="25%, 75%"]
|=======
|Command |	Description

|`!all`
|Execute the specified SQL against all the current connections.

|`!batch`
|Start or execute a batch of SQL statements.

|`!brief`
|Enable terse output mode.

|`!closeall`
|Close all current open connections.

|`!columns`
|Display columns of a table.

|`!connect`
|Connect to a database.

|`!dbinfo`
|List metadata information about the current connection.

|`!dropall`
|Drop all tables in the database.

|`!go`
|Change to a different active connection.

|`!help`
|Display help information.

|`!history`
|Display the command history.

|`!indexes`
|Display indexes for a table.

|`!list`
|Display all active connections.

|`!manual`
|Display SQLLine manual.

|`!metadata`
|Invoke arbitrary metadata commands.

|`!nickname`
|Create a friendly name for the connection (updates command prompt).

|`!outputformat`
|Change the method for displaying SQL results.

|`!primarykeys`
|Display the primary key columns for a table.

|`!properties`
|Connect to the database defined in the specified properties file.

|`!quit`
|Exit SQLLine.

|`!reconnect`
|Reconnect to the current database.

|`!record`
|Begin recording all output from SQL commands.

|`!run`
|Execute a command script.

|`!script`
|Save executed commands to a file.

|`!sql`
|Execute a SQL against a database.

|`!tables`
|List all the tables in the database.

|`!verbose`
|Enable verbose output mode.
|=======

Note that the above list may not be complete. Support for additional SQLLine commands can be added.

== Example
After connecting to the cluster, you can execute SQL statements and SQLLine commands:


Create tables:
[source,sql]
----
0: jdbc:ignite:thin://127.0.0.1/> CREATE TABLE City (id LONG PRIMARY KEY, name VARCHAR) WITH "template=replicated";
No rows affected (0.301 seconds)

0: jdbc:ignite:thin://127.0.0.1/> CREATE TABLE Person (id LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id))WITH "backups=1, affinityKey=city_id";
No rows affected (0.078 seconds)

0: jdbc:ignite:thin://127.0.0.1/> !tables
+-----------+--------------+--------------+-------------+-------------+
| TABLE_CAT | TABLE_SCHEM  |  TABLE_NAME  | TABLE_TYPE  | REMARKS     |
+-----------+--------------+--------------+-------------+-------------+
|           | PUBLIC       | CITY         | TABLE       |             |
|           | PUBLIC       | PERSON       | TABLE       |             |
+-----------+--------------+--------------+-------------+-------------+
----

Define indexes:

[source,sql]
----
0: jdbc:ignite:thin://127.0.0.1/> CREATE INDEX idx_city_name ON City (name);
No rows affected (0.039 seconds)

0: jdbc:ignite:thin://127.0.0.1/> CREATE INDEX idx_person_name ON Person (name);
No rows affected (0.013 seconds)

0: jdbc:ignite:thin://127.0.0.1/> !indexes
+-----------+--------------+--------------+-------------+-----------------+
| TABLE_CAT | TABLE_SCHEM  |  TABLE_NAME  | NON_UNIQUE  | INDEX_QUALIFIER |
+-----------+--------------+--------------+-------------+-----------------+
|           | PUBLIC       | CITY         | true        |                 |
|           | PUBLIC       | PERSON       | true        |                 |
+-----------+--------------+--------------+-------------+-----------------+
----

You can also watch a link:https://www.youtube.com/watch?v=FKS8A86h-VY[screencast, window=_blank] to learn more about how to use SQLLine.