Разделение хранения и вычислений

В системах, которые разделяют хранение и вычисления, данные хранятся в недорогих надежных удаленных системах хранения, таких как Amazon S3, Google Cloud Storage, Azure Blob Storage и других S3-совместимых хранилищах, таких как MinIO. Горячие данные кэшируются локально, и когда кэш попадает в цель, производительность запросов сопоставима с архитектурой связанного хранения и вычислений. Вычислительные узлы (CN) могут быть добавлены или удалены по требованию в течение секунд. Эта архитектура снижает стоимость хранения, обеспечивает лучшую изоляцию ресурсов и предоставляет эластичность и масштабируемость.

Этот учебник охватывает:

Запуск Selena в Docker контейнерах
Использование MinIO для объектного хранения
Настройка Selena для shared-data
Загрузка двух публичных наборов данных
Анализ данных с помощью SELECT и JOIN
Базовое преобразование данных (T в ETL)

Используемые данные предоставлены NYC OpenData и Национальными центрами экологической информации NOAA.

Оба этих набора данных очень большие, и поскольку этот учебник предназначен для того, чтобы помочь вам познакомиться с работой с Selena, мы не будем загружать данные за последние 120 лет. Вы можете запустить Docker образ и загрузить эти данные на машине с 4 ГБ оперативной памяти, выделенной для Docker. Для более крупных отказоустойчивых и масштабируемых развертываний у нас есть другая документация, которую мы предоставим позже.

В этом документе много информации, и она представлена с пошаговым содержанием в начале и техническими деталями в конце. Это сделано для достижения следующих целей в данном порядке:

Позволить читателю загрузить данные в shared-data развертывание и проанализировать эти данные.
Предоставить детали конфигурации для shared-data развертываний.
Объяснить основы преобразования данных во время загрузки.

Предварительные требования

Docker

Docker
4 ГБ оперативной памяти, выделенной для Docker
10 ГБ свободного дискового пространства, выделенного для Docker

SQL клиент

Вы можете использовать SQL клиент, предоставленный в Docker окружении, или использовать клиент в вашей системе. Многие MySQL совместимые клиенты будут работать, и это руководство охватывает конфигурацию DBeaver и MySQL Workbench.

curl

curl используется для отправки задания загрузки данных в Selena и для скачивания наборов данных. Проверьте, установлен ли он, выполнив curl или curl.exe в командной строке вашей ОС. Если curl не установлен, получите curl здесь.

`/etc/hosts`

Метод загрузки, используемый в этом руководстве, - Stream Load. Stream Load подключается к сервису FE для запуска задания загрузки. FE затем назначает задание узлу backend, CN в этом руководстве. Чтобы задание загрузки могло подключиться к CN, имя CN должно быть доступно вашей операционной системе. Добавьте эту строку в /etc/hosts:

127.0.0.1 starrocks-cn

Терминология

FE

Frontend узлы отвечают за управление метаданными, управление подключениями клиентов, планирование запросов и планирование выполнения запросов. Каждый FE хранит и поддерживает полную копию метаданных в своей памяти, что гарантирует беспристрастные сервисы среди FE.

CN

Compute Nodes отвечают за выполнение планов запросов в shared-data развертываниях.

BE

Backend узлы отвечают как за хранение данных, так и за выполнение планов запросов в shared-nothing развертываниях.

примечание

Это руководство не использует BE, эта информация включена здесь, чтобы вы понимали разницу между BE и CN.

Скачивание файлов лаборатории

Есть три файла для скачивания:

Файл Docker Compose, который развертывает окружение Selena и MinIO
Данные о авариях в Нью-Йорке
Данные о погоде

Это руководство использует MinIO, который является S3-совместимым объектным хранилищем, предоставляемым под лицензией GNU Affero General Public License.

Создайте директорию для хранения файлов лаборатории:

mkdir quickstart
cd quickstart

Скачайте файл Docker Compose

curl -O https://raw.githubusercontent.com/StarRocks/demo/master/documentation-samples/quickstart/docker-compose.yml

Скачайте данные

Скачайте эти два набора данных:

Данные о авариях в Нью-Йорке

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

Развертывание Selena и MinIO

docker compose up --detach --wait --wait-timeout 120

Запуск сервисов FE, CN и MinIO должен занять около 30 секунд, чтобы стать здоровыми. Контейнер quickstart-minio_mc-1 покажет статус Waiting и также код выхода. Код выхода 0 указывает на успех.

[+] Running 4/5
 ✔ Network quickstart_default       Created    0.0s
 ✔ Container minio                  Healthy    6.8s
 ✔ Container starrocks-fe           Healthy    29.3s
 ⠼ Container quickstart-minio_mc-1  Waiting    29.3s
 ✔ Container starrocks-cn           Healthy    29.2s
container quickstart-minio_mc-1 exited (0)

Проверка учетных данных MinIO

Чтобы использовать MinIO для объектного хранения с Selena, Selena нужен ключ доступа MinIO. Ключ доступа был сгенерирован во время запуска сервисов Docker. Чтобы помочь вам лучше понять способ подключения Selena к MinIO, вы должны проверить, что ключ существует.

Откройте веб-интерфейс MinIO

Перейдите по адресу http://localhost:9001/access-keys Имя пользователя и пароль указаны в файле Docker compose и являются miniouser и miniopassword. Вы должны увидеть, что есть один ключ доступа. Ключ - AAAAAAAAAAAAAAAAAAAA, вы не можете увидеть секрет в консоли MinIO, но он находится в файле Docker compose и является BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB:

Просмотр ключа доступа MinIO

подсказка

Если в веб-интерфейсе MinIO не отображаются ключи доступа, проверьте логи сервиса minio_mc:

docker compose logs minio_mc

Попробуйте перезапустить pod minio_mc:

docker compose run minio_mc

SQL клиенты

Эти три клиента протестированы с данным руководством, вам нужен только один:

mysql CLI: Вы можете запустить его из среды Docker или с вашей машины.
DBeaver доступен в виде community версии и Pro версии.
MySQL Workbench

Настройка клиента

mysql CLI
DBeaver
MySQL Workbench

Самый простой способ использовать mysql CLI — запустить его из контейнера Selena starrocks-fe:

docker compose exec starrocks-fe \
mysql -P 9030 -h 127.0.0.1 -u root --prompt="StarRocks > "

подсказка

Все команды docker compose должны выполняться из директории, содержащей файл docker-compose.yml.

Если вы хотите установить mysql CLI, разверните установка mysql клиента ниже:

установка mysql клиента

macOS: Если вы используете Homebrew и не нуждаетесь в MySQL Server, выполните brew install mysql для установки CLI.
Linux: Проверьте вашу систему репозиториев для клиента mysql. Например, yum install mariadb.
Microsoft Windows: Установите MySQL Community Server и запустите предоставленный клиент, или запустите mysql из WSL.

Конфигурация Selena для shared-data

На данный момент у вас запущена Selena, и у вас запущен MinIO. Ключ доступа MinIO используется для подключения Selena и MinIO. Когда Selena запустилась, она установила соединение с MinIO и создала том хранения по умолчанию в MinIO.

Это конфигурация, используемая для установки тома хранения по умолчанию для использования MinIO (это также в файле Docker compose). Конфигурация будет подробно описана в конце этого руководства, пока просто отметьте, что aws_s3_access_key установлен в строку, которую вы видели в консоли MinIO, и что run_mode установлен в shared_data:

# включить shared data, установить тип хранения, установить endpoint
run_mode = shared_data
cloud_native_storage_type = S3
aws_s3_endpoint = minio:9000

# установить путь в MinIO
aws_s3_path = starrocks

# учетные данные для чтения/записи объектов MinIO
aws_s3_access_key = AAAAAAAAAAAAAAAAAAAA
aws_s3_secret_key = BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
aws_s3_use_instance_profile = false
aws_s3_use_aws_sdk_default_behavior = false

# Установите это в false, если вы не хотите, чтобы хранилище по умолчанию
# создавалось в объектном хранилище, используя
# детали, предоставленные выше
enable_load_volume_from_conf = true

Подключение к Selena с помощью SQL клиента

подсказка

Выполните эту команду из директории, содержащей файл docker-compose.yml.

Если вы используете клиент, отличный от mysql CLI, откройте его сейчас.

