CREATE EXTERNAL SCHEMA

Overview

The CREATE EXTERNAL SCHEMA statement maps an external data source (Hive Metastore or Databricks Unity Catalog) as a schema in Lakehouse, allowing users to query external data directly in Lakehouse without migrating the data.

Usage Restrictions

The following external data sources are currently supported:

• Hive on OSS (Alibaba Cloud Object Storage, read and write supported)
• Hive on COS (Tencent Cloud Object Storage, read and write supported)
• Hive on S3 (AWS Object Storage, read and write supported)
• Hive on HDFS (Preview, read-only, contact Lakehouse support)
• Databricks Unity Catalog

Write formats supported: Parquet, ORC, Text.

Syntax

CREATE EXTERNAL SCHEMA [ IF NOT EXISTS ] schema_name CONNECTION connection_name [ OPTIONS ( option_key = 'option_value' [, ...] ) ];

Parameters

Parameter	Required	Description
schema_name	Yes	Name of the external schema, must be unique within the current workspace
IF NOT EXISTS	No	If the schema already exists, skip without error
CONNECTION connection_name	Yes	Pre-created Catalog Connection name; see Create Catalog Connection
OPTIONS	No	Additional configuration options for specifying the database or catalog name in the external data source

Common OPTIONS

Option	Applicable Scenario	Description
SCHEMA	Hive	The Hive database name to map. Defaults to schema_name if not specified
catalog	Databricks	The Databricks Unity Catalog name
schema	Databricks	The Databricks Unity Catalog schema name

Creation Process

Hive External Schema (HMS)

Creating a Hive External Schema requires three steps: Create Storage Connection -> Create Catalog Connection -> Create External Schema.

Step 1: Create Storage Connection

CREATE STORAGE CONNECTION IF NOT EXISTS catalog_storage_oss TYPE OSS ACCESS_ID = 'LTAIxxxxxxxxxxxx' ACCESS_KEY = 'T8Gexxxxxxmtxxxxxx' ENDPOINT = 'oss-cn-hangzhou-internal.aliyuncs.com';

Step 2: Create Catalog Connection

Ensure that the HMS server network is connected with the Lakehouse network. See Creating Alibaba Cloud PrivateLink Endpoint Service.

CREATE CATALOG CONNECTION IF NOT EXISTS catalog_api_connection TYPE hms hive_metastore_uris = 'xxxx:9083' storage_connection = 'catalog_storage_oss';

Step 3: Create External Schema

CREATE EXTERNAL SCHEMA IF NOT EXISTS my_external_schema CONNECTION catalog_api_connection OPTIONS (SCHEMA = 'default');

Verify Connectivity

-- Verify metadata reading SHOW TABLES IN my_external_schema; -- Verify data reading SELECT * FROM my_external_schema.my_table LIMIT 10;

Databricks Unity Catalog External Schema

Step 1: Create Catalog Connection

See Create Databricks Catalog.

CREATE CATALOG CONNECTION IF NOT EXISTS conn_databricks TYPE databricks HOST = 'https://dbc-12345678-9abc.cloud.databricks.com' CLIENT_ID = 'client_id_value' CLIENT_SECRET = 'client_secret_value' ACCESS_REGION = 'us-west-2';

Step 2: Create External Schema

CREATE EXTERNAL SCHEMA IF NOT EXISTS external_db_sch CONNECTION conn_databricks OPTIONS ('catalog' = 'quick_start', 'schema' = 'default');

Verify Connectivity

SHOW TABLES IN external_db_sch;

Cloud Platform Storage Connection Parameters

Hive on COS (Tencent Cloud)

CREATE STORAGE CONNECTION catalog_storage_cos TYPE COS ACCESS_KEY = '<access_key>' SECRET_KEY = '<secret_key>' REGION = 'ap-shanghai' APP_ID = '1310000503';

• REGION: COS data center region. See Regions and Access Endpoints.
• For network connectivity, see Creating Tencent Cloud PrivateLink Endpoint Service.

Hive on S3 (AWS)

CREATE STORAGE CONNECTION catalog_storage_s3 TYPE S3 ACCESS_KEY = 'AKIAQNBSBP6EIJE33***' SECRET_KEY = '7kfheDrmq***' ENDPOINT = 's3.cn-north-1.amazonaws.com.cn' REGION = 'cn-north-1';

• ENDPOINT: Beijing region s3.cn-north-1.amazonaws.com.cn, Ningxia region s3.cn-northwest-1.amazonaws.com.cn.
• REGION: Beijing region cn-north-1, Ningxia region cn-northwest-1.

Hive on HDFS (Preview)

CREATE STORAGE CONNECTION hdfs_conn TYPE HDFS NAME_NODE = 'zetta-cluster' NAME_NODE_RPC_ADDRESSES = ['11.110.239.148:8020'];

• NAME_NODE: Corresponds to dfs.nameservices in HDFS configuration, i.e., the logical cluster name.
• NAME_NODE_RPC_ADDRESSES: Corresponds to dfs.namenode.rpc-address, format: [<host>:<port>].

Notes

• A corresponding Catalog Connection must be created before creating an External Schema.
• When the SCHEMA parameter in OPTIONS is not specified, Lakehouse defaults to mapping schema_name to the Hive database.
• Hive on HDFS is currently in Preview and only supports reads; contact Lakehouse support if needed.
• Data in an External Schema is stored in the external system; deleting an External Schema does not delete the underlying data.