JavaC#/.NETC++SQL*IntegrationsIgnite for SparkTools
Guides
ExamplesAsk a Question

SQL Tooling

❗️

This is a legacy Apache Ignite documentation

The new documentation is hosted here: https://ignite.apache.org/docs/latest/

Apache Ignite JDBC and ODBC drivers make it possible to connect to the cluster and process data stored there from a SQL tool of a choice. All you need to do is to configure either JDBC or ODBC driver for the tool. In this documentation, it will be shown how to accomplish these basic configuration steps taking DBeaver tool as an example:

👍

These are examples of other SQL tools that are explicitly documented by Ignite community:

Installation and Configuration

As an installation example we will use DBeaver, which is a is free and open source universal database tool for developers and database administrators. It supports all popular databases including Apache Ignite.

Apache Ignite is shipped with its own implementation of the JDBC driver that can be used by DBeaver to work with the data stored in a distributed Ignite cluster.

Download and install DBeaver for an operating system of your choice and download the latest Apache Ignite version.

Once DBeaver is installed, launch it and select the Database->Driver Manager menu item to configure the Apache Ignite JDBC driver. Use Apache Ignite as a database/driver name and click on the New button:

2556

Fill out the required settings on the next screen, as follows:

  • Driver Name - a custom name, set it to Apache Ignite for simplicity.
  • Class Name - set org.apache.ignite.IgniteJdbcThinDriver as a value.
  • URL Template - that's Ignite's JDBC connection string, jdbc:ignite:thin://127.0.0.1/ should be used a part of this getting started guide.
  • Default Port - port 10800 is used by Ignite's JDBC driver by default. See the JDBC documentation if you need to change the port or modify the URL connection string shared above.
  • Libraries tab - click on Add file button and locate {apache-ignite-version}/libs/ignite-core-{version}.jar file that includes the Ignite's JDBC driver.
2556

Click on the OK button to complete the setup and close the Driver Manager dialog after that. You will see Apache Ignite among the drivers' list:

2538

Connecting to a Cluster

The next step is to start an Apache Ignite cluster and connect to it with DBeaver.

Open a preferred command line tool and go to {apache-ignite-version}/bin executing the ignite.sh or ignite.bat script after that:

./ignite.sh

ignite.bat

The script starts an Apache Ignite node. You can start as many nodes as you want using the same script. Once the node is started you will see output similar to the following:

[12:46:46]    __________  ________________ 
[12:46:46]   /  _/ ___/ |/ /  _/_  __/ __/ 
[12:46:46]  _/ // (7 7    // /  / / / _/   
[12:46:46] /___/\___/_/|_/___/ /_/ /___/  
[12:46:46] 
[12:46:46] ver. 2.1.0#20170720-sha1:a6ca5c8a
[12:46:46] 2017 Copyright(C) Apache Software Foundation
[12:46:46] 
[12:46:46] Ignite documentation: http://ignite.apache.org
[12:46:46] 
[12:46:46] Quiet mode.
[12:46:46]   ^-- Logging to file '/Users/dmagda/Downloads/apache-ignite-fabric-2.1.0-bin/work/log/ignite-20d0a1be.0.log'
[12:46:46]   ^-- To see **FULL** console log here add -DIGNITE_QUIET=false or "-v" to ignite.{sh|bat}
[12:46:46] 
[12:46:46] OS: Mac OS X 10.12.6 x86_64
[12:46:51] VM information: Java(TM) SE Runtime Environment 1.8.0_77-b03 Oracle Corporation Java HotSpot(TM) 64-Bit Server VM 25.77-b03
[12:46:51] Configured plugins:
[12:46:51]   ^-- None
[12:46:58] Ignite node started OK (id=20d0a1be)
[12:47:03] Topology snapshot [ver=1, servers=1, clients=0, CPUs=4, heap=1.0GB]

Switch back to DBeaver and select the Database->New Connection menu item. Look up Apache Ignite in the list and click on Next > button:

2552

Ensure that the JDBC URL setting points to the previously set up jdbc:ignite:thin://127.0.0.1/ connection string and push the Test Connection ... button to validate the connectivity between DBeaver and the Apache Ignite cluster running locally:

2542

🚧

If you do not see the connection string in the JDBC URL field, select the Back > button and then select the Next > button again.

If the test passes click on Next > on this and the following Network screen:

2548

Validate the settings on the final screen clicking on the Finish button at the end:

2538

Apache Ignite will appear in Database Navigator tab:

2548

🚧

Database Metadata not Supported Error

If you are on Apache Ignite version 2.0 or 2.1 then you will get SQL Error: Database metadata not supported by driver error in an attempt to unfold Tables, Views or other menu items. The metadata will be supported in the nearest Apache Ignite releases. However, the lack of this feature does not affect the overall usage experience as you will see below.

Data Querying and Analysis

The next step is to define an SQL schema, insert and query some data with DBeaver. Let's pick SQL Data Definition and Data Manipulation statements available in SQL Getting Started Guide.

Once again, ensure the tool is connected to the cluster. Click on the right mouse button to see a special menu and open SQL Editor:

2552

Tables and Indexes Creation

Create City and Person tables using the following SQL statements:

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"

Paste the statements into DBeaver's script window and click on the Execute SQL Statement menu item:

2546

Once the tables are created, define some indexes, as follows:

CREATE INDEX idx_city_name ON City (name)

CREATE INDEX idx_person_name ON Person (name)
2546

Inserting Data

Insert some records into the cluster using the following statements:

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

Next, you should execute all the statements separately (one-by-one). The bulk insert will be supported in a future Apache Ignite release:

2546

Querying Data

Once we have loaded some data, we can perform queries so that we can answer some questions. Here are some examples of how data can be selected, including joining across two tables:

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

Refer to SQL Getting Started Guide that includes more statements that can be executed for the defined SQL schema or go ahead and create your own cluster configuration and start working with it.

Updated over 4 years ago