Using IBM Cloud® Data Engine

IBM Cloud® Data Engine (formerly SQL Query) is now end of market. No new instances of Data Engine can be created. Existing instances can still be used until end of support. See Deprecation of Data Engine for more information.

IBM Cloud® Data Engine is a fully-managed service that lets you run SQL queries (that is, SELECT statements) to analyze, transform, or clean up rectangular data using the full ANSI SQL standard.

Querying Object Storage with SQL Query

You can use SQL Query to create SELECT statements only; actions such as CREATE, DELETE, INSERT, and UPDATE are not possible.

Input data for your queries are read from ORC, CSV, JSON, or Parquet files located in one or more IBM Cloud Object Storage instances. Each query result is written by default to a CSV file in a Cloud Object Storage instance where you created the integration. But you can freely override and customise the format and Object Storage location as part of the SQL statement that you run.

You can use a custom INTO clause of a SELECT statement to control where and how result data from a SELECT statement is written to IBM Cloud Object Storage.

Getting started using SQL Query SELECT statements from inside your instance is as easy as creating an integration. Objects of queryable data formats, as well as folders with multiple objects of a consistent queryable format (when shown in the "folders" view) are labeled as shown in Figure 1.

Object with SQL label — Figure 1. SQL label shows queryable objects

You can retrieve a SQL queryable URL for objects for a selected individual object (Object SQL URL) or for all objects currently displayed with an active prefix filter (Filtered SQL URL). You can use this URL inside the SQL statement as the table name.

Figure 1 shows how to access your data using Data Engine. When you click on the ellipses at the end of a row of an object that you can query, you will see a menu where you can "Access with SQL" by selecting that option.

Figure 2. Access with SQL shows on queryable objects

The panel shown in Figure 3 shows how to access your data using Data Engine. The location of your object appears in the panel for reference outside of the console. The instances to which you have access appear in the dropdown list in the panel. After you specify the instance, click on "Open in SQL Query" to launch your instance already pre-populated with a sample query written in the appropriate SQL.

Open with SQL — Figure 3. Access with SQL launch panel

Access is based on permissions, and you may wish to study more about authentication and access.

Getting Results

Figure 4 shows a sample SQL query you can modify. By pressing the "Run" button, the list below the query will populate with a new entry that links to your results. The results will be stored in the location shown beneath the query.

SQL Query Window — Figure 4. Access with SQL query window

The entry representing the job of the SELECT statement run previously is shown in Figure 5. There are two tabs, "Results" and "Details," at the top of the list that allow you to switch between seeing the results and more detailed information.

SQL Query Results — Figure 5. Access with SQL query jobs

The entry representing the details of running the SELECT statement run previously is shown in Figure 6.

SQL Query Details — Figure 6. Access with SQL query jobs

Next Steps

For more information on using Data Engine see the Data Engine documentation.