phoenix-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Vincent Poon (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (PHOENIX-5095) Support INTERLEAVE of parent and child tables
Date Fri, 11 Jan 2019 00:44:00 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-5095?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16739907#comment-16739907
] 

Vincent Poon edited comment on PHOENIX-5095 at 1/11/19 12:43 AM:
-----------------------------------------------------------------

Hm, in my example above, select on the view returns columns from Singers.  To avoid that,
we need to create a base table separate from both, and create the parent as a view as well.

CREATE TABLE IF NOT EXISTS SingersBase (SingerId BIGINT NOT NULL,Delimiter CHAR(10) NOT NULL,CONSTRAINT
PK PRIMARY KEY(SingerId,Delimiter));
 CREATE VIEW Singers (FirstName VARCHAR, LastName VARCHAR) AS SELECT * from SingersBase where
Delimiter = 'Singers';

This returns 'Delimiter' in the results, though.


was (Author: vincentpoon):
Hm, in my example above, select on the view returns columns from Singers.  To avoid that,
we need to create a base table separate from both, and create the parent as a view as well.

CREATE TABLE IF NOT EXISTS SingersBase (SingerId BIGINT NOT NULL,Delimiter CHAR(10) NOT NULL,CONSTRAINT
PK PRIMARY KEY(SingerId,Delimiter));
CREATE VIEW Singers (FirstName VARCHAR, LastName VARCHAR) AS SELECT * from SingersBase where
Delimiter = 'Singers';

> Support INTERLEAVE of parent and child tables
> ---------------------------------------------
>
>                 Key: PHOENIX-5095
>                 URL: https://issues.apache.org/jira/browse/PHOENIX-5095
>             Project: Phoenix
>          Issue Type: Improvement
>    Affects Versions: 4.15.0
>            Reporter: Vincent Poon
>            Priority: Major
>
> Spanner has a concept of [interleaved tables|https://cloud.google.com/spanner/docs/schema-and-data-model#creating-interleaved-tables]
> I'd like to brainstorm here how to implement this in Phoenix.  In general we want a design
that can have
> 1) Fast queries against the parent table PK
> 2) Fast queries against the child table PK
> 3) Fast joins between the parent and child
> It seems we can get pretty close to this with views.  Views can have their own PK which
adds to the rowkey of the base table.  However, there doesn't seem to be a delimiter to distinguish
PKs of different views on the base table.  The closest I could up with is adding a delimiter
to the base table PK, something like:
> CREATE TABLE IF NOT EXISTS Singers (
>         SingerId BIGINT NOT NULL,
>         Delimiter CHAR(10) NOT NULL,
>         FirstName VARCHAR,
>         CONSTRAINT PK PRIMARY KEY
>         (
>                 SingerId,
>                 Delimiter
>         )
> );
> CREATE VIEW Albums (AlbumId BIGINT PRIMARY KEY, AlbumTitle VARCHAR) AS SELECT * from
Singers where Delimiter = 'Albums';
> We also need to make the JOIN on these tables more intelligent, such that a single scan
can join across parent-child.  Perhaps by reading metadata created during INTERLEAVE table
creation, so we know we are joining across interleaved tables.
> We could also have a custom split policy to avoid splitting in the middle of an interleaved
table (though this might restrict how large your interleaved child table can be).



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Mime
View raw message