SOLR dataimport from SQL server

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

SOLR dataimport from SQL server

Nouman Zaheer

Hi Guys

I am not sure if this is correct email list to ask this question but I am facing a waired issue where I am being able to setup the SQL dataimport but only first column (dp_id  in this case as below) is being indexed in the indexes as when query anyother column it saying not found, if anyone has any insight about it , I will appreciate any help,   configuration below (using latest version SOLR 8.4.1 with OpenJDK  with mssql-jdbc-8.2.0.jre13.jar , with java version javac 13.0.2)

Below is  DEBUG with verbose here is the info and I can see columns are being selected from sql server

{

  "responseHeader": {

    "status": 0,

    "QTime": 3063

  },

  "initArgs": [

    "defaults",

    [

      "config",

      "data-config.xml"

    ]

  ],

  "command": "full-import",

  "mode": "debug",

  "documents": [

    {

      "id": "DVP000002",

      "_version_": 1661420746416586800,

      "_root_": "DVP000002"

    },

    {

      "id": "DVP000003",

      "_version_": 1661420746417635300,

      "_root_": "DVP000003"

    },

    {

      "id": "FSE001080",

      "_version_": 1661420746417635300,

      "_root_": "FSE001080"

    },

    {

      "id": "FSE001081",

      "_version_": 1661420746417635300,

      "_root_": "FSE001081"

    },

    {

      "id": "FSE001082",

      "_version_": 1661420746417635300,

      "_root_": "FSE001082"

    },

    {

      "id": "FSE001083",

      "_version_": 1661420746417635300,

      "_root_": "FSE001083"

    },

    {

      "id": "FSE001084",

      "_version_": 1661420746417635300,

      "_root_": "FSE001084"

    },

    {

      "id": "FSE001085",

      "_version_": 1661420746417635300,

      "_root_": "FSE001085"

    },

    {

      "id": "FSE001086",

      "_version_": 1661420746417635300,

      "_root_": "FSE001086"

    },

    {

      "id": "OMM000002",

      "_version_": 1661420746417635300,

      "_root_": "OMM000002"

    }

  ],

  "verbose-output": [

    "entity:RPS_Table",

    [

      "document#1",

      [

        "query",

        "select dp_id,dp_name,dp_street1,dp_city,dp_state,dp_ctry_co from MyTable",

        "time-taken",

        "0:0:0.228",

        null,

        "----------- row #1-------------",

        "dp_name",

        "HAP JANG GANG 6",

        "dp_state",

        null,

        "dp_city",

        null,

        "dp_ctry_co",

        "XX",

        "dp_id",

       "DVP000002",

        "dp_street1",

        null,

        null,

        "---------------------------------------------"

      ],

      "document#2",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "JI SONG 8",

        "dp_state",

        null,

        "dp_city",

        null,

        "dp_ctry_co",

        "XX",

        "dp_id",

        "DVP000003",

        "dp_street1",

        null,

        null,

        "---------------------------------------------"

      ],

      "document#3",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "BEKTAS, Halis",

        "dp_state",

        " ",

        "dp_city",

        "",

        "dp_ctry_co",

        "CH",

        "dp_id",

        "FSE001080",

        "dp_street1",

        "",

        null,

        "---------------------------------------------"

      ],

      "document#4",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "HOLLEBRAND, Alexander  AKA HOLLEBRAND, Sander",

        "dp_state",

        null,

        "dp_city",

        null,

        "dp_ctry_co",

        "XX",

        "dp_id",

        "FSE001081",

        "dp_street1",

        null,

        null,

        "---------------------------------------------"

      ],

      "document#5",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "VAN MAZIJK, Paul",

        "dp_state",

        "",

        "dp_city",

        "",

        "dp_ctry_co",

        "NL",

        "dp_id",

        "FSE001082",

        "dp_street1",

        "",

        null,

        "---------------------------------------------"

      ],

      "document#6",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "BLUEMARINE SA AKA BLUE MARINE SHIPPING AGENCY S.A. AKA BLUEMARINE AG  AKA BLUEMARINE LTD",

        "dp_state",

        " ",

        "dp_city",

        "Baar",

        "dp_ctry_co",

        "CH",

        "dp_id",

        "FSE001083",

        "dp_street1",

        "Lindenstrasse 2",

        null,

        "---------------------------------------------"

      ],

      "document#7",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "RIXO INTERNATIONAL TRADING LTD.",

        "dp_state",

        " ",

        "dp_city",

        "Baar",

        "dp_ctry_co",

        "CH",

        "dp_id",

        "FSE001084",

        "dp_street1",

        "Lindenstrasse 2",

        null,

       "---------------------------------------------"

      ],

      "document#8",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "STAROIL B.V.  AKA STAROIL S.A.",

        "dp_state",

        "",

        "dp_city",

        "",

        "dp_ctry_co",

        "",

        "dp_id",

        "FSE001085",

        "dp_street1",

        "",

        null,

        "---------------------------------------------"

      ],

      "document#9",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "KAYAKIRAN, Evren",

        "dp_state",

        " ",

        "dp_city",

        "",

        "dp_ctry_co",

        "TR",

        "dp_id",

        "FSE001086",

        "dp_street1",

        "",

       null,

        "---------------------------------------------"

      ],

      "document#10",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "CHOL RYONG  AKA RYONG GUN BONG",

        "dp_state",

        " ",

        "dp_city",

        "",

        "dp_ctry_co",

        "XX",

        "dp_id",

        "OMM000002",

        "dp_street1",

        "",

        null,

        "---------------------------------------------"

      ],

      "document#11",

      []

    ]

  ],

  "status": "idle",

  "importResponse": "",

  "statusMessages": {

    "Total Requests made to DataSource": "1",

    "Total Rows Fetched": "10",

    "Total Documents Processed": "10",

    "Total Documents Skipped": "0",

    "Full Dump Started": "2020-03-17 14:11:34",

    "": "Indexing completed. Added/Updated: 10 documents. Deleted 0 documents.",

    "Committed": "2020-03-17 14:11:34",

    "Time taken": "0:0:0.311"

  }

}

 

