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_r6df8bcd852d8a0b7_0000016e462d269e_1 ... (1s) 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][  229.0 B/  229.0 B]                                                \n",
      "Operation completed over 1 objects/229.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": [
       "s    int f     b       ts                               date       time         dt                           \n",
       "-------------------------------------------------------------------------------------------------------------\n",
       "AAPL 200 104.9 \"false\" \"2019-11-07 14:02:43.013946 UTC\" 2019.11.07 14:02:43.013 2019.11.07D14:02:43.013946000\n",
       "GOOG 42  100.3 \"true\"  \"2019-11-06 01:45:00 UTC\"        2019.11.07 14:02:43.013 2019.11.07D14:02:43.013946000\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 b 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_/:ts from `allBQSimpleTypes_fixed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "s    int f     b ts                            date       time         dt                           \n",
       "----------------------------------------------------------------------------------------------------\n",
       "AAPL 200 104.9 0 2019.11.07D14:02:43.013946000 2019.11.07 14:02:43.013 2019.11.07D14:02:43.013946000\n",
       "GOOG 42  100.3 1 2019.11.06D01:45:00.000000000 2019.11.07 14:02:43.013 2019.11.07D14:02:43.013946000\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": [
       "s      int f     b       ts                               date       time                 dt                           \n",
       "-----------------------------------------------------------------------------------------------------------------------\n",
       "\"AAPL\" 200 104.9 \"false\" \"2019-11-07 14:02:43.013946 UTC\" 2019.11.07 0D14:02:43.013946000 2019.11.07D14:02:43.013946000\n",
       "\"GOOG\" 42  100.3 \"true\"  \"2019-11-06 01:45:00 UTC\"        2019.11.07 0D14:02:43.013946000 2019.11.07D14:02:43.013946000\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",
       "s   | C    \n",
       "int | h    \n",
       "f   | e    \n",
       "b   | C    \n",
       "ts  | C    \n",
       "date| d    \n",
       "time| n    \n",
       "dt  | 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_r7c47ac4145124624_0000016e462d6953_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   Labe..\n",
       "\" ----------------- ----------------- ------------ ------------- ------------ ------------------- ------------------ -----..\n",
       "\"  07 Nov 15:03:36   |- s: string      2            216                                                                   ..\n",
       "\"                    |- int: integer                                                                                      ..\n",
       "\"                    |- f: float                                                                                          ..\n",
       "\"                    |- b: integer                                                                                        ..\n",
       "\"                    |- ts: string                                                                                        ..\n",
       "\"                    |- date: date                                                                                        ..\n",
       "\"                    |- time: time                                                                                        ..\n",
       "\"                    |- dt: 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": [
    "kdbToBigQueryBoolMap: value[bigQueryToKdbBoolMap]!key bigQueryToKdbBoolMap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "allBQSimpleTypes2: update kdbToBigQueryBoolMap b,\n",
    "    @[; 4 7 10; :; \"-- \"] each string ts, \n",
    "    @[; 4 7 10; :; \"-- \"] each string dt from allBQSimpleTypes_fixed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "s    int f     b       ts                              date       time         dt                             \n",
       "--------------------------------------------------------------------------------------------------------------\n",
       "AAPL 200 104.9 \"false\" \"2019-11-07 14:02:43.013946000\" 2019.11.07 14:02:43.013 \"2019-11-07 14:02:43.013946000\"\n",
       "GOOG 42  100.3 \"true\"  \"2019-11-06 01:45:00.000000000\" 2019.11.07 14:02:43.013 \"2019-11-07 14:02:43.013946000\"\n"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allBQSimpleTypes2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "`:/tmp/allBQSimpleTypes2.csv\n"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Waiting on bqjob_r602163027d409e28_0000016e462d8622_1 ... (1s) Current status: DONE   "
     ]
    },
    {
     "data": {
      "text/plain": [
       "\"\"\n",
       "\"\"\n"
      ]
     },
     "execution_count": 22,
     "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": 23,
   "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   Lab..\n",
       "\" ----------------- ------------------ ------------ ------------- ------------ ------------------- ------------------ ----..\n",
       "\"  07 Nov 15:03:44   |- s: string       2            110                                                                  ..\n",
       "\"                    |- int: integer                                                                                      ..\n",
       "\"                    |- f: float                                                                                          ..\n",
       "\"                    |- b: boolean                                                                                        ..\n",
       "\"                    |- ts: timestamp                                                                                     ..\n",
       "\"                    |- date: date                                                                                        ..\n",
       "\"                    |- time: time                                                                                        ..\n",
       "\"                    |- dt: timestamp                                                                                     ..\n",
       "\"\"\n"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "system \"bq show bqkdb.allBQSimpleTypes2\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Clean-up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "// Cloud Storage\n",
    "system \"gsutil rm \", csfilename"
   ]
  },
  {
   "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"
    },
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 25,
     "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": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": []
     },
     "execution_count": 26,
     "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
}