Example

• Create a users table in the default schema with

CREATE TABLE kudu.default.users (
  user_id int WITH (primary_key = true),
  first_name varchar,
  last_name varchar
) WITH (
  partition_by_hash_columns = ARRAY['user_id'],
  partition_by_hash_buckets = 2
);

On creating a Kudu table you must/can specify additional information about the primary key, encoding, and compression of columns and hash or range partitioning. Details see in section Create Table.

• The table can be described using

DESCRIBE kudu.default.users;

You should get something like

   Column   |  Type   |                      Extra                      | Comment
------------+---------+-------------------------------------------------+---------
 user_id    | integer | primary_key, encoding=auto, compression=default |
 first_name | varchar | nullable, encoding=auto, compression=default    |
 last_name  | varchar | nullable, encoding=auto, compression=default    |
(3 rows)

• Insert some data with

INSERT INTO kudu.default.users VALUES (1, 'Donald', 'Duck'), (2, 'Mickey', 'Mouse');

• Select the inserted data

SELECT * FROM kudu.default.users;

Example

CREATE TABLE mytable (
  name varchar WITH (primary_key = true, encoding = 'dictionary', compression = 'snappy'),
  index bigint WITH (nullable = true, encoding = 'runlength', compression = 'lz4'),
  comment varchar WITH (nullable = true, encoding = 'plain', compression = 'default'),
   ...
) WITH (...);

Hash partitioning

You can provide the first hash partition group with two table properties:

The partition_by_hash_columns defines the column(s) belonging to the partition group and partition_by_hash_buckets the number of partitions to split the hash values range into. All partition columns must be part of the primary key.

Example:

CREATE TABLE mytable (
  col1 varchar WITH (primary_key=true),
  col2 varchar WITH (primary_key=true),
  ...
) WITH (
  partition_by_hash_columns = ARRAY['col1', 'col2'],
  partition_by_hash_buckets = 4
)

This defines a hash partitioning with the columns col1 and col2 distributed over 4 partitions.

To define two separate hash partition groups, also use the second pair of table properties named partition_by_second_hash_columns and partition_by_second_hash_buckets.

Example:

CREATE TABLE mytable (
  col1 varchar WITH (primary_key=true),
  col2 varchar WITH (primary_key=true),
  ...
) WITH (
  partition_by_hash_columns = ARRAY['col1'],
  partition_by_hash_buckets = 2,
  partition_by_second_hash_columns = ARRAY['col2'],
  partition_by_second_hash_buckets = 3
)

This defines a two-level hash partitioning, with the first hash partition group over the column col1 distributed over 2 buckets, and the second hash partition group over the column col2 distributed over 3 buckets. As a result you have table with 2 x 3 = 6 partitions.

Range partitioning

You can provide at most one range partitioning in Apache Kudu. The columns are defined with the table property partition_by_range_columns. The ranges themselves are given either in the table property range_partitions on creating the table. Or alternatively, the procedures kudu.system.add_range_partition and kudu.system.drop_range_partition can be used to manage range partitions for existing tables. For both ways see below for more details.

Example:

CREATE TABLE events (
  rack varchar WITH (primary_key=true),
  machine varchar WITH (primary_key=true),
  event_time timestamp WITH (primary_key=true),
  ...
) WITH (
  partition_by_hash_columns = ARRAY['rack'],
  partition_by_hash_buckets = 2,
  partition_by_second_hash_columns = ARRAY['machine'],
  partition_by_second_hash_buckets = 3,
  partition_by_range_columns = ARRAY['event_time'],
  range_partitions = '[{"lower": null, "upper": "2018-01-01T00:00:00"}, {"lower": "2018-01-01T00:00:00", "upper": null}]'
)

This defines a tree-level partitioning with two hash partition groups and one range partitioning on the event_time column. Two range partitions are created with a split at “2018-01-01T00:00:00”.

Managing range partitions

For existing tables, there are procedures to add and drop a range partition.

• adding a range partition

  CALL kudu.system.add_range_partition(<schema>, <table>, <range_partition_as_json_string>),
  

• dropping a range partition

  CALL kudu.system.drop_range_partition(<schema>, <table>, <range_partition_as_json_string>)
  

  • <schema>: schema of the table

  • <table>: table names

  • <range_partition_as_json_string>: lower and upper bound of the range partition as json string in the form '{"lower": <value>, "upper": <value>}', or if the range partition has multiple columns: '{"lower": [<value_col1>,...], "upper": [<value_col1>,...]}'. The concrete literal for lower and upper bound values are depending on the column types.

    Examples:

    Presto Data Type	JSON string example
    BIGINT	‘{“lower”: 0, “upper”: 1000000}’
    SMALLINT	‘{“lower”: 10, “upper”: null}’
    VARCHAR	‘{“lower”: “A”, “upper”: “M”}’
    TIMESTAMP	‘{“lower”: “2018-02-01T00:00:00.000”, “upper”: “2018-02-01T12:00:00.000”}’
    BOOLEAN	‘{“lower”: false, “upper”: true}’
    VARBINARY	values encoded as base64 strings

    To specified an unbounded bound, use the value null.

Example:

CALL kudu.system.add_range_partition('myschema', 'events', '{"lower": "2018-01-01", "upper": "2018-06-01"}')

This adds a range partition for a table events in the schema myschema with the lower bound 2018-01-01, more exactly 2018-01-01T00:00:00.000, and the upper bound 2018-07-01.

Use the SQL statement SHOW CREATE TABLE to query the existing range partitions (they are shown in the table property range_partitions).