Monday, July 25, 2016

Crush Tools dbstream with PostgreSQL

There's a command line library from Google called Crush Tools. It hasn't been updated for a while, but a few commands look useful, like pivot and aggregate.

There's also one called dbstream, which is a perl script to stream to/from a database.

Here's how to run it (on OS X anyways):

Install it (crush-tools, Perl package manager, Perl PostgreSQL driver):
$ brew install crush-tools
$ curl -L | perl - --sudo App::cpanminus
$ sudo cpanm DBD::Pg

And run it:
$ dbstream -de , -ds dbi:Pg:dbname=foo -u alice -p secret -s "select 1,2;"

or insert each record from a file:
$ cut -d, -f1-2 input.csv | dbstream ... -s "insert into tbl values (?,?);"

or look up each record from a file:
$ cut -d, -f1 input.csv | dbstream ... -s "select name from tbl where code=?;"

or update up each record from a file:
$ awk -F, '{print $2","$1}' input.csv | dbstream ... -s "update tbl set name=? where id=?;"

-de is the input & output field separator
-ds is a Perl DBI datasource
-u username
-p password
-s SQL

It's here if it's not in your package manager:

No comments: