Quantcast

[SPAM] Query regarding LucidDB

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

[SPAM] Query regarding LucidDB

Sqlfreak
hi,

I am new to LucidDB and was thinking of moving my database from Mysql to LucidDB due to the advanced efficiency LucidDB provides.
I am currently stuck in a situation and would be glad if you could help me out.

I am actually trying to migrate data from MySQL server using the SQL data connector 5.1.
the statements i have used are as follows:-

CREATE or replace SERVER mysql
FOREIGN DATA WRAPPER sys_jdbc
OPTIONS (
     driver_class 'com.mysql.jdbc.Driver',
     url 'jdbc:mysql://localhost/test_data?useCursorFetch=true',
     user_name 'root',
     password '',
     login_timeout '10',
     validation_query 'select 1',
     fetch_size '10000',
     table_types 'TABLE',
     schema_name 'TESTING');

After this, i created a schema named 'Extraction_Schema' and did the following import.

import foreign schema TESTING
from server mysql_test
into extraction_schema;

Now when i run the query " select * from sys_root.dba_stored_tables;", i get the output which indicates as follows:-

| LOCALDB       | EXTRACTION_SCHEMA       | timetable                              |

| LOCALDB       | EXTRACTION_SCHEMA        | employee                            |

| LOCALDB       | EXTRACTION_SCHEMA        | user                                    |


This clearly shows that the data has been populated in the schema.

Now when i run the following query:-

set schema 'Extraction_Schema'

select count(*) from employee;

it gives me the following error:-

Error: From line 1, column 15 to line 1, column 22: Table 'EMPLOYEE' not found (
state=,code=0)

Could somebody please let me know where and what is going wrong?
I would really appreciate some help in this regard.

--
Regards,

Varun Gopinath

"To be the best, you need to survive the worst"

------------------------------------------------------------------------------
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: [SPAM] Query regarding LucidDB

Guillaume Theoret
Try
select count(*) from "employee";

LucidDB can be a bit picky about case and uppercases everything unless
you quote it.

On Tue, Oct 20, 2009 at 8:22 AM, Varun Gopinath
<[hidden email]> wrote:

> hi,
> I am new to LucidDB and was thinking of moving my database from Mysql to
> LucidDB due to the advanced efficiency LucidDB provides.
> I am currently stuck in a situation and would be glad if you could help me
> out.
> I am actually trying to migrate data from MySQL server using the SQL data
> connector 5.1.
> the statements i have used are as follows:-
> CREATE or replace SERVER mysql
> FOREIGN DATA WRAPPER sys_jdbc
> OPTIONS (
>      driver_class 'com.mysql.jdbc.Driver',
>      url 'jdbc:mysql://localhost/test_data?useCursorFetch=true',
>      user_name 'root',
>      password '',
>      login_timeout '10',
>      validation_query 'select 1',
>      fetch_size '10000',
>      table_types 'TABLE',
>      schema_name 'TESTING');
> After this, i created a schema named 'Extraction_Schema' and did the
> following import.
> import foreign schema TESTING
> from server mysql_test
> into extraction_schema;
> Now when i run the query " select * from sys_root.dba_stored_tables;", i get
> the output which indicates as follows:-
> | LOCALDB       | EXTRACTION_SCHEMA       | timetable
>        |
> | LOCALDB       | EXTRACTION_SCHEMA        | employee
>      |
> | LOCALDB       | EXTRACTION_SCHEMA        | user
>          |
>
> This clearly shows that the data has been populated in the schema.
> Now when i run the following query:-
> set schema 'Extraction_Schema'
> select count(*) from employee;
> it gives me the following error:-
> Error: From line 1, column 15 to line 1, column 22: Table 'EMPLOYEE' not
> found (
> state=,code=0)
> Could somebody please let me know where and what is going wrong?
> I would really appreciate some help in this regard.
> --
> Regards,
>
> Varun Gopinath
>
> "To be the best, you need to survive the worst"
>
> ------------------------------------------------------------------------------
> 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
>
>

------------------------------------------------------------------------------
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: [SPAM] Query regarding LucidDB

Tom Barber
In reply to this post by Sqlfreak
Hi Varun,

I'm forwarding this back to the lucid list, as when I hit reply to you I forgot to send my reply there, and those guys know more than me, but here's my guesses.....


1) My understanding is that largest the improvement is gained over large dataset where the query on a conventional db would run out of memory

2) Erm, sure.

3) Well if you're importing surely the tables are specifically created for luciddb?

