Solr 5. 3: Execute SQL queries

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!

Write a comment
Cancel Reply