Skip to main content

CREATE EXTERNAL SCHEMA

Creates a external schema in the current database.

Syntax​

When access control method is AWS Lake Formation:

CREATE EXTERNAL SCHEMA '<external_existing_schema_name>'
    CATALOG '<external_catalog_name>'
    USING DELTA
    WITH (
        "metastore.type" = '<external_metastore_type>',
        "glue.access-control.mode"="lake-formation",
        "glue.region"='<external_glue_catalog_region>',
        "s3.region" = '<storage_aws_region>'
    );

When access control method is Amazon IAM user AK/SK:

CREATE EXTERNAL SCHEMA '<external_existing_schema_name>'
    CATALOG '<external_catalog_name>'
    USING DELTA
    WITH (
        "metastore.type" = '<external_metastore_type>',
        --- When metastore type is AWS Glue
        "glue.access-key"= '<catalog_aws_access_key>',
        "glue.secret-key"= '<catalog_aws_secret_key>',
        "glue.region"='<external_glue_catalog_region>',
        "glue.endpoint"='<external_glue_endpoint>',
        --- When data storage is AWS S3
        "s3.access-key" = '<storage_aws_access_key>',
        "s3.secret-key" = '<storage_aws_secret_key>',
        "s3.endpoint" = '<storage_endpoint>',
        "s3.region" = '<storage_aws_region>'
    );

When access control method is Amazon IAM role:

CREATE EXTERNAL SCHEMA '<external_existing_schema_name>'
    CATALOG '<external_catalog_name>'
    USING DELTA
    WITH (
        "metastore.type" = '<external_metastore_type>',
      --- When metastore type is AWS Glue
        "glue.iam-role"= '<catalog_aws_iam_role_arn>',
        "glue.region"='<external_glue_catalog_region>',
        "glue.endpoint"='<external_glue_endpoint>',
       --- When data storage is AWS S3
        "s3.iam-role"= '<catalog_aws_iam_role_arn>',
        "s3.endpoint" = '<storage_endpoint>',
        "s3.region" = '<storage_aws_region>'
    );

Parameters​

  • <external_existing_schema_name>

    The name of the external schema. It must be consistent with the name of the target schema that exists in the external catalog.

  • <external_catalog_name>

    The name of the external catalog. Specify it as you like.

  • USING

    The USING clause specifies the data format, which is fixed to DELTA.

  • <external_metastore_type>

    The type of the metastore. Currently, only glue is supported.

  • <external_glue_catalog_region>

    The region where the metastore resides.

  • <external_glue_endpoint>

    The endpoint to connect to the metastore.

  • <storage_aws_region>

    The region where the S3 bucket resides.

  • <storage_endpoint>

    The endpoint to connect to S3 storage.

  • Other parameters

    When access control method is IAM user AK/SK:

    • <catalog_aws_access_key>

      The AWS access key to Glue.

    • <catalog_aws_secret_key>

      The AWS secret key to Glue.

    • <storage_aws_access_key>

      The AWS access key to S3.

    • <storage_aws_secret_key>

      The AWS secret key to S3.

    When access control method is IAM role:

    • <catalog_aws_iam_role_arn>

      The ARN of the AWS IAM role that has access permissions to Glue.

    • <catalog_aws_iam_role_arn>

      The ARN of the AWS IAM role that has access permissions to Glue.

Examples​

When access control method is AWS Lake Formation:

CREATE EXTERNAL SCHEMA "xxx-db" CATALOG "xxx_delta_test"
  USING DELTA
  WITH (
        "metastore.type" = "glue",
        "glue.access-control.mode"="lake-formation",
        "glue.region"="ap-east-1", 
        "s3.region"="ap-east-1"
);

When access control method is Amazon IAM user AK/SK:

CREATE EXTERNAL SCHEMA "xxx-db" CATALOG "xxx_delta_test"
  USING DELTA
  WITH (
        "metastore.type" = "glue",
        "glue.region"="ap-east-1", 
        "s3.region"="ap-east-1",
        "glue.endpoint-url"="glue.ap-east-1.amazonaws.com",
        "s3.endpoint"="s3.ap-east-1.amazonaws.com", 
        "glue.iam-role"="arn:aws:iam::{accountId}:role/MyLakehouseFederationRole",
        "s3.iam-role"="arn:aws:iam::{accountId}:role/MyLakehouseFederationRole"
);

When access control method is Amazon IAM role:

CREATE EXTERNAL SCHEMA "xxx-db" CATALOG "xxx_delta_test"
  USING DELTA
  WITH (
        "metastore.type"="glue",
        "glue.region"="ap-east-1",
        "glue.endpoint-url"="glue.ap-east-1.amazonaws.com",
        "glue.access-key"="XXXX",
        "glue.secret-key"="XXXX",
        "s3.access-key"="XXXX",
        "s3.secret-key"="XXXX",
        "s3.endpoint"="s3.ap-east-1.amazonaws.com",
        "s3.region"="ap-east-1"
    );