Author: dmagda
Date: Fri Jul 13 18:34:17 2018
New Revision: 1835853
URL: http://svn.apache.org/viewvc?rev=1835853&view=rev
Log:
updated SQL page
Added:
ignite/site/trunk/features/sql-2.html
Modified:
ignite/site/trunk/features/datagrid.html
Modified: ignite/site/trunk/features/datagrid.html
URL:
http://svn.apache.org/viewvc/ignite/site/trunk/features/datagrid.html?rev=1835853&r1=1835852&r2=1835853&view=diff
==============================================================================
--- ignite/site/trunk/features/datagrid.html (original)
+++ ignite/site/trunk/features/datagrid.html Fri Jul 13 18:34:17 2018
@@ -70,7 +70,7 @@ under the License.
</p>
<div class="page-heading">3rd Party Databases</div>
<p>
- Inite in-memory data grid can improve performance and
scalability of existing 3rd party databases, like RDBMS,
+ Ignite in-memory data grid can improve performance and
scalability of existing 3rd party databases, like RDBMS,
NoSQL, or Hadoop-based storages, by sliding in as a
distribute cache between the application and database layers. This approach
does not require
rip-and-replace of the existing data, and will
automatically write-through or read-through all the updates
or reads to or from the underlying database. Ignite
will automatically merge with the underlying database
Added: ignite/site/trunk/features/sql-2.html
URL:
http://svn.apache.org/viewvc/ignite/site/trunk/features/sql-2.html?rev=1835853&view=auto
==============================================================================
--- ignite/site/trunk/features/sql-2.html (added)
+++ ignite/site/trunk/features/sql-2.html Fri Jul 13 18:34:17 2018
@@ -0,0 +1,714 @@
+<!--
+ âââ ââââââ âââ ââââââ
âââ ââ ââââââ âââ âââââ
ââââ â ââââââââââââââââââ
+ââââââ ââââ âââââââââ
ââââ ââ ââââ âââââ â ââââ
âââ âââ ââ ââ â âââââ âââ ââââ
â
+âââ âââ ââââ âââââââ âââ âââ
â ââââââââââââ
âââââââââââââââ ââ ââââââââ
ââââ ââââââ
+âââââââââ âââââââ
ââââââââââ ââââ âââââââ âââ
âââ â âââââââ âââââââ
ââââââââââ ââââ â âââ â
+ ââ ââââââââ â â ââ âââââ
âââââ ââââââââââââââââ
ââââââââââââââââ ââââââââ
ââââ â âââââââ
+ ââ ââââââââ â â ââ âââââ ââ
â â â âââââââ ââ â ââ ââ â â
ââ â â ââ â ââ ââ ââ â
+ â ââ âââ â â ââ â â â â âââ
â â â â â â â â â ââ â ââ â â â
â â â
+ â â ââ â â â â ââ â â
â ââ â â â â â â â â â
+ â â â ââ â â â â â â
â â â â â â
+-->
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+<!DOCTYPE html>
+<html>
+<head>
+ <link rel="canonical" href="https://ignite.apache.org/features/sql.html"; />
+ <meta charset="utf-8">
+ <meta name="viewport" content="width=device-width, initial-scale=1.0">
+ <title>Distributed SQL - Apache Ignite</title>
+ <link media="all" rel="stylesheet" href="/css/all.css?v=1514336028">
+ <link
href="https://netdna.bootstrapcdn.com/font-awesome/4.2.0/css/font-awesome.css";
rel="stylesheet">
+ <link media="all" rel="stylesheet" href="/css/syntaxhighlighter.css">
+ <link
href='https://fonts.googleapis.com/css?family=Open+Sans:400,300,300italic,400italic,600,600italic,700,700italic,800,800italic'
rel='stylesheet' type='text/css'>
+
+ <!--#include virtual="/includes/sh.html" -->
+</head>
+<body>
+<div id="wrapper">
+ <!--#include virtual="/includes/header.html" -->
+
+
+ <main id="main" role="main" class="container">
+ <section id="sql-queries" class="page-section">
+ <h1 class="first">Distributed Memory-Centric SQL Database</h1>
+ <div class="col-sm-12 col-md-12 col-xs-12" style="padding:0 0 20px
0;">
+ <div class="col-sm-6 col-md-7 col-xs-12"
style="padding-left:0;">
+ <p>
+ Apache Ignite comes with ANSI-99 compliant,
horizontally scalable and fault-tolerant
+ distributed SQL database. You can interact with Ignite
as you would with any other SQL storage, using standard JDBC or ODBC
+ connectivity. Ignite also provides native SQL APIs for
Java, .NET and C++ developers for better performance.
+ </p>
+ <p>
+ Unlike other distributed SQL databases, Ignite treats
both memory and disk as active storage tiers.
+ The disk tier, a.k.a. <nobr><a
href="/arch/persistence.html">native persistence</a></nobr>, is disabled by
default,
+ in which case Ignite becomes a pure <nobr><a
href="/use-cases/database/in-memory-database.html">in-memory database</a>
(IMDB)</nobr>.
+ </p>
+ <p>
+
+ </p>
+ </div>
+
+ <div class="col-sm-6 col-md-5 col-xs-12"
style="padding-right:0">
+ <img class="img-responsive" src="/images/sql_database.png"
width="400px" style="float:right;"/>
+ </div>
+ </div>
+ <div class="page-heading">SQL Joins</div>
+ <p>
+ Ignite can <nobr><a
href="https://apacheignite-sql.readme.io/docs/distributed-joins";
target="_blank">join data</a></nobr>
+ in both collocated and non-collocated fashions. When <a
href="/features/collocatedprocessing.html">collocated</a>,
+ the JOINs are executed on the nodes where the data is stored,
avoiding
+ expensive serialization and network trips. Such approach
provides the best scalability and performance
+ in distributed databases.
+ </p>
+
+ <div class="page-heading">Memory Mode</div>
+ <p>
+ In scenarios when the whole data set and indexes fit in
memory, and the persistence is disabled,
+ Ignite will function as an in-memory database supporting SQL,
together with all the existing APIs for memory-only use cases.
+ </p>
+
+ <div class="page-heading">3rd Party Databases</div>
+ <p>
+ Owing to the support for read-through and write-through
capabilities, Ignite can be used as a caching layer (aka. data grid) above an
existing 3rd party database -
+ RDBMS, NoSQL, or HDFS. This mode is used to accelerate the
underlying database. Automatic integration is
+ provided with most of the known databases, like Oracle, MySQL,
PostgreSQL, Apache Cassandra, etc.
+ </p>
+
+ <div class="page-heading">Native Persistence</div>
+ <p>
+ Ignite <a
href="https://apacheignite.readme.io/docs/distributed-persistent-store";
target="_blank">native persistence</a> is a distributed ACID and SQL-compliant
disk store that transparently integrates with Ignite's durable memory.
+ Ignite persistence is optional and can be turned on and off.
+ When turned off, Ignite becomes a pure in-memory store. When
native persistence is enabled, Ignite stores both data and indexes on disk.
+ A subset of data and the most frequently used indexes are
stored in memory. Additionally, since Ignite persists indexes on disk,
+ they do not have to be rebuilt on cluster restart making the
system faster than other in-memory databases.
+ </p>
+
+
+ <p>
+ Want to learn more? See <a
href="https://apacheignite-sql.readme.io/docs/overview"; target="_blank">Ignite
SQL Capabilities</a>
+
+ </p>
+
+ <div class="code-examples">
+ <div class="page-heading">Connectivity</div>
+ <p>The following code examples show how to use the JDBC and
ODBC drivers and open a connection:</p>
+ <!-- Nav tabs -->
+ <ul id="connectivity-examples" class="nav nav-tabs">
+ <li class="active"><a href="#jdbc-connectivity"
aria-controls="profile" data-toggle="tab">JDBC</a></li>
+ <li><a href="#odbc-connectivity" aria-controls="profile"
data-toggle="tab">ODBC</a></li>
+ </ul>
+
+ <!-- Tab panes -->
+ <div class="tab-content">
+
+ <div class="tab-pane active" id="jdbc-connectivity">
+ <pre class="brush:java">
+
+
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");
+
+ Connection conn = DriverManager.getConnection(
+ "jdbc:ignite:thin://127.0.0.1/");
+
+ </pre>
+ </div>
+ <div class="tab-pane" id="odbc-connectivity">
+ <pre class="brush:cpp">
+ // Combining connect string
+ std::string connectStr = "DRIVER={Apache
Ignite};SERVER=localhost;PORT=10800;SCHEMA=Person;";
+
+ SQLCHAR outstr[ODBC_BUFFER_SIZE];
+ SQLSMALLINT outstrlen;
+
+ // Connecting to ODBC server
+ SQLRETURN ret = SQLDriverConnect(dbc, NULL,
reinterpret_cast<SQLCHAR*>(&connectStr[0]),
static_cast<SQLSMALLINT>(connectStr.size()),
+ outstr, sizeof(outstr), &outstrlen,
SQL_DRIVER_COMPLETE)
+ </pre>
+ </div>
+ </div>
+ <p>
+ Ignite <a
href="https://apacheignite.readme.io/docs/getting-started";
target="_blank">Java</a>,
+ <a
href="https://apacheignite-net.readme.io/docs/getting-started-2";
target="_blank">.NET</a> or
+ <a
href="https://apacheignite-cpp.readme.io/docs/getting-started-1";
target="_blank">C++</a> APIs can be
+ used as alternatives to the JDBC and ODBC drivers.
+ </p>
+ </div>
+
+
+ <div class="code-examples">
+ <div class="page-heading">SQL Tables Creation</div>
+ <p>Ignite supports <b>Data Definition Language (DDL)</b>
statements for creating and removing SQL tables
+ and indexes at runtime.
+ </p>
+ <p> The following examples show how to create tables:â </p>
+ <!-- Nav tabs -->
+ <ul id="ddl-examples" class="nav nav-tabs">
+ <li class="active"><a href="#ddl-sql"
aria-controls="profile" data-toggle="tab">SQL</a></li>
+ <li><a href="#ddl-jdbc" aria-controls="profile"
data-toggle="tab">JDBC</a></li>
+ <li><a href="#ddl-odbc" aria-controls="profile"
data-toggle="tab">ODBC</a></li>
+ <!--<li><a href="#ddl-java" aria-controls="profile"
data-toggle="tab">Java API</a></li>
+ <li><a href="#ddl-cpp" aria-controls="profile"
data-toggle="tab">C++ API</a></li>-->
+ </ul>
+
+ <!-- Tab panes -->
+ <div class="tab-content">
+
+ <div class="tab-pane active" id="ddl-sql">
+ <pre class="brush:sql">
+ 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"
+ </pre>
+ </div>
+ <!--<div class="tab-pane" id="ddl-java">
+ <pre class="brush:java">
+ IgniteCache<CityKey, City> cache =
ignite.cache("cityCache");
+
+ // Create table based on REPLICATED template
+ SqlFieldsQuery query = new SqlFieldsQuery(
+ "CREATE TABLE City (" +
+ " id LONG PRIMARY KEY, name VARCHAR)" +
+ " WITH \"template=replicated\"");
+
+ cache.query(query).getAll();
+
+ // Create table based on PARTITIONED template with
one backup
+ query = new SqlFieldsQuery(
+ "CREATE TABLE Person (" +
+ " id LONG, name VARCHAR, city_id LONG," +
+ " PRIMARY KEY (id, city_id))" +
+ " WITH \"backups=1, affinityKey=city_id\"");
+
+ cache.query(query).getAll();
+
+ </pre>
+ </div>-->
+ <div class="tab-pane" id="ddl-jdbc">
+ <pre class="brush:java">
+ // Create table
+ try (Statement stmt = conn.createStatement()) {
+
+ // Create table based on REPLICATED template
+ stmt.executeUpdate("CREATE TABLE City (" +
+ " id LONG PRIMARY KEY, name VARCHAR) " +
+ " WITH \"template=replicated\"");
+
+ // Create table based on PARTITIONED template
with one backup
+ stmt.executeUpdate("CREATE TABLE Person (" +
+ " id LONG, name VARCHAR, city_id LONG, " +
+ " PRIMARY KEY (id, city_id)) " +
+ " WITH \"backups=1, affinityKey=city_id\"");
+ }
+ </pre>
+ </div>
+ <div class="tab-pane" id="ddl-odbc">
+ <pre class="brush:cpp">
+ SQLHSTMT stmt;
+
+ // Allocate a statement handle
+ SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
+
+ // Create table based on REPLICATED template
+ SQLCHAR query[] = "CREATE TABLE City ( "
+ "id LONG PRIMARY KEY, name VARCHAR) "
+ "WITH \"template=replicated\"";
+ SQLSMALLINT queryLen =
static_cast<SQLSMALLINT>(sizeof(queryLen));
+
+ SQLRETURN ret = SQLExecDirect(stmt, query,
queryLen);
+
+ // Create table based on PARTITIONED template with
one backup
+ SQLCHAR query[] = "CREATE TABLE Person ( "
+ "id LONG, name VARCHAR, city_id LONG "
+ "PRIMARY KEY (id, city_id)) "
+ "WITH \"backups=1, affinityKey=city_id\"";
+ SQLSMALLINT queryLen =
static_cast<SQLSMALLINT>(sizeof(query));
+
+ SQLRETURN ret = SQLExecDirect(stmt, query,
queryLen);
+ </pre>
+ </div>
+ <!--<div class="tab-pane" id="ddl-cpp">
+ <pre class="brush:cpp">
+ Cache<CityKey, City> cache =
ignite.GetCache<CityKey, City>("cityCache");
+
+ // Create table based on REPLICATED template
+ SqlFieldsQuery query("CREATE TABLE City ("
+ " id LONG PRIMARY KEY, name VARCHAR)"
+ " WITH \"template=replicated\"");
+
+ cache.Query(query);
+
+ // Create table based on PARTITIONED template with
one backup
+ query.SetSql("CREATE TABLE Person ("
+ " id LONG, name VARCHAR, city_id LONG,"
+ " PRIMARY KEY (id, city_id))"
+ " WITH \"backups=1, affinityKey=city_id\"");
+
+ cache.Query(query);
+ </pre>
+ </div>-->
+ </div>
+
+ <p> In the above example, for the Person table, Ignite creates
a distributed cache with 1 backup of data and city_id as the affinity key.
+ These <b>extended parameters</b> are Ignite specific that
can be passed using the <code>WITH</code> clause. To set other cache
configurations for the table,
+ you should use the <code>template</code> parameter and
provide the name of the cache configuration previously registered(via XML or
code).
+ <a
href="https://apacheignite-sql.readme.io/docs/create-table#section-parameters";
target="_blank">Read more</a> about extended parameters.
+ </p>
+ <p>
+ See <a href="https://apacheignite-sql.readme.io/docs/ddl";
target="_blank">Ignite DDL documentation</a> for more details.
+ </p>
+ </div>
+ <div class="code-examples">
+ <div class="page-heading">Data Modification</div>
+ <p>
+ To modify data stored in Ignite, use standard <b>DML</b>
statements like INSERT, UPDATE, or DELETE.
+ </p>
+ <!-- Nav tabs -->
+ <ul id="dml-examples" class="nav nav-tabs">
+ <li class="active"><a href="#dml-sql"
aria-controls="profile" data-toggle="tab">SQL</a></li>
+ <li><a href="#dml-jdbc" aria-controls="profile"
data-toggle="tab">JDBC</a></li>
+ <li><a href="#dml-odbc" aria-controls="profile"
data-toggle="tab">ODBC</a></li>
+ <li><a href="#dml-java" aria-controls="profile"
data-toggle="tab">Java API</a></li>
+ <li><a href="#dml-dotnet" aria-controls="profile"
data-toggle="tab">.NET API</a></li>
+ <li><a href="#dml-cpp" aria-controls="profile"
data-toggle="tab">C++ API</a></li>
+ </ul>
+
+ <!-- Tab panes -->
+ <div class="tab-content">
+
+ <div class="tab-pane active" id="dml-sql">
+ <pre class="brush:sql">
+ INSERT INTO City (id, name) VALUES (1, 'Forest
Hill');
+ INSERT INTO City (id, name) VALUES (2, 'Denver');
+
+
+ UPDATE City
+ SET name = 'Foster City'
+ WHERE id = 2
+
+
+ DELETE FROM City
+ WHERE name = 'Foster City'
+ </pre>
+ </div>
+ <div class="tab-pane" id="dml-java">
+ <pre class="brush:java">
+ IgniteCache<Long, City> cache =
ignite.cache("SQL_PUBLIC_CITY");
+
+ // Insert data
+ cache.query(new SqlFieldsQuery("INSERT INTO
City(id, name) " +
+ " values (1, 'Forest Hill'), (2, 'Denver')"));
+
+ // Update data
+ cache.query(new SqlFieldsQuery("UPDATE City set
name = ? " +
+ "WHERE id = ?").setArgs("Foster City", 2L));
+
+ // Delete data
+ cache.query(new SqlFieldsQuery("DELETE FROM City "
+
+ "WHERE id = ?").setArgs(2L));
+ </pre>
+ </div>
+ <div class="tab-pane" id="dml-jdbc">
+ <pre class="brush:java">
+ // Populate City table
+ try (PreparedStatement stmt =
+ conn.prepareStatement("INSERT INTO City (id, name)
VALUES (?, ?)")) {
+
+ stmt.setLong(1, 1L);
+ stmt.setString(2, "Forest Hill");
+ stmt.executeUpdate();
+
+ stmt.setLong(1, 2L);
+ stmt.setString(2, "Denver");
+ stmt.executeUpdate();
+ }
+
+ // Update City
+ try (Statement stmt = conn.createStatement()) {
+
+ stmt.executeUpdate("UPDATE City SET name =
'Foster City' WHERE id = 2");
+ }
+
+ // Delete from City
+ try (Statement stmt = conn.createStatement()) {
+
+ stmt.executeUpdate("DELETE FROM City WHERE
name = 'Foster City'");
+ }
+
+ </pre>
+ </div>
+ <div class="tab-pane" id="dml-odbc">
+ <pre class="brush:cpp">
+ SQLHSTMT stmt;
+
+ // Allocate a statement handle
+ SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
+
+ // Populate City table
+ SQLCHAR query1[] = "INSERT INTO City (id, name)
VALUES (?, ?)";
+
+ ret = SQLPrepare(stmt, query1,
static_cast<SQLSMALLINT>(sizeof(query1)));
+
+ key = 1;
+ strncpy(name, "Forest Hill", sizeof(name));
+ ret = SQLExecute(stmt);
+
+ key = 2;
+ strncpy(name, "Denver", sizeof(name));
+ ret = SQLExecute(stmt);
+
+ // Update City
+ SQLCHAR query[] = "UPDATE City SET name = 'Foster
City' WHERE id = 2"
+
+ SQLSMALLINT queryLen =
static_cast<SQLSMALLINT>(sizeof(queryLen));
+
+ SQLRETURN ret = SQLExecDirect(stmt, query,
queryLen);
+
+ // Delete from City
+ SQLCHAR query[] = "DELETE FROM City WHERE name =
'Foster City'"
+
+ SQLSMALLINT queryLen =
static_cast<SQLSMALLINT>(sizeof(queryLen));
+
+ SQLRETURN ret = SQLExecDirect(stmt, query,
queryLen);
+ </pre>
+ </div>
+ <div class="tab-pane" id="dml-dotnet">
+ <pre class="brush:csharp">
+ ICache<long, City> cache =
ignite.GetCache<long, City>("SQL_PUBLIC_CITY");
+
+ // Insert data
+ cache.QueryFields(new SqlFieldsQuery("INSERT INTO
City(id, name) " +
+ " values (1, 'Forest Hill'), (2, 'Denver')"));
+
+ // Update data
+ cache.QueryFields(new SqlFieldsQuery("UPDATE City
set name = ? " +
+ "WHERE id = ?", "Foster City", 2));
+
+ // Delete data
+ cache.QueryFields(new SqlFieldsQuery("DELETE FROM
City " +
+ "WHERE id = ?", 2));
+ </pre>
+ </div>
+ <div class="tab-pane" id="dml-cpp">
+ <pre class="brush:cpp">
+ Cache<int64_t, City> cache =
ignite.GetCache<int64_t, City>("SQL_PUBLIC_CITY");
+
+ // Insert data
+ cache.Query(SqlFieldsQuery("INSERT INTO City(id,
name) "
+ " values (1, 'Forest Hill'), (2, 'Denver')"));
+
+ // Update data
+ query = SqlFieldsQuery("UPDATE City set name = ?
WHERE id = ?");
+ query.AddArgument("Foster City");
+ query.AddArgument(2LL);
+ cache.Query(query);
+
+ // Delete data
+ query = SqlFieldsQuery("DELETE FROM City WHERE id
= ?");
+ query.AddArgument(2LL);
+ cache.Query(query);
+ </pre>
+ </div>
+ </div>
+ <p>
+ See <a href="https://apacheignite-sql.readme.io/docs/dml";
target="_blank">Ignite DML documentation</a> for more details.
+ </p>
+ </div>
+ <div class="code-examples">
+ <div class="page-heading">Querying Data</div>
+ <p>Ignite supports free-form SQL queries and joins that are
fully distributed and fault-tolerant.
+ The SQL syntax is ANSI-99 compliant which means that you
can use
+ any kind of SQL functions, aggregations, groupings or
joins, defined by the specification,
+ as a part of an SQL query.
+ </p>
+ <!-- Nav tabs -->
+ <ul id="sql-examples" class="nav nav-tabs">
+ <li class="active"><a href="#query-sql"
aria-controls="home" data-toggle="tab">SQL</a></li>
+ <li><a href="#query-jdbc" aria-controls="profile"
data-toggle="tab">JDBC</a></li>
+ <li><a href="#query-odbc" aria-controls="profile"
data-toggle="tab">ODBC</a></li>
+ <li><a href="#query-java" aria-controls="profile"
data-toggle="tab">Java API</a></li>
+ <li><a href="#query-dotnet" aria-controls="profile"
data-toggle="tab">.NET API</a></li>
+ <li><a href="#query-cpp" aria-controls="profile"
data-toggle="tab">C++ API</a></li>
+ </ul>
+
+ <!-- Tab panes -->
+ <div class="tab-content">
+ <div class="tab-pane active" id="query-sql">
+ <pre class="brush:sql">
+ SELECT p.name, c.name
+ FROM Person p, City c
+ WHERE p.city_id = c.id
+ </pre>
+ </div>
+ <div class="tab-pane" id="query-java">
+ <pre class="brush:java">
+ IgniteCache<PersonKey, Person> personCache =
ignite.cache("SQL_PUBLIC_PERSON");
+
+ // SQL join on Person and City.
+ SqlFieldsQuery sql = new SqlFieldsQuery(
+ "SELECT p.name, c.name "
+ + "FROM Person as p, City as c "
+ + "WHERE p.city_id = c.id");
+
+ // Execute the query and obtain the query result
cursor.
+ try (QueryCursor<List<?>> cursor =
personCache.query(sql)) {
+ for (List<?> row : cursor)
+ System.out.println("Person name & city=" +
row.get(0));
+ }
+ </pre>
+ </div>
+ <div class="tab-pane" id="query-jdbc">
+ <pre class="brush:java">
+ try (Statement stmt = conn.createStatement()) {
+ try (ResultSet rs =
+ stmt.executeQuery("SELECT p.name, c.name " +
+ " FROM Person p, City c " +
+ " WHERE p.city_id = c.id")) {
+
+ System.out.println("Query results:");
+
+ while (rs.next())
+ System.out.println(">>> " +
+ rs.getString(1) +
+ ", " +
+ rs.getString(2));
+ }
+ }
+ </pre>
+ </div>
+ <div class="tab-pane" id="query-odbc">
+ <pre class="brush:cpp">
+ SQLHSTMT stmt;
+
+ // Allocate a statement handle
+ SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
+
+ // Get data
+ SQLCHAR query[] = "SELECT p.name, c.name "
+ "FROM Person p, City c "
+ "WHERE p.city_id = c.id";
+
+ SQLSMALLINT queryLen =
static_cast<SQLSMALLINT>(sizeof(queryLen));
+
+ SQLRETURN ret = SQLExecDirect(stmt, query,
queryLen);
+ </pre>
+ </div>
+ <div class="tab-pane" id="query-dotnet">
+ <pre class="brush:csharp">
+ ICache<PersonKey,
Person> personCache = ignite.GetCache<PersonKey,
Person>("SQL_PUBLIC_PERSON");
+
+ // SQL join on Person
and City.
+ SqlFieldsQuery sql =
new SqlFieldsQuery(
+ "SELECT p.name, c.name
"
+ + "FROM Person
as p, City as c "
+ + "WHERE
p.city_id = c.id");
+
+ // Execute the query
and obtain the query result cursor.
+ foreach (IList row in
personCache.QueryFields(sql))
+ {
+
Console.WriteLine($"Person '{row[0]}' from city '{row[1]}'");
+ }
+ </pre>
+ </div>
+ <div class="tab-pane" id="query-cpp">
+ <pre class="brush:cpp">
+ Cache<PersonKey, Person> personCache =
ignite.GetCache<int64_t, Person>("SQL_PUBLIC_PERSON");
+
+ // SQL join on Person and City.
+ SqlFieldsQuery sql("SELECT p.name, c.name "
+ "FROM Person as p, City as c "
+ "WHERE p.city_id = c.id");
+
+ // Execute the query and obtain the query result
cursor.
+ FieldsQueryCursor cursor = personCache.Query(sql));
+
+ while (cursor.HasNext())
+ {
+ QueryFieldsRow row = cursor.GetNext();
+
+ std::cout << "Person: " <<
row.GetNext<std::string>() << ", "
+ << "City: " <<
row.GetNext<std::string>() << std::endl;
+ }
+ </pre>
+ </div>
+ </div>
+ <p>
+ See <a
href="https://apacheignite-sql.readme.io/docs/select"; target="_blank">SELECT
queries documentation</a> for more details.
+ </p>
+ </div>
+
+ <div class="page-heading github-examples">GitHub Examples</div>
+ <p>
+ Also see <a
href="https://github.com/apache/ignite/blob/master/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryExample.java";
target="github">SQL queries examples</a>
+ and <a
href="https://github.com/apache/ignite/blob/master/examples/src/main/java/org/apache/ignite/examples/datagrid/CacheQueryDmlExample.java";
target="github"> DML example</a> available on GitHub.
+ </p>
+ </section>
+
+ <section id="key-features" class="page-section">
+ <h2>SQL Database Features</h2>
+ <table class="formatted" name="SQL Database Features">
+ <thead>
+ <tr>
+ <th width="35%" class="left">Feature</th>
+ <th>Description</th>
+ </tr>
+ </thead>
+ <tbody>
+ <tr>
+ <td class="left">SQL Queries</td>
+ <td>
+ <p>
+ Ignite supports free-form SQL queries without any
limitations. The SQL syntax is ANSI-99 compliant which means
+ that you can use any kind of SQL functions,
aggregations, groupings or joins.
+ </p>
+ <p>
+ SQL queries in Ignite are fully distributed and
perform in a fault-tolerant manner that guarantees
+ consistent query results regardless of cluster
topology changes.
+ </p>
+ <div class="page-links">
+ <a
href="https://apacheignite-sql.readme.io/docs/select"; target="docs">Docs for
this Feature <i class="fa fa-angle-double-right"></i></a>
+ </div>
+ </td>
+ </tr>
+ <tr>
+ <td class="left">Distributed DDL</td>
+ <td>
+ <p>
+ Apache Ignite supports Data Definition Language
(DDL) statements for creating and removing SQL tables and indexes at runtime.
Both native Apache Ignite SQL APIs as well as JDBC and ODBC drivers can be used
for SQL schema modifications.
+ </p>
+ <div class="page-links">
+ <a
href="https://apacheignite-sql.readme.io/docs/ddl"; target="docs">Docs for this
Feature <i class="fa fa-angle-double-right"></i></a>
+ </div>
+ </td>
+ </tr>
+ <tr>
+ <td class="left">Distributed DML</td>
+ <td>
+ <p>
+ Apache Ignite SQL Grid not only allows selecting
data from the Data Grid, using <code>SQL ANSI-99</code> syntax,
+ but it also makes it possible to modify that data
with well-known DML statements like <code>INSERT</code>, <code>MERGE</code>,
<code>UPDATE</code>, and <code>DELETE</code>.
+ </p>
+ <div class="page-links">
+ <a
href="https://apacheignite-sql.readme.io/docs/dml"; target="docs">Docs for this
Feature <i class="fa fa-angle-double-right"></i></a>
+ </div>
+ </td>
+ </tr>
+ <tr>
+ <td class="left">Collocated SQL Joins</td>
+ <td>
+ <p>
+ SQL joins can be performed on collocated data
stored across multiple caches.
+ Since all the cache entries with the same affinity
key are stored on the same processing node, these queries do not require
expensive network trips to fetch the data from remote nodes, and hence, are
extermely fast.
+ Joins can also be performed between
<code>PARTITIONED</code> and <code>REPLICATED</code> caches.
+ </p>
+ <div class="page-links">
+ <a
href="https://apacheignite-sql.readme.io/docs/distributed-joins#collocated-joins";
target="docs">Docs for this Feature <i class="fa
fa-angle-double-right"></i></a>
+ </div>
+ </td>
+ </tr>
+ <tr>
+ <td class="left">Non-Collocated SQL Joins</td>
+ <td>
+ <p>
+ In Ignite, the default implementation of SQL join
requires all the participating caches to be collocated. However,
+ for cases where it is extremely difficult to
collocate all the data, Ignite offers non-collocated SQL joins.
+ Data can be distributed in any way across the
cluster, and Ignite will figure out where to fetch the results from.
+ </p>
+ <div class="page-links">
+ <a
href="https://apacheignite-sql.readme.io/docs/distributed-joins#non-collocated-joins";
target="docs">Docs for this Feature <i class="fa
fa-angle-double-right"></i></a>
+ </div>
+ </td>
+ </tr>
+
+ <tr>
+ <td class="left">SQL Indexes</td>
+ <td>
+ <p>
+ For SQL queries, Ignite supports in-memory
indexing to facilitate fast data lookups.
+ If you are caching your data in off-heap memory,
then query indexes will also be cached in off-heap memory.
+ Ignite allows configuring single column indexes as
well as group indexes. It also provides support for custom indexing.
+ </p>
+ <div class="page-links">
+ <a
href="https://apacheignite-sql.readme.io/docs/create-index"; target="docs">Docs
for this feature <i class="fa fa-angle-double-right"></i></a>
+ </div>
+ </td>
+ </tr>
+ <tr>
+ <td class="left">JDBC/ODBC Driver</td>
+ <td>
+ <p>
+ Ignite is shipped with <code>JDBC/ODBC
Driver</code> that allows you to retrieve distributed data from
+ cache using standard SQL queries and JDBC/ODBC API.
+ </p>
+ <div class="page-links">
+ <a
href="https://apacheignite-sql.readme.io/docs/jdbc-driver"; target="docs">Docs
for this Feature <i class="fa fa-angle-double-right"></i></a>
+ </div>
+ </td>
+ </tr>
+ <tr>
+ <td class="left">Geospacial Support</td>
+ <td>
+ <p>
+ Ignite supports querying and indexing geometry
data types such as points, lines, and polygons considering the spatial
relationship between these geometries.
+ </p>
+ <div class="page-links">
+ <a
href="https://apacheignite-sql.readme.io/docs/geospatial-support";
target="docs">Docs for this Feature <i class="fa fa-angle-double-right"></i></a>
+ </div>
+ </td>
+ </tr>
+ <tr>
+ <td class="left">.NET & C++ Support</td>
+ <td>
+ <p>
+ Ignite.NET and Ignite C++ are buit on top of
Ignite. This allows you to perform almost all SQL Grid operations including SQL
and DML commands as well as distributed joins.
+ </p>
+ <div class="page-links">
+ <a
href="https://apacheignite-sql.readme.io/docs/net-sql-api"; target="docs">Docs
for this Feature <i class="fa fa-angle-double-right"></i></a>
+ </div>
+ </td>
+ </tr>
+ <tr>
+ <td class="left">SQL Tooling</td>
+ <td>
+ <p>
+ You can process data stored in the Ignite cluster
using a SQL tool of your choice -
+ <b>DBeaver</b>, <b>Pentaho</b>, <b>Tableau</b>,
<b>Web Console</b>, <b>Apache Zeppelin</b> and more.
+ </p>
+ <div class="page-links">
+ <a
href="https://apacheignite-sql.readme.io/docs/sql-tooling"; target="docs">Docs
for this Feature <i class="fa fa-angle-double-right"></i></a>
+ </div>
+ </td>
+ </tr>
+ </tbody>
+ </table>
+ </section>
+ </main>
+
+ <!--#include virtual="/includes/footer.html" -->
+</div>
+<!--#include virtual="/includes/scripts.html" -->
+</body>
+</html>
