SQL Grammar

Oracle AI Data Platform Workbench users can use SQL to automate their DDL workloads.

Topics:

Catalog SQL Grammar

Catalog 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.

Table 45-1 Standard and External Catalog SQL Grammar

Operation Grammar
Create Catalog

Catalog

CREATE CATALOG [ IF NOT EXISTS ] <<catalog_name>> [ PROPERTIES (DESCRIPTION = description ) ] OPTIONS ( { option_name = option_value } [ , ... ] )

External Catalog

CREATE EXTERNAL CATALOG [ IF NOT EXISTS ] <<catalog_name>> [PROPERTIES ( DESCRIPTION description ) ]OPTIONS ( { option_name = option_value } [ , ... ] )

OPTIONS will have connection details

External Catalog - Oracle Autonomous AI Lakehouse Example

wt = base64 encoded wallet contents
create_sql="create external catalog if not exists
catalog_adw options
 ('wallet.content' = '{wt}', 'type' = 'ORACLE_ADW',
'user.name' = 'ADMIN',
 'tns' = 'adw23ai_high', 'password' =
 'xxxxx','wallet.password' = 'xxxxx')"

Response

Catalog <<catalog_name>> created successfully

Error

<<SQL Command>> failed due to <<reason>>
Alter Catalog

Alter catalog name

ALTER CATALOG old_catalog_name RENAME new_catalog_name;

Alter catalog description 

ALTER CATALOG <catalog-name> set properties (DESCRIPTION=<property-value>)

Alter catalog options (conn) 

ALTER CATALOG <catalog-name> set options (option_name = option_value)

Response

Catalog <<catalog_name>> updated successfully

Error

<<SQL Command>> failed due to <<reason>>
Delete Catalog 
DROP CATALOG [ IF EXISTS ] catalog_name

By default during DROP catalog, all child objects will also get deleted

Response

Catalog <<catalog_name>> dropped successfully

Error 

<<SQL Command>> failed due to <<reason>>
List Catalogs 
SHOW CATALOGS [ [ LIKE ] [ regex_pattern ] [ TYPE = EXTERNAL CATALOG | CATALOG ]

regex_pattern: A regular expression pattern that is used to filter the results of the statement.

Response:
Catalog Type
<<catalog_name>> Catalog | External Catalog
<<catalog_name>> Catalog | External Catalog
<<catalog_name>> Catalog | External Catalog

Error 

<<SQL Command>> failed due to <<reason>>
Describe Catalog 
DESC CATALOG <<catalog_name>>
DESCRIBE CATALOG <<catalog_name>>
Response (Standard Catalog):
Attribute Value
Name Standard catalog name
Type Standard Catalog
Description Standard catalog description
Created by Principal that created the standard catalog
Created on Date and time created
Updated by Principal that last updated the standard catalog
Updated on Date and time last updated
Response (External catalog):
Attribute Value
Name External catalog name
Type External Catalog
Source type Source of external catalog (e.g. Oracle Autonomous AI Lakehouse)
Description External catalog description
Created by Principal that created the external catalog
Created on Date and time created
Updated by Principal that last updated the external catalog
Updated on Date and time last updated
Connection details Connection .json file

Error: 

<<SQL Command>> failed due to <<reason>>
Refresh Catalog 
REFRESH [EXTERNAL] CATALOG <<catalog_name>>
Response:
Refresh Initiated

Schema SQL Grammar

Schema 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.

Table 45-2 List of Schema SQL Grammar

Operation Grammar
Create Schema 
CREATE SCHEMA [IF NOT EXISTS] catalog_name.schema_name

Response 

<<SQL Command>> was successfully executed

Error

Error: <<SQL Command>> failed due to <<reason>>
Alter Schema
Alter Schema Description 
ALTER SCHEMA <schema-name> set dbproperties (DESCRIPTION=<property-value>)
Response 
<<SQL Command>> was successfully executed
Error 
Error: <<SQL Command>> failed due to <<reason>>
Delete Schema 
DROP SCHEMA [ IF EXISTS ] <<schema_name>>

By default during DROP schema, all child objects will also get deleted
List Schemas 
SHOW SCHEMAS [ { FROM | IN } catalog_name ] [ [ LIKE ] regex_pattern ]
Examples:
  • SHOW SCHEMAS FROM defaultcatalog1 LIKE 'd*'
  • SHOW SCHEMAS IN defaultcatalog1 LIKE 'd*'
Response:
  Schema
1 <<schema_1>>
2 <<schema_2>>
2 <<schema_3>>

Error

Error: <<SQL Command>> failed due to <<reason>>
Describe Schema (get details)

DESCRIBE SCHEMA <<catalog_name>>.<<schema_name>>

DESCRIBE SCHEMA <<schema_name>>

DESCRIBE SCHEMA <<schema_name>> in Catalog <<catalog_name>>

Attribute Value
Catalog name Catalog name
Schema Schema name
Description Schema description
Created by User that created the catalog
Created on Date and time created
Updated by User that last updated the catalog
Updated on Date and time last updated
Location Location in the catalog
Drop Schema drop schema [ IF EXISTS ] <<schema_name>> cascade
Refresh Schema 
REFRESH SCHEMA IN [EXTERNAL] CATALOG <<catalog_name.schema_name>>
Response:
Refresh Initiated

Volume SQL Grammar

Volume 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.

Table 45-3 Volume SQL Grammar

Operation Grammar
Create volume 
CREATE [ EXTERNAL ] VOLUME [IF NOT EXISTS ] <<catalog_name.schema_name.volume_name>> [ LOCATION location_path ] [ PROPERTIES (DESCRIPTION = description ) ]
Alter volume properties 
ALTER VOLUME <<volume_name>> { RENAME TO <<new_volume_name>> | [ set properties (DESCRIPTION = description) ] }
Drop volume 
DROP VOLUME [ IF EXISTS ] <<volume_name>>

OR

DROP VOLUME <<catalog_name>>.<<schema_name>>.<<volume_name>>

By default during DROP volume, all child objects will also get deleted
List Volumes 
SHOW VOLUMES [ { FROM | IN } catalog_name.schema_name ] [ [ LIKE ] regex_pattern } ]
Describe Volume 
DESCRIBE VOLUME volume_name
Attribute Value
Catalog name Catalog name
Schema name Schema name
Volume name Volume name
Description User defined description of volume
Location Location in catalog
Volume type Type of volume

Error: 

Error: <<SQL Command>> failed due to <<reason>>

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
Refresh Table 
REFRESH TABLE IN EXTERNAL CATALOG <<catalog_name.schema_name.table_name>>
Response:
Refresh Initiated

DML Queries

You can run select, insert and delete queries on data using Oracle AI Data Platform notebooks, SL, Python, and Spark scripts.

Oracle AI Data Platform Workbench currently supports Spark 3.5 with Delta Lake 3.2.0. For more information on DML queries, see: