Illustration Image

Cassandra.Link

The best knowledge base on Apache Cassandra®

Helping platform leaders, architects, engineers, and operators build scalable real time data platforms.

8/21/2020

Reading time:8 min

Presto and Cassandra - Business Platform Team

by Arpan Patel

In this blog post, we will introduce Presto, an open-source distributed SQL query engine, and discuss how to connect it with Cassandra. We will also give you a quick demo to show how you can quickly test it out yourself. Also, this will be part 1 of our series on “Doing SQL and Reporting on Apache Cassandra with Open Source Tools”, so be on the lookout for parts 2, 3, and 4!Presto is an open-source distributed SQL query engine that can be used for running interactive analytic queries against various data sources of various sizes. Presto was originally created by Martin Traverso, David Phillips, Dain Sundstrom, and Eric Hwang while at Facebook, and its original purpose was to solve for slow queries on a 300 PB Hive Data Warehouse. This original version of Presto is called PrestoDB. A few of the founders of PrestoDB, Martin Traverso, David Phillips, and Dain Sundstrom left Facebook in 2018 and created PrestoSQL. For the purposes of our blog and demo, we will focus on PrestoDB, since it is the original version of Presto.As mentioned above, Presto can run queries against various data sources of various sizes including Hive, Cassandra, RDBMS, and other NoSQL databases. A single Presto query can combine data from multiple sources to allow for analytics across a user’s entire system. For this blog, we will solely focus on Presto and Cassandra. If you have not already and would like to, you can learn how to deploy Presto here. For demo purposes, for the config properties file, we used the provided setup that functions both as a coordinator and worker. We also used the provided setups for the node properties file, JVM config file, and log properties file. We will be demoing this connection locally using Datastax Enterprise for Cassandra, Datastax Studio for a notebook, the Presto CLI to connect and make queries, and the Presto UI to view the analytics. To connect Presto and Cassandra, you will need to set up the Cassandra connector. To configure it, you will need to add a catalog properties file to etc/catalog called cassandra.properties. If you are using multiple Cassandra clusters, then you can create multiple .properties files prefixed by a descriptive indicator as mentioned here. Within this cassandra.properties, you will fill it out as so. Since we are doing this locally, our contact-points will be 127.0.0.1. connector.name=cassandracassandra.contact-points=127.0.0.1To begin the demonstration, we will need to download the Datastax studio notebook that we have already created for you. Presto_and_Cassandra_Demo_Notebook.studio-nbDownloadA general overview of the notebook is that we will create 2 tables, seed those two tables, and try to run a join query using CQL, which will not work. We will try that join statement in the Presto CLI however, and visualize the results. We will need to run the Datastax Enterprise Cassandra server, Datastax Studio server, and Presto server. You can choose however to run them, but we ran them in 3 separate tabs within 1 terminal with each tab cd’d into each respective directory. Datastax Enterprise Cassandra Servercd dse-6.8.1/bin/dse cassandraDatastax Studiocd datastax-studio-6.8.1/bin/server.shPresto Servercd presto-server-0.237.1/bin/launcher start for no logsbin/launcher run for logsIf permission errors: sudo bin/launcher start/runOnce we have all 3 servers running, we can open localhost:8080 for the Presto UI, and localhost:9091 for the Datastax Studio UI as seen below.Presto UIDatastax Studio UINow we can drag and drop the downloaded notebook file into Datastax Studio, which should import it. Once imported, we can open it.Demo NotebookMoving back to the notebook, we will start with the first cell. We can run the first cell by hovering over the cell and clicking Run LOCAL.QUORUM, which will create our demo keyspace. After this has been run, we can now use our Presto CLI and connect to it. If you have not already, download the Presto CLI and follow the instructions to make it executable. Run the following command in a new tab in our terminal that is running our servers, or however you choose to do so../presto --server localhost:8080 --catalog cassandra --schema demoThe terminal should look as so:Connecting to demo using Presto CLIIf we run show tables; in the CLI, we will see the following as we have not created any tables yet.Running show tables; in Presto CLI with no tables in demoNow, we will move back to the notebook, and run the second cell. Before running the cell, we will need to select the keyspace “demo”, and then hit Run LOCAL.QUORUM. Once that has run, we can again run show tables; in the Presto CLI, and see the results.Running show tables; in Presto CLI after first table createdWith this, we have now confirmed that Presto can connect to our local instance of Cassandra and make queries on it. We will now move onto the 3rd and 4th cells in the notebook. The 3rd cell will seed the table we just made, and the 4th cell will confirm that the table is seeded. Go ahead and run both cells, and remember to select “demo” as the keyspace. Once the 4th cell runs, you will see the result of the query from a CQL aspect. We will copy and paste the cell 4 query and try it out in the Presto CLI. The results should be as seen below.Running SELECT * FROM spacecraft_journey_catalog where spacecraft_name = 'vostok1'; in Presto CLIAlso, we can check out our query analytics in the Presto UI at localhost:8080. We can click on the “checkmark Finished” tab under “State” under QUERY DETAILS and you can visualize the analytics of the queries we have been making so far. Visualizing the SELECT * FROM spacecraft_journey_catalog where spacecraft_name = 'vostok1'; query in Presto UIMoving back to the notebook, we can run cells 5, 6, and 7, and repeat the steps we did for cells 2, 3, and 4 to verify that the table was made and seeded correctly, and visualize the query analytics in the Presto UI. Show Tables; in Presto CLI after running cell 5Results of select * from spacecraft_speed_over_time; in Presto CLIPresto UI analytics of select * from spacecraft_speed_over_time; queryWe have now reached the end of the demo, and more importantly, the purpose of why we are using Presto with a NoSQL database like Cassandra. Say we want to get the speed of a spacecraft by spacecraft name, and also return the summary of the journey by spacecraft name as well with only one query. Will we be able to do that with only CQL? NOTE: The data model we are using is strictly for demo purposes to showcase how Presto can connect with Cassandra.Take the command in the last cell of the notebook and try running it. It won’t work as we can’t do joins natively with CQL, and we would need to run at least 2 queries, one to each table, and then programmatically connect that data.Error running join query with CQLIf we try running the same query in the Presto CLI, then it will work. We will get results of a join statement that joins 2 Cassandra tables based off of the journey_id in both tables.Same query as above, but run in Presto CLIWe can also visualize the analytics of that join query in the Presto UI.Presto UI analytics of the above queryTo also demonstrate some other cool things you can do with Presto’s UI, we can filter and analyze queries by query. If you type show tables into the search bar, we can analyze the different show tables queries we made and use that for BI analysis. Analyzing different show tables queries made during the demoFor this demo, we can make an observation that the average wall time for the show tables query is around 226.59ms while ignoring the small sample size. Also, a more impactful observation that we can make is that as we added more tables, the wall time tended to increase ~5-10ms per table. However, these observations would not likely hold weight in the real world without a larger sample size and proper regression analysis.With Presto, an open-source distributed SQL query engine, we connected two NoSQL tables and ran a join query on them. This example demo only utilizes one data source, Cassandra, but if we want to use multiple supported data sources and connect them via Presto to run joins across them, then we can. This concludes part 1 of our “Doing SQL and Reporting on Apache Cassandra with Open Source Tools” series, so be on the lookout for parts 2, 3, and 4!If you want to watch this demo in video form, it is linked below: Also, the slide deck associated with the video is linked below:Cassandra.Link is a knowledge base that we created for all things Apache Cassandra. Our goal with Cassandra.Link was to not only fill the gap of Planet Cassandra, but to bring the Cassandra community together. Feel free to reach out if you wish to collaborate with us on this project in any capacity. We are a technology company that specializes in building business platforms. If you have any questions about the tools discussed in this post or about any of our services, feel free to send us an email!

