simpleCSV.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_rb65f9eeb7cb263c_0000016e5b56e7ca_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 \", project, \":bqkdb.allBQSimpleTypes gs://\", csbucketname, \"/allBQSimpleTypes.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "csfilename: \"gs://\", csbucketname, \"/allBQSimpleTypes.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Copying gs://storagebodon/allBQSimpleTypes.csv...\n",
      "- [1 files][  265.0 B/  265.0 B]                                                \n",
      "Operation completed over 1 objects/265.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": [
    "## Setting types manually\n",
    "\n",
    "* using q time results in losing microsecond precision\n",
    "* postprocessing is needed for types \n",
    "    * BOOL\n",
    "    * TIMESTAMP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "allBQSimpleTypes: (\"sIF**DTP\"; enlist \",\") 0: read0 hsym `$\"/tmp/allBQSimpleTypes.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "\\c 25 125"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "stringCol intCol floatCol boolCol tsCol                            dateCol    timeCol      dtCol                        \n",
       "------------------------------------------------------------------------------------------------------------------------\n",
       "GOOG      42     100.3    \"true\"  \"2019-11-06 01:45:00 UTC\"        2019.11.11 16:32:04.291 2019.11.11D16:32:04.291299000\n",
       "AAPL      200    104.9    \"false\" \"2019-11-11 16:32:04.291299 UTC\" 2019.11.11 16:32:04.291 2019.11.11D16:32:04.291299000\n"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allBQSimpleTypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Converting true/false literals to bool values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "bigQueryToKdbBoolMap: (\"true\";\"false\")!10b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "allBQSimpleTypes_fixed: update bigQueryToKdbBoolMap boolCol from allBQSimpleTypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Converting timestamps by chopping of \" UTC\" postfix."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "`allBQSimpleTypes_fixed\n"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "update \"P\"$-4_/:tsCol from `allBQSimpleTypes_fixed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "stringCol intCol floatCol boolCol tsCol                         dateCol    timeCol      dtCol                        \n",
       "---------------------------------------------------------------------------------------------------------------------\n",
       "GOOG      42     100.3    1       2019.11.06D01:45:00.000000000 2019.11.11 16:32:04.291 2019.11.11D16:32:04.291299000\n",
       "AAPL      200    104.9    0       2019.11.11D16:32:04.291299000 2019.11.11 16:32:04.291 2019.11.11D16:32:04.291299000\n"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allBQSimpleTypes_fixed"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Automatic type conversion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "\\l utils/csvutil.q"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "allBQSimpleTypes_auto: .csv.read hsym `$\"/tmp/allBQSimpleTypes.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "stringCol intCol floatCol boolCol tsCol                            dateCol    timeCol              dtCol                  ..\n",
       "--------------------------------------------------------------------------------------------------------------------------..\n",
       "\"GOOG\"    42     100.3    \"true\"  \"2019-11-06 01:45:00 UTC\"        2019.11.11 0D16:32:04.291299000 2019.11.11D16:32:04.291..\n",
       "\"AAPL\"    200    104.9    \"false\" \"2019-11-11 16:32:04.291299 UTC\" 2019.11.11 0D16:32:04.291299000 2019.11.11D16:32:04.291..\n"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allBQSimpleTypes_auto"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "c        | t f a\n",
       "---------| -----\n",
       "stringCol| C    \n",
       "intCol   | h    \n",
       "floatCol | e    \n",
       "boolCol  | C    \n",
       "tsCol    | C    \n",
       "dateCol  | d    \n",
       "timeCol  | n    \n",
       "dtCol    | p    \n"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meta allBQSimpleTypes_auto"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Automatic type conversion works well for all types except for BOOL and TIMESTAMP."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# From kdb to BigQuery\n",
    "\n",
    "Let us save the fixed kdb table to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "`:/tmp/allBQSimpleTypes_fixed.csv\n"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "save `:/tmp/allBQSimpleTypes_fixed.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Waiting on bqjob_r6e8703bc49513062_0000016e5b56fcd0_1 ... (1s) Current status: DONE   "
     ]
    },
    {
     "data": {
      "text/plain": [
       "\"\"\n",
       "\"\"\n"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "system \"bq load --autodetect bqkdb.allBQSimpleTypes_auto /tmp/allBQSimpleTypes_fixed.csv\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that boolean (b) and timestamp columns (ts, dt) are not casted properly, they are string columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"Table ferenc-world:bqkdb.allBQSimpleTypes_auto\"\n",
       "\"\"\n",
       "\"   Last modified           Schema          Total Rows   Total Bytes   Expiration   Time Partitioning   Clustered Fields  ..\n",
       "\" ----------------- ---------------------- ------------ ------------- ------------ ------------------- ------------------ ..\n",
       "\"  11 Nov 17:41:13   |- stringCol: string   2            216                                                              ..\n",
       "\"                    |- intCol: integer                                                                                   ..\n",
       "\"                    |- floatCol: float                                                                                   ..\n",
       "\"                    |- boolCol: integer                                                                                  ..\n",
       "\"                    |- tsCol: string                                                                                     ..\n",
       "\"                    |- dateCol: date                                                                                     ..\n",
       "\"                    |- timeCol: time                                                                                     ..\n",
       "\"                    |- dtCol: string                                                                                     ..\n",
       "\"\"\n"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "system \"bq show bqkdb.allBQSimpleTypes_auto\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can convert bool and timestamp manually in q"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "allBQSimpleTypes2: update bigQueryToKdbBoolMap?boolCol,\n",
    "    @[; 4 7 10; :; \"-- \"] each string tsCol, \n",
    "    @[; 4 7 10; :; \"-- \"] each string dtCol from allBQSimpleTypes_fixed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "stringCol intCol floatCol boolCol tsCol                           dateCol    timeCol      dtCol                          \n",
       "-------------------------------------------------------------------------------------------------------------------------\n",
       "GOOG      42     100.3    \"true\"  \"2019-11-06 01:45:00.000000000\" 2019.11.11 16:32:04.291 \"2019-11-11 16:32:04.291299000\"\n",
       "AAPL      200    104.9    \"false\" \"2019-11-11 16:32:04.291299000\" 2019.11.11 16:32:04.291 \"2019-11-11 16:32:04.291299000\"\n"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allBQSimpleTypes2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "`:/tmp/allBQSimpleTypes2.csv\n"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Waiting on bqjob_r5bbea663477e0e23_0000016e5b57433b_1 ... (4s) Current status: DONE   "
     ]
    },
    {
     "data": {
      "text/plain": [
       "\"\"\n",
       "\"\"\n"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "save `:/tmp/allBQSimpleTypes2.csv\n",
    "system \"bq load --autodetect bqkdb.allBQSimpleTypes2 /tmp/allBQSimpleTypes2.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"Table ferenc-world:bqkdb.allBQSimpleTypes2\"\n",
       "\"\"\n",
       "\"   Last modified           Schema          Total Rows   Total Bytes   Expiration   Time Partitioning   Clustered Fields  ..\n",
       "\" ----------------- ---------------------- ------------ ------------- ------------ ------------------- ------------------ ..\n",
       "\"  11 Nov 17:41:32   |- stringCol: string   2            110                                                              ..\n",
       "\"                    |- intCol: integer                                                                                   ..\n",
       "\"                    |- floatCol: float                                                                                   ..\n",
       "\"                    |- boolCol: boolean                                                                                  ..\n",
       "\"                    |- tsCol: timestamp                                                                                  ..\n",
       "\"                    |- dateCol: date                                                                                     ..\n",
       "\"                    |- timeCol: time                                                                                     ..\n",
       "\"                    |- dtCol: timestamp                                                                                  ..\n",
       "\"\"\n"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "system \"bq show bqkdb.allBQSimpleTypes2\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean-up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Removing gs://storagebodon/allBQSimpleTypes.csv...\n",
      "/ [1 objects]                                                                   \n",
      "Operation completed over 1 objects.                                              \n"
     ]
    },
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "// Cloud Storage\n",
    "system \"gsutil rm \", csfilename"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "// local files\n",
    "system \"rm /tmp/allBQSimpleTypes.csv\"\n",
    "system \"rm /tmp/allBQSimpleTypes_fixed.csv\"\n",
    "system \"rm /tmp/allBQSimpleTypes2.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "// BigQuery table\n",
    "system \"bq rm -f bqkdb.allBQSimpleTypes_auto\"\n",
    "system \"bq rm -f bqkdb.allBQSimpleTypes2\""
   ]
  }
 ],
 "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
}