hits-snowflake-schema

hits-snowflake-schema

CREATE OR REPLACE TABLE hits
(
    WatchID                BIGINT NOT NULL,
    JavaEnable             SMALLINT NOT NULL,
    Title                  TEXT,
    GoodEvent              SMALLINT NOT NULL,
    EventTime              TIMESTAMP NOT NULL,
    EventDate              DATE NOT NULL,
    CounterID              INTEGER NOT NULL,
    ClientIP               INTEGER NOT NULL,
    RegionID               INTEGER NOT NULL,
    UserID                 BIGINT NOT NULL,
    CounterClass           SMALLINT NOT NULL,
    OS                     SMALLINT NOT NULL,
    UserAgent              SMALLINT NOT NULL,
    URL                    TEXT NULL,
    Referer                TEXT NULL,
    IsRefresh              SMALLINT NOT NULL,
    RefererCategoryID      SMALLINT NOT NULL,
    RefererRegionID        INTEGER NOT NULL,
    URLCategoryID          SMALLINT NOT NULL,
    URLRegionID            INTEGER NOT NULL,
    ResolutionWidth        SMALLINT NOT NULL,
    ResolutionHeight       SMALLINT NOT NULL,
    ResolutionDepth        SMALLINT NOT NULL,
    FlashMajor             SMALLINT NOT NULL,
    FlashMinor             SMALLINT NOT NULL,
    FlashMinor2            TEXT NULL,
    NetMajor               SMALLINT NOT NULL,
    NetMinor               SMALLINT NOT NULL,
    UserAgentMajor         SMALLINT NOT NULL,
    UserAgentMinor         VARCHAR(255) NOT NULL,
    CookieEnable           SMALLINT NOT NULL,
    JavascriptEnable       SMALLINT NOT NULL,
    IsMobile               SMALLINT NOT NULL,
    MobilePhone            SMALLINT NOT NULL,
    MobilePhoneModel       TEXT NULL,
    Params                 TEXT NULL,
    IPNetworkID            INTEGER NOT NULL,
    TraficSourceID         SMALLINT NOT NULL,
    SearchEngineID         SMALLINT NOT NULL,
    SearchPhrase           TEXT NULL,
    AdvEngineID            SMALLINT NOT NULL,
    IsArtifical            SMALLINT NOT NULL,
    WindowClientWidth      SMALLINT NOT NULL,
    WindowClientHeight     SMALLINT NOT NULL,
    ClientTimeZone         SMALLINT NOT NULL,
    ClientEventTime        TIMESTAMP NOT NULL,
    SilverlightVersion1    SMALLINT NOT NULL,
    SilverlightVersion2    SMALLINT NOT NULL,
    SilverlightVersion3    INTEGER NOT NULL,
    SilverlightVersion4    SMALLINT NOT NULL,
    PageCharset            TEXT NULL,
    CodeVersion            INTEGER NOT NULL,
    IsLink                 SMALLINT NOT NULL,
    IsDownload             SMALLINT NOT NULL,
    IsNotBounce            SMALLINT NOT NULL,
    FUniqID                BIGINT NOT NULL,
    OriginalURL            TEXT NULL,
    HID                    INTEGER NOT NULL,
    IsOldCounter           SMALLINT NOT NULL,
    IsEvent                SMALLINT NOT NULL,
    IsParameter            SMALLINT NOT NULL,
    DontCountHits          SMALLINT NOT NULL,
    WithHash               SMALLINT NOT NULL,
    HitColor               CHAR NOT NULL,
    LocalEventTime         TIMESTAMP NOT NULL,
    Age                    SMALLINT NOT NULL,
    Sex                    SMALLINT NOT NULL,
    Income                 SMALLINT NOT NULL,
    Interests              SMALLINT NOT NULL,
    Robotness              SMALLINT NOT NULL,
    RemoteIP               INTEGER NOT NULL,
    WindowName             INTEGER NOT NULL,
    OpenerName             INTEGER NOT NULL,
    HistoryLength          SMALLINT NOT NULL,
    BrowserLanguage        TEXT NULL,
    BrowserCountry         TEXT NULL,
    SocialNetwork          TEXT NULL,
    SocialAction           TEXT NULL,
    HTTPError              SMALLINT NOT NULL,
    SendTiming             INTEGER NOT NULL,
    DNSTiming              INTEGER NOT NULL,
    ConnectTiming          INTEGER NOT NULL,
    ResponseStartTiming    INTEGER NOT NULL,
    ResponseEndTiming      INTEGER NOT NULL,
    FetchTiming            INTEGER NOT NULL,
    SocialSourceNetworkID  SMALLINT NOT NULL,
    SocialSourcePage       TEXT NULL,
    ParamPrice             BIGINT NOT NULL,
    ParamOrderID           TEXT NULL,
    ParamCurrency          TEXT NULL,
    ParamCurrencyID        SMALLINT NOT NULL,
    OpenstatServiceName    TEXT NULL,
    OpenstatCampaignID     TEXT NULL,
    OpenstatAdID           TEXT NULL,
    OpenstatSourceID       TEXT NULL,
    UTMSource              TEXT NULL,
    UTMMedium              TEXT NULL,
    UTMCampaign            TEXT NULL,
    UTMContent             TEXT NULL,
    UTMTerm                TEXT NULL,
    FromTag                TEXT NULL,
    HasGCLID               SMALLINT NOT NULL,
    RefererHash            BIGINT NOT NULL,
    URLHash                BIGINT NOT NULL,
    CLID                   INTEGER NOT NULL
);

COPY INTO hits
FROM 's3://clickhouse-public-datasets/hits_compatible/hits.tsv.gz'
FILE_FORMAT = (
    TYPE = CSV,
    FIELD_DELIMITER = '\t',
    RECORD_DELIMITER = '\n',
    COMPRESSION = AUTO
);