Solr 5.3: Execute SQL queries


By: Dikshant Shahi | June 19, 2015

SQL statement is the most widely used language for querying data and is the natural choice of data analyst.  It’s acceptance is so wide that projects like Apache Hive got birth for the purpose, which provides you mechanism to run SQL-like query, called HQL, to execute Map/Reduce jobs.

When it comes to Solr and SQL, there is a close connection! Many of us learning Solr, try to relate it with SQL databases initially. SQL Databases makes the primary store for many Solr implementations. Even if SQL Database is not your primary store, you make like to query Solr by executing SQL statements.

With Solr 5.3,  this will soon be possible. JIRA-7560 is being worked on, which will support execution of SQL queries in Solr. To execute SQL query, you need to request the endpoint for SQLHandler, which will do the processing.

You can search for results, group by, perform aggregation etc using SQL statements. Below is a sample query, which searches for all albums along with title count and average rating, of singer ‘Enrique’ which has been downloaded more than 20,000 times.

select album_fl, count(title_fl), avg(rating_fl)
from music_collection
where singer_fl='enrigue'
group by title_fl
having (sum(download_fl) > 20000);

Also, the SolrJ client will support queries using SQL.

With SQL queries, data scientists will for sure feel at home!

 

 

Dikshant Shahi (8 Posts)

Dikshant works as Solution Architect at The Digital Group. He takes interest in Semantic Search, Information Retrieval, Natural Language Processing and Machine Learning. He is the author of book "Apache Solr: A Practical Approach to Enterprise Search".


This post has been viewed 4,081 times

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>