Zad1 PostgreSQL / Elasticsearch

Dane

Na potrzeby tego zadania skorzystałem z danych Postcodes from UK które można pobrać tutaj http://ovh.to/73NLuDk, plik postcodes.csv rozmiar 275mb, ilość rekordów 2 532 817.

Struktura danych

CREATE TABLE postcodes (
  postcode VARCHAR,
  status VARCHAR,
  usertype VARCHAR,
  easting FLOAT,
  northing FLOAT,
  positional_quality_indicator FLOAT,
  country VARCHAR,
  latitude FLOAT,
  longitude FLOAT,
  postcode_no_space VARCHAR,
  postcode_fixed_width_seven VARCHAR,
  postcode_fixed_width_eight VARCHAR,
  postcode_area VARCHAR,
  postcode_district VARCHAR,
  postcode_sector VARCHAR,
  outcode VARCHAR,
  incode VARCHAR
);

Przykładowy rekord

AB1 0AA,terminated,small,385386,801193,1,Scotland,57.101474,-2.242851,AB10AA,AB1 0AA,AB1  0AA,AB,AB1,AB1 0,AB1,0AA

PostgreSQL 9.6

Wszystkie polecenia znajdują się w pliku https://github.com/artkolinski/NoSQL/blob/master/scripts/postgresAll.sql Uruchamiamy bazę danych, tworzymy tabelę skryptem https://github.com/artkolinski/NoSQL/blob/master/scripts/create.sql oraz włączamy pomiar czasu.

\i create.sql;
\timing;

Kopiujemy dane z pliku CSV do bazy

COPY postcodes FROM ' C:\postcodes.csv ' DELIMITER ',' CSV;

COPY 2532817

Czas: 11002,358 ms

Sprawdzamy ile jest rekordów

SELECT COUNT(*) FROM postcodes;

  count
---------
 2532817
(1 wiersz)

Czas: 370,137 ms

Wybieramy kurs wschodni oraz północny dla kodu ‘WR3 8AS’

select easting, northing from postcodes where postcode = 'WR3 8AS';

 easting | northing
---------+----------
  384785 |   256819
(1 wiersz)

Czas: 296,812 ms

Szukamy dystryktu kodu pocztowego ‘M1 1AA’:

select postcode_district from postcodes where postcode_fixed_width_eight = 'M1   1AA';

postcode_district
-------------------
 M1
(1 wiersz)

Czas: 408,702 ms

Szukamy współrzędnych geograficznych dla kodu ‘RG46SU’:

select latitude, longitude from postcodes where postcode_no_space = 'RG46SU';

 latitude  | longitude
-----------+-----------
 51.470889 | -0.918018
(1 wiersz)

Czas: 383,943 ms

Teraz chcemy znaleźć kurs wschodni oraz północny wieży w Londynie “The Tower of London” w celu dalszej bardziej ciekawej obróbki:

select easting, northing from postcodes where postcode = 'EC3N 4AB';

 easting | northing
---------+----------
  533602 |   180570
(1 wiersz)

Czas: 285,792 ms

Szukamy 10 najbliższych kodów pocztowych dla “The Tower of London” oraz obliczamy ich odległość:

select postcode, sqrt(pow(abs(533668 - easting),2) + pow(abs(180551 - northing),2)) as distance from postcodes order by distance limit 10;

 postcode |     distance
----------+------------------
 EC3N 4AE | 68.6804193347711
 EC3N 4AB | 68.6804193347711
 EC3N 4AQ | 85.7962703152066
 EC3N 4AD | 99.9849988748312
 E1W 1AY  | 111.162043881893
 E1W 1XA  | 111.162043881893
 E1W 1XE  | 111.162043881893
 E1W 1UU  | 111.162043881893
 E1W 1UY  | 111.162043881893
 E1W 1UN  | 111.162043881893
(10 wierszy)

Czas: 1311,928 ms

Zmieniamy trochę polecenie aby uzyskać tylko aktywne kody w obszarze 1km x 1km od “The Tower of London” ale bez kodu “The Tower of London” którym jest ‘EC3N 4AB’:

select postcode, sqrt(pow(abs(533668 - easting),2) + pow(abs(180551 - northing),2)) as distance from postcodes where status = 'live' and easting between 533668 - 500 and 533668 + 500 and northing between 180551 - 500 and 180551 + 500 and postcode != 'EC3N 4AB' order by distance limit 10;

