Updated on 2025-08-25 GMT+08:00

Creating and Managing Schemas

A schema is the logical organization of objects and data in a database. Schema management allows multiple users to use the same database without interfering with each other. Third-party applications can be added to corresponding schemas to avoid conflicts.

The same database object name can be used in different schemas in a database without causing conflicts. For example, both a_schema and b_schema can contain a table named mytable. Users with required permissions can access objects across multiple schemas in a database.

Creating a Schema

Run CREATE SCHEMA to create a schema.
1
CREATE SCHEMA myschema;

To create or access an object within a schema, the full object name comprises the schema name followed by the specific object name, separated by a period (.), for example, myschema.table.

Setting the Schema Search Path

The GUC parameter search_path specifies the schema search sequence. The parameter value is a series of schema names separated by commas (,). If no schema is specified during object creation, the object will be added to the first schema displayed in the search path. If there are objects with the same name in different schemas and no schema is specified for an object query, the object will be returned from the first schema containing the object in the search path.

  • Run the SHOW command to view the current search path.
    1
    SHOW SEARCH_PATH;
    

    The default value of search_path is default_db. DataArts Fabric SQL automatically creates a default schema for you.

  • Run the SET command to change the default schema of the current session. For example, if the search path is set to myschema1, myschema2, myschema1 is searched first.
    1
    SET SEARCH_PATH TO myschema1, myschema2;
    

Using a Schema

If you want to create or access an object in a specified schema, the object name must contain the schema name. To be specific, the name consists of a schema name and an object name, which are separated by a dot (.).

  • Create a table mytable in myschema. Create a table in schema_name.table_name format.
    1
    CREATE TABLE myschema.mytable(id int, name varchar(20)) STORE AS ORC;
    
  • Query all data in the table mytable in myschema.
    1
    2
    3
    4
    SELECT * FROM myschema.mytable;
     id | name
    ----+------
    (0 rows)
    

Viewing a Schema

  • Use the current_schema function to view the current schema.
    1
    2
    3
    4
    5
    SHOW current_schema;
     current_schema 
    ----------------
     
    (1 row)
    
  • To view the list of all schemas, run the following command:
    1
    SHOW SCHEMAS;
    
  • To view the details of a schema, run the following command:
    1
    DESCRIBE SCHEMA myschema;
    

Schema Permission Control

By default, a user can only access database objects in its own schema. To access objects in other schemas, the user must have the usage permission of the corresponding schema.

DataArts Fabric SQL currently does not support permission-related operations through SQL. You can perform permission-related operations on the LakeFormation console. For details, see LakeFormation Data Permissions.

Drop Schema

  • Run the DROP SCHEMA command to delete an empty schema (no database objects in the schema).
    1
    DROP SCHEMA IF EXISTS myschema;
    
  • By default, a schema must be empty before being deleted. To delete a schema and all its objects (such as tables, data, and functions), use the CASCADE keyword.
    1
    DROP SCHEMA myschema CASCADE;