Help with jdbc rivers and type mapping

Eric_Sims · April 30, 2014, 7:56pm

i can't seem to understand how to fully set up my type mappings while using
jdbc rivers and sql server.

here's an example.

PUT /_river/mytest_river/_meta
{
"type": "jdbc",
"jdbc": {
"url":"jdbc:sqlserver://mydbserver:1433;databaseName=mydatabase",
"user":"myuser",
"password":"xxx",
"sql":"select * from dbo.musicalbum (nolock)",
"strategy" : "oneshot",
"index" : "myindex",
"type" : "album",
"bulk_size" : 100,
"max_retries": 5,
"max_retries_wait":"30s",
"max_bulk_requests" : 5,
"bulk_flush_interval" : "5s",
"type_mapping": {
"album": {"properties": {
"AlbumDescription": {"type": "string"},
"AlbumID": {"type": "string"},
"Artist": {"type": "string"},
"Genre": {"type": "string","index" : "not_analyzed"},
"Label": {"type": "string"},
"Title": {"type": "string"},
"_id" : {"path" : "AlbumID"}
}
}
}
}
}

so you can see i've specified both a select statement (which normally would
dynamically produce the mapping for me) and also a type mapping. in the
type mapping i've tried to specify that i want the _id to be the same as
AlbumID, and also that i want the Genre to be not_analyzed. it ends up
throwing multiple errors, only indexing one document, and not creating my
full mapping.

here's what the mapping ends up looking like: (skipping some of the columns
altogether!)

{
"myindex": {
"mappings": {
"album": {
"properties": {
"AlbumDescription": {
"type": "string"
},
"AlbumID": {
"type": "string"
},
"Artist": {
"type": "string"
},
"Genre": {
"type": "string"
},
"Title": {
"type": "string"
}
}
}
}
}
}

any assistance would be helpful. it's driving me nuts.

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/4c9af783-cf6c-4e41-a287-83ff5589350e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

jprante · April 30, 2014, 8:06pm

Thanks for the report.

Does it work if you create the index with the custom mapping beforehand,
with tool like curl?

The JDBC river will use existing index then.

Jörg

On Wed, Apr 30, 2014 at 9:56 PM, Eric Sims eric.sims.aent.com@gmail.comwrote:

i can't seem to understand how to fully set up my type mappings while
using jdbc rivers and sql server.

here's an example.

PUT /_river/mytest_river/_meta
{
"type": "jdbc",
"jdbc": {
"url":"jdbc:sqlserver://mydbserver:1433;databaseName=mydatabase",
"user":"myuser",
"password":"xxx",
"sql":"select * from dbo.musicalbum (nolock)",
"strategy" : "oneshot",
"index" : "myindex",
"type" : "album",
"bulk_size" : 100,
"max_retries": 5,
"max_retries_wait":"30s",
"max_bulk_requests" : 5,
"bulk_flush_interval" : "5s",
"type_mapping": {
"album": {"properties": {
"AlbumDescription": {"type": "string"},
"AlbumID": {"type": "string"},
"Artist": {"type": "string"},
"Genre": {"type": "string","index" : "not_analyzed"},
"Label": {"type": "string"},
"Title": {"type": "string"},
"_id" : {"path" : "AlbumID"}
}
}
}
}
}

so you can see i've specified both a select statement (which normally
would dynamically produce the mapping for me) and also a type mapping. in
the type mapping i've tried to specify that i want the _id to be the same
as AlbumID, and also that i want the Genre to be not_analyzed. it ends up
throwing multiple errors, only indexing one document, and not creating my
full mapping.

here's what the mapping ends up looking like: (skipping some of the
columns altogether!)

{
"myindex": {
"mappings": {
"album": {
"properties": {
"AlbumDescription": {
"type": "string"
},
"AlbumID": {
"type": "string"
},
"Artist": {
"type": "string"
},
"Genre": {
"type": "string"
},
"Title": {
"type": "string"
}
}
}
}
}
}

any assistance would be helpful. it's driving me nuts.

