Data Warehouse: A Comprehensive Guide

Introduction

A data warehouse is a data repository that is typically used for analytic systems and Business Intelligence tools. It is typically composed of operational data that has been aggregated and organized in such a way that facilitates the requirements of the data teams. Data consumers need/want to be able to do their work at a very high speed to make decisions. By design, there is usually some level of latency involved in data appearing in a warehouse, keep that in mind when designing your systems and what the requirements are for your users. In this article, we’re going to review the data warehouse types, the different types of architecture, and the different warehouse model types.

Data Warehouse Architecture Types

The various data warehouse architecture types break down into three categories:

Single-tier architecture – The objective of this architecture is to dramatically reduce data duplication and produce a dense set of data. While this design keeps the volume of data as low as possible, it is not appropriate for complex data requirements that include numerous data sources.

Two-tier architecture – This architecture design splits the physical data from the warehouse itself, making use of a system and a database server. This design is typically used for a data mart in a small organization, and while efficient at data storage, it is not a scalable design and can only support a relatively small number of users.

Three-tier architecture – The three-tier architecture is the most common type of data warehouse as it provides a well-organized flow of your raw information to provide insights. It is comprised of the following components:

• Bottom tier – comprises the database of the warehouse servers. It creates an abstraction layer on the various information sources to be used in the warehouse. 
• Middle tier – includes an OLAP server to provide an abstracted view of the database for the users. Being pre-built into the architecture, this tier can be used as an OLAP-centric warehouse.
• Top tier – comprises the client-level tools and APIs that are used for data analysis and reporting. 

Data Warehouse Model Types 

The data warehouse model types break down into four categories:

1. Enterprise Data Warehouse

An EDW is a centralized warehouse that collects all the information on subjects across the entire organization. These tend to be a collection of databases as opposed to one monolith, that provides a unified approach to querying data by subject.

2. Data Mart 

 Consisting of a subset of a warehouse that is useful for a specific group of users. Consider a marketing data mart that is populated with data from ads, analytics, social media engagement, email campaign data, etc. This enables the (marketing) department to rapidly analyze their data without the need to scan through volumes of unrelated data. A data mart can be further broken into “independent”, where the data stands alone, or “dependent” where the data is coming from the warehouse.

3. Operational Data Store

The ODS might seem slightly counterintuitive to start with as it is used for operational reporting, and typically we don’t want to do reporting and analytic workloads on operational data. It is a synergistic component for the previously mentioned EDW and used for reporting on operational types of data. Low-velocity data that is managed in real-time, such as customer records or employee records, are typical of this kind of store.

4. Virtual Warehouse

The Virtual Warehouse is maybe a questionable inclusion, but nonetheless important. This is implemented as a set of views over your operational database. They tend to be limited in what they can make available due to the relationships in the data, and the fact that you don’t want to destroy your operational database performance by having large numbers of analytic activities taking place on it at the same time.

Summary

A warehouse provides an environment that fosters the ability to do drill-down analysis on your data looking for insights. As a data analyst is looking for trends or actionable insights, the ability to navigate through various data dimensions easily is paramount. The warehouse approach allows you to store and analyze vast amounts of information, which also comes at a cost for storage and compute. You can mitigate some of these costs by optimizing your warehouse for data retrieval. Picking a DW design and sticking with it, and ensuring that your data has been cleansed and standardized prior to loading.
Another option to the warehouse is the growing data lake approach, where information can be read in place from an object store such as (AWS) S3. Some advantages are reduced costs and latency as the load to the DW is no longer necessary. The Community Edition of the Presto managed service from Ahana is a great way to try out the data lake to test your requirements.

Related Articles

A Comprehensive Guide to Data Warehouse Types

A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing.

Presto vs Snowflake: Data Warehousing Comparisons

Presto is an open-source SQL query engine for data lakehouse analytics. Snowflake is a cloud data warehouse that offers a cloud-based information storage and analytics service. Learn more about the differences in this article

Data Warehouse Concepts for Beginners

A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. Typically a data warehouse contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from diverse data sources. It requires the process: Extract, Transform, and Load (ETL) from diverse data sources and create another copy within the data warehouse to support SQL queries and analysis. 

Following data design techniques are required to facilitate data retrieval for analytical processing:

Star Schema: It is the foundational and simplest schema among data warehousing modeling. It contains one or more fact tables indexing to any number of dimensional tables. Its graphical representation looks like a star hence why we call it a star schema. Fact tables are usually very large compared to dimension tables; and dimension tables can contain redundant data as these are not required to be normalized. 

Snowflake Schema: It is an extension of the star schema where a centralized fact table references the number of other dimension tables; however, those dimension tables are further normalized into multiple related tables. The entity-relationship diagram of this schema resembles a snowflake shape, hence we called it a snowflake schema.

Data Warehouse Example

Consider a fact table that stores sales quantities for each product and customer at a certain time. Sales quantities will be measured here and (Primary) keys from the customer, product, and time dimension tables will flow into the fact table. Additionally, all of the products can be further grouped under different product families and stored on a different table, the primary key of product family tables also goes into the product table as a foreign key. Such a construct is called a snow-flake schema as the product table is further snow-flaked into the product family.

Figure 1 explains the typical snowflake schema design. 

ETL or ELT—Extracting, Transforming, and Loading Data

Besides the difference in data modeling and schemas, building a data warehouse involves the critical task of ETL – the compiling of data into a warehouse from other sources.  

In data extraction, we move data out of source systems. It could be relational databases, NoSQL databases or streaming data sources. The challenge during this step is to identify the right data and manage access control. 

In a data pipeline or batch workloads, we frequently move a large amount of data from different source systems to the data warehouse. Here the challenges are to plan a realistic SLA and to have a reliable and fast network and infrastructure. 

In data transformation, we format data so that it can be represented consistently in the data warehouse. The original data might reside in different databases using different data types or in different table formats, or in different file formats in different file systems. 

We load data into the fact tables correctly with an error-handling procedure in data loading.

Data Warehouse To Data Lake To Data Lakehouse

A data lake is a centralized file system or storage designed to store, process, and secure large amounts of structured, semistructured, or unstructured data. It can store data in its native format and process any variety of it. Examples of a data lake include HDFS, AWS S3, ADLS or GCS.

Data lakes use the ELT (Extract Load Transform) process while data warehouses use ETL (Extract Transform Load) process. With a SQL engine like Presto you can run interactive queries, reports, and dashboards from a data lake, without the need to create yet another data warehouse or copy of your data. and add an operational overhead. 

A data lake is just one element of an Open Data Lakehouse, as it is taking the benefits from both: a data warehouse and a data lake. However, an Open Data Lakehouse is much more than that. It is the entire stack. In addition to hosting a data lake (AWS S3), and a SQL engine (presto), it also allows for governance (AWS Lake Formation), and ACID transactions. Transactionality or transaction support is achieved using technologies and projects such as Apache Hudi; while Presto is the SQL engine that then sits on top of the cloud data lake you’re querying. In addition to this, there is Ahana Cloud. Ahana is a managed service for Presto, designed to simplify the process of configuring and operating Presto. 

As cloud data warehouses become more cost-prohibitive and limited by vendor lock-in, and the data mesh, or data federation, the approach is not performant, more and more companies are migrating their workloads to an Open Data Lakehouse. If all your data is going to end up in cloud-native storage like Amazon S3, ADLS Gen2, GCS. then the most optimized and efficient data strategy is to leverage an Open Data Lakehouse stack, which provides much more flexibility and remedies the challenges noted above. Taking on the task of creating an Open Data Lakehouse is difficult. As ab introduction to the process check out this on-demand presentation, How to build an Open Data Lakehouse stack. In it you’ll see how you can build your stack in more detail, while incorporating technologies like Ahana, Presto, Apache Hudi, and AWS Lake Formation.

Related Articles

5 Components of Data Warehouse Architecture

In this article we’ll look at the contextual requirements of a data warehouse, which are the five components of a data warehouse.

Data Warehouse: A Comprehensive Guide

A data warehouse is a data repository that is typically used for analytic systems and Business Intelligence tools. Learn more about it in this article.

AWS Lake Formation vs AWS Glue – What are the differences?

Last updated: October 2022

As you start building your analytics stack in AWS, there are several AWS technologies to understand as you begin. In this article we’ll discuss two key technologies:

• AWS Lake Formation for security and governance; and
• AWS Glue. a data catalog.

While both of these services are typically used to build, manage, and operationalize AWS data lakes, they fulfil completely different roles. AWS Lake Formation is built around AWS Glue, and both services share the same AWS Glue Data Catalog; however, Lake Formation provides a wider breadth of governance and data management functionality, whereas Glue is focused on ETL and data processing.

What is AWS Lake Formation? 

AWS Lake Formation makes it easier for you to build, secure, and manage data lakes. It provides a means to address some of the challenges around unstructured data lake storage – including security, access control, governance, and performance.

How it works: AWS Lake Formation gives you a central console where you can discover data sources, set up transformation jobs to move data to an Amazon Simple Storage Service (S3) data lake, remove duplicates and match records, catalog data for access by analytic tools, configure data access and security policies, and audit and control access from AWS analytic and ML services

For AWS users who want to get governance on their data lake, AWS Lake Formation makes it easy to set up a secure data lake very quickly (in a matter of days). 

In order to provide better query performance when using services such as Athena or Presto, Lake Formation creates Glue workflows that integrates source tables, extract the data, and load it to Amazon S3 data lake.

When should you use AWS Lake Formation? 

At its core, Lake Formation is built to simplify the process of moving your data to a data lake, cataloging the data, and making it available for querying. Typical scenarios where this comes into play include:

• Build data lakes quickly – this means days not months. You can move, store, update and catalog your data faster, plus automatically organize and optimize your data.
• Add Authorization on your Data Lake  – You can centrally define and enforce security, governance, and auditing policies.
• Make data easy to discover and share – Catalog all of your company’s data assets and easily share datasets between consumers.

