I have a table called population with the following schema:
CREATE TABLE population ( state CHAR(2) NULL, city STRING NULL, population INT8 NULL, FAMILY "primary" (state, city, population, rowid) )
The associated CSV file has the following structure:
IMPORT TABLE population ( state char(2), city TEXT, population INT8 ) CSV DATA ('https://api.mockaroo.com/api/25164a90?count=1000&key=02f7f490');
IMPORT INTO population (state, city, population) CSV DATA ('https://api.mockaroo.com/api/25164a90?count=5000&key=02f7f490');
The big difference between
IMPORT INTOis the former will create the table for you and the latter expects the table to exist. That said, you can repeatedly issue
CockroachDB has ability to import CSV from cloud storage, http(s) endpoint, local filesystem, etc. You can read more about
IMPORTcommand, here and the associated
IMPORT INTOcommand, here.
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 command below:
cockroach sql -e="$(<import_into_statement.sql)" --url 'postgresql://'
-eis functionally equivalent to
--execute. This is pretty 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, 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.