Catalog

The name of a catalog or schema can contain letters, digits and underscores and must begin with a letter. The name is case-insensitive when referenced in statements or functions.

(1) Creating catalogs

To create a catalog, use function createCatalog. For example, create a catalog "trading":

createCatalog("trading")

To switch to a specific catalog, execute use statement and specify the catalog keyword. For example, switch to catalog "trading":

use catalog trading;

Once a catalog is used, subsequent operations would use the catalog by default unless a different catalog is explicitly specified.

(2) Creating schemas

To create a schema, use create database statement and specify catalog.schema instead of directory.

Syntax

create database catalog.schema partitioned by partitionType(partitionScheme),[partitionType(partitionScheme),partitionType(partitionScheme)],
[engine='OLAP'], [atomic='TRANS'], [chunkGranularity='TABLE']

For example, create a schema "stock" using VALUE partitions and OLAP storage engine.

create database stock partitioned by VALUE(1..10), engine='OLAP'

Since section (1) already specifies a default catalog "trading", stock in the above script is equivalent to trading.stock. If no catalog is used, the creation would fail and an error of "The catalog doesn't exist" will be thrown.

To add an existing database to a catalog, use createSchema. For example, add schema "stock2" referencing database "dfs://db1" to the catalog "trading".

database(directory="dfs://db1", partitionType=RANGE, partitionScheme=0 5 10)
createSchema("trading", "dfs://db1", "stock2")

(3) Creating tables

To create a table in a schema, use create table statement and specify catalog.schema.tableName instead of dbPath.

Syntax

create table catalog.schema.tableName(
    schema[columnDescription]
)
[partitioned by partitionColumns],
[sortColumns],
[keepDuplicates=ALL],
[sortKeyMappingFunction]

For example:

create table stock.quote (
     id INT,
     date DATE[comment="time_col", compress="delta"],
     value DOUBLE,
 )
 partitioned by id

Since section (1) already specifies a default catalog "trading", stock.quote in the above script is equivalent to trading.stock.quote.

(4) Dropping catalogs

To drop a catalog, call function dropCatalog. For example:

dropCatalog("catalog1")

(5) Dropping schemas from catalogs

To drop a schema from a catalog, use drop statement.

Syntax

drop table [if exists] catalog.schema

where catalog.schema specifies the schema to be dropped. If a default catalog is already used, the catalog can be omitted.

(6) Dropping tables from schemas

To drop a table from a schema, use drop statement.

Syntax

drop table [if exists] catalog.schema.tableName

where catalog.schema.tableName specifies the table to be dropped. If a default catalog is already used, the catalog can be omitted.

dbUrl = exec dbUrl from getSchemaByCatalog("trading") where schema = "stock"
data = table(take(1..10, 100) as id, take(2023.01.01..2023.01.30, 100) as date, rand(10.0, 100) as value)
loadTable(dbUrl[0], "quote").append!(data)

The following SQL statements can be used when managing catalogs and schemas:

Example 1. Select data from "stock.quote" with filtering conditions:

select * from stock.quote where id = 1;

Example 2. Update data of "stock.quote":

update stock.quote set value = -1.0 where id > 5;
select * from stock.quote where id > 5;

Example 3. Delete data from "stock.quote":

delete from stock.quote where id < 2;
select * from stock.quote where id < 2;

Note: If a script contains a variable with the same name as schema, the query may fail to access the schema.

For example, if a variable stock is defined and shares the same name as the schema, stock in the script will be parsed as the variable name and anerror will be raised.

stock=1
select * from stock.quote where id = 1; 
//getMember method not supported

You may use undef to undefine such variable:

undef(`stock)

(1) Checking default catalog

To view the default catalog used in the current session, call getCurrentCatalog.

use catalog trading;
getCurrentCatalog() 
// output: "trading"

use catalog trading2;
getCurrentCatalog() 
// output: "trading2"

(2) Renaming catalogs/schemas

To rename a catalog, call function renameCatalog.

renameCatalog("trading", "trading2") 
getAllCatalogs() 
// output: ["trading2"]

To rename a schema, call function renameSchema.

renameSchema("trading", "stock", "stock1") 
exec schema from getSchemaByCatalog("trading") 
// output: ["stock1"]

(3) Checking schema info

To get info on schemas within a catalog, call function getSchemaByCatalog. A schema created with create database has a unique identifier of dbUrl in the format "dfs://<name_timestamp>" which is unaltered even after renamed.

getSchemaByCatalog("trading")

(4) Checking logs on catalog operations

To view info of operations on catalogs and schemas, you can check the ACL Audit log on the controller. The log message is displayed in the following format:

ACL Audit: function createSchema [catalog=trading,dbUrl=dfs://db1,schema=stock2], called by user [xxx]

The following access privileges with prefix "CATALOG_" can be set for databases/tables within a specific catalog.

The parameter objs of function grant/deny/revoke should be the catalog name. For example, grant user Adam CATALOG_READ access on catalog "trading".

grant("Adam", CATALOG_READ, "trading")

The following access privileges with prefix "SCHEMA_" can be set for schemas.

The parameter objs of function grant/deny/revoke should be in the format of catalog.schema. For example, grant user Adam SCHEMA_READ access on schema "trading.stock".

grant("Adam", SCHEMA_READ, "trading.stock")