メインコンテンツまでスキップ
メインコンテンツまでスキップ

Cell Tower Datasetを使用した地理データ

目標​

このガイドでは以下のことを学びます：

  • ClickHouseにOpenCelliDデータをロードする
  • Apache SupersetをClickHouseに接続する
  • データセット内のデータに基づいてダッシュボードを構築する

こちらがこのガイドで作成されたダッシュボードのプレビューです：

mcc 204のラジオタイプによるセルタワーのダッシュボード

データセットを取得する​

このデータセットはOpenCelliDからのもので、世界最大のオープンセルタワーデータベースです。

2021年現在、世界中のセルタワー（GSM、LTE、UMTSなど）についての4,000万件以上のレコードが含まれており、地理座標やメタデータ（国コード、ネットワークなど）が含まれています。

OpenCelliDプロジェクトはクリエイティブ・コモンズ 表示-継承 4.0 国際ライセンスの下でライセンスされています。このデータセットのスナップショットを同じライセンスの下で再配布します。最新のデータセットバージョンはサインイン後にダウンロード可能です。

サンプルデータをロードする​

ClickHouse Cloudは、このデータセットをS3からアップロードするための簡単なボタンを提供します。ClickHouse Cloud組織にログインするか、ClickHouse.cloudで無料トライアルを作成してください。

サービスを選択し、その後 Data souces -> Predefined sample data を選択します。

Cloud service Actions menu

サンプルデータタブからCell Towersデータセットを選択し、データをロードします：

セルタワーデータセットのロード

cell_towersテーブルのスキーマを検査する​

SQLコンソール

SQLクライアント接続が必要な場合、あなたのClickHouse Cloudサービスには関連するウェブベースのSQLコンソールがあります。詳細は下のSQLコンソールに接続を展開してください。

SQLコンソールに接続

あなたのClickHouse Cloudサービス一覧から、サービスをクリックします。

SQLコンソールに接続

これにより、SQLコンソールにリダイレクトされます。

SQLコンソール

これはDESCRIBEの出力です。後述する部分でフィールドタイプの選択が説明されます。

一部の例クエリを実行する​

  1. タイプごとのセルタワーの数：
  1. モバイル国コード（MCC）ごとのセルタワー：

上記のクエリとMCCリストに基づくと、セルタワーが最も多い国は：アメリカ、ドイツ、ロシアです。

これらの値をデコードするためにClickHouseでDictionaryを作成することをお勧めします。

ユースケース：地理データを組み込む​

pointInPolygon関数を使用します。

  1. ポリゴンを保存するテーブルを作成します：
  1. これはモスクワの粗い形状です（「新モスクワ」を除く）：
  1. モスクワにあるセルタワーの数を確認する：

スキーマのレビュー​

Supersetでビジュアライゼーションを構築する前に、使用するカラムを確認してください。このデータセットは主に、世界中の携帯電話セルタワーの位置（経度と緯度）と無線技術のタイプを提供します。カラムの説明はコミュニティフォーラムにあります。ビジュアライゼーションで使用されるカラムの説明は以下の通りです：

カラム説明
radio技術世代：CDMA、GSM、UMTS、5G NR
mccモバイル国コード：204はオランダを指す
lon経度：緯度とともに、タワーの推定位置
lat緯度：経度とともに、タワーの推定位置
mcc

MCCを見つけるには、モバイルネットワークコードを確認し、モバイル国コードカラムにある3桁の数字を使用します。

このテーブルのスキーマは、ディスク上へのコンパクトな保存とクエリ速度を考慮して設計されています。

  • radioデータは文字列ではなくEnum8 (UInt8)として保存されています。
  • mcc、すなわちモバイル国コードは、1 - 999の範囲が分かっているため、UInt16として保存されています。
  • lonとlatはFloat64です。

このガイド内でのクエリやビジュアライゼーションで使用されていない他のフィールドについても、上記のフォーラムに記載されていますので、興味がある方はご覧ください。

Apache Supersetでビジュアライゼーションを構築する​

SupersetはDockerから簡単に実行できます。すでにSupersetが稼働している場合は、pip install clickhouse-connectを使ってClickHouseを追加するだけです。Supersetをインストールする必要がある場合は、以下のDockerでApache Supersetを起動を直接ご覧ください。

DockerでApache Supersetを起動する

Supersetは、Docker Composeを使用してSupersetをローカルにインストールする手順を提供しています。 GitHubからApache Supersetのリポジトリをチェックアウトした後、最新の開発コードまたは特定のタグを実行することができます。リリース2.0.0は、pre-releaseとしてマークされていない最新のリリースであるため、お勧めします。

docker composeを実行する前にいくつかの作業を行う必要があります：

  1. 公式のClickHouse Connectドライバーを追加する
  2. Mapbox APIキーを取得し、それを環境変数として追加する（オプション）
  3. 実行するSupersetのバージョンを指定する
ヒント

以下のコマンドは、GitHubリポジトリのトップレベルsupersetから実行します。

公式ClickHouse Connectドライバー​

