Share this page: Follow me to be notified of new articles:

GCE BigQuery vs AWS Redshift vs AWS Athena: schema

Original table schema

CREATE TABLE t (
    antiNucleus BIGINT,
    eventFile BIGINT,
    eventNumber BIGINT,
    eventTime DOUBLE PRECISION,
    histFile BIGINT,
    multiplicity BIGINT,
    NaboveLb BIGINT,
    NbelowLb BIGINT,
    NLb BIGINT,
    primaryTracks BIGINT,
    prodTime DOUBLE PRECISION,
    Pt FLOAT,
    runNumber BIGINT,
    vertexX FLOAT,
    vertexY FLOAT,
    vertexZ FLOAT
);

Optimized table schema

CREATE TABLE t (
    antinucleus BIGINT ENCODE zstd, 
    eventfile BIGINT ENCODE zstd, 
    eventnumber BIGINT ENCODE raw, 
    eventtime DOUBLE PRECISION ENCODE zstd, 
    histfile BIGINT ENCODE zstd, 
    multiplicity BIGINT ENCODE zstd, 
    nabovelb BIGINT ENCODE zstd, 
    nbelowlb BIGINT ENCODE zstd, 
    nlb BIGINT ENCODE zstd, 
    primarytracks BIGINT ENCODE zstd, 
    prodtime DOUBLE PRECISION ENCODE zstd, 
    pt FLOAT ENCODE zstd, 
    runnumber BIGINT ENCODE zstd, 
    vertexx FLOAT ENCODE zstd, 
    vertexy FLOAT ENCODE zstd, 
    vertexz FLOAT ENCODE zstd
) DISTSTYLE KEY DISTKEY (eventfile)
SORTKEY (eventnumber);
Back to article
Last update of this page: July 6, 2017
Please do not copy any of the content of this website without asking me first.