GridDB Advanced Edition SQL reference

Create a table.

Syntax

-- Table (Collection)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] );

-- Timeseries table (Timeseries container)
CREATE TABLE [IF NOT EXISTS] table_name ( column_name TIMESTAMP PRIMARY KEY [, column definition ...] )
USING TIMESERIES [WITH (property_key=property_value [, property_key=property_value ...])];

column definition

• column_name data_type [ column_constraint ]

column_constraint

• PRIMARY KEY (only the 1st column can be specified)
• NULL
• NOT NULL

Specifications

• See Naming rules for the rules of table name and column name.
• If “IF NOT EXISTS” is specified, the specified table can be created only if another table with the same name does not exist.
• The column name and data type name need to be specified in column definition. See Data types used in data storage for the data types that can be specified.
• See “GridDB technical reference” (GridDB_TechnicalReference.html) for details of timeseries table.
• For time series tables, options about expiry release can be specified by the format " WITH (property_key=property_value, ...)".

  Function	Item	Property key	Property value
  Expiry release function	Type	expiration_type	STRING
  			(Any of the followings.
  			ROW: Row expiry release)
  	Elapsed time	expiration_time	INTEGER
  	Elapsed time unit	expiration_time_unit	STRING
  			(Any of the followings.
  			DAY / HOUR / MINUTE / SECOND / MILLISECOND )
  	Division count	expiration_division_count	INTEGER

  • See “GridDB technical reference” (GridDB_TechnicalReference.html) for details of each item.

Examples

• Creating table

  CREATE TABLE IF NOT EXISTS myTable (
    key INTEGER PRIMARY KEY,
    value1 DOUBLE NOT NULL,
    value2 DOUBLE NOT NULL
  );
  

• Creating timeseries table using the row expiry release function

  CREATE TABLE IF NOT EXISTS myTimeseries (
    mycolumn1 TIMESTAMP PRIMARY KEY,
    mycolumn2 STRING
  ) USING TIMESERIES WITH (
    expiration_type='ROW',
    expiration_time=10,
    expiration_time_unit='DAY'
  );
  

Create a partitioned table.

See “GridDB technical reference” (GridDB_TechnicalReference.html) for details of each partitioning function.

(1) Creating a hash partitioned table

Syntax

-- Table (collection)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] )
PARTITION BY HASH (column_name_of_partitioning_key) PARTITIONS division_count;

-- Timeseries table (timeseries container)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] )
USING TIMESERIES [WITH (property_key=property_value, ...)]]
PARTITION BY HASH (column_name_of_partitioning_key) PARTITIONS division_count;

Specifications

• Create a hash partitioned table by the column name of partitioning key and the value of division count.
• Specify the value from 1 to 1024 for “division_count”.
• The column specified as partitioning key cannot be updated.
• For time series tables, options about expiry release can be specified by the format " WITH (property_key=property_value, ...)". The options that can be specified are same as normal table.

Examples

• Creating a hash partitioned table

  CREATE TABLE IF NOT EXISTS myHashPartition (
    id INTEGER PRIMARY KEY,
    value STRING
  ) PARTITION BY HASH (id) PARTITIONS 128;
  

(2) Creating an interval partitioned table

Syntax

-- Table (collection)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] )
[WITH (property_key=property_value, ...)]
PARTITION BY RANGE(column_name_of_partitioning_key) EVERY(interval_value [, interval_unit ]);

-- Timeseries table (timeseries container)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] )
USING TIMESERIES [WITH (property_key=property_value, ...)]
PARTITION BY RANGE(column_name_of_partitioning_key) EVERY(interval_value [, interval_unit ]);

Specifications

• Specify the column which type is BYTE, SHORT, INTEGER, LONG or TIMESTAMP for “column_name_of_partitioning_key”.
• The column specified for “column_name_of_partitioning_key” is required to be “PRIMARY KEY” or have “NOT NULL” constraint.
• The column specified as partitioning key cannot be updated.
• The following values can be specified as the “interval_value”.

  Partitioning key type	Possible interval value
  BYTE	1 to 127
  SHORT	1 to 32767
  INTEGER	1 to 2147483647
  LONG	1000 to 9223372036854775807
  TIMESTAMP	from 1

  • If the column of TIMESTAMP is specified, it is also required to specify the interval unit. DAY is the only value that can be specified as the interval unit.
  • The interval unit cannot be specified for any types other than TIMESTAMP.
• The options about expiry release can be specified by the format " WITH (property_key=property_value, ...)".

  Function	Item	Property key	Property value
  Expiry release function	Type	expiration_type	STRING
  			(Any of the followings. If omitted, PARTITION.
  			ROW: Row expiry release
  			PARTITION: Partition expiry release)
  	Elapsed time	expiration_time	INTEGER
  	Elapsed time unit	expiration_time_unit	STRING
  			(Any of the followings.
  			DAY / HOUR / MINUTE / SECOND / MILLISECOND )
  	Division count	expiration_division_count	INTEGER
  			(Only specified for row expiry release)

  • The row expiry release can only be specified for timeseries table (timeseries container).
  • The partition expiry release can only be specified for followings:
    • Timeseries table (timeseries container)
    • Table (collection) whose partitioning key is TIMESTAMP type.
  • See “GridDB technical reference” (GridDB_TechnicalReference.html) for details of each item.

