# COPY FROM `COPY FROM` copies data into a table using the COPY protocol. `COPY FROM` copies data into a table using the [Postgres `COPY` protocol][pg-copy-from]. ## Syntax **Copy from STDIN:** ```mzsql COPY [INTO] [ ( [, ...] ) ] FROM STDIN [[WITH] ( [=] [, ...] ] )] ; ``` | Syntax element | Description | | --- | --- | | `` | Name of an existing table to copy data into. | | `( [, ...] )` | If specified, correlate the inserted rows' columns to ``'s columns by ordinal position, i.e. the first column of the row to insert is correlated to the first named column. If not specified, all columns must have data provided, and will be referenced using their order in the table. With a partial column list, all unreferenced columns will receive their default value. | | `[WITH] ( [=] [, ...] )` | The following `` are supported for the `COPY FROM` operation: \| Name \| Description \| \|------\|---------------\| \| `FORMAT` \| Sets the input formatting method. Valid input formats are `TEXT` and `CSV`. For more information see [Text formatting](#text-formatting) and [CSV formatting](#csv-formatting).

Default: `TEXT`. \| `DELIMITER` \| A single-quoted one-byte character to use as the column delimiter. Must be different from `QUOTE`.

Default: A tab character in `TEXT` format, a comma in `CSV` format. \| `NULL` \| A single-quoted string that represents a _NULL_ value.

Default: `\N` (backslash-N) in text format, an unquoted empty string in CSV format. \| `QUOTE` \| _For `FORMAT CSV` only._ A single-quoted one-byte character that specifies the character to signal a quoted string, which may contain the `DELIMITER` value (without beginning new columns). To include the `QUOTE` character itself in column, wrap the column's value in the `QUOTE` character and prefix all instance of the value you want to literally interpret with the `ESCAPE` value. Must be different from `DELIMITER`.

Default: `"`. \| `ESCAPE` \| _For `FORMAT CSV` only._ A single-quoted string that specifies the character to allow instances of the `QUOTE` character to be parsed literally as part of a column's value.

Default: `QUOTE`'s value. \| `HEADER` \| _For `FORMAT CSV` only._ A boolean that specifies that the file contains a header line with the names of each column in the file. The first line is ignored on input.

