SQL Query connection

To access your data in SQL Query, create a connection asset for it.

SQL Query is a service on IBM Cloud that you use to build, manage, and consume data lakes and their table assets in IBM Cloud Object Storage (COS). SQL Query provides functions to load, prepare, and query big data that is stored in various formats. It also includes a metastore with table definitions.

Prerequisites

• An SQL Query Standard plan instance is required in order to create tables or views.
• Before you can run SQL queries, you need to have one or more Cloud Object Storage buckets to hold the data to be analyzed and to hold the query results. For instructions, see Provisioning storage.
  

Create a connection to SQL Query

To create the connection asset, you need these connection details:

• The Cloud Resource Name (CRN) of the SQL Query instance. Go to the SQL Query service instance in your resources list in your IBM Cloud dashboard and copy the value of the CRN from the deployment details.
• Target Cloud Object Storage: A default location where SQL Query stores query results. You can specify any Cloud Object Storage bucket that you have access to. You can also select the default Cloud Object Storage bucket that is created when you open the SQL Query web console for the first time from IBM Cloud dashboard. See the Target location field in the SQL Query web console.
• IBM Cloud API key: An API key for a user or service ID that has access to your SQL Query and Cloud Object Storage services (for both the Cloud Object Storage data that you want to query and the default target Cloud Object Storage location).
  You can create a new API key for your own user:
• In the IBM Cloud console, go to Manage > Access (IAM).
• In the left navigation, select API keys.
• Select Create an IBM Cloud API Key.

Credentials

SQL Query uses the SSO credentials that are specified as a single API key, which authenticates a user or service ID.
The API key must have the following properties:

• Manage permission for the SQL Query instance
• Read access to all Cloud Object Storage locations that you want to read from
• Write access to the default Cloud Object Storage target location
• Write access to the SQL Query instance

For Credentials, you can use secrets if a vault is configured for the platform and the service supports vaults. For information, see Using secrets from vaults in connections.

Choose the method for creating a connection based on where you are in the platform

In a project Click Add to project > Connection. See Adding a connection to a project.

In a catalog Click Add to catalog > Connection. See Adding a connection asset to a catalog.

In a deployment space Click Add to space > Connection. See Adding connections to a deployment space.

In the Platform assets catalog Click New connection. See Adding platform connections.

Next step: Add data assets from the connection

• See Add data from a connection in a project.
• See Add data from a connection in a catalog.

Where you can use this connection

You can use SQL Query connections in the following workspaces and tools:

Analytics projects

• Data Refinery (Watson Studio or Watson Knowledge Catalog)
• Metadata import (Watson Knowledge Catalog)
• SPSS Modeler (SPSS Modeler service)

Catalogs

• Platform assets catalog
• Other catalogs (Watson Knowledge Catalog)

Restrictions

You can only use this connection for source data. You cannot write to data or export data with this connection.

SQL Query setup

To set up SQL Query on IBM Cloud Object Storage, see Getting started with IBM Cloud SQL Query.

Supported encryption

By default, all objects that are stored in IBM Cloud Object Storage are encrypted by using randomly generated keys and an all-or-nothing-transform (AONT). For details, see Encrypting your data. Additionally, you can use managed keys to encrypt the SQL query texts and error messages that are stored in the job information. See Encrypting SQL queries with Key Protect.

Running SQL statements

Video to learn more about SQL Query and how you can get started to run a basic query

Learn more

• IBM Cloud SQL Query
• Connecting to a Cloud Data Lake with IBM Cloud Pak for Data

Parent topic: Supported connections