docker compose exec starrocks-fe \
mysql -P9030 -h127.0.0.1 -uroot --prompt="StarRocks > "

Проверка тома хранения

SHOW STORAGE VOLUMES;

+------------------------+
| Storage Volume         |
+------------------------+
| builtin_storage_volume |
+------------------------+
1 row in set (0.00 sec)

DESC STORAGE VOLUME builtin_storage_volume\G

подсказка

Некоторые SQL в этом документе и многих других документах в документации Selena заканчиваются на \G вместо точки с запятой. \G заставляет mysql CLI отображать результаты запроса вертикально.

Многие SQL клиенты не интерпретируют вертикальный формат вывода, поэтому вы должны заменить \G на ;.

*************************** 1. row ***************************
     Name: builtin_storage_volume
     Type: S3
IsDefault: true
 Location: s3://starrocks
   Params: {"aws.s3.access_key":"******","aws.s3.secret_key":"******","aws.s3.endpoint":"minio:9000","aws.s3.region":"","aws.s3.use_instance_profile":"false","aws.s3.use_aws_sdk_default_behavior":"false"}
  Enabled: true
  Comment:
1 row in set (0.03 sec)

Проверьте, что параметры соответствуют конфигурации.

примечание

Папка builtin_storage_volume не будет видна в списке объектов MinIO до тех пор, пока данные не будут записаны в bucket.

Создание таблиц

Создание базы данных

Введите эти две строки в командной строке Selena > и нажмите Enter после каждой:

CREATE DATABASE IF NOT EXISTS quickstart;

USE quickstart;

Создание двух таблиц

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

Аналогично данным о ДТП, набор данных о погоде содержит гораздо больше столбцов (всего 125 столбцов), и в базу данных включены только те, которые предположительно помогут ответить на поставленные вопросы.

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

Загрузка двух наборов данных

Есть много способов загрузить данные в Selena. Для этого учебника самый простой способ - использовать curl и Stream Load Selena.

подсказка

Выполните эти команды curl из директории, где вы скачали набор данных.

Вам будет предложено ввести пароль. Вы, вероятно, не назначили пароль пользователю MySQL root, поэтому просто нажмите enter.

Команды curl выглядят сложными, но они подробно объясняются в конце учебника. Пока мы рекомендуем выполнить команды и запустить некоторые SQL для анализа данных, а затем прочитать о деталях загрузки данных в конце.

Данные о столкновениях в Нью-Йорке - Аварии

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

Вот вывод приведенной выше команды. Первый выделенный раздел показывает, что вы должны ожидать увидеть (OK и все строки, кроме одной, вставлены). Одна строка была отфильтрована, потому что она не содержит правильное количество столбцов.

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

Если была ошибка, вывод предоставляет URL для просмотра сообщений об ошибках. Сообщение об ошибке также содержит узел backend, которому было назначено задание Stream Load (starrocks-cn). Поскольку вы добавили запись для starrocks-cn в файл /etc/hosts, вы должны иметь возможность перейти к нему и прочитать сообщение об ошибке.

Разверните сводку для содержимого, увиденного при разработке этого учебника:

Чтение сообщений об ошибках в браузере

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 http://localhost:9001/browser/starrocks/ и убедитесь, что у вас есть записи data, metadata и schema в каждой из директорий под starrocks/shared/

подсказка

Имена папок под starrocks/shared/ генерируются при загрузке данных. Вы должны увидеть одну директорию под shared, а затем еще две под ней. Внутри каждой из этих директорий вы найдете записи data, metadata и schema.

Браузер объектов MinIO

Ответы на некоторые вопросы

Эти запросы можно выполнить в вашем SQL-клиенте. Все запросы используют базу данных quickstart.

USE quickstart;

Сколько аварий происходит в час в Нью-Йорке?

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

Вот часть результата. Обратите внимание, что я внимательно изучаю 6 и 7 января, поскольку это понедельник и вторник непраздничной недели. Анализ Нового года, вероятно, не показателен для обычного утра в час пик.

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

Похоже, что в понедельник или вторник утром в час пик происходит около 40 аварий, и примерно столько же в 17:00.

