CREATE EXTERNAL SCHEMA
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 Hive Metastore. 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
Parameter |
Description |
Value Range |
---|---|---|
schema_name |
Name of an external schema.
|
A string, which must comply with Identifier Naming Conventions. |
SOURCE |
Type of the external metadata storage engine. |
Currently, the type can only be dli, lakeformation, or hive. |
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, dli, obs, 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 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)
- Create lf_server. The corresponding foreign data wrapper is DFS_FDW.
- 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)
- Create obs/hdfs server, with DFS_FDW as the foreign data wrapper.
Helpful Links
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot