You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by Wendy <wt...@rcsb.rutgers.edu> on 2016/08/19 20:01:48 UTC
help with DIH transformer to add a suffix to column names
Hi,How can I append a suffix _* to all column names from a mysql database.I
am working on a project index data from mysql . I would like to use dynamic
field to dynamically index fields without specifying each field/column
names. I have been tried DIH customer transformer to append a suffix to
column name. But no error, no data. Does anyone has a good working
example?Thanks!
--
View this message in context: http://lucene.472066.n3.nabble.com/help-with-DIH-transformer-to-add-a-suffix-to-column-names-tp4292448.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: help with DIH transformer to add a suffix to column names
Posted by Wendy <wt...@rcsb.rutgers.edu>.
Hi Alex,Thank you for your response. I include the details below. Hope it
makes more clear. I wanted to append _stem to each column name, so I can
specify fields dynamically with *_stem w/o specifying each field
names.Thanks!I use solr 6.1.0 to index data from mysql database.I wrote a
java class of FieldTransformer as following. But it didn't work. It got null
when retrieving column name. How to fix the java code?
Thanks!-----------------------------------------------------------package
my.solr.transformer; import java.util.ArrayList; public class
FieldTransformer { public Object transformRow(Map<String, Object> row) {
int s = row.size(); System.out.println("row size = " + s);
for (Entry<String, Object> entry : row.entrySet()) { String
columnName = entry.getKey(); row.put(columnName + "_stem",
entry.getValue()); System.out.println("column name = " +
columnName); System.out.println("value = " + entry.getKey());
row.remove(columnName); } System.out.println("after processed row
size = " + row.size()); return row; } }-----------Other solr
config files------------------------------1. part of db-data-config.xml
<entity name="pdb_entry" pk="pdb_id"
transformer="my.solr.transformer.FieldTransformer"
query="select * from pdb_entry where status_code = 'REL' "
<entity name="citation" onError="continue"
transformer="my.solr.transformer.FieldTransformer"
query="select title from citation where
Structure_ID='${pdb_entry.pdb_id}' and id = 'primary' and title is not
null" parentDeltaQuery="select pdb_id from
pdb_entry where pdb_id=${citation.Structure_ID}" >
..... 2. part of managed-schema file ...... ......3.
part of solrconfig.xml file db-data-config.xml true
explicit edismax pdb_id^20.0
classification_stem^10.0 *_stem^0.3
rest_fields_stem ^0.3 7
1000 text
--
View this message in context: http://lucene.472066.n3.nabble.com/help-with-DIH-transformer-to-add-a-suffix-to-column-names-tp4292448p4292627.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: help with DIH transformer to add a suffix to column names
Posted by Wendy <wt...@rcsb.rutgers.edu>.
Hi Alex,
Thank you for your response.
It worked. I am very happy for the results. I reports the steps below. The
purpose is to create a dynamic field to simplify field definition in
managed-schema file and to simplify field rank in solrconfig.xml file.
STEPS:
1. file creation of db-data-config.xml
<dataConfig>
<dataSource name ="data_source_?????"
type="JdbcDataSource"
driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://machineName:3306/databaseName?zeroDateTimeBehavior=convertToNull"
user="?????"
password="?????" />
<document name="db-fulltext-index">
<entity name="pdb_entry" pk="pdb_id_stem"
transformer="my.solr.transformer.FieldTransformer"
query=" * from pdb_entry where status_code = 'REL' " >
<entity name="citation" onError="continue"
transformer="my.solr.transformer.FieldTransformer"
query="select title from citation where
Structure_ID='${pdb_entry.pdb_id_stem}' and id = 'primary' " >
</entity>
<entity name="citation_author" onError="continue"
transformer="my.solr.transformer.FieldTransformer"
query="select name from citation_author where
Structure_ID='${pdb_entry.pdb_id_stem}' and citation_id = 'primary' " >
</entity>
</entity>
</document>
</dataConfig>
2. Modification of solrconfig.xml file: notice of the ranking
<lib dir="${solr.install.dir:../../../..}/dist/"
regex="solr-dataimporthandler-\d.*\.jar" />
<lib dir="${solr.install.dir:../../../..}/dist/"
regex="mysql-connector-java-5.0.7-bin.jar" />
<lib dir="${solr.install.dir:../../../..}/dist/"
regex="solr-rcsb-plugin.jar" />
<requestHandler name="/dataimport"
class="org.apache.solr.handler.dataimport.DataImportHandler">
<lst name="defaults">
<str name="config">db-data-config.xml</str>
</lst>
</requestHandler>
<requestHandler name="/search" class="solr.SearchHandler">
<lst name="defaults">
<str name="indent">true</str>
<str name="echoParams">explicit</str>
<str name="defType">edismax</str>
<str name="qf">pdb_id_stem^20.0</str>
<str name="qf">title_stem^20.0</str>
<str name="qf">keywords_stem^10.0</str>
<str name="qf">*_stem^0.3</str>
<str name="qf">rest_fields_stem ^0.3</str>
<str name="mm">7</str>
<int name="rows">1000</int>
<str name="df">text</str>
</lst>
</requestHandler>
3. Modification of managed-schema file: Notice of change <uniqueKey>,
creation of a dynamic field "*_stem",,,
<field name="pdb_id_stem" type="string" indexed="true" stored="true"
required="true" multiValued="false" />
<field name="rest_fields_stem" type="pdb_text_stem" indexed="true"
stored="true" multiValued="true"/>
<copyField source="*_stem" dest="rest_fields_stem"/>
<dynamicField name="*_stem" type="pdb_text_stem" indexed="true"
stored="true"/>
<fieldtype name="pdb_text_stem" class="solr.TextField"
positionIncrementGap="100">
<analyzer type="index">
<tokenizer class="solr.WhitespaceTokenizerFactory"/>
<filter class="solr.SynonymFilterFactory" synonyms="synonyms.txt"
ignoreCase="true" expand="true" />
<filter class="solr.StopFilterFactory" ignoreCase="true"/>
<filter class="solr.WordDelimiterFilterFactory" generateWordParts="1"
generateNumberParts="1" catenateWords="0" catenateNumbers="0"
catenateAll="0"/>
<filter class="solr.LowerCaseFilterFactory"/>
<filter class="solr.RemoveDuplicatesTokenFilterFactory"/>
<filter class="solr.PorterStemFilterFactory"/>
</analyzer>
<analyzer type="query">
<tokenizer class="solr.WhitespaceTokenizerFactory"/>
<filter class="solr.StopFilterFactory" ignoreCase="true"/>
<filter class="solr.WordDelimiterFilterFactory" generateWordParts="1"
generateNumberParts="1" catenateWords="0" catenateNumbers="0"
catenateAll="0"/>
<filter class="solr.SynonymFilterFactory" synonyms="synonyms.txt"
ignoreCase="true" expand="true" />
<filter class="solr.LowerCaseFilterFactory"/>
<filter class="solr.RemoveDuplicatesTokenFilterFactory"/>
<filter class="solr.PorterStemFilterFactory"/>
</analyzer>
</fieldtype>
<uniqueKey>pdb_id_stem</uniqueKey>
4. creation of a customer transformer:
package my.solr.transformer;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.solr.handler.dataimport.Context;
import org.apache.solr.handler.dataimport.DataImporter;
import org.apache.solr.handler.dataimport.Transformer;
public class FieldTransformer extends Transformer {
public Map<String, Object> transformRow(Map<String, Object> row, Context
context) {
List<Map<String, String>> fields = ((Context)
context).getAllEntityFields();
int rowSize = row.size();
//System.out.println("row size to start = " + rowSize);
//Converting HashMap keys into ArrayList
Set<String> keySet = row.keySet();
List<String> keyList = new ArrayList<String>(keySet);
for (int i = 0; i < rowSize; i++) {
String columnName = keyList.get(i);
Object value = row.get(columnName);
if (value != null && !value.toString().trim().equals("")) {
row.put(columnName + "_stem", value.toString().trim());
//System.out.println("value = " + value.toString().trim());
//System.out.println("row.size = " + row.size());
};
row.remove(columnName);
}
System.out.println("row size ended = " + row.size());
return row;
}
}
5. NOTE: when using customer transformer, need to add the following two jar
fiels to this destination:
cp solr-dataimporthandler-6.1.0.jar
/opt/solr-6.1.0/server/solr-webapp/webapp/WEB-INF/lib
cp solr-dataimporthandler-extras-6.1.0.jar
/opt/solr-6.1.0/server/solr-webapp/webapp/WEB-INF/lib
6. screen shot:
<http://lucene.472066.n3.nabble.com/file/n4293261/Screenshot-20.png>
--
View this message in context: http://lucene.472066.n3.nabble.com/help-with-DIH-transformer-to-add-a-suffix-to-column-names-tp4292448p4293261.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: help with DIH transformer to add a suffix to column names
Posted by Wendy <wt...@rcsb.rutgers.edu>.
<http://lucene.472066.n3.nabble.com/file/n4292972/Screenshot-19.png>
Hi Alex,
It worked out kindly. I have to specify table column names. Using customer
transformer allowed me to change column name to ????_stem. In this way, it
simplifies field ranking in solrconfig.xml file and simplifies field
specification in managed-schema file. I listed the steps below.
Steps:
-------------------------------------------------------
1. sample of db-data-config.xml file
<dataConfig>
<dataSource name ="data_source_?????"
type="JdbcDataSource"
driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://machineName:3306/databaseName?zeroDateTimeBehavior=convertToNull"
user="?????"
password="?????" />
<document name="db-fulltext-index">
<entity name="pdb_entry" pk="pdb_id"
transformer="my.solr.transformer.FieldTransformer"
query="select pdb_id,author_list, method, header,
deposit_site, process_site, initial_deposition_date, date_of_RCSB_release
from pdb_entry where status_code = 'REL' "
deltaImputQuery="select pdb_id, author_list , method, header ,
deposit_site, process_site, initial_deposition_date, date_of_RCSB_release
from pdb_entry a, database_PDB_rev b where b.Structure_ID = a.pdb_id and
a.status_code = 'REL' and b.date > '${dih.last_index_time}' "
parentDeltaImputQuery="select pdb_id from pdb_entry where
pdb_id=${database_PDB_rev.Structure_ID}"
deltaQuery="select pdb_id, author_list, method, header,
deposit_site, process_site, initial_deposition_date, date_of_RCSB_release
from pdb_entry a, database_PDB_rev b where b.Structure_ID = a.pdb_id and
a.status_code = 'REL' and b.date > '${dih.last_index_time}' "
parentDeltaQuery="select pdb_id from pdb_entry where
pdb_id=${database_PDB_rev.Structure_ID}" >
<entity name="citation" onError="continue"
transformer="my.solr.transformer.FieldTransformer"
query="select title from citation where
Structure_ID='${pdb_entry.pdb_id}' and id = 'primary' and title is not null"
parentDeltaQuery="select pdb_id from pdb_entry where
pdb_id=${citation.Structure_ID}" >
</entity>
<entity name="citation_author" onError="continue"
transformer="my.solr.transformer.FieldTransformer"
query="select name from citation_author where
Structure_ID='${pdb_entry.pdb_id}' and citation_id = 'primary' "
parentDeltaQuery="select pdb_id from pdb_entry where
pdb_id=${citation_author.Structure_ID}" >
</entity>
</entity>
</document>
</dataConfig>
2. Modification of solrconfig.xml file:
Add the following lines:
<lib dir="${solr.install.dir:../../../..}/dist/"
regex="solr-dataimporthandler-\d.*\.jar" />
<lib dir="${solr.install.dir:../../../..}/dist/"
regex="mysql-connector-java-5.0.7-bin.jar" />
<lib dir="${solr.install.dir:../../../..}/dist/"
regex="solr-rcsb-plugin.jar" />
<requestHandler name="/dataimport"
class="org.apache.solr.handler.dataimport.DataImportHandler">
<lst name="defaults">
<str name="config">db-data-config.xml</str>
</lst>
</requestHandler>
<requestHandler name="/search" class="solr.SearchHandler">
<lst name="defaults">
<str name="indent">true</str>
<str name="echoParams">explicit</str>
<str name="defType">edismax</str>
<str name="qf">pdb_id^20.0</str>
<str name="qf">author_list_stem^20.0</str>
<str name="qf">header^10.0</str>
<str name="qf">reflns.resolution^5.0</str>
<str name="qf">keywords_stem^10.0</str>
<str name="qf">rest_field_stem^0.3</str>
<str name="mm">7</str>
<int name="rows">1000</int>
<str name="df">text</str>
</lst>
</requestHandler>
3. Modification of managed-schema file:
<field name="pdb_id" type="string" indexed="true" stored="true"
required="true" multiValued="false" />
<field name="rest_fields_stem" type="pdb_text_stem" indexed="true"
stored="true" multiValued="true"/>
<copyField source="*_stem" dest="rest_fields_stem"/>
<dynamicField name="*_stem" type="pdb_text_stem" indexed="true"
stored="true"/>
<fieldtype name="pdb_text_stem" class="solr.TextField"
positionIncrementGap="100">
<analyzer type="index">
<tokenizer class="solr.WhitespaceTokenizerFactory"/>
<filter class="solr.SynonymFilterFactory" synonyms="synonyms.txt"
ignoreCase="true" expand="true" />
<filter class="solr.StopFilterFactory" ignoreCase="true"/>
<filter class="solr.WordDelimiterFilterFactory" generateWordParts="1"
generateNumberParts="1" catenateWords="0" catenateNumbers="0"
catenateAll="0"/>
<filter class="solr.LowerCaseFilterFactory"/>
<filter class="solr.RemoveDuplicatesTokenFilterFactory"/>
<filter class="solr.PorterStemFilterFactory"/>
</analyzer>
<analyzer type="query">
<tokenizer class="solr.WhitespaceTokenizerFactory"/>
<filter class="solr.StopFilterFactory" ignoreCase="true"/>
<filter class="solr.WordDelimiterFilterFactory" generateWordParts="1"
generateNumberParts="1" catenateWords="0" catenateNumbers="0"
catenateAll="0"/>
<filter class="solr.SynonymFilterFactory" synonyms="synonyms.txt"
ignoreCase="true" expand="true" />
<filter class="solr.LowerCaseFilterFactory"/>
<filter class="solr.RemoveDuplicatesTokenFilterFactory"/>
<filter class="solr.PorterStemFilterFactory"/>
</analyzer>
</fieldtype>
4. Java class of FieldTransformer:
package my.solr.transformer;
import java.util.List;
import java.util.Map;
import org.apache.solr.handler.dataimport.Context;
import org.apache.solr.handler.dataimport.DataImporter;
import org.apache.solr.handler.dataimport.Transformer;
public class FieldTransformer extends Transformer {
public Map<String, Object> transformRow(Map<String, Object> row, Context
context) {
List<Map<String, String>> fields = ((Context)
context).getAllEntityFields();
for (Map<String, String> field : fields) {
String columnName = field.get(DataImporter.COLUMN);
// Get this field's value from the current row
Object value = row.get(columnName);
if (value != null && !value.toString().trim().equals("")) {
row.put(columnName + "_stem", value.toString().trim());
}
}
return row;
}
}
5. NOTES:
1. When write customer transformer, need to copy the following files:
cp /opt/solr-6.1.0/dist/solr-dataimporthandler-extras-6.1.0.jar
/opt/solr-6.1.0/server/solr-webapp/webapp/WEB-INF/lib/
cp /opt/solr-6.1.0/dist/solr-dataimporthandler-6.1.0.jar
/opt/solr-6.1.0/server/solr-webapp/webapp/WEB-INF/lib/
2. put the customer transformer jar file to the following directory and
specify it solrconfig.xml file (see step 2 above)
/opt/solr-6.1.0/dist/solr-rcsb-plugin.jar
--
View this message in context: http://lucene.472066.n3.nabble.com/help-with-DIH-transformer-to-add-a-suffix-to-column-names-tp4292448p4292972.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: help with DIH transformer to add a suffix to column names
Posted by Alexandre Rafalovitch <ar...@gmail.com>.
I am still not sure it is the right approach. As opposed to managed schema,
etc. But...
If you add dynamic field * it will accept any field. And DIH test for
skipping fields unknown in schema during automatic name matching should
probably accept it.
Do that as step one, see that you get fields with content into the schema
at all.
Then, try your renaming magic to match to the more specific dynamic field
with extension. If it still fails, try doing that renaming in
UpdateRequestProcessor chain.
If this works, you can then set the * dynamicField to ignored (store,
index, docvalues all false) and it should still work for your renamed
fields and ignore others.
If this does work all together, please report back to the list (I am
curious) AND document this really well for the poor next maintainer of your
configuration.
Good luck,
Alex
On 23 Aug 2016 8:12 PM, "Wendy" <wt...@rcsb.rutgers.edu> wrote:
Hi Emir,I have many tables and columns to index. One of the requirements is
to dynamically index columns without knowing column names. In this way, if a
new column is added later on, we don't need to change the configurations,
just need to do a delta-imput.I did use Solr with mongodb and
mongo-connector for a couple of applications. We were happy with the results
and performance. Now try to use Solr with mysql in a much large scale. I am
kind of stuck at this step for several days :-(Will try again today.Thanks
again for your response!
--
View this message in context: http://lucene.472066.n3.
nabble.com/help-with-DIH-transformer-to-add-a-suffix-to-column-names-
tp4292448p4292925.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: help with DIH transformer to add a suffix to column names
Posted by Wendy <wt...@rcsb.rutgers.edu>.
Hi Emir,I have many tables and columns to index. One of the requirements is
to dynamically index columns without knowing column names. In this way, if a
new column is added later on, we don't need to change the configurations,
just need to do a delta-imput.I did use Solr with mongodb and
mongo-connector for a couple of applications. We were happy with the results
and performance. Now try to use Solr with mysql in a much large scale. I am
kind of stuck at this step for several days :-(Will try again today.Thanks
again for your response!
--
View this message in context: http://lucene.472066.n3.nabble.com/help-with-DIH-transformer-to-add-a-suffix-to-column-names-tp4292448p4292925.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: help with DIH transformer to add a suffix to column names
Posted by Emir Arnautovic <em...@sematext.com>.
Hi Wendy,
Why don't you simply specify column names in your query? Do you have
that much columns that "SELECT *" is THE way to go?
For the transformer - you changed the row, but fields in context are
still using old names - maybe try setting field names in context (if
possible - did not look at code)
Emir
On 22.08.2016 21:47, Wendy wrote:
> Hi Emlr,
>
> I use the example of "A General TrimTransformer" in the following link:
>
> https://wiki.apache.org/solr/DIHCustomTransformer#transformer
>
> But instead of trim the field value, I wanted to change the table column
> name to columnName_stem.
> So I can use *_stem to copy all fields.
>
> Here is my code, but just not working. I don't what is the problem with the
> code? Any ideas? Thanks!
>
> public class RowTransformer extends Transformer {
> public Map<String, Object> transformRow(Map<String, Object> row, Context
> context) {
> List<Map<String, String>> fields = ((Context)
> context).getAllEntityFields();
>
> System.out.println("fields = " + fields.size());
>
> for (Map<String, String> field : fields) {
> String columnName = field.get(DataImporter.COLUMN);
> System.out.println("columnName = " + columnName);
> // Get this field's value from the current row
> Object value = row.get(columnName);
> if (value != null && !value.toString().trim().equals("")) {
> row.put(columnName + "_stem", value.toString().trim());
> System.out.println("positive columnName = " + columnName);
> System.out.println("positive columnValue = " +
> value.toString());
> }
> }
> return row;
> }
> }
>
>
>
> --
> View this message in context: http://lucene.472066.n3.nabble.com/help-with-DIH-transformer-to-add-a-suffix-to-column-names-tp4292448p4292796.html
> Sent from the Solr - User mailing list archive at Nabble.com.
--
Monitoring * Alerting * Anomaly Detection * Centralized Log Management
Solr & Elasticsearch Support * http://sematext.com/
Re: help with DIH transformer to add a suffix to column names
Posted by Wendy <wt...@rcsb.rutgers.edu>.
Hi Emlr,
I use the example of "A General TrimTransformer" in the following link:
https://wiki.apache.org/solr/DIHCustomTransformer#transformer
But instead of trim the field value, I wanted to change the table column
name to columnName_stem.
So I can use *_stem to copy all fields.
Here is my code, but just not working. I don't what is the problem with the
code? Any ideas? Thanks!
public class RowTransformer extends Transformer {
public Map<String, Object> transformRow(Map<String, Object> row, Context
context) {
List<Map<String, String>> fields = ((Context)
context).getAllEntityFields();
System.out.println("fields = " + fields.size());
for (Map<String, String> field : fields) {
String columnName = field.get(DataImporter.COLUMN);
System.out.println("columnName = " + columnName);
// Get this field's value from the current row
Object value = row.get(columnName);
if (value != null && !value.toString().trim().equals("")) {
row.put(columnName + "_stem", value.toString().trim());
System.out.println("positive columnName = " + columnName);
System.out.println("positive columnValue = " +
value.toString());
}
}
return row;
}
}
--
View this message in context: http://lucene.472066.n3.nabble.com/help-with-DIH-transformer-to-add-a-suffix-to-column-names-tp4292448p4292796.html
Sent from the Solr - User mailing list archive at Nabble.com.
Re: help with DIH transformer to add a suffix to column names
Posted by Emir Arnautovic <em...@sematext.com>.
Hi Wendy,
It seems to me that you misunderstood concept of dynamic fields. It is
something that is defined in Solr schema, e.g. *_text, and then in your
DIH config you define fields that match that pattern, e.g. name_text,
desc_text etc.
HTH,
Emir
On 20.08.2016 00:58, Alexandre Rafalovitch wrote:
> Can you give an example of what SQL column name and what Solr field name
> you want correspondingly.
>
> Because 'name_*' is not a valid field name.
>
> Also, why specifically you are doing this.
>
> Regards,
> Alex
>
> On 20 Aug 2016 6:04 AM, "Wendy" <wt...@rcsb.rutgers.edu> wrote:
>
> Hi,How can I append a suffix _* to all column names from a mysql database.I
> am working on a project index data from mysql . I would like to use dynamic
> field to dynamically index fields without specifying each field/column
> names. I have been tried DIH customer transformer to append a suffix to
> column name. But no error, no data. Does anyone has a good working
> example?Thanks!
>
>
>
> --
> View this message in context: http://lucene.472066.n3.
> nabble.com/help-with-DIH-transformer-to-add-a-suffix-
> to-column-names-tp4292448.html
> Sent from the Solr - User mailing list archive at Nabble.com.
>
--
Monitoring * Alerting * Anomaly Detection * Centralized Log Management
Solr & Elasticsearch Support * http://sematext.com/
Re: help with DIH transformer to add a suffix to column names
Posted by Alexandre Rafalovitch <ar...@gmail.com>.
Can you give an example of what SQL column name and what Solr field name
you want correspondingly.
Because 'name_*' is not a valid field name.
Also, why specifically you are doing this.
Regards,
Alex
On 20 Aug 2016 6:04 AM, "Wendy" <wt...@rcsb.rutgers.edu> wrote:
Hi,How can I append a suffix _* to all column names from a mysql database.I
am working on a project index data from mysql . I would like to use dynamic
field to dynamically index fields without specifying each field/column
names. I have been tried DIH customer transformer to append a suffix to
column name. But no error, no data. Does anyone has a good working
example?Thanks!
--
View this message in context: http://lucene.472066.n3.
nabble.com/help-with-DIH-transformer-to-add-a-suffix-
to-column-names-tp4292448.html
Sent from the Solr - User mailing list archive at Nabble.com.