跳到主要内容
版本:Latest-3.3

存算分离

在 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 中导入数据以及下载数据集。您可以通过在终端运行 curlcurl.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-festarrocks-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 文件中指定,分别为 miniouserminiopassword。成功登录后,点击 Create access key + 创建密钥。

MinIO 将生成一对密钥,点击 Create 生成并下载密钥。

点击 Create

备注

系统不会自动保存密钥,所以请确认保存密钥后再离开页面。


SQL 客户端

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

  • MySQL CLI:您可以从 Docker 环境或您的本机运行此客户端。
  • 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

下载数据

将教程所需的数据集下载到 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_keyaws.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 键跳过。

以下是上述命令的返回。其中 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://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/ 下的每个路径中是否有 datametadataschema 路径。

提示

在导入数据时,starrocks/shared/ 下的文件夹名称是动态生成的。您会在 shared 下面看到一个路径,然后在该路径下看到另外两个路径。在每个路径内,您都能看到 datametadataschema 路径。

MinIO Web UI


查询数据并回答问题

在 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

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 设置为 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 中。

更多信息