Running queries from the Presto (C++) CLI or Query workspace

IBM® watsonx.data allows running SQL queries from Presto (C++) CLI or through the Query workspace with or without using Query Optimizer.

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.

Running Presto (C++) queries:

Option 1: Running Presto (C++) queries by using Query Optimizer.

a. Run the following command to enter into the directory ibm-lh-client/bin:
```
cd ibm-lh-client/bin
```
b. 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>
```
c. 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
```
d. Run the following command to run Presto (C++) queries using Query Optimizer.
```
./presto-run --engine=$engine_name --session is_query_rewriter_plugin_enabled=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.

a. Run the following command to enter into the directory ibm-lh-client/bin:
```
cd ibm-lh-client/bin
```
b. 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>
```
c. 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
```
d. Run the following command to run Presto (C++) queries without using Query Optimizer.
```
./presto-run --engine=$engine_name --session is_query_rewriter_plugin_enabled=false -f $LH_SANDBOX_DIR/sql-files.sql
```
If the session parameter is_query_rewriter_plugin_enabled is set to false, you will not be able to execute the ExecuteWxdQueryOptimizer commands.
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 is ibmlhapikey or ibmlhapikey_<watsonx.datauser_id>. Password : The API key of the watsonx.data user. For more information about retrieving the API key, see Generating the API key.
  
  Starting with watsonx.data version 2.2.0, authentication using ibmlhapikey and ibmlhtoken as usernames is deprecated. These formats will be phased out in a future release. To ensure compatibility with upcoming versions, use the new format:ibmlhapikey_username and ibmlhtoken_username.
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 is_query_rewriter_plugin_succeeded value to be true or false. The optimized queries has the parameter value set to true.