Default: `false`. | **Copy from S3 and S3 compatible services:** ```mzsql COPY [INTO] [ ( [, ...] ) ] FROM [ | ] [[WITH] ( [=] [, ...] ] )] ; ``` | Syntax element | Description | | --- | --- | | `` | Name of an existing [table](/sql/create-table/) to copy data into. | | `( [, ...] )` | If specified, correlate the inserted rows' columns to ``'s columns by ordinal position, i.e. the first column of the row to insert is correlated to the first named column. If not specified, all columns must have data provided, and will be referenced using their order in the table. With a partial column list, all unreferenced columns will receive their default value. | | `` | The unique resource identifier (URI) of the Amazon S3 bucket (and prefix) to retrieve the file(s) to be copied from. If using an s3 URI, an AWS connection must be provided in the `WITH` clause. | | `` | The URL (for example, s3 presigned URL) to retrieve the file(s) to be copied from. | | `[WITH] ( [=] [, ...] )` | The following `` are supported for the `COPY FROM` operation: Name \| Value type \| Default value \| Description -----\|-----------------\|---------------\|------------ `FORMAT` \| `CSV`, `PARQUET` \| None, must be provided \| Sets the input formatting method. For more information see [formatting details below](#details). `DELIMITER` \| Single-quoted one-byte character \| Format-dependent \| Overrides the format's default column delimiter. _`FORMAT CSV` only_ `NULL` \| Single-quoted strings \| Format-dependent \| Specifies the string that represents a _NULL_ value. _`FORMAT CSV` only_ `QUOTE` \| Single-quoted one-byte character \| `"` \| Specifies the character to signal a quoted string, which may contain the `DELIMITER` value (without beginning new columns). To include the `QUOTE` character itself in column, wrap the column's value in the `QUOTE` character and prefix all instance of the value you want to literally interpret with the `ESCAPE` value. _`FORMAT CSV` only_ `ESCAPE` \| Single-quoted strings \| `QUOTE`'s value \| Specifies the character to allow instances of the `QUOTE` character to be parsed literally as part of a column's value. _`FORMAT CSV` only_ `HEADER` \| `boolean` \| `false` \| Specifies that the file contains a header line with the names of each column in the file. The first line is ignored on input. _`FORMAT CSV` only._ `AWS CONNECTION` \| _connection_name_ \| \| The name of the AWS connection to use in the `COPY FROM` command. If using an s3 URI, must be specified. For details on creating connections, check the [`CREATE CONNECTION`](/sql/create-connection/#aws) documentation page. _Only valid with S3._ `FILES` \| array \| \| A list of files to be appended to the URI. Example: `[ "top.csv", "files/a.csv", "files/b.csv" ]`. `PATTERN` \| string \| \| A glob used to identify files at at the URI. Example: `"files/**"`. Note that `DELIMITER` and `QUOTE` must use distinct values. | ## Details ### S3 Bucket IAM Policies To use `COPY FROM` with S3, you need to allow the following actions in your IAM policy: | Action type | Action name | Action description | | ----------- | ----------------------------------------------------------------------------------------- | ----------------------------------------------------------------- | | Read | [`s3:GetObject`](https://docs.aws.amazon.com/AmazonS3/latest/API/API_GetObject.html) | Grants permission to retrieve an object from a bucket. | | List | [`s3:ListBucket`](https://docs.aws.amazon.com/AmazonS3/latest/API/API_ListObjectsV2.html) | Grants permission to list some or all of the objects in a bucket. | > **Note:** For S3-compatible object storage services (e.g., Google Cloud Storage, Cloudflare R2, MinIO), > you need to enable equivalent permissions on the service you are using. The specific > configuration steps will vary by provider, but the access credentials must allow the same > read and list operations on the target bucket. ### Text formatting As described in the **Text Format** section of [PostgreSQL's documentation][pg-copy-from]. ### CSV formatting As described in the **CSV Format** section of [PostgreSQL's documentation][pg-copy-from] except that: - More than one layer of escaped quote characters returns the wrong result. - Quote characters must immediately follow a delimiter to be treated as expected. - Single-column rows containing quoted end-of-data markers (e.g. `"\."`) will be treated as end-of-data markers despite being quoted. In PostgreSQL, this data would be escaped and would not terminate the data processing. - Quoted null strings will be parsed as nulls, despite being quoted. In PostgreSQL, this data would be escaped. To ensure proper null handling, we recommend specifying a unique string for null values, and ensuring it is never quoted. - Unterminated quotes are allowed, i.e. they do not generate errors. In PostgreSQL, all open unescaped quotation punctuation must have a matching piece of unescaped quotation punctuation or it generates an error. ### PARQUET formatting Supported PARQUET compression formats - snappy - gzip - brotli - zstd - lz4 | [Arrow type](https://github.com/apache/arrow/blob/main/format/Schema.fbs) | [Parquet primitive type](https://parquet.apache.org/docs/file-format/types/) | [Parquet logical type](https://github.com/apache/parquet-format/blob/master/LogicalTypes.md) | Materialize type | | ------------------------------------------------------------------------- | ---------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------- | | `bool` | `BOOLEAN` | | [`boolean`](/sql/types/boolean/) | | `date32` | `INT32` | `DATE` | [`date`](/sql/types/date/) | | `decimal128[38, 10 or max-scale]` | `FIXED_LEN_BYTE_ARRAY` | `DECIMAL` | [`numeric`](/sql/types/numeric/) | | `fixed_size_binary(16)` | `FIXED_LEN_BYTE_ARRAY` | | [`bytea`](/sql/types/bytea/) | | `float32` | `FLOAT` | | [`real`](/sql/types/float/#real-info) | | `float64` | `DOUBLE` | | [`double precision`](/sql/types/float/#double-precision-info) | | `int16` | `INT32` | `INT(16, true)` | [`smallint`](/sql/types/integer/#smallint-info) | | `int32` | `INT32` | | [`integer`](/sql/types/integer/#integer-info) | | `int64` | `INT64` | | [`bigint`](/sql/types/integer/#bigint-info) | | `interval[year-month]` | `INT32` | `INTERVAL(YEAR_MONTH)` | [`interval`](/sql/types/interval/) | | `interval[day-time]` | `FIXED_LEN_BYTE_ARRAY(12)` | `INTERVAL` | [`interval`](/sql/types/interval/) | | `large_binary` | `BYTE_ARRAY` | | [`bytea`](/sql/types/bytea/) | | `large_utf8` | `BYTE_ARRAY` | | [`jsonb`](/sql/types/jsonb/) | | `list` | Nested | | [`list`](/sql/types/list/) | | `map` | Nested | `MAP` | [`map`](/sql/types/map/) | | `struct` | Nested | | [Arrays](/sql/types/array/) (`[]`) | | `time64[microsecond]` | `INT64` | `TIMESTAMP[isAdjustedToUTC = false, unit = MICROS]` | [`timestamp`](/sql/types/timestamp/#timestamp-info) | | `time64[microsecond]` | `INT64` | `TIMESTAMP[isAdjustedToUTC = true, unit = MICROS]` | [`timestamp with time zone`](/sql/types/timestamp/#timestamp-with-time-zone-info) | | `time64[nanosecond]` | `INT64` | `TIME[isAdjustedToUTC = false, unit = NANOS]` | [`time`](/sql/types/time/) | | `uint16` | `INT32` | `INT(16, false)` | [`uint2`](/sql/types/uint/#uint2-info) | | `uint32` | `INT32` | `INT(32, false)` | [`uint4`](/sql/types/uint/#uint4-info) | | `uint64` | `INT64` | `INT(64, false)` | [`uint8`](/sql/types/uint/#uint8-info) | | `utf8` or `large_utf8` | `BYTE_ARRAY` | `STRING` | [`text`](/sql/types/text/) | ### Limits You can copy up to 10 GiB of data at a time. If you need to copy more than that, please [contact support](/support/). When importing parquet files, entire row groups are held in memory at once, so ensure that your Materialize instance has enough available memory to accomodate your parquet files. If you are encountering memory issues, and are unable to reduce the sizes of your row groups, please [contact support](/support/). ## Examples ### From STDIN ```mzsql COPY t FROM STDIN WITH (DELIMITER '|'); ``` ```mzsql COPY t FROM STDIN (FORMAT CSV); ``` ```mzsql COPY t FROM STDIN (DELIMITER '|'); ``` ### From AWS S3 #### Using AWS connection Perform bulk import: Using `FILES` option: ```mzsql COPY INTO csv_table FROM 's3://example_bucket' (FORMAT CSV, AWS CONNECTION = example_aws_conn, FILES = ['example_data.csv']); ``` Using the full s3 URI: ```mzsql COPY INTO csv_table FROM 's3://example_bucket/example_data.csv' (FORMAT CSV, AWS CONNECTION = example_aws_conn); ``` Using `PATTERN` option: ```mzsql COPY INTO parquet_table FROM 's3://example_bucket' (FORMAT PARQUET, AWS CONNECTION = example_aws_conn, PATTERN = '*parquet*'); ``` #### Using S3-compatible object storage You can use `COPY FROM` with any S3-compatible object storage service, such as Google Cloud Storage, Cloudflare R2, or MinIO. First, [create an AWS connection for S3-compatible storage](/sql/create-connection/#s3-compatible-object-storage), then use it in the `COPY` command. Make sure your credentials have the necessary permissions as described in [S3 Bucket IAM Policies](#s3-bucket-iam-policies). ```mzsql COPY INTO csv_table FROM 's3://my_bucket/my_data.csv' (FORMAT CSV, AWS CONNECTION = gcs_connection); ``` #### Using presigned URL ```mzsql COPY INTO csv_table FROM '' (FORMAT CSV); ``` ## Privileges The privileges required to execute this statement are: - `USAGE` privileges on the schema containing the table. - `INSERT` privileges on the table. [pg-copy-from]: https://www.postgresql.org/docs/14/sql-copy.html