Presto is a high performance, distributed SQL query engine for big data. It was developed by Facebook, but now is open-source.
The main purpose of deploying Presto on Kubernetes is to access huge datasets in an efficient manner, using only SQL.
This document is focused on datasets stored in AWS S3, but the Presto cluster can read data from many distributed storage systems, such as HDFS, Azure Blob Storage, Azure Data Lake Storage, Google Cloud Storage and S3-compatible storage.
The steps to follow are:
The architecture of the Presto cluster looks like this:
Presto cluster architecture
Presto uses its Hive Connector to access datasets stored in S3. This connector relays on Hive Metastore to map S3 data files to schemas and tables. The Hive Metastore saves the results of this mapping in an external RDBMS like PostgreSQL.
To ensure the persistence of this metadata the cluster uses a Persistent Volume, this way, all the services of the cluster: Presto, Hive Metastore and the PostgreSQL database, can go down temporarily and we will not lose any of the table definitions that Presto rely upon.
This Presto cluster is designed to run on Kubernetes, locally on Docker for Desktop or Minikube, or on the cloud with:
Yes, you can run Kubernetes in Docker Desktop, as it includes a standalone Kubernetes server (single-node cluster) and a client. To enable it, launch Docker Desktop, go to Settings > Kubernetes and choose "Enable Kubernetes".
✸ If Kubernetes fails to start after being enabled, check this issue for help.
Before deploying Presto in Kubernetes, check in the Resources tab that the CPUs are set to at least 4, and the Memory to at least 8.0 GB.
Docker Desktop Resources tab
With Docker Desktop you can also run a local registry to upload your images, as you can do with Amazon EKS and Azure Kubernetes Service. To run it execute the following command:
$ docker run -d -p 5000:5000 --name registry registry:2 |
In fact, the URI of this local registry is the default value of the DOCKER_IMAGE_PREFIX variable in the cluster.sh script of the distribution:
DOCKER_IMAGE_PREFIX=localhost:5000/ |
Amazon EKS (Elastic Container Service for Kubernetes) is a managed Kubernetes service that allows you to run Kubernetes on AWS.
EKS architecture
These are the steps for setting up an EKS cluster:
When you are finished, it is important to clean up the Kubernetes resources, ECR repositories and EKS clusters, so that you do not incur additional charges for ongoing service.
For detailed information see https://docs.aws.amazon.com/eks/latest/userguide/what-is-eks.html.
Azure Kubernetes Service (AKS) is a managed Kubernetes service that allows you to run Kubernetes on Microsoft Azure.
AKS cluster deployment
The steps for setting up an AKS cluster are the following:
When you are done, it is important to clean up your Kubernetes resources, ACR repositories and AKS clusters, so that you do not incur additional charges for ongoing service.
For more information see https://docs.microsoft.com/en-us/azure/aks/tutorial-kubernetes-prepare-app
Before building and deploying the Presto cluster you may need to modify some of the following parameters.
This Java Keystore contains a self-signed certificate (certs/certificate.crt) that is distributed ONLY for testing purposes. You are encouraged to use a certificate issued by a CA in production and import it, with its private key, in the Java Keystore or replace the existing one (presto/conf/presto.jks).
✸ If you use the cluster.sh script to deploy the cluster, you only have to set the DOCKER_IMAGE_PREFIX variable to the proper container registry URI and the cluster.sh script will build the image name for you.
✸ The distributed schema.sql contains some CREATE TABLE sentences commented out, as an example, so no tables will be created if you don't modify this file. Alternatively, you can use your favourite SQL client to create any table you want when the cluster is running. Or, make use of the metadata discovery tool, explained later in this document.
Let's see now how to deploy Presto on Kubernetes!
The script cluster.sh , in the root folder of the distribution, automatizes the whole process explained in the Long Version section.
Usage: cluster.sh {deploy [s3 | wasb | adl] | delete}
✸ The script has two prerequisites: you have to be logged in the container registry (ECR, ACR, GCR, …) and you have to configure the connection to the Kubernetes cluster in the kubeconfig file.
cluster.sh deploy s3: deploys a cluster that accesses datasets in S3.
cluster.sh deploy wasb: deploys a cluster that accesses datasets in Azure Blob Storage.
cluster.sh deploy adl: deploys a cluster that access datasets in Azure Data Lake Gen 1.
cluster.sh deploy abfs: deploys a cluster that accesses datasets in Azure Data Lake Gen 2.
cluster.sh delete: removes the cluster and all the configuration created for it.
$ cd denodo-presto-k8scluster-<VERSION> $ docker build -t hive-metastore hive-metastore/ $ docker build -t presto presto/ |
The Hive Metastore Docker image:
The Presto Docker image:
$ docker login ... $ docker tag hive-metastore repository/hive-metastore $ docker push repository/hive-metastore $ docker tag presto repository/presto $ docker push repository/presto |
$ kubectl create secret generic credentials --from-literal=AWS_ACCESS_KEY_ID=YOUR_AWS_KEY --from-literal=AWS_SECRET_ACCESS_KEY=YOUR_AWS_SECRET |
For examples with Azure credentials take a look to the cluster.sh script.
$ cd denodo-presto-k8scluster-<VERSION> $ kubectl create configmap hive-metastore-conf --from-file=hive-metastore/conf/ $ kubectl create configmap presto-conf --from-file=presto/conf/ |
$ cd denodo-presto-k8scluster-<VERSION> $ kubectl apply -f hive-metastore-pvc.yaml |
$ cd denodo-presto-k8scluster-<VERSION> $ kubectl apply -f hive-metastore.yaml |
$ cd denodo-presto-k8scluster-<VERSION> $ kubectl apply -f presto.yaml |
Kubernetes pods status
In the example above we can see that the Hive Metastore is not in Running status, but in Pending. To find out why, use the kubectl describe pod <podname> on the Pending pod and look at the Events section:
Kubernetes pod description
In the Events section a Message says that we forgot to create the persistent volume described in the step 4.
After executing the missing step, we can verify that all is working OK now with kubectl get pods:
Kubernetes pods status
Once deployed, Presto is accessible at External-IP:8443 of the presto service. Run kubectl get svc to find out the external IP.
Kubernetes services status
Presto is accessible at:
Presto Web UI https://51.105.112.6:8443/ui/ JDBC URI jdbc:presto://51.105.112.6:8443/hive/default?SSL=true User presto Password pr3st% |
Do not forget to delete all the resources you have created, when you do not need the cluster anymore.
$ kubectl delete -f presto.yaml |
$ kubectl delete -f hive-metastore.yaml |
$ kubectl delete -f hive-metastore-pvc.yaml |
When talking about Presto, fewer bigger machines is better than more smaller machines. It is recommended that you start with big balanced machines, like these in AWS:
Presto requires a certain amount of CPU to process the data for a given query. And generally, if you double the CPU in the cluster, maintaining the same memory, the query will take half of the time.
So, as a generalization, more CPUs mean shorter queries.
Memory is about the data structures required to run the query, and is needed for JOIN, GROUP BY, and ORDER BY operations.
As a generalization, allocate 80% of the memory machine to the Java heap, -Xmx option in file presto/conf/jvm.config.
And the 70% of the Java heap for the maximum memory that a query can take up on a node, query.max-memory-per-node property in files presto/conf/config.properties.coordinator and presto/conf/config.properties.worker.
If you are suffering from network or IO bandwidth, it is recommended that you compress your files:
Also, avoid small files, any file less than 8 MB is considered small. Hundred of MB is the recommended size.
Do not use tiny row groups, default is 128 MB, but some tools generate Parquet files with typical size of 4k, causing bad IO patterns in Presto.
To access datasets from Presto, you have to map the AWS S3 data files (or Azure Blob Storage, or Azure Data Lake, etc..) to tables in the Hive Metastore. The mapping is done through the CREATE TABLE sentence, where you have to provide the schema of the data, the file format, and the data location.
CREATE TABLE orders ( orderkey bigint, custkey bigint, orderstatus varchar, totalprice double, orderpriority varchar, clerk varchar, shippriority integer, comment varchar ) WITH ( external_location = 's3a://my_bucket/path/to/folder/', format = 'ORC' ); |
✸ Don't forget to use a trailing slash, otherwise Presto will throw the following error: Can not create a Path from an empty string.
The WITH clause of the CREATE TABLE can also be used to set other properties on the table. The available properties are described in the Appendix: Hive Table Properties.
The file presto/conf/schema.sql contains the CREATE TABLE sentences that will be executed immediately after starting the Presto cluster. Alternatively, you can use your favourite SQL client to create any table you want when the cluster is running. Or execute the metadata discovery tool explained in the next section.
The metadata discovery tool explores the distributed storage, starting from a specific path given by the user, and from every Paquet dataset it finds, it creates the corresponding table in Presto. Then, it calls the Denodo stored procedures that introspect Presto and create the Denodo base views for all the tables found in Presto.
The metadata discovery script is distributed in the folder metadata-discovery/bin. A configuration file is required as input:
$ denodo-presto-metadata-discovery.sh ../conf/configuration.properties |
dirToExplore=s3a://example-bucket/path/to/object credentials.xml.path=../conf/core-site.xml #includedPattern=(.)*blahblah(.)* # Denodo connection properties denodo.jdbcUrl=jdbc:vdb://localhost:9999/presto_tests denodo.username=admin denodo.password=admin # Presto connection properties # Requisites for this code to be able to connect to Presto: # - import certificate.crt in the TrustStore of the JVM (the one # executing this code and the Denodo's JVM) # OR # - use the JDBC URI parameters (SSLTrustStorePath and # SSLTrustStorePassword) to provide the path # to a TrustStore with the certificate.crt presto.jdbcUrl=jdbc:presto://localhost:8443/hive/default?SSL=true\ &SSLTrustStorePath=truststore-path&SSLTrustStorePassword=truststore-pwd presto.username=presto presto.password=pr3st% # Default values denodo.datasource.name=ds_presto_k8s denodo.datasource.folder=/01 - Connectivity/01 - Data Sources denodo.baseview.folder=/01 - Connectivity/02 - Base Views denodo.baseview.prefix=bv_ presto.replace.tables=true |
configuration.properties
Requisites in Denodo:
The metadata-discovery/lib/presto-jdbc-<version>.jar has to be imported in the Denodo server with the name presto-<version>.
Import Presto driver with name: presto-343
Requisites to be able to connect to Presto |
OR
The certs/certificate.crt is distributed ONLY for testing purposes. This certificate accepts localhost and presto-denodo as Presto host names:
In this case, you have to add an entry in the hosts file where Denodo server is running, with presto-denodo and the IP that appears as the EXTERNAL-IP of the presto service. Kubernetes services status /etc/hosts file But you are encouraged to use a certificate issued by a CA in production. And this certificate will be the one that you have to import in the TrustStore of the client code and in the Java Keystore, with its private key, of the Presto cluster (presto/conf/presto.jks) before deploying it. |
The output of the process would be:
METADATA DISCOVERY RESULTS ****************************************** Commands executed at Presto: ------------------------------------------ DROP TABLE IF EXISTS "customer" CREATE TABLE IF NOT EXISTS "customer" ( DROP TABLE IF EXISTS "store" CREATE TABLE IF NOT EXISTS "store" ( DROP TABLE IF EXISTS "store_sales" CREATE TABLE IF NOT EXISTS "store_sales" ( DROP TABLE IF EXISTS "date_dim" CREATE TABLE IF NOT EXISTS "date_dim" ( DROP TABLE IF EXISTS "customer_mix_schemas" CREATE TABLE IF NOT EXISTS "customer_mix_schemas" ( DROP TABLE IF EXISTS "customer_wo_birth_mail" CREATE TABLE IF NOT EXISTS "customer_wo_birth_mail" ( ------------------------------------------ Commands executed at Denodo (data source): -------------------------------------------------------------- CREATE OR REPLACE FOLDER '/01 - Connectivity'; CREATE OR REPLACE FOLDER '/01 - Connectivity/01 - Data Sources'; CREATE OR REPLACE DATASOURCE JDBC "ds_presto_k8s" ------------------------------------------ Commands executed at Denodo (base views): ------------------------------------------------------------- CREATE OR REPLACE FOLDER '/01 - Connectivity/02 - Base Views'; CREATE OR REPLACE WRAPPER JDBC bv_customer CREATE OR REPLACE TABLE bv_customer I18N us_pst ( CREATE OR REPLACE WRAPPER JDBC bv_customer_mix_schemas CREATE OR REPLACE TABLE bv_customer_mix_schemas I18N us_pst ( CREATE OR REPLACE WRAPPER JDBC bv_customer_wo_birth_mail CREATE OR REPLACE TABLE bv_customer_wo_birth_mail I18N us_pst ( CREATE OR REPLACE WRAPPER JDBC bv_date_dim CREATE OR REPLACE TABLE bv_date_dim I18N us_pst ( CREATE OR REPLACE WRAPPER JDBC bv_store CREATE OR REPLACE TABLE bv_store I18N us_pst ( CREATE OR REPLACE WRAPPER JDBC bv_store_sales CREATE OR REPLACE TABLE bv_store_sales I18N us_pst ( |
Output of the metadata discovery process
"Error generating Denodo DDL" is most probably caused because you forgot to import the JDBC driver of PrestoSQL in the Denodo server.
METADATA DISCOVERY EXCEPTION ********************************************* Error: Error generating Denodo DDL |
Exception in the metadata discovery process
The driver presto-jdbc-<version>.jar can be found in the folder metadata-discovery/lib and it has to be imported with the name presto-<version> in the Denodo server.
Import Presto driver with name: presto-343
If your queries are complex and include joins of large data sets, running ANALYZE on tables/partitions may improve query performance. The metadata discovery tool runs ANALYZE automatically, after each table creation.
This command collects statistical information about the data that the Presto optimizer uses to plan the query based on cost strategies.
Once again, you can use your favourite SQL client to analyze the tables you want when the cluster is running.
ANALYZE store_sales10; |
The image above shows an example of the statistics collected by Presto after running ANALYZE table.
SHOW stats for table:
Table statistics
Tables can have partitions, as they can improve query performance, especially when the columns being queried for are the partitioned ones. This means that, for each column value of the partitioned column, there will be a separate folder under the location of the dataset. The Hive naming convention for partition folders is columnName=value, e.g. month=12.
.../page_views/year=2019/month=10/…
.../page_views/year=2019/month=11/…
.../page_views/year=2019/month=12/…
CREATE TABLE IF NOT EXISTS weblogs ( request_timestamp varchar, request_ip varchar, request_port integer, backend_ip varchar, backend_port integer, request_processing_time double, backend_processing_time double, client_response_time double, received_bytes bigint, sent_bytes bigint, request_verb varchar, url varchar, protocol varchar, elb_name varchar, elb_response_code varchar) WITH ( format = 'parquet', partitioned_by = ARRAY['elb_name', 'elb_response_code'], external_location = s3://my_bucket/path/to/folder/' ); |
✸ Note that Hive requires the partition columns to be the last columns in the CREATE TABLE sentence.
However, if we query a partition table after creating it, we will find that it returns zero results. The Hive Metastore needs to discover which partitions exist by querying the underlying storage system. The Presto procedure system.sync_partition_metadata(schema_name, table_name, mode) is in charge of detecting the existence of partitions.
When data is inserted in new partitions we need to invoke the sync_partition_metadata procedure again, to discover the new records.
call system.sync_partition_metadata('default', 'weblogs', 'FULL'); |
The metadata discovery tool invokes sync_partition_metadata automatically, after each partition table creation.
To establish a connection to Presto you have to create a JDBC data source in Denodo and fill all the required fields:
✸ Driver class path:
The metadata-discovery/lib/presto-jdbc-<version>.jar has to be imported in the Denodo server with the name presto-<version>.
Also, if you do not specify an alternative Java TrustStore in the Database URI, with the parameters SSLTrustStorePath and SSLTrustStorePassword, (see all the URI parameters accepted), you have to import the server certificate (certs/certificate.crt) in the Denodo server trust store.
The certs/certificate.crt is distributed ONLY for testing purposes. This certificate accepts localhost and presto-denodo as Presto host names:
In this case, you have to add an entry in the hosts file where Denodo server is running, with presto-denodo and the IP that appears as the EXTERNAL-IP of the presto service.
Kubernetes services status
/etc/hosts file
But you are encouraged to use a certificate issued by a CA in production. And this certificate will be the one that you have to import in the TrustStore of the Denodo server and in the Java Keystore, with its private key, of the Presto cluster (presto/conf/presto.jks) before deploying it.
Presto datasource in Denodo
Once the data source is configured, click on "Create base View" to explore Presto schemas with their tables and their columns.
Presto tables in Denodo
Finally, you have reached your goal! You can run any query you want on your S3 datasets.
Query results in Denodo
This Presto data source can be also configured to perform bulk data loads.
First, you have to follow the steps listed in the Configure the Hadoop client in the Denodo Server section in Configuring Databricks as MPP and Cache.
Before the feature #41043 was implemented in the Update 7.0u08, it was required to create a new schema in Presto that sets the location where the parquet files will be located. For this you can use your favourite SQL client:
CREATE SCHEMA hive.<schema_name> WITH (location = 'adl://<adl_host>/<folders>'); |
After Update 7.0u08 and in Denodo 8 you don't have to create the schema.
Then, in the Bulk Data Load API of the Presto data source in Denodo, fill the configuration parameters: the Hadoop client path, the storage path (adl://<adl_host>/presto in the example), the server timezone and the schema:
Finally, press the "Test bulk load" to check the configuration is working:
The optional WITH clause of the CREATE TABLE can be used to set properties on the newly created table or on single columns.
Available table properties are: