Querying Data with DbVisualizer and PrestoDb

Unleash the power of Presto and DbVisualizer in our latest tutorial. Discover the distributed SQL query capabilities of Presto and leverage DbVisualizer's intuitive interface for seamless data exploration and optimization. Dive in now to unlock the full potential of the Presto-DbVisualizer connection!

---

Tools used in this tutorial

DbVisualizer - top rated database management tool and SQL client.

The MySQL database version 8 or later.

The software platform Docker.

The Presto database.

---

Welcome to the exciting world of data-driven exploration! In today's fast-paced era, organizations grapple with the thrilling challenge of managing and deciphering enormous volumes of data spread across diverse systems. But fear not! With the right tools, you can unlock invaluable insights hidden within this data treasure trove.

Introducing PrestoDb often referred to as Presto, the mighty open-source distributed SQL query engine that empowers organizations to conquer mammoth datasets from multiple sources. Presto is like the ultimate data superhero, arming you with the strength to process and query data like never before.

But Presto doesn't work alone in this epic adventure. Enter DbVisualizer, the dynamic sidekick you've been waiting for! DbVisualizer is the caped crusader of database management and development tools, boasting a user-friendly interface and a comprehensive platform that supports various databases. It's like having a trusty companion that effortlessly connects you to Presto and other powerful data stores such as Hadoop, Cassandra, and MySQL.

In this exciting tutorial, we will embark on a quest to unleash the combined power of DbVisualizer and Presto. Prepare to be amazed as we navigate the data landscape together - we’ll be simplifying database exploration, constructing queries with ease, and even visualizing data like true data superheroes. By seamlessly integrating DbVisualizer with Presto, you'll unravel a trove of insights, weaving data from different sources into captivating charts, mesmerizing graphs, and dazzling dashboards.

Prerequisites

1. Basic knowledge of databases and SQL.

2. Docker

3. DbVisualizer

4. A MySQL database.

Presto Data Types

Presto supports a wide range of data types that are essential for accurate data representation and efficient query execution. Here are some commonly used Presto data types:

• Numeric Data Types: Integers, floating-point numbers, and decimals.

• String Data Types: Variable-length and fixed-length character strings.

• The Boolean Data Type: Represents true or false values.

• Date and Time Data Types: Represents dates, times, and timestamps.

• Complex Data Types: Arrays, maps, and rows.

Using the right data types is crucial in Presto to ensure data accuracy and optimize query performance. Presto also provides data type conversion functions for transforming data between different types when needed.

Understanding Presto data types empowers you to accurately represent and manipulate your data, leading to more effective data querying and analysis.

Setting Up Presto

For this tutorial, we will be running Presto locally on a docker container. Follow these steps to install Presto on your docker container:

Step 1: Pull the Presto Docker Image

The Presto project provides the "Prestodb/Presto" Docker image, which includes the Presto server and a default configuration. Pull the image from the Docker Hub using the following command:

$ docker pull ahanaio/prestodb-sandbox

This command will download the latest version of the Presto Docker image.

Step 2: Run the Presto Container

Create a container from the Presto image using the following command:

$ docker run -p 8080:8080 --name presto ahanaio/prestodb-sandbox

This command creates a container named "presto" from the "ahanaio/prestodb-sandbox" image. The container runs in the background and maps the default Presto port, 8080, from inside the container to port 8080 on your workstation.

Step 3: Verify the Container

To verify that the Presto container is running, use the following command:

$ docker ps

This command displays all the running containers. Look for the "Presto" container and ensure that it is listed with the appropriate status and port mapping.

Step 4: Wait for Presto to Start

When the Presto container starts, it might take a few moments for it to become fully ready. You can check its status using the following command:

$ docker logs Presto

This command displays the container logs. Look for the "health: starting" status initially, and once it becomes ready, it should display "(healthy)".

Congratulations! You have successfully installed Presto on a Docker container. You can now access Presto by visiting http://localhost:8080 in your web browser and start running SQL queries against your Presto cluster.

Connecting To Presto in DbVisualizer

