• User Manuals »
  • Denodo Presto Cluster on Kubernetes - User Manual

Denodo Presto Cluster on Kubernetes - User Manual

Download original document


Overview

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, using SQL, in an efficient manner.

In this document we are focused on datasets stored in AWS S3, but we 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:

  1. Store your dataset in S3.

  1. Build and deploy Presto on Kubernetes.

  1. Expose S3 data as Hive tables in Presto.

  1. Configure a Presto data source in Denodo Platform.

  1. Run SQL queries in Denodo against Presto.

Presto on Kubernetes

Architecture

The architecture of the cluster looks like this:

                                        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 RDMS, PostgreSQL in our case.

To ensure the persistence of this metadata we use a Persistent Volume in Kubernetes, so that 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.

Build and Deploy Presto

This Presto cluster is designed to run on Kubernetes, locally on Docker for Desktop or Minikube, or on the cloud with:

  • Amazon Elastic Container Service for Kubernetes (Amazon EKS).

  • Azure Kubernetes Service (AKS)

  • Google Kubernetes Engine (GKE)

Docker Desktop

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 is 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 for DOCKER_IMAGE_PREFIX variable in the cluster.sh script to deploy Presto:

DOCKER_IMAGE_PREFIX=localhost:5000/

Amazon EKS

Amazon EKS (Elastic Container Service for Kubernetes) is a managed Kubernetes service that allows you to run Kubernetes on AWS.

EKS architecture

The steps for setting up an EKS cluster are the following:

  1. Create an Amazon EKS cluster in the EKS console or with the eksctl command tool.

  1. Add a Node Group to the EKS cluster. By doing this you are creating the Kubernetes worker nodes, Amazon EC2 instances where the containers will run.

  1. When your cluster is ready, you can configure a Kubernetes tool (such as kubectl) to communicate with your cluster from the AWS CLI.

  1. Upload your Docker image to Amazon Elastic Container Registry (ECR). This registry allows the cluster to create pods by using your Docker images.

  1. Deploy and manage applications on your Amazon EKS cluster the same way that you would with any other Kubernetes environment.

It is important to clean up your Kubernete resources, ECR repositories and EKS clusters when you are finished, so that you do not incur additional charges for ongoing service.

For more detailed information see https://docs.aws.amazon.com/eks/latest/userguide/what-is-eks.html.

Azure Kubernetes Service

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:

  1. Create an AKS cluster in the Azure portal or with the Azure CLI.

  1. When your cluster is ready, you can configure a Kubernetes tool (such as kubectl) to communicate with your cluster from the Azure CLI.

  1. Upload your Docker image to  Azure Container Registry (ACR). This registry allows the cluster to create pods by using your Docker images.

  1. Deploy and manage applications on your AKS cluster the same way that you would with any other Kubernetes environment.

It is important to clean up your Kubernete resources, ACR repositories and AKS clusters when you are finished, so that you do not incur additional charges for ongoing service.

For more detailed information see https://docs.microsoft.com/en-us/azure/aks/tutorial-kubernetes-prepare-app

Build and Deploy

Things you may need to modify

Before building and deploying the Presto cluster you may need to modify some of the following parameters.

  • Versions in Dockerfile:

  • HADOOP_VERSION, currently set to 3.2.1

  • HIVE_VERSION, currently set to 3.1.2

  • PRESTO_VERSION, currently set to 338

   

  • The Java Keystore file for TLS (presto.jks with password sslpassphrase), as Presto uses TLS for client authentication.

This Java Keystore contains a self-signed certificate (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.jks).

  • Replicas (spec.replicas field) in Presto yaml file. The distributed Presto yaml file creates 2 Presto worker pods by default, but you can redefine how many replicas of Presto workers you would like to run.

  • Container image name (spec.template.spec.containers.image field) in Hive Metastore and Presto yaml files, as it depends on the registry being used: Amazon Elastic Container Registry (ECR), Azure Container Registry (ACR), Google Container Registry (GCR)...

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.

  • Presto schema file, schema.sql, that contains the DDL for the tables that will be created when the Presto cluster starts.

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.

   

  • StorageClass (spec.storageClassName field) and size (storage field) for the PostgreSQL volume in the Persistent Volume yaml file.

   

  • Memory settings, cpu settings and number of Presto workers in Hive Metastore and Presto yaml files.

Let's see now how to deploy Presto on Kubernetes!

Short Version

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: to be logged in the container registry (ECR, ACR, GCR, …) and to have configured the connection to the Kubernetes cluster in the kubeconfig file.

cluster.sh deploy s3: deploys a cluster that accesses datasets in S3.

