Geo Data using the Cell Tower Dataset
ゴール
このガイドでは、次のことを学びます:
- OpenCelliDデータをClickHouseにロードする
- Apache SupersetをClickHouseに接続する
- データセットに基づくダッシュボードを構築する
ここにこのガイドで作成されたダッシュボードのプレビューがあります:
データセットを取得する
このデータセットはOpenCelliDからのものであり、世界最大のオープンなセルスタワーデータベースです。
2021年現在、世界中のセルタワー（GSM、LTE、UMTSなど）に関する4000万件以上のレコードが、地理座標やメタデータ（国コード、ネットワークなど）とともに含まれています。
OpenCelliDプロジェクトは、クリエイティブ・コモンズ表示-継承4.0国際ライセンスの下でライセンスされており、同じライセンスの条件のもとでこのデータセットのスナップショットを再配布します。最新のデータセットのバージョンは、サインイン後にダウンロードできます。
- ClickHouse Cloud
- Self-managed
サンプルデータをロードする
ClickHouse Cloudは、このデータセットをS3からアップロードするための簡単なボタンを提供します。ClickHouse Cloud組織にログインするか、ClickHouse.cloudで無料トライアルを作成してください。
Select your service, followed by Data souces -> Predefined sample data.
サンプルデータタブからCell Towersデータセットを選択し、Load dataをクリックします。
cell_towersテーブルのスキーマを調べる
SQLクライアント接続が必要な場合、あなたのClickHouse Cloudサービスには関連するウェブベースのSQLコンソールがあります。詳細については、下のSQLコンソールに接続を展開してください。
SQLコンソールに接続
あなたのClickHouse Cloudサービスリストから、サービスをクリックします。
これにより、SQLコンソールにリダイレクトされます。
これはDESCRIBEの出力です。このガイドの後半では、フィールドタイプの選択が説明されます。
- テーブルを作成します:
- 公開S3バケットからデータセットをインポートします（686 MB）:
一部の例を実行する
- タイプ別のセルタワーの数:
- モバイル国コード（MCC）別のセルタワー:
上記のクエリとMCCリストに基づくと、セルタワーが最も多い国は、アメリカ、ドイツ、ロシアです。
これらの値をデコードするために、ClickHouseでDictionaryを作成したいかもしれません。
事例: ジオデータを組み込む
pointInPolygon関数を使用します。
- ポリゴンを保存するテーブルを作成します:
- ClickHouse Cloud
- Self-managed
- これは「新モスクワ」を除くモスクワの粗い形状です:
- モスクワにあるセルタワーの数をチェックします:
スキーマのレビュー
Supersetで視覚化を構築する前に、使用するカラムを確認してください。このデータセットは、主に世界中の携帯電話のセルタワーの位置（経度および緯度）と無線タイプを提供します。カラムの説明は、コミュニティフォーラムにあります。構築する視覚化で使用されるカラムは以下の通りです。
OpenCelliDフォーラムからのカラムの説明は次のとおりです：
| カラム | 説明 |
|---|---|
| radio | 技術世代: CDMA, GSM, UMTS, 5G NR |
| mcc | モバイル国コード: 204 はオランダ |
| lon | 経度: 緯度とともに、概算タワー位置 |
| lat | 緯度: 経度とともに、概算タワー位置 |
あなたのMCCを見つけるにはモバイルネットワークコードを確認し、モバイル国コードカラムの三桁を使用します。
このテーブルのスキーマは、ディスク上のコンパクトなストレージとクエリ速度のために設計されました。
radioデータは、文字列の代わりにEnum8（UInt8）として保存されています。mccまたはモバイル国コードは、範囲が1～999であるため、UInt16として保存されています。lonとlatはFloat64です。
他のフィールドはこのガイド内のクエリや視覚化で使用されていませんが、興味がある場合は、上記のフォーラムで説明されています。
Apache Supersetで視覚化を構築する
SupersetはDockerから簡単に実行できます。すでにSupersetを実行している場合は、必要なことは pip install clickhouse-connectを実行してClickHouseを接続することだけです。Supersetをインストールする必要がある場合は、直接下のLaunch Apache Superset in Dockerを開いてください。
DockerでApache Supersetを起動する
SupersetはDocker Composeを使用したSupersetのローカルインストールの手順を提供しています。 GitHubからApache Supersetリポジトリをチェックアウトした後、最新の開発コードまたは特定のタグを実行できます。 pre-releaseとマークされていない最新リリースであるバージョン2.0.0を推奨します。
docker composeを実行する前に、いくつかの作業を行う必要があります：
- 公式のClickHouse Connectドライバーを追加する
- Mapbox APIキーを取得し、環境変数として追加する (オプション)
- 実行するSupersetのバージョンを指定する
以下のコマンドは、GitHubリポジトリのトップレベルである superset から実行する必要があります。
公式のClickHouse Connectドライバー
SupersetのデプロイメントでClickHouse Connectドライバーを利用できるようにするために、ローカルの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データベースとして追加する
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.
ClickHouseにHTTP(S)で接続するには、次の情報が必要です：
-
HOSTとPORT: 通常、ポートはTLSを使用する場合は8443、TLSを使用しない場合は8123です。
-
DATABASE NAME: デフォルトでは、
defaultという名前のデータベースがあります。接続したいデータベースの名前を使用してください。 -
USERNAMEとPASSWORD: デフォルトでは、ユーザー名は
defaultです。ご利用のケースに適したユーザー名を使用してください。
ClickHouse Cloudサービスの詳細はClickHouse Cloudコンソールで確認できます。接続するサービスを選択し、Connectをクリックします：
HTTPSを選択すると、詳細が例のcurlコマンドで提供されます。
セルフマネージドのClickHouseを使用している場合、接続の詳細はClickHouseの管理者によって設定されます。
Supersetでは、データベースの種類を選択し、接続の詳細を提供することによってデータベースを追加できます。Supersetを開き、+を探してクリックすると、Dataメニューが表示され、そこからConnect databaseオプションがあります。
リストからClickHouse Connectを選択します:
ClickHouse Connectがオプションの1つでない場合は、インストールする必要があります。そのコマンドはpip install clickhouse-connectです。詳細はこちらで確認できます。
接続の詳細を追加します:
ClickHouse CloudまたはSSLの使用を強制する他のClickHouseシステムに接続する際は、SSLをオンにすることを確認してください。
テーブルcell_towersをSupersetのデータセットとして追加する
Supersetでは、データセットがデータベース内のテーブルにマップされます。データセットを追加をクリックし、ClickHouseサービス、テーブルを含むデータベース（default）、およびcell_towersテーブルを選択します:
いくつかのチャートを作成する
Supersetでチャートを追加することを選択すると、データセット（cell_towers）とチャートタイプを指定する必要があります。OpenCelliDデータセットはセルタワーの経度および緯度座標を提供しているので、Mapチャートを作成します。deck.gL Scatterplotタイプは、このデータセットに適しており、マップ上の密なデータポイントとよく機能します。
マップで使用されるクエリを指定する
deck.gl Scatterplotには経度と緯度が必要であり、一つ以上のフィルターをクエリに適用することもできます。この例では、UMTS無線を持つセルタワー用の1つと、オランダに割り当てられたモバイル国コード用の1つ、合計2つのフィルターが適用されます。
フィールドlonとlatには経度と緯度が含まれています:
フィルターにmcc = 204（または他の任意のmcc値に置き換え）を追加します:
フィルターにradio = 'UMTS'（または他の任意のradio値に置き換え、DESCRIBE TABLE cell_towersの出力で選択できます）を追加します:
これは、radio = 'UMTS'およびmcc = 204でフィルターするためのチャートの完全な構成です:
UPDATE CHARTをクリックしてビジュアライゼーションをレンダリングします。
チャートをダッシュボードに追加する
このスクリーンショットは、LTE、UMTS、およびGSM無線を持つセルタワーの位置を示しています。チャートはすべて同じように作成され、ダッシュボードに追加されます。