--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/4c9af783-cf6c-4e41-a287-83ff5589350e%40googlegroups.com https://groups.google.com/d/msgid/elasticsearch/4c9af783-cf6c-4e41-a287-83ff5589350e%40googlegroups.com?utm_medium=email&utm_source=footer
.
For more options, visit 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAKdsXoEGjQfv%2BkRgia-GRu8D805hmv%2BLUkLXtCBX8VxHSFTTEQ%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Eric_Sims · April 30, 2014, 8:34pm

no. i just tried deleting all indexes, then i did:

PUT /myindex

then

PUT /myindex/album/_mapping
{
"myindex": {
"mappings": {
"album": {
"properties": {
"AlbumDescription": {"type": "string"},
"AlbumID": {"type": "string"},
"Artist": {"type": "string"},
"Genre": {"type": "string","index" : "not_analyzed"},
"Label": {"type": "string"},
"Title": {"type": "string"},
"_id" : {"path" : "AlbumID"}
}
}
}
}
}

then i ran the PUT statement in my previous post.

it still treats it as dynamic mappings

On Wednesday, April 30, 2014 3:56:22 PM UTC-4, Eric Sims wrote:

i can't seem to understand how to fully set up my type mappings while
using jdbc rivers and sql server.

here's an example.

PUT /_river/mytest_river/_meta
{
"type": "jdbc",
"jdbc": {
"url":"jdbc:sqlserver://mydbserver:1433;databaseName=mydatabase",
"user":"myuser",
"password":"xxx",
"sql":"select * from dbo.musicalbum (nolock)",
"strategy" : "oneshot",
"index" : "myindex",
"type" : "album",
"bulk_size" : 100,
"max_retries": 5,
"max_retries_wait":"30s",
"max_bulk_requests" : 5,
"bulk_flush_interval" : "5s",
"type_mapping": {
"album": {"properties": {
"AlbumDescription": {"type": "string"},
"AlbumID": {"type": "string"},
"Artist": {"type": "string"},
"Genre": {"type": "string","index" : "not_analyzed"},
"Label": {"type": "string"},
"Title": {"type": "string"},
"_id" : {"path" : "AlbumID"}
}
}
}
}
}

so you can see i've specified both a select statement (which normally
would dynamically produce the mapping for me) and also a type mapping. in
the type mapping i've tried to specify that i want the _id to be the same
as AlbumID, and also that i want the Genre to be not_analyzed. it ends up
throwing multiple errors, only indexing one document, and not creating my
full mapping.

here's what the mapping ends up looking like: (skipping some of the
columns altogether!)

{
"myindex": {
"mappings": {
"album": {
"properties": {
"AlbumDescription": {
"type": "string"
},
"AlbumID": {
"type": "string"
},
"Artist": {
"type": "string"
},
"Genre": {
"type": "string"
},
"Title": {
"type": "string"
}
}
}
}
}
}

any assistance would be helpful. it's driving me nuts.

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/1bda2b24-8fc4-4706-a43f-cadf820ebc6c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

jprante · April 30, 2014, 8:51pm

The mapping has errors. Something like this might work better:

DELETE /myindex

PUT /myindex

PUT /myindex/album/_mapping
{

   "album": {
      "properties": {
           "AlbumDescription": {"type": "string"},
           "AlbumID": {"type": "string"},
           "Artist": {"type": "string"},
           "Genre": {"type": "string","index" : "not_analyzed"},
           "Label": {"type": "string"},
           "Title": {"type": "string"},
           "_id" : {
                "index_name" : "album.AlbumID",
                "path" : "full",
                "type" : "string"
           }
        }
   }

}

GET /myindex/album/_mapping

Jörg

On Wed, Apr 30, 2014 at 10:34 PM, Eric Sims eric.sims.aent.com@gmail.comwrote:

no. i just tried deleting all indexes, then i did:

PUT /myindex

then

PUT /myindex/album/_mapping
{
"myindex": {
"mappings": {
"album": {
"properties": {
"AlbumDescription": {"type": "string"},
"AlbumID": {"type": "string"},
"Artist": {"type": "string"},
"Genre": {"type": "string","index" : "not_analyzed"},
"Label": {"type": "string"},
"Title": {"type": "string"},
"_id" : {"path" : "AlbumID"}
}
}
}
}
}

then i ran the PUT statement in my previous post.

it still treats it as dynamic mappings

On Wednesday, April 30, 2014 3:56:22 PM UTC-4, Eric Sims wrote:

i can't seem to understand how to fully set up my type mappings while
using jdbc rivers and sql server.

here's an example.

PUT /_river/mytest_river/_meta
{
"type": "jdbc",
"jdbc": {
"url":"jdbc:sqlserver://mydbserver:1433;databaseName=mydatabase",
"user":"myuser",
"password":"xxx",
"sql":"select * from dbo.musicalbum (nolock)",
"strategy" : "oneshot",
"index" : "myindex",
"type" : "album",
"bulk_size" : 100,
"max_retries": 5,
"max_retries_wait":"30s",
"max_bulk_requests" : 5,
"bulk_flush_interval" : "5s",
"type_mapping": {
"album": {"properties": {
"AlbumDescription": {"type": "string"},
"AlbumID": {"type": "string"},
"Artist": {"type": "string"},
"Genre": {"type": "string","index" : "not_analyzed"},
"Label": {"type": "string"},
"Title": {"type": "string"},
"_id" : {"path" : "AlbumID"}
}
}
}
}
}

so you can see i've specified both a select statement (which normally
would dynamically produce the mapping for me) and also a type mapping. in
the type mapping i've tried to specify that i want the _id to be the same
as AlbumID, and also that i want the Genre to be not_analyzed. it ends up
throwing multiple errors, only indexing one document, and not creating my
full mapping.

here's what the mapping ends up looking like: (skipping some of the
columns altogether!)

{
"myindex": {
"mappings": {
"album": {
"properties": {
"AlbumDescription": {
"type": "string"
},
"AlbumID": {
"type": "string"
},
"Artist": {
"type": "string"
},
"Genre": {
"type": "string"
},
"Title": {
"type": "string"
}
}
}
}
}
}

any assistance would be helpful. it's driving me nuts.

--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/1bda2b24-8fc4-4706-a43f-cadf820ebc6c%40googlegroups.com https://groups.google.com/d/msgid/elasticsearch/1bda2b24-8fc4-4706-a43f-cadf820ebc6c%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAKdsXoGkTLqF6VC4kSYMT2WjnAcLiLF4RE-DG4914uc31DdRGg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Eric_Sims · April 30, 2014, 9:07pm

should i keep the mapping in the original PUT /_river/mytest_river/_meta
statement or removing in lieu of the other separate mapping statement?

because i tried what you just suggested and it didn't seem to make a
difference with having removed the mapping statement within the river.

On Wednesday, April 30, 2014 3:56:22 PM UTC-4, Eric Sims wrote:

i can't seem to understand how to fully set up my type mappings while
using jdbc rivers and sql server.

here's an example.

PUT /_river/mytest_river/_meta
{
"type": "jdbc",
"jdbc": {
"url":"jdbc:sqlserver://mydbserver:1433;databaseName=mydatabase",
"user":"myuser",
"password":"xxx",
"sql":"select * from dbo.musicalbum (nolock)",
"strategy" : "oneshot",
"index" : "myindex",
"type" : "album",
"bulk_size" : 100,
"max_retries": 5,
"max_retries_wait":"30s",
"max_bulk_requests" : 5,
"bulk_flush_interval" : "5s",
"type_mapping": {
"album": {"properties": {
"AlbumDescription": {"type": "string"},
"AlbumID": {"type": "string"},
"Artist": {"type": "string"},
"Genre": {"type": "string","index" : "not_analyzed"},
"Label": {"type": "string"},
"Title": {"type": "string"},
"_id" : {"path" : "AlbumID"}
}
}
}
}
}

so you can see i've specified both a select statement (which normally
would dynamically produce the mapping for me) and also a type mapping. in
the type mapping i've tried to specify that i want the _id to be the same
as AlbumID, and also that i want the Genre to be not_analyzed. it ends up
throwing multiple errors, only indexing one document, and not creating my
full mapping.

here's what the mapping ends up looking like: (skipping some of the
columns altogether!)