Creating a Presto connection in DbVisualizer is a straightforward process that allows you to unleash the power of Presto's distributed SQL query capabilities within the user-friendly environment of DbVisualizer. Here's how you can get started:

Now that we have a running Presto database in Docker, we can connect DbVisualizer to it by following the steps below:

1 - Go to the Connection tab. Click the "Create a Connection" button to create a new connection.

Creating a database connection in DbVisualizer.

2 - Select your server type. For this tutorial, we will be choosing Presto as the driver.

Choosing the driver in DbVisualizer.

3 - Information. In the Driver Connection tab, enter the following information:

Database server: localhost
Database Port: 8080
UserId: “user”

Connection Details for the Presto Server in DbVisualizer.

4 - Connecting. Click the "Connect" button to test the connection.

If you haven't updated your Presto driver, you will receive a prompt to do so.

Driver download message.

Open the Driver Manager tab and update the driver to connect to your Presto database.

Driver download.

Click on “Connect” again to test your connection. If the connection is successful, you should see a message indicating that the connection was established. You can now browse the database using DbVisualizer.

A Message Signifying a Successful Connection.

5 - Explore and Query Presto Data

With the Presto connection established in DbVisualizer, you are now ready to explore and query your Presto data. Utilize DbVisualizer's intuitive interface, query builder, and visualization tools to interact with Presto and extract valuable insights from your distributed datasets.

The Presto server tree.

Executing Queries in DbVisualizer with Trino

DbVisualizer provides a powerful interface for writing and executing SQL queries against Trino. You can leverage its user-friendly query editor to compose SQL statements efficiently. Simply expand the Trino server tree, pick any catalog from the list, and create a SQL query commander by clicking on the play icon with a plus next to it.

The create sql commander button.

You can start writing SQL queries in the SQL commander editor. A good query example is one to count the number of nations in the nation table:

1 select count(*) from tpch.sf1.nation;

Click on the play button above the SQL commander to execute the query. You would get the result as in the image below:

The Presto query result.

Leveraging Presto's Advanced Features through DbVisualizer

DbVisualizer provides a powerful platform for working with Presto, enabling you to unlock the full potential of Presto's advanced features. In this section, we will explore how DbVisualizer empowers you to work with Presto-specific functions and operators, optimize queries for improved performance, and harness Presto's distributed query capabilities.

Working with Presto-specific Functions and Operators in DbVisualizer

DbVisualizer offers seamless integration with Presto-specific functions and operators, allowing you to harness the full power of Presto's advanced SQL capabilities. These functions and operators are designed to handle complex data manipulations, transformations, and analysis tasks. With DbVisualizer, you can easily incorporate these functions into your queries and explore their potential.

Experimenting with the Presto’s GREATEST Function

Let's consider a scenario where you have a table in Presto that contains multiple numeric columns representing different measurements. You want to find the maximum value among these columns for each row. In DbVisualizer, you can experiment with the GREATEST function to achieve this.

1 SELECT GREATEST(column1, column2, column3) AS max_value
2 FROM your_table

The Presto GREATEST function query.

In this example, the GREATEST function is used to compare the values of the nationkey, and acctbal columns and retrieve the maximum value among them for each row. This allows you to identify the highest measurement value across multiple columns in your dataset.

By utilizing the SQL editor provided by DbVisualizer, you can easily modify and test different Presto-specific functions and operators. The Presto documentation provides a comprehensive list of available functions and operators along with usage examples.

Exploring Presto's Distributed Query Capabilities with DbVisualizer

One of the amazing strengths of Presto is its ability to execute distributed queries across multiple data sources. And with DbVisualizer as your trusty companion, exploring Presto's distributed query capabilities becomes a breeze!

With DbVisualizer's intuitive interface, you can effortlessly connect to multiple data sources, including Presto. This means you can tap into Presto's federated query feature and analyze data from various systems all in one place. No more jumping between different tools or struggling with complex integrations!

