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 13^{th 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',
	  "RANDOM_INT":1,
	  "COUNTRY":'Canada'});

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 )
select doc_view.country,  
	 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 doc_view.country = 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,
	user_text text FAST PREPROCESS ON FUZZY SEARCH INDEX ON
	
);

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	doc_view.country 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 doc_view.country = 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 Mueller 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 13^th  !