{
"myindex": {
"mappings": {
"album": {
"properties": {
"AlbumDescription": {
"type": "string"
},
"AlbumID": {
"type": "string"
},
"Artist": {
"type": "string"
},
"Genre": {
"type": "string"
},
"Title": {
"type": "string"
}
}
}
}
}
}

any assistance would be helpful. it's driving me nuts.

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/6198cd66-c2b9-42e9-a8a8-f8ca2fba9ee5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Eric_Sims · April 30, 2014, 9:17pm

here's another weird bit. it doesn't seem to show the mappings right after
i set them:

PUT /myindex/album/_mapping
{
"myindex": {
"mappings": {
"album": {
"properties": {
"albumdescription": {"type": "string"},
"albumid": {"type": "string"},
"artist": {"type": "string"},
"genre": {"type": "string", "index" : "not_analyzed"},
"label": {"type": "string", "analyzer": "whitespace"},
"title": {"type": "string"},
"time": {"type" : "string"},
"_id" : {
"index_name" : "album.AlbumID",
"path" : "full",
"type" : "string"
}
}
}
}
}
}

GET /myindex/album/_mapping

returns this:

{
"myindex": {
"mappings": {
"album": {
"properties": {}
}
}
}
}

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/55b7887e-43e3-4836-bef7-55e4c9c6c8e5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Eric_Sims · April 30, 2014, 9:20pm

forget that part - i didn't need the
"myindex" : { "mappings" {
part.

other issue still stands though.

On Wednesday, April 30, 2014 5:17:00 PM UTC-4, Eric Sims wrote:

here's another weird bit. it doesn't seem to show the mappings right after
i set them:

PUT /myindex/album/_mapping
{
"myindex": {
"mappings": {
"album": {
"properties": {
"albumdescription": {"type": "string"},
"albumid": {"type": "string"},
"artist": {"type": "string"},
"genre": {"type": "string", "index" : "not_analyzed"},
"label": {"type": "string", "analyzer": "whitespace"},
"title": {"type": "string"},
"time": {"type" : "string"},
"_id" : {
"index_name" : "album.AlbumID",
"path" : "full",
"type" : "string"
}
}
}
}
}
}

GET /myindex/album/_mapping

returns this:

{
"myindex": {
"mappings": {
"album": {
"properties": {}
}
}
}
}

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/cf3b18c2-3393-413c-98c8-e6e84ac7c5ff%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Eric_Sims · April 30, 2014, 9:57pm

i'm able to get the mappings working as you suggested. However, the custom
_id mapping is not working.

it's still generating a dynamic _id.

any ideas?

On Wednesday, April 30, 2014 5:07:57 PM UTC-4, Eric Sims wrote:

should i keep the mapping in the original PUT /_river/mytest_river/_meta
statement or removing in lieu of the other separate mapping statement?

because i tried what you just suggested and it didn't seem to make a
difference with having removed the mapping statement within the river.

On Wednesday, April 30, 2014 3:56:22 PM UTC-4, Eric Sims wrote:

i can't seem to understand how to fully set up my type mappings while
using jdbc rivers and sql server.

here's an example.

PUT /_river/mytest_river/_meta
{
"type": "jdbc",
"jdbc": {
"url":"jdbc:sqlserver://mydbserver:1433;databaseName=mydatabase",
"user":"myuser",
"password":"xxx",
"sql":"select * from dbo.musicalbum (nolock)",
"strategy" : "oneshot",
"index" : "myindex",
"type" : "album",
"bulk_size" : 100,
"max_retries": 5,
"max_retries_wait":"30s",
"max_bulk_requests" : 5,
"bulk_flush_interval" : "5s",
"type_mapping": {
"album": {"properties": {
"AlbumDescription": {"type": "string"},
"AlbumID": {"type": "string"},
"Artist": {"type": "string"},
"Genre": {"type": "string","index" : "not_analyzed"},
"Label": {"type": "string"},
"Title": {"type": "string"},
"_id" : {"path" : "AlbumID"}
}
}
}
}
}

so you can see i've specified both a select statement (which normally
would dynamically produce the mapping for me) and also a type mapping. in
the type mapping i've tried to specify that i want the _id to be the same
as AlbumID, and also that i want the Genre to be not_analyzed. it ends up
throwing multiple errors, only indexing one document, and not creating my
full mapping.

here's what the mapping ends up looking like: (skipping some of the
columns altogether!)

