Skip to Content

Create a User, Tables and Import Data Using SAP HANA HDBSQL

test
0 %
Create a User, Tables and Import Data Using SAP HANA HDBSQL
Details

Create a User, Tables and Import Data Using SAP HANA HDBSQL

July 9, 2020
Created by
March 29, 2020
Use the command line tool HDBSQL to connect to a SAP HANA database, create a user, and create tables which will be used in subsequent tutorials in this mission.

You will learn

  • How to connect to SAP HANA from a client machine using HDBSQL
  • How to create a user, schema, tables and import data

HDBSQL is used in this tutorial as it is part of the SAP HANA client install. HDBSQL is a very basic tool for executing SQL scripts and providing an interface for interactive queries. Another more user-friendly option to execute SQL operations is the SAP HANA database explorer which is part of a HANA Cloud instance or a server + applications SAP HANA, express edition install.


Step 1: Connect to SAP HANA using hdbsql

This section demonstrates how to connect to a SAP HANA instance using HDBSQL from the SAP HANA client installation.

  1. Copy and paste the following command to see the command line options and press the space bar to advance though the information.

    hdbsql -h | more
    
  2. Connect to either SAP HANA Cloud or SAP HANA, express edition using host, port, and credentials. As mentioned previously, the host and port can be found in the SAP Cloud Platform cockpit.

    • To connect to SAP HANA Cloud, see the following general command.

      Note: the connection must be encrypted (-e).

      hdbsql -e -n <host name>:<port> -u <user> -p <password>
      

      The following is a connection example for the SAP HANA Cloud.

      hdbsql -e -n 61964be8-39e8-4622-9a2b-ba3a38be2f75.hana.canary-eu10.hanacloud.ondemand.com:443 -u DBADMIN -p your_password
      

      The HANA Cloud instance can be configured to enable applications running from outside the SAP Cloud Platform to connect. The current setting is shown in the screenshot below.

      screenshot showing the whitelist

      If you are on a Linux or Mac machine and the hdbsql connection fails with the error message below, it indicates that the client could not locate a trust store in the default location.

      Cannot create SSL context: SSL trust store cannot be found: /Users/user1/.ssl/trust.pem

      A public root certificate to validate the server’s certificate is needed. More information about the required DigiCert root certificate can be found at Secure Communication Between SAP HANA Cloud and JDBC/ODBC Clients.

      It can be downloaded from Download PEM, renamed to trust.pem and saved to the specified location. For further details, see Secure connection from HDBSQL to SAP HANA Cloud.

    • For SAP HANA, express edition, there are two options shown below to connect.

      hdbsql -n <ip address>:39015 -u <user> -p <password>
      or
      hdbsql -n <ip address> -d <database name> -i <instance number> -u <user> -p <password>
      

      Below are two connection examples for SAP HANA, express edition.

      hdbsql -n <ip address>:39015 -u SYSTEM -p your_password
      or
      hdbsql -n <ip address> -d HXE -i 90 -u SYSTEM -p your_password
      

      Notice that either the host and port combination or the host, database name and instance number can be used to connect.

  3. Type \s for status information

    \s
    

    Notice that for the SAP HANA Cloud instance, the version number is 4.x.

    Connect to HANA Cloud

    Notice that for the HANA, express edition, the version number is 2.x.

    Connect to HXE
Log on to answer question
Step 2: Create user and schema

In this section, create a user named USER1. USER1 will be the owner of the tables that will be created in a subsequent section and will be used to connect to the database.

On Linux or a Mac, turn off page by page scroll output. Also consult the -j hdbsql option. This enables multiple commands to be pasted at one time and does not require each result to be exited by pressing q.