Variables AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY have to be set in the cluster.sh script.

Also, the DOCKER_IMAGE_PREFIX has to point in the script, to the Amazon Elastic Container Registry URI, e.g. "<aws_account_id>.dkr.ecr.<region>.amazonaws.com/repo:", or to a local registry in case of testing a local cluster.

cluster.sh deploy wasb: deploys a cluster that accesses datasets in Azure Blob Storage.

Variables AZURE_STORAGE_ACCOUNT and AZURE_STORAGE_KEY have to be set in the cluster.sh script.

Also, the DOCKER_IMAGE_PREFIX has to point, in the script, to the Azure Container Registry URI, e.g. "<registry>.azurecr.io/", or to a local registry in case of testing a local cluster.

 

cluster.sh deploy adl: deploys a cluster that access datasets in Azure Data Lake Gen 1.

Variables AZURE_CLIENT_ID, AZURE_CLIENT_SECRET and AZURE_TENANT_ID have to be set in the cluster.sh script.

Also, the DOCKER_IMAGE_PREFIX has to point, in the script, to the Azure Container Registry URI, e.g. "<registry>.azurecr.io/", or to a local registry in case of testing a local cluster.

cluster.sh deploy abfs: deploys a cluster that accesses datasets in Azure Data Lake Gen 2.

Variables ABFS_STORAGE_ACCOUNT and ABFS_STORAGE_KEY have to be set in the cluster.sh script.

Also, the DOCKER_IMAGE_PREFIX has to point, in the script, to the Azure Container Registry URI, e.g. "<registry>.azurecr.io/" , or to a local registry in case of testing a local cluster.

cluster.sh delete: removes the cluster and all the configuration created for it.

Long Version

  1. Build Docker images for Hive Metastore and Presto:

           

$ cd denodo-presto-k8scluster-<VERSION>

$ docker build -t hive-metastore hive-metastore/

$ docker build -t presto presto/

            The Hive Metastore Docker image:

  • Downloads Hadoop and its s3a connector, because the datasets are stored in S3.

It also downloads the Azure Storage connector.

  • Downloads Hive and PostgreSQL (the external RDBMS for Hive Metastore).
  • Adds a Hive Metastore starting script that, as a first step, will initialize the PostgreSQL database.

The Presto Docker image:

  • Downloads Presto.
  • Downloads Presto CLI for initializing the Presto schema, with the S3 datasets exposed as Hive tables.
  • Adds a Presto starting script that will also load the DDL of the schema.sql file using  Presto CLI.

  1. Log in the Docker repository (ECR, ACR, GCR, …) and push the images so they can be pulled by Kubernetes later in the deployment.

$ docker login ...

$ docker tag hive-metastore repository/hive-metastore

$ docker push repository/hive-metastore

$ docker tag presto repository/presto

$ docker push repository/presto

  1. Create a secret for your AWS credentials that are required to access data on S3:

$ 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 of Azure credentials take a look to the cluster.sh script.

  1. Create two configmaps, one for the Hive Metastore and one for Presto, with the configuration files, so you can change configuration values without having to rebuild Docker images:

$ 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/

  1. Create a persistent volume to store the Presto schema metadata (S3 datasets exposed as Hive tables):

           

$ cd denodo-presto-k8scluster-<VERSION>

$ kubectl apply -f hive-metastore-pvc.yaml

  1. Deploy Hive Metastore with PostgreSQL on the Kubernetes cluster, using the Docker image built in step 1:

                     

$ cd denodo-presto-k8scluster-<VERSION>

$ kubectl apply -f hive-metastore.yaml

  1. Deploy Presto on the Kubernetes cluster: the service, the coordinator and the worker(s), using the Docker image built on step 1:

$ cd denodo-presto-k8scluster-<VERSION>

$ kubectl apply -f presto.yaml

  1. Finally, you can check the pods status in the cluster:

Kubernetes pods status

In the example above we can see that the Hive Metastore is not running. To find out why, we use the kubectl describe pod on the Pending pod and look at the Events section:

Kubernetes pods description

In the Events section a Message is displayed saying that we forgot to create the persistent volume described in the step 4.

After creating the persistent volume, we can verify that all is working OK now:

Kubernetes pods status

Once deployed, Presto is accessible at External-IP:8443 of the presto service, defined as LoadBalancer (spec.type field) in presto.yaml. Alternatively, you could define a service of type NodePort if you are not running Kubernetes on the cloud, although it is not mandatory.

Kubernetes services status

Presto is accessible at:

Presto Web UI      https://51.105.112.6:8443/ui/

JDBC URI        

         jdbc:presto://<PRESTO_HOST>:8443/hive/default?SSL=true

User          presto

Password      pr3st%

Cleanup

Don't forget to delete all the resources you have created, when you don't need the cluster anymore.

  1. Delete Presto:

$ kubectl delete -f presto.yaml

  1. Delete Hive Metastore:

   

$ kubectl delete -f hive-metastore.yaml

  1. Delete persistent volume. Be careful as this command deletes the whole Presto schema metadata, that is, S3 datasets exposed as Hive tables:

$ kubectl delete -f hive-metastore-pvc.yaml

Datasets as Tables

To access data you have to map the AWS S3 data files (or Azure Blob Storage, or Azure Data Lake, etc..) to tables in the Hive Metastore. For this, you have to provide the schema of the data, the file format, and the data location with the CREATE TABLE sentence.

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.

CREATE TABLE orders (

    orderkey bigint,

    custkey bigint,

    orderstatus varchar,

    totalprice double,

    orderpriority varchar,

    clerk varchar,

    shippriority integer,

    comment varchar

) WITH (

     external_location = 's3://my_bucket/path/to/folder/',

     format = 'ORC'

);

  • external_location: The data location must be a bucket name or a bucket name and one or more folders, not a specific file.

Don't forget to use a trailing slash, otherwise Presto will throw the following error: Can not create a Path from an empty string.

  • format: Supported file formats are:

  • ORC
  • PARQUET
  • AVRO
  • RCBINARY
  • RCTEXT
  • SEQUENCEFILE
  • JSON
  • TEXTFILE
  • CSV

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.

Cost based optimization

If your queries are complex and include joins of large data sets, running ANALYZE on tables/partitions may improve query performance. 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.

Table statistics

Partition Tables

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.

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');

Connect Denodo to Presto

To establish a connection to Presto you have to create a JDBC data source in Denodo and fill all the required fields:

  • Name
  • Database adapter: Presto
  • Database URI: jdbc:presto://<PRESTO_HOST>:8443/hive/default?SSL=true
  • Login: presto
  • Password: pr3st%

Also, if you do not specify an alternative Java TrustStore in the Database URI, (see all the URI parameters accepted), you have to import the server certificate (certificate.crt) in the Denodo server trust store.

The certificate.crt is distributed ONLY for testing purposes. This certificate accepts localhost and presto-denodo as Presto host names:

  • localhost: for tests performed in a local Kubernetes cluster, like the one provided by Docker Desktop.

  • presto-denodo: for tests performed in a Kubernetes cluster on the cloud, like the ones provided by AWS and Azure.

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.

Presto datasource in Denodo

Denodo can connect to Presto using an official JDBC driver.

 Denodo includes the Presto JDBC driver from prestodb while the Presto distribution installed in the cluster is from prestosql.  This setup works perfectly well as both Presto distributions are pretty similar. Although if you experience from some issue you can download the prestosql JDBC driver from https://repo1.maven.org/maven2/io/prestosql/presto-jdbc/ and install it in $DENODO_HOME/lib-external/jdbc-drivers/presto-VERSION.

Once the data source is configured, click on "Create base View" to explore Presto schemas with their tables and their columns.

Presto schemas in Denodo

Finally, you have reached your goal! You can run any query you want on your S3 datasets.

Query results in Denodo

Bulk Data Load

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:

Appendix: Hive Table Properties

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:

  • avro_schema_url (varchar): URI pointing to Avro schema for the table.

  • bucket_count (integer): Number of buckets.

  • bucketed_by (array(varchar)): Bucketing columns.

  • bucketing_version (integer): Bucketing version.

  • csv_escape (varchar): CSV escape character.

  • csv_quote (varchar): CSV quote character.

  • csv_separator (varchar): CSV separator character.

  • external_location (varchar): File system location URI for external table.

  • format (varchar): Hive storage format for the table. Possible values: [ORC, PARQUET, AVRO, RCBINARY, RCTEXT, SEQUENCEFILE, JSON, TEXTFILE, CSV].

  • orc_bloom_filter_columns (array(varchar)): ORC Bloom filter index columns.

  • orc_bloom_filter_fpp (double): ORC Bloom filter false positive probability.

  • partitioned_by (array(varchar)): Partition columns.

  • skip_footer_line_count (integer): Number of footer lines.

  • skip_header_line_count,integer,Number of header lines.

  • sorted_by,array(varchar),Bucket sorting columns.

  • textfile_field_separator (varchar): TEXTFILE field separator character.

  • textfile_field_separator_escape (varchar): TEXTFILE field separator escape character.