Skip to content

Commit 5e2742a

Browse files
danielvadanielva
committed
New tutorial on SQL Console in HCC
1 parent a81c4d2 commit 5e2742a

22 files changed

Lines changed: 166 additions & 24 deletions

‎tutorials/hana-clients-node/hana-clients-node.md‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -100,7 +100,7 @@ Node.js packages are available using [NPM](https://www.npmjs.com/), which is the
100100
npm uninstall @sap/hana-client
101101
npm install @sap/hana-client
102102
>```
103-
103+
104104
>```Shell (Linux or Mac)
105105
export HDB_NODE_PLATFORM_CLEAN=1
106106
npm uninstall @sap/hana-client
@@ -128,7 +128,7 @@ Node.js packages are available using [NPM](https://www.npmjs.com/), which is the
128128
129129
![npm list](npm-list.png)
130130
131-
> ### Some Tips
131+
> Some Tips
132132
133133
>At this point, the SAP HANA client module has been installed into the `HANAClientsTutorials\node\node_modules` folder and added as a dependency in the `packages.json` file. The following is some extra optional information on NPM.
134134

‎tutorials/hana-dbx-create-schema/hana-dbx-create-schema.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -616,7 +616,7 @@ Another option for data that is accessed less frequently is the SAP HANA Data La
616616
END;
617617
```
618618

619-
Details on SQL Error codes such as 301 ERR_SQL_UNIQUE_VIOLATED can be found at ![SAP HANA Cloud, SAP HANA Database SQL Reference Guide](https://help.sap.com/docs/HANA_CLOUD_DATABASE/c1d3f60099654ecfb3fe36ac93c121bb/20a78d3275191014b41bae7c4a46d835.html).
619+
Details on SQL Error codes such as 301 ERR_SQL_UNIQUE_VIOLATED can be found at [SAP HANA Cloud, SAP HANA Database SQL Reference Guide](https://help.sap.com/docs/HANA_CLOUD_DATABASE/c1d3f60099654ecfb3fe36ac93c121bb/20a78d3275191014b41bae7c4a46d835.html).
620620

621621
5. To run this procedure, execute the SQL statement below.
622622

‎tutorials/hana-dbx-extension/hana-dbx-extension.md‎

Lines changed: 10 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -27,13 +27,13 @@ primary_tag: software-product>sap-hana-cloud
2727

2828
![Download Visual Studio Code](downloadVSCode.png)
2929

30-
2. Open Visual Studio Code and install the [SAP HANA database explorer Visual Studio Code extension](https://marketplace.visualstudio.com/items?itemName=SAPSE.hana-database-explorer) from the Extensions Marketplace.
30+
2. Open Visual Studio Code and install the [SAP HANA database explorer Visual Studio Code extension](https://marketplace.visualstudio.com/items?itemName=SAPSE.hana-database-explorer) from [https://marketplace.visualstudio.com/](Visual Studio Marketplace) by navigating to **Extensions** and searching for **SAP HANA Database Explorer**.
3131

3232
>For more information about the latest features and version updates to the extension, you can refer to the **CHANGELOG** tab.
3333
3434
![Install extension](installExtension.png)
3535

36-
3. Open the extension from the new icon in the activity bar (left) and navigate to the Database List.
36+
3. Open the extension from the new icon in the activity bar (left) and navigate to the **Database List**.
3737

3838
![Open extension](openExtension.png)
3939

@@ -49,7 +49,7 @@ primary_tag: software-product>sap-hana-cloud
4949

5050
The SAP HANA database explorer extension can connect to SAP HANA Cloud and on-premise databases as well as an SAP HANA User Store. In this tutorial, a connection to an SAP HANA Cloud database will be made, but the steps to connect to the other types are very similar. Adding local connections do not require authentication to the SAP Business Technology Platform (BTP) or Cloud Foundry.
5151

52-
1. Hover over the Database List section and click the **+** button to **Add SAP HANA Database**.
52+
1. Hover over the **Database List** section and click the **+** button to **Add SAP HANA Database**.
5353

5454
![Add Database](addDatabase.png)
5555

@@ -63,7 +63,7 @@ The SAP HANA database explorer extension can connect to SAP HANA Cloud and on-pr
6363
currentSchema=HOTEL;
6464
```
6565
66-
>Ensure that you connect the database securely using TLS/SSL by ticking the checkbox. If you do not check the Save Password checkbox, you will have to enter your password each time you open a new SQL Console.
66+
>Ensure that you connect the database securely using TLS/SSL by ticking the checkbox. If you do not check the Save Password checkbox, you will have to enter your password each time you start using the extension.
6767
6868
![SAP HANA Cloud Connection](cloudConnection.png)
6969
@@ -98,7 +98,7 @@ The SAP HANA database explorer extension also allows you to connect to the datab
9898
cf login -help
9999
```
100100
101-
Ensure that your API Endpoint is correct. Navigate to SAP BTP Cockpit, click on your sub-account, and click on the Cloud Foundry Environment tab, where your API Endpoint will be listed.
101+
Ensure that the **API Endpoint** is correct. Navigate to SAP BTP Cockpit, click on your sub-account, and click on the Cloud Foundry Environment tab, where your API Endpoint will be listed.
102102
103103
![API Endpoint](cloudFoundryEnvironment.png)
104104
@@ -127,7 +127,7 @@ The SAP HANA database explorer extension also allows you to connect to the datab
127127
128128
### Using the Database List and Catalog Browser
129129
130-
1. Use the Database List to open the list of schemas in the catalog browser by expanding your database instance, expanding the catalog, and then clicking on Schemas.
130+
1. Select **Schemas** to open the list of schemas in the Catalog Browser.
131131
132132
As you can see, since the current Schema was set to HOTEL using advanced options when adding the database, this list is already filtered for you. By hovering or clicking on the Catalog Browser section, you will see a filter icon and a refresh icon.
133133
@@ -146,7 +146,7 @@ The SAP HANA database explorer extension also allows you to connect to the datab
146146
147147
Learn how to change settings and leverage features such as split editor, layout toggles, and the terminal.
148148
149-
1. If you would like to change the color theme of Visual Studio Code, one way to do that is through **File > Preferences > Themes > Color Theme**. From here, you can select from the existing light and dark color themes that are available as well as the additional ones you may have installed from the extension marketplace.
149+
1. If you would like to change the color theme of Visual Studio Code, one way to do that is through **File > Preferences > Themes > Color Theme**. From here, you can select from a light or dark color theme.
150150
151151
![change Theme](changeTheme.png)
152152
@@ -206,7 +206,7 @@ Learn how to change settings and leverage features such as split editor, layout
206206
207207
![Create Notepad File](notepadTerminal.png)
208208
209-
Paste the following code into the file and save it as a SQL file by adding **.sql** to the file name. This function will return the average rating of a specific destination from the TOURIST_REVIEWS table.
209+
Paste the following code into the file and save it as a SQL file by adding `.sql` to the file name. This function will return the average rating of a specific destination from the TOURIST_REVIEWS table.
210210
211211
```SQL
212212
CREATE OR REPLACE FUNCTION AVERAGE_RATING(destination_id INT)
@@ -226,15 +226,12 @@ The extension also allows you to import and export SQL files to and from your co
226226
227227
![Import File](openSQLFile.png)
228228
229-
2. Edit the code in the editor by changing the INT in line 2 to FLOAT to change the function to return a more precise average.
229+
3. Edit the SQL in the editor by changing the INT in line 2 to FLOAT to change the function to return a more precise average.
230230
231231
![Make Changes and Save](saveFileButton.png)
232232
233233
Click the save icon to save your changes to the SQL file on your computer.
234234
235-
236-
237-
238235
### Run SQL code
239236
240237
1. Open a new SQL Console and use the newly created function with the following code. Click on the dropdown arrow beside Run to view the different Run options. Then, normally **Run** your code.
@@ -269,7 +266,7 @@ The extension also allows you to import and export SQL files to and from your co
269266
> * Global Search
270267
> * Debugging SQLScript
271268
> * Graph Views
272-
> * Viewing Diagnostic Files in the Catalog Browser
269+
> * Viewing diagnostic files in the Catalog Browser
273270
274271
### Knowledge check
275272

‎tutorials/hana-dbx-extension/openExtension.png‎

-8 KB
Loading

‎tutorials/hana-dbx-hcc/change-credenitals2.png‎

15.6 KB
Loading

‎tutorials/hana-dbx-hcc/change-credentials-sql.png‎

41.4 KB
Loading

‎tutorials/hana-dbx-hcc/change-credentials.png‎

43.9 KB
Loading

‎tutorials/hana-dbx-hcc/hana-dbx-hcc.md‎

Lines changed: 141 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,141 @@
1+
---
2+
parser: v2
3+
auto_validation: true
4+
time: 10
5+
tags: [tutorial>beginner, software-product-function>sap-hana-cloud--sap-hana-database, software-product-function>sap-hana-cloud--data-lake]
6+
primary_tag: software-product>sap-hana-cloud
7+
---
8+
9+
# Query databases using the SQL Console in SAP HANA Cloud Central
10+
<!-- description --> Learn how the SQL Console can be used within SAP HANA Cloud Central to quickly query a selected database.
11+
12+
## Prerequisites
13+
- An SAP HANA Cloud database
14+
- You have completed [this](hana-dbx-create-schema) tutorial which creates a database schema for an SAP HANA Cloud, SAP HANA database.
15+
- You have completed [this](hana-cloud-dl-clients-overview) tutorial which creates a database schema for an SAP HANA Cloud, data lake Relational Engine
16+
17+
## You will learn
18+
- How to open a SQL Console, specify the credentials, and set the current schema
19+
- An overview of the functionality provided in the SQL Console
20+
21+
---
22+
23+
### Open a SQL Console
24+
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.
25+
26+
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.
27+
28+
![open SQL Console](open-sql-console.png)
29+
30+
>Additional SQL Consoles can also be opened by selecting the **+** icon.
31+
32+
2. This time select **Instances**, select a database, and choose **Open SQL Console** from the actions menu.
33+
34+
![open SQL Console from an instance](open-sql-console-instance.png)
35+
36+
Notice that it is now connected to the instance named `HC_HDB`.
37+
38+
![SQL Console connected to HC_HDB](sql-console-connected.png)
39+
40+
>The currently connected user can be seen by executing the SQL statement below.
41+
42+
>```SQL
43+
>SELECT CURRENT_USER FROM DUMMY;
44+
>```
45+
46+
3. If you wish to connect to the database using a different set of credentials, select the **Connect this SQL console to a different instance** icon, select the current database and uncheck **Use cached credentials if possible**.
47+
48+
![Change credentials](change-credentials.png)
49+
50+
The Enter Credentials dialog will then ask for the new credentials to be used.
51+
52+
![change credentials](change-credenitals2.png)
53+
54+
You can also use the following SQL to view and change the connected user.
55+
56+
```SQL
57+
CONNECT USER1 PASSWORD Password1;
58+
SELECT CURRENT_USER FROM DUMMY;
59+
```
60+
61+
![Change credentials using SQL](change-credentials-sql.png)
62+
63+
4. The current schema can be set and viewed using the SQL statements below.
64+
65+
```SQL
66+
SET SCHEMA HOTEL;
67+
SELECT CURRENT_SCHEMA FROM DUMMY;
68+
```
69+
70+
![Set the schema](set-schema.png)
71+
72+
For data lake Relational Engine instances, the current schema can be set and viewed as shown below.
73+
74+
```SQL
75+
SET SCHEMA HOTEL;
76+
SELECT CURRENT SCHEMA;
77+
```
78+
79+
![set the schema of a data lake RE](set-schema-dl.png)
80+
81+
82+
### Execute SQL
83+
This step demonstrates how to execute a SQL query and view query results, messages, and history within a SQL Console.
84+
85+
1. Execute the following SQL statements.
86+
87+
```SQL
88+
SELECT * FROM CUSTOMER;
89+
SELECT * FROM HOTEL.HOTEL_ROOMS_VIEW;
90+
SELECT * FROM NON_EXISTENT_TABLE;
91+
```
92+
93+
![SQL results](sql-results.png)
94+
95+
Notice that two result tabs are shown, one for each SQL statement.
96+
97+
2. Place the cursor on line two and open the **Statement Help** panel.
98+
99+
![statement help panel](statement-help.png)
100+
101+
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.
102+
103+
3. Examine the **Messages** tab.
104+
105+
![messages tab](messages-tab.png)
106+
107+
Notice that details of the statements executed are shown including metrics information such as the amount of memory consumed.
108+
109+
4. Examine the **History** tab.
110+
111+
![history view](history.png)
112+
113+
Notice that the statements can be located using a search and that a selected item can be inserted back into the SQL Console or copied.
114+
115+
>The history is not persisted across browser reloads.
116+
117+
### A few things to note
118+
The SQL Console within SAP HANA Cloud Central appears similar to the one within the SAP HANA database explorer but there are some differences.
119+
120+
* Opening the SQL Console within the SAP HANA Cloud Central can be done much quicker than opening the full SAP HANA database explorer.
121+
122+
* The SQL Console that you access from within SAP HANA Cloud Central can only connect to databases that are within the same BTP subaccount as SAP HANA Cloud Central.
123+
124+
* The SAP HANA database explorer has some additional functionality
125+
126+
* Persistency of SQL tabs and their contents
127+
* SQL debugging
128+
* SQL parsing to detect potential errors
129+
* Code completion of schema objects
130+
* SQL formatting
131+
* SQL viewers for HTML, XML, JSON, binary, and spatial
132+
* Results can be downloaded in a CSV
133+
* Ability to run statements in the background
134+
* Ability to run statements against multiple instances
135+
* Generate SQL Analyzer file or explain plan
136+
* Statement library
137+
* SQL Console settings
138+
139+
### Knowledge check
140+
141+
Congratulations! You have now used the SQL Console in SAP HANA Cloud Central and have become familiar with some of the features it provides.

‎tutorials/hana-dbx-hcc/history.png‎

70.1 KB
Loading

‎tutorials/hana-dbx-hcc/messages-tab.png‎

48.8 KB
Loading

0 commit comments

Comments
 (0)