4) Dunno, but I would also agree with that, maybe this can be improved upon.

I also come from a MySQL/Postgres background and I know the learning curve is pretty steep, but worth it in the end. And I also know that the MySQL converts are the type of people Lucid need so we need to work out where the problems lie for people like yourself!

Cheers
Tom

On Fri 23/10/09 8:32 AM , Varun Gopinath [hidden email] sent:
Hey Tom,

i would also like ur opinion on some other things.Would be glad if you could throw some light on the pointers below:-

I am basically planning to move my MySQL database to LucidDB.
i had a few queries related to it:-

1) What difference in performance can i expect if i move it to LucidDB?
2) i have a star schema already in place in Mysql.Now if i transfer the tables using a foreign wrapper to LucidDB, would there be any change in which the schema would be stored? As in, would it still be placed as a star schema?

3) Also, would the compression and storage be more optimised if i create the tables specifically in LucidDB instead of importing the same from some other database?

4) Lastly, i was going through the explain parameters for the ucidDB query, and it seemed so complicated to interpret? is there some way to figure things out or some easier way to understand and interpret slow queries?

Thanks in advance for your time.
really appreciate it. its really very important for me.

Tc

Cheers,
Varun

On Wed, Oct 21, 2009 at 10:55 AM, Varun Gopinath <[hidden email]> wrote:
Thanks a lot buddy. :)

you saved so much of my time.


On Tue, Oct 20, 2009 at 9:23 PM, <[hidden email]> wrote:
hi Varun

By default lucid will change all table names field names etc to upper case unless you surround them in quotes.

Try that..

Tom



On Tue 20/10/09 1:22 PM , Varun Gopinath [hidden email] sent:
hi,

I am new to LucidDB and was thinking of moving my database from Mysql to LucidDB due to the advanced efficiency LucidDB provides.
I am currently stuck in a situation and would be glad if you could help me out.

I am actually trying to migrate data from MySQL server using the SQL data connector 5.1.
the statements i have used are as follows:-

CREATE or replace SERVER mysql
FOREIGN DATA WRAPPER sys_jdbc
OPTIONS (
     driver_class 'com.mysql.jdbc.Driver',
     url 'jdbc:mysql://localhost/test_data?useCursorFetch=true',
     user_name 'root',
     password '',
     login_timeout '10',
     validation_query 'select 1',
     fetch_size '10000',
     table_types 'TABLE',
     schema_name 'TESTING');

After this, i created a schema named 'Extraction_Schema' and did the following import.

import foreign schema TESTING
from server mysql_test
into extraction_schema;

Now when i run the query " select * from sys_root.dba_stored_tables;", i get the output which indicates as follows:-

| LOCALDB       | EXTRACTION_SCHEMA       | timetable                              |

| LOCALDB       | EXTRACTION_SCHEMA        | employee                            |

| LOCALDB       | EXTRACTION_SCHEMA        | user                                    |


This clearly shows that the data has been populated in the schema.

Now when i run the following query:-

set schema 'Extraction_Schema'

select count(*) from employee;

it gives me the following error:-

Error: From line 1, column 15 to line 1, column 22: Table 'EMPLOYEE' not found (
state=,code=0)

Could somebody please let me know where and what is going wrong?
I would really appreciate some help in this regard.

--
Regards,

Varun Gopinath

"To be the best, you need to survive the worst"




--
Regards,

Varun Gopinath

"To be the best, you need to survive the worst"



--
Regards,

Varun Gopinath

"To be the best, you need to survive the worst"


------------------------------------------------------------------------------
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: [SPAM] Query regarding LucidDB

Zelaine Fong
For #4, take a look at http://pub.eigenbase.org/wiki/FarragoExplainPlanExplained for a basic overview of explain output.
 
There's also http://pub.eigenbase.org/wiki/LucidDbSlowQueryDiagnosis, which has some more complex examples.
 
-- Zelaine


From: "[hidden email]" <[hidden email]>
To: Varun Gopinath <[hidden email]>
Cc: [hidden email]
Sent: Friday, October 23, 2009 1:22:23 AM
Subject: Re: [luciddb-users] [SPAM] Query regarding LucidDB

Hi Varun,

I'm forwarding this back to the lucid list, as when I hit reply to you I forgot to send my reply there, and those guys know more than me, but here's my guesses.....


1) My understanding is that largest the improvement is gained over large dataset where the query on a conventional db would run out of memory

2) Erm, sure.

