You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Michael McAllister <mm...@homeaway.com> on 2016/08/12 15:50:47 UTC
Tables can have schema name but indexes cannot
Hi
Is there any reason we can specify the schema name for a table, but not an index. I note that the grammar online makes it clear this isn’t part of the syntax, but it would be nice if we could do it. To illustrate what I’d like:-
-- Create the table
CREATE TABLE IF NOT EXISTS MMCALLISTER.TEST
( c1 INTEGER NOT NULL
, c2 VARCHAR NULL
, c3 VARCHAR NULL
, CONSTRAINT TEST_PK PRIMARY KEY (c1)
);
-- This does not work
CREATE INDEX IF NOT EXISTS MMCALLISTER.TEST_IX01
ON MMCALLISTER.TEST
( c2 )
INCLUDE ( c3 );
-- This works
CREATE INDEX IF NOT EXISTS TEST_IX01
ON MMCALLISTER.TEST
( c2 )
INCLUDE ( c3 );
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image001.png@01D1F487.6250F490]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
Re: Tables can have schema name but indexes cannot
Posted by John Leach <jl...@gmail.com>.
Michael,
The object browser in DBVisualizer is driven by the jdbc driver. If you get any weird interaction, it usually means the JDBC implementation has an issue. We had issues at Splice Machine with our Foreign Keys returning incorrectly and then realized any deviation from the spec causes significant issues on the application side.
Regards,
John
> On Aug 12, 2016, at 11:43 AM, Michael McAllister <mm...@homeaway.com> wrote:
>
> James
>
> Thanks – looks like I was misled by DBVisualizer. The underlying hbase index tables automatically have the parent table’s schema name prepended, which is perfect. For some reason in the DBVisualizer object browser the indexes don’t show up in the correct schema, they’re showing up in a schema named (null).
>
> Michael McAllister
> Staff Data Warehouse Engineer | Decision Systems
> mmcallister@HomeAway.com <ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha <ma...@hotmail.com> | webex: https://h.a/mikewebex <https://h.a/mikewebex>
> <image001.png>
> This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
>
> From: James Taylor <jamestaylor@apache.org <ma...@apache.org>>
> Reply-To: "user@phoenix.apache.org <ma...@phoenix.apache.org>" <user@phoenix.apache.org <ma...@phoenix.apache.org>>
> Date: Friday, August 12, 2016 at 10:56 AM
> To: user <user@phoenix.apache.org <ma...@phoenix.apache.org>>
> Subject: Re: Tables can have schema name but indexes cannot
>
> Hi Michael,
> SQL dictates that an index must be in the same schema as the table it's indexing.
> Thanks,
> James
>
> On Fri, Aug 12, 2016 at 8:50 AM, Michael McAllister <mmcallister@homeaway.com <ma...@homeaway.com>> wrote:
> Hi
>
> Is there any reason we can specify the schema name for a table, but not an index. I note that the grammar online makes it clear this isn’t part of the syntax, but it would be nice if we could do it. To illustrate what I’d like:-
>
> -- Create the table
>
> CREATE TABLE IF NOT EXISTS MMCALLISTER.TEST
> ( c1 INTEGER NOT NULL
> , c2 VARCHAR NULL
> , c3 VARCHAR NULL
> , CONSTRAINT TEST_PK PRIMARY KEY (c1)
> );
>
> -- This does not work
>
> CREATE INDEX IF NOT EXISTS MMCALLISTER.TEST_IX01
> ON MMCALLISTER.TEST
> ( c2 )
> INCLUDE ( c3 );
>
> -- This works
>
> CREATE INDEX IF NOT EXISTS TEST_IX01
> ON MMCALLISTER.TEST
> ( c2 )
> INCLUDE ( c3 );
>
> Michael McAllister
> Staff Data Warehouse Engineer | Decision Systems
> mmcallister@HomeAway.com <ma...@HomeAway.com> | C: 512.423.7447 <tel:512.423.7447> | skype: michael.mcallister.ha <ma...@hotmail.com> | webex: https://h.a/mikewebex <https://h.a/mikewebex>
> <image002.png>
> This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
>
Re: Tables can have schema name but indexes cannot
Posted by Michael McAllister <mm...@homeaway.com>.
James
Thanks – looks like I was misled by DBVisualizer. The underlying hbase index tables automatically have the parent table’s schema name prepended, which is perfect. For some reason in the DBVisualizer object browser the indexes don’t show up in the correct schema, they’re showing up in a schema named (null).
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447 | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image001.png@01D1F48E.CF258110]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
From: James Taylor <ja...@apache.org>
Reply-To: "user@phoenix.apache.org" <us...@phoenix.apache.org>
Date: Friday, August 12, 2016 at 10:56 AM
To: user <us...@phoenix.apache.org>
Subject: Re: Tables can have schema name but indexes cannot
Hi Michael,
SQL dictates that an index must be in the same schema as the table it's indexing.
Thanks,
James
On Fri, Aug 12, 2016 at 8:50 AM, Michael McAllister <mm...@homeaway.com>> wrote:
Hi
Is there any reason we can specify the schema name for a table, but not an index. I note that the grammar online makes it clear this isn’t part of the syntax, but it would be nice if we could do it. To illustrate what I’d like:-
-- Create the table
CREATE TABLE IF NOT EXISTS MMCALLISTER.TEST
( c1 INTEGER NOT NULL
, c2 VARCHAR NULL
, c3 VARCHAR NULL
, CONSTRAINT TEST_PK PRIMARY KEY (c1)
);
-- This does not work
CREATE INDEX IF NOT EXISTS MMCALLISTER.TEST_IX01
ON MMCALLISTER.TEST
( c2 )
INCLUDE ( c3 );
-- This works
CREATE INDEX IF NOT EXISTS TEST_IX01
ON MMCALLISTER.TEST
( c2 )
INCLUDE ( c3 );
Michael McAllister
Staff Data Warehouse Engineer | Decision Systems
mmcallister@HomeAway.com<ma...@HomeAway.com> | C: 512.423.7447<tel:512.423.7447> | skype: michael.mcallister.ha<ma...@hotmail.com> | webex: https://h.a/mikewebex
[cid:image002.png@01D1F48E.CF258110]
This electronic communication (including any attachment) is confidential. If you are not an intended recipient of this communication, please be advised that any disclosure, dissemination, distribution, copying or other use of this communication or any attachment is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail and promptly destroy all electronic and printed copies of this communication and any attachment.
Re: Tables can have schema name but indexes cannot
Posted by James Taylor <ja...@apache.org>.
Hi Michael,
SQL dictates that an index must be in the same schema as the table it's
indexing.
Thanks,
James
On Fri, Aug 12, 2016 at 8:50 AM, Michael McAllister <
mmcallister@homeaway.com> wrote:
> Hi
>
>
>
> Is there any reason we can specify the schema name for a table, but not an
> index. I note that the grammar online makes it clear this isn’t part of the
> syntax, but it would be nice if we could do it. To illustrate what I’d
> like:-
>
>
>
> -- Create the table
>
>
>
> CREATE TABLE IF NOT EXISTS MMCALLISTER.TEST
>
> ( c1 INTEGER NOT NULL
>
> , c2 VARCHAR NULL
>
> , c3 VARCHAR NULL
>
> , CONSTRAINT TEST_PK PRIMARY KEY (c1)
>
> );
>
>
>
> -- This does not work
>
>
>
> CREATE INDEX IF NOT EXISTS MMCALLISTER.TEST_IX01
>
> ON MMCALLISTER.TEST
>
> ( c2 )
>
> INCLUDE ( c3 );
>
>
>
> -- This works
>
>
>
> CREATE INDEX IF NOT EXISTS TEST_IX01
>
> ON MMCALLISTER.TEST
>
> ( c2 )
>
> INCLUDE ( c3 );
>
>
>
> Michael McAllister
>
> Staff Data Warehouse Engineer | Decision Systems
>
> mmcallister@HomeAway.com | C: 512.423.7447 | skype: michael.mcallister.ha
> <zi...@hotmail.com> | webex: https://h.a/mikewebex
>
> This electronic communication (including any attachment) is confidential.
> If you are not an intended recipient of this communication, please be
> advised that any disclosure, dissemination, distribution, copying or other
> use of this communication or any attachment is strictly prohibited. If you
> have received this communication in error, please notify the sender
> immediately by reply e-mail and promptly destroy all electronic and printed
> copies of this communication and any attachment.
>