postcode |     distance
----------+------------------
 EC3N 4AE | 68.6804193347711
 E1W 1YL  | 111.162043881893
 E1W 1AY  | 111.162043881893
 E1W 1XB  | 111.162043881893
 E1W 1UN  | 111.162043881893
 E1W 1LE  | 125.251746494809
 E1W 1DD  | 135.594985158006
 E1W 1BA  | 145.248063670398
 EC3N 4DR | 196.468827043885
 E1W 1LD  |  220.67623342807
(10 wierszy)

Czas: 342,497 ms

Zrobienie wykresu dla ostatniego polecenia:

COPY(select postcode, sqrt(pow(abs(533668 - easting),2) + pow(abs(180551 - northing),2)) as distance from postcodes where status = 'live' and easting between 533668 - 500 and 533668 + 500 and northing between 180551 - 500 and 180551 + 500 and postcode != 'EC3N 4AB' order by distance) to 'D:\chart.csv' with CSV HEADER;

COPY 286

Czas: 825,638 ms

Użycie RStudio do rysowania

dane <- read.csv('chart.csv', sep=',')
plot(dane$postcode, dane$distance, cex = 0.5, main="Postcode / Distance", xlab = "Postcode ", ylab = "Distance", las = 1)

ElasticSearch 5.2.2

Do importu danych z csv użyłem programu logstash-5.3.0, mój skrypt importujący wygląda następująco:

input {
stdin {
type => "stdin-type"
}
file {
path => ["C:/postcodes.csv"]
start_position => "beginning"
}
}
filter {
csv {
columns => ["postcode","status","usertype","easting","northing","positional_quality_indicator","country","latitude","longitude","postcode_no_space","postcode_fixed_width_seven","postcode_fixed_width_eight","postcode_area","postcode_district","postcode_sector","outcode","incode"]
separator => ","
}
}
output {
stdout { codec => rubydebug }
elasticsearch {
hosts => "localhost:9200"
index => "csv_index"
}
}
Czas importu ~32min

Sprawdzenie liczby zaimportowanych danych

curl -XGET localhost:9200/csv_index/_count
{"count":2532817,"_shards":{"total":5,"successful":5,"failed":0}}

Sprawdzmy czy maping został dokonany prawidłowo podczas importu

curl -s "http://localhost:9200/csv_index/"
{"csv_index":{"aliases":{},"mappings":{"logs":{"properties":{"@timestamp":{"type":"date"},"@version":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"country":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"easting":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"host":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"incode":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"latitude":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"longitude":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"message":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"northing":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"outcode":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"path":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"positional_quality_indicator":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"postcode":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"postcode_area":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"postcode_district":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"postcode_fixed_width_eight":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"postcode_fixed_width_seven":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"postcode_no_space":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"postcode_sector":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"status":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}},"usertype":{"type":"text","fields":{"keyword":{"type":"keyword","ignore_above":256}}}}}},"settings":{"index":{"creation_date":"1491224430222","number_of_shards":"5","number_of_replicas":"1","uuid":"163JkMJDQQWRL7otLJLejw","version":{"created":"5020299"},"provided_name":"csv_index"}}}}

Szukamy kodu ‘M11AA’:

curl -s "http://localhost:9200/csv_index/_search?pretty=true&q=postcode_no_space:'M11AA'"
{
  "took" : 21,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 12.727802,
    "hits" : [
      {
        "_index" : "csv_index",
        "_type" : "logs",
        "_id" : "AVsz8J-1mvZBZk3K4IVs",
        "_score" : 12.727802,
        "_source" : {
          "postcode_district" : "M1",
          "country" : "England",
          "easting" : "385022",
          "positional_quality_indicator" : "1",
          "latitude" : "53.487362",
          "postcode_fixed_width_eight" : "M1   1AA",
          "postcode" : "M1 1AA",
          "usertype" : "large",
          "message" : "M1 1AA,terminated,large,385022,398985,1,England,53.487362,-2.227197,M11AA,M1  1AA,M1   1AA,M,M1,M1 1,M1,1AA",
          "northing" : "398985",
          "path" : "C:/postcodes.csv",
          "postcode_sector" : "M1 1",
          "@timestamp" : "2017-04-03T13:10:59.054Z",
          "postcode_fixed_width_seven" : "M1  1AA",
          "outcode" : "M1",
          "@version" : "1",
          "host" : "DESKTOP-MJ9FGTH",
          "postcode_area" : "M",
          "postcode_no_space" : "M11AA",
          "incode" : "1AA",
          "status" : "terminated",
          "longitude" : "-2.227197"
        }
      }
    ]
  }
}