Solrconfig.xml

..

..

<lib dir="${solr.install.dir:../../../..}/dist/" regex="solr-dataimporthandler-\d.*\.jar" /> 

  <lib dir="${solr.install.dir:../../../..}/dist/" regex="solr-dataimporthandler-extras-\d.*\.jar" />

  <requestHandler name="/dataimport" class="org.apache.solr.handler.dataimport.DataImportHandler"> 

             <lst name="defaults"> 

                  <str name="config">data-config.xml</str> 

             </lst> 

  </requestHandler>

 

 

data-config.xml

<dataConfig>  

  <dataSource type="JdbcDataSource"   

     driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"   

     url="jdbc:sqlserver://servername;databaseName=mydatabasename;integratedSecurity=true"   

    />  

  <document>  

   <entity name="RPS_Table" pk="id" query="select dp_id,dp_name,dp_street1,dp_city,dp_state,dp_ctry_co from Mytable">  

     <field column="dp_id" name="id"/>

     <field column="dp_name" name="name"/>    

     <field column="dp_street1" name="street1"/>   

     <field column="dp_city" name="city"/> 

                 <field column="dp_state" name="state"/>  

                 <field column="dp_ctry_co" name="ctry"/> 

     </entity>  

  </document>  

  </dataConfig>

 

 

 

Nouman Zaheer

Software Engineer

U.S. & Canada: +1.877.328.7866 x365

International: +1.716.881.2590 x365

[hidden email]

https://www.visualcompliance.com

cid:image001.png@01D52D98.84265040

 

CONFIDENTIALITY NOTE:
This electronic transmission, including all attachments, is directed in confidence solely to the person(s) to whom it is addressed, or an authorized recipient, and may not otherwise be distributed, copied or disclosed. The contents of the transmission may also be subject to intellectual property rights and all such rights are expressly claimed and are not waived. If you have received this transmission in error, please notify the sender immediately by return electronic transmission and then immediately delete this transmission, including all attachments, without copying, distributing or disclosing same.

 

Reply | Threaded
Open this post in threaded view
|

Re: SOLR dataimport from SQL server

david.w.smiley@gmail.com
Please ask on "solr-user" instead.  The "dev" list is for internal development.

~ David Smiley
Apache Lucene/Solr Search Developer


On Tue, Mar 17, 2020 at 12:10 PM Nouman Zaheer <[hidden email]> wrote:

Hi Guys

I am not sure if this is correct email list to ask this question but I am facing a waired issue where I am being able to setup the SQL dataimport but only first column (dp_id  in this case as below) is being indexed in the indexes as when query anyother column it saying not found, if anyone has any insight about it , I will appreciate any help,   configuration below (using latest version SOLR 8.4.1 with OpenJDK  with mssql-jdbc-8.2.0.jre13.jar , with java version javac 13.0.2)

Below is  DEBUG with verbose here is the info and I can see columns are being selected from sql server

{

  "responseHeader": {

    "status": 0,

    "QTime": 3063

  },

  "initArgs": [

    "defaults",

    [

      "config",

      "data-config.xml"

    ]

  ],

  "command": "full-import",

  "mode": "debug",

  "documents": [

    {

      "id": "DVP000002",

      "_version_": 1661420746416586800,

      "_root_": "DVP000002"

    },

    {

      "id": "DVP000003",

      "_version_": 1661420746417635300,

      "_root_": "DVP000003"

    },

    {

      "id": "FSE001080",

      "_version_": 1661420746417635300,

      "_root_": "FSE001080"

    },

    {

      "id": "FSE001081",

      "_version_": 1661420746417635300,

      "_root_": "FSE001081"

    },

    {

      "id": "FSE001082",

      "_version_": 1661420746417635300,

      "_root_": "FSE001082"

    },

    {

      "id": "FSE001083",

      "_version_": 1661420746417635300,

      "_root_": "FSE001083"

    },

    {

      "id": "FSE001084",

      "_version_": 1661420746417635300,

      "_root_": "FSE001084"

    },

    {

      "id": "FSE001085",

      "_version_": 1661420746417635300,

      "_root_": "FSE001085"

    },

    {

      "id": "FSE001086",

      "_version_": 1661420746417635300,

      "_root_": "FSE001086"

    },

    {

      "id": "OMM000002",

      "_version_": 1661420746417635300,

      "_root_": "OMM000002"

    }

  ],

  "verbose-output": [

    "entity:RPS_Table",

    [

      "document#1",

      [

        "query",

        "select dp_id,dp_name,dp_street1,dp_city,dp_state,dp_ctry_co from MyTable",

        "time-taken",

        "0:0:0.228",

        null,

        "----------- row #1-------------",

        "dp_name",

        "HAP JANG GANG 6",

        "dp_state",

        null,

        "dp_city",

        null,

        "dp_ctry_co",

        "XX",

        "dp_id",

       "DVP000002",

        "dp_street1",

        null,

        null,

        "---------------------------------------------"

      ],

      "document#2",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "JI SONG 8",

        "dp_state",

        null,

        "dp_city",

        null,

        "dp_ctry_co",

        "XX",

        "dp_id",

        "DVP000003",

        "dp_street1",

        null,

        null,

        "---------------------------------------------"

      ],

      "document#3",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "BEKTAS, Halis",

        "dp_state",

        " ",

        "dp_city",

        "",

        "dp_ctry_co",

        "CH",

        "dp_id",

        "FSE001080",

        "dp_street1",

        "",

        null,

        "---------------------------------------------"

      ],

      "document#4",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "HOLLEBRAND, Alexander  AKA HOLLEBRAND, Sander",

        "dp_state",

        null,

        "dp_city",

        null,

        "dp_ctry_co",

        "XX",

        "dp_id",

        "FSE001081",

        "dp_street1",

        null,

        null,

        "---------------------------------------------"

      ],

      "document#5",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "VAN MAZIJK, Paul",

        "dp_state",

        "",

        "dp_city",

        "",

        "dp_ctry_co",

        "NL",

        "dp_id",

        "FSE001082",

        "dp_street1",

        "",

        null,

        "---------------------------------------------"

      ],

      "document#6",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "BLUEMARINE SA AKA BLUE MARINE SHIPPING AGENCY S.A. AKA BLUEMARINE AG  AKA BLUEMARINE LTD",

        "dp_state",

        " ",

        "dp_city",

        "Baar",

        "dp_ctry_co",

        "CH",

        "dp_id",

        "FSE001083",

        "dp_street1",

        "Lindenstrasse 2",

        null,

        "---------------------------------------------"

      ],

      "document#7",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "RIXO INTERNATIONAL TRADING LTD.",

        "dp_state",

        " ",

        "dp_city",

        "Baar",

        "dp_ctry_co",

        "CH",

        "dp_id",

        "FSE001084",

        "dp_street1",

        "Lindenstrasse 2",

        null,

       "---------------------------------------------"

      ],

      "document#8",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "STAROIL B.V.  AKA STAROIL S.A.",

        "dp_state",

        "",

        "dp_city",

        "",

        "dp_ctry_co",

        "",

        "dp_id",

        "FSE001085",

        "dp_street1",

        "",

        null,

        "---------------------------------------------"

      ],

      "document#9",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "KAYAKIRAN, Evren",

        "dp_state",

        " ",

        "dp_city",

        "",

        "dp_ctry_co",

        "TR",

        "dp_id",

        "FSE001086",

        "dp_street1",

        "",

       null,

        "---------------------------------------------"

      ],

      "document#10",

      [

        null,

        "----------- row #1-------------",

        "dp_name",

        "CHOL RYONG  AKA RYONG GUN BONG",

        "dp_state",

        " ",

        "dp_city",

        "",

        "dp_ctry_co",

        "XX",

        "dp_id",

        "OMM000002",

        "dp_street1",

        "",

        null,

        "---------------------------------------------"

      ],

      "document#11",

      []

    ]

  ],

  "status": "idle",

  "importResponse": "",

  "statusMessages": {

    "Total Requests made to DataSource": "1",

    "Total Rows Fetched": "10",

    "Total Documents Processed": "10",

    "Total Documents Skipped": "0",

    "Full Dump Started": "2020-03-17 14:11:34",

    "": "Indexing completed. Added/Updated: 10 documents. Deleted 0 documents.",

    "Committed": "2020-03-17 14:11:34",

    "Time taken": "0:0:0.311"

  }

}

 