Какая средняя температура в Нью-Йорке?

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

Результат:

Обратите внимание, что это данные за 2014 год, в последнее время в Нью-Йорке не было таких холодов.

+-------------------------------+---------------------+
| 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 |

Безопасно ли ездить в Нью-Йорке при плохой видимости?

Давайте посмотрим на количество аварий при плохой видимости (от 0 до 1,0 мили). Чтобы ответить на этот вопрос, используем JOIN двух таблиц по столбцу DATETIME.

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;

Наибольшее количество аварий за один час при плохой видимости составляет 129. Нужно учесть несколько факторов:

3 февраля 2014 года был понедельник
8 утра — час пик
Шел дождь (0,12 дюйма осадков за этот час)
Температура 32 градуса по Фаренгейту (точка замерзания воды)
Плохая видимость 0,25 мили, нормальная для Нью-Йорка — 10 миль

+---------+--------+------------+---------------+-------------------+
| 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 |

А как насчет вождения в гололедицу?

Водяной пар может сублимироваться в лед при 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;

Результаты для отрицательных температур меня немного удивили, я не ожидал такого интенсивного движения в воскресенье утром в городе в холодный январский день. Быстрый взгляд на weather.com показал, что в тот день была большая буря с множеством аварий, именно то, что видно в данных.

+---------+--------+------------+---------------+-------------------+
| 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 |

Ездите осторожно!

Настройка Selena для shared-data

Теперь, когда вы испытали использование Selena с shared-data, важно понимать конфигурацию.

Конфигурация CN

Конфигурация CN, используемая здесь, является конфигурацией по умолчанию, поскольку CN предназначен для использования shared-data. Конфигурация по умолчанию показана ниже. Вам не нужно вносить никаких изменений.

sys_log_level = INFO

# порты для admin, web, heartbeat сервиса
be_port = 9060
be_http_port = 8040
heartbeat_service_port = 9050
brpc_port = 8060
starlet_port = 9070

Конфигурация FE

Конфигурация FE немного отличается от конфигурации по умолчанию, поскольку FE должен быть настроен на ожидание того, что данные хранятся в объектном хранилище, а не на локальных дисках на узлах BE.

Файл docker-compose.yml генерирует конфигурацию FE в command.

# включить shared data, установить тип хранения, установить endpoint
run_mode = shared_data
cloud_native_storage_type = S3
aws_s3_endpoint = minio:9000

# установить путь в MinIO
aws_s3_path = starrocks

# учетные данные для чтения/записи объектов MinIO
aws_s3_access_key = AAAAAAAAAAAAAAAAAAAA
aws_s3_secret_key = BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
aws_s3_use_instance_profile = false
aws_s3_use_aws_sdk_default_behavior = false

# Установите это в false, если вы не хотите, чтобы хранилище по умолчанию
# создавалось в объектном хранилище, используя
# детали, предоставленные выше
enable_load_volume_from_conf = true

примечание

Этот файл конфигурации не содержит записи по умолчанию для FE, показана только конфигурация shared-data.

Настройки конфигурации FE, отличные от настроек по умолчанию:

примечание

Многие параметры конфигурации имеют префикс s3_. Этот префикс используется для всех типов хранения, совместимых с Amazon S3 (например: S3, GCS и MinIO). При использовании Azure Blob Storage префикс - azure_.

`run_mode=shared_data`

Это включает использование shared-data.

`cloud_native_storage_type=S3`

Это указывает, используется ли S3-совместимое хранилище или Azure Blob Storage. Для MinIO это всегда S3.

`aws_s3_endpoint=minio:9000`

Endpoint MinIO, включая номер порта.

`aws_s3_path=starrocks`

Имя bucket.

`aws_s3_access_key=AA`

Ключ доступа MinIO.

`aws_s3_secret_key=BB`

Секрет ключа доступа MinIO.

`aws_s3_use_instance_profile=false`

При использовании MinIO используется ключ доступа, и поэтому профили экземпляров не используются с MinIO.

`aws_s3_use_aws_sdk_default_behavior=false`

При использовании MinIO этот параметр всегда устанавливается в false.

`enable_load_volume_from_conf=true`

