Integrate Apache Solr with Teiid

In this article, I will walk through an example, where I will show to move some data from CSV file into Apache Solr using Teiid. Then issue queries to Apache Solr, to get the contents of search using Teiid.  Once the search results are available in Teiid, you can further integrate/enhance this data with other data sources.  The prerequisites for this example are to have some familiarity with Teiid and Apache Solr. For the purposes of this article I am using Teiid 8.7.Alpha1 (or later) and Apache Solr 4.6.0


For the purpose of this example, I am using sample data from http://data.gov about baby names collected from social security numbers. The data is arranged by state. You can download data from http://catalog.data.gov/dataset/baby-names-from-social-security-card-applications-data-by-state-and-district-of-  A copy of the file is attached. This ZIP file contains may different files, one file for each state with names of the babies with following format.


STATE, MALE or FEMALE, YEAR, NAME, # of babies
KS,F,1910,Mary,251 
KS,F,1910,Helen,172 
KS,F,1910,Dorothy,144
...













 

Unzip the "namesbystate.zip" file into any directory. For the purposes of this article I will assume this is unzipped into "/babynames" directory on your local machine.

 

The goal is load this data into Solr using Teiid, and issue queries against it using SQL. Note that, I am showing full example, where data is gathered in source system (CSV files) and loading of that data into Solr. Typically you may already have data in a RDBMS, Web Service etc. that needs to be loaded in Solr for searching. In that case you can skip the Step 2

 

Step 1: Install and Configure Apache Solr

 

  • Download Apache Solr, and install it. It is assumed that you installed in "/solr" directory
  • Copy "/solr/example/solr/collection1" directory, save in some temp directory
  • Now rename "/solr/example/solr/collection1" to "/solr/example/solr/babynames"
  • Delete the contents of "/solr/example/solr/babynames/data" directory if any
  • Delete "/solr/example/solr/babynames/core.properties" file
  • Edit "/solr/example/solr/babynames/conf/schema.xml" and delete all elements under "<fields>" and "<copyFields>"
  • Now the below contents under "<fields>" contents.

 

<field indexed="true" name="_version_" stored="true" type="long"/>
<field indexed="true" name="_root_" stored="false" type="string"/>
<field indexed="true" name="id" stored="true" type="string" required="true" multiValued="false" />
<field indexed="true" name="state" stored="true" type="string" omitNorms="true"/>
<field indexed="true" name="name" required="true" stored="true" type="string"/>
<field indexed="true" name="gender" stored="true" type="string" omitNorms="true"/>
<field indexed="true" name="birthyear" stored="true" type="int"/>
<field indexed="true" name="totalcount" stored="true" type="int"/>
<field indexed="true" name="text" stored="false" type="text_general" multiValued="true"/>












 

  • Save and close above "schema.xml" file. These fields represent the schema for the data that is present in baby names CSV document from above. The schemal.xml file defined the internal document structure of Solr.
  • Now start the Apache Solr by executing following command

 

cd "/solr/example"
java -jar start.jar
















  • Once the Solr is started, using a web browser go to address "http://localhost:8983/solr"
  • On Left hand side navigation go to "Core Admin", if you see "unload" button click it
  • Now click "Add Core", and "babies" as core name and "babynames" as directory name as shown below.

solr_config.png


  • Now the Solr set up is complete.  Make sure there were no errors while doing this.
  • The above tasks, configured Solr with a "core", that can store/search documents.


Step 2: Building a VDB


Option 1: Using Teiid Designer (Skip to Option 2 for Dynamic VDB)

 

  • Install Teiid 8.7.Alpha1 (or Later)
  • Install Teiid Designer 8.3 or later
  • Start Teiid Designer, switch to "Teiid Designer" perspective.
  • Configure the Teiid Server, make sure you have made connection to Teiid Server.
  • Create a new "Teiid Model Project" and name it "BabyNames".
  • We need to create two separate source models, one for the CSV files, and another for the "Solr".
  • A pre-built sample project attached "designer_project.tar"

 

Build Flat File based Model

  • Choose "File -> import", then choose "Teiid Designer/File Source Flat >> Source and View Model". Follow directions in the article Connect to a Flat File using Teiid to create model based on files. Once you are done a view table like below.

csv_view.png

  • You can preview the data on right clicking on "babies" table, "Modeling -> Preview Data". Make sure this is successful.

 

Build Solr based Model

  • Choose "File -> import", then choose "Teiid Designer/Teiid Connection >> Source Model".

source_conn.png

 

  • Next ..
  • On the "Data the Source to use for Import" dialog box, click "New..", on the Create Data Source dialog should look like


