gianm commented on code in PR #19830:
URL: https://github.com/apache/druid/pull/19830#discussion_r3853665645
##########
docs/development/extensions-core/catalog.md:
##########
@@ -43,6 +43,177 @@ allowing queries to be more concise, and simpler to write.
This also allows the
written into a defined column of the table is consistent with that columns
definition, minimizing errors where unexpected
data is written into a particular column of the table.
+### SQL DDL
+
+Tables can be defined with SQL instead of by posting a table specification.
`CREATE TABLE` and `ALTER TABLE` are
+submitted to the Broker like any other SQL statement, and write the same
catalog metadata the REST API does. They
+return no rows.
+
+These statements change catalog metadata only. They never create, modify, or
delete segments: defining a table does
Review Comment:
> never create, modify, or delete segments
Well, they don't immediately do these things synchronously, but they may
trigger these things to happen in the background as part of compaction. This is
worth mentioning.
(Or do they? What's the current state of catalog-based compaction?)
##########
docs/development/extensions-core/catalog.md:
##########
@@ -43,6 +43,177 @@ allowing queries to be more concise, and simpler to write.
This also allows the
written into a defined column of the table is consistent with that columns
definition, minimizing errors where unexpected
data is written into a particular column of the table.
+### SQL DDL
+
+Tables can be defined with SQL instead of by posting a table specification.
`CREATE TABLE` and `ALTER TABLE` are
+submitted to the Broker like any other SQL statement, and write the same
catalog metadata the REST API does. They
+return no rows.
+
+These statements change catalog metadata only. They never create, modify, or
delete segments: defining a table does
+not ingest anything, and altering a column does not rewrite existing data.
Column changes take effect for subsequent
+ingestion.
+
+These statements are disabled by default. Set
`druid.sql.planner.enableCatalogDdl` to `true` on the Broker to enable
+them. They require `WRITE` permission on the datasource, the same permission
the catalog API requires, so enabling
Review Comment:
Can you make these require both `READ` and `WRITE`? This is a change I'm
proposing to #20075 for `INSERT` and `REPLACE`, on the grounds that users
without `READ` can't see a table and therefore can't write to it.
##########
docs/development/extensions-core/catalog.md:
##########
@@ -43,6 +43,177 @@ allowing queries to be more concise, and simpler to write.
This also allows the
written into a defined column of the table is consistent with that columns
definition, minimizing errors where unexpected
data is written into a particular column of the table.
+### SQL DDL
+
+Tables can be defined with SQL instead of by posting a table specification.
`CREATE TABLE` and `ALTER TABLE` are
+submitted to the Broker like any other SQL statement, and write the same
catalog metadata the REST API does. They
+return no rows.
+
+These statements change catalog metadata only. They never create, modify, or
delete segments: defining a table does
+not ingest anything, and altering a column does not rewrite existing data.
Column changes take effect for subsequent
+ingestion.
+
+These statements are disabled by default. Set
`druid.sql.planner.enableCatalogDdl` to `true` on the Broker to enable
+them. They require `WRITE` permission on the datasource, the same permission
the catalog API requires, so enabling
+them lets anyone who can ingest into a datasource also change its catalog
definition; leave them disabled if you
+manage catalog entries with your own tooling. The setting cannot be overridden
per query.
+
+The `druid-catalog` extension must be loaded on both the Broker and the
Coordinator; without it, these statements
+report that the extension is not available.
+
+```sql
+CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table>
+ [ ( { <column> <type> | PROJECTION <name> AS ( <select> ) } [, ...] ) ]
+ [ PARTITIONED BY <granularity> ]
+ [ CLUSTERED BY <column> [, ...] ]
+ [ SEALED ]
Review Comment:
Feels like this should go closer to the column list. Maybe this?
```
CREATE TABLE tbl SEALED ( columns ... )
```
##########
docs/development/extensions-core/catalog.md:
##########
@@ -43,6 +43,177 @@ allowing queries to be more concise, and simpler to write.
This also allows the
written into a defined column of the table is consistent with that columns
definition, minimizing errors where unexpected
data is written into a particular column of the table.
+### SQL DDL
+
+Tables can be defined with SQL instead of by posting a table specification.
`CREATE TABLE` and `ALTER TABLE` are
+submitted to the Broker like any other SQL statement, and write the same
catalog metadata the REST API does. They
+return no rows.
+
+These statements change catalog metadata only. They never create, modify, or
delete segments: defining a table does
+not ingest anything, and altering a column does not rewrite existing data.
Column changes take effect for subsequent
+ingestion.
+
+These statements are disabled by default. Set
`druid.sql.planner.enableCatalogDdl` to `true` on the Broker to enable
+them. They require `WRITE` permission on the datasource, the same permission
the catalog API requires, so enabling
+them lets anyone who can ingest into a datasource also change its catalog
definition; leave them disabled if you
+manage catalog entries with your own tooling. The setting cannot be overridden
per query.
+
+The `druid-catalog` extension must be loaded on both the Broker and the
Coordinator; without it, these statements
+report that the extension is not available.
+
+```sql
+CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table>
+ [ ( { <column> <type> | PROJECTION <name> AS ( <select> ) } [, ...] ) ]
Review Comment:
This one line may be clearer if formatted on multiple lines.
##########
docs/development/extensions-core/catalog.md:
##########
@@ -43,6 +43,177 @@ allowing queries to be more concise, and simpler to write.
This also allows the
written into a defined column of the table is consistent with that columns
definition, minimizing errors where unexpected
data is written into a particular column of the table.
+### SQL DDL
+
+Tables can be defined with SQL instead of by posting a table specification.
`CREATE TABLE` and `ALTER TABLE` are
+submitted to the Broker like any other SQL statement, and write the same
catalog metadata the REST API does. They
+return no rows.
+
+These statements change catalog metadata only. They never create, modify, or
delete segments: defining a table does
+not ingest anything, and altering a column does not rewrite existing data.
Column changes take effect for subsequent
+ingestion.
+
+These statements are disabled by default. Set
`druid.sql.planner.enableCatalogDdl` to `true` on the Broker to enable
+them. They require `WRITE` permission on the datasource, the same permission
the catalog API requires, so enabling
+them lets anyone who can ingest into a datasource also change its catalog
definition; leave them disabled if you
+manage catalog entries with your own tooling. The setting cannot be overridden
per query.
+
+The `druid-catalog` extension must be loaded on both the Broker and the
Coordinator; without it, these statements
+report that the extension is not available.
+
+```sql
+CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table>
+ [ ( { <column> <type> | PROJECTION <name> AS ( <select> ) } [, ...] ) ]
+ [ PARTITIONED BY <granularity> ]
+ [ CLUSTERED BY <column> [, ...] ]
+ [ SEALED ]
+```
+
+`OR REPLACE` replaces the specification of an existing table; `IF NOT EXISTS`
leaves an existing table unchanged.
+The two cannot be combined. `PARTITIONED BY` sets
[`segmentGranularity`](#table-properties) and `CLUSTERED BY` sets
+`clusterKeys`, both of which a later `INSERT` or `REPLACE` inherits unless it
states its own. `SEALED` sets
+[`sealed`](#table-properties), which requires every ingested column to be
declared.
+
+Note that the table-level `CLUSTERED BY` is a sort order applied to each
ingestion, which is a different thing from
+the `CLUSTERED BY` inside a [`__base` projection](#the-base-table), which
defines how segments physically group rows.
+
+Column types are written as SQL types, such as `VARCHAR`, `BIGINT`, `DOUBLE`,
or `VARCHAR ARRAY`. The `__time` column
+is written as `TIMESTAMP`. Types that have no SQL spelling, such as complex
types, use `TYPE('...')` with the Druid
+native type string:
+
+```sql
+CREATE TABLE "druid"."visits" (
+ __time TIMESTAMP,
+ user_id VARCHAR,
+ pages_visited BIGINT,
+ sketch TYPE('COMPLEX<thetaSketch>')
+)
+PARTITIONED BY DAY
+CLUSTERED BY user_id
+```
+
+`ALTER TABLE` supports one change per statement, so that each statement is a
single atomic catalog operation:
+
+```sql
+ALTER TABLE <table> ADD COLUMN <column> <type>
+ALTER TABLE <table> DROP COLUMN <column>
+ALTER TABLE <table> ALTER COLUMN <column> SET DATA TYPE <type>
+ALTER TABLE <table> ADD [IF NOT EXISTS] PROJECTION <name> AS ( <select> )
+ALTER TABLE <table> DROP PROJECTION [IF EXISTS] <name>
+ALTER TABLE <table> SET PROPERTIES ( <property> = <value> [, ...] )
+```
+
+`ADD COLUMN` fails if the column already exists, and `ALTER COLUMN` fails if
it does not, so a misspelled column name
+is reported rather than quietly creating or replacing a column. Each statement
is also checked against the rest of the
+table definition, not only the part it changes: adding a column, changing a
type, or setting a property is rejected if
+the resulting table would be invalid, such as a segment granularity coarser
than a projection the table declares.
+
+#### Projections
+
+A table may declare [projections](../../querying/projections.md), which are
pre-aggregated views stored inside each
+segment. A projection is written as a `SELECT` over the table's own columns,
with no `FROM` clause:
+
+```sql
+CREATE TABLE "druid"."visits" (
+ __time TIMESTAMP,
+ user_id VARCHAR,
+ user_agent VARCHAR,
+ pages_visited BIGINT,
+ PROJECTION daily_by_agent AS (
+ SELECT TIME_FLOOR(__time, 'P1D'), user_agent, SUM(pages_visited) AS
total_pages
+ WHERE user_agent IS NOT NULL
+ GROUP BY 1, 2
+ )
+)
+PARTITIONED BY DAY
+```
+
+The body is planned exactly as the equivalent query would be, so a projection
matches the queries it was written to
+serve. Every aggregate needs an alias, which becomes the name of the stored
column. Time granularity is expressed
+with `TIME_FLOOR`, as it would be in a query.
+
+Because the body is planned like a query, it is planned under the statement's
own query context, including any `SET`
+clauses. Only context parameters that affect planning can change the stored
definition; parameters that only affect
+query execution have no effect, since the body is planned and stored rather
than run. The context itself is not part
+of the definition: what the catalog stores is the projection the body planned
to, so nothing from the statement's
+context is carried over to queries that later use it. Note also that a
projection is matched to a query by its shape,
+so a definition planned under a context that changes that shape only matches
queries run under the same context.
+
+A projection body accepts a select list, an optional `WHERE` and an optional
`GROUP BY`. It cannot use `ORDER BY`,
+`LIMIT` or `HAVING`: a projection's ordering follows its grouping columns and
is not something you choose. It also
+cannot use joins, subqueries, or expressions computed over aggregates. Store
the aggregates instead: `SUM(x)` and
+`COUNT(x)` rather than `AVG(x)`.
Review Comment:
This doc point about `AVG(x)` doesn't really make sense unless you know that
Druid internally converts `AVG` to `SUM / COUNT`. Not all databases do this, so
a better example would be an explicit expression like `SUM(x) / COUNT(x)`.
There is also a list of aggregation functions that can be used at ingest time
at `docs/multi-stage-query/concepts.md#rollup`, which you can link to.
##########
docs/development/extensions-core/catalog.md:
##########
@@ -43,6 +43,177 @@ allowing queries to be more concise, and simpler to write.
This also allows the
written into a defined column of the table is consistent with that columns
definition, minimizing errors where unexpected
data is written into a particular column of the table.
+### SQL DDL
+
+Tables can be defined with SQL instead of by posting a table specification.
`CREATE TABLE` and `ALTER TABLE` are
+submitted to the Broker like any other SQL statement, and write the same
catalog metadata the REST API does. They
+return no rows.
+
+These statements change catalog metadata only. They never create, modify, or
delete segments: defining a table does
+not ingest anything, and altering a column does not rewrite existing data.
Column changes take effect for subsequent
+ingestion.
+
+These statements are disabled by default. Set
`druid.sql.planner.enableCatalogDdl` to `true` on the Broker to enable
+them. They require `WRITE` permission on the datasource, the same permission
the catalog API requires, so enabling
+them lets anyone who can ingest into a datasource also change its catalog
definition; leave them disabled if you
+manage catalog entries with your own tooling. The setting cannot be overridden
per query.
+
+The `druid-catalog` extension must be loaded on both the Broker and the
Coordinator; without it, these statements
+report that the extension is not available.
+
+```sql
+CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table>
+ [ ( { <column> <type> | PROJECTION <name> AS ( <select> ) } [, ...] ) ]
+ [ PARTITIONED BY <granularity> ]
+ [ CLUSTERED BY <column> [, ...] ]
+ [ SEALED ]
+```
+
+`OR REPLACE` replaces the specification of an existing table; `IF NOT EXISTS`
leaves an existing table unchanged.
+The two cannot be combined. `PARTITIONED BY` sets
[`segmentGranularity`](#table-properties) and `CLUSTERED BY` sets
+`clusterKeys`, both of which a later `INSERT` or `REPLACE` inherits unless it
states its own. `SEALED` sets
+[`sealed`](#table-properties), which requires every ingested column to be
declared.
+
+Note that the table-level `CLUSTERED BY` is a sort order applied to each
ingestion, which is a different thing from
+the `CLUSTERED BY` inside a [`__base` projection](#the-base-table), which
defines how segments physically group rows.
+
+Column types are written as SQL types, such as `VARCHAR`, `BIGINT`, `DOUBLE`,
or `VARCHAR ARRAY`. The `__time` column
+is written as `TIMESTAMP`. Types that have no SQL spelling, such as complex
types, use `TYPE('...')` with the Druid
+native type string:
+
+```sql
+CREATE TABLE "druid"."visits" (
+ __time TIMESTAMP,
+ user_id VARCHAR,
+ pages_visited BIGINT,
+ sketch TYPE('COMPLEX<thetaSketch>')
+)
+PARTITIONED BY DAY
+CLUSTERED BY user_id
+```
+
+`ALTER TABLE` supports one change per statement, so that each statement is a
single atomic catalog operation:
+
+```sql
+ALTER TABLE <table> ADD COLUMN <column> <type>
+ALTER TABLE <table> DROP COLUMN <column>
+ALTER TABLE <table> ALTER COLUMN <column> SET DATA TYPE <type>
+ALTER TABLE <table> ADD [IF NOT EXISTS] PROJECTION <name> AS ( <select> )
+ALTER TABLE <table> DROP PROJECTION [IF EXISTS] <name>
+ALTER TABLE <table> SET PROPERTIES ( <property> = <value> [, ...] )
+```
+
+`ADD COLUMN` fails if the column already exists, and `ALTER COLUMN` fails if
it does not, so a misspelled column name
+is reported rather than quietly creating or replacing a column. Each statement
is also checked against the rest of the
+table definition, not only the part it changes: adding a column, changing a
type, or setting a property is rejected if
+the resulting table would be invalid, such as a segment granularity coarser
than a projection the table declares.
+
+#### Projections
+
+A table may declare [projections](../../querying/projections.md), which are
pre-aggregated views stored inside each
+segment. A projection is written as a `SELECT` over the table's own columns,
with no `FROM` clause:
+
+```sql
+CREATE TABLE "druid"."visits" (
+ __time TIMESTAMP,
+ user_id VARCHAR,
+ user_agent VARCHAR,
+ pages_visited BIGINT,
+ PROJECTION daily_by_agent AS (
+ SELECT TIME_FLOOR(__time, 'P1D'), user_agent, SUM(pages_visited) AS
total_pages
+ WHERE user_agent IS NOT NULL
+ GROUP BY 1, 2
+ )
+)
+PARTITIONED BY DAY
+```
+
+The body is planned exactly as the equivalent query would be, so a projection
matches the queries it was written to
+serve. Every aggregate needs an alias, which becomes the name of the stored
column. Time granularity is expressed
+with `TIME_FLOOR`, as it would be in a query.
+
+Because the body is planned like a query, it is planned under the statement's
own query context, including any `SET`
+clauses. Only context parameters that affect planning can change the stored
definition; parameters that only affect
+query execution have no effect, since the body is planned and stored rather
than run. The context itself is not part
+of the definition: what the catalog stores is the projection the body planned
to, so nothing from the statement's
+context is carried over to queries that later use it. Note also that a
projection is matched to a query by its shape,
+so a definition planned under a context that changes that shape only matches
queries run under the same context.
+
+A projection body accepts a select list, an optional `WHERE` and an optional
`GROUP BY`. It cannot use `ORDER BY`,
+`LIMIT` or `HAVING`: a projection's ordering follows its grouping columns and
is not something you choose. It also
+cannot use joins, subqueries, or expressions computed over aggregates. Store
the aggregates instead: `SUM(x)` and
+`COUNT(x)` rather than `AVG(x)`.
+
+Projections may also be added to and removed from an existing table:
+
+```sql
+ALTER TABLE "druid"."visits" ADD [IF NOT EXISTS] PROJECTION by_agent AS (
+ SELECT user_agent, SUM(pages_visited) AS total_pages GROUP BY user_agent
+)
+ALTER TABLE "druid"."visits" DROP PROJECTION [IF EXISTS] by_agent
+```
+
+Both take effect for subsequent ingestion. Segments already built keep
whatever projections they were built with, so
+dropping a projection does not rewrite data.
+
+#### The base table
+
+The reserved projection name `__base` describes the table's own physical
layout rather than an additional
+pre-aggregation. Defining it makes the table a 'clustered' table: rows of
segments are stored grouped by the clustering
+columns.
+
+Its body lists the columns in the order segments store them, so it must name
every declared column, in declared
+order. An item written as `<expr> AS <name>` makes that column computed at
ingest time, from the columns it reads:
+
+```sql
+CREATE TABLE "druid"."events" (
+ tenant VARCHAR,
+ bucket BIGINT,
+ __time TIMESTAMP,
+ user_id BIGINT,
+ payload TYPE('COMPLEX<json>'),
+ PROJECTION __base AS (
+ SELECT tenant, ABS(user_id) % 128 AS bucket, __time, user_id, payload
+ CLUSTERED BY tenant, bucket
+ )
+)
+PARTITIONED BY DAY
+SEALED
+```
+
+The clustering columns must be the leading columns of the table, because the
declared order is the physical order.
+`SEALED` is optional: a column the query produces but the table does not
declare is stored after the declared
+layout, in the order it arrives. Declare `SEALED` to reject such columns
instead.
+
+A computed column is written by the expression, not by the ingestion query, so
an `INSERT` must supply the
Review Comment:
"computed column is written by the expression, not by the ingestion query"
to me is unclear wording. The example helps though. Maybe try:
> Computed columns, like `bucket` in the example above, are computed based
on inputs provided by `INSERT` or `REPLACE`. In the example above, the `INSERT`
or `REPLACE` command should provide `user_id`, not `bucket`.
##########
docs/development/extensions-core/catalog.md:
##########
@@ -43,6 +43,177 @@ allowing queries to be more concise, and simpler to write.
This also allows the
written into a defined column of the table is consistent with that columns
definition, minimizing errors where unexpected
data is written into a particular column of the table.
+### SQL DDL
+
+Tables can be defined with SQL instead of by posting a table specification.
`CREATE TABLE` and `ALTER TABLE` are
+submitted to the Broker like any other SQL statement, and write the same
catalog metadata the REST API does. They
+return no rows.
+
+These statements change catalog metadata only. They never create, modify, or
delete segments: defining a table does
+not ingest anything, and altering a column does not rewrite existing data.
Column changes take effect for subsequent
+ingestion.
+
+These statements are disabled by default. Set
`druid.sql.planner.enableCatalogDdl` to `true` on the Broker to enable
+them. They require `WRITE` permission on the datasource, the same permission
the catalog API requires, so enabling
+them lets anyone who can ingest into a datasource also change its catalog
definition; leave them disabled if you
+manage catalog entries with your own tooling. The setting cannot be overridden
per query.
+
+The `druid-catalog` extension must be loaded on both the Broker and the
Coordinator; without it, these statements
+report that the extension is not available.
+
+```sql
+CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table>
+ [ ( { <column> <type> | PROJECTION <name> AS ( <select> ) } [, ...] ) ]
+ [ PARTITIONED BY <granularity> ]
+ [ CLUSTERED BY <column> [, ...] ]
+ [ SEALED ]
+```
+
+`OR REPLACE` replaces the specification of an existing table; `IF NOT EXISTS`
leaves an existing table unchanged.
+The two cannot be combined. `PARTITIONED BY` sets
[`segmentGranularity`](#table-properties) and `CLUSTERED BY` sets
+`clusterKeys`, both of which a later `INSERT` or `REPLACE` inherits unless it
states its own. `SEALED` sets
+[`sealed`](#table-properties), which requires every ingested column to be
declared.
+
+Note that the table-level `CLUSTERED BY` is a sort order applied to each
ingestion, which is a different thing from
+the `CLUSTERED BY` inside a [`__base` projection](#the-base-table), which
defines how segments physically group rows.
+
+Column types are written as SQL types, such as `VARCHAR`, `BIGINT`, `DOUBLE`,
or `VARCHAR ARRAY`. The `__time` column
+is written as `TIMESTAMP`. Types that have no SQL spelling, such as complex
types, use `TYPE('...')` with the Druid
+native type string:
+
+```sql
+CREATE TABLE "druid"."visits" (
+ __time TIMESTAMP,
+ user_id VARCHAR,
+ pages_visited BIGINT,
+ sketch TYPE('COMPLEX<thetaSketch>')
+)
+PARTITIONED BY DAY
+CLUSTERED BY user_id
+```
+
+`ALTER TABLE` supports one change per statement, so that each statement is a
single atomic catalog operation:
+
+```sql
+ALTER TABLE <table> ADD COLUMN <column> <type>
+ALTER TABLE <table> DROP COLUMN <column>
+ALTER TABLE <table> ALTER COLUMN <column> SET DATA TYPE <type>
+ALTER TABLE <table> ADD [IF NOT EXISTS] PROJECTION <name> AS ( <select> )
+ALTER TABLE <table> DROP PROJECTION [IF EXISTS] <name>
+ALTER TABLE <table> SET PROPERTIES ( <property> = <value> [, ...] )
+```
+
+`ADD COLUMN` fails if the column already exists, and `ALTER COLUMN` fails if
it does not, so a misspelled column name
+is reported rather than quietly creating or replacing a column. Each statement
is also checked against the rest of the
+table definition, not only the part it changes: adding a column, changing a
type, or setting a property is rejected if
+the resulting table would be invalid, such as a segment granularity coarser
than a projection the table declares.
+
+#### Projections
+
+A table may declare [projections](../../querying/projections.md), which are
pre-aggregated views stored inside each
+segment. A projection is written as a `SELECT` over the table's own columns,
with no `FROM` clause:
+
+```sql
+CREATE TABLE "druid"."visits" (
+ __time TIMESTAMP,
+ user_id VARCHAR,
+ user_agent VARCHAR,
+ pages_visited BIGINT,
+ PROJECTION daily_by_agent AS (
+ SELECT TIME_FLOOR(__time, 'P1D'), user_agent, SUM(pages_visited) AS
total_pages
+ WHERE user_agent IS NOT NULL
+ GROUP BY 1, 2
+ )
+)
+PARTITIONED BY DAY
+```
+
+The body is planned exactly as the equivalent query would be, so a projection
matches the queries it was written to
+serve. Every aggregate needs an alias, which becomes the name of the stored
column. Time granularity is expressed
+with `TIME_FLOOR`, as it would be in a query.
+
+Because the body is planned like a query, it is planned under the statement's
own query context, including any `SET`
+clauses. Only context parameters that affect planning can change the stored
definition; parameters that only affect
+query execution have no effect, since the body is planned and stored rather
than run. The context itself is not part
+of the definition: what the catalog stores is the projection the body planned
to, so nothing from the statement's
+context is carried over to queries that later use it. Note also that a
projection is matched to a query by its shape,
+so a definition planned under a context that changes that shape only matches
queries run under the same context.
+
+A projection body accepts a select list, an optional `WHERE` and an optional
`GROUP BY`. It cannot use `ORDER BY`,
+`LIMIT` or `HAVING`: a projection's ordering follows its grouping columns and
is not something you choose. It also
+cannot use joins, subqueries, or expressions computed over aggregates. Store
the aggregates instead: `SUM(x)` and
+`COUNT(x)` rather than `AVG(x)`.
+
+Projections may also be added to and removed from an existing table:
+
+```sql
+ALTER TABLE "druid"."visits" ADD [IF NOT EXISTS] PROJECTION by_agent AS (
+ SELECT user_agent, SUM(pages_visited) AS total_pages GROUP BY user_agent
+)
+ALTER TABLE "druid"."visits" DROP PROJECTION [IF EXISTS] by_agent
+```
+
+Both take effect for subsequent ingestion. Segments already built keep
whatever projections they were built with, so
+dropping a projection does not rewrite data.
+
+#### The base table
+
+The reserved projection name `__base` describes the table's own physical
layout rather than an additional
+pre-aggregation. Defining it makes the table a 'clustered' table: rows of
segments are stored grouped by the clustering
+columns.
Review Comment:
Re: "defining it makes the table a clustered table": Is it an error to
provide `__base` without `CLUSTERED BY`?
##########
docs/development/extensions-core/catalog.md:
##########
@@ -43,6 +43,177 @@ allowing queries to be more concise, and simpler to write.
This also allows the
written into a defined column of the table is consistent with that columns
definition, minimizing errors where unexpected
data is written into a particular column of the table.
+### SQL DDL
+
+Tables can be defined with SQL instead of by posting a table specification.
`CREATE TABLE` and `ALTER TABLE` are
Review Comment:
It would be great to have `SHOW CREATE TABLE` or `DESCRIBE TABLE` too,
otherwise people will find these APIs hard to use without referring back to the
JSON. This can be done in a follow up.
##########
docs/development/extensions-core/catalog.md:
##########
@@ -43,6 +43,177 @@ allowing queries to be more concise, and simpler to write.
This also allows the
written into a defined column of the table is consistent with that columns
definition, minimizing errors where unexpected
data is written into a particular column of the table.
+### SQL DDL
+
+Tables can be defined with SQL instead of by posting a table specification.
`CREATE TABLE` and `ALTER TABLE` are
+submitted to the Broker like any other SQL statement, and write the same
catalog metadata the REST API does. They
+return no rows.
+
+These statements change catalog metadata only. They never create, modify, or
delete segments: defining a table does
+not ingest anything, and altering a column does not rewrite existing data.
Column changes take effect for subsequent
+ingestion.
+
+These statements are disabled by default. Set
`druid.sql.planner.enableCatalogDdl` to `true` on the Broker to enable
+them. They require `WRITE` permission on the datasource, the same permission
the catalog API requires, so enabling
+them lets anyone who can ingest into a datasource also change its catalog
definition; leave them disabled if you
+manage catalog entries with your own tooling. The setting cannot be overridden
per query.
+
+The `druid-catalog` extension must be loaded on both the Broker and the
Coordinator; without it, these statements
+report that the extension is not available.
+
+```sql
+CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table>
+ [ ( { <column> <type> | PROJECTION <name> AS ( <select> ) } [, ...] ) ]
+ [ PARTITIONED BY <granularity> ]
+ [ CLUSTERED BY <column> [, ...] ]
+ [ SEALED ]
+```
+
+`OR REPLACE` replaces the specification of an existing table; `IF NOT EXISTS`
leaves an existing table unchanged.
+The two cannot be combined. `PARTITIONED BY` sets
[`segmentGranularity`](#table-properties) and `CLUSTERED BY` sets
+`clusterKeys`, both of which a later `INSERT` or `REPLACE` inherits unless it
states its own. `SEALED` sets
+[`sealed`](#table-properties), which requires every ingested column to be
declared.
+
+Note that the table-level `CLUSTERED BY` is a sort order applied to each
ingestion, which is a different thing from
+the `CLUSTERED BY` inside a [`__base` projection](#the-base-table), which
defines how segments physically group rows.
+
+Column types are written as SQL types, such as `VARCHAR`, `BIGINT`, `DOUBLE`,
or `VARCHAR ARRAY`. The `__time` column
+is written as `TIMESTAMP`. Types that have no SQL spelling, such as complex
types, use `TYPE('...')` with the Druid
+native type string:
+
+```sql
+CREATE TABLE "druid"."visits" (
+ __time TIMESTAMP,
+ user_id VARCHAR,
+ pages_visited BIGINT,
+ sketch TYPE('COMPLEX<thetaSketch>')
+)
+PARTITIONED BY DAY
+CLUSTERED BY user_id
+```
+
+`ALTER TABLE` supports one change per statement, so that each statement is a
single atomic catalog operation:
+
+```sql
+ALTER TABLE <table> ADD COLUMN <column> <type>
+ALTER TABLE <table> DROP COLUMN <column>
+ALTER TABLE <table> ALTER COLUMN <column> SET DATA TYPE <type>
+ALTER TABLE <table> ADD [IF NOT EXISTS] PROJECTION <name> AS ( <select> )
+ALTER TABLE <table> DROP PROJECTION [IF EXISTS] <name>
+ALTER TABLE <table> SET PROPERTIES ( <property> = <value> [, ...] )
+```
+
+`ADD COLUMN` fails if the column already exists, and `ALTER COLUMN` fails if
it does not, so a misspelled column name
+is reported rather than quietly creating or replacing a column. Each statement
is also checked against the rest of the
+table definition, not only the part it changes: adding a column, changing a
type, or setting a property is rejected if
+the resulting table would be invalid, such as a segment granularity coarser
than a projection the table declares.
+
+#### Projections
+
+A table may declare [projections](../../querying/projections.md), which are
pre-aggregated views stored inside each
+segment. A projection is written as a `SELECT` over the table's own columns,
with no `FROM` clause:
+
+```sql
+CREATE TABLE "druid"."visits" (
+ __time TIMESTAMP,
+ user_id VARCHAR,
+ user_agent VARCHAR,
+ pages_visited BIGINT,
+ PROJECTION daily_by_agent AS (
+ SELECT TIME_FLOOR(__time, 'P1D'), user_agent, SUM(pages_visited) AS
total_pages
+ WHERE user_agent IS NOT NULL
+ GROUP BY 1, 2
+ )
+)
+PARTITIONED BY DAY
+```
+
+The body is planned exactly as the equivalent query would be, so a projection
matches the queries it was written to
+serve. Every aggregate needs an alias, which becomes the name of the stored
column. Time granularity is expressed
+with `TIME_FLOOR`, as it would be in a query.
+
+Because the body is planned like a query, it is planned under the statement's
own query context, including any `SET`
+clauses. Only context parameters that affect planning can change the stored
definition; parameters that only affect
+query execution have no effect, since the body is planned and stored rather
than run. The context itself is not part
+of the definition: what the catalog stores is the projection the body planned
to, so nothing from the statement's
+context is carried over to queries that later use it. Note also that a
projection is matched to a query by its shape,
+so a definition planned under a context that changes that shape only matches
queries run under the same context.
+
+A projection body accepts a select list, an optional `WHERE` and an optional
`GROUP BY`. It cannot use `ORDER BY`,
+`LIMIT` or `HAVING`: a projection's ordering follows its grouping columns and
is not something you choose. It also
+cannot use joins, subqueries, or expressions computed over aggregates. Store
the aggregates instead: `SUM(x)` and
+`COUNT(x)` rather than `AVG(x)`.
+
+Projections may also be added to and removed from an existing table:
+
+```sql
+ALTER TABLE "druid"."visits" ADD [IF NOT EXISTS] PROJECTION by_agent AS (
+ SELECT user_agent, SUM(pages_visited) AS total_pages GROUP BY user_agent
+)
+ALTER TABLE "druid"."visits" DROP PROJECTION [IF EXISTS] by_agent
+```
+
+Both take effect for subsequent ingestion. Segments already built keep
whatever projections they were built with, so
+dropping a projection does not rewrite data.
Review Comment:
But will it trigger compaction?
##########
docs/development/extensions-core/catalog.md:
##########
@@ -43,6 +43,177 @@ allowing queries to be more concise, and simpler to write.
This also allows the
written into a defined column of the table is consistent with that columns
definition, minimizing errors where unexpected
data is written into a particular column of the table.
+### SQL DDL
+
+Tables can be defined with SQL instead of by posting a table specification.
`CREATE TABLE` and `ALTER TABLE` are
+submitted to the Broker like any other SQL statement, and write the same
catalog metadata the REST API does. They
+return no rows.
+
+These statements change catalog metadata only. They never create, modify, or
delete segments: defining a table does
+not ingest anything, and altering a column does not rewrite existing data.
Column changes take effect for subsequent
+ingestion.
+
+These statements are disabled by default. Set
`druid.sql.planner.enableCatalogDdl` to `true` on the Broker to enable
+them. They require `WRITE` permission on the datasource, the same permission
the catalog API requires, so enabling
+them lets anyone who can ingest into a datasource also change its catalog
definition; leave them disabled if you
+manage catalog entries with your own tooling. The setting cannot be overridden
per query.
+
+The `druid-catalog` extension must be loaded on both the Broker and the
Coordinator; without it, these statements
+report that the extension is not available.
+
+```sql
+CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table>
+ [ ( { <column> <type> | PROJECTION <name> AS ( <select> ) } [, ...] ) ]
+ [ PARTITIONED BY <granularity> ]
+ [ CLUSTERED BY <column> [, ...] ]
+ [ SEALED ]
+```
+
+`OR REPLACE` replaces the specification of an existing table; `IF NOT EXISTS`
leaves an existing table unchanged.
+The two cannot be combined. `PARTITIONED BY` sets
[`segmentGranularity`](#table-properties) and `CLUSTERED BY` sets
+`clusterKeys`, both of which a later `INSERT` or `REPLACE` inherits unless it
states its own. `SEALED` sets
+[`sealed`](#table-properties), which requires every ingested column to be
declared.
+
+Note that the table-level `CLUSTERED BY` is a sort order applied to each
ingestion, which is a different thing from
+the `CLUSTERED BY` inside a [`__base` projection](#the-base-table), which
defines how segments physically group rows.
+
+Column types are written as SQL types, such as `VARCHAR`, `BIGINT`, `DOUBLE`,
or `VARCHAR ARRAY`. The `__time` column
+is written as `TIMESTAMP`. Types that have no SQL spelling, such as complex
types, use `TYPE('...')` with the Druid
+native type string:
+
+```sql
+CREATE TABLE "druid"."visits" (
+ __time TIMESTAMP,
+ user_id VARCHAR,
+ pages_visited BIGINT,
+ sketch TYPE('COMPLEX<thetaSketch>')
+)
+PARTITIONED BY DAY
+CLUSTERED BY user_id
+```
+
+`ALTER TABLE` supports one change per statement, so that each statement is a
single atomic catalog operation:
Review Comment:
There should be some commentary here about what happens after you do an
`ALTER TABLE`. I suppose it is:
- Future `SELECT` queries will cast types to the provided `DATA TYPE` if
different from what is physically stored.
- Future `INSERT` and `REPLACE` commands will pick up this new
configuration. (Do they validate against it or just use it as defaults? If you
do an `INSERT` of a `BIGINT` into a column declared as `VARCHAR` then does it
end up as `BIGINT` or `VARCHAR` or an error?)
- Compaction, if configured to use the catalog (how?) will rewrite segments
in some cases (which cases?) following an `ALTER TABLE`. (Or will it? What's
the current state of catalog-based compaction?)
- Catalog entries do not affect streaming ingestion such as Kafka (or do
they?), however they are still eligible for compaction, which will align the
physically-stored data to the specification in the catalog (is this right?).
As you can tell from the parenthetical questions I am not really sure what
happens today.
Maybe this is discussed elsewhere in the catalog docs. It probably should
be, since it applies to JSON manipulations too. If it is, this section should
link to the place where it is discussed.
##########
docs/development/extensions-core/catalog.md:
##########
@@ -43,6 +43,177 @@ allowing queries to be more concise, and simpler to write.
This also allows the
written into a defined column of the table is consistent with that columns
definition, minimizing errors where unexpected
data is written into a particular column of the table.
+### SQL DDL
+
+Tables can be defined with SQL instead of by posting a table specification.
`CREATE TABLE` and `ALTER TABLE` are
+submitted to the Broker like any other SQL statement, and write the same
catalog metadata the REST API does. They
+return no rows.
+
+These statements change catalog metadata only. They never create, modify, or
delete segments: defining a table does
+not ingest anything, and altering a column does not rewrite existing data.
Column changes take effect for subsequent
+ingestion.
+
+These statements are disabled by default. Set
`druid.sql.planner.enableCatalogDdl` to `true` on the Broker to enable
+them. They require `WRITE` permission on the datasource, the same permission
the catalog API requires, so enabling
+them lets anyone who can ingest into a datasource also change its catalog
definition; leave them disabled if you
+manage catalog entries with your own tooling. The setting cannot be overridden
per query.
+
+The `druid-catalog` extension must be loaded on both the Broker and the
Coordinator; without it, these statements
+report that the extension is not available.
+
+```sql
+CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table>
+ [ ( { <column> <type> | PROJECTION <name> AS ( <select> ) } [, ...] ) ]
+ [ PARTITIONED BY <granularity> ]
+ [ CLUSTERED BY <column> [, ...] ]
+ [ SEALED ]
+```
+
+`OR REPLACE` replaces the specification of an existing table; `IF NOT EXISTS`
leaves an existing table unchanged.
+The two cannot be combined. `PARTITIONED BY` sets
[`segmentGranularity`](#table-properties) and `CLUSTERED BY` sets
+`clusterKeys`, both of which a later `INSERT` or `REPLACE` inherits unless it
states its own. `SEALED` sets
+[`sealed`](#table-properties), which requires every ingested column to be
declared.
+
+Note that the table-level `CLUSTERED BY` is a sort order applied to each
ingestion, which is a different thing from
+the `CLUSTERED BY` inside a [`__base` projection](#the-base-table), which
defines how segments physically group rows.
+
+Column types are written as SQL types, such as `VARCHAR`, `BIGINT`, `DOUBLE`,
or `VARCHAR ARRAY`. The `__time` column
+is written as `TIMESTAMP`. Types that have no SQL spelling, such as complex
types, use `TYPE('...')` with the Druid
+native type string:
+
+```sql
+CREATE TABLE "druid"."visits" (
+ __time TIMESTAMP,
+ user_id VARCHAR,
+ pages_visited BIGINT,
+ sketch TYPE('COMPLEX<thetaSketch>')
+)
+PARTITIONED BY DAY
+CLUSTERED BY user_id
+```
+
+`ALTER TABLE` supports one change per statement, so that each statement is a
single atomic catalog operation:
+
+```sql
+ALTER TABLE <table> ADD COLUMN <column> <type>
+ALTER TABLE <table> DROP COLUMN <column>
+ALTER TABLE <table> ALTER COLUMN <column> SET DATA TYPE <type>
+ALTER TABLE <table> ADD [IF NOT EXISTS] PROJECTION <name> AS ( <select> )
+ALTER TABLE <table> DROP PROJECTION [IF EXISTS] <name>
+ALTER TABLE <table> SET PROPERTIES ( <property> = <value> [, ...] )
+```
+
+`ADD COLUMN` fails if the column already exists, and `ALTER COLUMN` fails if
it does not, so a misspelled column name
+is reported rather than quietly creating or replacing a column. Each statement
is also checked against the rest of the
+table definition, not only the part it changes: adding a column, changing a
type, or setting a property is rejected if
+the resulting table would be invalid, such as a segment granularity coarser
than a projection the table declares.
+
+#### Projections
+
+A table may declare [projections](../../querying/projections.md), which are
pre-aggregated views stored inside each
+segment. A projection is written as a `SELECT` over the table's own columns,
with no `FROM` clause:
+
+```sql
+CREATE TABLE "druid"."visits" (
+ __time TIMESTAMP,
+ user_id VARCHAR,
+ user_agent VARCHAR,
+ pages_visited BIGINT,
+ PROJECTION daily_by_agent AS (
+ SELECT TIME_FLOOR(__time, 'P1D'), user_agent, SUM(pages_visited) AS
total_pages
+ WHERE user_agent IS NOT NULL
+ GROUP BY 1, 2
+ )
+)
+PARTITIONED BY DAY
+```
+
+The body is planned exactly as the equivalent query would be, so a projection
matches the queries it was written to
+serve. Every aggregate needs an alias, which becomes the name of the stored
column. Time granularity is expressed
+with `TIME_FLOOR`, as it would be in a query.
+
+Because the body is planned like a query, it is planned under the statement's
own query context, including any `SET`
+clauses. Only context parameters that affect planning can change the stored
definition; parameters that only affect
+query execution have no effect, since the body is planned and stored rather
than run. The context itself is not part
+of the definition: what the catalog stores is the projection the body planned
to, so nothing from the statement's
+context is carried over to queries that later use it. Note also that a
projection is matched to a query by its shape,
+so a definition planned under a context that changes that shape only matches
queries run under the same context.
+
+A projection body accepts a select list, an optional `WHERE` and an optional
`GROUP BY`. It cannot use `ORDER BY`,
+`LIMIT` or `HAVING`: a projection's ordering follows its grouping columns and
is not something you choose. It also
Review Comment:
Hopefully these will be syntax errors with nice error messages. Do we have
tests asserting what the error messages are if you do various incorrect things?
##########
docs/development/extensions-core/catalog.md:
##########
@@ -43,6 +43,177 @@ allowing queries to be more concise, and simpler to write.
This also allows the
written into a defined column of the table is consistent with that columns
definition, minimizing errors where unexpected
data is written into a particular column of the table.
+### SQL DDL
+
+Tables can be defined with SQL instead of by posting a table specification.
`CREATE TABLE` and `ALTER TABLE` are
+submitted to the Broker like any other SQL statement, and write the same
catalog metadata the REST API does. They
+return no rows.
+
+These statements change catalog metadata only. They never create, modify, or
delete segments: defining a table does
+not ingest anything, and altering a column does not rewrite existing data.
Column changes take effect for subsequent
+ingestion.
+
+These statements are disabled by default. Set
`druid.sql.planner.enableCatalogDdl` to `true` on the Broker to enable
+them. They require `WRITE` permission on the datasource, the same permission
the catalog API requires, so enabling
+them lets anyone who can ingest into a datasource also change its catalog
definition; leave them disabled if you
+manage catalog entries with your own tooling. The setting cannot be overridden
per query.
+
+The `druid-catalog` extension must be loaded on both the Broker and the
Coordinator; without it, these statements
+report that the extension is not available.
+
+```sql
+CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table>
+ [ ( { <column> <type> | PROJECTION <name> AS ( <select> ) } [, ...] ) ]
+ [ PARTITIONED BY <granularity> ]
+ [ CLUSTERED BY <column> [, ...] ]
+ [ SEALED ]
+```
+
+`OR REPLACE` replaces the specification of an existing table; `IF NOT EXISTS`
leaves an existing table unchanged.
+The two cannot be combined. `PARTITIONED BY` sets
[`segmentGranularity`](#table-properties) and `CLUSTERED BY` sets
+`clusterKeys`, both of which a later `INSERT` or `REPLACE` inherits unless it
states its own. `SEALED` sets
+[`sealed`](#table-properties), which requires every ingested column to be
declared.
+
+Note that the table-level `CLUSTERED BY` is a sort order applied to each
ingestion, which is a different thing from
+the `CLUSTERED BY` inside a [`__base` projection](#the-base-table), which
defines how segments physically group rows.
+
+Column types are written as SQL types, such as `VARCHAR`, `BIGINT`, `DOUBLE`,
or `VARCHAR ARRAY`. The `__time` column
+is written as `TIMESTAMP`. Types that have no SQL spelling, such as complex
types, use `TYPE('...')` with the Druid
+native type string:
+
+```sql
+CREATE TABLE "druid"."visits" (
+ __time TIMESTAMP,
+ user_id VARCHAR,
+ pages_visited BIGINT,
+ sketch TYPE('COMPLEX<thetaSketch>')
+)
+PARTITIONED BY DAY
+CLUSTERED BY user_id
+```
+
+`ALTER TABLE` supports one change per statement, so that each statement is a
single atomic catalog operation:
+
+```sql
+ALTER TABLE <table> ADD COLUMN <column> <type>
+ALTER TABLE <table> DROP COLUMN <column>
Review Comment:
Presumably, if unsealed, `DROP COLUMN` can leave a column still visible to
queries, just undefined in the catalog. Seems strange. Or does it somehow hide
the column? Should be documented what it does.
--
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]
