Studio 2.0 Goes Multi-Model with CQL Support

Great news!  In addition to support for DSE Graph and Apache TinkerPop™, Datastax Studio 2.0 introduces support for the Apache Cassandra™ Query Language(CQL).  A big part of that support is an intelligent CQL editor that will give you a productivity boost when working with CQL and Datastax Enterprise(DSE) 5.0+.  In this blog post we’ll take a deep dive on what the CQL editor has to offer.

Getting Started with CQL and Studio

CQL support for Studio requires DSE 5.0 or higher and Studio 2.0 or higher.  Both can be downloaded here http://docs.datastax.com/en/latest-dse/ and here http://docs.datastax.com/en/latest-studio/.

As is customary in Studio, you work with CQL in a notebook with one or more notebook cells.  To use CQL, just select it as the language for one of your notebook cells:

Figure 1.a:  Shows where to click to get the drop down menu of language options

Figure 1.b:  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.

Keyspaces

If you have worked with CQL in the past the next thing you’ll want to know is how to select a keyspace.  You have a few options with Studio:

1. Fully qualify schema elements by their keyspace in your CQL statements:

    

2. Use a USE statement, which will change the keyspace context for all subsequent statements in the same cell:

    

3. 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 USE statement):

    

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.

CQL Validations

Figure 2:  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.  But if you don’t use any of the above options how do you know you’ve made a mistake without executing the statement?  The answer is shown in Figure 3 above.  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.  A syntax validation is simply whether or not your statement is valid with respect to the CQL grammar:

Domain validations provide you with errors or warnings that you would get from DSE when executing a statement when some constraint is violated.  Most are based on checking if a statement is valid with regards to your schema.  But they can include anything, such as informing you that you’ve specified an invalid table option:

Figure 3:  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 ctrl+space to get a list of proposals.  Let’s take a look at content assist now.

Content Assist

Like validations, content assist can help you by proposing the next valid keywords in the grammar, or it can provide domain specific proposals.  Let’s see how we might correct the statement that specified an invalid table option by invoking content assist with ctrl+space:

Figure 4:  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 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.  Some of the more common are:

• Proposing table names anywhere a table can be referenced in a statement
• Proposing column names anywhere a table can be referenced
• Proposing the next valid keyword, e.g. CREATE <ctrl+space> should propose the TABLE keyword, among others

CQL Templates

Perhaps the most useful place to invoke content assist is at the very beginning of a statement:

Figure 5:  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}, {viewName}) are CQL statement templates.  If we select the ALTER TABLE(add column) template a statement is inserted with each placeholder being a portion of the statement you need to complete.  You can TAB through these placeholders to jump around a statement, as well as use SHIFT+TAB to move back to the previous placeholder:

Figure 6:  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.  For this template we need to provide a table name, a column name and a type for the column.  Templates like these can be very handy when dealing with large complicated statements that you might not remember the syntax for off hand.  Such as the CREATE MATERIALIZED VIEW statement:

Figure 7:  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!  All you have to do is invoke it with ctrl+space and you will pleasantly surprised how much Studio can help you with crafting your CQL statements.

Effective Schema

When either validating your statements or making content assist proposals, Studio makes schema based domain validations and content assist proposals using an effective schema.  The effective schema is your existing schema combined with changes each of your DDL statements would effectively make to the database schema.  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:

Figure 8:  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.  In this cell we can see the following being demonstrated:

1. A CREATE TABLE statement applies a change to the effective schema so that the videos table now exists from the perspective of the second statement
2. 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.
3. The third statement tries to select from the videos table.  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.

Note that effective schema also carries across cells:

Figure 9:  Example of effective schema across multiple cells

And as mentioned previously, content assist also leverages effective schema

Figure 10:  Example of content assist leveraging the effective schema

The example above shows that content assist is aware that the videos2 table exists in the effective schema, but that videos1 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 DESCRIBE statement support.

DESCRIBE Statement Support

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.  One way to do this quickly is to leverage Studio’s support for describing CQL schema elements.  Like CQL shell(cqlsh), executing DESCRIBE statements will produce the equivalent DDL to create that schema element.  Which is a handy thing to copy and then modify to meet your new types needs:

Figure 11:  Shows the result of executing a DESCRIBE TYPE statement

In general Studio's DESCRIBE command support is a great way to inspect parts of your schema quickly without leaving the editor.  However, it’s important to note that DESCRIBE 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 DESCRIBE commands using cqlsh.

What DESCRIBE commands does Studio support?

• DESCRIBE CLUSTER
• DESCRIBE KEYSPACES
• DESCRIBE KEYSPACE
• DESCRIBE TABLES
• DESCRIBE TABLE
• DESCRIBE INDEX
• DESCRIBE MATERIALIZED VIEW
• DESCRIBE TYPES
• DESCRIBE TYPE
• DESCRIBE FUNCTIONS
• DESCRIBE FUNCTION
• DESCRIBE AGGREGATES
• DESCRIBE AGGREGATE

 Next Steps

A great place for you to go next is to download Studio and walk through the Working With CQL tutorial that ships with it.  That tutorial contains even more info about how to work with CQL in Studio, including:

• Browsing your CQL schema with our fabulous CQL schema viewer
• Different ways to visualize your CQL results, including a detailed JSON view of nested data in a single column
• How to create custom CQL execution configurations, including ones that enable tracing and give you a profile view of your queries execution

Thanks!

We hope that Studio will be an extremely productive environment for you to craft your CQL queries to run against Datastax Enterprise.  If you have any feedback or requests, don’t hesitate to contact the Studio team at:  studio-feedback@datastax.com.