Skip to Content
Technical Articles
Author's profile photo Cameron Swift

SAP HANA Cloud Document Store – HANA DocStore First Steps

Introduction

Within SAP HANA Cloud, we have the ability to activate the JSON Document Store (covered here). 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

Just like Tables, our Collections are created within a given schema. There are no Document Store specific privileges required to work with Collections – only those already required to work with Tables in a given schema

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)

Opening%20the%20SQL%20Console%20for%20our%20HDI%20Container

Opening the SQL Console for our HDI Container from the right-click menu

 

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%20is%20active%2C%20and%20we%20have%2030%20Collections

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%20our%20first%20Collection

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%20using%20the%20JSON%20Interface

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%20using%20the%20SQL%20Interface

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;

 

Select%20*%20from%20our%20Collection

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%20*%20using%20a%20WHERE%20clause

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%20*%20using%20a%20Nested%20WHERE%20clause

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

 

Conclusion

Throughout this blog post we’ve demonstrated how to check that the Document Store is enabled, how to create a JSON Collection, how to insert JSON Documents and how we can select against our JSON Collection

I hope this blog post has been useful to you, and I welcome any comments or questions in the comments

For those already familiar with JSON Documents, I’ve included a brief note on JSON Arrays below

 

 

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”)

Selecting%20*%20using%20a%20WHERE%20clause

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%20a%20JSON%20Array%20Document%20in%20HANA%20DocStore

Storing a JSON Array Document in HANA DocStore

 

Other Resources

 

SAP HANA Cloud Document Store – Enabling JSON Document Store by Cameron Swift

Spotlight: SAP HANA Cloud JSON Document Store by Laura Nevin

The Small JSON Document Store Cookbook by Mathias Kemeter

The SAP HANA JSON Document Store – Introduction (Part 1) by Kai Mueller

Ariba Analytics using SAP Analytics Cloud, Data Intelligence Cloud and HANA DocStore – Part 1 by Cameron Swift

 

Note: While I am an employee of SAP, any views/thoughts are my own, and do not necessarily reflect those of my employer

Assigned Tags

      6 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Javier Andrés Cáceres Moreno
      Javier Andrés Cáceres Moreno

      Wow

      Author's profile photo Patricia Rolon
      Patricia Rolon

      Hello,

       

      Nice Blog!!!

      Where can I find documentation about the privileges that are needed for this?

      It looks that, as the collections are tables cretaed in the SYSTEM schema, the users that will readd, insert, update and delete JSON will have to have privileges related to this schema. Is this correct?

      Thank you!

       

      Author's profile photo Mathias Kemeter
      Mathias Kemeter

      Hi Patricia, Collections are created within a schema (not restricted to SYSTEM). Just like tables. There are no privileges required, that are specific to the Document Store. From an authorization perspective Collections are treated like Tables.

      Author's profile photo Cameron Swift
      Cameron Swift
      Blog Post Author

      Thanks for reading Patricia - this is a great question, helpfully answered by my colleague Mathias. I've updated the blog post to be more clear about this

      Author's profile photo Patricia Rolon
      Patricia Rolon

      Hi Cameron,

      We are having an issue when storing JSONs in a collection using INSERT: the JSON gets truncated when its length is more than 1024 characters. Is this the limitation? If not (I hope so), how can this be solved?

       

      Thank you so much!

      Best Regards,

      Patricia

       

      Author's profile photo Mathias Kemeter
      Mathias Kemeter

      Hi Patricia, there is no such restriction for JSON documents in a collection. Depending if you are using HANA Cloud or on-premise, the limits can be checked in the documentation (cloud documentation, on-prem documentation). For HANA Cloud documents can have a size up to 2GB. It sounds like your JSON strings get truncated on the application layer before reaching the Document Store. Maybe there is an issue with the client or datatypes when passing the string?