title: Easy indexing of data into Solr with ETL operations author: admin type: post date: 2016-08-22T10:02:08+00:00 url: /easy-indexing-of-data-into-solr/ sf_thumbnail_type:
There are exciting new features coming in Hue 3.11 week and later in CDH 5.9 this Fall. One of which is Hue’s brand new tool to create Apache Solr Collections from file data. Hue’s Solr dashboards are great for visualizing and learning more about your data so being able to easily load data into Solr collections can be really useful.
In the past, indexing data into Solr has been quite difficult. The task involved writing a Solr schema and a morphlines file then submitting a job to YARN to do the indexing. Often times getting this correct for non trivial imports could take a few days of work. Now with Hue’s new feature you can start your YARN indexing job in minutes. This tutorial offers a step by step guide on how to do it.
{{< youtube uS0MpzW0ep8 >}}
First you'll need to have a running Solr cluster that Hue is configured with.
Next you'll need to install these required libraries. To do so place them in a directory somewhere on HDFS and set the path for _config_indexer_libspath under indexer in the Hue ini to match by default, the _config_indexer_libspath value is set to _/tmp/smart_indexerlib. Additionally under indexer in the Hue ini you’ll need to set _enable_newindexer to true.
[indexer]
\# Flag to turn on the morphline based Solr indexer.
enable_new_indexer=false
\# Oozie workspace template for indexing.
\## config_indexer_libs_path=/tmp/smart_indexer_lib
Note:
If using Cloudera Manager, check how to add properties in Hue.ini safety valve and put the abov
We are going to create a new Solr collection from business review data. To start let’s put the data file somewhere on HDFS so we can access it.
Now we can get started! Under the search tab in the navbar select Index.
We'll pick a name for our new collection and select our reviews data file from HDFS. Then we'll click next.
On this tab we can see all the fields the indexer has picked up from the file. Note that Hue has also made an educated guess on the field type. Generally, Hue does a good job inferring data type. However, we should do a quick check to confirm that the field types look correct.
For our data we're going to perform 4 operations to make a very searchable Solr Collection.
Convert Date
This operation is implicit. By setting the field type to date we inform Hue that we want to convert this date to a Solr Date. Hue can convert most standard date formats automatically. If we had a unique date format we would have to define it for Hue by explicitly using the convert date operation.
Before we index, let’s make sure everything looks good with a quick scan of the preview. This can be handy to avoid any silly typos or anything like that.
Now that we've defined our ETL Hue can do the rest. Click index and wait for Hue to index our data. At the bottom of this screen we can see a progress bar of the process. Yellow means our data is currently being indexed and green means it's done. Feel free to close this window. The indexing will continue on your cluster.
Once our data has been indexed into a Solr Collection we have access to all of Hue's search features and can make a nice analytics dashboard like this one for our data.
The indexer libs path is where all required libraries for indexing should be. If you’d prefer you can assemble this directory yourself. There are three main components to the libs directory:
On top of the ease of use, this is where the real power of Hue's new indexer lies. Heavily leveraging Morphlines, operations let us easily transform our data into a more searchable format. Before we add some to our fields let's quickly go over the operations that the indexer offers.
Operation list:
<li style="font-weight: 400;">
<span style="font-weight: 400;">With the split operation we can take a field and produce new fields by splitting the original field on a delimiting character</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Input: “2.1,-3.5,7.1”</span><span style="font-weight: 400;"><br /> </span><span style="font-weight: 400;">Split Character: “,”</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Outputs 3 fields:</span><span style="font-weight: 400;"><br /> </span><span style="font-weight: 400;">Field 1: “2.1”<br /> </span>Field 2: “-3.5”<br /> Field 3: “7.1”
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Grok is an extension of Regex and can be used to match specific subsections of a field and pull them out. You can read more about the Grok syntax </span><a href="https://www.elastic.co/guide/en/logstash/current/plugins-filters-grok.html"><span style="font-weight: 400;">here</span></a>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Input: “Winnipeg (Canada)”</span><span style="font-weight: 400;"><br /> </span><span style="font-weight: 400;">Regular Expression: “\w+ \((?<country>\w+)\)”</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Outputs 1 field:</span><span style="font-weight: 400;"><br /> </span><span style="font-weight: 400;"><span style="font-weight: 400;">country: “Canada”</span></span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Generally the indexer converts dates automatically to Solr's native format. However, if you have a very obscure date format you can define it using a </span><a href="http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html"><span style="font-weight: 400;">SimpleDateFormat</span></a><span style="font-weight: 400;"> here to ensure it is converted correctly</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Input: “Aug (2016) 24”</span><span style="font-weight: 400;"><br /> </span><span style="font-weight: 400;">Date Format: “MMM (YYYY) dd”</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Output: </span><span style="font-weight: 400;"><span style="font-weight: 400;">In place replacement: “2016-08-24T00:00:00Z”</span></span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Extract URI Components lets you grab specific parts of a URI and put it in its own field without having to write the Regex yourself.</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">The following components can be extracted:</span> <ul>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Authority</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Fragment</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Host</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Path</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Port</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Query</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Scheme</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Scheme Specific Path</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">User Info</span>
</li>
</ul>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Input: “</span><a href="https://www.google.com/#q=cloudera+hue"><span style="font-weight: 400;">https://www.google.com/#q=cloudera+hue</span></a><span style="font-weight: 400;">”</span><span style="font-weight: 400;"><br /> </span><span style="font-weight: 400;">Selected: Host</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;"><span style="font-weight: 400;">Output: “www.google.com”</span></span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Geo IP performs a Maxmind GeoIP lookup to match public IP addresses with a location.</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">The following location information can be extracted with this operation:</span> <ul>
<li style="font-weight: 400;">
<span style="font-weight: 400;">ISO Code</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Country Name</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Subdivision Names</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Subdivision ISO Code</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">City Name</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Postal Code</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Latitude</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Longitude</span>
</li>
</ul>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Input: “74.217.76.101”</span><span style="font-weight: 400;"><br /> </span><span style="font-weight: 400;">Selected: ISO Code, City Name, Latitude, Longitude</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;"><span style="font-weight: 400;">Output: “US”, “Palo Alto”, “37.3762”, “-122.1826”</span></span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Translate will take given hard coded values and replace them with set values in place.</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Input: “Five Stars”</span><span style="font-weight: 400;"><br /> </span><span style="font-weight: 400;"><br /> </span><span style="font-weight: 400;">Mapping:</span><span style="font-weight: 400;"><br /> </span><span style="font-weight: 400;">“Five Stars” -> “5”</span><span style="font-weight: 400;"><br /> </span><span style="font-weight: 400;">“Four Stars” -> “4”</span><span style="font-weight: 400;"><br /> </span><span style="font-weight: 400;">“Three Stars” -> “3”</span><span style="font-weight: 400;"><br /> </span><span style="font-weight: 400;">“Two Stars” -> “2”</span><span style="font-weight: 400;"><br /> </span><span style="font-weight: 400;">“One Star” -> “1”</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Output: </span><span style="font-weight: 400;"><span style="font-weight: 400;">In place Replacement: “5”</span></span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Find and Replace takes a Grok string as the find argument and will replace all matches in the field with the specified replace value in place.</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Input: “Hello World”</span><span style="font-weight: 400;"><br /> </span><span style="font-weight: 400;">Find: “(?<word>\b\w+\b)”</span><span style="font-weight: 400;"><br /> </span><span style="font-weight: 400;">Replace: “"${word}!"</span>
</li>
<li style="font-weight: 400;">
<span style="font-weight: 400;">Output: </span><span style="font-weight: 400;">In place replacement: “Hello! World!”</span>
</li>
Hue successfully recognized our file as a CSV. The indexer currently supports the following file types:
Beyond files, metastore tables and Hive SQL queries are also supported. Read more about these in an upcoming 3.11 blog post.
**
During the indexing process records can be dropped if they fail to match the Solr Schema. (e.g., trying to place a string into a long field). If some of your records are missing and you are unsure why you can always check the mapper log for the indexing job to get a better idea on what’s going on.****