lucene-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nouman Zaheer <NZah...@descartes.com>
Subject SOLR dataimport from SQL server
Date Tue, 17 Mar 2020 14:17:40 GMT
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
nzaheer@descartes.com <mailto:nzaheer@descartes.com>
https://www.visualcompliance<https://www.visualcompliance.com/>.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.


Mime
View raw message