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(`IBM`MSFT`GM`C`YHOO`GOOG), 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) //通过 database 函数创建的 db1
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.

The following SQL statements can be used when working with catalogs.

Use the following script to generate sample data using the above created catalog "trading":

create database stock partitioned by VALUE(1..6), engine='OLAP'
database(directory="dfs://db1", partitionType=RANGE, partitionScheme=0 5 10) 
createSchema("trading", "dfs://db1", "stock2") 

create table stock.quote (
     id INT,
     date DATE[comment="time_col", compress="delta"],
     value DOUBLE,
 )
partitioned by id
 
dbUrl = exec dbUrl from getSchemaByCatalog("trading") where schema = "stock"
id = 1 2 3 4 5 6 1 2 3 4 5 6
date = 2023.01.01..2023.01.12
value = 1..12
data = table( id, date, value)
loadTable(dbUrl[0], "quote").append!(data)
select * from stock.quote

Table trading.stock.quote:

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 = 2
select * from stock.quote where id = 2

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

delete from stock.quote where id = 3
select * from stock.quote where id = 3 // returns an empty table

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 an error will be raised.

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

Such variables can be dropped with undef.

undef(`stock)

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")

grant("user1@cluster2", TABLE_READ, "trading.stock.quote@cluster1")