COPY FROM

Applies to: ❌ Data 360 SQL ✅ Tableau Hyper API

Copy data from a file into a table.

1COPY <table_name> [ (<column_name> [, ...] ) ]
2  FROM <source_location>
3  [ WITH (<option> [, ...]) ]

where <option> can be one of:

1FORMAT => <format_name>
2<format_specific_option> => <value>

COPY FROM loads data from a source location into a Hyper table, appending the data to whatever is in the table already.

If a column list is specified, each field in the file is inserted, in order, into the specified column. Table columns not specified in the column list will receive their default values.

Depending on the source location, Hyper will read data, e.g., from a local file or directly from S3. More information on the available locations can be found in External Locations.

<table_name>

The name (optionally database- or schema-qualified) of an existing table.

<column_name>

An optional list of columns to be copied. If no column list is specified, all columns of the table will be copied.

<source_location>

Location to read the data from. See External Location documentation for more information.

FORMAT => format_name

Selects the data format to be read. This option can be omitted in case the format can be inferred from the file extension. In case of a list of sources, all of them need to share this extension. Supported formats are depicted in detail in External formats.

format_specific_option => value

A format-specific option. The available options for each respective format can be found in External formats.

Copy a local CSV file from the working directory of the Hyper server, having a custom delimiter. The schema of the CSV file is expected to be the same as the schema of the table products, which must already exist:

1COPY products FROM './products.csv' WITH ( FORMAT => 'csv', DELIMITER => '|' )

Same but reading from multiple CSV files:

1COPY products FROM ARRAY['./products1.csv', './products2.csv', './products3.csv']
2WITH ( FORMAT => 'csv', DELIMITER => '|' )

Copy from an Apache Parquet file stored on Amazon S3 using empty credentials and inferring the bucket region. The file format is inferred from the file extension:

1COPY products FROM s3_location(
2    's3://mybucket/mydirectory/products.parquet',
3    access_key_id => '',
4    secret_access_key => ''
5)

Same but with explicit Amazon S3 credentials and bucket region:

1COPY products
2FROM s3_location(
3    's3://mybucket/mydirectory/products.parquet',
4    access_key_id => 'ACCESSKEYID12EXAMPLE',
5    secret_access_key => 'sWfssWSmnME5X/36dsf3G/cbyDzErEXAMPLE123',
6    region => 'us-east-1'
7)

Files named in a COPY command are read directly by the server, not by the client application. Therefore, they must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable by the Hyper user (the user ID the server runs as), not the client.

COPY input and output is affected by date_style.

COPY stops operation at the first error.

Hyper also supports the PostgreSQL syntax of the COPY command, which is slightly different from the syntax depicted here. This is only supported for PostgreSQL compatibility. When writing SQL for Hyper, we recommend using the syntax documented here.

Issuing a COPY is equivalent to an INSERT command that reads from the external function. Furthermore, the external function can also be combined with CREATE TABLE AS to concisely create and fill a new Hyper table. For example, this will create and fill a products table:

1CREATE TABLE products AS (SELECT * FROM external('products.parquet'))

Also, note that Hyper can directly operate on external formats, and importing the data into Hyper tables is unnecessary for most use cases.

For a discussion of the pros and cons of the various alternatives, see Reading External Data in SQL.