docs/_docs/SQL/sql-api.adoc - ignite - Git at Google

 // 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 KIND, either express or implied.
 // See the License for the specific language governing permissions and
 // limitations under the License.
 = SQL API
 :javaSourceFile: {javaCodeDir}/SqlAPI.java

 In addition to using the JDBC driver, Java developers can use Ignite's SQL APIs to query and modify data stored in Ignite.

 The `SqlFieldsQuery` class is an interface for executing SQL statements and navigating through the results. `SqlFieldsQuery` is executed through the `IgniteCache.query(SqlFieldsQuery)` method, which returns a query cursor.

 == Configuring Queryable Fields

 If you want to query a cache using SQL statements, you need to define which fields of the value objects are queryable. Queryable fields are the fields of your data model that the SQL engine can "see" and query.

 NOTE: If you create tables using JDBC or SQL tools, you do not need to define queryable fields.

 [NOTE]
 ====
 Indexing capabilities are provided by the 'ignite-indexing' module. If you start Ignite from java code, link:setup#enabling-modules[add this module to the classpath of your application].
 ====

 In Java, queryable fields can be configured in two ways:

 * using annotations
 * by defining query entities


 === @QuerySqlField Annotation

 To make specific fields queryable, annotate the fields in the value class definition with the `@QuerySqlField` annotation and call `CacheConfiguration.setIndexedTypes(...)`.
 ////
 TODO : CacheConfiguration.setIndexedTypes is presented only in java, C# got different API, rewrite sentence above
 ////


 [tabs]
 --
 tab:Java[]

 [source,java]
 ----
 include::{javaCodeDir}/QueryEntitiesExampleWithAnnotation.java[tags=query-entity-annotation, indent=0]
 ----

 Make sure to call `CacheConfiguration.setIndexedTypes(...)` to let the SQL engine know about the annotated fields.

 tab:C#/.NET[]
 [source,csharp]
 ----
 include::code-snippets/dotnet/UsingSqlApi.cs[tag=sqlQueryFields,indent=0]
 ----
 tab:C++[unsupported]
 --

 === Query Entities

 You can define queryable fields using the `QueryEntity` class. Query entities can be configured via XML configuration.


 [tabs]
 --
 tab:XML[]
 [source,xml]
 ----
 include::code-snippets/xml/query-entities.xml[tags=ignite-config,indent=0]
 ----
 tab:Java[]
 [source,java]
 ----
 include::{javaCodeDir}/QueryEntityExample.java[tags=query-entity,indent=0]
 ----
 tab:C#/.NET[]
 [source,csharp]
 ----
 include::code-snippets/dotnet/UsingSqlApi.cs[tag=queryEntities,indent=0]
 ----
 tab:C++[unsupported]
 --

 == Querying

 To execute a select query on a cache, simply create an object of `SqlFieldsQuery` providing the query string to the constructor and run `cache.query(...)`.
 Note that in the following example, the Person cache must be configured to be <<Configuring Queryable Fields,visible to the SQL engine>>.


 [tabs]
 --
 tab:Java[]
 [source,java]
 ----
 include::{javaSourceFile}[tag=simple-query,indent=0]
 ----

 tab:C#/.NET[]
 [source,csharp]
 ----
 include::code-snippets/dotnet/UsingSqlApi.cs[tag=querying,indent=0]
 ----
 tab:C++[]
 [source,cpp]
 ----
 include::code-snippets/cpp/src/sql.cpp[tag=sql-fields-query,indent=0]
 ----
 --

 `SqlFieldsQuery` returns a cursor that iterates through the results that match the SQL query.

 === Local Execution

 To force local execution of a query, use `SqlFieldsQuery.setLocal(true)`. In this case, the query is executed against the data stored on the node where the query is run. It means that the results of the query are almost always incomplete. Use the local mode only if you are confident you understand this limitation.

 === Subqueries in WHERE Clause

 `SELECT` queries used in `INSERT` and `MERGE` statements as well as `SELECT` queries generated by `UPDATE` and `DELETE` operations are distributed and executed in either link:SQL/distributed-joins[colocated or non-colocated distributed modes].

 However, if there is a subquery that is executed as part of a `WHERE` clause, then it can be executed in the colocated mode only.

 For instance, let's consider the following query:

 [source,sql]
 ----
 DELETE FROM Person WHERE id IN
     (SELECT personId FROM Salary s WHERE s.amount > 2000);
 ----
 The SQL engine generates the `SELECT` query in order to get a list of entries to be deleted. The query is distributed and executed across the cluster and looks like the one below:
 [source,sql]
 ----
 SELECT _key, _val FROM Person WHERE id IN
     (SELECT personId FROM Salary s WHERE s.amount > 2000);
 ----
 However, the subquery from the `IN` clause (`SELECT personId FROM Salary ...`) is not distributed further and is executed over the local data set available on the node.

 == Inserting, Updating, Deleting, and Merging

 With `SqlFieldsQuery` you can execute the other DML commands in order to modify the data:


 [tabs]
 --
 tab:INSERT[]
 [source,java]
 ----
 include::{javaSourceFile}[tag=insert,indent=0]
 ----

 tab:UPDATE[]
 [source,java]
 ----
 include::{javaSourceFile}[tag=update,indent=0]
 ----

 tab:DELETE[]
 [source,java]
 ----
 include::{javaSourceFile}[tag=delete,indent=0]
 ----

 tab:MERGE[]
 [source,java]
 ----
 include::{javaSourceFile}[tag=merge,indent=0]
 ----
 --

 When using `SqlFieldsQuery` to execute DDL statements, you must call `getAll()` on the cursor returned from the `query(...)` method.

 == Specifying the Schema

 By default, any SELECT statement executed via `SqlFieldsQuery` is resolved against the PUBLIC schema. However, if the table you want to query is in a different schema, you can specify the schema by calling `SqlFieldsQuery.setSchema(...)`. In this case, the statement is executed in the given schema.

 [tabs]
 --
 tab:Java[]
 [source,java]
 ----
 include::{javaSourceFile}[tag=set-schema,indent=0]
 ----

 tab:C#/.NET[]
 [source,csharp]
 ----
 include::code-snippets/dotnet/UsingSqlApi.cs[tag=schema,indent=0]
 ----

 tab:C++[]
 [source,cpp]
 ----
 include::code-snippets/cpp/src/sql.cpp[tag=sql-fields-query-scheme,indent=0]
 ----
 --

 Alternatively, you can define the schema in the statement:

 [source,java]
 ----
 SqlFieldsQuery sql = new SqlFieldsQuery("select name from Person.City");
 ----

 == Creating Tables

 You can pass any supported DDL statement to `SqlFieldsQuery` and execute it on a cache as shown below.

 [tabs]
 --
 tab:Java[]
 [source,java]
 ----
 include::{javaSourceFile}[tag=create-table,indent=0]
 ----

 tab:C#/.NET[]
 [source,csharp]
 ----
 include::code-snippets/dotnet/UsingSqlApi.cs[tag=creatingTables,indent=0]
 ----

 tab:C++[]
 [source,cpp]
 ----
 include::code-snippets/cpp/src/sql_create.cpp[tag=sql-create,indent=0]
 ----
 --


 In terms of SQL schema, the following tables are created as a result of executing the code:

 * Table "Person" in the "Person" schema (if it hasn't been created before).
 * Table "City" in the "Person" schema.

 To query the "City" table, use statements like `select * from Person.City` or `new SqlFieldsQuery("select * from City").setSchema("PERSON")` (note the uppercase).


 ////////////////////////////////////////////////////////////////////////////////
 == Joining Tables


 == Cross-Table Queries


 `SqlQuery.setSchema("PUBLIC")`

 ++++
 <code-tabs>
 <code-tab data-tab="Java">
 ++++
 [source,java]
 ----
 IgniteCache cache = ignite.getOrCreateCache(
     new CacheConfiguration<>()
         .setName("Person")
         .setIndexedTypes(Long.class, Person.class));

 // Creating City table.
 cache.query(new SqlFieldsQuery("CREATE TABLE City " +
     "(id int primary key, name varchar, region varchar)").setSchema("PUBLIC")).getAll();

 // Creating Organization table.
 cache.query(new SqlFieldsQuery("CREATE TABLE Organization " +
     "(id int primary key, name varchar, cityName varchar)").setSchema("PUBLIC")).getAll();

 // Joining data between City, Organizaion and Person tables. The latter
 // was created with either annotations or QueryEntity approach.
 SqlFieldsQuery qry = new SqlFieldsQuery("SELECT o.name from Organization o " +
     "inner join \"Person\".Person p on o.id = p.orgId " +
     "inner join City c on c.name = o.cityName " +
     "where p.age > 25 and c.region <> 'Texas'");

 // Set the query's default schema to PUBLIC.
 // Table names from the query without the schema set will be
 // resolved against PUBLIC schema.
 // Person table belongs to "Person" schema (person cache) and this is why
 // that schema name is set explicitly.
 qry.setSchema("PUBLIC");

 // Executing the query.
 cache.query(qry).getAll();
 ----
 ++++
 </code-tab>
 <code-tab data-tab="C#/.NET">
 ++++
 [source,csharp]
 ----

 ----
 ++++
 </code-tab>
 <code-tab data-tab="C++">
 ++++
 [source,cpp]
 ----
 TODO
 ----
 ++++
 </code-tab>
 </code-tabs>
 ++++


 ////////////////////////////////////////////////////////////////////////////////

 == Cancelling Queries
 There are two ways to cancel long running queries.

 The first approach is to prevent run away queries by setting a query execution timeout.

 [tabs]
 --
 tab:Java[]
 [source,java]
 ----
 include::{javaSourceFile}[tag=set-timeout,indent=0]
 ----
 tab:C#/.NET[]
 [source,csharp]
 ----
 include::code-snippets/dotnet/UsingSqlApi.cs[tag=qryTimeout,indent=0]
 ----
 tab:C++[unsupported]
 --

 The second approach is to halt the query by using `QueryCursor.close()`.

 [tabs]
 --
 tab:Java[]
 [source,java]
 ----
 include::{javaSourceFile}[tag=cancel-by-closing,indent=0]
 ----
 tab:C#/.NET[]
 [source,csharp]
 ----
 include::code-snippets/dotnet/UsingSqlApi.cs[tag=cursorDispose,indent=0]
 ----
 tab:C++[unsupported]
 --

 == Example

 The Ignite distribution package includes a ready-to-run `SqlDmlExample` as a part of its link:{githubUrl}/examples/src/main/java/org/apache/ignite/examples/sql/SqlDmlExample.java[source code, window=_blank]. This example demonstrates the usage of all the above-mentioned DML operations.
	// 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 KIND, either express or implied.
	// See the License for the specific language governing permissions and
	// limitations under the License.
	= SQL API
	:javaSourceFile: {javaCodeDir}/SqlAPI.java

	In addition to using the JDBC driver, Java developers can use Ignite's SQL APIs to query and modify data stored in Ignite.

	The `SqlFieldsQuery` class is an interface for executing SQL statements and navigating through the results. `SqlFieldsQuery` is executed through the `IgniteCache.query(SqlFieldsQuery)` method, which returns a query cursor.

	== Configuring Queryable Fields

	If you want to query a cache using SQL statements, you need to define which fields of the value objects are queryable. Queryable fields are the fields of your data model that the SQL engine can "see" and query.

	NOTE: If you create tables using JDBC or SQL tools, you do not need to define queryable fields.

	[NOTE]
	====
	Indexing capabilities are provided by the 'ignite-indexing' module. If you start Ignite from java code, link:setup#enabling-modules[add this module to the classpath of your application].
	====

	In Java, queryable fields can be configured in two ways:

	* using annotations
	* by defining query entities


	=== @QuerySqlField Annotation

	To make specific fields queryable, annotate the fields in the value class definition with the `@QuerySqlField` annotation and call `CacheConfiguration.setIndexedTypes(...)`.
	////
	TODO : CacheConfiguration.setIndexedTypes is presented only in java, C# got different API, rewrite sentence above
	////


	[tabs]
	--
	tab:Java[]

	[source,java]
	----
	include::{javaCodeDir}/QueryEntitiesExampleWithAnnotation.java[tags=query-entity-annotation, indent=0]
	----

	Make sure to call `CacheConfiguration.setIndexedTypes(...)` to let the SQL engine know about the annotated fields.

	tab:C#/.NET[]
	[source,csharp]
	----
	include::code-snippets/dotnet/UsingSqlApi.cs[tag=sqlQueryFields,indent=0]
	----
	tab:C++[unsupported]
	--

	=== Query Entities

	You can define queryable fields using the `QueryEntity` class. Query entities can be configured via XML configuration.


	[tabs]
	--
	tab:XML[]
	[source,xml]
	----
	include::code-snippets/xml/query-entities.xml[tags=ignite-config,indent=0]
	----
	tab:Java[]
	[source,java]
	----
	include::{javaCodeDir}/QueryEntityExample.java[tags=query-entity,indent=0]
	----
	tab:C#/.NET[]
	[source,csharp]
	----
	include::code-snippets/dotnet/UsingSqlApi.cs[tag=queryEntities,indent=0]
	----
	tab:C++[unsupported]
	--

	== Querying

	To execute a select query on a cache, simply create an object of `SqlFieldsQuery` providing the query string to the constructor and run `cache.query(...)`.
	Note that in the following example, the Person cache must be configured to be <<Configuring Queryable Fields,visible to the SQL engine>>.


	[tabs]
	--
	tab:Java[]
	[source,java]
	----
	include::{javaSourceFile}[tag=simple-query,indent=0]
	----

	tab:C#/.NET[]
	[source,csharp]
	----
	include::code-snippets/dotnet/UsingSqlApi.cs[tag=querying,indent=0]
	----
	tab:C++[]
	[source,cpp]
	----
	include::code-snippets/cpp/src/sql.cpp[tag=sql-fields-query,indent=0]
	----
	--

	`SqlFieldsQuery` returns a cursor that iterates through the results that match the SQL query.

	=== Local Execution

	To force local execution of a query, use `SqlFieldsQuery.setLocal(true)`. In this case, the query is executed against the data stored on the node where the query is run. It means that the results of the query are almost always incomplete. Use the local mode only if you are confident you understand this limitation.

	=== Subqueries in WHERE Clause

	`SELECT` queries used in `INSERT` and `MERGE` statements as well as `SELECT` queries generated by `UPDATE` and `DELETE` operations are distributed and executed in either link:SQL/distributed-joins[colocated or non-colocated distributed modes].

	However, if there is a subquery that is executed as part of a `WHERE` clause, then it can be executed in the colocated mode only.

	For instance, let's consider the following query:

	[source,sql]
	----
	DELETE FROM Person WHERE id IN
	(SELECT personId FROM Salary s WHERE s.amount > 2000);
	----
	The SQL engine generates the `SELECT` query in order to get a list of entries to be deleted. The query is distributed and executed across the cluster and looks like the one below:
	[source,sql]
	----
	SELECT _key, _val FROM Person WHERE id IN
	(SELECT personId FROM Salary s WHERE s.amount > 2000);
	----
	However, the subquery from the `IN` clause (`SELECT personId FROM Salary ...`) is not distributed further and is executed over the local data set available on the node.

	== Inserting, Updating, Deleting, and Merging

	With `SqlFieldsQuery` you can execute the other DML commands in order to modify the data:


	[tabs]
	--
	tab:INSERT[]
	[source,java]
	----
	include::{javaSourceFile}[tag=insert,indent=0]
	----

	tab:UPDATE[]
	[source,java]
	----
	include::{javaSourceFile}[tag=update,indent=0]
	----

	tab:DELETE[]
	[source,java]
	----
	include::{javaSourceFile}[tag=delete,indent=0]
	----

	tab:MERGE[]
	[source,java]
	----
	include::{javaSourceFile}[tag=merge,indent=0]
	----
	--

	When using `SqlFieldsQuery` to execute DDL statements, you must call `getAll()` on the cursor returned from the `query(...)` method.

	== Specifying the Schema

	By default, any SELECT statement executed via `SqlFieldsQuery` is resolved against the PUBLIC schema. However, if the table you want to query is in a different schema, you can specify the schema by calling `SqlFieldsQuery.setSchema(...)`. In this case, the statement is executed in the given schema.

	[tabs]
	--
	tab:Java[]
	[source,java]
	----
	include::{javaSourceFile}[tag=set-schema,indent=0]
	----

	tab:C#/.NET[]
	[source,csharp]
	----
	include::code-snippets/dotnet/UsingSqlApi.cs[tag=schema,indent=0]
	----

	tab:C++[]
	[source,cpp]
	----
	include::code-snippets/cpp/src/sql.cpp[tag=sql-fields-query-scheme,indent=0]
	----
	--

	Alternatively, you can define the schema in the statement:

	[source,java]
	----
	SqlFieldsQuery sql = new SqlFieldsQuery("select name from Person.City");
	----

	== Creating Tables

	You can pass any supported DDL statement to `SqlFieldsQuery` and execute it on a cache as shown below.

	[tabs]
	--
	tab:Java[]
	[source,java]
	----
	include::{javaSourceFile}[tag=create-table,indent=0]
	----

	tab:C#/.NET[]
	[source,csharp]
	----
	include::code-snippets/dotnet/UsingSqlApi.cs[tag=creatingTables,indent=0]
	----

	tab:C++[]
	[source,cpp]
	----
	include::code-snippets/cpp/src/sql_create.cpp[tag=sql-create,indent=0]
	----
	--


	In terms of SQL schema, the following tables are created as a result of executing the code:

	* Table "Person" in the "Person" schema (if it hasn't been created before).
	* Table "City" in the "Person" schema.

	To query the "City" table, use statements like `select * from Person.City` or `new SqlFieldsQuery("select * from City").setSchema("PERSON")` (note the uppercase).


	////////////////////////////////////////////////////////////////////////////////
	== Joining Tables


	== Cross-Table Queries


	`SqlQuery.setSchema("PUBLIC")`

	++++
	<code-tabs>
	<code-tab data-tab="Java">
	++++
	[source,java]
	----
	IgniteCache cache = ignite.getOrCreateCache(
	new CacheConfiguration<>()
	.setName("Person")
	.setIndexedTypes(Long.class, Person.class));

	// Creating City table.
	cache.query(new SqlFieldsQuery("CREATE TABLE City " +
	"(id int primary key, name varchar, region varchar)").setSchema("PUBLIC")).getAll();

	// Creating Organization table.
	cache.query(new SqlFieldsQuery("CREATE TABLE Organization " +
	"(id int primary key, name varchar, cityName varchar)").setSchema("PUBLIC")).getAll();

	// Joining data between City, Organizaion and Person tables. The latter
	// was created with either annotations or QueryEntity approach.
	SqlFieldsQuery qry = new SqlFieldsQuery("SELECT o.name from Organization o " +
	"inner join \"Person\".Person p on o.id = p.orgId " +
	"inner join City c on c.name = o.cityName " +
	"where p.age > 25 and c.region <> 'Texas'");

	// Set the query's default schema to PUBLIC.
	// Table names from the query without the schema set will be
	// resolved against PUBLIC schema.
	// Person table belongs to "Person" schema (person cache) and this is why
	// that schema name is set explicitly.
	qry.setSchema("PUBLIC");

	// Executing the query.
	cache.query(qry).getAll();
	----
	++++
	</code-tab>
	<code-tab data-tab="C#/.NET">
	++++
	[source,csharp]
	----

	----
	++++
	</code-tab>
	<code-tab data-tab="C++">
	++++
	[source,cpp]
	----
	TODO
	----
	++++
	</code-tab>
	</code-tabs>
	++++


	////////////////////////////////////////////////////////////////////////////////

	== Cancelling Queries
	There are two ways to cancel long running queries.

	The first approach is to prevent run away queries by setting a query execution timeout.

	[tabs]
	--
	tab:Java[]
	[source,java]
	----
	include::{javaSourceFile}[tag=set-timeout,indent=0]
	----
	tab:C#/.NET[]
	[source,csharp]
	----
	include::code-snippets/dotnet/UsingSqlApi.cs[tag=qryTimeout,indent=0]
	----
	tab:C++[unsupported]
	--

	The second approach is to halt the query by using `QueryCursor.close()`.

	[tabs]
	--
	tab:Java[]
	[source,java]
	----
	include::{javaSourceFile}[tag=cancel-by-closing,indent=0]
	----
	tab:C#/.NET[]
	[source,csharp]
	----
	include::code-snippets/dotnet/UsingSqlApi.cs[tag=cursorDispose,indent=0]
	----
	tab:C++[unsupported]
	--

	== Example

	The Ignite distribution package includes a ready-to-run `SqlDmlExample` as a part of its link:{githubUrl}/examples/src/main/java/org/apache/ignite/examples/sql/SqlDmlExample.java[source code, window=_blank]. This example demonstrates the usage of all the above-mentioned DML operations.