CockroachDB Statement Redirection From an External File
CockroachDB is the SQL database for building scalable cloud services that survive disasters. It is designed to be a PostgreSQL compatible database with distributed roots.
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.
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
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
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
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.
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 ...
Since I've written this document, the original issue has been addressed and CockroachDB now has native support for
--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.