{ "cells": [ { "cell_type": "markdown", "id": "1334d92e-34f4-448c-b6af-73cba89e9d6d", "metadata": {}, "source": [ "# PyKX Query Components\n", "Query all components, RDB, HDB, and Gateway.\n" ] }, { "cell_type": "code", "execution_count": 26, "id": "11e9b62a-9cda-4a57-99ca-3b13aff6b7f3", "metadata": {}, "outputs": [], "source": [ "import warnings\n", "warnings.simplefilter(action='ignore', category=FutureWarning)\n", "\n", "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_kdb_1 import *\n", "\n", "from basictick_setup import *" ] }, { "cell_type": "code", "execution_count": 27, "id": "cb429a9f-e8a4-41d2-8572-c4e8a49ad387", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Using variables ...\n" ] } ], "source": [ "# triggers credential get\n", "session=None\n", "\n", "try:\n", " # aws: use ada for credentials\n", " subprocess.call([\"which\", \"ada\"])\n", " os.system(f\"ada credentials update --account={ACCOUNT_ID} --provider=isengard --role=Admin --once\")\n", "except: \n", " None\n", "\n", "if AWS_ACCESS_KEY_ID is None:\n", " print(\"Using Defaults ...\")\n", " # create AWS session: using access variables\n", " session = boto3.Session()\n", "else:\n", " print(\"Using variables ...\")\n", " session = boto3.Session(\n", " aws_access_key_id=AWS_ACCESS_KEY_ID,\n", " aws_secret_access_key=AWS_SECRET_ACCESS_KEY,\n", " aws_session_token=AWS_SESSION_TOKEN\n", " )\n", "\n", "# create finspace client\n", "client = session.client(service_name='finspace', endpoint_url=ENDPOINT_URL)" ] }, { "cell_type": "markdown", "id": "32c26a3e-e51e-4981-951b-efca08e20e9a", "metadata": {}, "source": [ "# Current State of HDB" ] }, { "cell_type": "code", "execution_count": 28, "id": "125c2a8e-1880-4043-9230-1089778e5bf8", "metadata": {}, "outputs": [], "source": [ "# Query the HDB\n", "hdb = get_pykx_connection(client, \n", " environmentId=ENV_ID, clusterName=HDB_CLUSTER_NAME, \n", " userName=KDB_USERNAME, boto_session=session)\n", "\n", "# Dates and Counts\n", "hdb_pdf = hdb(\"select counts:count i by date from example\").pd()\n", "\n", "# Number of Rows\n", "hdb_rows = hdb(\"count example\").py()" ] }, { "cell_type": "markdown", "id": "47a1f5c4-3433-4b06-9ef6-8164ad2fb100", "metadata": {}, "source": [ "# Current State of RDB" ] }, { "cell_type": "code", "execution_count": 29, "id": "3371f4c9-fcd3-46ab-8780-feb58d382a66", "metadata": {}, "outputs": [], "source": [ "# Query the RDB\n", "rdb = get_pykx_connection(client, \n", " environmentId=ENV_ID, clusterName=RDB_CLUSTER_NAME, \n", " userName=KDB_USERNAME, boto_session=session)\n", "\n", "# Dates and Counts\n", "rdb_head_pdf = rdb(\"select [5] from example\").pd()\n", "rdb_tail_pdf = rdb(\"select [-5] from example\").pd()\n", "\n", "# Number of Rows\n", "rdb_rows = rdb(\"count example\").py()" ] }, { "cell_type": "markdown", "id": "ddafebe2-5729-42b2-9598-b304d2af0776", "metadata": {}, "source": [ "# Current State of GW" ] }, { "cell_type": "code", "execution_count": 30, "id": "4c09a5be-ce4a-43bf-a6df-3305b3f9332e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
processhandleconnectedaddress
0rdb16True:ip-192-168-7-230.ec2.internal:5000:GATEWAY_basictickdb:Host=ip-192-168-7-230.ec2.internal&Port=5000&User=GATE...
1hdb17True:ip-192-168-14-219.ec2.internal:5000:GATEWAY_basictickdb:Host=ip-192-168-14-219.ec2.internal&Port=5000&User=GA...
2hdb18True:ip-192-168-1-82.ec2.internal:5000:GATEWAY_basictickdb:Host=ip-192-168-1-82.ec2.internal&Port=5000&User=GATEWA...
\n", "
" ], "text/plain": [ " process handle connected \\\n", "0 rdb 16 True \n", "1 hdb 17 True \n", "2 hdb 18 True \n", "\n", " address \n", "0 :ip-192-168-7-230.ec2.internal:5000:GATEWAY_basictickdb:Host=ip-192-168-7-230.ec2.internal&Port=5000&User=GATE... \n", "1 :ip-192-168-14-219.ec2.internal:5000:GATEWAY_basictickdb:Host=ip-192-168-14-219.ec2.internal&Port=5000&User=GA... \n", "2 :ip-192-168-1-82.ec2.internal:5000:GATEWAY_basictickdb:Host=ip-192-168-1-82.ec2.internal&Port=5000&User=GATEWA... " ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Query the GW\n", "gw = get_pykx_connection(client, \n", " environmentId=ENV_ID, clusterName=GW_CLUSTER_NAME, \n", " userName=KDB_USERNAME, boto_session=session)\n", "\n", "proc_pdf = gw(\"select process, handle, connected, address from .conn.procs\").pd()\n", "\n", "# are any processes not connected? if so-reconnect\n", "if len(proc_pdf[proc_pdf.connected == False].index) > 0:\n", " print(\"reinit Gateway\")\n", " gw(\"reinit[hdb_name; rdb_name]\")\n", " proc_pdf = gw(\"select process, handle, connected, address from .conn.procs\").pd()\n", "\n", "# truncate with elipsis\n", "proc_pdf['address'] = proc_pdf['address'].str.slice(0,110)+\"...\"\n", "\n", "display(proc_pdf)" ] }, { "cell_type": "code", "execution_count": 31, "id": "728e4415-82c8-4700-ac13-6c76b5d36e4e", "metadata": {}, "outputs": [], "source": [ "# query GW\n", "gw(\"res: `time xasc queryData[`example;`]\").pd()\n", "\n", "gw_pdf = gw(\"res\").pd()\n", "gw_head_pdf = gw(\"select [5] from res\").pd()\n", "gw_tail_pdf = gw(\"select [-5] from res\").pd()\n", "\n", "# Number of Rows\n", "gw_rows = len(gw_pdf.index)" ] }, { "cell_type": "markdown", "id": "d797007e-355b-4fee-9c6e-a11273d27d76", "metadata": {}, "source": [ "# HDB Contents" ] }, { "cell_type": "code", "execution_count": 32, "id": "f6107bbb-e8fd-46df-97a1-6033d628f38b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
counts
date
2023-04-141000000
2023-04-151000000
2023-04-161000000
2023-04-171000000
2023-04-181000000
2023-04-191000000
2023-04-201000000
2023-04-211000000
2023-04-221000000
2023-04-231000000
2023-06-251830865
2023-06-2612174316
2023-07-069856512
2023-07-074904083
2023-07-123087509
2023-07-251208316
\n", "
" ], "text/plain": [ " counts\n", "date \n", "2023-04-14 1000000\n", "2023-04-15 1000000\n", "2023-04-16 1000000\n", "2023-04-17 1000000\n", "2023-04-18 1000000\n", "2023-04-19 1000000\n", "2023-04-20 1000000\n", "2023-04-21 1000000\n", "2023-04-22 1000000\n", "2023-04-23 1000000\n", "2023-06-25 1830865\n", "2023-06-26 12174316\n", "2023-07-06 9856512\n", "2023-07-07 4904083\n", "2023-07-12 3087509\n", "2023-07-25 1208316" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "Rows: 43,061,601\n" ] } ], "source": [ "display(hdb_pdf)\n", "\n", "# Number of Rows\n", "print(f\"Rows: {hdb_rows:,}\")" ] }, { "cell_type": "markdown", "id": "6569399c-4be5-43fa-bb73-1fbab9895fbc", "metadata": {}, "source": [ "# RDB Contents" ] }, { "cell_type": "code", "execution_count": 33, "id": "ffd15de4-c777-42e1-bf6f-e951abd16b82", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
symtimenumber
0add2023-07-26 17:23:05.6954064539
1lfd2023-07-26 17:23:05.69540645348
2okj2023-07-26 17:23:05.69540645326
3feb2023-07-26 17:23:05.69540645375
4idg2023-07-26 17:23:05.69540645374
\n", "
" ], "text/plain": [ " sym time number\n", "0 add 2023-07-26 17:23:05.695406453 9\n", "1 lfd 2023-07-26 17:23:05.695406453 48\n", "2 okj 2023-07-26 17:23:05.695406453 26\n", "3 feb 2023-07-26 17:23:05.695406453 75\n", "4 idg 2023-07-26 17:23:05.695406453 74" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
symtimenumber
0njh2023-07-26 19:22:49.68536740421
1hko2023-07-26 19:22:49.68536740446
2lnb2023-07-26 19:22:49.6853674048
3pga2023-07-26 19:22:49.68536740460
4lcd2023-07-26 19:22:49.68536740486
\n", "
" ], "text/plain": [ " sym time number\n", "0 njh 2023-07-26 19:22:49.685367404 21\n", "1 hko 2023-07-26 19:22:49.685367404 46\n", "2 lnb 2023-07-26 19:22:49.685367404 8\n", "3 pga 2023-07-26 19:22:49.685367404 60\n", "4 lcd 2023-07-26 19:22:49.685367404 86" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "Rows: 3,234,340\n" ] } ], "source": [ "display(rdb_head_pdf)\n", "display(rdb_tail_pdf)\n", "\n", "# Number of Rows\n", "print(f\"Rows: {rdb_rows:,}\")" ] }, { "cell_type": "markdown", "id": "8c3c2616-72f0-4263-bc1d-68ec636f45c4", "metadata": {}, "source": [ "# GW Contents" ] }, { "cell_type": "code", "execution_count": 34, "id": "c7c67a6b-01bf-4918-83e6-61d90a549b03", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
symtimenumber
0aoi2023-04-14 23:17:28.47353231
1aoi2023-04-14 23:17:28.473153560
2aoi2023-04-14 23:17:28.473449428
3aoi2023-04-14 23:17:28.473631966
4aoi2023-04-14 23:17:28.473941566
\n", "
" ], "text/plain": [ " sym time number\n", "0 aoi 2023-04-14 23:17:28.473 53231\n", "1 aoi 2023-04-14 23:17:28.473 153560\n", "2 aoi 2023-04-14 23:17:28.473 449428\n", "3 aoi 2023-04-14 23:17:28.473 631966\n", "4 aoi 2023-04-14 23:17:28.473 941566" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
symtimenumber
0egb2023-07-26 19:22:50.68537827092
1jpg2023-07-26 19:22:50.68537827094
2cfn2023-07-26 19:22:50.68537827073
3lka2023-07-26 19:22:50.68537827072
4lnj2023-07-26 19:22:50.68537827065
\n", "
" ], "text/plain": [ " sym time number\n", "0 egb 2023-07-26 19:22:50.685378270 92\n", "1 jpg 2023-07-26 19:22:50.685378270 94\n", "2 cfn 2023-07-26 19:22:50.685378270 73\n", "3 lka 2023-07-26 19:22:50.685378270 72\n", "4 lnj 2023-07-26 19:22:50.685378270 65" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "Rows: 46,296,391\n" ] } ], "source": [ "display(gw_head_pdf)\n", "display(gw_tail_pdf)\n", "\n", "# Number of Rows\n", "print(f\"Rows: {gw_rows:,}\")\n", "\n", "# Note: RDB will have less than GW b/c data is always arriving to the RDB\n", "# RDB alone was queried first and then again as part of GW query" ] }, { "cell_type": "code", "execution_count": 35, "id": "70e7ef98-be09-4d93-ae90-2e957732de4e", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Last Run: 2023-07-26 19:23:27.236464\n" ] } ], "source": [ "print( f\"Last Run: {datetime.datetime.now()}\" )" ] }, { "cell_type": "code", "execution_count": null, "id": "da54f39e-a460-4036-85eb-83eabf28ee8e", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "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.9.12" } }, "nbformat": 4, "nbformat_minor": 5 }