Help Center/ Data Warehouse Service / SQL Syntax Reference/ SQL Syntax Reference (9.1.0.x)/ DDL Syntax/ CREATE EXTERNAL SCHEMA
Updated on 2025-09-19 GMT+08:00
View PDF
Share

CREATE EXTERNAL SCHEMA

Function

Creates an EXTERNAL schema. This syntax is supported only in 8.3.0 and later versions.

You can create an external schema to access the LakeFormation,DLI, and Hive MetaStore tables. 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.

Precautions

  • 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.
  • CREATE EXTERNAL SCHEMA does not support subcommands for creating objects in the new schema.

Syntax

  • Create an external schema with a specified name. 
    1
2
3
4
5
6
7
8
    		 
    CREATE EXTERNAL SCHEMA schema_name 
    WITH SOURCE source_name
         DATABASE 'database_name'
         SERVER server_name
         [ CATALOG 'catalog_name' ]
         [ OPTIONS ( { option_name ' value ' } [, ...] ) ]
         [METAADDRESS 'address']
         [CONFIGURATION 'confpath'];

Parameter Description

Table 1 CREATE EXTERNAL SCHEMA parameters

Parameter

Description

Value Range

schema_name

Name of an external schema.

  • The name must be unique.
  • The name cannot start with pg_.

A string, which must comply with Identifier Naming Conventions.

SOURCE

Type of the external metadata storage engine.

Currently, only dli,lakeformation, and hive are supported.

DATABASE

Database corresponding to the external schema.

-

SERVER

Name of the external server associated with the external schema. External data can be accessed after the association.

Servers whose type is lf, obs,dli, or hdfs can be associated. For details, see CREATE SERVER.

CATALOG

Catalog to be accessed in LakeFormation. This parameter is mandatory only when server type is set to lf.

-

OPTIONS

Specifies the parameters for a foreign table are listed in the right column. This function is supported only by 8.3.0 and later versions.

dli_project_id: Specifies the project ID corresponding to DLI. You can obtain the project ID from the management console. This parameter is available only when the server type is DLI.

METAADDRESS

Hive Metastore communication interface. This parameter is supported only by 9.1.0 and later versions.

-

CONFIGURATION

Path for storing Hive Metastore configuration files. This parameter is supported only by 9.1.0 and later versions.

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

  • Read the LakeFormation table using the external schema.
    • Create lf_server. The corresponding foreign data wrapper is DFS_FDW.

      For details about how to create lf_server, see section "Managing LakeFormation Data Sources" in Data Warehouse Service (DWS) User Guide.

    • Create an external schema, set SOURCE to lakeformation, and set the DLI server associated with the table to lf_server. In the following command, DATABASE is the LakeFormation database, and CATALOG is the LakeFormation catalog to be accessed. Replace them with the actual ones you use. 
      1
2
3
4
5
      		 
      CREATE EXTERNAL SCHEMA ex_lf
    WITH SOURCE lakeformation
         DATABASE 'demo'
         SERVER lf_server
         CATALOG 'hive';
    • Role authorization
      • Query the current user.
      • SELECT current_user;
      • Create a role that matches the current role on the LakeFormation management plane and assign it the table access permissions.
    • Query data in the LakeFormation table using the external schema. test_lf indicates the LakeFormation table to be accessed. 
      1
2
3
4
5
      		 
      SELECT COUNT(*) FROM ex_dli.test_lf;
 count 
-------
    20
(1 row)
  • Read the Hive Metastore table using the external schema. Only 9.1.0 and later versions support this function.
    • Create obs/hdfs server, with DFS_FDW as the foreign data wrapper. 
       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
      		 
      CREATE SERVER hdfs_server 
FOREIGN DATA WRAPPER HDFS_FDW 
OPTIONS (
address '***.***.***.***:9000', 
type'HDFS');

CREATE SERVER obs_server 
FOREIGN DATA WRAPPER dfs_fdw 
OPTIONS ( 
    address 'obs.ap-southeast-1.myhuaweicloud.com' , 
    ACCESS_KEY 'access_key_value_to_be_replaced', 
    SECRET_ACCESS_KEY 'secret_access_key_value_to_be_replaced', 
    encrypt 'on',
    type 'obs' );
    • Create an external schema. Set SOURCE to hive and the server associated with the table to obs/hdfs server. DATABASE indicates the HMS database, METAADDRESS indicates the Hive Metastore communication interface, and CONFIGURATION indicates the location where Hive Metastore configuration files are saved. Adjust these parameters to meet your site's requirements. 
      1
2
3
4
5
6
      		 
      CREATE EXTERNAL SCHEMA ex_hms 
    WITH SOURCE source_type
    DATABASE 'db_name'
    SERVER srv_name
    METAADDRESS 'address'
    CONFIGURATION 'confpath';
    • Queries data in the HMS table using the external schema. test_hms indicates the HMS table to be accessed. Replace it as needed. 
      1
2
3
4
5
      		 
      SELECT COUNT(*) FROM ex_hms.test_hms;
 count 
-------
    20
(1 row)

Helpful Links

ALTER EXTERNAL SCHEMA

Parent Topic: DDL Syntax