> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# IMPORT
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
The statements on this page are **deprecated** as of v23.1 and will be removed in a future release. To move data into CockroachDB, use `IMPORT INTO` or `COPY FROM`.
The `IMPORT` statement imports the following types of data into CockroachDB:
* [PostgreSQL dump files][postgres]
* [MySQL dump files][mysql]
To import CSV, Avro, or delimited data files, see `IMPORT INTO`.
Certain `IMPORT TABLE` statements that defined the table schema inline are **not** supported in v22.1 and later versions. These include running `IMPORT TABLE ... CREATE USING` and `IMPORT TABLE` with any non-bundle format (`CSV`, `DELIMITED`, `PGCOPY`, or `AVRO`) data types.
To import data into a new table, use `CREATE TABLE` followed by `IMPORT INTO`.
`IMPORT INTO` supports CSV/TSV, Avro, and delimited data files. For an example, read Import into a new table from a CSV file.
## Considerations
* `IMPORT` is a blocking statement. To run an import job asynchronously, use the [`DETACHED`](#options-detached) option.
* `IMPORT` cannot be used within a rolling upgrade.
* Certain `IMPORT TABLE` statements that defined the table schema inline are **not** supported in v22.1 and later versions. These include running `IMPORT TABLE ... CREATE USING` and `IMPORT TABLE` with any non-bundle format (`CSV`, `DELIMITED`, `PGCOPY`, or `AVRO`) data types. Instead, use `CREATE TABLE` and `IMPORT INTO`; see this example for more detail.
* For instructions and working examples on how to migrate data from other databases, see the Migration Overview.
* `IMPORT` cannot directly import data to `REGIONAL BY ROW` tables that are part of multi-region databases. Instead, use `IMPORT INTO` which supports importing into `REGIONAL BY ROW` tables.
Optimize import operations in your applications by following our Import Performance Best Practices.
## Required privileges
### Table privileges
The user must have the `CREATE` privileges on the target database.
### Source privileges
You can grant a user the `EXTERNALIOIMPLICITACCESS` system-level privilege to interact with external resources that require implicit access.
Either the `EXTERNALIOIMPLICITACCESS` system-level privilege or the `admin` role is required for the following scenarios:
* Interacting with a cloud storage resource using `IMPLICIT` authentication.
* Using a [custom endpoint](https://docs.aws.amazon.com/sdk-for-go/api/aws/endpoints/) on S3.
* Using the `cockroach nodelocal upload` command.
* Using HTTP or HTTPS.
No special privilege is required for:
* Interacting with an Amazon S3 and Google Cloud Storage resource using `SPECIFIED` credentials. Azure Storage is always `SPECIFIED` by default.
* Using Userfile storage.
We recommend using cloud storage. You also need to ensure that the permissions at your storage destination are configured for the operation. See Storage Permissions for a list of the necessary permissions that each bulk operation requires.
While Cockroach Labs actively tests Amazon S3, Google Cloud Storage, and Azure Storage, we **do not** test S3-compatible services S3-compatible services (e.g., [MinIO](https://min.io/), [Red Hat Ceph](https://docs.ceph.com/en/pacific/radosgw/s3/)).
## Synopsis
## Parameters
### For import from dump file
| Parameter | Description |
| --------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `table_name` | The name of the table you want to import/create. Use this when the dump file contains a specific table. Leave out `TABLE table_name FROM` when the dump file contains an entire database. |
| `import_format` | [`PGDUMP`](#import-a-postgresql-database-dump) OR [`MYSQLDUMP`](#import-a-mysql-database-dump) |
| `file_location` | The [URL](#import-file-location) of a dump file you want to import. |
| `WITH kv_option_list` | Control your import's behavior with [these options](#import-options). |
### Import options
You can control the `IMPORT` process's behavior using any of the following optional key-value pairs as a `kv_option`. To set multiple import options, use a comma-separated list ([see examples](#examples)).
| Key | Context | Value |
| -------------------------------------- | --------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `decompress` | General | The decompression codec to be used: `gzip`, `bzip`, `auto`, or `none`. **Default:** `'auto'`, which guesses based on file extension (`.gz`, `.bz`, `.bz2`). `none` disables decompression. |
| `row_limit` | General | The number of rows to import. Useful for doing a test run of an import and finding errors quickly. This option will import the first *n* rows from each table in the dump file. |
| `skip_foreign_keys` | `PGDUMP`, `MYSQLDUMP` | Ignore foreign key constraints in the dump file's DDL. **Default:** `Off`. May be necessary to import a table with unsatisfied foreign key constraints from a full database dump. |
| `max_row_size` | `PGDUMP` | Override limit on line size. **Default:** `0.5MB`. This setting may need to be tweaked if your PostgreSQL dump file has extremely long lines, for example as part of a `COPY` statement. |
| `ignore_unsupported_statements` | `PGDUMP` | Ignore SQL statements in the dump file that are unsupported by CockroachDB. |
| `log_ignored_statements` | `PGDUMP` | Log unsupported statements when using `ignore_unsupported_statements` to a specified destination (i.e., cloud storage or userfile storage). |
| `DETACHED` | N/A | When an import runs in `DETACHED` mode, it will execute asynchronously and the job ID will be returned immediately without waiting for the job to finish. Note that with `DETACHED` specified, further job information and the job completion status will not be returned. For more on the differences between the returned job data, see the example below. To check on the job status, use the `SHOW JOBS` statement.
To run an import within a transaction, use the `DETACHED` option. |
For examples showing how to use these options, see the [Examples](#examples) section below.
For instructions and working examples showing how to migrate data from other databases and formats, see the Migration Overview.
## Requirements
### Before you begin
Before using `IMPORT`, you should have:
* The schema of the table you want to import.
* The data you want to import, preferably hosted on cloud storage. This location must be equally accessible to all nodes using the same import file location. This is necessary because the `IMPORT` statement is issued once by the client, but is executed concurrently across all nodes of the cluster. For more information, see [Import file location](#import-file-location).
For more information on details to consider when running an `IMPORT`, see [Considerations](#considerations).
### Import targets
Imported tables must not exist and must be created in the `IMPORT` statement with the schema and data importing from the same source. If the table you want to import already exists, you must drop it with `DROP TABLE` or use `IMPORT INTO`.
You can specify the target database in the table name in the `IMPORT` statement. If it's not specified there, the active database in the SQL session is used.
### Create table
Your `IMPORT` statement must reference an import file that specifies the schema of the data you want to import. You have several options:
* Load a file that already contains a `CREATE TABLE` statement. For an example, see [Import a PostgreSQL database dump](#import-a-postgresql-database-dump) below.
* Use `CREATE TABLE` followed by `IMPORT INTO`. For an example, see Import into a new table from a CSV file.
We also recommend specifying all secondary indexes you want to use in the `CREATE TABLE` statement. It is possible to add secondary indexes later, but it is significantly faster to specify them during import. For large imports, read additional guidance in Import into a schema with secondary indexes.
Other support considerations include:
* `IMPORT` supports computed columns for PostgreSQL dump files only.
* By default, the [PostgreSQL][postgres] and [MySQL][mysql] import formats support foreign keys. However, the most common dependency issues during import are caused by unsatisfied foreign key relationships that cause errors like `pq: there is no unique constraint matching given keys for referenced table tablename`. You can avoid these issues by adding the [`skip_foreign_keys`](#import-options) option to your `IMPORT` statement as needed. Ignoring foreign constraints will also speed up data import.
### Available storage
Each node in the cluster is assigned an equal part of the imported data, and so must have enough temp space to store it. In addition, data is persisted as a normal table, and so there must also be enough space to hold the final, replicated data. The node's first-listed/default `store` directory must have enough available storage to hold its portion of the data.
On `cockroach start`, if you set `--max-disk-temp-storage`, it must also be greater than the portion of the data a node will store in temp space.
### Import file location
CockroachDB uses the URL provided to construct a secure API call to the service you specify. The URL structure depends on the type of file storage you are using. For more information, see the following:
* Use Cloud Storage
* Use `userfile` Storage
* Use a Local File Server
You can create an external connection to represent an external storage or sink URI. This allows you to specify the external connection's name in statements rather than the provider-specific URI. For detail on using external connections, see the `CREATE EXTERNAL CONNECTION` page.
### Table users and privileges
Imported tables are treated as new tables, so you must `GRANT` privileges to them.
## Performance
* All nodes are used during the import job, which means all nodes' CPU and RAM will be partially consumed by the `IMPORT` task in addition to serving normal traffic.
* To improve performance, import at least as many files as you have nodes (i.e., there is at least one file for each node to import) to increase parallelism.
* To further improve performance, order the data in the imported files by primary key and ensure the primary keys do not overlap between files.
* An import job will pause if a node in the cluster runs out of disk space. See [Viewing and controlling import jobs](#viewing-and-controlling-import-jobs) for information on resuming and showing the progress of import jobs. For instructions on how to free up disk space as quickly as possible after dropping a table, see How can I free up disk space that was used by a dropped table?
* An import job will pause instead of entering a `failed` state if it continues to encounter transient errors once it has retried a maximum number of times. Once the import has paused, you can either resume or cancel it.
For more detail on optimizing import performance, see Import Performance Best Practices.
## Viewing and controlling import jobs
After CockroachDB initiates an import, you can view its progress with `SHOW JOBS` and on the **Jobs** page of the DB Console, and you can control it with `PAUSE JOB`, `RESUME JOB`, and `CANCEL JOB`.
If initiated correctly, the statement returns when the import is finished or if it encounters an error. In some cases, the import can continue after an error has been returned (the error message will tell you that the import has resumed in the background).
When resumed, paused imports now continue from their internally recorded progress instead of starting over.
## Examples
The following examples make use of:
* Amazon S3 connection strings. For guidance on connecting to other storage options or using other authentication parameters instead, read Use Cloud Storage.
* The **default** `AUTH=specified` parameter. For guidance on using `AUTH=implicit` authentication with Amazon S3 buckets instead, read Cloud Storage Authentication.
Also, note the following features for connecting and authenticating to cloud storage:
* External connections, which allow you to represent an external storage or sink URI. You can then specify the external connection's name in statements rather than the provider-specific URI. For detail on using external connections, see the `CREATE EXTERNAL CONNECTION` page.
* Assume role authentication, which allows you to limit the control specific users have over your storage buckets. See Assume role authentication for more information.
### Import a PostgreSQL database dump
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IMPORT PGDUMP 's3://{BUCKET NAME}/{customers.sql}?AWS_ACCESS_KEY_ID={ACCESS KEY}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
WITH ignore_unsupported_statements;
```
For this command to succeed, you need to have created the dump file with specific flags to `pg_dump`, and use the `WITH ignore_unsupported_statements` clause. For more information, see Migrate from PostgreSQL.
### Import a table from a PostgreSQL database dump
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IMPORT TABLE employees
FROM PGDUMP 's3://{BUCKET NAME}/{employees-full.sql}?AWS_ACCESS_KEY_ID={ACCESS KEY}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
WITH skip_foreign_keys WITH ignore_unsupported_statements;
```
If the table schema specifies foreign keys into tables that do not exist yet, the `WITH skip_foreign_keys` option may be needed. For more information, see the list of [import options](#import-options).
For this command to succeed, you need to have created the dump file with specific flags to `pg_dump`. For more information, see Migrate from PostgreSQL.
### Import a MySQL database dump
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IMPORT MYSQLDUMP 's3://{BUCKET NAME}/{employees-full.sql}?AWS_ACCESS_KEY_ID={ACCESS KEY}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}';
```
For more detailed information about importing data from MySQL, see Migrate from MySQL.
### Import a table from a MySQL database dump
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IMPORT TABLE employees
FROM MYSQLDUMP 's3://{BUCKET NAME}/{employees-full.sql}?AWS_ACCESS_KEY_ID={ACCESS KEY}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}' WITH skip_foreign_keys;
```
If the table schema specifies foreign keys into tables that do not exist yet, the `WITH skip_foreign_keys` option may be needed. For more information, see the list of [import options](#import-options).
For more detailed information about importing data from MySQL, see Migrate from MySQL.
### Import a limited number of rows
The `row_limit` option determines the number of rows to import. This option will import the first *n* rows from each table in the dump file. It is useful for finding errors quickly before executing a more time- and resource-consuming import. Imported tables can be inspected for their schema and data, but must be dropped before running the actual import.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IMPORT PGDUMP
's3://{BUCKET NAME}/{customers.sql}?AWS_ACCESS_KEY_ID={ACCESS KEY}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
WITH
row_limit = '10';
```
### Import a compressed file
CockroachDB chooses the decompression codec based on the filename (the common extensions `.gz` or `.bz2` and `.bz`) and uses the codec to decompress the file during import.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IMPORT TABLE employees
FROM PGDUMP 's3://{BUCKET NAME}/{employees-full.sql.gz}?AWS_ACCESS_KEY_ID={ACCESS KEY}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}';
```
Optionally, you can use the `decompress` option to specify the codec to be used for decompressing the file during import:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IMPORT TABLE employees
FROM PGDUMP 's3://{BUCKET NAME}/{employees-full.sql}?AWS_ACCESS_KEY_ID={ACCESS KEY}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
WITH decompress = 'gzip';
```
### Run an import within a transaction
The `DETACHED` option allows an import to be run asynchronously, returning the job ID immediately once initiated. You can run imports within transactions by specifying the `DETACHED` option.
To use the `DETACHED` option with `IMPORT` in a transaction:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
BEGIN;
CREATE DATABASE newdb;
SET DATABASE = newdb;
IMPORT TABLE employees FROM PGDUMP 's3://{BUCKET NAME}/{employees-full.sql}?AWS_ACCESS_KEY_ID={ACCESS KEY}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}' WITH DETACHED;
COMMIT;
```
The job ID is returned immediately without waiting for the job to finish:
```
job_id
----------------------
592786066399264769
(1 row)
```
**Without** the `DETACHED` option, `IMPORT` will block the SQL connection until the job completes. Once finished, the job status and more detailed job data is returned:
```
job_id | status | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+--------
652471804772712449 | succeeded | 1 | 50 | 0 | 4911
(1 row)
```
### Import a table from a local file
You can import a file from `nodelocal`, which is the external IO directory on a node's local file system. To import from `nodelocal`, a `nodeID` is required and the data files will be in the `extern` directory of the specified node.
The file system backup location on the NFS drive is relative to the path specified by the `--external-io-dir` flag set while starting the node. If the flag is set to `disabled`, then imports from local directories and NFS drives are disabled. Use `self` if you do not want to specify a `nodeID`, and the individual data files will be in the `extern` directories of arbitrary nodes; however, to work correctly, each node must have the `--external-io-dir` flag point to the same NFS mount or other network-backed, shared storage.
If a `nodeID` is provided, the data files to import will be in the `extern` directory of the specified node:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
cd /tmp/node2 && ls
```
```
000355.log cockroach-temp700212211
000357.log cockroach.advertise-addr
000359.sst cockroach.advertise-sql-addr
COCKROACHDB_VERSION cockroach.http-addr
CURRENT cockroach.listen-addr
IDENTITY cockroach.sql-addr
LOCK extern
MANIFEST-000010 logs
OPTIONS-000005 temp-dirs-record.txt
auxiliary
```
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
cd /tmp/node2/extern && ls
```
```
customers.sql
```
Then, specify which node to access by including the `nodeID` in the `IMPORT` statement:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IMPORT TABLE customers FROM PGDUMP 'nodelocal://2/customers.sql';
```
You can also use the `cockroach nodelocal upload` command to upload a file to the external IO directory on a node's (the gateway node, by default) local file system.
### Import data into your CockroachDB Cloud cluster
You can import data into your CockroachDB Cloud cluster using either `userfile` or cloud storage:
#### Import using `userfile`
To import from `userfile`, first create the table that you would like to import into:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE customers (
id INT,
dob DATE,
first_name STRING,
last_name STRING,
joined DATE
);
```
Then, use `IMPORT INTO` to import data into the table:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
IMPORT INTO customers (id, dob, first_name, last_name, joined)
CSV DATA ('userfile:///test-data.csv');
```
`userfile:///` references the default path (`userfile://defaultdb.public.userfiles_$user/`).
```
job_id | status | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+--------+---------------+-----------
599865027685613569 | succeeded | 1 | 300024 | 0 | 13389972
(1 row)
```
For more import options, see `IMPORT INTO`.
#### Import using cloud storage
To import a table into your cluster:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> IMPORT TABLE customers (
id UUID PRIMARY KEY,
name TEXT,
INDEX name_idx (name)
)
CSV DATA ('s3://{BUCKET NAME}/{customer-data}?AWS_ACCESS_KEY_ID={ACCESS KEY}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}')
;
```
## Known limitation
`IMPORT` can sometimes fail with a "context canceled" error, or can restart itself many times without ever finishing. If this is happening, it is likely due to a high amount of disk contention. This can be mitigated by setting the `kv.bulk_io_write.max_rate` cluster setting to a value below your max disk write speed. For example, to set it to 10MB/s, execute:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SET CLUSTER SETTING kv.bulk_io_write.max_rate = '10MB';
```
## See also
* Use Cloud Storage
* Migration Overview
* Migrate from MySQL
* Migrate from PostgreSQL
* `IMPORT INTO`
[postgres]: /docs/molt/migrate-to-cockroachdb
[mysql]: /docs/molt/migrate-to-cockroachdb?filters=mysql