CREATE TABLE `mforns`.`webrequest` (
  `hostname` STRING COMMENT 'Source node hostname',
  `sequence` BIGINT COMMENT 'Per host sequence number',
  `dt` STRING COMMENT 'Timestamp at cache in ISO 8601',
  `time_firstbyte` DOUBLE COMMENT 'Time to first byte',
  `ip` STRING COMMENT 'IP of packet at cache',
  `cache_status` STRING COMMENT 'Cache status',
  `http_status` STRING COMMENT 'HTTP status of response',
  `response_size` BIGINT COMMENT 'Response size',
  `http_method` STRING COMMENT 'HTTP method of request',
  `uri_host` STRING COMMENT 'Host of request',
  `uri_path` STRING COMMENT 'Path of request',
  `uri_query` STRING COMMENT 'Query of request',
  `content_type` STRING COMMENT 'Content-Type header of response',
  `referer` STRING COMMENT 'Referer header of request',
  `x_forwarded_for` STRING COMMENT 'X-Forwarded-For header of request (deprecated)',
  `user_agent` STRING COMMENT 'User-Agent header of request',
  `accept_language` STRING COMMENT 'Accept-Language header of request',
  `x_analytics` STRING COMMENT 'X-Analytics header of response',
  `range` STRING COMMENT 'Range header of response',
  `is_pageview` BOOLEAN COMMENT 'Indicates if this record was marked as a pageview during refinement',
  `record_version` STRING COMMENT 'Keeps track of changes in the table content definition - https://wikitech.wikimedia.org/wiki/Analytics/Data/Webrequest',
  `client_ip` STRING COMMENT 'Client IP - DEPRECATED - Same as IP.',
  `geocoded_data` MAP<STRING, STRING> COMMENT 'Geocoded map with continent, country_code, country, city, subdivision, postal_code, latitude, longitude, timezone keys and associated values.',
  `x_cache` STRING COMMENT 'X-Cache header of response',
  `user_agent_map` MAP<STRING, STRING> COMMENT 'User-agent map with browser_family, browser_major, device_family, os_family, os_major, os_minor and wmf_app_version keys and associated values',
  `x_analytics_map` MAP<STRING, STRING> COMMENT 'X_analytics map view of the x_analytics field',
  `ts` TIMESTAMP COMMENT 'Unix timestamp in milliseconds extracted from dt',
  `access_method` STRING COMMENT 'Method used to access the site (mobile app|mobile web|desktop)',
  `agent_type` STRING COMMENT 'Categorise the agent making the webrequest as either user or spider (automatas to be added).',
  `is_zero` BOOLEAN COMMENT 'NULL as zero program is over',
  `referer_class` STRING COMMENT 'Indicates if a referer is internal, external or unknown.',
  `normalized_host` STRUCT<`project_class`: STRING, `project`: STRING, `qualifiers`: ARRAY<STRING>, `tld`: STRING, `project_family`: STRING> COMMENT 'struct containing project_family (such as wikipedia or wikidata for instance), project (such as en or commons), qualifiers (a list of in-between values, such as m) and tld (org most often)',
  `pageview_info` MAP<STRING, STRING> COMMENT 'map containing project, language_variant and page_title values only when is_pageview = TRUE.',
  `page_id` BIGINT COMMENT 'MediaWiki page_id for this page title. For redirects this could be the page_id of the redirect or the page_id of the target. This may not always be set, even if the page is actually a pageview.',
  `namespace_id` INT COMMENT 'MediaWiki namespace_id for this page title. This may not always be set, even if the page is actually a pageview.',
  `tags` ARRAY<STRING> COMMENT 'List containing tags qualifying the request, ex: [portal, wikidata]. Will be used to split webrequest into smaller subsets.',
  `isp_data` MAP<STRING, STRING> COMMENT 'Internet Service Provider data in a map with keys isp, organization, autonomous_system_organization and autonomous_system_number',
  `accept` STRING COMMENT 'Accept header of request',
  `tls` STRING COMMENT 'TLS information of request',
  `tls_map` MAP<STRING, STRING> COMMENT 'Map view of TLS information (keys are vers, keyx, auth and ciph)',
  `ch_ua` STRING COMMENT 'Value of the Sec-CH-UA request header',
  `ch_ua_mobile` STRING COMMENT 'Value of the Sec-CH-UA-Mobile request header',
  `ch_ua_platform` STRING COMMENT 'Value of the Sec-CH-UA-Platform request header',
  `ch_ua_arch` STRING COMMENT 'Value of the Sec-CH-UA-Arch request header',
  `ch_ua_bitness` STRING COMMENT 'Value of the Sec-CH-UA-Bitness request header',
  `ch_ua_full_version_list` STRING COMMENT 'Value of the Sec-CH-UA-Full-Version-List request header',
  `ch_ua_model` STRING COMMENT 'Value of the Sec-CH-UA-Model request header',
  `ch_ua_platform_version` STRING COMMENT 'Value of the Sec-CH-UA-Platform-Version request header',
  `referer_data` STRUCT<`referer_class`: STRING, `referer_name`: STRING> COMMENT 'Struct containing referer_class (indicates if a referer is internal, external, external(media sites), external(search engine) or unknown.) and referer name (name of referer when referer class is external(search engine) or external(media sites))',
  `webrequest_source` STRING COMMENT 'Source cluster',
  `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')
USING parquet
PARTITIONED BY (webrequest_source, year, month, day, hour)
LOCATION 'hdfs://analytics-hadoop/user/mforns/data/wmf/webrequest'

spark3-submit \
    --name refine_webrequest_hourly_text_special_allevents_test \
    --master yarn \
    --deploy-mode client \
    --queue production \
    --conf spark.executorEnv.SPARK_HOME=/usr/lib/spark3 \
    --conf spark.executorEnv.SPARK_CONF_DIR=/etc/spark3/conf \
    --conf spark.dynamicAllocation.maxExecutors=128 \
    --conf spark.yarn.appMasterEnv.SPARK_CONF_DIR=/etc/spark3/conf \
    --conf spark.yarn.appMasterEnv.SPARK_HOME=/usr/lib/spark3 \
    --driver-cores 1 \
    --driver-memory 4G \
    --executor-cores 2 \
    --executor-memory 12G \
    --class org.apache.spark.sql.hive.thriftserver.WMFSparkSQLCLIDriver \
    hdfs:///wmf/cache/artifacts/airflow/analytics/wmf-sparksqlclidriver-1.0.0.jar \
    -f hdfs://analytics-hadoop/wmf/refinery/current/hql/webrequest/refine_webrequest_hourly.hql \
    -d refinery_jar=hdfs:///user/mforns/artifacts/refinery-job-0.2.46-SNAPSHOT-shaded.jar \
    -d source_table=wmf_raw.webrequest \
    -d webrequest_source=text \
    -d destination_table=mforns.webrequest \
    -d year=2024 \
    -d month=8 \
    -d day=13 \
    -d hour=18 \
    -d record_version=0.0.27 \
    -d coalesce_partitions=256 \
    -d spark_sql_shuffle_partitions=256 \
    -d excluded_row_ids=

> select count(*) from wmf.webrequest where year=2024 and month=8 and day=13 and hour=18 and webrequest_source='text';
count(1)           
346708973
Time taken: 19.559 seconds, Fetched 1 row(s)

> select count(*) from mforns.webrequest where year=2024 and month=8 and day=13 and hour=18 and webrequest_source='text';
count(1)
346708973
Time taken: 39.385 seconds, Fetched 1 row(s)

> select count(*) from wmf.webrequest where year=2024 and month=8 and day=13 and hour=18 and webrequest_source='text' and is_pageview;
count(1)
34767312
Time taken: 41.352 seconds, Fetched 1 row(s)

> select count(*) from mforns.webrequest where year=2024 and month=8 and day=13 and hour=18 and webrequest_source='text' and is_pageview;
count(1)
34767313
Time taken: 40.784 seconds, Fetched 1 row(s)