{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "faedc0fb-b099-4064-9ad6-736f6292fe6a",
   "metadata": {},
   "source": [
    "# Amazon FinSpace Managed kdb Insights: Query welcomedb\n",
    "\n",
    "Query the tables in welcomedb from the cluster hdb-cluster-welcomedb created in welcome."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb0da5aa-f799-4c07-95ed-cbcd52f89275",
   "metadata": {},
   "source": [
    "# Connect to the Cluster, List Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "76dec0d7-66e5-4d8a-84e7-01eddabae1e0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "\\c 30 150\n",
    "\n",
    "/ open a connection to the managed KX Cluster\n",
    "\n",
    "/ Cluster: cluster_welcomedb\n",
    "hdb_conn:\":tcps://vpce-0188812954e611136-vq08bd72.vpce-svc-03bd75084b0024f2d.us-east-1.vpce.amazonaws.com:443:bob:Host=vpce-0188812954e611136-vq08bd72.vpce-svc-03bd75084b0024f2d.us-east-1.vpce.amazonaws.com&Port=5000&User=bob&Action=finspace%3AConnectKxCluster&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEG8aCXVzLWVhc3QtMSJHMEUCIEXcxWp3FowhKu3apm7QPDxz6cMl%2BS6eP1D91XpFzM0tAiEA%2BUVIsaHMOKzgvsKOcZ2gmOVGiVVdtjJd5UuOHD0jY0IqgAMIp%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw2MTI4NDEzODM1OTQiDH5x68VCDZ9xo1uVjirUApGo1FQ5hziq28UiqnYgRA%2B0hII401FJZDmTxSl1BXr98LLmLlM03hx7%2FLKJKHTdoWcSPcQFSs6rQwaugNOVX3DXbsfaxgudxVhx7segPnaIGqb9YuIGFBHF09qi1yDz9or1DJGy9j2ntAXSRbegp%2FTw02NOTKbW8QIHRjLCooabSMSMHKX6PcS%2FeaPyvYCBTyssRmXFd%2B%2Bdup2bklX3L3KvE6tvCYE0eSujItDna4Zz50GGhY7iLMX6xDRlc2mLUIQYKESZapg95gpQCyT8Lxewy%2B9UZs4qeeyqecJQEH9NbPOrWzCWZH1x1uMWOo9rVyYpHSanUUx17W7nnadIbY0d83dih2sHzoSgyPRJNolBVry46R0Y065UWesYgXoEif2Q%2F%2Fogl8EpW1XW3GNGyDDvJPVJaoxziOsXRiHTtybtYYP%2BhAYfvn6aUhitkpXnxopb9eQwo6zdowY6vwFMm%2BsXPwDzPwbO9L3G%2BPmnZnnTSz8it%2FE36XcKY78vVgMQ5%2BmfwNVvt1x%2FA7mFFGwNExhWk%2BrPrYkbEF9FGpjf9ATgWr2gMgM2vC3Tw2yNpWEWrRa5siWSzaGQFc5OggmpHRyG463imenXmLU2Afb4jkTP7hSFIQEu4OICRM1nTAT0gQr8yiSUia%2BDF5JrawQ71sskGFQeXU56rcT0l%2B02uBPyMDquWuPQpcUEevB48DbkLY2JR7VZeehQbff88g%3D%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Date=20230531T141355Z&X-Amz-SignedHeaders=host&X-Amz-Expires=900&X-Amz-Credential=ASIAY5MBRM2VL4ADRQAW%2F20230531%2Fus-east-1%2Ffinspace-apricot%2Faws4_request&X-Amz-Signature=d4a66f6e44cfc022a13492c68248df49b5fe8a3c1b00c626c31e309500e1574b\"\n",
    "\n",
    "/ open connection to cluster\n",
    "hdb:hopen(`$hdb_conn)\n",
    "\n",
    "/ database\n",
    "dbname: \"welcomedb\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e3ff6787-fea0-47da-947b-5c88a9f88d43",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2074832 67108864 67108864 0 0 33172062208\n"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hdb\"system \\\"w\\\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6ba74f68-18b9-40c4-bb17-7a7e4596b416",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       ",`example\n"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hdb\"tables[]\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62371245-b248-492c-b2be-7ab15ed62949",
   "metadata": {},
   "source": [
    "# Schema: example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cfe5acbf-14cd-4289-b2fc-353adb9b4ce5",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "c     | t f a\n",
       "------| -----\n",
       "date  | d    \n",
       "sym   | s   p\n",
       "time  | p    \n",
       "number| j    \n"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hdb(meta; `example)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e31f7751-bb05-4a59-a00c-9bcb4196ca0b",
   "metadata": {},
   "source": [
    "# Rows per Date "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "97130090-0179-402c-b733-835154812cf5",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date      | counts  avg_num  avg_sq_num  \n",
       "----------| -----------------------------\n",
       "2023.04.14| 1000000 499800.5 3.331963e+11\n",
       "2023.04.15| 1000000 499865   3.332692e+11\n",
       "2023.04.16| 1000000 499912.4 3.33206e+11 \n",
       "2023.04.17| 1000000 500078.4 3.334004e+11\n",
       "2023.04.18| 1000000 500264.7 3.336606e+11\n",
       "2023.04.19| 1000000 499850   3.333436e+11\n",
       "2023.04.20| 1000000 500082.3 3.334472e+11\n",
       "2023.04.21| 1000000 500169.5 3.336027e+11\n",
       "2023.04.22| 1000000 499903   3.331833e+11\n",
       "2023.04.23| 1000000 500277.5 3.335924e+11\n"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "\"Rows: 10,000,000\"\n"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "/ size of the table, by dates and total, calling lib function sq as well\n",
    "hdb \"select counts:count i, avg_num: avg number, avg_sq_num: avg sq number by date from example\"\n",
    "hdb \"\\\"Rows: \\\", {reverse\\\",\\\"sv 0N 3#reverse string x}count example\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "cf6d813b-03d7-4e2e-9779-88ef7ce4f355",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2023.05.31T14:15:17.507\n"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    ".z.z"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0baf31bc-af2e-4551-b0b7-6339134d62d9",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Q (kdb+)",
   "language": "q",
   "name": "qpk"
  },
  "language_info": {
   "file_extension": ".q",
   "mimetype": "text/x-q",
   "name": "q",
   "version": "4.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}