Tracker V3 migration - Githubissues

dioptre commented 4 years ago

@cyberjunk here is the new schema:

CREATE KEYSPACE sfpla WITH replication = {'class': 'NetworkTopologyStrategy', 'DC1': '1'}  AND durable_writes = true;

CREATE TYPE sfpla.geo_point (
    lat double,
    lon double
);

CREATE TYPE sfpla.geo_pol (
    country text,
    region text,
    county text,
    city text
);

CREATE TYPE sfpla.payment (
    invid timeuuid,
    invoiced timestamp,
    product text,
    pid timeuuid,
    pcat text,
    man text,
    model text,
    qty double,
    duration text,
    starts date,
    ends date,
    price double,
    discount double,
    revenue double,
    margin double,
    cost double,
    tax double,
    tax_rate double,
    commission double,
    referral double,
    fees double,
    subtotal double,
    total double,
    payment double,
    paid timestamp
);

CREATE TYPE sfpla.viewport (
    w bigint,
    h bigint
);

CREATE TABLE sfpla.redirects (
    urlfrom text PRIMARY KEY,
    hhash text,
    updated timestamp,
    updater timeuuid,
    urlto text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.logs (
    id timeuuid PRIMARY KEY,
    created timestamp,
    host text,
    hostname text,
    ip inet,
    iphash text,
    ldate date,
    level int,
    ltime time,
    msg text,
    name text,
    owner timeuuid,
    params map<text, text>,
    topic text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.events_recent (
    eid timeuuid PRIMARY KEY,
    app text,
    auth timeuuid,
    bhash text,
    campaign text,
    cflags bigint,
    city text,
    country text,
    created timestamp,
    duration bigint,
    ename text,
    etyp text,
    hhash text,
    ip inet,
    iphash text,
    last text,
    latlon frozen<geo_point>,
    medium text,
    nparams map<text, double>,
    params map<text, text>,
    payment frozen<payment>,
    ptyp text,
    region text,
    rel text,
    relation text,
    rid timeuuid,
    score double,
    sid timeuuid,
    sink text,
    source text,
    split text,
    targets map<text, frozen<set<text>>>,
    term text,
    uid timeuuid,
    url text,
    ver int,
    vid timeuuid,
    xid text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 2678400
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.dailies (
    ip inet,
    day date,
    total counter,
    PRIMARY KEY (ip, day)
) WITH CLUSTERING ORDER BY (day DESC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.agreed (
    vid timeuuid,
    created timestamp,
    aff text,
    app text,
    avid timeuuid,
    bhash text,
    browser text,
    campaign text,
    cflags bigint,
    compliances map<text, frozen<set<text>>>,
    country text,
    culture text,
    device text,
    fbid text,
    gaid text,
    hhash text,
    idfa text,
    ip inet,
    iphash text,
    latlon frozen<geo_point>,
    loc frozen<geo_pol>,
    medium text,
    msid text,
    org timeuuid,
    os text,
    owner timeuuid,
    rcode text,
    ref timeuuid,
    region text,
    rel text,
    sid timeuuid,
    source text,
    term text,
    tz text,
    uid timeuuid,
    url text,
    vp frozen<viewport>,
    zip text,
    PRIMARY KEY (vid, created)
) WITH CLUSTERING ORDER BY (created DESC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.locations (
    hhash text,
    vid timeuuid,
    latlon frozen<geo_point>,
    sid timeuuid,
    uid timeuuid,
    PRIMARY KEY ((hhash, vid, latlon))
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.actions (
    sid timeuuid,
    did timeuuid,
    completed timestamp,
    created timestamp,
    dsrc text,
    exqid timeuuid,
    meta map<text, text>,
    src text,
    started timestamp,
    PRIMARY KEY (sid, did)
) WITH CLUSTERING ORDER BY (did ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.outcomes (
    hhash text,
    outcome text,
    sink text,
    url text,
    created date,
    total counter,
    PRIMARY KEY ((hhash, outcome), sink, url, created)
) WITH CLUSTERING ORDER BY (sink ASC, url ASC, created ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.ips (
    hhash text,
    ip inet,
    total counter,
    PRIMARY KEY ((hhash, ip))
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.browsers (
    hhash text,
    bhash text,
    browser text,
    total counter,
    PRIMARY KEY ((hhash, bhash), browser)
) WITH CLUSTERING ORDER BY (browser ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.accounts (
    uid timeuuid PRIMARY KEY,
    created timestamp,
    creds map<text, frozen<map<text, text>>>,
    expires timestamp,
    ip inet,
    msg text,
    owner timeuuid,
    pwd text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.redirect_history (
    hostfrom text,
    updated timestamp,
    hostto text,
    pathto text,
    searchto text,
    slugfrom text,
    updater timeuuid,
    urlfrom text,
    urlto text,
    PRIMARY KEY (hostfrom, updated)
) WITH CLUSTERING ORDER BY (updated DESC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';
CREATE INDEX hostto_redirect_history_idx ON sfpla.redirect_history (hostto);

CREATE TABLE sfpla.sequences (
    name text PRIMARY KEY,
    seq int
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.referrers (
    hhash text,
    url text,
    total counter,
    PRIMARY KEY ((hhash, url))
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.aliases (
    hhash text,
    vid timeuuid,
    uid timeuuid,
    sid timeuuid,
    PRIMARY KEY ((hhash, vid), uid)
) WITH CLUSTERING ORDER BY (uid ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.usernames (
    hhash text,
    uhash text,
    vid timeuuid,
    sid timeuuid,
    PRIMARY KEY ((hhash, uhash), vid)
) WITH CLUSTERING ORDER BY (vid ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.jurisdictions (
    regulation text,
    compliance text,
    seq int,
    created timestamp,
    locs frozen<set<frozen<geo_pol>>>,
    rules map<text, text>,
    PRIMARY KEY (regulation, compliance, seq)
) WITH CLUSTERING ORDER BY (compliance ASC, seq DESC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';
CREATE INDEX locs_jurisdictions_idx ON sfpla.jurisdictions (full(locs));

CREATE TABLE sfpla.action_names (
    name text PRIMARY KEY
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.agreements (
    vid timeuuid PRIMARY KEY,
    aff text,
    app text,
    avid timeuuid,
    bhash text,
    browser text,
    campaign text,
    cflags bigint,
    compliances map<text, frozen<set<text>>>,
    country text,
    created timestamp,
    culture text,
    device text,
    fbid text,
    gaid text,
    hhash text,
    idfa text,
    ip inet,
    iphash text,
    latlon frozen<geo_point>,
    loc frozen<geo_pol>,
    medium text,
    msid text,
    org timeuuid,
    os text,
    owner timeuuid,
    rcode text,
    ref timeuuid,
    region text,
    rel text,
    sid timeuuid,
    source text,
    term text,
    tz text,
    uid timeuuid,
    url text,
    vp frozen<viewport>,
    zip text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.actions_ext (
    sid text,
    svc text,
    created timestamp,
    iid timeuuid,
    meta map<text, text>,
    uid timeuuid,
    updated timestamp,
    PRIMARY KEY (sid, svc)
) WITH CLUSTERING ORDER BY (svc ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 1209600
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.nodes (
    hhash text,
    vid timeuuid,
    iphash text,
    ip inet,
    sid timeuuid,
    uid timeuuid,
    PRIMARY KEY ((hhash, vid), iphash)
) WITH CLUSTERING ORDER BY (iphash ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.events (
    eid timeuuid PRIMARY KEY,
    app text,
    auth timeuuid,
    bhash text,
    campaign text,
    cflags bigint,
    city text,
    country text,
    created timestamp,
    duration bigint,
    ename text,
    etyp text,
    hhash text,
    ip inet,
    iphash text,
    last text,
    latlon frozen<geo_point>,
    medium text,
    nparams map<text, double>,
    params map<text, text>,
    payment frozen<payment>,
    ptyp text,
    region text,
    rel text,
    relation text,
    rid timeuuid,
    score double,
    sid timeuuid,
    sink text,
    source text,
    split text,
    targets map<text, frozen<set<text>>>,
    term text,
    uid timeuuid,
    url text,
    ver int,
    vid timeuuid,
    xid text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.counters (
    id text PRIMARY KEY,
    total counter
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.tlvv (
    hhash text,
    vid timeuuid,
    orid timeuuid,
    created timestamp,
    org timeuuid,
    owner timeuuid,
    paid double,
    payments set<frozen<payment>>,
    sid timeuuid,
    uid timeuuid,
    updated timestamp,
    updater timeuuid,
    PRIMARY KEY ((hhash, vid), orid)
) WITH CLUSTERING ORDER BY (orid ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.cohorts (
    name text PRIMARY KEY,
    completed timestamp,
    created timestamp,
    imported int,
    owner timeuuid,
    started timestamp,
    uids_url text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.tlvu (
    hhash text,
    uid timeuuid,
    orid timeuuid,
    created timestamp,
    org timeuuid,
    owner timeuuid,
    paid double,
    payments set<frozen<payment>>,
    sid timeuuid,
    updated timestamp,
    updater timeuuid,
    vid timeuuid,
    PRIMARY KEY ((hhash, uid), orid)
) WITH CLUSTERING ORDER BY (orid ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.users (
    hhash text,
    uid timeuuid,
    vid timeuuid,
    sid timeuuid,
    PRIMARY KEY ((hhash, uid), vid)
) WITH CLUSTERING ORDER BY (vid ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.sessions (
    vid timeuuid,
    sid timeuuid,
    aff text,
    app text,
    auth timeuuid,
    bhash text,
    browser text,
    campaign text,
    cflags bigint,
    city text,
    country text,
    created timestamp,
    culture text,
    device text,
    did text,
    duration bigint,
    ename text,
    etyp text,
    fbid text,
    gaid text,
    hhash text,
    idfa text,
    ip inet,
    iphash text,
    last text,
    latlon frozen<geo_point>,
    medium text,
    msid text,
    nparams map<text, double>,
    os text,
    params map<text, text>,
    ptyp text,
    rcode text,
    ref timeuuid,
    region text,
    rel text,
    score double,
    sink text,
    source text,
    split text,
    term text,
    tz text,
    uid timeuuid,
    url text,
    ver int,
    vp frozen<viewport>,
    xid text,
    PRIMARY KEY (vid, sid)
) WITH CLUSTERING ORDER BY (sid DESC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.affiliates (
    hhash text,
    vid timeuuid,
    aff text,
    PRIMARY KEY ((hhash, vid))
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.visitors (
    vid timeuuid PRIMARY KEY,
    aff text,
    app text,
    auth timeuuid,
    bhash text,
    browser text,
    campaign text,
    cflags bigint,
    city text,
    country text,
    created timestamp,
    culture text,
    device text,
    did text,
    ename text,
    etyp text,
    fbid text,
    gaid text,
    hhash text,
    idfa text,
    ip inet,
    iphash text,
    last text,
    latlon frozen<geo_point>,
    medium text,
    msid text,
    nparams map<text, double>,
    os text,
    params map<text, text>,
    ptyp text,
    rcode text,
    ref timeuuid,
    region text,
    rel text,
    score double,
    sid timeuuid,
    sink text,
    source text,
    split text,
    term text,
    tz text,
    uid timeuuid,
    url text,
    ver int,
    vp frozen<viewport>,
    xid text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.updates (
    id text PRIMARY KEY,
    msg text,
    updated timestamp
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.services (
    name text PRIMARY KEY,
    created timestamp,
    expiry date,
    owner timeuuid,
    roles set<text>,
    secret text,
    updated timestamp,
    updater timeuuid
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.reqs (
    hhash text,
    vid timeuuid,
    total counter,
    PRIMARY KEY ((hhash, vid))
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.emails (
    hhash text,
    ehash text,
    sid timeuuid,
    vid timeuuid,
    PRIMARY KEY ((hhash, ehash))
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.hits (
    hhash text,
    url text,
    total counter,
    PRIMARY KEY ((hhash, url))
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.referrals (
    hhash text,
    vid timeuuid,
    gen int,
    ref timeuuid,
    PRIMARY KEY ((hhash, vid))
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.tlv (
    hhash text,
    uid timeuuid,
    created timestamp,
    org timeuuid,
    owner timeuuid,
    paid double,
    payments set<frozen<payment>>,
    sid timeuuid,
    updated timestamp,
    updater timeuuid,
    vid timeuuid,
    PRIMARY KEY ((hhash, uid))
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.countries (
    country text PRIMARY KEY,
    continent text,
    name text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.cells (
    hhash text,
    chash text,
    sid timeuuid,
    vid timeuuid,
    PRIMARY KEY ((hhash, chash))
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.messages (
    id timeuuid PRIMARY KEY,
    aff text,
    app text,
    auth text,
    campaign text,
    chashes set<text>,
    cohorts set<text>,
    completed timestamp,
    created timestamp,
    ehashes set<text>,
    medium text,
    owner timeuuid,
    promo text,
    ptyp text,
    ref timeuuid,
    rel text,
    repl map<text, text>,
    schedule timestamp,
    score double,
    sink text,
    source text,
    split double,
    splitn text,
    started timestamp,
    subject text,
    template text,
    term text,
    updated timestamp,
    updater timeuuid,
    ver int,
    xid text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.routed (
    hhash text,
    ip inet,
    url text,
    PRIMARY KEY ((hhash, ip))
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.hosts (
    hhash text,
    hostname text,
    PRIMARY KEY (hhash, hostname)
) WITH CLUSTERING ORDER BY (hostname ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.geo_ip (
    ipc text,
    ips varint,
    ipe varint,
    city text,
    country text,
    ipie inet,
    ipis inet,
    lat double,
    lon double,
    region text,
    tz text,
    zip text,
    PRIMARY KEY (ipc, ips, ipe)
) WITH CLUSTERING ORDER BY (ips ASC, ipe ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.queues (
    id timeuuid PRIMARY KEY,
    completed timestamp,
    created timestamp,
    host text,
    ip text,
    owner timeuuid,
    schedule timestamp,
    sid timeuuid,
    skey text,
    src text,
    started timestamp,
    updated timestamp,
    updater timeuuid
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';
CREATE INDEX queues_started_idx ON sfpla.queues (started);
CREATE INDEX queues_type_idx ON sfpla.queues (src);
CREATE INDEX queues_completed_idx ON sfpla.queues (completed);

CREATE TABLE sfpla.referred (
    hhash text,
    vid timeuuid,
    gen int,
    rcode text,
    PRIMARY KEY ((hhash, vid))
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

The old schema:

CREATE KEYSPACE sfpla WITH replication = {'class': 'NetworkTopologyStrategy', 'DC1': '1', 'DC2': '1'}  AND durable_writes = false;

CREATE TYPE sfpla.geo_point (
    lat double,
    lon double
);

CREATE TYPE sfpla.viewport (
    w bigint,
    h bigint
);

CREATE TABLE sfpla.logs (
    id timeuuid PRIMARY KEY,
    created timestamp,
    host text,
    hostname text,
    ip inet,
    ldate date,
    level int,
    ltime time,
    msg text,
    name text,
    owner uuid,
    params map<text, text>,
    topic text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': '10'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';
CREATE CUSTOM INDEX elastic_logs_idx ON sfpla.logs () USING 'org.elassandra.index.ExtendedElasticSecondaryIndex';

CREATE TABLE sfpla.dailies (
    ip inet,
    day date,
    total counter,
    PRIMARY KEY (ip, day)
) WITH CLUSTERING ORDER BY (day DESC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.locations (
    vid timeuuid,
    latlon frozen<geo_point>,
    sid timeuuid,
    uid uuid,
    PRIMARY KEY ((vid, latlon))
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.outcomes (
    outcome text,
    sink text,
    url text,
    created date,
    total counter,
    PRIMARY KEY (outcome, sink, url, created)
) WITH CLUSTERING ORDER BY (sink ASC, url ASC, created ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.ips (
    ip inet PRIMARY KEY,
    total counter
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.browsers (
    bhash text,
    browser text,
    total counter,
    PRIMARY KEY (bhash, browser)
) WITH CLUSTERING ORDER BY (browser ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.sequences (
    name text PRIMARY KEY,
    seq int
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.referrers (
    url text PRIMARY KEY,
    total counter
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.aliases (
    vid timeuuid,
    uid uuid,
    sid timeuuid,
    PRIMARY KEY (vid, uid)
) WITH CLUSTERING ORDER BY (uid ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': '100'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.usernames (
    uhash text,
    vid timeuuid,
    sid timeuuid,
    PRIMARY KEY (uhash, vid)
) WITH CLUSTERING ORDER BY (vid ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.nodes (
    vid timeuuid,
    ip inet,
    sid timeuuid,
    uid uuid,
    PRIMARY KEY (vid, ip)
) WITH CLUSTERING ORDER BY (ip ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.events (
    eid timeuuid PRIMARY KEY,
    app text,
    auth uuid,
    bhash text,
    created timestamp,
    duration bigint,
    ename text,
    etyp text,
    ip inet,
    last text,
    latlon frozen<geo_point>,
    params map<text, text>,
    ptyp text,
    rel text,
    rid uuid,
    score double,
    sid timeuuid,
    sink text,
    split text,
    targets map<text, frozen<set<text>>>,
    uid uuid,
    url text,
    ver int,
    vid timeuuid,
    xid text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': '10'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.counters (
    id text PRIMARY KEY,
    total counter
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.users (
    uid uuid,
    vid timeuuid,
    sid timeuuid,
    PRIMARY KEY (uid, vid)
) WITH CLUSTERING ORDER BY (vid ASC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.sessions (
    vid timeuuid,
    sid timeuuid,
    aff text,
    app text,
    auth uuid,
    bhash text,
    browser text,
    campaign text,
    country text,
    created timestamp,
    culture text,
    device text,
    duration bigint,
    ename text,
    etyp text,
    ip inet,
    last text,
    latlon frozen<geo_point>,
    medium text,
    os text,
    params map<text, text>,
    ptyp text,
    ref uuid,
    rel text,
    score double,
    sink text,
    source text,
    split text,
    term text,
    tz text,
    uid uuid,
    url text,
    ver int,
    vp frozen<viewport>,
    xid text,
    PRIMARY KEY (vid, sid)
) WITH CLUSTERING ORDER BY (sid DESC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.affiliates (
    vid timeuuid PRIMARY KEY,
    aff text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.visitors (
    vid timeuuid PRIMARY KEY,
    aff text,
    app text,
    auth uuid,
    bhash text,
    browser text,
    campaign text,
    country text,
    created timestamp,
    culture text,
    device text,
    ename text,
    etyp text,
    ip inet,
    last text,
    latlon frozen<geo_point>,
    medium text,
    os text,
    params map<text, text>,
    ptyp text,
    ref uuid,
    rel text,
    score double,
    sid timeuuid,
    sink text,
    source text,
    split text,
    term text,
    tz text,
    uid uuid,
    url text,
    ver int,
    vp frozen<viewport>,
    xid text
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.updates (
    id text PRIMARY KEY,
    msg text,
    updated timestamp
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.reqs (
    vid timeuuid PRIMARY KEY,
    total counter
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.emails (
    ehash text PRIMARY KEY,
    sid timeuuid,
    vid timeuuid
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.hits (
    url text PRIMARY KEY,
    total counter
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

CREATE TABLE sfpla.referrals (
    vid timeuuid PRIMARY KEY,
    gen int,
    ref uuid
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 86400
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';

Please take a look, and let's chat when you have time to work out next steps.

cyberjunk commented 4 years ago

@dioptre

Wonderful, I created the diff of both SFPLA keyspaces and started looking into the differences, let's discuss them here:

diff.txt old.txt new.txt

1) New Types

geo_pol
payment

--> Don't see any problem here.

2) New Tables

accounts
actions
actions_ext (<-- We got this in HD keyspace??)
action_names
agreed
agreements
cells
cohorts (<-- We got this in HD keyspace??)
countries
events_recent
geo_ip
hosts
jurisdictions
messages (<-- We got this in HD keyspace??)
queues
redirects
redirect_history
referred
routed
services
tlvu
tlvv
tlv

--> @dioptre : Please clarify: regarding the tables that we seem to have in HD keyspace instead. Will they be moved? Do we need to upgrade the HD table instead? Will they co-exist? Is there any case where a row in an old existing table A may rely on an row in a new table B (that we do not have, yet)?

3) Changed Tables

uuid -> timeuuid

You mentioned that change, literally all UUIDv4 were replaced by UUIDv1, they are both 128 Bits, but still quite different so how will we do this? Are we just going to keep the V4 uuids in the V1 timeuuid columns? Wouldn't that break? Trying to read the timestamp or the mac from a V4 will just give you garbage back.. so we could never fully use the timeuuid data because everything old would not be a timeuuid? Also the uuid got the version encoded (I made it bold below), so anything can (and may) figure out that these are not timeuuids.

V1: a9db38e2-e051-11ea-87d0-0242ac130003

V4: 61bab3cb-2a62-4f1c-b2de-587b4ddeeb64

Combined PKs (uuid+hash)

Several existing tables had their PK extended by an additional hhash value (e.g. tables ips, browsers, ...). How are we going to migrate these? When we try to insert our old records into these new tables, we will have to generate this hhash value for each exported row before we can import it into new schema

WIP

Some Thoughts

It might be easier to simply setup a new sfpla2 keyspace and switch to it and leave the old sfpla untouched? It's not perfectly nice with regards to superset etc. but it is a lot easier to setup a second keyspace in superset/jupyter etc. rather than to migrate this super huge old and quite incompatible sfpla data?

dioptre commented 4 years ago

Responses mate:

2) Nothing new needs to be ported to sfpla from hd. We should probably check that all the existing calls from hd code don't break in the new schema (I think everything should generally be additive and should be ok fingers crossed but lets try and break it/find any issues).

3) Everything that I setup should be using v1s (the only exception to this was the admin user who had 00000-0000-0000-00000 etc.) which I think we can ignore. If anyone has made this v4 tho now we are in trouble (easy to test though). Plan to check this is to nix every table except for events and do a migration for it and it alone (@hilsbos ?).

4) Should solve this in 3 (combined key is important now as we can measure related assets and control usage moving forward).

5) Yeah we could do a full migration, but I don't think we need to?!

cyberjunk commented 4 years ago

Nothing new needs to be ported to sfpla from hd. We should probably check that all the existing calls from hd code don't break in the new schema (I think everything should generally be additive and should be ok fingers crossed but lets try and break it/find any issues).

Ok. But I was asking: Why do we have some of the new sfpla tables in our hd keyspace instead (like cohorts, messages_ext, ..)? Do we have to create them in sfpla? Keep the hd ones? Delete them? Or replace the hd ones instead?

Everything that I setup should be using v1s (the only exception to this was the admin user who had 00000-0000-0000-00000 etc.) which I think we can ignore. If anyone has made this v4 tho now we are in trouble (easy to test though). Plan to check this is to nix every table except for events and do a migration for it and it alone (@hilsbos ?).

Ok. I just checked a few rows in sfpla.events, and yes, these have already V1 timeuuid stored in the V4 uuid columns. I really did not expect this, so I'm not sure if we did this everywhere that way (because the current column type uuid would actually be v4 not v1.. see here).

dioptre commented 4 years ago

I'd ignore the duplicate schema for now - originally it was intended to run from tracker, but I saw the data as mandatory given our emailer so put it in hd aswell for that reason (so we'd have production vs analytics separation)
Yeah I appreciate that, and for a single instance (messages - v4 was necessary at the time). I don't think anything else should be v4 (so is irrelevant HOPEFULLY for the move)

dioptre commented 4 years ago

I'm going to consider the indexes in events table over the weekend. How you feeling @cyberjunk ?

dioptre commented 4 years ago

@cyberjunk showing the differences between the old and current schema

1c1
< CREATE KEYSPACE sfpla WITH replication = {'class': 'NetworkTopologyStrategy', 'DC1': '1'}  AND durable_writes = true;
---
> CREATE KEYSPACE sfpla3 WITH replication = {'class': 'NetworkTopologyStrategy', 'DC1': '1', 'DC2': '1'}  AND durable_writes = true;
3c3
< CREATE TYPE sfpla.geo_point (
---
> CREATE TYPE sfpla3.geo_point (
8c8
< CREATE TYPE sfpla.geo_pol (
---
> CREATE TYPE sfpla3.geo_pol (
10d9
<     rcode text,
13,14c12
<     city text,
<     zip text
---
>     city text
17c15
< CREATE TYPE sfpla.payment (
---
> CREATE TYPE sfpla3.payment (
45c43
< CREATE TYPE sfpla.viewport (
---
> CREATE TYPE sfpla3.viewport (
50c48
< CREATE TABLE sfpla.redirects (
---
> CREATE TABLE sfpla3.redirects (
71c69
< CREATE TABLE sfpla.logs (
---
> CREATE TABLE sfpla3.logs (
99a98
> CREATE CUSTOM INDEX elastic_logs_idx ON sfpla3.logs () USING 'org.elassandra.index.ExtendedElasticSecondaryIndex';
101c100
< CREATE TABLE sfpla.events_recent (
---
> CREATE TABLE sfpla3.events_recent (
139,140c138
<     xid text,
<     zip text
---
>     xid text
149c147
<     AND gc_grace_seconds = 14400
---
>     AND gc_grace_seconds = 864000
154a153
> CREATE CUSTOM INDEX elastic_events_recent_idx ON sfpla3.events_recent () USING 'org.elassandra.index.ExtendedElasticSecondaryIndex';
156c155
< CREATE TABLE sfpla.dailies (
---
> CREATE TABLE sfpla3.dailies (
177c176
< CREATE TABLE sfpla.agreed (
---
> CREATE TABLE sfpla3.agreed (
233c232
< CREATE TABLE sfpla.locations (
---
> CREATE TABLE sfpla3.locations (
255c254
< CREATE TABLE sfpla.actions (
---
> CREATE TABLE sfpla3.actions (
282c281
< CREATE TABLE sfpla.outcomes (
---
> CREATE TABLE sfpla3.outcomes (
306c305
< CREATE TABLE sfpla.ips (
---
> CREATE TABLE sfpla3.ips (
326c325
< CREATE TABLE sfpla.browsers (
---
> CREATE TABLE sfpla3.browsers (
348c347
< CREATE TABLE sfpla.accounts (
---
> CREATE TABLE sfpla3.accounts (
372c371
< CREATE TABLE sfpla.redirect_history (
---
> CREATE TABLE sfpla3.redirect_history (
398c397
< CREATE INDEX hostto_redirect_history_idx ON sfpla.redirect_history (hostto);
---
> CREATE INDEX hostto_redirect_history_idx ON sfpla3.redirect_history (hostto);
400c399
< CREATE TABLE sfpla.sequences (
---
> CREATE TABLE sfpla3.sequences (
418c417
< CREATE TABLE sfpla.referrers (
---
> CREATE TABLE sfpla3.referrers (
438c437
< CREATE TABLE sfpla.aliases (
---
> CREATE TABLE sfpla3.aliases (
460c459
< CREATE TABLE sfpla.usernames (
---
> CREATE TABLE sfpla3.usernames (
482c481
< CREATE TABLE sfpla.jurisdictions (
---
> CREATE TABLE sfpla3.jurisdictions (
505c504
< CREATE INDEX locs_jurisdictions_idx ON sfpla.jurisdictions (full(locs));
---
> CREATE INDEX locs_jurisdictions_idx ON sfpla3.jurisdictions (full(locs));
507c506
< CREATE TABLE sfpla.action_names (
---
> CREATE TABLE sfpla3.action_names (
524c523
< CREATE TABLE sfpla.agreements (
---
> CREATE TABLE sfpla3.agreements (
578c577
< CREATE TABLE sfpla.actions_ext (
---
> CREATE TABLE sfpla3.actions_ext (
603c602
< CREATE TABLE sfpla.nodes (
---
> CREATE TABLE sfpla3.nodes (
627c626
< CREATE TABLE sfpla.events (
---
> CREATE TABLE sfpla3.events (
665,666c664
<     xid text,
<     zip text
---
>     xid text
682c680
< CREATE TABLE sfpla.counters (
---
> CREATE TABLE sfpla3.counters (
700c698
< CREATE TABLE sfpla.tlvv (
---
> CREATE TABLE sfpla3.tlvv (
730c728
< CREATE TABLE sfpla.cohorts (
---
> CREATE TABLE sfpla3.cohorts (
753c751
< CREATE TABLE sfpla.tlvu (
---
> CREATE TABLE sfpla3.tlvu (
783c781
< CREATE TABLE sfpla.users (
---
> CREATE TABLE sfpla3.users (
805c803
< CREATE TABLE sfpla.sessions (
---
> CREATE TABLE sfpla3.sessions (
853d850
<     zip text,
871,932c868
< CREATE TABLE sfpla.zips (
<     country text,
<     zip text,
<     asian double,
<     black double,
<     carpool double,
<     childpoverty double,
<     city text,
<     construction double,
<     county text,
<     created timestamp,
<     culture text,
<     drive double,
<     employed int,
<     familywork double,
<     hispanic double,
<     income double,
<     incomeerr double,
<     incomepercap double,
<     incomepercaperr double,
<     latlon frozen<geo_point>,
<     loc frozen<geo_pol>,
<     meancommute double,
<     men int,
<     native double,
<     office double,
<     othertransport double,
<     pacific double,
<     population int,
<     poverty double,
<     privatework double,
<     production double,
<     professional double,
<     publicwork double,
<     rcode text,
<     region text,
<     selfemployed double,
<     service double,
<     transit double,
<     unemployment double,
<     voters int,
<     walk double,
<     white double,
<     women int,
<     workathome double,
<     PRIMARY KEY ((country, zip))
< ) WITH bloom_filter_fp_chance = 0.01
<     AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
<     AND comment = ''
<     AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
<     AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
<     AND crc_check_chance = 1.0
<     AND dclocal_read_repair_chance = 0.1
<     AND default_time_to_live = 0
<     AND gc_grace_seconds = 60
<     AND max_index_interval = 2048
<     AND memtable_flush_period_in_ms = 0
<     AND min_index_interval = 128
<     AND read_repair_chance = 0.0
<     AND speculative_retry = '99PERCENTILE';
< 
< CREATE TABLE sfpla.affiliates (
---
> CREATE TABLE sfpla3.affiliates (
952c888
< CREATE TABLE sfpla.visitors (
---
> CREATE TABLE sfpla3.visitors (
998,999c934
<     xid text,
<     zip text
---
>     xid text
1015c950
< CREATE TABLE sfpla.updates (
---
> CREATE TABLE sfpla3.updates (
1034c969
< CREATE TABLE sfpla.services (
---
> CREATE TABLE sfpla3.services (
1058c993
< CREATE TABLE sfpla.reqs (
---
> CREATE TABLE sfpla3.reqs (
1078c1013
< CREATE TABLE sfpla.emails (
---
> CREATE TABLE sfpla3.emails (
1099c1034
< CREATE TABLE sfpla.hits (
---
> CREATE TABLE sfpla3.hits (
1119c1054
< CREATE TABLE sfpla.referrals (
---
> CREATE TABLE sfpla3.referrals (
1140c1075
< CREATE TABLE sfpla.tlv (
---
> CREATE TABLE sfpla3.tlv (
1168c1103
< CREATE TABLE sfpla.countries (
---
> CREATE TABLE sfpla3.countries (
1187c1122
< CREATE TABLE sfpla.cells (
---
> CREATE TABLE sfpla3.cells (
1208c1143
< CREATE TABLE sfpla.messages (
---
> CREATE TABLE sfpla3.messages (
1255c1190
< CREATE TABLE sfpla.routed (
---
> CREATE TABLE sfpla3.routed (
1275c1210
< CREATE TABLE sfpla.hosts (
---
> CREATE TABLE sfpla3.hosts (
1295c1230
< CREATE TABLE sfpla.geo_ip (
---
> CREATE TABLE sfpla3.geo_ip (
1325c1260
< CREATE TABLE sfpla.queues (
---
> CREATE TABLE sfpla3.queues (
1353,1355c1288,1290
< CREATE INDEX queues_started_idx ON sfpla.queues (started);
< CREATE INDEX queues_type_idx ON sfpla.queues (src);
< CREATE INDEX queues_completed_idx ON sfpla.queues (completed);
---
> CREATE INDEX queues_started_idx ON sfpla3.queues (started);
> CREATE INDEX queues_type_idx ON sfpla3.queues (src);
> CREATE INDEX queues_completed_idx ON sfpla3.queues (completed);
1357c1292
< CREATE TABLE sfpla.referred (
---
> CREATE TABLE sfpla3.referred (
1377d1311
<

ichliebedich / tracker

Tracker V3 migration #16

1) New Types

2) New Tables

3) Changed Tables

uuid -> timeuuid

Combined PKs (uuid+hash)

WIP

Some Thoughts