基于Hive的天气情况大数据分析系统（通过hive进行大数据分析将分析的数据通过sqoop导入到mysql，通过Django基于mysql的数据做可视化）_基于hive的天气数据分析系统设计与实现

city STRING

)ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’;

– 插入数据到weather_data表（示例数据）
INSERT INTO TABLE weather_data VALUES
(1, ‘2022-01-01 周六’, ‘6°’, ‘-7°’, ‘晴’, ‘西北风3级’, ‘北京’),
(2, ‘2022-01-02 周日’, ‘2°’, ‘-7°’, ‘多云’, ‘南风2级’, ‘北京’);

– 创建etl_weather_data表
CREATE TABLE IF NOT EXISTS etl_weather_data (
date STRING,
day_of_week STRING,
high_temperature INT,
low_temperature INT,
weather STRING,
wind_direction STRING,
wind_speed STRING,
city STRING
);

– 插入数据到etl_weather_data表
INSERT INTO TABLE etl_weather_data
SELECT
SUBSTR(date, 1, INSTR(date, ’ ') - 1) AS date,
SUBSTR(date, INSTR(date, ’ ') + 1) AS day_of_week,
CAST(SUBSTR(high_temperature, 1, INSTR(high_temperature, ‘°’) - 1) AS INT) AS high_temperature,
CAST(SUBSTR(low_temperature, 1, INSTR(low_temperature, ‘°’) - 1) AS INT) AS low_temperature,
weather,
REGEXP_REPLACE(SUBSTR(wind_direction, 1, INSTR(wind_direction, ‘级’) - 1), ‘[0-9]’, ‘’) AS wind_direction,
SUBSTR(SUBSTR(wind_direction, INSTR(wind_direction, ‘风’) + 1),1,1) AS wind_speed,
city
FROM
weather_data;

– 1.统计一年中每个城市晴天个数的top10
CREATE TABLE IF NOT EXISTS top_sunny_cities (
city STRING,
sunny_days_count INT
);