Examples

• Creating an interval partitioned table.

  CREATE TABLE IF NOT EXISTS myIntervalPartition (
    date TIMESTAMP PRIMARY KEY,
    value STRING
  ) PARTITION BY RANGE (date) EVERY (30, DAY);
  

• Creating an interval partitioned table (timeseries table) using the partition expiry release function.

  CREATE TABLE IF NOT EXISTS myIntervalPartition2 (
    date TIMESTAMP PRIMARY KEY,
    value STRING
  ) USING TIMESERIES WITH (
    expiration_type='PARTITION',
    expiration_time=90,
    expiration_time_unit='DAY'
  ) PARTITION BY RANGE (date) EVERY (30, DAY);
  

(3) Creating an interval-hash partitioned table

Syntax

-- Table (collection)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] )
[WITH (property_key=property_value, ...) ]
PARTITION BY RANGE(column_name_of_interval_partitioning_key) EVERY(interval_value [, interval_unit ])
SUBPARTITION BY HASH(column_name_of_hash_partitioning_key) SUBPARTITIONS division_count;

-- Timeseries table (timeseries container)
CREATE TABLE [IF NOT EXISTS] table_name ( column definition [, column definition ...] )
USING TIMESERIES [WITH (property_key=property_value, ...)]
PARTITION BY RANGE(column_name_of_interval_partitioning_key) EVERY(interval_value [, interval_unit ])
SUBPARTITION BY HASH(column_name_of_hash_partitioning_key) SUBPARTITIONS division_count;

Specifications

• Specify the column which type is BYTE, SHORT, INTEGER, LONG or TIMESTAMP for “column_name_of_interval_partitioning_key”.
• The column specified for “column_name_of_interval_partitioning_key” is required to be “PRIMARY KEY” or have “NOT NULL” constraint.
• The following values can be specified as the “interval_value“.

  Partitioning key type	Possible interval value
  BYTE	1 to 127
  SHORT	1 to 32767
  INTEGER	1 to 2147483647
  LONG	1000 * division_count to 9223372036854775807
  TIMESTAMP	from 1

  • If the column of TIMESTAMP is specified, it is also required to specify the interval unit. DAY is the only value that can be specified as the interval unit.
  • The interval unit cannot be specified for any types other than TIMESTAMP.
• Specify the value from 1 to 1024 for “division_count”.
• The column specified as partitioning key cannot be updated.
• The options about expiry release can be specified by the format " WITH (property_key=property_value, ...)". The options that can be specified are same as interval partitioned table.

Examples

• Creating an interval-hash partitioned table

  CREATE TABLE IF NOT EXISTS myIntervalHashPartition (
    date TIMESTAMP PRIMARY KEY,
    value STRING
  ) PARTITION BY RANGE (date) EVERY (60, DAY)
  SUBPARTITION BY HASH (value) SUBPARTITIONS 64;
  

• Creating an interval-hash partitioned table (timeseries table) using the partition expiry release function.

  CREATE TABLE IF NOT EXISTS myIntervalHashPartition2 (
    date TIMESTAMP PRIMARY KEY,
    value STRING
  ) USING TIMESERIES WITH (
    expiration_type='PARTITION',
    expiration_time=90,
    expiration_time_unit='DAY'
  ) PARTITION BY RANGE (date) EVERY (60, DAY)
  SUBPARTITION BY HASH (value) SUBPARTITIONS 64;
  

Add columns to the end of the table.

Syntax

ALTER TABLE table_name ADD [COLUMN] column definition [,ADD [COLUMN] column definition ...];

column definition

• column_name data_type [ column_constraint ]

column_constraint

• NULL
• NOT NULL

Specifications

• The added column is located in the end of the table. If multiple columns are specified, they are located in their order.
• "PRIMARY KEY" can not be specified to the column constraint.
• If the same name column exists, an error occurs.

Examples

• Adding multiple columns to the table

  ALTER TABLE myTable1
    ADD COLUMN col111 STRING NOT NULL,
    ADD COLUMN col112 INTEGER;
  

Delete data partitions created by table partitioning.

Syntax

ALTER TABLE table_name DROP PARTITION FOR ( value_included_in_the_data_partition );

Specifications

• Data partitions can be deleted only for interval and interval-hash partitioning.
• Specify the value included in the data partition to be deleted.
• Data in the range of the once deleted data partition (from the lower limit value to the upper limit value of the data partition) cannot be registered.
• The lower limit value of a data partition can be checked by metatable. The upper limit value is obtained by adding the interval value to the lower limit value.
• For interval-hash partitioned tables, there are multiple data partitions which have the same lower limit value, and the maximum number of those partitions is equal to the hash division count. Those data partitions are deleted simultaneously. Deleted partitions are checked by metatable.

