Scenario
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:
IN,Evansville,7415326
TX,Fort Worth,9000748
IN,South Bend,2590270
KS,Shawnee Mission,9444064
GA,Macon,9804704
WA,Seattle,7928277
The associated
IMPORT
command:
IMPORT TABLE population (
state char(2),
city TEXT,
population INT8
) CSV DATA ('https://api.mockaroo.com/api/25164a90?count=1000&key=02f7f490');
The associated
IMPORT INTO
command:
IMPORT INTO population (state, city, population)
CSV DATA ('https://api.mockaroo.com/api/25164a90?count=5000&key=02f7f490');
The big difference between
IMPORT
and IMPORT INTO
is the former will create the table for you and the latter expects the table to exist. That said, you can repeatedly issue IMPORT INTO
command.
CockroachDB has ability to import CSV from cloud storage, http(s) endpoint, local filesystem, etc. You can read more about
IMPORT
command, here and the associated IMPORT INTO
command, 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://'
-e
is 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 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, 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.
No comments:
Post a Comment