Weather Exploration with Drill

README.md

Getting weather data

Use this python script to download weather data: https://github.com/cjmatta/wundergroundloader

Drill config

Ensure that the csv type for drill has "extractHeader": true, set in the filesystem plugin, and that you haven't used the --strip-headers option in the wundergroundloader script.

Create View

CREATE OR REPLACE VIEW maprfs.cmatta.`weather_view` AS
SELECT`dir0` AS `airportcode`,
      cast(`dir1` AS INT) AS `year`,
      cast(`dir2` AS INT) AS `month`,
      CASE
          WHEN `temperaturef` = -9999.0 THEN NULL
          WHEN `temperaturef` = '-9999.0' THEN NULL
          ELSE cast(`temperaturef` AS FLOAT)
      END AS `temperaturef`,
      CASE
          WHEN `dewpointf` = -9999.0 THEN NULL
          WHEN `dewpointf` = '-9999.0' THEN NULL
          ELSE cast(`dewpointf` AS FLOAT)
      END AS `dewpointf`,
      CASE
          WHEN `humidity` = -9999.0 THEN NULL
          WHEN `humidity` = '-9999.0'THEN NULL
          ELSE cast(`humidity` AS FLOAT)
      END AS `humidity`,
      CASE
          WHEN `sealevelpressurein` = -9999.0 THEN NULL
          WHEN `sealevelpressurein` = '-9999.0' THEN NULL
          ELSE cast(`sealevelpressurein` AS FLOAT)
      END AS `sealevelpressurein`,
      CASE
          WHEN `visibilitymph` = -9999.0 THEN NULL
          WHEN `visibilitymph` = '-9999.0' THEN NULL
          ELSE cast(`visibilitymph` AS FLOAT)
      END AS `visibilitymph`,
      cast(`winddirection` AS varchar(128)),
      CASE
          WHEN `windspeedmph` = 'Calm' THEN NULL
          WHEN `windspeedmph` = -9999.0 THEN NULL
          WHEN `windspeedmph` = '-9999.0' THEN NULL
          ELSE cast(`windspeedmph` AS FLOAT)
      END AS `windspeedmph`,
      CASE
          WHEN `gustspeedmph` = '-' THEN NULL
          ELSE cast(`gustspeedmph` AS FLOAT)
      END AS `gustspeedmph`,
      CASE
          WHEN `precipitationin` = 'N/A' THEN NULL
          WHEN `precipitationin` = '' THEN NULL
          ELSE cast(`precipitationin` AS FLOAT)
      END AS `precipitationin`,
      cast(`events` AS varchar(128)) AS `events`,
      cast(`conditions` AS varchar(128)) AS `conditions`,
      cast(`winddirdegrees` AS INT) AS `winddirdegrees`,
      cast(`dateutc` AS TIMESTAMP) AS `dateutc`
FROM `maprfs`.`cmatta`.`weather/data`;