Schema Manipulation
Overview on the available statements for defining and altering the schema in a BNF-like form.statement:
alterStatement
| createSchemaStatement
| createTableStatement
| createViewStatement
| createMaterializedViewStatement
| dropSchemaStatement
| dropTableStatement
| dropViewStatement
| truncateTableStatement
createSchemaStatement:
CREATE [ OR REPLACE ] SCHEMA [ IF NOT EXISTS ] name
createTableStatement:
CREATE TABLE [ IF NOT EXISTS ] [ schemaName . ] name
[ '(' tableElement [, tableElement ]* ')' ]
[ ON STORE store ]
[ PARTITION BY ( HASH | RANGE | LIST ) '(' columnName ')' [PARTITIONS numberPartitions | with (partitionName1, partitionName2 [, partitionNameN]* )] ]
createViewStatement:
CREATE VIEW [ OR REPLACE ] [ schemaName . ] name
[ '(' tableElement [, tableElement ]* ')' ]
[ AS query ]
createMaterializedViewStatement:
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] [ schemaName . ] name
[ '(' tableElement [, tableElement ]* ')' ]
[ AS query ]
[ <ON> <STORE> storeName ( <COMMA> storeName )*]
[ FRESHNESS (INTERVAL interval timeUnit | UPDATE interval | MANUAL ) ]
tableElement:
columnName type [ NOT ] NULL [ DEFAULT value ]
| PRIMARY KEY '(' columnName [, columnName ]* ')'
| UNIQUE '(' columnName [, columnName ]* ')'
dropSchemaStatement:
DROP SCHEMA [ IF EXISTS ] [ schemaName . ] name
dropTableStatement:
DROP TABLE [ IF EXISTS ] [ schemaName . ] name
dropViewStatement:
DROP [MATERIALIZED] VIEW [ IF EXISTS ] [ schemaName . ] name
truncateTableStatement:
TRUNCATE TABLE [ schemaName . ] name
alterStatement:
ALTER SCHEMA schemaName RENAME TO newSchemaName
| ALTER SCHEMA schemaName OWNER TO userName
| ALTER TABLE [ schemaName . ] tableName RENAME TO newTableName
| ALTER TABLE [ schemaName . ] tableName OWNER TO userName
| ALTER TABLE [ schemaName . ] tableName RENAME COLUMN columnName TO newColumnName
| ALTER TABLE [ schemaName . ] tableName DROP COLUMN columnName
| ALTER TABLE [ schemaName . ] tableName ADD COLUMN columnName type [ NULL | NOT NULL ] [DEFAULT defaultValue] [(BEFORE | AFTER) columnName]
| ALTER TABLE [ schemaName . ] tableName ADD COLUMN columnName physicalName AS name [DEFAULT defaultValue] [(BEFORE | AFTER) columnName]
| ALTER TABLE [ schemaName . ] tableName MODIFY COLUMN columnName SET NOT NULL
| ALTER TABLE [ schemaName . ] tableName MODIFY COLUMN columnName DROP NOT NULL
| ALTER TABLE [ schemaName . ] tableName MODIFY COLUMN columnName SET COLLATION collation
| ALTER TABLE [ schemaName . ] tableName MODIFY COLUMN columnName SET DEFAULT value
| ALTER TABLE [ schemaName . ] tableName MODIFY COLUMN columnName DROP DEFAULT
| ALTER TABLE [ schemaName . ] tableName MODIFY COLUMN columnName SET TYPE type
| ALTER TABLE [ schemaName . ] tableName MODIFY COLUMN columnName SET POSITION ( BEFORE | AFTER ) columnName
| ALTER TABLE [ schemaName . ] tableName ADD PRIMARY KEY ( columnName | '(' columnName [ , columnName ]* ')' )
| ALTER TABLE [ schemaName . ] tableName DROP PRIMARY KEY
| ALTER TABLE [ schemaName . ] tableName ADD CONSTRAINT constraintName UNIQUE ( columnName| '(' columnName [ , columnName ]* ')' )
| ALTER TABLE [ schemaName . ] tableName DROP CONSTRAINT constraintName
| ALTER TABLE [ schemaName . ] tableName ADD CONSTRAINT foreignKeyName FOREIGN KEY ( columnName | '(' columnName [ , columnName ]* ')' ) REFERENCES [ schemaName . ] tableName '(' columnName [ , columnName ]* ')' [ ON UPDATE ( NONE | RESTRICT ) ] [ ON DELETE ( NONE | RESTRICT ) ]
| ALTER TABLE [ schemaName . ] tableName DROP FOREIGN KEY foreignKeyName
| ALTER TABLE [ schemaName . ] tableName ADD [UNIQUE] INDEX indexName ON ( columnName | '(' columnName [ , columnName ]* ')' ) [ USING indexMethod ] [ ON STORE storeUniqueName ]
| ALTER TABLE [ schemaName . ] tableName DROP INDEX indexName
| ALTER TABLE [ schemaName . ] tableName ADD PLACEMENT [( columnName | '(' columnName [ , columnName ]* ')' )] ON STORE storeUniqueName [ WITH PARTITIONS '(' partitionName [ , partitionName ]* ')' ]
| ALTER TABLE [ schemaName . ] tableName MODIFY PLACEMENT ( ADD | DROP ) COLUMN columnName ON STORE storeUniqueName
| ALTER TABLE [ schemaName . ] tableName MODIFY PLACEMENT '(' columnName [ , columnName ]* ')' ON STORE storeUniqueName [ WITH PARTITIONS '(' partitionName [ , partitionName ]* ')' ]
| ALTER TABLE [ schemaName . ] tableName DROP PLACEMENT ON STORE storeUniqueName
| ALTER TABLE [ schemaName . ] tableName PARTITION BY ( HASH | RANGE | LIST) '(' columnName ')' [ PARTITIONS numPartitions | WITH '(' partitionName1, partitionName2 [, partitionNameN]* ')' ]
| ALTER TABLE [ schemaName . ] tableName MERGE PARTITIONS
| ALTER TABLE [ schemaName . ] tableName MODIFY PARTITIONS '(' partitionId [ , partitionId ]* ')' ON STORE storeUniqueName
| ALTER VIEW [ schemaName . ] viewName RENAME TO newViewName
| ALTER VIEW [ schemaName . ] viewName RENAME COLUMN columnName TO newColumnName
| ALTER MATERIALIZED VIEW [ schemaName . ] viewName RENAME TO newViewName
| ALTER MATERIALIZED VIEW [ schemaName . ] viewName RENAME COLUMN columnName TO newColumnName
| ALTER MATERIALIZED VIEW [ schemaName . ] viewName FRESHNESS MANUAL
| ALTER MATERIALIZED VIEW [ schemaName . ] viewName ADD [UNIQUE] INDEX indexName ON ( columnName | '(' columnName [ , columnName ]* ')' ) [ USING indexMethod ] [ ON STORE storeUniqueName ]
| ALTER MATERIALIZED VIEW [ schemaName . ] viewName DROP INDEX indexName