DataStax Astra Analytics Tutorial

Table of Contents

• Introduction
• Connecting to Your Astra Datasource
• Querying Your Data
• Analyzing and Visualizing Your Data
• Adding Drilldowns to Your Visualization
• Querying Your Data Using Search-Based Analytics
• Summary

Introduction

DataStax Astra is a new database-as-a-service (DBaaS) platform offered by DataStax and built on open source Apache Cassandra NoSQL. Astra removes the overhead required to install and operate Cassandra without sacrificing the reliability and security that Cassandra is known for, which makes it an intriguing up-and-comer in the DBaaS market.

Knowi offers broad native integration to DataStax Astra, which eliminates the need for tedious ETL processes and makes it very easy to connect to Astra, analyze data, and create visualizations. In this tutorial, we’re going to learn how to use Knowi to connect to DataStax Astra, analyze, and visualize our data. Before you get started, you’ll need the following things:

1. A Knowi trial account. You can sign up for one here.
2. A secure connect bundle zip file which you can download here.

Connecting to Your Astra Datasource

Once you’ve downloaded your secure connect bundle zip file and logged into your free trial account, you’re ready to get started by connecting to Astrastax. Here’s how to do that:

1. Find “Datasources” on the panel on the left side of your screen and click on it.
2. Select DataStax Astra.
3. Click on “Upload File.” This should automatically direct to the downloads folder in your computer, and your secure bundle zip should be right there. Upload it.
4. Click “Test Connection” at the bottom of your screen. Give it some time, and once you get a green “Connection Successful” alert at the top of your screen, click “Save.”

Congratulations on connecting to your datasource!

Querying Your Data

1. When you saved datasource, you should’ve received an alert at the top of your page that said “Datasource Added. Configure Queries.” Click on the word queries. (Alternatively, you can go back to the panel on the left side of your screen, go right below “Data Sources” and click on “Queries.” Then select “New Query +” from the top right.)
2. Enter the following syntax into “CQL Query*”:

select CAST(reading_time as text) as time, location, journey_id, spacecraft_name
from knowi.spacecraft_location_over_time
limit 10000

3. Right now, if we were to run this query, we would see the location of Apollo 11 at given points in time. Our end goal is to see all of this information as well as the temperature and speed of this spacecraft. The location and temperature are each in different tables, so we’re going to need to create two new queries within our main query that retrieves data from those tables. In order to do this, click “Join” in the bottom left corner of your screen and select “DSE Database.” This will create a new query builder; copy and paste the following syntax into the “CQL Query*” for your new query builder:

select CAST(reading_time as text) as time, speed, journey_id, spacecraft_name
from knowi.spacecraft_speed_over_time
limit 10000

4. Now we’ve got location and speed, but we still need to add temperature. Do this by almost exactly repeating the above process: click “Join” in the bottom left corner, select “DSE Database”, and copy and paste the following syntax into the new “CQL Query*:”

select CAST(reading_time as text) as time, temperature, journey_id, spacecraft_name
from knowi.spacecraft_temperature_over_time
limit 10000

5. Before we get into joining this data, we’re going to need to fix the format on our date. In order to do this, we’re going to switch over from CQL to Cloud9QL, Knowi’s powerful query language. Head back to the first query which calls from the “spacecraft_location_over_time” table and enter the following syntax into “Cloud9QL Query:”

