Database Tools

These are up-to-date representations of our raw data pipeline pixel, as converted to various third-party database schemas. For an easy way to generate them, check out our parsely_raw_data open source project.

Redshift Schema

Column Example Type
action 'pageview' VARCHAR(256) NOT NULL
apikey 'mashable.com' VARCHAR(256) NOT NULL
campaign_id 'facebook_campaign' VARCHAR(256)
display True BOOLEAN
display_avail_height 735 INTEGER
display_avail_width 1280 INTEGER
display_pixel_depth 24 INTEGER
display_total_height 800 INTEGER
display_total_width 1280 INTEGER
engaged_time_inc INTEGER
event_id '0xe6508eda93d5598367b18...' VARCHAR(64) NOT NULL
extra_data JSON
flags_is_amp BOOLEAN
metadata True BOOLEAN
metadata_authors ['Laura Vitto'] VARCHAR(MAX)
metadata_canonical_url 'http://mashable.com/201...' VARCHAR(4096)
metadata_custom_metadata '{"site":"Mashable"}' VARCHAR(4096)
metadata_duration INTEGER
metadata_data_source 'crawl' VARCHAR(8)
metadata_full_content_word_count 174 INTEGER
metadata_image_url 'http://a.amz.mshcdn.com...' VARCHAR(4096)
metadata_page_type 'post' VARCHAR(256)
metadata_post_id 'http://mashable.com/201...' VARCHAR(4096)
metadata_pub_date_tmsp 1473275118000 BIGINT
metadata_save_date_tmsp 1473275204000 BIGINT
metadata_section 'watercooler' VARCHAR(256)
metadata_share_urls VARCHAR(MAX)
metadata_tags ['gadgets', 'iphone-7'] VARCHAR(MAX)
metadata_thumb_url 'https://images.parsely....' VARCHAR(4096)
metadata_title 'Everyone has the same f...' VARCHAR(4096)
metadata_urls ['http://mashable.com/2016/09/07/airpods-jokes/'] VARCHAR(MAX)
ref_category 'internal' VARCHAR(64)
ref_clean 'http://mashable.com/' VARCHAR(4096)
ref_domain 'mashable.com' VARCHAR(256)
ref_fragment '' VARCHAR(4096)
ref_netloc 'mashable.com' VARCHAR(256)
ref_params '' VARCHAR(4096)
ref_path '/' VARCHAR(4096)
ref_query '' VARCHAR(4096)
ref_scheme 'http' VARCHAR(64)
referrer 'http://mashable.com/' VARCHAR(4096)
session True BOOLEAN
session_id 6 INTEGER
session_initial_referrer 'http://mashable.com/' VARCHAR(4096)
session_initial_url 'http://mashable.com/' VARCHAR(4096)
session_last_session_timestamp 1473271351611 BIGINT
session_timestamp 1473277747806 BIGINT
slot False BOOLEAN
sref_category 'internal' VARCHAR(64)
sref_clean 'http://mashable.com/' VARCHAR(4096)
sref_domain 'mashable.com' VARCHAR(256)
sref_fragment '' VARCHAR(4096)
sref_netloc 'mashable.com' VARCHAR(256)
sref_params '' VARCHAR(4096)
sref_path '/' VARCHAR(4096)
sref_query '' VARCHAR(4096)
sref_scheme 'http' VARCHAR(64)
surl_clean 'http://mashable.com/' VARCHAR(4096)
surl_domain 'mashable.com' VARCHAR(256)
surl_fragment '' VARCHAR(4096)
surl_netloc 'mashable.com' VARCHAR(256)
surl_params '' VARCHAR(4096)
surl_path '/' VARCHAR(4096)
surl_query '' VARCHAR(4096)
surl_scheme 'http' VARCHAR(64)
timestamp_info True BOOLEAN
timestamp_info_nginx_ms 1473277850000 BIGINT NOT NULL
timestamp_info_override_ms BIGINT
timestamp_info_pixel_ms 1473277850017 BIGINT
ts_action '2016-09-07 19:50:50' TIMESTAMP
ts_session_current '2016-09-07 19:49:07' TIMESTAMP
ts_session_last '2016-09-07 18:02:31' TIMESTAMP
ua_browser 'Safari' VARCHAR(4096)
ua_browserversion '9.1.2' VARCHAR(4096)
ua_device 'Other' VARCHAR(4096)
ua_devicebrand None VARCHAR(4096)
ua_devicemodel None VARCHAR(4096)
ua_devicetouchcapable True BOOLEAN
ua_devicetype 'desktop' VARCHAR(4096)
ua_os 'Mac OS X' VARCHAR(4096)
ua_osversion '10.10.5' VARCHAR(4096)
url 'http://mashable.com/201...' VARCHAR(4096)
url_clean 'http://mashable.com/201...' VARCHAR(4096)
url_domain 'mashable.com' VARCHAR(256)
url_fragment 'L.eZPflSGqq5' VARCHAR(4096)
url_netloc 'mashable.com' VARCHAR(256)
url_params '' VARCHAR(4096)
url_path '/2016/09/07/airpods-jok...' VARCHAR(4096)
url_query '' VARCHAR(4096)
url_scheme 'http' VARCHAR(64)
utm_campaign 'facebook_campaign' VARCHAR(256)
utm_medium 'partners' VARCHAR(64)
utm_source 'facebook' VARCHAR(64)
utm_term '8098' VARCHAR(64)
utm_content 'sports' VARCHAR(256)
user_agent 'Mozilla/5.0 (Macintosh;...' VARCHAR(4096)
version 1 INTEGER
visitor True BOOLEAN
visitor_ip '108.225.131.20' VARCHAR(256)
visitor_network_id 'ac94fe31-a307-4020-9a23...' VARCHAR(128)
visitor_site_id 'ab94fd31-a207-4010-8a25...' VARCHAR(128) NOT NULL

