Rails tips that I always forget

I occasionally work with large csv files. With large files, full featured programs often move really slowly, which can be frustrating when I have a few simple questions. By large, I mean csvs ranging from a thousand lines all the way up into the gigabyte ranges. To get around this it can be much faster to get these answers from some simple command line tools. Here’s a few tips that I’ve picked up over the years. I’ll use “csv” to specify the path/to/csv for simplicity

wc -l csv => number of lines in the csv

head -100 csv => Shows first 100 lines. Use -1 to just see the headers

tail -100 csv => Shows the last 100 lines

You can combine the commands to access any contiguous range of lines

head -1000 csv | tail - 50 => Shows lines 951-1000

tail -50 csv | head -20 => Shows the first 20 of the last 50 lines of the file

With head and tail you can access any lines in the csv you want, but it gives all of the columns. That can be difficult to parse through if the csv you’re dealing with has a lot of columns, or a lot of content in the columns. Luckily we can use the cut command to filter for the columns you want

cut -d , -f 1 csv => Shows you the first column of the csv. NOTE: the -d flag specifies the delimiter. You can specifiy any character. The default is the TAB character so if you’re working with a tsv (tab separated values) you can omit the -d flag entirely. On the other hand, cut is not sophisticated, so if you csv has commas in the values then it might return incorrect values. This works best for large csvs that don’t have commas in the values. Okay, back to more copy-pastable commands!

cut -d , -f 2,5,10 csv => Shows the 2nd, 5th, and 10th columns

If you’re working with a large csv you might have realized that this isn’t particularly useful because it prints out every line. However we can combine this with head and tail and then we can really start to see any part of the csv we want!

cut -d , -f 2,4 csv | head -20 => View columns 2 and 4 but only the first 20 lines

head -20 csv | cut -d , -f 2,4 => Same as above, but this should run faster b/c head will run first which will significantly reduce the amount of data cut has to sort through. However these commands can run so fast sometimes that it often doesn’t matter. Unless your csvs are pushing the 100MB ranges you probably won’t notice a difference

Let’s put them all together

head -1000 csv | tail -50 | cut -d , -f 10,15 => View columns 10 and 15 of lines 951 to 1000

There’s plenty more options to uncover. Run man cut to view them all. There are a few other commands you can put in there too which can be helpful, such as sort.

LEFT OUTER JOIN and LEFT JOIN are the same thing. OUTER is just syntactic sugar. LEFT JOIN and RIGHT JOIN are identical if you swap the order of the tables you’re joining:

table_a LEFT JOIN table_b ON (conditions) is the same as:

Here’s a great tip from the PostgreSQL docs:

Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage inPostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage inPostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

Tip: There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage inPostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

I pasted it 3 times so that maybe you’ll read it three times now and never have to google it every time you want to make a new varchar or text column like me.

Redshift does not support Postgres style DISTINCT ON, but you can get the same thing with a nested window function. Here’s a great gist explaining how to do it

In postgres there is a small difference between the COPY command and the \copy command. \copy (and every other command that starts with a \) is a client command, meaning that the psql program interprets it instead of just sending it to the postgres server. Like all the other client commands (like \d and \e) it is delimited by a newline, not a semi colon. If you’re like me and you’re trying to save the output of a complicated query to a CSV then you’ve probably realized that it’s really frustrating that you can’t have newlines in your query when you put them in a \copy command. You spend 30 minutes writing this query that joins 3 tables and spans 30+ lines, and then you have to remove all the newlines that make it readable so you can put it in a \copy command. Maybe that’s not you, maybe you have a different (or better?) workflow, but I don’t care. This is what I have to deal with. But fortunately, I’ve found a better way!!

Just use COPY instead!

COPY (

select a, b c

from some_table

where some_condition

order by a

) TO STDOUT WITH CSV;

If you replace STDOUT with a filename (like you would do if you were using \copy) postgres would write a file on the server that postgres is running on. Not only does that require root access, that’s also probably not what you want. You want the file on your computer, so unless you’re running a production database server on your computer (or you’re ssh-ing directly into the db server) you won’t get it.

You can combine this with the -f flag in psql to make it even easier. Just put you query (COPY (select ...) TO STDOUT WITH CSV;) into a file, say my_query.sql and run this:

psql -f my_query.sql postgres://my-db-url > output.csv

