Query Databases Using the SQL Console in SAP HANA Cloud Central

This step demonstrates how a SQL console can quickly be opened from within SAP HANA Cloud Central and how to change the SQL console’s credentials and schema.

1. In SAP HANA Cloud Central open a SQL console by selecting SQL Console in the left pane. Notice that the SQL console is not associated with a database when opened in this way.

   

   Additional SQL consoles can also be opened by selecting the + icon.

2. This time select Instances, select a database, and choose Open SQL Console from the actions menu.

   

   Notice that it is now connected to the instance named HC_HDB as shown by the name of the tab and the connection label or through the below SQL.

   SQL Copy

   SELECT CURRENT_USER FROM DUMMY;

   

3. There are several ways to connect to a database as a different user:

   • From the Instances list: Click the three dots next to the instance and select Sign in with Different User. Enter the username and password of the desired user after clicking the “Sign in with a different user” option in the confirmation dialog.

     

   • From the SQL console Connect menu: Select the Connect to link, click the Connect … button, and provide the new username and password.

     

     The Enter Credentials dialog will then ask for the new credentials to be used.

     

   • Using SQL: Run the CONNECT statement to switch users within the current console session.

     SQL Copy

     CONNECT USER1 PASSWORD Password1;
     SELECT CURRENT_USER FROM DUMMY;

     

     The SQL to view the current user for a data lake Relational Engine is shown below.

     SQL Copy

     SELECT CURRENT USER;

     

   The Connect menu and SQL CONNECT statement are session-scoped — after a disconnect and reconnect, the console reverts to the credentials it was originally opened with.

4. If you need to alter the credentials for a database user, there are two ways to do this:

   • Using the SQL console: Run the following statement, replacing the placeholders with the target username and new password.

     SQL Copy

     ALTER USER <user_name> PASSWORD <new_password>;

   • Using the SAP HANA Cloud Central UI: Open the instance, go to the Overview tab, and scroll down to User and Authorization Management. Select Users, click on the user you want to update, and choose Edit to make your changes.

   

5. The current schema can be set and viewed for a SAP HANA database using the SQL statements below or by clicking on the Current Schema link.

   SQL Copy

   SET SCHEMA HOTELS;
   SELECT CURRENT_SCHEMA, CURRENT_USER FROM DUMMY;

   

   For data lake Relational Engine instances, the current schema can be set and viewed as shown below.

   SQL Copy

   SET SCHEMA HOTELS;
   SELECT CURRENT SCHEMA, CURRENT USER;

   

This step demonstrates how to execute a SQL query, examine the statement help, view the query results, and history tab within a SQL console.

1. Execute the following SQL statements.

   SQL Copy

   SELECT * FROM HOTELS.CUSTOMER;
   SELECT * FROM NON_EXISTENT_TABLE;
   SELECT * FROM NON_EXISTENT_TABLE2;
   SELECT * FROM HOTELS.HOTEL_ROOMS_VIEW;

   The following error dialog appears. Select Skip All.

   

   Notice that there is an error marker beside the lines that could not be executed. Pressing Alt+E will display further details and advances to the next error when pressed more than once.

   

   Notice that two result tabs are shown, one for each SQL statement that generated a result.

2. Results can be downloaded as shown below.

   

   Options are provided on how to format the data.

   

   Rows can also be selected and then copied to the clipboard by pressing Ctrl+C.

   

3. Place the cursor on line four and open the Syntax Help panel.

   

   Notice that for SAP HANA Cloud, SAP HANA databases, links to the related documentation and details on the objects used in the SQL statement are shown including a link which will open the database objects app where additional details of the object can be viewed.

5. The SQL console provides the ability to search across all open tabs. Navigate to the three dots menu in the SQL console and select Search Through Tabs to search for text across all open SQL console tabs.

   

6. Commonly used shortcut keys are listed below. Try a few of them out.

   Action	Shortcut
   Add Comment Block	Ctrl+Shift+/
   Comment/Uncomment Line	Ctrl+/
   To Uppercase	Ctrl+U
   To Lowercase	Ctrl+Shift+U
   Go to Next Error	Alt+E
   Go to Previous Error	Alt+Shift+E
   Go to Line	Ctrl+L
   Jump to Matching Brackets	Ctrl+P
   Run All	F8
   Run Statement	F9
   Text Completion	Ctrl+Space
   Save to statement library	Ctrl+S
   Move Tab Left	Ctrl+Shift+< (less than sign)
   Move Tab Right	Ctrl+Shift+> (greater than sign)

   The shortcut keys may vary depending on the OS and browser used.

   For the complete list see Common Keyboard Shortcuts for the SQL Console.

7. Examine the History tab.

   

   Notice details for each executed statement are displayed, including metrics such as memory consumption. Statements can be quickly located using the search functionality. Selected statements may be inserted back into the SQL console or copied for further use.

   The columns can also be changed by selecting the gear icon in the right corner.

   

8. Examine the SQL Console Settings.

   

   • Execute the following SQL which is used to illustrate the result behavior settings.

     SQL Copy

     SELECT * FROM M_SYSTEM_INFORMATION_STATEMENTS;
     
     SELECT COUNT(*) FROM SYS.TABLE_COLUMNS;
     SELECT * FROM TABLE_COLUMNS;

     Double click on a cell to open the viewer. Notice that only the first 1024 bytes from the column STATEMENT are displayed in the results view for the Blocked Transactions row.

     

     Notice that over 6000 rows are in TABLE_COLUMNS.

     

     The first 1000 are displayed.

     

     These limits can be adjusted in the result behavior settings shown below.

     