Redshift DDL

CREATE TABLE parsely.rawdata (
        action                              VARCHAR(256) NOT NULL,   
        apikey                              VARCHAR(256) NOT NULL,   
        campaign_id                         VARCHAR(256),            
        display                             BOOLEAN,                 
        display_avail_height                INTEGER,                 
        display_avail_width                 INTEGER,                 
        display_pixel_depth                 INTEGER,                 
        display_total_height                INTEGER,                 
        display_total_width                 INTEGER,                 
        engaged_time_inc                    INTEGER,                 
        event_id                            VARCHAR(64) NOT NULL,    
        flags_is_amp                        BOOLEAN,                 
        metadata                            BOOLEAN,                 
        metadata_authors                    VARCHAR(MAX),            
        metadata_canonical_url              VARCHAR(4096),           
        metadata_custom_metadata            VARCHAR(4096),           
        metadata_duration                   INTEGER,                 
        metadata_data_source                VARCHAR(8),              
        metadata_full_content_word_count    INTEGER,                 
        metadata_image_url                  VARCHAR(4096),           
        metadata_page_type                  VARCHAR(256),            
        metadata_post_id                    VARCHAR(4096),           
        metadata_pub_date_tmsp              BIGINT,                  
        metadata_save_date_tmsp             BIGINT,                  
        metadata_section                    VARCHAR(256),            
        metadata_share_urls                 VARCHAR(MAX),            
        metadata_tags                       VARCHAR(MAX),            
        metadata_thumb_url                  VARCHAR(4096),           
        metadata_title                      VARCHAR(4096),           
        metadata_urls                       VARCHAR(MAX),            
        ref_category                        VARCHAR(64),             
        ref_clean                           VARCHAR(4096),           
        ref_domain                          VARCHAR(256),            
        ref_fragment                        VARCHAR(4096),           
        ref_netloc                          VARCHAR(256),            
        ref_params                          VARCHAR(4096),           
        ref_path                            VARCHAR(4096),           
        ref_query                           VARCHAR(4096),           
        ref_scheme                          VARCHAR(64),             
        referrer                            VARCHAR(4096),           
        session                             BOOLEAN,                 
        session_id                          INTEGER,                 
        session_initial_referrer            VARCHAR(4096),           
        session_initial_url                 VARCHAR(4096),           
        session_last_session_timestamp      BIGINT,                  
        session_timestamp                   BIGINT,                  
        slot                                BOOLEAN,                 
        sref_category                       VARCHAR(64),             
        sref_clean                          VARCHAR(4096),           
        sref_domain                         VARCHAR(256),            
        sref_fragment                       VARCHAR(4096),           
        sref_netloc                         VARCHAR(256),            
        sref_params                         VARCHAR(4096),           
        sref_path                           VARCHAR(4096),           
        sref_query                          VARCHAR(4096),           
        sref_scheme                         VARCHAR(64),             
        surl_clean                          VARCHAR(4096),           
        surl_domain                         VARCHAR(256),            
        surl_fragment                       VARCHAR(4096),           
        surl_netloc                         VARCHAR(256),            
        surl_params                         VARCHAR(4096),           
        surl_path                           VARCHAR(4096),           
        surl_query                          VARCHAR(4096),           
        surl_scheme                         VARCHAR(64),             
        timestamp_info                      BOOLEAN,                 
        timestamp_info_nginx_ms             BIGINT NOT NULL,         
        timestamp_info_override_ms          BIGINT,                  
        timestamp_info_pixel_ms             BIGINT,                  
        ts_action                           TIMESTAMP,               
        ts_session_current                  TIMESTAMP,               
        ts_session_last                     TIMESTAMP,               
        ua_browser                          VARCHAR(4096),           
        ua_browserversion                   VARCHAR(4096),           
        ua_device                           VARCHAR(4096),           
        ua_devicebrand                      VARCHAR(4096),           
        ua_devicemodel                      VARCHAR(4096),           
        ua_devicetouchcapable               BOOLEAN,                 
        ua_devicetype                       VARCHAR(4096),           
        ua_os                               VARCHAR(4096),           
        ua_osversion                        VARCHAR(4096),           
        url                                 VARCHAR(4096),           
        url_clean                           VARCHAR(4096),           
        url_domain                          VARCHAR(256),            
        url_fragment                        VARCHAR(4096),           
        url_netloc                          VARCHAR(256),            
        url_params                          VARCHAR(4096),           
        url_path                            VARCHAR(4096),           
        url_query                           VARCHAR(4096),           
        url_scheme                          VARCHAR(64),             
        utm_campaign                        VARCHAR(256),            
        utm_medium                          VARCHAR(64),             
        utm_source                          VARCHAR(64),             
        utm_term                            VARCHAR(64),             
        utm_content                         VARCHAR(256),            
        user_agent                          VARCHAR(4096),           
        version                             INTEGER,                 
        visitor                             BOOLEAN,                 
        visitor_ip                          VARCHAR(256),            
        visitor_network_id                  VARCHAR(128),            
        visitor_site_id                     VARCHAR(128) NOT NULL   
);

