 Hello and welcome. This is Dr. Anita Poojar, Professor of Computer Science and Engineering Department from Volchian Institute of Technology, Solapur, presenting you a session on Cassandra query language, its data types and Cassandra shell. At the end of this session, learners will be able to use different data types in queries and execute commands on Cassandra shell. Cassandra provides a prompt known as SQLSH that is Cassandra query language shell that allows the users to communicate with it. Using this shell, one can execute queries written in Cassandra query language. The syntax of Cassandra query language is very similar to SQL that is structured query language used for relational databases. Now using SQLSH, you can define a schema, insert data into it and execute queries. Now to start this shell, open command prompt, navigate to the Cassandra bin directory and type the command as shown below. That is type SQLSH on the prompt. The command prompt changes to SQLSH. Now on this command prompt, you can execute any Cassandra queries or Cassandra statements. Now let's see some of the options of this shell. Help, it displays help topics for all options of SQLSH commands. Version shows the version of the shell being used. Debug shows additional debugging information. Color, it directs the shell to use a colored output. On the contrary, if we use no color, then it will direct the shell not to use the colored output. Execute SQL statement. This directs the shell to execute the SQL command. SQLSH U username. This authenticates a user. The default username is Cassandra. SQLSH P password. This authenticates a password. The default password is Cassandra. SQLSH file. This executes a command in the given file name. Now let's see the categories of Cassandra query language commands. There are three categories documented shell commands, data definition commands and data manipulation commands. Now let's see documented shell commands. Only few are mentioned here. Capture. This command captures the output of a command and adds it to a file. Below is the example shown. Capture. Home. Hadoop. Cassandra procs. The output of this command is stored into the file name called as output file. Consistency. This command is used to see the current consistency level or set a new consistency level. Copy. This command is used to copy data to and from Cassandra. Now in the example shown below, student is the key space that has been used. So on this key space, I am using the command copy student info. Student info is the name of the table created, which has four attributes. Student ID, student name, student mail and student contact. Now this complete student information is copied to a file known as file one. Describe command is used to describe the current cluster of the Cassandra and its objects. For example, describe cluster. It shows the existing clusters or the clusters created for the Cassandra and its objects. Now to mention few other commands. Paging. This enables or disables a query paging. Show. It displays the details of current Cassandra shell session such as Cassandra version, host or data type assumptions. Source. File name. This executes a file that contains SQL statements. Tracing. It enables or disables. Request. Tracing. Now we will go on to the second category of SQL commands that is data definition commands. Key space is an holder for the application data. Now every Cassandra cluster has one key space to hold the data. So we can create a key space using create key space command in Cassandra. Then we have to connect to the key space using use key space command. We can change the properties of a key space using alter key space command. A key space can be removed or deleted using drop key space. We can create a table within this key space using create table after connecting to the key space. Then we can alter the table that is modify the column properties of the table. Now we will move to the third category of the SQL commands that is data definition commands. Drop table which removes a table. Truncate removes all the data from the table. Create index. This defines a new index on the single column of a table. As you know indexing helps in searching the data faster. Drop index. It deletes a named index. Now data manipulation commands. Insert. This adds columns for a row in a table. Update. Updates a column of a row. Delete command reads data from a table. And batch command is used to execute multiple DML statements at once. Now we will see some of the Cassandra query language clauses which are used for querying. Select this clause reads data from the table. Where clause is used along with the select to read a specific data based on certain conditions. Order by the order by clause is used along with the select to read a specific data in specific order. Now let's move on to Cassandra query language data types. Cassandra query language provides a rich set of built-in data types including collection types. Additionally users can also create their own custom data types that is user defined data types. Now the table here shows a list of built-in data types int. It is used for 32 bit signed integer. Big int. 64 bit signed long integer. Double 64 bit floating point numbers. Float 32 bit floating point numbers. Boolean to hold the value either true or false. Log binary large object used to hold the images. Counter distributed counter value. Decimal this is variable precision integers. Timestamp to hold date plus time. Where care UTF8 encoded string value. Where int. Arbitrary precision integers. Text again it is UTF8 encoded string value but there is difference between where care and text which we are going to study further in the next videos. Now we'll move on to collection types. Cassandra query language also provides a collection data type. Collections help to store and process multiple values in a single variable. Now there are three types of collections provided by the Cassandra that is list, map and set. List is a collection of one or more ordered elements. Map is a collection of key value pairs and set is a collection of one or more elements. Here it is not at all mandatory that element should be ordered. Now let's move on to key spaces as I have already told you. A key space in Cassandra is a container to hold application data. Cluster contains one key space per application. Replication below is a syntax for creating a key space using the statement create key space. So you can see here that when we create a key space we also specify the replication strategy for that key space and the replication factor. So create key space students with replication strategy is simple strategy and replication factor is one. This means that a key space called as students is created with replication strategy is simple that means there is only one data center per cluster and replication factor is one which means there is only one replica stored in that one data center. As you know that replication strategies are of two types. A simple replication strategy has only one data center and one rack. So replicas are stored only in one data center and one rack. Network topology strategy you can set the replication factor for one or more data centers here independently. Now before I use the queries for this key space I need to connect to this key space. So we connect to this key space by use clause that is use students. So as students is the key space so use students will connect to the key space students and the prompt changes to SQL SH students. Altar key spaces this can be used to alter the property such as number of replicas and durable rights of a key space given below is a syntax of this command. Altar key space here it is used to change the replication strategy from simple to network topology strategy and replication factor from 1 to 3. Drop key space deletes a key space you can verify it again using describe key spaces. Now describe command describe key space is used to show all the existing key spaces. So here describe key space shows the two key spaces system students and system traces. Similarly we can use describe tables this command lists all the tables in a key space. So after using the command describe tables the output shown here is students info which is the table existing on students key space. Now pause the video for a while think and write replication strategy is defined per key space and is set during key space creation. Is it true or false? Yes replication strategy is defined when key space is created as you have seen in the previous video when we create a key space that time itself replication strategy is defined and even replication factor is defined. Cassandra's architecture allows any authorized user to connect to a node in any data center and access data using Cassandra query language. Yes authorized user can connect to any node in data center and access data because Cassandra follows peer to peer architecture and every node can execute the client's request whether it is a read data or write data request every node has the same role here. In Cassandra replication is a copy of is it rows columns or tables. In Cassandra replication is a copy of rows because Cassandra uses horizontal fragmentation. So these are the answers for those questions. These are some of the references. Thank you.