地震を追跡中: Amazon Redshift によりETL処理を通じて視覚化のための非構造化データセットを準備する方法

組織が分析慣行を拡大し、データ科学者やその他の専門家を雇用するにつれ、ビッグデータのパイプラインはますます複雑になります。高度なモデルが毎秒収集されるデータを使用して構築されています。

今日のボトルネックは分析技術のノウハウではない場合がよくあります。むしろ、クラウドには適さないことがあるツールを使用した ETL (抽出、変換およびロード) ジョブの構築と維持の難しさがボトルネックになっています。

この記事では、この課題の解決策を示します。私は数年にわたり、地球のあちこちで記録された地震イベントの中途半端に構造化されたデータセットから始めます。私は地球の表面自体、つまり構造プレートストラクチャを形成する岩の性質に関する広範囲な洞察を取得して、Amazon QuickSightのマッピング機能を使用して視覚化ようとしました。

これを達成するために、いくつかの AWS サービスを使用して、Matillion ETL for Amazon Redshift を使用して次のものを編成します:

AWS Glue (to crawl and catalog Amazon S3 データのクロールとカタログ作成)
Amazon Athena (JSON データいｎ SQL インターフェイスを提示する)
Amazon Redshift (大量のデータに対して空間的な変換を実行するのに必要な計算能力を提供する)

構造プレートストラクチャのコンテキスト

地震は積み上げられた圧力が突然解放されることによって生じます。地震は破壊構造プレートの境界で、より深刻になる傾向があります。これらの境界は重量があり、密度が高い海洋プレートが軽量な大陸プレートとぶつかるとき、または2つの海洋プレートがぶつかるときに形成されます。密度の違いのため、海洋のリソスフェアは大陸プレートの下に押し込まれ、いわゆる沈み込み帯を形成します。(下図を参照してください。) 沈み込み帯では、深さ 700 km の場所で地震が発生する可能性があります。

Wikimedia Commons から KDS4444 [CC BY-SA 4.0 (https://creativecommons.org/licenses/by-sa/4.0)] による写真

私たちの分析のために、最終的には地震の深さを視覚化して沈み込み帯を特定し、最も深刻な地震が生じた地点を見つけたいと考えています。

地震データソース

データソースは、International Federation of Digital Seismograph Networks (FDSN) からのものです。イベントデータは、JSON 形式 (European Mediterranean Seismological Centre または EMSC から取得) になっています。外部プロセスは、以下に示したとおり、Amazon S3 バケットにファイルを毎日蓄積していきます。

個々の各ファイルには、以下の例で示すように、「features」という名前の埋め込みアレイに1 日のすべての地震イベントを含みます。通常は、数百あります。

 {
  "type": "FeatureCollection",
  "metadata": {
    "totalCount": 103
  },
  "features": [
    {
      "geometry": {
        "type": "Point",
        "coordinates": [26.76, 45.77, -140]
      },
      "type": "Feature",
      "id": "20180302_0000103",
      "properties": {
        "lastupdate": "2018-03-02T23:27:00.0Z",
        "lon": 26.76, "lat": 45.77, "depth": 140,
        "mag": 3.7,
        "time": "2018-03-02T23:22:52.1Z",
        "flynn_region": "ROMANIA"
      }
    },
    {
      "geometry": {
        "type": "Point",

アーキテクチャの概要

Athena は S3 データを読み込んで平坦化して、Matillion ETL が JDBC 経由で Amazon Redshift にロードできるようにします。Matillion はこのデータの動きを編成し、またグラフィカルなフレームワークも提供して、Amazon Redshift が実行するより複雑なデータエンリッチメントと集積手順を設計し、構築できるようにします。最終的に、準備されたデータは、視覚化のために、Amazon QuickSightでクエリーされます。

Amazon Athena のセットアップ

Athena を使用して、ユーザーの代わりに、AWS により全体的に管理されたサーバーレスインフラストラクチャ経由で、標準的な SQL を使用する S3 のデータをクエリーすることができます。データをクエリーするにはまず、外部テーブルの作成から開始します。これを行うことにより、クエリーされるデータに適用するスキーマを定義しています。

AWS Glue クローラーを使用して、ソースデータファイルのスキーマと形式を自動的に探索することを支援することができます。

以下は地震データをクエリーするために必要なスキーマを作成するために、コピーして Athena コンソールに貼り付けることができる CREATE TABLE ステートメントです。正しい S3 パスをステートメントの LOCATION フィールドの地震データに置換していることを確認してください。

CREATE EXTERNAL TABLE `sp_events`(
  `type` string COMMENT 'from deserializer',
  `metadata` struct<totalcount:int> COMMENT 'from deserializer',
  `features` array<struct<geometry:struct<type:string,coordinates:array<double>>,type:string,id:string,properties:struct<lastupdate:string,magtype:string,evtype:string,lon:double,auth:string,lat:double,depth:double,unid:string,mag:double,time:string,source_id:string,source_catalog:string,flynn_region:string>>> COMMENT 'from deserializer')
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' 
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://your-bucket/SeismicPortal'

テーブルが作成されたら、それをクエリーする準備ができます。Athena は、Apache Presto と呼ばれるメモリ内の分散型 SQL エンジンを使用します。これは次に使用する、アレイのネストを解除する機能を提供します。

Amazon Redshift への転送

各ソースレコードに埋め込まれたアレイは、Amazon Redshift への JDBC のエクスポート中 (.jar ファイルのダウンロード) に平坦化され個々のレコードに変換されます。Matillion ETL データベースクエリーコンポーネントを使用して、次の画像で示されるように、この手順中にデータ転送を支援します。

このコンポーネントは、以下の手順を自動化することにより、ETL を簡素化します。

SQL SELECT ステートメント (以下の例で示されます) を実行します。

Athena から S3 の一時ストレージにネットワーク経由でクエリー結果をストリーミングします。
Amazon Redshift. に一括データ読み込みを実行します。

Athena は以下の SQL ステートメントを実行します。

SELECT f.id,
	   f.properties.time AS event_time,
	   f.properties.lastupdate,
   f.properties.lon,
   f.properties.lat,
   f.properties.depth,
   f.properties.mag,
   f.properties.flynn_region
FROM “seismic”.”sp_events”
CROSS JOIN UNNEST (features) as t(f)

CROSS JOIN UNNEST 構文は埋め込みアレイを平坦化し、1 日当たり何百もの独立したイベントレコードを生成します。

データは独立したイベントレコードにコピーされ、平坦化され (下図で示されています)、エンリッチメントと集計の準備ができます。

データエンリッチメント

地震は連続する範囲の空間座標に沿って発生します。それらを集計するために、まもなく行う予定ですが、最初のグループにまとめる必要があります。便利な方法は、各イベントを Universal Transverse Mercator (UTM) ゾーンに割り当てることです。.これらのゾーンは、球の緯度/経度座標を 2D 表示に変換する経度 6 度のバンドです。この変換を実施すると、後で視覚化するときにうまく細分化されます。

球の経度/緯度座標を 2 次元 UTM 座標に変換する計算は複雑です。Amazon Redshift のユーザー定義関数 (UDF) を使用して理想的に実施することができます。次のステップの、Matillion コンポーネント経由で、それを呼び出す機能に対して UDF を選択しました。

CREATE OR REPLACE FUNCTION f_ll_utm (Lat float, Long float)
      RETURNS VARCHAR
STABLE
AS $$
From math import pi, sin, cos, tan, sqrt

_deg2rad = pi / 180.0
_rad2deg = 180.0 / pi

_EquatorialRadius = 1
_eccentricitySquared = 2
_ellipsoid = [ “WGS-84”, 6378137, 0.00669438]

UDF は次の 3 個の情報を返さなければなりません。

UTM ゾーンコード
Easting（メートル単位の x 軸測定）
Northing（同上、y 軸測定）

スカラー UDF は単一の値しか返すことができません。したがって、3 つの結果はパイプで区切られた文字列として返され、3 つの値はパイプで区切られます。

値を独立したフィールドに移動するために、UDF が最初に Matillion ETL Calculator コンポーネントを使用して呼び出され、次にフィールドスプリッタと Calculator を使用し、データ型変換と切り捨てが実行されます。

データの集計

繰り返すと、破壊的なプレート境界に具体的に焦点を当てた地震の深さに興味をもっています。深さは地震の潜在的な重要度を予測する上で役立ちます。

沈み込みゾーンをハイライトする空間パターンが現れることを期待して、各 UTM ゾーン内のイベントの深さの平均を見つける必要があります。

Matillion 変換の最後の 3 つの手順 (以下の図で示されています) は、必要な集計を実行し、深さの四分位を追加し、結果データから出力テーブルを作成します。

「UTM ref への集計」手順は、Amazon Redshift に SQL の GROUP BY 関数を実行して、各イベントを適切な UTM ゾーンに近づけます。この集計を行う間、同時に以下のことを行います。

イベントのカウント (視覚的表示のサイズを決めます)。
平均深度の検索 (視覚的表示のカラーを決めます)。
平均の緯度と経度の判別 (UTM ゾーンの中心に近づけ、視覚的表示の位置うを決めます)。

下図は、各列の集計タイプを示します。

平均の深さは便利な測定ですが、最終的なプレゼンテーションでの視覚的なインパクトを最大化するためには、結果を四分位にランク付けする機会も得ます。これにより、最も深い四分位のゾーンが地図で一貫性をもって強調されます。

NTILE(4) OVER (ORDER BY "avg_depth")

Amazon Redshift はこの種のアナリティクスを実施するのに優れており、別の Matillion ETL Calculator コンポーネントの中で配布されます。

Recreate Output 手順では、データセットが Amazon Redshift テーブルに現れ、Amazon QuickSight が視覚化できる状態になります。

Amazon QuickSight の視覚化

Amazon QuickSight の「マップ上のポイント」の視覚化は、2D レンダリングに最適です。このフィールドの値は、Amazon Redshift で集計されたデータから直接取り出されます。

地理空間 — UTM グリッドごとの緯度/経度の平均。
サイズ — レコードのカウント。言い換えると、その UTM ゾーンのイベントの数です。
カラー — 深度 Ntile、ピンク色の 4 番目の四分位。

結果の地図には、平均で最も深い地震の焦点を持つ地域を明確にピンクで強調した、地球上の沈み込み帯が示されています。

まとめとサマリー

この記事では、例として地震学的データを用いて、非構造化データの視覚化を中心とした課題を探索し、ベストプラクティスを示しました。地質学を超えた幅広いソースからのデータセットにも適用可能なアーキテクチャを使って、これらの課題を克服する方法を提案しました。そして、Matillion ETL for Amazon Redshift を使用して、S3、Athena、Amazon Redshift の間のさまざまなデータ処理タスクの活動を編成する方法を検討しました。

AWS Marketplace で Amazon Redshift の Matillion ETL の 14 日間無料トライアルを開始するか、今すぐ、デモの予定を立ててください。ご質問またはご提案については、以下でコメントを残してください。

その他の参考資料

この記事がお役に立てた場合は、Top 8 Best Practices for High-Performance ETL Processing Using Amazon Redshift (Amazon Redshift を使用した高性能 ETL 処理のベストプラクティス・トップ8) とOrchestrate multiple ETL jobs using AWS Step Functions and AWS Lambda (AWS ステップ関数と AWS Lambdaを使用した複数の ETL ジョブの編成)をご確認ください。.

今回のブログ投稿者について

イアン・ファネルは、Matillion の主任ソリューションアーキテクトですが、熟練したクラウドとデータウェアハウスの専門家です。イアンは、お客様のデータ変換を推進し、最先端の技術的課題を解決するためにお客様を支援することに専念していますイアンは技術産業において、25 年以上の経験を有しています。

Amazon Web Services ブログ