You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@solr.apache.org by Anuj Bhargava <an...@gmail.com> on 2023/01/13 14:46:42 UTC
Importing Data from MySql
How do I import data from MySql database in Solr 9.1
Earlier I was using DIH in version 8.6.2
Regards,
Anuj
Re: Importing Data from MySql
Posted by dmitri maziuk <dm...@gmail.com>.
On 2023-01-13 11:12 PM, Anuj Bhargava wrote:
> So what would be the advice to a layman like me?
>
> 1. Use Solr 8.x with builtin DIH
> or
> 2. Use a 3rd part DIH with Solr 9.x
Export your data as JSON documents (look it up on Google) and POST them
to Solr with curl (look it up in Solr documentation). Most of us will do
that in a script because we can, but in the simple case the above should
work too.
Dima
Re: Importing Data from MySql
Posted by Anuj Bhargava <an...@gmail.com>.
So what would be the advice to a layman like me?
1. Use Solr 8.x with builtin DIH
or
2. Use a 3rd part DIH with Solr 9.x
I have more than 20 cores, updating data from mysql daily through cron.
Regards,
Anuj
On Fri, 13 Jan 2023 at 20:33, Markus Jelsma <ma...@openindex.io>
wrote:
> Hello,
>
> DIH was removed in Solr 9.x. You can either build your own importer, or use
> the, now 3rd party, DIH [1] that is available as a package.
>
> Regards,
> Markus
>
> [1] https://github.com/rohitbemax/dataimporthandler
>
> Op vr 13 jan. 2023 om 14:47 schreef Anuj Bhargava <an...@gmail.com>:
>
> > How do I import data from MySql database in Solr 9.1
> >
> > Earlier I was using DIH in version 8.6.2
> >
> > Regards,
> >
> > Anuj
> >
>
Re: Importing Data from MySql
Posted by Gus Heck <gu...@gmail.com>.
Not sure I'd say it's trivial. But there are lots of folks who've done it
successfully. As noted, batching is important, depending on the nature of
the data fault tolerance can be important too. Daily data loads are a bit
different than continuous feeds of data. Also depends on to what extent
one wants to massage the data going in to reduce load on the server from
indexing. Size of the dataset can imply performance needs, and scalability
etc.
-Gus
On Fri, Jan 13, 2023 at 10:41 AM Dave <ha...@gmail.com> wrote:
> Yeah, it’s trivial building your own indexer in any language that can read
> a db. Also I wouldn’t trust the dih on its own even when supported
>
> > On Jan 13, 2023, at 10:17 AM, Jan Høydahl <ja...@cominvent.com> wrote:
> >
> > I don't think the 3rd party DIH is maintained.
> >
> > Other options are using other 3rd party frameworks like
> > ManifoldCF <
> https://datafari.atlassian.net/wiki/spaces/DATAFARI/pages/2718007297/How+to+crawl+a+database+on+Solr+without+DIH+-+DIH+alternative>
> or Apache NiFI <
> https://www.datainmotion.dev/2020/04/building-search-indexes-with-apache.html>
> which is a more visual approach.
> > Personally I'd write a small Python program.
> >
> > When asking ChatGPT to sketch such a program, this is what I got :)
> >
> > Sure, here is a sample Python program that demonstrates how to import
> data from a MySQL database and index it into Solr using the SolrPy library:
> >
> > import mysql.connector
> > from solr import Solr
> >
> > # Connect to MySQL database
> > cnx = mysql.connector.connect(user='username',
> > password='password',
> > host='hostname',
> > database='database_name')
> > cursor = cnx.cursor()
> >
> > # Execute MySQL query
> > query = "SELECT * FROM table_name"
> > cursor.execute(query)
> >
> > # Fetch all data
> > data = cursor.fetchall()
> >
> > # Connect to Solr
> > solr = Solr('http://localhost:8983/solr/core_name')
> >
> > # Index data into Solr
> > for row in data:
> > solr.add(row)
> >
> > # Commit changes
> > solr.commit()
> >
> > # Close database and Solr connection
> > cursor.close()
> > cnx.close()
> > solr.close()
> >
> >
> > I have not tested it, but with some massage you should be able to fly
> >
> > Jan
> >
> >> 13. jan. 2023 kl. 16:02 skrev Markus Jelsma <markus.jelsma@openindex.io
> >:
> >>
> >> Hello,
> >>
> >> DIH was removed in Solr 9.x. You can either build your own importer, or
> use
> >> the, now 3rd party, DIH [1] that is available as a package.
> >>
> >> Regards,
> >> Markus
> >>
> >> [1] https://github.com/rohitbemax/dataimporthandler
> >>
> >>> Op vr 13 jan. 2023 om 14:47 schreef Anuj Bhargava <anujboss@gmail.com
> >:
> >>>
> >>> How do I import data from MySql database in Solr 9.1
> >>>
> >>> Earlier I was using DIH in version 8.6.2
> >>>
> >>> Regards,
> >>>
> >>> Anuj
> >>>
> >
>
--
http://www.needhamsoftware.com (work)
http://www.the111shift.com (play)
Re: Importing Data from MySql
Posted by Dave <ha...@gmail.com>.
Agreed. I like either waiting until the entire thing is done or a modulus of like 1000 if you’re worried about it failing part way through and it’s big. The less commits the better
> On Jan 13, 2023, at 10:49 AM, Thomas Corthals <th...@klascement.net> wrote:
>
> Build an indexer that can send updates in batches. It'll be faster than
> sending each document in a separate request.
>
> Op vr 13 jan. 2023 om 16:41 schreef Dave <ha...@gmail.com>:
>
>> Yeah, it’s trivial building your own indexer in any language that can read
>> a db. Also I wouldn’t trust the dih on its own even when supported
>>
>>>> On Jan 13, 2023, at 10:17 AM, Jan Høydahl <ja...@cominvent.com> wrote:
>>>
>>> I don't think the 3rd party DIH is maintained.
>>>
>>> Other options are using other 3rd party frameworks like
>>> ManifoldCF <
>> https://datafari.atlassian.net/wiki/spaces/DATAFARI/pages/2718007297/How+to+crawl+a+database+on+Solr+without+DIH+-+DIH+alternative>
>> or Apache NiFI <
>> https://www.datainmotion.dev/2020/04/building-search-indexes-with-apache.html>
>> which is a more visual approach.
>>> Personally I'd write a small Python program.
>>>
>>> When asking ChatGPT to sketch such a program, this is what I got :)
>>>
>>> Sure, here is a sample Python program that demonstrates how to import
>> data from a MySQL database and index it into Solr using the SolrPy library:
>>>
>>> import mysql.connector
>>> from solr import Solr
>>>
>>> # Connect to MySQL database
>>> cnx = mysql.connector.connect(user='username',
>>> password='password',
>>> host='hostname',
>>> database='database_name')
>>> cursor = cnx.cursor()
>>>
>>> # Execute MySQL query
>>> query = "SELECT * FROM table_name"
>>> cursor.execute(query)
>>>
>>> # Fetch all data
>>> data = cursor.fetchall()
>>>
>>> # Connect to Solr
>>> solr = Solr('http://localhost:8983/solr/core_name')
>>>
>>> # Index data into Solr
>>> for row in data:
>>> solr.add(row)
>>>
>>> # Commit changes
>>> solr.commit()
>>>
>>> # Close database and Solr connection
>>> cursor.close()
>>> cnx.close()
>>> solr.close()
>>>
>>>
>>> I have not tested it, but with some massage you should be able to fly
>>>
>>> Jan
>>>
>>>> 13. jan. 2023 kl. 16:02 skrev Markus Jelsma <markus.jelsma@openindex.io
>>> :
>>>>
>>>> Hello,
>>>>
>>>> DIH was removed in Solr 9.x. You can either build your own importer, or
>> use
>>>> the, now 3rd party, DIH [1] that is available as a package.
>>>>
>>>> Regards,
>>>> Markus
>>>>
>>>> [1] https://github.com/rohitbemax/dataimporthandler
>>>>
>>>>> Op vr 13 jan. 2023 om 14:47 schreef Anuj Bhargava <anujboss@gmail.com
>>> :
>>>>>
>>>>> How do I import data from MySql database in Solr 9.1
>>>>>
>>>>> Earlier I was using DIH in version 8.6.2
>>>>>
>>>>> Regards,
>>>>>
>>>>> Anuj
>>>>>
>>>
>>
Re: Importing Data from MySql
Posted by Thomas Corthals <th...@klascement.net>.
Build an indexer that can send updates in batches. It'll be faster than
sending each document in a separate request.
Op vr 13 jan. 2023 om 16:41 schreef Dave <ha...@gmail.com>:
> Yeah, it’s trivial building your own indexer in any language that can read
> a db. Also I wouldn’t trust the dih on its own even when supported
>
> > On Jan 13, 2023, at 10:17 AM, Jan Høydahl <ja...@cominvent.com> wrote:
> >
> > I don't think the 3rd party DIH is maintained.
> >
> > Other options are using other 3rd party frameworks like
> > ManifoldCF <
> https://datafari.atlassian.net/wiki/spaces/DATAFARI/pages/2718007297/How+to+crawl+a+database+on+Solr+without+DIH+-+DIH+alternative>
> or Apache NiFI <
> https://www.datainmotion.dev/2020/04/building-search-indexes-with-apache.html>
> which is a more visual approach.
> > Personally I'd write a small Python program.
> >
> > When asking ChatGPT to sketch such a program, this is what I got :)
> >
> > Sure, here is a sample Python program that demonstrates how to import
> data from a MySQL database and index it into Solr using the SolrPy library:
> >
> > import mysql.connector
> > from solr import Solr
> >
> > # Connect to MySQL database
> > cnx = mysql.connector.connect(user='username',
> > password='password',
> > host='hostname',
> > database='database_name')
> > cursor = cnx.cursor()
> >
> > # Execute MySQL query
> > query = "SELECT * FROM table_name"
> > cursor.execute(query)
> >
> > # Fetch all data
> > data = cursor.fetchall()
> >
> > # Connect to Solr
> > solr = Solr('http://localhost:8983/solr/core_name')
> >
> > # Index data into Solr
> > for row in data:
> > solr.add(row)
> >
> > # Commit changes
> > solr.commit()
> >
> > # Close database and Solr connection
> > cursor.close()
> > cnx.close()
> > solr.close()
> >
> >
> > I have not tested it, but with some massage you should be able to fly
> >
> > Jan
> >
> >> 13. jan. 2023 kl. 16:02 skrev Markus Jelsma <markus.jelsma@openindex.io
> >:
> >>
> >> Hello,
> >>
> >> DIH was removed in Solr 9.x. You can either build your own importer, or
> use
> >> the, now 3rd party, DIH [1] that is available as a package.
> >>
> >> Regards,
> >> Markus
> >>
> >> [1] https://github.com/rohitbemax/dataimporthandler
> >>
> >>> Op vr 13 jan. 2023 om 14:47 schreef Anuj Bhargava <anujboss@gmail.com
> >:
> >>>
> >>> How do I import data from MySql database in Solr 9.1
> >>>
> >>> Earlier I was using DIH in version 8.6.2
> >>>
> >>> Regards,
> >>>
> >>> Anuj
> >>>
> >
>
Re: Importing Data from MySql
Posted by Dave <ha...@gmail.com>.
Yeah, it’s trivial building your own indexer in any language that can read a db. Also I wouldn’t trust the dih on its own even when supported
> On Jan 13, 2023, at 10:17 AM, Jan Høydahl <ja...@cominvent.com> wrote:
>
> I don't think the 3rd party DIH is maintained.
>
> Other options are using other 3rd party frameworks like
> ManifoldCF <https://datafari.atlassian.net/wiki/spaces/DATAFARI/pages/2718007297/How+to+crawl+a+database+on+Solr+without+DIH+-+DIH+alternative> or Apache NiFI <https://www.datainmotion.dev/2020/04/building-search-indexes-with-apache.html> which is a more visual approach.
> Personally I'd write a small Python program.
>
> When asking ChatGPT to sketch such a program, this is what I got :)
>
> Sure, here is a sample Python program that demonstrates how to import data from a MySQL database and index it into Solr using the SolrPy library:
>
> import mysql.connector
> from solr import Solr
>
> # Connect to MySQL database
> cnx = mysql.connector.connect(user='username',
> password='password',
> host='hostname',
> database='database_name')
> cursor = cnx.cursor()
>
> # Execute MySQL query
> query = "SELECT * FROM table_name"
> cursor.execute(query)
>
> # Fetch all data
> data = cursor.fetchall()
>
> # Connect to Solr
> solr = Solr('http://localhost:8983/solr/core_name')
>
> # Index data into Solr
> for row in data:
> solr.add(row)
>
> # Commit changes
> solr.commit()
>
> # Close database and Solr connection
> cursor.close()
> cnx.close()
> solr.close()
>
>
> I have not tested it, but with some massage you should be able to fly
>
> Jan
>
>> 13. jan. 2023 kl. 16:02 skrev Markus Jelsma <ma...@openindex.io>:
>>
>> Hello,
>>
>> DIH was removed in Solr 9.x. You can either build your own importer, or use
>> the, now 3rd party, DIH [1] that is available as a package.
>>
>> Regards,
>> Markus
>>
>> [1] https://github.com/rohitbemax/dataimporthandler
>>
>>> Op vr 13 jan. 2023 om 14:47 schreef Anuj Bhargava <an...@gmail.com>:
>>>
>>> How do I import data from MySql database in Solr 9.1
>>>
>>> Earlier I was using DIH in version 8.6.2
>>>
>>> Regards,
>>>
>>> Anuj
>>>
>
Re: Importing Data from MySql
Posted by Jan Høydahl <ja...@cominvent.com>.
I don't think the 3rd party DIH is maintained.
Other options are using other 3rd party frameworks like
ManifoldCF <https://datafari.atlassian.net/wiki/spaces/DATAFARI/pages/2718007297/How+to+crawl+a+database+on+Solr+without+DIH+-+DIH+alternative> or Apache NiFI <https://www.datainmotion.dev/2020/04/building-search-indexes-with-apache.html> which is a more visual approach.
Personally I'd write a small Python program.
When asking ChatGPT to sketch such a program, this is what I got :)
Sure, here is a sample Python program that demonstrates how to import data from a MySQL database and index it into Solr using the SolrPy library:
import mysql.connector
from solr import Solr
# Connect to MySQL database
cnx = mysql.connector.connect(user='username',
password='password',
host='hostname',
database='database_name')
cursor = cnx.cursor()
# Execute MySQL query
query = "SELECT * FROM table_name"
cursor.execute(query)
# Fetch all data
data = cursor.fetchall()
# Connect to Solr
solr = Solr('http://localhost:8983/solr/core_name')
# Index data into Solr
for row in data:
solr.add(row)
# Commit changes
solr.commit()
# Close database and Solr connection
cursor.close()
cnx.close()
solr.close()
I have not tested it, but with some massage you should be able to fly
Jan
> 13. jan. 2023 kl. 16:02 skrev Markus Jelsma <ma...@openindex.io>:
>
> Hello,
>
> DIH was removed in Solr 9.x. You can either build your own importer, or use
> the, now 3rd party, DIH [1] that is available as a package.
>
> Regards,
> Markus
>
> [1] https://github.com/rohitbemax/dataimporthandler
>
> Op vr 13 jan. 2023 om 14:47 schreef Anuj Bhargava <an...@gmail.com>:
>
>> How do I import data from MySql database in Solr 9.1
>>
>> Earlier I was using DIH in version 8.6.2
>>
>> Regards,
>>
>> Anuj
>>
Re: Importing Data from MySql
Posted by Markus Jelsma <ma...@openindex.io>.
Hello,
DIH was removed in Solr 9.x. You can either build your own importer, or use
the, now 3rd party, DIH [1] that is available as a package.
Regards,
Markus
[1] https://github.com/rohitbemax/dataimporthandler
Op vr 13 jan. 2023 om 14:47 schreef Anuj Bhargava <an...@gmail.com>:
> How do I import data from MySql database in Solr 9.1
>
> Earlier I was using DIH in version 8.6.2
>
> Regards,
>
> Anuj
>