# Exporting Data

## COPY TO

```
COPY ( <SELECT statement> ) TO '<file path>' [WITH (<property> = value, ...)];
```

`<file path>` must be a path on the server. This command exports the results of any SELECT statement to the file. There is a special mode when `<file path>` is empty. In that case, the server automatically generates a file in `<HEAVY.AI Directory>/export` that is the client session id with the suffix `.txt`.

Available properties in the optional WITH clause are described in the following table.

| Parameter             | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | Default Value                                                                                                                                                                                                 |
| --------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `array_null_handling` | <p>Define how to export with arrays that have null elements:</p><ul><li><code>'abort'</code> - Abort the export. Default.</li><li><code>'raw'</code> - Export null elements as raw values.</li><li><code>'zero'</code> - Export null elements as zero (or an empty string).</li><li><code>'nullfield'</code> - Set the entire array column field to null for that row.</li></ul><p>Applies only to GeoJSON and GeoJSONL files.</p>                                                                                                                                                                             | `'abort'`                                                                                                                                                                                                     |
| `delimiter`           | <p>A single-character string for the delimiter between column values; most commonly:</p><ul><li><code>,</code> for CSV files</li><li> for tab-delimited files</li></ul><p>Other delimiters include</p>                                                                                                                                                                                                                                                                                                                                                                                                         | <p>,<code>\~</code>, <code>^</code>, and<code>;</code>.</p><p>Applies to only CSV and tab-delimited files.</p><p><strong>Note:</strong> HEAVY.AI does not use file extensions to determine the delimiter.</p> |
| `escape`              | A single-character string for escaping quotes. Applies to only CSV and tab-delimited files.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | `'` (quote)                                                                                                                                                                                                   |
| `file_compression`    | <p>File compression; can be one of the following:</p><ul><li><code>'none'</code></li><li><code>'gzip'</code></li><li><code>'zip'</code></li></ul><p>For GeoJSON and GeoJSONL files, using GZip results in a compressed single file with a .gz extension. No other compression options are currently available.</p>                                                                                                                                                                                                                                                                                             | `'none'`                                                                                                                                                                                                      |
| `file_type`           | <p>Type of file to export; can be one of the following:</p><ul><li><code>'csv'</code> - Comma-separated values file.</li><li><code>'geojson'</code> - FeatureCollection GeoJSON file.</li><li><code>'geojsonl'</code> - Multiline GeoJSONL file.</li><li><code>'shapefile'</code> - Geospatial shapefile.</li></ul><p>For all file types except CSV, exactly one geo column (POINT, LINESTRING, POLYGON or MULTIPOLYGON) must be projected in the query. CSV exports can contain zero or any number of geo columns, exported as WKT strings.</p><p>Export of array columns to shapefiles is not supported.</p> | `'csv'`                                                                                                                                                                                                       |
| `header`              | Either `'true'` or `'false'`, indicating whether to output a header line for all the column names. Applies to only CSV and tab-delimited files.                                                                                                                                                                                                                                                                                                                                                                                                                                                                | `'true'`                                                                                                                                                                                                      |
| `layer_name`          | <p>A layer name for the geo layer in the file. If unspecified, the stem of the given filename is used, without path or extension.</p><p>Applies to all file types except CSV.</p>                                                                                                                                                                                                                                                                                                                                                                                                                              | Stem of the filename, if unspecified                                                                                                                                                                          |
| `line_delimiter`      | A single-character string for terminating each line. Applies to only CSV and tab-delimited files.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | `'\n'`                                                                                                                                                                                                        |
| `nulls`               | A string pattern indicating that a field is NULL. Applies to only CSV and tab-delimited files.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 | An empty string, `'NA'`, or                                                                                                                                                                                   |
| `quote`               | A single-character string for quoting a column value. Applies to only CSV and tab-delimited files.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | `"` (double quote)                                                                                                                                                                                            |
| `quoted`              | Either `'true'` or `'false'`, indicating whether all the column values should be output in quotes. Applies to only CSV and tab-delimited files.                                                                                                                                                                                                                                                                                                                                                                                                                                                                | `'true'`                                                                                                                                                                                                      |

<Info>
  When using the `COPY TO` command, you might encounter the following error:

  ```
  Query couldn’t keep the entire working set of columns in GPU Memory.
  ```

  To avoid this error, use the `heavysql` command `\cpu` to put your HEAVY.AI server in CPU mode before using the `COPY TO` command. See [Configuration](https://docs.omnisci.com/latest/4_configuration.html).
</Info>

### Example

```sql
COPY (SELECT * FROM tweets) TO '/tmp/tweets.csv';
COPY (SELECT * tweets ORDER BY tweet_time LIMIT 10000) TO
  '/tmp/tweets.tsv' WITH (delimiter = '\t', quoted = 'true', header = 'false');
```