Let's imagine a scenario where you have data stored in both Presto and MySQL databases. Using DbVisualizer's magic, you can create a single query that seamlessly joins data from these two sources. By connecting to both Presto and MySQL within DbVisualizer, you can construct a query that combines tables from both databases, taking advantage of Presto's distributed query capabilities. This allows you to perform powerful analytics and gain insights by joining data from disparate sources. To write a query like that you need to connect to the MySQL database in your Presto server.

Connecting to MySQL server In Presto

To connect to your MySQL database from Presto, you need to add your MySQL database as a catalog under your Presto server. To achieve this, follow the steps below:

• Go to your Presto container terminal using the command below:

$ docker exec -it presto /bin/bash

• In your container terminal navigate to etc/catalog directory:

1 cd etc/catalog

• In this directory create a new file with create a catalog properties file in etc/catalog named, for example, mysql.properties, to mount the MySQL connector as the mysql catalog.

$ touch mysql.properties

• Create the file with the following contents, replacing the connection properties as appropriate for your setup:

1 connector.name=mysql
2 connection-url=jdbc:mysql://localhost:3306
3 connection-user=root
4 connection-password=secret

You can have as many catalogs as you need, so if you have additional MySQL servers, simply add another properties file to etc/catalog with a different name (making sure it ends in .properties).

• Restart your Presto docker container and refresh your Presto objects tree in DbVisualizer to see your new catalog.

Mysql connection in Presto catalogs.

Running a Distributed Query with DbVisualizer

To seamlessly join data from both the MySQL database connection and Presto, you can use the following sample query:

1 SELECT table.column1, table.column2
2 FROM mysql.schema_name.table AS m
3 JOIN table.schema_name.table AS p
4 ON m.id = p.id

Sample join query on MySQL and Presto.

In this query, mysql.schema_name.table refers to the table in the MySQL database, and presto.schema.table refers to the table in the Presto catalog. Replace mysql.schema_name.table and presto.schema_name.table with the actual table names in your databases.

The JOIN keyword is used to combine the data from both sources based on a common column, represented by m.id = p.id. Adjust the join condition based on the specific columns you want to use for the join.

By executing this query, you can seamlessly retrieve data from both the MySQL database and Presto catalog, combining and analyzing information from multiple sources in a single query.

Conclusion

In this tutorial, we discovered the powerful integration of Presto and DbVisualizer for data querying and analysis. By connecting DbVisualizer to Presto, we unlocked advanced features, optimized query performance, and seamlessly joined data from multiple sources. Now, I encourage you to take the next step and try DbVisualizer today. Dive deeper into Presto using their documentation, experiment with functions, operators, and optimization techniques, and uncover valuable insights from your data. The possibilities are endless, so embrace the adventure of data-driven exploration and enjoy the journey of querying and analyzing with Presto and DbVisualizer!

FAQ

How do I install Presto on a Docker container?

• Pull the Presto Docker image: docker pull ahanaio/prestodb-sandbox.

• Run the Presto container: docker run -p 8080:8080 --name presto ahanaio/prestodb-sandbox.

• Verify the container: docker ps.

• Wait for Presto to start: docker logs Presto.

How do I connect DbVisualizer to Presto?

• Open DbVisualizer, and go to the Connection tab.

• Click "Create a Connection" and select Presto as the driver.

• Enter the connection details (server: localhost, port: 8080).

• Click "Connect" to test the connection.

How can I write and execute SQL queries in DbVisualizer with Presto?

• Expand the Trino server tree in DbVisualizer, and select a catalog.

• Click "Create SQL Commander" to open the query editor.

• Write your SQL query, and click the play icon to execute.

Can I use Presto-specific functions and operators in DbVisualizer?

Yes, DbVisualizer supports Presto-specific functions and operators.

Is it possible to join data from multiple sources using DbVisualizer and Presto?

Yes, you can seamlessly join data from multiple sources using Presto's distributed query capabilities in DbVisualizer.

About the author

Ochuko Onojakpor is a full-stack Python/React software developer and freelance Technical Writer. He spends his free time contributing to open source and tutoring students on programming in collaboration with Google DSC.