You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by mm...@apache.org on 2022/06/01 07:32:48 UTC
[ignite] branch master updated: IGNITE-17020 Update documentation for the CREATE TABLE clause with additional examples (#10040)
This is an automated email from the ASF dual-hosted git repository.
mmuzaf pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/master by this push:
new a8eaa92b54b IGNITE-17020 Update documentation for the CREATE TABLE clause with additional examples (#10040)
a8eaa92b54b is described below
commit a8eaa92b54b55473428a63bcf52219797fe01482
Author: Maxim Muzafarov <ma...@gmail.com>
AuthorDate: Wed Jun 1 10:32:40 2022 +0300
IGNITE-17020 Update documentation for the CREATE TABLE clause with additional examples (#10040)
* IGNITE-17020 Update documentation for the CREATE TABLE clause with additional examples
* IGNITE-17020 Fix review comments
* IGNITE-17020 Fix review comments 2
* IGNITE-17020 Fix review comments 3
* IGNITE-17020 Fix review comments
* IGNITE-17020 Fix review comments 2
---
docs/_docs/sql-reference/ddl.adoc | 109 +++++++++++++++++++++++++++++---------
1 file changed, 84 insertions(+), 25 deletions(-)
diff --git a/docs/_docs/sql-reference/ddl.adoc b/docs/_docs/sql-reference/ddl.adoc
index 5e91773a71a..332f93a0620 100644
--- a/docs/_docs/sql-reference/ddl.adoc
+++ b/docs/_docs/sql-reference/ddl.adoc
@@ -12,6 +12,7 @@
// 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.
+
= Data Definition Language (DDL)
:toclevels:
@@ -20,7 +21,14 @@ This page encompasses all data definition language (DDL) commands supported by I
== CREATE TABLE
-Create a new table and an underlying cache.
+The command creates a new Ignite cache and defines a SQL table on top of it. The underlying cache stores the data in
+the form of key-value pairs while the table allows processing the data with SQL queries.
+
+The table will reside in the link:SQL/schemas[Schema] specified in the connection parameters. If no schema is specified,
+the `PUBLIC` will be used as a default.
+
+The `CREATE TABLE` command is synchronous. Moreover, it blocks the execution of other DDL commands that are issued before the
+`CREATE TABLE` command has finished execution. The execution of DML commands is not affected and can be performed in parallel.
[source,sql]
----
@@ -49,7 +57,8 @@ Parameters:
sets the write synchronization mode for the underlying cache. If neither this nor the `TEMPLATE` parameter is set, then the cache is created with `FULL_SYNC` mode enabled.
** `CACHE_GROUP=<group name>` - specifies the link:configuring-caches/cache-groups[group name] the underlying cache belongs to.
** `AFFINITY_KEY=<affinity key column name>` - specifies an link:data-modeling/affinity-collocation[affinity key] name which is a column of the `PRIMARY KEY` constraint.
-** `CACHE_NAME=<custom name of the new cache>` - the name of the underlying cache created by the command.
+** `CACHE_NAME=<custom name of the new cache>` - the name of the underlying cache created by the command,
+or the `SQL_{SCHEMA_NAME}_{TABLE}` format will be used if the parameter not specified.
** `DATA_REGION=<existing data region name>` - name of the link:memory-configuration/data-regions[data region] where table entries should be stored. By default, Ignite stores all the data in a default region.
** `KEY_TYPE=<custom name of the key type>` - sets the name of the custom key type that is used from the key-value APIs in Ignite. The name should correspond to a Java, .NET, or C++ class, or it can be a random one if link:data-modeling/data-modeling#binary-object-format[BinaryObjects] is used instead of a custom class. The number of fields and their types in the custom key type has to correspond to the `PRIMARY KEY`. Refer to the <<Description>> section below for more details.
** `VALUE_TYPE=<custom name of the value type of the new cache>` - sets the name of a custom value type that is used from the key-value and other non-SQL APIs in Ignite. The name should correspond to a Java, .NET, or C++ class, or it can be a random one if
@@ -57,23 +66,43 @@ link:data-modeling/data-modeling#binary-object-format[BinaryObjects] is used ins
** `WRAP_KEY=<true | false>` - this flag controls whether a _single column_ `PRIMARY KEY` should be wrapped in the link:data-modeling/data-modeling#binary-object-format[BinaryObjects] format or not. By default, this flag is set to false. This flag does not have any effect on the `PRIMARY KEY` with multiple columns; it always gets wrapped regardless of the value of the parameter.
** `WRAP_VALUE=<true | false>` - this flag controls whether a single column value of a primitive type should be wrapped in the link:data-modeling/data-modeling#binary-object-format[BinaryObjects] format or not. By default, this flag is set to true. This flag does not have any effect on the value with multiple columns; it always gets wrapped regardless of the value of the parameter. Set this parameter to false if you have a single column value and do not plan to add additional columns to [...]
-The CREATE TABLE command creates a new Ignite cache and defines a SQL table on top of it. The cache stores the data in the form of key-value pairs while the table allows processing the data with SQL queries.
-The table will reside in the schema specified in the connection parameters. If no schema is specified, the PUBLIC schema will be used. See link:SQL/schemas[Schemas] for more information about schemas in Ignite.
+Read more about the database architecture on the link:SQL/sql-introduction[SQL Introduction] page.
-Note that the CREATE TABLE operation is synchronous and blocks the execution of other DDL commands that are issued while CREATE TABLE is still in progress. The execution of DML commands is not affected and can be performed in parallel.
-If you wish to access the data using the key-value APIs, then setting the `CACHE_NAME`, `KEY_TYPE`, and `VALUE_TYPE` parameters may be useful for the following reasons:
+=== Define Primary Key
-- When the CREATE TABLE command is executed, the name of the cache is generated with the following format- `SQL_{SCHEMA_NAME}_{TABLE}`. Use the CACHE_NAME parameter to override the default name.
-- Additionally, the command creates two new binary types - for the key and value respectively. Ignite generates the names of the types randomly including a UUID string. This complicates the usage of these 'types' from a non-SQL API. Use KEY_TYPE and VALUE_TYPE to override the names with custom ones corresponding to your business model objects.
+The example below shows how to create a table with `PRIMARY KEY` specified in the column definition and override cache
+related parameters. A new distributed cache `SQL_PUBLIC_PERSON` will be created (the `SQL_{SCHEMA_NAME}_{TABLE}` format
+is used for naming) which stores objects of the `Person` type that corresponds to a specific Java, .NET, C++ class or BinaryObject.
-Read more about the database architecture on the link:SQL/sql-introduction[SQL Introduction] page.
+The distributed cache related parameters are passed in the `WITH` clause of the statement. If the `WITH` clause is omitted,
+then the cache will be created with default parameters set in the `CacheConfiguration` object.
+
+[source,sql]
+----
+CREATE TABLE Person (
+ id int PRIMARY KEY,
+ city_id int,
+ name varchar,
+ age int,
+ company varchar
+) WITH "atomicity=transactional,cachegroup=somegroup";
+----
-Examples:
+=== Use non-SQL API
+
+If you wish to access the table data by the key-value or other non-SQL API, then you might be need to set the `CACHE_NAME` and
+`KEY_TYPE`, `VALUE_TYPE` parameters corresponding to your business model objects to make non-SQL APIs usage more convenient.
-Create Person table:
+- Use the `CACHE_NAME` parameter to override the default name with the following format `SQL_{SCHEMA_NAME}_{TABLE}`.
+- By default, the command also creates two new binary types - for the key and value respectively. Ignite in turn generates
+the names of the types randomly including a UUID string which complicates the usage of these types from a non-SQL API.
+
+The example below shows how to create a table `PERSON` and the underlying cache with the same name. The cache will store objects
+of the `Person` type with explicitly defined the key type `PersonKey` and value type `PersonValue`. The `PRIMARY KEY` columns will
+be used as the object's key, the rest of the columns will belong to the value.
[source,sql]
----
@@ -84,28 +113,58 @@ CREATE TABLE IF NOT EXISTS Person (
age int,
company varchar,
PRIMARY KEY (id, city_id)
-) WITH "template=partitioned,backups=1,affinity_key=city_id, key_type=PersonKey, value_type=MyPerson";
+) WITH "template=partitioned,backups=1,affinity_key=city_id,CACHE_NAME=Person,KEY_TYPE=PersonKey,VALUE_TYPE=PersonValue";
----
-Once the CREATE TABLE command gets executed, the following happens:
-- A new distributed cache is created and named SQL_PUBLIC_PERSON. This cache stores objects of the `Person` type that corresponds to a specific Java, .NET, C++ class or BinaryObject. Furthermore, the key type (`PersonKey`) and value type (`MyPerson`) are defined explicitly assuming the data is to be processed by key-value and other non-SQL APIs.
-- SQL table/schema with all the parameters will be defined.
-- The data will be stored in the form of key-value pairs. The `PRIMARY KEY` columns will be used as the object's key; the rest of the columns will belong to the value.
-- Distributed cache related parameters are passed in the `WITH` clause of the statement. If the `WITH` clause is omitted, then the cache will be created with default parameters set in the CacheConfiguration object.
+=== Use non-Upper Case Columns
+
+Ignite parses all unquoted identifiers, names of a table columns and converts them to uppercase
+during the `CREATE TABLE` command execution which, in turn, makes the command with explicitly defined key
+and value types a bit more challenging.
+
+There are a few options that might help you to deal with such a case:
-The example below shows how to create the same table with `PRIMARY KEY` specified in the column definition and overrid some cache related parameters:
+* Use link:SQL/sql-api[QuerySqlField] annotation. This will prevent checking the field non-UpperCase each time because of
+an alias for the column is created each time the `CREATE TABLE` command being executed.
+* Keeping in mind that column names converted each time to the upper case by default, you have to be sure that DDL fields
+and cache type fields are always match the letters case.
+
+In the example below you can see the usage of quotes for the `affKey` CamelCase field in the `CREATE TABLE` command with
+matching of the same field in the `PersonKey` cache key type.
[source,sql]
----
-CREATE TABLE Person (
- id int PRIMARY KEY,
- city_id int,
- name varchar,
- age int,
- company varchar
-) WITH "atomicity=transactional,cachegroup=somegroup";
+CREATE TABLE IF NOT EXISTS Person (
+ id INT,
+ "affKey" INT,
+ val VARCHAR,
+ PRIMARY KEY (id, "affKey")
+) WITH "template=partitioned,backups=1,affinity_key=affKey,CACHE_NAME=Person,KEY_TYPE=PersonKey,VALUE_TYPE=PersonValue";
+----
+
+[source,java]
----
+class PersonKey {
+ private int id;
+
+ /*
+ * This is a camel case field 'affKey' must match the DDL table schema, so you must be sure:
+ * - Using the quoted "affKey" field name in the DDL table definition;
+ * - Convert the 'affKey' field to the upper case 'AFFKEY' to match the DDL table definition;
+ */
+ @AffinityKeyMapped
+ private int affKey;
+
+ public PersonKey(int id, int affKey) {
+ this.id = id;
+ this.affKey = affKey;
+ }
+}
+----
+
+Note that some integrations with the Apache Ignite like the link:extensions-and-integrations/spring/spring-data[Spring Data]
+`CrudRepository` doesn't support the quoted fields to access the data.
== ALTER TABLE