Rohit, ADS does not support a Create Table - Add Partition, because it can get very complicated and so we stick with a more standard GUI. We can extract and display the partition information in the Schema Browser for nCluster.
Ivan, we recently added a new node named Partitions for Greenplum (See issue# 9713). Can you extend this to nCluster as well ?
See attached pic
Partition is supported by NCluster 4.6 or newer versions. Partition hierarchy is presented in ADS' schema browser as:
Table
Partitions
Partitions
Partitions
...
See attached screenshot for more info.
14.0 - SVN r33642/14.0.0-beta-87
trunk - SVN r33643/15.0.0-dev-8
Included below are 3 scripts which will create a 1-level, 2-level, 3-level partition hierarchy FYI.
====== Example: 1 level ======
CREATE FACT TABLE "public"."issue_9758_level_1" (
"userid" int NOT NULL,
"pageid" int NOT NULL,
"ts" timestamp NOT NULL
)
DISTRIBUTE BY HASH ("userid")
PARTITION BY RANGE(ts) (
PARTITION clicks_pre_2013( END '2013-01-01'),
PARTITION clicks_2013_01 ( END '2013-02-01'),
PARTITION clicks_2013_02 ( END '2013-03-01'),
PARTITION clicks_2013_03 ( END '2013-04-01'),
PARTITION clicks_2013_04 ( END '2013-05-01'),
PARTITION clicks_2013_05 ( END '2013-06-01'),
PARTITION clicks_2013_06 ( END '2013-07-01'),
PARTITION clicks_2013_07 ( END '2013-08-01'),
PARTITION clicks_2013_08 ( END '2013-09-01')
)
go
====== Example: 2 levels ======
CREATE FACT TABLE "public"."issue_9758_level_2" (
"id" int NOT NULL,
"country" varchar NOT NULL,
"ts" timestamp NOT NULL
)
DISTRIBUTE BY HASH ("id")
PARTITION BY RANGE(ts) (
PARTITION ts_2010 ( END '2011-01-01'
PARTITION BY LIST ( country ) (
PARTITION c_na ( VALUES ('usa', 'canada', 'mexico' ) ),
PARTITION c_eu ( VALUES ('germany', 'spain' ) )
)
),
PARTITION ts_2011 ( END '2012-01-01'
PARTITION BY LIST ( country ) (
PARTITION c_na ( VALUES ('usa', 'canada', 'mexico' ) ),
PARTITION c_eu ( VALUES ('germany', 'spain' ) )
)
),
PARTITION ts_2012 ( END '2013-01-01'
PARTITION BY LIST ( country ) (
PARTITION c_na ( VALUES ('usa', 'canada', 'mexico' ) ),
PARTITION c_eu ( VALUES ('germany', 'spain' ) )
)
),
PARTITION ts_2013 ( END '2014-01-01'
PARTITION BY LIST ( country ) (
PARTITION c_na ( VALUES ('usa', 'canada', 'mexico' ) ),
PARTITION c_eu ( VALUES ('germany', 'spain' ) )
)
)
)
go
====== Example: 3 levels ======
CREATE FACT TABLE "public"."issue_9758_level_3" (
"id" int NOT NULL,
"gid" int NOT NULL,
"tid" varchar NOT NULL,
"ts" timestamp NOT NULL
)
DISTRIBUTE BY HASH ("id")
PARTITION BY RANGE(ts) (
PARTITION ts_2012 ( END '2013-01-01'
PARTITION BY RANGE ( gid ) (
PARTITION group_1 ( START 1 INCLUSIVE END 100 INCLUSIVE COMPRESS HIGH
PARTITION BY LIST ( tid ) (
PARTITION type_1 ( VALUES ( 't1', 't11' ) ),
PARTITION type_2 ( VALUES ( 't2', 't12' ) )
)
),
PARTITION group_2 ( START 101 INCLUSIVE END 200 INCLUSIVE COMPRESS LOW
PARTITION BY LIST ( tid ) (
PARTITION type_1 ( VALUES ( 't1', 't11' ) ),
PARTITION type_2 ( VALUES ( 't2', 't12' ) )
)
)
)
),
PARTITION ts_2013 ( END '2014-01-01'
PARTITION BY RANGE ( gid ) (
PARTITION group_1 ( START 1 INCLUSIVE END 100 INCLUSIVE COMPRESS MEDIUM
PARTITION BY LIST ( tid ) (
PARTITION type_1 ( VALUES ( 't1', 't11' ) ),
PARTITION type_2 ( VALUES ( 't2', 't12' ) )
)
),
PARTITION group_2 ( START 101 INCLUSIVE END 200 INCLUSIVE NOCOMPRESS
PARTITION BY LIST ( tid ) (
PARTITION type_1 ( VALUES ( 't1', 't11' ) ),
PARTITION type_2 ( VALUES ( 't2', 't12' ) )
)
)
)
)
)
go
Partition is supported by NCluster 4.6 or newer versions. Partition hierarchy is presented in ADS' schema browser as:
Table
Partitions
Partitions
Partitions
...
See attached screenshot for more info.
14.0 - SVN r33642/14.0.0-beta-87
trunk - SVN r33643/15.0.0-dev-8
Included below are 3 scripts which will create a 1-level, 2-level, 3-level partition hierarchy FYI.
====== Example: 1 level ======
CREATE FACT TABLE "public"."issue_9758_level_1" (
"userid" int NOT NULL,
"pageid" int NOT NULL,
"ts" timestamp NOT NULL
)
DISTRIBUTE BY HASH ("userid")
PARTITION BY RANGE(ts) (
PARTITION clicks_pre_2013( END '2013-01-01'),
PARTITION clicks_2013_01 ( END '2013-02-01'),
PARTITION clicks_2013_02 ( END '2013-03-01'),
PARTITION clicks_2013_03 ( END '2013-04-01'),
PARTITION clicks_2013_04 ( END '2013-05-01'),
PARTITION clicks_2013_05 ( END '2013-06-01'),
PARTITION clicks_2013_06 ( END '2013-07-01'),
PARTITION clicks_2013_07 ( END '2013-08-01'),
PARTITION clicks_2013_08 ( END '2013-09-01')
)
go
====== Example: 2 levels ======
CREATE FACT TABLE "public"."issue_9758_level_2" (
"id" int NOT NULL,
"country" varchar NOT NULL,
"ts" timestamp NOT NULL
)
DISTRIBUTE BY HASH ("id")
PARTITION BY RANGE(ts) (
PARTITION ts_2010 ( END '2011-01-01'
PARTITION BY LIST ( country ) (
PARTITION c_na ( VALUES ('usa', 'canada', 'mexico' ) ),
PARTITION c_eu ( VALUES ('germany', 'spain' ) )
)
),
PARTITION ts_2011 ( END '2012-01-01'
PARTITION BY LIST ( country ) (
PARTITION c_na ( VALUES ('usa', 'canada', 'mexico' ) ),
PARTITION c_eu ( VALUES ('germany', 'spain' ) )
)
),
PARTITION ts_2012 ( END '2013-01-01'
PARTITION BY LIST ( country ) (
PARTITION c_na ( VALUES ('usa', 'canada', 'mexico' ) ),
PARTITION c_eu ( VALUES ('germany', 'spain' ) )
)
),
PARTITION ts_2013 ( END '2014-01-01'
PARTITION BY LIST ( country ) (
PARTITION c_na ( VALUES ('usa', 'canada', 'mexico' ) ),
PARTITION c_eu ( VALUES ('germany', 'spain' ) )
)
)
)
go
====== Example: 3 levels ======
CREATE FACT TABLE "public"."issue_9758_level_3" (
"id" int NOT NULL,
"gid" int NOT NULL,
"tid" varchar NOT NULL,
"ts" timestamp NOT NULL
)
DISTRIBUTE BY HASH ("id")
PARTITION BY RANGE(ts) (
PARTITION ts_2012 ( END '2013-01-01'
PARTITION BY RANGE ( gid ) (
PARTITION group_1 ( START 1 INCLUSIVE END 100 INCLUSIVE COMPRESS HIGH
PARTITION BY LIST ( tid ) (
PARTITION type_1 ( VALUES ( 't1', 't11' ) ),
PARTITION type_2 ( VALUES ( 't2', 't12' ) )
)
),
PARTITION group_2 ( START 101 INCLUSIVE END 200 INCLUSIVE COMPRESS LOW
PARTITION BY LIST ( tid ) (
PARTITION type_1 ( VALUES ( 't1', 't11' ) ),
PARTITION type_2 ( VALUES ( 't2', 't12' ) )
)
)
)
),
PARTITION ts_2013 ( END '2014-01-01'
PARTITION BY RANGE ( gid ) (
PARTITION group_1 ( START 1 INCLUSIVE END 100 INCLUSIVE COMPRESS MEDIUM
PARTITION BY LIST ( tid ) (
PARTITION type_1 ( VALUES ( 't1', 't11' ) ),
PARTITION type_2 ( VALUES ( 't2', 't12' ) )
)
),
PARTITION group_2 ( START 101 INCLUSIVE END 200 INCLUSIVE NOCOMPRESS
PARTITION BY LIST ( tid ) (
PARTITION type_1 ( VALUES ( 't1', 't11' ) ),
PARTITION type_2 ( VALUES ( 't2', 't12' ) )
)
)
)
)
)
go
Issue #9758 |
Closed |
Fixed |
Resolved |
Completion |
No due date |
Fixed Build 14.0.0-beta-87/15.0.0-dev-8 |
No time estimate |
1 issue link |
relates to #9713
Issue #9713Display partition tables in the schema browser |
Rohit, ADS does not support a Create Table - Add Partition, because it can get very complicated and so we stick with a more standard GUI. We can extract and display the partition information in the Schema Browser for nCluster.
Ivan, we recently added a new node named Partitions for Greenplum (See issue# 9713). Can you extend this to nCluster as well ?
See attached pic