Когда это true, создается том хранения Selena с именем builtin_storage_volume, использующий объектное хранилище MinIO, и он устанавливается как том хранения по умолчанию для таблиц, которые вы создаете.

Настройка режима FQDN

Команда для запуска FE также изменена. Команда сервиса FE в файле Docker Compose имеет добавленную опцию --host_type FQDN. Установив host_type в FQDN, задание Stream Load перенаправляется на полное доменное имя pod CN, а не на IP-адрес. Это делается потому, что IP-адрес находится в диапазоне, назначенном окружению Docker, и обычно недоступен с хост-машины.

Эти три изменения позволяют CN быть перенаправленным из хост-сети:

установка --host_type в FQDN
экспонирование порта CN 8040 в хост-сеть
добавление записи в файл hosts для starrocks-cn, указывающей на 127.0.0.1

Резюме

В этом учебнике вы:

Развернули Selena и MinIO в Docker
Создали ключ доступа MinIO
Настроили том хранения Selena, который использует MinIO
Загрузили данные об авариях, предоставленные Нью-Йорком, и данные о погоде, предоставленные NOAA
Проанализировали данные, используя SQL JOIN, чтобы выяснить, что вождение при низкой видимости или на обледенелых дорогах - плохая идея

Есть еще что изучить; мы намеренно поверхностно рассмотрели преобразование данных, выполненное во время Stream Load. Подробности об этом находятся в примечаниях к командам curl ниже.

Примечания к командам curl

Selena Stream Load и curl принимают множество аргументов. Здесь описаны только те, которые используются в этом руководстве, остальные будут приведены в разделе дополнительной информации.

`--location-trusted`

Это настраивает curl для передачи учетных данных любым перенаправленным URL.

`-u root`

Имя пользователя, используемое для входа в Selena

`-T filename`

T означает transfer (передача), имя файла для передачи.

`label:name-num`

Метка для связывания с этим заданием Stream Load. Метка должна быть уникальной, поэтому если вы запускаете задание несколько раз, вы можете добавить номер и продолжать его увеличивать.

`column_separator:,`

Если вы загружаете файл, который использует одну ,, то установите её как показано выше, если вы используете другой разделитель, то установите этот разделитель здесь. Распространенные варианты: \t, , и |.

`skip_header:1`

Некоторые CSV файлы имеют одну строку заголовка со всеми именами столбцов, а некоторые добавляют вторую строку с типами данных. Установите skip_header в 1 или 2, если у вас есть одна или две строки заголовка, и установите в 0, если их нет.

`enclose:\"`

Обычно строки, содержащие встроенные запятые, заключают в двойные кавычки. Примеры наборов данных, используемые в этом руководстве, имеют географические местоположения, которые содержат запятые, поэтому параметр enclose установлен в \". Не забудьте экранировать " с помощью \.

`max_filter_ratio:1`

Это позволяет некоторые ошибки в данных. В идеале это должно быть установлено в 0, и задание должно завершаться неудачей при любых ошибках. Оно установлено в 1, чтобы разрешить всем строкам завершаться неудачей во время отладки.

`columns:`

Сопоставление столбцов CSV файла со столбцами таблицы Selena. Вы заметите, что в CSV файлах гораздо больше столбцов, чем столбцов в таблице. Любые столбцы, которые не включены в таблицу, пропускаются.

Вы также заметите, что в строке columns: для набора данных о авариях включено некоторое преобразование данных. Очень часто в CSV файлах встречаются даты и времена, которые не соответствуют стандартам. Это логика для преобразования CSV данных времени и даты аварии в тип DATETIME:

Строка columns

Это начало одной записи данных. Дата в формате MM/DD/YYYY, а время HH:MI. Поскольку DATETIME обычно имеет формат YYYY-MM-DD HH:MI:SS, нам нужно преобразовать эти данные.

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

Это начало параметра 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')

Это указывает Selena:

Присвоить содержимое первого столбца CSV файла в tmp_CRASH_DATE
Присвоить содержимое второго столбца CSV файла в tmp_CRASH_TIME
concat_ws() объединяет tmp_CRASH_DATE и tmp_CRASH_TIME вместе с пробелом между ними
str_to_date() создает DATETIME из объединенной строки
сохранить полученный DATETIME в столбце CRASH_DATE

