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
- AWS Storage Gateway Overview
- Configure AWS Storage Gateway via the AWS Console
- Mount S3 File System
- 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
- 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.