To understand how this works in practice, check out our article on using Redshift Spectrum in Lake Formation.

What is AWS Glue?

AWS Glue is a serverless data integration service that makes it easy to discover, prepare, and join data for analytics, machine learning, and application development. AWS Glue consists of a central metadata repository known as the AWS Glue Data Catalog which discovers and catalogs metadata about your data stores or data lake.  Using the AWS Glue Data Catalog, users can easily find and access data.

Glue ETL can be used to run managed Apache Spark jobs in order to prepare the data for analytics, perform transformations, compact data, and convert it into columnar formats such as Apache Parquet.

Read more: What’s the difference between Athena and Glue?

When should you use AWS Glue?

To make data in your data lake accessible, some type of data catalog is essential. Glue is often the default option as it’s well-integrated into the broader AWS ecosystem, although you could consider open-source alternatives such as Apache Iceberg. Glue ETL is one option to process data, where alternatives might include running your own Spark cluster on Amazon EMR or using Databricks.

Typical scenarios where you might use include:

• Create a unified data catalog to find data across multiple data stores – View the Data Catalog to quickly search and discover the datasets that you own, and maintain the relevant metadata in one central repository.
• Data Catalog for data lake analytics with S3 – Organize, cleanse, validate, and format data for storage in a data warehouse or data lake
• Build ETL pipelines to ingest data into your S3 data lake. 

The data workflows initiated from AWS Lake Formation blueprint are executed as AWS Glue jobs. You can view and manage these workflows in either the Lake Formation console and the AWS Glue console.

AWS Lake Formation vs AWS Glue: A Summary

AWS Lake formation simplifies security and governance on the Data Lake whereas AWS Glue simplifies the metadata and data discovery for Data Lake Analytics. While both of these services are used as data lake building blocks, they are complimentary. Glue provides basic functionality needed in order to enable analytics, including data cataloging and ETL; Lake Formation offers a simplified way to manage your data lake, including the underlying Glue jobs.

Check out our community roundtable where we discuss how you can build simple data lake with the new stack: Presto + Apache Hudi + AWS Glue and S3 = The PHAS3 stack

How do I sync my partition and metastore in Presto?

Sync partition metadata is used to sync the metastore with information on the file system/s3 for the external table. Depending upon the number of partitions the sync can take time.

Here is a quick reference from the presto docs: https://prestodb.io/docs/current/connector/hive.html?highlight=sync_partition_metadata

Procedures:

• system.create_empty_partition(schema_name, table_name, partition_columns, partition_values)
  Create an empty partition in the specified table.
• system.sync_partition_metadata(schema_name, table_name, mode, case_sensitive)
  Check and update partitions list in metastore. There are three modes available:
  • ADD : add any partitions that exist on the file system but not in the metastore.
  • DROP: drop any partitions that exist in the metastore but not on the file system.
  • FULL: perform both ADD and DROP.

The case_sensitive argument is optional. The default value is true for compatibility with Hive’s MSCK REPAIR TABLE behavior, which expects the partition column names in file system paths to use lowercase (e.g. col_x=SomeValue). Partitions on the file system not conforming to this convention are ignored, unless the argument is set to false.

If you want to get started with Presto easily, check out Ahana Cloud. It’s SaaS for Presto and takes away all the complexities of tuning, management and more. It’s free to try out for 14 days, then it’s pay-as-you-go through the AWS marketplace.

What are the AWS Glue partition limits and does it apply to AWS Athena?

Typically you’ll use AWS Glue to create the data sources and tables that Athena will query. You could also create your tables using Athena and then use the tables in AWS Glue. Today the limit for AWS Glue partitions is 10M while Athena’s partition limit is 20K partitions per table. So if you’re using AWS Glue you get more partitions if you’re using the AWS Glue catalog with your Athena deployment. 

When it comes to using Amazon Athena, there are a lot of other limits besides partitions including query, concurrent query, and database limits. Many times AWS Athena users find these limits to hinder SLAs and other business-critical applications. As a result, they’re looking for alternatives to AWS Athena and have found that Ahana Cloud’s Presto managed service can address those issues.

Ahana Cloud is a Presto managed service that gives users the power of Presto, similar to AWS Athena, but without the limitations around queries, performance, and control. With Ahana you get the full control of your Presto deployment on AWS, so you can scale as you need without having to worry about partition or query limits.

Learn more about how Ahana Cloud compares to AWS Athena.

Ready to get started? Sign up try out Ahana Cloud for free – if you’re an AWS Athena user, it’s really easy to move from Athena to Ahana Cloud and requires not change in your existing architecture.

You can also check out the case study from ad tech company Carbon on why they moved from AWS Athena to Ahana Cloud for better query performance and more control over their deployment.

What level of concurrency performance can I expect using Presto as part of the AWS Athena service?

I’m getting a lot of my workloads queued up when I use AWS Athena. What are my other options?

Background:

Concurrency is an important factor when it comes to submitting a lot of query workloads. Many data platform teams depend on being able to handle multiple requests from multiple users at the same time. In AWS Athena you can submit queries but they won’t always be running. The number of concurrently running queries provides you with your overall performance. As you may know, AWS Athena is serverless, so you don’t see much behind the curtains. As such depending on what amount the load is on the shared service (with other customers), you’ll see different operating characteristics, leading to non-deterministic performance. 

When your number of running queries hits a limit, any additional queries will be put into a queue to wait for queries to finish. We’ve heard that queuing can occur when there are as low as 3 or 5 concurrently running queries. This can make it difficult to provide consistent levels of performance to your users. 

But I thought you could increase the service limits to address the queuing behavior?

Yes and no. AWS Athena has soft limits of the number of active queries. But it turns out that raising those limits don’t change the number of concurrently running queries, instead it seems to allow you to submit more requests without getting the “TooManyRequest” exception and there may be more queries that are in a RUNNING/0KB state, meaning they’re queued. 

Other alternatives

If you need to have consistent performance across a large number of queries that another approach would be to run your own Presto service. AWS Athena is a serverless version of Presto but as mentioned, has limits on performance. In addition, the pay per TB scanned model of Athena can also be cost prohibitive. There are 2 options for running your own query service: 

1. Operating your own Presto clusters with AWS EMR or by yourself using AMIs
2. Using a managed service like Ahana Cloud for Presto

On #2, Ahana Cloud for Presto provides you with a scalable amount of concurrent query performance but without the operations burden of managing your own clusters. Because the Presto is a distributed SQL query engine, by adding more Presto instances you can increase the amount of queries that are running at the same time:

To compare the performance of a set of query workloads, it’s always best to look at the overall throughput, which means including the wait time associated with queued workloads in Athena. It is for this reason that running your own Presto service will always allow you to have higher performance with more flexibility than AWS Athena. 

This also has price-performance considerations and taking into account the wait time associated with queued workloads, you’ll see a much higher price-performance ratio when comparing AWS Athena to your own Presto service. 

Check out the case study from ad tech company Carbon on why they moved from AWS Athena to Ahana Cloud for better query performance and more control over their deployment.

Do I have to use AWS Lambda to connect to data sources with Athena?

The Athena Federated Query Journey

AWS announced the public preview of Athena federated query in November 2019 and moved the service to General Availability (GA) in November 2020. As of this writing, Athena supports two versions of Athena – engine 1 based on Presto 0.172, and engine 2 based on Presto 0.217. Among other features, the federated query functionality is only supported on engine 2 of Athena. 

What connectors are supported?

Athena currently supports a number of data sources connectors

• Amazon Athena CloudWatch Connector
• Amazon Athena CloudWatch Metrics Connector
• Athena AWS CMDB Connector
• Amazon Athena DocumentDB Connector
• Amazon Athena DynamoDB Connector
• Amazon Athena Elasticsearch Connector
• Amazon Athena HBase Connector
• Amazon Athena Connector for JDBC-Compliant Data Sources (PostgreSQL, MySQL, and Amazon Redshift)
• Amazon Athena Neptune Connector
• Amazon Athena Redis Connector
• Amazon Athena Timestream Connector
• Amazon Athena TPC Benchmark DS (TPC-DS) Connector

In line with the serverless model of AWS Athena, in order to maintain an impedance match between Athena and the Connectors, the connectors are also Serverless based on AWS Lambda and run within the region. These connectors are open-sourced under the Apache 2.0 license and are available on GitHub or can be accessed from the AWS Serverless Application Repository. Third-party connectors are also available in the serverless application repository.

Athena is based on Presto, Can I use the open-source Presto data source connectors with Athena?

Presto has an impressive set of connectors right out of the box, these connectors however cannot be used as-is with Athena. The Presto service provider interface (SPI) required by the Presto connectors is different from AWS Athena’s Lambda-based implementation which is based on the Athena Query Federation SDK.

You can use the Athena Query Federation SDK to write your own connector using Lamba or to customize one of the prebuilt connectors that Amazon Athena provides and maintains. The Athena connectors use Apache Arrow format for returning data requested in the query. An example Athena connector can be found here.

If you’re looking for a managed service approach for PrestoDB, Ahana Cloud offers that based on open source Presto. Ahana’s managed deployment is 100% compatible with the open source Presto connectors, and you can get started with a click of a button.

Check out the case study from ad tech company Carbon on why they moved from AWS Athena to Ahana Cloud for better query performance and more control over their deployment.

How do I do geospatial queries and spatial joins in Presto?

A question that often comes up is “how do I do geospatial queries and spatial joins in Presto?”. Fortunately Presto supports a wealth of functions and geospatial-specific joins to get the job done.

Let’s get started with a step-by-step tutorial. First we’ll set-up some test data in two tables.  The first table is trips_table which will store 3 rows each representing simple taxi trips. We store a trip id, the origin and destination long/lat coordinates, and the journey start time:

