• 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 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:

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

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

  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 so, you are creating the Kubernetes worker nodes (Amazon EC2 instances where the containers will run).

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

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

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

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

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

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

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

   

  • The Java Keystore file for TLS (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 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.

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

   

  • Memory settings, cpu settings and number of Presto workers in Hive Metastore template and Presto template 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: 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.

  • 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/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.
  • 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 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, 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.

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

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

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

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

User          presto

Password      pr3st%

Cleanup

Do not forget to delete all the resources you have created, when you do not 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, datasets exposed as Hive tables:

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

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:

  • m5.4xlarge
  • 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.

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.

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

Configuration file

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

  • dirToExplore: 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: enables to include only certain files or folders in the exploring process.

  • Denodo connection properties: JDBC URI, user and password.

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

  • Presto Connection properties: JDBC URI, user and password.

Requisites to be able to connect to Presto
  1. Import certs/certificate.crt in the TrustStore of the JVM (the one executing this code and the Denodo's JVM)

      OR

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

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:

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

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

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 presto-<version> in the Denodo server.

Import Presto driver with name: presto-343

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

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

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
  • Driver class path: presto-343
  • Driver class: com.facebook.presto.jdbc.PrestoDriver
  • Database URI: jdbc:presto://<PRESTO_HOST>: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 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:

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