Apache Ignite SQL Documentation

The Apache Ignite SQL Developer Hub

Welcome to the Apache Ignite SQL developer hub. You'll find comprehensive guides and documentation to help you start working with Apache Ignite SQL as quickly as possible, as well as support if you get stuck. Let's jump right in!

Get Started    

Apache Zeppelin

Overview

Apache Zeppelin is a web-based notebook that enables interactive data analytics. You can make beautiful data-driven, interactive and collaborative documents with SQL, Scala and more.

You can use Zeppelin to retrieve distributed data from Ignite caches using Ignite SQL interpreter. Moreover, Ignite interpreter allows you to execute any Scala code in cases when SQL doesn't fit your requirements - For example, you can populate data into your Ignite caches or execute distributed computations.

Zeppelin Installation and Configuration

In order to start using Ignite interpreters, you should install Zeppelin in two simple steps:

  1. Clone Zeppelin Git repository
    git clone https://github.com/apache/incubator-zeppelin.git
  2. Build Zeppelin from sources
    cd incubator-zeppelin
    mvn clean install -Dignite-version=1.7.0 -DskipTests

Building Zeppelin with specific Ignite version

You can use ignite-version property for building Zeppelin with a specific Ignite version. Use version 1.7.0 or later.

Adding Ignite Interpreters

By default, Ignite and Ignite SQL interpreters are already configured in Zeppelin. Otherwise, you should add the following interpreter class names to the corresponding configuration file or environment variable (see "Configure" section of Zeppelin installation guide):

  • org.apache.zeppelin.ignite.IgniteInterpreter
  • org.apache.zeppelin.ignite.IgniteSqlInterpreter

Note: First interpreter becomes a default.

Once Zeppelin is installed and configured, you can start it by using the following command:

./bin/zeppelin-daemon.sh start

Then, open the start page in your browser (the default start page URL is http://localhost:8080).

Apache Zeppelin start page

Apache Zeppelin start page

Configuring Ignite Interpreter

Click on the Interpreter menu item. This page contains settings for all configured interpreter groups. Scroll down to the Ignite section and modify the properties as you need using the Edit button. Click the Save button to save the changes. Don't forget to restart the interpreter after making any changes to the configuration.

Apache Ignite interpreters settings

Apache Ignite interpreters settings

Configuring Ignite SQL Interpreter

Ignite SQL interpreter requires only ignite.jdbc.url property that contains the JDBC connection URL. In our example, we will use words cache. Edit ignite.jdbc.url property by setting the following value: jdbc:ignite://localhost:11211/words.

Also see JDBC Driver section for more details.

Configuring Ignite Interpreter

For most simple cases, Ignite interpreter requires the following properties:

  • ignite.addresses - Coma separated list of Ignite cluster hosts. See Cluster Configuration section for details.
  • ignite.clientMode - You can connect to the Ignite cluster as client or server node. See Clients vs. Servers section for details. Use true or false values in order to connect in client or server mode respectively.
  • ignite.peerClassLoadingEnabled - Enables peer-class-loading. See Zero Deployment section for details. Use true or false values in order to enable or disable P2P class loading respectively.

For more complicated cases, you can define your own configuration of Ignite using ignite.config.url property that contains URL to Ignite configuration file. Note that if ignite.config.url property is defined, then all the aforementioned properties will be ignored.

Using Ignite Interpreter

Starting Ignite cluster

Before using Zeppelin, we need to start the Ignite cluster. Download the latest Apache Ignite In-Memory Data Fabric binary release and unpack the downloaded archive:
unzip apache-ignite-fabric-{version}-bin.zip -d <dest_dir>

Examples are shipped as a separate Maven project, so to start running, you simply need
to import the provided <dest_dir>/apache-ignite-fabric-{version}-bin/pom.xml file into your favorite IDE.

Start the following examples:

  • org.apache.ignite.examples.ExampleNodeStartup - starts one Ignite node. You can start one or more nodes.
  • org.apache.ignite.examples.streaming.wordcount.StreamWords - starts client node that continuously streams words into words cache.

Now you are ready to use Zeppelin for accessing​ the Ignite cluster.

Creating new note in Zeppelin

Create a new (or open existing) note using the Notebook menu item.

Creating new note

Creating new note

After creating a new note, you should click the Notebook menu item again and open the created note. Click to the note's name in order to rename it. Enter a new title and press the Enter key.

New note

New note

Since the note is created, you can input a SQL query or Scala code and execute it by clicking the Execute button (blue triangle icon).

New note with user defined name

New note with user defined name

Using Ignite SQL interpreter

For executing a SQL query, use %ignite.ignitesql prefix followed with your SQL query. For example, we can select top 10 words in our words cache using the following query:

%ignite.ignitesql select _val, count(_val) as cnt from String group by _val order by cnt desc limit 10
Using Ignite SQL interpreter

Using Ignite SQL interpreter

After executing this example, you can see result as table or graph. Use corresponding icons to change the view.

SQL query result as table

SQL query result as table

SQL query result as graph

SQL query result as graph

SQL query result as pie chart

SQL query result as pie chart

Using Ignite interpreter

For executing Scala code, use %ignite prefix followed with your code snippet. For example, we can select average, min and max counts of all the words in a string:

%ignite
import org.apache.ignite._
import org.apache.ignite.cache.affinity._
import org.apache.ignite.cache.query._
import org.apache.ignite.configuration._

import scala.collection.JavaConversions._

val cache: IgniteCache[AffinityUuid, String] = ignite.cache("words")

val qry = new SqlFieldsQuery("select avg(cnt), min(cnt), max(cnt) from (select count(_val) as cnt from String group by _val)", true)

val res = cache.query(qry).getAll()

collectionAsScalaIterable(res).foreach(println _)
Using Ignite interpreter

Using Ignite interpreter

After executing this example, you will see the output of Scala REPL.

Scala REPL output

Scala REPL output

Note that Ignite version of your Ignite cluster and Zeppelin installation must be same.