Latest Post

Wednesday, 21 December 2016

How to Create User-Defined Types in Cassandra Database

User-Defined Types
CQL support user-defined types (UDT). User-defined types (UDT) can be created, modified and removed. A UDT is a set of named and typed fields. Fields name can be any type, including collections or other UDT.

Syntax:-CREATE TYPE [ IF NOT EXISTS ] udt_name  '(' field_definition ( ',' field_definition )* ')

Creating a User-Defined Types (UDT)
cqlsh:payroll> CREATE TYPE phone (
           ...     country_code int,
           ...     number text,
           ... );
cqlsh:payroll>
cqlsh:payroll> CREATE TYPE address (
           ...     street text,
           ...     city text,
           ...     zip text,
           ...     phones map<text, frozen<phone>>
           ... );
cqlsh:payroll> 
cqlsh:payroll> CREATE TABLE user (
           ...     id int PRIMARY KEY,
           ...     name text,
           ...     addresses map<text, frozen<address>>
           ... );
cqlsh:payroll>
Once a used-defined type has been created, value can be input using a UDT literal:
udt_literal ::=  '{' identifier ':' term ( ',' identifier ':' term )* '}'
Inserting records in user table with user defined type

cqlsh:payroll> INSERT INTO user (id,name, addresses)
           ...     VALUES (786,'alok singh', {
           ...           'home' : {
           ...                street: 'A-174',
           ...                city: 'Mayur vihar',
           ...                zip: '110096',
           ...                phones: { 'cell' : { country_code: 91, number: '7985451250' },
           ...                         'landline' : { country_code: 91, number: '...' } }
           ...               },
           ...           'work' : {
           ...               street: '602A sector-22',
           ...               city: 'Delhi',
           ...               zip: '110095',
           ...               phones: { 'fax' : { country_code: 1, number: '123134534' } }
           ...               }
           ...           });
cqlsh:payroll> Select *from user;

 id  | addresses                                                                                                                                                                                                                                                                                             | name
-----+--------------------------------------------------------------------------------+------------
 786 | {'home': {street: 'A-174', city: 'Mayur vihar', zip: '110096',        phones: {'cell': {country_code: 91, number: '7985451250'}, 
        'landline': {country_code: 91, number: '...'}}},
         'work': {street: '602A sector-22', city: 'Delhi', zip:   '110095',
         phones: {'fax': {country_code: 1, number: '123134534'}}}} | alok singh

(1 rows)

Altering a User-Defined Types (UDT)
An existing user-defined type can be modified using an ALTER TYPE statement:

alter_type_statement    ::=  ALTER TYPE udt_name alter_type_modification
alter_type_modification ::=  ALTER identifier TYPE cql_type
                             | ADD field_definition
                             | RENAME identifier TO identifier ( identifier TO identifier )*

Dropping a User-Defined Types (UDT)
You can drop an existing user-defined type using a DROP TYPE statement:

drop_type_statement ::=  DROP TYPE [ IF EXISTS ] udt_name


Government Jobs