Illustration Image

In this blog post, we will introduce Presto, an open-source distributed SQL query engine, and discuss how to connect it with Cassandra. We will also give you a quick demo to show how you can quickly test it out yourself. Also, this will be part 1 of our series on “Doing SQL and Reporting on Apache Cassandra with Open Source Tools”, so be on the lookout for parts 2, 3, and 4!

Presto is an open-source distributed SQL query engine that can be used for running interactive analytic queries against various data sources of various sizes. Presto was originally created by Martin Traverso, David Phillips, Dain Sundstrom, and Eric Hwang while at Facebook, and its original purpose was to solve for slow queries on a 300 PB Hive Data Warehouse. This original version of Presto is called PrestoDB. A few of the founders of PrestoDB, Martin Traverso, David Phillips, and Dain Sundstrom left Facebook in 2018 and created PrestoSQL. For the purposes of our blog and demo, we will focus on PrestoDB, since it is the original version of Presto.

As mentioned above, Presto can run queries against various data sources of various sizes including Hive, Cassandra, RDBMS, and other NoSQL databases. A single Presto query can combine data from multiple sources to allow for analytics across a user’s entire system. For this blog, we will solely focus on Presto and Cassandra. 

If you have not already and would like to, you can learn how to deploy Presto here. For demo purposes, for the config properties file, we used the provided setup that functions both as a coordinator and worker. We also used the provided setups for the node properties file, JVM config file, and log properties file. We will be demoing this connection locally using Datastax Enterprise for Cassandra, Datastax Studio for a notebook, the Presto CLI to connect and make queries, and the Presto UI to view the analytics. To connect Presto and Cassandra, you will need to set up the Cassandra connector. To configure it, you will need to add a catalog properties file to etc/catalog called cassandra.properties. If you are using multiple Cassandra clusters, then you can create multiple .properties files prefixed by a descriptive indicator as mentioned here. Within this cassandra.properties, you will fill it out as so. Since we are doing this locally, our contact-points will be 127.0.0.1. 

