Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
Showing results for 
Search instead for 
Did you mean: 
Active Contributor
You are floating quietly on a lake of relational transactions… You can run SQL for your CRUD operations.. And your boat is a schema swiftly carrying you home… this feels good and safe… you can perform different updates and inserts and trust that the database has got your (roll)back.

Suddenly the names of your columns are not fixed anymore. Schemas do not even make sense, your data is unstructured, can be nested and have depth. You are storing documents.

JSON is here

Source: Friday the 13th. Dir Sean S Cunningham. Paramount Pictures. 1980.


A nightmare? Not at all.

This is a very simple combo of capabilities in HANA that I find appropriate to share on a Friday 13th just for the fun of it.

Of course, I am running this on my tenant database in HANA Express, so you can run it too.

I start by creating the collection and inserting some random values into it.

Do you see what is going on there? That insert statement?

Here is a piece of the code if you are not feeling too creative to write an example.
create collection doc_store;
insert into doc_store values(
{ "USER_SAYS":'The platform is mad at me',
"USER_MEANS":'I have forgotten my password and do not care to read the error message',

I had imported the countries and (what I suppose are) their central points form Google’s Dataset Publishing Language repository. So I want to join this JSON store with my relational, row-based master data table in a SELECT JOIN statement:
with doc_view as 
( select country
from doc_store
where random_int >= 1 )
st_geomFromText('Point( -73.985809 40.758830 )', 4326).st_distance(st_geomFromtext('Point( '|| countries.longitude || ' ' || countries.latitude || ' )', 4326), 'meter') / 1000 as DISTANCE_KM
from doc_view
left outer join "PEP_HDI_DB_1"."countries.COUNTRIES_MD" as countries
on = countries.country_name;

And what about the first select statement? It's SQL on  NoSQL! I don’t know about you, but I think this is just beautiful:

beautiful even if it can be technically improved beyond a demo…

Let's reflect on this briefly: I can join data between my collection in the document store with the master data in a relational table while using a geospatial query to find out the distance between my location and the coordinates in it using the same platform.

While I’m there, why not go a bit wilder and find where Jason is hiding in our document store with a text index:

In order to run the fuzzy text search on my wide column values I need to first move them to columnar table with the proper index. Something like this:
create column table text_analysis_t 
country varchar(100),
distance double,


Then I can run the same join statement to populate the table
insert into text_analysis_t
with doc_view as (select country, user_says from doc_store where random_int >= 1 )
select as country_doc_store,
st_geomFromText('Point( -73.985809 40.758830 )', 4326).st_distance(st_geomFromtext('Point( '|| countries.longitude || ' ' || countries.latitude || ' )', 4326), 'meter') / 1000 as DISTANCE_KM,
cast(doc_view.user_says as VARCHAR(5000)) as user_text
from doc_view
left outer join "PEP_HDI_DB_1"."countries.COUNTRIES_MD" as countries
on = countries.country_name ;

I’m using the results to populate my columnar table and finally run the fuzzy text search query.
select user_text, score() as similarity 
from text_analysis_t
where contains(USER_TEXT, 'jason', fuzzy(0.4,'textsearch=compare'))

So if you are looking to dive into this topic with some minimum seriousness, like for a real life use case involving IoT, the introductory series of blog posts starting here by kai.mueller01 contain very clear explanations of what this is for and how to use it. The SAP Help was also a great reference, especially if you are looking into doing this in XS Advanced.

Happy Friday 13th  !