Edit

Getting Started Quickly with SQL Via the Command Line

If you just want to start up a cluster on the local machine and add a few rows of data without running Java or starting up an IDE, you can do some basic data loading and run some queries via the command line purely in SQL in less than 5 minutes.

To do this, we’ll use the sqlline utility (located in the /bin directory of your Ignite installation).

Note

This example shows just one simple way to load data into Ignite, quickly, for the sake of experimenting. For larger, production-scale work, you would want to use a more robust method of loading data (IgniteDataStreamer, Spark, advanced SQL, etc.). Refer to the External Storage page for the information on how to load data from an RDBMS.

Installing Ignite

Before we can get to any of that, we’ll first need to install Ignite.

To get started with the Apache Ignite binary distribution:

Download the Ignite binary as a zip archive.
Unzip the zip archive into the installation folder in your system.
(Optional) Enable required modules.
(Optional) Set the IGNITE_HOME environment variable or Windows PATH to point to the installation folder and make sure there is no trailing / (or \ for Windows) in the path.

Running Ignite

You can start a node from the command line using the default configuration or by passing a custom configuration file. You can start as many nodes as you like and they will all automatically discover each other.

Navigate into the bin folder of the Ignite installation directory from the command shell. Your command might look like this:

cd {IGNITE_HOME}/bin/

cd {IGNITE_HOME}\bin\

Start a node with a custom configuration file that is passed as a parameter to ignite.sh|bat like this:

./ignite.sh ../examples/config/example-ignite.xml

ignite.bat ..\examples\config\example-ignite.xml

You will see output similar to this:

[08:53:45] Ignite node started OK (id=7b30bc8e)
[08:53:45] Topology snapshot [ver=1, locNode=7b30bc8e, servers=1, clients=0, state=ACTIVE, CPUs=4, offheap=1.6GB, heap=2.0GB]

Open another tab from your command shell and run the same command again:

./ignite.sh ../examples/config/example-ignite.xml

ignite.bat ..\examples\config\example-ignite.xml

Check the Topology snapshot line in the output. Now you have a cluster of two server nodes with more CPUs and RAM available cluster-wide:

[08:54:34] Ignite node started OK (id=3a30b7a4)
[08:54:34] Topology snapshot [ver=2, locNode=3a30b7a4, servers=2, clients=0, state=ACTIVE, CPUs=4, offheap=3.2GB, heap=4.0GB]

Note	By default, `ignite.sh\|bat` starts a node with the default configuration file: `config/default-config.xml`.

This is the most basic startup method. It starts a node on the local machine, which gives us a place into which we can load data.

Now just connect to the node and add data.

Using sqlline

Using the sqlline utility is easy — you just need to connect to the node and then start entering SQL statements.

Open one more command shell tab and ensure you’re in the {IGNITE_HOME}\bin folder.

Connect to the cluster with sqlline:

$ ./sqlline.sh -u jdbc:ignite:thin://127.0.0.1/

$ sqlline -u jdbc:ignite:thin://127.0.0.1

Create two tables by running these two statements in sqlline:

CREATE TABLE City (id LONG PRIMARY KEY, name VARCHAR) WITH "template=replicated";

CREATE TABLE Person (id LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id))
WITH "backups=1, affinityKey=city_id";

Insert some rows by copy-pasting the statements below:

INSERT INTO City (id, name) VALUES (1, 'Forest Hill');
INSERT INTO City (id, name) VALUES (2, 'Denver');
INSERT INTO City (id, name) VALUES (3, 'St. Petersburg');
INSERT INTO Person (id, name, city_id) VALUES (1, 'John Doe', 3);
INSERT INTO Person (id, name, city_id) VALUES (2, 'Jane Roe', 2);
INSERT INTO Person (id, name, city_id) VALUES (3, 'Mary Major', 1);
INSERT INTO Person (id, name, city_id) VALUES (4, 'Richard Miles', 2);

And then run some basic queries:

SELECT * FROM City;

+--------------------------------+--------------------------------+
|               ID               |              NAME              |
+--------------------------------+--------------------------------+
| 1                              | Forest Hill                    |
| 2                              | Denver                         |
| 3                              | St. Petersburg                 |
+--------------------------------+--------------------------------+
3 rows selected (0.05 seconds)

As well as queries with distributed JOINs:

SELECT p.name, c.name FROM Person p, City c WHERE p.city_id = c.id;

+--------------------------------+--------------------------------+
|              NAME              |              NAME              |
+--------------------------------+--------------------------------+
| Mary Major                     | Forest Hill                    |
| Jane Roe                       | Denver                         |
| John Doe                       | St. Petersburg                 |
| Richard Miles                  | Denver                         |
+--------------------------------+--------------------------------+
4 rows selected (0.011 seconds)

Easy!

Next Steps

From here, you may want to:

Read more about using Ignite and SQL
Read more about using sqlline

© 2024 The Apache Software Foundation.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.