drill for amazon spot prices

queries.txt

/*
I'm interested in the EC2 instance types with the most stable spot price.
I want to give myself the best chance of having long-running instances at
the lowest possible price.

I'll get my data via the AWS CLI: 

aws ec2 describe-spot-price-history --product-description "Linux/UNIX" --output text > /tmp/spot_price_history.tsv

Not sure Excel will do too well with this much data, so I'll give Drill a shot.

First, let's explore the data, to see what's there. We'll do a "select *" with a limit to get an idea of the data.
*/

select * from dfs.`/tmp/spot_price_history.tsv` limit 50;

/*
Okay, I'm interested in the zone, price and instance type. Let's add some names to the columns.
*/

select 
  columns[1] as zone, 
  columns[2] as instancetype, 
  columns[3] as description, 
  cast(columns[4] AS FLOAT) as price, 
  columns[5] as stamp 
from dfs.`/tmp/spot_price_history.tsv`;

/*
Provides some nicely formatted columns.
Let's get the min, max and average prices.
*/

select zone,instancetype,min(price) as min_price,avg(price) as avg_price,max(price) as max_price, variance(price) as price_variance
from (
  select 
    columns[1] as zone, 
    columns[2] as instancetype, 
    columns[3] as description, 
    cast(columns[4] AS FLOAT) as price, 
    columns[5] as stamp 
  from dfs.`/tmp/spot_price_history.tsv`
  )
 where instancetype in ('m3.large', 'm1.xlarge', 'm3.xlarge', 'm3.2xlarge', 'c3.2xlarge', 'hi1.4xlarge') group by zone,instancetype order by price_variance asc;