What's New
Function Overview
Product Bulletin
- Product Notice
- Version Support Notes
Service Overview
- GaussDB(DWS) Infographics
- What Is GaussDB(DWS)?
- Data Warehouse Types
- Data Warehouse Flavors
- Advantages
- Application Scenarios
- Functions
- Concepts
- Related Services
- Security
- GaussDB(DWS) Permissions Management
- GaussDB(DWS) Access
- Restrictions
- Technical Support
- Service Quotas
- GaussDB(DWS) Technical Specifications
Billing
- GaussDB(DWS) Billing Overview
- Billing Modes
- Item
- Billing Examples
- Billing Mode Change
- Renewal
  - Overview
  - Manual Renewal
- Bills
- Arrears
- Stopping Billing
- Cost Management
- Billing FAQs
Getting Started
- Checkpoint Vehicle Analysis
- Supply Chain Requirement Analysis of a Company
- Operations Status Analysis of a Retail Department Store
- Creating a Time Series Table
- Best Practices of Hot and Cold Data Management
- Best Practices for Automatic Partition Management
- Creating a Cluster and Connecting to It
- Using CDM to Migrate MySQL Data to the GaussDB(DWS) Cluster
- Using DLI Flink Jobs to Write Kafka Data to GaussDB(DWS) in Real Time
- Basic SQL Operations
- Database Quick Start
- Getting Started with Common Practices
User Guide
- Using GaussDB(DWS)
- Preparations
- Creating a GaussDB(DWS) Cluster
- Connecting to a GaussDB(DWS) Cluster
- Creating a GaussDB(DWS) Database and User
- Migrating Service Data to a GaussDB(DWS) Cluster
  - Migrating Data to a GaussDB(DWS) Cluster Using GDS-Kafka
  - Data Source Management
- GaussDB(DWS) Cluster Data Security and Encryption
- GaussDB(DWS) Cluster Management
- GaussDB(DWS) Cluster O&M
Best Practices
- Import and Export
- Data Migration
- Data Analytics
- Decoupled Storage and Compute
  - DWS 3.0 Decoupled Storage and Compute Usage Suggestions and Performance Optimization
- Data Development
- Database Management
- Performance Tuning
  - Optimizing Table Structure Design to Enhance GaussDB(DWS) Query Performance
  - Analyzing SQL Statements That Are Being Executed to Handle GaussDB(DWS) Performance Issues
- Cluster Management
  - Binding Different Resource Pools to Two Types of Jobs to Balance Load for GaussDB(DWS)
  - Scaling Options for GaussDB(DWS) with a Coupled Storage-Compute Architecture
Data Migration and Synchronization
- Data Migration to GaussDB(DWS)
- Importing Data
- Full Database Migration
  - Using CDM to Migrate Data to GaussDB(DWS)
  - Using DSC to Migrate SQL Scripts
- Real-time Import
  - Using DRS to Import Data to GaussDB(DWS)
  - Real-time Data Import From Kafka GaussDB(DWS)
- Metadata Migration
  - Using gs_dump and gs_dumpall to Export Metadata
  - Using gs_restore to Import Data
