Геоданные с использованием набора данных вышек сотовой связи

Цель

В этом руководстве вы узнаете как:

• Загрузить данные OpenCelliD в ClickHouse
• Подключить Apache Superset к ClickHouse
• Создать панель инструментов на основе данных, доступных в наборе данных

Вот предварительный просмотр панели инструментов, созданной в этом руководстве:

Получение набора данных

Этот набор данных от OpenCelliD - крупнейшая в мире открытая база данных сотовых вышек.

На 2021 год он содержит более 40 миллионов записей о сотовых вышках (GSM, LTE, UMTS и т.д.) по всему миру с их географическими координатами и метаданными (код страны, сеть и т.д.).

Проект OpenCelliD лицензирован на условиях лицензии Creative Commons Attribution-ShareAlike 4.0 International, и мы перераспределяем снимок этого набора данных на тех же условиях. Актуальная версия набора данных доступна для загрузки после входа в систему.

ClickHouse Cloud

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

ClickHouse Cloud предоставляет удобный способ загрузки этого набора данных с S3. Войдите в свою организацию ClickHouse Cloud или создайте бесплатную пробную версию на ClickHouse.cloud.

Выберите вашу услугу, затем Источники данных -> Предопределенные образцы данных.

Выберите набор данных Cell Towers на вкладке Sample data и нажмите Load data:

Изучите схему таблицы cell_towers

SQL консоль

Если вам необходимо подключение клиента SQL, ваш сервис ClickHouse Cloud имеет связанный веб-основанный SQL консоль; разверните Подключение к SQL консоли ниже для получения подробностей.

Подключение к SQL консоли

Из списка ваших сервисов ClickHouse Cloud щелкните по нужному сервису.

Это перенаправит вас в SQL консоль.

Это вывод команды DESCRIBE. Далее в этом руководстве будут описаны выборы типов полей.

Self-managed

1. Создайте таблицу:

2. Импортируйте набор данных из публичного S3-ведра (686 МБ):

Выполните несколько примеров запросов

1. Количество вышек сотовой связи по типу:

2. Вышки сотовой связи по мобильному коду страны (MCC):

Судя по вышеуказанному запросу и списку MCC, страны с наибольшим количеством вышек сотовой связи: США, Германия и Россия.

Вы можете создать словарь в ClickHouse, чтобы расшифровать эти значения.

Случай использования: Включение геоданных

Используя функцию pointInPolygon.

1. Создайте таблицу, в которой мы будем хранить полигоны:

ClickHouse Cloud

Self-managed

2. Это примерная форма Москвы (без "новой Москвы"):

3. Проверьте, сколько вышек сотовой связи находится в Москве:

Обзор схемы

Перед созданием визуализаций в Superset взгляните на столбцы, которые вы будете использовать. Этот набор данных в основном предоставляет местоположение (долготу и широту) и типы радиосигналов на мобильных вышках по всему миру. Описания столбцов можно найти на форуме сообщества. Ниже приведены описания столбцов, используемых в визуализациях, которые будут построены.

Вот описание столбцов, взятых с форума OpenCelliD:

Столбец	Описание
radio	Поколение технологии: CDMA, GSM, UMTS, 5G NR
mcc	Мобильный код страны: 204 - Нидерланды
lon	Долгота: С широтой, приблизительное местоположение вышки
lat	Широта: С долготой, приблизительное местоположение вышки

mcc

Чтобы найти свой MCC, проверьте Коды мобильных сетей и используйте три цифры в колонке Мобильный код страны.

Схема этой таблицы была разработана для компактного хранения на диске и скорости запросов.

• Данные radio хранятся как Enum8 (UInt8), а не как строка.
• mcc или мобильный код страны хранится как UInt16, так как мы знаем, что диапазон составляет от 1 до 999.
• lon и lat имеют тип Float64.

Ни одно из других полей не используется в запросах или визуализациях в этом руководстве, но их описание доступно на форуме, указанном выше, если вам интересно.

Создание визуализаций с Apache Superset

Superset легко запускать из Docker. Если у вас уже запущен Superset, все что вам нужно сделать, это добавить ClickHouse Connect с помощью pip install clickhouse-connect. Если вам нужно установить Superset, откройте Запустить Apache Superset в Docker прямо под этим текстом.

Запуск Apache Superset в Docker

Superset предоставляет инструкции по установке Superset локально с использованием Docker Compose. После того как вы склонируете репозиторий Apache Superset с GitHub, вы можете запустить последний код разработки или конкретный тег. Мы рекомендуем релиз 2.0.0, так как это последний релиз, не помеченный как pre-release.

Перед запуском docker compose необходимо выполнить несколько задач:

1. Добавить официальный драйвер ClickHouse Connect
2. Получить ключ API Mapbox и добавить его в качестве переменной окружения (по желанию)
3. Указать версию Superset для запуска

подсказка

