GridGain Developers Hub

GridGain Quick Start Guide for SQL

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 GridGain installation).

Installing GridGain

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

GridGain is available in three editions: Community Edition (CE), Enterprise Edition (EE) and Ultimate Edition (UE). For this example, we’ll use the GridGain CE, which is distributed as binary, docker, and cloud images, and via RPM/DEB. This chapter explains how to install the GridGain CE binary distribution.

To get started with the GridGain CE binary distribution:

  1. Download the GridGain binary as a zip archive.

  2. Unzip the zip archive into the installation folder in your system.

  3. Move the ignite-rest-http folder from {gridgain}/libs/optional to {gridgain}/libs to enable the Ignite REST library for the cluster. The library is used by GridGain Nebula for cluster management and monitoring needs.

  4. (Optional) Enable required modules.

  5. (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.

(Optional) Opening Ports

Depending on your environment and what your plan is, you may want to open additional ports. GridGain uses the following ports:

  • 47100-47200 — ports used by GridGain nodes to communicate. Specific ports used depend on node configuration.

  • 47500-47600 — ports used by GridGain nodes to discover other nodes in the network. Specific ports used depend on node configuration.

  • 10800 — the port used for thin clients, JDBC and ODBC connections.

  • 8080 — the port used for REST API.

  • 11211 — the port used by control script calls. This port should only be opened on nodes that need to send control script messages to other nodes.

Running GridGain

You can start a GridGain 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 GridGain installation directory from the command shell. Your command might look like this:

cd {gridgain}/bin/
cd {gridgain}\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]

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.

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

  2. 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

  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";

  2. 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);

  3. 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)

  4. 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!

Using Control Center to Run Queries

For larger clusters and more complex datasets, you can execute SQL queries directly in GridGain Control Center.

Next Steps

From here, you may want to:

© 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 Dec 04, 2024