ScalarDB SQL Grammar
- DDL
- DML
- DCL
- CREATE USER
- ALTER USER
- DROP USER
- GRANT
- REVOKE
- CREATE ABAC_POLICY
- ENABLE ABAC_POLICY
- DISABLE ABAC_POLICY
- CREATE ABAC_LEVEL
- DROP ABAC_LEVEL
- CREATE ABAC_COMPARTMENT
- DROP ABAC_COMPARTMENT
- CREATE ABAC_GROUP
- DROP ABAC_GROUP
- SET ABAC_LEVEL
- ADD ABAC_COMPARTMENT
- REMOVE ABAC_COMPARTMENT
- ADD ABAC_GROUP
- REMOVE ABAC_GROUP
- DROP ABAC_USER_TAG_INFO
- CREATE ABAC_NAMESPACE_POLICY
- ENABLE ABAC_NAMESPACE_POLICY
- DISABLE ABAC_NAMESPACE_POLICY
- CREATE ABAC_TABLE_POLICY
- ENABLE ABAC_TABLE_POLICY
- DISABLE ABAC_TABLE_POLICY
- Others
- USE
- BEGIN
- START TRANSACTION
- JOIN
- PREPARE
- VALIDATE
- COMMIT
- ROLLBACK
- ABORT
- SET MODE
- SHOW NAMESPACES
- SHOW TABLES
- DESCRIBE
- SUSPEND
- RESUME
- SHOW USERS
- SHOW GRANTS
- SHOW ABAC_POLICY
- SHOW ABAC_POLICIES
- SHOW ABAC_LEVEL
- SHOW ABAC_LEVELS
- SHOW ABAC_COMPARTMENT
- SHOW ABAC_COMPARTMENTS
- SHOW ABAC_GROUP
- SHOW ABAC_GROUPS
- SHOW ABAC_USER_TAG_INFO
- SHOW ABAC_NAMESPACE_POLICY
- SHOW ABAC_NAMESPACE_POLICIES
- SHOW ABAC_TABLE_POLICY
- SHOW ABAC_TABLE_POLICIES
- Literal
DDL
CREATE NAMESPACE
Before creating tables, namespaces must be created since a table belongs to one namespace.
The CREATE NAMESPACE
command creates a namespace.
Grammar
CREATE NAMESPACE [IF NOT EXISTS] <namespace name> [WITH creation_options]
creation_options: <option name>=<option value> [AND <option name>=<option value>] ...
For details about creation_options
, see Creation options.
Examples
Examples of CREATE NAMESPACE
are as follows:
-- Create a namespace "ns"
CREATE NAMESPACE ns;
-- Create a namespace only if it does not already exist
CREATE NAMESPACE IF NOT EXISTS ns;
-- Create a namespace with options
CREATE NAMESPACE ns WITH 'option1' = 'value1' AND 'option2' = 'value2' AND 'option3' = 'value3';
Examples of building statement objects for CREATE NAMESPACE
are as follows:
// Create a namespace "ns"
CreateNamespaceStatement statement1 = StatementBuilder.createNamespace("ns").build();
// Create a namespace only if it does not already exist
CreateNamespaceStatement statement2 =
StatementBuilder.createNamespace("ns").ifNotExists().build();
// Create a namespace with options
CreateNamespaceStatement statement3 =
StatementBuilder.createNamespace("ns")
.withOption("option1", "value1")
.withOption("option2", "value2")
.withOption("option3", "value3")
.build();
CREATE TABLE
The CREATE TABLE
command creates a table.
For details about the ScalarDB data model, see ScalarDB Design Document.
Grammar
Create a table with a primary key that is composed of a single column:
CREATE TABLE [IF NOT EXISTS] [<namespace name>.]<table name> (
<primary key column name> data_type PRIMARY KEY,
<column name> data_type,
...
) [WITH creation_options]
data_type: BOOLEAN | INT | BIGINT | FLOAT | DOUBLE | TEXT | BLOB | DATE | TIME | TIMESTAMP | TIMESTAMPTZ
creation_options: <option name>=<option value> [AND <option name>=<option value>] ...
- You can specify
ENCRYPTED
for non-primary-key columns to encrypt the data in those columns.
Create a table with a primary key that is composed of one partition key column and multiple clustering key columns:
CREATE TABLE [IF NOT EXISTS] [<namespace name>.]<table name> (
<partition key column name> data_type,
<clustering key column name> data_type,
...,
<column name> data_type [ENCRYPTED],
...,
PRIMARY KEY (<partition key column name>, <clustering key column name> [, <clustering key column name>] ...)
) [WITH [clustering_order_definition [AND creation_options]] | creation_options]
clustering_order_definition: CLUSTERING ORDER BY (<clustering key column name> [clustering_order] [, <clustering key column name> [clustering_order]] ...)
clustering_order: ASC | DESC
- If you omit
clustering_order
, the default clustering orderASC
will be used.
Create a table with a primary key that is composed of multiple partition key columns and multiple clustering key columns:
CREATE TABLE [IF NOT EXISTS] [<namespace name>.]<table name> (
<partition key column name> data_type,
...,
<clustering key column name> data_type,
...,
<column name1> data_type [ENCRYPTED],
<column name2> data_type [ENCRYPTED],
...,
PRIMARY KEY ((<partition key column name> [, <partition key column name>] ...), <clustering key column name> [, <clustering key column name>] ...)
) [WITH [clustering_order_definition [AND creation_options]] | creation_options]
Examples
Examples of CREATE TABLE
are as follows:
-- Create a table with a primary key ("c1") and creation options
CREATE TABLE ns.tbl (
c1 INT PRIMARY KEY,
c2 TEXT,
c3 FLOAT,
c4 BIGINT,
c5 BOOLEAN
) WITH 'option1' = 'value1' AND 'option2' = 'value2' AND 'option3' = 'value3';
-- Create a table with a partition key ("c1") and a clustering key ("c2" and "c3") with clustering order definition only if it does not already exist
CREATE TABLE IF NOT EXISTS tbl (
c1 INT,
c2 TEXT,
c3 FLOAT,
c4 BIGINT,
c5 BOOLEAN,
PRIMARY KEY (c1, c2, c3)
) WITH CLUSTERING ORDER BY (c2 DESC, c3 ASC);
-- Create a table with a partition key ("c1", "c2") and a clustering key ("c3" and "c4") with clustering order definition and creation options
CREATE TABLE ns.tbl (
c1 INT,
c2 TEXT,
c3 FLOAT,
c4 BIGINT,
c5 BOOLEAN,
PRIMARY KEY ((c1, c2), c3, c4)
) WITH CLUSTERING ORDER BY (c3 DESC, c4 ASC) AND 'option1' = 'value1' AND 'option2' = 'value2' AND 'option3' = 'value3';
-- Create a table with a primary key ("c1") and encrypted columns ("c2", "c3", "c4", and "c5")
CREATE TABLE ns.tbl (
c1 INT PRIMARY KEY,
c2 TEXT ENCRYPTED,
c3 FLOAT ENCRYPTED,
c4 BIGINT ENCRYPTED,
c5 BOOLEAN ENCRYPTED
);
Examples of building statement objects for CREATE TABLE
are as follows:
// Create a table with a primary key ("c1") and creation options
CreateTableStatement statement1 =
StatementBuilder.createTable("ns", "tbl")
.withPartitionKey("c1", DataType.INT)
.withColumn("c2", DataType.TEXT)
.withColumn("c3", DataType.FLOAT)
.withColumn("c4", DataType.BIGINT)
.withColumn("c5", DataType.BOOLEAN)
.withOption("option1", "value1")
.withOption("option2", "value2")
.withOption("option3", "value3")
.build();
// Create a table with a partition key ("c1") and a clustering key ("c2" and "c3") with clustering order definition
CreateTableStatement statement2 =
StatementBuilder.createTable("tbl")
.ifNotExists()
.withPartitionKey("c1", DataType.INT)
.withClusteringKey("c2", DataType.TEXT)
.withClusteringKey("c3", DataType.FLOAT)
.withColumn("c4", DataType.BIGINT)
.withColumn("c5", DataType.BOOLEAN)
.withClusteringOrder("c2", ClusteringOrder.DESC)
.withClusteringOrder("c3", ClusteringOrder.ASC)
.build();
// Create a table with a partition key ("c1", "c2") and a clustering key ("c3" and "c4") with clustering order definition and creation options
CreateTableStatement statement3 =
StatementBuilder.createTable("ns", "tbl")
.withPartitionKey("c1", DataType.INT)
.withPartitionKey("c2", DataType.TEXT)
.withClusteringKey("c3", DataType.FLOAT)
.withClusteringKey("c4", DataType.BIGINT)
.withColumn("c5", DataType.BOOLEAN)
.withClusteringOrder("c3", ClusteringOrder.DESC)
.withClusteringOrder("c4", ClusteringOrder.ASC)
.withOption("option1", "value1")
.withOption("option2", "value2")
.withOption("option3", "value3")
.build();
// Create a table with a primary key ("c1") and encrypted columns ("c2", "c3", "c4", and "c5")
CreateTableStatement statement4 =
StatementBuilder.createTable("ns", "tbl")
.withPartitionKey("c1", DataType.INT)
.withColumn("c2", DataType.TEXT, true)
.withColumn("c3", DataType.FLOAT, true)
.withColumn("c4", DataType.BIGINT, true)
.withColumn("c5", DataType.BOOLEAN, true)
.build();
CREATE INDEX
The CREATE INDEX
command creates a secondary index on a table.
You cannot create a secondary index on encrypted columns.
Grammar
CREATE INDEX [IF NOT EXISTS] ON [<namespace name>.]<table name> (<column name>)
Examples
Examples of CREATE INDEX
are as follows:
-- Create a secondary index on a column "c4" of a table "ns.tbl"
CREATE INDEX ON ns.tbl (c4);
-- Create a secondary index only if it does not already exist
CREATE INDEX IF NOT EXISTS ON tbl (c4);
-- Create a secondary index with options
CREATE INDEX ON ns.tbl (c4) WITH 'option1' = 'value1' AND 'option2' = 'value2' AND 'option3' = 'value3';
Examples of building statement objects for CREATE INDEX
are as follows:
// Create a secondary index on a column "c4" of a table "ns.tbl"
CreateIndexStatement statement1 =
StatementBuilder.createIndex().onTable("ns", "tbl").column("c4").build();
// Create a secondary index only if it does not already exist
CreateIndexStatement statement2 =
StatementBuilder.createIndex().ifNotExists().onTable("tbl").column("c4").build();
// Create a secondary index with options
CreateIndexStatement statement3 =
StatementBuilder.createIndex()
.onTable("ns", "tbl")
.column("c4")
.withOption("option1", "value1")
.withOption("option2", "value2")
.withOption("option3", "value3")
.build();
TRUNCATE TABLE
The TRUNCATE TABLE
command truncates a table.
Grammar
TRUNCATE TABLE [<namespace name>.]<table name>
Examples
Examples of TRUNCATE TABLE
are as follows:
-- Truncate a table "ns.tbl"
TRUNCATE TABLE ns.tbl;
Examples of building statement objects for TRUNCATE TABLE
are as follows:
// Truncate a table "ns.tbl"
TruncateTableStatement statement = StatementBuilder.truncateTable("ns", "tbl").build();
DROP INDEX
The DROP INDEX
command drops a secondary index.
Grammar
DROP INDEX [IF EXISTS] ON [<namespace name>.]<table name> (<column name>)
Examples
Examples of DROP INDEX
are as follows:
-- Drop a secondary index on a column "c4" of a table "ns.tbl"
DROP INDEX ON ns.tbl (c4);
-- Drop a secondary index only if it exists
DROP INDEX IF EXISTS ON tbl (c4);
Examples of building statement objects for DROP INDEX
are as follows:
// Drop a secondary index on a column "c4" of a table "ns.tbl"
DropIndexStatement statement1 =
StatementBuilder.dropIndex().onTable("ns", "tbl").column("c4").build();
// Drop a secondary index only if it exists
DropIndexStatement statement2 =
StatementBuilder.dropIndex().ifExists().onTable("ns", "tbl").column("c4").build();
DROP TABLE
The DROP TABLE
command drops a table.
Grammar
DROP TABLE [IF EXISTS] [<namespace name>.]<table name>
Examples
Examples of DROP TABLE
are as follows:
-- Drop a table "ns.tbl"
DROP TABLE ns.tbl;
-- Drop a table only if it exists
DROP TABLE IF EXISTS tbl;
Examples of building statement objects for DROP TABLE
are as follows:
// Drop a table "ns.tbl"
DropTableStatement statement1 = StatementBuilder.dropTable("ns", "tbl").build();
// Drop a table only if it exists
DropTableStatement statement2 = StatementBuilder.dropTable("ns", "tbl").ifExists().build();
DROP NAMESPACE
The DROP NAMESPACE
command drops a namespace.
Grammar
DROP NAMESPACE [IF EXISTS] <namespace name> [CASCADE]
Examples
Examples of DROP NAMESPACE
are as follows:
-- Drop a namespace "ns"
DROP NAMESPACE ns;
-- Drop a namespace only if it exists
DROP NAMESPACE IF EXISTS ns;
-- Drop a namespace with cascade
DROP NAMESPACE ns CASCADE;
Examples of building statement objects for DROP NAMESPACE
are as follows:
// Drop a namespace "ns"
DropNamespaceStatement statement1 = StatementBuilder.dropNamespace("ns").build();
// Drop a namespace only if it exists
DropNamespaceStatement statement2 = StatementBuilder.dropNamespace("ns").ifExists().build();
// Drop a namespace with cascade
DropNamespaceStatement statement3 = StatementBuilder.dropNamespace("ns").cascade().build();
CREATE COORDINATOR TABLES
The CREATE COORDINATOR TABLES
command creates coordinator tables.
Grammar
CREATE COORDINATOR TABLES [IF NOT {EXIST|EXISTS}] [WITH creation_options]
creation_options: <option name>=<option value> [AND <option name>=<option value>] ...
Examples
Examples of CREATE COORDINATOR TABLES
are as follows:
-- Create coordinator tables
CREATE COORDINATOR TABLES;
-- Create coordinator tables only if they do not already exist
CREATE COORDINATOR TABLES IF NOT EXIST;
-- Create coordinator tables with options
CREATE COORDINATOR TABLES WITH 'option1' = 'value1' AND 'option2' = 'value2' AND 'option3' = 'value3';
Examples of building statement objects for CREATE COORDINATOR TABLES
are as follows:
// Create coordinator tables
CreateCoordinatorTablesStatement statement1 =
StatementBuilder.createCoordinatorTables().build();
// Create coordinator tables only if they do not already exist
CreateCoordinatorTablesStatement statement2 =
StatementBuilder.createCoordinatorTables().ifNotExist().build();
// Create coordinator tables with options
CreateCoordinatorTablesStatement statement3 =
StatementBuilder.createCoordinatorTables()
.withOption("option1", "value1")
.withOption("option2", "value2")
.withOption("option3", "value3")
.build();
TRUNCATE COORDINATOR TABLES
The TRUNCATE COORDINATOR TABLES
command truncates coordinator tables.
Grammar
TRUNCATE COORDINATOR TABLES
Examples
An example of building statement objects for TRUNCATE COORDINATOR TABLES
is as follows:
// Truncate coordinator tables
TruncateCoordinatorTablesStatement statement =
StatementBuilder.truncateCoordinatorTables().build();
DROP COORDINATOR TABLES
The DROP COORDINATOR TABLES
command drops coordinator tables.
Grammar
DROP COORDINATOR TABLES [IF {EXIST|EXISTS}]
Examples
Examples of DROP COORDINATOR TABLES
are as follows:
-- Drop coordinator tables
DROP COORDINATOR TABLES;
-- Drop coordinator tables if they exist
DROP COORDINATOR TABLES IF EXIST;
Examples of building statement objects for DROP COORDINATOR TABLES
are as follows:
// Drop coordinator tables
DropCoordinatorTablesStatement statement1 = StatementBuilder.dropCoordinatorTables().build();
// Drop coordinator tables if they exist
DropCoordinatorTablesStatement statement2 =
StatementBuilder.dropCoordinatorTables().ifExist().build();
ALTER TABLE
The ALTER TABLE
command alters a table (ex. adding a column).
Grammar
ALTER TABLE [<namespace name>.]<table name> ADD [COLUMN] <column name> data_type [ENCRYPTED]
data_type: BOOLEAN | INT | BIGINT | FLOAT | DOUBLE | TEXT | BLOB | DATE | TIME | TIMESTAMP | TIMESTAMPTZ
- You can specify
ENCRYPTED
for the column to encrypt the data in that column.
Examples
Examples of ALTER TABLE
are as follows:
-- Add a new column "new_col" to "ns.tbl"
ALTER TABLE ns.tbl ADD COLUMN new_col INT;
-- Add a new encrypted column "new_col" to "ns.tbl"
ALTER TABLE ns.tbl ADD COLUMN new_col TEXT ENCRYPTED;
Examples of building statement objects for ALTER TABLE
are as follows:
// Add a new column "new_col" to "ns.tbl"
AlterTableAddColumnStatement statement =
StatementBuilder.alterTable("ns", "tbl").addColumn("new_col", DataType.INT).build();
// Add a new encrypted column "new_col" to "ns.tbl"
AlterTableAddColumnStatement statement =
StatementBuilder.alterTable("ns", "tbl").addColumn("new_col", DataType.TEXT, true).build();
DML
SELECT
The SELECT
command retrieves records from the database. ScalarDB SQL creates an execution plan for a SELECT
command by using one of the Get
, partition Scan
, and cross-partition Scan
operations in ScalarDB to retrieve records from the database. For the best results, specify primary key columns uniquely as much as possible in the WHERE
clause to avoid using the cross-partition scan since it might cause performance and consistency issues, especially in non-JDBC databases. See the following rules for selecting operations. The former ones are prior to the latter ones and more efficient. ScalarDB data model also helps in understanding the best practices for modeling and accessing data.
- If you fully specify the primary-key columns in the
WHERE
clause, theSELECT
command will use aGet
operation for a single record in a single partition. - If you fully specify the partition key and properly specify the clustering key and order in the
WHERE
andORDER BY
clauses, theSELECT
command will use aScan
operation for records in a single partition. For more details, see the Examples of partition scans and index scans. - If you specify the indexed column value literal with the
equal to
(=
) operator in theWHERE
clause without theORDER BY
clause, theSELECT
command will use an indexScan
operation. - For other cases, the
SELECT
command will be converted to a cross-partitionScan
operation.
You need to enable the cross-partition scan option and the cross-partition scan with filtering and ordering options if you want to flexibly retrieve records across partitions with arbitrary conditions and orderings. Currently, the ordering option is available only for JDBC databases. For details about configurations, see Cross-partition scan configurations and ScalarDB Cluster SQL configurations.
For non-JDBC databases, transactions could be executed at read-committed snapshot isolation (SNAPSHOT
), which is a lower isolation level, even if you enable cross-partition scan with the SERIALIZABLE
isolation level. When using non-JDBC databases, use cross-partition scan only if consistency does not matter for your transactions.
Grammar
SELECT projection [, projection] ...
FROM [<namespace name>.]<table name> [AS <alias>] [join_specification [join_specification] ...]
[WHERE and_predicates [OR and_predicates ...] | or_predicates [AND or_predicates ...]]
[ORDER BY identifier [order] [, identifier [order]] ...]
[LIMIT <limit>]
[WITH operation_attributes_or_abac_read_tags]
projection: * | identifier [AS <alias>]
join_specification: [INNER] JOIN [<namespace name>.]<table name> [AS <alias>] ON join_predicate [AND join_predicate] ... | {LEFT|RIGHT} [OUTER] JOIN [<namespace name>.]<table name> [AS <alias>] ON join_predicate [AND join_predicate] ...
join_predicate: identifier = identifier
and_predicates: predicate | (predicate [AND predicate ...])
or_predicates: predicate | (predicate [OR predicate ...])
predicate: identifier operator <literal> | identifier BETWEEN <literal> AND <literal> | identifier [NOT] LIKE <pattern> [ESCAPE <escape character>] | identifier IS [NOT] NULL
identifier: [[<namespace name>.]<table name>.]<column name> | [alias.]<column name>
operator: = | <> | != | > | >= | < | <=
order: ASC | DESC
operation_attributes_or_abac_read_tags: operation_attribute_or_abac_read_tag [AND operation_attribute_or_abac_read_tag] ...
operation_attribute_or_abac_read_tag: operation_attribute | abac_read_tag
operation_attribute: <operation attribute name>=<operation attribute value>
abac_read_tag: ABAC_READ_TAG <abac read tag> FOR [POLICY] <abac policy name>
Note
JOIN
clause:
- For
[INNER] JOIN
andLEFT [OUTER] JOIN
:- The
join_predicate
s must include either all primary-key columns or a secondary-index column from the right table. - The
WHERE
predicates and theORDER BY
clause can only include columns from the table specified in theFROM
clause.
- The
- For
RIGHT [OUTER] JOIN
:- It must be specified as the first
join_specification
. - The
join_predicate
s must contain all primary-key columns or a secondary-index column from the left table. - The
WHERE
predicates and theORDER BY
clause can only specify columns from the table specified in theRIGHT OUTER JOIN
clause.
- It must be specified as the first
WHERE
clauses:
- You can use arbitrary predicates for any columns in the
WHERE
clause. - In the
WHERE
clause, predicates must be an OR-wise ofand_predicates
(known as disjunctive normal form) or an AND-wise ofor_predicates
(known as conjunctive normal form). - When connecting multiple
and_predicates
oror_predicates
, which have more than one predicate, you need to put parentheses aroundand_predicates
andor_predicates
. - You can specify
<literal>
to a bind marker (positional?
and named:<name>
). See the Literal section for the literal syntax. - You cannot specify encrypted columns in the
WHERE
clause.
LIKE
predicate:
_
in<pattern>
matches any single character.%
in<pattern>
matches any sequence of zero or more characters.\
in<pattern>
works as the escape character by default.- You can change the escape character by specifying the
ESCAPE
clause. - You can disable the escape character by specifying an empty escape character,
ESCAPE ''
.
- You can change the escape character by specifying the
ORDER BY
clause:
- You can specify
order
for any columns in theORDER BY
clause. - If you omit
order
, the default orderASC
will be used. - You cannot specify encrypted columns in the
ORDER BY
clause.
LIMIT
clause:
- You can specify
<limit>
to a bind marker (positional?
and named:<name>
).
For details about retrieving data from a database in ScalarDB, see Get operation and Scan operation.
Examples of partition scans and index scans
Let's say you have the following table and index:
CREATE TABLE ns.tbl (
c1 INT,
c2 TEXT,
c3 FLOAT,
c4 BIGINT,
c5 BOOLEAN,
PRIMARY KEY (c1, c2, c3)
) WITH CLUSTERING ORDER BY (c2 DESC, c3 ASC);
CREATE INDEX ON ns.tbl (c4);
Examples of SELECT
are as follows:
-- With a full primary key
SELECT * FROM ns.tbl WHERE c1 = 10 AND c2 = 'aaa' AND c3 = 1.23;
-- With a full primary key and predicates for non-primary-key columns
SELECT * FROM ns.tbl WHERE c1 = 10 AND c2 = 'aaa' AND c3 = 1.23 AND c4 < 100;
-- With a partial primary key
SELECT * FROM ns.tbl WHERE c1 = 10 AND c2 = 'aaa';
-- With a partial primary key and predicates for non-primary-key columns
SELECT * FROM ns.tbl WHERE c1 = 10 AND c2 = 'aaa' AND (c4 < 100 OR c4 > 500);
-- With projections and a partition key and clustering-key boundaries
SELECT c1, c2, c3, c5 FROM ns.tbl WHERE c1 = 10 AND c2 = 'aaa' AND c3 >= 1.23 AND c3 < 4.56;
-- With projections and a partition key and clustering-key boundaries and orders and limit
SELECT c1 AS a, c2 AS b, c3 AS c, c5 FROM ns.tbl WHERE c1 = 10 AND c2 > 'aaa' AND c2 <= 'ddd' ORDER BY c2 ASC, c3 DESC LIMIT 10;
-- With an equality predicate for an indexed column
SELECT * FROM ns.tbl WHERE c4 = 100;
-- With an equality predicate for an indexed column and predicates for non-primary-key columns
SELECT * FROM ns.tbl WHERE c4 = 100 AND c5 = false;
-- With projections and an indexed column and limit
SELECT c1, c2, c3, c4 FROM ns.tbl WHERE c4 = 100 LIMIT 10;
-- With positional bind markers
SELECT * FROM ns.tbl WHERE c1 = ? AND c2 > ? AND c2 <= ? ORDER BY c2 ASC, c3 DESC LIMIT ?;
-- With operations attributes
SELECT * FROM ns.tbl WHERE c1 = 10 AND c2 = 'aaa' AND c3 = 1.23 WITH 'attribute1' = 'value1' AND 'attribute2' = 'value2';
-- With an ABAC read tag
SELECT * FROM ns.tbl WHERE c1 = 10 AND c2 = 'aaa' AND c3 = 1.23 WITH ABAC_READ_TAG 'read_tag1' FOR POLICY policy1;
-- With operations attributes and an ABAC read tag
SELECT * FROM ns.tbl WHERE c1 = 10 AND c2 = 'aaa' AND c3 = 1.23 WITH 'attribute1' = 'value1' AND 'attribute2' = 'value2' AND ABAC_READ_TAG 'read_tag1' FOR POLICY policy1;
Examples of building statement objects for SELECT
are as follows:
// With a full primary key
SelectStatement statement1 =
StatementBuilder.select()
.from("ns", "tbl")
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
.and(Predicate.column("c3").isEqualTo(Value.of(1.23F)))
.and(Predicate.column("c4").isLessThan(Value.of(100)))
.build();
// With a full primary key and predicates for non-primary-key columns
SelectStatement statement1 =
StatementBuilder.select()
.from("ns", "tbl")
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
.and(Predicate.column("c4").isEqualTo(Value.of(1.23F)))
.and(Predicate.column("c4").isLessThan(Value.of(100)))
.build();
// With a partial primary key
SelectStatement statement2 =
StatementBuilder.select()
.from("ns", "tbl")
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
.build();
// With a partial primary key and predicates for non-primary-key columns
SelectStatement statement2 =
StatementBuilder.select()
.from("ns", "tbl")
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
.and(
AndPredicateList.predicate(Predicate.column("c4").isLessThan(Value.of(100)))
.and(Predicate.column("c4").isGreaterThan(Value.of(500)))
.build())
.build();
// With projections and a partition key and clustering-key boundaries
SelectStatement statement3 =
StatementBuilder.select("c1", "c2", "c3", "c5")
.from("ns", "tbl")
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
.and(Predicate.column("c3").isGreaterThanOrEqualTo(Value.of(1.23F)))
.and(Predicate.column("c3").isLessThan(Value.of(4.56F)))
.build();
// With projections and a partition key and clustering key boundaries and orders and limit
SelectStatement statement4 =
StatementBuilder.select(
Projection.column("c1").as("a"),
Projection.column("c2").as("b"),
Projection.column("c3").as("c"),
Projection.column("c5").as("d"))
.from("ns", "tbl")
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isGreaterThan(Value.of("aaa")))
.and(Predicate.column("c2").isLessThanOrEqualTo(Value.of("ddd")))
.orderBy(Ordering.column("c2").asc(), Ordering.column("c3").desc())
.limit(10)
.build();
// With an equality predicate for an indexed column
SelectStatement statement5 =
StatementBuilder.select()
.from("ns", "tbl")
.where(Predicate.column("c4").isEqualTo(Value.of(100)))
.build();
// With an equality predicate for an indexed column and predicates for non-primary-key columns
SelectStatement statement5 =
StatementBuilder.select()
.from("ns", "tbl")
.where(Predicate.column("c4").isEqualTo(Value.of(100)))
.and(Predicate.column("c5").isEqualTo(Value.of(false)))
.build();
// With projections and an indexed column and limit
SelectStatement statement6 =
StatementBuilder.select("c1", "c2", "c3", "c4")
.from("ns", "tbl")
.where(Predicate.column("c4").isEqualTo(Value.of(100)))
.limit(10)
.build();
// With positional bind markers
SelectStatement statement7 =
StatementBuilder.select()
.from("ns", "tbl")
.where(Predicate.column("c1").isEqualTo(BindMarker.positional()))
.and(Predicate.column("c2").isGreaterThan(BindMarker.positional()))
.and(Predicate.column("c2").isLessThanOrEqualTo(BindMarker.positional()))
.orderBy(Ordering.column("c2").asc(), Ordering.column("c3").desc())
.limit(BindMarker.positional())
.build();
// With operations attributes
SelectStatement statement8 =
StatementBuilder.select()
.from("ns", "tbl")
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
.and(Predicate.column("c3").isEqualTo(Value.of(1.23F)))
.withAttribute("attribute1", "value1")
.withAttribute("attribute2", "value2")
.build();
// With an ABAC read tag
SelectStatement statement9 =
StatementBuilder.select()
.from("ns", "tbl")
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
.and(Predicate.column("c3").isEqualTo(Value.of(1.23F)))
.withAbacReadTag("read_tag1", "policy1")
.build();
// With operations attributes and an ABAC read tag
SelectStatement statement10 =
StatementBuilder.select()
.from("ns", "tbl")
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
.and(Predicate.column("c3").isEqualTo(Value.of(1.23F)))
.withAttribute("attribute1", "value1")
.withAttribute("attribute2", "value2")
.withAbacReadTag("read_tag1", "policy1")
.build();
Examples of SELECT
with JOIN
are as follows:
-- For INNER JOIN and LEFT OUTER JOIN:
SELECT * FROM tbl1 as t1
INNER JOIN tbl2 as t2 on t1.col1=t2.id1 and t1.col2=t2.id2 -- This part must have all primary key columns or a secondary index column of `tbl2`.
WHERE t1.pkey=1 -- Only columns of `tbl1` can be specified here.
ORDER BY t1.ckey DESC; -- Only columns of `tbl1` can be specified here.
SELECT * FROM tbl1 as t1
INNER JOIN tbl2 as t2 on t1.col1=t2.id -- This part must have all primary key columns or a secondary index column of `tbl2`.
LEFT OUTER JOIN tbl3 as t3 on t1.col2=t3.id -- This part must have all primary key columns or a secondary index column of `tbl3`.
WHERE t1.pkey=1 -- Only columns of `tbl1` can be specified here.
ORDER BY t1.ckey DESC; -- Only columns of `tbl1` can be specified here.
-- For RIGHT OUTER JOIN:
SELECT * FROM tbl1 as t1
RIGHT OUTER JOIN tbl2 as t2 on t1.id=t2.col -- Acceptable as the first join. And this part must have all primary key columns or a secondary index column of `tbl1`.
LEFT OUTER JOIN tbl3 as t3 on t1.col2=t3.id
WHERE t2.pkey=1 -- Only columns of `tbl2` can be specified here.
ORDER BY t2.ckey DESC; -- Only columns of `tbl2` can be specified here.
SELECT * FROM tbl1 as t1
RIGHT OUTER JOIN tbl2 as t2 on t1.id1=t2.col1 and t1.id2=t2.col2 -- This part must have all primary key columns or a secondary index column of `tbl1`.
WHERE t2.pkey=1 -- Only columns of `tbl2` can be specified here.
ORDER BY t2.ckey DESC; -- Only columns of `tbl2` can be specified here.
Examples of building statement objects for SELECT
with JOIN
are as follows:
// For INNER JOIN and LEFT OUTER JOIN:
SelectStatement statement1 =
StatementBuilder.select()
.from("tbl1", "t1")
.innerJoin("tbl2", "t2")
.on(JoinPredicate.column("t1", "col1").isEqualTo("t2", "id1"))
.and(JoinPredicate.column("t1", "col2").isEqualTo("t2", "id2")) // This part must have all primary key columns or a secondary index column of `tbl2`.
.where(Predicate.column("t1", "pkey").isEqualTo(Value.of(1))) // Only columns of `tbl1` can be specified here.
.orderBy(Ordering.column("t1", "ckey").desc()) // Only columns of `tbl1` can be specified here.
.build();
SelectStatement statement2 =
StatementBuilder.select()
.from("tbl1", "t1")
.innerJoin("tbl2", "t2")
.on(JoinPredicate.column("t1", "col1").isEqualTo("t2", "id")) // This part must have all primary key columns or a secondary index column of `tbl2`.
.leftOuterJoin("tbl3", "t3")
.on(JoinPredicate.column("t1", "col2").isEqualTo("t3", "id")) // This part must have all primary key columns or a secondary index column of `tbl3`.
.where(Predicate.column("t1", "pkey").isEqualTo(Value.of(1))) // Only columns of `tbl1` can be specified here.
.orderBy(Ordering.column("t1", "ckey").desc()) // Only columns of `tbl1` can be specified here.
.build();
// For RIGHT OUTER JOIN:
SelectStatement statement3 =
StatementBuilder.select()
.from("tbl1", "t1")
.rightOuterJoin("tbl2", "t2")
.on(JoinPredicate.column("t1", "id").isEqualTo("t2", "col")) // Acceptable as the first join. And this part must have all primary key columns or a secondary index column of `tbl1`.
.leftOuterJoin("tbl3", "t3")
.on(JoinPredicate.column("t1", "col2").isEqualTo("t3", "id"))
.where(Predicate.column("t2", "pkey").isEqualTo(Value.of(1))) // Only columns of `tbl2` can be specified here.
.orderBy(Ordering.column("t2", "ckey").desc()) // Only columns of `tbl2` can be specified here.
.build();
SelectStatement statement4 =
StatementBuilder.select()
.from("tbl1", "t1")
.rightOuterJoin("tbl2", "t2")
.on(JoinPredicate.column("t1", "id1").isEqualTo("t2", "col1"))
.and(JoinPredicate.column("t1", "id2").isEqualTo("t2", "col2")) // This part must have all primary key columns or a secondary index column of `tbl1`.
.where(Predicate.column("t2", "pkey").isEqualTo(Value.of(1))) // Only columns of `tbl2` can be specified here.
.orderBy(Ordering.column("t2", "ckey").desc()) // Only columns of `tbl2` can be specified here.
.build();
Examples of cross-partition scan
If you have the following table, for example:
CREATE TABLE ns.user (
id INT,
name TEXT,
age INT,
height FLOAT,
PRIMARY KEY (id)
)
Examples of SELECT
with cross-partition scan are as follows:
-- Without the WHERE clause to retrieve all the records of a table
SELECT * FROM ns.user;
-- Without the WHERE clause and with projections and a limit
SELECT id, name FROM ns.user LIMIT 10;
-- With AND predicates for non-primary-key columns
SELECT * FROM ns.user WHERE age > 10 AND height > 140;
-- With OR predicates for non-primary key columns
SELECT * FROM ns.user WHERE age > 10 OR height > 140;
-- With OR-wise of AND predicates
SELECT * FROM ns.user WHERE (age > 10 AND height > 150) OR (age > 15 AND height > 145);
-- With AND-wise of OR predicates
SELECT * FROM ns.user WHERE (age < 10 OR age > 65) AND (height < 145 OR height > 175);
-- With LIKE predicates
SELECT * FROM ns.user WHERE name LIKE 'A%' OR name NOT LIKE 'B_b';
-- With LIKE predicates with an escape character
SELECT * FROM ns.user WHERE name LIKE '+%Alice' ESCAPE '+';
-- With IS NULL predicates
SELECT * FROM ns.user WHERE name IS NOT NULL AND age IS NULL;
-- With projections
SELECT name, age, height FROM ns.user WHERE (age < 10 OR age > 65) AND age <> 0;
-- With limit
SELECT name, age, height FROM ns.user WHERE age < 10 OR age > 65 LIMIT 10;
-- With orderings
SELECT * FROM ns.user WHERE age < 10 ORDER BY height DESC;
-- With orderings without the WHERE clause
SELECT * FROM ns.user ORDER BY height;
-- With positional bind markers
SELECT * FROM ns.user WHERE age < ? ORDER BY age ASC, height DESC LIMIT ?;
For examples that use the JOIN
clause, see Examples of partition scans and index scans.
Examples of building statement objects for SELECT
are as follows:
// Without the WHERE clause to retrieve all the records of a table
SelectStatement statement1 = StatementBuilder.select().from("ns", "user").build();
// Without the WHERE clause and with projections and a limit
SelectStatement statement2 =
StatementBuilder.select("id", "name").from("ns", "user").limit(10).build();
// With AND predicates for non-primary-key columns
SelectStatement statement2 =
StatementBuilder.select()
.from("ns", "user")
.where(Predicate.column("age").isGreaterThan(Value.of(10)))
.and(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
.build();
// With OR predicates for non-primary key columns
SelectStatement statement3 =
StatementBuilder.select()
.from("ns", "user")
.where(Predicate.column("age").isGreaterThan(Value.of(10)))
.or(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
.build();
// With OR-wise of AND predicates
SelectStatement statement4 =
StatementBuilder.select()
.from("ns", "user")
.where(
AndPredicateList.predicate(Predicate.column("age").isGreaterThan(Value.of(10)))
.and(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
.build())
.or(
AndPredicateList.predicate(Predicate.column("age").isGreaterThan(Value.of(15)))
.and(Predicate.column("height").isGreaterThan(Value.of(145.0F)))
.build())
.build();
// With AND-wise of OR predicates
SelectStatement statement5 =
StatementBuilder.select()
.from("ns", "user")
.where(
OrPredicateList.predicate(Predicate.column("age").isLessThan(Value.of(10)))
.or(Predicate.column("age").isGreaterThan(Value.of(65)))
.build())
.and(
OrPredicateList.predicate(Predicate.column("height").isLessThan(Value.of(145.0F)))
.or(Predicate.column("height").isGreaterThan(Value.of(175.0F)))
.build())
.build();
// With LIKE predicates
SelectStatement statement6 =
StatementBuilder.select()
.from("ns", "user")
.where(Predicate.column("name").isLike(Value.of("A%")))
.or(Predicate.column("name").isNotLike(Value.of("B_b")))
.build();
// With LIKE predicates with an escape character
SelectStatement statement7 =
StatementBuilder.select()
.from("ns", "user")
.where(Predicate.column("name").isLike(Value.of("+%Alice"), Value.of("+")))
.build();
// With IS NULL predicates
SelectStatement statement8 =
StatementBuilder.select()
.from("ns", "user")
.where(Predicate.column("name").isNotNull())
.and(Predicate.column("age").isNull())
.build();
// With projections
SelectStatement statement9 =
StatementBuilder.select("name", "age", "height")
.from("ns", "user")
.where(
OrPredicateList.predicate(Predicate.column("age").isLessThan(Value.of(10)))
.or(Predicate.column("age").isGreaterThan(Value.of(65)))
.build())
.and(Predicate.column("height").isNotEqualTo(Value.of(0)))
.build();
// With limit
SelectStatement statement10 =
StatementBuilder.select("name", "age", "height")
.from("ns", "user")
.where(Predicate.column("age").isLessThan(Value.of(10)))
.or(Predicate.column("age").isGreaterThan(Value.of(65)))
.limit(10)
.build();
// With orderings
SelectStatement statement11 =
StatementBuilder.select()
.from("ns", "user")
.where(Predicate.column("age").isLessThan(Value.of(10)))
.orderBy(Ordering.column("height").desc())
.build();
// With orderings without the WHERE clause
SelectStatement statement12 =
StatementBuilder.select()
.from("ns", "user")
.orderBy(Ordering.column("height").desc())
.build();
// With positional bind markers
SelectStatement statement13 =
StatementBuilder.select()
.from("ns", "user")
.where(Predicate.column("age").isLessThan(BindMarker.positional()))
.orderBy(Ordering.column("age").asc(), Ordering.column("height").desc())
.limit(BindMarker.positional())
.build();
For examples that use the JOIN
clause, see Examples of partition scans and index scans.
INSERT
The INSERT
command inserts new records into the database. If any of the target records already exist, a transaction conflict error will be thrown.
This command returns the following column:
updateCount
:INT
- the number of inserted records
Grammar
INSERT INTO [<namespace name>.]<table name> [(<column name> [, <column name>] ...)]
VALUES (<literal> [, <literal>] ...) [, (<literal> [, <literal>] ...)] ...
[WITH operation_attributes]
operation_attributes: <operation attribute name>=<operation attribute value> [AND <operation attribute name>=<operation attribute value>] ...
- You must specify a full primary key in
INSERT
. - You can specify
<literal>
to a bind marker (positional?
and named:<name>
). See the Literal section for the literal syntax.
Examples
Examples of INSERT
are as follows:
-- Insert a record without specifying column names
INSERT INTO ns.tbl VALUES (10, 'aaa', 1.23, 100, true);
-- Insert a record with column names
INSERT INTO ns.tbl (c1, c2, c3, c4) VALUES (10, 'aaa', 1.23, 100);
-- With positional bind markers
INSERT INTO tbl VALUES (?, ?, ?, ?, ?);
-- Insert multiple records
INSERT INTO ns.tbl (c1, c2, c3, c4) VALUES (10, 'aaa', 1.23, 100), (20, 'bbb', 4.56, 200);
Examples of building statement objects for INSERT
are as follows:
// Insert a record without specifying column names.
InsertStatement statement1 = StatementBuilder.insertInto("ns", "tbl")
.values(Value.ofInt(10), Value.ofText("aaa"), Value.of(1.23F), Value.of(100L), Value.of(true))
.build();
// Insert a record with column names.
InsertStatement statement2 = StatementBuilder.insertInto("ns", "tbl")
.columns("c1", "c2", "c3", "c4", "c5")
.values(Value.ofInt(10), Value.ofText("aaa"), Value.of(1.23F), Value.of(100L), Value.of(true))
.build();
// With positional bind markers
InsertStatement statement3 =
StatementBuilder.insertInto("tbl")
.columns("c1", "c2", "c3", "c4", "c5")
.values(
BindMarker.positional(),
BindMarker.positional(),
BindMarker.positional(),
BindMarker.positional(),
BindMarker.positional())
.build();
// Insert multiple records.
InsertStatement statement4 = StatementBuilder.insertInto("ns", "tbl")
.columns("c1", "c2", "c3", "c4", "c5")
.values(Value.ofInt(10), Value.ofText("aaa"), Value.of(1.23F), Value.of(100L), Value.of(true))
.values(Value.ofInt(20), Value.ofText("bbb"), Value.of(2.46F), Value.of(200L), Value.of(false))
.build();
UPSERT
The UPSERT
command inserts new records into the database if they don't exist or updates the target records if they already exist.
This command returns the following column:
updateCount
:INT
- the number of inserted or updated records
Grammar
UPSERT INTO [<namespace name>.]<table name> [(<column name> [, <column name>] ...)]
VALUES (<literal> [, <literal>] ...) [, (<literal> [, <literal>] ...)] ...
[WITH operation_attributes_or_abac_read_tags_or_abac_write_tags]
operation_attributes_or_abac_read_tags_or_abac_write_tags: operation_attribute_or_abac_read_tag_or_abac_write_tag [AND operation_attribute_or_abac_read_tag_or_abac_write_tag] ...
operation_attribute_or_abac_read_tag_or_abac_write_tag: operation_attribute | abac_read_tag | abac_write_tag
operation_attribute: <operation attribute name>=<operation attribute value>
abac_read_tag: ABAC_READ_TAG <abac read tag> FOR [POLICY] <abac policy name>
abac_write_tag: ABAC_WRITE_TAG <abac write tag> FOR [POLICY] <abac policy name>
- You must specify a full primary key in
UPSERT
. - You can specify
<literal>
to a bind marker (positional?
and named:<name>
). See the Literal section for the literal syntax.
Examples
Examples of UPSERT
are as follows:
-- Upsert a record without specifying column names.
UPSERT INTO ns.tbl VALUES (10, 'aaa', 1.23, 100, true);
-- Upsert a record with column names.
UPSERT INTO ns.tbl (c1, c2, c3, c4) VALUES (10, 'aaa', 1.23, 100);
-- With positional bind markers
UPSERT INTO tbl VALUES (?, ?, ?, ?, ?);
-- Upsert multiple records.
UPSERT INTO ns.tbl (c1, c2, c3, c4) VALUES (10, 'aaa', 1.23, 100), (20, 'bbb', 4.56, 200);
-- With operations attributes
UPSERT INTO ns.tbl VALUES (10, 'aaa', 1.23, 100, true) WITH 'attribute1' = 'value1' AND 'attribute2' = 'value2';
-- With an ABAC read tag and an ABAC write tag
UPSERT INTO ns.tbl VALUES (10, 'aaa', 1.23, 100, true) WITH ABAC_READ_TAG 'read_tag1' FOR POLICY policy1 AND ABAC_WRITE_TAG 'write_tag1' FOR POLICY policy1;
-- With operations attributes and ABAC read and write tags
UPSERT INTO ns.tbl VALUES (10, 'aaa', 1.23, 100, true) WITH 'attribute1' = 'value1' AND 'attribute2' = 'value2' AND ABAC_READ_TAG 'read_tag1' FOR POLICY policy1 AND ABAC_WRITE_TAG 'write_tag1' FOR POLICY policy1;
Examples of building statement objects for UPSERT
are as follows:
// Upsert a record without specifying column names.
UpsertStatement statement1 =
StatementBuilder.upsertInto("ns", "tbl")
.values(
Value.ofInt(10),
Value.ofText("aaa"),
Value.of(1.23F),
Value.of(100L),
Value.of(true))
.build();
// Upsert a record with column names.
UpsertStatement statement2 =
StatementBuilder.upsertInto("ns", "tbl")
.columns("c1", "c2", "c3", "c4", "c5")
.values(
Value.ofInt(10),
Value.ofText("aaa"),
Value.of(1.23F),
Value.of(100L),
Value.of(true))
.build();
// With positional bind markers
UpsertStatement statement3 =
StatementBuilder.upsertInto("tbl")
.columns("c1", "c2", "c3", "c4", "c5")
.values(
BindMarker.positional(),
BindMarker.positional(),
BindMarker.positional(),
BindMarker.positional(),
BindMarker.positional())
.build();
// Upsert multiple records.
UpsertStatement statement4 =
StatementBuilder.upsertInto("ns", "tbl")
.columns("c1", "c2", "c3", "c4", "c5")
.values(
Value.ofInt(10),
Value.ofText("aaa"),
Value.of(1.23F),
Value.of(100L),
Value.of(true))
.values(
Value.ofInt(20),
Value.ofText("bbb"),
Value.of(2.46F),
Value.of(200L),
Value.of(false))
.build();
// With operations attributes
UpsertStatement statement5 =
StatementBuilder.upsertInto("ns", "tbl")
.values(
Value.ofInt(10),
Value.ofText("aaa"),
Value.of(1.23F),
Value.of(100L),
Value.of(true))
.withAttribute("attribute1", "value1")
.withAttribute("attribute2", "value2")
.build();
// With an ABAC read tag and an ABAC write tag
UpsertStatement statement6 =
StatementBuilder.upsertInto("ns", "tbl")
.values(
Value.ofInt(10),
Value.ofText("aaa"),
Value.of(1.23F),
Value.of(100L),
Value.of(true))
.withAbacReadTag("read_tag1", "policy1")
.withAbacWriteTag("write_tag1", "policy1")
.build();
// With operations attributes and ABAC read and write tags
UpsertStatement statement7 =
StatementBuilder.upsertInto("ns", "tbl")
.values(
Value.ofInt(10),
Value.ofText("aaa"),
Value.of(1.23F),
Value.of(100L),
Value.of(true))
.withAttribute("attribute1", "value1")
.withAttribute("attribute2", "value2")
.withAbacReadTag("read_tag1", "policy1")
.withAbacWriteTag("write_tag1", "policy1")
.build();
UPDATE
The UPDATE
command updates existing records in the database. You can specify any conditions in the WHERE
clause to filter records. However, specifying a primary key uniquely as much as possible is recommended to avoid the cross-partition operation since it might cause performance and consistency issues, especially in non-JDBC databases. Because ScalarDB SQL creates an execution plan for an UPDATE
command that uses a Get
or Scan
operation to identify the target records, the same rule is applied for the selection of records. To understand what kinds of WHERE
clauses cause cross-partition operations and to avoid such operations, see SELECT.
You need to enable the cross-partition scan option if you want to update all records across partitions without specifying the WHERE
clause. You also need to enable the cross-partition scan with filtering option if you want to flexibly update records across partitions with arbitrary conditions in the WHERE
clause. For details about configurations, see Cross-partition scan configurations and ScalarDB Cluster SQL configurations.
For non-JDBC databases, transactions could be executed at read-committed snapshot isolation (SNAPSHOT
), which is a lower isolation level, even if you enable cross-partition scan with the SERIALIZABLE
isolation level. When using non-JDBC databases, use cross-partition scan only if consistency does not matter for your transactions.
This command returns the following column:
updateCount
:INT
- the number of updated records
Grammar
UPDATE [<namespace name>.]<table name> [AS <alias>]
SET <column identifier> = <literal> [, <column identifier> = <literal>] ...
[WHERE and_predicates [OR and_predicates ...] | or_predicates [AND or_predicates ...]]
[WITH operation_attributes_or_abac_read_tags_or_abac_write_tags]
identifier: [[<namespace name>.]<table name>.]<column name> | [alias.]<column name>
and_predicates: predicate | (predicate [AND predicate ...])
or_predicates: predicate | (predicate [OR predicate ...])
predicate: <identifier> operator <literal> | <identifier> BETWEEN <literal> AND <literal> | <identifier> [NOT] LIKE <pattern> [ESCAPE <escape character>] | <identifier> IS [NOT] NULL
operator: = | <> | != | > | >= | < | <=
operation_attributes_or_abac_read_tags_or_abac_write_tags: operation_attribute_or_abac_read_tag_or_abac_write_tag [AND operation_attribute_or_abac_read_tag_or_abac_write_tag] ...
operation_attribute_or_abac_read_tag_or_abac_write_tag: operation_attribute | abac_read_tag | abac_write_tag
operation_attribute: <operation attribute name>=<operation attribute value>
abac_read_tag: ABAC_READ_TAG <abac read tag> FOR [POLICY] <abac policy name>
abac_write_tag: ABAC_WRITE_TAG <abac write tag> FOR [POLICY] <abac policy name>
Note
WHERE
clause:
- You can use arbitrary predicates for any columns in the
WHERE
clause. - In the
WHERE
clause, predicates must be an OR-wise ofand_predicates
(known as disjunctive normal form) or an AND-wise ofor_predicates
(known as conjunctive normal form). - When connecting multiple
and_predicates
oror_predicates
, which have more than one predicate, you need to put parentheses aroundand_predicates
andor_predicates
. - You can specify
<literal>
to a bind marker (positional?
and named:<name>
). See the Literal section for the literal syntax. - You cannot specify encrypted columns in the
WHERE
clause.
LIKE
predicate:
_
in<pattern>
matches any single character.%
in<pattern>
matches any sequence of zero or more characters.\
in<pattern>
works as the escape character by default.- You can change the escape character by specifying the
ESCAPE
clause. - You can disable the escape character by specifying an empty escape character,
ESCAPE ''
.
- You can change the escape character by specifying the
Examples with the full primary key specified
If you have the following table, for example:
CREATE TABLE ns.tbl (
c1 INT,
c2 TEXT,
c3 FLOAT,
c4 BIGINT,
c5 BOOLEAN,
PRIMARY KEY (c1, c2, c3)
) WITH CLUSTERING ORDER BY (c2 DESC, c3 ASC);
Examples of UPDATE
with the full primary key specified are as follows:
-- Update a record
UPDATE ns.tbl SET c4 = 200, c5 = false WHERE c1 = 10 AND c2 = 'aaa' AND c3 = 1.23;
-- With positional bind markers
UPDATE ns.tbl SET c4 = ?, c5 = ? WHERE c1 = ? AND c2 = ? AND c3 = ?;
-- With operations attributes
UPDATE ns.tbl SET c4 = 200, c5 = false WHERE c1 = 10 AND c2 = 'aaa' AND c3 = 1.23 WITH 'attribute1' = 'value1' AND 'attribute2' = 'value2';
-- With an ABAC read tag and an ABAC write tag
UPDATE ns.tbl SET c4 = 200, c5 = false WHERE c1 = 10 AND c2 = 'aaa' AND c3 = 1.23 WITH ABAC_READ_TAG 'read_tag1' FOR POLICY policy1 AND ABAC_WRITE_TAG 'write_tag1' FOR POLICY policy1;
-- With operations attributes and ABAC read and write tags
UPDATE ns.tbl SET c4 = 200, c5 = false WHERE c1 = 10 AND c2 = 'aaa' AND c3 = 1.23 WITH 'attribute1' = 'value1' AND 'attribute2' = 'value2' AND ABAC_READ_TAG 'read_tag1' FOR POLICY policy1 AND ABAC_WRITE_TAG 'write_tag1' FOR POLICY policy1;
Examples of building statement objects for UPDATE
are as follows:
// Update a record
UpdateStatement statement1 =
StatementBuilder.update("ns", "tbl")
.set(
Assignment.column("c4").value(Value.of(200L)),
Assignment.column("c5").value(Value.of(false)))
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
.and(Predicate.column("c3").isEqualTo(Value.of(1.23F)))
.build();
// With positional bind markers
UpdateStatement statement2 =
StatementBuilder.update("tbl")
.set(
Assignment.column("c4").value(BindMarker.positional()),
Assignment.column("c5").value(BindMarker.positional()))
.where(Predicate.column("c1").isEqualTo(BindMarker.positional()))
.and(Predicate.column("c2").isEqualTo(BindMarker.positional()))
.and(Predicate.column("c3").isEqualTo(BindMarker.positional()))
.build();
// With operations attributes
UpdateStatement statement3 =
StatementBuilder.update("ns", "tbl")
.set(
Assignment.column("c4").value(Value.of(200L)),
Assignment.column("c5").value(Value.of(false)))
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
.and(Predicate.column("c3").isEqualTo(Value.of(1.23F)))
.withAttribute("attribute1", "value1")
.withAttribute("attribute2", "value2")
.build();
// With an ABAC read tag and an ABAC write tag
UpdateStatement statement4 =
StatementBuilder.update("ns", "tbl")
.set(
Assignment.column("c4").value(Value.of(200L)),
Assignment.column("c5").value(Value.of(false)))
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
.and(Predicate.column("c3").isEqualTo(Value.of(1.23F)))
.withAbacReadTag("read_tag1", "policy1")
.withAbacWriteTag("write_tag1", "policy1")
.build();
// With operations attributes and ABAC read and write tags
UpdateStatement statement5 =
StatementBuilder.update("ns", "tbl")
.set(
Assignment.column("c4").value(Value.of(200L)),
Assignment.column("c5").value(Value.of(false)))
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
.and(Predicate.column("c3").isEqualTo(Value.of(1.23F)))
.withAttribute("attribute1", "value1")
.withAttribute("attribute2", "value2")
.withAbacReadTag("read_tag1", "policy1")
.withAbacWriteTag("write_tag1", "policy1")
.build();
Examples without the full primary key specified
If you have the following table, for example:
CREATE TABLE ns.user (
id INT,
name TEXT,
age INT,
height FLOAT,
PRIMARY KEY (id)
)
Examples of UPDATE
without the full primary key specified are as follows:
-- Without the WHERE clause to update all the records of a table
UPDATE ns.user SET age = 31;
-- With AND predicates for non-primary key columns
UPDATE ns.user SET age = 31 WHERE age > 10 AND height > 140;
-- With OR predicates for non-primary key columns
UPDATE ns.user SET age = 31 WHERE age > 10 OR height > 140;
-- With OR-wise of AND predicates
UPDATE ns.user SET age = 31 WHERE (age > 10 AND height > 150) OR (age > 15 AND height > 145);
-- With AND-wise of OR predicates
UPDATE ns.user SET age = 31 WHERE (age < 10 OR age > 65) AND (height < 145 OR height > 175);
-- With LIKE predicates
UPDATE ns.user SET age = 31 WHERE name LIKE 'A%' OR name NOT LIKE 'B_b';
-- With LIKE predicates with an escape character
UPDATE ns.user SET age = 31 WHERE name LIKE '+%Alice' ESCAPE '+';
-- With IS NULL predicates
UPDATE ns.user SET age = 31 WHERE name IS NOT NULL AND age IS NULL;
-- With positional bind markers
UPDATE ns.user SET age = ? WHERE age < ?;
Examples of building statement objects for UPDATE
are as follows:
// Without the WHERE clause to update all the records of a table
UpdateStatement statement1 =
StatementBuilder.update("ns", "user")
.set(Assignment.column("age").value(Value.of(30)))
.build();
// With AND predicates for non-primary key columns
UpdateStatement statement2 =
StatementBuilder.update("ns", "user")
.set(Assignment.column("age").value(Value.of(30)))
.where(Predicate.column("age").isGreaterThan(Value.of(10)))
.and(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
.build();
// With OR predicates for non-primary key columns
UpdateStatement statement3 =
StatementBuilder.update("ns", "user")
.set(Assignment.column("age").value(Value.of(30)))
.where(Predicate.column("age").isGreaterThan(Value.of(10)))
.or(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
.build();
// With OR-wise of AND predicates
UpdateStatement statement4 =
StatementBuilder.update("ns", "user")
.set(Assignment.column("age").value(Value.of(30)))
.where(
AndPredicateList.predicate(Predicate.column("age").isGreaterThan(Value.of(10)))
.and(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
.build())
.or(
AndPredicateList.predicate(Predicate.column("age").isGreaterThan(Value.of(15)))
.and(Predicate.column("height").isGreaterThan(Value.of(145.0F)))
.build())
.build();
// With AND-wise of OR predicates
UpdateStatement statement5 =
StatementBuilder.update("ns", "user")
.set(Assignment.column("age").value(Value.of(30)))
.where(
OrPredicateList.predicate(Predicate.column("age").isLessThan(Value.of(10)))
.or(Predicate.column("age").isGreaterThan(Value.of(65)))
.build())
.and(
OrPredicateList.predicate(Predicate.column("height").isLessThan(Value.of(145.0F)))
.or(Predicate.column("height").isGreaterThan(Value.of(175.0F)))
.build())
.build();
// With LIKE predicates
UpdateStatement statement6 =
StatementBuilder.update("ns", "user")
.set(Assignment.column("age").value(Value.of(30)))
.where(Predicate.column("name").isLike(Value.of("A%")))
.or(Predicate.column("name").isNotLike(Value.of("B_b")))
.build();
// With LIKE predicates with an escape character
UpdateStatement statement7 =
StatementBuilder.update("ns", "user")
.set(Assignment.column("age").value(Value.of(30)))
.where(Predicate.column("name").isLike(Value.of("+%Alice"), Value.of("+")))
.build();
// With IS NULL predicates
UpdateStatement statement8 =
StatementBuilder.update("ns", "user")
.set(Assignment.column("age").value(Value.of(30)))
.where(Predicate.column("name").isNotNull())
.and(Predicate.column("age").isNull())
.build();
// With positional bind markers
UpdateStatement statement9 =
StatementBuilder.update("ns", "user")
.set(Assignment.column("age").value(BindMarker.positional()))
.where(Predicate.column("age").isLessThan(BindMarker.positional()))
.build();
DELETE
The DELETE
command deletes records in the database. You can specify any conditions in the WHERE
clause to filter records. However, specifying a primary key uniquely is recommended as much as possible to avoid the cross-partition operation since it might cause performance and consistency issues, especially in non-JDBC databases. Because ScalarDB SQL creates an execution plan for a DELETE
command that uses a Get
or Scan
operation to identify the target records, the same rule is applied for the selection of records. To understand what kinds of WHERE
clauses cause cross-partition operations and to avoid such operations, see SELECT.
You need to enable the cross-partition scan option if you want to delete all records across partitions without specifying the WHERE
clause. You also need to enable the cross-partition scan with filtering option if you want to flexibly delete records across partitions with arbitrary conditions in the WHERE
clause. For details about configurations, see Cross-partition scan configurations and ScalarDB Cluster SQL configurations.
For non-JDBC databases, transactions could be executed at read-committed snapshot isolation (SNAPSHOT
), which is a lower isolation level, even if you enable cross-partition scan with the SERIALIZABLE
isolation level. When using non-JDBC databases, use cross-partition scan only if consistency does not matter for your transactions.
This command returns the following column:
updateCount
:INT
- the number of deleted records
Grammar
DELETE FROM [<namespace name>.]<table name> [AS <alias>]
[WHERE and_predicates [OR and_predicates ...] | or_predicates [AND or_predicates ...]]
[WITH operation_attributes_or_abac_read_tags_or_abac_write_tags]
identifier: [[<namespace name>.]<table name>.]<column name> | [alias.]<column name>
and_predicates: predicate | (predicate [AND predicate ...])
or_predicates: predicate | (predicate [OR predicate ...])
predicate: <identifier> operator <literal> | <identifier> BETWEEN <literal> AND <literal> | <identifier> [NOT] LIKE <pattern> [ESCAPE <escape character>] | <identifier> IS [NOT] NULL
operator: = | <> | != | > | >= | < | <=
operation_attributes_or_abac_read_tags_or_abac_write_tags: operation_attribute_or_abac_read_tag_or_abac_write_tag [AND operation_attribute_or_abac_read_tag_or_abac_write_tag] ...
operation_attribute_or_abac_read_tag_or_abac_write_tag: operation_attribute | abac_read_tag | abac_write_tag
operation_attribute: <operation attribute name>=<operation attribute value>
abac_read_tag: ABAC_READ_TAG <abac read tag> FOR [POLICY] <abac policy name>
abac_write_tag: ABAC_WRITE_TAG <abac write tag> FOR [POLICY] <abac policy name>
Note
WHERE
clause:
- You can use arbitrary predicates for any columns in the
WHERE
clause. - In the
WHERE
clause, predicates must be an OR-wise ofand_predicates
(known as disjunctive normal form) or an AND-wise ofor_predicates
(known as conjunctive normal form). - When connecting multiple
and_predicates
oror_predicates
, which have more than one predicate, you need to put parentheses aroundand_predicates
andor_predicates
. - You can specify
<literal>
to a bind marker (positional?
and named:<name>
). See the Literal section for the literal syntax. - You cannot specify encrypted columns in the
WHERE
clause.
LIKE
predicate:
_
in<pattern>
matches any single character.%
in<pattern>
matches any sequence of zero or more characters.\
in<pattern>
works as the escape character by default.- You can change the escape character by specifying the
ESCAPE
clause. - You can disable the escape character by specifying an empty escape character,
ESCAPE ''
.
- You can change the escape character by specifying the
Examples with the full primary key specified
If you have the following table, for example:
CREATE TABLE ns.tbl (
c1 INT,
c2 TEXT,
c3 FLOAT,
c4 BIGINT,
c5 BOOLEAN,
PRIMARY KEY (c1, c2, c3)
) WITH CLUSTERING ORDER BY (c2 DESC, c3 ASC);
Examples of DELETE
are as follows:
-- Delete a record
DELETE FROM ns.tbl WHERE c1 = 10 AND c2 = 'aaa' AND c3 = 1.23;
-- With positional bind markers
DELETE FROM tbl WHERE c1 = ? AND c2 = ? AND c3 = ?;
-- With operations attributes
DELETE FROM ns.tbl WHERE c1 = 10 AND c2 = 'aaa' AND c3 = 1.23 WITH 'attribute1' = 'value1' AND 'attribute2' = 'value2';
-- With an ABAC read tag and an ABAC write tag
DELETE FROM ns.tbl WHERE c1 = 10 AND c2 = 'aaa' AND c3 = 1.23 WITH ABAC_READ_TAG 'read_tag1' FOR POLICY policy1 AND ABAC_WRITE_TAG 'write_tag1' FOR POLICY policy1;
-- With operations attributes and ABAC read and write tags
DELETE FROM ns.tbl WHERE c1 = 10 AND c2 = 'aaa' AND c3 = 1.23 WITH 'attribute1' = 'value1' AND 'attribute2' = 'value2' AND ABAC_READ_TAG 'read_tag1' FOR POLICY policy1 AND ABAC_WRITE_TAG 'write_tag1' FOR POLICY policy1;
Examples of building statement objects for DELETE
are as follows:
// Delete a record
DeleteStatement statement1 =
StatementBuilder.deleteFrom("ns", "tbl")
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
.and(Predicate.column("c3").isEqualTo(Value.of(1.23F)))
.build();
// With positional bind markers
DeleteStatement statement2 =
StatementBuilder.deleteFrom("tbl")
.where(Predicate.column("c1").isEqualTo(BindMarker.positional()))
.and(Predicate.column("c2").isEqualTo(BindMarker.positional()))
.and(Predicate.column("c3").isEqualTo(BindMarker.positional()))
.build();
// With operations attributes
DeleteStatement statement3 =
StatementBuilder.deleteFrom("ns", "tbl")
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
.and(Predicate.column("c3").isEqualTo(Value.of(1.23F)))
.withAttribute("attribute1", "value1")
.withAttribute("attribute2", "value2")
.build();
// With an ABAC read tag and an ABAC write tag
DeleteStatement statement4 =
StatementBuilder.deleteFrom("ns", "tbl")
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
.and(Predicate.column("c3").isEqualTo(Value.of(1.23F)))
.withAbacReadTag("read_tag1", "policy1")
.withAbacWriteTag("write_tag1", "policy1")
.build();
// With operations attributes and ABAC read and write tags
DeleteStatement statement5 =
StatementBuilder.deleteFrom("ns", "tbl")
.where(Predicate.column("c1").isEqualTo(Value.of(10)))
.and(Predicate.column("c2").isEqualTo(Value.of("aaa")))
.and(Predicate.column("c3").isEqualTo(Value.of(1.23F)))
.withAttribute("attribute1", "value1")
.withAttribute("attribute2", "value2")
.withAbacReadTag("read_tag1", "policy1")
.withAbacWriteTag("write_tag1", "policy1")
.build();
Examples without the full primary key specified
If you have the following table, for example:
CREATE TABLE ns.user (
id INT,
name TEXT,
age INT,
height FLOAT,
PRIMARY KEY (id)
)
Examples of DELETE
with cross-partition scan filtering are as follows:
-- Without the WHERE clause to delete all the records of a table
DELETE FROM ns.user;
-- With AND predicates for non-primary key columns
DELETE FROM ns.user WHERE age > 10 AND height > 140;
-- With OR predicates for non-primary key columns
DELETE FROM ns.user WHERE age > 10 OR height > 140;
-- With OR-wise of AND predicates
DELETE FROM ns.user WHERE (age > 10 AND height > 150) OR (age > 15 AND height > 145);
-- With AND-wise of OR predicates
DELETE FROM ns.user WHERE (age < 10 OR age > 65) AND (height < 145 OR height > 175);
-- With LIKE predicates
DELETE FROM ns.user WHERE name LIKE 'A%' OR name NOT LIKE 'B_b';
-- With LIKE predicates with an escape character
DELETE FROM ns.user WHERE name LIKE '+%Alice' ESCAPE '+';
-- With IS NULL predicates
DELETE FROM ns.user WHERE name IS NOT NULL AND age IS NULL;
-- With positional bind markers
DELETE FROM ns.user WHERE age < ?;
Examples of building statement objects for DELETE
are as follows:
// Without the WHERE clause to delete all the records of a table
DeleteStatement statement1 = StatementBuilder.deleteFrom("ns", "tbl").build();
// With AND predicates for non-primary key columns
DeleteStatement statement2 =
StatementBuilder.deleteFrom("ns", "tbl")
.where(Predicate.column("age").isGreaterThan(Value.of(10)))
.and(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
.build();
// With OR predicates for non-primary key columns
DeleteStatement statement3 =
StatementBuilder.deleteFrom("ns", "tbl")
.where(Predicate.column("age").isGreaterThan(Value.of(10)))
.or(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
.build();
// With OR-wise of AND predicates
DeleteStatement statement4 =
StatementBuilder.deleteFrom("ns", "tbl")
.where(
AndPredicateList.predicate(Predicate.column("age").isGreaterThan(Value.of(10)))
.and(Predicate.column("height").isGreaterThan(Value.of(140.0F)))
.build())
.or(
AndPredicateList.predicate(Predicate.column("age").isGreaterThan(Value.of(15)))
.and(Predicate.column("height").isGreaterThan(Value.of(145.0F)))
.build())
.build();
// With AND-wise of OR predicates
DeleteStatement statement5 =
StatementBuilder.deleteFrom("ns", "tbl")
.where(
OrPredicateList.predicate(Predicate.column("age").isLessThan(Value.of(10)))
.or(Predicate.column("age").isGreaterThan(Value.of(65)))
.build())
.and(
OrPredicateList.predicate(Predicate.column("height").isLessThan(Value.of(145.0F)))
.or(Predicate.column("height").isGreaterThan(Value.of(175.0F)))
.build())
.build();
// With LIKE predicates
DeleteStatement statement6 =
StatementBuilder.deleteFrom("ns", "tbl")
.where(Predicate.column("name").isLike(Value.of("A%")))
.or(Predicate.column("name").isNotLike(Value.of("B_b")))
.build();
// With LIKE predicates with an escape character
DeleteStatement statement7 =
StatementBuilder.deleteFrom("ns", "tbl")
.where(Predicate.column("name").isLike(Value.of("+%Alice"), Value.of("+")))
.build();
// With IS NULL predicates
DeleteStatement statement8 =
StatementBuilder.deleteFrom("ns", "tbl")
.where(Predicate.column("name").isNotNull())
.and(Predicate.column("age").isNull())
.build();
// With positional bind markers
DeleteStatement statement9 =
StatementBuilder.deleteFrom("ns", "tbl")
.where(Predicate.column("age").isLessThan(BindMarker.positional()))
.build();
DCL
CREATE USER
The CREATE USER
command creates a new user with the specified username, password, and user attributes.
Grammar
CREATE USER <username> [WITH] {PASSWORD <password> | SUPERUSER | NO_SUPERUSER} ...
- If you don't specify a password for a user, the user is created without a password.
- If you specify the
SUPERUSER
attribute for a user, the user becomes a superuser. - If you specify the
NO_SUPERUSER
attribute for a user, the user becomes a normal user. - If you don't specify either
SUPERUSER
orNO_SUPERUSER
for a user, the user becomes a normal user.
Examples
Examples of CREATE USER
are as follows:
-- Create a user with a password as a superuser.
CREATE USER user1 WITH PASSWORD 'password1' SUPERUSER;
-- Create a user with a password.
CREATE USER user2 WITH PASSWORD 'password2';
-- Create a user without a password.
CREATE USER user3;
Examples of building statement objects for CREATE USER
are as follows:
// Create a user with a password and the `SUPERUSER` attribute.
CreateUserStatement statement1 =
StatementBuilder.createUser("user1").with("password", UserOption.SUPERUSER).build();
// Create a user with a password.
CreateUserStatement statement2 = StatementBuilder.createUser("user1").with("password").build();
// Create a user without a password.
CreateUserStatement statement3 = StatementBuilder.createUser("user1").build();
ALTER USER
The ALTER USER
command changes the password and user attributes of the specified user.
Grammar
ALTER USER <username> [WITH] {PASSWORD <password> | SUPERUSER | NO_SUPERUSER} ...
- If you specify the
SUPERUSER
attribute for a user, the user becomes a superuser. - If you specify the
NO_SUPERUSER
attribute for a user, the user becomes a normal user.
Examples
Examples of ALTER USER
are as follows:
-- Change the password of a user.
ALTER USER user1 WITH PASSWORD 'password1';
-- Change a user to a superuser.
ALTER USER user1 WITH SUPERUSER;
Examples of building statement objects for ALTER USER
are as follows:
// Change the password of a user.
AlterUserStatement statement1 = StatementBuilder.alterUser("user1").with("password2").build();
// Change a user to a superuser.
AlterUserStatement statement2 =
StatementBuilder.alterUser("user1").with(UserOption.SUPERUSER).build();
DROP USER
The DROP USER
command deletes the specified user.
Grammar
DROP USER <username>
Examples
An example of DROP USER
is as follows:
-- Delete a user.
DROP USER user1;
An example of building statement objects for DROP USER
is as follows:
// Delete a user.
DropUserStatement statement = StatementBuilder.dropUser("user1").build();
GRANT
The GRANT
command grants privileges to the specified user.
Grammar
GRANT {privilege [, privilege] ... | ALL [PRIVILEGES]} ON [TABLE] <table name> [, <table name>] ... TO [USER] <username> [, <username>] ... [WITH GRANT OPTION]
GRANT {privilege [, privilege] ... | ALL [PRIVILEGES]} ON NAMESPACE <namespace name> [, <namespace name>] ... TO [USER] <username> [, <username>] ... [WITH GRANT OPTION]
privilege: SELECT | INSERT | UPDATE | DELETE | CREATE | DROP | TRUNCATE | ALTER
- You must grant
INSERT
andUPDATE
privileges together. - To grant a user the
UPDATE
orDELETE
privilege, the target user must have theSELECT
privilege. - If you specify the
WITH GRANT OPTION
option, the user can grant privileges to other users.
Examples
Examples of GRANT
are as follows:
-- Grant the SELECT privilege on a table to a user.
GRANT SELECT ON ns.tbl TO user1;
-- Grant the SELECT privilege on tables to users.
GRANT SELECT ON ns.tbl1, ns.tbl2 TO user1, user2;
-- Grant the SELECT privilege on all tables in a namespace to a user.
GRANT SELECT ON NAMESPACE ns TO user1;
-- Grant all privileges and GRANT OPTION on a table to a user.
GRANT ALL ON ns.tbl TO user1 WITH GRANT OPTION;
-- Grant all privileges and GRANT OPTION on all tables in a namespace to a user.
GRANT ALL ON NAMESPACE ns TO user1 WITH GRANT OPTION;
Examples of building statement objects for GRANT
are as follows:
// Grant the SELECT privilege on a table to a user.
GrantStatement statement1 =
StatementBuilder.grant(Privilege.SELECT).onTable("ns", "tbl").toUser("user1").build();
// Grant the SELECT privilege on tables to users.
GrantStatement statement2 =
StatementBuilder.grant(Privilege.SELECT)
.onTable("ns", "tbl1", "ns", "tbl2")
.toUser("user1", "user2")
.build();
// Grant the SELECT privilege on all tables in a namespace to a user.
GrantStatement statement3 =
StatementBuilder.grant(Privilege.SELECT).onNamespace("ns").toUser("user1").build();
// Grant all privileges and GRANT OPTION on a table to a user.
GrantStatement statement4 =
StatementBuilder.grant(Privilege.values())
.onTable("ns", "tbl")
.toUser("user1")
.withGrantOption()
.build();
// Grant all privileges and GRANT OPTION on all tables in a namespace to a user.
GrantStatement statement5 =
StatementBuilder.grant(Privilege.values())
.onNamespace("ns")
.toUser("user1")
.withGrantOption()
.build();
REVOKE
The REVOKE
command revokes privileges from the specified user.
Grammar
REVOKE {privilege [, privilege] ... | ALL [PRIVILEGES]} [, GRANT OPTION] ON [TABLE] <table name> [, <table name>] ... FROM [USER] <username> [, <username>] ...
REVOKE GRANT OPTION ON [TABLE] <table name> [, <table name>] ... FROM [USER] <username> [, <username>] ...
REVOKE {privilege [, privilege] ... | ALL [PRIVILEGES]} [, GRANT OPTION] ON NAMESPACE <namespace name> [, <namespace name>] ... FROM [USER] <username> [, <username>] ...
REVOKE GRANT OPTION ON NAMESPACE <namespace name> [, <namespace name>] ... FROM [USER] <username> [, <username>] ...
privilege: SELECT | INSERT | UPDATE | DELETE | CREATE | DROP | TRUNCATE | ALTER
- You must revoke
INSERT
andUPDATE
privileges together. - If the target user has the
INSERT
orUPDATE
privilege, you cannot revoke theSELECT
privilege from them.
Examples
Examples of REVOKE
are as follows:
-- Revoke the SELECT privilege on a table from a user.
REVOKE SELECT ON ns.tbl FROM user1;
-- Revoke the SELECT privilege on tables from users.
REVOKE SELECT ON ns.tbl1, ns.tbl2 FROM user1, user2;
-- Revoke the SELECT privilege on all tables in a namespace from a user.
REVOKE SELECT ON NAMESPACE ns FROM user1;
-- Revoke all privileges and GRANT OPTION on a table from a user.
REVOKE ALL, GRANT OPTION ON ns.tbl FROM user1;
-- Revoke all privileges and GRANT OPTION on all tables in a namespace from a user.
REVOKE ALL, GRANT OPTION ON NAMESPACE ns FROM user1;
Examples of building statement objects for REVOKE
are as follows:
// Revoke the SELECT privilege on a table from a user.
RevokeStatement statement1 =
StatementBuilder.revoke(Privilege.SELECT).onTable("ns", "tbl").fromUser("user1").build();
// Revoke the SELECT privilege on tables from users.
RevokeStatement statement2 =
StatementBuilder.revoke(Privilege.SELECT)
.onTable("ns", "tbl1", "ns", "tbl2")
.fromUser("user1", "user2")
.build();
// Revoke the SELECT privilege on all tables in a namespace from a user.
RevokeStatement statement3 =
StatementBuilder.revoke(Privilege.SELECT).onNamespace("ns").fromUser("user1").build();
// Revoke all privileges and GRANT OPTION on a table from a user.
RevokeStatement statement4 =
StatementBuilder.revoke(Privilege.values())
.onTable("ns", "tbl")
.fromUser("user1")
.build();
// Revoke all privileges and GRANT OPTION on all tables in a namespace from a user.
RevokeStatement statement5 =
StatementBuilder.revoke(Privilege.values())
.onNamespace("ns")
.fromUser("user1")
.build();
CREATE ABAC_POLICY
The CREATE ABAC_POLICY
command creates a new ABAC policy with the specified policy name and data tag column name. The newly added policy is enabled by default.
Grammar
CREATE ABAC_POLICY <POLICY_NAME> [WITH [DATA_TAG_COLUMN] <DATA_TAG_COLUMN_NAME>];
- If you don't specify the data tag column name, the default data tag column name will be
<POLICY_NAME>_data_tag
.
Examples
Examples of CREATE ABAC_POLICY
are as follows:
-- Create an ABAC policy with the default data tag column name.
CREATE ABAC_POLICY policy1;
-- Create an ABAC policy with a custom data tag column name.
CREATE ABAC_POLICY policy2 WITH DATA_TAG_COLUMN data_tag;
Examples of building statement objects for CREATE ABAC_POLICY
are as follows:
// Create an ABAC policy with the default data tag column name.
CreateAbacPolicyStatement statement1 = StatementBuilder.createAbacPolicy("policy1").build();
// Create an ABAC policy with a custom data tag column name.
CreateAbacPolicyStatement statement2 =
StatementBuilder.createAbacPolicy("policy1")
.withDataTagColumn("data_tag")
.build();
ENABLE ABAC_POLICY
The ENABLE ABAC_POLICY
command enables the specified ABAC policy.
Grammar
ENABLE ABAC_POLICY <POLICY_NAME>;
Examples
An example of ENABLE ABAC_POLICY
is as follows:
-- Enable an ABAC policy.
ENABLE ABAC_POLICY policy1;
An example of building statement objects for ENABLE ABAC_POLICY
is as follows:
// Enable an ABAC policy.
EnableAbacPolicyStatement statement = StatementBuilder.enableAbacPolicy("policy1").build();
DISABLE ABAC_POLICY
The DISABLE ABAC_POLICY
command disables the specified ABAC policy. This command effectively disables ABAC functionality for all namespaces and tables associated with the specified policy.
Grammar
DISABLE ABAC_POLICY <POLICY_NAME>;
Examples
An example of DISABLE ABAC_POLICY
is as follows:
-- Disable an ABAC policy.
DISABLE ABAC_POLICY policy1;
An example of building statement objects for DISABLE ABAC_POLICY
is as follows:
// Disable an ABAC policy.
DisableAbacPolicyStatement statement = StatementBuilder.disableAbacPolicy("policy1").build();
CREATE ABAC_LEVEL
The CREATE ABAC_LEVEL
command creates a new ABAC level with the specified level short name, long name, and level number in the specified policy.
Grammar
CREATE ABAC_LEVEL <LEVEL_SHORT_NAME> WITH [LONG_NAME] <LEVEL_LONG_NAME> AND [LEVEL_NUMBER] <LEVEL_NUMBER> IN [POLICY] <POLICY_NAME>;
Examples
An example of CREATE ABAC_LEVEL
are as follows:
-- Create an ABAC level.
CREATE ABAC_LEVEL l1 WITH LONG_NAME level1 AND LEVEL_NUMBER 10 IN POLICY policy1;
An example of building statement objects for CREATE ABAC_LEVEL
are as follows:
// Create an ABAC level.
CreateAbacLevelStatement statement =
StatementBuilder.createAbacLevel("l1")
.withLongName("level1")
.andLevelNumber(10)
.inPolicy("policy1")
.build();
DROP ABAC_LEVEL
The DROP ABAC_LEVEL
command drops the specified ABAC level in the specified policy.
Grammar
DROP ABAC_LEVEL <LEVEL_SHORT_NAME> IN [POLICY] <POLICY_NAME>;
Examples
An example of DROP ABAC_LEVEL
is as follows:
-- Drop an ABAC level.
DROP ABAC_LEVEL l1 IN POLICY policy1;
An example of building statement objects for DROP ABAC_LEVEL
is as follows:
// Drop an ABAC level.
DropAbacLevelStatement statement =
StatementBuilder.dropAbacLevel("l1").inPolicy("policy1").build();
CREATE ABAC_COMPARTMENT
The CREATE ABAC_COMPARTMENT
command creates a new ABAC compartment with the specified compartment short name and long name in the specified policy.
Grammar
CREATE ABAC_COMPARTMENT <COMPARTMENT_SHORT_NAME> WITH [LONG_NAME] <COMPARTMENT_LONG_NAME> IN [POLICY] <POLICY_NAME>;
Examples
An example of CREATE ABAC_COMPARTMENT
are as follows:
-- Create an ABAC compartment.
CREATE ABAC_COMPARTMENT c1 WITH LONG_NAME compartment1 IN POLICY policy1;
An example of building statement objects for CREATE ABAC_COMPARTMENT
are as follows:
// Create an ABAC compartment.
CreateAbacCompartmentStatement statement =
StatementBuilder.createAbacCompartment("c1")
.withLongName("compartment1")
.inPolicy("policy1")
.build();
DROP ABAC_COMPARTMENT
The DROP ABAC_COMPARTMENT
command drops the specified ABAC compartment in the specified policy.
Grammar
DROP ABAC_COMPARTMENT <COMPARTMENT_SHORT_NAME> IN [POLICY] <POLICY_NAME>;
Examples
An example of DROP ABAC_COMPARTMENT
is as follows:
-- Drop an ABAC compartment.
DROP ABAC_COMPARTMENT c1 IN POLICY policy1;
An example of building statement objects for DROP ABAC_COMPARTMENT
is as follows:
// Drop an ABAC compartment.
DropAbacCompartmentStatement statement =
StatementBuilder.dropAbacCompartment("c1").inPolicy("policy1").build();
CREATE ABAC_GROUP
The CREATE ABAC_GROUP
command creates a new ABAC group with the specified short name, long name, and optionally a parent group, within the specified policy.
Grammar
CREATE ABAC_GROUP <GROUP_SHORT_NAME> WITH [LONG_NAME] <GROUP_LONG_NAME> [AND [PARENT_GROUP] <PARENT_GROUP_SHORT_NAME>] IN [POLICY] <POLICY_NAME>;
Examples
Examples of CREATE ABAC_GROUP
are as follows:
-- Create an ABAC group with a parent group.
CREATE ABAC_GROUP g1 WITH LONG_NAME group1 AND PARENT_GROUP g0 IN POLICY policy1;
-- Create an ABAC group without a parent group.
CREATE ABAC_GROUP g2 WITH LONG_NAME group2 IN POLICY policy1;
Examples of building statement objects for CREATE ABAC_GROUP
are as follows:
// Create an ABAC group with a parent group.
CreateAbacGroupStatement statement1 =
StatementBuilder.createAbacGroup("g1")
.withLongName("group1")
.andParentGroup("g0")
.inPolicy("policy1")
.build();
// Create an ABAC group without a parent group.
CreateAbacGroupStatement statement2 =
StatementBuilder.createAbacGroup("g2").withLongName("group2").inPolicy("policy1").build();
DROP ABAC_GROUP
The DROP ABAC_GROUP
command drops the specified ABAC group in the specified policy.
Grammar
DROP ABAC_GROUP <GROUP_SHORT_NAME> IN [POLICY] <POLICY_NAME>;
Examples
An example of DROP ABAC_GROUP
is as follows:
-- Drop an ABAC group.
DROP ABAC_GROUP g1 IN POLICY policy1;
An example of building statement objects for DROP ABAC_GROUP
is as follows:
// Drop an ABAC group.
DropAbacGroupStatement statement =
StatementBuilder.dropAbacGroup("g1").inPolicy("policy1").build();
SET ABAC_LEVEL
The SET ABAC_LEVEL
command sets the specified ABAC levels for the specified user in the specified policy.
Grammar
SET ABAC_LEVEL <LEVEL_SHORT_NAME> [AND [DEFAULT_LEVEL] <DEFAULT_LEVEL_SHORT_NAME>] [AND [ROW_LEVEL] <ROW_LEVEL_SHORT_NAME>] FOR [USER] <USER_NAME> IN [POLICY] <POLICY_NAME>;
- If you omit the default level, the level specified by
<LEVEL_SHORT_NAME>
will be used as the default level. - If you omit the row level, the default level will be used as the row level.
Examples
Examples of SET ABAC_LEVEL
are as follows:
-- Set the levels for a user.
SET ABAC_LEVEL l1 AND DEFAULT_LEVEL l2 AND ROW_LEVEL l3 FOR USER user1 IN POLICY policy1;
-- Set the levels for a user without a row level.
SET ABAC_LEVEL l1 AND DEFAULT_LEVEL l2 FOR USER user1 IN POLICY policy1;
-- Set the levels for a user without a default level and a row level.
SET ABAC_LEVEL l1 FOR USER user1 IN POLICY policy1;
Examples of building statement objects for SET ABAC_LEVEL
are as follows:
// Set the levels for a user.
SetAbacLevelsForUserStatement statement1 =
StatementBuilder.setAbacLevel("l1")
.andDefaultLevel("l2")
.andRowLevel("l3")
.forUser("user1")
.inPolicy("policy1")
.build();
// Set the levels for a user without a row level.
SetAbacLevelsForUserStatement statement2 =
StatementBuilder.setAbacLevel("l1")
.andDefaultLevel("l2")
.forUser("user1")
.inPolicy("policy1")
.build();
// Set the levels for a user without a default level and a row level.
SetAbacLevelsForUserStatement statement3 =
StatementBuilder.setAbacLevel("l1").forUser("user1").inPolicy("policy1").build();
ADD ABAC_COMPARTMENT
The ADD ABAC_COMPARTMENT
command adds the specified ABAC compartment to the specified user in the specified policy.
Grammar
ADD ABAC_COMPARTMENT <COMPARTMENT_SHORT_NAME> TO [USER] <USER_NAME> [WITH {accessMode [AND default] [AND row] | default [AND row] | row}] IN [POLICY] <POLICY_NAME>;
accessMode: READ_ONLY_ACCESS | READ_WRITE_ACCESS
default: DEFAULT [= {TRUE | FALSE}]
row: ROW [= {TRUE | FALSE}]
- If you omit the access mode, the default access mode will be
READ_ONLY_ACCESS
. - If you omit the default part, the compartment will be in the default compartments.
- If you omit the row part, the compartment won't be in the row compartments.
- If you omit the boolean literal for
default
orrow
, the value ofdefault
orrow
will be set toTRUE
.
Examples
Examples of ADD ABAC_COMPARTMENT
are as follows:
-- Add a compartment to a user without specifying the access mode, default, and row.
ADD ABAC_COMPARTMENT c1 TO USER user1 IN POLICY policy1;
-- Add a compartment to a user with the READ_WRITE_ACCESS access mode.
ADD ABAC_COMPARTMENT c1 TO USER user1 WITH READ_WRITE_ACCESS IN POLICY policy1;
-- Add a compartment to a user with specifying the default.
ADD ABAC_COMPARTMENT c1 TO USER user1 WITH DEFAULT IN POLICY policy1;
-- Add a compartment to a user with specifying the row to TRUE.
ADD ABAC_COMPARTMENT c1 TO USER user1 WITH ROW = TRUE IN POLICY policy1;
Examples of building statement objects for ADD ABAC_COMPARTMENT
are as follows:
// Add a compartment to a user without specifying the access mode, default, and row.
AddAbacCompartmentToUserStatement statement1 =
StatementBuilder.addAbacCompartment("c1").toUser("user1").inPolicy("policy1").build();
// Add a compartment to a user with the READ_WRITE_ACCESS access mode.
AddAbacCompartmentToUserStatement statement2 =
StatementBuilder.addAbacCompartment("c1")
.toUser("user1")
.withReadWriteAccess()
.inPolicy("policy1")
.build();
// Add a compartment to a user with specifying the default.
AddAbacCompartmentToUserStatement statement3 =
StatementBuilder.addAbacCompartment("c1")
.toUser("user1")
.withDefault()
.inPolicy("policy1")
.build();
// Add a compartment to a user with specifying the row to TRUE.
AddAbacCompartmentToUserStatement statement4 =
StatementBuilder.addAbacCompartment("c1")
.toUser("user1")
.withRow(true)
.inPolicy("policy1")
.build();
REMOVE ABAC_COMPARTMENT
The REMOVE ABAC_COMPARTMENT
command removes the specified compartment from the specified user in the specified policy.
Grammar
REMOVE ABAC_COMPARTMENT <COMPARTMENT_SHORT_NAME> FROM [USER] <USER_NAME> IN [POLICY] <POLICY_NAME>;
Examples
An example of REMOVE ABAC_COMPARTMENT
is as follows:
-- Remove a compartment from a user.
REMOVE ABAC_COMPARTMENT c1 FROM USER user1 IN POLICY policy1;
An example of building statement objects for REMOVE ABAC_COMPARTMENT
is as follows:
// Remove a compartment from a user.
RemoveAbacCompartmentFromUserStatement statement =
StatementBuilder.removeAbacCompartment("c1").fromUser("user1").inPolicy("policy1").build();
ADD ABAC_GROUP
The ADD ABAC_GROUP
command adds the specified ABAC group to the specified user in the specified policy.
Grammar
ADD ABAC_GROUP <GROUP_SHORT_NAME> TO [USER] <USER_NAME> [WITH {accessMode [AND default] [AND row] | default [AND row] | row}] IN [POLICY] <POLICY_NAME>;
accessMode: READ_ONLY_ACCESS | READ_WRITE_ACCESS
default: DEFAULT [= {TRUE | FALSE}]
row: ROW [= {TRUE | FALSE}]
- If you omit the access mode, the default access mode will be
READ_ONLY_ACCESS
. - If you omit the default part, the group will be in the default compartments.
- If you omit the row part, the group won't be in the row compartments.
- If you omit the boolean literal for
default
orrow
, the value ofdefault
orrow
will be set toTRUE
.
Examples
Examples of ADD ABAC_GROUP
are as follows:
-- Add a group to a user without specifying the access mode, default, and row.
ADD ABAC_GROUP g1 TO USER user1 IN POLICY policy1;
-- Add a group to a user with the READ_WRITE_ACCESS access mode.
ADD ABAC_GROUP g1 TO USER user1 WITH READ_WRITE_ACCESS IN POLICY policy1;
-- Add a group to a user with specifying the default.
ADD ABAC_GROUP g1 TO USER user1 WITH DEFAULT IN POLICY policy1;
-- Add a group to a user with specifying the row to TRUE.
ADD ABAC_GROUP g1 TO USER user1 WITH ROW = TRUE IN POLICY policy1;
Examples of building statement objects for ADD ABAC_GROUP_TO_USER
are as follows:
// Add a group to a user without specifying the access mode, default, and row.
AddAbacGroupToUserStatement statement1 =
StatementBuilder.addAbacGroup("g1").toUser("user1").inPolicy("policy1").build();
// Add a group to a user with the READ_WRITE_ACCESS access mode.
AddAbacGroupToUserStatement statement2 =
StatementBuilder.addAbacGroup("g1")
.toUser("user1")
.withReadWriteAccess()
.inPolicy("policy1")
.build();
// Add a group to a user with specifying the default.
AddAbacGroupToUserStatement statement3 =
StatementBuilder.addAbacGroup("g1")
.toUser("user1")
.withDefault()
.inPolicy("policy1")
.build();
// Add a group to a user with specifying the row to TRUE.
AddAbacGroupToUserStatement statement4 =
StatementBuilder.addAbacGroup("g1")
.toUser("user1")
.withRow(true)
.inPolicy("policy1")
.build();
REMOVE ABAC_GROUP
The REMOVE ABAC_GROUP
command removes the specified ABAC group from the specified user in the specified policy.
Grammar
REMOVE ABAC_GROUP <GROUP_SHORT_NAME> FROM [USER] <USER_NAME> IN [POLICY] <POLICY_NAME>;
Examples
An example of REMOVE ABAC_GROUP
is as follows:
-- Remove a group from a user.
REMOVE ABAC_GROUP g1 FROM USER user1 IN POLICY policy1;
An example of building statement objects for REMOVE ABAC_GROUP
is as follows:
// Remove a group from a user.
RemoveAbacGroupFromUserStatement statement =
StatementBuilder.removeAbacGroup("g1").fromUser("user1").inPolicy("policy1").build();
DROP ABAC_USER_TAG_INFO
The DROP ABAC_USER_TAG_INFO
command drops the specified ABAC user tag information from the specified user in the specified policy.
Grammar
DROP ABAC_USER_TAG_INFO FROM [USER] <USER_NAME> IN [POLICY] <POLICY_NAME>;
Examples
An example of DROP ABAC_USER_TAG_INFO
is as follows:
-- Drop user tag information from a user.
DROP ABAC_USER_TAG_INFO FROM USER user1 IN POLICY policy1;
An example of building statement objects for DROP ABAC_USER_TAG_INFO
is as follows:
// Drop user tag information from a user.
DropAbacUserTagInfoFromUserStatement statement =
StatementBuilder.dropAbacUserTagInfoFromUser("user1").inPolicy("policy1").build();
CREATE ABAC_NAMESPACE_POLICY
The CREATE ABAC_NAMESPACE_POLICY
command creates a new ABAC namespace policy, with the specified namespace policy name using the specified policy and namespace. This command is used to apply the policy to the specified namespace. The newly added namespace policy is enabled by default.
Grammar
CREATE ABAC_NAMESPACE_POLICY <NAMESPACE POLICY_NAME> USING [POLICY] <POLICY_NAME> AND [NAMESPACE] <NAMESPACE_NAME>;
Examples
An example of CREATE ABAC_NAMESPACE_POLICY
is as follows:
-- Create an ABAC namespace policy.
CREATE ABAC_NAMESPACE_POLICY ns_policy1 USING POLICY policy1 AND NAMESPACE ns;
An example of building statement objects for CREATE ABAC_NAMESPACE_POLICY
is as follows:
// Create an ABAC namespace policy.
CreateAbacNamespacePolicyStatement statement =
StatementBuilder.createAbacNamespacePolicy("ns_policy1")
.usingPolicy("policy1")
.andNamespace("ns")
.build();
ENABLE ABAC_NAMESPACE_POLICY
The ENABLE ABAC_NAMESPACE_POLICY
command enables the specified ABAC namespace policy.
Grammar
ENABLE ABAC_NAMESPACE_POLICY <NAMESPACE POLICY_NAME>;
Examples
An example of ENABLE ABAC_NAMESPACE_POLICY
is as follows:
-- Enable an ABAC namespace policy.
ENABLE ABAC_NAMESPACE_POLICY ns_policy1;
An example of building statement objects for ENABLE ABAC_NAMESPACE_POLICY
is as follows:
// Enable an ABAC namespace policy.
EnableAbacNamespacePolicyStatement statement =
StatementBuilder.enableAbacNamespacePolicy("ns_policy1").build();
DISABLE ABAC_NAMESPACE_POLICY
The DISABLE ABAC_NAMESPACE_POLICY
command disables the specified ABAC namespace policy. This command effectively disables ABAC functionality for the namespace associated with the specified namespace policy.
Grammar
DISABLE ABAC_NAMESPACE_POLICY <NAMESPACE POLICY_NAME>;
Examples
An example of DISABLE ABAC_NAMESPACE_POLICY
is as follows:
-- Disable an ABAC namespace policy.
DISABLE ABAC_NAMESPACE_POLICY ns_policy1;
An example of building statement objects for DISABLE ABAC_NAMESPACE_POLICY
is as follows:
// Disable an ABAC namespace policy.
DisableAbacNamespacePolicyStatement statement =
StatementBuilder.disableAbacNamespacePolicy("ns_policy1").build();
CREATE ABAC_TABLE_POLICY
The CREATE ABAC_TABLE_POLICY
command creates a new ABAC table policy, with the specified table policy name using the specified policy and table. This command is used to apply the policy to the specified table. The newly added table policy is enabled by default.
Grammar
CREATE ABAC_TABLE_POLICY <TABLE_POLICY_NAME> USING [POLICY] <POLICY_NAME> AND [TABLE] <NAMESPACE_NAME>.<TABLE_NAME>;
Examples
An example of CREATE ABAC_TABLE_POLICY
is as follows:
-- Create an ABAC table policy.
CREATE ABAC_TABLE_POLICY tbl_policy1 USING POLICY policy1 AND TABLE ns.tbl;
An example of building statement objects for CREATE ABAC_TABLE_POLICY
is as follows:
// Create an ABAC table policy.
CreateAbacTablePolicyStatement statement =
StatementBuilder.createAbacTablePolicy("tbl_policy1")
.usingPolicy("policy1")
.andTable("ns", "tbl")
.build();
ENABLE ABAC_TABLE_POLICY
The ENABLE ABAC_TABLE_POLICY
command enables the specified ABAC table policy.
Grammar
ENABLE ABAC_TABLE_POLICY <TABLE_POLICY_NAME>;
Examples
An example of ENABLE ABAC_TABLE_POLICY
is as follows:
-- Enable an ABAC table policy.
ENABLE ABAC_TABLE_POLICY tbl_policy1;
An example of building statement objects for ENABLE ABAC_TABLE_POLICY
is as follows:
// Enable an ABAC table policy.
EnableAbacTablePolicyStatement statement =
StatementBuilder.enableAbacTablePolicy("tbl_policy1").build();
DISABLE ABAC_TABLE_POLICY
The DISABLE ABAC_TABLE_POLICY
command disables the specified ABAC table policy. This command effectively disables ABAC functionality for the table associated with the specified table policy.
Grammar
DISABLE ABAC_TABLE_POLICY <TABLE_POLICY_NAME>;
Examples
An example of DISABLE ABAC_TABLE_POLICY
is as follows:
-- Disable an ABAC table policy.
DISABLE ABAC_TABLE_POLICY tbl_policy1;
An example of building statement objects for DISABLE ABAC_TABLE_POLICY
is as follows:
// Disable an ABAC table policy.
DisableAbacTablePolicyStatement statement =
StatementBuilder.disableAbacTablePolicy("tbl_policy1").build();
Others
USE
The USE
command specifies a default namespace. If a namespace name is omitted in a SQL statement, the default namespace will be used.
Grammar
USE <namespace name>
Examples
An example of USE
is as follows:
-- Specify a default namespace name "ns"
USE ns;
An example of building statement objects for USE
is as follows:
// Specify a default namespace name "ns"
UseStatement statement = StatementBuilder.use("ns").build();
BEGIN
The BEGIN
command begins a transaction.
This command returns the following column:
transactionId
:TEXT
- a transaction ID associated with the transaction you have begun
Grammar
BEGIN
Examples
An example of building statement objects for BEGIN
is as follows:
// Begin a transaction
BeginStatement statement = StatementBuilder.begin().build();
START TRANSACTION
The START TRANSACTION
command starts a transaction. This command is an alias of BEGIN
.
This command returns the following column:
transactionId
:TEXT
- the transaction ID associated with the transaction you have started
Grammar
START TRANSACTION
Examples
An example of building statement objects for START TRANSACTION
is as follows:
// Start a transaction.
StartTransactionStatement statement = StatementBuilder.startTransaction().build();
JOIN
The JOIN
command joins a transaction associated with the specified transaction ID.
Grammar
JOIN <transaction ID>
Examples
An example of JOIN
is as follows:
-- Join a transaction
JOIN 'id';
An example of building statement objects for JOIN
is as follows:
// Join a transaction
JoinStatement statement = StatementBuilder.join("id").build();
PREPARE
The PREPARE
command prepares the current transaction.
Grammar
PREPARE
Examples
An example of building statement objects for PREPARE
is as follows:
// Prepare the current transaction
PrepareStatement statement = StatementBuilder.prepare().build();
VALIDATE
The VALIDATE
command validates the current transaction.
Grammar
VALIDATE
Examples
An example of building statement objects for VALIDATE
is as follows:
// Validate the current transaction
ValidateStatement statement = StatementBuilder.validate().build();
COMMIT
The COMMIT
command commits the current transaction.
Grammar
COMMIT
Examples
An example of building statement objects for COMMIT
is as follows:
// Commit the current transaction
CommitStatement statement = StatementBuilder.commit().build();
ROLLBACK
The ROLLBACK
command rolls back the current transaction.
Grammar
ROLLBACK
Examples
An example of building statement objects for ROLLBACK
is as follows:
// Rollback the current transaction
RollbackStatement statement = StatementBuilder.rollback().build();
ABORT
The ABORT
command rolls back the current transaction. This command is an alias of ROLLBACK
.
Grammar
ABORT
Examples
An example of building statement objects for ABORT
is as follows:
// Abort the current transaction.
AbortStatement statement = StatementBuilder.abort().build();
SET MODE
The SET MODE
command switches the current transaction mode.
Grammar
SET MODE transaction_mode
transaction_mode: TRANSACTION | TWO_PHASE_COMMIT_TRANSACTION
Examples
An example of SET MODE
is as follows:
-- Switch the current transaction mode to Two-phase Commit Transaction
SET MODE TWO_PHASE_COMMIT_TRANSACTION;
An example of building statement objects for SET MODE
is as follows:
// Switch the current transaction mode to Two-phase Commit Transaction
SetModeStatement statement =
StatementBuilder.setMode(TransactionMode.TWO_PHASE_COMMIT_TRANSACTION).build();
SHOW NAMESPACES
The SHOW NAMESPACES
command shows namespace names.
This command extracts the namespace names of user tables dynamically. As a result, only namespaces that contain tables are returned. Starting from ScalarDB 4.0, we plan to improve the design to remove this limitation.
This command returns the following column:
namespaceName
:TEXT
- the namespace name
Grammar
SHOW NAMESPACES
Examples
An example of building statement objects for SHOW NAMESPACES
is as follows:
// Show namespace names.
ShowNamespacesStatement statement = StatementBuilder.showNamespaces().build();
SHOW TABLES
The SHOW TABLES
command shows table names in a namespace. If a namespace name is omitted, the default namespace will be used.
This command returns the following column:
tableName
:TEXT
- a table name
Grammar
SHOW TABLES [FROM <namespace name>]
Examples
Examples of SHOW TABLES
is as follows:
-- Show table names in the default namespace
SHOW TABLES;
-- Show table names in a namespace "ns"
SHOW TABLES FROM ns;
Examples of building statement objects for SHOW TABLES
is as follows:
// Show table names in the default namespace
ShowTablesStatement statement1 = StatementBuilder.showTables().build();
// Show table names in a namespace "ns"
ShowTablesStatement statement2 = StatementBuilder.showTables().from("ns").build();
DESCRIBE
The DESCRIBE
command returns column metadata for the specified table.
This command returns the following columns:
columnName
:TEXT
- a table nametype
:TEXT
- a type nameisPrimaryKey
:BOOLEAN
- whether it's a column part of primary keyisPartitionKey
:BOOLEAN
- whether it's a column part of partition keyisClusteringKey
:BOOLEAN
- whether it's a column part of clustering keyclusteringOrder
:TEXT
- a clustering orderisIndexed
:BOOLEAN
- whether it's an indexed column
Grammar
DESCRIBE [<namespace name>.]<table name>
DESC [<namespace name>.]<table name>
Examples
Examples of DESCRIBE
is as follows:
-- Returns column metadata for "ns.tbl"
DESCRIBE ns.tbl;
-- Returns column metadata for "tbl"
DESC tbl;
Examples of building statement objects for DESCRIBE
is as follows:
// Returns column metadata for "ns.tbl"
DescribeStatement statement1 = StatementBuilder.describe("ns", "tbl").build();
// Returns column metadata for "tbl"
DescribeStatement statement2 = StatementBuilder.describe("tbl").build();
SUSPEND
The SUSPEND
command suspends the ongoing transaction in the current session.
Grammar
SUSPEND
Examples
Examples of building statement objects for SUSPEND
is as follows:
// Suspend the ongonig transaction in the current session
SuspendStatement statement = StatementBuilder.suspend().build();
RESUME
The RESUME
command resumes the transaction associated with the specified transaction ID in the current session.
Grammar
RESUME <transaction ID>
Examples
An example of RESUME
is as follows:
-- Resume a transaction
RESUME 'id';
An example of building statement objects for RESUME
is as follows:
// Resume a transaction
ResumeStatement statement = StatementBuilder.resume("id").build();
SHOW USERS
The SHOW USERS
command shows usernames and user attributes. If you are a superuser, you can see all users. If you are a normal user, you can see only your own username and attributes.
This command returns the following columns:
username
:TEXT
- a usernameisSuperuser
:BOOLEAN
- whether the user is a superuser
Grammar
SHOW USERS
Examples
An example of SHOW USERS
is as follows:
-- Show usernames and user attributes
SHOW USERS;
An example of building statement objects for SHOW USERS
is as follows:
// Show usernames and user attributes
ShowUsersStatement statement = StatementBuilder.showUsers().build();
SHOW GRANTS
The SHOW GRANTS
command shows the privileges granted to the current user or the specified user.
This command returns the following columns:
name
:TEXT
- a namespace name or a table name, depending on the typetype
:TEXT
- the type of the object (eitherNAMESPACE
orTABLE
)privilege
:TEXT
- a privilege
Grammar
-- Show privileges granted to the current user
SHOW GRANTS
-- Show privileges granted to the specified user
SHOW GRANTS FOR [USER] <username>
Examples
Examples of SHOW GRANTS
are as follows:
-- Show privileges granted to the current user
SHOW GRANTS;
-- Show privileges granted to user1
SHOW GRANTS FOR user1;
Examples of building statement objects for SHOW GRANTS
are as follows:
// Show privileges granted to the current user
ShowGrantsStatement statement1 = StatementBuilder.showGrants().build();
// Show privileges granted to user1
ShowGrantsStatement statement2 = StatementBuilder.showGrants().forUser("user1").build();
SHOW ABAC_POLICY
The SHOW ABAC_POLICY
command shows the specified ABAC policy.
This command returns the following columns:
name
:TEXT
- the policy namedataTagColumnName
:TEXT
- the data tag column namestate
:TEXT
- the state of the policy (eitherENABLED
orDISABLED
)
Grammar
SHOW ABAC_POLICY <POLICY_NAME>;
Examples
An example of SHOW ABAC_POLICY
is as follows:
-- Show the specified ABAC policy
SHOW ABAC_POLICY policy1;
An example of building statement objects for SHOW ABAC_POLICY
is as follows:
// Show the specified ABAC policy
ShowAbacPolicyStatement statement = StatementBuilder.showAbacPolicy("policy1").build();
SHOW ABAC_POLICIES
The SHOW ABAC_POLICIES
command shows all ABAC policies.
This command returns the following columns:
name
:TEXT
- the policy namedataTagColumnName
:TEXT
- the data tag column namestate
:TEXT
- the state of the policy (eitherENABLED
orDISABLED
)
Grammar
SHOW ABAC_POLICIES;
Examples
An example of SHOW ABAC_POLICIES
is as follows:
-- Show all ABAC policies
SHOW ABAC_POLICIES;
An example of building statement objects for SHOW ABAC_POLICIES
is as follows:
// Show all ABAC policies
ShowAbacPoliciesStatement statement = StatementBuilder.showAbacPolicies().build();
SHOW ABAC_LEVEL
The SHOW ABAC_LEVEL
command shows the specified ABAC level in the specified policy.
This command returns the following columns:
policyName
:TEXT
- the policy nameshortName
:TEXT
- the short name of the levellongName
:TEXT
- the long name of the levellevelNumber
:INTEGER
- the level number of the level
Grammar
SHOW ABAC_LEVEL <LEVEL_SHORT_NAME> IN [POLICY] <POLICY_NAME>;
Examples
An example of SHOW ABAC_LEVEL
is as follows:
-- Show the specified ABAC level in the specified policy
SHOW ABAC_LEVEL l1 IN POLICY policy1;
An example of building statement objects for SHOW ABAC_LEVEL
is as follows:
// Show the specified ABAC level in the specified policy
ShowAbacLevelStatement statement = StatementBuilder.showAbacLevel("l1").inPolicy("policy1").build();
SHOW ABAC_LEVELS
The SHOW ABAC_LEVELS
command shows all ABAC levels in the specified policy.
This command returns the following columns:
policyName
:TEXT
- the policy nameshortName
:TEXT
- the short name of the levellongName
:TEXT
- the long name of the levellevelNumber
:INTEGER
- the level number of the level
Grammar
SHOW ABAC_LEVELS IN [POLICY] <POLICY_NAME>;
Examples
An example of SHOW ABAC_LEVELS
is as follows:
-- Show all ABAC levels in the specified policy
SHOW ABAC_LEVELS IN POLICY policy1;
An example of building statement objects for SHOW ABAC_LEVELS
is as follows:
// Show all ABAC levels in the specified policy
ShowAbacLevelsStatement statement = StatementBuilder.showAbacLevelsInPolicy("policy1").build();
SHOW ABAC_COMPARTMENT
The SHOW ABAC_COMPARTMENT
command shows the specified ABAC compartment in the specified policy.
This command returns the following columns:
policyName
:TEXT
- the policy nameshortName
:TEXT
- the short name of the compartmentlongName
:TEXT
- the long name of the compartment
Grammar
SHOW ABAC_COMPARTMENT <COMPARTMENT_SHORT_NAME> IN [POLICY] <POLICY_NAME>;
Examples
An example of SHOW ABAC_COMPARTMENT
is as follows:
-- Show the specified ABAC compartment in the specified policy
SHOW ABAC_COMPARTMENT c1 IN POLICY policy1;
An example of building statement objects for SHOW ABAC_COMPARTMENT
is as follows:
// Show the specified ABAC compartment in the specified policy
ShowAbacCompartmentStatement statement =
StatementBuilder.showAbacCompartment("c1").inPolicy("policy1").build();
SHOW ABAC_COMPARTMENTS
The SHOW ABAC_COMPARTMENTS
command shows all ABAC compartments in the specified policy.
This command returns the following columns:
policyName
:TEXT
- the policy nameshortName
:TEXT
- the short name of the compartmentlongName
:TEXT
- the long name of the compartment
Grammar
SHOW ABAC_COMPARTMENTS IN [POLICY] <POLICY_NAME>;
Examples
An example of SHOW ABAC_COMPARTMENTS
is as follows:
-- Show all ABAC compartments in the specified policy
SHOW ABAC_COMPARTMENTS IN POLICY policy1;
An example of building statement objects for SHOW ABAC_COMPARTMENTS
is as follows:
// Show all ABAC compartments in the specified policy
ShowAbacCompartmentsStatement statement =
StatementBuilder.showAbacCompartmentsInPolicy("policy1").build();
SHOW ABAC_GROUP
The SHOW ABAC_GROUP
command shows the specified ABAC group in the specified policy.
This command returns the following columns:
policyName
:TEXT
- the policy nameshortName
:TEXT
- the short name of the grouplongName
:TEXT
- the long name of the groupparentGroupShortName
:TEXT
- the short name of the parent group. If the group has no parent group, this column will benull
Grammar
SHOW ABAC_GROUP <GROUP_SHORT_NAME> IN [POLICY] <POLICY_NAME>;
Examples
An example of SHOW ABAC_GROUP
is as follows:
-- Show the specified ABAC group in the specified policy
SHOW ABAC_GROUP g1 IN POLICY policy1;
An example of building statement objects for SHOW ABAC_GROUP
is as follows:
// Show the specified ABAC group in the specified policy
ShowAbacGroupStatement statement =
StatementBuilder.showAbacGroup("g1").inPolicy("policy1").build();
SHOW ABAC_GROUPS
The SHOW ABAC_GROUPS
command shows all ABAC groups in the specified policy.
This command returns the following columns:
policyName
:TEXT
- the policy nameshortName
:TEXT
- the short name of the grouplongName
:TEXT
- the long name of the groupparentGroupShortName
:TEXT
- the short name of the parent group. If the group has no parent group, this column will benull
Grammar
SHOW ABAC_GROUPS IN [POLICY] <POLICY_NAME>;
Examples
An example of SHOW ABAC_GROUPS
is as follows:
-- Show all ABAC groups in the specified policy
SHOW ABAC_GROUPS IN POLICY policy1;
An example of building statement objects for SHOW ABAC_GROUPS
is as follows:
// Show all ABAC groups in the specified policy
ShowAbacGroupsStatement statement = StatementBuilder.showAbacGroupsInPolicy("policy1").build();
SHOW ABAC_USER_TAG_INFO
The SHOW ABAC_USER_TAG_INFO
command shows the ABAC user tag information for the specified user in the specified policy.
This command returns the following columns:
policyName
:TEXT
- the policy nameusername
:TEXT
- the usernamereadTag
:TEXT
- the read tagwriteTag
:TEXT
- the write tagdefaultReadTag
:TEXT
- the default read tagdefaultWriteTag
:TEXT
- the default write tagrowTag
:TEXT
- the row tag
Grammar
SHOW ABAC_USER_TAG_INFO [FOR USER <USER_NAME>] IN [POLICY] <POLICY_NAME>;
- If you omit the user, the user tag info for the current user will be displayed.
Examples
Examples of SHOW ABAC_USER_TAG_INFO
are as follows:
-- Show the ABAC user tag information for the current user in the specified policy
SHOW ABAC_USER_TAG_INFO IN POLICY policy1;
-- Show the ABAC user tag information for user1 in the specified policy
SHOW ABAC_USER_TAG_INFO FOR USER user1 IN POLICY policy1;
Examples of building statement objects for SHOW ABAC_USER_TAG_INFO
are as follows:
// Show the ABAC user tag information for the current user in the specified policy
ShowAbacUserTagInfoStatement statement1 =
StatementBuilder.showAbacUserTagInfo().inPolicy("policy1").build();
// Show the ABAC user tag information for user1 in the specified policy
ShowAbacUserTagInfoStatement statement2 =
StatementBuilder.showAbacUserTagInfo().forUser("user1").inPolicy("policy1").build();
SHOW ABAC_NAMESPACE_POLICY
The SHOW ABAC_NAMESPACE_POLICY
command shows the specified ABAC namespace policy.
This command returns the following columns:
name
:TEXT
- the name of the namespace policypolicyName
:TEXT
- the policy namenamespaceName
:TEXT
- the namespace namestate
:TEXT
- the state of the policy (eitherENABLED
orDISABLED
)
Grammar
SHOW ABAC_NAMESPACE_POLICY <NAMESPACE_POLICY_NAME>;
Examples
An example of SHOW ABAC_NAMESPACE_POLICY
is as follows:
-- Show the specified ABAC namespace policy
SHOW ABAC_NAMESPACE_POLICY ns_policy1;
An example of building statement objects for SHOW ABAC_NAMESPACE_POLICY
is as follows:
// Show the specified ABAC namespace policy
ShowAbacNamespacePolicyStatement statement =
StatementBuilder.showAbacNamespacePolicy("ns_policy1").build();
SHOW ABAC_NAMESPACE_POLICIES
The SHOW ABAC_NAMESPACE_POLICIES
command shows all ABAC namespace policies.
This command returns the following columns:
name
:TEXT
- the name of the namespace policypolicyName
:TEXT
- the policy namenamespaceName
:TEXT
- the namespace namestate
:TEXT
- the state of the policy (eitherENABLED
orDISABLED
)
Grammar
SHOW ABAC_NAMESPACE_POLICIES
Examples
An example of SHOW ABAC_NAMESPACE_POLICIES
is as follows:
-- Show all ABAC namespace policies
SHOW ABAC_NAMESPACE_POLICIES;
An example of building statement objects for SHOW ABAC_NAMESPACE_POLICIES
is as follows:
// Show all ABAC namespace policies
ShowAbacNamespacePoliciesStatement statement =
StatementBuilder.showAbacNamespacePolicies().build();
SHOW ABAC_TABLE_POLICY
The SHOW ABAC_TABLE_POLICY
command shows the specified ABAC table policy.
This command returns the following columns:
name
:TEXT
- the name of the table policypolicyName
:TEXT
- the policy namenamespaceName
:TEXT
- the namespace nametableName
:TEXT
- the table namestate
:TEXT
- the state of the policy (eitherENABLED
orDISABLED
)
Grammar
SHOW ABAC_TABLE_POLICY <TABLE_POLICY_NAME>;
Examples
An example of SHOW ABAC_TABLE_POLICY
is as follows:
-- Show the specified ABAC table policy
SHOW ABAC_TABLE_POLICY tbl_policy1;
An example of building statement objects for SHOW ABAC_TABLE_POLICY
is as follows:
// Show the specified ABAC table policy
ShowAbacTablePolicyStatement statement =
StatementBuilder.showAbacTablePolicy("tbl_policy1").build();
SHOW ABAC_TABLE_POLICIES
The SHOW ABAC_TABLE_POLICIES
command shows all ABAC table policies.
This command returns the following columns:
name
:TEXT
- the name of the table policypolicyName
:TEXT
- the policy namenamespaceName
:TEXT
- the namespace nametableName
:TEXT
- the table namestate
:TEXT
- the state of the policy (eitherENABLED
orDISABLED
)
Grammar
SHOW ABAC_TABLE_POLICIES;
Examples
An example of SHOW ABAC_TABLE_POLICIES
is as follows:
-- Show all ABAC table policies
SHOW ABAC_TABLE_POLICIES;
An example of building statement objects for SHOW ABAC_TABLE_POLICIES
is as follows:
// Show all ABAC table policies
ShowAbacTablePoliciesStatement statement = StatementBuilder.showAbacTablePolicies().build();
Literal
Literal and column values are synonymous and refer to a fixed data value used when writing SQL statements. For example, 1
, 'abc'
, 1.23
, and '2024-05-19'
are literals.
Text
A text literal is a sequence of characters enclosed in single quotes '
, such as 'abc'
and 'abc def'
.
Numeric
Numeric literals include exact-value (INTEGER and BIGINT) and approximate-value (FLOAT and DOUBLE) literals.
An exact-value literal is a sequence of digits, such as 123
and -5
.
An approximate-value literal is a sequence of digits with a decimal point, such as 4.754
and -1.2
.
Date and time
Date and time literals are text literals that follow a specific format to represents DATE, TIME, TIMESTAMP, and TIMESTAMPTZ values.
ScalarDB type | Format | Note | Example |
---|---|---|---|
DATE | 'YYYY-MM-DD' | '2024-05-19' | |
TIME | 'HH:MM:[SS[.FFFFFF]]' | Second and fractional second (up to 1 microsecond) are optional. | '12:34' , '12:34:56' , '12:34:56.789123' |
TIMESTAMP | 'YYYY-MM-DD HH:MM:[SS[.FFF]]' | Second and fractional second (up to 1 millisecond) are optional. | '2024-05-19 12:34' , '2024-05-19 12:34:56' , '2024-05-19 12:34:56.789' |
TIMESTAMPTZ | 'YYYY-MM-DD HH:MM:[SS[.FFF]] Z' | Second and fractional second (up to 1 millisecond) are optional. | '2024-05-19 12:34 Z' , '2024-05-19 12:34:56 Z' , '2024-05-19 12:34:56.789 Z' |