Apache Ignite SQL Documentation

The Apache Ignite SQL Developer Hub

Welcome to the Apache Ignite SQL developer hub. You'll find comprehensive guides and documentation to help you start working with Apache Ignite SQL as quickly as possible, as well as support if you get stuck. Let's jump right in!

Get Started    

SQLLine

Command line tool for SQL connectivity in Ignite.

Apache Ignite comes with SQLLine tool – a console-based utility for connecting to relational databases and executing SQL commands. It is the default command line tool for SQL connectivity in Ignite. This documentation describes how to connect SQLLine to an Ignite cluster, as well as various SQLLine commands supported by Ignite.

Connecting to Ignite Cluster

From your IGNITE_HOME/bin directory, run sqlline.sh -u jdbc:ignite:thin:[host] command to connect SQLLine to the cluster. Substitute [host] with actual value. For example:

./sqlline.sh --verbose=true -u jdbc:ignite:thin://127.0.0.1/
sqlline.bat --verbose=true -u jdbc:ignite:thin://127.0.0.1/

Type ./sqlline.sh -h or ./sqlline.sh --help to see various options that can be used with the command.

Using 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]" command to connect SQLLine to the cluster. Substitute [address], [port], [username] and [password] with actual values. For example:

./sqlline.sh --verbose=true -u "jdbc:ignite:thin://127.0.0.1:10800;user=ignite;password=ignite"
sqlline.bat --verbose=true -u "jdbc:ignite:thin://127.0.0.1:10800;user=ignite;password=ignite"

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

Following is the list of SQLLine commands that are supported by Ignite:

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.There may be other SQLLine commands that are supported by Ignite. For a full list of SQLLine commands, see - http://sqlline.sourceforge.net/#commands

Example

After connecting to the ignite cluster, you can execute SQL statements and SQLLine commands, as shown below:

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       |             |
+-----------+--------------+--------------+-------------+-------------+
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        |                 |
+-----------+--------------+--------------+-------------+-----------------+

If you would like to run another example, you can try executing SQL examples from the Getting Started guide. Furthermore, watch a screencast that shows how to use the tool.

SQLLine


Command line tool for SQL connectivity in Ignite.

Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.