Перейти к основному содержимому
Перейти к основному содержимому

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

Цель​

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

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

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

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

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

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

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

Загрузите тестовые данные​

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

Select your service, followed by Data souces -> Predefined sample data.

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

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

SQL console

If you need a SQL client connection, your ClickHouse Cloud service has an associated web based SQL console; expand Connect to SQL console below for details.

Connect to SQL console

From your ClickHouse Cloud services list, click on a service.

This will redirect you to the SQL console.

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

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

  1. Количество башен по типу:
  1. Башни по мобильному коду страны (MCC):

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

Вы можете создать Словарь в ClickHouse для декодирования этих значений.

Случай использования: Интеграция геоданных​

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

  1. Создайте таблицу, в которой мы будем хранить полигоны:
  1. Это приблизительная форма Москвы (без "новой Москвы"):
  1. Проверьте, сколько башен связи в Москве:

Обзор схемы​

Перед построением визуализаций в 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 с помощью pip install clickhouse-connect. Если вам нужно установить Superset, откройте раздел Запустить Apache Superset в Docker прямо ниже.

Launch Apache Superset in Docker

Superset provides installing Superset locally using Docker Compose instructions. After checking out the Apache Superset repo from GitHub you can run the latest development code, or a specific tag. We recommend release 2.0.0 as it is the latest release not marked as pre-release.

There are a few tasks to be done before running docker compose:

  1. Add the official ClickHouse Connect driver
  2. Obtain a Mapbox API key and add that as an environment variable (optional)
  3. Specify the version of Superset to run
подсказка

The commands below are to be run from the top level of the GitHub repo, superset.

Official ClickHouse Connect driver​

To make the ClickHouse Connect driver available in the Superset deployment add it to the local requirements file:

Mapbox​

This is optional, you can plot location data in Superset without a Mapbox API key, but you will see a message telling you that you should add a key and the background image of the map will be missing (you will only see the data points and not the map background). Mapbox provides a free tier if you would like to use it.

Some of the sample visualizations that the guides have you create use location, for example longitude and latitude, data. Superset includes support for Mapbox maps. To use the Mapbox visualizations you need a Mapbox API key. Sign up for the Mapbox free tier, and generate an API key.

Make the API key available to Superset:

Deploy Superset version 2.0.0​

To deploy release 2.0.0 run:

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

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

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

To connect to ClickHouse with HTTP(S) you need this information:

  • The HOST and PORT: typically, the port is 8443 when using TLS or 8123 when not using TLS.

  • The DATABASE NAME: out of the box, there is a database named default, use the name of the database that you want to connect to.

  • The USERNAME and PASSWORD: out of the box, the username is default. Use the username appropriate for your use case.

The details for your ClickHouse Cloud service are available in the ClickHouse Cloud console. Select the service that you will connect to and click Connect:

Choose HTTPS, and the details are available in an example curl command.

If you are using self-managed ClickHouse, the connection details are set by your ClickHouse administrator.

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

Выберите 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 предоставляет координаты долготы и широты для башен связи, мы создадим график Карта. Тип 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 (соблюдайте правильные имя хоста и номер порта).