GridGain Developers Hub
GitHub logo GridGain iso GridGain.com
GridGain Software Documentation

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 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 Web Console 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.

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 GridGain 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 Web Console to Run Queries

For larger clusters and more complex datasets, you can execute SQL queries directly in the GridGain Web Console. The Web Console can connect to your entire cluster and provides some graphical and tabular components for use when running queries. See GridGain Web Console below for information about how to get up and running with Web Console.

Next Steps

From here, you may want to:

  • Read more about using GridGain and SQL (LINK)

  • Read more about using sqlline (LINK)

  • Use GridGain Web Console to monitor your cluster

Using GridGain Web Console

GridGain Web Console is an interactive configuration, management, and monitoring tool, built on top of Apache Ignite Web Console.

  1. Go to https://console.gridgain.com and create an account.

  2. Log in with your new account and go to the "Monitoring Dashboard" screen. Click the three horizontal lines at the top in order to expand the left-hand menu:

    Web Console Left Nav
  3. Click the "Download Agent" button as shown in the screenshot below:

    Download the Web Console Agent
  4. Extract the web agent into a separate folder.

  5. Navigate to the folder where you extracted the web agent files, and execute the ignite-web-agent.sh (or ignite-web-agent.bat) script.

  6. Ensure that the agent can connect to both console.gridgain.com and your single node cluster started earlier. Look for messages similar to the following in the agent’s log:

    Web Console Log
  7. Go back to the console.gridgain.com Monitoring Dashboard and refresh it. Confirm that the tool successfully displays metrics for your local single node cluster.