# Newb questions on indexing data from SQL Server and how to setup ES **URL:** \ **Category:** Elasticsearch\ **Created:** [January 16, 2013, 4:00pm UTC](https://discuss.elastic.co/t/newb-questions-on-indexing-data-from-sql-server-and-how-to-setup-es/10375 "2013-01-16T16:00:31Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Lee\_Duhl](https://avatars.discourse-cdn.com/v4/letter/l/5e9695/32.png) [@Lee\_Duhl](https://discuss.elastic.co/u/Lee_Duhl)\ **Post date:** [January 16, 2013, 4:00pm UTC](https://discuss.elastic.co/t/newb-questions-on-indexing-data-from-sql-server-and-how-to-setup-es/10375/1 "2013-01-16T16:00:31Z")
I am looking at ES as a replacement to the 3rd party index server that we are currently using on property search website. I have not installed ES yet as I’m just gathering information at this point, but I do expect to install a test instance within the next couple of days and start working with it. Our main need is to index a set of property data that comes out of our SQL server database. We are talking only a relatively small number of db records (50k initially but could go to a couple hundred thousand depending on how we leverage ES). My concern is that our data updates frequently and we will want to keep somewhat close to real-time data in our index server. This will require a rather constant stream of creates, updates and deletes to the target indexes. Based on what I've read so far, there appears to be 2 applicable ways to get SQL data into the index server: 1) River and 2) ETL process that will probably post to the "\_Bulk" API method. 1. A "River" seems like the easiest method, but the samples I've seen have to do with creating/updating documents in the index and I've not seen any way to delete documents. Is there a way to delete documents via a river and if so how is that done? 2. An ETL "Post" to the "\_Bulk" api method will require a little more work but it appears to be a more robust solution that directly supports deleting documents. Samples/API, however, only review to "Create" and "Delete" methods for handling documents and I'm a bit confused on how the "Create" method handles updates? I've read the info on versioning that applies to creates, but I'm not really interested in any previous version of a document. I want "Create" actions to existing documents just to overwrite the existing document. Is there anything I need to do/setup to handle the "Create" to make sure that any existing document is overwritten? Once data is posted it needs to be available for searching immediately? Are there any concerns I need to address from an "Index" standpoint to make this possible? Other things to know: - I am initially looking at using a single "Index" that would contain all of our property records. Is there any reason other than fault tolerance not to do this? - I am initially looking at using a simple single, possible 2 server cluster setup. Is there any reason other than fault tolerance not to do this? - As far as searching goes, we are looking to some basic field searching (matches, starts with, contains) in combination with some Geospatial searching (polygon and radius). Any info provided will be greatly appreciated Thanks Lee -- ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [January 16, 2013, 4:24pm UTC](https://discuss.elastic.co/t/newb-questions-on-indexing-data-from-sql-server-and-how-to-setup-es/10375/2 "2013-01-16T16:24:41Z")
Hi Lee, I would say first that you don't have to wait to install and launch an instance. It takes some seconds to be up and running! 😉 That said, yes you're right, there are some options here: 1. river: Jörg did an amazing job with the jdbc river and it could be a good point to start with it as it won't cost you much development to have all the stuff running. About deletions, look at what Jörg wrote about it: [GitHub - jprante/elasticsearch-jdbc: JDBC importer for Elasticsearch](https://github.com/jprante/elasticsearch-river-jdbc#managing-updates-with-elasticsearch-versioning) [https://github.com/jprante/elasticsearch-river-jdbc#managing-updates-with-elasticsearch-versioning](https://github.com/jprante/elasticsearch-river-jdbc#managing-updates-with-elasticsearch-versioning) 2. ETL: To me, ETL means that an external process is getting information from your database (Extract), then parse it and create documents (Transform) and then index it in Elasticsearch (Load). Fine. I think there is a third option here. 3. index from your business or service layer: I don't know if you have an access to the code that create,update and delete your entities, but let's imagine you can do it. Just after C(R)UD operations, you can index or delete documents in Elasticsearch directly. It won't cost you much (in term of performance). You can also think of it in an asynchronous way with ActiveMQ or RabbitMQ... So each time a creation or deletion occurs, you will send the action to Elasticsearch. For the first load process (aka initialize ES), you can create a batch that read all your entities and index them 10000 by 10000 for example using bulks. This last option is the one I used on a previous project and we were happy with that. Some notes: When you send a document to ES, by default, ES will index it or update it. ES never stores different versions of the same doc. Versioning is here for concurrency management. When a document is posted, you can GET it immediately but you will have to wait for about 1 second before it will be searchable. If you really need to, you can sometimes call the refresh API to make your documents searchable but you should avoid of using it. Is your use case really need that once the document is sent, it must be in the search result? About you hardware, it depends if you want to avoid downtimes or not. If you want to have a real cluster, start 2 nodes on 2 different boxes. If one box crash, your users won't see the difference. One single index seems to be fine but hard to tell without knowing your use case and what kind of documents you are going to index. Does it help? David. Le 16 janvier 2013 à 17:00, Lee Duhl [leevduhl@gmail.com](mailto:leevduhl@gmail.com) a écrit : > I am looking at ES as a replacement to the 3rd party index server that we are > currently using on property search website. I have not installed ES yet as I’m > just gathering information at this point, but I do expect to install a test > instance within the next couple of days and start working with it. > > \ Our main need is to index a set of property data that comes out of our SQL server database. We are talking only a relatively small number of db records (50k initially but could go to a couple hundred thousand depending on how we leverage ES). My concern is that our data updates frequently and we will want to keep somewhat close to real-time data in our index server. This will require a rather constant stream of creates, updates and deletes to the target indexes. Based on what I've read so far, there appears to be 2 applicable ways to get SQL data into the index server: 1) River and 2) ETL process that will probably post to the "\_Bulk" API method. 1) A "River" seems like the easiest method, but the samples I've seen have to do with creating/updating documents in the index and I've not seen any way to delete documents. Is there a way to delete documents via a river and if so how is that done? 2) An ETL "Post" to the "\_Bulk" api method will require a little more work but it appears to be a more robust solution that directly supports deleting documents. Samples/API, however, only review to "Create" and "Delete" methods for handling documents and I'm a bit confused on how the "Create" method handles updates? I've read the info on versioning that applies to creates, but I'm not really interested in any previous version of a document. I want "Create" actions to existing documents just to overwrite the existing document. Is there anything I need to do/setup to handle the "Create" to make sure that any existing document is overwritten? Once data is posted it needs to be available for searching immediately? Are there any concerns I need to address from an "Index" standpoint to make this possible? Other things to know: - I am initially looking at using a single "Index" that would contain all of our property records. Is there any reason other than fault tolerance not to do this? - I am initially looking at using a simple single, possible 2 server cluster setup. Is there any reason other than fault tolerance not to do this? - As far as searching goes, we are looking to some basic field searching (matches, starts with, contains) in combination with some Geospatial searching (polygon and radius). Any info provided will be greatly appreciated Thanks Lee -- -- David Pilato [http://www.scrutmydocs.org/](http://www.scrutmydocs.org/) [http://dev.david.pilato.fr/](http://dev.david.pilato.fr/) Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs -- ---
**Author:** ![Ivan](https://avatars.discourse-cdn.com/v4/letter/i/df788c/32.png) [@Ivan](https://discuss.elastic.co/u/Ivan)\ **Post date:** [January 16, 2013, 4:24pm UTC](https://discuss.elastic.co/t/newb-questions-on-indexing-data-from-sql-server-and-how-to-setup-es/10375/3 "2013-01-16T16:24:50Z")
Hi Lee, Responses inline. On Wed, Jan 16, 2013 at 8:00 AM, Lee Duhl [leevduhl@gmail.com](mailto:leevduhl@gmail.com) wrote: > 1. A "River" seems like the easiest method, but the samples I've seen have > to do with creating/updating documents in the index and I've not seen any > way to delete documents. Is there a way to delete documents via a river and > if so how is that done? Technically a river is an embedded application that is run as a singleton in the cluster. Only one node at a time will run the river. A river can do anything, not just indexing. Since it is part of the JVM, it can use an exposed modules like any other plugins. Of course, AFAIK rivers have only been used for indexing, which is their only use case. Most rivers will bulk index and can delete documents as well. The full indexing API can be used. > 1. An ETL "Post" to the "\_Bulk" api method will require a little more work > but it appears to be a more robust solution that directly supports deleting > documents. Samples/API, however, only review to "Create" and "Delete" > methods for handling documents and I'm a bit confused on how the "Create" > method handles updates? I've read the info on versioning that applies to > creates, but I'm not really interested in any previous version of a > document. I want "Create" actions to existing documents just to overwrite > the existing document. Is there anything I need to do/setup to handle the > "Create" to make sure that any existing document is overwritten? Elasticsearch only keep tracks of version numbers, not the contents of each version. Many developers ignore the version number returned by ES. Elasticsearch uses Lucene underneath, so it has no true update mechanism. Each update is actually an atomic delete-create. Once data is posted it needs to be available for searching immediately? Are > there any concerns I need to address from an "Index" standpoint to make > this possible? Data will become available after the refresh interval, the default being 1 second. Get requests should contain all data since they work off both the index and the transaction log. > * * * > > * * * > > Other things to know:\*\*\*\* > > - I am initially looking at using a single "Index" that would contain all > of our property records. Is there any reason other than fault tolerance not > to do this? Sounds good. A single index is ideal for many reasons such as less OS-level file resources and better shard allocation (not competing with other indices). > * * * > > - I am initially looking at using a simple single, possible 2 server > cluster setup. Is there any reason other than fault tolerance not to do > this? Sounds good. > * * * > > - As far as searching goes, we are looking to some basic field searching > (matches, starts with, contains) in combination with some Geospatial > searching (polygon and radius). That's what a search engine does! Cheers, Ivan -- ---
**Author:** ![Lee\_Duhl](https://avatars.discourse-cdn.com/v4/letter/l/5e9695/32.png) [@Lee\_Duhl](https://discuss.elastic.co/u/Lee_Duhl)\ **Post date:** [January 16, 2013, 8:35pm UTC](https://discuss.elastic.co/t/newb-questions-on-indexing-data-from-sql-server-and-how-to-setup-es/10375/4 "2013-01-16T20:35:53Z")
Wow, thanks for the quick responses to my questoins. Over the next day or so I'll look into the info supplied and post back with my findings/additional questions. Thanks Lee On Wednesday, January 16, 2013 11:00:31 AM UTC-5, Lee Duhl wrote: > I am looking at ES as a replacement to the 3rd party index server that we > are currently using on property search website. I have not installed ES yet > as I’m just gathering information at this point, but I do expect to install > a test instance within the next couple of days and start working with it. > > Our main need is to index a set of property data that comes out of our SQL > server database. We are talking only a relatively small number of db > records (50k initially but could go to a couple hundred thousand depending > on how we leverage ES). My concern is that our data updates frequently and > we will want to keep somewhat close to real-time data in our index server. > This will require a rather constant stream of creates, updates and deletes > to the target indexes. > > Based on what I've read so far, there appears to be 2 applicable ways to > get SQL data into the index server: 1) River and 2) ETL process that will > probably post to the "\_Bulk" API method. > > 1. A "River" seems like the easiest method, but the samples I've seen have > to do with creating/updating documents in the index and I've not seen any > way to delete documents. Is there a way to delete documents via a river and > if so how is that done? > > 2. An ETL "Post" to the "\_Bulk" api method will require a little more work > but it appears to be a more robust solution that directly supports deleting > documents. Samples/API, however, only review to "Create" and "Delete" > methods for handling documents and I'm a bit confused on how the "Create" > method handles updates? I've read the info on versioning that applies to > creates, but I'm not really interested in any previous version of a > document. I want "Create" actions to existing documents just to overwrite > the existing document. Is there anything I need to do/setup to handle the > "Create" to make sure that any existing document is overwritten? > > Once data is posted it needs to be available for searching immediately? > Are there any concerns I need to address from an "Index" standpoint to make > this possible? > > Other things to know: > > - I am initially looking at using a single "Index" that would contain all > of our property records. Is there any reason other than fault tolerance not > to do this? > > - I am initially looking at using a simple single, possible 2 server > cluster setup. Is there any reason other than fault tolerance not to do > this? > > - As far as searching goes, we are looking to some basic field searching > (matches, starts with, contains) in combination with some Geospatial > searching (polygon and radius). > > Any info provided will be greatly appreciated > > Thanks > > Lee -- ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 2:56am UTC](https://discuss.elastic.co/t/newb-questions-on-indexing-data-from-sql-server-and-how-to-setup-es/10375/5 "2017-07-06T02:56:05Z")