跳到主要内容
版本:3.2

存算一体

本教程涵盖了以下内容:

  • 使用 Docker Compose 部署 StarRocks 存算一体集群。
  • 导入数据集,并在导入过程中进行基本的数据转换。
  • 查询分析数据。

本教程中使用的数据由 NYC OpenData 和 NOAA 的 National Centers for Environmental Information 提供。教程仅截取了数据集的部分字段。


前提条件

Docker

  • 安装 Docker
  • 为 Docker 分配 4 GB RAM。
  • 为 Docker 分配 10 GB 的空闲磁盘空间。

SQL 客户端

您可以使用 Docker 环境中提供的 MySQL Client,也可以使用其他兼容 MySQL 的客户端,包括本教程中涉及的 DBeaver 和 MySQL Workbench。

curl

curl 命令用于向 StarRocks 中导入数据以及下载数据集。您可以通过在终端运行 curlcurl.exe 来检查您的操作系统是否已安装 curl。如果未安装 curl,请点击此处获取 curl


术语

FE

FE 节点负责元数据管理、客户端连接管理、查询计划和查询调度。每个 FE 在其内存中存储和维护完整的元数据副本,确保每个 FE 都能提供无差别的服务。

CN

CN 节点在存算分离存算一体集群中负责执行查询。

BE

BE 节点在存算一体集群中负责数据存储和执行查询。

备注

当前教程不包含 CN 节点,以上内容仅供您了解 BE 和 CN 之间的区别。


启动 StarRocks

运行以下命令启动 StarRocks 存算一体集群。

docker run -p 9030:9030 -p 8030:8030 -p 8040:8040 -itd \
--name quickstart starrocks/allin1-ubuntu

下载数据集

将教程所需的数据集下载到您的主机上,无需在容器内下载。

下载纽约市交通事故数据

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/72505394728.csv

SQL 客户端

当前教程可以使用以下客户端进行测试,您只需选择其中一个:

  • MySQL Client:您可以从 Docker 环境或您的本机运行 MySQL Client。
  • DBeaver(社区版或专业版)
  • MySQL Workbench

配置客户端

您可以从 StarRocks 容器 quickstart 中直接运行 MySQL Client:

docker exec -it quickstart \
mysql -P 9030 -h 127.0.0.1 -u root --prompt="StarRocks > "
提示

所有 docker compose 命令必须从包含 docker-compose.yml 文件的目录中运行。

如果您需要安装 MySQL Client,请点击展开以下 安装 MySQL 客户端 部分:

安装 MySQL 客户端
  • macOS:如果您使用 Homebrew 并且不需要安装 MySQL 服务器,请运行 brew install mysql 安装 MySQL Client。
  • Linux:请检查您的 mysql 客户端的 Repository。例如,运行 yum install mariadb
  • Microsoft Windows:安装 MySQL Community Server 后,运行提供的客户端,或在 WSL 中运行 mysql

使用 SQL 客户端连接到 StarRocks

客户端配置完成后,连接至 StarRocks。


建表

创建数据库

执行以下语句创建数据库 quickstart,并切换到该数据库下。

CREATE DATABASE IF NOT EXISTS quickstart;

USE quickstart;

建表

crashdata 表

创建 crashdata 表,用于存储交通事故数据集中的数据。该表的字段经过裁剪,仅包含与该教程相关字段。

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
);

weatherdata 表

创建 weatherdata 表,用于存储天气数据集中的数据。该表的字段同样经过裁剪,仅包含与该教程相关字段。

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
);

导入数据集

StarRocks 提供多种数据导入方法。本教程使用 curl 命令发起 Stream Load 任务导入数据。

Stream Load 使用的 curl 命令虽然看起来复杂,但本教程在最后部分提供了详细解释。建议您先运行该命令导入数据,然后在教程结束后了解有关数据导入的详细内容。

导入纽约市交通事故数据

打开一个新的 Shell 终端,进入数据集文件所在的路径,然后运行以下命令。

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

运行命令后,系统会提示您输入密码。由于您尚未为 root 用户分配密码,所以只需按 Enter 键跳过。

以下是上述命令的返回。其中 StatusSuccess,表示导入成功。NumberFilteredRows 为 1,表示数据集中包含一行错误数据,StarRocks 在导入过程将该行过滤。

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://127.0.0.1:8040/api/_load_error_log?file=error_log_da41dd88276a7bfc_739087c94262ae9f"
}%

当 Stream Load 发生错误时,StarRocks 会返回一个包含错误信息的 URL,您可以通过 curl 命令查看。

curl http://127.0.0.1:8040/api/_load_error_log?file=error_log_da41dd88276a7bfc_739087c94262ae9f
错误信息
Error: Value count does not match column count. Expect 29, but got 32.
Column delimiter: 44,Row delimiter: 10.. Row: 09/06/2015,14:15,,,40.6722269,-74.0110059,"(40.6722269, -74.0110059)",,,"R/O 1 BEARD ST. ( IKEA'S
09/14/2015,5:30,BRONX,10473,40.814551,-73.8490955,"(40.814551, -73.8490955)",TORRY AVENUE ,NORTON AVENUE ,,0,0,0,0,0,0,0,0,Driver Inattention/Distraction,Unspecified,,,,3297457,PASSENGER VEHICLE,PASSENGER VEHICLE,,,

导入天气数据

依照同样的方式导入天气数据。

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

查询数据并回答问题

在 SQL 客户端中运行以下查询。

查询一:纽约市每小时交通事故数量

SELECT COUNT(*),
date_trunc("hour", crashdata.CRASH_DATE) AS Time
FROM crashdata
GROUP BY Time
ORDER BY Time ASC
LIMIT 200;

以下截取了部分输出数据:

|       14 | 2014-01-06 06:00:00 |
| 16 | 2014-01-06 07:00:00 |
| 43 | 2014-01-06 08:00:00 |
| 44 | 2014-01-06 09:00:00 |
| 21 | 2014-01-06 10:00:00 |
| 28 | 2014-01-06 11:00:00 |
| 34 | 2014-01-06 12:00:00 |
| 31 | 2014-01-06 13:00:00 |
| 35 | 2014-01-06 14:00:00 |
| 36 | 2014-01-06 15:00:00 |
| 33 | 2014-01-06 16:00:00 |
| 40 | 2014-01-06 17:00:00 |
| 35 | 2014-01-06 18:00:00 |
| 23 | 2014-01-06 19:00:00 |
| 16 | 2014-01-06 20:00:00 |
| 12 | 2014-01-06 21:00:00 |
| 17 | 2014-01-06 22:00:00 |
| 14 | 2014-01-06 23:00:00 |
| 10 | 2014-01-07 00:00:00 |
| 4 | 2014-01-07 01:00:00 |
| 1 | 2014-01-07 02:00:00 |
| 3 | 2014-01-07 03:00:00 |
| 2 | 2014-01-07 04:00:00 |
| 6 | 2014-01-07 06:00:00 |
| 16 | 2014-01-07 07:00:00 |
| 41 | 2014-01-07 08:00:00 |
| 37 | 2014-01-07 09:00:00 |
| 33 | 2014-01-07 10:00:00 |

从结果可以看出,1 月 6 日和 7 日(正常工作日的周一和周二)的早高峰(08:00 至 10:00)每小时约有 40 起事故,较其他时间时间段事故数量明显更多。除此之外,晚高峰(17:00 左右)的事故数量也接近这个数字。

查询二:纽约市的平均气温

SELECT avg(HourlyDryBulbTemperature),
date_trunc("hour", weatherdata.DATE) AS Time
FROM weatherdata
GROUP BY Time
ORDER BY Time ASC
LIMIT 100;

以下截取了部分输出数据:

+-------------------------------+---------------------+
| avg(HourlyDryBulbTemperature) | Time |
+-------------------------------+---------------------+
| 25 | 2014-01-01 00:00:00 |
| 25 | 2014-01-01 01:00:00 |
| 24 | 2014-01-01 02:00:00 |
| 24 | 2014-01-01 03:00:00 |
| 24 | 2014-01-01 04:00:00 |
| 24 | 2014-01-01 05:00:00 |
| 25 | 2014-01-01 06:00:00 |
| 26 | 2014-01-01 07:00:00 |

查询三:能见度情况对驾驶安全的影响

为了解能见度情况对驾驶安全的影响,需要对两张表格的 DATETIME 列进行 JOIN,分析在能见度不佳的情况下(0 到 1.0 英里之间)时的交通事故数量。

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;

以下截取了部分输出数据:

+---------+--------+------------+---------------+-------------------+
| Crashes | Temp_F | Visibility | Precipitation | Hour |
+---------+--------+------------+---------------+-------------------+
| 129 | 32 | 0.25 | 0.12 | 03 Feb 2014 08:00 |
| 114 | 32 | 0.25 | 0.12 | 03 Feb 2014 09:00 |
| 104 | 23 | 0.33 | 0.03 | 09 Jan 2015 08:00 |
| 96 | 26.3 | 0.33 | 0.07 | 01 Mar 2015 14:00 |
| 95 | 26 | 0.37 | 0.12 | 01 Mar 2015 15:00 |
| 93 | 35 | 0.75 | 0.09 | 18 Jan 2015 09:00 |
| 92 | 31 | 0.25 | 0.12 | 03 Feb 2014 10:00 |
| 87 | 26.8 | 0.5 | 0.09 | 01 Mar 2015 16:00 |
| 85 | 55 | 0.75 | 0.20 | 23 Dec 2015 17:00 |
| 85 | 20 | 0.62 | 0.01 | 06 Jan 2015 11:00 |
| 83 | 19.6 | 0.41 | 0.04 | 05 Mar 2015 13:00 |
| 80 | 20 | 0.37 | 0.02 | 06 Jan 2015 10:00 |
| 76 | 26.5 | 0.25 | 0.06 | 05 Mar 2015 09:00 |
| 71 | 26 | 0.25 | 0.09 | 05 Mar 2015 10:00 |
| 71 | 24.2 | 0.25 | 0.04 | 05 Mar 2015 11:00 |

从以上结果可以得出,在能见度较低的一小时内的最高交通事故数量是 129。

当然,除了能见度因素外,还有其他因素需要考虑在内:

  • 2014 年 2 月 3 日是星期一(工作日)
  • 上午 8 点是早高峰时段
  • 当时正在下雨(一小时内降水量为 0.12 英寸)
  • 温度为 32 华氏度(水的冰点)

查询四:结冰情况对驾驶安全的影响

由于路面上水大约会在 40 华氏度时开始转变为冰,因此以下查询分析了温度区间为 0 到 40 华氏度的交通事故数量。

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.HourlyDryBulbTemperature BETWEEN 0.0 AND 40.5
GROUP BY Hour
ORDER BY Crashes DESC
LIMIT 100;

以下截取了部分输出数据:

+---------+--------+------------+---------------+-------------------+
| Crashes | Temp_F | Visibility | Precipitation | Hour |
+---------+--------+------------+---------------+-------------------+
| 192 | 34 | 1.5 | 0.09 | 18 Jan 2015 08:00 |
| 170 | 21 | NULL | | 21 Jan 2014 10:00 |
| 145 | 19 | NULL | | 21 Jan 2014 11:00 |
| 138 | 33.5 | 5 | 0.02 | 18 Jan 2015 07:00 |
| 137 | 21 | NULL | | 21 Jan 2014 09:00 |
| 129 | 32 | 0.25 | 0.12 | 03 Feb 2014 08:00 |
| 114 | 32 | 0.25 | 0.12 | 03 Feb 2014 09:00 |
| 104 | 23 | 0.7 | 0.04 | 09 Jan 2015 08:00 |
| 98 | 16 | 8 | 0.00 | 06 Mar 2015 08:00 |
| 96 | 26.3 | 0.33 | 0.07 | 01 Mar 2015 14:00 |