create table trips_table (
trip_id int, 
orig_long double, 
orig_lat double, 
dest_long double, 
dest_lat double, 
trip_start timestamp);

insert into trips_table values (1, 51.50953, -0.13467, 51.503041, -0.117648, cast('2021-03-02 09:00:00 UTC' as timestamp));
insert into trips_table values (2, 34.039874, -118.258663, 34.044806, -118.235187, cast('2021-03-02 09:30:00 UTC' as timestamp));
insert into trips_table values (3, 48.858965, 2.293497,48.859952, 2.340328, cast('2021-03-02 09:45:00 UTC' as timestamp));
insert into trips_table values (4, 51.505120, -0.089522, 51.472602, -0.489912, cast('2021-03-02 10:45:00 UTC' as timestamp));

For information:

• Trip 1 is a ride within central London, from Piccadilly Circus to the London Eye.
• Trip 2 is a ride in downtown Los Angeles
• Trip 3 is a ride from the Eiffel Tower to Musée du Louvre, Paris 
• Trip 4 is a ride from Borough Market in central London to Heathrow Airport Terminal 5 (outside central London).

The second table is city_table with each row storing the shape representing an area e.g. central London, and a name for the shape. We represent the shape with a sequence of coordinates that enclose a specific area: 

create table city_table (
geo_shape varchar, 
name varchar);

insert into city_table values ('POLYGON((51.519287 -0.172316,51.519287 -0.084103,51.496393 -0.084103,51.496393 -0.172316,51.519287 -0.172316))', 'London, central');
insert into city_table values('POLYGON((33.9927 -118.3023,33.9902 -118.1794,34.0911 -118.2436,33.9927 -118.3023))', 'Los Angeles, downtown');

For information:

• I used a simple triangle to represent downtown LA (see illustration below)
• I used a rectangle representing central London. 
• In each case the first pair coordinates for the shape are the same as the last pair – so it’s an enclosed bounding box or polygon we’re describing.
• We’re storing our shapes as text in a varchar column for simplicity.
• We describe each polygon as comma-separated pairs of long/lat coords using the POLYGON(()) function.  The double brackets are required. 

Now let’s run a query to count how many trips occurred in each city. We join our two tables, and we use each journey’s originating long/lat coordinates to determine – using ST_CONTAINS() – if that point exists in any of our shapes.  This function requires the polygon to be expressed as a special type – Geometry – so we convert our shape from text using ST_GeometryFromText() function:

SELECT c.name as City, count(*) as Trips 
FROM trips_table as t 
JOIN city_table as c 
ON ST_Contains(ST_GeometryFromText(c.geo_shape), st_point(t.orig_long, t.orig_lat)) 
GROUP BY 1;

         City          | Trips 
-----------------------+-------
 Los Angeles, downtown |     1 
 London, central       |     2 
(2 rows)

We see both London trips made it into the result set, despite one of the trips ending at the airport which is a way outside the shape we defined for central London – this is because the query uses the originating coordinates for each trip, not the destination coordinates. 

Also notice the Paris trip didn’t make it into the result – this is because we did not define a shape for Paris.

In this example you’ve seen some of the benefits of using Ahana Cloud for Presto. 

Presto’s Geospatial functions are listed in the Presto documentation.  

We have a ton of resources to help you get started with Presto, check them out here.

How do I query JSON documents with Presto?

JSON documents are a common data type. A lot of people collect logs and load them into S3. Querying JSON with Presto can be challenging because you may not always have a consistent schema. However, the great part about Presto is that it has functionality that enables you to get insights on your JSON data.

Presto has a JSON data type. The JSON data type is similar to a string. One way to think about this is that it is just a string containing JSON, with the exception that the data inside the string has already been parsed, it’s already well-formed JSON.

But it’s also slightly semantically different in that JSON has maps. And when you have a string with a map, the keys of the map can be ordered differently. But in the JSON data type, the keys in the map are always going to be in a consistent order. And it’s worth noting that two maps of the data with different JSON data type values will compare the same, even if the keys are in different order. This can be important when doing an aggregation regrouping or a comparison. Logically two maps will be treated as the same value, no matter what the order is.  

How the JSON parse function works

The JSON parse function takes a JSON string and returns a JSON data type. Here is an example of the JSON data type:

SELECT json_parse('null'); -- JSON 'null'
SELECT json_parse('true'); -- JSON 'true'
SELECT json_parse('42'); -- JSON '42'
SELECT json_parse('"abc"'); -- JSON '"abc"'
SELECT json_parse('[1, 2, 3]'); -- JSON '[1,2,3]'
SELECT json_parse('["a": 1, "b": 2]'); -- JSON '["a": 1, "b": 2]'
 
SELECT json_parse('[”hey”, 42, {“xyz”: 100, “abc” : false}]’); 

After its parts with json_parse, the result is that the keys in the map are now ordered. This is how you result with the same JSON data type and how they compare the same. When two different maps with differently ordered keys are parsed, they’ll have the same contents.

In Presto, all the JSON functions take the JSON data type. And if you pass a string into the JSON function, it will be implicitly parsed into JSON.

Another question that comes up is what is the difference of NULL between JSON and SQL? J_null and a SQL null?

This can be confusing because JSON has a NULL and so does SQL. These are two different things. A SQL NULL is considered a special value and work very differently like they do in other programming languages. Operations on a NULL gets turned into a NULL. Whereas a JSON NULL is just another value, like a number or a string or an array. So they’re totally different. This has implications when doing Boolean expressions.

We have a lot more resources on Presto if you’d like to learn more, check out our Getting Started with Presto page.

Is there latency overhead for Presto queries if everything fits into memory and doesn’t need to be distributed?

Presto is both in-memory and distributed, so each work has memory and uses it. However, Presto is not an in-memory database. Presto is the query engine and reads from storage underneath. This is where Presto’s caching capabilities become important.

If all the data required to satisfy a given query arrives from the connected data sources and fits within the memory of the Presto cluster, everything should work accordingly. However, Presto’s MPP, in-memory pipelining architecture will accelerate workloads further in two ways:

1) Presto will ensure data is divided across all the workers to bring maximum processing power and memory capacity to bear.

2) Presto’s generated query plan will ensure execution steps are distributed amongst all the workers such that processing takes place in parallel on the data across all workers simultaneously, as efficiently as possible. Furthermore, with its caching capabilities, Presto can accelerate query execution even further for specific workload patterns, further reducing latency.

If you want to get started with Presto, check out our docs to learn more about the Ahana Cloud managed service for Presto.

Is the Hive metastore a hard dependency of Presto, or could Presto be configured to use something else like Postgres?

With Presto, there’s no hard dependency of having to use the Hive metastore – it’s catalog-agnostic. However, there are significant advantages like better performance when you use the Hive metastore.

A managed service like Ahana Cloud for Presto provides a managed version of a Hive metastore catalog, so you don’t have to deal with the complexity of managing their own metastore. Ahana also supports AWS Glue as a data catalog service.

You don’t necessarily have to use the metastore if you are connecting via the provided connectors such as MySQL, PostGres, Redshift, Elastic, etc. But if you intend to query data on S3, for instance, you will need a metastore (i.e. Hive or Glue) to define external tables and their formats.

Presto has many connectors including Postgres, which is a supported data source.

The Differences Between Apache Drill vs Presto

Drill is an open source SQL query engine which began life as a paper “Dremel: Interactive Analysis of Web-Scale Datasets” from Google in 2010. Development of Apache Drill began in 2012.

Performance & Concurrency
Drill has never enjoyed wide adoption partially because it was tied to one Hadoop distribution (MapR) but mainly because of inherent performance and concurrency limitations. There are companies that built products based on Drill who report these performance and concurrency issues, and many have migrated away from Drill as a result. Presto’s popularity and adoption, on the other hand, has exploded as numerous companies from SME to web-scale have deployed Presto and contribute to the project. These include Facebook, Uber, Alibaba, Twitter, Netflix, AirBnB and LinkedIn. Users value Presto’s vendor-neutrality and rate of innovation.

Metastores
Presto connects to external metastores (AWS Glue, Hive Metastore Catalog); many users deploy Presto + AWS Glue/Hive for their data lake analytics. In addition, schema for relational data sources can be obtained directly from each connector. On the other hand, Drill performs its own schema discovery and does not need a metastore such as Hive (but can use Hive if needed), which is a benefit.

Community
Compared to Presto, the Apache Drill community has dwindled somewhat especially when it comes to adoption and contributions. We talk to many users who are looking to move from Apache Drill to Presto. If we look at DB engines – https://db-engines.com/en/ranking/relational+dbms – we see that Presto has continued on a positive upward trend and ranks #27, as opposed to Drill which is at #49 in a downward trend.

Overall, Drill is in decline. This was accelerated when HPE (Hewlett Packard Enterprise, who acquired MapR) announced they will no longer support or contribute to Drill (a drawback of having a query engine tied to an ecosystem such as Hadoop). Presto’s popularity continues to increase, as illustrated by 1) the number of commits to the project (In the past month 46 authors have pushed 79 commits to master and 88 commits to all branches, and on master 568 files have changed and there have been 10,930 additions – as of Feb 26 2021), and 2) Presto’s continued rise on DB-engines.

Drill is a top-level Apache Foundation project but does not have a strong and active community behind it. Presto is backed by a strong community and is overseen by the Presto Foundation which is part of the Linux Foundation with 8 premier organizations driving it including Facebook, Uber, Twitter, Intel and Ahana.

Want more Presto tips & tricks? Sign up for our Presto community newsletter.

Why am I getting zero records when I use AWS Athena to query a CSV file?

There’s a common error many AWS Athena users see when they query CSV files – they will get zero records back. This tends to happen when they run the AWS Glue crawler and create a new table on the CSV file. There are several reasons why the query might return no records.

