PrestoDB Questions & Answers

One of the drawbacks of AWS Athena is the fact that as a user, you don’t have control over query performance and predictability. One specific issue that comes up quite … Continue reading Why does a single AWS Athena query get stuck in QUEUED state before being executed?

Because Presto is a distributed system composed of a coordinator and workers, each worker can connect to one or more data sources through corresponding connectors. The coordinator receives the query … Continue reading How Does Presto Join Data?

Executing Presto queries on Spark is possible, but why leverage Spark as an execution framework for Presto’s queries when Presto is itself an efficient execution engine?  The fact that both … Continue reading Executing Presto Spark

AWS Athena is well documented in having performance issues, both in terms of unpredictability and speed. Many users have pointed out that even relatively lightweight queries on Athena will fail. … Continue reading When I run a query with AWS Athena, I get the error message ‘query exhausted resources on this scale factor’. Why?

Using Presto’s Hive connector you want to create a new table in a directory that already exists, in an s3 bucket that already exists that you have full read/write access … Continue reading Creating tables in a S3 Bucket gives “Query failed External location must be a directory”

When it comes to querying big data using standard SQL you have come to the right place as this is what Presto was designed to do. Presto, the leading open … Continue reading Big Data Query

Background Many medium-sized companies start out using one of the six flavors of Amazon RDS: Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database, and SQL Server. As they grow they can … Continue reading I use RDS Postgres databases and need some complex queries done which tend to slow down my databases for everyone else on the system. What do I need to consider as I add a data lake for the analytics?

JSON Presto has a wide-range of JSON functions. Consider this json test input data (represented in the query using the  VALUES function) which contains 3 key/value elements. The key is … Continue reading Advanced SQL Tutorial

Complex SQL queries benefit from Presto’s distributed, parallel, in-memory processing architecture and cost-based optimizer. And with Presto’s federation capabilities even more complex queries can be unleashed on multiple data sources … Continue reading Complex SQL Queries

Advanced SQL Queries with Presto Advanced SQL features and functions are used by analysts when, for example, complex calculations are needed, or when many tables (perhaps from multiple sources) need … Continue reading Advanced SQL Queries with Presto | SQL Queries Presto | Ahana

Presto Platform Overview Presto is an open source distributed SQL query engine for running interactive analytic queries against data sources of all sizes. Some of the world’s most innovative and … Continue reading Presto Platform Overview & Scaling | Interactive Analytic Queries

How To Stop Presto If you are using the presto-admin tool this is how to stop Presto safely: $ presto-admin server stop In addition these commands are also useful: presto-admin … Continue reading How To Stop Presto | Presto Admin & EMR Restart | Ahana

Presto New Releases Where is the latest release of PrestoDB? And where can I find the release notes? Where is the documentation? These are common questions with easy answers. Presto’s … Continue reading Presto New Releases | Presto New & Latest Release | Ahana

How Much Memory Should I Give A Presto Worker Node? Presto is an in-memory query engine and so naturally memory configuration and management is important.   JVM Memory Presto’s JVM memory … Continue reading How Much Memory Should I Give A Presto Worker Node | Presto Memory

In Presto it is straightforward to show all tables in a schema e.g. If we have a MySQL data source/catalog that has a “demo” schema we use show tables in mysql.demo; … Continue reading How do I show tables from all schemas with Presto?

UNIX timestamps are normally stored as doubles. If you have UNIX timestamps stored as big integers then you may encounter errors when trying to cast them as timestamps: There is … Continue reading How do I convert Bigint to Timestamp with Presto?

Luckily Presto has a wide range of conversion functions and they are listed in the docs.  Many of these allow us to specifically convert a timestamp type to a date … Continue reading How do I convert timestamp to date with Presto?

When dealing with character data, case sensitivity can be important when  searching for specific matches or patterns. But not all databases and query engines behave in the same way. Some … Continue reading How do I configure Case Sensitive Search with Presto?

If you’re working with open data lakes using open source and open formats, you can have multiple formats. Presto works with both. You’ll probably want to optimize for your workloads.  … Continue reading When should I use ORC versus Parquet when using Presto?

First let’s define what Apache Hive is versus Amazon Glue. Apache Hive reads, writes, and manages large datasets using SQL. Hive was built for Hadoop. AWS Glue is a fully … Continue reading What’s the advantage of having your own Hive metastore with Presto? How does it compare to Amazon Glue?

Presto has a typeof() function to make finding the data type of a value easy. This is particularly useful when you are getting values from nested maps for example and … Continue reading How do you find out data type of value with Presto?

Sometimes called pivoting, here is one example of how to rotate row data with Presto.   Suppose we have rows of data like this: We want to pivot this data so … Continue reading How do you rotate rows to columns with Presto?

Sometimes called unpivoting, here is one example of how to rotate column data with Presto.   Suppose we have some integer data arranged in two columns called a and b: a … Continue reading How do you rotate columns to rows with Presto?

First let’s hear from an AWS Solution Architect: “Ahana Cloud uses the best practices of both a SaaS provider and somebody who would build it themselves on-premises. So the advantage … Continue reading What are the operational benefits of using a managed service for Presto with Ahana Cloud?

Hive’s explode() function takes an array (or a map) as input and outputs the elements of the array (map) as separate rows. Explode is a built-in Table-Generating Function (UDTF) in … Continue reading How do you do a Lateral View Explode in Presto?

Here’s an example using test results data in json form as input, from which we compute the average score per student.  We use the WITH clause to define a common … Continue reading How do you cross join unnest a JSON array?

With Ahana Cloud, we’ve made it easy for you write output of queries to S3. While there’s a variety of formats, here’s an example:

With Amazon Athena you’re limited in scope when it comes to doing joins across other data sources like relational data systems and more. You have to set up a Lambda, … Continue reading Does Amazon Athena do joins across other data sources besides S3? Does Amazon Athena connect to other data sources?

If you’re managing Presto on your own, either through your own installation or through a service like AWS EMR or AWS Athena, you have to maintain and manage all of … Continue reading If I have catalogs connected and configurations attached to my Presto cluster, what happens when I take the cluster down?

Check if Presto map or array is empty When working with array (indexable lists) or map (key-value tuple) complex types in Presto, it is useful to be able to test … Continue reading Check If Map Or Presto Array Is Empty Or Contains | Ahana

Export result of Select statement A common question is “how can I run a query and export the result set quickly and easily in Presto?”  You are in luck, as … Continue reading Export Result Of Select Statement | Ahana

Presto queries running slow? Reasons for Presto queries running slow, diagnosis, tuning, things to check and possible solutions: How many workers do you have in your cluster? If your PrestoDB … Continue reading Presto Queries Running Slow | Presto Slow | Ahana

Extract keys in a nested JSON array object Let’s say we have some JSON that looks like this: In this instance our json contains key value pairs. How can we … Continue reading Extract Keys In A Nested JSON Array Object | Ahana

Generate Interval from Variable Using PrestoDB There are numerous ways of generating intervals using PrestoDB SQL for different data types. Dates Date intervals can be generated for a given date … Continue reading Generate Interval from Variable Using PrestoDB | Ahana

How To Escape a Single Quote With Presto In reality there will be some occasions when you need to use a quote character in your data, in your query, or … Continue reading How To Presto Escape Single Quote | Pesto Escape Quote | Ahana

Check if null and return default (NVL analog) SQL has a number of functions for dealing with null values, and everyone has their favorite. The complicating factor is that not … Continue reading Check If Null & Return Default (NVL analog) | Ahana

Static date and timestamp in where clause Many databases automatically convert between CHAR or VARCHAR and other types like DATE and TIMESTAMP as a convenience feature.  Using constants in a … Continue reading Static Date & Timestamp In Where Clause | PrestoDB Timestamp

Where can I find the Presto Server Bootstrap logs? A common question is “where are Presto’s bootstrap logs?”  These are logs that indicate what is happening to the cluster on … Continue reading Where Can I Find The Presto Server Bootstrap Logs | Ahana

Where can I find different Presto metrics for monitoring? There are several ways to monitor Presto.  Let’s look at some options. 1. Presto Console Presto provides a web interface for … Continue reading Where Can I Find Different Presto Metrics For Monitoring | Ahana

Where do I find Presto logs? Presto needs a data directory for storing logs, etc. and it is recommended this is created in a data directory outside of the installation … Continue reading Presto Logs | Where Do I Find Presto Logs | Ahana

Presto equivalent of mysql group_concat PrestoDB supports ANSI SQL and includes support for several SQL dialects, including MySQL dialect, making it easy to group and aggregate data in a variety … Continue reading Presto Equivalent Of mysql group_concat | group_concat Presto

How to get the Presto CLI Tool The Presto CLI provides a terminal-based interactive shell for running queries. The CLI is a self-executing JAR file, which means it acts like … Continue reading How to get the Presto CLI Tool | Presto-CLI Download | Ahana

How To Convert Date String to Presto Date Format PrestoDB supports ANSI SQL and includes support for several SQL dialects, so it’s straightforward to convert a date string to date … Continue reading Convert Date String to Date Format in Presto | Presto Date Format

The main branch: PrestoDB source code Presto is an open source project that is developed in the open on the public Github repository: https://github.com/prestodb/presto. The prestodb repo is the original, … Continue reading Where to Find Presto Source Code, and How to Work With it