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
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';