Running queries from the Presto (C++) CLI and inspecting watsonx.data logs

IBM® watsonx.data allows running SQL queries from Presto (C++) CLI with or without using Query Optimizer. You can also inspect the log files of the queries that are run.

Before you begin

Follow the instructions in Establishing connection to watsonx.data using ibm-lh-client package utilities to establish connection.

Note: Optimizer is disabled if:

global environment variable is false and session is disabled.
global environment variable is true and session is disabled.
global environment variable is false and no session parameter is passed.

Optimizer is enabled if:

global environment variable is true and session is enabled.
global environment variable is false and session is enabled.
global environment variable is true and no session parameter is passed.

About this task

You can select the option of running Presto (C++) CLI queries with or without using Query Optimizer operator in watsonx.data by using the following steps. Also, watsonx.data allows inspecting the logs of the queries that are run.

Running Presto (C++) queries

Option 1: Running Presto (C++) queries by using Query Optimizer
1. Run the following command to enter into the directory ibm-lh-client/bin:
```
cd ibm-lh-client/bin
```
2. Create an SQL file and export the file path to LH_SANDBOX_DIR. For example, with file name sql-files.
```
export LH_SANDBOX_DIR=<path to sql-files>
```
3. Get the list of engine names and choose the one to be used. For example, engine name engine1.
```
./manage-engines --op=list
export engine_name=<engine1>
```
4. Run the following command to run Presto (C++) queries using Query Optimizer.
```
./presto-run --engine=$engine_name --session enable_wxd_query_optimizer=true -f $LH_SANDBOX_DIR/sql-files.sql
```
You must use either fully qualified name (3 part name such as <catalog.schema.table>) or 2 part name with the USE statement to qualify the catalog and schema.

Examples: 3 part name: select * from catalog.schema.table;

2 part name: use "catalog".schema; followed by select * from schema.table;
Option 2: Running Presto (C++) CLI queries without using Query Optimizer
1. Run the following command to enter into the directory ibm-lh-client/bin:
```
cd ibm-lh-client/bin
```
2. Create an SQL file and export the file path to LH_SANDBOX_DIR. For example, with file name sql-files.
```
export LH_SANDBOX_DIR=<path to sql-files>
```
3. Get the list of engine names and choose the one to be used. For example with engine name engine1.
```
./manage-engines --op=list
export engine_name=<engine1>
```
4. Run the following command to run queries using Query Optimizer.
```
./presto-run --engine=$engine_name --session enable_wxd_query_optimizer=false -f $LH_SANDBOX_DIR/sql-files.sql
```
Follow the steps to get details of the queries and to verify if the queries executed are optimized:
1. Log in to watsonx.data console.
2. From the navigation menu, open Infrastructure manager page.
3. Click on the Presto (C++) engine to open the component details page.
4. Copy and browse open the host URL of the Presto (C++) engine from the details page to open the Cluster Overview external web page.
5. Enter the username and password to login to the Cluster Overview page.
  
  Username : The username used in watsonx.data instance. Password : The password used for the watsonx.data instance.
6. Click on the Query ID you want to verify. This will open the Query Details page.
7. Open the JSON file from the Query Details page to verify the parameter wxdQueryOptimized value to be true or false. The optimized queries has the parameter value set to true.