After completing the provisioning of your standalone data lake in SAP HANA Cloud, you can access the data lake and start storing, querying and manipulating your data. In this tutorial, you will learn some of the ways you can do that, focusing mostly on how to access and interact with your standalone data lake using SAP HANA database explorer.
You can also learn how to access the same instance using SAP Interactive SQL (dbisql) and isql, in case you are familiar with those tools. Before you access the data lake instance, it is important to understand the function of users, roles and privileges while interacting with your instance.
Step 1Introduction to users, roles, and privileges
—
SAP HANA Cloud, data lake defines user permissions and privileges using a role-based security model. A role is a set of privileges that can be assigned to a user. As a user, you can grant or revoke these roles or privileges. This means that your privileges as a user change depending on the change in the privileges assigned to your role.
When you create a new data lake instance using the default user HDLADMIN, you are automatically granted all system and object-level privileges in the data lake. You can also create a new user, thereby granting the user with the public system role automatically. This role provides access to view data, execute system stored procedures, and grant roles and privileges to the user.
When you create a new object, you become the default owner of the object unless you define otherwise. This gives you privileges to modify the structure of the table and grant further privileges to other data lake users. Although, you cannot load data into a table based on being the owner, unless you assign INSERT privileges to the role.
To learn more about users, roles and privileges in SAP HANA Cloud, data lake, please see our technical documentation.
Step 2Confirm your data lake is running
+
Ensure that your standalone data lake instance is currently running before trying to connect to it using SAP HANA database explorer.
To do this, navigate to the SAP HANA Cloud Central in the subaccount level in SAP BTP cockpit.
Locate your standalone data lake instance details by selecting to view all instances.
If your instance is showing any status other than RUNNING, click on the three dots button assigned to the instance for more options.
Now, select the option to Start the instance and wait until it has the status RUNNING.
DL Running
Step 3Connection options
+
You can use different tools to connect to a standalone data lake in SAP HANA Cloud. In this tutorial, we will show you four different options you can choose from:
Option 3: Interactive SQL command line interface (CLI)
Option 4: isql
In the next steps, you can learn how to use each of these connection methods. Once you have decided which option you would like to use to connect, following the other steps is not mandatory.
Step 4Connect to data lake
+
For this step, select your preferred connection method by clicking on the options under the step title.
Options Screenshot
After confirming the RUNNING status, click on the three dots under the Actions column and select the option to Open SQL Console. This will take you to the SQL Console tab and open a console connected to your data lake.
You can also navigate to the same destination by selecting the option to Open SAP HANA Database Explorer, then opening a SQL console.
Look for the name of your standalone data lake from the top-right corner of the screen.
You can confirm the connection to your standalone data lake from the connection status provided.
Confirm connection
Connecting to a data lake instance with Interactive SQL is another option besides using the SAP HANA database explorer. If you want to connect using Interactive SQL (dbisql), you need to have it installed on your local system. You can download the Interactive SQL using the link provided in the technical documentation.
There are two ways to connecting to an instance through an Interactive SQL- from the graphical user interface or from the command line.
These are the steps towards connecting to your instance through the graphical user interface of Interactive SQL:
Launch the SAP Interactive SQL in your system. The Connect dialog box should appear as shown below.
Open dbisql
Select Change database type and choose SAP HANA Cloud Data Lake.
Change Database type
After selecting the database type, ensure that the encryption parameters placed under the security tab have been pre-filled, as shown below.
Check security
Enter the credentials for your data lake instance under the Identification section. For example, in the User ID field, enter HDLADMIN and then enter your password that you set while creating your instance.
User credentials
Next, you must enter your Instance ID and Landscape. These details can be found in the SAP HANA Cloud Central wizard of your instance in the SAP HANA Cloud.
Open the SAP HANA Cloud Central wizard and locate your standalone data lake from the list of all instances. Select the three dots under the Actions column for your instance and choose Copy Instance ID to copy the Instance ID to the clipboard. Then, paste the Instance ID into the Instance ID field in the Interactive SQL.
Copy Instance ID
Switch back to the SAP HANA Cloud Central window. Again, select Actions to choose Copy SQL Endpoint. Paste the SQL Endpoint in a notepad file for viewing. The SQL Endpoint is composed of the Instance ID, Landscape and Port.
Paste the information on the Landscape that you isolated from the SQL Endpoint into the Interactive SQL, and click Connect.
Instance ID and Landscape
Now, you should be connected to your data lake instance thorough Interactive SQL, as seen in the below image.
Connected to Instance
If you would like to connect to your data lake instance through an Interactive SQL from the command line, please follow the instructions given below.
Connecting to a data lake instance through an Interactive SQL from the command line requires a Connection String. To extract the connection string, ensure all fields on the Identification tab are filled in. Select Tools, then choose Copy Connection String to Clipboard.
Copy Connection string
After pasting the connection string in a notepad file, you can the distinguish the parts as seen below. The password field must be filled using the password for your data lake instance before using this string to connect from the command line. The connecting string consists of the port number at the end of Landscape information and is followed by the encryption parameters.
Connection string
Now, open a Command Prompt window in your system. To begin an Interactive SQL session, execute the following command given below.
Shell
dbisql -c <CONNECTION_STRING>
**For example**, paste the connection string as shown below.
An Interactive SQL window should show up on your screen and you are now connected to your data lake instance, as seen below.
Call dbisql
You can also setup the connection without opening the graphical user interface of the Interactive SQL, for which you must simply add ’-nogui’ at the end of your connection string.
Your User ID will be displayed after the execution of the above command which indicates that a connection to your data lake instance has successfully been made.
Attention: The following instructions apply to you if you’re using isql from the on-premise SAP IQ 16.1 version. Using any other open-source isql versions may lead to errors. Therefore, it’s highly recommended to use the same version to avoid any errors.
Set up an open client connection to your database. Open a Linux instance and use the superuser account. For this example, your $SYBASE environment variable points to /opt/sap as your root folder.
To use isql, you need to have an interfaces file, which you can create in the vim editor. Follow these steps to create the interfaces file:
Navigate to the root folder using command: /opt/sap/OCS-16_0/bin
Root folder
Enter the vim editor as the superuser account
a. sudo vim interfaces
b. enter password
Connected to Root folder
Once you are in the vim editor, you need the SQL endpoint of your data lake instance. Retrieve it from the SAP HANA Cloud Central as shown earlier.
Enter your respective credentials using the following format:
>Note: There must be a tab used at the beginning of the second line, prior to query. Using spaces will not work.
SQL Endpoint query
Once you have entered your details, save the interfaces file, and exit the vim editor.
Before connecting to a database, run the command source SYBASE.sh
The successful execution of this command can be verified by running the command which isql
Verify SQL query
If the editor shows location of the isql, this indicates that now you can start an isql session.
Start an isql session and connect to your database by running the following command and entering your credentials:
Shell
isql -U <username> -S <Instance_Name> -I<path to interfaces file>
You will be prompted to enter your password.
Enter Password
When the editor shows 1>, you are connected to your database and may run queries.
For Example, Test query: select @@ version
Test query
If you encounter the following error while trying to start an isql session:
Troubleshoot
Run the following command before trying again:
Shell
unset LANG
Attention: This error was observed while using Ubuntu on a virtual Linux machine. This error will not occur when supported programs are used.
In this tutorial, you have learned how to access your standalone data lake in SAP HANA Cloud. In the next tutorial, you will see how to load data into your standalone data lake.
Joule is an AI assistant. Generative AI may produce inaccurate, incomplete, or biased information. Always verify important details before acting on them.
Conversations are sent to SAP-hosted large language models for processing. Do not include personal data, credentials, or confidential information in your messages.
Joule's responses are based on the SAP tutorial catalog and may not reflect the latest product changes. For authoritative guidance, consult the linked tutorials and official SAP documentation.
