Creating a sample data for a data product using DBeaver

[former_member801800]

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: https://dbeaver.io/download/

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.

 

Conclusion: When listing a data product in the Data Marketplace of SAP Data Warehouse Cloud, you need a sample data so that customers are able to have an overview about what is the data product about and which data contain in the data product. This blog post will help you create the sample data as JSON file so that you can upload it in the Data Marketplace.

Feel free to reach out to us if you have problems with creating a sample data for a data product in the Data Marketplace.