IBM watsonx.data Presto connection

To access your data in IBM watsonx.data, create a connection asset for it. The connection asset includes information for connecting to a watsonx.data instance and to the Presto query engine that is running on that instance.

IBM watsonx.data is an open, hybrid, and governed data lakehouse that is an optimized query engine for all data and AI workloads.

Before you begin

Differences between the watsonx.data Presto and the Presto connections

IBM watsonx.data incorporates the Presto SQL Query Engine. Both the watsonx.data Presto and Presto connections can create connection assets to interact with the Presto SQL Query Engine in IBM watsonx.data.

IBM watsonx.data Presto connection

The IBM watsonx.data Presto connection enables reading from IBM watsonx.data using the Presto SQL Query Engine. It also supports writing tables in Iceberg and Delta Lake table format to the following storage systems:

Amazon S3
Amazon S3 with multi-region access points
Apache Ozone
Google Cloud Storage
IBM Ceph
IBM Cloud Object Storage
IBM Storage Scale
MinIO
Custom S3

Additionally, with the IBM watsonx.data Presto connection, you can write Iceberg tables to Azure Data Lake Storage. The connection supports multiple write modes, including update, and merge, as well as executing UPDATE statements. All write operations are executed through the Presto engine. The connection also provides an ingest write mode, which enables writing data directly to supported storage systems while automatically registering Iceberg and Delta tables in the Metadata Service (MDS) in IBM watsonx.data. This connection is also required to enable integration with IBM watsonx.data intelligence, allowing you to fully leverage intelligence capabilities such as data masking and rule protection within IBM watsonx.data.

IBM recommends using the watsonx.data Presto connection when connecting from Cloud Pak for Data to IBM watsonx.data.

For more information about the watsonx.data Presto connection, see the rest of the topic.

Presto

The Presto connection provides a generic way to connect to any Presto engine, including the implementation in IBM watsonx.data. It supports insert, update, and merge write modes, as well as executing UPDATE statements, but only in the context of Iceberg tables. As with the watsonx.data-specific connection, all write operations are executed through the Presto engine. However, this connection does not provide full integration with IBM watsonx.data intelligence.

Prerequisite

Set up an instance of watsonx.data.

You can connect to software or as a service instances:

watsonx.data software on Cloud Pak for Data: See Installing watsonx.data on Cloud Pak for Data.
watsonx.data as a Service: See Getting started with watsonx.data as a Service
watsonx.data stand-alone software: See Installing stand-alone watsonx.data

Create a connection to watsonx.data

Your connection details vary between the deployment type chosen. To create the connection asset, in the Connection details section of the Connect to a data source page, select the deployment type:

IBM watsonx.data Developer edition
IBM watsonx.data as a Service
IBM watsonx.data on Red Hat OpenShift

You can also leave the deployment type in the default value where you see the legacy connection details.

The details that you need to provide will change based on the deployment type you pick:

IBM watsonx.data Developer edition

You can import a JSON file to complete these fields using the Import connection values. To get the JSON file needed for this connection, you need to go to your watsonx.data instance's console page and navigate to the Connect information field and you can copy the JSON file.

Hostname or IP address: Find this information in the console under Configurations > Connection information > Instance details.
Port: The default port number is 443. You can find this information in the console under Configurations > Connection information > Instance details.
Instance ID: Find this value in the watsonx.data console. Click Instance details from the navigation menu. You can also find this information in the console under Configurations > Connection information > Instance details.
Prepare statement support: Enable this option if you want to allow prepare statements for the connector.

IBM watsonx.data as a Service

Hostname or IP address: Find this information in the console under Configurations > Connection information > Instance details.
Port: The default port number is 443. You can find this information in the console under Configurations > Connection information > Instance details.
CRN: Cloud resource name: Find this value in the watsonx.data console. Click Instance details from the navigation menu. You can also find this information in the console under Configurations > Connection information > Instance details.
Prepare statement support: Enable this option if you want to allow prepare statements for the connector.

IBM watsonx.data on Red Hat OpenShift

Hostname or IP address: Find this information in the console under Configurations > Connection information > Instance details.
Port: The default port number is 443. You can find this information in the console under Configurations > Connection information > Instance details.
Instance ID: Find this value in the watsonx.data console. Click Instance details from the navigation menu. You can also find this information in the console under Configurations > Connection information > Instance details.
Prepare statement support: Enable this option if you want to allow prepare statements for the connector.

Legacy connection details

watsonx.data software

