{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Elastic search query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-29T22:08:19.850891",
     "start_time": "2017-05-29T22:08:19.835879"
    }
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "from elasticsearch import Elasticsearch\n",
    "import pandas as pd\n",
    "import os\n",
    "import numpy as np\n",
    "import dateutil"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Just replace servername by the name of the server where the elastic search is running"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "es = Elasticsearch('http://servername:9200', timeout=20.0, bulk_size=100000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Basic request"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Default size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-29T22:08:46.358304",
     "start_time": "2017-05-29T22:08:46.337841"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "156459 documents found\n"
     ]
    }
   ],
   "source": [
    "results = es.search(index=\"asm\", doc_type=\"dimm\")\n",
    "print(\"{0:d} documents found\".format(results['hits']['total']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The result of the search is a dictionary with 4 keys:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-29T22:08:49.793894",
     "start_time": "2017-05-29T22:08:49.787134"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dict_keys(['_shards', 'hits', 'took', 'timed_out'])\n"
     ]
    }
   ],
   "source": [
    "print(results.keys())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-29T22:08:50.816620",
     "start_time": "2017-05-29T22:08:50.786747"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results['took']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-29T22:08:51.583989",
     "start_time": "2017-05-29T22:08:51.576472"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results['timed_out']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-27T03:37:17.084298",
     "start_time": "2017-05-27T03:37:17.076817"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'failed': 0, 'successful': 5, 'total': 5}"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results['_shards']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The hits is itself a dictionnary with different keys: total, max_score and hits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-27T03:37:22.254079",
     "start_time": "2017-05-27T03:37:22.246286"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dict_keys(['total', 'max_score', 'hits'])\n",
      "10\n"
     ]
    }
   ],
   "source": [
    "print(results['hits'].keys())\n",
    "print(len(results['hits']['hits']))\n",
    "#print(results['hits'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we look at one of those hits:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-27T03:37:30.505193",
     "start_time": "2017-05-27T03:37:30.494899"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'_id': 'AVxGFXJkj_k52QMFZJII', '_type': 'dimm', '_index': 'asm', '_score': 1.0, '_source': {'@timestamp': '2017-04-28T03:43:16', 'dimm_seeing': 0.527}}\n",
      "{'@timestamp': '2017-04-28T03:43:16', 'dimm_seeing': 0.527}\n"
     ]
    }
   ],
   "source": [
    "i=2\n",
    "print(results['hits']['hits'][i])\n",
    "print(results['hits']['hits'][i]['_source'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can turn that into a dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-27T03:37:52.325598",
     "start_time": "2017-05-27T03:37:52.283856"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "feList={'timestamp':[],'dimm_seeing':[]}\n",
    "for r in results['hits']['hits']:\n",
    "    feList['timestamp'].append(dateutil.parser.parse(r['_source']['@timestamp'], ignoretz=True))\n",
    "    feList['dimm_seeing'].append(r['_source']['dimm_seeing'])\n",
    "\n",
    "# Create a Dataframe with the data retrieved\n",
    "fe_dt = pd.DataFrame(feList['dimm_seeing'], index=feList['timestamp'], columns=['dimm_seeing'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-27T03:37:58.597986",
     "start_time": "2017-05-27T03:37:58.574661"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dimm_seeing</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-04-28 03:36:57</th>\n",
       "      <td>0.542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-28 03:41:57</th>\n",
       "      <td>0.570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-28 03:43:16</th>\n",
       "      <td>0.527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-28 03:58:23</th>\n",
       "      <td>0.454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-28 04:03:35</th>\n",
       "      <td>0.439</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     dimm_seeing\n",
       "2017-04-28 03:36:57        0.542\n",
       "2017-04-28 03:41:57        0.570\n",
       "2017-04-28 03:43:16        0.527\n",
       "2017-04-28 03:58:23        0.454\n",
       "2017-04-28 04:03:35        0.439"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(len(fe_dt))\n",
    "fe_dt.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Increased size"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We saw before that the default size returns only 10 hits. Let's increase that to 1000 and loop over the search to get all possible hits."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-27T03:35:31.611742",
     "start_time": "2017-05-27T03:34:55.980160"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 1000\n",
      "Scrolling...\n",
      "scroll size: 459\n",
      "Scrolling...\n",
      "scroll size: 0\n",
      "311918\n"
     ]
    }
   ],
   "source": [
    "feList = { 'timestamp': [], 'dimm_seeing': []}\n",
    "\n",
    "# Initialize the scroll\n",
    "results = es.search(index = 'asm',doc_type = 'dimm',scroll = '2m',size = 1000)\n",
    "for r in results['hits']['hits']:\n",
    "    feList['timestamp'].append(dateutil.parser.parse(r['_source']['@timestamp'], ignoretz=True))\n",
    "    feList['dimm_seeing'].append(r['_source']['dimm_seeing'])\n",
    "\n",
    "sid = results['_scroll_id']\n",
    "scroll_size = results['hits']['total']\n",
    "total=scroll_size\n",
    "# Start scrolling\n",
    "while (scroll_size > 0):\n",
    "    print(\"Scrolling...\")\n",
    "    results = es.scroll(scroll_id = sid, scroll = '2m')\n",
    "    # Update the scroll ID\n",
    "    sid = results['_scroll_id']\n",
    "    # Get the number of results that we returned in the last scroll\n",
    "    scroll_size = len(results['hits']['hits'])\n",
    "    print(\"scroll size: {0:d}\".format(scroll_size))\n",
    "    # Do something with the obtained page\n",
    "    for r in results['hits']['hits']:\n",
    "        feList['timestamp'].append(dateutil.parser.parse(r['_source']['@timestamp'], ignoretz=True))\n",
    "        feList['dimm_seeing'].append(r['_source']['dimm_seeing'])\n",
    "    total+=scroll_size\n",
    "print(total)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-27T03:38:09.952098",
     "start_time": "2017-05-27T03:38:09.932079"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dimm_seeing</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-04-28 03:36:57</th>\n",
       "      <td>0.542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-28 03:41:57</th>\n",
       "      <td>0.570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-28 03:43:16</th>\n",
       "      <td>0.527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-28 03:58:23</th>\n",
       "      <td>0.454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-28 04:03:35</th>\n",
       "      <td>0.439</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     dimm_seeing\n",
       "2017-04-28 03:36:57        0.542\n",
       "2017-04-28 03:41:57        0.570\n",
       "2017-04-28 03:43:16        0.527\n",
       "2017-04-28 03:58:23        0.454\n",
       "2017-04-28 04:03:35        0.439"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a Dataframe with the data retrieved\n",
    "fe_dt = pd.DataFrame(feList['dimm_seeing'], index=feList['timestamp'], columns=['dimm_seeing'])\n",
    "print(len(fe_dt))\n",
    "fe_dt.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Advanced request"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## First trial using es.search"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-26T22:53:58.070199",
     "start_time": "2017-05-26T22:53:57.908612"
    },
    "code_folding": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "458763 documents found\n"
     ]
    }
   ],
   "source": [
    "query={\n",
    "  \"size\": 0,\n",
    "  \"query\": {\n",
    "    \"bool\": {\n",
    "      \"must\": [\n",
    "        {\n",
    "          \"query_string\": {\n",
    "            \"analyze_wildcard\": True,\n",
    "            \"query\": \"*\"\n",
    "          }\n",
    "        },\n",
    "        {\n",
    "          \"range\": {\n",
    "            \"@timestamp\": {\n",
    "              \"gte\": 1432666102379,\n",
    "              \"lte\": 1495828102379,\n",
    "              \"format\": \"epoch_millis\"\n",
    "            }\n",
    "          }\n",
    "        }\n",
    "      ],\n",
    "      \"must_not\": []\n",
    "    }\n",
    "  },\n",
    "  \"_source\": {\n",
    "    \"excludes\": []\n",
    "  },\n",
    "  \"aggs\": {\n",
    "    \"2\": {\n",
    "      \"date_histogram\": {\n",
    "        \"field\": \"@timestamp\",\n",
    "        \"interval\": \"1w\",\n",
    "        \"time_zone\": \"America/Santiago\",\n",
    "        \"min_doc_count\": 1\n",
    "      },\n",
    "      \"aggs\": {\n",
    "        \"1\": {\n",
    "          \"avg\": {\n",
    "            \"field\": \"dimm_seeing\"\n",
    "          }\n",
    "        }\n",
    "      }\n",
    "    }\n",
    "  }\n",
    "}\n",
    "results = es.search(index=\"asm\", body=query)\n",
    "print(\"{0:d} documents found\".format(results['hits']['total']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-26T22:54:04.374949",
     "start_time": "2017-05-26T22:54:04.332224"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "feList = { 'timestamp': [], 'dimm_seeing': []}\n",
    "keep_fetching = True\n",
    "\n",
    "count = 0\n",
    "while keep_fetching:\n",
    "    i = 0\n",
    "    for r in results['hits']['hits']:\n",
    "        feList['timestamp'].append(dateutil.parser.parse(r['_source']['@timestamp'], ignoretz=True))\n",
    "        feList['dimm_seeing'].append(r['_source']['dimm_seeing'])\n",
    "        i += 1\n",
    "\n",
    "    if i == 10000:\n",
    "        count += i\n",
    "        #print (count, \"mark...\")\n",
    "        uri = 'http://134.171.189.13:9200/_search/scroll'\n",
    "        esBody = \"\"\"\n",
    "        {{\n",
    "            \"scroll\": \"1m\",\n",
    "            \"scroll_id\": \"{}\"\n",
    "        }}\n",
    "        \"\"\".format(results['_scroll_id'])\n",
    "        response = requests.post(uri, esBody)\n",
    "        results = json.loads(response.text)\n",
    "    else:\n",
    "        keep_fetching = False\n",
    "\n",
    "# Create a Dataframe with the data retrieved\n",
    "fe_dt = pd.DataFrame(feList['dimm_seeing'], index=feList['timestamp'], columns=['dimm_seeing'])\n",
    "fe_dt['occurrence'] = np.ones(len(fe_dt), dtype=int)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-26T22:54:45.470628",
     "start_time": "2017-05-26T22:54:45.463022"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'hits': [], 'max_score': 0.0, 'total': 458763}"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results['hits']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Second trial using json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-27T02:52:21.754414",
     "start_time": "2017-05-27T02:52:21.670774"
    }
   },
   "outputs": [],
   "source": [
    "# Fetching from ES\n",
    "# The data will be retrieved in batches of 10k records \n",
    "# Only fields requested are the timestamp and dimm_seeing\n",
    "esBody = \"\"\"\n",
    "{\n",
    "    \"_source\": [\"@timestamp\", \"dimm_seeing\"],\n",
    "    \"sort\" : {\n",
    "        \"@timestamp\": {\"order\": \"asc\"}\n",
    "    },\n",
    "    \"query\" :{\n",
    "        \"query_string\" : {\n",
    "            \"query\": @timestamp:[2016-01-01 TO now]\"\n",
    "        }\n",
    "    }\n",
    "    ,\"size\": 10000\n",
    "}\n",
    "\"\"\"\n",
    "uri = 'http://134.171.189.13:9200/asm/_search?scroll=1m'\n",
    "response = requests.post(uri, esBody)\n",
    "results = json.loads(response.text)\n",
    "\n",
    "feList = { 'timestamp': [], 'dimm_seeing': []}\n",
    "keep_fetching = True\n",
    "\n",
    "count = 0\n",
    "while keep_fetching:\n",
    "    i = 0\n",
    "    for r in results['hits']['hits']:\n",
    "        feList['timestamp'].append(dateutil.parser.parse(r['_source']['@timestamp'], ignoretz=True))\n",
    "        feList['dimm_seeing'].append(r['_source']['dimm_seeing'])\n",
    "        i += 1\n",
    "\n",
    "    if i == 10000:\n",
    "        count += i\n",
    "        #print (count, \"mark...\")\n",
    "        uri = 'http://134.171.189.13:9200/_search/scroll'\n",
    "        esBody = \"\"\"\n",
    "        {{\n",
    "            \"scroll\": \"1m\",\n",
    "            \"scroll_id\": \"{}\"\n",
    "        }}\n",
    "        \"\"\".format(results['_scroll_id'])\n",
    "        response = requests.post(uri, esBody)\n",
    "        results = json.loads(response.text)\n",
    "    else:\n",
    "        keep_fetching = False\n",
    "\n",
    "# Create a Dataframe with the data retrieved\n",
    "fe_dt = pd.DataFrame(feList['dimm_seeing'], index=feList['timestamp'], columns=['dimm_seeing'])\n",
    "fe_dt['occurrence'] = np.ones(len(fe_dt), dtype=int)\n",
    "\n",
    "del(feList)\n",
    "del(response)\n",
    "del(results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-27T02:54:03.563741",
     "start_time": "2017-05-27T02:54:03.556486"
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-26T22:49:21.238643",
     "start_time": "2017-05-26T22:49:21.226649"
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-05-26T19:49:28.354869",
     "start_time": "2017-05-26T19:49:28.330058"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "\n"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python [default]",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  },
  "toc": {
   "colors": {
    "hover_highlight": "#DAA520",
    "running_highlight": "#FF0000",
    "selected_highlight": "#FFD700"
   },
   "moveMenuLeft": true,
   "nav_menu": {
    "height": "66px",
    "width": "252px"
   },
   "navigate_menu": true,
   "number_sections": true,
   "sideBar": true,
   "threshold": 4,
   "toc_cell": false,
   "toc_section_display": "block",
   "toc_window_display": false,
   "widenNotebook": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}