CREATE TABLE IF NOT EXISTS action_request (
  dt         STRING             COMMENT 'Timestamp in ISO 8601',
  client_ip  STRING             COMMENT 'IP of requesting user-agent (follows XFF)',
  user_agent STRING             COMMENT 'User-Agent header of request',
  params     MAP<STRING,STRING> COMMENT 'Request parameters',
  ip_class   STRING             COMMENT 'IP based origin derived from client_ip, can be internal, external or labs',
  year       INT                COMMENT 'Unpadded year of request',
  month      INT                COMMENT 'Unpadded month of request',
  day        INT                COMMENT 'Unpadded day of request',
  hour       INT                COMMENT 'Unpadded hour of request',
  COMMENT 'Action API requests'
  )
PARTITIONED BY (year INT, month INT, day INT, hour INT);

-- NOTE: action_request.client_ip is not needed at all if we can do the ip classification as part of the import from Kafka.


CREATE TABLE IF NOT EXISTS action_ua_hourly (
  user_agent STRING COMMENT 'Raw user-agent',
  ip_class   STRING COMMENT 'IP based origin, can be internal, external or labs',
  view_count BIGINT COMMENT 'Number of requests',
  year       INT    COMMENT 'Unpadded year of request',
  month      INT    COMMENT 'Unpadded month of request',
  day        INT    COMMENT 'Unpadded day of request',
  hour       INT    COMMENT 'Unpadded hour of request',
  COMMENT 'Hourly summary of Action API requests bucketed by user-agent'
)
PARTITIONED BY (year INT, month INT, day INT, hour INT);


CREATE TABLE IF NOT EXISTS action_action_hourly (
  action     STRING COMMENT 'Action parameter value',
  ip_class   STRING COMMENT 'IP based origin, can be internal, external or labs',
  view_count BIGINT COMMENT 'Number of requests',
  year       INT    COMMENT 'Unpadded year of request',
  month      INT    COMMENT 'Unpadded month of request',
  day        INT    COMMENT 'Unpadded day of request',
  hour       INT    COMMENT 'Unpadded hour of request',
  COMMENT 'Hourly summary of Action API requests bucketed by action'
)
PARTITIONED BY (year INT, month INT, day INT, hour INT);


CREATE TABLE IF NOT EXISTS action_param_hourly (
  action     STRING COMMENT 'Action parameter value',
  param      STRING COMMENT 'Parameter name, can be prop, list, meta, generator, etc',
  value      STRING COMMENT 'Parameter value',
  ip_class   STRING COMMENT 'IP based origin, can be internal, external or labs',
  view_count BIGINT COMMENT 'Number of requests',
  year       INT    COMMENT 'Unpadded year of request',
  month      INT    COMMENT 'Unpadded month of request',
  day        INT    COMMENT 'Unpadded day of request',
  hour       INT    COMMENT 'Unpadded hour of request',
  COMMENT 'Hourly summary of Action API requests bucketed by action, parameter and value'
)
PARTITIONED BY (year INT, month INT, day INT, hour INT);

-- Create tables for Action API stats
--
-- Usage:
--     hive -f create-action-tables.sql --database wmf

CREATE TABLE IF NOT EXISTS action_ua_hourly (
  userAgent STRING COMMENT 'Raw user-agent',
  wiki      STRING COMMENT 'Target wiki (e.g. enwiki)',
  ipClass   STRING COMMENT 'IP based origin, can be wikimedia, wikimedia_labs or internet',
  viewCount BIGINT COMMENT 'Number of requests'
)
COMMENT 'Hourly summary of Action API requests bucketed by user-agent and wiki'
PARTITIONED BY (
  year      INT COMMENT 'Unpadded year of request',
  month     INT COMMENT 'Unpadded month of request',
  day       INT COMMENT 'Unpadded day of request',
  hour      INT COMMENT 'Unpadded hour of request'
)
STORED AS PARQUET;


CREATE EXTERNAL TABLE IF NOT EXISTS action_action_hourly (
  action    STRING COMMENT 'Action parameter value',
  wiki      STRING COMMENT 'Target wiki (e.g. enwiki)',
  ipClass   STRING COMMENT 'IP based origin, can be wikimedia, wikimedia_labs or internet',
  viewCount BIGINT COMMENT 'Number of requests'
)
COMMENT 'Hourly summary of Action API requests bucketed by action and wiki'
PARTITIONED BY (
  year      INT COMMENT 'Unpadded year of request',
  month     INT COMMENT 'Unpadded month of request',
  day       INT COMMENT 'Unpadded day of request',
  hour      INT COMMENT 'Unpadded hour of request'
)
STORED AS PARQUET;


CREATE EXTERNAL TABLE IF NOT EXISTS action_param_hourly (
  action    STRING COMMENT 'Action parameter value',
  param     STRING COMMENT 'Parameter name, can be prop, list, meta, generator, etc',
  value     STRING COMMENT 'Parameter value',
  wiki      STRING COMMENT 'Target wiki (e.g. enwiki)',
  ipClass   STRING COMMENT 'IP based origin, can be wikimedia, wikimedia_labs or internet',
  viewCount BIGINT COMMENT 'Number of requests'
)
COMMENT 'Hourly summary of Action API requests bucketed by action, parameter, value and wiki'
PARTITIONED BY (
  year      INT COMMENT 'Unpadded year of request',
  month     INT COMMENT 'Unpadded month of request',
  day       INT COMMENT 'Unpadded day of request',
  hour      INT COMMENT 'Unpadded hour of request'
)
STORED AS PARQUET;

-- NOTE: there are many params we would not want to count distinct values of
-- at all (eg maxlag, smaxage, maxage, requestid, origin, centralauthtoken,
-- titles, pageids). It will be easier to whitelist in the ETL process
-- than to try and selectively blacklist.