create_ds.png


  • OK, then "Next >"
  • In this dialog box make sure your "translator" is set to "solr", and click "Next >"
  • The dialog will show DDL for the model, with table "babies", click up to "Finish". Currently I see (TEIIDDES-2017)

 

CREATE FOREIGN TABLE babies (
     id string OPTIONS (SEARCHABLE 'Searchable'),
     birthyear integer OPTIONS (SEARCHABLE 'Searchable'),
     name string OPTIONS (SEARCHABLE 'Searchable'),
     state string OPTIONS (SEARCHABLE 'Searchable'),
     gender string OPTIONS (SEARCHABLE 'Searchable'),
    totalcount integer OPTIONS (SEARCHABLE 'Searchable')
) OPTIONS (UPDATABLE TRUE);











 

  • When done you will see below table. Do a manual fix for [TEIIDDES-2018] by editing the data type property.

solr_table.png

  • Now build a VDB called "babynames" with all the models in this VDB. Deploy to server. You can right click deploy. Make sure you created data sources correctly.

 

Option 2: Building with Dynamic VDB (Skip this if you did Option 1)

 

  • Edit "standalone-teiid.xml", under "resource-adapter" section add the XML for creation of data sources.

 

Data Source for Flat File

                <resource-adapter id="file-ds">
                    <module slot="main" id="org.jboss.teiid.resource-adapter.file"/>
                    <transaction-support>NoTransaction</transaction-support>
                    <connection-definitions>
                        <connection-definition class-name="org.teiid.resource.adapter.file.FileManagedConnectionFactory" jndi-name="java:/file-ds" enabled="true" pool-name="file-ds">
                            <config-property name="ParentDirectory">
                                /babynames
                            </config-property>
                        </connection-definition>
                    </connection-definitions>
                </resource-adapter>










Data Source for Solr

                <resource-adapter id="solr-ds">
                    <module slot="main" id="org.jboss.teiid.resource-adapter.solr"/>
                    <transaction-support>XATransaction</transaction-support>
                    <connection-definitions>
                        <connection-definition class-name="org.teiid.resource.adapter.solr.SolrManagedConnectionFactory" jndi-name="java:/solr-ds" enabled="true" pool-name="solr-ds">
                            <config-property name="CoreName">
                                babies
                            </config-property>
                            <config-property name="url">
                                http://localhost:8983/solr/babies
                            </config-property>
                        </connection-definition>
                    </connection-definitions>
                </resource-adapter>










 

  • Start JBoss EAP + Teiid Server using command
./standalone.sh -c standalone-teiid.xml










 

  • Create the following dynamic VDB, name the file as "babynames-vdb.xml"
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="babynames" version="1">
    <model name="solr">
        <source name="solr" translator-name="solr" connection-jndi-name="java:/solr-ds"/>
    </model>
    <model name="file_source">
        <source name="file" translator-name="file" connection-jndi-name="java:/file-ds"/>
    </model>

    <model name = "file" visible = "true" type = "VIRTUAL" >
         <metadata type = "DDL"><![CDATA[
              CREATE VIEW babies (
                name varchar,
                state varchar,
                gender varchar,
                birthyear integer,
                totalcount integer
                ) AS
                  SELECT A.name, A.state, A.gender, A.birthyear,  A.totalcount FROM
           (EXEC file_source.getTextFiles('VA.TXT')) AS f,
                  TEXTTABLE(f.file COLUMNS state string, gender string, birthyear integer, name string, totalcount integer) AS A;
         ]]>
         </metadata>
    </model>
</vdb>










 

  • Using either admin-console or CLI, deploy the above VDB. See directions at Deploying VDBs

 

Step 3: Testing

 

Check the records in the File Source

select * from file.babies










 

Check records in Solr Source (Should be none right now)

select * from solr.babies










 

Dump records from File Source into Solr

 

insert into solr.babies (id, name, state, gender, birthyear, totalcount)
  select f.name, f.name, f.state, f.gender, f.birthyear, f.totalcount from file.babies as f










 

you should see

(131638 rows affected)
Elapsed Time:  0 hr, 1 min, 31 sec, 424 ms.










 

Now you can issue commands like

select * from solr.babies
select * from solr.babies where name = 'Mary'
select * from solr.babies where name like '*ary'
select * from solr.babies where birthyear > 1910 and birthyear < 1920










 

If you have other models like RDBMS, Salesforce, Web Service etc you can join the table between those sources and Solr. You can also issue SQL queries like INESRT/UPDATE/DELETE on Solr source to manage the documents in the store. For full documentation on Solr in Teiid read this documentation. Apache SOLR Translator

 

Hopefully this article gave how to integrate Solr data with Teiid. Let us know if you have any comments or suggestions in Teiid forums.

 

Ramesh..