9. Execute the following SQL which is used to illustrate the result display settings.

    ```SQL
    SELECT CURRENT_DATE, CURRENT_TIMESTAMP(7), RAND() * 10 FROM DUMMY;
    ```
   
    The below is the result when this setting is disabled.
   
    ![no formatting](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/no-formatting.png)
   
    Enable Result Format Settings in the settings.
   
    ![format settings](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/result-format-settings.png)
   
    The below is the result when this setting is enabled.
   
    ![result formatted](https://raw.githubusercontent.com/sap-tutorials/Tutorials/master/tutorials/hana-cloud-sqltools-console/formatted.png)
   

10. The overall theme for SAP HANA Cloud Central can be set in the settings which also affects the theme of the SQL console.

    

11. Execute the following SQL statements.

    SQL Copy

    SELECT HEXTOBIN ('48656C6C6F20776F726C64') BINARY_EXAMPLE FROM DUMMY;
    
    SELECT '{
        "name":"John",
        "age":30,
        "cars": {
            "car1":"Ford",
            "car2":"BMW",
            "car3":"Fiat"
        }
        }' AS JSON_EXAMPLE
        FROM DUMMY;
    
    SELECT '<?xml version="1.0" encoding="UTF-8"?>
        <breakfast_menu>
        <food>
            <name>Strawberry Belgian Waffles</name>
            <price>$7.95</price>
            <description>
            Light Belgian waffles covered with strawberries and whipped cream
            </description>
            <calories>900</calories>
        </food>
        <food>
            <name>French Toast</name>
            <price>$4.50</price>
            <description>
            Thick slices made from our homemade sourdough bread
            </description>
            <calories>600</calories>
        </food>
        <food>
            <name>Homestyle Breakfast</name>
            <price>$6.95</price>
            <description>
            Two eggs, bacon or sausage, toast, and our ever-popular hash browns
            </description>
            <calories>950</calories>
        </food>
        </breakfast_menu>' XML_EXAMPLE FROM DUMMY

    Show all 40 lines Show less

    Double tapping on a result will open a result viewer.

    

12. The Auto-save contents of SQL consoles setting can be used to persist the open SQL console tabs and their contents. This setting is on by default.

    

13. SQL statements can also be executed in the background. This allows users to start a long-running statement, disconnect, and return later to view the results.

    Execute the following statement by clicking the Run dropdown menu and selecting Run Statement in Background:

    SQL Copy

    DO BEGIN
    --Wait for a few SECONDS
        USING SQLSCRIPT_SYNC AS SYNCLIB;
        CALL SYNCLIB:SLEEP_SECONDS(3); --runs for longer than 3 SECONDS
        --Now execute a query
        SELECT * FROM M_TABLES;
    END

    

    The results will appear in the Background Statements tab at the bottom on the console.

14. The actions menu for a SQL console tab offers additional options such as the ability to rename a tab, to move a tab, to open a previously closed tab, or to search through the content of all open SQL console tabs.

    

The statement library is a convenient location in the SQL console to store and retrieve frequently executed SQL statements. It provides a place to store statements that are used frequently as to not type them in repeatedly.

The library is pre-populated with useful statements called ‘SYSTEM’ statements.

You may also define custom statements that are only available to you. These are ‘USER’ statements.

/*

[DESCRIPTION]

- Future guest check-ins across all hotels

*/

SELECT * FROM RESERVATION
    WHERE ARRIVAL >= CURRENT_DATE;

1. Save your custom statement, add a name and an optional description.

   

   Set the name to All Future Check-ins.

   

2. View your custom statement in the statement library

   

3. To run a statement, select one from the statement library and click the Run button.

   

   If you select multiple saved statements, you additionally have the option to open them together in one tab or in individual tabs.

It is also possible to export and import SQL statements directly to/from the file system

Files can then be shared using a version control system such as git.

User-defined statements can be edited. From the Statement library, select the desired statement, click open, and make the desired changes. Select Save to overwrite the previous statement.

The SQL console also provides GenAI tools that assist with creating, understanding, and optimizing SQL statements. To enable GenAI in the SQL console, refer to the following Using Gen AI in SAP HANA Cloud Central.

1. By selecting SQL text and clicking Generate SQL From Natural Language from the Run dropdown menu, users can generate SQL statements from natural-language instructions, and also provides an explanation. Execute the following prompt.

   Code Copy

   List all the column store tables and their columns in the schema HOTELS

   

2. To understand an existing query, select the SQL statement and click Explain SQL Statement using AI from the same menu. This returns a clear natural-language explanation of what the query does.

   SQL Copy

   SELECT
       t.SCHEMA_NAME,
       t.TABLE_NAME,
       c.COLUMN_NAME
   FROM
       SYS.TABLES AS t
   JOIN
       SYS.TABLE_COLUMNS AS c
       ON t.SCHEMA_NAME = c.SCHEMA_NAME
       AND t.TABLE_NAME = c.TABLE_NAME
   ORDER BY
       t.SCHEMA_NAME,
       t.TABLE_NAME,
       c.POSITION;

   

3. To improve a query, select the SQL statement and click Optimize SQL Statement using AI. The system will suggest a refined version of the query, often improving readability and performance.

   SQL Copy

   SELECT 
       TABLES.SCHEMA_NAME,
       TABLES.TABLE_NAME,
       TABLE_COLUMNS.COLUMN_NAME
   FROM 
       TABLE_COLUMNS
       INNER JOIN
       TABLES
       ON TABLE_COLUMNS.TABLE_NAME = TABLES.TABLE_NAME;

   

Congratulations! You have now used the SQL console in SAP HANA Cloud Central and have become familiar with some of the features it provides.