Pertaining to indexes on Foreign tables.

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Pertaining to indexes on Foreign tables.

Sqlfreak
Hi,

Guess have started loving the performance of LucidDB even before i have seen some more magic of it. :)

Guys, i would like your help on these questions too. This forms some core part of my testing.

1) Can i add indexes on table that i have imported? The ones created as foreign tables in my Schema?
    I tried adding index to a certain table in my LucidDB but got the following error:- ( the table and column names are in quotes since they have been exported from MySQL).

Error: java.lang.ClassCastException: net.sf.farrago.fem.med.FemForeignTable$Hib
SQLState:  null
ErrorCode: 0
Error occured in:
create index index1 on "mktng_f1"("brand_name")

2) I need to upload close to 15 million rows in one of my tables in LucidDb from a .SQL file? how do i go about it?

3) I was trying to insert close to 25,000 rows through the insert statement but it was throwing me an error?

0: jdbc:luciddb:rmi://localhost> java heap

I tried to increase the -XM and _XS parameters to 512 MB but it still dint work.
Any views on this?

4) Also, can i work across schemas? What i mean is that if i have a certain table in say "schema1" and a few more in "schema2", could you let me know of a method by which i could access and query across schemas?
   i tried the following but it dint work:-

select * from localdb.schema1.table1, localdb.schema2."table2" where localdb.schema1.table1.id = localdb.schema2."table2"."id";


5) Lastly, in the tables that i extracted from MySQL DB, i am facing a particular problem. My tabels along with the data has been imported but sadly, the indexes i had on the tables have been ignored?
    My tables do not seem to have any indexes which were already present in their MySQL counterparts?
    Any idea why it is so?

i would be really glad if you provide me with some references and examples with the answers. It would really be very helpful.
Thanks a lot for your time.


Cheers,

Varun

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Pertaining to indexes on Foreign tables.

Nicholas Goodman

On Oct 24, 2009, at 5:38 AM, Varun Gopinath wrote:

1) Can i add indexes on table that i have imported? The ones created as foreign tables in my Schema?
    I tried adding index to a certain table in my LucidDB but got the following error:- ( the table and column names are in quotes since they have been exported from MySQL).
It's not possible to add an index on a foreign table, and I'm not sure what the behavior would be.  When you "import" a foreign table into LucidDB (using the IMPORT FOREIGN SCHEMA command) you are importing the metadata so it is available for querying but it is not actually bring the data over.  You have to do a ETL import (confusing to use the same term I know) as

insert into table select * from foreign server

to be able to get the physical data from your MySQL instance to LucidDB.  Indexes are physical views into a set of records so creating it only makes sense in the context of a physical set of data.

I've added http://issues.eigenbase.org/browse/FRG-384 to validate and give a better error message when trying to create an idx against a foreign table.

2) I need to upload close to 15 million rows in one of my tables in LucidDb from a .SQL file? how do i go about it?
I'd suggest you do some preprocessing to turn that SQL file into a flat file (pipe, csv, delimited) and then use the regular flat file import methods (http://pub.eigenbase.org/wiki/LucidDbFlatFileWrapper).  Alternatively, you could import it into mysql/hypersonic and then use the JDBC connector (http://pub.eigenbase.org/wiki/FarragoMedJdbcPlugin).  Do NOT try and execute 15 million INSERT statements.  They will perform terrible.

3) I was trying to insert close to 25,000 rows through the insert statement but it was throwing me an error?

0: jdbc:luciddb:rmi://localhost> java heap

I tried to increase the -XM and _XS parameters to 512 MB but it still dint work.
Any views on this?
Please post your SQL (or a representative sample), and the information about the tables.

4) Also, can i work across schemas? What i mean is that if i have a certain table in say "schema1" and a few more in "schema2", could you let me know of a method by which i could access and query across schemas?
   i tried the following but it dint work:-

select * from localdb.schema1.table1, localdb.schema2."table2" where localdb.schema1.table1.id = localdb.schema2."table2"."id";
Queries work across schemas, including foreign data tables.  Beware that doing joins to remote sources can be very very inefficient.  Here's a two schema query (with a foreign JDBC source) that works for me:
(f = foreign schema/tables) (l = local tables)
 select f.customernumber, l.customernumber from extraction.customers f, schema2.customers_local l where f.customernumber = l.customernumber;
122 rows selected (0.741 seconds)

5) Lastly, in the tables that i extracted from MySQL DB, i am facing a particular problem. My tabels along with the data has been imported but sadly, the indexes i had on the tables have been ignored?
Yes.  LucidDB will not import the indexes.  You'll need to create indexes in LucidDB.  There's enough variation, and often people typically reshape the data on it's way from foreign data sources (MySQL) to local tables.  Usually this reorganization/ETL process changes the structure and needs new indexes.

We have some planned work to make a point / click / migrate (w/ ETL) that could include auto index creation, but I'm pretty certain that it won't be part of the import foreign schema.

Good luck Varun!

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Pertaining to indexes on Foreign tables.

Zelaine Fong
In reply to this post by Sqlfreak
For problem #3, if you're trying to insert from a MySQL source, perhaps you've run into this -- http://n2.nabble.com/server-crash-during-import-tt3769391.html#a3770113
-- Zelaine


From: Varun Gopinath <[hidden email]>
To: [hidden email]
Sent: Saturday, October 24, 2009 5:38:56 AM
Subject: [luciddb-users] Pertaining to indexes on Foreign tables.

 
3) I was trying to insert close to 25,000 rows through the insert statement but it was throwing me an error?

0: jdbc:luciddb:rmi://localhost> java heap

I tried to increase the -XM and _XS parameters to 512 MB but it still dint work.
Any views on this?


------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Loading...