— copy data from a file into a table
COPY <table_name> [ (<column_name> [, ...] ) ]
[ WITH (<option> [, ...]) ]
<option> can be one of:
FORMAT => <format_name>
<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.
- The name (optionally database- or schema-qualified) of an existing table.
- An optional list of columns to be copied. If no column list is specified, all columns of the table will be copied.
- 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
COPY products FROM './products.csv' WITH ( FORMAT => 'csv', DELIMITER => '|' )
Same but reading from multiple CSV files:
COPY products FROM ARRAY['./products1.csv', './products2.csv', './products3.csv']
WITH ( 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:
COPY products FROM s3_location(
access_key_id => '',
secret_access_key => ''
Same but with explicit Amazon S3 credentials and bucket region:
access_key_id => 'ACCESSKEYID12EXAMPLE',
secret_access_key => 'sWfssWSmnME5X/36dsf3G/cbyDzErEXAMPLE123',
region => 'us-east-1'
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
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.
COPY is equivalent to an INSERT command that
reads from 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:
CREATE 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.