1
2
3
4
5
6
7

#下载交通事故数据集

curl -O https://raw.githubusercontent.com/StarRocks/demo/master/documentation-samples/quickstart/datasets/NYPD_Crash_Data.csv

# 下载天气数据

curl -O https://raw.githubusercontent.com/StarRocks/demo/master/documentation-samples/quickstart/datasets/NYPD_Crash_Data.csv

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

CREATE STORAGE VOLUME shared

TYPE = S3

LOCATIONS = ("s3://starrocks/shared/")

PROPERTIES

(

"enabled" = "true",

"aws.s3.endpoint" = "http://10.7.10.190:9000",

"aws.s3.use_aws_sdk_default_behavior" = "false",

"aws.s3.enable_ssl" = "false",

"aws.s3.use_instance_profile" = "false",

"aws.s3.access_key" = "AAAAAAAAAAAAAAA",

"aws.s3.secret_key" = "BBBBBBBBBBBBBBBBBBBBBBBBBBBBB"

);

SET shared AS DEFAULT STORAGE VOLUME;

1

CREATE DATABASE IF NOT EXISTS quickstart;USE quickstart;

1

CREATE TABLE IF NOT EXISTS crashdata (    CRASH_DATE DATETIME,    BOROUGH STRING,    ZIP_CODE STRING,    LATITUDE INT,    LONGITUDE INT,    LOCATION STRING,    ON_STREET_NAME STRING,    CROSS_STREET_NAME STRING,    OFF_STREET_NAME STRING,    CONTRIBUTING_FACTOR_VEHICLE_1 STRING,    CONTRIBUTING_FACTOR_VEHICLE_2 STRING,    COLLISION_ID INT,    VEHICLE_TYPE_CODE_1 STRING,    VEHICLE_TYPE_CODE_2 STRING);

1

CREATE TABLE IF NOT EXISTS weatherdata (    DATE DATETIME,    NAME STRING,    HourlyDewPointTemperature STRING,    HourlyDryBulbTemperature STRING,    HourlyPrecipitation STRING,    HourlyPresentWeatherType STRING,    HourlyPressureChange STRING,    HourlyPressureTendency STRING,    HourlyRelativeHumidity STRING,    HourlySkyConditions STRING,    HourlyVisibility STRING,    HourlyWetBulbTemperature STRING,    HourlyWindDirection STRING,    HourlyWindGustSpeed STRING,    HourlyWindSpeed STRING);

1

curl --location-trusted -u root             \    -T ./NYPD_Crash_Data.csv                \    -H "label:crashdata-0"                  \    -H "column_separator:,"                 \    -H "skip_header:1"                      \    -H "enclose:\""                         \    -H "max_filter_ratio:1"                 \    -H "columns:tmp_CRASH_DATE, tmp_CRASH_TIME, CRASH_DATE=str_to_date(concat_ws(' ', tmp_CRASH_DATE, tmp_CRASH_TIME), '%m/%d/%Y %H:%i'),BOROUGH,ZIP_CODE,LATITUDE,LONGITUDE,LOCATION,ON_STREET_NAME,CROSS_STREET_NAME,OFF_STREET_NAME,NUMBER_OF_PERSONS_INJURED,NUMBER_OF_PERSONS_KILLED,NUMBER_OF_PEDESTRIANS_INJURED,NUMBER_OF_PEDESTRIANS_KILLED,NUMBER_OF_CYCLIST_INJURED,NUMBER_OF_CYCLIST_KILLED,NUMBER_OF_MOTORIST_INJURED,NUMBER_OF_MOTORIST_KILLED,CONTRIBUTING_FACTOR_VEHICLE_1,CONTRIBUTING_FACTOR_VEHICLE_2,CONTRIBUTING_FACTOR_VEHICLE_3,CONTRIBUTING_FACTOR_VEHICLE_4,CONTRIBUTING_FACTOR_VEHICLE_5,COLLISION_ID,VEHICLE_TYPE_CODE_1,VEHICLE_TYPE_CODE_2,VEHICLE_TYPE_CODE_3,VEHICLE_TYPE_CODE_4,VEHICLE_TYPE_CODE_5" \    -XPUT http://localhost:8030/api/quickstart/crashdata/_stream_load

1

Enter host password for user 'root':{    "TxnId": 2,    "Label": "crashdata-0",    "Status": "Success",    "Message": "OK",    "NumberTotalRows": 423726,    "NumberLoadedRows": 423725,    "NumberFilteredRows": 1,    "NumberUnselectedRows": 0,    "LoadBytes": 96227746,    "LoadTimeMs": 1013,    "BeginTxnTimeMs": 21,    "StreamLoadPlanTimeMs": 63,    "ReadDataTimeMs": 563,    "WriteDataTimeMs": 870,    "CommitAndPublishTimeMs": 57,    "ErrorURL": "http://10.5.0.3:8040/api/_load_error_log?file=error_log_da41dd88276a7bfc_739087c94262ae9f"}%

1

curl http://10.5.0.3:8040/api/_load_error_log?file=error_log_da41dd88276a7bfc_739087c94262ae9f

1

