data migration

Database design and implementation

Database design

Seperate python Job was written to conduct Data Migration

• Initial preparation

  • Column Data
    • Temperature (C), Apparent Temperature (C) columns round-off to 1 decimal place
    • Visibility (km) round-off to 1 decimal place
    • Formatted Date modified to this format '2008-07-12 17:00:00'

• Running the migration application populate these values in the main.py`` run the python main.py`` dbUrl=url dbUser=user dbPassword=password database=database name port=port number

• Normalization

  • weather_data table `CREATE TABLE weather_analytics_data_db.weather_data ( weather_data_id int NOT NULL AUTO_INCREMENT, observed_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, summary varchar(30) default null, precip_type ENUM ('rain', 'snow', 'sleet', 'hail') DEFAULT null, temperature_celsius decimal default 0.0, apprnt_temperature_celsius decimal default 0.0, humidity decimal default 0.0, wind_speed_km_h decimal default 0.0, wind_bearing_degrees int default 0, visibility_km decimal default 0.0, cloud_cover tinyint(1) default 0, pressure_millibar decimal default 0.0, daily_summary varchar(150) default null, primary key (weather_data_id), unique key weather_id_observed_at (weather_data_id,observed_at) )

  • weather_summary table CREATE TABLE weather_analytics_data_db.weather_summary ( weather_summary_id int NOT NULL AUTO_INCREMENT, summary varchar(30) default null, precip_type ENUM ('rain', 'snow', 'sleet', 'hail') DEFAULT null, daily_summary varchar(150) default null, primary key (weather_summary_id), unique key weather_summary_id_precip_type (summary,precip_type,daily_summary) ) `