Vantageでのパフォーマンスを意識した時系列データの分析

著者 [Remi Turpaud](remi.turpaud@teradata.com)
最終更新日 2022年4月15日

:::note warn
警告
本記事はTeradata Corporationのサイト[Getting Started](https://quickstarts.teradata.com/index.html#getting-access-to-vantage)に掲載された内容を抄訳したものです。掲載内容の正確性・完全性・信頼性・最新性を保証するものではございません。正確な内容については原本をご参照下さい。
また、修正が必要な箇所やご要望についてはコメントをよろしくお願いします。
:::

# 概要
時系列とは時間順にインデックス化された一連のデータポイントのことです。IoTやそのほかの幅広いアプリケーションやデバイスによって継続的に生成、収集されるデータを言います。Vantageは時系列データの分析を簡素化するためのさまざまな機能を提供します。

# 前提条件
Vantageインスタンスにアクセスする必要があります。Vantage ExpressからDeveloper、DYI、Vantage as a ServiceまでのすべてのVantageエディションのバージョン17.10以降で時系列の機能とNOSが有効になっています。

::: note info
Vantageの新しいインスタンスが必要な場合は、[Google Cloud](https://qiita.com/ykswi/items/10a382de5fa5a4a42c50)、[Azure](https://qiita.com/ykswi/items/2c30aa1f5413b16c838c)、[AWS](https://qiita.com/ykswi/items/fba559149ad568678d58)のクラウドに無料のVantage Expressをデプロイメントすることができます。
また、[VMware](https://qiita.com/ykswi/items/ebe1d854c38db9bb0f51)、[VirtualBox](https://qiita.com/ykswi/items/ffadaa375add309f2075)、または[UTM](https://qiita.com/ykswi/items/94995954ae723185c2f7)を使用してローカルマシン上でVantage Expressを実行することもできます。
:::

# NOSを使用してAWS S3からデータセットをインポートする
サンプルデータセットはS3バケットにありNOSを使用してVantageから直接アクセスすることが可能です。データはCSV形式なので、antageに取り込んで時系列分析をしてみましょう。

まずはデータを見てみましょう。以下のクエリはS3バケットから10行を取得します。

```sql:SelectfromS3.sql
SELECT TOP 10 * FROM (
	LOCATION='/s3/nos-demo-apj.s3.amazonaws.com/taxi/2014/11/data_2014-11-25.csv'
) AS d;
```

返される結果は以下のようになります。

|Location|vendor_id|pickup_datetime|dropoff_datetime|passenger_count|trip_distance|pickup_longitude|pickup_latitude|rate_code|store_and_fwd_flag|dropoff_longitude|dropoff_latitude|payment_type|fare_amount|surcharge|mta_tax|tip_amount|tolls_amount|total_amount|
|:------------------------------------------------------------------|:---------|:-----------------|:-----------------|----------------:|--------------:|-----------------:|----------------:|----------:|:-------------------|------------------:|-----------------:|:-------------|------------:|----------:|--------:|----------:|------------:|------------:|
|/S3/s3.amazonaws.com/nyc-tlc/csv_backup/yellow_tripdata_2013-11.csv|CMT|25/11/2013 15:18|25/11/2013 15:33|1|1|-73.992423|40.749517|1|N|-73.98816|40.746557|CRD|10|0|0.5|2.22|0|12.72|
|/S3/s3.amazonaws.com/nyc-tlc/csv_backup/yellow_tripdata_2013-11.csv|CMT|25/11/2013 5:34|25/11/2013 5:48|1|3.6|-73.971555|40.794548|1|N|-73.975399|40.755404|CRD|14.5|0.5|0.5|1|0|16.5|
|/S3/s3.amazonaws.com/nyc-tlc/csv_backup/yellow_tripdata_2013-11.csv|CMT|25/11/2013 8:31|25/11/2013 8:55|1|5.9|-73.94764|40.830465|1|N|-73.972323|40.76332|CRD|21|0|0.5|3|0|24.5|
|/S3/s3.amazonaws.com/nyc-tlc/csv_backup/yellow_tripdata_2013-11.csv|CMT|25/11/2013 7:00|25/11/2013 7:04|1|1.2|-73.983357|40.767193|1|N|-73.978394|40.75558|CRD|5.5|0|0.5|1|0|7|
|/S3/s3.amazonaws.com/nyc-tlc/csv_backup/yellow_tripdata_2013-11.csv|CMT|25/11/2013 15:24|25/11/2013 15:30|1|0.5|-73.982313|40.764827|1|N|-73.982129|40.758889|CRD|5.5|0|0.5|3|0|9|
|/S3/s3.amazonaws.com/nyc-tlc/csv_backup/yellow_tripdata_2013-11.csv|CMT|25/11/2013 15:53|25/11/2013 16:00|1|0.6|-73.978104|40.752966|1|N|-73.985756|40.762685|CRD|6|1|0.5|1|0|8.5|
|/S3/s3.amazonaws.com/nyc-tlc/csv_backup/yellow_tripdata_2013-11.csv|CMT|25/11/2013 6:49|25/11/2013 7:04|1|3.8|-73.976005|40.744481|1|N|-74.016063|40.717298|CRD|14|0|0.5|2.9|0|17.4|
|/S3/s3.amazonaws.com/nyc-tlc/csv_backup/yellow_tripdata_2013-11.csv|CMT|25/11/2013 21:20|25/11/2013 21:26|1|1.1|-73.946371|40.775369|1|N|-73.95309|40.785103|CRD|6.5|0.5|0.5|1.5|0|9|
|/S3/s3.amazonaws.com/nyc-tlc/csv_backup/yellow_tripdata_2013-11.csv|CMT|25/11/2013 10:02|25/11/2013 10:17|1|2.2|-73.952625|40.780962|1|N|-73.98163|40.777978|CRD|12|0|0.5|2|0|14.5|
|/S3/s3.amazonaws.com/nyc-tlc/csv_backup/yellow_tripdata_2013-11.csv|CMT|25/11/2013 9:43|25/11/2013 10:02|1|3.3|-73.982013|40.762507|1|N|-74.006854|40.719582|CRD|15|0|0.5|2|0|17.5|

完全なデータを抽出しVantageに取り込んでさらに解析してみましょう。

```sql:CreateandInsert.sql
CREATE TABLE trip
(
  vendor_id varchar(10) character set latin NOT casespecific,
  rate_code          integer,
  pickup_datetime timestamp(6),
  dropoff_datetime timestamp(6),
  passenger_count   smallint,
  trip_distance float,
  pickup_longitude float,
  pickup_latitude float,
  dropoff_longitude float,
  dropoff_latitude float
)
NO PRIMARY INDEX ;

INSERT INTO trip
SELECT TOP 200000 vendor_id ,
  rate_code,
  pickup_datetime,
  dropoff_datetime ,
  passenger_count,
   trip_distance ,
  pickup_longitude,
  pickup_latitude ,
  dropoff_longitude ,
  dropoff_latitude FROM (
	LOCATION='/s3/nos-demo-apj.s3.amazonaws.com/taxi/2014/11/data_2014-11-25.csv'
) AS d;
```

結果は以下のように表示されるはずです。

```sql:Result.sql
200000 rows affected.
```

VantageはS3からデータを取得し、作成したtripテーブルに挿入しました。

# 基本的な時系列操作
データセットができたのでVantageの機能を使用してデータセットをすばやく分析することができるようになりました。まず11月の1時間ごとに何人の乗客がピックアップされているかを確認します。

```sql:SelectCountby1hour.sql
SELECT TOP 10
	$TD_TIMECODE_RANGE
	,begin($TD_TIMECODE_RANGE) time_bucket_start
	,sum(passenger_count) passenger_count
FROM trip
WHERE extract(month from pickup_datetime)=11
GROUP BY TIME(HOURS(1))
USING TIMECODE(pickup_datetime)
ORDER BY 1;
```

[GROUP BY TIME](https://www.docs.teradata.com/r/Teradata-VantageTM-Time-Series-Tables-and-Operations/July-2021/Time-Series-Aggregates-and-SELECT-Extensions/GROUP-BY-TIME-Clause)についての詳しい説明はこちらを参考にしてください。

以下のような結果が返されます。

|TIMECODE_RANGE|time_bucket_start|passenger_count|
|:---------------------------------------------------------|:---------------------------------|---------------:|
|(2013-11-04 11:00:00.000000, 2013-11-04 12:00:00.000000)|2013-11-04 11:00:00.000000-05:00	|4|
|(2013-11-04 12:00:00.000000, 2013-11-04 13:00:00.000000)|2013-11-04 12:00:00.000000-05:00	|2|
|(2013-11-04 14:00:00.000000, 2013-11-04 15:00:00.000000)|2013-11-04 14:00:00.000000-05:00	|5|
|(2013-11-04 15:00:00.000000, 2013-11-04 16:00:00.000000)|2013-11-04 15:00:00.000000-05:00	|2|
|(2013-11-04 16:00:00.000000, 2013-11-04 17:00:00.000000)|2013-11-04 16:00:00.000000-05:00	|9|
|(2013-11-04 17:00:00.000000, 2013-11-04 18:00:00.000000)|2013-11-04 17:00:00.000000-05:00	|11|
|(2013-11-04 18:00:00.000000, 2013-11-04 19:00:00.000000)|2013-11-04 18:00:00.000000-05:00	|41|
|(2013-11-04 19:00:00.000000, 2013-11-04 20:00:00.000000)|2013-11-04 19:00:00.000000-05:00	|2791|
|(2013-11-04 20:00:00.000000, 2013-11-04 21:00:00.000000)|2013-11-04 20:00:00.000000-05:00	|15185|
|(2013-11-04 21:00:00.000000, 2013-11-04 22:00:00.000000)|2013-11-04 21:00:00.000000-05:00	|27500|

これは時間のデータから時間帯を抽出して集計した結果です。このと同じ結果を時系列固有の機能がなしで取得することは追加のコードや作業が必要ですが可能です。
しかしさらに一歩進んで11月に何人の乗客がピックアップされベンダーによる15分ごとの平均移動時間がどのくらいかを特定しましょう。

```sql:SelectCountandAverage.sql
SELECT TOP 10
    $TD_TIMECODE_RANGE,
    vendor_id,
    SUM(passenger_count),
    AVG((dropoff_datetime - pickup_datetime ) MINUTE (4)) AS avg_trip_time_in_mins
FROM trip
GROUP BY TIME (MINUTES(15) AND vendor_id)
USING TIMECODE(pickup_datetime)
WHERE EXTRACT(MONTH FROM pickup_datetime)=11
ORDER BY 1,2;
```

このクエリーの結果は以下となります。

|TIMECODE_RANGE|vendor_id|passenger_count|avg_trip_time_in_mins|
|:--------------------------------------------------------|:----------|----------------:|----------------------:|
|(2013-11-04 11:00:00.000000, 2013-11-04 11:15:00.000000)|VTS|1|16|
|(2013-11-04 11:15:00.000000, 2013-11-04 11:30:00.000000)|VTS|1|10|
|(2013-11-04 11:45:00.000000, 2013-11-04 12:00:00.000000)|VTS|2|6|
|(2013-11-04 12:00:00.000000, 2013-11-04 12:15:00.000000)|VTS|1|11|
|(2013-11-04 12:15:00.000000, 2013-11-04 12:30:00.000000)|VTS|1|57|
|(2013-11-04 14:15:00.000000, 2013-11-04 14:30:00.000000)|VTS|1|3|
|(2013-11-04 14:30:00.000000, 2013-11-04 14:45:00.000000)|VTS|2|19|
|(2013-11-04 14:45:00.000000, 2013-11-04 15:00:00.000000)|VTS|2|9|
|(2013-11-04 15:15:00.000000, 2013-11-04 15:30:00.000000)|VTS|1|11|
|(2013-11-04 15:30:00.000000, 2013-11-04 15:45:00.000000)|VTS|1|31|

これがVantageの時系列機能の威力です。複雑で面倒なロジックを必要とせずgroup by time 節を変更するだけで15分ごとにベンダー別の平均旅行時間を求めることができます。
ではこれに基づいて移動平均を作成するのがいかに簡単かを見てみましょう。まず以下のようなビューを作成します。

```sql:ReplaceView.sql
REPLACE VIEW NYC_taxi_trip_ts as
SELECT
	$TD_TIMECODE_RANGE time_bucket_per
	,vendor_id
	,sum(passenger_count) passenger_cnt
	,avg(CAST((dropoff_datetime - pickup_datetime MINUTE(4) ) AS INTEGER))  avg_trip_time_in_mins
FROM trip
GROUP BY TIME (MINUTES(15) and vendor_id)
USING TIMECODE(pickup_datetime)
WHERE extract(month from pickup_datetime)=11
```

15分足の時系列に2時間足の移動平均を計算してみましょう。2時間は8×15分の期間となることはお判りでしょう。

```sql:SelectMovingAverage.sql
SELECT * FROM MovingAverage (
  ON NYC_taxi_trip_ts PARTITION BY vendor_id ORDER BY time_bucket_per
  USING
  MAvgType ('S')
  WindowSize (8)
  TargetColumns ('passenger_cnt')
) AS dt
WHERE begin(time_bucket_per)(date) = '2014-11-25'
ORDER BY vendor_id, time_bucket_per;
```

このクエリーの結果は下記のようなものになります。

|time_bucket_per|vendor_id|passenger_cnt|avg_trip_time_in_mins|passenger_cnt_smavg|
|:---------------------------------------------------------|:--------------|----------------------:|--------------------:|--------------------:|
|(2013-11-04 14:45:00.000000, 2013-11-04 15:00:00.000000)|VTS|2|9|1.375|
|(2013-11-04 15:15:00.000000, 2013-11-04 15:30:00.000000)|VTS|1|11|1.375|
|(2013-11-04 15:30:00.000000, 2013-11-04 15:45:00.000000)|VTS|1|31|1.375|
|(2013-11-04 16:15:00.000000, 2013-11-04 16:30:00.000000)|VTS|2|16|1.375|
|(2013-11-04 16:30:00.000000, 2013-11-04 16:45:00.000000)|VTS|1|3|1.375|
|(2013-11-04 16:45:00.000000, 2013-11-04 17:00:00.000000)|VTS|6|38|2|
|(2013-11-04 17:15:00.000000, 2013-11-04 17:30:00.000000)|VTS|2|29.5|2.125|
|(2013-11-04 17:45:00.000000, 2013-11-04 18:00:00.000000)|VTS|9|20.33333333|3|
|(2013-11-04 18:00:00.000000, 2013-11-04 18:15:00.000000)|VTS|6|23.4|3.5|
|(2013-11-04 18:15:00.000000, 2013-11-04 18:30:00.000000)|VTS|4|15.66666667|3.875|
|(2013-11-04 18:30:00.000000, 2013-11-04 18:45:00.000000)|VTS|8|24.5|4.75|
|(2013-11-04 18:45:00.000000, 2013-11-04 19:00:00.000000)|VTS|23|38.33333333|7.375|
|(2013-11-04 19:00:00.000000, 2013-11-04 19:15:00.000000)|VTS|195|26.61538462|31.625|
|(2013-11-04 19:15:00.000000, 2013-11-04 19:30:00.000000)|VTS|774|13.70083102|127.625|
|(2013-11-04 19:30:00.000000, 2013-11-04 19:45:00.000000)|VTS|586|12.38095238|200.625|
|(2013-11-04 19:45:00.000000, 2013-11-04 20:00:00.000000)|VTS|1236|15.54742097|354|
|(2013-11-04 20:00:00.000000, 2013-11-04 20:15:00.000000)|VTS|3339|11.78947368|770.625|
|(2013-11-04 20:15:00.000000, 2013-11-04 20:30:00.000000)|VTS|3474|10.5603396|1204.375|
|(2013-11-04 20:30:00.000000, 2013-11-04 20:45:00.000000)|VTS|3260|12.26484323|1610.875|
|(2013-11-04 20:45:00.000000, 2013-11-04 21:00:00.000000)|VTS|5112|12.05590062|2247|

（日本語訳注）
この処理を時系列機能なしに実現する場合実に面倒な作業が必要になります。ぜひ一度考えてみてください。

::: note info
上記の時系列操作に加えてVantageにはプライマリタイムインデックス（PTI）を持つ特別な時系列テーブルも用意されています。これはプライマリ インデックス（PI）ではなくPT が定義された通常のVantageテーブルです。PTI 付きのテーブルは時系列機能/操作に必須ではありませんがPTIは時系列データの物理的な保存方法を最適化するため通常のテーブルと比較してパフォーマンスが大幅に向上します。
:::

# まとめ
今回のクイックスタートでは、Vantageの時系列機能を使用して、時系列データセットを簡単に分析する方法について学びました。

# さらに詳しく
・[Teradata Vantage™ - Time Series Tables and Operations](https://docs.teradata.com/r/Teradata-VantageTM-Time-Series-Tables-and-Operations/July-2021/Introduction-to-Teradata-Time-Series-Tables-and-Operations)
・[Query data stored in object storage](https://quickstarts.teradata.com/nos.html)
・[Teradata Vantage™ - Native Object Store Getting Started Guide](https://docs.teradata.com/r/2mw8ooFr~xX0EaaGFaDW8A/root)

#### Teradata Vantageへのお問合せ
[Teradata Vantage へのお問合せ](https://www.teradata.jp/About-Us/Contact)