To create the connection asset, in the Connection details section of the Connect to a data source page, select Connect to watsonx.data on Cloud Pak for Data and provide these details:

Hostname or IP address: Find this information in the console under Configurations > Connection information > Instance details.
Port: The default port number is 443. You can find this information in the console under Configurations > Connection information > Instance details.
Instance ID: Find this value in the watsonx.data console. Click Instance details from the navigation menu. You can also find this information in the console under Configurations > Connection information > Instance details.
Instance name: Find the instance name in the Cloud Pak for Data web client home page. Click Services > Instances from the navigation menu.
Prepare statement support: Enable this option if you want to allow prepare statements for the connector.

watsonx.data as a Service

Hostname or IP address: Find this information in the console under Configurations > Connection information > Instance details.
Port: The default port number is 443. You can find this information in the console under Configurations > Connection information > Instance details.
Instance ID: Find this value in the watsonx.data console. Click Instance details from the navigation menu. You can also find this information in the console under Configurations > Connection information > Instance details.
Instance name: Find this value on the watsonx.ai Service instances page. Click Administration > Services > Service instances. For example, watsonx.data-aaa. Do not use the suggested instance name that is shown in the field.
CRN: Cloud resource name: Find this value in the watsonx.data console. Click Instance details from the navigation menu. You can also find this information in the console under Configurations > Connection information > Instance details.
Prepare statement support: Enable this option if you want to allow prepare statements for the connector.

Credentials

Your credentials vary between the deployment type chosen:

IBM watsonx.data Developer edition
IBM watsonx.data as a Service
IBM watsonx.data on Red Hat OpenShift

You can also leave the deployment type in the default value where you see the legacy connection details.

IBM watsonx.data Developer edition

Username and password: The username and password that is used to log in to the watsonx.data standalone console.

IBM watsonx.data as a Service

API key: The API key of the account that has access to the watsonx.data instance.

To create an API key, see Creating an API key in the console.
To create an Amazon Web Services API key, see Creating API keys.

IBM watsonx.data on Red Hat OpenShift

Select an authentication method:

Username and password: The username and password that is used to access Cloud Pak for Data where the watsonx.data instance is located.
Username and API key: The username and API key that is used to access Cloud Pak for Data where the watsonx.data instance is located.

This authentication method is recommended if Cloud Pak for Data uses an Identity Management Service (IAM), for example, LDAP or SSO. The API key is located in the Profile and settings of the target Cloud Pak for Data cluster. For information on API keys, see Generating API keys for authentication.

Legacy connection details

watsonx.data software

The username and password or usernames and API key for the watsonx.data instance. The same credentials are also used for the engine.

Select the authentication method:

Username and password: The username and password that is used to access Cloud Pak for Data where the watsonx.data instance is located, or the username and password for watsonx.data standalone.
Username and API key: The username and API key that is used to access Cloud Pak for Data where the watsonx.data instance is located, or the username and password for watsonx.data standalone. This authentication method is recommended if Cloud Pak for Data uses an Identity Management Service (IAM), for example, LDAP or SSO. The API key is located in the Profile and settings of the target Cloud Pak for Data cluster. For information on API keys, see Generating API keys for authentication.

watsonx.data as a Service

The username and password for the watsonx.data instance. The same credentials are also used for the engine.

Username: The default username is ibmlhapikey_<cloud-account-email-address>. For example, ibmlhapikey_username@example.com.
Password: The password is the user's API key. To create an API key, see IBM Cloud docs: Creating an API key in the console.
To create an Amazon Web Services API key, see Creating API keys.

Certificates

By default, SSL is enabled is selected. This setting is recommended for increased security. If you do not use SSL, the data might be subject to vulnerabilities such as data leakage. Although the database that is hosted in watsonx.data can also have an SSL certificate, the connection goes through the engine.

The SSL certificate must be in PEM format.

The SSL certificates information vary between the deployment type chosen:

IBM watsonx.data Developer edition
IBM watsonx.data as a Service
IBM watsonx.data on Red Hat OpenShift

IBM watsonx.data Developer edition

The SSL certificate is optional.

If SSL is enabled on a watsonx.data instance on Cloud Pak for Data and the certificate is a self-signed certificate, you must enter the certificate in the SSL certificate field.

Ask your watsonx.data administrator if SSL is set up. You can find the SSL certificate in the watsonx.data console under Configurations > Connection information > Instance details.

IBM watsonx.data as a Service

The SSL certificate is optional.

IBM watsonx.data on Red Hat OpenShift

The SSL certificate is optional.

If SSL is enabled on a watsonx.data instance on Cloud Pak for Data and the certificate is a self-signed certificate, you must enter the certificate in the SSL certificate field.

Ask your watsonx.data administrator if SSL is set up. You can find the SSL certificate in the watsonx.data console under Configurations > Connection information > Instance details.

Engine connection details

Important: Engine connection details are deprecated, but you can still add and update engine connection details. However, this will be discontinued in the future.

Enter the engine connection details

Supported engine versions

For watsonx.data on Cloud Pak for Data version 5.0.3 and later:

Presto (Java)
Presto (C++)

For watsonx.data on Cloud Pak for Data version 5.0.2 and before:

Presto (Java)

For watsonx.data as a Service:

Presto (Java)
Presto (C++)

Provide these engine connection details. Find this information in the watsonx.data web console under Configurations > Connection information > Engine and service connection details.

Engine's hostname or IP address: The hostname or IP address is the value of the Internal host field.
Engine ID: This value is in the Engine ID field.
Engine's port: The port number is the value in the Internal host field after the colon (:). The default port number is 8443.

Writing data into watsonx.data

You can ingest data into watsonx.data using the IBM watsonx.data Presto connector. You must enter a catalog_name, schema_name, and table_name properties. The table_name property is required. You can pass the fully qualified name, catalog_name.schema_name.table_name, into the table_name property.

The watsonx.data Presto connector creates Iceberg tables and Delta Lake tables directly on storage defined in IBM watsonx.data.

Currently the connector supports writing to the following storage:

Amazon S3
Apache Ozone
Google Cloud Storage
IBM Ceph
IBM Cloud Object Storage
IBM Storage Scale
MinIO

Writing to the following storage for Iceberg tables only:

Azure Data Lake Storage

The Presto engine is used only in update or merge write modes.

Table actions

You can use the IBM watsonx.data Presto connector to ingest data into IBM watsonx.data. The data can be written in the Iceberg table format or the Delta Lake table format.

Using table actions, you can specify what operation you want to do with the Iceberg table or the Delta Lake table.

The following table actions are supported:

Append: The connector will create a new Iceberg table or Delta Lake table, or append data to the existing table.
Create: The connector will create a new Iceberg table or Delta Lake table. When using this action on the existing table, you get an error.
Replace: The connector overwrites the contents of the Iceberg or Delta Lake table with new data. The table's schema and metadata can change as well.
Truncate: The connector logically removes data from the existing table and add new rows of data. This action cannot change the table's column definition.

Write modes

In IBM watsonx.data Presto connection you can use the following write modes:

Ingest: Loads records directly into a table in storage and registers the table in the watsonx.data metastore. The Presto engine is not used in this operation.
Update: Modifies existing records based on specified columns in the key_column_names property. Only records matching the given keys are updated.
Merge: Combines insert and update operations. It updates existing records and inserts new ones based on the columns from key_column_names.
Update statement: A custom SQL statement manually provided by the user. It can include any operation such as INSERT, UPDATE, MERGE, or DELETE.
Update statement with table action: A custom SQL statement manually provided by the user, preceded by a table action. It can include any operation such as INSERT, UPDATE, MERGE, or DELETE.
Static statement: Any SQL statement that is executed without restrictions on type or purpose.

watsonx.data web console

Limitation

The watsonx.data Presto connection does not support TIME and TIMESTAMP data types when the Presto C++ engine is used.
The watsonx.data Presto connection does not support TIME and TIMESTAMP data types when reading Data Lake tables.
The watsonx.data Presto connection does not support connecting to watsonx.data as a Service in Sydney if watsonx.data as a Service only uses private endpoints
The watsonx.data Presto connection does not support writing to watsonx.data as a Service in Toronto if watsonx.data as a Service only uses private endpoints.
The watsonx.data Presto connection does not support writing Delta Lake tables to Azure Data Lake Storage.
The watsonx.data Presto connection supports update and merge operations only for iceberg tables.
The watsonx.data Presto C++ engine does not support update and merge operations.
Iceberg tables do not support concurrent writes. To mitigate this, the connector retries conflicting update and merge operations up to three times. If conflicts continue, execute jobs sequentially.
If Prepare statement support is enabled, and a long query is sent to the Presto engine through the connector, then the following error might occur:
```
SQL statement failed. Network closed for unknown reason
```

Learn more

Related connections

Milvus connection