In this section we will learn how to create user defined Type for column of table.
User-defined types (UDTs) can attach multiple data fields, each named and typed, to a single column.UDT can having Collection data type. The fields used to create a UDT may be any valid data type, including collections and other existing UDTs.
Create User Defined Type Address
|
1 2 3 4 5 6 7 |
CREATE TYPE putracode.address( street text, country text, zipCode int, telephone set<text> ); |
Show User define Type
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
cassandra@cqlsh:putracode> desc types; address cassandra@cqlsh:putracode> desc type address; CREATE TYPE putracode.address ( street text, country text, zipcode int, telephone set<text>, new_field2 text ); |
Create Table for storing customer data and use type address for storing address of customer
|
1 2 3 4 5 6 |
CREATE TABLE putracode.customer_udt( id UUID PRIMARY KEY, name text, address frozen<address> ); |
Insert Data for Customer UDT
|
1 2 3 |
INSERT INTO customer_udt(id,name,address) values(uuid(),'Nagita',{street:'GATOT SUBROTO',country:'INDONESIA',zipCode:80237,telephone:{'081xxxxx','081yyyyy'}}); |
Retrive data from column UDT
|
1 2 |
SELECT address FROM customer_udt; |
Using dot [.] notation for spesific data on address Datatype
|
1 2 3 4 5 6 7 |
SELECT address.street,address.zipCode FROM customer_udt; cassandra@cqlsh:putracode> SELECT address.street,address.zipCode FROM customer_udt; address.street | address.zipcode ----------------+----------------- GATOT SUBROTO | 80237 |
Filtering UDT
create index first
|
1 2 |
CREATE INDEX ON putracode.customer_udt(address); |
|
1 2 3 4 5 6 7 8 9 |
SELECT name FROM customer_udt WHERE address={street:'GATOT SUBROTO',country:'INDONESIA',zipCode:80237,telephone:{'081xxxxx','081yyyyy'}}; cassandra@cqlsh:putracode> SELECT name FROM customer_udt WHERE address= {street:'GATOT SUBROTO',country:'INDONESIA',zipCode:80237,telephone:{'081xxxxx','081yyyyy'}}; name -------- Nagita (1 rows) |
Alter User Defined Column
alter type can
1. adding new field on type
2. rename the field.
3. change type
example code
|
1 2 3 4 |
ALTER TYPE address ADD new_field text; ALTER TYPE address RENAME new_field to new_field2; ALTER TYPE address ALTER new_field2 TYPE varchar; |
Note : Altering of types is not allowed
Drop Table
|
1 2 |
DROP TYPE version; |
Dropping a user-defined type that is in use by a table or another type is not allowed.
