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 22-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 - ADW 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. ADW)
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>>