Denodo Presto Cluster on Kubernetes - User Manual

Last modified on: 30 Sep 2021

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

The steps to follow are:

Store your dataset in S3.

Build and deploy Presto on Kubernetes.

Expose S3 data as Hive tables in Presto.

Configure a Presto data source in Denodo Platform.

Run SQL queries in Denodo against Presto.

This document focuses on datasets stored in AWS S3, as an example, 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.

Presto on Kubernetes

Architecture

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

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.

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

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

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:

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

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

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

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

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

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

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:

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

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

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

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

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

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 352

The Java Keystore file for TLS of the Presto cluster (presto/conf/presto.jks with password sslpassphrase), as Presto uses TLS for client authentication.

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 of the Presto cluster or replace the existing one (presto/conf/presto.jks).

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

✸ If you use the cluster.sh script to deploy the cluster, you only have to set the NUM_WORKERS variable and the cluster.sh script will set the number of Presto workers for you.

Container image name (spec.template.spec.containers.image field) in Hive Metastore template and Presto template 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. Or, make use of the metadata discovery tool, explained later in this document.

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

How to connect to S3-compatible storage

S3-compatible storage is a solution that allows access to and management of the data it stores over an S3 compliant interface, e.g. MinIO, IBM Cloud Object Storage, Dell EMC ECS,...

In order to connect to S3-compatible storage from the Presto cluster the following information is required:

Access Key Id: similar to a username. It should be provided in the cluster.sh script through the AWS_ACCESS_KEY_ID variable.
Secret Access Key: similar to a password. It should be provided in the cluster.sh script through the AWS_SECRET_ACCESS_KEY variable.
REST Endpoint: an address used to send requests to. It can be found in the storage documentation. The endpoint should be provided to the cluster in these both files:

hive-metastore/conf/core-site.xml through the fs.s3a.endpoint property.
presto/conf/hive.properties.template through the hive.s3.endpoint property.

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 | s3_instancerole | wasb | adl] | start | stop | 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.

✸ The script has three input variables that are mandatory for cluster fine-tuning:

NUM_WORKERS: the number of Presto workers that should have the cluster.

CPUS_PER_NODE: number of CPUs of each node of the cluster.

MEMORY_PER_NODE: total memory in GB of each node of the cluster.

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.
Variable DOCKER_IMAGE_PREFIX has to point to the Amazon Elastic Container Registry URI, e.g. "<aws_account_id>.dkr.ecr.<region>.amazonaws.com", or to a local registry in case of testing a local cluster.

✸ In case of using the Amazon Elastic Container Registry, before the cluster.sh script can push the images to Amazon ECR, you must create two repository to store them: hive-metastore and presto.

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

Recommended when a cluster runs in EC2 as it uses the instance profile credentials (no need to set AWS credentials).
Variable DOCKER_IMAGE_PREFIX has to point to the Amazon Elastic Container Registry URI, e.g. "<aws_account_id>.dkr.ecr.<region>.amazonaws.com", or to a local registry in case of testing a local cluster.

✸ In case of using the Amazon Elastic Container Registry, before the cluster.sh script can push the images to Amazon ECR, you must create two repository to store them: hive-metastore and presto.

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.
Variable DOCKER_IMAGE_PREFIX has to point 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.
Variable DOCKER_IMAGE_PREFIX has to point 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.
Variable DOCKER_IMAGE_PREFIX has to point 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 start: recreates a previously stopped cluster with previous configuration and metadata, that is, the Presto table definitions.

cluster.sh stop: deletes the cluster but keeps the configuration and the metadata: the table definitions that Presto rely upon.

cluster.sh delete: removes the cluster and all the configuration created for it, destroying the metadata persistence, that is, the Presto table definitions.

Long Version

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, the s3a connector and the Azure Storage connector to access datasets in AWS and Azure.
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.
Adds a Presto starting script that will also load the DDL of the schema.sql file using Presto CLI, to expose datasets as Hive tables.

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:<version> <repository>/hive-metastore:<version>

$ docker push <repository>/hive-metastore:<version>

$ docker tag presto:<version> <repository>/presto:<version>

$ docker push <repository>/presto:<version>

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

Create two configmaps with the configuration files, one for the Hive Metastore and one for Presto, so you can change configuration values without having to rebuild the 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/

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

$ cd denodo-presto-k8scluster-<VERSION>

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

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

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

Finally, you can check the pods status in the cluster with kubectl get pods:

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

↓ (TLS certificate)

jdbc:presto://presto-denodo:8443/hive/default?SSL=true

User presto

Password pr3st%

/etc/hosts file

Cleanup

