在solr中执行增量导入时的时间戳兼容性 [英] Timestamp compatibility while performing delta import in solr

问题描述

Im新到solr.I已成功索引oracle 10g xe数据库。我试图执行增量导入相同。
增量查询需要将表的 last_modified 列与 $ {dih.last_index_time} 进行比较。
然而在我的应用程序中，我没有这样的列。此外，我无法添加此列。因此，我使用' scn_to_timestamp（ora_rowscn）'给出所需的时间戳的值。此查询以以下格式返回类型timestamp的值 24-JUL-13 12.42.32.000000000 PM 和 dih.last_index_time 的格式为 2013-07-24 12:18:03 。因此，我将 dih.last_index_time 的格式更改为 to_timestamp（'$ {dih.last_index_time}'，'YYYY / MM / DD HH：MI ：SS'）。

我的数据配置如下 -

 < dataConfig> 
< dataSource type =JdbcDataSourcedriver =oracle.jdbc.OracleDriverurl =jdbc：oracle：thin：@ XXX.XXX.XX.XX：XXXX：xeuser =XXXXXXXXpassword = XXXXXXX/> 
< document name =product_info> 
< entity name =PRODUCTpk =PIDquery =SELECT * FROM PRODUCTdeltaImportQuery =SELECT * FROM PRODUCT WHERE PID = $ {dih.delta.id}deltaQuery =SELECT PID FROM PRODUCT WHERE scn_to_timestamp（ora_rowscn）> to_timestamp（'$ {dih.last_index_time}'，'YYYY / MM / DD HH：MI：SS'）> 
< field column =PIDname =id/> 
< field column =PNAMEname =itemName/> 
< field column =INITQTYname =itemQuantity/> 
< field column =REMQTYname =remQuantity/> 
< field column =PRICEname =itemPrice/> 
< field column =SPECIFICATIONname =specifications/> 
< entity name =SUB_CATEGORYquery =SELECT * FROM SUB_CATEGORY WHERE SCID = $ {PRODUCT.SCID}> 
< field column =SUBCATNAMEname =brand/> 
< entity name =CATEGORYquery =SELECT CNAME FROM CATEGORY WHERE CID = $ {SUB_CATEGORY.CID}> 
< field column =CNAMEname =itemCategory/> 
< / entity> 
< / entity> 
< / entity> 
< / document> 
< / dataConfig> 
  

但是，这不工作，会收到以下错误 -

无法执行查询：SELECT * FROM PRODUCT WHERE PID =处理文档＃1
原因：java.sql.SQLException：ORA-00936：missing表达式


请帮助我！

解决方案

我有一个类似的问题，使用* to_date *更成功。但是再看看这个，它只是看起来像你只需要在delatImportQuery中引用你的delta id：

deltaImportQuery =SELECT * FROM PRODUCT WHERE PID = ' $ {dih.delta.id} '

Im new to solr.I have successfully indexed oracle 10g xe database. Im trying to perform delta import on the same. The delta query requires a comparison of last_modified column of the table with ${dih.last_index_time}. However in my application I do not have such a column . Also, i cannot add this column. Therefore i used 'scn_to_timestamp(ora_rowscn)' to give the value of the required timestamps. This query returns the value of type timestamp in the following format 24-JUL-13 12.42.32.000000000 PM and dih.last_index_time is in the format 2013-07-24 12:18:03. So, I changed the format of dih.last_index_time as to_timestamp('${dih.last_index_time}', 'YYYY/MM/DD HH:MI:SS').

My Data-config looks like this -

<dataConfig>
<dataSource type="JdbcDataSource" driver="oracle.jdbc.OracleDriver" url="jdbc:oracle:thin:@XXX.XXX.XX.XX:XXXX:xe" user="XXXXXXXX" password="XXXXXXX" /> 
<document name="product_info">
<entity name="PRODUCT" pk="PID" query="SELECT * FROM PRODUCT" deltaImportQuery="SELECT * FROM PRODUCT WHERE PID=${dih.delta.id}" deltaQuery="SELECT PID FROM PRODUCT WHERE scn_to_timestamp(ora_rowscn) > to_timestamp('${dih.last_index_time}', 'YYYY/MM/DD HH:MI:SS')">
  <field column="PID" name="id" /> 
  <field column="PNAME" name="itemName" /> 
  <field column="INITQTY" name="itemQuantity" />
  <field column="REMQTY" name="remQuantity" />
  <field column="PRICE" name="itemPrice" /> 
  <field column="SPECIFICATION" name="specifications" /> 
  <entity name="SUB_CATEGORY" query="SELECT * FROM SUB_CATEGORY WHERE SCID=${PRODUCT.SCID}">
    <field column="SUBCATNAME" name="brand" /> 
    <entity name="CATEGORY" query="SELECT CNAME FROM CATEGORY WHERE CID=${SUB_CATEGORY.CID}">
      <field column="CNAME" name="itemCategory" /> 
    </entity>
  </entity>
</entity>
</document>
</dataConfig>

However,This is not working and im getting the following error -

Unable to execute query: SELECT * FROM PRODUCT WHERE PID= Processing Document # 1
Caused by: java.sql.SQLException: ORA-00936: missing expression

Please help me out!!!

解决方案

I had a similar issue and had more success with *to_date*. But looking at this again, it just looks like perhaps you just need to quote your delta id in the delatImportQuery:

deltaImportQuery="SELECT * FROM PRODUCT WHERE PID='${dih.delta.id}'"

这篇关于在solr中执行增量导入时的时间戳兼容性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！