See Metatables for the details on the metatable.

Examples

Interval partitioned table

• Check the lower limit value of the interval partitioned table “myIntervalPartition1” (partitioning key type: TIMESTAMP, interval: 30 DAY)

  SELECT PARTITION_BOUNDARY_VALUE FROM "#table_partitions"
  WHERE TABLE_NAME='myIntervalPartition1' ORDER BY PARTITION_BOUNDARY_VALUE;
  
  PARTITION_BOUNDARY_VALUE
  -----------------------------------
   2017-01-10T13:00:00Z
   2017-02-09T13:00:00Z
   2017-03-11T13:00:00Z
         :
  

• Delete unnecessary data partitions

  ALTER TABLE myIntervalPartition1 DROP PARTITION FOR ('2017-01-10T13:00:00Z');
  

Interval-hash partitioned table

• Check the lower limit value of each data partitions on the interval hash partitioned table “myIntervalHashPartition” (partitioning key type: TIMESTAMP, interval value: 90 DAY, division count 3)

  SELECT PARTITION_BOUNDARY_VALUE FROM "#table_partitions"
  WHERE TABLE_NAME='myIntervalHashPartition' ORDER BY PARTITION_BOUNDARY_VALUE;
  
  PARTITION_BOUNDARY_VALUE 
  -----------------------------------
  2016-08-01T10:00:00Z    # There are 3 data partitions that are
  2016-08-01T10:00:00Z    # divided by same boundary value.
  2016-08-01T10:00:00Z    #
  2016-10-30T10:00:00Z
  2016-10-30T10:00:00Z
  2016-10-30T10:00:00Z
  2017-01-29T10:00:00Z
         :
  

• Delete unnecessary data partitions

  ALTER TABLE myIntervalHashPartition DROP PARTITION FOR ('2016-09-15T10:00:00Z');
  

• Data partitions that have same boundary value will be deleted

  SELECT PARTITION_BOUNDARY_VALUE FROM "#table_partitions"
  WHERE TABLE_NAME='myIntervalHashPartition' ORDER BY PARTITION_BOUNDARY_VALUE;
  
  PARTITION_BOUNDARY_VALUE 
  -----------------------------------
  2016-10-30T10:00:00Z    # The 3 data partitions that in the interval (boundary value is '2016-08-01T10:00:00Z')
  2016-10-30T10:00:00Z    # includes '2016-09-15T10:00:00Z' have been deleted.
  2016-10-30T10:00:00Z
  2017-01-29T10:00:00Z
         :
  

Control parallelization processing.

• MaxDegreeOfParallelism(upper_limit)
  • Specify the maximum number of processing threads for one query when processing SQL in a node.
• MaxDegreeOfTaskInput(upper_limit)
  • Specify the maximum number of inputs for one task. It applies to the following processing:
    • UNION ALL processing when scanning the partitioned table
• MaxDegreeOfExpansion(upper_limit)
  • UNION ALL processing when scanning the partitioned table It applies to the following processing:
    • Push down join optimization processing

Specify distributed planning policy.

• DistributedPolicy(policy)
  • Following distributed planning policies are available:
    • 'LOCAL_ONLY'
      • The planner uses only the local information of the connected node without distributing. An error occurs if no table found in local.
    • 'LOCAL_PREFER'
      • The planner gives priority to the local information of the connected node. A remote plan is generated if no table found in local.

Specify scanning method.

• IndexScan(table)
  • Index scan is used if possible. If it cannot be used, nothing is done.
• NoIndexScan(table)
  • No index scan is used.

Specify which joining method to select for a table combination.

• IndexJoin(table table)
  • Index join is used if possible. If it cannot be used, nothing is done.
• NoIndexJoin(table table)
  • No index join is used.

Specify in what order the tables are joined.

(1) Specify only joining order: Leading(table table [table ...])

Specify the table names or aliases in order from the first table to be joined. In this method, using only Left-deep join orders.

[Example 1]

/*+ Leading(S R Q P) */
SELECT * FROM P,Q,R,S WHERE P.x = Q.x AND ...

(2) Specify joining order and direction: Leading(( _table set_ _table set_ ))

table set = { table or ( table set table set ) }

In case of specifying only joining order like (1), the joining direction (different for outer table or inner table) may be different from expectation. To fix the joining direction, use the following expression.

/*+ Leading((t1 (t2 t3))) */
SELECT ...

In this expression, parentheses can be nested. It joins the first specified table set as the outer table and the second set as the inner table.

[Example 2-1]

/*+ Leading(((P Q) R)) */
SELECT * FROM P,Q,R WHERE P.x = Q.x AND ...

[Example 2-2]

/*+ Leading((R (Q P))) */
SELECT * FROM P,Q,R WHERE P.x = Q.x AND ...

[Example 2-3]

/*+ Leading(((P Q) (R S))) */
SELECT * FROM P,Q,R,S WHERE P.x = Q.x AND ...

[Memo]

• If three or more tables are joined and there is no join condition between the tables, it is impossible to specify the order by hint.