Data Pipeline and third-party database schemas

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.

This is for Parse.ly Data Pipeline v2.3.0. Looking for a previous version? Look here.

Redshift Schema

Column	Example	Type
action	‘pageview’	VARCHAR(256) NOT NULL
apikey	‘mashable.com’	VARCHAR(256) NOT NULL
campaign_id	‘facebook_campaign’	VARCHAR(256)
channel	‘website’	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
ip_city	‘Newark’	VARCHAR(4096)
ip_continent	‘NA’	VARCHAR(2)
ip_country	‘US’	VARCHAR(2)
ip_lat	37.5147	FLOAT
ip_lon	-122.0423	FLOAT
ip_postal	‘94560’	VARCHAR(64)
ip_subdivision	‘CA’	VARCHAR(3)
ip_timezone	‘America/Los_Angeles’	VARCHAR(256)
ip_market_name	‘New York’	VARCHAR(256)
ip_market_nielsen	‘501’	VARCHAR(3)
ip_market_doubleclick	‘3’	VARCHAR(3)
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)
pageload_id	17911340	VARCHAR(256)
pageview_id	24977559	VARCHAR(256)
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)
schema_version	2.3.0	VARCHAR(64)
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_utm_campaign	‘facebook_campaign’	VARCHAR(256)
surl_utm_medium	‘partners’	VARCHAR(64)
surl_utm_source	‘facebook’	VARCHAR(64)
surl_utm_term	‘8098’	VARCHAR(64)
surl_utm_content	‘sports’	VARCHAR(256)
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
videostart_id	99887766	VARCHAR(256)
visitor	True	BOOLEAN
visitor_network_id	NULL	VARCHAR(128)
visitor_site_id	NULL	VARCHAR(128) NOT NULL

Redshift DDL

CREATE TABLE parsely.rawdata (
    action               VARCHAR(256) NOT NULL,
    apikey               VARCHAR(256) NOT NULL,
    campaign_id             VARCHAR(256),
    channel               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,
    ip_city               VARCHAR(4096),
    ip_continent            VARCHAR(2),
    ip_country             VARCHAR(2),
    ip_lat               FLOAT,
    ip_lon               FLOAT,
    ip_postal              VARCHAR(64),
    ip_subdivision           VARCHAR(3),
    ip_timezone             VARCHAR(256),
    ip_market_name           VARCHAR(256),
    ip_market_nielsen          VARCHAR(3),
    ip_market_doubleclick        VARCHAR(3),
    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),
    pageload_id             VARCHAR(256),
    pageview_id             VARCHAR(256),
    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),
    schema_version           VARCHAR(64),
    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_utm_campaign          VARCHAR(256),
    surl_utm_medium           VARCHAR(64),
    surl_utm_source           VARCHAR(64),
    surl_utm_term            VARCHAR(64),
    surl_utm_content          VARCHAR(256),
    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,
    videostart_id            VARCHAR(256),
    visitor               BOOLEAN,
    visitor_network_id         VARCHAR(128),
    visitor_site_id           VARCHAR(128) NOT NULL
);

CREATE TABLE parsely.rawdata (
    action               VARCHAR(256) NOT NULL,
    apikey               VARCHAR(256) NOT NULL,
    campaign_id             VARCHAR(256),
    channel               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,
    ip_city               VARCHAR(4096),
    ip_continent            VARCHAR(2),
    ip_country             VARCHAR(2),
    ip_lat               FLOAT,
    ip_lon               FLOAT,
    ip_postal              VARCHAR(64),
    ip_subdivision           VARCHAR(3),
    ip_timezone             VARCHAR(256),
    ip_market_name           VARCHAR(256),
    ip_market_nielsen          VARCHAR(3),
    ip_market_doubleclick        VARCHAR(3),
    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),
    pageload_id             VARCHAR(256),
    pageview_id             VARCHAR(256),
    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),
    schema_version           VARCHAR(64),
    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_utm_campaign          VARCHAR(256),
    surl_utm_medium           VARCHAR(64),
    surl_utm_source           VARCHAR(64),
    surl_utm_term            VARCHAR(64),
    surl_utm_content          VARCHAR(256),
    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,
    videostart_id            VARCHAR(256),
    visitor               BOOLEAN,
    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
channel	‘website’	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
ip_city	‘Newark’	STRING
ip_continent	‘NA’	STRING
ip_country	‘US’	STRING
ip_lat	37.5147	FLOAT
ip_lon	-122.0423	FLOAT
ip_postal	‘94560’	STRING
ip_subdivision	‘CA’	STRING
ip_timezone	‘America/Los_Angeles’	STRING
ip_market_name	‘New York’	STRING
ip_market_nielsen	‘501’	STRING
ip_market_doubleclick	‘3’	STRING
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)
pageload_id	11223344	STRING
pageview_id	55667788	STRING
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
schema_version	‘2.3.0’	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_utm_campaign	‘facebook_campaign’	STRING
surl_utm_medium	‘partners’	STRING
surl_utm_source	‘facebook’	STRING
surl_utm_term	‘8098’	STRING
surl_utm_content	‘sports’	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
videostart_id	99887766	STRING
visitor	True	BOOLEAN
visitor_network_id	NULL	STRING
visitor_site_id	‘ab94fd31-a207-4010-8a25…’	STRING

BigQuery DDL

{"name": "action", "type": "STRING", "mode": "NULLABLE"}
{"name": "apikey", "type": "STRING", "mode": "NULLABLE"}
{"name": "campaign_id", "type": "STRING", "mode": "NULLABLE"}
{"name": "channel", "type": "STRING", "mode": "NULLABLE"}
{"name": "display", "type": "BOOLEAN", "mode": "NULLABLE"}
{"name": "display_avail_height", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "display_avail_width", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "display_pixel_depth", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "display_total_height", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "display_total_width", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "engaged_time_inc", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "event_id", "type": "STRING", "mode": "NULLABLE"}
{"name": "flags_is_amp", "type": "BOOLEAN", "mode": "NULLABLE"}
{"name": "ip_city", "type": "STRING", "mode": "NULLABLE"}
{"name": "ip_continent", "type": "STRING", "mode": "NULLABLE"}
{"name": "ip_country", "type": "STRING", "mode": "NULLABLE"}
{"name": "ip_lat", "type": "FLOAT", "mode": "NULLABLE"}
{"name": "ip_lon", "type": "FLOAT", "mode": "NULLABLE"}
{"name": "ip_postal", "type": "STRING", "mode": "NULLABLE"}
{"name": "ip_subdivision", "type": "STRING", "mode": "NULLABLE"}
{"name": "ip_timezone", "type": "STRING", "mode": "NULLABLE"}
{"name": "ip_market_name", "type": "STRING", "mode": "NULLABLE"}
{"name": "ip_market_nielsen", "type": "STRING", "mode": "NULLABLE"}
{"name": "ip_market_doubleclick", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata", "type": "BOOLEAN", "mode": "NULLABLE"}
{"name": "metadata_authors", "type": "STRING", "mode": "REPEATED"}
{"name": "metadata_canonical_url", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_custom_metadata", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_duration", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "metadata_data_source", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_full_content_word_count", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "metadata_image_url", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_page_type", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_post_id", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_pub_date_tmsp", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "metadata_save_date_tmsp", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "metadata_section", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_share_urls", "type": "STRING", "mode": "REPEATED"}
{"name": "metadata_tags", "type": "STRING", "mode": "REPEATED"}
{"name": "metadata_thumb_url", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_title", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_urls", "type": "STRING", "mode": "REPEATED"}
{"name": "pageload_id", "type": "STRING", "mode": "NULLABLE"}
{"name": "pageview_id", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_category", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_clean", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_domain", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_fragment", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_netloc", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_params", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_path", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_query", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_scheme", "type": "STRING", "mode": "NULLABLE"}
{"name": "referrer", "type": "STRING", "mode": "NULLABLE"}
{"name": "schema_version", "type": "STRING", "mode": "NULLABLE"}
{"name": "session", "type": "BOOLEAN", "mode": "NULLABLE"}
{"name": "session_id", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "session_initial_referrer", "type": "STRING", "mode": "NULLABLE"}
{"name": "session_initial_url", "type": "STRING", "mode": "NULLABLE"}
{"name": "session_last_session_timestamp", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "session_timestamp", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "slot", "type": "BOOLEAN", "mode": "NULLABLE"}
{"name": "sref_category", "type": "STRING", "mode": "NULLABLE"}
{"name": "sref_clean", "type": "STRING", "mode": "NULLABLE"}
{"name": "sref_domain", "type": "STRING", "mode": "NULLABLE"}
{"name": "sref_fragment", "type": "STRING", "mode": "NULLABLE"}
{"name": "sref_netloc", "type": "STRING", "mode": "NULLABLE"}
{"name": "sref_params", "type": "STRING", "mode": "NULLABLE"}
{"name": "sref_path", "type": "STRING", "mode": "NULLABLE"}
{"name": "sref_query", "type": "STRING", "mode": "NULLABLE"}
{"name": "sref_scheme", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_utm_campaign", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_utm_medium", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_utm_source", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_utm_term", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_utm_content", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_clean", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_domain", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_fragment", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_netloc", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_params", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_path", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_query", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_scheme", "type": "STRING", "mode": "NULLABLE"}
{"name": "timestamp_info", "type": "BOOLEAN", "mode": "NULLABLE"}
{"name": "timestamp_info_nginx_ms", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "timestamp_info_override_ms", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "timestamp_info_pixel_ms", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "ts_action", "type": "STRING", "mode": "NULLABLE"}
{"name": "ts_session_current", "type": "STRING", "mode": "NULLABLE"}
{"name": "ts_session_last", "type": "STRING", "mode": "NULLABLE"}
{"name": "ua_browser", "type": "STRING", "mode": "NULLABLE"}
{"name": "ua_browserversion", "type": "STRING", "mode": "NULLABLE"}
{"name": "ua_device", "type": "STRING", "mode": "NULLABLE"}
{"name": "ua_devicebrand", "type": "STRING", "mode": "NULLABLE"}
{"name": "ua_devicemodel", "type": "STRING", "mode": "NULLABLE"}
{"name": "ua_devicetouchcapable", "type": "BOOLEAN", "mode": "NULLABLE"}
{"name": "ua_devicetype", "type": "STRING", "mode": "NULLABLE"}
{"name": "ua_os", "type": "STRING", "mode": "NULLABLE"}
{"name": "ua_osversion", "type": "STRING", "mode": "NULLABLE"}
{"name": "url", "type": "STRING", "mode": "NULLABLE"}
{"name": "url_clean", "type": "STRING", "mode": "NULLABLE"}
{"name": "url_domain", "type": "STRING", "mode": "NULLABLE"}
{"name": "url_fragment", "type": "STRING", "mode": "NULLABLE"}
{"name": "url_netloc", "type": "STRING", "mode": "NULLABLE"}
{"name": "url_params", "type": "STRING", "mode": "NULLABLE"}
{"name": "url_path", "type": "STRING", "mode": "NULLABLE"}
{"name": "url_query", "type": "STRING", "mode": "NULLABLE"}
{"name": "url_scheme", "type": "STRING", "mode": "NULLABLE"}
{"name": "utm_campaign", "type": "STRING", "mode": "NULLABLE"}
{"name": "utm_medium", "type": "STRING", "mode": "NULLABLE"}
{"name": "utm_source", "type": "STRING", "mode": "NULLABLE"}
{"name": "utm_term", "type": "STRING", "mode": "NULLABLE"}
{"name": "utm_content", "type": "STRING", "mode": "NULLABLE"}
{"name": "user_agent", "type": "STRING", "mode": "NULLABLE"}
{"name": "version", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "videostart_id", "type": "STRING", "mode": "NULLABLE"}
{"name": "visitor", "type": "BOOLEAN", "mode": "NULLABLE"}
{"name": "visitor_network_id", "type": "STRING", "mode": "NULLABLE"}
{"name": "visitor_site_id", "type": "STRING", "mode": "NULLABLE"}

{"name": "action", "type": "STRING", "mode": "NULLABLE"}
{"name": "apikey", "type": "STRING", "mode": "NULLABLE"}
{"name": "campaign_id", "type": "STRING", "mode": "NULLABLE"}
{"name": "channel", "type": "STRING", "mode": "NULLABLE"}
{"name": "display", "type": "BOOLEAN", "mode": "NULLABLE"}
{"name": "display_avail_height", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "display_avail_width", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "display_pixel_depth", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "display_total_height", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "display_total_width", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "engaged_time_inc", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "event_id", "type": "STRING", "mode": "NULLABLE"}
{"name": "flags_is_amp", "type": "BOOLEAN", "mode": "NULLABLE"}
{"name": "ip_city", "type": "STRING", "mode": "NULLABLE"}
{"name": "ip_continent", "type": "STRING", "mode": "NULLABLE"}
{"name": "ip_country", "type": "STRING", "mode": "NULLABLE"}
{"name": "ip_lat", "type": "FLOAT", "mode": "NULLABLE"}
{"name": "ip_lon", "type": "FLOAT", "mode": "NULLABLE"}
{"name": "ip_postal", "type": "STRING", "mode": "NULLABLE"}
{"name": "ip_subdivision", "type": "STRING", "mode": "NULLABLE"}
{"name": "ip_timezone", "type": "STRING", "mode": "NULLABLE"}
{"name": "ip_market_name", "type": "STRING", "mode": "NULLABLE"}
{"name": "ip_market_nielsen", "type": "STRING", "mode": "NULLABLE"}
{"name": "ip_market_doubleclick", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata", "type": "BOOLEAN", "mode": "NULLABLE"}
{"name": "metadata_authors", "type": "STRING", "mode": "REPEATED"}
{"name": "metadata_canonical_url", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_custom_metadata", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_duration", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "metadata_data_source", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_full_content_word_count", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "metadata_image_url", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_page_type", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_post_id", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_pub_date_tmsp", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "metadata_save_date_tmsp", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "metadata_section", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_share_urls", "type": "STRING", "mode": "REPEATED"}
{"name": "metadata_tags", "type": "STRING", "mode": "REPEATED"}
{"name": "metadata_thumb_url", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_title", "type": "STRING", "mode": "NULLABLE"}
{"name": "metadata_urls", "type": "STRING", "mode": "REPEATED"}
{"name": "pageload_id", "type": "STRING", "mode": "NULLABLE"}
{"name": "pageview_id", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_category", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_clean", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_domain", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_fragment", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_netloc", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_params", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_path", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_query", "type": "STRING", "mode": "NULLABLE"}
{"name": "ref_scheme", "type": "STRING", "mode": "NULLABLE"}
{"name": "referrer", "type": "STRING", "mode": "NULLABLE"}
{"name": "schema_version", "type": "STRING", "mode": "NULLABLE"}
{"name": "session", "type": "BOOLEAN", "mode": "NULLABLE"}
{"name": "session_id", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "session_initial_referrer", "type": "STRING", "mode": "NULLABLE"}
{"name": "session_initial_url", "type": "STRING", "mode": "NULLABLE"}
{"name": "session_last_session_timestamp", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "session_timestamp", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "slot", "type": "BOOLEAN", "mode": "NULLABLE"}
{"name": "sref_category", "type": "STRING", "mode": "NULLABLE"}
{"name": "sref_clean", "type": "STRING", "mode": "NULLABLE"}
{"name": "sref_domain", "type": "STRING", "mode": "NULLABLE"}
{"name": "sref_fragment", "type": "STRING", "mode": "NULLABLE"}
{"name": "sref_netloc", "type": "STRING", "mode": "NULLABLE"}
{"name": "sref_params", "type": "STRING", "mode": "NULLABLE"}
{"name": "sref_path", "type": "STRING", "mode": "NULLABLE"}
{"name": "sref_query", "type": "STRING", "mode": "NULLABLE"}
{"name": "sref_scheme", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_utm_campaign", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_utm_medium", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_utm_source", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_utm_term", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_utm_content", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_clean", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_domain", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_fragment", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_netloc", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_params", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_path", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_query", "type": "STRING", "mode": "NULLABLE"}
{"name": "surl_scheme", "type": "STRING", "mode": "NULLABLE"}
{"name": "timestamp_info", "type": "BOOLEAN", "mode": "NULLABLE"}
{"name": "timestamp_info_nginx_ms", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "timestamp_info_override_ms", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "timestamp_info_pixel_ms", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "ts_action", "type": "STRING", "mode": "NULLABLE"}
{"name": "ts_session_current", "type": "STRING", "mode": "NULLABLE"}
{"name": "ts_session_last", "type": "STRING", "mode": "NULLABLE"}
{"name": "ua_browser", "type": "STRING", "mode": "NULLABLE"}
{"name": "ua_browserversion", "type": "STRING", "mode": "NULLABLE"}
{"name": "ua_device", "type": "STRING", "mode": "NULLABLE"}
{"name": "ua_devicebrand", "type": "STRING", "mode": "NULLABLE"}
{"name": "ua_devicemodel", "type": "STRING", "mode": "NULLABLE"}
{"name": "ua_devicetouchcapable", "type": "BOOLEAN", "mode": "NULLABLE"}
{"name": "ua_devicetype", "type": "STRING", "mode": "NULLABLE"}
{"name": "ua_os", "type": "STRING", "mode": "NULLABLE"}
{"name": "ua_osversion", "type": "STRING", "mode": "NULLABLE"}
{"name": "url", "type": "STRING", "mode": "NULLABLE"}
{"name": "url_clean", "type": "STRING", "mode": "NULLABLE"}
{"name": "url_domain", "type": "STRING", "mode": "NULLABLE"}
{"name": "url_fragment", "type": "STRING", "mode": "NULLABLE"}
{"name": "url_netloc", "type": "STRING", "mode": "NULLABLE"}
{"name": "url_params", "type": "STRING", "mode": "NULLABLE"}
{"name": "url_path", "type": "STRING", "mode": "NULLABLE"}
{"name": "url_query", "type": "STRING", "mode": "NULLABLE"}
{"name": "url_scheme", "type": "STRING", "mode": "NULLABLE"}
{"name": "utm_campaign", "type": "STRING", "mode": "NULLABLE"}
{"name": "utm_medium", "type": "STRING", "mode": "NULLABLE"}
{"name": "utm_source", "type": "STRING", "mode": "NULLABLE"}
{"name": "utm_term", "type": "STRING", "mode": "NULLABLE"}
{"name": "utm_content", "type": "STRING", "mode": "NULLABLE"}
{"name": "user_agent", "type": "STRING", "mode": "NULLABLE"}
{"name": "version", "type": "INTEGER", "mode": "NULLABLE"}
{"name": "videostart_id", "type": "STRING", "mode": "NULLABLE"}
{"name": "visitor", "type": "BOOLEAN", "mode": "NULLABLE"}
{"name": "visitor_network_id", "type": "STRING", "mode": "NULLABLE"}
{"name": "visitor_site_id", "type": "STRING", "mode": "NULLABLE"}

Athena Schema

Column	Example	Type
action	‘pageview’	STRING
apikey	‘mashable.com’	STRING
campaign_id	‘facebook_campaign’	STRING
channel	‘website’	STRING
display	True	BOOLEAN
display_avail_height	735	INT
display_avail_width	1280	INT
display_pixel_depth	24	INT
display_total_height	800	INT
display_total_width	1280	INT
engaged_time_inc	\| INT
event_id	‘0xe6508eda93d5598367b18…’	STRING
extra_data	\| STRING
flags_is_amp	\| BOOLEAN
ip_city	‘Newark’	STRING
ip_continent	‘NA’	STRING
ip_country	‘US’	STRING
ip_lat	37.5147	DOUBLE
ip_lon	-122.0423	DOUBLE
ip_postal	‘94560’	STRING
ip_subdivision	‘CA’	STRING
ip_timezone	‘America/Los_Angeles’	STRING
ip_market_name	‘New York’	STRING
ip_market_nielsen	‘501’	STRING
ip_market_doubleclick	‘3’	STRING
metadata	True	BOOLEAN
metadata_authors	[‘Laura Vitto’]	ARRAY (STRING)
metadata_canonical_url	‘http://mashable.com/201…’	STRING
metadata_custom_metadata	‘{“site”:”Mashable”}’	STRING
metadata_duration	\| INT
metadata_data_source	‘crawl’	STRING
metadata_full_content_word_count	174	INT
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	INT
metadata_save_date_tmsp	1473275204000	INT
metadata_section	‘watercooler’	STRING
metadata_share_urls	\| ARRAY (STRING)
metadata_tags	[‘gadgets’, ‘iphone-7’]	ARRAY (STRING)
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/’]	ARRAY (STRING)
pageload_id	11223344	STRING
pageview_id	55667788	STRING
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
schema_version	‘2.3.0’	STRING
session	True	BOOLEAN
session_id	6	INT
session_initial_referrer	‘http://mashable.com/’	STRING
session_initial_url	‘http://mashable.com/’	STRING
session_last_session_timestamp	1473271351611	INT
session_timestamp	1473277747806	INT
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_utm_campaign	‘facebook_campaign’	STRING
surl_utm_medium	‘partners’	STRING
surl_utm_source	‘facebook’	STRING
surl_utm_term	‘8098’	STRING
surl_utm_content	‘sports’	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	INT
timestamp_info_override_ms	\| INT
timestamp_info_pixel_ms	1473277850017	INT
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	INT
videostart_id	99887766	STRING
visitor	True	BOOLEAN
visitor_network_id	NULL	STRING
visitor_site_id	‘ab94fd31-a207-4010-8a25…’	STRING

Athena DDL

CREATE EXTERNAL TABLE `table_name`(
 `action` string,
 `apikey` string,
 `campaign_id` string,
 `channel` string,
 `display_avail_height` int,
 `display_avail_width` int,
 `display_pixel_depth` int,
 `display_total_height` int,
 `display_total_width` int,
 `display` boolean,
 `engaged_time_inc` int,
 `event_id` string,
 `extra_data` string,
 `flags_is_amp` string,
 `ip_city` string,
 `ip_continent` string,
 `ip_country` string,
 `ip_lat` double,
 `ip_lon` double,
 `ip_market_doubleclick` string,
 `ip_market_name` string,
 `ip_market_nielsen` string,
 `ip_postal` string,
 `ip_subdivision` string,
 `ip_timezone` string,
 `metadata_authors` array<string>,
 `metadata_canonical_url` string,
 `metadata_custom_metadata` string,
 `metadata_data_source` string,
 `metadata_duration` string,
 `metadata_full_content_word_count` int,
 `metadata_image_url` string,
 `metadata_page_type` string,
 `metadata_post_id` string,
 `metadata_pub_date_tmsp` bigint,
 `metadata_save_date_tmsp` bigint,
 `metadata_section` string,
 `metadata_share_urls` array<string>,
 `metadata_tags` array<string>,
 `metadata_thumb_url` string,
 `metadata_title` string,
 `metadata_urls` array<string>,
 `metadata` boolean,
 `pageload_id` string,
 `pageview_id` string,
 `ref_category` string,
 `ref_clean` string,
 `ref_domain` string,
 `ref_fragment` string,
 `ref_netloc` string,
 `ref_params` string,
 `ref_path` string,
 `ref_query` string,
 `ref_scheme` string,
 `referrer` string,
 `schema_version` string,
 `session_id` int,
 `session_initial_referrer` string,
 `session_initial_url` string,
 `session_last_session_timestamp` bigint,
 `session_timestamp` bigint,
 `session` boolean,
 `slot` boolean,
 `sref_category` string,
 `sref_clean` string,
 `sref_domain` string,
 `sref_fragment` string,
 `sref_netloc` string,
 `sref_params` string,
 `sref_path` string,
 `sref_query` string,
 `sref_scheme` string,
 `surl_clean` string,
 `surl_domain` string,
 `surl_fragment` string,
 `surl_netloc` string,
 `surl_params` string,
 `surl_path` string,
 `surl_query` string,
 `surl_scheme` string,
 `surl_utm_campaign` string,
 `surl_utm_content` string,
 `surl_utm_medium` string,
 `surl_utm_source` string,
 `timestamp_info_nginx_ms` bigint,
 `timestamp_info_override_ms` string,
 `timestamp_info_pixel_ms` bigint,
 `timestamp_info` boolean,
 `ts_action` string,
 `ts_session_current` string,
 `ts_session_last` string,
 `ua_browser` string,
 `ua_browserversion` string,
 `ua_device` string,
 `ua_devicebrand` string,
 `ua_devicemodel` string,
 `ua_devicetype` string,
 `ua_os` string,
 `ua_osversion` string,
 `url_clean` string,
 `url_domain` string,
 `url_fragment` string,
 `url_netloc` string,
 `url_params` string,
 `url_path` string,
 `url_query` string,
 `url_scheme` string,
 `url` string,
 `user_agent` string,
 `utm_campaign` string,
 `utm_content` string,
 `utm_medium` string,
 `utm_source` string,
 `version` int,
 `videostart_id` string,
 `visitor_ip` string,
 `visitor_network_id` string,
 `visitor_site_id` string,
 `visitor` boolean,
 `ua_devicetouchcapable` boolean)
PARTITIONED BY (
  `year` string,
  `month` string)
ROW FORMAT SERDE
 'org.openx.data.jsonserde.JsonSerDe'
LOCATION
 's3://parsely-dw-bucket-name/events'
TBLPROPERTIES (
 'classification'='json',
 'compressionType'='gzip',
 'transient_lastDdlTime'='1570652964',
 'typeOfData'='file');

CREATE EXTERNAL TABLE `table_name`(
 `action` string,
 `apikey` string,
 `campaign_id` string,
 `channel` string,
 `display_avail_height` int,
 `display_avail_width` int,
 `display_pixel_depth` int,
 `display_total_height` int,
 `display_total_width` int,
 `display` boolean,
 `engaged_time_inc` int,
 `event_id` string,
 `extra_data` string,
 `flags_is_amp` string,
 `ip_city` string,
 `ip_continent` string,
 `ip_country` string,
 `ip_lat` double,
 `ip_lon` double,
 `ip_market_doubleclick` string,
 `ip_market_name` string,
 `ip_market_nielsen` string,
 `ip_postal` string,
 `ip_subdivision` string,
 `ip_timezone` string,
 `metadata_authors` array<string>,
 `metadata_canonical_url` string,
 `metadata_custom_metadata` string,
 `metadata_data_source` string,
 `metadata_duration` string,
 `metadata_full_content_word_count` int,
 `metadata_image_url` string,
 `metadata_page_type` string,
 `metadata_post_id` string,
 `metadata_pub_date_tmsp` bigint,
 `metadata_save_date_tmsp` bigint,
 `metadata_section` string,
 `metadata_share_urls` array<string>,
 `metadata_tags` array<string>,
 `metadata_thumb_url` string,
 `metadata_title` string,
 `metadata_urls` array<string>,
 `metadata` boolean,
 `pageload_id` string,
 `pageview_id` string,
 `ref_category` string,
 `ref_clean` string,
 `ref_domain` string,
 `ref_fragment` string,
 `ref_netloc` string,
 `ref_params` string,
 `ref_path` string,
 `ref_query` string,
 `ref_scheme` string,
 `referrer` string,
 `schema_version` string,
 `session_id` int,
 `session_initial_referrer` string,
 `session_initial_url` string,
 `session_last_session_timestamp` bigint,
 `session_timestamp` bigint,
 `session` boolean,
 `slot` boolean,
 `sref_category` string,
 `sref_clean` string,
 `sref_domain` string,
 `sref_fragment` string,
 `sref_netloc` string,
 `sref_params` string,
 `sref_path` string,
 `sref_query` string,
 `sref_scheme` string,
 `surl_clean` string,
 `surl_domain` string,
 `surl_fragment` string,
 `surl_netloc` string,
 `surl_params` string,
 `surl_path` string,
 `surl_query` string,
 `surl_scheme` string,
 `surl_utm_campaign` string,
 `surl_utm_content` string,
 `surl_utm_medium` string,
 `surl_utm_source` string,
 `timestamp_info_nginx_ms` bigint,
 `timestamp_info_override_ms` string,
 `timestamp_info_pixel_ms` bigint,
 `timestamp_info` boolean,
 `ts_action` string,
 `ts_session_current` string,
 `ts_session_last` string,
 `ua_browser` string,
 `ua_browserversion` string,
 `ua_device` string,
 `ua_devicebrand` string,
 `ua_devicemodel` string,
 `ua_devicetype` string,
 `ua_os` string,
 `ua_osversion` string,
 `url_clean` string,
 `url_domain` string,
 `url_fragment` string,
 `url_netloc` string,
 `url_params` string,
 `url_path` string,
 `url_query` string,
 `url_scheme` string,
 `url` string,
 `user_agent` string,
 `utm_campaign` string,
 `utm_content` string,
 `utm_medium` string,
 `utm_source` string,
 `version` int,
 `videostart_id` string,
 `visitor_ip` string,
 `visitor_network_id` string,
 `visitor_site_id` string,
 `visitor` boolean,
 `ua_devicetouchcapable` boolean)
PARTITIONED BY (
  `year` string,
  `month` string)
ROW FORMAT SERDE
 'org.openx.data.jsonserde.JsonSerDe'
LOCATION
 's3://parsely-dw-bucket-name/events'
TBLPROPERTIES (
 'classification'='json',
 'compressionType'='gzip',
 'transient_lastDdlTime'='1570652964',
 'typeOfData'='file');

Last updated: October 23, 2024