3) Well if you're importing surely the tables are specifically created for luciddb?

4) Dunno, but I would also agree with that, maybe this can be improved upon.

I also come from a MySQL/Postgres background and I know the learning curve is pretty steep, but worth it in the end. And I also know that the MySQL converts are the type of people Lucid need so we need to work out where the problems lie for people like yourself!

Cheers
Tom

On Fri 23/10/09 8:32 AM , Varun Gopinath [hidden email] sent:
Hey Tom,

i would also like ur opinion on some other things.Would be glad if you could throw some light on the pointers below:-

I am basically planning to move my MySQL database to LucidDB.
i had a few queries related to it:-

1) What difference in performance can i expect if i move it to LucidDB?
2) i have a star schema already in place in Mysql.Now if i transfer the tables using a foreign wrapper to LucidDB, would there be any change in which the schema would be stored? As in, would it still be placed as a star schema?

3) Also, would the compression and storage be more optimised if i create the tables specifically in LucidDB instead of importing the same from some other database?

4) Lastly, i was going through the explain parameters for the ucidDB query, and it seemed so complicated to interpret? is there some way to figure things out or some easier way to understand and interpret slow queries?

Thanks in advance for your time.
really appreciate it. its really very important for me.

Tc

Cheers,
Varun

On Wed, Oct 21, 2009 at 10:55 AM, Varun Gopinath <[hidden email]> wrote:
Thanks a lot buddy. :)

you saved so much of my time.


On Tue, Oct 20, 2009 at 9:23 PM, <[hidden email]> wrote:
hi Varun

By default lucid will change all table names field names etc to upper case unless you surround them in quotes.

Try that..

Tom



On Tue 20/10/09 1:22 PM , Varun Gopinath [hidden email] sent:
hi,

I am new to LucidDB and was thinking of moving my database from Mysql to LucidDB due to the advanced efficiency LucidDB provides.
I am currently stuck in a situation and would be glad if you could help me out.

I am actually trying to migrate data from MySQL server using the SQL data connector 5.1.
the statements i have used are as follows:-

CREATE or replace SERVER mysql
FOREIGN DATA WRAPPER sys_jdbc
OPTIONS (
     driver_class 'com.mysql.jdbc.Driver',
     url 'jdbc:mysql://localhost/test_data?useCursorFetch=true',
     user_name 'root',
     password '',
     login_timeout '10',
     validation_query 'select 1',
     fetch_size '10000',
     table_types 'TABLE',
     schema_name 'TESTING');

After this, i created a schema named 'Extraction_Schema' and did the following import.

import foreign schema TESTING
from server mysql_test
into extraction_schema;

Now when i run the query " select * from sys_root.dba_stored_tables;", i get the output which indicates as follows:-

| LOCALDB       | EXTRACTION_SCHEMA       | timetable                              |

| LOCALDB       | EXTRACTION_SCHEMA        | employee                            |

| LOCALDB       | EXTRACTION_SCHEMA        | user                                    |


This clearly shows that the data has been populated in the schema.

Now when i run the following query:-

set schema 'Extraction_Schema'

select count(*) from employee;

it gives me the following error:-

Error: From line 1, column 15 to line 1, column 22: Table 'EMPLOYEE' not found (
state=,code=0)

Could somebody please let me know where and what is going wrong?
I would really appreciate some help in this regard.

--
Regards,

Varun Gopinath

"To be the best, you need to survive the worst"




--
Regards,

Varun Gopinath

"To be the best, you need to survive the worst"



--
Regards,

Varun Gopinath

"To be the best, you need to survive the worst"


------------------------------------------------------------------------------
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: [SPAM] Query regarding LucidDB

Francisco Reyes-3
In reply to this post by Tom Barber
[hidden email] writes:

>    1) What difference in performance can i expect if i move it to LucidDB?

I am a relatively new user to Lucid myself, but in some (highly informal)
tests I did against postgresql the difference on the same queries with the
same structure was usually 3+ times. In a few cases Postgres chose the wrong
plans and in those cases Lucid was nearly 100 times faster; in those cases
when postgres chose a bad plan I had to manually force a different plan,
even then Lucid was 4  times faster.

Hoping to see even better performance once I finish copying all the data to
a star schema in Lucid.

> As in, would it still be placed as a  star schema?

Yes.

>    3) Also, would the compression and storage be more optimised if i
>    create the tables specifically in LucidDB instead of importing the same
>    from some other database?

Don't think so.


------------------------------------------------------------------------------
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...