content/short_intro_to_hdf5.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# A healthy intro to HDF5\n",
    "\n",
    "Inspired by notes from [Wolfgang Kopp](https://gist.github.com/wkopp/1443c258a95021da2c4e9630da155f13), [Christopher Lovell](https://www.christopherlovell.co.uk/blog/2016/04/27/h5py-intro.html) and [Andrew Colette](https://www.oreilly.com/library/view/python-and-hdf5/9781491944981/)\n",
    "\n",
    "The usecase for Tablite is given as:\n",
    "\n",
    "- Create, read and update larger-than-memory dataset efficiently.\n",
    "- Have clean exit from Python without loss of data (or having to recompute)\n",
    "- Have fast calculations (in memory, on demand)\n",
    "- Be able to append, extend, filter, concatenate, group, etc. using a simple python api.\n",
    "\n",
    "So I need a wrapper around something that gives the convenience of tablite api. What is that something?\n",
    "\n",
    "- SQLite? Works very well as file format, but is quite slow. Even with all the locks turned off. On spinning disks we've seen throughput as low as 27,000 rows per second.\n",
    "- numpy? Is certainly fast enough for most use cases, but requires mmap to get to disk. The stream of bytes is linear, so non-linear read will be slow (proof follows).\n",
    "- HDF5? Is a bit more bloated than mmap'ed numpy, but since the bloat mostly resides on disk it wont matter. The bloat is indices that overcome the random read access issue.\n",
    "- Why not use pyTables? Well I'm no expert, but from the documentation I couldn't find the option to handle data that resides in memory in such a way that pythons `atexit` function to drop particular tables to disk without having to do a lot of magic.\n",
    "\n",
    "So what \"smells\" right? Probably HDF5 (the hint was in the title).\n",
    "\n",
    "- HDF5 file created using `io.bytesIO` for in-memory usage.\n",
    "- HDF5 file on disk for larger-than-memory usage.\n",
    "- A simple wrapper for the pythonic functionality I need that allows me to use `__del__` to save to disk. This is automatically invoked by python `atexit` as a part of the garbage collection.\n",
    "\n",
    "However before I prematurely commit to HDF5, let's do a performance test:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import io\n",
    "import pathlib\n",
    "import numpy as np\n",
    "import h5py\n",
    "import cProfile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "c:\\Data\\github\\github-pages\\content\\tmp\n"
     ]
    }
   ],
   "source": [
    "# let's make a tempfolder to wipe at the end.\n",
    "tmp = pathlib.Path(\"./tmp\")\n",
    "tmp.mkdir(exist_ok=True)\n",
    "tmp.exists()\n",
    "print(str(tmp.absolute()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Ordinary numpy array\n",
    "arr = np.arange(50_000_000, dtype=np.float64)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Memory map\n",
    "nmmarr = np.memmap( shape=arr.shape, filename=tmp /\"benchmark.nmm\", mode='w+', dtype=np.float64)\n",
    "nmmarr[:] = arr[:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# hdf5 file\n",
    "f = h5py.File(tmp / \"benchmark.hdf5\", \"w\", driver='core')\n",
    "d = f.create_dataset(\"mydataset\", arr.shape, dtype=arr.dtype)\n",
    "d[:] = arr[:]\n",
    "f.close()\n",
    "f = h5py.File(tmp / \"benchmark.hdf5\", \"r\", driver='core')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "fh = io.BytesIO()\n",
    "f2 = h5py.File(fh, 'r+')\n",
    "h5io = f2.create_dataset('mydataset', arr.shape, dtype=arr.dtype, data=arr[:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "hdarr = f.get('mydataset')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "pyarr = [v for v in arr]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "def run(x, bs=10000, check_sum=1249999975000000.0):  # test function!\n",
    "    j = 0\n",
    "    for i in range(len(x)//bs):\n",
    "        j+= sum(x[(i*bs):((i+1)*bs)])\n",
    "    assert j == check_sum,j"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def np_run(x, bs=10000, check_sum=1249999975000000.0):  # test function using broadcasting!\n",
    "    j = 0\n",
    "    for i in range(len(x)//bs):\n",
    "        j+= x[(i*bs):((i+1)*bs)].sum()\n",
    "    assert j == check_sum,j"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         50040005 function calls in 29.730 seconds\n",
      "\n",
      "   Ordered by: standard name\n",
      "\n",
      "   ncalls  tottime  percall  cumtime  percall filename:lineno(function)\n",
      "        1    0.014    0.014   29.730   29.730 4288317929.py:1(run)\n",
      "     5000    0.007    0.000    0.023    0.000 <__array_function__ internals>:2(may_share_memory)\n",
      "        1    0.000    0.000   29.730   29.730 <string>:1(<module>)\n",
      "     5000    0.013    0.000    0.039    0.000 memmap.py:288(__array_finalize__)\n",
      " 50010000   19.611    0.000   19.650    0.000 memmap.py:333(__getitem__)\n",
      "     5000    0.002    0.000    0.002    0.000 multiarray.py:1368(may_share_memory)\n",
      "        1    0.000    0.000   29.730   29.730 {built-in method builtins.exec}\n",
      "     5000    0.003    0.000    0.003    0.000 {built-in method builtins.hasattr}\n",
      "        1    0.000    0.000    0.000    0.000 {built-in method builtins.len}\n",
      "     5000   10.067    0.002   29.663    0.006 {built-in method builtins.sum}\n",
      "     5000    0.015    0.000    0.015    0.000 {built-in method numpy.core._multiarray_umath.implement_array_function}\n",
      "        1    0.000    0.000    0.000    0.000 {method 'disable' of '_lsprof.Profiler' objects}\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cProfile.run('run(nmmarr)')  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         5005 function calls in 3.124 seconds\n",
      "\n",
      "   Ordered by: standard name\n",
      "\n",
      "   ncalls  tottime  percall  cumtime  percall filename:lineno(function)\n",
      "        1    0.007    0.007    3.124    3.124 4288317929.py:1(run)\n",
      "        1    0.000    0.000    3.124    3.124 <string>:1(<module>)\n",
      "        1    0.000    0.000    3.124    3.124 {built-in method builtins.exec}\n",
      "        1    0.000    0.000    0.000    0.000 {built-in method builtins.len}\n",
      "     5000    3.117    0.001    3.117    0.001 {built-in method builtins.sum}\n",
      "        1    0.000    0.000    0.000    0.000 {method 'disable' of '_lsprof.Profiler' objects}\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cProfile.run('run(arr)')  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         15005 function calls in 0.117 seconds\n",
      "\n",
      "   Ordered by: standard name\n",
      "\n",
      "   ncalls  tottime  percall  cumtime  percall filename:lineno(function)\n",
      "        1    0.005    0.005    0.117    0.117 957082684.py:1(np_run)\n",
      "        1    0.000    0.000    0.117    0.117 <string>:1(<module>)\n",
      "     5000    0.002    0.000    0.110    0.000 _methods.py:46(_sum)\n",
      "        1    0.000    0.000    0.117    0.117 {built-in method builtins.exec}\n",
      "        1    0.000    0.000    0.000    0.000 {built-in method builtins.len}\n",
      "        1    0.000    0.000    0.000    0.000 {method 'disable' of '_lsprof.Profiler' objects}\n",
      "     5000    0.108    0.000    0.108    0.000 {method 'reduce' of 'numpy.ufunc' objects}\n",
      "     5000    0.003    0.000    0.112    0.000 {method 'sum' of 'numpy.ndarray' objects}\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cProfile.run('np_run(arr)')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         30026 function calls (30024 primitive calls) in 3.274 seconds\n",
      "\n",
      "   Ordered by: standard name\n",
      "\n",
      "   ncalls  tottime  percall  cumtime  percall filename:lineno(function)\n",
      "        1    0.017    0.017    3.274    3.274 4288317929.py:1(run)\n",
      "        1    0.000    0.000    3.274    3.274 <string>:1(<module>)\n",
      "        4    0.000    0.000    0.000    0.000 base.py:305(id)\n",
      "        1    0.000    0.000    0.000    0.000 dataset.py:409(shape)\n",
      "     5000    0.002    0.000    0.002    0.000 dataset.py:469(_fast_reader)\n",
      "        1    0.000    0.000    0.000    0.000 dataset.py:572(_extent_type)\n",
      "        1    0.000    0.000    0.000    0.000 dataset.py:631(__len__)\n",
      "        1    0.000    0.000    0.000    0.000 dataset.py:642(len)\n",
      "        1    0.000    0.000    0.000    0.000 dataset.py:683(_fast_read_ok)\n",
      "     5000    0.008    0.000    0.092    0.000 dataset.py:691(__getitem__)\n",
      "      2/1    0.000    0.000    0.000    0.000 functools.py:973(__get__)\n",
      "        1    0.000    0.000    3.274    3.274 {built-in method builtins.exec}\n",
      "     5000    0.002    0.000    0.002    0.000 {built-in method builtins.getattr}\n",
      "     5001    0.001    0.000    0.001    0.000 {built-in method builtins.isinstance}\n",
      "      2/1    0.000    0.000    0.000    0.000 {built-in method builtins.len}\n",
      "     5000    3.165    0.001    3.165    0.001 {built-in method builtins.sum}\n",
      "        2    0.000    0.000    0.000    0.000 {method '__exit__' of '_thread.RLock' objects}\n",
      "        2    0.000    0.000    0.000    0.000 {method '__exit__' of 'h5py._objects.FastRLock' objects}\n",
      "        1    0.000    0.000    0.000    0.000 {method 'disable' of '_lsprof.Profiler' objects}\n",
      "        4    0.000    0.000    0.000    0.000 {method 'get' of 'dict' objects}\n",
      "     5000    0.079    0.000    0.079    0.000 {method 'read' of 'h5py._selector.Reader' objects}\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cProfile.run('run(hdarr)') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         35025 function calls (35023 primitive calls) in 3.367 seconds\n",
      "\n",
      "   Ordered by: standard name\n",
      "\n",
      "   ncalls  tottime  percall  cumtime  percall filename:lineno(function)\n",
      "        1    0.017    0.017    3.367    3.367 4288317929.py:1(run)\n",
      "        1    0.000    0.000    3.367    3.367 <string>:1(<module>)\n",
      "     5003    0.001    0.000    0.001    0.000 base.py:305(id)\n",
      "        1    0.000    0.000    0.000    0.000 dataset.py:409(shape)\n",
      "     5000    0.099    0.000    0.100    0.000 dataset.py:469(_fast_reader)\n",
      "        1    0.000    0.000    0.000    0.000 dataset.py:572(_extent_type)\n",
      "        1    0.000    0.000    0.000    0.000 dataset.py:631(__len__)\n",
      "        1    0.000    0.000    0.000    0.000 dataset.py:642(len)\n",
      "        1    0.000    0.000    0.000    0.000 dataset.py:683(_fast_read_ok)\n",
      "     5000    0.022    0.000    0.217    0.000 dataset.py:691(__getitem__)\n",
      "      2/1    0.000    0.000    0.000    0.000 functools.py:973(__get__)\n",
      "        1    0.000    0.000    3.367    3.367 {built-in method builtins.exec}\n",
      "     5000    0.002    0.000    0.002    0.000 {built-in method builtins.getattr}\n",
      "     5001    0.001    0.000    0.001    0.000 {built-in method builtins.isinstance}\n",
      "      2/1    0.000    0.000    0.000    0.000 {built-in method builtins.len}\n",
      "     5000    3.132    0.001    3.132    0.001 {built-in method builtins.sum}\n",
      "        2    0.000    0.000    0.000    0.000 {method '__exit__' of '_thread.RLock' objects}\n",
      "        2    0.000    0.000    0.000    0.000 {method '__exit__' of 'h5py._objects.FastRLock' objects}\n",
      "        1    0.000    0.000    0.000    0.000 {method 'disable' of '_lsprof.Profiler' objects}\n",
      "        4    0.000    0.000    0.000    0.000 {method 'get' of 'dict' objects}\n",
      "     5000    0.092    0.000    0.092    0.000 {method 'read' of 'h5py._selector.Reader' objects}\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cProfile.run('run(h5io)')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         5005 function calls in 2.192 seconds\n",
      "\n",
      "   Ordered by: standard name\n",
      "\n",
      "   ncalls  tottime  percall  cumtime  percall filename:lineno(function)\n",
      "        1    0.374    0.374    2.192    2.192 4288317929.py:1(run)\n",
      "        1    0.000    0.000    2.192    2.192 <string>:1(<module>)\n",
      "        1    0.000    0.000    2.192    2.192 {built-in method builtins.exec}\n",
      "        1    0.000    0.000    0.000    0.000 {built-in method builtins.len}\n",
      "     5000    1.818    0.000    1.818    0.000 {built-in method builtins.sum}\n",
      "        1    0.000    0.000    0.000    0.000 {method 'disable' of '_lsprof.Profiler' objects}\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cProfile.run('run(pyarr)') # pure python function for comparison. As this is all in memory. This won't scale."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### In summary\n",
    "\n",
    "- nmarr = 29.730 seconds (Worst, numpy array memory mapped)\n",
    "- h5io = 3.367 seconds (python of H5 w. bytesIO)\n",
    "- hdarr = 3.274 seconds (python on H5)\n",
    "- arr = 3.124 seconds (python on np array.)\n",
    "- pyarr = 2.192 seconds (pure python)\n",
    "- arr (np_run) = 0.117 seconds (Best, optimised numpy)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "f.close()\n",
    "del nmmarr"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Performance says it's HDF5. Can we handle all datatypes?\n",
    "\n",
    "With the performance question out of the way, the next question is whether hdf5 can handle all the dirty data cases that tablite copes with.\n",
    "\n",
    "They are:\n",
    "\n",
    "- Booleans\n",
    "- Integers\n",
    "- Floats\n",
    "- Nones\n",
    "- Strings\n",
    "- Datetimes\n",
    "- Times\n",
    "- Dates\n",
    "- A mixture of them all.\n",
    "\n",
    "It is of course best for all the cases where hdf5 can handle the datatype natively.\n",
    "The [h5py FAQ](https://docs.h5py.org/en/latest/faq.html) answers that well: Integers, Floats, Strings (fixed length, variable length), Booleans are handled natively.\n",
    "\n",
    "As strings are handled as bytes, the unicode encoding needs to included. This suits me well as tablite often is used to process data with various UTF-8 dialects.\n",
    "\n",
    "> datetime64 and timedelta64, can optionally be stored in HDF5 opaque data using opaque_dtype(). h5py will read this data back with the same dtype, but non-python will probably not understand the datatype.\n",
    "\n",
    "By sticking to ISO8601 format we can store the Time as bytes and convert it on demand. Likewise for Dates as we don't want to impose a false sense of time onto dates. By using [numbas `jit`](https://numba.pydata.org/) compiler this can probably be very fast, though I'm not fond of the dependency.\n",
    "\n",
    "Finally: The data structure: HDF5 supports that each column is its own dataset inside the file. This suits me very well as all I have to keep track of is to assure that the columns won't be distorted. Renaming column is solved by manipulating the HDF5 references:\n",
    "\n",
    "> `>>> myfile[\"two\"] = myfile[\"one\"]`\n",
    "> `>>> del myfile[\"one\"]`\n",
    "> [Andrew](https://groups.google.com/g/h5py/c/rGqWfX-H4No)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "The [modes](https://docs.h5py.org/en/stable/high/file.html?highlight=w%20#opening-creating-files) of the `h5py.File` creation are :\n",
    "\n",
    "- `x` tries to create file, but will fail if the file exists.\n",
    "- `a` tries read/write if exists, otherwise creates the file.\n",
    "- `w` create file, truncate if it exists\n",
    "\n",
    "\n",
    "\n",
    "I choose to create it and fail if it already exists. For tablite I can raise a IOError and ask the user to load `Table.from_file(....h5)`; or I can allow the user to create the table as Table(use_disk=`table.h5`) as \"load if exists, otherwise create.\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "hf = h5py.File(tmp/'table1.h5', 'x')  # Create file, truncate if exists"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "I now want to create a dataset for a column of data and set the datasets metadata as the python users datatype."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "dset = hf.create_dataset('column1', data=list(range(10)))\n",
    "dset.attrs['datatype'] = 'int'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "I could use compression when creating the dataset, as it adds delay to read/writes that others are linear, I question whether it's beneficial. For LZF compression on column1 the create_dataset call is extended with `compression=\"lzf\"`:\n",
    "\n",
    "`hf.create_dataset('column1', data=list(range(10)), compression=\"lzf\",)`\n",
    "\n",
    "Andrew Collette recommends to measure the effect as the benefit of compression depends on the dataset:\n",
    "\n",
    "| type               | compress time | decompress time | compression |\n",
    "|:-------------------|--------------:|----------------:|------------:|\n",
    "| trivial data       |       18.6 ms |         17.8 ms |      96.65% |\n",
    "| sine wave w. noise |       65.5 ms |         24.4 ms |      15.53% |\n",
    "| random data        |       67.8 ms |         24.8 ms |       8.94% |\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "dset = hf.create_dataset('column2', data=[str(i) for i  in range(10)])\n",
    "dset.attrs['datatype'] = 'str'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "hf.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "As I've created the dataset and closed I can now reopen and inspect the file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "hf = h5py.File(tmp/'table1.h5', 'r')  # Readonly, file must exist (default)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<KeysViewHDF5 ['column1', 'column2']>\n",
      "column1 {'datatype': 'int'} [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]\n",
      "column2 {'datatype': 'str'} [b'0', b'1', b'2', b'3', b'4', b'5', b'6', b'7', b'8', b'9']\n"
     ]
    }
   ],
   "source": [
    "print(hf.keys())\n",
    "for name, dset in hf.items():\n",
    "    print(name, {k:v for k,v in dset.attrs.items()}, list(dset))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "As you can see above, `column2`s strings are encoded to bytes. To decode I'd need:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "str ['0', '1', '2', '3', '4', '5', '6', '7', '8', '9']\n"
     ]
    }
   ],
   "source": [
    "c2 = hf.get('column2')\n",
    "print(c2.attrs['datatype'], [i.decode(\"utf-8\") for i in c2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "And finally - always remember to close the file handle."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "hf.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Time to append some data..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "hf = h5py.File(tmp/'table1.h5', 'r+')  #Read/write, file must exist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "dset = hf.create_dataset('column3', data=[float(i*10) for i  in range(10)])\n",
    "dset.attrs['datatype'] = 'float'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "from datetime import datetime, timedelta\n",
    "now = datetime.now()\n",
    "data = [(now.replace(microsecond=0) + timedelta(days=i)).isoformat() for i in range(10)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2022-03-12T10:18:59',\n",
       " '2022-03-13T10:18:59',\n",
       " '2022-03-14T10:18:59',\n",
       " '2022-03-15T10:18:59',\n",
       " '2022-03-16T10:18:59',\n",
       " '2022-03-17T10:18:59',\n",
       " '2022-03-18T10:18:59',\n",
       " '2022-03-19T10:18:59',\n",
       " '2022-03-20T10:18:59',\n",
       " '2022-03-21T10:18:59']"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "dset = hf.create_dataset('column4', data=data)\n",
    "dset.attrs['datatype'] = 'datetime'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "At this point I now have 4 columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<KeysViewHDF5 ['column1', 'column2', 'column3', 'column4']>"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hf.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "column1 {'datatype': 'int'} [0, 1, 2, 3, 4]\n",
      "column2 {'datatype': 'str'} [b'0', b'1', b'2', b'3', b'4']\n",
      "column3 {'datatype': 'float'} [0.0, 10.0, 20.0, 30.0, 40.0]\n",
      "column4 {'datatype': 'datetime'} [b'2022-03-12T10:18:59', b'2022-03-13T10:18:59', b'2022-03-14T10:18:59', b'2022-03-15T10:18:59', b'2022-03-16T10:18:59']\n"
     ]
    }
   ],
   "source": [
    "for k,v in hf.items():\n",
    "    print(k,{k:v for k,v in v.attrs.items()}, list(v[:5]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "To view this as rows I can gather and rotate a sensible small sample:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(0, b'0', 0.0, b'2022-03-12T10:18:59')\n",
      "(1, b'1', 10.0, b'2022-03-13T10:18:59')\n",
      "(2, b'2', 20.0, b'2022-03-14T10:18:59')\n",
      "(3, b'3', 30.0, b'2022-03-15T10:18:59')\n",
      "(4, b'4', 40.0, b'2022-03-16T10:18:59')\n"
     ]
    }
   ],
   "source": [
    "L = []\n",
    "for k, v in hf.items():\n",
    "    L.append(v[:5])\n",
    "\n",
    "def rotate(L):\n",
    "    for row_ix,_ in enumerate(L[0]):\n",
    "        yield tuple(L[col_ix][row_ix] for col_ix,_ in enumerate(L))\n",
    "\n",
    "for row in rotate(L):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Finally - always remember:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "hf.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "for file in tmp.iterdir():\n",
    "    file.unlink()\n",
    "tmp.rmdir()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# But what about datatypes?\n",
    "\n",
    "Since numpy 1.2.0 the [datatypes](https://numpy.org/devdocs/release/1.20.0-notes.html#deprecations) are inferred from pythons' type system.\n",
    "\n",
    "So numpy detects the type automatically **if** the datatypes are **homogenous**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'numpy.ndarray'> float64 [12345678.8765432]\n",
      "<class 'numpy.ndarray'> int32 [234565432]\n",
      "<class 'numpy.ndarray'> bool [ True]\n",
      "<class 'numpy.ndarray'> <U4 ['Fish']\n"
     ]
    }
   ],
   "source": [
    "L = [ 12345678.8765432, 234565432, True, \"Fish\"]\n",
    "\n",
    "for i in L:\n",
    "    npxed = np.array([i])\n",
    "    print(type(npxed), npxed.dtype, npxed)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "(!)Be wary though, the int32 is limited and overflow errors are not uncommon\n",
    "\n",
    "However if the datatypes are a **heterogenous**, numpy uses bytes for storage:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'numpy.ndarray'> object <-- numpy \"object\" type\n",
      "[1 1.23 'fish' False None]\n",
      "<class 'int'> 1\n",
      "<class 'float'> 1.23\n",
      "<class 'str'> fish\n",
      "<class 'bool'> False\n",
      "<class 'NoneType'> None\n"
     ]
    }
   ],
   "source": [
    "nn =np.array([1,1.23, \"fish\", False, None])\n",
    "print(type(nn), nn.dtype, \"<-- numpy \\\"object\\\" type\")\n",
    "print(nn)\n",
    "for v in nn:\n",
    "    print(type(v),v)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "So how does HDF5 react to that?\n",
    "\n",
    "I'm first going to create a HDF5 file in memory, and then poke it with some mixed datatypes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import io\n",
    "filehandle = io.BytesIO()\n",
    "h5file = h5py.File(filehandle, \"r+\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Object dtype dtype('O') has no native HDF5 equivalent\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    h5file.create_dataset('column1', data=nn)\n",
    "except TypeError as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "So HDF5 does not have an equivalent datatype. What then? Can it at least handle `None`s?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Object dtype dtype('O') has no native HDF5 equivalent\n"
     ]
    }
   ],
   "source": [
    "data=[1,2,3,None,5,6]\n",
    "try:\n",
    "    h5file.create_dataset('column1', data=[1,2,3,None,5,6])\n",
    "except TypeError as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Nope. `None`s aren't allowed either. So the fallback option is to turn this mixed pot into bytes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "datatype was non HDF5, so utf-8 encoded bytes are used\n"
     ]
    }
   ],
   "source": [
    "from collections import defaultdict\n",
    "data=[1,2,3,None,5,6]*50_000  # 300_000 values.\n",
    "try:\n",
    "    dset = h5file.create_dataset('column1', data=data)\n",
    "except TypeError as e:\n",
    "    dtypes = defaultdict(int)\n",
    "    for v in data:\n",
    "        dtypes[type(v).__name__] += 1\n",
    "    print(\"datatype was non HDF5, so utf-8 encoded bytes are used\")\n",
    "    dset = h5file.create_dataset('column1', data=[str(v) for v in data])\n",
    "    dset.attrs['datatype'] = str(dtypes)\n",
    "except Exception:\n",
    "    raise"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "column1 {'datatype': \"defaultdict(<class 'int'>, {'int': 250000, 'NoneType': 50000})\"} [b'1', b'2', b'3', b'None', b'5']\n"
     ]
    }
   ],
   "source": [
    "for k,v in h5file.items():\n",
    "    print(k,{k:v for k,v in v.attrs.items()}, list(v[:5]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "With this information I can apply tablite's type detection and use the histogram to guess the datatype."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "h5file.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}