1. The crawler is pointing to a file instead of an AWS S3 bucket
2. The LOCATION path is wrong or there’s a double slash in it
3. The partitions haven’t loaded into the Glue Data Catalog or the internal Athena data catalog
4. In general, CSV crawlers can be sensitive to different issues (i.e. embedded newlines, partially quoted files, blanks in integer fields), so make sure everything is accurate

On the first point, if you have selected a file instead of your S3 bucket, the crawler will succeed but you won’t be able to query the contents which is why you’ll see the ‘Zero Records Returned’ error message. If there are other files that you don’t want crawled, you’ll need to create a new folder and move your CSV to that new folder, and then update the include path accordingly (and you’ll need to re-crawl it).

In order to get your S3 data lake to work, you’ll need to make sure each batch of same-schema files has its own top level directory.

If you’re running into issues like this with AWS Athena and want to offload the management while still getting the power of Presto, check out Ahana Cloud. It’s a fully managed service for AWS that removes these types of complexities and makes it really easy to run Presto in the cloud.

Check out the case study from ad tech company Carbon on why they moved from AWS Athena to Ahana Cloud for better query performance and more control over their deployment.

Does Presto work natively with GraphQL?

Some users may have a primary data store that is GraphQL-based (AWS AppSync) and want to leverage Presto. For context, GraphQL falls in the application as part of the service mesh infrastructure. It is an abstraction API for mostly operational sources.

Typically for analytics, users move their data into an analytical stack, because the properties and queries of an operational stack and analytical stack are quite different.

Users with your environment move the data to S3 to run data lake analytics. Presto is the de facto engine for data lake analytics, but in addition to that is also allows for running queries across S3 and operational systems like RDS.

Running analytics on GraphQL / AppSync is not recommended because:

1. GraphQL isn’t really a query engine with an optimizer, etc.
2. Analytical queries are fairly intensive from a compute perspective and needs the right analytical engine to process and execute the queries. GraphQL is mostly a pass through and this will affect your operational systems.

Want to learn more about Presto? Download our free whitepaper: What is Presto?

Why does a single AWS Athena query get stuck in QUEUED state before being executed?

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 a bit for Athena users is single queries getting stuck in a QUEUED state before being executed. This happens because of Athena’s serverless nature in combination with its multi-tenant service – you are essentially competing for resources when you run a query.

Many users will get the message “Query waiting in queue” somewhat randomly. For this reason, it’s recommended to not use Athena for user-facing requests because of its unpredictability, and generally many users have decided they can’t rely on Athena at all based on this issue. If you can’t predict demand and manage multi-tenancy, it’s not ideal for ad-hoc analytics (which is a core use case for PrestoDB).

For users who still want to leverage PrestoDB for ad-hoc analytics and data lake analytics, Ahana Cloud is a solution that allows you to run Presto transparently as a managed service. You won’t run into the issues around unpredictability and queries getting stuck. There’s a 14 day free trial if you’re interested in checking it out.

Check out the case study from ad tech company Carbon on why they moved from AWS Athena to Ahana Cloud for better query performance and more control over their deployment.

How Presto Joins Data

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 from the client and optimises and plans the query execution, breaking it down into constituent parts, to produce the most efficient execution steps. The execution steps are sent to the workers which then use the connectors to submit tasks to the data sources. The tasks could be file reads, or SQL statements, and are optimised for the data source and the way in which the source organises its data, taking into account partitioning and indexing for example.

The data sources supported by Presto are numerous and can be an RDBMS, a noSQL DB, or Parquet/ORC files in an object store like S3 for example. The data sources execute the low level queries by scanning, performing filtering, partition pruning etc. and return the results back to the Presto workers. The Presto join operation (and other processing) is performed by the workers on the received data, consolidated, and the joined result set is returned back to the coordinator.

You will notice Presto uses a “push model” which is different, for example, to Hive’s “pull model”. Presto pushes execution steps to the data sources, so some processing happens at the source, and some happens in Presto’s workers. The workers also communicate between each other, and the processing takes place in memory which makes it very efficient, suitable for interactive queries.  Hive on the other hand will read/pull a block of a data file, execute tasks, then wait for the next block, using the map reduce framework. Hive’s approach is not suitable for interactive queries since it is reading raw data from disk and storing intermediate data to disk, all using the framework MapReduce, which is better suited to long-running batch processing. This diagram compares Hive and Presto’s execution approaches:

How Presto Joins Relational and Non-Relational Sources

The next diagram shows some of Presto’s core Coordinator components, and the kinds of tasks   Presto’s workers handle. In this simplistic example there are two data sources being accessed; one Worker is scanning a Hive data source, the other worker is scanning a mongoDB data source. Remember Presto does not use Hive’s mapreduce query engine or HQL – the diagram’s “hive” worker means it is using the “hive connector” and the file system is the metastore information, and the raw source data is external to Presto, maybe in HDFS in Parquet or Orc format, for example.  The Worker dealing with mongo data is described as being on “the probe side” (in this example) whereby the mongo data is read, processed, normalized into columnar stores in Presto and then shuffled (or exchanged) across the cluster to the “builder” worker (the worker dealing with the hive data here) for the actual Presto joins to take place.

This is a simplistic example since in reality Presto is more sophisticated – the join operation could be running in parallel across multiple workers, with a final stage running on one node (since it cannot be parallelized). This final stage is represented by the third worker at the top of the diagram labeled  “Output”. This outputNode’s task is to stream out the result set back to the coordinator, and then back to the client.

To see an example of a complex join in Presto, check out our previous article on CROSS JOIN UNNEST.

Joins – Summary

It’s easy to see how Presto is the “Polyglot Data Access Layer” since it doesn’t matter where your data lives, any query can access any data, in-place, without ETL or data shipping or duplication. It is true federation. Even when blending very different sources of data, like JSON data in elasticsearch or mongodb with tables in a MySQL RDBMS, Presto takes care of the flattening and processing to provide a complete, unified view of your data corpus.

If you want to try out Presto, take a look at Ahana Cloud. It provides a managed service for Presto in AWS.

Using Spark’s Execution Engine With Presto

Learn when to use Spark as an additional engine alongside open-source Presto, and how you can configure and invoke a Spark job from Presto.

Executing Presto Spark queries 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 are in-memory execution engines tends to add further confusion.

Presto and Spark are different kinds of engines – Presto specializes in analytical query execution; Spark’s emphasis is on calculation. The following should help clarify why Presto users would or would not want to use Spark as an additional engine. 

A Quick Presto/Spark Comparison 

Executing Presto queries on Spark can be useful for some very specific workloads,  such as queries that we want to run on thousands of nodes, or for queries requiring 10s or 100s of TBs of memory, queries that may consume many CPU years, or if we need extra execution resilience.  So if you have a particularly ugly, complex and very long-running query, this may be an option. 

Spark provides several features that can be desirable for certain workloads, like:

• Resource isolation
• Fine-grained resource management
• Spark’s scalable materialized exchange mechanism 
• In terms of memory usage, both are in-memory execution engines but with one important difference: Spark will write/spill data to disk when memory is exhausted.
• Spark has data processing fault tolerance, so if a worker node fails and its data partition goes away,that partition will be re-computed. 

There are also some downsides, or at least considerations, when using Spark with Presto: 

• Spark commits tasks and applies for resources dynamically as it needs them. So at each stage tasks will grab their required resources, a strategy that can slow down processing speed. Presto on the other hand does this “upfront” on the most part..
• The way in which data is processed by the two engines differs: Spark needs data to be fully processed before progressing to the next processing stage. Presto uses a pipeline processing approach where data is sent to the next task as soon as possible which can reduce total execution time.
• Spark SQL supports a subset of the ANSI SQL standard. Many features are missing, and for this reason many developers avoid Spark SQL. 

When To Use Spark’s Execution Engine With Presto

It is recommended that you:

• Do not use Spark for interactive queries. This is not what Spark was designed for, so performance will be poor.
• Do use Spark for long-running, SQL-based batch ETL queries, provided Spark SQL supports the features and functions you need. You can run ETL/batch type queries in Presto, but you may choose to use Spark as the execution engine with Presto if a user / developer wants to test their query in adhoc mode using Presto first and later convert it to Spark when they want to run a batch pipeline in production.

Another scenario where Presto and Spark can be used together is if there is no connector available in Spark for the data source you want to access then use Presto and its connectors to access the data then access Presto via JDBC from Spark.

Setup  

Setting up Presto and Spark and submitting jobs is well documented in PrestoDB’s docs at https://prestodb.io/docs/current/installation/spark.html and it is straightforward to set-up.  You will need Java and Scala installed. You need a working Presto cluster, and a Spark cluster of course. Then there are two packages to download, and a file to configure. You then execute your queries from the Spark cluster, by running spark-submit.

Tip: If you are using a Mac, standalone Spark can be installed very simply with brew install apache-spark. Similarly Presto can be installed with  brew install prestodb. 

Verify your Spark cluster by running spark-shell on the command line.

Configuration

Presto’s config.properties file needs to have its task.concurrency, task.max-worker-threads and task.writer-count parameters set to values appropriate for your hardware. Ensure these values also match the parameters used with the spark-submit command.

Invocation 

Use the spark-submit command to invoke Spark, passing the sql query file as an argument. See https://prestodb.io/docs/current/installation/spark.html for an example spark-submit command. 

The above article should help you understand the how, why and when of executing Presto queries on Spark.

When I run a query with AWS Athena, I get the error message ‘query exhausted resources on this scale factor’. Why?

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. One part of the issue may be due to how many columns the user has in the Group By clause – even a small amount of columns (like less than 5 columns) will run into this issue of not having enough resources to complete. Other times it may be due to how much data is being parsed, and again even small amounts of data (like less than 200MB) will run into this issue of not having enough resources to complete.

Presto stores Group By columns in memory while it works to match rows with the same group by key. The more columns that are in the Group By clause, the fewer number of rows that will get collapsed with the aggregation. To address this problem, users will have to reduce the number of columns in the Group By clause and retry the query.