Команды ниже должны выполняться из корневой директории репозитория GitHub, superset.

Официальный драйвер ClickHouse Connect

Чтобы сделать драйвер ClickHouse Connect доступным в развертывании Superset, добавьте его в локальный файл зависимостей:

Mapbox

Это необязательно, вы можете отображать данные о местоположении в Superset без ключа API Mapbox, но вы получите сообщение с предложением добавить ключ, и фоновое изображение карты будет отсутствовать (вы увидите только точки данных, а не фоновую карту). Mapbox предоставляет бесплатный тариф, если вы хотите его использовать.

Некоторые из образцов визуализаций, которые предлагают создать руководства, используют данные о местоположении, например долготу и широту. Superset включает поддержку карт Mapbox. Чтобы использовать визуализации Mapbox, вам нужен ключ API Mapbox. Зарегистрируйтесь для получения бесплатного тарифа Mapbox и сгенерируйте ключ API.

Сделайте ключ API доступным для Superset:

Развертывание Superset версии 2.0.0

Чтобы развернуть релиз 2.0.0, выполните:

Чтобы создать панель инструментов Superset с использованием набора данных OpenCelliD, вам следует:

• Добавить свой сервис ClickHouse как базу данных Superset
• Добавить таблицу cell_towers как набор данных Superset
• Создать несколько графиков
• Добавить графики на панель инструментов

Добавьте свой сервис ClickHouse как базу данных Superset

Чтобы подключиться к ClickHouse с помощью HTTP(S), вам нужна следующая информация:

• ХОСТ и ПОРТ: как правило, порт составляет 8443 при использовании TLS или 8123 при отсутствии TLS.

• НАЗВАНИЕ БАЗЫ ДАННЫХ: по умолчанию существует база данных с именем default, используйте имя базы данных, к которой вы хотите подключиться.

• ИМЯ ПОЛЬЗОВАТЕЛЯ и ПАРОЛЬ: по умолчанию имя пользователя равно default. Используйте имя пользователя, соответствующее вашему случаю.

Сведения о вашем ClickHouse Cloud-сервисе доступны в консоли ClickHouse Cloud. Выберите сервис, к которому вы будете подключаться, и нажмите Подключиться:

Выберите HTTPS, и детали доступны в примере команды curl.

Если вы используете самоуправляемый ClickHouse, детали подключения устанавливаются вашим администратором ClickHouse.

В Superset базу данных можно добавить, выбрав тип базы данных, а затем указав данные для подключения. Откройте Superset и найдите +, в нем есть меню с опциями Data и Connect database.

Выберите ClickHouse Connect из списка:

примечание

Если ClickHouse Connect не является одним из ваших вариантов, вам нужно будет установить его. Команда: pip install clickhouse-connect, дополнительная информация доступна здесь.

Добавьте ваши данные для подключения:

подсказка

Убедитесь, что вы включили SSL при подключении к ClickHouse Cloud или другим системам ClickHouse, которые требуют использования SSL.

Добавьте таблицу cell_towers как набор данных Superset

В Superset набор данных отображает таблицу внутри базы данных. Нажмите на добавление набора данных и выберите свой сервис ClickHouse, базу данных, содержащую вашу таблицу (default), и выберите таблицу cell_towers:

Создайте несколько графиков

Когда вы выбираете добавление графика в Superset, вам необходимо указать набор данных (cell_towers) и тип графика. Поскольку набор данных OpenCelliD предоставляет координаты долготы и широты для вышек связи, мы создадим график Картa. Тип deck.gL Scatterplot подходит для этого набора данных, так как он хорошо работает с плотными точками данных на карте.

Укажите запрос, используемый для карты

График deck.gl Scatterplot требует долготы и широты, и к запросу также можно применить один или несколько фильтров. В этом примере применяются два фильтра: один для вышек с радиосигналами UMTS и один для мобильного кода страны, присвоенного Нидерландам.

Поля lon и lat содержат долготу и широту:

Добавьте фильтр с mcc = 204 (или замените любое другое значение mcc):

Добавьте фильтр с radio = 'UMTS' (или замените любое другое значение radio, вы можете увидеть выборы в выводе DESCRIBE TABLE cell_towers):

Вот полная конфигурация для графика, фильтрующего по radio = 'UMTS' и mcc = 204:

Нажмите UPDATE CHART, чтобы отобразить визуализацию.

Добавьте графики на панель инструментов

Этот снимок экрана показывает местоположение вышек сотовой связи с радиосигналами LTE, UMTS и GSM. Все графики созданы одинаково и добавлены на панель инструментов.

подсказка

Данные также доступны для интерактивных запросов в Playground.

Этот пример заполнит имя пользователя и даже запрос для вас.

Хотя вы не можете создавать таблицы в Playground, вы можете выполнять все запросы и даже использовать Superset (отредактируйте имя хоста и номер порта).