Skip to Content
Author's profile photo Naveen Kumar

HANA Doc Store Service

Overview

HANA so far has been excellent providing Row and column store But as they say, there is always scope of improvement and SAP proving it again.  

SAP HANA now also includes a DocStore. Not unlike MongoDB, the SAP HANA JSON Document Store enables the developer to store and manage JSON documents (artifacts) with support for native operations on JSON including filtering, aggregation, and joining JSON documents with HANA relational tables. We are not talking about storing JSON as CLOB in a regular database column, in other words – the quick and dirty way

Doc store service:

The Document Store is an optional feature of the SAP HANA database and you have to enable the docstore operating system process per tenant database.
The DocStore does not have a pre-determined SQL port, all communication is routed through the regular indexserver (database engine) process

 

It will require 14 GB of space specifically on tenant Basis.

Procedure to Setup

As HANA 2.0 SPS01 on wards, it is only MDC so providing only way for Multi DB.

For multi-database instances, there can be 0 (none) or 1 (one) DocStore per tenant database; you can enable the DocStore in a database tenant by running the following command as administrator in the SQL console:

ALTER DATABASE <database> ADD ‘docstore’

You can add to particular hostname and port also
Alter database <SID> add ‘docstore’ at location ‘<hostname>:<Available Port>’

 

You can find the already used ports using below command

SELECT DATABASE_NAME, SERVICE_NAME, PORT, SQL_PORT, (PORT + 2) HTTP_PORT FROM SYS_DATABASES.M_SERVICES 

 

Removing Service

Alter database <SID> remove ‘docstore’ at location ‘<hostname>:<port>’

NOTE: If you remove the docstore service, you will lose all the data stored in docstore.

Operations on JSON document

Login to Tenant DB using HANA studio or hdbsql or hana cockpit, We just want SQL editor , anyway from mentioned way as per one’s comfort.

Run a query for operations on JSON format. If you are developer or ABAPer it will be very easy to run queries. If you know JSON coding, it will be easy to work in HANA also like Select, update, delete, rename to name a few

Showing example of hdbsql also and running delete query as below:

 

DELETE FROM CUSTOMERS

You can also rename JSON documents also similar like running sql queries.

 

This way SAP has improved another feature of HANA and now you can store your documents directly into HANA using DocStore service.

 

 

Assigned Tags

      15 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Steve Guo
      Steve Guo

      It's  a great function.

      I can image one of the usage of this function is to store customer defined fields dynamically without change the structure of table.

      But as you mentioned, the data in json can do sql calculation with data in table, can I get the same poformance compare to traditional way?

      Author's profile photo Naveen Kumar
      Naveen Kumar
      Blog Post Author

      Thank you Steve for Kind words.

       

      Data stored in collection format rather than as sql table hence sql statements used slightly differently. We expect the same performance as well. We have tested on small amount of collection data. which was swift.

      Author's profile photo Bill McGagh
      Bill McGagh

      Hello Naveen, Is there add'l tech documentation available (e.g. full functions available) beyond HANA 2.1 dev guide. Our client's very interested in getting moving with this capability.

      Author's profile photo Naveen Kumar
      Naveen Kumar
      Blog Post Author

      Hello Bill,

      Extremely sorry for replying so late. It skipped from my end due to travel.

      HANA 2.0 SPS 01 is providing doc store and there is no much functionality available in terms of document. It is in infant stage right now. I do recommend someone having experience in JSON language can help the client to fully utilize this functionality.

       

      Regards,

      Naveen

      Author's profile photo Swathi Radhakrishnan
      Swathi Radhakrishnan

      Hi Naveen,

      I tried doing the same to add the docstore, ALTER DATABASE "db_name" ADD 'docstore';

      But this throws me an error "Could not execute 'ALTER DATABASE "db_name" ADD 'docstore''
      SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near """: line 1 col 16 (at pos 16)

      Could you please help me with this?

       

      Regards,

      Swathi

       

      Author's profile photo Naveen Kumar
      Naveen Kumar
      Blog Post Author

      Hello Swathi,

      try different syntax. Avoid giving DBname is quotes. ,

      Naveen

      Author's profile photo Swathi Radhakrishnan
      Swathi Radhakrishnan

      Hi Naveen,

      Thanks for your reply.

      I tried executing the statement by avoiding the quotes as well and with other possible syntax too. Still it throws the same error.

       

      Regards,

      Swathi

      Author's profile photo Former Member
      Former Member

      Thanks Naveen,

      The example is working fine, when trying to execute update it shows syntax error...

      "UPDATE Customers SET { "name": 'Paul', "age": 50 } where "age" > 30;"

      incorrect syntax near "{":

      this update statement is from the SAP documentation https://help.sap.com/viewer/4505d0bdaf4948449b7f7379d24d0f0d/2.0.02/en-US/fc6a0ab114c8416eb561c9b5aba8fc7d.html

      Do you have any suggestion what should be changed to make the syntax correct?

       

      Thanks,

      Khaled

      Author's profile photo Naveen Kumar
      Naveen Kumar
      Blog Post Author

      Hello Khaled,

       

      I have faced the similar issue also. Resolved by using different quotes, analyzing bit more over conditions, Putting Number also in quotes. Hope it helps.

      Author's profile photo Former Member
      Former Member

      this works fine

      UPDATE Customers SET "name"= 'Paul', "details"."age"= 50 where "details"."age" > 30;

      Author's profile photo Naveen Kumar
      Naveen Kumar
      Blog Post Author

      W'nderful!

      Author's profile photo Arjun V Prakash
      Arjun V Prakash

      Hi Naveen,

       

      Thanks for the blog.

      I am getting an error:-

      SAP DBTech JDBC: [257]: sql syntax error: invalid service type: line 1 col 25 (at pos 25)

      when I am trying to enable the DocStore as follows:-

      ALTER DATABASE TEST ADD 'docstore';

      Please help me out.

       

      Thanks in advance

      @rjun

      Author's profile photo Naveen Kumar
      Naveen Kumar
      Blog Post Author

      Hello Arjun,

       

      What is the DB level you are trying to setup this service. ? It seems you are trying to run it on HANA 1.0 where service type is unknown.

      Author's profile photo Ayman Elgendi
      Ayman Elgendi

      Dear,

      Thanks for sharing, how to use this function using abap ?

      Author's profile photo Basis Support
      Basis Support

      While doing an installation, the docstore is in stopped state and installation is also stuck since then.

      What can be done to start the docstore?