{
"myindex": {
"mappings": {
"album": {
"properties": {
"AlbumDescription": {
"type": "string"
},
"AlbumID": {
"type": "string"
},
"Artist": {
"type": "string"
},
"Genre": {
"type": "string"
},
"Title": {
"type": "string"
}
}
}
}
}
}

any assistance would be helpful. it's driving me nuts.

--
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/03aec83a-b365-44d7-bbe0-89dd9e486ac1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

jprante · May 1, 2014, 7:57am

The _id redirecting is a special feature (I have not been aware of this!)

Please use something like this

PUT /myindex/album/_mapping
{
"album": {
"_id" : {
"path" : "AlbumID"
},
"properties": {
"AlbumDescription": {"type": "string"},
"AlbumID": {"type": "string"},
"Artist": {"type": "string"},
"Genre": {"type": "string","index" : "not_analyzed"},
"Label": {"type": "string"},
"Title": {"type": "string"}
}
}
}

Jörg

On Wed, Apr 30, 2014 at 11:57 PM, Eric Sims eric.sims.aent.com@gmail.comwrote:

i'm able to get the mappings working as you suggested. However, the custom
_id mapping is not working.

it's still generating a dynamic _id.

any ideas?

On Wednesday, April 30, 2014 5:07:57 PM UTC-4, Eric Sims wrote:

should i keep the mapping in the original PUT /_river/mytest_river/_meta
statement or removing in lieu of the other separate mapping statement?

because i tried what you just suggested and it didn't seem to make a
difference with having removed the mapping statement within the river.

On Wednesday, April 30, 2014 3:56:22 PM UTC-4, Eric Sims wrote:

i can't seem to understand how to fully set up my type mappings while
using jdbc rivers and sql server.

here's an example.

PUT /_river/mytest_river/_meta
{
"type": "jdbc",
"jdbc": {
"url":"jdbc:sqlserver://mydbserver:1433;databaseName=mydatabase",
"user":"myuser",
"password":"xxx",
"sql":"select * from dbo.musicalbum (nolock)",
"strategy" : "oneshot",
"index" : "myindex",
"type" : "album",
"bulk_size" : 100,
"max_retries": 5,
"max_retries_wait":"30s",
"max_bulk_requests" : 5,
"bulk_flush_interval" : "5s",
"type_mapping": {
"album": {"properties": {
"AlbumDescription": {"type": "string"},
"AlbumID": {"type": "string"},
"Artist": {"type": "string"},
"Genre": {"type": "string","index" : "not_analyzed"},
"Label": {"type": "string"},
"Title": {"type": "string"},
"_id" : {"path" : "AlbumID"}
}
}
}
}
}

so you can see i've specified both a select statement (which normally
would dynamically produce the mapping for me) and also a type mapping. in
the type mapping i've tried to specify that i want the _id to be the same
as AlbumID, and also that i want the Genre to be not_analyzed. it ends up
throwing multiple errors, only indexing one document, and not creating my
full mapping.

here's what the mapping ends up looking like: (skipping some of the
columns altogether!)

{
"myindex": {
"mappings": {
"album": {
"properties": {
"AlbumDescription": {
"type": "string"
},
"AlbumID": {
"type": "string"
},
"Artist": {
"type": "string"
},
"Genre": {
"type": "string"
},
"Title": {
"type": "string"
}
}
}
}
}
}

any assistance would be helpful. it's driving me nuts.

--
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/03aec83a-b365-44d7-bbe0-89dd9e486ac1%40googlegroups.com https://groups.google.com/d/msgid/elasticsearch/03aec83a-b365-44d7-bbe0-89dd9e486ac1%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/CAKdsXoG11Z2bh64Y8h5QgahGFa74X1xoFXHYcugo%3Dk8MF3JXww%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Eric_Sims · May 1, 2014, 12:58pm

