存算分离
在 StarRocks 存算分离架构中,数据存储在成本更低且更可靠的远程存储系统中,例如 Amazon S3、Google Cloud Storage、Azure Blob Storage 以及其他支持 S3 协议的存储,如 MinIO。热数据会被缓存到本地,在查询命中缓存的前提下,存算分离集群的查询性能与存算一体集群相当。同时,在存算分离架构下,计算节点(CN)可以在几秒内根据需要实现扩缩容。因此,StarRocks 的存算分离架构不仅降低了存储成本,保证了资源隔离性能,还提供了计算资源的弹性和可扩展性。
本教程涵盖以下内容:
- 使用 Docker Compose 部署 StarRocks 存算分离集群以及 MinIO 作为对象存储。
- 导入数据集,并在导入过程中进行基本的数据转换。
- 查询分析数据。
本教程中使用的数据由 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 中导入数据以及下载数据集。您可以通过在终端运行 curl
或 curl.exe
来检查您的操作系统是否已安装 curl。如果未安装 curl,请点击此处获取 curl。
术语
FE
FE 节点负责元数据管理、客户端连接管理、查询计划和查询调度。每个 FE 在其内存中存储和维护完整的元数据副本,确保每个 FE 都能提供无差别的服务。
CN
CN 节点在存算分离或存算一体集群中负责执行查询。
BE
BE 节点在存算一体集群中负责数据存储和执行查询。
当前教程不包含 BE 节点,以上内容仅供您了解 BE 和 CN 之间的区别。
启动 StarRocks
要基于对象存储部署 StarRocks 存算分离集群,您需要部署以下服务:
- 一个 FE 节点
- 一个 CN 节点
- 对象存储
本教程使用 MinIO 作为对象存储。
StarRocks 提供了一个 Docker Compose 文件,用于搭建包含以上三个必要容器的环境。
下载 Docker Compose 文件。
mkdir quickstart
cd quickstart
curl -O https://raw.githubusercontent.com/StarRocks/demo/master/documentation-samples/quickstart/docker-compose.yml
启动容器。
docker compose up -d
检查环境状态
成功启动后,FE 和 CN 节点大约需要 30 秒才能部署完成。需要通过 docker compose ps
命令检查服务的运行状态,直到 starrocks-fe
和 starrocks-cn
的状态变为 healthy
。
docker compose ps
返回:
SERVICE CREATED STATUS PORTS
starrocks-cn 25 seconds ago Up 24 seconds (healthy) 0.0.0.0:8040->8040/tcp
starrocks-fe 25 seconds ago Up 24 seconds (healthy) 0.0.0.0:8030->8030/tcp, 0.0.0.0:9020->9020/tcp, 0.0.0.0:9030->9030/tcp
minio 25 seconds ago Up 24 seconds 0.0.0.0:9000-9001->9000-9001/tcp
以上返回不会显示 MinIO 容器的健康状态,您需要在下一步中通过使用 MinIO Web UI 验证其健康状态。
生成 MinIO 认证凭证
要访问 MinIO,您需要生成一个 访问密钥。
打开 MinIO Web UI
从浏览器进入 http://localhost:9001/access-keys
。登录用的用户名和密码已经在 Docker Compose 文件中指定,分别为 miniouser
和 miniopassword
。成功登录后,点击 Create access key + 创建密钥。
MinIO 将生成一对密钥,点击 Create 生成并下载密钥。
系统不会自动保存密钥,所以请确认保存密钥后再离开页面。
SQL 客户端
当前教程可以使用以下三个客户端进行测试,您只需选择其中一个:
- MySQL CLI:您可以从 Docker 环境或您的本机运行此客户端。
- DBeaver(社区版或专业版)
- MySQL Workbench
配置客户端
- mysql CLI
- DBeaver
- MySQL Workbench
您可以从 StarRocks FE 节点容器 starrocks-fe
中直接运行 MySQL Client:
docker compose exec starrocks-fe \
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
。
- 安装 DBeaver 并连接 StarRocks。
- 配置端口、IP 地址和用户名,并点击 Test Connection 测试连接。如果测试成功,请点击 Finish 完成配置。
- 安装 MySQL Workbench 并连接 StarRocks。
- 配置端口、IP 地址和用户名,并点击 Test Connection 测试连接。
- 在 Workbench 检查 MySQL 版本时,会弹出以下警告。您可以忽略这些警告,并选择不再提示:
下载数据
将教程所需的数据集下载到 FE 容器中。
在容器中启动 Shell。
docker compose exec starrocks-fe bash
创建用于存放数据集文件的路径。
mkdir quickstart
cd quickstart
下载交通事故数据集。
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
配置 StarRocks 存算分离集群
启动 StarRocks 以及 MinIO 后,您需要连接并配置 StarRocks 存算分离集群。
使用 SQL 客户端连接到 StarRocks
-
如果您使用 StarRocks 容器中的 MySQL Client,需要从包含
docker-compose.yml
文件的路径运行以下命令。docker compose exec starrocks-fe \
mysql -P9030 -h127.0.0.1 -uroot --prompt="StarRocks > " -
如果您使用其他客户端,请打开客户端并连接至 StarRocks。
创建存储卷
StarRocks 存算分离集群需要通过存储卷将数据持久化到对象存储中。
以下为创建存储卷时需要用到的信息:
- MinIO 服务器 URL 为
http://minio:9000
。 - 集群中的数据将通过存储卷持久化在
starrocks
存储桶中的shared
的文件夹下。请注意,shared
文件夹将在数据第一次导入集群时自动创建。 - MinIO 服务器未使用 SSL。
- 连接 MinIO 需要 Access Key 和 Secret Key 即为 MinIO Web UI 中创建的访问密钥。
- 您需要将该存储卷设置为默认存储卷。
运行以下命令之前,您需要将 aws.s3.access_key
和 aws.s3.secret_key
配置项的值替换为您在 MinIO 中创建的 Access Key 和 Secret Key。
CREATE STORAGE VOLUME shared
TYPE = S3
LOCATIONS = ("s3://starrocks/shared/")
PROPERTIES
(
"enabled" = "true",
"aws.s3.endpoint" = "http://minio: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;
创建成功后,查看该存储卷。
DESC STORAGE VOLUME shared\G
\G
表示将查询结果按列打印。如果您的 SQL 客户端不支持垂直格式输出,则需要将 \G
替换为 ;
。
返回:
*************************** 1. row ***************************
Name: shared
Type: S3
IsDefault: true
Location: s3://starrocks/shared/
Params: {"aws.s3.access_key":"******","aws.s3.secret_key":"******","aws.s3.endpoint":"http://minio:9000","aws.s3.region":"us-east-1","aws.s3.use_instance_profile":"false","aws.s3.use_aws_sdk_default_behavior":"false"}
Enabled: true
Comment:
1 row in set (0.03 sec)
建表
创建数据库
执行以下语句创建数据库 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 命令虽然看起来复杂,但本教程在最后部分提供了详细解释。建议您先运行该命令导入数据,然后在教程结束后了解有关数据导入的详细内容。
导入纽约市交通事故数据
通过 FE 容器的 Shell Session 进入数据集文件所在的路径,然后运行以下命令。
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 键跳过。
以下是上述命令的返回。其中 Status
为 Success
,表示导入成功。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://10.5.0.3:8040/api/_load_error_log?file=error_log_da41dd88276a7bfc_739087c94262ae9f"
}%
当 Stream Load 发生错误时,StarRocks 会返回一个包含错误信息的 URL。由于容器具有私有 IP 地址,您必须通过容器中的 Shell Session 运行 curl 命令查看。
curl http://10.5.0.3:8040/api/_load_error_log?file=error_log_da41dd88276a7bfc_739087c94262ae9f
点击下方 错误信息 查看 URL 中包含的错误信息。
错误信息
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
验证数据是否存储在 MinIO 中
打开 MinIO Web UI 并验证在 starrocks/shared/
下的每个路径中是否有 data
、metadata
和 schema
路径。
在导入数据时,starrocks/shared/
下的文件夹名称是动态生成的。您会在 shared
下面看到一个路径,然后在该路径下看到另外两个路径。在每个路径内,您都能看到 data
、metadata
和 schema
路径。
查询数据并回答问题
在 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 显示,当天下了一场大雪,导致许多交通事故。
存算分离集群配置细节
本小节介绍 StarRocks 存算分离集群的配置信息。
CN 配置
本教程中使用的 CN 配置是默认配置。如下所示。您无需进行任何更改。
sys_log_level = INFO
be_port = 9060
be_http_port = 8040
heartbeat_service_port = 9050
brpc_port = 8060
FE 配置
FE 配置与默认配置略有不同,需要额外添加与对象存储相关的配置项。
以下为 docker-compose.yml
文件中用于生成 FE 配置的命令。
command: >
bash -c "echo run_mode=shared_data >> /opt/starrocks/fe/conf/fe.conf &&
echo cloud_native_meta_port=6090 >> /opt/starrocks/fe/conf/fe.conf &&
echo aws_s3_path=starrocks >> /opt/starrocks/fe/conf/fe.conf &&
echo aws_s3_endpoint=minio:9000 >> /opt/starrocks/fe/conf/fe.conf &&
echo aws_s3_use_instance_profile=false >> /opt/starrocks/fe/conf/fe.conf &&
echo cloud_native_storage_type=S3 >> /opt/starrocks/fe/conf/fe.conf &&
echo aws_s3_use_aws_sdk_default_behavior=false >> /opt/starrocks/fe/conf/fe.conf &&
sh /opt/starrocks/fe/bin/start_fe.sh"
以上命令会生成以下 FE 配置文件 fe.conf:
LOG_DIR = ${STARROCKS_HOME}/log
DATE = "$(date +%Y%m%d-%H%M%S)"
JAVA_OPTS="-Dlog4j2.formatMsgNoLookups=true -Xmx8192m -XX:+UseMembar -XX:SurvivorRatio=8 -XX:MaxTenuringThreshold=7 -XX:+PrintGCDateStamps -XX:+PrintGCDetails -XX:+UseConcMarkSweepGC -XX:+UseParNewGC -XX:+CMSClassUnloadingEnabled -XX:-CMSParallelRemarkEnabled -XX:CMSInitiatingOccupancyFraction=80 -XX:SoftRefLRUPolicyMSPerMB=0 -Xloggc:${LOG_DIR}/fe.gc.log.$DATE -XX:+PrintConcurrentLocks"
JAVA_OPTS_FOR_JDK_11="-Dlog4j2.formatMsgNoLookups=true -Xmx8192m -XX:+UseG1GC -Xlog:gc*:${LOG_DIR}/fe.gc.log.$DATE:time"
sys_log_level = INFO
http_port = 8030
rpc_port = 9020
query_port = 9030
edit_log_port = 9010
mysql_service_nio_enabled = true
run_mode=shared_data
aws_s3_path=starrocks
aws_s3_endpoint=minio:9000
aws_s3_use_instance_profile=false
cloud_native_storage_type=S3
aws_s3_use_aws_sdk_default_behavior=false
参数的具体解释如下:
run_mode=shared_data
该参数用于启用存算分离模式。
aws_s3_path=starrocks
存储桶名称。
aws_s3_endpoint=minio:9000
MinIO 端点,包括端口号。
aws_s3_use_instance_profile=false
是否使用 Instance Profile 作为凭证。因为您需要使用访问密钥连接 MinIO,需要该项设置为 false。
cloud_native_storage_type=S3
用于指定对象存储类型。对于 MinIO,需要将该项设置为 S3。
aws_s3_use_aws_sdk_default_behavior=false
是否使用 AWS S3 默认 SDK 认证方式。在使用 MinIO 时,始终将此参数设置为 false。
总结
在本教程中,您完成了以下目标:
- 在 Docker 中部署了 StarRocks 和 MinIO
- 创建了 MinIO 访问密钥
- 配置了使用 MinIO 的 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
设置为 1
或 2
。如果您使用的 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')
通过设置以上参数可实现以下目标:
- 将 CSV 文件的第一列内容分配给
tmp_CRASH_DATE
列; - 将 CSV 文件的第二列内容分配给
tmp_CRASH_TIME
列; - 通过
concat_ws()
函数,使用空格将tmp_CRASH_DATE
列和tmp_CRASH_TIME
列连接在一起; - 通过
str_to_date()
函数使用连接后的字符串生成 DATETIME 数据; - 将生成的 DATETIME 数据存储在列
CRASH_DATE
中。
更多信息
- StarRocks 表设计
- Stream Load
- 纽约市交通事故数据 数据集由纽约市提供,受到以下使用条款和隐私政策的约束。
- 气象数据(LCD)由 NOAA 提供,附带免责声明和隐私政策。