clintropolis opened a new pull request, #19830:
URL: https://github.com/apache/druid/pull/19830
### Description
This PR adds DDL statement support to Druid so that `CREATE` and `ALTER`
statements can be used to manage the catalog contents. This functionality
requires WRITE permissions to the datasource (same as the backing catalog
APIs), and is gated behind a new runtime property
`druid.sql.planner.enableCatalogDdl` which is false by default. This PR
includes support for defining the logical schema (columns list in catalog),
aggregate projections (projections property in catalog), base table projection
(baseTable property in catalog), clustering (`clusterKeys` in catalog), time
partitioning (`segmentGranularity` in catalog), and any other properties
(sealed, targetSegmentRows) with generic property setting syntax.
These operations are purely metadata operations, and so `DROP TABLE` has
been omitted since in my mind it has the most room for confusion (a DROP TABLE
only cleared metadata and didn't do anything else sounds kind of confusing to
me). Dropping columns and projections from a table felt more easily explainable
that these do not modify any existing data and instead stage the schema for
subsequent ingestion (and future work should wire this catalog stuff better
into compaction/reindexing so that we can also frame it such that
compaction/reindexing will begin to eventually converge on the updated schema).
These all go through the regular query path, and return 0 rows on successful
operation. Some follow-up work is needed to improve web-console syntax
highlighting and behaviors, and since there is no drop table statement it would
still probably be nice to eventually add a catalog management ui, but the
statements at least already work as-is through the current query interface.
Some examples:
create table:
```sql
CREATE TABLE "wikipedia" (
"channel" VARCHAR,
"__time" TIMESTAMP,
"page" VARCHAR,
"namespace" VARCHAR,
"user" VARCHAR,
"comment" VARCHAR,
"added" BIGINT,
"delta" BIGINT
)
PARTITIONED BY DAY
CLUSTERED BY "channel"
```
alter table to add column:
```sql
ALTER TABLE "wikipedia" ADD COLUMN "deleted" BIGINT
```
alter table to add projection:
```sql
ALTER TABLE "wikipedia" ADD PROJECTION "channel_sums" AS (
SELECT
TIME_FLOOR("__time", 'PT1H'),
"channel",
SUM("added") as "sum_added",
SUM("delta") as "sum_delta",
SUM("deleted") as "sum_deleted"
GROUP BY 1,2
)
```
alter table set property:
```sql
ALTER TABLE "wikipedia" SET PROPERTIES (sealed = true)
```
alter table set 'base table' projection to create clustered segments:
```sql
ALTER TABLE "wikipedia" ADD PROJECTION __base AS (
SELECT
"channel",
"__time",
"page",
"namespace,"
"user",
"comment",
"added",
"delta",
"deleted"
CLUSTERED BY "channel"
)
```
create table with base table and projection definitions:
```sql
CREATE TABLE "wikipedia" (
"channel" VARCHAR,
"__time" TIMESTAMP,
"page" VARCHAR,
"namespace" VARCHAR,
"user" VARCHAR,
"comment" VARCHAR,
"added" BIGINT,
"delta" BIGINT,
"deleted" BIGINT,
PROJECTION __base AS (
SELECT
"channel",
"__time",
"page",
"namespace",
"user",
"comment",
"added",
"delta",
"deleted"
CLUSTERED BY "channel"
),
PROJECTION "channel_sums" AS (
SELECT
TIME_FLOOR("__time", 'PT1H'),
"channel",
SUM("added") as "sum_added",
SUM("delta") as "sum_delta",
SUM("deleted") as "sum_deleted"
GROUP BY 1,2
),
PROJECTION "channel_page_max" AS (
SELECT
TIME_FLOOR("__time", 'PT1H'),
"channel",
"page",
MAX("added") as "sum_added",
MAX("delta") as "sum_delta",
MAX("deleted") as "sum_deleted"
GROUP BY 1,2,3
)
)
PARTITIONED BY DAY
CLUSTERED BY channel
SEALED
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
