{"id":84,"date":"2010-10-11T14:15:04","date_gmt":"2010-10-11T12:15:04","guid":{"rendered":"http:\/\/sematext.solr.pl\/?p=84"},"modified":"2020-11-10T14:15:43","modified_gmt":"2020-11-10T13:15:43","slug":"data-import-handler-how-to-import-data-from-sql-databases-part-1","status":"publish","type":"post","link":"https:\/\/solr.pl\/en\/2010\/10\/11\/data-import-handler-how-to-import-data-from-sql-databases-part-1\/","title":{"rendered":"Data Import Handler \u2013 How to import data from SQL databases (part 1)"},"content":{"rendered":"

In the article on how to import data (http:\/\/solr.pl\/2010\/09\/06\/solr-data-indexing-for-fun-and-profit\/?lang=en<\/a>) I mentioned the Data Import Handler<\/strong> (DIH). The main advantage of this method of data importing is no need for additional software development and the rapid integration of the data source. This second advantage, however, requires skill and practice. In this entry I’ll show you the basics of DIH integration with SQL data source.<\/p>\n\n\n\n\n\n

In the examples PostgeSQL database was used containing Polish Wikipedia data. Test solr instance is defined as the root of “wikipedia” and was available at:<\/p>\n

http:\/\/localhost:8983\/solr\/wikipedia\/admin\/<\/a><\/p>\n

Solrconfig.xml configuration<\/strong><\/p>\n

All what we need to do is add an additional request handler. Parameter: config determines configuration file which provides the definition of data sources.\n<\/p>\n

 <requestHandler name=\"\/dataimport\" class=\"org.apache.solr.handler.dataimport.DataImportHandler\">\n  <lst name=\"defaults\">\n   <str name=\"config\">db-data-config.xml<\/str>\n  <\/lst>\n<\/requestHandler><\/pre>\n
With this definition  we gain the ability to call an HTTP address that supports import  commands like:<\/p>\n
\/dataimport – in order  to obtain the current status
\n\/dataimport?command=reload-config –  in order to re-read the configuration
\n\/dataimport?command=full-import –  in order to start a full indexing
\n\/dataimport?command=delta-import –  in order to start an incremental indexing<\/p>\n
(for my configuration,  the full address is: http:\/\/localhost:8983\/solr\/wikipedia\/dataimport<\/a>)<\/p>\n
Above we see two  possibilities to import data: full and incremental import.<\/p>\n
The full import<\/strong> of  data rely on loading all data every time, while incremental import<\/strong> means  only adding the data that changed since the last indexing.  The full  import usually takes much longer from here a simple conclusion: if the  full import time is not an issue for use – it’s probably not worth to  bother  with the incremental indexing configuration, especially since it  imposes additional requirements on the structure of the data source.<\/p>\n
It is worth noting  that the full import of default begins with the removal of the existing  index. You  can avoid this behavior by adding the parameter: clean=false<\/em>.<\/p>\n
Configuring data sources<\/strong><\/p>\n
Configuration is to  define a queries allowing solr to download data to be indexed.  Configuration is included in the file specified in the definition of a  handler (in our case: db-data-config.xml). In this part of the article  we will define the full import. Then, in another part of  the article we extend it with the ability to import incrementally.<\/p>\n
Full import<\/strong>\n<\/p>\n
<dataConfig>\n  <dataSource driver=\"org.postgresql.Driver\"\n     url=\"jdbc:postgresql:\/\/localhost:5432\/wikipedia\"\n     user=\"wikipedia\"\n     password=\"secret\" \/>\n  <document>\n    <entity name=\"page\" query=\"SELECT page_id, page_title from page\">\n      <field column=\"page_id\" name=\"id\" \/>\n      <field column=\"page_title\" name=\"name\" \/>\n      <entity name=\"revision\" query=\"select rev_id from revision where rev_page=${page.page_id}\">\n        <entity name=\"pagecontent\" query=\"select old_text from pagecontent where old_id=${revision.rev_id}\">\n          <field column=\"old_text\" name=\"text\" \/>\n        <\/entity>\n      <\/entity>\n   <\/entity>\n  <\/document>\n<\/dataConfig><\/pre>\n
As you can see the  definition of the source consists of a description of how to link to  databases and a description of an indexed document. Import will proceed  with the following algorithm:<\/p>\n
    \n
  1.  The old index is  removed (if not used with parameter clean=false)<\/li>\n
  2.  After indexing  command is called Solr connects to the database.<\/li>\n
  3.  Database cursor is  defined using the database query specified in the argument “query” in  the main entity<\/li>\n
  4.  Some amount of data is  collected<\/li>\n
  5.  For each record  downloaded there are variables defined as <entity name>.<returned column>, so the returned value can be appealed in nested  entities<\/li>\n
  6.  Queries in nested  entities are called<\/li>\n
  7.  Entity may contain  fields definition. Thanks to that, Solr  is able to identify the mapping of a columns of the result to the field  of the document defined in the schema.xml<\/li>\n
  8.  Document created by the  values returned by the query is added to the index<\/li>\n
  9.  If the cursor has  another result we jump to step 4<\/li>\n
  10.  Solr stores data to a  dataimport.properties file, the data are approved (commit) and index  optimization is performed<\/li>\n<\/ol>\n
    After opening the page  http:\/\/localhost:8983\/solr\/wikipedia\/dataimport<\/a> the following answer  appeared:\n<\/p>\n
    <?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<response>\n  <lst name=\"responseHeader\">\n    <int name=\"status\">0<\/int>\n    <int name=\"QTime\">0<\/int>\n  <\/lst>\n  <lst name=\"initArgs\">\n    <lst name=\"defaults\">\n      <str name=\"config\">db-data-config.xml<\/str>\n    <\/lst>\n  <\/lst>\n  <str name=\"status\">idle<\/str>\n  <str name=\"importResponse\"\/>\n  <lst name=\"statusMessages\"\/>\n  <str name=\"WARNING\">This response format is experimental.  It is likely to change in the future.<\/str>\n<\/response><\/pre>\n
    What is worth seeing  is entry: status: idle. This means that our  importer is ready to work. In another case (eg  error in the  XML configuration file), we get a description of the exception.  Unfortunately,  at this stage we are not able to detect such errors as improper  connection to the database or lack of JDBC driver.<\/p>\n
    So we go to page:  http:\/\/localhost:8983\/solr\/wikipedia\/dataimport?command=full-import<\/a><\/p>\n
    What we should get,  is similar to the above XML. However, after re-entering the page:  http:\/\/localhost:8983\/solr\/wikipedia\/dataimport<\/a> we get a different  result.\n<\/p>\n
    <?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<response>\n  <lst name=\"responseHeader\">\n    <int name=\"status\">0<\/int>\n    <int name=\"QTime\">0<\/int>\n  <\/lst>\n  <lst name=\"initArgs\">\n    <lst name=\"defaults\">\n      <str name=\"config\">db-data-config.xml<\/str>\n    <\/lst>\n  <\/lst>\n  <str name=\"status\">busy<\/str>\n  <str name=\"importResponse\">A command is still running...<\/str>\n  <lst name=\"statusMessages\">\n    <str name=\"Time Elapsed\">0:1:15.460<\/str>\n    <str name=\"Total Requests made to DataSource\">39547<\/str>\n    <str name=\"Total Rows Fetched\">59319<\/str>\n    <str name=\"Total Documents Processed\">19772<\/str>\n    <str name=\"Total Documents Skipped\">0<\/str>\n    <str name=\"Full Dump Started\">2010-10-03 14:28:00<\/str>\n  <\/lst>\n  <str name=\"WARNING\">This response format is experimental.  It is likely to change in the future.<\/str>\n<\/response><\/pre>\n
    So the importer is  working.<\/p>\n
    After some time,  depending on the amount of indexed data and the speed of your computer  you will receive:\n<\/p>\n
    <?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<response>\n  <lst name=\"responseHeader\">\n    <int name=\"status\">0<\/int>\n    <int name=\"QTime\">0<\/int>\n  <\/lst>\n  <lst name=\"initArgs\">\n    <lst name=\"defaults\">\n      <str name=\"config\">db-data-config.xml<\/str>\n    <\/lst>\n  <\/lst>\n  <str name=\"status\">idle<\/str>\n  <str name=\"importResponse\"\/>\n  <lst name=\"statusMessages\">\n    <str name=\"Total Requests made to DataSource\">2118645<\/str>\n    <str name=\"Total Rows Fetched\">3177966<\/str>\n    <str name=\"Total Documents Skipped\">0<\/str>\n    <str name=\"Full Dump Started\">2010-10-03 14:28:00<\/str>\n    <str name=\"\">Indexing completed. Added\/Updated: 1059322 documents. Deleted 0 documents.<\/str>\n    <str name=\"Committed\">2010-10-03 14:55:20<\/str>\n    <str name=\"Optimized\">2010-10-03 14:55:20<\/str>\n    <str name=\"Total Documents Processed\">1059322<\/str>\n    <str name=\"Time taken \">0:27:20.325<\/str>\n  <\/lst>\n  <str name=\"WARNING\">This response format is experimental.  It is likely to change in the future.<\/str>\n<\/response><\/pre>\n
    As you can see indexing  was successful.<\/p>\n
    In the next section we  will try to add the ability to import incrementally.<\/p>","protected":false},"excerpt":{"rendered":"
    In the article on how to import data (http:\/\/solr.pl\/2010\/09\/06\/solr-data-indexing-for-fun-and-profit\/?lang=en) I mentioned the Data Import Handler (DIH). The main advantage of this method of data importing is no need for additional software development and the rapid integration of the data source.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[27],"tags":[],"class_list":["post-84","post","type-post","status-publish","format-standard","hentry","category-solr-en"],"_links":{"self":[{"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/posts\/84","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/comments?post=84"}],"version-history":[{"count":1,"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/posts\/84\/revisions"}],"predecessor-version":[{"id":85,"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/posts\/84\/revisions\/85"}],"wp:attachment":[{"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/media?parent=84"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/categories?post=84"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/solr.pl\/en\/wp-json\/wp\/v2\/tags?post=84"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}