SAP HANA Cloud Document Store – HANA DocStore First Steps

Introduction

In this blog post, we’re going to walk through some of the technical details around how we interact with the SAP HANA DocStore

JSON Collections

While the Column and Row Stores use Tables to store data, the Document Store uses a JSON Collection

When getting started with DocStore, we’ll be using the Database Explorer, but any method of passing SQL to HANA Cloud should be sufficient (we could, for example use the Business Application Studio instead)

First, we run the below statement to verify that DocStore is active in our SAP HANA Cloud tenant

SELECT object_count FROM m_feature_usage 

WHERE component_name = 'DOCSTORE' 

AND feature_name = 'COLLECTIONS';

 

If the result is NULL, then DocStore isn’t active in our tenant. If the result is a number, DocStore is active and we have a number of Collections equal to the result

DocStore is active, and we have 30 Collections

Creating a JSON Collection

We can create a JSON Collection with one line of SQL

CREATE COLLECTION myFirstCollection;

Creating our first Collection

Inserting a JSON Document

We have two different so-called Interfaces for inserting Documents into HANA Cloud DocStore Collections

Both of these Interfaces can be used through SQL

JSON Interface (JSON Syntax)

JSON Documents use double quotes around both Keys and Values. With the JSON Interface, we can import valid JSON Object Documents using SQL (split into lines for readability)

INSERT INTO MyFirstCollection VALUES ('{ 

"name":"Peter Peterson",

"address": {

"street":"Faux St",

"city":"Melbourne"

}

}');

Inserting using the JSON Interface

Note that the values are entirely contained within brackets with single quotes

INSERT INTO Collection VALUES (' <JSON String Here> ');

 

SQL Interface (HANA SQL Syntax)

With HANA DocStore, the goal is to make JSON Documents a first-class citizen in SQL. For this reason, we can also insert a JSON Document using SQL Syntax

To insert a document using SQL Syntax, we can provide Keys enclosed in double quotes (or UPPERCASE without quotes), and Values enclosed in single quotes (split into lines for readability)

INSERT INTO MyFirstCollection VALUES ({ 

"name":'Steven Stevenson',

"address": {

"street":'Falso St',

"city":'Melbourne'

}

});

Inserting using the SQL Interface

Note that the JSON Document within our round brackets isn’t entirely contained within single quotes. Keys use double quotes, and Values use single quotes

INSERT INTO Collection VALUES ({ "Keys":'Values' });

Selecting our JSON Documents

We can use SQL Select statements to retrieve Documents or Keys from a Collection

We can use Select * to retrieve all documents within the Collection

SELECT * FROM MyFirstCollection;

Selecting * from our Collection

If we want to retrieve the JSON Document for Peter Peterson, we can use a WHERE clause against the Key for name

SELECT * FROM MyFirstCollection WHERE "name" = 'Peter Peterson';

Selecting * using a WHERE clause

If we encounter any issues here, we can double check two things:

Check that our Key is written exactly as in the document (the WHERE is case-sensitive)

Check that our Value is inside single quotes – not double quotes

If we want to select against a nested key (such as city, inside address), we can use a full stop in our WHERE clause

SELECT * FROM MyFirstCollection WHERE "address"."city" = 'Melbourne';

Selecting * using a Nested WHERE clause

If we encounter issues here, we can check that each part of the WHERE clause key has its own pair of double quotes

WHERE “address”.”city” will work

WHERE “address.city” won’t work

A Note on JSON Documents

Traditionally, JSON Documents can be either:

◉ A JSON Object (inside curly braces – { }) or

◉ A JSON Array (inside brackets – [ ])

HANA DocStore has been integrated with SQL, to allow for joins between JSON Documents and tables within Row and Column Stores. When we’re retrieving data from Documents, we retrieve values using keys (such as “name”)

Retrieving a JSON Document through SQL

For this reason, even though a JSON Array such as [0,1,2,3] is a valid JSON Document, we can’t directly store it in our HANA DocStore Collections because we don’t have a key to query against

In this case, we can use a JSON Object which contains our JSON Array, as long as the array has a key we can query against

Storing a JSON Array Document in HANA DocStore