You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by Charuta Rajopadhye <ch...@gmail.com> on 2017/10/12 09:16:10 UTC
Newbie: Join queries in MySQL.
Hi Team,
I have created a configuration for JDBC storage plugin in Drill, for a
MySQL database.
I am trying to fire join query on 2 tables. Both my tables have a column
called 'id'/
Table defination:
CREATE TABLE `simpleone` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
CREATE TABLE `simpletwo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`city` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
because of the redundant ‘id’ column, i get values from one ‘id’ column as
NULL.
I tried using a few permutations to fire my queries:
0: jdbc:drill:zk=local>* select * from **pluginjdbc**.drillTest.simpleone, *
*pluginjdbc**.drillTest.simpletwo where simpleone.id <http://simpleone.id>
= simpletwo.id <http://simpletwo.id>;*
+-----+-------------------+-------+----------------+
| id | name | *id0* | city |
+-----+-------------------+-------+----------------+
| 1 | J. D Salinger | *null* | New York City |
| 2 | Charlotte Bronte | *null* | Thornton |
+-----+-------------------+-------+----------------+
2 rows selected (0.224 seconds)
0: jdbc:drill:zk=local> *select tb1.id <http://tb1.id>, tb2.id
<http://tb2.id> from pluginjdbc.drillTest.simpleone as tb1,
pluginjdbc.drillTest.simpletwo as tb2 where tb1.id <http://tb1.id> = tb2.id
<http://tb2.id>;*
+-----+-------+
| id | id0 |
+-----+-------+
| 1 | null |
| 2 | null |
+-----+-------+
2 rows selected (0.368 seconds)
0: jdbc:drill:zk=local> *select tb1.id <http://tb1.id> as col1, tb2.id
<http://tb2.id> as col2 from pluginjdbc.drillTest.simpleone as tb1,
pluginjdbc.drillTest.simpletwo as tb2 where tb1.id <http://tb1.id> = tb2.id
<http://tb2.id>;*
+-------+-------+
| col1 | col2 |
+-------+-------+
| 1 | null |
| 2 | null |
+-------+-------+
2 rows selected (0.236 seconds)
Could somebody please guide me through this? Please let me know if i am
missing something.
Thanks,
Charuta
Re: Newbie: Join queries in MySQL.
Posted by Charuta Rajopadhye <ch...@gmail.com>.
I can try that, thanks a lot!
On Fri, Oct 13, 2017 at 11:06 PM, Kunal Khatua <kk...@mapr.com> wrote:
> You could try creating simple views (with different column names) on those
> tables. That way, you don’t have to try changing the column names in the
> table itself.
>
> -----Original Message-----
> From: Charuta Rajopadhye [mailto:charutar90@gmail.com]
> Sent: Thursday, October 12, 2017 11:28 PM
> To: dev@drill.apache.org
> Subject: Re: Newbie: Join queries in MySQL.
>
> Hi Timothy,
>
> Thank you very much for your reply.
>
> Regards,
> Charuta
>
> On Fri, Oct 13, 2017 at 9:53 AM, Timothy Farkas <tf...@mapr.com> wrote:
>
> > Hi Charuta,
> >
> > This is a known issue that other people have been bumping into as
> > well. As of now there is no work around other than changing the column
> > names in your tables. I've filed these tickets regarding the issue but
> > haven't gotten around to working on them yet:
> >
> > https://issues.apache.org/jira/browse/DRILL-5713
> > https://issues.apache.org/jira/browse/DRILL-4211
> >
> > Maybe someone else has some bandwidth to pick them up?
> >
> > Thanks,
> > Tim
> >
> > ________________________________
> > From: Charuta Rajopadhye <ch...@gmail.com>
> > Sent: Thursday, October 12, 2017 2:16:10 AM
> > To: dev@drill.apache.org
> > Subject: Newbie: Join queries in MySQL.
> >
> > Hi Team,
> >
> >
> > I have created a configuration for JDBC storage plugin in Drill, for a
> > MySQL database.
> >
> > I am trying to fire join query on 2 tables. Both my tables have a
> > column called 'id'/
> >
> > Table defination:
> >
> > CREATE TABLE `simpleone` (
> >
> > `id` int(11) NOT NULL AUTO_INCREMENT,
> >
> > `name` text NOT NULL,
> >
> > PRIMARY KEY (`id`)
> >
> > ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
> >
> > CREATE TABLE `simpletwo` (
> >
> > `id` int(11) NOT NULL AUTO_INCREMENT,
> >
> > `city` text NOT NULL,
> >
> > PRIMARY KEY (`id`)
> >
> > ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
> >
> >
> > because of the redundant ‘id’ column, i get values from one ‘id’
> > column as NULL.
> >
> > I tried using a few permutations to fire my queries:
> >
> > 0: jdbc:drill:zk=local>* select * from
> > **pluginjdbc**.drillTest.simpleone,
> > *
> > *pluginjdbc**.drillTest.simpletwo where simpleone.id
> > <http://simpleone.id> = simpletwo.id <http://simpletwo.id>;*
> >
> > +-----+-------------------+-------+----------------+
> >
> > | id | name | *id0* | city |
> >
> > +-----+-------------------+-------+----------------+
> >
> > | 1 | J. D Salinger | *null* | New York City |
> >
> > | 2 | Charlotte Bronte | *null* | Thornton |
> >
> > +-----+-------------------+-------+----------------+
> >
> > 2 rows selected (0.224 seconds)
> >
> >
> > 0: jdbc:drill:zk=local> *select tb1.id <http://tb1.id>, tb2.id
> > <http://tb2.id> from pluginjdbc.drillTest.simpleone as tb1,
> > pluginjdbc.drillTest.simpletwo as tb2 where tb1.id <http://tb1.id> =
> > tb2.id
> > <http://tb2.id>;*
> >
> > +-----+-------+
> >
> > | id | id0 |
> >
> > +-----+-------+
> >
> > | 1 | null |
> >
> > | 2 | null |
> >
> > +-----+-------+
> >
> > 2 rows selected (0.368 seconds)
> >
> >
> > 0: jdbc:drill:zk=local> *select tb1.id <http://tb1.id> as col1, tb2.id
> > <http://tb2.id> as col2 from pluginjdbc.drillTest.simpleone as tb1,
> > pluginjdbc.drillTest.simpletwo as tb2 where tb1.id <http://tb1.id> =
> > tb2.id
> > <http://tb2.id>;*
> >
> > +-------+-------+
> >
> > | col1 | col2 |
> >
> > +-------+-------+
> >
> > | 1 | null |
> >
> > | 2 | null |
> >
> > +-------+-------+
> >
> > 2 rows selected (0.236 seconds)
> >
> >
> > Could somebody please guide me through this? Please let me know if i
> > am missing something.
> >
> > Thanks,
> >
> > Charuta
> >
>
RE: Newbie: Join queries in MySQL.
Posted by Kunal Khatua <kk...@mapr.com>.
You could try creating simple views (with different column names) on those tables. That way, you don’t have to try changing the column names in the table itself.
-----Original Message-----
From: Charuta Rajopadhye [mailto:charutar90@gmail.com]
Sent: Thursday, October 12, 2017 11:28 PM
To: dev@drill.apache.org
Subject: Re: Newbie: Join queries in MySQL.
Hi Timothy,
Thank you very much for your reply.
Regards,
Charuta
On Fri, Oct 13, 2017 at 9:53 AM, Timothy Farkas <tf...@mapr.com> wrote:
> Hi Charuta,
>
> This is a known issue that other people have been bumping into as
> well. As of now there is no work around other than changing the column
> names in your tables. I've filed these tickets regarding the issue but
> haven't gotten around to working on them yet:
>
> https://issues.apache.org/jira/browse/DRILL-5713
> https://issues.apache.org/jira/browse/DRILL-4211
>
> Maybe someone else has some bandwidth to pick them up?
>
> Thanks,
> Tim
>
> ________________________________
> From: Charuta Rajopadhye <ch...@gmail.com>
> Sent: Thursday, October 12, 2017 2:16:10 AM
> To: dev@drill.apache.org
> Subject: Newbie: Join queries in MySQL.
>
> Hi Team,
>
>
> I have created a configuration for JDBC storage plugin in Drill, for a
> MySQL database.
>
> I am trying to fire join query on 2 tables. Both my tables have a
> column called 'id'/
>
> Table defination:
>
> CREATE TABLE `simpleone` (
>
> `id` int(11) NOT NULL AUTO_INCREMENT,
>
> `name` text NOT NULL,
>
> PRIMARY KEY (`id`)
>
> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
>
> CREATE TABLE `simpletwo` (
>
> `id` int(11) NOT NULL AUTO_INCREMENT,
>
> `city` text NOT NULL,
>
> PRIMARY KEY (`id`)
>
> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
>
>
> because of the redundant ‘id’ column, i get values from one ‘id’
> column as NULL.
>
> I tried using a few permutations to fire my queries:
>
> 0: jdbc:drill:zk=local>* select * from
> **pluginjdbc**.drillTest.simpleone,
> *
> *pluginjdbc**.drillTest.simpletwo where simpleone.id
> <http://simpleone.id> = simpletwo.id <http://simpletwo.id>;*
>
> +-----+-------------------+-------+----------------+
>
> | id | name | *id0* | city |
>
> +-----+-------------------+-------+----------------+
>
> | 1 | J. D Salinger | *null* | New York City |
>
> | 2 | Charlotte Bronte | *null* | Thornton |
>
> +-----+-------------------+-------+----------------+
>
> 2 rows selected (0.224 seconds)
>
>
> 0: jdbc:drill:zk=local> *select tb1.id <http://tb1.id>, tb2.id
> <http://tb2.id> from pluginjdbc.drillTest.simpleone as tb1,
> pluginjdbc.drillTest.simpletwo as tb2 where tb1.id <http://tb1.id> =
> tb2.id
> <http://tb2.id>;*
>
> +-----+-------+
>
> | id | id0 |
>
> +-----+-------+
>
> | 1 | null |
>
> | 2 | null |
>
> +-----+-------+
>
> 2 rows selected (0.368 seconds)
>
>
> 0: jdbc:drill:zk=local> *select tb1.id <http://tb1.id> as col1, tb2.id
> <http://tb2.id> as col2 from pluginjdbc.drillTest.simpleone as tb1,
> pluginjdbc.drillTest.simpletwo as tb2 where tb1.id <http://tb1.id> =
> tb2.id
> <http://tb2.id>;*
>
> +-------+-------+
>
> | col1 | col2 |
>
> +-------+-------+
>
> | 1 | null |
>
> | 2 | null |
>
> +-------+-------+
>
> 2 rows selected (0.236 seconds)
>
>
> Could somebody please guide me through this? Please let me know if i
> am missing something.
>
> Thanks,
>
> Charuta
>
Re: Newbie: Join queries in MySQL.
Posted by Charuta Rajopadhye <ch...@gmail.com>.
Hi Timothy,
Thank you very much for your reply.
Regards,
Charuta
On Fri, Oct 13, 2017 at 9:53 AM, Timothy Farkas <tf...@mapr.com> wrote:
> Hi Charuta,
>
> This is a known issue that other people have been bumping into as well. As
> of now there is no work around other than changing the column names in your
> tables. I've filed these tickets regarding the issue but haven't gotten
> around to working on them yet:
>
> https://issues.apache.org/jira/browse/DRILL-5713
> https://issues.apache.org/jira/browse/DRILL-4211
>
> Maybe someone else has some bandwidth to pick them up?
>
> Thanks,
> Tim
>
> ________________________________
> From: Charuta Rajopadhye <ch...@gmail.com>
> Sent: Thursday, October 12, 2017 2:16:10 AM
> To: dev@drill.apache.org
> Subject: Newbie: Join queries in MySQL.
>
> Hi Team,
>
>
> I have created a configuration for JDBC storage plugin in Drill, for a
> MySQL database.
>
> I am trying to fire join query on 2 tables. Both my tables have a column
> called 'id'/
>
> Table defination:
>
> CREATE TABLE `simpleone` (
>
> `id` int(11) NOT NULL AUTO_INCREMENT,
>
> `name` text NOT NULL,
>
> PRIMARY KEY (`id`)
>
> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
>
> CREATE TABLE `simpletwo` (
>
> `id` int(11) NOT NULL AUTO_INCREMENT,
>
> `city` text NOT NULL,
>
> PRIMARY KEY (`id`)
>
> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
>
>
> because of the redundant ‘id’ column, i get values from one ‘id’ column as
> NULL.
>
> I tried using a few permutations to fire my queries:
>
> 0: jdbc:drill:zk=local>* select * from **pluginjdbc**.drillTest.simpleone,
> *
> *pluginjdbc**.drillTest.simpletwo where simpleone.id <http://simpleone.id>
> = simpletwo.id <http://simpletwo.id>;*
>
> +-----+-------------------+-------+----------------+
>
> | id | name | *id0* | city |
>
> +-----+-------------------+-------+----------------+
>
> | 1 | J. D Salinger | *null* | New York City |
>
> | 2 | Charlotte Bronte | *null* | Thornton |
>
> +-----+-------------------+-------+----------------+
>
> 2 rows selected (0.224 seconds)
>
>
> 0: jdbc:drill:zk=local> *select tb1.id <http://tb1.id>, tb2.id
> <http://tb2.id> from pluginjdbc.drillTest.simpleone as tb1,
> pluginjdbc.drillTest.simpletwo as tb2 where tb1.id <http://tb1.id> =
> tb2.id
> <http://tb2.id>;*
>
> +-----+-------+
>
> | id | id0 |
>
> +-----+-------+
>
> | 1 | null |
>
> | 2 | null |
>
> +-----+-------+
>
> 2 rows selected (0.368 seconds)
>
>
> 0: jdbc:drill:zk=local> *select tb1.id <http://tb1.id> as col1, tb2.id
> <http://tb2.id> as col2 from pluginjdbc.drillTest.simpleone as tb1,
> pluginjdbc.drillTest.simpletwo as tb2 where tb1.id <http://tb1.id> =
> tb2.id
> <http://tb2.id>;*
>
> +-------+-------+
>
> | col1 | col2 |
>
> +-------+-------+
>
> | 1 | null |
>
> | 2 | null |
>
> +-------+-------+
>
> 2 rows selected (0.236 seconds)
>
>
> Could somebody please guide me through this? Please let me know if i am
> missing something.
>
> Thanks,
>
> Charuta
>
Re: Newbie: Join queries in MySQL.
Posted by Timothy Farkas <tf...@mapr.com>.
Hi Charuta,
This is a known issue that other people have been bumping into as well. As of now there is no work around other than changing the column names in your tables. I've filed these tickets regarding the issue but haven't gotten around to working on them yet:
https://issues.apache.org/jira/browse/DRILL-5713
https://issues.apache.org/jira/browse/DRILL-4211
Maybe someone else has some bandwidth to pick them up?
Thanks,
Tim
________________________________
From: Charuta Rajopadhye <ch...@gmail.com>
Sent: Thursday, October 12, 2017 2:16:10 AM
To: dev@drill.apache.org
Subject: Newbie: Join queries in MySQL.
Hi Team,
I have created a configuration for JDBC storage plugin in Drill, for a
MySQL database.
I am trying to fire join query on 2 tables. Both my tables have a column
called 'id'/
Table defination:
CREATE TABLE `simpleone` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
CREATE TABLE `simpletwo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`city` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
because of the redundant ‘id’ column, i get values from one ‘id’ column as
NULL.
I tried using a few permutations to fire my queries:
0: jdbc:drill:zk=local>* select * from **pluginjdbc**.drillTest.simpleone, *
*pluginjdbc**.drillTest.simpletwo where simpleone.id <http://simpleone.id>
= simpletwo.id <http://simpletwo.id>;*
+-----+-------------------+-------+----------------+
| id | name | *id0* | city |
+-----+-------------------+-------+----------------+
| 1 | J. D Salinger | *null* | New York City |
| 2 | Charlotte Bronte | *null* | Thornton |
+-----+-------------------+-------+----------------+
2 rows selected (0.224 seconds)
0: jdbc:drill:zk=local> *select tb1.id <http://tb1.id>, tb2.id
<http://tb2.id> from pluginjdbc.drillTest.simpleone as tb1,
pluginjdbc.drillTest.simpletwo as tb2 where tb1.id <http://tb1.id> = tb2.id
<http://tb2.id>;*
+-----+-------+
| id | id0 |
+-----+-------+
| 1 | null |
| 2 | null |
+-----+-------+
2 rows selected (0.368 seconds)
0: jdbc:drill:zk=local> *select tb1.id <http://tb1.id> as col1, tb2.id
<http://tb2.id> as col2 from pluginjdbc.drillTest.simpleone as tb1,
pluginjdbc.drillTest.simpletwo as tb2 where tb1.id <http://tb1.id> = tb2.id
<http://tb2.id>;*
+-------+-------+
| col1 | col2 |
+-------+-------+
| 1 | null |
| 2 | null |
+-------+-------+
2 rows selected (0.236 seconds)
Could somebody please guide me through this? Please let me know if i am
missing something.
Thanks,
Charuta