- Exporting Data
- Other Operations
Developer Guide
- Standard data warehouse (9.1.0.x)
- Standard data warehouse (8.1.3.x)
- Hybrid Data Warehouse (9.1.0.x）
- Hybrid Data Warehouse (8.1.3.x）
- Historical Versions
SQL Syntax Reference
- SQL Syntax Reference (9.1.0.x)
- SQL Syntax Reference (8.1.3.x)
- Historical Versions
Performance Tuning
- Overview of Query Performance Optimization
- Query Execution Process
- SQL Execution Plan
- SQL Optimization Guide
- Optimization Cases
- SQL Execution Troubleshooting
- query_band Load Identification
- Common Performance Parameter Optimization Design
Tool Guide
- Overview
- Downloading Related Tools
- gsql
- Data Studio
- GDS
- DSC
- DataCheck
- DWS-Connector
- Server Tool
API Reference
- Before You Start
- API Overview
- Calling APIs
- Getting Started
- API Description
- Application Cases
  - Using Postman to Call the API for Creating a Cluster
  - Using Postman to Call the API for Creating a Snapshot
- Introduction
- Appendix
SDK Reference
- SDK Overview
FAQs
- Top FAQs
- Product Consulting
- Database Connections
- Data Migration
- Database Usage
- Cluster Management
- Account Permissions
- Database Performance
- Backup and Restoration
  1. Why Does It Take a Long Time to Create an Automated Snapshot in GaussDB(DWS)?
  2. Does a GaussDB(DWS) Snapshot Have the Same Function as an EVS Snapshot?
Troubleshooting
- Database Connections
- JDBC/ODBC
- Data Import and Export
- Database Parameter Modification
- Account/Permission/Password
- Cluster Performance
- Cluster Exceptions
  - The Disk Usage Alarm Is Frequently Generated
- Database Use
Videos
Performance White Paper
- Overview
- Test Result
  - TPC-H Single Query Test
  - TPC-DS Single Query Test
- Test Methods
- Appendixes
  - TPC-H Test Sets
  - TPC-DS Test Sets
Technical White Paper
- GaussDB(DWS)
- Platforms and Technical Specifications Supported by GaussDB(DWS)
  - Technical Specifications
- GaussDB(DWS) Core Technologies
- GaussDB(DWS) Tools
  - Client Tools
  - Database Monitoring Tool
- External APIs
Error Code Reference
- 8.2.0 and earlier versions
  - Management Console Error Code
  - Data Warehouse Service Error Codes
- 8.2.1 or later versions
Glossary
More Documents
- User Guide
- API Reference (ME-Abu Dhabi Region)
- Developer Guide (ME-Abu Dhabi Region)
- SQL Syntax Reference (ME-Abu Dhabi Region)
- Tool Guide (ME-Abu Dhabi Region)
- Error Code Reference (ME-Abu Dhabi Region)
  - Management Console Error Code
- User Guide (Paris Region)
- API Reference (Paris Region)
- Developer Guide (Paris Region)
- SQL Syntax Reference (Paris Region)
- Tool Guide (Paris Region)
- Error Code Reference (Paris Region)
  - Management Console Error Code
- User Guide (Kuala Lumpur Region)
- API Reference (Kuala Lumpur Region)
- Developer Guide (Kuala Lumpur Region)
- SQL Syntax Reference (Kuala Lumpur Region)
- Tool Guide (Kuala Lumpur Region)
- Error Code Reference (Kuala Lumpur Region)
  - Management Console Error Code
General Reference
- Glossary
- Service Level Agreement
- White Papers
- Endpoints
- Permissions

On this page

Show all

Help Center/ GaussDB(DWS)/ SQL Syntax Reference/ SQL Syntax Reference (9.1.0.x)/ DDL Syntax/ CREATE EXTERNAL SCHEMA

CREATE EXTERNAL SCHEMA

Updated on 2025-01-13 GMT+08:00

View PDF

Function

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

The external schema is used to access tables of the LakeFormation, DLI, or HMS service. 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.

      
         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

schema_name
Name of an external schema.

Value range: a string. It must comply with the naming convention.
NOTICE:
- The name must be unique.
- The name cannot start with pg_.
SOURCE
Type of the external metadata storage engine. Currently, source_type can only be dli or lakeformation.
DATABASE
Database corresponding to the external schema.
SERVER
Value range: an existing foreign server whose type is lf or dli.

You can associate an external schema with a foreign server to access external data.
CATALOG
Catalog to be accessed in LakeFormation. This parameter is mandatory only when server type is set to lf.
OPTIONS
Specifies the following parameters for a foreign table: 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
Hivemetastore communication interface. This parameter is supported only by 9.1.0 and later versions.
CONFIGURATION
Path for storing hivemetastore configuration files. This parameter is supported only by 9.1.0 and later versions.
NOTE:

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.
NOTE:

For details about how to create lf_server, see section "Managing LakeFormation Data Sources" in 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.

        
           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.

        
           SELECT COUNT(*) FROM ex_dli.test_lf;
 count 
-------
    20
(1 row)

Read a DLI multi-version foreign table using external schema. This function is supported only in 8.3.0 or later.

NOTE:

DLI tables and DLI internal tables in Lakehouse mode can be accessed.

Create dli_server, with DFS_FDW as the foreign data wrapper.

       
          CREATE SERVER dli_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( 
  ADDRESS 'obs.ap-southeast-1.myhuaweicloud.com', 
  ACCESS_KEY 'xxxxxxxxx', 
  SECRET_ACCESS_KEY 'yyyyyyyyyyyyy', 
  TYPE 'DLI',
  DLI_ADDRESS 'dli.example.com',
  DLI_ACCESS_KEY 'xxxxxxxxx',
  DLI_SECRET_ACCESS_KEY 'yyyyyyyyyyyyy'
);

NOTE:

ADDRESS is the endpoint of OBS. DLI_ADDRESS is the endpoint of DLI. Replace it with the actual endpoint.
ACCESS_KEY and SECRET_ACCESS_KEY are access keys for the cloud account system to access OBS. Replace the values as needed.
DLI_ACCESS_KEY and DLI_SECRET_ACCESS_KEY are access keys for the cloud account system to access DLI. Replace the values as needed.
TYPE indicates the server type. Retain the value DLI.

Create an external schema, set SOURCE to dli, and set the DLI server associated with the table to dli_server. project_id is xxxxxxxxxxxxxxx, and database_name on DLI is database123. Replace them as needed.

       
          CREATE EXTERNAL SCHEMA ex_dli 
    WITH SOURCE dli
         DATABASE 'database123'
         SERVER dli_server 
         options (dli_project_id 'xxxxxxxxxxxxxxx');

Query data in the DLI multi-version table using the external schema. test_dli indicates the DLI table to be accessed. Replace it as needed.

       
          SELECT COUNT(*) FROM ex_dli.test_dli;
 count 
-------
    20
(1 row)

Reads the hivemetastore 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.

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

         

       
      

Creates 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 HiveMetaStore communication interface, and CONFIGURATION indicates the location where HiveMetaStore configuration files are saved. Adjust these parameters to meet your site's requirements.

       
          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.

       
          SELECT COUNT(*) FROM ex_hms.test_hms;
 count 
-------
    20
(1 row)

Helpful Links

ALTER EXTERNAL SCHEMA

Parent topic: DDL Syntax

Previous topic: CREATE EXCEPT RULE

Next topic: CREATE FOREIGN TABLE (for GDS Import and Export)

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot