Skip to main content

Loading from Bucket

Use COPY INTO to load files from an object storage bucket into Databend. Start here for SQL loading from Amazon S3. This tutorial uses a Parquet sample file. For other formats, see Input & Output File Formats.

Task flow: Prepare S3 data → Choose authentication → Connect and verify → Load the sample data.

note

This tutorial covers SQL loading. For scheduled loading through the Cloud console, see Amazon S3 Integration Task. For S3 event notifications, see Amazon SQS (S3) Integration Task.

Prepare Your S3 Data​

Prepare these items before you configure access:

  • An S3 bucket and its AWS Region. This tutorial uses databend-toronto in us-east-2. Replace these values with your own values.
  • AWS permissions for your chosen authentication method. IAM Role needs policies and roles. Access Keys needs a policy, an IAM user, and an access key. Ask your AWS administrator to perform these tasks if needed. Do not use AWS root user access keys.
  • Databend permissions to create a Connection, a Stage, and a table.

Download books.parquet. The file contains two records:

books.parquet
Transaction Processing,Jim Gray,1992
Readings in Database Systems,Michael Stonebraker,2004

Upload the file to load/books.parquet in your bucket. Keep the object private. You do not need to disable S3 Block Public Access.

For the AWS tasks, see Creating a bucket and Uploading objects.

Choose an Authentication Method​

Configure one authentication method. Both methods use the same Stage, verification queries, and loading SQL below.

MethodWhen to use itSetup and next task
IAM Role (recommended for Databend Cloud)Use temporary AWS credentials without storing access keys in Databend.Complete AWS IAM Role authorization. Then return to Create a Connection and Stage and select IAM Role.
Access KeysUse an IAM user's access key when role-based access is not suitable for your deployment.Complete Configure Access Keys. Then select Access Keys in Create a Connection and Stage.

A Connection stores reusable authentication parameters. A Stage specifies the S3 location. Creating a Connection is not a separate AWS authentication method.

Configure Access Keys​

Skip this task if you use IAM Role. If you already have a suitable IAM user and access key, continue with Create a Connection and Stage.

Create a read policy, IAM user, and access key

Create a Read-Only Permissions Policy​

In the AWS IAM console, open Policies → Create policy → JSON.

Paste the following policy into the editor. Replace databend-toronto and load/ with your bucket and prefix. Save the policy as databend-s3-user-read.

{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "GetBucketRegion",
"Effect": "Allow",
"Action": "s3:GetBucketLocation",
"Resource": "arn:aws:s3:::databend-toronto"
},
{
"Sid": "ListLoadPrefix",
"Effect": "Allow",
"Action": "s3:ListBucket",
"Resource": "arn:aws:s3:::databend-toronto",
"Condition": {
"StringLike": {
"s3:prefix": ["load/", "load/*"]
}
}
},
{
"Sid": "ReadLoadObjects",
"Effect": "Allow",
"Action": "s3:GetObject",
"Resource": "arn:aws:s3:::databend-toronto/load/*"
}
]
}

This policy allows listing and reading only the specified prefix. It does not permit uploads or deletes. For KMS-encrypted objects or write access, see Additional Permissions.

Create an IAM User​

In the IAM console, open Users → Create user. Use these settings:

SettingValue
User namedatabend-s3-reader
AWS Management Console accessDisabled
PermissionsAttach policies directly → databend-s3-user-read

Review the settings. Select Create user.

Create and Store an Access Key​

On the user's details page, open Security credentials → Access keys → Create access key.

Select Third-party service as the use case. Complete the wizard to create the key. Store the Access key ID and Secret access key in your approved secret manager.

warning

AWS shows the secret access key only when you create it. Do not put real keys in screenshots, source control, support tickets, or shared SQL examples. SQL clients and worksheets can retain statement text. Follow your organization's secret-handling rules when you enter credentials.

Create a Connection and Stage​

Open a Databend Cloud worksheet or connect with BendSQL. Select your authentication method below. Replace its example values with your own configuration.

