2.2.1_LEC_clickhouse_and_Docker_desktop.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Где собирать логи\n",
    "1. Ставим Docker desktop\n",
    "2. Устанавливаем [образ](https://hub.docker.com/r/yandex/clickhouse-server/) Clickhouse\n",
    "```\n",
    "!docker run -d -p 0.0.0.0:8123:8123 --volume=/path/to/some/folder/on/disk/some_clickhouse_database:/var/lib/clickhouse --name some-clickhouse-server --ulimit nofile=262144:262144 yandex/clickhouse-server\n",
    "```\n",
    "\n",
    "Опция -p 0.0.0.0:8123:8123 открывает доступ к контейнеру по порту 8123 (иногда сразу его нет).\n",
    "\n",
    "При повторной установке в случае ошибки вида\n",
    "```\n",
    "docker: Error response from daemon: Conflict. The container name \"/some-clickhouse-server\" is already in use by container \"34899ff1c1d78111048b762fb730963adac0b90eedb9751f4c5d62aa4d90c589\". You have to remove (or rename) that container to be able to reuse that name.\n",
    "```\n",
    "удалите контейнер командой (только замените ID контейнера на свой)\n",
    "```\n",
    "!docker rm 34899ff1c1d78111048b762fb730963adac0b90eedb9751f4c5d62aa4d90c589\n",
    "```\n",
    "\n",
    "Как узнать ID_контейнера\n",
    "```\n",
    "!docker ps\n",
    "```\n",
    "\n",
    "Как зайти в контейнер (лучше делать в командной строке):\n",
    "```\n",
    "docker exec -it ID_контейнера bash\n",
    "```\n",
    "\n",
    "Открыть clickhouse-client:\n",
    "```\n",
    "docker run -it --rm --link some-clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-server\n",
    "```\n",
    "\n",
    "3. Проверьте наличие доступа к clickhouse в контейнере в браузере, открыв ссылку [localhost:8123](http://localhost:8123), должны увидеть Ok.\n",
    "\n",
    "4. Берем открытые данные [Метрики](https://clickhouse.tech/docs/ru/getting-started/example-datasets/metrica/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!docker pull postgres"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "from io import StringIO\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "HOST = 'http://localhost:8123'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clickhouse_data(query, host=HOST, timeout=60, columns=None):\n",
    "    r = requests.post(host, params = {'timeout_before_checking_execution_speed': 120, 'max_execution_time': 6000}\n",
    "                          , timeout = timeout, data = query)\n",
    "    if r.status_code == 200:\n",
    "        return r.text\n",
    "    else:\n",
    "        print('Что-то пошло не так')\n",
    "        raise ValueError(r.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'40065653\\n'"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clickhouse_data('select count(*) from datasets.hits_v1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'��\\t27311685\\nTp\\t9652403\\nTi\\t1602144\\nI7\\t807476\\nIP\\t145466\\n'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "text = clickhouse_data('select BrowserCountry, count(*) as cnt from datasets.hits_v1 group by BrowserCountry order by cnt desc limit 5')\n",
    "text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>BrowserCountry</th>\n",
       "      <th>cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>��</td>\n",
       "      <td>27311685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tp</td>\n",
       "      <td>9652403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ti</td>\n",
       "      <td>1602144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>I7</td>\n",
       "      <td>807476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>IP</td>\n",
       "      <td>145466</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  BrowserCountry       cnt\n",
       "0             ��  27311685\n",
       "1             Tp   9652403\n",
       "2             Ti   1602144\n",
       "3             I7    807476\n",
       "4             IP    145466"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(StringIO(text), sep='\\t', names=['BrowserCountry', 'cnt'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3.72 ms, sys: 1.34 ms, total: 5.07 ms\n",
      "Wall time: 87.4 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "text = clickhouse_data('select EventDate, count(*) from datasets.hits_v1 group by EventDate order by EventDate')\n",
    "df = pd.read_csv(StringIO(text), sep='\\t', names=['EventDate', 'hits'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EventDate</th>\n",
       "      <th>hits</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014-03-17</td>\n",
       "      <td>6319544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014-03-18</td>\n",
       "      <td>6194055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2014-03-19</td>\n",
       "      <td>6306972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014-03-20</td>\n",
       "      <td>6079328</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014-03-21</td>\n",
       "      <td>5749616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2014-03-22</td>\n",
       "      <td>4684006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2014-03-23</td>\n",
       "      <td>4732132</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    EventDate     hits\n",
       "0  2014-03-17  6319544\n",
       "1  2014-03-18  6194055\n",
       "2  2014-03-19  6306972\n",
       "3  2014-03-20  6079328\n",
       "4  2014-03-21  5749616\n",
       "5  2014-03-22  4684006\n",
       "6  2014-03-23  4732132"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 4.13 ms, sys: 2.02 ms, total: 6.15 ms\n",
      "Wall time: 257 ms\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EventDate</th>\n",
       "      <th>unique_users_exact</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014-03-17</td>\n",
       "      <td>36613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014-03-18</td>\n",
       "      <td>36531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2014-03-19</td>\n",
       "      <td>36940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014-03-20</td>\n",
       "      <td>36462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014-03-21</td>\n",
       "      <td>35447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2014-03-22</td>\n",
       "      <td>31555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2014-03-23</td>\n",
       "      <td>31200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    EventDate  unique_users_exact\n",
       "0  2014-03-17               36613\n",
       "1  2014-03-18               36531\n",
       "2  2014-03-19               36940\n",
       "3  2014-03-20               36462\n",
       "4  2014-03-21               35447\n",
       "5  2014-03-22               31555\n",
       "6  2014-03-23               31200"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "text = clickhouse_data('select EventDate, uniqExact(UserID) from datasets.hits_v1 group by EventDate order by EventDate')\n",
    "df = pd.read_csv(StringIO(text), sep='\\t', names=['EventDate', 'unique_users_exact'])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# pandahouse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 5.95 ms, sys: 2.93 ms, total: 8.89 ms\n",
      "Wall time: 419 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "text = clickhouse_data('select TraficSourceID, EventDate, uniqExact(UserID) from datasets.hits_v1 group by TraficSourceID, EventDate')\n",
    "df = pd.read_csv(StringIO(text), sep='\\t', names=['TraficSourceID', 'EventDate', 'unique_users_exact'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TraficSourceID</th>\n",
       "      <th>EventDate</th>\n",
       "      <th>unique_users_exact</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>2014-03-21</td>\n",
       "      <td>528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>2014-03-20</td>\n",
       "      <td>1294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2014-03-18</td>\n",
       "      <td>1606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-1</td>\n",
       "      <td>2014-03-21</td>\n",
       "      <td>21386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10</td>\n",
       "      <td>2014-03-21</td>\n",
       "      <td>3824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>-1</td>\n",
       "      <td>2014-03-23</td>\n",
       "      <td>18574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>10</td>\n",
       "      <td>2014-03-23</td>\n",
       "      <td>3434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>6</td>\n",
       "      <td>2014-03-17</td>\n",
       "      <td>1410</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>0</td>\n",
       "      <td>2014-03-22</td>\n",
       "      <td>26854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>2</td>\n",
       "      <td>2014-03-19</td>\n",
       "      <td>1705</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>70 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    TraficSourceID   EventDate  unique_users_exact\n",
       "0                4  2014-03-21                 528\n",
       "1                6  2014-03-20                1294\n",
       "2                2  2014-03-18                1606\n",
       "3               -1  2014-03-21               21386\n",
       "4               10  2014-03-21                3824\n",
       "..             ...         ...                 ...\n",
       "65              -1  2014-03-23               18574\n",
       "66              10  2014-03-23                3434\n",
       "67               6  2014-03-17                1410\n",
       "68               0  2014-03-22               26854\n",
       "69               2  2014-03-19                1705\n",
       "\n",
       "[70 rows x 3 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}