Do not forget to delete all the resources you have created, when you do not need the cluster anymore.

Delete Presto:

$ kubectl delete -f presto.yaml

Delete Hive Metastore:

$ kubectl delete -f hive-metastore.yaml

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

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

If you want to delete the persistent volume, but save the cluster metadata for a later deployment, you need to execute a dump over the PostgreSQL database of the Hive Metastore, before deleting the cluster. This dump generates a text file with SQL commands that, when fed back to the Hive Metastore, will recreate the database in the same state as it was at the time of the dump.

Dump in the old cluster:

$ kubectl exec <Hive Metastore Pod> -- bash -c "PGPASSWORD=hive pg_dump -U hive -h localhost metastore" > database.sql

Restore in the new cluster:

$ cat database.sql | kubectl exec -i <Hive Metastore Pod> -- bash -c "PGPASSWORD=hive psql -U hive -h localhost -d metastore

Cluster Tuning

Machines

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:

r5.4xlarge

m5.8xlarge

r5.8xlarge

m5.16xlarge

c5.24xlarge

CPU

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

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 - this is done automatically by the cluster.sh script of this distribution.

Other properties you can configure, regarding memory are the query.max-memory and query.max-memory-per-node. In this distribution we used their default values. But they can be tuned in files presto/conf/config.properties.coordinator and presto/conf/config.properties.worker.

The query.max-memory property is the maximum amount of user memory a query can utilize across all workers in the cluster. For example, memory used by the hash tables built during execution, memory used during sorting, etc. When the user memory allocation of a query across all workers hits this limit it is killed. Default value is 20GB.

But if the cluster needs to handle bigger queries, you should make query.max-memory bigger. For this, the recommendation is to run the EXPLAIN ANALYZE with these big queries and see what the peak max memory is examining the query plan.

The query.max-memory-per-node property is the maximum amount of user memory a query can use on a worker. Default value is JVM max memory * 0.1. It is a useful parameter when you have skewed distributions. When doing a distributed hash join, the rows are divided across machines using a hash function. If your data is not uniformly distributed, you will get more data on a machine. So if you have perfectly uniformly distributed data you could set query.max-memory-per-node = query.max-memory / number of workers. If you have a small cluster, you could double this value.

Parquet files

If you are suffering from network or IO bandwidth, it is recommended that you compress your files:

High compression algorithm: ZSTD is preferred over ZIP

Low compression algorithm: LZ4 is preferred over Snappy

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.

Datasets as Tables

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'

);

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.

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.

Metadata Discovery Tool for Parquet files

The metadata discovery tool explores the distributed storage, starting from a folder given by the user, and from every Paquet dataset it finds, in a folder or subfolder, it creates the corresponding table in Presto. Then, it calls a Denodo stored procedure that introspects Presto and creates the Denodo base views for all the new tables created in Presto.

The tool is distributed both as a script and as a Denodo stored procedure.

Script

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

Configuration file

dirToExplore=s3a://example-bucket/dir/

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.analyze.tables=true

# Possible values: NEVER_REPLACE, REPLACE_IF_NEW_COLUMNS, REPLACE_ALWAYS

metadata.duplicated.strategy=NEVER_REPLACE

configuration.properties

dirToExplore: directory of the distributed storage path (s3a, adl, wasb, abfs), to start the metadata discovery process.

crendentials.xml.path: core-site.xml with the distributed storage credentials.

includedPattern: a regular expression that will select only certain files or folders in the exploring process. E.g., (.*)invoices(.*).

Denodo connection properties: JDBC URI, user and password.

Prerequisites in Denodo (before Denodo 8.0 Update 20210209):

The metadata-discovery/lib/presto-jdbc-<version>.jar has to be imported in the Denodo server with the name prestosql-3xx.

✸ With Denodo 8.0 Update 20210209 installed you do not have to import the driver.

Import Presto driver with name: prestosql-3xx

Presto Connection properties: JDBC URI, user and password.

Requisites to be able to connect to Presto

