Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

JSON Is Here

DZone's Guide to

JSON Is Here

Happy Friday the 13th, everyone! Here is a post about a very simple combo of capabilities in HANA.

· Database Zone ·
Free Resource

Running out of memory? Learn how Redis Enterprise enables large dataset analysis with the highest throughput and lowest latency while reducing costs over 75%! 

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.

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

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.

I am running this on my tenant database in HANA Express.

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 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 the 13th!

Running out of memory? Never run out of memory with Redis Enterprise databaseStart your free trial today.

Topics:
database ,json ,tutorial ,hana

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}