HANA Amazon S3 Integration - The Easy Way

[Ian_Henry]

SAP HANA customers are often requesting the integration of SAP HANA data with data stored in AWS S3 object storage.  In this blog I am relaying some work of an esteemed colleague. We will describe the easiest possible integration with zero coding effort by simply using the AWS Storage Gateway product.

The main steps covered here include

1. AWS Storage Gateway Overview


2. Configure AWS Storage Gateway via the AWS Console


3. Mount S3 File System


4. Configure HANA Smart Data Integration (SDI) File Adapter to S3

AWS Storage Gateway Overview

What is the AWS Storage Gateway?

• A gateway to connect on-premise IT environments with cloud-based storage


• Provides file-based, volume-based and tape-based storage solution

– File Gateway

• 
  
  
  
  • Supports a file interface into Amazon S3
  
  
  • Stores and retrieves objects in S3 using NFS or SMB protocols
  
  
  • Easy to access a file system mount on S3
  
  
  
  

– Volume Gateway

• 
  
  
  
  • Provides cloud backed storage using iSCSI protocol
  
  
  • Cache volumes and Stored volumes configurations
  
  
  
  

– Tape Gateway

• 
  
  
  
  • Archives durable data in AWS Glacier
  
  
  • Provides a virtual tape infrastructure
  
  
  
  

 

Configure AWS Storage Gateway via the AWS Console (13 steps)

1: Console / Storage Section / Storage Gateway

2: Select gateway type: File

3: Select gateway platform

4: Select EC2 instance type (M4, M5, C4, C5, R3, I3), xlarge or 2xlarge

 

5: Configure instance with right resources - VPC, CPU allocation…

 

6: Add Storage, Min 150GB for Cache

7: Configure Security Group (Open NFS but whitelist the IPs able to connect)

8: Wait for the EC2 instance to be created and get its IP from the AWS Console

9: Go to the first screen where Storage Gateway was created and click next, type the IP from previous step

10: Give the gateway a name and activate it

11: Map a local disk for Gateway Cache

12: Create the file share, mapping the S3 bucket (already created) to the Gateway name

13: Click next, wait until the file share is created and record mount instructions

HANA Smart Data Integration (SDI) File Adapter

• Use existing File Adapter via AWS Gateway


• Virtualize data access, can federate or copy data into HANA


• Read S3 data and Write S3 data too


• No code required

 File Adapter Capabilities

• Reads formatted files and free-form text files
  
  
  
  • CSV and fixed width text files
  
  
  • XML
  
  
  
  


• File systems
  
  
  
  • Local file systems
  
  
  • SharePoint source only
  
  
  • HDFS target only
  
  
  
  


• DML Support
  
  
  
  • Select
  
  
  • Insert
  
  
  
  


• Shared data access must be mounted as a directory under the DP Agent root installation directory

Mount the S3 File Gateway

DP Agent Root Installation on our setup this is:

/data/sap/dataprovagent

As root user

– Create a mount point, here we use s3

mkdir /data/sap/dataprovagent/s3

– Mount AWS Storage Gateway, created by the steps above from the file share gateway

mount -t nfs -o nolock,hard IP_OF_AWS_SG:/GATEWAY_NAME /data/sap/dataprovagent/s3

– If the S3 bucket contains files, check these are visible

ls –al /data/sap/dataprovagent/s3

SDI & Amazon S3 "greetings" example

File Adapter Config File - greetings.cfg



Here we can see  some sample files stored in S3, but now available via NFS

Create a Remote Source using the SDI File Adapter

In HANA Studio we will use the following parameters

• The Root Directory indicates where to read the files from, here that is s3

Directory of the file format definitions indicates where the configuration files are (greetings.cfg)

SDI Virtual Table Access To S3 Files

Here is a simple example to access S3 files via greetings virtual table



Read the S3 Files in HANA (via virtual table)



Write to S3 files from SQL (via virtual table)

insert into "SYSTEM"."FileAdapter_greetings" values ('/data/sap/dataprovagent/s3','demo_4.csv',1, NULL, 8, 110, 'merhaba')

Validate the data in S3 (via the AWS Console)



We can also validate the files have been created using the command line



And finally validate the results from our greetings virtual table in HANA

Conclusion

In this blog we have seen how we can integrate SAP HANA data to Amazon S3 storage using the AWS Storage Gateway via the HANA Smart Data Integration File Adapter.