connector.name=cassandra
cassandra.contact-points=127.0.0.1

To begin the demonstration, we will need to download the Datastax studio notebook that we have already created for you.

Presto_and_Cassandra_Demo_Notebook.studio-nbDownload

A general overview of the notebook is that we will create 2 tables, seed those two tables, and try to run a join query using CQL, which will not work. We will try that join statement in the Presto CLI however, and visualize the results. 

We will need to run the Datastax Enterprise Cassandra server, Datastax Studio server, and Presto server. You can choose however to run them, but we ran them in 3 separate tabs within 1 terminal with each tab cd’d into each respective directory. 

  • Datastax Enterprise Cassandra Server
    • cd dse-6.8.1/
    • bin/dse cassandra
  • Datastax Studio
    • cd datastax-studio-6.8.1/
    • bin/server.sh
  • Presto Server
    • cd presto-server-0.237.1/
    • bin/launcher start for no logs
    • bin/launcher run for logs
    • If permission errors: sudo bin/launcher start/run

Once we have all 3 servers running, we can open localhost:8080 for the Presto UI, and localhost:9091 for the Datastax Studio UI as seen below.

Presto UI
Presto UI
Datastax Studio UI
Datastax Studio UI

Now we can drag and drop the downloaded notebook file into Datastax Studio, which should import it. Once imported, we can open it.

Demo Notebook
Demo Notebook

Moving back to the notebook, we will start with the first cell. We can run the first cell by hovering over the cell and clicking Run LOCAL.QUORUM, which will create our demo keyspace. After this has been run, we can now use our Presto CLI and connect to it. If you have not already, download the Presto CLI and follow the instructions to make it executable. Run the following command in a new tab in our terminal that is running our servers, or however you choose to do so.

./presto --server localhost:8080 --catalog cassandra --schema demo

The terminal should look as so:

Connecting to demo using Presto CLI
Connecting to demo using Presto CLI

If we run show tables; in the CLI, we will see the following as we have not created any tables yet.

Running show tables; in Presto CLI with no tables in demo
Running show tables; in Presto CLI with no tables in demo

Now, we will move back to the notebook, and run the second cell. Before running the cell, we will need to select the keyspace “demo”, and then hit Run LOCAL.QUORUM. Once that has run, we can again run show tables; in the Presto CLI, and see the results.

Running show tables; in Presto CLI after first table created
Running show tables; in Presto CLI after first table created

With this, we have now confirmed that Presto can connect to our local instance of Cassandra and make queries on it. We will now move onto the 3rd and 4th cells in the notebook. The 3rd cell will seed the table we just made, and the 4th cell will confirm that the table is seeded. Go ahead and run both cells, and remember to select “demo” as the keyspace. Once the 4th cell runs, you will see the result of the query from a CQL aspect. We will copy and paste the cell 4 query and try it out in the Presto CLI. The results should be as seen below.