Szukamy kodu ‘RG46SU’:

curl -s "http://localhost:9200/csv_index/_search?pretty=true&q=postcode_no_space:'RG46SU'"
{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 12.728338,
    "hits" : [
      {
        "_index" : "csv_index",
        "_type" : "logs",
        "_id" : "AVsz9K54mvZBZk3K6NHf",
        "_score" : 12.728338,
        "_source" : {
          "postcode_district" : "RG4",
          "country" : "England",
          "easting" : "475249",
          "positional_quality_indicator" : "1",
          "latitude" : "51.470889",
          "postcode_fixed_width_eight" : "RG4  6SU",
          "postcode" : "RG4 6SU",
          "usertype" : "large",
          "message" : "RG4 6SU,live,large,475249,175213,1,England,51.470889,-0.918018,RG46SU,RG4 6SU,RG4  6SU,RG,RG4,RG4 6,RG4,6SU",
          "northing" : "175213",
          "path" : "C:/postcodes.csv",
          "postcode_sector" : "RG4 6",
          "@timestamp" : "2017-04-03T13:15:24.975Z",
          "postcode_fixed_width_seven" : "RG4 6SU",
          "outcode" : "RG4",
          "@version" : "1",
          "host" : "DESKTOP-MJ9FGTH",
          "postcode_area" : "RG",
          "postcode_no_space" : "RG46SU",
          "incode" : "6SU",
          "status" : "live",
          "longitude" : "-0.918018"
        }
      }
    ]
  }
}

Szukamy współrzędnych geograficznych dla kodu ‘RG46SU’:

Curl -s "http://localhost:9200/csv_index/_search?pretty=true&q=postcode_no_space:'RG46SU'&filter_path=took,hits.hits._source.longitude,hits.hits._source.latitude"
{
  "took" : 2,
  "hits" : {
    "hits" : [
      {
        "_source" : {
          "latitude" : "51.470889",
          "longitude" : "-0.918018"
        }
      }
    ]
  }
}

Teraz chcemy znaleźć współrzędne wieży w Londynie “The Tower of London”:

Curl -s "http://localhost:9200/csv_index/_search?pretty=true&q=postcode_no_space:'EC3N4AB'&filter_path=took,hits.hits._source.latitude,hits.hits._source.longitude"
{
  "took" : 2,
  "hits" : {
    "hits" : [
      {
        "_source" : {
          "latitude" : "51.470889",
          "longitude" : "-0.918018"
        }
      }
    ]
  }
}

Następnie chcemy znaleźć kurs wschodni oraz północny wieży w Londynie “The Tower of London”:

Curl -s "http://localhost:9200/csv_index/_search?pretty=true&q=postcode_no_space:'EC3N4AB'&filter_path=took,hits.hits._source.easting,hits.hits._source.northing"
{
  "took" : 2,
  "hits" : {
    "hits" : [
      {
        "_source" : {
          "easting" : "533602",
          "northing" : "180570"
        }
      }
    ]
  }
}

Podsumowanie

Zadanie	Czas ElasticSearch	Czas PostgreSQL
import danych 2,5mln	32min	11s
wyszukanie ‘M11AA’	21ms	408ms
wyszukanie ‘RG46SU’	2ms	380ms
wyszukanie ‘RG46SU’
lat / lon	2ms	383ms
wyszukanie ‘EC3N4AB’
lat / lon	2ms	285ms
wyszukanie ‘EC3N4AB’
easting/northing	2ms	290ms

Skala ocen 1-10 (więcej = lepiej)

Kryteria	ElasticSearch	PostgreSQL
import danych	2	10
latwość użycia	5	7
predkość nauki	1	8
czas odpowiedzi	10	1
czas tworzenia zap.	1	9