Sap Hana Cloud

SAP HANA Cloud Database Development

Related Skills

• sap-rap-comprehensive — CDS views consumed by HANA models
• sap-s4hana-extensibility — Custom CDS/HANA artifacts in S/4HANA extensions
• sap-business-ai-joule — Vector engine for AI/RAG scenarios
• sap-hana-tools — hdbsql CLI, HDI management, and HANA client tooling
• sap-cap-advanced — CAP with HANA Cloud native artifacts

Quick Start

Choose your development approach:

Minimal HDI procedure:

PROCEDURE "mySchema.myProc" (
  IN iv_customer_id NVARCHAR(10),
  OUT et_orders TABLE (order_id NVARCHAR(10), amount DECIMAL(15,2))
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
READS SQL DATA
AS
BEGIN
  et_orders = SELECT order_id, amount
              FROM "ORDERS"
              WHERE customer_id = :iv_customer_id;
END;

Core Concepts

HDI Containers (HANA Deployment Infrastructure)

• Schema-less development: Objects reference each other by name, HDI assigns runtime schema
• Build plugins: Each artifact type (.hdbcds, .hdbprocedure, .hdbtable) has a build plugin
• Container groups: Isolate tenants; cross-container access via synonyms + .hdbgrants
• Undeploy whitelist: undeploy.json controls what can be removed on redeploy

Multi-Model Engines

Data Tiering

1. Hot store: In-memory, fastest, most expensive
2. HANA Native Storage Extension (NSE): Disk-based, buffer cache, warm data
3. HANA Cloud Data Lake (HDLR): Relational cold storage, SQL access
4. Data Lake Files: Object store for unstructured/semi-structured data

Common Patterns

Pattern 1: Table with NSE Page Loadable Columns

-- .hdbtable
COLUMN TABLE "SALES_HISTORY" (
  "ID"          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  "CUSTOMER_ID" NVARCHAR(10) NOT NULL,
  "ORDER_DATE"  DATE NOT NULL,
  "AMOUNT"      DECIMAL(15,2),
  "DETAILS"     NCLOB
) WITH PARAMETERS ('PARTITION_SPEC' = 'RANGE (ORDER_DATE)
  (PARTITION VALUE <= ''2023-12-31'' PAGE LOADABLE,
   PARTITION OTHERS COLUMN LOADABLE)');

Pattern 2: SQLScript with Error Handling

PROCEDURE "processOrders" (
  IN iv_date DATE,
  OUT ov_count INTEGER,
  OUT ov_status NVARCHAR(20)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ov_status = 'ERROR';
    ov_count = 0;
  END;

  DECLARE lv_count INTEGER;

  SELECT COUNT(*) INTO lv_count
    FROM "ORDERS"
    WHERE order_date = :iv_date
      AND status = 'PENDING';

  UPDATE "ORDERS"
    SET status = 'PROCESSED', processed_at = CURRENT_TIMESTAMP
    WHERE order_date = :iv_date
      AND status = 'PENDING';

  ov_count = :lv_count;
  ov_status = 'SUCCESS';
END;

Pattern 3: Graph Workspace

-- .hdbgraphworkspace
GRAPH WORKSPACE "EMPLOYEE_ORG"
  EDGE TABLE "ORG_EDGES"
    SOURCE COLUMN "MANAGER_ID"
    TARGET COLUMN "EMPLOYEE_ID"
    KEY COLUMN "EDGE_ID"
  VERTEX TABLE "EMPLOYEES"
    KEY COLUMN "EMPLOYEE_ID";

-- Query: find all reports (direct + indirect) for a manager
SELECT * FROM "EMPLOYEES" e
WHERE e."EMPLOYEE_ID" IN (
  SELECT "EMPLOYEE_ID" FROM GRAPH_TABLE("EMPLOYEE_ORG"
    MATCH (mgr)-[EDGE * 1..10]->(emp)
    WHERE mgr."EMPLOYEE_ID" = 'MGR001'
    COLUMNS (emp."EMPLOYEE_ID")
  )
);

Pattern 4: JSON Document Store

CREATE COLLECTION "DEVICE_EVENTS";

INSERT INTO "DEVICE_EVENTS" VALUES('{"deviceId":"D001","ts":"2026-01-15T10:30:00Z","temp":22.5,"status":"OK"}');

-- Query nested JSON
SELECT "deviceId", "ts", "temp"
  FROM "DEVICE_EVENTS"
  WHERE "temp" > 30.0
  ORDER BY "ts" DESC;

Pattern 5: Vector Engine for Embeddings

CREATE TABLE "KNOWLEDGE_BASE" (
  "ID"        BIGINT PRIMARY KEY,
  "CONTENT"   NCLOB,
  "EMBEDDING" REAL_VECTOR(1536)
);

-- Similarity search (cosine)
SELECT TOP 5 "ID", "CONTENT",
       COSINE_SIMILARITY("EMBEDDING", TO_REAL_VECTOR(:iv_query_embedding)) AS score
  FROM "KNOWLEDGE_BASE"
  ORDER BY score DESC;

Pattern 6: Cross-Container Access

// .hdbgrants file
{
  "external_service": {
    "object_owner": {
      "schema_privileges": [
        { "schema_reference": "external_schema", "privileges": ["SELECT"] }
      ]
    },
    "application_user": {
      "schema_privileges": [
        { "schema_reference": "external_schema", "privileges": ["SELECT"] }
      ]
    }
  }
}

// .hdbsynonym file
{
  "EXT_CUSTOMERS": {
    "target": {
      "object": "CUSTOMERS",
      "schema.configure": "external_schema"
    }
  }
}

Pattern 7: Calculation View (Column Engine)

Calculation views are typically created in SAP Business Application Studio graphical editor. Key design principles:

• Push filters as low as possible in the view stack
• Use star joins for dimension/fact models
• Prefer calculated columns over calculated attributes for complex logic
• Set cardinality on joins to enable query optimization
• Use input parameters for mandatory runtime filters

Error Catalog

Performance Tips

1. Partition large tables — Hash for OLTP, range on date for time-series, round-robin for parallel scans
2. Use NSE for warm data — ALTER TABLE ... PAGE LOADABLE for columns accessed < daily
3. **Avoid SELECT *** — HANA is columnar; fewer columns = faster scans
4. Use HINTS sparingly — WITH HINT(NO_CS_JOIN) only after plan analysis
5. Monitor with: M_SQL_PLAN_CACHE, M_EXPENSIVE_STATEMENTS, M_SERVICE_MEMORY
6. Delta merge: Large delta stores slow reads; schedule merges for bulk-load tables
7. Parameterize queries — Prepared statements reuse execution plans
8. Data Lake for cold data — Move data older than N years to HDLR, keep hot queries fast
9. Calculation view pruning — Enable ANALYTIC_VIEW_PARAMETERS for optimizer pruning

Gotchas

• HDI deploy order: Dependencies must be resolved; circular references between .hdbsynonym and .hdbview will fail — use .hdbsynonymconfig for external schemas
• CURRENT_SCHEMA vs container schema: In HDI, never hard-code schema names; use synonyms
• Session variables: SET 'variable' = 'value' does NOT persist across connections in cloud
• Data Lake SQL subset: HDLR supports SQL but not full SQLScript — no procedures in Data Lake
• Memory limits: HANA Cloud has per-statement memory limits (default 8 GB) — monitor with M_EXPENSIVE_STATEMENTS

MCP Server Integration

{
  "mcpServers": {
    "hana-mcp": {
      "command": "npx", "args": ["-y", "hana-mcp-server"],
      "env": { "HANA_HOST": "your-instance.hana.trial-us10.hanacloud.ondemand.com",
               "HANA_PORT": "443", "HANA_USER": "YOUR_USER", "HANA_PASSWORD": "YOUR_PASSWORD" }
    }
  }
}

• HANA MCP: Direct HANA Cloud database access for queries and administration