Use Userfile for Bulk Operations

On this page

To put files on your CockroachDB cluster without external servers, use userfile, a per-user bulk file storage. To interact with userfile, use the following commands:

• cockroach userfile upload
• cockroach userfile list
• cockroach userfile get
• cockroach userfile delete

Once a userfile is uploaded, you can run IMPORT.

New in v21.1: For PGDUMP and MYSQLDUMP formats, you can use cockroach import to upload a userfile, import its data, and delete the userfile in one command.

Upload a file

$ cockroach userfile upload /Users/maxroach/Desktop/test-data.csv /test-data.csv --certs-dir=certs

successfully uploaded to userfile://defaultdb.public.userfiles_root/test-data.csv

For more information, see cockroach userfile upload.

List files

$ cockroach userfile list '*.csv' --certs-dir=certs

userfile://defaultdb.public.userfiles_root/test-data-2.csv
userfile://defaultdb.public.userfiles_root/test-data.csv

For more information, see cockroach userfile list.

Get files

$ cockroach userfile get test-data.csv --certs-dir=certs

For more information, see cockroach userfile get.

Delete files

$ cockroach userfile delete test-data.csv --certs-dir=certs

deleted userfile://defaultdb.public.userfiles_root/test-data.csv

For more information, see cockroach userfile delete.

Upload and import from a dump file

$ cockroach import db mysqldump /Users/maxroach/Desktop/test-db.sql --certs-dir=certs

successfully imported mysqldump file /Users/maxroach/Desktop/test-db.sql

For more information, see cockroach import.

Import from userfile

To import from userfile, first create the table that you would like to import into:

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:

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.

Backup and restore with userfile

We recommend starting backups from a time at least 10 seconds in the past using AS OF SYSTEM TIME. Read our guidance in the Performance section on the BACKUP page.

When working on the same cluster, userfile storage allows for database and table-level backups.

First, run the following statement to backup a database to a directory in the default userfile space:

BACKUP DATABASE bank INTO 'userfile://defaultdb.public.userfiles_$user/bank-backup' AS OF SYSTEM TIME '-10s';

This directory will hold the files that make up a backup; including the manifest file and data files.

In cases when your database needs to be restored, run the following:

RESTORE DATABASE bank FROM LATEST IN 'userfile://defaultdb.public.userfiles_$user/bank-backup';

It is also possible to run userfile:///bank-backup as userfile:/// refers to the default path userfile://defaultdb.public.userfiles_$user/.

Once the backup data is no longer needed, delete from the userfile storage with the following command:

cockroach userfile delete bank-backup --url {CONNECTION STRING}

If you use cockroach userfile delete {file}, it will take as long as the garbage collection to be removed from disk.

See also

• cockroach userfile upload
• cockroach userfile list
• cockroach userfile get
• cockroach userfile delete
• Other Cockroach Commands
• IMPORT
• IMPORT INTO

Was this helpful?