that worked! awesome!!!

yes, the current documentation still leaves a LOT to be desired. small
snippets of code are there in the documentation, but i would have never
known to put that _id outside of the properties declaration! full examples
would be much more helpful. also needs to be more step-based. (step 1, do
this, step 2 do that). too much is assumed.

one more thing i noticed was that the naming of the properties are
case-sensitive to how they are in the db. for example, if my db column says
AlbumID and i create a mapping called albumid, then run a 'select * from
myalbumtable' in the jdbc put statement, it would create two json mappings:
AlbumID and albumid. only one of which would be populated with the data.

learning!

thanks again for your help and maybe this will help others.

On Thursday, May 1, 2014 3:57:45 AM UTC-4, Jörg Prante wrote:

The _id redirecting is a special feature (I have not been aware of this!)

Please use something like this

PUT /myindex/album/_mapping
{
"album": {
"_id" : {
"path" : "AlbumID"
},
"properties": {
"AlbumDescription": {"type": "string"},
"AlbumID": {"type": "string"},
"Artist": {"type": "string"},
"Genre": {"type": "string","index" : "not_analyzed"},
"Label": {"type": "string"},
"Title": {"type": "string"}
}
}
}

Jörg

On Wed, Apr 30, 2014 at 11:57 PM, Eric Sims <eric.sims...@gmail.com<javascript:>

wrote:

i'm able to get the mappings working as you suggested. However, the
custom _id mapping is not working.

it's still generating a dynamic _id.

any ideas?

On Wednesday, April 30, 2014 5:07:57 PM UTC-4, Eric Sims wrote:

should i keep the mapping in the original PUT /_river/mytest_river/_meta
statement or removing in lieu of the other separate mapping statement?

because i tried what you just suggested and it didn't seem to make a
difference with having removed the mapping statement within the river.

On Wednesday, April 30, 2014 3:56:22 PM UTC-4, Eric Sims wrote:

i can't seem to understand how to fully set up my type mappings while
using jdbc rivers and sql server.

here's an example.

PUT /_river/mytest_river/_meta
{
"type": "jdbc",
"jdbc": {
"url":"jdbc:sqlserver://mydbserver:1433;databaseName=mydatabase",
"user":"myuser",
"password":"xxx",
"sql":"select * from dbo.musicalbum (nolock)",
"strategy" : "oneshot",
"index" : "myindex",
"type" : "album",
"bulk_size" : 100,
"max_retries": 5,
"max_retries_wait":"30s",
"max_bulk_requests" : 5,
"bulk_flush_interval" : "5s",
"type_mapping": {
"album": {"properties": {
"AlbumDescription": {"type": "string"},
"AlbumID": {"type": "string"},
"Artist": {"type": "string"},
"Genre": {"type": "string","index" : "not_analyzed"},
"Label": {"type": "string"},
"Title": {"type": "string"},
"_id" : {"path" : "AlbumID"}
}
}
}
}
}

so you can see i've specified both a select statement (which normally
would dynamically produce the mapping for me) and also a type mapping. in
the type mapping i've tried to specify that i want the _id to be the same
as AlbumID, and also that i want the Genre to be not_analyzed. it ends up
throwing multiple errors, only indexing one document, and not creating my
full mapping.

here's what the mapping ends up looking like: (skipping some of the
columns altogether!)

{
"myindex": {
"mappings": {
"album": {
"properties": {
"AlbumDescription": {
"type": "string"
},
"AlbumID": {
"type": "string"
},
"Artist": {
"type": "string"
},
"Genre": {
"type": "string"
},
"Title": {
"type": "string"
}
}
}
}
}
}

any assistance would be helpful. it's driving me nuts.

--
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 elasticsearc...@googlegroups.com <javascript:>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/elasticsearch/03aec83a-b365-44d7-bbe0-89dd9e486ac1%40googlegroups.com https://groups.google.com/d/msgid/elasticsearch/03aec83a-b365-44d7-bbe0-89dd9e486ac1%40googlegroups.com?utm_medium=email&utm_source=footer
.

For more options, visit 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/61406f56-c789-4c2c-8c38-f1c4e9f2e84d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.