And still at other times, the issue may not be how long the query takes but if the query runs at all. Users that experience “internal errors” on queries one hour will re-run the same queries that triggered those errors and they will succeed.

Ultimately, AWS Athena is not predictable when it comes to query performance. That’s where Ahana Cloud, a managed service for Presto, can help. Ahana’s managed service for PrestoDB can help with some of the trade offs associated with a serverless service.

Some of the reasons you might want to try a managed service if you’re running into performance issues with AWS Athena:

• You get full control of your deployment, including the number PrestoDB nodes in your deployment and the node instance-types for optimum price/performance. 
• Consistent performance because you have full control of the deployment
• Ahana is cloud-native and runs on Amazon Elastic Kubernetes (EKS), helping you to reduce operational costs with its automated cluster management, increased resilience, speed, and ease of use. 
• No limits on queries

Plus you can use your existing metastore, so you don’t need to modify your existing architecture.

Check out the case study from ad tech company Carbon on why they moved from AWS Athena to Ahana Cloud for better query performance and more control over their deployment.

Creating tables in a S3 Bucket gives “Query failed External location must be a directory”

So here’s why you are here. You are using Presto’s Hive connector and you want to create a new table using an S3 folder that already exists. You have full read/write access to the folder, you can see the folder, but Presto tells you “External location must be a directory”.

Whiskey Tango Foxtrot – The directory isn’t a directory? 

In this example, you have already created your target directory myDir using the AWS S3 console, in a bucket called myBucket,  but attempts to create the customer table in that directory fail:

presto> CREATE TABLE hive.customer ( customer_id bigint, email varchar(64) ) 
WITH ( external_location = 's3a://myBucket/myDir/' , format = 'PARQUET' );

Query 20210104_110546_00015_mhsym failed: External location must be a directory

The issue here is not immediately obvious, read on further to learn more.

Why is the issue occurring?

S3 is an immutable object store and not a traditional file system. Objects are stored as key-value pairs internally, the key is the “Path” and the value is the actual object. There is no such thing as a folder in the traditional filesystem sense of the world. All there is are objects and associated metadata. If the path doesn’t have the appropriate metadata associated with it (in this case content-type: ‘application/x-directory’) Presto will complain that the path is not a directory

You have 2 primary options of addressing the issue:

1. Manually associate the metadata via the AWS Console UI
2. Copy the object over again using S3 command line or python script

Note: Since S3 is an immutable store, so there is no way to simply “update the metadata”. Under the covers, if you use the AWS Console, the UI is performing a clone of the object and updating the metadata. If you need to do it via API you have to perform a copyObject and update the metadata. 

See snippet from the AWS docs:  https://docs.aws.amazon.com/AmazonS3/latest/userguide/UsingMetadata.html

“You can set object metadata in Amazon S3 at the time you upload the object. Object metadata is a set of name-value pairs. After you upload the object, you cannot modify object metadata. The only way to modify object metadata is to make a copy of the object and set the metadata.“

So how do I fix the issue?

Option 1: Change metadata using the AWS Console UI

Choose “Edit metadata” for the path in question

Add ‘Content-Type’ as ‘application/x-directory’ as a system defined metadata

Option 2: Change metadata using tools (copy objects over again)

AWS Cli

aws s3 cp --content-type 'application/x-directory' s3://<bucket>/<object> s3://<bucket>/<object> --metadata-directive REPLACE

S3 API:

#Put with metadata
╰$ aws s3api put-object --bucket mybucket --key folder_name_xxx/ --content-type application/x-directory
{
    "ETag": "\"d41d8cd98f00b204e9800998ecf8427e\""
}
 
#Verify the metadata
 
╰$ aws s3api head-object --bucket mybucket --key folder_name_xxx/
{
    "AcceptRanges": "bytes",
    "LastModified": "Fri, 23 Apr 2021 16:03:21 GMT",
    "ContentLength": 0,
    "ETag": "\"d41d8cd98f00b204e9800998ecf8427e\"",
    "ContentType": "application/x-directory",
    "Metadata": {}
}

Python Script

import boto3
s3 = boto3.resource('s3')
s3_object = s3.Object('bucket-name', 'key')
s3_object.metadata.update({'Content-Type':'application/x-directory'})
s3_object.copy_from(CopySource={'Bucket':'bucket-name', 'Key':'key'}, Metadata=s3_object.metadata, MetadataDirective='REPLACE')
 
Alternatively:
s3 = boto3.client('s3')
s3.put_object(Bucket=BUCKET, Key=KEY, ContentType='application/x-directory')

The above-mentioned steps should help you resolve the issue and your create table should work fine without complaining about the ‘External location must be a directory’ error.

--Now CREATE TABLE works fine
presto> CREATE TABLE hive.customer ( customer_id bigint, email varchar(64) )
 WITH ( external_location = 's3a://myBucket/myDir/' , format = 'PARQUET' );
CREATE TABLE

Big Data Query

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 source distributed query engine,  gives users the ability to interactively query any data and any data source at any scale.  And it is proven – Presto has been in large-scale production deployment at internet-scale companies like Facebook, Uber, and Twitter for several years and the project is enjoying continued innovation as a result. 

Ahana’s mission is to simplify ad hoc analytics for organizations of all shapes and sizes by making open source Presto a fully integrated, cloud-native, managed service for AWS – the easiest Presto experience there is. 

Utilizing Big Data

The role of a data analyst, and those of other members within a data team, is to discover patterns, trends and correlations in large datasets. Large data analytics is the umbrella term for such activities as generating reports, creating dashboards, to running complex queries.  A big data query is a request for data from a database table or from a combination of many different tables or files, which may be in different file formats, in relational databases and in object storage like S3.

Subtitle for This Block

Title for This Block

Text for This Block

In the past, data analysts and data teams (which usually is comprised of a variation of members including: data engineers, data architects, and data scientists) typically queried data stored in traditional relational databases and data warehouses – like a data warehouse. These days, the amount of data being generated is too big to be stored solely in on-prem relational databases; as a result, big data is now increasingly stored in the cloud and/or in distributed clusters. To query these huge and disperse datasets, you need a distributed query engine like Presto.

The good news is, if you work with terabytes or even petabytes of data, Presto is the ideal high-performance, distributed SQL query engine for actively querying expansive and diverse datasets. It is an open source, distributed SQL query engine that is specifically designed to run big data queries against data of any size, on a wide variety of sources. It makes use of multiple connectors which allow you to access these data sources and query them in place; there is no need to move the data.

One of the main benefits of Presto is that it separates data storage from processing, so analysts can query big data where it is stored without having to move all of the data into a separate analytics system. With just a single Presto query, you can query big data from multiple sources with fast response times ranging from sub-seconds to minutes. Another benefit of Presto is that big data analysts can use standard query language (SQL) to query the data, which means they don’t have to learn any new complex languages.

This is why Presto is becoming the de facto standard for Big Data Querying.

Related Articles

A Comprehensive Guide to Data Warehouse Types

A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. Learn more about what these data warehouse types are, what they are not, and the benefits they provide to data analytics team members within organizations.

Presto vs Snowflake: Data Warehousing Comparisons

Presto is an open-source SQL query engine, developed by Facebook, for large-scale data lakehouse analytics. Snowflake is a cloud data warehouse that offers a cloud-based information storage and an analytics service. Learn more about the differences between Presto and Snowflake in this article.

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?

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 have numerous instances of RDS with numerous databases for each customer, whether internal or external. When one customer tries a large analytic query, that workload can cause problems with the RDS cluster, perhaps making it to drop other workloads, fail, or slow down for others. As the needs for processing huge amounts of data increases, so does the need to take your analytics to the next level.

In addition to your operational databases, the idea is to have a much more open analytics stack where you have the ability to run different kinds of processing on the same data. A modern analytics stack lets your organization have much more insights without impacting your operational side. And doing that with open data formats is another key consideration.

Considerations

There’s a couple options for evolving your analytics stack. One would be to use a cloud data warehouse like Amazon Redshift or Snowflake. Another would be to use open formats in a data lake with a modern SQL query engine. In the first case, there are some advantages of having the highest performance possible on your data but it comes at a certain cost as well as an amount of lock-in, as you cannot easily get at the data in proprietary formats. Considering the data lake with query engine option, we believe that Presto is one of the best choices because of its performance, scalability, and flexibility to connect to S3-based data lakes and federate other data sources as well.

So our recommendation would be to run Presto on top of data stored in an open Parquet or ORC format in S3. Doing it this way, you can put other engines on top of the data as needed, do you’re not going to face a lot of rework in the future should you decide to change something.

From OLTP to Data Lake Analytics

The high level concept is to have an initial one-time bulk migration of your data in OLTP databases to get a copy moved in to S3. After that, as your operational databases will continue to generate or change data, you’ll need establish a pipeline, a stream, or a Change Data Capture (CDC) process in place to get those into S3. BTW, not often will you want data going back from S3 into your relational databases.

While there are different ways to pipe data into S3, one AWS recommended approach is to use the AWS Database Migration Service a.k.a. DMS(much in the same way you may have used it when you migrated off-prem). With AWS Database Migration Service, you can get the first one-time bulk load and then continuously replicate your data with high availability and stream data to Amazon S3. AWS DMS would run in the background and handle all of the data changes for you. You can pick the instance and what period of time you’d like it to run, for example, you may want it to run hourly partitions or daily partitions. That’ll depend on how fast your data is changing and what your requirements are for analytics as well.

Next you’ll want to install Presto on top, and for that you can build a Presto cluster yourself, or simply use Ahana Cloud for Presto to create pre-configured clusters in about 30 minutes.

It’s also worth noting that after you’ve ingested the data into S3 based on what you think the most optimized format or folder structure would be, you may find out that you you need it different. In that case, not to worry, you can use Presto itself to do data lake transformations as well, using SQL you can do a CTAS, Create Table As Select:

The CREATE TABLE AS SELECT (CTAS) statement is one of the most important SQL features available. CTAS is a parallel operation that creates a new table based on the output of a SELECT statement. CTAS is the simplest and fastest way to create and insert data into a table with a single command.

Summary

By adding a modern analytics stack to your operational databases, you evolve your analytics capabilities and deliver more insights for better decisions. We suggest moving to an open data lake analytics reference architecture with Presto. This allow a meta analysis of all your data, giving a look at broader trends across databases and other data sources.

Complex SQL Queries

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 in a single query. 

To ensure optimum performance with complex queries Presto has features like dynamic filtering which can significantly improve the performance of queries with selective joins by avoiding reading data that would be filtered by join condition.  And the collection of table statistics using ANALYZE tablename is highly recommended. 

Complex SQL features and functions are used when advanced calculations are needed, or when many tables (perhaps from multiple sources) are to be joined, when dealing with nested or repeated data, dealing with time-series data or complex data types like maps, arrays, structs and JSON, or perhaps a combination of all these things.  

Presto’s ANSI SQL engine supports numerous advanced functions which can be split into the following categories – links to the PrestoDB documentation are provided for convenience:

• Functions for JSON and other complex data types – https://prestodb.io/docs/current/functions/json.html 
• Advanced aggregation techniques – https://prestodb.io/docs/current/functions/aggregate.html 
• Window functions – https://prestodb.io/docs/current/functions/window.html 
• Array and map functions – https://prestodb.io/docs/current/functions/array.html and https://prestodb.io/docs/current/functions/map.html 
• Lambda expressions to assist with nested data – https://prestodb.io/blog/2020/03/02/presto-lambda 
• Geospatial – https://prestodb.io/docs/current/functions/geospatial.html 
• Sampling – https://prestodb.io/docs/current/sql/select.html “Table Sampling” section.

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 to be joined, when dealing with nested or repeated data, dealing with time-series data or complex data types like maps, arrays, structs and JSON, or perhaps a combination of all these things.  

Presto’s ANSI SQL engine supports numerous advanced functions which can be split into the following categories – links to the PrestoDB documentation are provided for convenience:

• Functions for JSON and other complex data types – https://prestodb.io/docs/current/functions/json.html 
• Advanced aggregation techniques – https://prestodb.io/docs/current/functions/aggregate.html 
• Window functions – https://prestodb.io/docs/current/functions/window.html 
• Array and map functions – https://prestodb.io/docs/current/functions/array.html and https://prestodb.io/docs/current/functions/map.html 
• Lambda expressions to assist with nested data – https://prestodb.io/blog/2020/03/02/presto-lambda 
• Geospatial – https://prestodb.io/docs/current/functions/geospatial.html 
• Sampling – https://prestodb.io/docs/current/sql/select.html “Table Sampling” section.

Running advanced SQL queries can benefit greatly from Presto’s distributed, in-memory processing architecture and cost-based optimizer. 

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 server start
presto-admin server restart
presto-admin server status

If you are using Presto on EMR, you can EMR restart Presto with:

sudo stop presto
sudo start presto

(If you have changed any configuration params you should do this on every node where you made a change).

The above “How To Stop Presto” information is correct for PrestoDB. But other “hard forks” of Presto may use different methods to stop and start. 

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 web site https://prestodb.io/ and GitHub have all the information you need on Presto new releases:

Releases: https://github.com/prestodb for the Presto and Presto-admin repositories.

Release Notes: https://prestodb.io/docs/current/release.html 

Documentation: https://prestodb.io/docs/current/ 

The above list has all the main resources you need for working with Presto’s New Releases. If you’re looking to get up and running quickly with Presto, Ahana Cloud is a SaaS for Presto platform. Presto can be complicated with over 200 parameters to configure and tune – Ahana Cloud takes care of all those; all you do is point and click to get your Presto cluster up and querying your data sources. You can sign up for a free trial and check it out at https://ahana.io/sign-up

How Much Memory To Give A Presto Worker Node

Presto is an in-memory query engine and so naturally memory configuration and management is important. A common question that comes up is how much memory should I give a worker node?

JVM Memory

Presto’s JVM memory config nearly always needs to be configured – you shouldn’t be running Presto with its default setting of 16GB of memory per worker/coordinator. That’s the Presto max.

The Xmx flag specifies the maximum memory allocation pool for a Java virtual machine. Change the  -Xmx16G in the jvm.config file to a number based on your cluster’s capacity, and number of nodes.  See https://prestodb.io/presto-admin/docs/current/installation/presto-configuration.html on how to do this.  

Rule of Thumb

It is recommended you set aside 15-20% of total physical memory for the OS.  So for example if you are using EC2 “r5.xlarge” instances which have 32GB of memory,  32GB-20% = 25.6GB so you would use  -Xmx25G in the jvm.config file for coordinator and worker (or  -Xmx27G if you want to go with 15% for the OS).

This is assuming there are no other services running on the server/instance, so maximum memory can be given to Presto. 

Presto Memory 

Like with JVM above, there are two memory related settings that you should check before starting Presto. For most workloads Presto’s other memory settings will work perfectly well when left at their defaults. There are configurable parameters that control memory allocation that could be useful for specific workloads however.  The practical guidelines below will 1) help you decide if you need to change your Presto memory configuration, and 2) which parameters to change.

Workload Considerations

You may want to change Presto’s memory configuration to optimise ETL workloads versus analytical workloads, or for high query concurrency versus single-query scenarios.  There’s a great in-depth blog on Presto’s memory management written by one of Presto’s contributors at  https://prestodb.io/blog/2019/08/19/memory-tracking which will guide you in making more detailed tweaks. 

Configuration Files & Parameters 

When first deploying Presto there are two memory settings that need checking. Locate the config.properties files for both the coordinator and worker. There are two important parameters here: query.max-memory-per-node and query.max-memory.  Again see https://prestodb.io/presto-admin/docs/current/installation/presto-configuration.html for rules-of-thumb and how to configure these parameters based on the available memory. 

The above guidelines and links should help you when considering how much memory should you give a worker node. You can see there’s a lot of tuning and config’s to manage – over 200. To avoid all memory configuration work you can use Ahana Cloud for Presto – a fully managed service for Presto, that needs zero configuration. Sign up for a free trial at https://ahana.io/sign-up.

How to Show Tables From All Schemas with Presto

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; but this only reveals the tables managed by that data source.

There is no equivalent way to show all tables in all schemas for a data source. However there’s the metastore to fall back on which we can query: In MySQL, Glue, Hive and others there is a schema called “information_schema” which contains a table called “tables”.  This maintains a list of  schemas and tables relating to that data source.

For a MySQL data source, here’s what Presto shows in a Presto table:

presto> select table_schema, table_name from mysql.information_schema.tables order by 1,2;
    table_schema    |                  table_name                   
--------------------+-----------------------------------------------
 demo               | geography                                     
 demo               | state                                         
 demo               | test                                          
 information_schema | applicable_roles                              
 information_schema | columns                                       
 information_schema | enabled_roles                                 
 information_schema | roles                                         
 information_schema | schemata                                      
 information_schema | table_privileges                              
 information_schema | tables                                        
 information_schema | views                                         
 sys                | host_summary                                  
 sys                | host_summary_by_file_io                       
 sys                | host_summary_by_file_io_type                  
...

For Ahana’s integrated Hive metastore:

presto:demo> select table_schema, table_name from ahana_hive.information_schema.tables order by 1,2;
    table_schema    |        table_name         
--------------------+---------------------------
 csv_test           | yellow_taxi_trips         
 csv_test           | yellow_taxi_trips_orc     
 csv_test           | yellow_taxi_trips_staging 
 information_schema | applicable_roles          
 information_schema | columns
...    

This should help you show tables from all schemas.

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:

presto> select col1 from table_a;
     col1      
------------
 1606485526 
 1606485575 
 
presto> select cast(col1 as timestamp) from table_a;
Query 20201127_150824_00052_xnxra failed: line 1:8: Cannot cast bigint to timestamp

There is a solution!  Presto’s from_unixtime() function takes a UNIX timestamp and returns a timestamp:

presto> select col1,from_unixtime(col1) as ts from table_a;
    col1    |          ts          
------------+-------------------------
 1606485526 | 2020-11-27 13:58:46.000 
 1606485575 | 2020-11-27 13:59:35.000 

And we can optionally modify the format of the result by using date_format():

presto> select date_format(from_unixtime(col1),'%Y-%m-%d %h:%i%p') from table_a;
        _col0        
---------------------
 2020-11-27 01:58PM 
 2020-11-27 01:59PM 

That’s how to use from_unixtime() to convert bigint to timestamp. 

Want more Presto tips & tricks? Sign up for our Presto community newsletter.

Case Sensitive Search Configuration 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 are case insensitive by default, some are not. How do we configure things so they behave in the way we want?

Here’s an example of why we might need to take steps for case sensitive search configuration. We’re accessing a MySQL database directly:

mysql> select * from state where name='iowa';
+------+----+--------------+
| name | id | abbreviation |
+------+----+--------------+
| Iowa | 19 | IA           |
+------+----+--------------+
1 row in set (0.00 sec)

MySQL is case-insensitive by default. Even though the MySQL column contains the capitalized string ‘Iowa’ it still matched the query’s restriction of ‘iowa’.  This may be acceptable, but in some use cases it could lead to unexpected results.

Using Presto to access the same MySQL data source things behave differently, and arguably, in a more expected way:

presto:demo> select * from state where name='iowa';
 name | id | abbreviation 
------+----+--------------
(0 rows)
 
Query 20201120_151345_00001_wjx6r, FINISHED, 1 node
Splits: 17 total, 17 done (100.00%)
0:07 [1 rows, 0B] [0 rows/s, 0B/s]
 
presto:demo> select * from state where name='Iowa';
 name | id | abbreviation 
