CREATE SCHEMA

Purpose

Use this statement to create a new schema. This schema can be a physical schema where you can create standard schema objects, or it can be a virtual schema with a virtual link to an external data source, mapping the remote tables including its data and metadata.

Prerequisites

  • System privilege CREATE SCHEMA or CREATE VIRTUAL SCHEMA
  • For virtual schemas, you also need the EXECUTE privilege for the corresponding adapter script

Syntax

create_schema::=

Create Schema Statement

create_virtual_schema::=

Create Virtual Schema Statement

Create Virtual Schema Statement

Usage Notes

  • A user can create any number of schemas.
  • The schema that has been created belongs to the current user, however, it can be assigned to another user or role with the ALTER SCHEMA statement.
  • The data volume of the objects within a schema can be limited using quotas as in the Schema Quota section.
  • When you create a new schema, you implicitly open this new schema. Which means that this new schema is set as the CURRENT_SCHEMA, and any further objects created are within this new schema.
  • If you have specified the option IF NOT EXISTS, then no error message is thrown if a schema with the same name already exists. Also the specified schema is opened even if it already exists.
  • For details about the virtual schema concept, refer to Virtual Schemas section.
  • The USING option in a virtual schema specifies the adapter UDF script which then defines the content of the virtual schema.
  • Using the WITH clause, you can specify certain properties that will be used by the adapter script.

Example

CREATE SCHEMA IF NOT EXISTS my_schema;

CREATE VIRTUAL SCHEMA hive
USING adapter.jdbc_adapter
WITH
 CONNECTION_STRING   = 'jdbc:hive2://localhost:10000/default'
 SCHEMA_NAME         = 'default'
 USERNAME            = 'hive-usr'
 PASSWORD            = 'hive-pwd';