Solrconfig.xml

..

..

<lib dir="${solr.install.dir:../../../..}/dist/" regex="solr-dataimporthandler-\d.*\.jar" /> 

  <lib dir="${solr.install.dir:../../../..}/dist/" regex="solr-dataimporthandler-extras-\d.*\.jar" />

  <requestHandler name="/dataimport" class="org.apache.solr.handler.dataimport.DataImportHandler"> 

             <lst name="defaults"> 

                  <str name="config">data-config.xml</str> 

             </lst> 

  </requestHandler>

 

 

data-config.xml

<dataConfig>  

  <dataSource type="JdbcDataSource"   

     driver="com.microsoft.sqlserver.jdbc.SQLServerDriver"   

     url="jdbc:sqlserver://servername;databaseName=mydatabasename;integratedSecurity=true"   

    />  

  <document>  

   <entity name="RPS_Table" pk="id" query="select dp_id,dp_name,dp_street1,dp_city,dp_state,dp_ctry_co from Mytable">  

     <field column="dp_id" name="id"/>

     <field column="dp_name" name="name"/>    

     <field column="dp_street1" name="street1"/>   

     <field column="dp_city" name="city"/> 

                 <field column="dp_state" name="state"/>  

                 <field column="dp_ctry_co" name="ctry"/> 

     </entity>  

  </document>  

  </dataConfig>

 

 

 

Nouman Zaheer

Software Engineer

U.S. & Canada: +1.877.328.7866 x365

International: +1.716.881.2590 x365

[hidden email]

https://www.visualcompliance.com

cid:image001.png@01D52D98.84265040

 

CONFIDENTIALITY NOTE:
This electronic transmission, including all attachments, is directed in confidence solely to the person(s) to whom it is addressed, or an authorized recipient, and may not otherwise be distributed, copied or disclosed. The contents of the transmission may also be subject to intellectual property rights and all such rights are expressly claimed and are not waived. If you have received this transmission in error, please notify the sender immediately by return electronic transmission and then immediately delete this transmission, including all attachments, without copying, distributing or disclosing same.