athena_shit.md

CREATE EXTERNAL TABLE IF NOT EXISTS ookla (
  quadkey string,
  tile string,
  avg_d_kbps int,
  avg_u_kbps int,
  avg_lat_ms int,
  avg_lat_down_ms int,
  avg_lat_up_ms int,
  tests int,
  devices int)
STORED AS PARQUET
LOCATION 's3://datasets-documentation/ookla/parquet/performance/type=fixed/'

CREATE TABLE ookla_iceberg (
  quadkey string,
  tile string,
  avg_d_kbps int,
  avg_u_kbps int,
  avg_lat_ms int,
  avg_lat_down_ms int,
  avg_lat_up_ms int,
  tests int,
  devices int,
  year_month date)
  PARTITIONED BY (year(year_month))
  LOCATION 's3://datasets-documentation/ookla/iceberg/'
  TBLPROPERTIES ( 'table_type' ='ICEBERG')
  
  
  INSERT INTO ookla_iceberg SELECT *, DATE_PARSE(
CONCAT_WS('-', REGEXP_EXTRACT("$path" , 'year=(\S+)/quarter', 1), 
LPAD(CAST(CAST(REGEXP_EXTRACT("$path" , 'quarter=(\S+)/', 1) AS int) * 3 AS varchar), 2, '0')
), '%Y-%m') as year_month FROM ookla