Zach Kurey

Great news! &nbsp;In addition to support for DSE Graph and Apache TinkerPop™, Datastax Studio 2.0 introduces support for the Apache Cassandra™ Query Language(CQL). &nbsp;A big part of that support is an&nbsp;intelligent CQL editor&nbsp;that will give you a productivity boost when working with CQL and Datastax Enterprise(DSE) 5.0+. &nbsp;In this blog post we’ll take a deep dive on what the CQL editor has to offer.

<h2>Getting Started with CQL and Studio</h2>

CQL support for Studio requires DSE 5.0 or higher and Studio 2.0 or higher. &nbsp;Both can be downloaded here&nbsp;<a href="http://docs.datastax.com/en/latest-dse/">http://docs.datastax.com/en/latest-dse/</a>&nbsp;and here&nbsp;<a href="http://docs.datastax.com/en/latest-studio/">http://docs.datastax.com/en/latest-studio/</a>.

As is customary in Studio, you work with CQL in a notebook with one or more notebook cells. &nbsp;To use CQL, just select it as the language for one of your notebook cells:
<img alt="Select CQL" data-align="center" data-entity-type="file" data-entity-uuid="d9c9a5e5-f1b3-46fd-9517-22c296ecf57c" src="https://www.datastax.com/sites/default/files/inline-images/chart1.png" />
 
Figure 1.a: &nbsp;Shows where to click to get the drop down menu of language options
<img alt="Select CQL" data-align="center" data-entity-type="file" data-entity-uuid="f48f9eab-dc3f-44d2-9a99-1c86a1bf0569" src="https://www.datastax.com/sites/default/files/inline-images/chart2.png" />
 
Figure 1.b: &nbsp;Demonstrates selecting CQL as a language which will enable the intelligent editor features

You won’t have to make this selection every time as any new cell automatically inherits the prior cells language to avoid having to select the language you want to work with repeatedly.

<h2>Keyspaces</h2>

If you have worked with CQL in the past the next thing you’ll want to know is how to select a keyspace. &nbsp;You have a few options with Studio:

<ol>
	<li>Fully qualify schema elements by their keyspace in your CQL statements:<img alt="Qualified Schema Elements" data-align="center" data-entity-type="file" data-entity-uuid="bd59e329-897f-406f-b242-3d7e0ada77d9" src="https://www.datastax.com/sites/default/files/inline-images/chart3.png" />
	&nbsp;
	</li>
	<li>Use a&nbsp;USE&nbsp;statement, which will change the keyspace context for all subsequent statements&nbsp;in the same cell:<img alt="Using Use Statements" data-align="center" data-entity-type="file" data-entity-uuid="ba8a774c-7219-4c11-9d9b-33623f23d0b4" src="https://www.datastax.com/sites/default/files/inline-images/chart4.png" />
	&nbsp;
	</li>
	<li>Configure the keyspace by selecting one from keyspace drop down for a cell, which will set the keyspace context for all statements in a cell (except for statements following a&nbsp;USE&nbsp;statement):<img alt="Select Keyspace" data-align="center" data-entity-type="file" data-entity-uuid="0fcce203-bbd4-4863-aa24-48cfddd774c1" src="https://www.datastax.com/sites/default/files/inline-images/chart5.png" />
	&nbsp;
	</li>
</ol>

Like with the cell language, if a new cell is created directly below a CQL cell the keyspace setting will be inherited.

Now that we know how to work with keyspaces, let’s move on.

<h2>CQL Validations</h2>
<img alt="CQL Validations" data-align="center" data-entity-type="file" data-entity-uuid="51babb60-48c7-4d1f-9fdf-956c9d1711cf" src="https://www.datastax.com/sites/default/files/inline-images/chart5_0.png" />
 
Figure 2: &nbsp;A CQL schema aware domain validation error, indicating a keyspace needs to be specified

In the previous section we showed several ways to work with keyspaces. &nbsp;But if you don’t use any of the above options how do you know you’ve made a mistake without executing the statement? &nbsp;The answer is shown in Figure 3 above. &nbsp;There we can see that Studio let’s us know when our statements has an issue by showing a validation error.

Studio supports supports both CQL syntax and DSE domain specific validations. &nbsp;A&nbsp;syntax validation&nbsp;is simply whether or not your statement is valid with respect to the CQL grammar:
<img alt="Syntax Validation" data-align="center" data-entity-type="file" data-entity-uuid="1e82fd4e-99b2-4a3b-8e43-32d5ba1af51b" src="https://www.datastax.com/sites/default/files/inline-images/chart7.png" />
Domain validations&nbsp;provide you with errors or warnings that you would get from DSE when executing a statement when some constraint is violated. &nbsp;Most are based on checking if a statement is valid with regards to your schema. &nbsp;But they can include anything, such as informing you that you’ve specified an invalid table option:
<img alt="Execution Error" data-align="center" data-entity-type="file" data-entity-uuid="6ca73e94-5cb9-4ade-a2cc-d44d4bbfe639" src="https://www.datastax.com/sites/default/files/inline-images/chart8.png" />
 
