Importing Data with Hive

Fusion ships with a Serializer/Deserializer (SerDe) for Hive, included in the distribution as lucidworks-hive-serde-{connectorVersion}.jar in fusion/3.1.x/apps/connectors/resources/lucid.hadoop/jobs.


  • Index Hive table data to Solr.

  • Read Solr index data to a Hive table.

  • Kerberos support for securing communication between Hive and Solr.

  • As of v2.2.4 of the SerDe, integration with Lucidworks Fusion is supported.

    • Fusion’s index pipelines can be used to index data to Fusion.

    • Fusion’s query pipelines can be used to query Fusion’s Solr instance for data to insert into a Hive table.

Install the SerDe Jar to Hive

In order for Hive to work with Solr, the Hive SerDe jar must be added as a plugin to Hive.

From a Hive prompt, use the ADD JAR command and reference the path and filename of the SerDe jar for your Hive version.

   hive> ADD JAR lucidworks-hive-serde-2.2.6.jar;

This can also be done in your Hive command to create the table, as in the example below.

Indexing Data to Fusion

If you use Lucidworks Fusion, you can index data from Hive to Solr via Fusion’s index pipelines. These pipelines allow you several options for further transforming your data.


If you are using Fusion v3.0.x, you already have the Hive SerDe in Fusion’s ./apps/connectors/resources/lucid.hadoop/jobs directory. The SerDe jar that supports Fusion is v2.2.4 or higher. This was released with Fusion 3.0.

If you are using Fusion 3.1.x, you will need to download the Hive SerDe from Choose the proper Hadoop distribution and the resulting .zip file will include the Hive SerDe.

A 2.2.4 or higher jar built from this repository will also work with Fusion 2.4.x releases.

This is an example Hive command to create an external table to index documents in Fusion and to query the table later.

hive> CREATE EXTERNAL TABLE fusion (id string, field1 string, field2 int)
      STORED BY 'com.lucidworks.hadoop.hive.FusionStorageHandler'
      LOCATION '/tmp/fusion'
      TBLPROPERTIES('fusion.endpoints' = 'http://localhost:8764/api/apollo/index-pipelines/<pipeline>/collections/<collection>/index',
                    '' = 'false',
                    'fusion.buffer.timeoutms' = '1000',
                    'fusion.batchSize' = '500',
                    'fusion.realm' = 'KERBEROS',
                    'fusion.user' = 'fusion-indexer@FUSIONSERVER.COM',
                    '' = '/path/to/JAAS/file',
                    'fusion.jaas.appname' = 'FusionClient',
                    'fusion.query.endpoints' = 'http://localhost:8764/api/apollo/query-pipelines/pipeline-id/collections/collection-id',
                    'fusion.query' = '*:*');

In this example, we have created an external table named "fusion", and defined a custom storage handler (STORED BY 'com.lucidworks.hadoop.hive.FusionStorageHandler').

The LOCATION indicates the location in HDFS where the table data will be stored. In this example, we have chosen to use /tmp/fusion.

In the section TBLPROPERTIES, we define several properties for Fusion so the data can be indexed to the right Fusion installation and collection:


The full URL to the index pipeline in Fusion. The URL should include the pipeline name and the collection data will be indexed to.

If true, when an error is encountered, such as if a row could not be parsed, indexing will stop. This is false by default.


The amount of time, in milliseconds, to buffer documents before sending them to Fusion. The default is 1000. Documents will be sent to Fusion when either this value or fusion.batchSize is met.


The number of documents to batch before sending the batch to Fusion. The default is 500. Documents will be sent to Fusion when either this value or fusion.buffer.timeoutms is met.


This is used with fusion.user and fusion.password to authenticate to Fusion for indexing data. Two options are supported, KERBEROS or NATIVE.

Kerberos authentication is supported with the additional definition of a JAAS file. The properties and fusion.jaas.appname are used to define the location of the JAAS file and the section of the file to use.

Native authentication uses a Fusion-defined username and password. This user must exist in Fusion, and have the proper permissions to index documents.


The Fusion username or Kerberos principal to use for authentication to Fusion. If a Fusion username is used ('fusion.realm' = 'NATIVE'), the fusion.password must also be supplied.


This property is not shown in the example above. The password for the fusion.user when the fusion.realm is NATIVE.

This property defines the path to a JAAS file that contains a service principal and keytab location for a user who is authorized to read from and write to Fusion and Hive.

The JAAS configuration file must be copied to the same path on every node where a Node Manager is running (i.e., every node where map/reduce tasks are executed). Here is a sample section of a JAAS file:

Client { (1) required
  keyTab="/data/fusion-indexer.keytab" (2)
  principal="fusion-indexer@FUSIONSERVER.COM"; (3)
  1. The name of this section of the JAAS file. This name will be used with the fusion.jaas.appname parameter.

  2. The location of the keytab file.

  3. The service principal name. This should be a different principal than the one used for Fusion, but must have access to both Fusion and Hive. This name is used with the fusion.user parameter described above.


Used only when indexing to or reading from Fusion when it is secured with Kerberos.

This property provides the name of the section in the JAAS file that includes the correct service principal and keytab path.


The full URL to a query pipeline in Fusion. The URL should include the pipeline name and the collection data will be read from. You should also specify the request handler to be used.

If you do not intend to query your Fusion data from Hive, you can skip this parameter.


The query to run in Fusion to select records to be read into Hive. This is *:* by default, which selects all records in the index.

If you do not intend to query your Fusion data from Hive, you can skip this parameter.

Query and Insert Data to Hive

Once the table is configured, any syntactically correct Hive query will be able to query the index.

For example, to select three fields named "id", "field1", and "field2" from the "solr" table, you would use a query such as:

hive> SELECT id, field1, field2 FROM solr;

Replace the table name as appropriate to use this example with your data.

To join data from tables, you can make a request such as:

hive> SELECT id, field1, field2 FROM solr left
      JOIN sometable right
      WHERE =;

And finally, to insert data to a table, simply use the Solr table as the target for the Hive INSERT statement, such as:

hive> INSERT INTO solr
      SELECT id, field1, field2 FROM sometable;

Example Indexing Hive to Solr

Solr includes a small number of sample documents for use when getting started. One of these is a CSV file containing book metadata. This file is found in your Solr installation, at $SOLR_HOME/example/exampledocs/books.csv.

Using the sample books.csv file, we can see a detailed example of creating a table, loading data to it, and indexing that data to Solr.


LOAD DATA LOCAL INPATH '/solr/example/exampledocs/books.csv' OVERWRITE INTO TABLE books; (2)

ADD JAR {packageUser}-hive-serde-{connectorVersion}.jar; (3)

CREATE EXTERNAL TABLE solr (id STRING, cat_s STRING, title_s STRING, price_f FLOAT, in_stock_b BOOLEAN, author_s STRING, series_s STRING, seq_i INT, genre_s STRING) (4)
     STORED BY 'com.lucidworks.hadoop.hive.LWStorageHandler' (5)
     LOCATION '/tmp/solr' (6)
     TBLPROPERTIES('solr.zkhost' = 'zknode1:2181,zknode2:2181,zknode3:2181/solr',
                   'solr.collection' = 'gettingstarted',
                   'solr.query' = '*:*'), (7)
                   'lww.jaas.file' = '/data/jaas-client.conf'; (8)

  1. Define the table books, and provide the field names and field types that will make up the table.

  2. Load the data from the books.csv file.

  3. Add the lucidworks-hive-serde-2.2.6.jar file to Hive. Note the jar name shown here omits the version information which will be included in the jar file you have. If you are using Hive 0.13, you must also use a jar specifically built for 0.13.

  4. Create an external table named solr, and provide the field names and field types that will make up the table. These will be the same field names as in your local Hive table, so we can index all of the same data to Solr.

  5. Define the custom storage handler provided by the lucidworks-hive-serde-2.2.6.jar.

  6. Define storage location in HDFS.

  7. The query to run in Solr to read records from Solr for use in Hive.

  8. Define the location of Solr (or ZooKeeper if using SolrCloud), the collection in Solr to index the data to, and the query to use when reading the table. This example also refers to a JAAS configuration file that will be used to authenticate to the Kerberized Solr cluster.