ScopeDB LogoDOCUMENTATION

Data Definition Language (DDL) statements

DDL commands are used to create, manipulate, and modify objects in ScopeDB, such as databases, schemas, and tables.

CREATE DATABASE

Creates a new database in the system.

Syntax

CREATE DATABASE [ IF NOT EXISTS ] <name>

DROP DATABASE

Removes a database from the system.

Syntax

DROP DATABASE [ IF EXISTS ] <name>

CREATE SCHEMA

Creates a new schema in the system.

Syntax

CREATE SCHEMA [ IF NOT EXISTS ] <name>

DROP SCHEMA

Removes a schema from the system.

Syntax

DROP SCHEMA [ IF EXISTS ] <name>

CREATE TABLE

Creates a new table in the system.

Syntax

CREATE TABLE [ IF NOT EXISTS ] <table_name> (
    [
        <col_name> <col_type> [ COMMENT '<string_literal>' ]
    ],*
)
[ COMMENT = '<string_literal>' ]
WITH (
    ['string_literal' = <literal>],*
)

Examples

CREATE TABLE t1 (x int);

CREATE TABLE t2 (y int)
with (
    'storage.type' = 's3',
    'storage.endpoint' = 'http://127.0.0.1:9000/',
    'storage.bucket' = 'test-bucket',
    'storage.region' = 'us-east-1',
    'storage.access_key_id' = 'minioadmin',
    'storage.secret_access_key' = 'minioadmin'
);

DROP TABLE

Removes a table from the system.

Syntax

DROP TABLE [ IF EXISTS ] <name>

CREATE VIEW

Creates a new view in the system.

A view is a virtual table based on the result set of a query. Views can help simplify complex queries and provide an abstraction layer over the underlying tables.

Syntax

CREATE [ OR REPLACE ] VIEW <name>
AS <query>
[ COMMENT = '<string_literal>' ]

Parameters

[ OR REPLACE ]

Optional clause that allows you to replace an existing view if it already exists.

<name>

The name of the view to create.

COMMENT

Optional comment to describe the purpose of the view.

<query>

The SELECT query that defines the view's contents.

Examples

-- Create a table to store user events
CREATE TABLE user_events (
    user_id int,
    event_time timestamp,
    status string,
    score int
);

-- Insert sample data
VALUES
    (1, '2024-01-01T10:00:00Z', 'active', 85),
    (1, '2024-01-02T15:30:00Z', 'inactive', 75),
    (2, '2024-01-01T09:00:00Z', 'active', 90),
    (2, '2024-01-03T14:20:00Z', 'active', 95)
INSERT INTO user_events;

-- Create a view that keeps only the latest record for each user
CREATE VIEW latest_user_status AS
  FROM user_events
  DISTINCT ON user_id
  BY event_time DESC;

-- Create or replace a view with a comment
CREATE OR REPLACE VIEW highest_user_scores AS
  FROM user_events
  DISTINCT ON user_id
  BY score DESC
  COMMENT = 'Users highest achieved scores';

Usage Notes

  • Views are read-only and cannot be used as targets for INSERT, UPDATE, or DELETE operations
  • Views are useful for:
    • Simplifying repeated queries
    • Creating logical abstractions of the data
    • Storing commonly used DISTINCT ON operations

DROP VIEW

Removes a view from the system.

Syntax

DROP VIEW [ IF EXISTS ] <name>

Parameters

<name>

The name of the view to drop.

Examples

-- Drop a view
DROP VIEW latest_user_status;

-- Drop a view if it exists
DROP VIEW IF EXISTS highest_user_scores;

SHOW TABLES

Lists the tables for which you have access privileges.

Syntax

SHOW TABLES [ IN <schema_name> ]

Parameters

<schema_name>

Optional name of the schema to list tables from. If not specified, all tables in any schema are listed.

DESCRIBE TABLE

Describes the columns in a table.

Syntax

DESCRIBE TABLE <name>

CREATE INDEX

Creates a new index on a table to improve query performance.

Syntax

CREATE <index_type> INDEX [ IF NOT EXISTS ] [ <name> ] ON <table_name> (<index_expressions>)

Parameters

<index_type>

The type of index to create:

  • EQUALITY: An index that optimizes performance of equality comparisons on the indexed expressions
  • RANGE: An index that optimizes performance of range queries on the indexed expressions
  • SEARCH: An index that optimizes performance of text search operations using the search function
  • OBJECT: An index that optimizes performance of queries on object keys
  • MATERIALIZED: An index that stores the results of a query for faster access, useful for complex expressions or semi-structured data path access

<name>

The name of the index.

This is optional; if not provided, a default name will be generated based on the table and expression.

The index name must be unique within the table.

<table_name>

The name of the table on which to create the index.

<index_expressions>

A comma-separated list of expressions to index.

Examples

-- Create a table with different column types
CREATE TABLE logs (
    id int,
    time timestamp,
    message string,
    level string,
    var object,
);

-- EQUALITY index on a column
CREATE EQUALITY INDEX ON logs (id);

-- EQUALITY index on a timestamp expression
CREATE EQUALITY INDEX ON logs (trunc(time, unit => 'hour'));

-- RANGE index on a computed expression
CREATE RANGE INDEX ON logs (var['type']::string);

-- SEARCH index for text searching in log messages
CREATE SEARCH INDEX ON logs (message);

When to Use Each Index Type

  1. Use EQUALITY or RANGE indexes when:

    • You frequently filter on specific columns or expressions.
    • You need to optimize queries that use range conditions or equality comparisons.

  2. Use SEARCH indexes when:

    • You need to perform text search operations using the search function.
    • You want to optimize queries that look for specific text patterns in string columns.

  3. Use MATERIALIZED indexes when:

    • You have complex expressions or semi-structured data paths that are expensive to compute.
    • You want to pre-compute and store results for faster access.

Notes

  • Indexes improve query performance but require additional storage space.
  • Creating too many indexes can slow down write operations, and may increase query latency.
  • The query optimizer automatically determines when to use available indexes.

DROP INDEX

Removes an index from a table.

Syntax

DROP INDEX [ IF EXISTS ] <name> ON <table_name>

Parameters

<name>

The name of the index to drop.

<table_name>

The name of the table containing the index.

Examples

DROP INDEX idx_log_id ON logs;
DROP INDEX IF EXISTS idx_log_message ON logs;

CREATE NODEGROUP

Creates a new nodegroup.

Syntax

CREATE NODEGROUP <name>

DROP NODEGROUP

Removes a nodegroup.

Syntax

DROP NODEGROUP <name>

CREATE JOB

Creates a new job.

Syntax

CREATE [ OR REPLACE ] JOB <name>
SCHEDULE = '<cronexpr>'
NODEGROUP = '<comma-separated-nodegroups>'
AS
  <statement>

Examples

CREATE JOB archive_table_t
SCHEDULE = '4 2 * * * Asia/Shanghai'
NODEGROUP = 'background'
AS
  DELETE FROM t
  WHERE created_at < NOW() - INTERVAL 'PT720h';

DROP JOB

Removes a job.

Syntax

DROP JOB <name>

SHOW JOBS

Lists the jobs for which you have access privileges.

Syntax

SHOW JOBS

OPTIMIZE TABLE

Optimizing a table.

Execute compaction or purge historical data to save storage space and enhance query performance.

Syntax

OPTIMIZE TABLE <name>