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

Connecting With SQL

It’s assumed that you have already created an account on the GridGain Cloud and started the first cluster there.

The following example image shows a running cluster called MyCluster using the free-tier.

GridGain Cloud Sample Cluster

Connecting to the Cloud

GridGain Cloud supports JDBC and ODBC connectivity as well as REST protocol and thin clients. These APIs make it possible to use the managed offering for distributed SQL database and key-value store scenarios.

The managed service goes with GridGain Client Cloud Pack and is also fully compatible with GridGain Community, Enterprise, and Ultimate Editions:

  1. Download a GridGain Client Cloud Pack from the Cluster Info window that pops up once you click the Actions button or on the cluster row of the cluster table. Other GridGain Editions can be downloaded from the website.

    GridGain Cloud Settings
  2. Unzip the ZIP archive into the installation folder on your system.

  3. Download your personal SSL certificate from the same Cluster Info. Refer to this page for more details on how to use the certificates.

  4. Copy the SSL certificate (keyStore.jks) to the bin directory of GridGain Cloud Pack or your GridGain distribution.

GridGain Cloud Cluster Info

Connecting With SQLLine

From the bin directory of GridGain Cloud Pack or your GridGain distribution, launch SQLLine with the command copied from the JDBC row of the Cluster Info screen:

GridGain Cloud Settings

The command should look like as follows with [address], [port], and [cluster_password] parameters from your Cluster Info screen:

./sqlline.sh --verbose=true -u "jdbc:ignite:thin://[address]:[port];user=ignite;password=[cluster_password];
sslMode=require;sslClientCertificateKeyStoreUrl=keyStore.jks;sslTrustCertificateKeyStoreUrl=keyStore.jks"
sqlline.bat --color=true --verbose=true -u jdbc:ignite:thin://[address]:[port];user=ignite;password=[cluster_password];
sslMode=require;sslClientCertificateKeyStoreUrl=keyStore.jks;sslTrustCertificateKeyStoreUrl=keyStore.jks

Preloading Sample Database

Once the SQLLine tool successfully launches, SQL commands can be directly entered into the shell-like environment. Let’s start with preloading the sample World Database shipped with the latest GridGain and Apache Ignite distributions. You can find it in your {gridgain-version}/examples/sql/ directory. Execute the !run command supported by SQLLine to preload the database:

!run ../examples/sql/world.sql
!run ..\examples\sql\world.sql

Running Queries

Now, let’s run a query from SQLLine to get the most populated countries in the world in descending order, as follows:

SELECT name, population
FROM Country
ORDER BY population DESC LIMIT 10;

This will provide the following output:

+--------------------------------+--------------------------------+
|              NAME              |            MAX_POP             |
+--------------------------------+--------------------------------+
| China                          | 1277558000                     |
| United States                  | 278357000                      |
| Russian Federation             | 146934000                      |
+--------------------------------+--------------------------------+

Next, get the most inhabited cities across China, Russia, and the USA by joining the data stored in the Country and City tables:

SELECT country.name, city.name, MAX(city.population) as max_pop
FROM country
JOIN city ON city.countrycode = country.code
WHERE country.code IN ('USA','RUS','CHN')
GROUP BY country.name, city.name
ORDER BY max_pop DESC;

This will provide the following output:

+--------------------------------+--------------------------------+------------+
|              NAME              |              NAME              |            |
+--------------------------------+--------------------------------+------------+
| China                          | Shanghai                       | 9696300    |
| Russian Federation             | Moscow                         | 8389200    |
| United States                  | New York                       | 8008278    |
| China                          | Peking                         | 7472000    |
| China                          | Chongqing                      | 6351600    |
| Russian Federation             | St Petersburg                  | 4694000    |
| United States                  | Los Angeles                    | 3694820    |
| United States                  | Chicago                        | 2896016    |
| Russian Federation             | Novosibirsk                    | 1398800    |
+--------------------------------+--------------------------------+------------+