Home Services Partners Company
Apache Solr ODBC over REST for OBIEE, Business Objects, Qlikview

Apache Solr ODBC over REST for OBIEE, Business Objects, Qlikview

February 06, 2014 0 Comments

Based on some recent projects, I am seeing a trend that continues to blur the lines between business intelligence (BI) and application development. It’s only fitting to blog about this topic since Progress Software is the worldwide leader in data connectivity and earned Gartner’s Visionary status for its application development platform.

My recent intelligent application consulting projects:

BI Platform Requirements Sources of Data
Oracle Business Intelligence OBIEE Dashboards that enable core interactive business decisions in real-time Oracle, Force.com
SAP Business Objects Intelligence portal that merges data warehouse history with up to the minute search capabilities SQL Server, Apache SOLR
Qlikview Interactive charts to move historic opportunity data from warehouse to operational CRM Teradata, Salesforce
Tableau hosted in Rollbase Interactive project activity application that refreshes analytics in real-time using DataDirect Cloud. Rollbase, OpenEdge

As the appdev/BI lines continue to blur, there is increased interest in querying unstructured data such as Apache SOLR.  To provide standards based connectivity to Solr, DataDirect provides self service rapid development capabilities to build a prototype in days.  This is achieved with DataDirect OpenAccess, and I will share my Apache Solr ODBC connector built last Saturday morning that took me less than an hour.

oasolr

Less than an hour to ODBC and JDBC connectivity to Apache SOLR

1-      Get started with SOLR tutorial: http://lucene.apache.org/solr/4_6_0/tutorial.html (for test purposes, I launched jetty with Solr WAR from example folder as “java -jar start”)

2-      Indexed sample data in exampledocs: user:~/solr/example/exampledocs$ java -jar post.jar *.xml

3-      Verify query works: http://localhost:8983/solr/collection1/select?q=solr&wt=json

4-      Install and configure the OpenAccess SDK by following the OpenAccess SDK Installation Guide.

5-      Configure the DataDirect OpenAccess sample for Apache Solr.

6-      Once complete, below is sample query using Interactive SQL (ODBC):

ISQL> SELECT * FROM SOLRDOCS WHERE QUERY = ‘solr’
NAME ID FEATURES QUERY
Solr, the Enterprise Search Server SOLR1000 {id=SOLR1000, name=Solr,
 the Enterprise Search Server, manu=Apache Software Foundation, cat=[software, s
earch], features=[Advanced Full-Text Search Capabilities using Lucene, Optimized
 for High Volume Web Traffic, Standards Based Open Interfaces - XML and HTTP, Co
mprehensive HTML Administration Interfaces, Scalability - Efficient Replication
to other Solr Search Servers, Flexible and Adaptable with XML configuration and
Schema, Good unicode support: héllo (hello with an accent over the e)], price=0.
0, price_c=0,USD, popularity=10, inStock=true, incubationdate_dt=Mon Jan 16 19:0
0:00 EST 2006, _version_=1457970426419347456} *
Rows selected = 1
Queries are optimized by indexed pseudo column named “QUERY”.  For example, equivalent SQL for this REST like query "http://localhost:8983/solr/collection1/select?q=solr&wt=json" is: SELECT * FROM SOLRDOCS WHERE QUERY = ‘solr’

Solr data returned to Microsoft Excel using ODBC

Solr data returned to Microsoft Excel using ODBC

What about importing data into Solr?

DataDirect has an amazing selection of JDBC drivers (relational data sources,SaaS sources such as Salesforce, Eloqua, Marketo, and aPaaS such as Rollbase, Force.com) if you're looking to import data into your Solr server using the DataImportHandler JDBC interface for data imports.

Are you a data architect, project manager, or java developer with questions?

Call us at 1-800-876-3101 to learn more about how DataDirect OpenAccess can get you connected using ODBC or JDBC to Apache SOLR, as well as any web service API or unstructured data source.  This is one of those projects everyone at a data driven organization will want their hands on.

Sumit Sakar

Sumit Sarkar

Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others. 

Read next SAS ODBC Access to Salesforce, SQL Server and Marketo
Comments
Comments are disabled in preview mode.