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
orCREATE VIRTUAL SCHEMA
- For virtual schemas, you also need the
EXECUTE
privilege for the corresponding adapter script
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';