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 Tim Dunphy <bl...@gmail.com> on 2014/11/04 20:52:41 UTC
import 2 mysql tables into Solr 4
Hey all,
I finally got mysql data into Solr 4 with your help. First off, thank you
for that!
But now I'm hoping to refine the resulting process a bit.
What I'm trying to do, now that mysql imports are working, is to import 2
separate tables from the same mysql database. I tried this in my xml and it
doesn't work:
<dataConfig>
<dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://web1.mydomain.com:3306/mydomain" user="admin"
password=“secret” batchSize="1" />
<document name="mydomain_users">
<entity name="mydomain_users" query="SELECT * FROM wp_users">
<field column="ID" name="ID" />
<field column="user_login" name="user_login" />
<field column="user_nicename" name="user_nicename" />
<field column="user_email" name="user_email" />
<field column="user_url" name="user_url" />
<field column="user_registered" name="user_registered" />
<field column="user_activation_key" name="user_activation_key"
/>
<field column="user_status" name="user_status" />
<field column="display_name" name="display_name" />
</entity>
</document>
<document name="mydomain_posts">
<entity name="mydomain_posts" query="SELECT * FROM wp_posts">
<field column="ID" name="ID" />
<field column="post_author" name="post_author" />
<field column="post_date" name="post_date" />
<field column="post_content" name="post_content" />
</entity>
</document>
</dataConfig>
What happens is that the first table (document name mydomain_users) gets
imported. But the second table (document name mydomain_posts) does not.
Can anybody tell me what I'm doing wrong? I know that I need to get a
little more used to using XML.
Thanks
Tim
--
GPG me!!
gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: import 2 mysql tables into Solr 4
Posted by Tim Dunphy <bl...@gmail.com>.
>
> Looks right. Just remember to double check the preImportDeleteQuery
> documentation to avoid surprises later
Sure! Sounds good. Thanks again.
On Tue, Nov 4, 2014 at 4:10 PM, Alexandre Rafalovitch <ar...@gmail.com>
wrote:
> Looks right. Just remember to double check the preImportDeleteQuery
> documentation to avoid surprises later
>
> Regards,
> Alex.
> Personal: http://www.outerthoughts.com/ and @arafalov
> Solr resources and newsletter: http://www.solr-start.com/ and @solrstart
> Solr popularizers community: https://www.linkedin.com/groups?gid=6713853
>
>
> On 4 November 2014 15:56, Tim Dunphy <bl...@gmail.com> wrote:
> > Hey Alexandre,
> >
> > Thanks for the example! This is what worked for me:
> >
> > <dataConfig>
> >
> >
> > <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
> > url="jdbc:mysql://web1.mydomain.com:3306/mydomain" user="admin"
> > password=“secret” batchSize="1" />
> >
> > <document name="mydomain_db">
> >
> >
> > <entity name="mydomain_users" query="SELECT * FROM wp_users">
> >
> > <field column="ID" name="ID" />
> >
> > <field column="user_login" name="user_login" />
> >
> > <field column="user_nicename" name="user_nicename" />
> >
> > <field column="user_email" name="user_email" />
> >
> > <field column="user_url" name="user_url" />
> >
> > <field column="user_registered" name="user_registered" />
> >
> > <field column="user_activation_key"
> name="user_activation_key"
> > />
> >
> > <field column="user_status" name="user_status" />
> >
> > <field column="display_name" name="display_name" />
> >
> > </entity>
> >
> >
> > <entity name="mydomain_posts" query="SELECT * FROM wp_posts">
> >
> > <field column="ID" name="ID" />
> >
> > <field column="post_author" name="post_author" />
> >
> > <field column="post_date" name="post_date" />
> >
> > <field column="post_content" name="post_content" />
> >
> > </entity>
> >
> >
> > </document>
> >
> > </dataConfig>
> >
> >
> > Thank you again!
> >
> >
> > Tim
> >
> > On Tue, Nov 4, 2014 at 3:12 PM, Alexandre Rafalovitch <
> arafalov@gmail.com>
> > wrote:
> >
> >> I can't remember what document element does, but I am quite sure the
> >> entities just need to be side-by-side.
> >>
> >> See the example from my book:
> >>
> >>
> https://github.com/arafalov/solr-indexing-book/blob/master/published/dihdb/conf/dih-definition.xml
> >>
> >> Notice that you need preImportDeleteQuery for each definition,
> >> otherwise they will do "delete *" and break each other's content.
> >>
> >> Regards,
> >> Alex.
> >> Personal: http://www.outerthoughts.com/ and @arafalov
> >> Solr resources and newsletter: http://www.solr-start.com/ and
> @solrstart
> >> Solr popularizers community:
> https://www.linkedin.com/groups?gid=6713853
> >>
> >>
> >> On 4 November 2014 14:52, Tim Dunphy <bl...@gmail.com> wrote:
> >> > Hey all,
> >> >
> >> > I finally got mysql data into Solr 4 with your help. First off, thank
> >> you
> >> > for that!
> >> >
> >> > But now I'm hoping to refine the resulting process a bit.
> >> >
> >> > What I'm trying to do, now that mysql imports are working, is to
> import
> >> 2
> >> > separate tables from the same mysql database. I tried this in my xml
> and
> >> it
> >> > doesn't work:
> >> >
> >> > <dataConfig>
> >> > <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
> >> > url="jdbc:mysql://web1.mydomain.com:3306/mydomain" user="admin"
> >> > password=“secret” batchSize="1" />
> >> > <document name="mydomain_users">
> >> > <entity name="mydomain_users" query="SELECT * FROM wp_users">
> >> > <field column="ID" name="ID" />
> >> > <field column="user_login" name="user_login" />
> >> > <field column="user_nicename" name="user_nicename" />
> >> > <field column="user_email" name="user_email" />
> >> > <field column="user_url" name="user_url" />
> >> > <field column="user_registered" name="user_registered" />
> >> > <field column="user_activation_key"
> >> name="user_activation_key"
> >> > />
> >> > <field column="user_status" name="user_status" />
> >> > <field column="display_name" name="display_name" />
> >> > </entity>
> >> > </document>
> >> >
> >> > <document name="mydomain_posts">
> >> > <entity name="mydomain_posts" query="SELECT * FROM wp_posts">
> >> > <field column="ID" name="ID" />
> >> > <field column="post_author" name="post_author" />
> >> > <field column="post_date" name="post_date" />
> >> > <field column="post_content" name="post_content" />
> >> > </entity>
> >> > </document>
> >> > </dataConfig>
> >> >
> >> > What happens is that the first table (document name mydomain_users)
> gets
> >> > imported. But the second table (document name mydomain_posts) does
> not.
> >> >
> >> > Can anybody tell me what I'm doing wrong? I know that I need to get a
> >> > little more used to using XML.
> >> >
> >> > Thanks
> >> > Tim
> >> >
> >> > --
> >> > GPG me!!
> >> >
> >> > gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
> >>
> >
> >
> >
> > --
> > GPG me!!
> >
> > gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
>
--
GPG me!!
gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: import 2 mysql tables into Solr 4
Posted by Tim Dunphy <bl...@gmail.com>.
Hi Alex,
On Tue, Nov 4, 2014 at 4:10 PM, Alexandre Rafalovitch <ar...@gmail.com>
wrote:
> Looks right. Just remember to double check the preImportDeleteQuery
> documentation to avoid surprises later
>
> Regards,
> Alex.
> Personal: http://www.outerthoughts.com/ and @arafalov
> Solr resources and newsletter: http://www.solr-start.com/ and @solrstart
> Solr popularizers community: https://www.linkedin.com/groups?gid=6713853
>
>
> On 4 November 2014 15:56, Tim Dunphy <bl...@gmail.com> wrote:
> > Hey Alexandre,
> >
> > Thanks for the example! This is what worked for me:
> >
> > <dataConfig>
> >
> >
> > <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
> > url="jdbc:mysql://web1.mydomain.com:3306/mydomain" user="admin"
> > password=“secret” batchSize="1" />
> >
> > <document name="mydomain_db">
> >
> >
> > <entity name="mydomain_users" query="SELECT * FROM wp_users">
> >
> > <field column="ID" name="ID" />
> >
> > <field column="user_login" name="user_login" />
> >
> > <field column="user_nicename" name="user_nicename" />
> >
> > <field column="user_email" name="user_email" />
> >
> > <field column="user_url" name="user_url" />
> >
> > <field column="user_registered" name="user_registered" />
> >
> > <field column="user_activation_key"
> name="user_activation_key"
> > />
> >
> > <field column="user_status" name="user_status" />
> >
> > <field column="display_name" name="display_name" />
> >
> > </entity>
> >
> >
> > <entity name="mydomain_posts" query="SELECT * FROM wp_posts">
> >
> > <field column="ID" name="ID" />
> >
> > <field column="post_author" name="post_author" />
> >
> > <field column="post_date" name="post_date" />
> >
> > <field column="post_content" name="post_content" />
> >
> > </entity>
> >
> >
> > </document>
> >
> > </dataConfig>
> >
> >
> > Thank you again!
> >
> >
> > Tim
> >
> > On Tue, Nov 4, 2014 at 3:12 PM, Alexandre Rafalovitch <
> arafalov@gmail.com>
> > wrote:
> >
> >> I can't remember what document element does, but I am quite sure the
> >> entities just need to be side-by-side.
> >>
> >> See the example from my book:
> >>
> >>
> https://github.com/arafalov/solr-indexing-book/blob/master/published/dihdb/conf/dih-definition.xml
> >>
> >> Notice that you need preImportDeleteQuery for each definition,
> >> otherwise they will do "delete *" and break each other's content.
> >>
> >> Regards,
> >> Alex.
> >> Personal: http://www.outerthoughts.com/ and @arafalov
> >> Solr resources and newsletter: http://www.solr-start.com/ and
> @solrstart
> >> Solr popularizers community:
> https://www.linkedin.com/groups?gid=6713853
> >>
> >>
> >> On 4 November 2014 14:52, Tim Dunphy <bl...@gmail.com> wrote:
> >> > Hey all,
> >> >
> >> > I finally got mysql data into Solr 4 with your help. First off, thank
> >> you
> >> > for that!
> >> >
> >> > But now I'm hoping to refine the resulting process a bit.
> >> >
> >> > What I'm trying to do, now that mysql imports are working, is to
> import
> >> 2
> >> > separate tables from the same mysql database. I tried this in my xml
> and
> >> it
> >> > doesn't work:
> >> >
> >> > <dataConfig>
> >> > <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
> >> > url="jdbc:mysql://web1.mydomain.com:3306/mydomain" user="admin"
> >> > password=“secret” batchSize="1" />
> >> > <document name="mydomain_users">
> >> > <entity name="mydomain_users" query="SELECT * FROM wp_users">
> >> > <field column="ID" name="ID" />
> >> > <field column="user_login" name="user_login" />
> >> > <field column="user_nicename" name="user_nicename" />
> >> > <field column="user_email" name="user_email" />
> >> > <field column="user_url" name="user_url" />
> >> > <field column="user_registered" name="user_registered" />
> >> > <field column="user_activation_key"
> >> name="user_activation_key"
> >> > />
> >> > <field column="user_status" name="user_status" />
> >> > <field column="display_name" name="display_name" />
> >> > </entity>
> >> > </document>
> >> >
> >> > <document name="mydomain_posts">
> >> > <entity name="mydomain_posts" query="SELECT * FROM wp_posts">
> >> > <field column="ID" name="ID" />
> >> > <field column="post_author" name="post_author" />
> >> > <field column="post_date" name="post_date" />
> >> > <field column="post_content" name="post_content" />
> >> > </entity>
> >> > </document>
> >> > </dataConfig>
> >> >
> >> > What happens is that the first table (document name mydomain_users)
> gets
> >> > imported. But the second table (document name mydomain_posts) does
> not.
> >> >
> >> > Can anybody tell me what I'm doing wrong? I know that I need to get a
> >> > little more used to using XML.
> >> >
> >> > Thanks
> >> > Tim
> >> >
> >> > --
> >> > GPG me!!
> >> >
> >> > gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
> >>
> >
> >
> >
> > --
> > GPG me!!
> >
> > gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
>
--
GPG me!!
gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: import 2 mysql tables into Solr 4
Posted by Alexandre Rafalovitch <ar...@gmail.com>.
Looks right. Just remember to double check the preImportDeleteQuery
documentation to avoid surprises later
Regards,
Alex.
Personal: http://www.outerthoughts.com/ and @arafalov
Solr resources and newsletter: http://www.solr-start.com/ and @solrstart
Solr popularizers community: https://www.linkedin.com/groups?gid=6713853
On 4 November 2014 15:56, Tim Dunphy <bl...@gmail.com> wrote:
> Hey Alexandre,
>
> Thanks for the example! This is what worked for me:
>
> <dataConfig>
>
>
> <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
> url="jdbc:mysql://web1.mydomain.com:3306/mydomain" user="admin"
> password=“secret” batchSize="1" />
>
> <document name="mydomain_db">
>
>
> <entity name="mydomain_users" query="SELECT * FROM wp_users">
>
> <field column="ID" name="ID" />
>
> <field column="user_login" name="user_login" />
>
> <field column="user_nicename" name="user_nicename" />
>
> <field column="user_email" name="user_email" />
>
> <field column="user_url" name="user_url" />
>
> <field column="user_registered" name="user_registered" />
>
> <field column="user_activation_key" name="user_activation_key"
> />
>
> <field column="user_status" name="user_status" />
>
> <field column="display_name" name="display_name" />
>
> </entity>
>
>
> <entity name="mydomain_posts" query="SELECT * FROM wp_posts">
>
> <field column="ID" name="ID" />
>
> <field column="post_author" name="post_author" />
>
> <field column="post_date" name="post_date" />
>
> <field column="post_content" name="post_content" />
>
> </entity>
>
>
> </document>
>
> </dataConfig>
>
>
> Thank you again!
>
>
> Tim
>
> On Tue, Nov 4, 2014 at 3:12 PM, Alexandre Rafalovitch <ar...@gmail.com>
> wrote:
>
>> I can't remember what document element does, but I am quite sure the
>> entities just need to be side-by-side.
>>
>> See the example from my book:
>>
>> https://github.com/arafalov/solr-indexing-book/blob/master/published/dihdb/conf/dih-definition.xml
>>
>> Notice that you need preImportDeleteQuery for each definition,
>> otherwise they will do "delete *" and break each other's content.
>>
>> Regards,
>> Alex.
>> Personal: http://www.outerthoughts.com/ and @arafalov
>> Solr resources and newsletter: http://www.solr-start.com/ and @solrstart
>> Solr popularizers community: https://www.linkedin.com/groups?gid=6713853
>>
>>
>> On 4 November 2014 14:52, Tim Dunphy <bl...@gmail.com> wrote:
>> > Hey all,
>> >
>> > I finally got mysql data into Solr 4 with your help. First off, thank
>> you
>> > for that!
>> >
>> > But now I'm hoping to refine the resulting process a bit.
>> >
>> > What I'm trying to do, now that mysql imports are working, is to import
>> 2
>> > separate tables from the same mysql database. I tried this in my xml and
>> it
>> > doesn't work:
>> >
>> > <dataConfig>
>> > <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
>> > url="jdbc:mysql://web1.mydomain.com:3306/mydomain" user="admin"
>> > password=“secret” batchSize="1" />
>> > <document name="mydomain_users">
>> > <entity name="mydomain_users" query="SELECT * FROM wp_users">
>> > <field column="ID" name="ID" />
>> > <field column="user_login" name="user_login" />
>> > <field column="user_nicename" name="user_nicename" />
>> > <field column="user_email" name="user_email" />
>> > <field column="user_url" name="user_url" />
>> > <field column="user_registered" name="user_registered" />
>> > <field column="user_activation_key"
>> name="user_activation_key"
>> > />
>> > <field column="user_status" name="user_status" />
>> > <field column="display_name" name="display_name" />
>> > </entity>
>> > </document>
>> >
>> > <document name="mydomain_posts">
>> > <entity name="mydomain_posts" query="SELECT * FROM wp_posts">
>> > <field column="ID" name="ID" />
>> > <field column="post_author" name="post_author" />
>> > <field column="post_date" name="post_date" />
>> > <field column="post_content" name="post_content" />
>> > </entity>
>> > </document>
>> > </dataConfig>
>> >
>> > What happens is that the first table (document name mydomain_users) gets
>> > imported. But the second table (document name mydomain_posts) does not.
>> >
>> > Can anybody tell me what I'm doing wrong? I know that I need to get a
>> > little more used to using XML.
>> >
>> > Thanks
>> > Tim
>> >
>> > --
>> > GPG me!!
>> >
>> > gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
>>
>
>
>
> --
> GPG me!!
>
> gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: import 2 mysql tables into Solr 4
Posted by Tim Dunphy <bl...@gmail.com>.
Hey Alexandre,
Thanks for the example! This is what worked for me:
<dataConfig>
<dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://web1.mydomain.com:3306/mydomain" user="admin"
password=“secret” batchSize="1" />
<document name="mydomain_db">
<entity name="mydomain_users" query="SELECT * FROM wp_users">
<field column="ID" name="ID" />
<field column="user_login" name="user_login" />
<field column="user_nicename" name="user_nicename" />
<field column="user_email" name="user_email" />
<field column="user_url" name="user_url" />
<field column="user_registered" name="user_registered" />
<field column="user_activation_key" name="user_activation_key"
/>
<field column="user_status" name="user_status" />
<field column="display_name" name="display_name" />
</entity>
<entity name="mydomain_posts" query="SELECT * FROM wp_posts">
<field column="ID" name="ID" />
<field column="post_author" name="post_author" />
<field column="post_date" name="post_date" />
<field column="post_content" name="post_content" />
</entity>
</document>
</dataConfig>
Thank you again!
Tim
On Tue, Nov 4, 2014 at 3:12 PM, Alexandre Rafalovitch <ar...@gmail.com>
wrote:
> I can't remember what document element does, but I am quite sure the
> entities just need to be side-by-side.
>
> See the example from my book:
>
> https://github.com/arafalov/solr-indexing-book/blob/master/published/dihdb/conf/dih-definition.xml
>
> Notice that you need preImportDeleteQuery for each definition,
> otherwise they will do "delete *" and break each other's content.
>
> Regards,
> Alex.
> Personal: http://www.outerthoughts.com/ and @arafalov
> Solr resources and newsletter: http://www.solr-start.com/ and @solrstart
> Solr popularizers community: https://www.linkedin.com/groups?gid=6713853
>
>
> On 4 November 2014 14:52, Tim Dunphy <bl...@gmail.com> wrote:
> > Hey all,
> >
> > I finally got mysql data into Solr 4 with your help. First off, thank
> you
> > for that!
> >
> > But now I'm hoping to refine the resulting process a bit.
> >
> > What I'm trying to do, now that mysql imports are working, is to import
> 2
> > separate tables from the same mysql database. I tried this in my xml and
> it
> > doesn't work:
> >
> > <dataConfig>
> > <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
> > url="jdbc:mysql://web1.mydomain.com:3306/mydomain" user="admin"
> > password=“secret” batchSize="1" />
> > <document name="mydomain_users">
> > <entity name="mydomain_users" query="SELECT * FROM wp_users">
> > <field column="ID" name="ID" />
> > <field column="user_login" name="user_login" />
> > <field column="user_nicename" name="user_nicename" />
> > <field column="user_email" name="user_email" />
> > <field column="user_url" name="user_url" />
> > <field column="user_registered" name="user_registered" />
> > <field column="user_activation_key"
> name="user_activation_key"
> > />
> > <field column="user_status" name="user_status" />
> > <field column="display_name" name="display_name" />
> > </entity>
> > </document>
> >
> > <document name="mydomain_posts">
> > <entity name="mydomain_posts" query="SELECT * FROM wp_posts">
> > <field column="ID" name="ID" />
> > <field column="post_author" name="post_author" />
> > <field column="post_date" name="post_date" />
> > <field column="post_content" name="post_content" />
> > </entity>
> > </document>
> > </dataConfig>
> >
> > What happens is that the first table (document name mydomain_users) gets
> > imported. But the second table (document name mydomain_posts) does not.
> >
> > Can anybody tell me what I'm doing wrong? I know that I need to get a
> > little more used to using XML.
> >
> > Thanks
> > Tim
> >
> > --
> > GPG me!!
> >
> > gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
>
--
GPG me!!
gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: import 2 mysql tables into Solr 4
Posted by Alexandre Rafalovitch <ar...@gmail.com>.
I can't remember what document element does, but I am quite sure the
entities just need to be side-by-side.
See the example from my book:
https://github.com/arafalov/solr-indexing-book/blob/master/published/dihdb/conf/dih-definition.xml
Notice that you need preImportDeleteQuery for each definition,
otherwise they will do "delete *" and break each other's content.
Regards,
Alex.
Personal: http://www.outerthoughts.com/ and @arafalov
Solr resources and newsletter: http://www.solr-start.com/ and @solrstart
Solr popularizers community: https://www.linkedin.com/groups?gid=6713853
On 4 November 2014 14:52, Tim Dunphy <bl...@gmail.com> wrote:
> Hey all,
>
> I finally got mysql data into Solr 4 with your help. First off, thank you
> for that!
>
> But now I'm hoping to refine the resulting process a bit.
>
> What I'm trying to do, now that mysql imports are working, is to import 2
> separate tables from the same mysql database. I tried this in my xml and it
> doesn't work:
>
> <dataConfig>
> <dataSource type="JdbcDataSource" driver="com.mysql.jdbc.Driver"
> url="jdbc:mysql://web1.mydomain.com:3306/mydomain" user="admin"
> password=“secret” batchSize="1" />
> <document name="mydomain_users">
> <entity name="mydomain_users" query="SELECT * FROM wp_users">
> <field column="ID" name="ID" />
> <field column="user_login" name="user_login" />
> <field column="user_nicename" name="user_nicename" />
> <field column="user_email" name="user_email" />
> <field column="user_url" name="user_url" />
> <field column="user_registered" name="user_registered" />
> <field column="user_activation_key" name="user_activation_key"
> />
> <field column="user_status" name="user_status" />
> <field column="display_name" name="display_name" />
> </entity>
> </document>
>
> <document name="mydomain_posts">
> <entity name="mydomain_posts" query="SELECT * FROM wp_posts">
> <field column="ID" name="ID" />
> <field column="post_author" name="post_author" />
> <field column="post_date" name="post_date" />
> <field column="post_content" name="post_content" />
> </entity>
> </document>
> </dataConfig>
>
> What happens is that the first table (document name mydomain_users) gets
> imported. But the second table (document name mydomain_posts) does not.
>
> Can anybody tell me what I'm doing wrong? I know that I need to get a
> little more used to using XML.
>
> Thanks
> Tim
>
> --
> GPG me!!
>
> gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B