Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Using CQL to create and manage multi-Region tables

You can use Cassandra Query Language (CQL) to create and manage multi-Region keyspaces and tables in Amazon Keyspaces.

This section provides examples of how to create and manage multi-Region tables with CQL. All tables that you create in a multi-Region keyspace automatically inherit the multi-Region settings from the keyspace. For more information about CQL, see the Amazon Keyspaces CQL language reference.

For more information about supported configurations and features, see Amazon Keyspaces Multi-Region Replication usage notes.

Creating a multi-Region keyspace (CQL)

To create a multi-Region keyspace, use NetworkTopologyStrategy to specify the Amazon Web Services Regions that the keyspace is going to be replicated in. You must include your current Region and at least one additional Region. The following CQL statement is an example of this.

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

All tables in the keyspace use the same replication strategy as the keyspace. You can't change the replication strategy at the table level.

NetworkTopologyStrategy – The replication factor for each Region is three because Amazon Keyspaces replicates data across three Availability Zones within the same Amazon Web Services Region, by default.

You can use a CQL statement to query the tables table in the system_multiregion_info keyspace to programmatically list the Regions and the status of the multi-Region table that you specify. The following code is an example of this.

SELECT * from system_multiregion_info.tables WHERE keyspace_name = 'mykeyspace' AND table_name = 'mytable';

The output of the statement looks like the following:

 keyspace_name  | table_name     | region         | status
----------------+----------------+----------------+--------
 mykeyspace     | mytable        | us-east-1      | ACTIVE
 mykeyspace     | mytable        | ap-southeast-1 | ACTIVE
 mykeyspace     | mytable        | eu-west-1      | ACTIVE

Creating a multi-Region table with default settings (CQL)

To create a multi-Region table with default settings, you can use the following example.

CREATE TABLE mykeyspace.mytable(pk int, ck int, PRIMARY KEY (pk, ck))
    WITH CUSTOM_PROPERTIES = {
	'capacity_mode':{
		'throughput_mode':'PAY_PER_REQUEST'
	},
	'point_in_time_recovery':{
		'status':'enabled'
	},
	'encryption_specification':{
		'encryption_type':'AWS_OWNED_KMS_KEY'
	},
	'client_side_timestamps':{
		'status':'enabled'
	}
};

Creating a multi-Region table with provisioned capacity mode and auto scaling (CQL)

To create a multi-Region table in provisioned mode with auto scaling, you must first specify the capacity mode by defining CUSTOM_PROPERTIES for the table. After specifying provisioned capacity mode, you can configure the auto scaling settings for the table using AUTOSCALING_SETTINGS.

For detailed information about auto scaling settings, the target tracking policy, target value, and optional settings, see Create a new table with automatic scaling using CQL.

When you're creating a multi-Region table, you can also specify different read capacity and read auto scaling settings for each replica of the table. The settings that you specify overwrite the general settings of the table for the specified Amazon Web Services Region. The write capacity, however, remains synchronized between all replicas to ensure that there's enough capacity to replicate writes across all Regions.

To define the read capacity for a table replica in a specific Region, you can configure the following parameters as part of the table's replica_updates:

The following example shows a CREATE TABLE statement for a multi-Region table in provisioned mode. The general write and read capacity auto scaling settings are the same. However, the read auto scaling settings specify additional cooldown periods of 60 seconds before scaling the table's read capacity up or down. In addition, the read capacity auto scaling settings for the Region US East (N. Virginia) are higher than those for other replicas. Also, the target value is set to 70% instead of 50%.

CREATE TABLE mykeyspace.mytable(pk int, ck int, PRIMARY KEY (pk, ck))
WITH CUSTOM_PROPERTIES = {  
    'capacity_mode': {  
        'throughput_mode': 'PROVISIONED',  
        'read_capacity_units': 5,  
        'write_capacity_units': 5  
    }
} AND AUTOSCALING_SETTINGS = {
    'provisioned_write_capacity_autoscaling_update': {
        'maximum_units': 10,  
        'minimum_units': 5,  
        'scaling_policy': {
            'target_tracking_scaling_policy_configuration': {
                'target_value': 50
            }  
        }  
    },
    'provisioned_read_capacity_autoscaling_update': {  
        'maximum_units': 10,  
        'minimum_units': 5,  
        'scaling_policy': {  
            'target_tracking_scaling_policy_configuration': {  
                'target_value': 50,
                'scale_in_cooldown': 60,  
                'scale_out_cooldown': 60
            }  
        }  
    },
    'replica_updates': {
        'us-east-1': {
            'provisioned_read_capacity_autoscaling_update': {
                'maximum_units': 20,
                'minimum_units': 5,
                'scaling_policy': {
                    'target_tracking_scaling_policy_configuration': {
                        'target_value': 70
                    } 
                }
            }
        }
    }
};

Updating the provisioned capacity and auto scaling settings of a multi-Region table (CQL)

