# JDBC River Fails When Mapping Is Used **URL:** \ **Category:** Elasticsearch\ **Created:** [April 11, 2014, 11:40am UTC](https://discuss.elastic.co/t/jdbc-river-fails-when-mapping-is-used/16952 "2014-04-11T11:40:11Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Ajay\_Singh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ajay_singh/32/1516_2.png) [@Ajay\_Singh](https://discuss.elastic.co/u/Ajay_Singh)\ **Post date:** [April 11, 2014, 11:40am UTC](https://discuss.elastic.co/t/jdbc-river-fails-when-mapping-is-used/16952/1 "2014-04-11T11:40:11Z")
We are facing a weird issue by using mapping on index. I am creating an index using this syntax - PUT /searchindex { "index": { "analysis": { "filter": { "mynGram" : {"type": "nGram", "min\_gram": 2, "max\_gram": 10} }, "analyzer": { "myAnalyzer" : { "type":"custom", "tokenizer": "standard", "filter": ["lowercase", "mynGram"] } } } } } After this I am putting this mapping - PUT /searchindex/searchproducts/\_mapping { "index\_analyzer" : "myAnalyzer", "search\_analyzer" : "standard", "properties" : { "\_id" : {"type" : "string", "include\_in\_all": false }, "ProductID" : {"type":"integer" }, "CategoryID": {"type":"integer" }, "ManufacturerID" : {"type":"integer" }, "ProductTitle": {"type": "string", "analyzer":"myAnalyzer"}, "MFName" : {"type": "string", "analyzer":"myAnalyzer"}, "CategoryName" : {"type": "string", "analyzer":"myAnalyzer"}, "TechnicalSpecification" : {"type": "string", "analyzer":"myAnalyzer"}, "MfgPartNumber" : {"type": "string", "analyzer":"myAnalyzer"}, "MarketingText" : {"type": "string", "analyzer":"myAnalyzer"}, "ImageName" : {"type": "string", "include\_in\_all": false}, "IsActive" : {"type": "boolean"}, "IsFeatured" : {"type": "boolean"}, "IsInCatalog" : {"type": "boolean"}, "IsMASInsertSync" : {"type": "integer"}, "EDelivery" : {"type": "boolean"}, "ProductStatus" : {"type": "integer"}, "MFLogo" : {"type": "string", "include\_in\_all": false}, "MPartNumber" : {"type": "string", "include\_in\_all": false}, "ParentCategory" : {"type": "integer"}, "ContainsAccessory" : {"type": "boolean"}, "ContainsCompatible" : {"type": "boolean"}, "EMfID" : {"type": "integer"}, "ECategoryID" : {"type": "integer"}, "ESmallImage" : {"type": "string", "include\_in\_all": false}, "ELargeImage" : {"type": "string", "include\_in\_all": false}, "ProductType" : {"type": "string", "analyzer":"myAnalyzer"}, "ProductName" : {"type": "string", "analyzer":"myAnalyzer"} } } After this I am using _JDBC River create by JPrante_ for fetching data from SQL Server 2005, following is the script AllWebSearchProducts Create River Script PUT /\_river/searchproductsriver/\_meta { "type" : "jdbc", "jdbc" : { "driver":"com.microsoft.sqlserver.jdbc.SQLServerDriver", "url":"jdbc:sqlserver://xxx.xx.x.xxx;databaseName=Products", "user":"adminaccount","password":"password", "sql":"SELECT ProductID as \_id FROM ProductAttributes(nolock)", "strategy" : "oneshot", "index" : "searchindex", "type" : "searchproducts", "bulk\_size" : 500, "max\_retries": 5, "max\_retries\_wait":"30s", "max\_bulk\_requests" : 30, "bulk\_flush\_interval" : "5s" } } Here SQL string is truncated just to keep it simple, I am actually using a long string with all the columns given in mapping and with multiple joins. This river is supposed to bring around 2.6 million products, but it stops after fetching 1.9 to 2 million products. I have tried running it many times but it fails. This river runs for around 3hrs 30mnts. However if I run this river _without creating the mapping, it works_ fine and all products are fetched in Index. Please let me know if I am doing something wrong. No error is given or logged by ElasticSearch, it simply stops running the river. Thanks & Regards, Ajay. -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/0ee8c762-6fb5-4326-a126-f0565a9dc5cd%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/0ee8c762-6fb5-4326-a126-f0565a9dc5cd%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [April 12, 2014, 8:12am UTC](https://discuss.elastic.co/t/jdbc-river-fails-when-mapping-is-used/16952/2 "2014-04-12T08:12:35Z")
From the symptoms you describe, it may be the ngram tokenzation puts more load on the cluster than it can handle, but without error messages, this must be a blind guess. You could play with max\_bulk\_requests and bulk\_size to reduce the number of docs submitted at once to see if this works better. Jörg On Fri, Apr 11, 2014 at 1:40 PM, Ajay Singh [ajay0055@gmail.com](mailto:ajay0055@gmail.com) wrote: > We are facing a weird issue by using mapping on index. I am creating an > index using this syntax - > > PUT /searchindex > { > "index": > { > "analysis": { > "filter": { > "mynGram" : {"type": "nGram", "min\_gram": 2, "max\_gram": > 10} > }, > "analyzer": { "myAnalyzer" : { > "type":"custom", > "tokenizer": "standard", > "filter": ["lowercase", "mynGram"] > } > } > } > } > } > > After this I am putting this mapping - > > PUT /searchindex/searchproducts/\_mapping > { > "index\_analyzer" : "myAnalyzer", > "search\_analyzer" : "standard", > "properties" : { > "\_id" : {"type" : "string", "include\_in\_all": false }, > "ProductID" : {"type":"integer" }, > "CategoryID": {"type":"integer" }, > "ManufacturerID" : {"type":"integer" }, > "ProductTitle": {"type": "string", "analyzer":"myAnalyzer"}, > "MFName" : {"type": "string", "analyzer":"myAnalyzer"}, > "CategoryName" : {"type": "string", "analyzer":"myAnalyzer"}, > "TechnicalSpecification" : {"type": "string", > "analyzer":"myAnalyzer"}, > "MfgPartNumber" : {"type": "string", "analyzer":"myAnalyzer"}, > "MarketingText" : {"type": "string", "analyzer":"myAnalyzer"}, > "ImageName" : {"type": "string", "include\_in\_all": false}, > "IsActive" : {"type": "boolean"}, > "IsFeatured" : {"type": "boolean"}, > "IsInCatalog" : {"type": "boolean"}, > "IsMASInsertSync" : {"type": "integer"}, > "EDelivery" : {"type": "boolean"}, > "ProductStatus" : {"type": "integer"}, > "MFLogo" : {"type": "string", "include\_in\_all": false}, > "MPartNumber" : {"type": "string", "include\_in\_all": false}, > "ParentCategory" : {"type": "integer"}, > "ContainsAccessory" : {"type": "boolean"}, > "ContainsCompatible" : {"type": "boolean"}, > "EMfID" : {"type": "integer"}, > "ECategoryID" : {"type": "integer"}, > "ESmallImage" : {"type": "string", "include\_in\_all": false}, > "ELargeImage" : {"type": "string", "include\_in\_all": false}, > "ProductType" : {"type": "string", "analyzer":"myAnalyzer"}, > "ProductName" : {"type": "string", "analyzer":"myAnalyzer"} > } > } > > After this I am using _JDBC River create by JPrante_ for fetching data > from SQL Server 2005, following is the script > > AllWebSearchProducts > > Create River Script > PUT /\_river/searchproductsriver/\_meta > { > "type" : "jdbc", > "jdbc" : { > "driver":"com.microsoft.sqlserver.jdbc.SQLServerDriver", > "url":"jdbc:sqlserver://xxx.xx.x.xxx;databaseName=Products", > "user":"adminaccount","password":"password", > "sql":"SELECT ProductID as \_id FROM ProductAttributes(nolock)", > "strategy" : "oneshot", > "index" : "searchindex", > "type" : "searchproducts", > "bulk\_size" : 500, > "max\_retries": 5, > "max\_retries\_wait":"30s", > "max\_bulk\_requests" : 30, > "bulk\_flush\_interval" : "5s" > } > } > Here SQL string is truncated just to keep it simple, I am actually using a long string with all the columns given in mapping and with multiple joins. > > This river is supposed to bring around 2.6 million products, but it stops after fetching 1.9 to 2 million products. I have tried > running it many times but it fails. This river runs for around 3hrs 30mnts. > > However if I run this river _without creating the mapping, it works_ fine and all products are fetched in Index. > > Please let me know if I am doing something wrong. No error is given or logged by Elasticsearch, it simply stops running the river. > > Thanks & Regards, > Ajay. > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/0ee8c762-6fb5-4326-a126-f0565a9dc5cd%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/0ee8c762-6fb5-4326-a126-f0565a9dc5cd%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/0ee8c762-6fb5-4326-a126-f0565a9dc5cd%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/0ee8c762-6fb5-4326-a126-f0565a9dc5cd%40googlegroups.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGPbxuUtsQ\_3hYwkZRUK2NqGurHuj2WH%3DnkyRLbmZVS7g%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGPbxuUtsQ_3hYwkZRUK2NqGurHuj2WH%3DnkyRLbmZVS7g%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**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, 1:36am UTC](https://discuss.elastic.co/t/jdbc-river-fails-when-mapping-is-used/16952/3 "2017-07-06T01:36:18Z")