结果显示,2015 年 1 月 18 日发生了大量交通事故。虽然当天是星期天早晨,但根据 weather.com 显示,当天下了一场大雪,导致许多交通事故。


总结

在本教程中,您完成了以下目标:

  • 在 Docker 容器中部署了 StarRocks
  • 向 StarRocks 导入了纽约市的交通事故数据和天气数据
  • 使用 JOIN 查询分析数据

除以上内容外,本教程有意略过了在 Stream Load 命令中数据转换相关的参数说明。关于此内容的详细信息在下一小节中提供。


关于 curl 命令的注释

StarRocks 的 Stream Load 导入方式需要使用 curl 命令,涉及许多参数。以下仅列出此教程中需要使用的参数,其余参数请参考更多信息部分。

--location-trusted

此参数用于允许 curl 将认证凭据传输给任何重定向的 URL。

-u root

用于登录 StarRocks 的用户名。

-T filename

T 代表传输(Transfer),用于指定需要传输的文件名。

label:name-num

与此 Stream Load 作业关联的标签。标签必须唯一,因此如果多次运行作业,您可以添加一个数字保持递增。

column_separator:,

如果导入的文件使用单个 , 作为列分隔符,则设置如上所示。如果使用其他分隔符,则在此处设置该分隔符。常见分隔符包括 \t,|

skip_header:1

某些 CSV 文件会在首行(Header)记录所有的列名,还有些会在第二行记录所有列的数据类型信息。如果 CSV 文件有一或两个 Header 行,需要将 skip_header 设置为 12。如果您使用的 CSV 没有 Header 行,请将其设置为 0

enclose:\"

如果某些字段包含带有逗号的字符串,则需要用双引号括起该字段。本教程使用的示例数据集中,地理位置信息包含逗号,因此需将 enclose 设置为 \",其中 \ 用于转义 "

max_filter_ratio:1

导入数据中允许出现错误行的比例。理想情况下,应将其设置为 0,即当导入的数据中有任意一行出现错误时,导入作业会失败。本教程中需要将其设置为 1,即在调试过程中,允许所有数据行出现错误。

columns:

此参数用于将 CSV 文件中的列映射到 StarRocks 表中的列。当前教程中使用的 CSV 文件中有大量的列,而 StarRocks 表中的列经过裁剪,仅保留部分列。未包含在表中的列在导入过程中都将被跳过。

本教程中的 columns: 参数中还包含数据转换逻辑。在 CSV 文件中经常会有不符合标准的日期和时间。以下是将日期和时间数据转换为 DATETIME 类型的逻辑:

数据转换

如下所示,数据集中的日期以 MM/DD/YYYY 为格式,时间以 HH:MI 为格式。

08/05/2014,9:10,BRONX,10469,40.8733019,-73.8536375,"(40.8733019, -73.8536375)",

由于 StarRocks 中的 DATETIME 格式为 YYYY-MM-DD HH:MI:SS,因此需要转换数据集中的数据,将两列数据合并,并以空格分隔。则此处 columns: 参数应为:

-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')

通过设置以上参数可实现以下目标:

  1. 将 CSV 文件的第一列内容分配给 tmp_CRASH_DATE 列;
  2. 将 CSV 文件的第二列内容分配给 tmp_CRASH_TIME 列;
  3. 通过 concat_ws() 函数,使用空格将 tmp_CRASH_DATE 列和 tmp_CRASH_TIME 列连接在一起;
  4. 通过 str_to_date() 函数使用连接后的字符串生成 DATETIME 数据;
  5. 将生成的 DATETIME 数据存储在列 CRASH_DATE 中。

更多信息