Access a Standalone Data Lake in SAP HANA Cloud

After confirming the status, click on the three dots again and select the option to Open SQL Console in the SAP HANA Database Explorer.

You can also navigate to the same destination by selecting the option to open SAP HANA Database Explorer.

1. Look for the name of your standalone data lake from the database menu located on the left side in the database explorer.

2. Click on the icon in the top-left corner to open a new SQL Console.

3. You can confirm the connection to your standalone data lake from the connection status provided on the menu in the SQL Console.

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:

1. Launch the SAP Interactive SQL in your system. The Connect dialog box should appear as shown below.

   

2. Select Change database type and choose SAP HANA Cloud Data Lake.

   

3. After selecting the database type, ensure that the encryption parameters placed under the security tab have been pre-filled, as shown below.

   

4. 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.

   

   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.

5. 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.

   

6. 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.

7. Paste the information on the Landscape that you isolated from the SQL Endpoint into the Interactive SQL, and click Connect.

   

8. Now, you should be connected to your data lake instance thorough Interactive SQL, as seen in the below image.

   

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.

1. 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.

   

2. 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.

   

3. Now, open a Command Prompt window in your system. To begin an Interactive SQL session, execute the following command given below.

   dbisql -c <CONNECTION_STRING>
   

   For example, paste the connection string as shown below.

   dbisql -c "UID=HDLADMIN;PWD=SamplePassword;host=a111111a-1a11-11aa-a11a-1a1a11a1a111.iq.hdl.test.hanacloud.ondemand.com:443;ENC=TLS(tls_type=rsa;direct=yes)"
   

   

4. An Interactive SQL window should show up on your screen and you are now connected to your data lake instance, as seen below.

   

5. 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.

   For example, the command will look like this:

   dbisql -c "UID=HDLADMIN;PWD=SamplePassword;host=a111111a-1a11-11aa-a11a-1a1a11a1a111.iq.hdl.test.hanacloud.ondemand.com:443;ENC=TLS(tls_type=rsa;direct=yes)" -nogui
   

   

6. 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:

1. Navigate to the root folder using command: /opt/sap/OCS-16_0/bin

   

2. Enter the vim editor as the superuser account

   a. sudo vim interfaces

   b. enter password

   

3. 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.

4. Enter your respective credentials using the following format:

   <Instance_Name>
   
   query tcp ether <InstanceID>.<Landscape> <Port> ssl="CN=hanacloud.ondemand.com"
   

   For example, as shown below:

   DEMO_INSTANCE
   
   query tcp ether a111111a-1a11-11aa-a11a-1a1a11a1a111.iq.hdl.beta-us21.hanacloud.ondemand.com 443 ssl="CN=hanacloud.ondemand.com"
   

   Note: there must be a tab used at the beginning of the second line, prior to query. Using spaces will not work.

   

5. Once you have entered your details, save the interfaces file, and exit the vim editor.

6. Before connecting to a database, run the command source SYBASE.sh

7. The successful execution of this command can be verified by running the command which isql

   

8. If the editor shows location of the isql, this indicates that now you can start an isql session.

9. Start an isql session and connect to your database by running the following command and entering your credentials:

   isql -U <username> -S <Instance_Name> -I<path to interfaces file>
   

10. You will be prompted to enter your password.

11. When the editor shows 1>, you are connected to your database and may run queries.

    For Example, Test query: select @@ version

    

12. If you encounter the following error while trying to start an isql session:

    

    Run the following command before trying again:

    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.