------+----+--------------
 Iowa | 19 | IA           
(1 row)

Now we only get a match with ‘Iowa’, and not with ‘iowa’. Presto has essentially made this data source (MySQL) case sensitive, even though it is exactly the same database in both the above examples, with default configurations used.

Reconfigure Case Sensitivity 

With a RDBMS like MySQL you can configure the collation setting to control if you want case sensitivity or not.  You can set the collation at the database creation or table creation level as a part of the CREATE statement. Or you can use ALTER to change the collation of a database, table or individual column. This is described in MySQL’s documentation. 

But how do you change Presto to be case-insensitive? Presto does not support collation, because it is not a database and doesn’t store data. And there is no configuration parameter that controls this.  To manage case sensitivity in Presto, and mimic collation, we rewrite the query to force case insensitivity explicitly by using:

• simple lower() or upper() functions

Example:

select * from state where lower(name)='california';
    name      
------------
 california 
 California 
 CALIFORNIA 
(3 rows)

This query has matched any upper/lower case combination in the table, mimicking case insensitivity.

Or regular expressions. 

Example:

select * from state where regexp_like(name, '(?i)california');
    name      
------------
 california 
 California 
 CALIFORNIA 
(3 rows)

The regular expression syntax (?i) means matches are case insensitive. 

When it comes to Case Sensitive Search Configuration you are now an eXpErT.

When should I use ORC versus Parquet when using Presto?

If you’re working with open data lakes using open source and open formats, you can have multiple formats. Presto works with both – ORC Presto and Parquet Presto. You’ll probably want to optimize for your workloads. 

Both ORC and Parquet store data in columns. For Presto Parquet, it is most efficient when it comes to storage and performance. ORC on the other hand is ideal for storing compact data and skipping over irrelevant data without complex or manually maintained indices. For example, ORC is typically better suited for dimension tables which are slightly smaller while Parquet works better for the fact tables, which are much bigger.

If you’re looking to get up and running quickly with Presto, you can check out Ahana Cloud. It’s a SaaS for Presto and takes care of all the configuration, tuning, deployment, etc.

What’s the advantage of having your own Hive metastore with Presto? How does it compare to Amazon Glue?

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 managed ETL service for preparing and loading data for analytics. It automates ETL and handles the schemas and transformations. AWS Glue is serverless, so there’s no infrastructure needed to provision or manage it (you only pay for the resources used while your jobs are running).

Presto isn’t a database and does not come with a catalog, so you’d want to use Hive to read/write/manage your datasets. Presto abstracts a catalog like Hive underneath it. You can use the Glue catalog as the default Hive metastore for Presto.

With Ahana Cloud, you don’t really need to worry about integrating Hive and/or AWS Glue with Presto. Presto clusters created with Ahana come with a managed Hive metastore and pre-integrated Amazon S3 data lake bucket. Ahana takes care connecting external catalogs like Hive and Amazon Glue, so you can focus more on analytics and less on integrating your catalogs manually. You can also create managed tables as opposed to external tables.

How to Find Out Data Type of Value with Presto

Presto has a typeof() function to make finding out data types of values easy. This is particularly useful when you are getting values from nested maps for example and the data types need to be determined.

Here’s a simple example showing the type returned by Presto’s now() function:

presto:default> select now() as System_Timestamp, typeof( now() ) as "the datatype is";
           System_Timestamp            |     the datatype is      
---------------------------------------+--------------------------
 2020-11-18 15:15:09.872 Europe/London | timestamp with time zone 

Some more examples:

presto:default> select typeof( 'abc' ) as "the datatype is";
 the datatype is 
-----------------
 varchar(3)      
 
presto:default> select typeof( 42 ) as "the datatype is";
 the datatype is 
-----------------
 integer         
 
presto:default> select typeof( 9999999999 ) as "the datatype is";
 the datatype is 
-----------------
 bigint          
 
presto:default> select typeof( 3.14159 ) as "the datatype is";
 the datatype is 
-----------------
 decimal(6,5)

Armed with this info you should now be able to find out the data types of values.

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.  

Suppose we have rows of data like this:

'a', 9
'b', 8
'a', 7 

We want to pivot this data so that all the ‘a’ row values are arranged in one column, and all the ‘b’ row values are in a second column like this:

To rotate from rows to columns we will add an id to make aggregation easy. We will name the output columns a and b for the Presto key, and we’ll include the id in our result set. This is how we do the rotation in Presto, using VALUES() to supply the test data, and simple conditional CASE WHEN END logic:

presto:default> SELECT id
, MAX(CASE WHEN key = 'a' THEN value END) AS a
, MAX(CASE WHEN key = 'b' THEN value END) AS b 
FROM (VALUES (1, 'a', 9), (2, 'b', 8), (3, 'a', 7 )) as test_data (id, key, value) 
GROUP BY id ORDER BY id;
 
 id |  a   |  b   
----+------+------
  1 |    9 | NULL 
  2 | NULL |    8 
  3 |    7 | NULL 
(3 rows)

There are other SQL options for transforming (pivoting) rows into columns – you can use the map_agg function for example.

The code sample and description here should help when you need to rotate data from rows to columns using Presto.

Want more Presto tips & tricks? Sign up for our Presto community newsletter.

How to Lateral View Explode in Presto

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 hive and can be used in a SELECT expression list and as a part of LATERAL VIEW.

The explode function doesn’t exist in Presto; instead we can use Presto’s similar UNNEST. 

A Practical Example: 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 table expression (CTE) named example with a column alias name of data. The VALUES function returns a table rowset. 

WITH example(data) as 
(
    VALUES
    (json '{"result":[{"name":"Jarret","score":"90"},{"name":"Blanche","score":"95"}]}'),
    (json '{"result":[{"name":"Blanche","score":"76"},{"name":"Jarret","score":"88"}]}')
)
SELECT n.name as "Student Name", avg(n.score) as "Average Score"
FROM example
CROSS JOIN
    UNNEST ( 
        CAST (JSON_EXTRACT(data, '$.result')
        as ARRAY(ROW(name VARCHAR, score INTEGER )))
    ) as n
--WHERE n.name='Jarret'
GROUP BY n.name;

Student Name | Average Score 
-------------+---------------
 Jarret      |          89.0 
 Blanche     |          85.5 
(2 rows)

The UNNEST function takes an array within a column of a single row and returns the elements of the array as multiple rows.

CAST converts the JSON type to an ARRAY type which UNNEST requires.

JSON_EXTRACT uses a jsonPath expression to return the array value of the result key in the data.

This code sample and description should help when you need to to do a lateral view explode in Presto.

Want more Presto tips & tricks? Sign up for our Presto community newsletter.

How to check if Presto array or map contains values

In Presto, it is important to be able to determine if complex types like array (indexable lists) or map (key-value tuple) are empty. The following examples will demonstrate various methods to verify if a map or array is empty in Presto.

Check if a Presto map is empty

First, let’s create a prestomap from an array of key values:

presto> select map(array['taco','donut'], array[1,2]) as myMap;
       myMap       
-------------------
 {donut=2, taco=1} 

We can use the CARDINALITY() function to check if the map contains anything:

presto> select cardinality(map(array['taco','donut'], array[1,2])) = 0 as is_empty;
 is_empty 
----------
 false    

presto> select cardinality(map(array[], array[])) = 0 as is_empty;
 is_empty 
----------
 true     

We can also compare a map with an empty map() to test if it is empty:

presto> select (map(array['taco'], array['donut']) = map()) as is_empty;
 is_empty 
----------
 false 

In the code above, the first query creates a presto map by pairing keys and their corresponding values from two arrays.

The second query uses the “cardinality” function to check if the map created in the first query has any key-value pairs. Since the map is not empty, the result of the query is “false”.

The third query creates an empty map using two empty arrays as arguments to the “map” function. Then, the “cardinality” function is used to check if this empty map contains any key-value pairs. Since there are no key-value pairs in the empty map, the result is “true”.

The final query compares a non-empty map (created with one key-value pair) to an empty map using the “=” operator. Since the two maps are not equal, the result is “false”.

Check if a Presto array contains values

Again, we can use the CARDINALITY() function to check if an array contains anything. Here’s an example using an array with 3 elements:

presto> SELECT cardinality(ARRAY['Ahana', 'Cloud', 'Presto']) = 0 as is_empty;
is_empty 
--------
 false  


presto> SELECT cardinality(ARRAY[]) = 0 as is_empty;
is_empty 
--------
 true  

 Another method to check if an array is empty is to compare it with array[] like this:

presto> SELECT (map_keys(map(ARRAY['Ahana'],ARRAY['Presto']))= array[]) as is_empty;
 is_empty 
----------
 false 

Tip: Be aware of null values. If an array contains nulls it is not considered to be empty:

presto> SELECT cardinality(ARRAY['', '', '']) = 0 as is_empty;
is_empty 
--------
 false  

presto> SELECT cardinality(ARRAY[null,null,null]) = 0 as is_empty;
is_empty 
--------
 false  

In the code above, the first query creates a presto map by pairing keys and their corresponding values from two arrays.

The second query uses the “cardinality” function to check if the map created in the first query has any key-value pairs. Since the map is not empty, the result of the query is “false”.

The third query creates an empty map using two empty arrays as arguments to the “map” function. Then, the “cardinality” function is used to check if this empty map contains any key-value pairs. Since there are no key-value pairs in the empty map, the result is “true”.

The final query compares a non-empty map (created with one key-value pair) to an empty map using the “=” operator. Since the two maps are not equal, the result is “false”.

The above query examples should enable you to check if a map or array is empty.

If you want to get up and running with Presto, Ahana Cloud’s cloud managed service built for AWS is the easiest way to do that. See our docs for more details.

Export Result Of Select Statement in Presto

A common question is “how can I run a query and export result of select statement quickly and easily in Presto?”  You are in luck, as several solutions exist – your choice will depend on your preferred tool, the output format you want, and the size of the result-set. Here are two options.