Use your customer IAM role ARN and the matching external ID from AWS IAM Role authorization. Do not use the Databend Cloud principal ARN as ROLE_ARN.

CREATE CONNECTION s3_books_conn
STORAGE_TYPE = 's3'
ROLE_ARN = 'arn:aws:iam::987654321987:role/databend-docs-s3-role'
EXTERNAL_ID = 'my-external-id-123'
REGION = 'us-east-2';

Create one Stage for the authorized prefix. This SQL is the same for both authentication methods:

CREATE STAGE s3_books_stage
URL = 's3://databend-toronto/load/'
CONNECTION = (CONNECTION_NAME = 's3_books_conn');

Keep the trailing / in the Stage URL. The AWS policy enforces the access scope. The Connection name and Stage URL alone do not restrict the credentials to that path.

Verify S3 Access​

List the files in the Stage:

LIST @s3_books_stage;

The result should include books.parquet. Read the file to check object access:

SELECT title::VARCHAR AS title,
author::VARCHAR AS author,
date::VARCHAR AS date
FROM @s3_books_stage/books.parquet
LIMIT 2;

The result should contain the two sample records. Listing checks s3:ListBucket. Reading also checks s3:GetObject and any required KMS decryption access. Fix authentication or permissions errors before you load data.

For role assumption errors, see IAM Role Troubleshooting. For other errors, see Troubleshooting below.

Load the Sample Data​

Create a test table in a database where you have the required privileges:

CREATE TABLE books
(
title VARCHAR,
author VARCHAR,
date VARCHAR
);

Copy and Check the Rows​

Load only the sample file. Keep source file deletion disabled:

COPY INTO books
FROM @s3_books_stage
FILES = ('books.parquet')
FILE_FORMAT = (TYPE = 'PARQUET')
PURGE = FALSE;

Check the loaded data:

SELECT * FROM books ORDER BY title;

Expected records:

title |author |date|
----------------------------+-------------------+----+
Readings in Database Systems|Michael Stonebraker|2004|
Transaction Processing |Jim Gray |1992|

Troubleshooting​

SymptomWhat to check
AssumeRole fails or STS returns AccessDeniedCheck the role ARN, trusted Databend principal, and external ID. See IAM Role Troubleshooting.
InvalidAccessKeyId or SignatureDoesNotMatchCheck the access key ID and secret access key. Confirm that the key is active. Do not add whitespace when you copy the values.
LIST returns AccessDeniedCheck s3:ListBucket on the bucket ARN and the s3:prefix condition. Check for explicit denies in bucket, organization, or endpoint policies.
Listing succeeds, but reading failsCheck s3:GetObject on the object ARN. For KMS-encrypted objects, check kms:Decrypt and the KMS key policy.
No files appearCheck that the file is at load/books.parquet and the Stage URL ends in /load/.
An endpoint or Region error occursSet REGION to your bucket's AWS Region.
SQL reports insufficient privilegesCheck the current Databend role and its Connection, Stage, or table privileges. These privileges are separate from AWS permissions.

Rotate Credentials or Clean Up​

Stages usually copy Connection parameters at creation. Replacing a Connection does not update those stored parameters. When you change the role, external ID, or access keys, recreate the affected Stages with the new Connection parameters. Verify access before you revoke the old configuration. See CREATE CONNECTION.

To remove the Databend test objects, run:

DROP TABLE books;
DROP STAGE s3_books_stage;
DROP CONNECTION s3_books_conn;

These commands do not delete S3 objects, deactivate AWS keys, or revoke IAM role access. Remove test-only AWS resources separately. For role revocation, see Maintain or Remove Access.

TaskDocumentation
Configure AWS role permissions and trustAuthenticate with AWS IAM Role
Check Connection parameters and privilegesCREATE CONNECTION
Check Stage syntax and file format optionsCREATE STAGE
Check loading options and error handlingCOPY INTO