Presto is powerful, but running it on your own can get complicated. If you’re looking for a managed Presto experience that can let you focus on querying your data rather than managing infrastructure, try Ahana Cloud today.
PrestoDB uses regular ANSI SQL to query big data stored in object storage. If you’ve used SQL in databases before, you should be very comfortable using Presto. However, there are some quirks you need to keep in mind, stemming from the fact Presto is typically used to query semi-structured storage such as Amazon S3 rather than relational databases.
Below you’ll find some of our most popular resources relating to writing Presto SQL
Working with Date and Time Data
- How do I convert timestamp to date with Presto: 5 ways to convert timestamps to dates, covering most common scenarios.
- How To Convert Date String to Presto Date Format: How to convert ISO 8601 format strings or timestamps like “2020-09-16 14:27:00″ to a date format.
- How do I convert Unix Epoch time to a date or something more human readable with SQL: How to use the from_unixtime function used to convert the Unix Epoch Time to timestamp.
- How To Convert Bigint to Timestamp 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. Luckily there’s a solution.
Working With Different Data Types
- How to Find Out Data Type of Value with Presto: A simple example showing the type returned by Presto’s now() function:
Manipulating Tables and Data
- How to Rotate Rows to Columns with Presto: Sometimes called pivoting, here is one example of how to switch columns to rows via rotation with Presto.
- What is a Presto lag example? Learn how to use the lag window function, which returns the value of an offset before the current row in a window. One common use case for this is time series analysis, such as autocorrelation.
- Presto substring operations: How do I get the X characters from a string of a known length? Presto provides an overloaded substring function to extract characters from a string. We will use the string “Presto String Operations” to demonstrate the use of this function.
- How to Lateral View Explode in Presto: The explode function doesn’t exist in Presto; instead we can use Presto’s similar UNNEST.
- How to Show Tables From All Schemas with Presto: There is no easy way to show all tables in all schemas for a data source in Presto. However there’s the metastore to fall back on – learn how.
- Presto equivalent of mysql group_concat: Let’s look at the really useful MySQL and MariaDB SQL function GROUP_CONCAT() – this is used to concatenate data in column(s) from multiple rows into one field.
- How do I query JSON documents with Presto? The JSON parse function takes a JSON string and returns a JSON data type. See example of working with the JSON data type.
- Presto functions: mathematical, operators and aggregate: Presto offers several classes of mathematical functions that operate on single values and mathematical operators that allow for operations on values across columns.
Additional Presto SQL Resources
- SQL statement syntax on prestodb.io – a complete description of the SQL syntax used in open source PrestoDB
- Presto query example – see how to write a Presto query in our previous article
- Defining SQL functions in Ahana Cloud – from the Ahana Cloud documentation website
- Tutorial: How to run SQL queries with Presto on BigQuery: How to use Prestos’s BigQuery connector to query data stored in Google BigQuery.