COPY FROM

Imports data from a comma-separated values (CSV) file or a delimited text file into an existing table. Each line in the source file is imported as a row. All rows in the dataset must contain the same number of fields and have values in the PRIMARY KEY fields.

The process verifies the PRIMARY KEY and updates existing records. If HEADER = false and no column names are specified, the fields are imported in deterministic order. When HEADER = true, the first row of a file is a header row.

Only use COPY FROM to import datasets that have less than two million rows. To import large datasets, use sstableloader.

Synopsis

COPY <table_name> [ ( <column_list> ) ]
  FROM '<file_name>'[ , '<file2_name>', ... ] | STDIN
  [ WITH option = 'value' [ AND ... ] ]

COPY supports a list of one or more comma-separated file names or Python glob expressions.
Legend
Syntax conventions Description

UPPERCASE

Literal keyword.

Lowercase

Not literal.

< >

Variable value. Replace with a user-defined value.

[]

Optional. Square brackets ([]) surround optional command arguments. Do not type the square brackets.

( )

Group. Parentheses ( ( ) ) identify a group to choose from. Do not type the parentheses.

|

Or. A vertical bar (|) separates alternative elements. Type any one of the elements. Do not type the vertical bar.

...

Repeatable. An ellipsis ( ... ) indicates that you can repeat the syntax element as often as required.

'<Literal string>'