curl --location-trusted -u root             \    -T ./72505394728.csv                    \    -H "label:weather-0"                    \    -H "column_separator:,"                 \    -H "skip_header:1"                      \    -H "enclose:\""                         \    -H "max_filter_ratio:1"                 \    -H "columns: STATION, DATE, LATITUDE, LONGITUDE, ELEVATION, NAME, REPORT_TYPE, SOURCE, HourlyAltimeterSetting, HourlyDewPointTemperature, HourlyDryBulbTemperature, HourlyPrecipitation, HourlyPresentWeatherType, HourlyPressureChange, HourlyPressureTendency, HourlyRelativeHumidity, HourlySkyConditions, HourlySeaLevelPressure, HourlyStationPressure, HourlyVisibility, HourlyWetBulbTemperature, HourlyWindDirection, HourlyWindGustSpeed, HourlyWindSpeed, Sunrise, Sunset, DailyAverageDewPointTemperature, DailyAverageDryBulbTemperature, DailyAverageRelativeHumidity, DailyAverageSeaLevelPressure, DailyAverageStationPressure, DailyAverageWetBulbTemperature, DailyAverageWindSpeed, DailyCoolingDegreeDays, DailyDepartureFromNormalAverageTemperature, DailyHeatingDegreeDays, DailyMaximumDryBulbTemperature, DailyMinimumDryBulbTemperature, DailyPeakWindDirection, DailyPeakWindSpeed, DailyPrecipitation, DailySnowDepth, DailySnowfall, DailySustainedWindDirection, DailySustainedWindSpeed, DailyWeather, MonthlyAverageRH, MonthlyDaysWithGT001Precip, MonthlyDaysWithGT010Precip, MonthlyDaysWithGT32Temp, MonthlyDaysWithGT90Temp, MonthlyDaysWithLT0Temp, MonthlyDaysWithLT32Temp, MonthlyDepartureFromNormalAverageTemperature, MonthlyDepartureFromNormalCoolingDegreeDays, MonthlyDepartureFromNormalHeatingDegreeDays, MonthlyDepartureFromNormalMaximumTemperature, MonthlyDepartureFromNormalMinimumTemperature, MonthlyDepartureFromNormalPrecipitation, MonthlyDewpointTemperature, MonthlyGreatestPrecip, MonthlyGreatestPrecipDate, MonthlyGreatestSnowDepth, MonthlyGreatestSnowDepthDate, MonthlyGreatestSnowfall, MonthlyGreatestSnowfallDate, MonthlyMaxSeaLevelPressureValue, MonthlyMaxSeaLevelPressureValueDate, MonthlyMaxSeaLevelPressureValueTime, MonthlyMaximumTemperature, MonthlyMeanTemperature, MonthlyMinSeaLevelPressureValue, MonthlyMinSeaLevelPressureValueDate, MonthlyMinSeaLevelPressureValueTime, MonthlyMinimumTemperature, MonthlySeaLevelPressure, MonthlyStationPressure, MonthlyTotalLiquidPrecipitation, MonthlyTotalSnowfall, MonthlyWetBulb, AWND, CDSD, CLDD, DSNW, HDSD, HTDD, NormalsCoolingDegreeDay, NormalsHeatingDegreeDay, ShortDurationEndDate005, ShortDurationEndDate010, ShortDurationEndDate015, ShortDurationEndDate020, ShortDurationEndDate030, ShortDurationEndDate045, ShortDurationEndDate060, ShortDurationEndDate080, ShortDurationEndDate100, ShortDurationEndDate120, ShortDurationEndDate150, ShortDurationEndDate180, ShortDurationPrecipitationValue005, ShortDurationPrecipitationValue010, ShortDurationPrecipitationValue015, ShortDurationPrecipitationValue020, ShortDurationPrecipitationValue030, ShortDurationPrecipitationValue045, ShortDurationPrecipitationValue060, ShortDurationPrecipitationValue080, ShortDurationPrecipitationValue100, ShortDurationPrecipitationValue120, ShortDurationPrecipitationValue150, ShortDurationPrecipitationValue180, REM, BackupDirection, BackupDistance, BackupDistanceUnit, BackupElements, BackupElevation, BackupEquipment, BackupLatitude, BackupLongitude, BackupName, WindEquipmentChangeDate" \    -XPUT http://localhost:8030/api/quickstart/weatherdata/_stream_load

1

SELECT COUNT(*),       date_trunc("hour", crashdata.CRASH_DATE) AS TimeFROM crashdataGROUP BY TimeORDER BY Time ASCLIMIT 200;

1

SELECT avg(HourlyDryBulbTemperature),       date_trunc("hour", weatherdata.DATE) AS TimeFROM weatherdataGROUP BY TimeORDER BY Time ASCLIMIT 100;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT COUNT(DISTINCT c.COLLISION_ID) AS Crashes,  
truncate(avg(w.HourlyDryBulbTemperature), 1) AS Temp_F,  
truncate(avg(w.HourlyVisibility), 2) AS Visibility,  
max(w.HourlyPrecipitation) AS Precipitation,  
date_format((date_trunc("hour", c.CRASH_DATE)), '%d %b %Y %H:%i') AS Hour  
FROM crashdata c  
LEFT JOIN weatherdata w  
ON date_trunc("hour", c.CRASH_DATE)=date_trunc("hour", w.DATE)  
WHERE w.HourlyVisibility BETWEEN 0.0 AND 1.0  
GROUP BY Hour  
ORDER BY Crashes DESC  
LIMIT 100;

1

SELECT COUNT(DISTINCT c.COLLISION_ID) AS Crashes,       truncate(avg(w.HourlyDryBulbTemperature), 1) AS Temp_F,       truncate(avg(w.HourlyVisibility), 2) AS Visibility,       max(w.HourlyPrecipitation) AS Precipitation,       date_format((date_trunc("hour", c.CRASH_DATE)), '%d %b %Y %H:%i') AS HourFROM crashdata cLEFT JOIN weatherdata wON date_trunc("hour", c.CRASH_DATE)=date_trunc("hour", w.DATE)WHERE w.HourlyDryBulbTemperature BETWEEN 0.0 AND 40.5 GROUP BY HourORDER BY Crashes DESCLIMIT 100;