Figure 3: &nbsp;Example of a CQL domain validation error, that if you execute the statement gives you similar feedback from DSE

In this case, and many others you can figure out how to correct your statement by removing the part of the statement with an error and invoking content assist with&nbsp;ctrl+space&nbsp;to get a list of proposals. &nbsp;Let’s take a look at content assist now.

<h2>Content Assist</h2>

Like validations, content assist can help you by proposing the next valid keywords in the grammar, or it can provide domain specific proposals. &nbsp;Let’s see how we might correct the statement that specified an invalid table option by invoking content assist with&nbsp;ctrl+space:
<img alt="Content Assist" data-align="center" data-entity-type="file" data-entity-uuid="9485ae97-68a6-4552-b9e0-2e35145bbd41" src="https://www.datastax.com/sites/default/files/inline-images/chart9.png" />
 
Figure 4: &nbsp;Example of proposing valid table options in a CREATE TABLE statement

In Figure 4 above we can see that the table option that we probably wanted before was&nbsp;bloom_filter_fp_chance, which after being selected will be inserted with a valid default value.

There are many places in CQL statements that Studio supports invoking content assist. &nbsp;Some of the more common are:

<ul>
	<li>Proposing table names anywhere a table can be referenced in a statement</li>
	<li>Proposing column names anywhere a table can be referenced</li>
	<li>Proposing the next valid keyword, e.g.&nbsp;CREATE&nbsp;&lt;ctrl+space&gt; should propose the&nbsp;TABLE&nbsp;keyword, among others</li>
</ul>

<h2>CQL Templates</h2>

Perhaps the most useful place to invoke content assist is at the very beginning of a statement:
<img alt="CQL Templates" data-align="center" data-entity-type="file" data-entity-uuid="d85a77b2-4008-4710-884c-593285fdbeac" src="https://www.datastax.com/sites/default/files/inline-images/chart10.png" />
 
Figure 5: &nbsp;Invocation of content assist at the beginning of a statement that propose CQL statement templates

What you see in Figure 5 is that the proposals that contain placeholder values ({keyspaceName},&nbsp;{viewName}) are CQL statement templates. &nbsp;If we select the&nbsp;ALTER TABLE(add column)&nbsp;template a statement is inserted with each placeholder being a portion of the statement you need to complete. &nbsp;You can&nbsp;TAB&nbsp;through these placeholders to jump around a statement, as well as use&nbsp;SHIFT+TAB&nbsp;to move back to the previous placeholder:
<img alt="Proposals With Placeholder Values" data-align="center" data-entity-type="file" data-entity-uuid="f91d6c0d-5c7f-4ccc-a973-30fc2c1fecaf" src="https://www.datastax.com/sites/default/files/inline-images/chart11.png" />
 
Figure 6: &nbsp;Show the ALTER TABLE(add column) template inserted, with the current placeholder highlighted

In Figure 6 you can see that the placeholders are emphasized with the current placeholder being highlighted. &nbsp;For this template we need to provide a table name, a column name and a type for the column. &nbsp;Templates like these can be very handy when dealing with large complicated statements that you might not remember the syntax for off hand. &nbsp;Such as the&nbsp;CREATE MATERIALIZED VIEW&nbsp;statement:
<img alt="Create Materialized View " data-align="center" data-entity-type="file" data-entity-uuid="70405d01-f544-47cd-8a51-5d384b5edbee" src="https://www.datastax.com/sites/default/files/inline-images/chart12.png" />
 
Figure 7: &nbsp;Shows how handy templates can be for large complex statements such as CREATE MATERIALIZED VIEW(with clustering order)

When in doubt, give content assist a try! &nbsp;All you have to do is invoke it with&nbsp;ctrl+space&nbsp;and you will pleasantly surprised how much Studio can help you with crafting your CQL statements.

<h2>Effective Schema</h2>

When either validating your statements or making content assist proposals, Studio makes schema based domain validations and content assist proposals using an&nbsp;effective schema. &nbsp;The effective schema is your existing schema combined with changes each of your DDL statements would&nbsp;effectively&nbsp;make to the database schema. &nbsp;More specifically the changes from every DDL statement prior to the current statement that you are either trying to invoke content assist on, or that the editor is validating.

This ensures that if you were to execute your cells one by one from the top down that they would each execute successfully.

To make this clearer, take a look at the following example:
<img alt="Effective Schema" data-align="center" data-entity-type="file" data-entity-uuid="f1d4e4f7-6255-4153-8732-fbc93324eeac" src="https://www.datastax.com/sites/default/files/inline-images/chart13.png" />
 
Figure 8: &nbsp;Example of effective schema in a single cell

In the example above, assume that the database schema does not have the videos table, and that we have not executed this notebook cell. &nbsp;In this cell we can see the following being demonstrated:

<ol>
	<li>A&nbsp;CREATE TABLE&nbsp;statement applies a change to the effective schema so that the videos table now exists from the perspective of the second statement</li>
	<li>Even though we haven’t executed it, the second statement(drop table videos) does not have a validation error, because the videos table exists in the effective schema of the drop table statement.</li>
	<li>The third statement tries to&nbsp;select&nbsp;from the videos table. &nbsp;But the effective schema for that statement no longer has the videos table due to the prior drop statement, so it is flagged with a validation error.</li>