BigQuery Schema

Column Example Type
action 'pageview' STRING
apikey 'mashable.com' STRING
campaign_id 'facebook_campaign' STRING
display True BOOLEAN
display_avail_height 735 INTEGER
display_avail_width 1280 INTEGER
display_pixel_depth 24 INTEGER
display_total_height 800 INTEGER
display_total_width 1280 INTEGER
engaged_time_inc INTEGER
event_id '0xe6508eda93d5598367b18...' STRING
extra_data JSON
flags_is_amp BOOLEAN
metadata True BOOLEAN
metadata_authors ['Laura Vitto'] STRING (REPEATED)
metadata_canonical_url 'http://mashable.com/201...' STRING
metadata_custom_metadata '{"site":"Mashable"}' STRING
metadata_duration INTEGER
metadata_data_source 'crawl' STRING
metadata_full_content_word_count 174 INTEGER
metadata_image_url 'http://a.amz.mshcdn.com...' STRING
metadata_page_type 'post' STRING
metadata_post_id 'http://mashable.com/201...' STRING
metadata_pub_date_tmsp 1473275118000 INTEGER
metadata_save_date_tmsp 1473275204000 INTEGER
metadata_section 'watercooler' STRING
metadata_share_urls STRING (REPEATED)
metadata_tags ['gadgets', 'iphone-7'] STRING (REPEATED)
metadata_thumb_url 'https://images.parsely....' STRING
metadata_title 'Everyone has the same f...' STRING
metadata_urls ['http://mashable.com/2016/09/07/airpods-jokes/'] STRING (REPEATED)
ref_category 'internal' STRING
ref_clean 'http://mashable.com/' STRING
ref_domain 'mashable.com' STRING
ref_fragment '' STRING
ref_netloc 'mashable.com' STRING
ref_params '' STRING
ref_path '/' STRING
ref_query '' STRING
ref_scheme 'http' STRING
referrer 'http://mashable.com/' STRING
session True BOOLEAN
session_id 6 INTEGER
session_initial_referrer 'http://mashable.com/' STRING
session_initial_url 'http://mashable.com/' STRING
session_last_session_timestamp 1473271351611 INTEGER
session_timestamp 1473277747806 INTEGER
slot False BOOLEAN
sref_category 'internal' STRING
sref_clean 'http://mashable.com/' STRING
sref_domain 'mashable.com' STRING
sref_fragment '' STRING
sref_netloc 'mashable.com' STRING
sref_params '' STRING
sref_path '/' STRING
sref_query '' STRING
sref_scheme 'http' STRING
surl_clean 'http://mashable.com/' STRING
surl_domain 'mashable.com' STRING
surl_fragment '' STRING
surl_netloc 'mashable.com' STRING
surl_params '' STRING
surl_path '/' STRING
surl_query '' STRING
surl_scheme 'http' STRING
timestamp_info True BOOLEAN
timestamp_info_nginx_ms 1473277850000 INTEGER
timestamp_info_override_ms INTEGER
timestamp_info_pixel_ms 1473277850017 INTEGER
ts_action '2016-09-07 19:50:50' STRING
ts_session_current '2016-09-07 19:49:07' STRING
ts_session_last '2016-09-07 18:02:31' STRING
ua_browser 'Safari' STRING
ua_browserversion '9.1.2' STRING
ua_device 'Other' STRING
ua_devicebrand None STRING
ua_devicemodel None STRING
ua_devicetouchcapable True BOOLEAN
ua_devicetype 'desktop' STRING
ua_os 'Mac OS X' STRING
ua_osversion '10.10.5' STRING
url 'http://mashable.com/201...' STRING
url_clean 'http://mashable.com/201...' STRING
url_domain 'mashable.com' STRING
url_fragment 'L.eZPflSGqq5' STRING
url_netloc 'mashable.com' STRING
url_params '' STRING
url_path '/2016/09/07/airpods-jok...' STRING
url_query '' STRING
url_scheme 'http' STRING
utm_campaign 'facebook_campaign' STRING
utm_medium 'partners' STRING
utm_source 'facebook' STRING
utm_term '8098' STRING
utm_content 'sports' STRING
user_agent 'Mozilla/5.0 (Macintosh;...' STRING
version 1 INTEGER
visitor True BOOLEAN
visitor_ip '108.225.131.20' STRING
visitor_network_id 'ac94fe31-a307-4020-9a23...' STRING
visitor_site_id 'ab94fd31-a207-4010-8a25...' STRING

BigQuery DDL

{"type": "STRING", "name": "action", "mode": "NULLABLE"}
{"type": "STRING", "name": "apikey", "mode": "NULLABLE"}
{"type": "STRING", "name": "campaign_id", "mode": "NULLABLE"}
{"type": "BOOLEAN", "name": "display", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "display_avail_height", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "display_avail_width", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "display_pixel_depth", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "display_total_height", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "display_total_width", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "engaged_time_inc", "mode": "NULLABLE"}
{"type": "STRING", "name": "event_id", "mode": "NULLABLE"}
{"type": "BOOLEAN", "name": "flags_is_amp", "mode": "NULLABLE"}
{"type": "BOOLEAN", "name": "metadata", "mode": "NULLABLE"}
{"type": "STRING", "name": "metadata_authors", "mode": "REPEATED"}
{"type": "STRING", "name": "metadata_canonical_url", "mode": "NULLABLE"}
{"type": "STRING", "name": "metadata_custom_metadata", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "metadata_duration", "mode": "NULLABLE"}
{"type": "STRING", "name": "metadata_data_source", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "metadata_full_content_word_count", "mode": "NULLABLE"}
{"type": "STRING", "name": "metadata_image_url", "mode": "NULLABLE"}
{"type": "STRING", "name": "metadata_page_type", "mode": "NULLABLE"}
{"type": "STRING", "name": "metadata_post_id", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "metadata_pub_date_tmsp", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "metadata_save_date_tmsp", "mode": "NULLABLE"}
{"type": "STRING", "name": "metadata_section", "mode": "NULLABLE"}
{"type": "STRING", "name": "metadata_share_urls", "mode": "REPEATED"}
{"type": "STRING", "name": "metadata_tags", "mode": "REPEATED"}
{"type": "STRING", "name": "metadata_thumb_url", "mode": "NULLABLE"}
{"type": "STRING", "name": "metadata_title", "mode": "NULLABLE"}
{"type": "STRING", "name": "metadata_urls", "mode": "REPEATED"}
{"type": "STRING", "name": "ref_category", "mode": "NULLABLE"}
{"type": "STRING", "name": "ref_clean", "mode": "NULLABLE"}
{"type": "STRING", "name": "ref_domain", "mode": "NULLABLE"}
{"type": "STRING", "name": "ref_fragment", "mode": "NULLABLE"}
{"type": "STRING", "name": "ref_netloc", "mode": "NULLABLE"}
{"type": "STRING", "name": "ref_params", "mode": "NULLABLE"}
{"type": "STRING", "name": "ref_path", "mode": "NULLABLE"}
{"type": "STRING", "name": "ref_query", "mode": "NULLABLE"}
{"type": "STRING", "name": "ref_scheme", "mode": "NULLABLE"}
{"type": "STRING", "name": "referrer", "mode": "NULLABLE"}
{"type": "BOOLEAN", "name": "session", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "session_id", "mode": "NULLABLE"}
{"type": "STRING", "name": "session_initial_referrer", "mode": "NULLABLE"}
{"type": "STRING", "name": "session_initial_url", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "session_last_session_timestamp", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "session_timestamp", "mode": "NULLABLE"}
{"type": "BOOLEAN", "name": "slot", "mode": "NULLABLE"}
{"type": "STRING", "name": "sref_category", "mode": "NULLABLE"}
{"type": "STRING", "name": "sref_clean", "mode": "NULLABLE"}
{"type": "STRING", "name": "sref_domain", "mode": "NULLABLE"}
{"type": "STRING", "name": "sref_fragment", "mode": "NULLABLE"}
{"type": "STRING", "name": "sref_netloc", "mode": "NULLABLE"}
{"type": "STRING", "name": "sref_params", "mode": "NULLABLE"}
{"type": "STRING", "name": "sref_path", "mode": "NULLABLE"}
{"type": "STRING", "name": "sref_query", "mode": "NULLABLE"}
{"type": "STRING", "name": "sref_scheme", "mode": "NULLABLE"}
{"type": "STRING", "name": "surl_clean", "mode": "NULLABLE"}
{"type": "STRING", "name": "surl_domain", "mode": "NULLABLE"}
{"type": "STRING", "name": "surl_fragment", "mode": "NULLABLE"}
{"type": "STRING", "name": "surl_netloc", "mode": "NULLABLE"}
{"type": "STRING", "name": "surl_params", "mode": "NULLABLE"}
{"type": "STRING", "name": "surl_path", "mode": "NULLABLE"}
{"type": "STRING", "name": "surl_query", "mode": "NULLABLE"}
{"type": "STRING", "name": "surl_scheme", "mode": "NULLABLE"}
{"type": "BOOLEAN", "name": "timestamp_info", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "timestamp_info_nginx_ms", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "timestamp_info_override_ms", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "timestamp_info_pixel_ms", "mode": "NULLABLE"}
{"type": "STRING", "name": "ts_action", "mode": "NULLABLE"}
{"type": "STRING", "name": "ts_session_current", "mode": "NULLABLE"}
{"type": "STRING", "name": "ts_session_last", "mode": "NULLABLE"}
{"type": "STRING", "name": "ua_browser", "mode": "NULLABLE"}
{"type": "STRING", "name": "ua_browserversion", "mode": "NULLABLE"}
{"type": "STRING", "name": "ua_device", "mode": "NULLABLE"}
{"type": "STRING", "name": "ua_devicebrand", "mode": "NULLABLE"}
{"type": "STRING", "name": "ua_devicemodel", "mode": "NULLABLE"}
{"type": "BOOLEAN", "name": "ua_devicetouchcapable", "mode": "NULLABLE"}
{"type": "STRING", "name": "ua_devicetype", "mode": "NULLABLE"}
{"type": "STRING", "name": "ua_os", "mode": "NULLABLE"}
{"type": "STRING", "name": "ua_osversion", "mode": "NULLABLE"}
{"type": "STRING", "name": "url", "mode": "NULLABLE"}
{"type": "STRING", "name": "url_clean", "mode": "NULLABLE"}
{"type": "STRING", "name": "url_domain", "mode": "NULLABLE"}
{"type": "STRING", "name": "url_fragment", "mode": "NULLABLE"}
{"type": "STRING", "name": "url_netloc", "mode": "NULLABLE"}
{"type": "STRING", "name": "url_params", "mode": "NULLABLE"}
{"type": "STRING", "name": "url_path", "mode": "NULLABLE"}
{"type": "STRING", "name": "url_query", "mode": "NULLABLE"}
{"type": "STRING", "name": "url_scheme", "mode": "NULLABLE"}
{"type": "STRING", "name": "utm_campaign", "mode": "NULLABLE"}
{"type": "STRING", "name": "utm_medium", "mode": "NULLABLE"}
{"type": "STRING", "name": "utm_source", "mode": "NULLABLE"}
{"type": "STRING", "name": "utm_term", "mode": "NULLABLE"}
{"type": "STRING", "name": "utm_content", "mode": "NULLABLE"}
{"type": "STRING", "name": "user_agent", "mode": "NULLABLE"}
{"type": "INTEGER", "name": "version", "mode": "NULLABLE"}
{"type": "BOOLEAN", "name": "visitor", "mode": "NULLABLE"}
{"type": "STRING", "name": "visitor_ip", "mode": "NULLABLE"}
{"type": "STRING", "name": "visitor_network_id", "mode": "NULLABLE"}
{"type": "STRING", "name": "visitor_site_id", "mode": "NULLABLE"}
Do you have an urgent support question?