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
|
1 2 3 4 |
CREATE TABLE IF NOT EXISTS keyspace_name.table_name ( column_definition, column_definition, ...) WITH property AND property ... |
Example Create Table
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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
|
1 2 3 4 5 6 7 8 9 |
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;
|
1 2 3 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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
|
1 2 |
ALTER TABLE putracode.customers ADD customer_password text; |
- Change Datatype of columns(Not Support Again error_code=2200)
|
1 2 |
ALTER TABLE putracode.customers ALTER customer_password TYPE varchar; |
- Drop Column
|
1 2 |
ALTER TABLE putracode.customers DROP customer_password; |
- RENAME PRIMARY KEY column (only primary key)
|
1 2 |
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
|
1 2 |
DROP TABLE customer_tuples; |
Happy Coding and Learning.
Happy New Years 2018…
Cheerss
