Created
January 2, 2019 14:59
-
-
Save raven-rock/74940dd7143d9ed608d0e8a2928f83b8 to your computer and use it in GitHub Desktop.
My suite of TSV helper tools for working with PostgreSQL/Redshift, MySQL, SQLite
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/env perl | |
use strict; | |
use File::Basename qw(basename); | |
my $PROGRAM = basename($0); | |
if ($ARGV[0] =~ /^(?:-h|--help)$/) { | |
print "Turn blanks from TSV input lines into \\N for mysqlimport consumption.\n"; | |
print "Usage examples:\n"; | |
print " $PROGRAM FILE\n"; | |
print " cat FILE | $PROGRAM\n"; | |
exit; | |
} | |
while (<>) { | |
chomp; | |
my @row = map { $_ eq "" ? "\\N" : $_ } split("\t", $_, -1); | |
print join("\t", @row), "\n"; | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/env bash | |
# For psql to connect to a database, we assume user has the environment | |
# variables PGHOST, PGPORT, PGDATABASE, PGUSER, and PGPASSWORD set. | |
if [[ -z $PGHOST || -z $PGPORT || -z $PGDATABASE || -z $PGUSER || -z $PGPASSWORD ]]; then | |
>&2 echo "psql2tsv: Please set PGHOST, PGPORT, PGDATABASE, PGUSER, and PGPASSWORD to connect to a db" | |
exit 1 | |
fi | |
# -A, --no-align | |
# -F, --field-separator=separator | |
# -P, --pset=assignment | |
# -q, --quiet # dont print some common stuff that's printed | |
psql \ | |
--no-align \ | |
--field-separator $'\t' \ | |
--pset footer=off \ | |
--quiet \ | |
"$@" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/env ruby | |
# The goal here is a script that can be called like this: | |
# | |
# sqlite-now-mem users.tsv,users,id,login,email products.tsv,products,id,name | |
# | |
# For each comma-separated tuple passed are arguments, the input file | |
# will be loaded into the table, and any following values are the | |
# names of columns for which indexes will be created. | |
# | |
# The --file=FILE flag can also be specified. If FILE doesn't exist it | |
# will be created. If it does, data will be imported into it, | |
# including if when the table already exists, rows will be appended. | |
# | |
# sqlite-now-mem --file=website.sqlite users.tsv,users,id,login,email products.tsv,products,id,name | |
require "optimist" | |
opts = Optimist::options do | |
opt :file, "SQLite database filename. Optional. If not specified, db is in memory.", :type => :string | |
end | |
p opts | |
sqlite_cmds = [ | |
".timer on", | |
".mode tab", | |
] | |
ARGV.each do |definition| | |
file, table, *index_cols = definition.split(",") | |
p [file, table, index_cols] | |
sqlite_cmds << ".import #{file} #{table}" | |
index_cols.each do |index_col| | |
sqlite_cmds << "create index index_#{table}_#{index_col} on #{table} (#{index_col});" | |
end | |
end | |
# final_command = "sqlite3 -header" + " " + sqlite_cmds.map { |cmd| %[-cmd "#{cmd}"] }.join(" ") + " " + %{-cmd ".mode column"} | |
final_system_cmd_elements = [ | |
"sqlite3", | |
"-interactive", | |
"-header", | |
*sqlite_cmds.map { |cmd| %[-cmd "#{cmd}"] }, | |
%{-cmd ".mode column"}, | |
*opts[:file], | |
] | |
puts final_system_cmd_elements.join(" \\\n ") | |
system final_system_cmd_elements.join(" \\\n ") |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/usr/bin/env bash | |
sqlite3 -header -separator $'\t' "$@" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment