DDL statements (data definition language) in Amazon Keyspaces

Data definition language (DDL) is the set of Cassandra Query Language (CQL) statements that you use to manage data structures in Amazon Keyspaces (for Apache Cassandra), such as keyspaces and tables. You use DDL to create these data structures, modify them after they are created, and remove them when they're no longer in use. Amazon Keyspaces performs DDL operations asynchronously. For more information about how to confirm that an asynchronous operation has completed, see Asynchronous creation and deletion of keyspaces and tables.

The following DDL statements are supported:

CREATE KEYSPACE
ALTER KEYSPACE
DROP KEYSPACE
CREATE TABLE
ALTER TABLE
RESTORE TABLE
DROP TABLE

Keyspaces

A keyspace groups related tables that are relevant for one or more applications. In terms of a relational database management system (RDBMS), keyspaces are roughly similar to databases, tablespaces, or similar constructs.

Note

In Apache Cassandra, keyspaces determine how data is replicated among multiple storage nodes. However, Amazon Keyspaces is a fully managed service: The details of its storage layer are managed on your behalf. For this reason, keyspaces in Amazon Keyspaces are logical constructs only, and aren't related to the underlying physical storage.

For information about quota limits and constraints for Amazon Keyspaces keyspaces, see Quotas for Amazon Keyspaces (for Apache Cassandra).

CREATE KEYSPACE

Use the CREATE KEYSPACE statement to create a new keyspace.

Syntax


create_keyspace_statement ::= 
    CREATE KEYSPACE [ IF NOT EXISTS ] keyspace_name
    WITH options

Where:

keyspace_name is the name of the keyspace to be created.
options are one or more of the following:
- REPLICATION – A map that indicates the replication strategy for the keyspace:
  - SingleRegionStrategy – For a single-Region keyspace. (Required)
  - NetworkTopologyStrategy – Specify at least two and up to six AWS Regions. The replication factor for each Region is three. (Optional)
- DURABLE_WRITES – Writes to Amazon Keyspaces are always durable, so this option isn't required. However, if specified, the value must be true.
- TAGS – A list of key-value pair tags to be attached to the resource when you create it. (Optional)

Example

Create a keyspace as follows.


CREATE KEYSPACE my_keyspace
    WITH REPLICATION = {'class': 'SingleRegionStrategy'} and TAGS ={'key1':'val1', 'key2':'val2'} ;

To create a multi-Region keyspace, specify NetworkTopologyStrategy and include at least two and up to six AWS Regions. The replication factor for each Region is three.


CREATE KEYSPACE my_keyspace
    WITH REPLICATION = {'class':'NetworkTopologyStrategy', 'us-east-1':'3', 'ap-southeast-1':'3','eu-west-1':'3'};

ALTER KEYSPACE

Use the ALTER KEYSPACE to add or remove tags from a keyspace.

Syntax


