What is SAP Hana
• In Memory
• Column oriented(with Row support as well)
• Combination of Hardware and software
• Process Massive real time data
Architecture of SAP HANA
Advantages of column Bases storage
• Faster data access
• Better compression
• Better Parallel processing
Agenda
o SAP HANA Cloud Connectivity
o Data Virtualization (Federation) and Replication.
o Remote subscription for RTR .
SAP HANA Cloud Connectivity
Data Virtualization & Replication
o Virtually access the remote table data
(without replication) using virtual table
o Replicate the remote table data into
HANA cloud using Snapshot
Replica/Real time replica
o Fabric Virtual table is one of the
important feature of Hana cloud, it
allows the Virtual table to toggle
between the Federation and
Replication.
RTR
• It’s a replica table, which is created on top of virtual table(which in turns
access the data from remote table via remote sources). This is achievable
via SDA hanaodbc adapter , for Hana cloud system.
Add Replica
ALTER VIRTUAL TABLE <table_name> ADD SHARED [SNAPSHOT] REPLICA [PAGE|
COLUMN LOADABLE] ;
Disable Replica (Not supported for Snapshot
Replica)
ALTER VIRTUAL TABLE <table_name> DISABLE REPLICA;
Enable Replica (Not supported for Snapshot
Replica)
ALTER VIRTUAL TABLE <table_name> ENABLE REPLICA;
Alter replica
ALTER VIRTUAL TABLE <table_name> ALTER REPLICA PAGE|COLUMN LOADABLE;
Delete Replica
ALTER VIRTUAL TABLE <table_name> DROP REPLICA;
Override of Replica
• 1. SELECT * FROM <virtual_table_name> WITH
HINT(NO_VIRTUAL_TABLE_REPLICA);
• 2. SET 'VIRTUAL_TABLE_REPLICA' = 'FALSE';
Refresh Snapshot Replica
ALTER VIRTUAL TABLE <table_name> REFRESH SNAPSHOT REPLICA ;
Syntaxes for RTR
• CREATE VIRTUAL TABLE [Link] AT "REMOTE_REP"."<NULL>"."DEMO"."T";
• CREATE COLUMN TABLE [Link] LIKE [Link];
• CREATE REMOTE SUBSCRIPTION SUB ON [Link] TARGET TABLE [Link];
• ALTER REMOTE SUBSCRIPTION SUB DISTRIBUTE;--To
synchronizes the schema and records and
activate(
then activates replication)
• ALTER REMOTE SUBSCRIPTION SUB RESET; -- To Deactivate
• DROP REMOTE SUBSCRIPTION SUB; -- To drop
JSON Document Store
SAP HANA Cloud | JSON Document Store
Key Characteristics
The SAP HANA Cloud Document Store provides the storage and access of JSON documents in an efficient and easy way
JSON documents may be deeply structured but do not have a schema. This means that any valid JSON data can be inserted
without first declaring its structure.
It allows native operations on JSON documents while providing an unified SQL access layer for filtering, aggregating, and joining
JSON documents with relational data in SAP HANA column or row store tables
The Document Store is a dedicated, but fully integrated binary store in SAP HANA Cloud providing all core database capabilities. It
is an optional feature of an SAP HANA Cloud instance and can be currently enabled/disabled via service request.
SAP HANA Cloud | JSON Document Store
Why using JSON data?
Example “Product Catalog”: Need for dynamic changes, e.g. for new items and characteristics
Modelled as relational data Modelled as JSON data structure
Category Brand Pcs Price Comment {
Computer Samsung 5 1000 5% Discount
"Categories": [
{
Advantages:
"Computer": [
{
Schema-less (new key-value pairs can
Computer Lenovo 4 1500 "Brand": "Samsung", be inserted)
"Pcs": "5",
"Price": "1000",
"Comment": "5% Discount"
Data itself describes structure
Food KitKat 10 2
},
{
Arrays, objects, key-value pairs
"Brand": "Lenovo",
"Pcs": "4", No empty or NULL values
"Price": "1500"
}
]
},
{
"Food": {
"Brand": "KitKat",
"Pcs": "10",
"Price": "2"
}
}
]
}
SAP HANA Cloud | JSON Document Store
Architecture Overview
SAP HANA Cloud Key Capabilities:
optional One transaction handling with Indexserver
indexserver docstore server Compression, Indexing
Process Tx, DDL/DML Process
Paging to disk (binary format, not NSE)
Rowstore
Document
SQL integration
Store
Columnstore
Based on common HANA Cloud persistence
Join with rowstore / columnstore tables
HANA Cloud Persistence
Aggregation within the Document Store
Import/Export of files with JSON format
SAP HANA Cloud Volume
SAP HANA Cloud | JSON Document Store
Enabling in SAP HANA Cloud
Enabling in SAP HANA Cloud Central
• Needs SAP HANA Cloud instanced with at
least 3 vCPUs
Disabling requires service request (self-
service planned)
Not part of SAP HANA Cloud BTP Free-Tier
offering (planned in 2022)
Check existence for document store in SAP
HANA Cloud (with QRC4 2022):
SELECT object_count FROM m_feature_usage WHERE
component_name= 'DOCSTORE' AND feature_name=
'COLLECTIONS';
Returns #of collections or NULL (no document store enabled)
SAP HANA Cloud | JSON Document Store
SQL Integration
-- Create a collection -- Join with Column Store table
CREATE COLLECTION customers; CREATE COLUMN TABLE t1 ("name" nvarchar(20), "age" int);
INSERT INTO t1 VALUES('Paul', 34);
-- Insert data into collection WITH myView AS (SELECT "name", "address"."city" as "city" FROM customers)
INSERT INTO customers VALUES({"name": 'Paul', SELECT t."age", t."name", myView."city" FROM myView INNER JOIN t1 AS t
"address": { ON myView."name" = t."name";
"street": 'Hauptstrasse 10',
"city": 'Heidelberg'},
"itemsSold": 3, -- Update individual fields
"active": True UPDATE customers SET "address"."city" = 'Mannheim'
}); WHERE "name" = 'Paul'
-- Filter (get JSON string as result) -- Compose new structure
SELECT * FROM customers SELECT { "firstName": "name", "city": "address"."city" }
WHERE "address"."city" = 'Heidelberg'; FROM customers;
-- Filter (get tabular result set) -- Aggregation
SELECT "name", "address"."street" FROM customers SELECT "address"."city" AS city, COUNT(*) AS count
WHERE "address"."city" = 'Heidelberg'; FROM customers
GROUP BY "address"."city";
-- Import/Export file from AWS S3 bucket (import from Azure Store is also supported – see HC online help)
IMPORT customers INTO 's3-eu-central-1://<AWS Access Key ID>:<AWS Secret Access Key>@<AWS S3 bucket>' WITH REPLACE FAIL ON INVALID DATA;
EXPORT customers INTO 's3-eu-central-1://<AWS Access Key ID>:<AWS Secret Access Key>@<AWS S3 bucket>' WITH THREADS 4 REPLACE;
SAP HANA Cloud | JSON Document Store
ARRAY Support in SQL
Collection “MEASUREMENTS”
{
"sensor": "ABCD",
"date": "2020-01-01", SELECT "sensor", "date", "t"."time", "t"."value" FROM measurements
"temperatures": [ UNNEST "temperatures" AS "t" ORDER BY "sensor", "date",
{
"time": "11:00",
"t"."time";
"value": 6
}, 2020-01-01 11:00 6
{ 2020-01-01 13:00 11
"time": "13:00", 2020-01-02 11:00 8
"value": 11
}
2020-01-02 13:00 10
]
}
{
"sensor": "EFGH",
"date": "2020-01-01",
"temperatures": [ SELECT "sensor" FROM measurements WHERE FOR ANY "t" IN
{
"time": "11:00",
"temperatures" SATISFIES "t"."value" < 7 END;
"value": 8
}, ABCD
{
"time": "13:00",
"value": 10
}
]
}
SAP Business Technology Platform
Ready-made platform for application development, handling
the underlying infrastructure
provides tools and services for developers to create, test, and
deploy applications without worrying about the underlying
infrastructure.
BTP provides 3 environments - Cloud Foundry, ABAP and
Kyma
build the solution with minimal effort, low cost and have faster
time to market.
• Infrastructure
• Security
• Ready-made tools and equipment
• Training to your staff, expert advice
Key pillar of SAP BTP
Architecture of SAP Business Technology Platform (BTP)
SAP HANA CLOUD - multi-model support
D A T A B A S E
Document store Machine learning
ACID-compliant, flexible Expert library designed
management of JSON and optimized for fast in-
documents memory processing
JSON
document
store
Enterprise search
Machine
Spatial Spatial Powerful search solution for business
learning
Location-based intelligence entities
Predictive modeling
Graph
Easily apply automated predictive engine for n
Processing of highly connected
Enterprise Predictive expert data scientists
data
search modeling
Graph
Vector
Store high dimensional
vectors, context to AI Vector
model
Federation / Data replication / Live access
DBMSs – Hadoop – Spark - Streams
SAP HANA Cloud Modeling in Business Application
Studio