Using Presto-CLI

If you are using the Presto command line tool presto-cli (or just presto on the Mac if you used brew install presto) then use the --output-format parameter and redirect the output to a regular file. For example:

$ presto --server https://myPrestoCluster.com \
--catalog glue --schema amazon \
--execute "select product_title, count(*) as Num_Reviews from reviews where lower(product_title) like '%tacos%' group by 1 order by 2 desc limit 20" \
--output-format CSV > top20_taco_reviews.csv

$ head top20_taco_reviews.csv
"Dragons Love Tacos","832"
"Vitacost Extra Virgin Certified Organic Coconut Oil 16 Oz","281"
"Vitacost 100% Pure Peppermint Oil -- 4 fl oz","178"
"Vitacost 100% Pure Lavender Oil -- 4 fl oz","168"
"Taco Tender Holder - Plastic Red Stand - Holds 3 Tacos","106"
"Vitacost Infant Health - Baby-D's Liquid Vitamin D Drops -- 400 IU - 1 fl oz","101"
"Vitacost Butterbur Extract - Standardized -- 75 mg - 120 Capsules","84"
"Tacos, Tortas, and Tamales: Flavors from the Griddles, Pots, and Streetside Kitchens of Mexico","63"
"Vitacost Pine Bark Extract - Standardized to 95% OPC -- 100 mg - 300 Capsules","51"
"Vegan Tacos: Authentic and Inspired Recipes for Mexico's Favorite Street Food","45"

There are several formats supported by Presto-CLI, the default being quoted CSV:

--output-format <output-format>
            Output format for batch mode [ALIGNED, VERTICAL, CSV, TSV,
            CSV_HEADER, TSV_HEADER, NULL] (default: CSV)

So if you want to see column headers in your CSV format output file use --output-format CSV_HEADER

The advantage of using this approach is speed.

Using a Database Tool

If you are using a third-party SQL database tool like DbVisualizer, DBeaver or SQuirreL SQL then the UI will allow you to save the query output. For example, using DbVisualizer, run your query and click the Export button.

The advantage of this method is the huge number of output formatting options on offer. The disadvantage is it is usually slower than using Presto-CLI. 

The two options above should help you export results of a Select statement.

Extract Keys in a Nested JSON Array Object With Presto

Let’s say we have some JSON that looks like this:

[{"id": 1, "value":"xxx"}, {"id": 2, "value":"yyy"}]

In this instance our json contains key value pairs. How can we decode that using PrestoDB SQL to extract only the keys (1,2 in this example)?  

We can extract keys in a nested JSON array object using SQL arrays, the JSON_PARSE function and the TRANSFORM function.  The TRANSFORM function’s syntax is:

For this example query we’ll also use the VALUES function to provide the test data – VALUES is a handy way to define an inline  table – kind of like a dummy table.  

select 
TRANSFORM(CAST(JSON_PARSE(array) AS ARRAY<MAP<VARCHAR, VARCHAR>>), entry -> entry['id']) as My_Keys 
from (values ('[{"id": 1, "value":"xxx"}, {"id":2, "value":"yy"}]')) t(array);

 My_Keys 
--------
 [1, 2]  

This works by first creating an array containing the input JSON data called ‘array’.  We then use JSON_PARSE which converts a string or array containing JSON text into deserialized JSON values, and we cast those values as an array with two varchar elements.   TRANSFORM then returns an array containing only the elements we want  (we call our desired elements ‘entry’ in this example but it could be called anything).

So with Presto’s rich set of array and JSON functions we can see how to easily extract keys in a nested JSON array object. If you want to get up and running with Presto, Ahana Cloud’s cloud managed service built for AWS is the easiest way to do that. See our docs for more details.

How to Presto Escape a Single Quote

In reality there will be some occasions when you need to use a quote character in your data, in your query, or in result sets, but you want the SQL interpreter to treat the quote as “just another character”, as opposed a quote which has special meaning – namely to denote a string literal in most SQL dialects. This is called escaping a character.

Some SQL dialects use a backslash as the escape character. Presto’s SQL needs no escape character; instead it uses a double character notation. Here’s a simple example of what I mean:

presto:default> select 'hello world';
 _col0 
-------
 hello world

What if I want single quotes to actually appear in the result?  I need to do this in Presto:

presto:default> select '''hello world''' as col1;
  col1   
---------
 'hello world' 

I have simply used two single quotes to tell the interpreter the places where “I really want to include a single quote”. 

Another example: What if there are single quotes in my table? 

presto:default> select * from mytable where column_a in ('Driver's License');

Query n failed: line 1:43: mismatched input 'in'. Expecting: 'AND', 'EXCEPT', 'GROUP', 'HAVING', 'INTERSECT', 'LIMIT', 'OR', 'ORDER', 'UNION', <EOF>

The query was rejected because the interpreter encountered an unexpected single quote in “Driver’s License”.  To handle this we use two single quotes:

presto:default> select * from mytable where column_a in ('Driver''s License');

(query runs ok)

So that’s how to escape a single quote in Presto.

Static Date and Timestamp in Where Clause

Static Date & Timestamp

In this post we’ll look at the static date and timestamp in where clause when it comes to Presto. Many databases automatically convert between CHAR or VARCHAR and other types like DATE and TIMESTAMP as a convenience feature.  Using constants in a query are also often auto-converted.  Take these example queries which count rows where the table’s DATE column is equal to a specific date, or range of time:

Select count(*) from myTable where myDateCol = '2020-09-28';

Select count(*) from myTable where myTimeCol between '2020-09-28 14:00:00.000' and '2020-09-28 14:59:59.000';

The underlying table has columns defined as DATE and TIMESTAMP types.  But the query is actually providing date and timestamp literals in the form of strings!  Some databases will tolerate this, but Presto is stricter in this respect and will give you an error like this:

presto:sf1 > select count(*) from orders where orderdate < '2020-09-01';
Query failed: line 1:45: '<' cannot be applied to date, varchar(10)

With Presto you must either cast your data types or a slightly simpler way is to use the date or timestamp type constructors:

$ presto --schema sf1 --catalog tpch

presto:sf1> select count(*) from orders where orderdate < date '2020-09-01';
  _col0  
---------
 1500000 
(1 row)

> select count(*) from transactions where myTimestampCol between timestamp '2020-09-01 22:00:00.000' and timestamp '2020-09-01 22:59:59.000';
  _col0  
---------
   42 
(1 row)

--Using CAST() also works:
presto:sf1> select count(*) from orders where orderdate < CAST('2020-09-01' as DATE);
  _col0  
---------
 1500000 
(1 row)

In summary, by using CAST() or the DATE/TIMESTAMP type constructors you will be able to specify a static date and timestamp in where clause.

Related Articles

What is an Open Data Lake in the Cloud?

The Open Data Lake in the cloud is the solution to the massive data problem. Many companies are adopting that architecture because of better price-performance, scale, and non-proprietary architecture.

Data Warehouse Concepts for Beginners

A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. Check out this article for more information about data warehouses.

Where can I find the Presto Server Bootstrap logs?

A common question is “where can I find the Presto server bootstrap logs?”  These are logs that indicate what is happening to the cluster on startup. The Presto server generates them, and the content looks like this:

2020-05-22T11:59:32.045-0400 INFO main io.airlift.log.Logging Logging to stderr
2020-05-22T11:59:32.049-0400 INFO main Bootstrap Loading configuration
2020-05-22T11:59:32.224-0400 INFO main Bootstrap Initializing logging
2020-05-22T11:59:33.268-0400 INFO main Bootstrap PROPERTY DEFAULT RUNTIME DESCRIPTION
2020-05-22T11:59:33.268-0400 INFO main Bootstrap discovery.uri null http://localhost:8080 Discovery service base URI
2020-05-22T11:59:33.268-0400 INFO main Bootstrap service-inventory.uri null null Service inventory base URI
2020-05-22T11:59:33.268-0400 INFO main Bootstrap service-inventory.update-interval 10.00s 10.00s Service inventory update interval
2020-05-22T11:59:33.268-0400 INFO main Bootstrap discovery.presto.pool general general
2020-05-22T11:59:33.268-0400 INFO main Bootstrap discovery.collector.pool general general
2020-05-22T11:59:33.270-0400 INFO main Bootstrap discovery.max-age 30.00s 30.00s
2020-05-22T11:59:33.270-0400 INFO main Bootstrap discovery.store-cache-ttl 1.00s 1.00s
2020-05-22T11:59:33.270-0400 INFO main Bootstrap dynamic.store.gc-interval 1.00h 1.00h
2020-05-22T11:59:33.271-0400 INFO main Bootstrap dynamic.store.remote.max-batch-size 1000 1000
...

In the above excerpt you can see Bootstrap logs specific lines.

There are 3 logs of interest :

The log files’ location depends on where you are running your PrestoDB cluster and how you have it configured:

• If you are running on EC2 look here: /media/ephemeral0/presto/var/log/bootstrap.log 
• Other places to check (not just for the server log) are: /var/log/ and /var/lib/presto/data/var/log/ and /var/presto/data/

Additional Bootstrap logs Support

If all else fails when looking for bootstrap logs, look in your config.properties file (look in /opt/presto-server/etc/) to see if there is anything deployment-specific configured for logging. 

Configuration tip: Presto needs a data directory for storing logs, etc. and it is recommended this is created in a data directory outside of the installation directory (which allows it to be easily preserved when upgrading Presto). 

• Presto’s node properties file etc/node.properties sets the location of the data directory. 
• In your node.properties file set the node.data-dir property to point to the location (filesystem path) of the intended data directory – Presto will store logs and other data here. 

Finally, if you are wondering where to start when diagnosing bootstrap issues, it is worth looking at the output in the bootstrap section of the server.log file first. 

Armed with the above information you should now be able to find the Presto Server Bootstrap logs.