You can use ALTER TABLE to update the capacity mode and auto scaling settings of an existing table. If you're updating a table that is currently in on-demand capacity mode, capacity_mode is required. If your table is already in provisioned capacity mode, this field can be omitted.

For detailed information about auto scaling settings, the target tracking policy, target value, and optional settings, see Create a new table with automatic scaling using CQL.

In the same statement, you can also update the read capacity and auto scaling settings of table replicas in specific Regions by updating the table's replica_updates property. The following statement is an example of this.

ALTER TABLE mykeyspace.mytable
WITH CUSTOM_PROPERTIES = {  
    'capacity_mode': {  
        'throughput_mode': 'PROVISIONED',  
        'read_capacity_units': 1,  
        'write_capacity_units': 1  
    }
} AND AUTOSCALING_SETTINGS = {
    'provisioned_write_capacity_autoscaling_update': {
        'maximum_units': 10,  
        'minimum_units': 5,  
        'scaling_policy': {
            'target_tracking_scaling_policy_configuration': {
                'target_value': 50
            }  
        }  
    },
    'provisioned_read_capacity_autoscaling_update': {  
        'maximum_units': 10,  
        'minimum_units': 5,  
        'scaling_policy': {  
            'target_tracking_scaling_policy_configuration': {  
                'target_value': 50,
                'scale_in_cooldown': 60,  
                'scale_out_cooldown': 60
            }  
        }  
    },
    'replica_updates': {
        'us-east-1': {
            'provisioned_read_capacity_autoscaling_update': {
                'maximum_units': 20,
                'minimum_units': 5,
                'scaling_policy': {
                    'target_tracking_scaling_policy_configuration': {
                        'target_value': 70
                    } 
                }
            }
        }
    }
};

Viewing the provisioned capacity and auto scaling settings of a multi-Region table (CQL)

To view the auto scaling configuration of a multi-Region table, use the following command.

SELECT * FROM system_multiregion_info.autoscaling WHERE keyspace_name = 'mykeyspace' AND table_name = 'mytable';

The output for this command looks like the following:

 keyspace_name  | table_name | region         | provisioned_read_capacity_autoscaling_update                                                                                                                                                                      | provisioned_write_capacity_autoscaling_update
----------------+------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  mykeyspace    |  mytable   | ap-southeast-1 | {'minimum_units': 5, 'maximum_units': 10, 'scaling_policy': {'target_tracking_scaling_policy_configuration': {'scale_out_cooldown': 60, 'disable_scale_in': false, 'target_value': 50, 'scale_in_cooldown': 60}}} | {'minimum_units': 5, 'maximum_units': 10, 'scaling_policy': {'target_tracking_scaling_policy_configuration': {'scale_out_cooldown': 0, 'disable_scale_in': false, 'target_value': 50, 'scale_in_cooldown': 0}}}
  mykeyspace    |  mytable   | us-east-1      | {'minimum_units': 5, 'maximum_units': 20, 'scaling_policy': {'target_tracking_scaling_policy_configuration': {'scale_out_cooldown': 60, 'disable_scale_in': false, 'target_value': 70, 'scale_in_cooldown': 60}}} | {'minimum_units': 5, 'maximum_units': 10, 'scaling_policy': {'target_tracking_scaling_policy_configuration': {'scale_out_cooldown': 0, 'disable_scale_in': false, 'target_value': 50, 'scale_in_cooldown': 0}}}
  mykeyspace    |  mytable   | eu-west-1      | {'minimum_units': 5, 'maximum_units': 10, 'scaling_policy': {'target_tracking_scaling_policy_configuration': {'scale_out_cooldown': 60, 'disable_scale_in': false, 'target_value': 50, 'scale_in_cooldown': 60}}} | {'minimum_units': 5, 'maximum_units': 10, 'scaling_policy': {'target_tracking_scaling_policy_configuration': {'scale_out_cooldown': 0, 'disable_scale_in': false, 'target_value': 50, 'scale_in_cooldown': 0}}}

Turning off auto scaling for a multi-Region table (CQL)

You can use ALTER TABLE to turn off auto scaling for an existing table. Note that you can't turn off auto scaling for an individual table replica.

In the following example, auto scaling is turned off for the table's read capacity.

ALTER TABLE mykeyspace.mytable
WITH AUTOSCALING_SETTINGS = {
    'provisioned_read_capacity_autoscaling_update': {
        'autoscaling_disabled': true
    }
};

Setting the provisioned capacity of a multi-Region table manually (CQL)

If you have to turn off auto scaling for a multi-Region table, you can use ALTER TABLE to provision the table's read capacity for a replica table manually.

ALTER TABLE mykeyspace.mytable
WITH CUSTOM_PROPERTIES = {  
    'capacity_mode': {  
        'throughput_mode': 'PROVISIONED',  
        'read_capacity_units': 1,  
        'write_capacity_units': 1  
    },
    'replica_updates': {
        'us-east-1': {
            'read_capacity_units': 2
         }
    }
};