> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

> Reference and syntax for the COPY TO command that exports or unloads data from Firebolt to Amazon S3.

# COPY TO

Exports the results of a `SELECT` query to Amazon S3 in your specified file format. You can specify the output file format, compression type, and other options to control how the data is written, such as file name conventions and whether to overwrite existing files. Use `COPY TO` for backing up query results or integrating Firebolt with other data processing tools.

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
COPY (<select_query>)
  TO <location_name>
  [ WITH ] ( <option> [, ...] )

COPY (<select_query>)
  TO '<url>'
  [ WITH ] ( <option> [, ...] )

<option>:
    [ CREDENTIALS = ( <credentials> ) ] 
    [ TYPE = CSV | TSV | JSON | PARQUET ]
    [ COMPRESSION = GZIP | SNAPPY | NONE ]
    [ INCLUDE_QUERY_ID_IN_FILE_NAME = TRUE | FALSE ]
    [ FILE_NAME_PREFIX = <string> ]
    [ SINGLE_FILE = TRUE | FALSE ]
    [ MAX_FILE_SIZE = <bytes> ]
    [ OVERWRITE_EXISTING_FILES = TRUE | FALSE ]
    [ HEADER = TRUE | FALSE ]
    [ <settings> ]


<credentials>: (    -- Only applicable when using URL destination
  AWS_ACCESS_KEY_ID = '<aws_access_key_id>',
  AWS_SECRET_ACCESS_KEY = '<aws_secret_access_key>'
  [, AWS_SESSION_TOKEN = '<aws_session_token>' ]
  |
  AWS_ROLE_ARN = '<aws_role_arn>'
  [, AWS_ROLE_EXTERNAL_ID = '<aws_role_external_id>' ]
)
```

<Note>
  The `WITH` keyword before the options list is optional. Additionally, you can use either `=` or a space to separate option names from their values. For example, both `TYPE = JSON` and `TYPE JSON` are valid.
</Note>

## Parameters

| Parameter                       | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| ------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `<select_query>`                | Any valid `SELECT` statement.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| `<location_name>`               | The name of a location object that contains the Amazon S3 URL and credentials. This is the recommended approach for specifying the destination. See [CREATE LOCATION](/reference-sql/commands/data-definition/create-location) for more information.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| `<URL>`                         | The path to an Amazon S3 or Google Cloud Storage URL where the query result file or files are saved. For example, `s3://my_bucket/my_folder` or `gs://my_bucket/my_folder`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| `CREDENTIALS`                   | The credentials for accessing the specified `<URL>`. For more information, see [CREDENTIALS](#credentials).                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| `TYPE`                          | Specifies the file type to save to Amazon S3. If omitted, `CSV` is the default.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| `COMPRESSION`                   | Specifies the compression algorithm to use for the output files. If omitted, defaults to GZIP. If `NONE` is specified, exported files are not compressed. For `TYPE=PARQUET`, `COMPRESSION` can be set to `NONE`, the default `GZIP`, or `SNAPPY`. Each output file is written in Parquet format, using the specified algorithm to compress each column according to the Parquet specification.  This kind of compression is internal to the Parquet file. For `TYPE=CSV`, `TSV`, or `JSON`, `COMPRESSION` can be set to `NONE` or the default `GZIP`. With `GZIP`, the file is first created in the specified format and then compressed into a GZIP file.                                                                                                                                                                                                                              |
| `INCLUDE_QUERY_ID_IN_FILE_NAME` | Specifies whether a query ID is included in the file name. Each time the statement runs, Firebolt generates a new query ID. If omitted, defaults to `TRUE`, and Firebolt saves file names using the pattern `<query_id>.[type].gz`, for example, `123ABCXY2.csv.gz`. This allows Firebolt to generate unique file names by default. If `FALSE`, and `FILE_NAME_PREFIX` is not specified, files are exported with a generic `output` file name, for example, `output.csv.gz`.                                                                                                                                                                                                                                                                                                                                                                                                             |
| `FILE_NAME_PREFIX`              | Specifies an optional string to use in the file name. If `FILE_NAME_PREFIX` is omitted and `INCLUDE_QUERY_ID_IN_FILE_NAME` is `TRUE`, the exported file name is in the pattern `<query_id><file_string>.<type>.gz`. If `FILE_NAME_PREFIX` is specified and `INCLUDE_QUERY_ID_IN_FILE_NAME` is set to `FALSE`, the specified string replaces `output` and the file is in the pattern `<file_string>.<type>.gz`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| `SINGLE_FILE`                   | Specifies whether the export should be a single file or multiple files. If omitted, the default is `FALSE`, and the export is split based on the `MAX_FILE_SIZE` value. Exported files are appended with `_<n>` incrementally to indicate the position in series, starting with `0`. For example, the first file in a series might be named `123ABCXY2_0.parquet.gz`. If `TRUE`, only a single file is written. If set to `TRUE` and the file exceeds `MAX_FILE_SIZE`, an error occurs.                                                                                                                                                                                                                                                                                                                                                                                                  |
| `MAX_FILE_SIZE`                 | Specifies the target file size in bytes. If omitted, the default value is 134,217,728 bytes (128 MiB). Firebolt starts a new file once the current one has passed this size, so files usually come out over it rather than under. With `SINGLE_FILE = TRUE` it is instead a hard limit, and the query fails if the single file exceeds it. `0` means unlimited, so Firebolt never rolls over to a new file and each writer produces a single file. Regardless of this setting, no single file written through a multipart upload can exceed 52,428,800,000 bytes (50,000 MiB), because such an upload accepts at most 10,000 parts and Firebolt uploads 5 MiB parts. That bounds every `s3://` destination, and a `gs://` destination that supplies an HMAC key. A `gs://` destination that uses the engine identity writes each file in one resumable session, which has no part limit. |
| `OVERWRITE_EXISTING_FILES`      | Specifies whether exported files should overwrite existing files of the same name in the specified Amazon S3 URL. If omitted, defaults to `FALSE`, and files of the same name are not overwritten.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| `HEADER`                        | Specifies whether CSV files should contain a header line containing the column names. If omitted, defaults to `TRUE`. Must be omitted for TSV, JSON, or Parquet files.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| `<settings>`                    | List of [query-specific settings](/reference-sql/system-settings#setting-via-with) overrides.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |

For `TYPE = PARQUET`, Firebolt ends a row group at whichever comes first: 1,000,000 rows, or 33,554,432 bytes (32 MiB) of data. The row count is a limit that no row group exceeds; the byte figure is a target that a row group can overshoot, and it counts the data before compression, so row groups in the exported file are smaller than 32 MiB. Larger row groups let a reader skip more of a file at once, which is why row group size affects how efficiently the exported files can be queried later. Firebolt only starts a new file at a row group boundary, so a file can exceed `MAX_FILE_SIZE` by up to the size of the last row group it wrote, and the final file from each writer contains only the remaining rows and can be much smaller.

## Credentials

Firebolt needs permissions to write query results to the specified Amazon S3 URL. You can specify IAM credentials using the AWS access keys, and the specified credentials must be associated with a user with permissions to write objects to the bucket.

### Credentials for Amazon S3

Specify access key credentials using the syntax shown below.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
  CREDENTIALS = ( AWS_ACCESS_KEY_ID = '<aws_access_key_id>', AWS_SECRET_ACCESS_KEY = '<aws_secret_access_key>' [, AWS_SESSION_TOKEN = '<aws_session_token>' ] | AWS_ROLE_ARN = '<aws_role_arn>' [, AWS_ROLE_EXTERNAL_ID = '<aws_role_external_id>' ] )
```

For Amazon S3 locations, you can use either access key-based authentication or role-based authentication:

| Parameter               | Description                                                |
| :---------------------- | :--------------------------------------------------------- |
| `AWS_ACCESS_KEY_ID`     | Your AWS access key ID                                     |
| `AWS_SECRET_ACCESS_KEY` | Your AWS secret access key                                 |
| `AWS_SESSION_TOKEN`     | Optional temporary session token for temporary credentials |
| `AWS_ROLE_ARN`          | The ARN of the IAM role to assume                          |
| `AWS_ROLE_EXTERNAL_ID`  | Optional external ID for role assumption                   |

<Note>
  For role-based AWS access you can additionally set an external ID. An external ID is a value you choose and control that AWS checks when Firebolt assumes your role, adding a second condition on top of your account's unique IAM principal. Configuring one is a recommended best practice. See [IAM roles](/security#iam-roles).
</Note>

For more information on how to create access keys, see [Creating Access Key and Secret ID](/guides/loading-data/creating-access-keys-aws).

### Credentials for Google Cloud Storage

A `gs://` destination supports two credential modes:

| Mode            | How to specify it                                                                                   | How Firebolt authenticates                                                                                                                                                                                                                                                                  | Principal to grant the permissions below to                                                                              |
| :-------------- | :-------------------------------------------------------------------------------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | :----------------------------------------------------------------------------------------------------------------------- |
| HMAC key        | `CREDENTIALS = ( AWS_ACCESS_KEY_ID = '<hmac_access_id>', AWS_SECRET_ACCESS_KEY = '<hmac_secret>' )` | The Google Cloud Storage HMAC key goes in the AWS-named fields. Firebolt writes through the S3-compatible XML API.                                                                                                                                                                          | The service account or user the key belongs to.                                                                          |
| Engine identity | Omit `CREDENTIALS`                                                                                  | Firebolt writes through the Google Cloud Storage gRPC API. Where the deployment sets `storage.gcp.intermediary_service_account_id`, the engine impersonates that service account for every external `gs://` write; where it sets none, the engine authenticates as its own Google identity. | The intermediary service account where the deployment sets one, and the engine's own Google identity where it sets none. |

Whether the engine-identity mode is available depends on the deployment. Where it sets `storage.gcp.intermediary_service_account_id`, that service account is the principal, so the mode is available whatever `storage.gcp.allow_engine_identity` says: that setting governs only the engine's own identity. Where it sets none, the engine authenticates as its own Google identity, which the deployment permits with `storage.gcp.allow_engine_identity = true`. The Firebolt Operator and the Helm chart enable it by default for the engines they deploy, so a self-managed engine on Kubernetes has it. A standalone engine has it once you enable it. On the Firebolt managed service the engine's Google identity belongs to Firebolt rather than to your account, so the mode is not available there. When the mode is not available, a `gs://` destination without `CREDENTIALS` fails with an error that asks for an HMAC key.

Both modes need `storage.objects.list`, because Firebolt lists the destination prefix to confirm the bucket is reachable before it exports anything, and `storage.objects.create`. With `OVERWRITE_EXISTING_FILES = FALSE` they also need `storage.objects.get`, because Firebolt reads each destination object's metadata to confirm that the object does not already exist. With `OVERWRITE_EXISTING_FILES = TRUE` they need `storage.objects.delete` instead, because Google Cloud Storage counts replacing an existing object as a delete. On top of that:

* **HMAC key.** An object that outgrows a single 5 MiB upload part is written through the XML API's multipart upload, which additionally needs `storage.multipartUploads.create`, `storage.multipartUploads.abort`, and `storage.multipartUploads.listParts`. The default `MAX_FILE_SIZE` is far above one part, so grant these unless every exported object is small enough for a single request.
* **Engine identity.** The gRPC API writes each object in one resumable session, whatever its size, so no multipart permissions apply.

`MAX_FILE_SIZE` applies to a `gs://` destination exactly as it does to `s3://`, and the 50,000 MiB ceiling described in its row above binds the HMAC-key mode only.

`AWS_SESSION_TOKEN` and `AWS_ROLE_ARN` are rejected for `gs://` destinations.

### Least privileged permissions

The example AWS IAM policy statement below demonstrates the minimum actions that must be allowed for Firebolt to write query files to an example Amazon S3 URL. A permissions policy that allows at least these actions for the `<s3_url>` that you specify in the `COPY TO` statement must be attached to the user or role specified in the `CREDENTIALS` clause.

```json theme={"theme":{"light":"css-variables","dark":"css-variables"}}
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:Get*",
                "s3:List*",
                "s3:PutObject",
                "s3:DeleteObject"
            ],
            "Resource": [
                "arn:aws:s3:::my_s3_bucket",
                "arn:aws:s3:::my_s3_bucket/*"
            ]
        }
    ]
}
```

## Using Location Objects

Location objects provide a secure and reusable way to specify Amazon S3 destinations for your data exports. This is the recommended approach as it:

* Centralizes credential management
* Eliminates the need to specify credentials in each COPY statement
* Provides better security through role-based access control
* Simplifies maintenance and updates

For a comprehensive guide to LOCATION objects, see [LOCATION objects](/security/guides/location). For complete syntax reference, see [CREATE LOCATION](/reference-sql/commands/data-definition/create-location).

### Example with Location Object

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
COPY (
  SELECT * FROM test_table
)
TO my_export_location
WITH (
  TYPE = JSON,
  COMPRESSION = NONE,
  SINGLE_FILE = TRUE,
  FILE_NAME_PREFIX = 'latest-export',
  MAX_FILE_SIZE = 5000000000,
  OVERWRITE_EXISTING_FILES = TRUE
);
```

The location object `my_export_location` contains the Amazon S3 URL and credentials, making the statement cleaner and more secure.

## Examples

* [COPY TO with location object](#copy-to-with-location-object)
* [COPY TO with defaults and role ARN](#copy-to-with-defaults-and-role-arn)
* [COPY TO with single file and AWS access keys](#copy-to-with-single-file-and-aws-access-keys)
* [COPY TO with custom file name prefix and ARN with external ID](#copy-to-with-custom-file-name-prefix-and-arn-with-external-id)
* [COPY TO with custom file name and overwrite set to TRUE](#copy-to-with-custom-file-name-and-overwrite-set-to-true)

### COPY TO with location object

This example demonstrates the recommended approach using a location object. The location object contains the Amazon S3 URL and credentials, making the statement more secure and maintainable.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
COPY (
  SELECT * FROM test_table
)
TO my_export_location
WITH (
  TYPE = JSON,
  COMPRESSION = NONE,
  SINGLE_FILE = TRUE,
  FILE_NAME_PREFIX = 'latest-export',
  MAX_FILE_SIZE = 5000000000,
  OVERWRITE_EXISTING_FILES = TRUE
);
```

Firebolt writes a single file as shown below.

```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
s3://my_bucket/exports/
  latest-export.json
```

### COPY TO with defaults and role ARN

The example below shows a `COPY TO` statement with minimal parameters that specifies an `AWS_ROLE_ARN` together with a recommended `AWS_ROLE_EXTERNAL_ID`. Because `TYPE` is omitted, the file or files will be written in CSV format, and because `COMPRESSION` is omitted, they are compressed using GZIP  (`*.csv.gz`).

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
COPY (
  SELECT * FROM test_table
)
TO 's3://my-bucket/path/to/data'
WITH (
  CREDENTIALS = (AWS_ROLE_ARN='arn:aws:iam::123456789012:role/my-firebolt-role', AWS_ROLE_EXTERNAL_ID='my-external-id')
);
```

`AWS_ROLE_EXTERNAL_ID` is optional; omit it to assume the role without an external ID.

Firebolt assigns a UUID formatted query ID, to the query at runtime. The compressed output is 500 MB, exceeding the default `MAX_FILE_SIZE` of 128 MiB, so Firebolt writes 4 files as shown below.

```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
s3://my_bucket/my_fb_queries/
  153831d6-c400-4014-aa58-7bdce3918019_0.csv.gz
  153831d6-c400-4014-aa58-7bdce3918019_1.csv.gz
  153831d6-c400-4014-aa58-7bdce3918019_2.csv.gz
  153831d6-c400-4014-aa58-7bdce3918019_3.csv.gz
```

### COPY TO with single file and AWS access keys

The example below exports a single, uncompressed JSON file with the same name as the query ID. If the file to be written exceeds the specified `MAX_FILE_SIZE` (5 GB), an error occurs. AWS access keys are specified for credentials.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
COPY (
  SELECT * FROM test_table
)
TO 's3://my-bucket/path/to/data'
WITH (
  TYPE=JSON,
  COMPRESSION=NONE,
  CREDENTIALS=(AWS_ACCESS_KEY_ID='AKIAIOSFODNN7EXAMPLE', AWS_SECRET_ACCESS_KEY='wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY'),
  INCLUDE_QUERY_ID_IN_FILE_NAME=TRUE,
  SINGLE_FILE=TRUE,
  MAX_FILE_SIZE=5000000000
);
```

Firebolt writes a single file as shown below.

```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
s3://my_bucket/my_fb_queries/
  16B90E96716236D0.json
```

### COPY TO with custom file name prefix and ARN with external ID

In the example below, because `FILE_NAME_PREFIX` parameter is specified, Firebolt adds a string to the query ID to form file names. The IAM role specified for `CREDENTIALS` also has an external ID configured in AWS, so the ID is specified.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
COPY (
  SELECT * FROM test_table
)
TO 's3://my-bucket/path/to/data'
WITH (
  TYPE=JSON,
  COMPRESSION=NONE,
  CREDENTIALS = (AWS_ROLE_ARN='arn:aws:iam::123456789012:role/my-firebolt-role', AWS_ROLE_EXTERNAL_ID='99291'),
  FILE_NAME_PREFIX='_query_result'
);
```

Firebolt assigns the query an id of `16B90E96716236D6` at runtime and writes files as shown below.

```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
s3://my_bucket/my_fb_queries/
  16B90E96716236D6_query_result_0.json
  16B90E96716236D6_query_result_1.json
  16B90E96716236D6_query_result_2.json
  16B90E96716236D6_query_result_3.json
  16B90E96716236D6_query_result_4.json
```

### COPY TO with custom file name and overwrite set to TRUE

In the example below, `INCLUDE_QUERY_ID_IN_FILE_NAME` is set to `FALSE` and a `FILE_NAME_PREFIX` is specified. In this case, Firebolt writes files using only the specified `FILE_NAME_PREFIX` as the file name. In addition, `SINGLE_FILE` and `OVERWRITE_EXISTING_FILES` are set to `TRUE` so that the Amazon S3 URL always contains a single file with the latest query results.

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
COPY (
  SELECT * FROM test_table
)
TO 's3://my_bucket/my_fb_query'
WITH (
  TYPE=JSON,
  COMPRESSION=NONE,
  CREDENTIALS=(AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/my-firebolt-role'),
  INCLUDE_QUERY_ID_IN_FILE_NAME=FALSE,
  SINGLE_FILE=TRUE,
  FILE_NAME_PREFIX='latest-fb-query-result',
  MAX_FILE_SIZE=5000000000,
  OVERWRITE_EXISTING_FILES=TRUE
);
```

Firebolt writes a single file as shown below.

```bash theme={"theme":{"light":"css-variables","dark":"css-variables"}}
s3://my_bucket/my_fb-query/
  latest-fb-query-result.json
```