Redshift Table Architectures

create_tables.sql

/*
  this first set of tables explicitly defines
  foreign-to-primary-key relationships
  in addition to shared distribution key
 */
drop table if exists public.event_rel;
create table public.event_rel (
  created_at      timestamp       encode raw
  , event_type    varchar(200)    encode text32k
  , license_slug  varchar(18)     encode raw
  , instance_slug varchar(36)     encode raw
  , user_id       integer         encode raw
  , ip_address    varchar(15)     encode lzo
  , agent         varchar(600)    encode lzo
  , uri           varchar(10000)  encode lzo
  , foreign key(instance_slug) references public.instance_rel(slug))
distkey(instance_slug)
sortkey(created_at);

insert into public.event_rel (
  select created_at
    , event_type
    , license_slug
    , looker_instance_slug
    , user_id
    , ip_address
    , agent
    , uri
  from pinger.event
  where created_at between '2016-01-01'::timestamp and '2016-03-01'::timestamp);

create table public.instance_rel (
  slug          varchar(36)   encode raw
  , created_at  timestamp     encode delta
  , host_url    varchar(80)   encode lzo
  , primary key(slug))
distkey(slug)
sortkey(slug);

insert into public.instance_rel (
  select 
    slug
    , created_at
    , host_url
  from (select row_number() over(partition by slug order by created_at desc) as sequence
          , slug
          , created_at
          , host_url
        from license.looker_instance)
  where sequence = 1
  and slug is not null);


/*
  this next set of tables omits any
  foreign-to-primary-key relationships but
  does specify a shared distribution key
 */
drop table if exists public.event_no_rel;
create table public.event_no_rel (
  created_at      timestamp       encode raw
  , event_type    varchar(200)    encode text32k
  , license_slug  varchar(18)     encode raw
  , instance_slug varchar(36)     encode raw
  , user_id       integer         encode raw
  , ip_address    varchar(15)     encode lzo
  , agent         varchar(600)    encode lzo
  , uri           varchar(10000)  encode lzo)
distkey(instance_slug)
sortkey(created_at);

insert into public.event_no_rel (
  select *
  from public.event_rel);

drop table if exists public.instance_no_rel;
create table public.instance_no_rel (
  slug          varchar(36)   encode raw
  , created_at  timestamp     encode delta
  , host_url    varchar(80)   encode lzo)
distkey(slug)
sortkey(slug);

insert into public.instance_no_rel (
  select *
  from public.instance_rel);


/*
  this set of tables distributes events evenly,
  the instance table on all, and includes 
  foreign-to-primary-key relationships.
 */
drop table if exists public.event_all_rel;
create table public.event_all_rel (
  created_at      timestamp       encode raw
  , event_type    varchar(200)    encode text32k
  , license_slug  varchar(18)     encode raw
  , instance_slug varchar(36)     encode raw
  , user_id       integer         encode raw
  , ip_address    varchar(15)     encode lzo
  , agent         varchar(600)    encode lzo
  , uri           varchar(10000)  encode lzo
  , foreign key(instance_slug) references public.instance_rel(slug))
diststyle even
sortkey(created_at);

insert into public.event_all_rel (
  select *
  from public.event_rel);

drop table if exists public.instance_all_rel;
create table public.instance_all_rel (
  slug          varchar(36)   encode raw
  , created_at  timestamp     encode delta
  , host_url    varchar(80)   encode lzo
  , primary key(slug))
diststyle all
sortkey(slug);

insert into public.instance_all_rel (
  select *
  from public.instance_rel);


/*
  this set of tables distributes events evenly,
  the instance table on all, and includes 
  foreign-to-primary-key relationships.
 */
drop table if exists public.event_all_no_rel;
create table public.event_all_no_rel (
  created_at      timestamp       encode raw
  , event_type    varchar(200)    encode text32k
  , license_slug  varchar(18)     encode raw
  , instance_slug varchar(36)     encode raw
  , user_id       integer         encode raw
  , ip_address    varchar(15)     encode lzo
  , agent         varchar(600)    encode lzo
  , uri           varchar(10000)  encode lzo)
diststyle even
sortkey(created_at);

insert into public.event_all_no_rel (
  select *
  from public.event_rel);

drop table if exists public.instance_all_no_rel;
create table public.instance_all_no_rel (
  slug          varchar(36)   encode raw
  , created_at  timestamp     encode delta
  , host_url    varchar(80)   encode lzo)
diststyle all
sortkey(slug);

insert into public.instance_all_no_rel (
  select *
  from public.instance_rel);


/*
  this set of tables distributes events evenly,
  the instance table evenly, and includes 
  foreign-to-primary-key relationships.
 */
drop table if exists public.event_even_rel;
create table public.event_even_rel (
  created_at      timestamp       encode raw
  , event_type    varchar(200)    encode text32k
  , license_slug  varchar(18)     encode raw
  , instance_slug varchar(36)     encode raw
  , user_id       integer         encode raw
  , ip_address    varchar(15)     encode lzo
  , agent         varchar(600)    encode lzo
  , uri           varchar(10000)  encode lzo
  , foreign key(instance_slug) references public.instance_even_rel(slug))
diststyle even
sortkey(created_at);

insert into public.event_even_rel (
  select *
  from public.event_rel);

drop table if exists public.instance_even_rel;
create table public.instance_even_rel (
  slug          varchar(36)   encode raw
  , created_at  timestamp     encode delta
  , host_url    varchar(80)   encode lzo
  , primary key(slug))
diststyle even
sortkey(slug);

insert into public.instance_even_rel (
  select *
  from public.instance_rel);