Import certs/certificate.crt in the TrustStore of the JVM (the one executing this code and the Denodo's JVM)

Use the JDBC URI parameters SSLTrustStorePath and SSLTrustStorePassword to provide the path to a TrustStore with the certs/certificate.crt

The certs/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. 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.

denodo.datasource.name: the name of the Presto data source that will be created at Denodo by this script.

denodo.datasource.folder: the Presto data source created at Denodo by this script will be located at this folder when this parameter is specified. The folder will be created by this script if needed.

denodo.baseview.folder: all the base views created by this script will be located at this folder when this parameter is specified. The folder will be created by this script if needed.

denodo.baseview.prefix: all the base views created by this script will be prefixed with this value when this parameter is specified.

presto.analyze.tables: whether to run the ANALYZE command after each table creation or not. This command collects statistical information about the data that the Presto optimizer uses to plan the query, based on cost strategies. Default is true.

metadata.duplicated.strategy: it chooses what to do with the metadata that already exists at Presto and at Denodo. Possible values are NEVER_REPLACE, REPLACE_IF_NEW_COLUMNS, ALWAYS_REPLACE. Default is NEVER_REPLACE.

NEVER_REPLACE: It will not replace tables at Presto and data sources and base views at Denodo if they already exist.

REPLACE_IF_NEW_COLUMNS: It will replace tables at Presto, and its corresponding base views at Denodo, if new columns are detected for existing Presto tables.

This can affect existing base views at Denodo that had modifications made by the user, like renamed fields, field types modifications, cache configuration... as base views will be replaced and these kinds of changes will be lost.

Our recommendation is to always leave Denodo base views as is and make changes only in views that derive from these base views.

ALWAYS_REPLACE: It will replace tables at Presto, and its corresponding base views at Denodo, if any kind of change is detected for existing Presto tables: new columns, deleted columns, different column types, etc.

Our recommendation is to always leave Denodo base views as is and make changes only in views that derive from these base views.

Output

The output of the process would be:

the list of tables created at Presto

the Presto data source created at Denodo, with the configured presto.jdbcUrl as the Database URI of the data source in Denodo.

the Presto base views created at Denodo

METADATA DISCOVERY RESULTS

******************************************

Commands executed at Presto:

------------------------------------------

CREATE TABLE "customer" (

ANALYZE "customer"

CREATE TABLE "store" (

ANALYZE TABLE "store"

CREATE TABLE "store_sales" (

ANALYZE "store_sales"

CREATE TABLE "date_dim" (

ANALYZE "date_dim"

------------------------------------------

Commands executed at Denodo (data source):

--------------------------------------------------------------

CREATE FOLDER '/01 - Connectivity';

CREATE FOLDER '/01 - Connectivity/01 - Data Sources';

CREATE 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_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

Troubleshooting

"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 prestosql-3xx in the Denodo server, but only in Denodo 8.0 installations previous to Update 20210209.

Import Presto driver with name: prestosql-3xx

Stored Procedure

The DISCOVER_PRESTO_METADATA stored procedure is distributed in the folder metadata-discovery/sp/denodo-presto-k8scluster-{version}-jar-with-dependencies.jar.

To be able to use this store procedure in Denodo, first, you need to import the denodo-presto-k8scluster-{version}-jar-with-dependencies.jar file using the File > Extension management menu option of the VDP Administration.

Second, you have to create the stored procedure by clicking on the menu File > New > Stored procedure option, in the VDP Administration, and provide the following values:

Name: a name

Class name: com.denodo.connect.presto.MetadataDiscoveryStoredProcedure

Select Jars: denodo-discover-presto-metadata-<version>

FInally, you can execute the stored procedure:

Using the Execute button in the dialog that displays the schema of the stored procedure. Denodo will show a dialog where you have to enter the input values.

Using the VQL Shell :

SELECT * FROM discoverprestometadata()

WHERE dir_to_explore='s3a://bucket/dir/'

and credentials_path='/absolute/path/to/core-site.xml'

and datasource_name='the_presto_datasource'

and duplicate_strategy='NEVER_REPLACE'

and included_pattern=NULL

and baseview_folder='/01 - Connectivity/02 - Base Views'

and baseview_prefix='bv_'

and analyze_tables=true

The input parameters required by the stored procedure are:

dir_to_explore: directory of the distributed storage path (s3a, adl, wasb, abfs), to start the metadata discovery process.

crendentials_path: absolute path to core-site.xml with the distributed storage credentials.

datasource_name: the name of a Presto data source already created in Denodo. The base views created by this stored procedure will belong to this Presto data source.

The following input parameters are optional:

duplicate_strategy: it chooses what to do with the metadata that already exists at Presto and at Denodo. Possible values are NEVER_REPLACE, REPLACE_IF_NEW_COLUMNS, ALWAYS_REPLACE. If no value is provided for the parameter NEVER_REPLACE is selected.

NEVER_REPLACE: It will not replace tables at Presto and data sources and base views at Denodo if they already exist.

REPLACE_IF_NEW_COLUMNS: It will replace tables at Presto, and its corresponding base views at Denodo, if new columns are detected for existing Presto tables.

Our recommendation is to always leave Denodo base views as is and make changes only in views that derive from these base views.

ALWAYS_REPLACE: It will replace tables at Presto, and its corresponding base views at Denodo, if any kind of change is detected for existing Presto tables: new columns, deleted columns, different column types, etc.

Our recommendation is to always leave Denodo base views as is and make changes only in views that derive from these base views.

included_pattern: a regular expression that will select only certain files or folders in the exploring process. E.g., (.*)invoices(.*)

baseview_folder: all the base views created by the stored procedure will be located at this folder when this parameter is specified. The folder will be created by the stored procedure if needed.

baseview_prefix: all the base views created by the stored procedure will be prefixed with this value when this parameter is specified.

analyze_tables: whether to run the ANALYZE command after each table creation or not.

This command collects statistical information about the data that the Presto optimizer uses to plan the query, based on cost strategies.

The output of this stored procedure is EXECUTED_COMMAND, that shows the DDL statements executed successfully at Presto and at Denodo:

the SQL of each tables created at Presto

the VQL of each Presto wrapper and base view created at Denodo

Cost Based Optimization

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, by default, 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

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.

.../weblogs/elb_name=abc/elb_response_code=200/…

.../weblogs/elb_name=abc/elb_response_code=404/…

.../weblogs/elb_name=xyz/elb_response_code=200/…

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.

✸ Note that in case the directories don’t respect the naming convention of Hive, it is possible to add the partitions manually using the system.register_partition call.

.../weblogs/abc/200/…

.../weblogs/abc/404/…

.../weblogs/xyz/200/…

Due to security reasons, this procedure is enabled only when hive.allow-register-partition-procedure is set to true. For this reason this option is not enabled in the configuration of our cluster.

However, if you wish to enable this option you must modify the file /presto/conf/hive.properties.template and add the property hive.allow-register-partition-procedure=true. After that you can run your cluster as usual and create a table in hive. In this case you can’t use the metadata discovery tool:

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

);

After the table creation you must run:

call system.sync_partition_metadata('default', 'weblogs', 'FULL');

And finally you can define the partitions:

CALL SYSTEM.register_partition('default', 'weblogs', ARRAY['elb_name', 'elb_response_code'], ARRAY['abc','200'],'s3a://my_bucket/path/to/folder/abc/200')

CALL SYSTEM.register_partition('default', 'weblogs', ARRAY['elb_name', 'elb_response_code'], ARRAY['abc','404'],'s3a://my_bucket/path/to/folder/abc/404')

CALL SYSTEM.register_partition('default', 'weblogs', ARRAY['elb_name', 'elb_response_code'], ARRAY['xyz','200'],'s3a://my_bucket/path/to/folder/xyz/200')

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: prestosql (Generic in Denodo 8.0 installations previous to Update 20210209.
Driver class path: prestosql-3xx
Driver class: com.facebook.presto.jdbc.PrestoDriver
Database URI: jdbc:presto://presto-denodo:8443/hive/default?SSL=true

Login: presto
Password: pr3st%

✸ Driver class path:

The metadata-discovery/lib/presto-jdbc-<version>.jar has to be imported in the Denodo server with the name prestosql-3xx, but only in Denodo 8.0 installations previous to Update 20210209.

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:

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. 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

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 Update 7.0u08, you have 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 A: 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.

Appendix B: Using IAM Assumed Roles

If using IAM Assume Role you need to configure, besides the credentials AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY in the cluster.sh, these two files before executing the command:

$ cluster.sh deploy s3

presto/conf/hive.properties.template

hive.s3.iam-role=arn:aws:iam::ACCOUNT:role/POLICYFORROLE

hive-metastore/conf/core-site.xml

<name>fs.s3a.access.key</name>

<value>ACCESS KEY</value>

</property>

<name>fs.s3a.secret.key</name>

<value>SECRET KEY</value>

</property>

<name>fs.s3a.aws.credentials.provider</name>

<value>org.apache.hadoop.fs.s3a.AssumedRoleCredentialProvider

</value>

<value>org.apache.hadoop.fs.s3a.auth.AssumedRoleCredentialProvider

</value>

</property>

<name>fs.s3a.assumed.role.arn</name>

<value>arn:aws:iam::ACCOUNT:role/POLICYFORROLE</value>

</property>

<name>fs.s3a.assumed.role.sts.endpoint</name>

<value>sts.REGION.amazonaws.com</value>

</property>

<name>fs.s3a.assumed.role.sts.endpoint.region</name>

<value>REGION</value>

</property>

<name>fs.s3a.assumed.role.credentials.provider</name>

<value>org.apache.hadoop.fs.s3a.SimpleAWSCredentialsProvider

</value>

</property>

</configuration>