{"id":131,"date":"2019-07-11T16:27:59","date_gmt":"2019-07-11T13:27:59","guid":{"rendered":"http:\/\/b00blik.ru\/tech\/?p=131"},"modified":"2022-08-01T23:36:57","modified_gmt":"2022-08-01T20:36:57","slug":"how-to-analyse-csv-data-with-elastic-stack","status":"publish","type":"post","link":"https:\/\/b00blik.ru\/tech\/?p=131","title":{"rendered":"How to analyse CSV data with Elastic stack"},"content":{"rendered":"

\ud83d\udc41 124<\/p>\n\n\n

Hi!<\/p>\n

Imagine that we have to analyse a big amount of users data. At this article there will be a data in CSV format.<\/p>\n

For example, we can have a data in next format:<\/p>\n

(0118) 008 0694 | 180504 | Hattersheim am Main | 6<\/pre>\n
First column is user’s phone number, second column is date in YYMMDD format, third is a city, and the last one is a billed amount for this phone number for this day.<\/p>\n
It would be great to get some aggregated info by data\/cities\/etc. To perform this aggregations for a huge amount of data we can use ELK-stack.<\/p>\n
<\/p>\n
What is ELK-stack? According to official website:<\/p>\n
«ELK» is the acronym for three open source projects: Elasticsearch, Logstash, and Kibana. Elasticsearch<\/strong> is a search and analytics engine. Logstash<\/strong> is a server\u2011side data processing pipeline that ingests data from multiple sources simultaneously, transforms it, and then sends it to a «stash» like Elasticsearch. Kibana<\/strong> lets users visualize data with charts and graphs in Elasticsearch.<\/p><\/blockquote>\n
Well, we need to perform next steps:<\/p>\n
    \n
  1. Download and install Elasticsearch, Logstash and Kibana;<\/li>\n
  2. Configure Logstash to put data from CSV file to Elasticsearch index;<\/li>\n
  3. Perform some magic queries to Elasticsearch to aggregate some data.<\/li>\n<\/ol>\n
    Great. Let’s find our three components at official page: elastic.co<\/a><\/p>\n
    Extract archives (zip\/tar) to some directories (I extracted to ~\/elk\/kibana and etc. for example).<\/p>\n
    It’s not necessary to change default configuration for Kibana and Elasticsearch. Firstly, we should run Elasticsearch and Kibana after that:<\/p>\n
    ~\/elk\/elasticsearch\/bin\/elasticsearch\n~\/elk\/kibana\/bin\/kibana<\/pre>\n
    So, after few seconds terminal will look like that:\"elkscreen\"<\/a><\/p>\n
    After starting Elasticsearch we can find some info about this instance at localhost<\/a> with port 9200:<\/p>\n
    {\n  \"name\" : \"MacBook-Air-Yuri.local\",\n  \"cluster_name\" : \"elasticsearch\",\n  \"cluster_uuid\" : \"EKex54PqTSOsU9GoE6nuZg\",\n  \"version\" : {\n    \"number\" : \"7.2.0\",\n    \"build_flavor\" : \"default\",\n    \"build_type\" : \"tar\",\n    \"build_hash\" : \"508c38a\",\n    \"build_date\" : \"2019-06-20T15:54:18.811730Z\",\n    \"build_snapshot\" : false,\n    \"lucene_version\" : \"8.0.0\",\n    \"minimum_wire_compatibility_version\" : \"6.8.0\",\n    \"minimum_index_compatibility_version\" : \"6.0.0-beta1\"\n  },\n  \"tagline\" : \"You Know, for Search\"\n}<\/pre>\n
    And at port 5601<\/a> we can find Kibana. Kibana connects to our running Elasticsearch instance. Let’s click Monitoring tab at the left and get some info about instance:<\/p>\n
    \"kibana\"<\/a><\/p>\n
     <\/p>\n
    We’ve found that everything is OK with our ES and Kibana instances, so let’s configure Logstash and put some data into Elasticsearch index.<\/p>\n
    Q: What is an index?<\/p>\n
    A:  An index is like a table<\/span> in a relational database. It has a mapping which contains a type, which contains the fields in the index. An index is a logical namespace which maps to one or more primary shards and can have zero or more replica shards.<\/p><\/blockquote>\n
    Let’s configure pipeline. The Logstash event processing pipeline has three stages: inputs \u2192 filters \u2192 outputs. Inputs generate events, filters modify them, and outputs ship them elsewhere.<\/p>\n
    There is pipeline<\/em> directory in Logstash installation path. So, there are pipelines configurations located. We can create example_pipeline.conf<\/em> file.<\/p>\n
    input {\n    file {\n        path => \"\/home\/usershome\/somelocation\/datadir\/*.csv\"\n        start_position => \"beginning\"\n        sincedb_path => \"\/dev\/null\"\n    }\n}\n\nfilter {\n    csv {\n        separator => \" | \"\n\tcolumns => [\"phone\", \"date\", \"city\", \"amount\"]\n    }\n}\noutput {\n    elasticsearch {\n        hosts => [\"http:\/\/localhost:9200\"]\n        index => \"cdr_index_v1\"\n    }\n    stdout { }\n}<\/pre>\n
    Our target index is cdr_index_v1<\/em>.<\/p>\n
    Now let’s tell Logstash that we prepared configuration for the new pipeline. There is also config<\/em> directory in Logstash installation. We have to remove example configuration files and make some changes in pipelines.yml:<\/p>\n
     - pipeline.id: example_pipeline\n   pipeline.workers: 2\n   path.config: \"\/Users\/b00blik\/elk\/logstash\/pipeline\/example_pipeline.conf\"<\/pre>\n
    OK, our configuration is prepared. Now let’s go to logstash\/bin<\/em> directory and start logstash <\/em>executable file. Something like following will be written during data process:<\/p>\n
    \"logstash\"<\/a>And after that we can check our Index Management in Kibana:<\/p>\n
    \"kibana_index\"<\/a><\/p>\n
     <\/p>\n
    Let’s go to «Dev Tools» tab and perform some query to select data:<\/p>\n
    \"query_by_city\"<\/a><\/p>\n
    We performed query to select data by city. hits<\/em>->total<\/em>->value<\/em> is 2000, so ES returned to us 2000 documents. In hits<\/em> array there are all selected documents.<\/p>\n
    Pay attention to amount<\/em> field in documents \u2013 it contains string value instead of number and date<\/em> is also string-typed instead of datetime.<\/p>\n
    To perform some aggregations we have to convert this data into specific type. Conversion can be performed by reindexing<\/a>.<\/p>\n
    Using reindex API we can make a copy of source index with some changes to field types.<\/p>\n
    Firstly, we have to prepare ingest node<\/a>. In Elasticsearch installation directory set following string in config\/elasticsearch.yml<\/em> file:<\/p>\n
    node.ingest: true<\/pre>\n
    If it was false, restart an Elasticsearch.<\/p>\n
    Go to Dev Tools console. Let’s create pipeline for data converting:<\/p>\n
    PUT _ingest\/pipeline\/ingest-pipeline-Date\n{\n  \"description\": \"convert Date\",\n  \"processors\": [\n    {\n      \"date\": {\n        \"field\": \"date\",\n        \"target_field\": \"eventDate\",\n        \"formats\": [\"yyMMdd\"],\n        \"timezone\": \"UTC\"\n      },\n      \"convert\": {\n        \"field\": \"amount\",\n        \"type\": \"long\"\n      }\n    }\n  ]\n}<\/pre>\n
    And click «play» button to perform request. Answer will be like that:<\/p>\n
    {\n  \"acknowledged\" : true\n}<\/pre>\n
    Next, let’s perform reindex request to make new index in ES with updated fields’ types:<\/p>\n
    POST _reindex?refresh=true\n{\n  \"source\": {\n    \"index\": \"cdr_index_v1\"\n  },\n  \"dest\": {\n    \"index\": \"cdr_index_refiltered_v1\",\n    \"pipeline\": \"ingest-pipeline-Date\"\n  }\n}<\/pre>\n
    A new index can be found in Kibana:<\/p>\n
    \"indices\"<\/a><\/p>\n
    Great! After querying to ES all docs will look like that:<\/p>\n
    {\n        \"_index\" : \"cdr_index_refiltered_v1\",\n        \"_type\" : \"_doc\",\n        \"_id\" : \"OBGy4GsB4cQZQeUNx0Pq\",\n        \"_score\" : 5.013716,\n        \"_source\" : {\n          \"date\" : \"180503\",\n          \"amount\" : 9,\n          \"city\" : \"Norman\",\n          \"message\" : \"(0171)313 8219 | 180503 | Norman | 9\",\n          \"path\" : \"\/Users\/b00blik\/elk\/datadir\/example.csv\",\n          \"@timestamp\" : \"2019-07-11T11:02:21.152Z\",\n          \"phone\" : \"(0171)313 8219\",\n          \"@version\" : \"1\",\n          \"host\" : \"Air-Yuri.Dlink\",\n          \"eventDate\" : \"2018-05-03T00:00:00.000Z\"\n        }<\/pre>\n
    Looks good. But this index exists only in Elasticsearch, and we have to tell Kibana that we have something new! We can do it by creating Index Pattern. Select «Index Patterns» in Kibana section at Management console and create Index Pattern:<\/p>\n
    \"ind_p1\"<\/a><\/p>\n
    At the second step Kibana asks us to select a field with timestamp. I selected nothing.\"ind_p2\"<\/a><\/p>\n
    Well, now we can make some visualizations. Open «Visualize» tab in Kibana and click «Create new visualization»:<\/p>\n
    \"vis1\"<\/a><\/p>\n
    After that, for example, let’s select «Horizontal bar» and our recently created Kibana index.<\/p>\n
    We can make visualization by cities. Axis can be changed in the left panel:<\/p>\n
    \"vis2\"<\/a><\/p>\n
     <\/p>\n
    Another type of data analysis can be an aggregation<\/a>. For example, we can aggregate an amount value for every city. Query will look like this:<\/p>\n
    POST cdr_index_refiltered_v1\/_search?size=0\n{\n  \"aggs\": {\n    \"by_city\": {\n        \"terms\": {\n          \"field\":\"city.keyword\"\n        },\n        \"aggs\": {\n                    \"sum_au\": {\n                      \"sum\": {\n                        \"field\": \"amount\"\n                      }\n                    }\n       }\n    }\n  }\n}<\/pre>\n
    And result of query will look like that:<\/p>\n
    {\n  \"took\" : 70,\n  \"timed_out\" : false,\n  \"_shards\" : {\n    \"total\" : 1,\n    \"successful\" : 1,\n    \"skipped\" : 0,\n    \"failed\" : 0\n  },\n  \"hits\" : {\n    \"total\" : {\n      \"value\" : 10000,\n      \"relation\" : \"gte\"\n    },\n    \"max_score\" : null,\n    \"hits\" : [ ]\n  },\n  \"aggregations\" : {\n    \"by_city\" : {\n      \"doc_count_error_upper_bound\" : 0,\n      \"sum_other_doc_count\" : 274000,\n      \"buckets\" : [\n        {\n          \"key\" : \"Castelnovo del Friuli\",\n          \"doc_count\" : 4000,\n          \"sum_au\" : {\n            \"value\" : 20000.0\n          }\n        },\n        {\n          \"key\" : \"Roveredo in Piano\",\n          \"doc_count\" : 4000,\n          \"sum_au\" : {\n            \"value\" : 24000.0\n          }\n        },\n        {\n          \"key\" : \"Allentown\",\n          \"doc_count\" : 3000,\n          \"sum_au\" : {\n            \"value\" : 21000.0\n          }\n        },\n        {\n          \"key\" : \"Devon\",\n          \"doc_count\" : 3000,\n          \"sum_au\" : {\n            \"value\" : 26000.0\n          }\n        },\n        {\n          \"key\" : \"Flin Flon\",\n          \"doc_count\" : 3000,\n          \"sum_au\" : {\n            \"value\" : 16000.0\n          }\n        },\n        {\n          \"key\" : \"Acciano\",\n          \"doc_count\" : 2000,\n          \"sum_au\" : {\n            \"value\" : 2000.0\n          }\n        },\n        {\n          \"key\" : \"Alphen aan den Rijn\",\n          \"doc_count\" : 2000,\n          \"sum_au\" : {\n            \"value\" : 4000.0\n          }\n        },\n        {\n          \"key\" : \"Al\u00e8s\",\n          \"doc_count\" : 2000,\n          \"sum_au\" : {\n            \"value\" : 14000.0\n          }\n        },\n        {\n          \"key\" : \"Aquila d'Arroscia\",\n          \"doc_count\" : 2000,\n          \"sum_au\" : {\n            \"value\" : 20000.0\n          }\n        },\n        {\n          \"key\" : \"Bendigo\",\n          \"doc_count\" : 2000,\n          \"sum_au\" : {\n            \"value\" : 10000.0\n          }\n        }\n      ]\n    }\n  }\n}\n<\/pre>\n
    Useful links:<\/p>\n
      \n
    1. https:\/\/www.elastic.co\/guide\/en\/elastic-stack\/current\/index.html<\/a><\/li>\n
    2. https:\/\/www.elastic.co\/guide\/en\/kibana\/current\/setup.html<\/a><\/li>\n
    3. https:\/\/www.elastic.co\/guide\/en\/kibana\/current\/visualize.html<\/a><\/li>\n<\/ol>","protected":false},"excerpt":{"rendered":"
      Hi! Imagine that we have to analyse a big amount of users data. At this article there will be a data in CSV format. For example, we can have a data in next format: (0118) 008 0694 | 180504 | Hattersheim am Main | 6 First column is user’s phone number, second column is date … <\/p>\n
      \u0427\u0438\u0442\u0430\u0442\u044c \u0434\u0430\u043b\u0435\u0435 \u00abHow to analyse CSV data with Elastic stack\u00bb<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false},"version":2}},"categories":[1],"tags":[29,27,28],"class_list":["post-131","post","type-post","status-publish","format-standard","hentry","category-1","tag-data","tag-elastic","tag-elk","entry"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","views":{"total":109,"cached_at":""},"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p6oGDv-27","_links":{"self":[{"href":"https:\/\/b00blik.ru\/tech\/index.php?rest_route=\/wp\/v2\/posts\/131","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/b00blik.ru\/tech\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/b00blik.ru\/tech\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/b00blik.ru\/tech\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/b00blik.ru\/tech\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=131"}],"version-history":[{"count":11,"href":"https:\/\/b00blik.ru\/tech\/index.php?rest_route=\/wp\/v2\/posts\/131\/revisions"}],"predecessor-version":[{"id":563,"href":"https:\/\/b00blik.ru\/tech\/index.php?rest_route=\/wp\/v2\/posts\/131\/revisions\/563"}],"wp:attachment":[{"href":"https:\/\/b00blik.ru\/tech\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=131"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/b00blik.ru\/tech\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=131"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/b00blik.ru\/tech\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=131"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}