select str_to_date(time, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") as Time, location, journey_id, reading_time, spacecraft_name

6. Repeat this process for the next two queries, but make sure to change “location” to “speed” in the second query, and to change “location” to “temperature” after pasting this syntax.
7. Now that you’ve done this, it’s time to join your tables. Use the join builder in between your first and second query to conduct an inner join and set “time” to equal “time.” Then, use the blue plus sign twice to add two join new fields, and set “journey_id” equal to “journey_id”, and “spacecraft_name” equal to “spacecraft_name” in those new fields. Use the “join builder” in between your second and third query to repeat this exact same process.
8. You’re almost ready to start visualizing your data; you just need to complete 3 more quick steps to finish this query. Start by scrolling to the top of your screen and giving this query a name in “Report Name.” We’ll call this one “Spacecraft Query.”
9. Scroll down to the very bottom of your screen and find “Cloud9QL Post Query.” Here, we’re going to use Cloud9QL one more time to post-process all of this data after it’s been joined. Enter the following syntax into “Cloud9QL Post Query:”

select *, expand(location);
select Time, speed as Speed, temperature as Temperature, x_coordinate as XCoord, y_coordinate as YCoord, z_coordinate as ZCoord

10. Now, click “Preview” in the bottom left corner of your screen to preview your data. If you did everything right, you’ll see speed, temperature, and 3 coordinates for Apollo 11 at different points in time on July 16th, 1969. Once you’ve confirmed this, click the green “Save & Run Now” button.

As soon as you ran your query, the results were saved within Knowi’s elastic data warehouse. This will happen every time you update your query or create a new query.

Analyzing and Visualizing Your Data

That query was the hard part; now it’s time to enjoy the fruits of our labor by creating some visualizations.

1. We’ll need a place for our visualizations, so before anything else, we have to create a dashboard. To do this, head to the top of the panel on the left side of your screen, click dashboards, and click on the plus icon to create a new dashboard. Let’s keep it simple and name this one “Spacecraft Dashboard.”
2. Head back to the panel on the left side of your screen, just below dashboards, and click widgets. Drag the “Spacecraft Query” widget on to your blank dashboard.
3. Click on the 3 dots in the top right corner of your new widget and select “Settings.” Change “Visualization Type” to “Data Grid”, then scroll down, click “Title,” change your Visualization title to “Spacecraft Data Grid” and click “save.”

4. Now, click on those 3 dots again and click “analyze.” Drag “YCoord” and “XCoord” over to “Fields/Metrics:” in that order, then head to the top of your screen and click “Visualization.”
5. Change “Visualization Type” to “Scatter” and then click “Options” to expand your options and set “X-Axis Type” to “Number – Linear Axis.”
6. This chart is almost done, but just to add some clarity, let’s add an X-Axis Title, get rid of the unnecessary legend at the top of our screen. Scroll down and click on “Options,” then scroll down to “More Options,” type “Legend” in order to find “Legend Show/Hide,” uncheck the box to remove the legend, and then click “OK.” Then type “Title” in order to find “X-Axis Title,” title your X-Axis “XCoord,” and click “OK.”
7. Now, scroll up to the top right corner, find the small icon that looks like two pieces of paper stacked atop one another, and click on it in order to clone your chart. Name your new widget “X, Y Coordinates,” then click clone and click “Add to Dashboard.”

As we can see here, the X and Y coordinates for our spacecraft seemed to increase at a pretty consistent rate over this sample. This might look boring, but it’s exactly what we’d expect: numbers moving at a healthy, steady rate. This means that we can effectively monitor our data and quickly notice any abnormalities.

Adding Drilldowns to Your Visualizations

The next step here is to add a drilldown. Drilldowns are a useful feature that allow the user to select a portion of their visualization that catches their eye, and drill down into that section of the raw data with just one click. Here’s how we’ll add a drilldown:

1. Click the 3 dots in the top right corner of the new visualization that you created and select “Drilldowns.”
2. Change “Drilldown type” to “Connected Widgets”, set it to drill when “XCoord” is clicked, and set “XCoord” equal to “XCoord.” Save and close your widget.
3. Click on a data point within your visualization to see the drilldown in action. When you’re done looking at the filtered section of your data, head to the top left corner of your data grid and click “Back.”

Querying Your Data Using Search-Based Analytics

Knowi’s search-based analytics feature empowers the user with the ability to ask questions of their data in plain English and receive results immediately. This feature makes dashboards more friendly to less technical users who still want to use their data to make smarter decisions. Let’s say that we were interested in seeing two variables: the temperature and speed of this spacecraft, and we wanted to analyze those two variables over a given subset of our sample. Here’s how to query your data and visualize this using search-based analytics:

1. Head back to the top right corner of your data grid widget and click on the 3 dots. This time, select “Analyze.”
2. In the search bar at the top of your screen, enter the following syntax:

show me temperature, speed, time where time is greater than 07/16/1969 13:34

3. Now that we’ve successfully manipulated our data in plain English, it’s time to visualize what we want to see. Click “Visualization” at the top of your screen and change “Visualization Type” to “Area.” Set “Temperature” as your Y-Axis and “Speed” as your Secondary Y-Axis. Click the clone icon in the top right corner of your screen and name this one “Temperature, Speed after 13:34.” Click “Clone”, and then click “Add to Dashboard.”

Summary

In closing, we started off by connecting to our DataStax Astra datasource. Then, we set up a query which pulled various different metrics for the spacecraft Apollo 11 from a few different tables, and then joined these tables together. Next, we created a visualization to view the x and y coordinates from our data, and then added a drilldown to that visualization which allowed us to drill down into a point within the visualization that caught our eye and look into the raw data for that point. Last, we used search-based analytics to query our data in plain English and visualize a few metrics over a subset of the entire sample.