CREATE SCHEMA (HADOOP) statement

This statement creates the schema for Hadoop tables.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

An authorization ID that holds DBADM authority can create a schema with any valid schema-name or authorization-name. An authorization ID that does not hold DBADM authority can only create a schema with a schema-name or authorization-name that matches the authorization ID of the statement. If the statement includes a schema-SQL-statement, the privileges held by authorization-name (which, if not specified, defaults to the authorization ID of the statement) must include at least one of the following authorities:
  • The privileges required to run each schema-SQL-statement
  • DBADM authority

Syntax

Read syntax diagramSkip visual syntax diagram CREATE SCHEMA IF NOT EXISTS schema-nameAUTHORIZATIONauthorization-nameschema-nameAUTHORIZATIONauthorization-nameCOMMENT'schema-comment'LOCATION'hdfs-path'WITHDBPROPERTIESSCHEMAPROPERTIES(,property-name='property-value')

Description

schema-name
A schema name provides a name under which tables can be grouped. The name must not identify a schema that is already described in the catalog (SQLSTATE 42710). If the schema exists, the statement fails unless the IF NOT EXISTS clause is specified.
The name cannot begin with SYS (SQLSTATE 42939). The owner of the schema is the authorization ID that issued the statement.
IF NOT EXISTS
Use this clause if you are not sure whether the schema already exists. If the schema exists and IF NOT EXISTS is specified, no error message is returned.
AUTHORIZATION authorization-name
Identifies the user who is the owner of the schema. The value of authorization-name is also used to name the schema. The authorization-name must not identify a schema that is already described in the catalog (SQLSTATE 42710).
schema-name AUTHORIZATION authorization-name
Identifies a schema named schema-name whose owner is authorization-name. The schema-name must not identify a schema that is already described in the catalog (SQLSTATE 42710). The schema-name cannot begin with SYS (SQLSTATE 42939).
COMMENT 'schema-comment'
A schema comment can be used to provide information about the schema.
LOCATION 'hdfs-path'
The distributed file system (DFS) name specifies a directory under which the tables in the schema are created. External tables (not managed by Hive) that are created with no specified location will still be stored in the default warehouse location for the database, instead of the schema location. In this case, Hive determines the table placement.
SCHEMAPROPERTIES property-name='property-value'
Schema properties associate arbitrary name and value pairs with the schema name. They can be used for documentation purposes or for associating external metadata with the schema.

Usage notes

The owner of the schema is determined as follows:
  • If an AUTHORIZATION clause is specified, the specified authorization-name is the schema owner.
  • If an AUTHORIZATION clause is not specified, the authorization ID that issued the CREATE SCHEMA statement is the schema owner.

The schema owner is assumed to be a user (not a group).

When the schema is explicitly created with the CREATE SCHEMA statement, the schema owner is granted CREATEIN, DROPIN, and ALTERIN privileges on the schema with the ability to grant these privileges to other users.

The DBPROPERTIES keyword will be deprecated in a later release, but is currently supported for compatibility with Db2® Big SQL. In future releases, the SCHEMAPROPERTIES keyword is the only correct syntax.

Restrictions

When you run the CREATE SCHEMA statement from within a context other than a top-level statement, such as a routine, a compound block, or a prepared statement, do not use the COMMENT, LOCATION, or WITH clause.

Example

The following example creates a schema only if the named database does not already exist:
CREATE SCHEMA IF NOT EXISTS mysample;