前回はHadoopやSparkなどとの連携について見てきました。今回は,
ログ分析用のSQL拡張関数
ログ分析のためのロジックは,
表1 おもなSQL拡張関数
関数 | 説明 |
---|---|
Time Series Analytics | 時系列の補間 |
Event Pattern Matching | イベントのパターンマッチング |
Geospatial Analytics | 地理空間分析 |
「Time Series Analytics」
図1 Time Series関数のサンプルSQL
SELECT * FROM TickStore ORDER BY 日付; 日付 | 株式 | 入札価格 ---------------------+------+---------- 2017-02-24 03:00:00 | A | 10.0 2017-02-24 03:00:00 | B | 7.0 2017-02-24 03:00:05 | A | 10.5 2017-02-24 03:00:06 | B | 6.7
図2 Time Series関数のサンプルSQL
SELECT slice_time, 株式, TS_LAST_VALUE(入札価格, 'LINEAR') 入札価格 FROM TickStore TIMESERIES slice_time AS '2 seconds' OVER(PARTITION BY 株式 ORDER BY 日付) ; slice_time | 株式 | 入札価格 ---------------------+------+---------- 2017-02-24 03:00:00 | B | 6.9 2017-02-24 03:00:02 | B | 6.8 2017-02-24 03:00:04 | B | 6.7 2017-02-24 03:00:06 | B | 2017-02-24 03:00:00 | A | 10.2 2017-02-24 03:00:02 | A | 10.4 2017-02-24 03:00:04 | A |
「Event Pattern Matching」
図3 Event Pattern Matching関数のサンプルSQL
select * from clickstream_log order by ts; uid | sid | ts | refURL | pageURL | action -----+-----+----------+-----------------------+-----------------------+-------- 1 | 100 | 12:00:00 | a.example.com | b.example.com/home | V 1 | 100 | 12:01:00 | b.example.com/home | b.example.com/floby | V 1 | 100 | 12:02:00 | b.example.com/floby | b.example.com/shamwow | V 1 | 100 | 12:03:00 | b.example.com/shamwow | b.example.com/buy | P 2 | 100 | 12:10:00 | a.example.com | b.example.com/home | V 2 | 100 | 12:11:00 | b.example.com/home | b.example.com/forks | V 2 | 100 | 12:13:00 | b.example.com/forks | b.example.com/buy | P
図4 Event Pattern Matching関数のサンプルSQL
SELECT uid, sid, ts, refurl, pageurl, action, event_name(), pattern_id(), match_id() FROM clickstream_log MATCH (PARTITION BY uid, sid ORDER BY ts DEFINE Entry AS RefURL NOT ILIKE '%b.example.com%' AND PageURL ILIKE '%b.example.com%', Onsite AS PageURL ILIKE '%b.example.com%' AND Action='V', Purchase AS PageURL ILIKE '%b.example.com%' AND Action = 'P' PATTERN P AS (Entry Onsite* Purchase) ROWS MATCH FIRST EVENT); uid | sid | ts | refurl | pageurl | action | event_name | pattern_id | match_id -----+-----+----------+-----------------------+-----------------------+--------+------------+------------+---------- ┌―――――――――――――――――――――――――――――――――――――――――――――――――――――――――┐ │ 1 | 100 | 12:00:00 | a.example.com | b.example.com/home | V | Entry | 1 | 1 │ │ 1 | 100 | 12:01:00 | b.example.com/home | b.example.com/floby | V | Onsite | 1 | 2 │ │ 1 | 100 | 12:02:00 | b.example.com/floby | b.example.com/shamwow | V | Onsite | 1 | 3 │ │ 1 | 100 | 12:03:00 | b.example.com/shamwow | b.example.com/buy | P | Purchase | 1 | 4 │ └―――――――――――――――――――――――――――――――――――――――――――――――――――――――――┘ ┌―――――――――――――――――――――――――――――――――――――――――――――――――――――――――┐ │ 2 | 100 | 12:10:00 | a.example.com | b.example.com/home | V | Entry | 1 | 1 │ │ 2 | 100 | 12:11:00 | b.example.com/home | b.example.com/forks | V | Onsite | 1 | 2 │ │ 2 | 100 | 12:13:00 | b.example.com/forks | b.example.com/buy | P | Purchase | 1 | 3 │ └―――――――――――――――――――――――――――――――――――――――――――――――――――――――――┘
テーブル内のデータから,
「Geospatial Analytics」
図6 シェープファイルのインポート
COPY table_name(columns) WITH SOURCE STV_ShpSource(file = 'filename') PARSER STV_ShpParser() ;
図7 New YorkとBostonの距離を算出
SELECT ST_Distance(ST_GeographyFromText('Point(-71.0598 42.3601)'), ST_GeographyFromText('Point(-74.0059 40.7128)') USING PARAMETERS spheroid = true); ST_Distanse ------------------ 306423.562065704
以上のように,