This blog describes how to set up SAP HANA, express edition for use with SAP HANA blockchain. Although it focuses on HANA Express, most of the steps below apply to any full-size HANA database as well.
Check out the other parts of my blog series on SAP HANA blockchain as well to get a conclusive overview about HANA blockchain.
Part 3: SAP HANA Blockchain: Setup SAP HANA, express edition for HANA blockchain
Before we get started, a brief overview about the prerequisites and tools that we will be working with. You will need:
- An instance of SCP Blockchain service on SAP Cloud Platform– either MultiChain or Hyperledger fabric.
- The SAP HANA Integration Service, also on SAP Cloud Platform.
- SAP HANA, express edition. Of course, any other SAP HANA database will work just as well.
- SDI Agent, version 2.2.4 or higher.
- SAP HANA Blockchain Adapter.
Step 1: SAP HANA, express edition
In this tutorial, I am using SAP HANA, express edition – HANA express in short. HANA express is a leaner version of SAP HANA, but provides almost the same feature set as SAP HANA, with a generally lower memory footprint. Another huge advantage is that it comes free-of-charge for small installations. This means that you can try HANA Blockchain even if you do not have access to your own full-size SAP HANA database.
There are a few options to deploy HANA express. Virtual machine images and binary installers (for bare metal installations) are available from SAPs portal page. There are also Docker containers in the docker store (with XSA or without XSA).
The setup process of all options is well documented, so I will not re-invent the wheel here. I will instead provide links that will guide you through the setup process of the different options. A HANA-only installation (that is, without XSA) is sufficient, but make sure to select Smart Data Integration if you’re prompted.
- Getting Started with SAP HANA 2.0, express edition (Virtual Machine Method)
- Pre-Installation Tasks
- Installing SAP HANA 2.0, express edition (Virtual Machine Method)
- Getting Started with SAP HANA 2.0, express edition (Binary Installer Method)
- Machine requirements
- Install SAP HANA 2.0, express edition
Step 2: Data Provisioning Agent (Smart Data Integration)
If everything was set up correctly during the HANA Express installation, the Data Provisioning Server should already be enabled. If not, you can follow the steps described in the Installation and Configuration Guide for SDI.
The SPS 03 version of SAP HANA Express includes an SDI Agent as well. Unfortunately for us, it is version 2.2.3, while SAP HANA blockchain requires at least version 2.2.4. Therefore, you will need to update the agent to a more recent version. The section “Install the Data Provisioning Agent” in the SDI Installation Guide describes how this is done. In this tutorial, I am setting up the Data Provisioning Agent on the same host as SAP HANA Express.
Download the SDI Agent
You’ll need two files for this step. The SDI Agent itself and SAPCAR to unpack the Agent.
- Go to the SDI section on the SAP Support Launchpad.
- SAP HANA SDI 2.0
- Comprised Software Component Versions
- HANA DP Agent 2.0
- Download the latest version. For example: IMDB_DPAGENT200_03P_10-70002516.SAR
Extract the downloaded archive
- Download SAP CAR from the SAP Support Launchpad, e.g., SAPCAR_1014-80000935.EXE.
- Assign execution rights:
chmod +x SAPCAR_1014-80000935.EXE
- Extract SDI Agent archive
./SAPCAR_1014-80000935.EXE -xvf IMDB_DPAGENT200_03P_10-70002516.SAR
Install the new SDI Agent
- Create the installation directory:
sudo mkdir /usr/sap/dataprovagent sudo chown <youruser> /usr/sap/dataprovagent
- Start the installation
The output should look similar to the screenshot below. The data provisioning agent is installed to /usr/sap/dataprovagent by default.
Configure the SDI Agent and deploy the SAP HANA Blockchain adapter
After successful installation of a compatible SDI agent, the agent will need some configuration. To do so, you require a HANA database user with at least the system privileges ADAPTER ADMIN and AGENT ADMIN.
The configuration tool is available on the command line, as well as with a graphical user interface. The GUI version requires an X11 server if started remotely, as well as X11 forwarding enabled in your SSH client.
The screenshot above shows the command line version of the configuration tool. Execute the steps below and follow the instructions to get the data provisioning agent set up for HANA blockchain.
- Start the command line tool in configuration mode
- [Option 2] Start the agent.
- [Option 5] Connect the agent to your SAP HANA Express installation.
- [Option 6] Register the agent with your SAP HANA Express installation.
- [Option 8] Deploy the SAP HANA blockchain adapter as a custom adapter.
- [Option 7] Register the adapter.
Once this has be completed, move on to SAP HANA Studio or the Database Explorer to complete the configuration on the SAP HANA database side.
Step 3: Connect SAP HANA to SCP Blockchain
To finalize the configuration and make the SCP blockchain instance available to SAP HANA, we will first create a remote source and then map this remote source into a database schema via virtual tables. Persisting blockchain data into SAP HANA is achieved via a remote subscription that regularly polls the SCP blockchain service and persists new records in SAP HANAs column store.
Before you can do that, you will have to configure the SCP side of SAP HANA blockchain via the SAP HANA integration service. This is outlined in my previous blog post on SAP HANA blockchain.
More information on the individual steps is available in the official documentation.
- SAP HANA Blockchain adapter @ SAP Help Portal
3.1 Create Remote Source
The first step is to create a remote source in SAP HANA. To do so, you will need the following information. Most of it must be extracted from the service key of the SAP HANA integration service as described at the very end of my previous blog post.
IMPORTANT: Append “/hanaintegration/api/v1” to the extracted URL
|Access token URL||
IMPORTANT: Append “/oauth” to the extracted URL
|Polling Interval||Defines how often polling operations run to check for new data on the blockchain. Default is 5 seconds.|
|Proxy Host||Only required if a proxy is needed for SAP HANA to connect to SCP Blockchain.|
|Credentials Mode||Choose “Technical User”.|
|Client ID||e.g., “sb-7d165a60-…-420adfc9-f96e-4090-a650-0386988b67e0!b1836“|
|Client Secret||e.g., “TJmM…Wsg=”|
Creating the remote source can be done graphically in the Database Explorer, but it is also possible via SQL. A database user needs the CREATE REMOTE SOURCE system privilege to create a remote source. Examples of the various options are displayed below.
Adding a remote source in the Database Explorer
Adding a remote source via SQL
-- create remote source CREATE REMOTE SOURCE "<NAME_OF_NEW_REMOTE_SOURCE>" ADAPTER "BlockchainAdapter" AT LOCATION AGENT "<NAME_OF_DPAGENT>" CONFIGURATION '<?xml version="1.0" encoding="UTF-8"?> <ConnectionProperties name="Connection"> <PropertyEntry name="url">[Blockchain URL]/hanaintegration/api/v1</PropertyEntry> <PropertyEntry name="accessTokenURL">[Access token URL]/oauth</PropertyEntry> <PropertyEntry name="pollingInterval">[Polling Interval]</PropertyEntry> <PropertyEntry name="proxyHostName">[Proxy Hostname]</PropertyEntry> <PropertyEntry name="proxyPortNumber">[Proxy Port]</PropertyEntry> </ConnectionProperties>' WITH CREDENTIAL TYPE 'PASSWORD' USING '<CredentialEntry name="credential"> <user>[Client ID]</user> <password>[Client Secret]</password> </CredentialEntry>';
3.2 Create Virtual Table
Once the remote source is created, virtual tables are required to access the data it references. The virtual tables map the remote sources into a database schema on SAP HANA, from which they can be accessed by, e.g., SQL queries.
There are three types of tables:
- Raw data: Information about blocks and transactions.
- Ledger: Individual messages; allow access to the whole history of transactions that were committed to the blockchain.
- Worldstate: Only the latest valid tuples that are known to the blockchain. A worldstate table looks and behaves in many ways just like a regular database table.Querying a “*_worldstate” virtual table always returns an empty result set. This behavior is intentional. To see the data, you will need to persist the data into SAP HANA first via a remote subscription
The virtual tables are created with a few clicks in the Database Explorer, as displayed below.
In my example I’m using a simple data model called INHABITANTS. The configuration of this data model in the HANA integration service (see also my previous blog) is displayed below.
Virtual tables can be named arbitrarily. In my case I chose
- inhabitants.inhabitant for the ledger table, and
- inhabitants.inhabitant_worldstate for the worldstate table.
3.3 Create Remote Subscription
The virtual tables we created in previous step are – as the name already suggests – only a virtual link to the actual data. This means that no data is actually persisted on SAP HANA, but the data is instead retrieved remotely from the blockchain with every access. Especially for large data volumes and performance-critical applications, this is most likely not the most efficient way of working with your data. The steps below describe how to setup a remote subscription to persist blockchain data physically in SAP HANAs column store.
Step 1: Create physical column store table, based on the virtual worldstate table.
-- create table for replicated data CREATE COLUMN TABLE INHABITANTS_WORLDSTATE_REPLICA LIKE "inhabitants.inhabitant_worldstate";
Step 2: Create the remote subscription that pulls data from the blockchain.
-- create a remote subscription that pulls all data from the world state table CREATE REMOTE SUBSCRIPTION INHABITANTS_WORLDSTATE_SUB AS (SELECT * FROM "inhabitants.inhabitant_worldstate") TARGET TABLE INHABITANTS_WORLDSTATE_REPLICA;
Step 3: Start queuing and distributing data.
-- start recording data ALTER REMOTE SUBSCRIPTION INHABITANTS_WORLDSTATE_SUB QUEUE; -- start distributing data ALTER REMOTE SUBSCRIPTION INHABITANTS_WORLDSTATE_SUB DISTRIBUTE;
After executing these steps, the physical worldstate table (in this example INHABITANTS_WORLDSTATE_REPLICA) is kept in sync with the blockchain and can be read just like any other column store table.
Step 4: Testing SAP HANA Blockchain
The queries below can be used to test if everything was setup correctly.
Before we start, a few things to keep in mind.
- DML statements will not change any transactions that were written to the blockchain before. Instead, they will append a new record with updated values to the blockchain.
- DELETE statements will flag a record on the blockchain as deleted, but not physically remove the record.
-- insert new record INSERT INTO "inhabitants.inhabitant_worldstate" ("SVNR", "firstName", "lastName", "birthTimestamp", "height", "married") VALUES ('123456789', 'Simpson', 'Homer', '1956-05-12', '1.75', true); -- update record UPDATE "inhabitants.inhabitant_worldstate" SET "firstName" = 'Abraham' WHERE "SVNR" = '123456789'; -- delete record DELETE FROM "inhabitants.inhabitant_worldstate" WHERE "SVNR" = '123456789';
SQL: Query on virtual ledger table
-- query entire transaction history from virtual table SELECT * FROM "inhabitants.inhabitant";
If you have executed the previous DML statements in the displayed order (INSERT, UPDATE, DELETE), a SELECT on the virtual ledger table will lead to the result below.
SQL: Query on physical worldstate table
-- query worldstate from virtual table --> ALWAYS EMPTY BY DESIGN SELECT * FROM "inhabitants.inhabitant_worldstate";
A query on the virtual worldstate table will always return an empty result set.
-- query replicated worldstate SELECT * FROM "INHABITANTS_WORLDSTATE_REPLICA";
A query on the physical worldstate table returns only the latest version. Since we deleted the record before, this is in this case also an empty result set.
Of course, these are just some short and very simple examples on how blockchain data can be accessed by SAP HANA. Since all data is in regular column store tables, the whole feature set of the HANA column store is available for these tables. This includes all advanced analytic engines, like graph and geospatial, and the usage with, for example, Calculation Views.