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

# Export Data to a CSV file

## Overview

Exporting data from Oxla to a CSV file can be accomplished using the `COPY TO` command. This guide outlines the methods for exporting data, including specifying delimiters, handling headers and controlling null value representation.

## Syntax

The syntax for this function is as follows:

<pre><code class="sql">COPY table\_name (column\_name) TO `{'file_path' | STDOUT}` WITH (<i>option</i>, ...);</code></pre>

## Parameters

* `table_name`: existing table from which the data will be exported
* `column_name`: **optionally** list of columns to be exported. If omitted, all columns are exported
* `TO`: destination for the exported data
  * `file_path`: path to the CSV file where the data will be written
  * `STDOUT`: exports the data to the standard output stream
* `option`: available options below
  * **FORMAT**: output format (currently only CSV is supported)
  * **DELIMITER**:  delimiter character represented as single quote string literal (By default, this function uses commas as the delimiter)
  * **NULL**: string to use for representing NULL values (e.g. NULL 'unknown')
  * **HEADER**: boolean value indicating whether to include a header row with column names (values can be `TRUE` or `FALSE`, with default set to `FALSE`)
  * **Endpoint**: provide object-based storage credentials

## Examples

### Exporting Data via STDOUT

These example demonstrates how to export data from the file table to a CSV file named `film_export.csv`, including a header row and using a semicolon as the delimiter.

**1. Creating a table**

Ensure that the table exists in your Oxla instance. If the table does not exist, create one using the following command:

```sql theme={null}
CREATE TABLE film (
    title text NOT NULL,
    length int,
    rating text
    );
```

**2. Inserting Data**

```sql theme={null}
INSERT INTO film(title, length, rating) VALUES
    ('ATTRACTION NEWTON', 83, 'PG-13'),
    ('CHRISTMAS MOONSHINE', 150, 'NC-17'),
    ('DANGEROUS UPTOWN', 121, 'PG'),
    ('KILL BROTHERHOOD', 54, 'G'),
    ('HALLOWEEN NUTS', 47, 'PG-13'),
    ('HOURS RAGE', 122, 'NC-17'),
    ('PIANIST OUTFIELD', 136, 'NC-17'),
    ('PICKUP DRIVING', 77, 'G'),
    ('INDEPENDENCE HOTEL', 157, 'NC-17'),
    ('PRIVATE DROP', 106, 'PG'),
    ('SAINTS BRIDE', 125, 'G'),
    ('FOREVER CANDIDATE', 131, 'NC-17'),
    ('MILLION ACE', 142, 'PG-13'),
    ('SLEEPY JAPANESE', 137, 'PG'),
    ('WRATH MILE', 176, 'NC-17'),
    ('YOUTH KICK', 179, 'NC-17'),
    ('CLOCKWORK PARADISE', 143, 'PG-13');
```

**3. Executing the Export Command**

```sql theme={null}
COPY `table_name` TO 'file_path' WITH (FORMAT CSV, HEADER TRUE, DELIMITER ';'); 
```

<Note>
  * Replace `table_name` with your target table (e.g. film)
  * Replace `file_path` with the full path where the data will be written
  * Use `HEADER` to include a header row with column names
</Note>

**4. Verifying Export**

Now let's check the contents of `film_export.csv` to ensure the data has been successfully exported

```csv theme={null}
public.film.title;public.film.length;public.film.rating
"ATTRACTION NEWTON";83;PG-13
"CHRISTMAS MOONSHINE";150;NC-17
"DANGEROUS UPTOWN";121;PG
"KILL BROTHERHOOD";54;G
"HALLOWEEN NUTS";47;PG-13
"HOURS RAGE";122;NC-17
"PIANIST OUTFIELD";136;NC-17
"PICKUP DRIVING";77;G
"INDEPENDENCE HOTEL";157;NC-17
"PRIVATE DROP";106;PG
"SAINTS BRIDE";125;G
"FOREVER CANDIDATE";131;NC-17
"MILLION ACE";142;PG-13
"SLEEPY JAPANESE";137;PG
"WRATH MILE";176;NC-17
"YOUTH KICK";179;NC-17
"CLOCKWORK PARADISE";143;PG-13
```

### Exporting Data to Cloud Storage

To export data to an object storage from an Oxla table, you can use the `COPY TO` command with object storage credentials. This command allows you to transfer data to a cloud storage services like AWS S3, Google Cloud Storage or Azure Blob Storage directly from your Oxla instance.

```sql theme={null}
COPY table_name TO 'cloud_storage_file_path' (object_storage(object_storage_credentials));
```

* `object storage`: `AWS_CRED`,`AZURE_CRED` or `GCS_CRED` (depending on your provider)
* `object_storage_credentials`: for accessing your cloud storage

You need to provide **Provider-Specific credentials** to authenticate access to your files. Use the following authentication parameters to access your cloud storage

#### AWS S3 Bucket

* `aws_region`: AWS region associated with the storage service
* `key_id`: key identifier for authentication
* `access_key`: access key for authentication
* `endpoint_url`: URL endpoint for the storage service

```sql theme={null}
    COPY table_name TO 's3://your-bucket/file_name' WITH (AWS_CRED(AWS_REGION 'us-west-1', AWS_KEY_ID 'key_id', AWS_PRIVATE_KEY 'access_key', ENDPOINT 's3.us-west-1.amazonaws.com'), FORMAT CSV, HEADER ON, NULL 'unknown');
```

<Tip>In the exported file, `NULL` values will be represented as 'unknown' as specified in the `NULL` option</Tip>

#### Google Cloud Storage

* `<path_to_credentials>`: path to JSON credentials file
* `<json_credentials_string>`: contents of the GCS's credentials file

```sql theme={null}
COPY table_name TO 'gs://your-bucket/file_name' (GCS_CRED('/path/to/credentials.json'));
```

<Tip>For Google Cloud Storage, it's recommended to use HMAC keys for authentication. You can find more details about that on the [HMAC keys - Cloud Storage](https://cloud.google.com/storage/docs/authentication/hmackeys) page.</Tip>

#### Azure Blob Storage

* `tenant_id`: tenant identifier representing your organization's identity in Azure
* `client_id`: client identifier used for authentication
* `client_secret`: secret identifier acting as a password for authentication.

```sql theme={null}
COPY table_name TO 'wasbs://container-name/your_blob' (AZURE_CRED(TENANT_ID 'your_tenant_id' CLIENT_ID 'your_client_id', CLIENT_SECRET 'your_client_secret'));
```