How to use Aggregate Functions in Stargate’s GraphQL API

The Stargate GraphQL API has gotten even better with aggregate query functions. Get started using aggregate functions in your database with this short tutorial.

Aggregate functions are a relatively new feature in the release of Stargate for Astra DB. If you’re not familiar with aggregate functions, they are functions that look at the data as a whole and perform a function like min(), max(), sum(), count() and avg().

Until now, aggregate functions were only available using cqlsh (the CQL Shell). However, with the Stargate 1.0.25 release, they are now also available using the GraphQL API. In this blog entry, I’ll walk you through the process to get early access to this exciting new functionality in Stargate, and how to set up everything you need to test your own aggregate queries.

Using cqlsh to perform an aggregate query is pretty straightforward. Let’s assume you have an employee table with the following sales data:

Now we want to find the highest sale number for employee 1, John. Our cqlsh query would look like the following:

Then it would return a single record:

Prerequisites for this tutorial

This blog tutorial assumes that you already have Docker and curl installed and configured on your machine. Alternatively, if you have an Astra DB account (even with the free tier free) you can do your testing there.

Getting Stargate

If you are using Astra DB you can skip this part and go to the next section.

The main repository for the Stargate source code is on GitHub. However, I recommend just using the Docker container that is already configured for testing and run this command:

Your Docker instance of Stargate will load and start executing.

Preparing Your Test Environment

Next, we need to create our keyspace and table. Then we will load our test table with test data. While it is possible to do almost all of this using GraphQL, I did most of it using the REST API since that is the API.

Note: All of these URLs are designed for the Docker container running locally on your machine. If you are using Astra, adjust the URLs accordingly.

Once the Docker image is fully up and running, you will need to get authentication credentials for the Cassandra instance it contains. Use this curl command to get the authentication token:

Set the auth token as an environment variable for easy reuse.

Now run the following command to get a list of existing keyspaces. This is a good test to ensure you’ve set your authToken environment variable correctly:

You should see the following output from the command:

Next, we need to create our keyspace for our database. The following command will create the test keyspace:

Now we need to create our employee table in the test keyspace. This command is rather lengthy for a blog post so I recommend getting the create_table.sh file from the GitHub repository.

It’s time to load some data into our table. The easiest way to do this is to download the load_data.sh file from my GitHub repository (another blog-unfriendly script) and execute it via the command:

This contains the curl commands to load the records into your Docker database.

Once the data is loaded, let’s run a quick query to ensure that everything is as we expect. Execute the following command:

You should get all nine rows of data back. They can be a little hard to read from the terminal. If you want to see a prettier version, I suggest copying the resulting text and pasting it into an online JSON browser like jsonbeautifier.org. You should see the following nine rows:

Now we are ready to get down to business!

Using Aggregate Queries

Since I’m new to GraphQL, it seems strange to me. Although once you get used to its way of doing things (like omitting commas in a JSON-esque data format) it’s pretty straightforward. Here is the curl command that will retrieve the highest sales year for employee 1, named John:

The payload is a little hard to read on the command line, so here it is in GraphQL format:

If you examine the command, you will see how we included the max() aggregate command (named as “highest_sale”). Just like a cqlsh version of the call, the max() function is applied to the return values, not the select criteria. Your output should match the following:

How about searching for the highest sale of all time? Here’s how you do it:

By removing the “where” clause from the GraphQL statement (traditionally in the parenthesis after the table name), you can search the entire table, across all partitions.

In cqlsh this is the equivalent of adding ALLOW FILTERING, which in general is regarded as a “bad thing” since it forces a full table scan across all partitions, which can be very slow. While aggregate functions are often used for reporting, it might be acceptable to do this for a few special queries.

ALLOW FILTERING isn’t necessarily bad, but you have to understand what it does and use it sparingly if you want to keep your database performing at max speed. ALLOW FILTERING can come in very handy when we’re invoking a SELECT operation on a single partition (for example, providing the full partition keys at the very minimum, which is id in this test.employee table’s case).

On a final note, bear in mind this is GraphQL API-specific. It won’t work with the REST or Document APIs.

Get started with aggregate queries now on Astra DB.

Get up to speed faster with tutorials on our DataStax Developers YouTube channel and subscribe to our DataStax Dev Twitter channel to get notified about new blogs. For exclusive posts on all things data, follow DataStax on Medium.

Resources

1. Join our Discord: Fellowship of the (Cassandra) Rings
2. Build your first app with javascript, node js and Astra DB
3. Astra DB — Apache Cassandra as a service
4. The Fast Lane and the Open Data Stack — exciting news from DataStax CEO Chet Kapoor
5. Compiling Stargate — more tips from Jeff Davies