Re: Data import handler with multi tables

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: Data import handler with multi tables

Stefan Matheis-2
> I think because <id> is unique. When importing tbl_tableA import first,
> tbl_tableB import after. tbl_tableB has id which the same id in tableA, so
> only data of tableB had indexed with unique id.
>
>

That's exactly what happens here :) If the second table would have fewer records than the first one, you'd still see records from that table.

> Anyone can help me to configure data import handler that can index all data
> of two (more) tables which have the same id in each table.
>
>

that requires the use of a key which is known as "compound key" (http://en.wikipedia.org/wiki/Compound_key), f.e. if data comes from Table A .. make it A1 instead of (only) 1, A2, B1, B2 .. and so on. you can still index the raw id's in another field .. but for the unique key .. you need something like that, to get it working.


HTH
Stefan



On Monday, October 28, 2013 at 10:45 AM, dtphat wrote:

> Hi,
> I wanna to import many tables from MySQL. Assume that, I have two tables:
> *** Tables 1: tbl_tableA(id, nameA) with data (1, A1), (2, A2), (3, A3).
> *** Tables 2: tbl_tableB(id, nameB) with data (1, B1), (2, B2), (3, B3), (4,
> B4), (5, B5).
>
> I configure:
> <dataConfig>
> <dataSource type="JdbcDataSource"
> driver="com.mysql.jdbc.Driver"
> url="jdbc:mysql://xxxxxx"
> user="xxx" password="xxx" batchSize="1" />
>
>     <document name = "atexpats6">
>
> <entity name="tableA"
> query="select * from tbl_tableA">
> <field name="id" column="id"/>
> <field name="nameA" column="nameA" />
> </entity>
>
>
> <entity name="tableB"
> query="select * from tbl_tableB">
> <field name="id" column="id"/>
> <field name="nameA" column="nameA" />
> </entity>
>     </document>
> </dataConfig>
>
> I define nameA, nameB in schema.xml and id is configured by
> <uniqueKey>id</uniqueKey>
>
> When I import data by
> http://localhost:8983/solr/dataimport?command=full-import
>
> It's successfull. But only data of tbl_tableB had indexed.
>
> I think because <id> is unique. When importing tbl_tableA import first,
> tbl_tableB import after. tbl_tableB has id which the same id in tableA, so
> only data of tableB had indexed with unique id.
>
> Anyone can help me to configure data import handler that can index all data
> of two (more) tables which have the same id in each table.
>
> Thanks.
>
>
>
> -----
> Phat T. Dong
> --
> View this message in context: http://lucene.472066.n3.nabble.com/Data-import-handler-with-multi-tables-tp4098026.html
> Sent from the Solr - User mailing list archive at Nabble.com (http://Nabble.com).
>
>


Reply | Threaded
Open this post in threaded view
|

Re: Data import handler with multi tables

dtphat
Hi,
is there no another way to import all data for this case instead Only the way using compound key?
Thanks.
Phat T. Dong
Reply | Threaded
Open this post in threaded view
|

Re: Data import handler with multi tables

Stefan Matheis-2
I've never looked for another way, what's the problem using a compound key?


On Monday, October 28, 2013 at 1:38 PM, dtphat wrote:

> Hi,
> is there no another way to import all data for this case instead Only the
> way using compound key?
> Thanks.
>
>
>
> -----
> Phat T. Dong
> --
> View this message in context: http://lucene.472066.n3.nabble.com/Re-Data-import-handler-with-multi-tables-tp4098048p4098056.html
> Sent from the Solr - User mailing list archive at Nabble.com (http://Nabble.com).
>
>


Reply | Threaded
Open this post in threaded view
|

Re: Data import handler with multi tables

Giovanni Bricconi
maybe

<entity name="bothTables"
                        query="select concat('A.',id) id, id originalId,
nameA from tbl_tableA union all select concat('B.',id) id, id originalId,
nameA from tbl_tableB ">
                        <field name="id" column="id"/>
                        <field name="originalId" column="originalId"/> <!--
need a new field -->
                        <field name="nameA" column="nameA" />
                </entity>

So you can keep the original id, maybe add also an originalTable field if
you don't like parsing the id colum to discover the table from which the
data was read.


2013/10/29 Stefan Matheis <[hidden email]>

> I've never looked for another way, what's the problem using a compound key?
>
>
> On Monday, October 28, 2013 at 1:38 PM, dtphat wrote:
>
> > Hi,
> > is there no another way to import all data for this case instead Only the
> > way using compound key?
> > Thanks.
> >
> >
> >
> > -----
> > Phat T. Dong
> > --
> > View this message in context:
> http://lucene.472066.n3.nabble.com/Re-Data-import-handler-with-multi-tables-tp4098048p4098056.html
> > Sent from the Solr - User mailing list archive at Nabble.com (
> http://Nabble.com).
> >
> >
>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Data import handler with multi tables

dtphat
yes, I've just used concat(id, '_', tableName) instead using compound key. I think this is an easy way.
Thanks.
Phat T. Dong
Reply | Threaded
Open this post in threaded view
|

Re: Data import handler with multi tables

Stefan Matheis-2
that is what i'd call a compound key? :) using multiple attribute to generate a unique key across multiple tables ..


On Wednesday, October 30, 2013 at 2:10 AM, dtphat wrote:

> yes, I've just used concat(id, '_', tableName) instead using compound key. I
> think this is an easy way.
> Thanks.
>
>
>
> -----
> Phat T. Dong
> --
> View this message in context: http://lucene.472066.n3.nabble.com/Re-Data-import-handler-with-multi-tables-tp4098048p4098328.html
> Sent from the Solr - User mailing list archive at Nabble.com (http://Nabble.com).
>
>