SQL Transformation Trick

kanyinsa

Ever wanted to find a quick way to increase your match rate when unifying your data? I recently came across a cool way to use text analysis to do just that. Check it out!

I started with a query on my tag list (using SQL transformation), to count the frequency of all the different words in my tag description field:

SELECT `word`, count(*) AS count_words from 

(SELECT explode(split(`Description`, ' ')) as `word` from In) 

group by `word` 

order by count_words desc

And then I used another SQL transformation to find tags with keywords I was interested in

Select 'Valves' as `Asset Type`, * From In

WHERE `Description` LIKE '%VLV%' OR

`Description` LIKE '%Valv%'

This allowed me to then join my tag list with my other data sources using these common key words.

adam_element

This is very cool @kanyinsa - thanks for sharing!