CockroachDB Statement Redirection From an External File

The PostgreSQL compatibility is being built from scratch in Go. That said, the product is undergoing a fast pace of innovation and development, yet one convenient method of passing SQL statements from an external file to CLI has been missing until recently. This post will discuss all available methods of achieving the same.

Scenario

I have a table called population with the following schema:

CREATE TABLE population (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY, 
    state CHAR(2) NULL,
    city STRING NULL,
    population INT8 NULL);

The associated CSV file has the following structure:

IN,Evansville,7415326
TX,Fort Worth,9000748
IN,South Bend,2590270
KS,Shawnee Mission,9444064
GA,Macon,9804704
WA,Seattle,7928277

The associated IMPORT INTO command:

IMPORT INTO population (
    state, 
    city, 
    population)
CSV DATA ('https://api.mockaroo.com/api/25164a90?count=5000&key=02f7f490');

CockroachDB has the ability to import CSV from cloud storage, http(s) endpoint, local filesystem, etc. You can read more about IMPORT INTO command, here. One of the advantages of IMPORT INTO is the ability to run it continuously as we're going to see in a bit.

Method 1a: Unix File Redirection

Suppose I want to pass an import statement from a file. With Unix file redirection the following can be done with the command below:

cockroach sql -e="$(<import_into_statement.sql)" --url 'postgresql://'

-e is functionally equivalent to --execute. It is a bit convoluted and can be further simplified by the next approach.

Method 1b: Unix File Redirection

cockroach sql < import_into_statement.sql --url 'postgresql://'

Method 2: Using psql

Given CockroachDB is Postgresql wire compatible, you can use psql utility to achieve the same.

I'm using Mac OSX and psql can be easily installed with:

brew install libpq
brew link --force libpq

Once installed, an import from an external file can be easily done with built-in -f and --file= flags:

psql -f import_into_statement.sql 'postgresql://root@localhost:26257/defaultdb?sslcert=certs%2Fclient.root.crt&sslkey=certs%2Fclient.root.key&sslmode=verify-full&sslrootcert=certs%2Fca.crt'

Additional file redirection tricks are illustrated in our docs.

Update

A question came up from a customer where they want to redirect input from a SQL file to Cockroach CLI on Windows. The following will work:

Get-Content file.sql | cockroach sql --certs=/certs ...

Update 2

Since I've written this document, the original issue has been addressed and CockroachDB now has native support for -f and --file= flags. Let's see that in action:

cockroach sql -f import_into_statement.sql --insecure

        job_id       |  status   | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+---------
  727833064397930497 | succeeded |                  1 | 5000 |             0 | 239054
(1 row)

cockroach sql --file=import_into_statement.sql --insecure

        job_id       |  status   | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+---------
  727833304641208321 | succeeded |                  1 | 5000 |             0 | 239596
(1 row)

That's all for today! Hope these methods help you with passing SQL statements from an external file to CLI.

Stay tuned for future posts.