Running SELECT * FROM spacecraft_journey_catalog where spacecraft_name = 'vostok1'; in Presto CLI
Running SELECT * FROM spacecraft_journey_catalog where spacecraft_name = 'vostok1'; in Presto CLI

Also, we can check out our query analytics in the Presto UI at localhost:8080. We can click on the “checkmark Finished” tab under “State” under QUERY DETAILS and you can visualize the analytics of the queries we have been making so far.

Visualizing the SELECT * FROM spacecraft_journey_catalog where spacecraft_name = 'vostok1'; query in Presto UI
Visualizing the SELECT * FROM spacecraft_journey_catalog where spacecraft_name = 'vostok1'; query in Presto UI

Moving back to the notebook, we can run cells 5, 6, and 7, and repeat the steps we did for cells 2, 3, and 4 to verify that the table was made and seeded correctly, and visualize the query analytics in the Presto UI.

Show Tables; in Presto CLI after running cell 5
Show Tables; in Presto CLI after running cell 5
Results of select * from spacecraft_speed_over_time; in Presto CLI
Results of select * from spacecraft_speed_over_time; in Presto CLI
Presto UI analytics of select * from spacecraft_speed_over_time; query
Presto UI analytics of select * from spacecraft_speed_over_time; query

We have now reached the end of the demo, and more importantly, the purpose of why we are using Presto with a NoSQL database like Cassandra. Say we want to get the speed of a spacecraft by spacecraft name, and also return the summary of the journey by spacecraft name as well with only one query. Will we be able to do that with only CQL? NOTE: The data model we are using is strictly for demo purposes to showcase how Presto can connect with Cassandra.

Take the command in the last cell of the notebook and try running it. It won’t work as we can’t do joins natively with CQL, and we would need to run at least 2 queries, one to each table, and then programmatically connect that data.

Error running join query with CQL
Error running join query with CQL

If we try running the same query in the Presto CLI, then it will work. We will get results of a join statement that joins 2 Cassandra tables based off of the journey_id in both tables.

Same query as above, but run in Presto CLI
Same query as above, but run in Presto CLI

We can also visualize the analytics of that join query in the Presto UI.

Presto UI analytics of the above query
Presto UI analytics of the above query

To also demonstrate some other cool things you can do with Presto’s UI, we can filter and analyze queries by query. If you type show tables into the search bar, we can analyze the different show tables queries we made and use that for BI analysis.

Analyzing different show tables queries made during the demo
Analyzing different show tables queries made during the demo

For this demo, we can make an observation that the average wall time for the show tables query is around 226.59ms while ignoring the small sample size. Also, a more impactful observation that we can make is that as we added more tables, the wall time tended to increase ~5-10ms per table. However, these observations would not likely hold weight in the real world without a larger sample size and proper regression analysis.

With Presto, an open-source distributed SQL query engine, we connected two NoSQL tables and ran a join query on them. This example demo only utilizes one data source, Cassandra, but if we want to use multiple supported data sources and connect them via Presto to run joins across them, then we can. This concludes part 1 of our “Doing SQL and Reporting on Apache Cassandra with Open Source Tools” series, so be on the lookout for parts 2, 3, and 4!

If you want to watch this demo in video form, it is linked below:

Also, the slide deck associated with the video is linked below:

Cassandra.Link is a knowledge base that we created for all things Apache Cassandra. Our goal with Cassandra.Link was to not only fill the gap of Planet Cassandra, but to bring the Cassandra community together. Feel free to reach out if you wish to collaborate with us on this project in any capacity.

We are a technology company that specializes in building business platforms. If you have any questions about the tools discussed in this post or about any of our services, feel free to send us an email!

Related Articles

hive
firebird
oracle

DBeaver Community | Free Universal Database Tool

John Doe

5/18/2023

Checkout Planet Cassandra

Claim Your Free Planet Cassandra Contributor T-shirt!

Make your contribution and score a FREE Planet Cassandra Contributor T-Shirt! 
We value our incredible Cassandra community, and we want to express our gratitude by sending an exclusive Planet Cassandra Contributor T-Shirt you can wear with pride.

Join Our Newsletter!

Sign up below to receive email updates and see what's going on with our company

Explore Related Topics

AllKafkaSparkScyllaSStableKubernetesApiGithubGraphQl

Explore Further

cassandra