 Hello and welcome to a session on Collections, TTL that is Time to Live and Alter Commands in Cassandra. This is Dr. Neeta Puja, Professor of Computer Science and Engineering Department from Volchin Institute of Technology, Solapor. At the end of this session, learners will be able to use Collections and TTL in Cassandra query language. Now moving to Collections, Collection is a group of elements in a single variable. There are three types of Collections, List, Map and Set. List is a data type that is used to store a group of ordered elements. Map it is a data type that is used to store a key value pair of elements. Set is a data type that is used to store a group of elements. The elements of set will be written in a sorted order. To start with list, create a sample table with two columns, name and contact where each name may have multiple contact numbers. So we create a table student info, name to be of type text and contact to be of type list of integers. Now let's see how to insert data into this list. So insert into student info, for name and contact we insert values, Ravi and two mobile numbers in square braces. Update list. Now we want to update the contact list by adding one more contact number. So update to table student info, set contact is equal to contact plus a new mobile number for a student named Ravi. Now to verify this, we use select star from student info. Set, it is a data type that is used to store a group of elements. Now let's see how to create a table with set type of data. So create table student info, name of type text, contact of type set of variant. Now let's see how to insert data into this. So insert into student info, for name and contact we insert values Ravi and two mobile numbers for Ravi in curly braces. Now how to update a set? Add another contact number to the set for student named Ravi. So update table student info, set contact is equal to contact plus a new mobile number for a student named Ravi. So verification can be done again by using select star from student info. Now moving on to map, we want to create a table with map. So create table student info with two columns, name and address. Now we want to store multiple addresses for a single name. So we create table student info having attributes name of type text and address is declared to be of type map with key value pairs of type timestamp is a key and text its value. Now let's see how to insert data into map. So insert into student info, name and address values Mahesh and address since it is declared to be of type map it stores the data in the form of key value pairs. So key here is home, value is Hyderabad and one more key is office and its value is Bangalore. So we are storing two addresses for a student named Mahesh. Now let's see how to update this map values. So I want to change the office address of a person named Mahesh to Mumbai. So update table student info, set address is equal to address plus now office has been changed from Bangalore to Mumbai for Mahesh. So verification can be done using select star from student info. So output is shown here Mahesh is the name and address there are two addresses for Mahesh. One is home address in other ways office address. So home address is Hyderabad and office addresses Mumbai. Now let's see how to use counter data type in tables. A counter is a 64 bit signed integer it is a special column that is changed in increments. Counters are useful in many applications for example to keep track of the number of webpage views received on a company website to keep track of number of games played online or number of players who have joined an online game to keep track of likes that is thumbs up for a particular comment or objects. So first let's create a table called books issue which has a column of type counter. So we have three attributes in books issue table counter value to be of type counter book name to be of type where care student name also of type where care and primary key we have set to book name and student name now why so now here there are three columns counter value is a counter type of column book name and student name are the non counter type of columns. So counter columns and non counter columns cannot exist in the table. So what we do is we declare non counter columns that is book name and student name as primary case how to insert data into the counter column we cannot use insert clause for this so we use update, update books issue set counter value is equal to counter value plus one where book name is equal to big data analytics and student name is equal to ABC second time again we use update that is update books issue set counter value is equal to counter value plus one where book name is equal to big data analytics and student name is equal to XYZ. Now we can verify this operation using select star from books issue so output is shown like this three columns book name, student name, counter value. So first row is big data analytics student name is abc and counter value has been incremented by one with the first update statement. Second time again we have used the update statement to increment the counter value again by one for book name is equal to big data analytics and student name is equal to x, y, z. So counter value shown here is two. So here we are using counter value column to keep the track of number of times the book big data analytics has been issued to the students. Now let's see TTL, time to live, these columns and tables in Cassandra support an optional expiration period called time to live. Data in a column can be set to have an expiration period. The client request may specify the specific period that is TTL value for the data and TTL value is specified always in seconds. But note one thing TTL is not supported on counter columns that is columns which are declared to be of type counter cannot have TTL values declared on it. So let's create a table user login with two attributes user ID of type int and which is also declared to be a primary key and password to be of type text. Now I will use the keywords using TTL to insert the data into table for a specific duration of time. So here it is shown in the first query insert into user login user ID password and values for them are 1 comma x y z using TTL 30 seconds which means I am specifying the expiration period of 30 seconds for this user ID and password. Now I can also use the TTL function to determine how much longer the data has to live. So I will use the select statement here select and I am calling the TTL function with the password passed to it from user login where user ID is equal to 1. So output shown here is TTL the current time to live for the password is 18 seconds. Now pause the video for a while think and write in which of the following data type order of the element entered is maintained. Is it list, map or set? Second question is Cassandra uses TTL function to know the current time to live for a record is it true or false. And third question is non-counter columns are declared as primary key in tables containing counter values is it true or false. So let's see what are the answers for these questions. So a date in which of the following data type the order of the element entered is maintained. Is the list because list is defined to be a group of elements in which the order of element is maintained. Now Cassandra uses TTL function to know the current time to live for a record yes it is true. We have already shown it in the above example. Non-counter columns are declared as primary key in tables containing counter true because non-counter columns cannot exist with counter columns. So non-counter columns are declared as primary key. So let's see Altar command. Altar command is used to change the table. The changes can be to add or change columns in the table, change the data type of a column, change the table properties or add or change the collection names. Let's see the examples. Now add a column age of type int to the table student info. So we use Altar table student info add age of type int. Now change the data type of a column student name from where care to text. So again I use Altar table student info, Altar student name type to be text. Now how to change the properties of the table using Altar command. So I use Altar table student info with caching is equal to keys none, rows per partition is equal to 15. So I am setting two properties here caching and rows per partition caching I am setting it to none that is no caching of the table or the rows is done and rows per partition is 15. So we know that horizontal partitioning is used in Cassandra. So one partition will consist of 15 rows here according to the query. Now let's see Altar type command. Altar type command can be used to add new columns to a user defined data type and to alter the data type of an existing column in a user defined type. So let's first create a user defined type using create type query. So create type full name, full name is the user defined type with two attributes first name and last name both of type where care. Add a middle name column of type text to the user defined type. So Altar type full name, add middle name of type where care. Now I want to rename this middle name to mid name. So again Altar type full name, rename middle name to mid name. To verify this, use describe type command as shown below, describe type full name. So output is the complete structure of the user defined type. These are some of the references, thank you.