You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by dtphat <ph...@gmail.com> on 2013/10/28 10:45:42 UTC
Data import handler with multi tables
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.
Re: Data import handler with multi tables
Posted by Stefan Matheis <ma...@gmail.com>.
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).
>
>
Re: Data import handler with multi tables
Posted by dtphat <ph...@gmail.com>.
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.
Re: Data import handler with multi tables
Posted by Giovanni Bricconi <gi...@banzai.it>.
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 <ma...@gmail.com>
> 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).
> >
> >
>
>
>
Re: Data import handler with multi tables
Posted by Stefan Matheis <ma...@gmail.com>.
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).
>
>
Re: Data import handler with multi tables
Posted by dtphat <ph...@gmail.com>.
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.
Re: Data import handler with multi tables
Posted by Stefan Matheis <ma...@gmail.com>.
> 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).
>
>