IMPORT INTO

On this page

The IMPORT INTO statement imports CSV, Avro, or delimited data into an existing table, by appending new rows into the table.

Considerations

• IMPORT INTO works for existing tables. To import data into new tables, read the following Import into a new table from a CSV file example.
• IMPORT INTO cannot be used within a transaction or during a rolling upgrade.
• IMPORT INTO takes the table offline before importing the data. The table will be online again once the job has completed successfully.
• IMPORT INTO invalidates all foreign keys on the target table. To validate the foreign key(s), use the VALIDATE CONSTRAINT statement.
• IMPORT INTO is an insert-only statement; it cannot be used to update existing rows—see UPDATE. Imported rows cannot conflict with primary keys in the existing table, or any other UNIQUE constraint on the table.
• IMPORT INTO does not offer SELECT or WHERE clauses to specify subsets of rows. To do this, use INSERT.

Required privileges

Table privileges

The user must have the INSERT and DROP privileges on the specified table. (DROP is required because the table is taken offline during the IMPORT INTO.)

Source privileges

The source file URL does not require the ADMIN role in the following scenarios:

• S3 and GS using SPECIFIED (and not IMPLICIT) credentials. Azure is always SPECIFIED by default.
• Userfile

The source file URL does require the ADMIN role in the following scenarios:

• S3 or GS using IMPLICIT credentials
• Use of a custom endpoint on S3
• Nodelocal, HTTP, or HTTPS

Learn more about cloud storage for bulk operations.

Synopsis

Parameters

Delimited data files

New in v20.2: The DELIMITED DATA format can be used to import delimited data from any text file type, while ignoring characters that need to be escaped, like the following:

• The file's delimiter (\t by default)
• Double quotes (")
• Newline (\n)
• Carriage return (\r)

For examples showing how to use the DELIMITED DATA format, see the Examples section below.

Import options

You can control the IMPORT process's behavior using any of the following key-value pairs as a <option> [= <value>].

For examples showing how to use these options, see the IMPORT - Examples section.

For instructions and working examples showing how to migrate data from other databases and formats, see the Migration Overview. For information on how to import data into new tables, see IMPORT.

Requirements

Prerequisites

Before using IMPORT INTO, you should have:

• An existing table to import into (use CREATE TABLE).

  New in v20.2: IMPORT INTO supports computed columns and the DEFAULT expressions listed below.

• The CSV or Avro 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 INTO statement is issued once by the client, but is executed concurrently across all nodes of the cluster. For more information, see the Import file location section below.

Supported DEFAULT expressions

New in v20.2: IMPORT INTO supports computed columns and the following DEFAULT expressions:

• Constant DEFAULT expressions, which are expressions that return the same value in different statements. Examples include:

  • Literals (booleans, strings, integers, decimals, dates)
  • Functions where each argument is a constant expression and the functions themselves depend solely on their arguments (e.g., arithmetic operations, boolean logical operations, string operations).

• Current TIMESTAMP functions that record the transaction timestamp, which include:

  • current_date()
  • current_timestamp()
  • localtimestamp()
  • now()
  • statement_timestamp()
  • timeofday()
  • transaction_timestamp()

• random()

• gen_random_uuid()

• unique_rowid()

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 for Bulk Operations
• New in v20.2: Use userfile for Bulk Operations
• Use a Local File Server for Bulk Operations

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.

Viewing and controlling import jobs

After CockroachDB successfully initiates an import into an existing table, it registers the import as a job, which you can view with SHOW JOBS.

After the import has been initiated, 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 background).

Examples

The following provide connection examples to cloud storage providers. For more information on connecting to different storage options, read Use Cloud Storage for Bulk Operations.

We recommend reading the Considerations section for important details when working with IMPORT INTO.

CREATE TABLE users (
        id UUID PRIMARY KEY,
        city STRING,
        name STRING,
        address STRING,
        credit_card STRING
      );

IMPORT INTO users (id, city, name, address, credit_card)
     CSV DATA (
       's3://{BUCKET NAME}/{customers.csv}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
     );

> IMPORT INTO customers (id, name)
    CSV DATA (
      's3://{BUCKET NAME}/{customers.csv}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
    );

> IMPORT INTO customers (id, name)
    CSV DATA (
      's3://{BUCKET NAME}/{customers.csv}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}',
      's3://{BUCKET NAME}/{customers2.csv}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}',
      's3://{BUCKET NAME}/{customers3.csv}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}',
      's3://{BUCKET NAME}/{customers4.csv}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}',
    );

> IMPORT INTO customers
    AVRO DATA (
      's3://{BUCKET NAME}/{customers.avro}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
    );

> IMPORT INTO customers
    DELIMITED DATA (
      's3://{BUCKET NAME}/{customers.csv}?AWS_ACCESS_KEY_ID={KEY ID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}'
    )
    WITH
      fields_terminated_by='|',
      fields_enclosed_by='"',
      fields_escaped_by='\';