</ol>

Note that effective schema also carries across cells:
<img alt="Effective Schema Carry Across Cells" data-align="center" data-entity-type="file" data-entity-uuid="0b899b5f-0551-42fb-96da-df44e4c10a7d" src="https://www.datastax.com/sites/default/files/inline-images/chart14.png" />
 
Figure 9: &nbsp;Example of effective schema across multiple cells

And as mentioned previously, content assist also leverages effective schema
<img alt="Content Assist" data-align="center" data-entity-type="file" data-entity-uuid="2381fa9a-e0d9-405d-aca2-812e3da9f516" src="https://www.datastax.com/sites/default/files/inline-images/chart15.png" />
 
Figure 10: &nbsp;Example of content assist leveraging the effective schema

The example above shows that content assist is aware that the&nbsp;videos2&nbsp;table exists in the effective schema, but that&nbsp;videos1&nbsp;has been dropped, so it isn’t proposed as a possible table to drop for the current statement.

Effective schema is a great tool to have to ensure you are writing statements that will execute successfully when working on a notebook that contains DDL statements. Especially notebooks with many statements.

One last topic for this post is a way for you to view your database schema from the editor itself using Studio’s&nbsp;DESCRIBE&nbsp;statement support.

<h2>DESCRIBE Statement Support</h2>

Suppose you want to create a new user defined type(UDT) that is fairly similar to an existing UDT, or you just don’t remember the syntax. &nbsp;One way to do this quickly is to leverage Studio’s support for&nbsp;describing&nbsp;CQL schema elements. &nbsp;Like CQL shell(cqlsh), executing&nbsp;DESCRIBE&nbsp;statements will produce the equivalent DDL to create that schema element. &nbsp;Which is a handy thing to copy and then modify to meet your new types needs:
<img alt="Result of Describe Type statement" data-align="center" data-entity-type="file" data-entity-uuid="cf0bc800-4108-4efe-bdbd-491dc965c1b5" src="https://www.datastax.com/sites/default/files/inline-images/chart16.png" />
 
Figure 11: &nbsp;Shows the result of executing a DESCRIBE TYPE statement

In general Studio's&nbsp;DESCRIBE&nbsp;command support is a great way to inspect parts of your schema quickly without leaving the editor. &nbsp;However, it’s important to note that&nbsp;DESCRIBE&nbsp;commands are not actual CQL statements and don't execute against your DSE cluster. Instead Studio uses the metadata it knows about your schema to generate equivalent output that you would find if issuing&nbsp;DESCRIBE&nbsp;commands using cqlsh.

What&nbsp;DESCRIBE&nbsp;commands does Studio support?

<ul>
	<li>DESCRIBE CLUSTER</li>
	<li>DESCRIBE KEYSPACES</li>
	<li>DESCRIBE KEYSPACE</li>
	<li>DESCRIBE TABLES</li>
	<li>DESCRIBE TABLE</li>
	<li>DESCRIBE INDEX</li>
	<li>DESCRIBE MATERIALIZED VIEW</li>
	<li>DESCRIBE TYPES</li>
	<li>DESCRIBE TYPE</li>
	<li>DESCRIBE FUNCTIONS</li>
	<li>DESCRIBE FUNCTION</li>
	<li>DESCRIBE AGGREGATES</li>
	<li>DESCRIBE AGGREGATE</li>
</ul>

<h2>&nbsp;Next Steps</h2>

A great place for you to go next is to download Studio and walk through the&nbsp;Working With CQL&nbsp;tutorial that ships with it. &nbsp;That tutorial contains even more info about how to work with CQL in Studio, including:

<ul>
	<li>Browsing your CQL schema with our fabulous CQL schema viewer</li>
	<li>Different ways to visualize your CQL results, including a detailed JSON view of nested data in a single column</li>
	<li>How to create custom CQL execution configurations, including ones that enable tracing and give you a profile view of your queries execution</li>
</ul>

<h2>Thanks!</h2>

We hope that Studio will be an extremely productive environment for you to craft your CQL queries to run against Datastax Enterprise. &nbsp;If you have any feedback or requests, don’t hesitate to contact the Studio team at: &nbsp;<a href="mailto:studio-feedback@datastax.com">studio-feedback@datastax.com</a>. &nbsp;

Studio 2.0 Goes Multi-Model with CQL Support

Zach Kurey

Discover more

Share

Share

Getting Started with CQL and Studio

Keyspaces

CQL Validations

Content Assist

CQL Templates

Effective Schema

DESCRIBE Statement Support

Next Steps

Thanks!

More Company

DataStax Acquires Langflow to Accelerate Generative AI Development

The Top 5 DataStax Stories from 2023

2023 Recap: Data = AI

DataStax Astra DB Nabs Three Prestigious 2023 TrustRadius “Best of” Awards, Dominates the Vector Databases Category

One-stop Data API for Production GenAI