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

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

Цель​

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

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

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

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

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

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

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

Загрузите пример данных​

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 для расшифровки этих значений.

Случай использования: Incorporate geo data​

Используя функцию 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 Connect с помощью 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 и найдите +, это меню с опциями 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 предоставляет координаты долготы и широты для вышек сотовой связи, мы создадим график Карта. Тип deck.gL Scatterplot хорошо подходит для этого набора данных, поскольку он работает с плотными точками данных на карте.

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

Scatterplot deck.gl требует долготу и широту, а также к запросу можно применить один или несколько фильтров. В этом примере применяется два фильтра: один для вышек с 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 (подкорректируйте имя хоста и номер порта).