Дополнительная информация

Дизайн таблиц Selena

Stream Load

Набор данных Motor Vehicle Collisions - Crashes предоставлен Нью-Йорком в соответствии с этими условиями использования и политикой конфиденциальности.

Local Climatological Data(LCD) предоставлены NOAA с этим отказом от ответственности и этой политикой конфиденциальности.

Предварительные требования​

Docker​

SQL клиент​

curl​

/etc/hosts​

Терминология​

FE​

CN​

BE​

Скачивание файлов лаборатории​

Создайте директорию для хранения файлов лаборатории:​

Скачайте файл Docker Compose​

Скачайте данные​

Данные о авариях в Нью-Йорке​

Данные о погоде​

Развертывание Selena и MinIO​

Проверка учетных данных MinIO​

Откройте веб-интерфейс MinIO​

SQL клиенты​

Настройка клиента​

Конфигурация Selena для shared-data​

Подключение к Selena с помощью SQL клиента​

Проверка тома хранения​

Создание таблиц​

Создание базы данных​

Создание двух таблиц​

Crashdata​

Weatherdata​

Загрузка двух наборов данных​

Данные о столкновениях в Нью-Йорке - Аварии​

Данные о погоде​

Проверка того, что данные хранятся в MinIO​

Ответы на некоторые вопросы​

Сколько аварий происходит в час в Нью-Йорке?​

Какая средняя температура в Нью-Йорке?​

Безопасно ли ездить в Нью-Йорке при плохой видимости?​

А как насчет вождения в гололедицу?​

Настройка Selena для shared-data​

Конфигурация CN​

Конфигурация FE​

run_mode=shared_data​

cloud_native_storage_type=S3​

aws_s3_endpoint=minio:9000​

aws_s3_path=starrocks​

aws_s3_access_key=AA​

aws_s3_secret_key=BB​

aws_s3_use_instance_profile=false​

aws_s3_use_aws_sdk_default_behavior=false​

enable_load_volume_from_conf=true​

Настройка режима FQDN​

Резюме​

Примечания к командам curl​

--location-trusted​

-u root​

-T filename​

label:name-num​

column_separator:,​

skip_header:1​

enclose:\"​

max_filter_ratio:1​

columns:​

Строка columns​

Дополнительная информация​

Предварительные требования

Docker

SQL клиент

curl

`/etc/hosts`

Терминология

FE

CN

BE

Скачивание файлов лаборатории

Создайте директорию для хранения файлов лаборатории:

Скачайте файл Docker Compose

Скачайте данные

Данные о авариях в Нью-Йорке

Данные о погоде

Развертывание Selena и MinIO

Проверка учетных данных MinIO

Откройте веб-интерфейс MinIO

SQL клиенты

Настройка клиента

Конфигурация Selena для shared-data

Подключение к Selena с помощью SQL клиента

Проверка тома хранения

Создание таблиц

Создание базы данных

Создание двух таблиц

Crashdata

Weatherdata

Загрузка двух наборов данных

Данные о столкновениях в Нью-Йорке - Аварии

Данные о погоде

Проверка того, что данные хранятся в MinIO

Ответы на некоторые вопросы

Сколько аварий происходит в час в Нью-Йорке?

Какая средняя температура в Нью-Йорке?

Безопасно ли ездить в Нью-Йорке при плохой видимости?

А как насчет вождения в гололедицу?

Настройка Selena для shared-data

Конфигурация CN

Конфигурация FE

`run_mode=shared_data`

`cloud_native_storage_type=S3`

`aws_s3_endpoint=minio:9000`

`aws_s3_path=starrocks`

`aws_s3_access_key=AA`

`aws_s3_secret_key=BB`

`aws_s3_use_instance_profile=false`

`aws_s3_use_aws_sdk_default_behavior=false`

`enable_load_volume_from_conf=true`

Настройка режима FQDN

Резюме

Примечания к командам curl

`--location-trusted`

`-u root`

`-T filename`

`label:name-num`

`column_separator:,`

`skip_header:1`

`enclose:\"`

`max_filter_ratio:1`

`columns:`

Строка columns

Дополнительная информация