Tutorial Step 1: Create a keyspace and a table in Amazon Keyspaces - Amazon Keyspaces (for Apache Cassandra)

Tutorial Step 1: Create a keyspace and a table in Amazon Keyspaces

In this section, you create a keyspace and add a table to it using the console.

Note

Before you begin, make sure that you have configured all the tutorial prerequisites.

Creating a keyspace

A keyspace groups related tables that are relevant for one or more applications. A keyspace contains one or more tables and defines the replication strategy for all the tables it contains. For more information about keyspaces, see the following topics:

When you create a keyspace, you must specify the keyspace name.

Note

The replication strategy of the keyspace must be SingleRegionStrategy. SingleRegionStrategy replicates data across three Availability Zones in its AWS Region.

To create a keyspace using the console
  1. Sign in to the AWS Management Console, and open the Amazon Keyspaces console at https://console.aws.amazon.com/keyspaces/home.

  2. In the navigation pane, choose Keyspaces.

  3. Choose Create keyspace.

  4. In the Keyspace name box, enter myGSGKeyspace as the name for your keyspace.

    Name constraints:
    • Cannot be empty.

    • Allowed characters: alphanumeric characters and underscore ( _ ).

    • Maximum length is 48 characters.

  5. To create the keyspace, choose Create keyspace.

  6. Verify that the keyspace myGSGKeyspace was created by doing the following:

    1. In the navigation pane, choose Keyspaces.

    2. Locate your keyspace myGSGKeyspace in the list of keyspaces.

The following procedure creates a keyspace using CQL.

To create a keyspace using CQL
  1. Open a command shell, and enter the following:

    cqlsh

  2. Create your keyspace using the following CQL command.

    CREATE KEYSPACE IF NOT EXISTS "myGSGKeyspace" WITH REPLICATION = {'class': 'SingleRegionStrategy'};

    SingleRegionStrategy uses a replication factor of three and replicates data across three AWS Availability Zones in its Region.

    Note

    Amazon Keyspaces defaults all input to lowercase unless you enclose it in quotation marks. In this case, note "myGSGKeyspace".

  3. Verify that your keyspace was created.

    SELECT * from system_schema.keyspaces ;

    Your keyspace should be listed.

Creating a table

A table is where your data is organized and stored. The primary key of your table determines how data will be partitioned in your table. The primary key is composed of a required partition key and one or more optional clustering columns. The combined values that compose the primary key must be unique across all the table’s data. For more information about tables, see the following topics:

When you create a table, you specify the following:

  • The name of the table.

  • The name and data type of each column in the table.

  • The primary key for the table.

    • Partition key – Required

    • Clustering columns – Optional

Use the following procedure to create a table with the specified columns, data types, partition key, and clustering column.

The following procedure creates the table employees_tbl with these columns and data types.

ID text name text region text division text project text role text pay_scale int vacation_hrs float manager_id text
To create a table using the console
  1. Sign in to the AWS Management Console, and open the Amazon Keyspaces console at https://console.aws.amazon.com/keyspaces/home.

  2. In the navigation pane, choose Keyspaces.

  3. Choose myGSGKeyspace as the keyspace you want to create this table in.

  4. Choose Create table.

  5. In the Table name box, enter employees_tbl as a name for your table.

    Name constraints:
    • Cannot be empty.

    • Allowed characters: alphanumeric characters and underscore ( _ ).

    • Maximum length is 48 characters.

  6. In the Columns section, repeat the following steps for each column that you want to add to this table.

    Add the following columns and data types.

    id text name text region text division text project text role text pay_scale int vacation_hrs float manager_id text
    1. Name – Enter a name for the column.

      Name constraints:
      • Cannot be empty.

      • Allowed characters: alphanumeric characters and underscore ( _ ).

      • Maximum length is 48 characters.

    2. Type – In the list of data types, choose the data type for this column.

    3. If you want to add another column, choose Add column.

  7. Choose id as a partition key under Partition Key. A partition key is required for each table. A partition key can be made of one or more columns.

  8. Add division as a clustering column. Clustering columns are optional and determine the sort order within each partition.

    1. To add a clustering column, choose Add clustering column.

    2. In the Column list, choose division. In the Order list, choose ASC to sort in ascending order on the values in this column. (Choose DESC for descending order.)

  9. In the Table settings section, choose Default settings.

  10. Choose Create table.

  11. Verify that your table was created.

    1. In the navigation pane, choose Tables.

    2. Confirm that your table is in the list of tables.

    3. Choose the name of your table.

    4. Confirm that all your columns and data types are correct.

      Note

      The columns might not be listed in the same order that you added them to the table.

    5. In the clustering column, confirm that division is identified with true. All other table columns should be false.

The following procedure creates a table with the following columns and data types using CQL. The id column is to be the partition key.

id text name text region text division text project text role text pay_scale int vacation_hrs float manager_id text
To create a table using CQL
  1. Open a command shell and enter the following:

    cqlsh

  2. At the cqlsh prompt (cqlsh>), specify a keyspace to create your table in.

    USE "myGSGKeyspace" ;
  3. At the keyspace prompt (cqlsh:keyspace_name>), create your table by entering the following code into your command window.

    CREATE TABLE IF NOT EXISTS "myGSGKeyspace".employees_tbl ( 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 CLUSTERING ORDER BY (division ASC) ;
    Note

    ASC is the default clustering order. You can also specify DESC for descending order.

    Note that the id column is to be the partition key. Then, division is the clustering column ordered by ascending order (ASC).

  4. Verify that your table was created.

    SELECT * from system_schema.tables WHERE keyspace_name='myGSGKeyspace' ;

    Your table should be listed.

  5. Verify your table's structure.

    SELECT * FROM system_schema.columns WHERE keyspace_name = 'myGSGKeyspace' AND table_name = 'employees_tbl' ;

    Confirm that all the columns and data types are as you expected. The order of the columns might be different than in the CREATE statement.

To perform CRUD (create, read, update, and delete) operations on the data in your table, proceed to Tutorial Step 2: Create, read, update, and delete data (CRUD).