simpleJSON.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "project: getenv `project_id\n",
    "csbucketname: getenv `csbucketname"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# From BigQuery to kdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Waiting on bqjob_r449c22e13fbc4e13_0000016e4638ce7d_1 ... (0s) Current status: DONE   "
     ]
    },
    {
     "data": {
      "text/plain": [
       "\"\"\n"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "// extract from BigQuery to Cloud Storage\n",
    "system \"bq extract --destination_format NEWLINE_DELIMITED_JSON \", project, \":bqkdb.allBQSimpleTypes gs://\", csbucketname, \"/allBQSimpleTypes.json\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "csfilename: \"gs://\", csbucketname, \"/allBQSimpleTypes.json\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Copying gs://storagebodon/allBQSimpleTypes.json...\n",
      "- [1 files][  387.0 B/  387.0 B]                                                \n",
      "Operation completed over 1 objects/387.0 B.                                      \n"
     ]
    },
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "// Copy from Cloud Storage to local box\n",
    "system \"gsutil cp \", csfilename, \" /tmp/\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Manual type conversion\n",
    "* string, boolean and float types are properly handled\n",
    "* other types need to be casted manually\n",
    "* timestamps needs minor conversion, chopping timezone information off\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "decode: {[fM; j] \n",
    "  k: .j.k j; \n",
    "  key[k]!fM[key k]@'value k}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "formatterMap: (enlist `)!enlist (::)   // default formatter: leave unchanged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "formatterMap[`intCol]: \"I\"$\n",
    "formatterMap[`tsCol]: \t{\"P\"$-4 _ x}\n",
    "formatterMap[`dateCol]: \"D\"$\n",
    "formatterMap[`timeCol]: \"T\"$\n",
    "formatterMap[`dtCol]: \"P\"$"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "t: formatterMap decode/: read0 hsym `$\"/tmp/allBQSimpleTypes.json\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "stringCol intCol floatCol boolCol tsCol                         dateCol    ti..\n",
       "-----------------------------------------------------------------------------..\n",
       "\"GOOG\"    42     100.3    1       2019.11.06D01:45:00.000000000 2019.11.11 16..\n",
       "\"AAPL\"    200    104.9    0       2019.11.11D16:32:04.291299000 2019.11.11 16..\n"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "c        | t f a\n",
       "---------| -----\n",
       "stringCol| C    \n",
       "intCol   | i    \n",
       "floatCol | f    \n",
       "boolCol  | b    \n",
       "tsCol    | p    \n",
       "dateCol  | d    \n",
       "timeCol  | t    \n",
       "dtCol    | p    \n"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meta t"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# From kdb to BigQuery\n",
    "\n",
    "Let us save the fixed kdb table to JSON"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "`:/tmp/t.json\n"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "save `:/tmp/t.json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Waiting on bqjob_r1e8487c369c5982a_0000016e5c3673e8_1 ... (3s) Current status: DONE   "
     ]
    },
    {
     "data": {
      "text/plain": [
       "\"\"\n",
       "\"\"\n"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "system \"bq load --autodetect --source_format NEWLINE_DELIMITED_JSON bqkdb.allBQSimpleTypes_json /tmp/t.json\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that the **column orders do not match**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"Table ferenc-world:bqkdb.allBQSimpleTypes_json\"\n",
       "\"\"\n",
       "\"   Last modified           Schema          Total Rows   Total Bytes   Expira..\n",
       "\" ----------------- ---------------------- ------------ ------------- -------..\n",
       "\"  11 Nov 21:45:18   |- dtCol: timestamp    2            110                 ..\n",
       "\"                    |- timeCol: time                                        ..\n",
       "\"                    |- dateCol: date                                        ..\n",
       "\"                    |- tsCol: timestamp                                     ..\n",
       "\"                    |- floatCol: float                                      ..\n",
       "\"                    |- boolCol: boolean                                     ..\n",
       "\"                    |- intCol: integer                                      ..\n",
       "\"                    |- stringCol: string                                    ..\n",
       "\"\"\n"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "system \"bq show bqkdb.allBQSimpleTypes_json\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleanup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Removing gs://storagebodon/allBQSimpleTypes.json...\n",
      "/ [1 objects]                                                                   \n",
      "Operation completed over 1 objects.                                              \n"
     ]
    },
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "// Cloud Storage\n",
    "system \"gsutil rm \", csfilename"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "// local files\n",
    "system \"rm /tmp/t.json\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "// BigQuery table\n",
    "system \"bq rm -f bqkdb.allBQSimpleTypes_json\""
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Q (kdb+)",
   "language": "q",
   "name": "qpk"
  },
  "language_info": {
   "file_extension": ".q",
   "mimetype": "text/x-q",
   "name": "q",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}