External data source examples

To create parquet external table, you must first create an external data source by using the CREATE EXTERNAL DATASOURCE command. You can also use the ALTER, SHOW, DROP, and other commands to work with external data sources.

Note: Parquet external tables are read-only. The INSERT, DROP, TRUNCATE, and other commands do not work with external data sources.

To create an external data source on AWS S3, run the following.

CREATE EXTERNAL DATASOURCE my_datasource
ON AWSS3 
USING (
   ACCESSKEYID "AKIAIOSFODNN7EXAMPLE"
   SECRETACCESSKEY "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
   BUCKET "example_aws_bucket"
   REGION "us-east-1"
);

Note: AWS S3 supports only the following authentication method:

access key id with secret access key.

To create data source on other S3-compatible provider, ENDPOINT option must be added.

CREATE EXTERNAL DATASOURCE my_datasource
ON AWSS3 
USING (
   ACCESSKEYID "347aa3a4b34344f8bc7c7cccdf85ecaabfe"
   SECRETACCESSKEY "gvurfb82712ad14W7a7915h763a6examplekey"
   BUCKET "example_ibm_cloud_bucket"
   REGION "us-south"
   ENDPOINT "s3.us-south.cloud-object-storage.appdomain.cloud"
);

Similarly, you can create external data source on Azure. Azure supports the following authentication methods:

Account key authentication:

CREATE EXTERNAL DATASOURCE my_datasource
ON AZUREBLOB 
USING (
   CONTAINER "example_container" 
   ACCOUNT "example_account"
   ACCOUNTKEY "abcdefghijklmnopqrstuvwxyz0123456789/+ABCDEabcdefghijklmnopqrstuvwxyz0123456789/+ABCDE=="
);

Note: The source is called AZUREBLOB, but it supports both BLOB and ADLSv2.

SAS token authentication:

CREATE EXTERNAL DATASOURCE my_datasource
ON AZUREBLOB 
USING (
   CONTAINER "example_container" 
   ACCOUNT "example_account"
   SASTOKEN "sp=r&st=2024-01-01T00:00:00Z&se=2024-01-02T00:00:00Z&spr=https&sv=2022-11-02&sr=c&sig=examplesignature"
);

Entra ID OAuth:

CREATE EXTERNAL DATASOURCE my_datasource
ON AZUREBLOB 
USING (
   CONTAINER "example_container"
   ACCOUNT "example_account"
   CLIENTID "a1b2c3d4-e5f6-a7b8-c9d0-e1f2a3b4c5d6"
   TENANTID "cc00bb11-aa22-dd33-ee44-ff55cc66bb77"
   CLIENTSECRET "Z616Z~0ZMry3aNgiExampleSecret"
);

Alternatively, you can create data source without credentials, if bucket or container allows anonymous connections.

For S3:

CREATE EXTERNAL DATASOURCE my_datasource
ON AWSS3 
USING (
   BUCKET "example_aws_bucket" 
   REGION "us-east-1"
);

For Azure:

CREATE EXTERNAL DATASOURCE my_datasource
ON AZUREBLOB 
USING (
   CONTAINER "example_container"
   ACCOUNT "example_account"
);

To alter the external datasource to modify columns, run the following.

ALTER EXTERNAL DATASOURCE my_datasource
ON AWSS3
USING (
   BUCKET "other_bucket"
);

To show the external data source column values, run the following.

SHOW EXTERNAL DATASOURCE my_datasource;

To show the external data source in a cloud connection string format, run the following.

SHOW EXTERNAL DATASOURCE my_datasource VERBOSE;

To drop an external data source, run the following.

DROP EXTERNAL DATASOURCE my_datasource;

Note: To DROP data source, you must first drop all the external tables that are using this data source.