SQLLine

Command line tool for SQL connectivity.

Overview

GridGain and Apache Ignite are shipped with the 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 GridGain and Ignite. This documentation describes how to connect SQLLine to your cluster, as well as various supported SQLLine commands.

Connecting to Ignite Cluster

From your {gridgain_dir}/bin directory, run sqlline.sh -u jdbc:ignite:thin:[host] to connect SQLLine to the cluster.

Substitute [host] with your 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/

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

./sqlline.sh -h
./sqlline.sh --help

sqlline.bat -h
sqlline.bat --help

Using Authentication

If you have authentication enabled for your cluster, then from your {gridgain_dir}/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:

./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"

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

Commands

Here is the list of supported SQLLine commands:

Command Description

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. If the cluster has multiple indexes with the same name, and the command is run with no arguments, it returns only one of the above indexes, arbitrarily selected. To make the command return the index for a specific table, specify this table in an argument. For example, `!indexes "schema_name".table_name` where `schema_name` must be encased in double quotes.
`!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.

!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:

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

You can also watch a screencast to learn more about how to use SQLLine.

© 2024 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.

Last updated on Nov 20, 2024