Using a user-defined type

An example of creating a user-defined type to attach multiple data fields to a column.

In Apache Cassandra 2.1 and later, you can create a user-defined type to attach multiple data fields to a column. This example shows how to accomplish these tasks:
  • Create the user-defined types address and fullname.
  • Create a table that defines map and set collection columns of the address and fullname types, respectively.
  • Include a name column, also of the fullname type, in the table.
  • Insert the street, city, and postal code in the addresses column.
  • Insert the first and last name of a person in the name column.
  • Filter data on a column of a user-defined type.
  • Insert a names of direct reports in the set collection.

Procedure

  1. Create a keyspace.
    CREATE KEYSPACE mykeyspace WITH REPLICATION = { 'class' : 'NetworkTopologyStrategy', 'datacenter1' : 1 };
  2. Create a user-defined type named address.
    CREATE TYPE mykeyspace.address (
      street text,
      city text,
      zip_code int,
      phones set<text>
    );
  3. Create a user-defined type for the name of a user.
    CREATE TYPE mykeyspace.fullname (
      firstname text,
      lastname text
    );
  4. Create a table for storing user data in columns of type fullname and address. Use the frozen keyword in the definition of the user-defined type column.
    CREATE TABLE mykeyspace.users (
      id uuid PRIMARY KEY,
      name frozen <fullname>,
      direct_reports set<frozen <fullname>>,     // a collection set
      addresses map<text, frozen <address>>     // a collection map
    );  
  5. Insert a user's name into the fullname column.
    INSERT INTO mykeyspace.users (id, name) VALUES (62c36092-82a1-3a00-93d1-46196ee77204, {firstname: 'Marie-Claude', lastname: 'Josset'});
  6. Insert an address labeled home into the table.
    UPDATE mykeyspace.users SET addresses = addresses + {'home': { street: '191 Rue St. Charles', city: 'Paris', zip_code: 75015, phones: {'33 6 78 90 12 34'}}} WHERE id=62c36092-82a1-3a00-93d1-46196ee77204;
  7. Retrieve the full name of a user.
    SELECT name FROM mykeyspace.users WHERE id=62c36092-82a1-3a00-93d1-46196ee77204;
    Using the column name of a user-defined type retrieves all fields: firstname and lastname.
    name
    -------------------------------------------------
     {firstname: 'Marie-Claude', lastname: 'Josset'}

    Using dot notation, you can retrieve a component of the user-defined type column, for example just the last name.

    SELECT name.lastname FROM mykeyspace.users WHERE id=62c36092-82a1-3a00-93d1-46196ee77204;
    
     name.lastname
    ---------------
            Josset
  8. Filter data on a column of a user-defined type. Create an index and then run a conditional query. In Cassandra 2.1.x, you need to list all components of the name column in the WHERE clause.
    CREATE INDEX on mykeyspace.users (name);
    
    SELECT id FROM mykeyspace.users WHERE name = {firstname: 'Marie-Claude', lastname: 'Josset'};
    Output is:
     id
    --------------------------------------
     62c36092-82a1-3a00-93d1-46196ee77204
  9. Insert names of people who report to Marie-Claude. Use the UPDATE command. Insert the name of a person who reports to another manager using the INSERT command.
    When using the frozen keyword, you cannot update parts of a user-defined type value. The entire value must be overwritten. Cassandra treats the value of a frozen, user-defined type like a blob.
    UPDATE mykeyspace.users SET direct_reports = { ( 'Naoko', 'Murai'), ( 'Sompom', 'Peh') } WHERE id=62c36092-82a1-3a00-93d1-46196ee77204;
    
    INSERT INTO mykeyspace.users (id, direct_reports) VALUES ( 7db1a490-5878-11e2-bcfd-0800200c9a66, { ('Jeiranan', 'Thongnopneua') } );
  10. Query the table for the direct reports to Marie-Claude.
    SELECT direct_reports FROM mykeyspace.users;
    
     direct_reports
    -----------------------------------------------------------------------------------
                                {{firstname: 'Jeiranan', lastname: 'Thongnopneua'}}
     {{firstname: 'Naoko', lastname: 'Murai'}, {firstname: 'Sompom', lastname: 'Peh'}}