Table SQL Grammar

Table objects support the listed SQL grammar for DDL workloads.

Oracle AI Data Platform Workbench supports all standard Spark SQL data types. For more information, see Apache Spark Documentation - Supported Data Types.

Operation	Grammar
Create Table	CREATE [EXTERNAL] TABLE [IF NOT EXISTS] <catalog_name>.<schema-name>.<table-name> [ ( <column1-name><column1-type> [comment <column1-comment>], ... ) ] USING [HIVE|DELTA, CSV, TXT, ORC, JDBC, PARQUET, etc.] [ options ( <key1>=<val1>[, ...]) ] [ PARTITIONED BY (<par-column-name>[, ...]) ] [ CLUSTERED BY ( <clus-column-name>[, ...]) [ SORTED BY ( <sort-column-name> [ asc | desc ][, ...]) ] INTO <num_buckets> buckets] [ LOCATION '<path>'] [TBLPROPERTIES ( DESCRIPTION = 'some-description', '<property-name>'='<property-value>'[, ...]) ] Response: <<SQL Command>> was successfully executed Error: Error: <<SQL Command>> failed due to <<reason>>
Create Managed Table	Create Managed Table CREATE TABLE <catalog>.<schema>.<table-name> [ ( <column1-name><column1-type> [comment <column1-comment>], ... ) ] USING <format>; Response: <<SQL Command>> was successfully executed Error: Error: <<SQL Command>> failed due to <<reason>>
Create Managed Table with Data	create datatable <<catalog_name>>.<<schema_name>>.<<table_name>> [ ( <column1-name><column1-type> [comment <column1-comment>], ... ) ] tblproperties ('lakehouse_storage_format'='PARQUET') using parquet with select ( <column1-name>], ... ) from parquet.'oci://bucket@namespace/folder/' Response: <<SQL Command>> was successfully executed Error: Error: <<SQL Command>> failed due to <<reason>>
Create Table with Uniform Support	CREATE [EXTERNAL] TABLE [IF NOT EXISTS] <catalog_name>.<schema-name>.<table-name> [ ( <column1-name> <column1-type> [comment <column1-comment>], ... ) ] [TBLPROPERTIES ('delta.universalFormat.enabledFormats' = 'iceberg') ]
Alter Table	ALTER TABLE table_old_name RENAME TO table_new_name ALTER TABLE table_name ADD COLUMNS ( col_spec [ , ... ]) ALTER TABLE table_name DROP { COLUMN | COLUMNS } [ ( ] col_name [ , ... ] [ ) ] ALTER TABLE table_name RENAME COLUMN col_name TO col_name ALTER TABLE table_name ADD [IF NOT EXISTS] ( partition_spec [partition_spec ... ] ) ALTER TABLE table_name DROP [ IF EXISTS ] partition_spec [PURGE] ALTER TABLE table_name set tblproperties (description ='some-description')
Drop Table	DROP TABLE [ IF EXISTS ] table_name [ PURGE ] Response: <<SQL Command>> was successfully executed Error: Error: <<SQL Command>> failed due to <<reason>>
List Tables in a schema	SHOW TABLES in catalog_name.schema_name [ LIKE <regex_pattern> ] regex_pattern: A regular expression pattern that is used to filter the results of the statement. Response: <<namesake>>,tableName,isTemporary Error: <<SQL Command>> failed due to <<reason>>
Describe Table	DESCRIBE TABLE [ FORMAT ] catalog_name.schema_name.table_name [ PARTITION (<partition_col_name> = <partition_col_val>, ...) ] [ catalog_name.schema_name.table_name.column_name ] Format: If EXTENDED is specified as the format, additional metadata information (such as parent database, owner, and access time) is returned. DESCRIBE TABLE catalog.schema.table Response: col_name,data_type,comment DESCRIBE TABLE catalog.schema.table column Response: info_name,info_value