ClickHouse ConnectドライバーをSupersetデプロイメントで使用できるようにするには、ローカルのrequirementsファイルに追加します：

Mapbox​

これはオプションですが、Mapbox APIキーなしでSupersetに位置データをプロットすることができます。ただし、キーを追加する必要があるというメッセージが表示され、マップの背景画像が表示されなくなります（データポイントだけが表示され、マップの背景は表示されません）。 Mapboxは、使用したい場合に無料プランを提供しています。

ガイドで作成するいくつかのサンプル視覚化は、緯度や経度などの位置データを使用します。 SupersetはMapboxマップをサポートしています。Mapboxの視覚化を使用するには、Mapbox APIキーが必要です。Mapboxの無料プランにサインアップし、APIキーを生成してください。

APIキーをSupersetで使用できるようにします：

Supersetバージョン2.0.0をデプロイする​

リリース2.0.0をデプロイするには、次のコマンドを実行します：

OpenCelliDデータセットを使用してSupersetのダッシュボードを構築するには、以下の手順に従う必要があります：

  • ClickHouseサービスをSupersetのデータベースとして追加します
  • テーブルcell_towersをSupersetのデータセットとして追加します
  • 一部のチャートを作成します
  • チャートをダッシュボードに追加します

ClickHouseサービスをSupersetデータベースとして追加する​

ClickHouseにHTTP(S)で接続するには、次の情報が必要です:

  • HOST と PORT: 通常、ポートはTLSを使用する場合は8443、TLSを使用しない場合は8123です。

  • DATABASE NAME: デフォルトでは、defaultという名前のデータベースがあります。接続したいデータベースの名前を使用してください。

  • USERNAME と PASSWORD: デフォルトでは、ユーザー名はdefaultです。あなたのユースケースに適したユーザー名を使用してください。

あなたのClickHouse Cloudサービスの詳細はClickHouse Cloudコンソールで確認できます。接続するサービスを選択し、Connectをクリックしてください:

ClickHouse Cloud service connect button

HTTPSを選択すると、詳細は例のcurlコマンドに表示されます。

ClickHouse Cloud HTTPS connection details

セルフマネージドのClickHouseを使用している場合、接続の詳細はあなたのClickHouse管理者によって設定されています。

Supersetでは、データベースタイプを選択し、接続詳細を提供することでデータベースを追加できます。Supersetを開き、+を探してデータメニューからデータベースに接続オプションを選択します。

データベースの追加

リストからClickHouse Connectを選択します：

データベースタイプとしてClickHouse Connectを選択

注記

ClickHouse Connectがオプションにない場合、インストールする必要があります。コマンドはpip install clickhouse-connectです。詳細はこちらにあります。

接続詳細を追加する​

ヒント

ClickHouse CloudまたはSSLの使用を強制する他のClickHouseシステムに接続する場合は、SSLをオンに設定してください。

ClickHouseをSupersetデータソースとして追加

テーブルcell_towersをSupersetのデータセットとして追加する​

Supersetでは、データセットはデータベース内のテーブルに対応します。データセットを追加し、ClickHouseサービスを選択し、テーブルを含むデータベース（default）を選択し、cell_towersテーブルを選択します：

データセットとしてcell_towersテーブルを追加

一部のチャートを作成する​

Supersetでチャートを追加する際は、データセット（cell_towers）とチャートタイプを指定する必要があります。OpenCelliDデータセットはセルタワーの経度と緯度の座標を提供するため、マップチャートを作成します。このデータセットには、地図上の密なデータポイントに適したdeck.gl Scatterplotタイプが合っています。

Supersetでマップを作成

マップに使用するクエリを指定する​

deck.gl Scatterplotでは、経度と緯度が必要で、また1つ以上のフィルターをクエリに適用することができます。この例では、UMTSラジオのセルタワーとオランダに割り当てられたモバイル国コードのフィルターの2つを適用しています。

lonとlatフィールドには経度と緯度が含まれています：

経度と緯度フィールドを指定

フィルターをmcc = 204（または他のmcc値を代入）として追加します：

MCC 204でフィルター

フィルターをradio = 'UMTS'（または他のradio値を代入。在スキーマのDESCRIBE TABLE cell_towersの出力で選択肢を確認できます）として追加します：

radio = UMTSでフィルター

これは、radio = 'UMTS'とmcc = 204でフィルターされたチャートの完全な構成です：

MCC 204のUMTSラジオ用チャート

UPDATE CHARTをクリックして、ビジュアライゼーションを描画します。

チャートをダッシュボードに追加する​

このスクリーンショットはLTE、UMTS、GSMラジオのセルタワーの位置を示しています。チャートはすべて同様の方法で作成され、ダッシュボードに追加されます。

mcc 204のラジオタイプによるセルタワーのダッシュボード

ヒント

データはPlayground内でもインタラクティブクエリ用に利用可能です。

この例を使用すると、ユーザー名とクエリも自動的に填充されます。

Playground内でテーブルを作成することはできませんが、すべてのクエリを実行したり、Supersetを使用したりできます（ホスト名とポート番号を調整してください）。