Single quotation (') marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.

{ <key> : <value> }

Map collection. Braces ({ }) enclose map collections or key value pairs. A colon separates the key and the value.

<<datatype1>,<datatype2>>

Set, list, map, or tuple. Angle brackets ( < > ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.

<cql_statement>;

End CQL statement. A semicolon (;) terminates all CQL statements.

[--]

Separate the command line options from the command arguments with two hyphens ( -- ). This syntax is useful when arguments might be mistaken for command line options.

' <<schema\> ... </schema\>> '

Search CQL only: Single quotation marks (') surround an entire XML schema declaration.

@<xml_entity>='<xml_entity_type>'

Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files.
<table_name>

Table for the copy operation.

<column_list>

List of columns in the table. All fields are included when no column names are specified. To omit columns, specify a column list with only the columns to include.

<file_name>, <file2_name>

CSV file name.

Set COPY options

You can set COPY options on the command line or in a cqlshrc file.

If you use the same settings frequently, the cqlshrc file can be more convenient that specifying them on the command line each time. The cqlshrc file is also recommended for storing connection options, like credentials.

Options set on the command line override options set in cqlshrc, and options set in cqlshrc override the default settings.

Because the COPY command has subcommands for COPY TO and COPY FROM, some options apply to the COPY command generally, some options apply to a specific subcommand only, and some options are available to both subcommands but specific in a subcommand section in cqlshrc.

The following example shows options in the [copy] section of the cqlshrc file. For subcommand-specific options, use [copy-to] or [copy-from] sections.

[copy]
BOOLSTYLE       = True,False
CONFIGFILE      = /home/
DATETIMEFORMAT  = %m/%d/%Y
DECIMALSEP      = ,
DELIMITER       = |
ESCAPE          = `
HEADER          = true
MAXATTEMPTS     = 10
NULL            = NULL
NUMPROCESSES    = -1
QUOTE           = "
REPORTFREQUENCY = 1
RATEFILE        = /home/copyrate.log
SKIPCOLS        = firstname
SKIPROWS        = 6
THOUSANDSSEP    = ,
Option Description Default Supported subcommand cqlshrc section

BOOLSTYLE

Boolean indicators for true and false. The values are case-insensitive. For example: yes,no and YES,NO are the same.

True,False

COPY TO, COPY FROM

[copy]

CONFIGFILE

Directory that contains the cqlshrc configuration file to look for options if an option isn’t set on the command line.

COPY TO, COPY FROM

[copy]

DATETIMEFORMAT

Time format for reading or writing CSV time data. The timestamp uses the strftime format.

%Y-%m-%d %H:%M:%S%z

COPY TO, COPY FROM

[copy]

DECIMALSEP

Decimal value separator.

. (period)

COPY TO, COPY FROM

[copy]

DELIMITER

Field separator.

, (comma)

COPY TO, COPY FROM

[copy]

ESCAPE

Single character that escapes literal uses of the QUOTE character.

\ (backslash)

COPY TO, COPY FROM

[copy]

HEADER

Whether to treat the first row of a CSV file as a header row.

  • true: First row contains headers (column names)

  • false: First row does not have headers

false

COPY TO, COPY FROM

[copy]

MAXATTEMPTS

Maximum number of attempts for errors.

5

COPY TO, COPY FROM

[copy]

NULL

Value automatically written if a field is empty.

<empty>

COPY TO, COPY FROM

[copy]

NUMPROCESSES

Number of worker processes. Maximum value is 16.

-1

COPY TO, COPY FROM

[copy]

QUOTE

Encloses field values.

" (double quotation mark)

COPY TO, COPY FROM

[copy]

REPORTFREQUENCY

Frequency with which status is displayed in seconds.

0.25

COPY TO, COPY FROM

[copy]

RATEFILE

Print output statistics to this file.

COPY TO, COPY FROM

[copy]

SKIPCOLS

Names of columns to skip.

COPY TO, COPY FROM

[copy]

SKIPROWS

Number of rows to skip, starting from the first row of data.

COPY TO, COPY FROM

[copy]

THOUSANDSSEP

Separator for thousands digit groups.

None

COPY TO, COPY FROM

[copy]

BEGINTOKEN

Minimum token string for exporting data.

COPY TO

[copy-to]

DOUBLEPRECISION

Number of digits to display after the decimal point for CQL double precision values.

12

COPY TO, COPY FROM

[copy-to], [copy-from]

ENCODING

Output string type.

UTF8

COPY TO, COPY FROM

[copy-to], [copy-from]

ENDTOKEN

Maximum token string for exporting data.

COPY TO

[copy-to]

ERRFILE

File to store all rows that are not imported.

A file named import_keyspace_table.err where keyspace is the keyspace name and table is the table name.

COPY FROM

[copy-from]

FLOATPRECISION

Number of digits to display after the decimal point for CQL float (single precision) values.

5

COPY TO, COPY FROM

[copy-to], [copy-from]

MAXOUTPUTSIZE

Maximum size of the output file, measured in number of lines. When set, the output file is split into segment when the value is exceeded. Use -1 for unlimited.

-1

COPY TO

[copy-to]

MAXREQUESTS

Maximum number of requests each worker can process in parallel.

6

COPY TO, COPY FROM

[copy-to], [copy-from]

PAGESIZE

Page size for fetching results.

1000

COPY FROM

[copy-from]

PAGETIMEOUT

Page timeout for fetching results.

10

COPY FROM

[copy-from]

TTL

Time to live in seconds. By default, data will not expire.

3600

COPY FROM

[copy-from]

CHUNKSIZE

Chunk size passed to worker processes.

1000

COPY FROM

[copy-from]

INGESTRATE

Approximate ingest rate in rows per second. Must be greater than the chunk size.

100000

COPY FROM

[copy-from]

MAXBATCHSIZE

Maximum size of an import batch.

20

COPY FROM

[copy-from]

MAXINSERTERRORS

Maximum global number of insert errors. Use -1 for no maximum.

-1

COPY FROM

[copy-from]

MAXPARSEERRORS

Maximum global number of parsing errors. Use -1 for no maximum.

-1

COPY FROM

[copy-from]

MAXROWS

Maximum number of rows. Use -1 for no maximum.

-1

COPY FROM

[copy-from]

MINBATCHSIZE

Minimum size of an import batch.

2

COPY FROM

[copy-from]

Set table-specific COPY options

On the command line, you specify the keyspace and table name is your command, so all options are applied to a specific table. For tables that are frequently targeted by COPY commands, you can set table-specific options in the cqlshrc file. Create separate sections for each table using the appropriate section naming convention.

chunksize

Chunk size passed to worker processes for both the COPY TO and COPY FROM subcommands.

  • Section: [copy:<keyspace_name.table_name>]

  • Default: 1000

[copy:cycling.rank_by_year_and_name]
chunksize = 10000
ingestrate

Approximate ingest rate in rows per second for the COPY FROM subcommand. Must be greater than chunksize.

  • Section: [copy-from:<keyspace_name.table_name>]

  • Default: 100000

[copy-from:cycling.rank_by_year_and_name]
ingestrate = 80000
pagetimeout

Page timeout when fetching results for the COPY TO subcommand.

  • Section: [copy-to:<keyspace_name.table_name>]

  • Default: 10

[copy-to:cycling.rank_by_year_and_name]
pagetimeout = 10

Examples

These examples use the following schema and data:

  1. Create a cycling keyspace:

    CREATE KEYSPACE cycling
WITH REPLICATION = {
  'class' : 'NetworkTopologyStrategy',
  'datacenter1' : 1
};

  2. Create the cycling.cyclist_name table:

    CREATE TABLE cycling.cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);

  3. Insert data into cycling.cyclist_name:

    INSERT INTO cycling.cyclist_name (id, lastname, firstname)
VALUES (5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 'VOS','Marianne');

INSERT INTO cycling.cyclist_name (id, lastname, firstname)
VALUES (e7cd5752-bc0d-4157-a80f-7523add8dbcd, 'VAN DER BREGGEN','Anna');

INSERT INTO cycling.cyclist_name (id, lastname, firstname)
VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0, 'FRAME','Alex');

INSERT INTO cycling.cyclist_name (id, lastname, firstname)
VALUES (220844bf-4860-49d6-9a4b-6b5d3a79cbfb, 'TIRALONGO','Paolo');

INSERT INTO cycling.cyclist_name (id, lastname, firstname)
VALUES (6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIKSWIJK','Steven');

INSERT INTO cycling.cyclist_name (id, lastname, firstname)
VALUES (fb372533-eb95-4bb4-8685-6ef61e994caa, 'MATTHEWS', 'Michael');

Export and import data from a table

  1. Export only the id and lastname columns from the cyclist_name table to a CSV file:

    COPY cycling.cyclist_name (id,lastname)
TO '../cyclist_lastname.csv' WITH HEADER = TRUE;

    The cyclist_lastname.csv file is created in the directory above the current working directory (indicated by ../). If the CSV file already exists, it is overwritten. If you do not have permission to create the file in the directory, you can use a different directory; for example, to use the current working directory, omit the directory path before the file name.

    Using 7 child processes

Starting copy of cycling.cyclist_name with columns [id, lastname].
Processed: 6 rows; Rate:      29 rows/s; Avg. rate:      29 rows/s
6 rows exported to 1 files in 0.223 seconds.

  2. Copy the id and firstname to a different CSV file named cyclist_firstname.csv:

    COPY cycling.cyclist_name (id,firstname)
TO '../cyclist_firstname.csv' WITH HEADER = TRUE;

    The CSV file is created:

    Using 7 child processes

Starting copy of cycling.cyclist_name with columns [id, firstname].
Processed: 6 rows; Rate:      30 rows/s; Avg. rate:      30 rows/s
6 rows exported to 1 files in 0.213 seconds.

  3. Remove all records from the cyclist name table:

    TRUNCATE cycling.cyclist_name;

  4. Verify that there are no rows:

    SELECT *
FROM cycling.cyclist_name;

    Query results are empty:

     id | firstname | lastname
----+-----------+----------


(0 rows)

  5. Import the cyclist first names:

    COPY cycling.cyclist_name (id,firstname)
FROM '../cyclist_firstname.csv' WITH HEADER = TRUE;

    The rows are imported:

    Using 7 child processes

Starting copy of cycling.cyclist_name with columns [id, firstname].
Processed: 6 rows; Rate:      10 rows/s; Avg. rate:      14 rows/s
6 rows imported from 1 files in 0.423 seconds (0 skipped).

  6. Verify the new rows:

    SELECT *
FROM cycling.cyclist_name;

    The rows were created with null last names because the lastname field was not in the imported dataset:

     id                                   | firstname | lastname
--------------------------------------+-----------+----------
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 |      Alex |     null
 fb372533-eb95-4bb4-8685-6ef61e994caa |   Michael |     null
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |  Marianne |     null
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |     Paolo |     null
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 |    Steven |     null
 e7cd5752-bc0d-4157-a80f-7523add8dbcd |      Anna |     null

(6 rows)

  7. Import the last names:

    COPY cycling.cyclist_name (id,lastname)
FROM '../cyclist_lastname.csv' WITH HEADER = TRUE;

    The records are imported but no new records are created:

    Using 7 child processes

Starting copy of cycling.cyclist_name with columns [id, lastname].
Processed: 6 rows; Rate:      10 rows/s; Avg. rate:      14 rows/s
6 rows imported from 1 files in 0.422 seconds (0 skipped).

  8. Verify the that the records were updated:

    SELECT *
FROM cycling.cyclist_name;

    The PRIMARY KEY id matched for all records and the lastname is populated.:

     id                                   | firstname | lastname
--------------------------------------+-----------+-----------------
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 |      Alex |           FRAME
 fb372533-eb95-4bb4-8685-6ef61e994caa |   Michael |        MATTHEWS
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |  Marianne |             VOS
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |     Paolo |       TIRALONGO
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 |    Steven |      KRUIKSWIJK
 e7cd5752-bc0d-4157-a80f-7523add8dbcd |      Anna | VAN DER BREGGEN

Copy data from standard input to a table

  1. Clear the data from the cyclist_name table:

    TRUNCATE cycling.cyclist_name;

  2. Start the copy input operation using the FROM STDIN option:

    COPY cycling.cyclist_name FROM STDIN;

    The line prompt changes to [copy]:

    Using 7 child processes

Starting copy of cycling.cyclist_name with columns [id, firstname, lastname].
[Use . on a line by itself to end input]
[copy]

  3. Next to the [copy] prompt, enter the field values in a common-separated list; on the last line of data, enter a period:

    [copy] e7cd5752-bc0d-4157-a80f-7523add8dbcd,Anna,VAN DER BREGGEN
    [copy] .

  4. Press Enter after the period:

    Processed: 1 rows; Rate:       0 rows/s; Avg. rate:       0 rows/s
1 rows imported from 1 files in 36.991 seconds (0 skipped).

  5. Run this query to view the contents of the cyclist_name table:

    SELECT *
FROM cycling.cyclist_name;
     id                                   | firstname | lastname
--------------------------------------+-----------+-----------------
 e7cd5752-bc0d-4157-a80f-7523add8dbcd |      Anna | VAN DER BREGGEN

(1 rows)

Copy to and from a table with a vector data type

To copy data from a table with a vector data type to a CSV file, use the following command:

COPY cycling.comments_vs (record_id,id,commenter,comment,created_at,comment_vector)
  TO '../CSV/comments-vs-new.csv' WITH HEADER=TRUE;

To copy data from a CSV file to a table with a vector data type, use the following command:

COPY cycling.comments_vs (record_id,id,commenter,comment,created_at,comment_vector)
  FROM '../CSV/comments-vs.csv' WITH HEADER=TRUE AND DELIMITER='|';

Was this helpful?

Give Feedback

How can we improve the documentation?

© Copyright IBM Corporation 2026 | Privacy policy | Terms of use |  Manage Privacy Choices

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: Contact IBM