Playing with Apache Hive and SOLR

As described in a previous post, Apache SOLR can perform very well to provide low latency analytics. Data logs can be pre-aggregated using Hive and then synced to SOLR. To this end, we developed a simple Storage Handler for SOLR so that data can be read and written to SOLR transparently using an external table.

We will show in this post how to install our SOLR storage handler and then run a simple example where we sync some data from Hive to SOLR.

We suppose in this post that you have Hive (see our previous post on installing Hive) and SOLR installed (see our post on SOLR Cloud).

SOLR adapter installation

To install:

$ git clone http://github.org/chimpler/hive-solr
$ cd hive-solr
$ mvn package
$ cp target/hive-solr-0.0.1-SNAPSHOT-jar-with-dependencies.jar $HIVE_HOME/lib

SOLR example

Let’s use the SOLR schema provided in a previous post at:

$ git clone http://github.com/chimpler/blog-solr-cloud-example

Copy the schema from the directory /etc/solr/core0 to your solr configuration and restart your webserver:

<?xml version="1.0" ?>
<schema name="segment_overlap" version="1.1">
  <types>
   <fieldtype name="string"  class="solr.StrField" sortMissingLast="true"
              omitNorms="true"/>
   <fieldType name="int" class="solr.TrieIntField" precisionStep="0"
              positionIncrementGap="0"/>
   <fieldType name="float" class="solr.TrieFloatField" precisionStep="0"
              positionIncrementGap="0"/>
   <fieldType name="long" class="solr.TrieLongField" precisionStep="0"
              positionIncrementGap="0"/>
   <fieldType name="double" class="solr.TrieDoubleField" precisionStep="0"
              positionIncrementGap="0"/>
  </types>

 <fields>
   <field name="id" type="int" indexed="true" stored="true" required="true" />
   <field name="_version_" type="long" indexed="true" stored="true"
          required="true" />
   <field name="item_id" type="int" indexed="true" stored="true" required="true" />
   <field name="name" type="string" indexed="true" stored="true" required="true" />
   <field name="year" type="int" indexed="true" stored="true" required="true" />
   <field name="month" type="int" indexed="true" stored="true" required="true" />
   <field name="shipping_method" type="int" indexed="true" stored="true"
          required="true" />
   <field name="us_sold" type="int" indexed="true" stored="true" required="true" />
   <field name="ca_sold" type="int" indexed="true" stored="true" required="true" />
   <field name="fr_sold" type="int" indexed="true" stored="true" required="true" />
   <field name="uk_sold" type="int" indexed="true" stored="true" required="true" />
 </fields>

 <!-- field to use to determine and enforce document uniqueness. -->
 <uniqueKey>id</uniqueKey>

 <!-- field for the QueryParser to use when an explicit fieldname is absent -->
 <defaultSearchField>name</defaultSearchField>

 <!-- SolrQueryParser configuration: defaultOperator="AND|OR" -->
 <solrQueryParser defaultOperator="AND"/>
</schema>

Now let’s generate some data that we want to import in SOLR through Hive.

In the directory example:

$ python gen-core0-data.py | tail -n +2 > data.csv

We suppose that the core URL is: http://localhost:8983/solr/core0

Let’s import the data into Hive:

$ hive
hive> create table source (
    id INT,
    item_id INT,
    name STRING,
    year INT,
    month INT,
    shipping_method INT,
    us_sold INT,
    ca_sold INT,
    fr_sold INT,
    uk_sold INT)
    ROW FORMAT
    DELIMITED FIELDS TERMINATED BY ',';
OK

hive> LOAD DATA LOCAL INPATH 'data.csv' OVERWRITE INTO TABLE source;

Let’s create a SOLR external table solr_items and do a copy from the table source to solr_items:

hive> create external table solr_items (
    id INT,
    item_id INT,
    name STRING,
    year INT,
    month INT,
    shipping_method INT,
    us_sold INT,
    ca_sold INT,
    fr_sold INT,
    uk_sold INT
) stored by "com.chimpler.hive.solr.SolrStorageHandler"
with serdeproperties ("solr.column.mapping"="id,item_id,name,year,month,shipping_method,us_sold,ca_sold,fr_sold,uk_sold")
tblproperties ("solr.url" = "http://localhost:8983/solr/core0");
OK

hive> insert into table solr_items select * from source;
OK

You can optionally specify the buffer size to use when data is loaded or stored in SOLR by passing the following parameters in tblproperties:

• solr.buffer.input.rows: number of rows to load in bulk when loading rows from SOLR. For instance: “solr.buffer.input.rows”=”10000” (default is 100,000)
• solr.buffer.output.rows: number of rows to store in bulk when storing rows into SOLR. For instance: “solr.buffer.output.rows”=”10000” (default is 100,000)

Now let’s check if the data is in SOLR:

$ curl http://localhost:8983/solr/core0/select?wt=csv -d 'q=*:*'
id,_version_,shipping_method,fr_sold,uk_sold,ca_sold,item_id,name,us_sold,month,year
-48009990,1428822836035715072,0,1246,5050,796,0,item 0,376,1,1990
-48009989,1428822836035715073,1,6691,5718,2874,0,item 0,2029,1,1990
-48009988,1428822836035715074,2,5816,5114,5387,0,item 0,4927,1,1990
-48009987,1428822836035715075,3,8234,278,7206,0,item 0,1770,1,1990