CREATE TABLE users (
        id UUID PRIMARY KEY,
        city STRING,
        name STRING,
        address STRING,
        credit_card STRING
      );

IMPORT INTO users (id, city, name, address, credit_card)
     CSV DATA (
       'azure://{CONTAINER NAME}/{customers.csv}?AZURE_ACCOUNT_NAME={ACCOUNT NAME}&AZURE_ACCOUNT_KEY={ENCODED KEY}'
     );

> IMPORT INTO customers (id, name)
    CSV DATA (
      'azure://{CONTAINER NAME}/{customers.csv}?AZURE_ACCOUNT_NAME={ACCOUNT NAME}&AZURE_ACCOUNT_KEY={ENCODED KEY}'
    );

> IMPORT INTO customers (id, name)
    CSV DATA (
      'azure://{CONTAINER NAME}/{customers.csv}?AZURE_ACCOUNT_NAME={ACCOUNT NAME}&AZURE_ACCOUNT_KEY={ENCODED KEY}',
      'azure://{CONTAINER NAME}/{customers2.csv}?AZURE_ACCOUNT_NAME={ACCOUNT NAME}&AZURE_ACCOUNT_KEY={ENCODED KEY}',
      'azure://{CONTAINER NAME}/{customers3.csv}?AZURE_ACCOUNT_NAME={ACCOUNT NAME}&AZURE_ACCOUNT_KEY={ENCODED KEY}',
      'azure://{CONTAINER NAME}/{customers4.csv}?AZURE_ACCOUNT_NAME={ACCOUNT NAME}&AZURE_ACCOUNT_KEY={ENCODED KEY}',
      'azure://{CONTAINER NAME}/{customers5.csv}?AZURE_ACCOUNT_NAME={ACCOUNT NAME}&AZURE_ACCOUNT_KEY={ENCODED KEY}',    
    );

> IMPORT INTO customers
    AVRO DATA (
      'azure://{CONTAINER NAME}/{customers.avro}?AZURE_ACCOUNT_NAME={ACCOUNT NAME}&AZURE_ACCOUNT_KEY={ENCODED KEY}'
    );

> IMPORT INTO customers
    DELIMITED DATA (
      'azure://{CONTAINER NAME}/{customers.csv}?AZURE_ACCOUNT_NAME={ACCOUNT NAME}&AZURE_ACCOUNT_KEY={ENCODED KEY}'
    )
    WITH
      fields_terminated_by='|',
      fields_enclosed_by='"',
      fields_escaped_by='\';

CREATE TABLE users (
        id UUID PRIMARY KEY,
        city STRING,
        name STRING,
        address STRING,
        credit_card STRING
      );

IMPORT INTO users (id, city, name, address, credit_card)
     CSV DATA (
       'gs://{BUCKET NAME}/{customers.csv}?AUTH=specified&CREDENTIALS={ENCODED KEY}'
     );

> IMPORT INTO customers (id, name)
    CSV DATA (
      'gs://{BUCKET NAME}/{customers.csv}?AUTH=specified&CREDENTIALS={ENCODED KEY}'
    );

> IMPORT INTO customers (id, name)
    CSV DATA (
      'gs://{BUCKET NAME}/{customers.csv}?AUTH=specified&CREDENTIALS={ENCODED KEY}',
      'gs://{BUCKET NAME}/{customers2.csv}?AUTH=specified&CREDENTIALS={ENCODED KEY}',
      'gs://{BUCKET NAME}/{customers3.csv}?AUTH=specified&CREDENTIALS={ENCODED KEY}',
      'gs://{BUCKET NAME}/{customers4.csv}?AUTH=specified&CREDENTIALS={ENCODED KEY}',
    );

> IMPORT INTO customers
    AVRO DATA (
      'gs://{BUCKET NAME}/{customers.avro}?AUTH=specified&CREDENTIALS={ENCODED KEY}'
    );

> IMPORT INTO customers
    DELIMITED DATA (
      'gs://{BUCKET NAME}/{customers.csv}?AUTH=specified&CREDENTIALS={ENCODED KEY}'
    )
    WITH
      fields_terminated_by='|',
      fields_enclosed_by='"',
      fields_escaped_by='\';

Known limitations

• You cannot import into a table with partial indexes.
• While importing into an existing table, the table is taken offline.
• After importing into an existing table, constraints will be un-validated and need to be re-validated.
• Imported rows must not conflict with existing rows in the table or any unique secondary indexes.
• IMPORT INTO works for only a single existing table, and the table must not be interleaved.
• IMPORT INTO cannot be used within a transaction.

• IMPORT INTO 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:

  > SET CLUSTER SETTING kv.bulk_io_write.max_rate = '10MB';
  

See also

• IMPORT
• Migration Overview
• Use Cloud Storage for Bulk Operations

Was this helpful?