redshit.sql

create table flights
(
    year           smallint,
    month          smallint,
    day            smallint,
    carrier        varchar(80) distkey,
    origin         char(3),
    dest           char(3),
    aircraft_code  char(3),
    miles          int,
    departures     int,
    minutes        int,
    seats          int,
    passengers     int,
    freight_pounds int
);

copy flights
    from 's3://us-west-2-aws-training/awsu-spl/spl17-redshift/static/data/flights-usa'
    IAM_ROLE 'arn:aws:iam::187195534902:role/Redshift-Role'
    GZIP delimiter ',' removequotes region 'us-west-2';

select count(*)
from flights;

-- pick 10 random rows
select *
from flights
order by random()
limit 10;

select carrier, sum(departures)
from flights
group by carrier
order by 2 desc
limit 10;

select aircraft_code, sum(departures)
from flights
group by aircraft_code
order by 2 desc
limit 10;

create table aircraft
(
    aircraft_code char(3) sortkey,
    aircraft      varchar(100)
);

copy aircraft
    from 's3://us-west-2-aws-training/awsu-spl/spl17-redshift/static/data/lookup_aircraft.csv'
    iam_role 'arn:aws:iam::187195534902:role/Redshift-Role'
    ignoreheader 1
    delimiter ','
    removequotes
    truncatecolumns
    region 'us-west-2';

select *
from aircraft
order by random()
limit 10;

select aircraft, sum(departures) as trips
from flights
         join aircraft using (aircraft_code)
group by aircraft
order by trips desc
limit 10;

set enable_result_cache_for_session to off;

explain select aircraft, sum(departures) as trips
        from flights
                 join aircraft using (aircraft_code)
        group by aircraft
        order by trips desc
        limit 10;

analyze compression flights;

create table airports
(
    airport_code char(3) sortkey,
    airport      varchar(100)
);

copy airports from 's3://us-west-2-aws-training/awsu-spl/spl17-redshift/static/data/lookup_airports.csv'
    iam_role 'arn:aws:iam::187195534902:role/Redshift-Role'
    ignoreheader 1
    delimiter ','
    removequotes
    truncatecolumns region 'us-west-2';

create table vegas_flights
    distkey ( origin )
    sortkey ( origin )
as
select flights.*, airport
from flights
         join airports on origin = aircraft_code
where dest = 'LAS';

SELECT
  airport,
  to_char(SUM(passengers), '999,999,999') as passengers
FROM vegas_flights
GROUP BY airport
ORDER BY SUM(passengers) desc
LIMIT 10;

SELECT
  owner AS node,
  diskno,
  used,
  capacity,
  used/capacity::numeric * 100 as percent_used
FROM stv_partitions
WHERE host = node
ORDER BY 1, 2;

SELECT
  name,
  count(*)
FROM stv_blocklist
JOIN (SELECT DISTINCT name, id as tbl from stv_tbl_perm) USING (tbl)
GROUP BY name;