Unfortunately this won’t work in read only databases

I wrote some code, wrote some tests to test it, made them all pass, and then pushed it to production. Then I decided to test it live on production, and guess what? The new feature didn’t work! But how? I wrote tests! I’ll spare you the suspense. I mistyped a method and I also mistyped the method in the tests that ensured the method was called correctly. Normally rspec saves you from this. If you run

expect(whatever).to receive(:some_method)

rspec will raise an error if whatever does not implement some_method. However the code I was testing uses DelayedJob, meaning I have to write a slightly more complicated expectation to ensure that methods are called using DelayedJob.

If I want to test this code:

MyClass.delay.my_method(my_args)

I set up my expectations like so:

delayed = double

allow(MyClass).to receive(:delay) { delayed }

expect(delayed).to receive(:my_method).with(my_args)

This tests that the method chain MyClass.delay.my_method(my_args) is called, but since we basically replaced MyClass with a generic rspec double, it no longer ensures that MyClass implements :my_method! So if you accidentally coded

MyClass.delay.not_a_method(my_args)

Your tests would still pass (given that you also used :not_a_method in the expectation)! And that’s exactly what I did. I typed the method :send_welcome instead of :send_welcome_email and I didn’t figure it out until I tested it on production.

The solution is to use

delayed = class_double(MyClass)

allow(MyClass).to receive(:delay) { delayed }

expect(delayed).to receive(:my_method).with(my_args)

Now if you expect the double object to receive a method rspec will raise an error if MyClass does not implement it.

Don’t add data to the database (use db/seeds.rb)

Don’t rely on data in the database (what if the data isn’t there, like on a dev box)

Don’t use specific gems. (What if the gem is removed in the future?)

Think about your new tables for a minute and add useful indexes

\copy (select * from foo) to ‘your_file.csv’ with csv

Since this uses the meta command “\copy” the command has to be run on 1 line. If you have a large query you have to remove all the new lines.

More Info:

http://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file

I ALWAYS forget what the syntax. So here’s a copy-pastable line to get you started

stub_request(:post, "whatever.com").with(headers: {}, body: {}).to_return(status: 200, body: "", headers: {})

Today I’m phoning it in from home. This SO post is all you need to know about sending arguments to a rake task.

If you’re comparing 2 branches in git you can see which commits are on one vs the other with “git log branch1..branch2″

If you’re like me, you always forget if this will show you the commits in branch1 that aren’t in branch2 or vice versa. Fortunately I’ve thought of a good way to remember.

I am pretty bad at keeping track of my config files and settings across multiple computers and devices. I am slowly getting better at it, but for now I still find pages like this super useful because I always forget what I had before. If you just got a new Mac computer and your git is all weird with default parameters, try running through this. It will probably make your life easier.

https://gist.github.com/trey/2722934

I also added this line to the “[color status]” options

#destroy runs callbacks on your model (before, around, and after_destroy)

#delete does not.

It’s that simple.

Here’s a StackOverflow answer that explains it well

http://stackoverflow.com/questions/22757450/difference-between-destroy-and-delete/22757533#22757533

And if you’re wondering what #around_destroy is, well I was curious about that too

Here's a guide with examples because I ALWAYS forget

Here's a CSV

name,age,gender "barbara","22","F" "george","60","M" "alex ""the yellow dart"" robinson","25","M" "felicity","55","F"

A couple of things to note: It's all escaped with double quotes, yay. "" is an escaped double quote, so we can still put double quotes in there The headers are there, but we wont use the headers when we copy into psql

STEP 1, CREATE TABLE create table persons (name varchar, age integer, gender varchar);

STEP 2, REMOVE THE HEADERS Let's store this as a file called "persons_data.csv"

"barbara","22","F" "george","60","M" "alex ""the yellow dart"" robinson","25","M" "felicity","55","F"

STEP 3, COPY IT IN psql -d your_db_name \copy persons(name, age, gender) from "./persons_data.csv" delimiter ',' csv;

SO EASY RIGHT? Pay attention to the column names. Basically you just copy the headers and put them there. That way it knows which columns to use. If you don't, it will default to the order that you created the columns in.

If you don't want to remove the headers, you can add HEADER after CSV, like this: \copy persons(name, age, gender) from "./persons_data.csv" delimiter ',' csv header; That will make it ignore the first row