Create, Alter and Drop Table Cassandra

Table in Cassandra is a collection of ordered (by name) columns fetched by row. A row consists of columns and have a primary key. The first part of the key is a column name. Subsequent parts of a compound key are other column names that define the order of columns in the table.

Syntax

CREATE TABLE IF NOT EXISTS keyspace_name.table_name 
( column_definition, column_definition, ...)
WITH property AND property ...

Example Create Table

CREATE TABLE IF NOT EXISTS putracode.customers(
customer_id uuid PRIMARY KEY,
customer_name text,
customer_birthday date,
customer_addresss text,
customer_email text,
customer_grade int,
customer_deposit decimal,
customer_point float,
customer_profile_picture blob
);

To check the table using desc table_name;

cassandra@cqlsh:putracode> desc customers;
CREATE TABLE putracode.customers (
    customer_id uuid PRIMARY KEY,
    customer_addresss text,
    customer_birthday date,
    customer_deposit decimal,
    customer_email text,
    customer_grade int,
    customer_name text,
    customer_point float,
    customer_profile_picture blob
) WITH bloom_filter_fp_chance = 0.01
....

Defined Table with Columns Collection Data Type

Cassandra is a NoSql database support column with collection type.
This is example table with include collection type

CREATE TABLE putracode.customers_with_collections(
    customer_id uuid PRIMARY KEY,
    customer_addresss text,
    customer_birthday date,
    customer_emails set<text>,
    customer_products list<text>,
    customer_score map<text,text>
    );

To Check tables using desc tables;

cassandra@cqlsh:putracode> desc tables;
customers  customers_with_collections

Create table with tupple-type

Tuple data type that holds fixed-length sets of typed positional fields. You can use a tuple as an alternative to a user-defined type when you don’t need to add new fields. A tuple can accommodate many fields (32768).
In the table creation statement, use angle brackets and a comma delimiter to declare the tuple component types. Surround tuple values in parentheses to insert the values into a table, as shown in this example.
Syntax

CREATE TABLE customer_tuples (
  customer_id uuid PRIMARY KEY,
  address tuple<text, text, text>
);
Insert into customer_tuples(customer_id,address) values(uuid(),('Gatot Subroto','Denpasar','80237');
cassandra@cqlsh:putracode> select * from customer_tuples where address = ('Gatot Subroto','Denpasar','80237');
 customer_id                          | address
--------------------------------------+----------------------------------------
 18e4bed3-2b12-45db-943f-396732d084d8 | ('Gatot Subroto', 'Denpasar', '80237')

(1 rows)

before searching data on tuple datatype you must create index on tuples column
create index on customer_tuples(address);

Modifying Column Table

• Add Column

ALTER TABLE putracode.customers ADD customer_password text; 

• Change Datatype of columns(Not Support Again error_code=2200)

ALTER TABLE putracode.customers ALTER customer_password TYPE varchar;

• Drop Column

ALTER TABLE putracode.customers DROP customer_password;

• RENAME PRIMARY KEY column (only primary key)

ALTER TABLE putracode.customers RENAME customer_id TO id; 

Drop Table Cassandra

DROP TABLE statement results in the immediate, irreversible removal of a table, including all data contained in the table
example

DROP TABLE customer_tuples;

Happy Coding and Learning.
Happy New Years 2018…
Cheerss