clintropolis commented on code in PR #19830:
URL: https://github.com/apache/druid/pull/19830#discussion_r3936883817
##########
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:
there is test coverage for this, the error messages aren't super specific,
they are like parser's generic syntax errors instead of 'projections cannot use
limit' or whatever (we would have to allow them in the grammar and then reject
them later in the translator for that to work)
--
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]
