SAP Data Warehouse Cloud, SAP Analytics Cloud

Creating a sample data for a data product using DBeaver

This blog post gives details on how to create a JSON sample file for a data product in the Data Marketplace of SAP Data Warehouse Cloud via a database tool DBeaver. DBeaver is a SQL client software application that act as a general database engine for developers, SQL programmers, database administrators and analysts. The community version of DBeaver has advantages of free multi-platform database tool and supporting all popular databases such as MySQL, PostgreSQL, Oracle, SAP HANA etc.

How to download and install DBeaver:

The entire process of creating a sample data – from setup to HANA connection and a JSON file generation – including 5 steps with some additional explanations are following:

1. Setting up a database user in SAP Data Warehouse Cloud

  • As a first step, you need to have a database user (DB) in SAP Data Warehouse Cloud in order to connect to HANA Cloud in DBeaver. In Space Management, you can create a DB user for data consumption and data ingestion. In this case, you only need to read the sample data views in DBeaver and then export it as a JSON file. As a result, the DB user setup can be done as following:
  • Database User Settings:

2. Create a SQL view which consists of a small amount of data (e.g filter on 1000 rows) of the data product and can present how the data product looks like.

  • As sample data is just an exemplar of the actual data product so that the data consumer can get a glimpse of what contains in the data product, we only need to select the small amount (some rows) data from the original view which is used as a source for the data product. Create a SQL view as following:

SELECT * FROM “View_1” limit 999

  • Then validate SQL to check if there is any error in the SQL statement. Set expose for consumption so that it can be seen in DBeaver.
  • Then save and deploy the SQL view.

3. Connectivity with HANA Database in DBeaver

  • After creating a DB user in SAP Data Warehouse Cloud, you need to connect to HANA Database using this user.
  • New Database Connection (the + button on the top left) → HANA → Next
  • The Connection Settings should be matched with the information of the DB user:
  • Click Finish to connect to the HANA Database.

4. Generate a JSON file for the sample data

  • After connecting to HANA Cloud, go the connection you just created on the Tab Database Navigator.
  • HANA Connection → Schemas → TEST → Views → View_1_Data_Sample → (right click) Export Data
  • The next steps should be done as following:
  • Note: ‘Directory’ as where you choose to save the exported JSON file and ‘File name patterns’ as the name of the file.

5. Format the JSON file

  • In order to read the JSON file in the Data Marketplace, the exported JSON file should be formatted as following:
  • Open the JSON file in the Text Edit, change the name of the value (in this example “View_1_Data_Sample”) into “data”. Then save.

After finishing the generation process, the JSON file is ready to be uploaded as the sample data for the data product in the Data Marketplace.