alter_keyspace_statement ::= 
    ALTER KEYSPACE keyspace_name
    [[ADD | DROP] TAGS

Where:

keyspace_name is the name of the keyspace to be altered.
TAGS – A list of key-value pair tags to be added or removed from the keyspace.

Example

Alter a keyspace as follows.


ALTER KEYSPACE "myGSGKeyspace" ADD TAGS {'key1':'val1', 'key2':'val2'};

DROP KEYSPACE

Use the DROP KEYSPACE statement to remove a keyspace—including all of its contents, such as tables.

Syntax


drop_keyspace_statement ::= 
    DROP KEYSPACE [ IF EXISTS ] keyspace_name

Where:

keyspace_name is the name of the keyspace to be dropped.

Example


DROP KEYSPACE "myGSGKeyspace";

Tables

Tables are the primary data structures in Amazon Keyspaces. Data in a table is organized into rows and columns. A subset of those columns is used to determine partitioning (and ultimately data placement) through the specification of a partition key.

Another set of columns can be defined into clustering columns, which means that they can participate as predicates in query execution.

By default, new tables are created with on-demand throughput capacity. You can change the capacity mode for new and existing tables. For more information about read/write capacity throughput modes, see Read/write capacity modes in Amazon Keyspaces.

For information about quota limits and constraints for Amazon Keyspaces tables, see Quotas for Amazon Keyspaces (for Apache Cassandra).

CREATE TABLE

Use the CREATE TABLE statement to create a new table.

Syntax


create_table_statement ::=  CREATE TABLE [ IF NOT EXISTS ] table_name
    '('
        column_definition 
        ( ',' column_definition )*
        [ ',' PRIMARY KEY '(' primary_key ')' ]
    ')' [ WITH table_options ]

column_definition      ::=  column_name cql_type [ FROZEN ][ STATIC ][ PRIMARY KEY]

primary_key            ::=  partition_key [ ',' clustering_columns ]

partition_key          ::=  column_name
                              | '(' column_name ( ',' column_name )* ')'

clustering_columns     ::=  column_name ( ',' column_name )*

table_options          ::=  [table_options]
                              | CLUSTERING ORDER BY '(' clustering_order ')' [ AND table_options ]
                              | options
                              | CUSTOM_PROPERTIES
                              | WITH TAGS
                              | WITH default_time_to_live

clustering_order       ::=  column_name (ASC | DESC) ( ',' column_name (ASC | DESC) )*

Where:

table_name is the name of the table to be created.
column_definition consists of the following:
- column_name – The name of the column.
- cql_type – An Amazon Keyspaces data type (see Data types).
- FROZEN – Designates this column of type collection (for example, LIST, SET, or MAP) as frozen. A frozen collection is serialized into a single immutable value and treated like a BLOB. For more information, see Collection types.
- STATIC – Designates this column as static. Static columns store values that are shared by all rows in the same partition.
- PRIMARY KEY – Designates this column as the table's primary key.
primary_key consists of the following:
- partition_key
- clustering_columns
partition_key:
- The partition key can be a single column, or it can be a compound value composed of two or more columns. The partition key portion of the primary key is required and determines how Amazon Keyspaces stores your data.
clustering_columns:
- The optional clustering column portion of your primary key determines how the data is clustered and sorted within each partition.
table_options consist of the following:
- CLUSTERING ORDER BY – The default CLUSTERING ORDER on a table is composed of your clustering keys in the ASC (ascending) sort direction. Specify it to override the default sort behavior.
- CUSTOM_PROPERTIES – A map of settings that are specific to Amazon Keyspaces.
  - capacity_mode: Specifies the read/write throughput capacity mode for the table. The options are throughput_mode:PAY_PER_REQUEST and throughput_mode:PROVISIONED. The provisioned capacity mode requires read_capacity_units and write_capacity_units as inputs. The default is throughput_mode:PAY_PER_REQUEST.
  - client_side_timestamps: Specifies if client-side timestamps are enabled or disabled for the table. The options are {'status': 'enabled'} and {'status': 'disabled'}. If it's not specified, the default is status:disabled. After client-side timestamps are enabled for a table, this setting cannot be disabled.
  - encryption_specification: Specifies the encryption options for encryption at rest. If it's not specified, the default is encryption_type:AWS_OWNED_KMS_KEY. The encryption option customer managed key requires the AWS KMS key in Amazon Resource Name (ARN) format as input: kms_key_identifier:ARN: kms_key_identifier:ARN.
  - point_in_time_recovery: Specifies if point-in-time restore is enabled or disabled for the table. The options are status:enabled and status:disabled. If it's not specified, the default is status:disabled.
  - TTL: Enables Time to Live custom settings for the table. To enable, use status:enabled. The default is status:disabled. After TTL is enabled, you can't disable it for the table.
- TAGS – A list of key-value pair tags to be attached to the resource when it's created.
- default_time_to_live – The default Time to Live setting in seconds for the table.
clustering_order consists of the following:
- column_name – The name of the column.
- ASC | DESC – Sets the ascendant (ASC) or descendant (DESC) order modifier. If it's not specified, the default order is ASC.

Example


CREATE TABLE IF NOT EXISTS "my_keyspace".my_table (
                                            id text,
                                            name text,
                                            region text,
                                            division text,
                                            project text,
                                            role text,
                                            pay_scale int,
                                            vacation_hrs float,
                                            manager_id text,
                                            PRIMARY KEY (id,division))
                                            WITH CUSTOM_PROPERTIES={
                                                'capacity_mode':{
                                                        'throughput_mode': 'PROVISIONED', 'read_capacity_units': 10, 'write_capacity_units': 20
                                                    },
                                                'point_in_time_recovery':{'status': 'enabled'},
                                                'encryption_specification':{
                                                        'encryption_type': 'CUSTOMER_MANAGED_KMS_KEY', 
                                                        'kms_key_identifier':'arn:aws:kms:eu-west-1:5555555555555:key/11111111-1111-111-1111-111111111111'
                                                    }
                                            }
                                            AND CLUSTERING ORDER BY (division ASC) 
                                            AND TAGS={'key1':'val1', 'key2':'val2'}
                                            AND default_time_to_live = 3024000;

In a table that uses clustering columns, non-clustering columns can be declared as static in the table definition. For more information about static columns, see Static columns in Amazon Keyspaces.

Example


CREATE TABLE "my_keyspace".my_table (
                                            id int,
                                            name text,
                                            region text,
                                            division text,
                                            project text STATIC,
                                            PRIMARY KEY (id,division));

ALTER TABLE

Use the ALTER TABLE statement to add new columns, add tags, or change the table's custom properties.

Syntax


alter_table_statement ::=  ALTER TABLE table_name    
 
        [ ADD ( column_definition | column_definition_list)  ] 
        [[ADD | DROP] TAGS {'key1':'val1', 'key2':'val2'}]            
        [ WITH table_options [ , ... ] ] ;
        
column_definition      ::=  column_name cql_type

Where:

table_name is the name of the table to be altered.
column_definition is the name of the column and data type to be added.
column_definition_list is a comma-separated list of columns placed inside parentheses.
TAGS is a list of key-value pair tags to be attached to the resource.
default_time_to_live: The default Time to Live setting in seconds for the table.
table_options consist of the following:
- CUSTOM_PROPERTIES – A map of settings specific to Amazon Keyspaces.
  - capacity_mode: Specifies the read/write throughput capacity mode for the table. The options are throughput_mode:PAY_PER_REQUEST and throughput_mode:PROVISIONED. The provisioned capacity mode requires read_capacity_units and write_capacity_units as inputs. The default is throughput_mode:PAY_PER_REQUEST.
  - client_side_timestamps: Specifies if client-side timestamps are enabled or disabled for the table. The options are {'status': 'enabled'} and {'status': 'disabled'}. If it's not specified, the default is status:disabled. After client-side timestamps are enabled for a table, this setting cannot be disabled.
  - encryption_specification: Specifies the encryption option for encryption at rest. The options are encryption_type:AWS_OWNED_KMS_KEY and encryption_type:CUSTOMER_MANAGED_KMS_KEY. The encryption option customer managed key requires the AWS KMS key in Amazon Resource Name (ARN) format as input: kms_key_identifier:ARN.
  - point_in_time_recovery: Specifies if point-in-time restore is enabled or disabled for the table. The options are status:enabled and status:disabled. The default is status:disabled.
  - ttl: Enables Time to Live custom settings for the table. To enable, use status:enabled. The default is status:disabled. After ttlis enabled, you can't disable it for the table.

Note

With ALTER TABLE, you can only change a single custom property. You can't combine more than one ALTER TABLE command in the same statement.

Examples

The following statement shows how to add a column to an existing table.


ALTER TABLE mykeyspace.mytable ADD (ID int);

This statement shows how to add two collection columns to an existing table:

A frozen collection column col_frozen_list that contains a nested frozen collection
A non-frozen collection column col_map that contains a nested frozen collection


ALTER TABLE my_Table ADD(col_frozen_list FROZEN<LIST<FROZEN<SET<TEXT>>>>, col_map MAP<INT, FROZEN<SET<INT>>>);

To change a table's capacity mode and specify read and write capacity units, you can use the following statement.


ALTER TABLE mykeyspace.mytable WITH CUSTOM_PROPERTIES={'capacity_mode':{'throughput_mode': 'PROVISIONED', 'read_capacity_units': 10, 'write_capacity_units': 20}};

The following statement specifies a customer managed KMS key for the table.


ALTER TABLE mykeyspace.mytable WITH CUSTOM_PROPERTIES={     
              'encryption_specification':{ 
                      'encryption_type': 'CUSTOMER_MANAGED_KMS_KEY', 
                      'kms_key_identifier':'arn:aws:kms:eu-west-1:5555555555555:key/11111111-1111-111-1111-111111111111'     
                  } 
         };

To enable point-in-time restore for a table, you can use the following statement.


ALTER TABLE mykeyspace.mytable WITH CUSTOM_PROPERTIES={'point_in_time_recovery': {'status': 'enabled'}};

To set a default Time to Live value in seconds for a table, you can use the following statement.


ALTER TABLE my_table WITH default_time_to_live = 2592000;

This statement enables custom Time to Live settings for a table.


ALTER TABLE mytable WITH CUSTOM_PROPERTIES={'ttl':{'status': 'enabled'}};

RESTORE TABLE

Use the RESTORE TABLE statement to restore a table to a point in time. This statement requires point-in-time recovery to be enabled on a table. For more information, see Point-in-time recovery for Amazon Keyspaces (for Apache Cassandra).

Syntax


restore_table_statement ::=  
    RESTORE TABLE table_name FROM TABLE table_name 
                    [ WITH table_options [ , ... ] ];

Where:

restored_table_name is the name of the restored table.
source_table_name is the name of the source table.
table_options consists of the following:
- restore_timestamp is the restore point time in ISO 8601 format. If it's not specified, the current timestamp is used.
- CUSTOM_PROPERTIES – A map of settings specific to Amazon Keyspaces.
  - capacity_mode: Specifies the read/write throughput capacity mode for the table. The options are throughput_mode:PAY_PER_REQUEST and throughput_mode:PROVISIONED. The provisioned capacity mode requires read_capacity_units and write_capacity_units as inputs. The default is the current setting from the source table.
  - encryption_specification: Specifies the encryption option for encryption at rest. The options are encryption_type:AWS_OWNED_KMS_KEY and encryption_type:CUSTOMER_MANAGED_KMS_KEY. The encryption option customer managed key requires the AWS KMS key in Amazon Resource Name (ARN) format as input: kms_key_identifier:ARN. To restore a table encrypted with a customer managed key to a table encrypted with an AWS owned key, Amazon Keyspaces requires access to the AWS KMS key of the source table.
  - point_in_time_recovery: Specifies if point-in-time restore is enabled or disabled for the table. The options are status:enabled and status:disabled. Unlike when you create new tables, the default status for restored tables is status:enabled because the setting is inherited from the source table. To disable PITR for restored tables, you must set status:disabled explicitly.
- TAGS is a list of key-value pair tags to be attached to the resource.

Note

Deleted tables can only be restored to the time of deletion.

Example


RESTORE TABLE mykeyspace.mytable_restored from table mykeyspace.my_table 
WITH restore_timestamp = '2020-06-30T04:05:00+0000'
AND custom_properties = {'point_in_time_recovery':{'status':'disabled'}, 'capacity_mode':{'throughput_mode': 'PROVISIONED', 'read_capacity_units': 10, 'write_capacity_units': 20}}
AND TAGS={'key1':'val1', 'key2':'val2'};

DROP TABLE

Use the DROP TABLE statement to remove a table from the keyspace.

Syntax


drop_table_statement ::=  
    DROP TABLE [ IF EXISTS ] table_name

Where:

IF EXISTS prevents DROP TABLE from failing if the table doesn't exist. (Optional)
table_name is the name of the table to be dropped.

Example


DROP TABLE "myGSGKeyspace".employees_tbl;

Warning Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Document Conventions

Language elements

DML statements