\pa on
  1. Create USER1. Note that the message ‘0 rows affected’ means that query was successful.

    CREATE USER USER1 PASSWORD Password1 no force_first_password_change SET USERGROUP DEFAULT;
    

    The SET USERGROUP command is only required for SAP HANA Cloud as the DBADMIN user does not have the privilege USER ADMIN.

    CREATE USER USER1 PASSWORD Password1 no force_first_password_change;
    

    To verify that the user was created, enter the following command.

    SELECT USER_NAME FROM USERS;
    

    Also notice that a schema with the same name is created for this user.

    SELECT SCHEMA_NAME FROM SCHEMAS;
    
  2. Create a new schema and grant USER1 the ability to create new objects in it.

    CREATE SCHEMA HOTEL;
    GRANT CREATE ANY ON SCHEMA HOTEL TO USER1;
    
  3. Connect as USER1 and notice that current user changes from DBADMIN or SYSTEM to USER1.

    SELECT CURRENT_USER, CURRENT_SCHEMA FROM DUMMY;
    CONNECT USER1 PASSWORD Password1;
    SELECT CURRENT_USER, CURRENT_SCHEMA FROM DUMMY;
    

For further information on SQL to create a user or schema, see CREATE USER Statement and CREATE SCHEMA Statement.

Log on to answer question
Step 3: Store connection details in secure user store (hdbuserstore)

Remembering and entering IP addresses, ports, user IDs and passwords can be difficult. Secure User Store offers a convenient means of storing this information and making it available to the SAP HANA client interfaces.

  1. Exit out of hdbsql and type hdbuserstore to see a list of available commands such as Set, List and Delete.

    \q
    hdbuserstore
    

    hdbuserstore is included with the install of the SAP HANA database clients.

  2. Using hdbuserstore, specify a key named USER1UserKey that has the host, port and credentials. Details on where to find these values are covered in first tutorial in this mission.

    hdbuserstore Set USER1UserKey <host name>:<port> USER1 Password1
    hdbuserstore List
    
  3. Connect with USER1 as the username.

    hdbsql -attemptencrypt -U User1UserKey
    

    Uppercase U is for specifying a key from the user store, lower case u is for user.

    When connecting using -U, a connection is not attempted until a query is issued.

Using Stored Connection Information contains details on how an application can use a hdbuserstore key.

Log on to answer question
Step 4: Create tables and import data
  1. Exit HDBSQL by entering \q.

  2. Create a folder for the exercises in this tutorial, enter that directory, and begin editing a file named hotel.sql.

    mkdir %HOMEPATH%\HANAClientsTutorial\sql
    cd %HOMEPATH%\HANAClientsTutorial\sql
    notepad hotel.sql
    

    Substitute pico below for your preferred text editor.

    mkdir -p $HOME/HANAClientsTutorial/sql
    cd $HOME/HANAClientsTutorial/sql
    pico hotel.sql
    
  3. The instructions at SAP HANA Cloud SQL Demo Data provide SQL statements to create the Hotels demo tables and populate them with data. Copy all the SQL statements, except for the first line which creates the schema hotel, into hotel.sql. The schema hotel was created in a previous step.

  4. Execute the SQL with the command below. This is an example of running commands in non-interactive mode.

    hdbsql -attemptencrypt -U USER1UserKey -I hotel.sql
    

    Confirm that 30 records have been inserted.

    hdbsql -attemptencrypt -U USER1UserKey "SELECT COUNT(*) FROM HOTEL.CITY";
    

    View the inserted data using the following query.

    hdbsql -attemptencrypt -U USER1UserKey "SELECT * FROM HOTEL.CITY";
    

Some Tips

For further information, see CREATE TABLE Statement.


Should you wish to remove the contents of a table, the table itself, a schema or a user, the following statements can be executed. Do not execute these now as USER1 and the hotel data set will be used subsequently in this tutorial.

DELETE FROM HOTEL.CITY;
DROP TABLE HOTEL.CITY;
DROP SCHEMA HOTEL CASCADE;
DROP USER USER1 CASCADE;

For further information, see Grant Statement(Access Control), Object Privileges, and SET SCHEMA.


HDBSQL can run commands in three different modes; interactive, non-interactive, and batch. An example of each is shown below.

SELECT * FROM HOTEL.CITY; -- interactive
hdbsql -attemptencrypt -U USER1UserKey "SELECT * FROM HOTEL.CITY"; -- non-interactive
hdbsql -attemptencrypt -U USER1UserKey -I cities.sql -- batch

Congratulations! You have now created a user and some tables using HDBSQL. This user will be used to connect and query the data in the following tutorials.

Which of the following statements are true? You can select more than one answer.
×

Next Steps

Back to top