CREATE EXTERNAL SCHEMA

Description

Creates an EXTERNAL schema.

This syntax is used to create EXTERNAL SCHEMA to access the table created in Hive. You can use an external schema name as the prefix for access. If there is no schema name prefix, you can access the named objects in the current schema.

Only DWS 3.0 supports the CREATE EXTERNAL SCHEMA syntax.

Important Notes

A user who has the CREATE permission on the current database can create a foreign schema.
When creating a named object, do not use EXTERNAL SCHEMA as the prefix. Objects cannot be created in EXTERNAL SCHEMA. Currently, only EXTERNAL SCHEMA can be used to perform SELECT, INSERT, and INSERT OVERWRITE operations on tables created in Hive.
CREATE EXTERNAL SCHEMA does not support subcommands for creating objects in the new schema.

Syntax

Create an external schema with a specified name.

      
           CREATE EXTERNAL SCHEMA schema_name 
    WITH SOURCE source_type
    DATABASE 'db_name'
    SERVER srv_name
    METAADDRESS 'address'
    CONFIGURATION 'confpath';

Parameters

schema_name
Name of an external schema.

Value range: a string. It must comply with the naming convention.
- The name must be unique,
- and cannot start with pg_.
SOURCE
Type of the external metadata storage engine. Currently, source_type can only be Hive.
DATABASE
Hive database corresponding to the external schema.

There is a many-to-one mapping between external schemas and Hive databases.
SERVER
Value range: an existing foreign server.

You can associate an external schema with a foreign server to access external data.
METAADDRESS
Hivemetastore communication interface.
CONFIGURATION
Path for storing hivemetastore configuration files.

If objects in the schema on the current search path are with the same name, specify the schemas different objects are in. You can run the SHOW SEARCH_PATH command to check the schemas on the current search path.

Examples

Create an EXTERNAL SCHEMA ex1:

    
         CREATE EXTERNAL SCHEMA ex1
    WITH SOURCE hive
         DATABASE 'demo'
         SERVER hdfs_server
         METAADDRESS   '***.***.***.***:***'
         CONFIGURATION  '/MRS/config'