ManagedkdbInsights/boto/query_welcomedb.ipynb

{
 "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": "code",
   "execution_count": 1,
   "id": "21a2e42a-4a1f-4f96-84cf-1a661bffd55d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import boto3\n",
    "import json\n",
    "import datetime\n",
    "\n",
    "import pykx as kx\n",
    "\n",
    "from managed_kx import *\n",
    "from env import *\n",
    "\n",
    "CLUSTER_NAME=\"cluster_welcomedb\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "0b707dd5-dfde-4376-bdc0-c4e196a963c6",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Using credentials and create service client\n",
    "session = boto3.Session()\n",
    "\n",
    "# create finspace client\n",
    "client = session.client(service_name='finspace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a0cc370b-8d4d-43e2-87b6-9ba3373eb42a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# get the connection string to the cluster\n",
    "conn_str = get_kx_connection_string(client, \n",
    "                          environmentId=ENV_ID, clusterName=CLUSTER_NAME, \n",
    "                          userName=KDB_USERNAME, boto_session=session)\n",
    "\n",
    "# parse the connection string to components\n",
    "host, port, username, password = parse_connection_string(conn_str)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb0da5aa-f799-4c07-95ed-cbcd52f89275",
   "metadata": {},
   "source": [
    "# Connect to the Cluster, List Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "76dec0d7-66e5-4d8a-84e7-01eddabae1e0",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2146800 67108864 67108864 0 0 16525275136\n",
      ",`example\n"
     ]
    }
   ],
   "source": [
    "%%q --host $host --port $port --user $username --pass $password\n",
    "\\c 30 150\n",
    "\n",
    "/ open a connection to the managed KX Cluster\n",
    "\n",
    "/ database\n",
    "dbname: \"welcomedb\"\n",
    "\n",
    "\\w\n",
    "\n",
    "tables[]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "62371245-b248-492c-b2be-7ab15ed62949",
   "metadata": {},
   "source": [
    "# Schema: example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "cfe5acbf-14cd-4289-b2fc-353adb9b4ce5",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "c     | t f a\n",
      "------| -----\n",
      "date  | d    \n",
      "sym   | s   p\n",
      "time  | p    \n",
      "number| j    \n"
     ]
    }
   ],
   "source": [
    "%%q --host $host --port $port --user $username --pass $password\n",
    "\n",
    "meta[`example]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e31f7751-bb05-4a59-a00c-9bcb4196ca0b",
   "metadata": {},
   "source": [
    "# Rows per Date "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "97130090-0179-402c-b733-835154812cf5",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date      | counts  avg_num  avg_sq_num  \n",
      "----------| -----------------------------\n",
      "2024.11.16| 1000000 499800.5 3.331963e+11\n",
      "2024.11.17| 1000000 499865   3.332692e+11\n",
      "2024.11.18| 1000000 499912.4 3.33206e+11 \n",
      "2024.11.19| 1000000 500078.4 3.334004e+11\n",
      "2024.11.20| 1000000 500264.7 3.336606e+11\n",
      "2024.11.21| 1000000 499850   3.333436e+11\n",
      "2024.11.22| 1000000 500082.3 3.334472e+11\n",
      "2024.11.23| 1000000 500169.5 3.336027e+11\n",
      "2024.11.24| 1000000 499903   3.331833e+11\n",
      "2024.11.25| 1000000 500277.5 3.335924e+11\n",
      "Rows: 10,000,000\n"
     ]
    }
   ],
   "source": [
    "%%q --host $host --port $port --user $username --pass $password\n",
    "\n",
    "/ size of the table, by dates and total, calling lib function sq as well\n",
    "select counts:count i, avg_num: avg number, avg_sq_num: avg sq number by date from example\n",
    "\n",
    "\"Rows: \", {reverse \",\" sv 0N 3#reverse string x}count example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "cf6d813b-03d7-4e2e-9779-88ef7ce4f355",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2024.11.26T15:24:41.909\n"
     ]
    }
   ],
   "source": [
    "%%q --host $host --port $port --user $username --pass $password\n",
    "\n",
    ".z.z"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "0baf31bc-af2e-4551-b0b7-6339134d62d9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10000000\n"
     ]
    }
   ],
   "source": [
    "%%q --host $host --port $port --user $username --pass $password\n",
    "\n",
    "count example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2cd6b034-419f-4276-aab2-f105a3f88dbe",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_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.10.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}