• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Open Source Project or Source Code - Importing/Moving MySQL table to Elastic Search

 
Ranch Hand
Posts: 123
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,

I have a table with size of 60 GB. I want to move the table to elastic search for faster search. Currently it takes more minutes to run even a count(1) query.

Is there is open source project which does this, so that we can use it for faster searching of results? Also whenever there is a considerable updates in the table need to push the changes to elastic search

There are about 30 columns in the table and all are NOT indexed. Adding index to all the column will impact the DB and also the INSERT into DB.

However wanted to do a full text search for any of the column. Please advise.

Thanks
 
Saloon Keeper
Posts: 27817
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
ElasticSearch is based on the Lucene search database and engine. It is not intended to be used in the same way as a SQL database.

Incidentally, Lucene is the engine that you invoke when you search for something on the Ranch.

Normally, Lucene does not have that much overhead, as it's incrementally populated as you add records. Only the initial load will be demanding,

If you don't mind investigating commercial solutions, Hitachivantara has an ElasticSearch bulk populating feature. Their Pentaho product line has been around for a long time now and a lot of it is available in free and open-source community editions. Their ETL (extract/transform/load) product is my go-to for pulling data from one database/spreadsheet/CSV file or whatever to sanitize, convert, augment, and upload into other databases/spreadsheets/CSV file(s) or whatevers. Used it just the other day to move my recipe database from SQLite to MySQL, in fact. I like it so much, I even contributed some improvements to the product (Excel Input).
 
Chris Mary
Ranch Hand
Posts: 123
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tim, Holloway...

Great and Ranch is working fine...

Can you please help me with some links or source code to migrate the table to Lucense and perform the search.

Is there is any UI for lucense as front-end to search or any open source UI which binds the lucence?

Also is there a way to update the lucene whenever there are rows are added in the table ?
 
Tim Holloway
Saloon Keeper
Posts: 27817
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You don't "migrate" to Lucence, it's an independent database. Our main forum data lives in a PostgreSQL database and Lucene provides the free-text indexing for it.

Normally you would invoke the Lucene API any time you added, updated, or deleted a Lucence-indexed column value.

There is a tool called Luke that can be used to manage Lucene.
 
Chris Mary
Ranch Hand
Posts: 123
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tim Holloway,

I need a API and UI to search for a keyword for mysql.

What you suggest?
 
Tim Holloway
Saloon Keeper
Posts: 27817
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
https://lucene.apache.org/
 
Chris Mary
Ranch Hand
Posts: 123
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Looks lucene is out of date - https://code.google.com/archive/p/luke/

Any other new library or any latest luke version ?
 
Tim Holloway
Saloon Keeper
Posts: 27817
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Chris Mary wrote:Looks lucene is out of date - https://code.google.com/archive/p/luke/

Any other new library or any latest luke version ?



lucene.apache.org wrote:
Now Luke is a part of Apache Lucene

 
Chris Mary
Ranch Hand
Posts: 123
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Any Apache Lucene URL? How to run in a linux box?
 
Tim Holloway
Saloon Keeper
Posts: 27817
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
https://lucene.apache.org/

The official website of the Apache Lucene project. Everything else hangs off of there.

Lucene is implemented in Java JAR form and is "write once/run anywhere". All is explained on the Lucene site. Though a web search should provide you with plenty of examples.
 
Chris Mary
Ranch Hand
Posts: 123
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tim, how about adding index via FULLText mentioned in MySQL - https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html for 50 to 80gb table size. Does it will work?
 
Tim Holloway
Saloon Keeper
Posts: 27817
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I would imagine so. This is a feature unique to MySQL and only to MySQL tables in InnoDB or MyISAM format. It is not available in standard SQL databases.

It is implemented internally using inverted indexes just like Apache Lucene. However, whether it is more performant than Lucene or not I do not know.
 
Chris Mary
Ranch Hand
Posts: 123
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So, what is your kind suggestion for the problem Tim?
 
Rancher
Posts: 326
14
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
@Mods
Please check if merge should be done:
https://coderanch.com/t/755206/databases/DB-selection
https://coderanch.com/t/755287/java/Java-Standalone-Search-Window-Program
https://coderanch.com/t/755288/databases/iterate-read-million-records-MySQL
https://coderanch.com/t/755293/databases/Possibility-Query-Time-Result
https://coderanch.com/t/755307/databases/form-query
https://coderanch.com/t/755310/open-source/Lookup-keyword-table
https://coderanch.com/t/755288/databases/iterate-read-million-records-MySQL
https://coderanch.com/t/755309/databases/Query-Time-Summary
https://coderanch.com/t/755297/java/Full-TEXT-search-quick-results
https://coderanch.com/t/755308/java/Fastest-export-millions-records-CSV
https://coderanch.com/t/755312/databases/add-index-columns

@Chris
As all these question seem to be related to the same overall topic - PLEASE, for F*s sake, keep it as ONE instead of open a dozen different ones in several different sub-forums.
One of your other topics is titled "How to improve skill?" - I strongly recommend to you to familiarize how forums such as coderanch work and how to use them in order to get answers to your questions.
 
Tim Holloway
Saloon Keeper
Posts: 27817
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ouch!

Yes, I have also mentioned that, if a little less forcefully.

We're going to leave them as separate threads, I think, because no one wants to glue that much stuff together and it would probably end up being even more confusing than leaving them as they are. But again, if you have a major topic keep it all in one thread. If we think it needs a separate thread, we'll tell you.
 
Chris Mary
Ranch Hand
Posts: 123
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sure, Tim. I thought keeping as separate thread in general will not confused and make the people easy to focus on one at a time. Sorry if that causes a confusion.
 
Tim Holloway
Saloon Keeper
Posts: 27817
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Chris Mary wrote:Sure, Tim. I thought keeping as separate thread in general will not confused and make the people easy to focus on one at a time. Sorry if that causes a confusion.



Well, it depends. When you're asking questions on 2 very different subjects, separate threads are preferable. But when they're all closely related, it's less confusing to keep it all in one thread.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic