ManagedkdbInsights/torq/pykx_query_all.ipynb (1,092 lines of code) (raw):
{
"cells": [
{
"cell_type": "markdown",
"id": "1334d92e-34f4-448c-b6af-73cba89e9d6d",
"metadata": {},
"source": [
"# PyKX Query Components\n",
"Query all TorQ Components.\n"
]
},
{
"cell_type": "code",
"execution_count": 1,
"id": "11e9b62a-9cda-4a57-99ca-3b13aff6b7f3",
"metadata": {
"tags": []
},
"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",
"\n",
"from env import *\n",
"from config import *\n",
"\n",
"GATEWAY='gateway1' # Cluster (gateway) that queries will be sent to"
]
},
{
"cell_type": "code",
"execution_count": 2,
"id": "cb429a9f-e8a4-41d2-8572-c4e8a49ad387",
"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": "markdown",
"id": "1d25aa5e-b491-4b2f-b255-75f4e417daee",
"metadata": {},
"source": [
"# List of Clusters"
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "59c4f259-3c63-438c-bf29-b4e7a46d9420",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>clusterName</th>\n",
" <th>status</th>\n",
" <th>clusterType</th>\n",
" <th>capacityConfiguration</th>\n",
" <th>commandLineArguments</th>\n",
" <th>clusterDescription</th>\n",
" <th>lastModifiedTimestamp</th>\n",
" <th>createdTimestamp</th>\n",
" <th>databaseName</th>\n",
" <th>cacheConfigurations</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>discovery1</td>\n",
" <td>RUNNING</td>\n",
" <td>GP</td>\n",
" <td>None</td>\n",
" <td>[{'key': 'proctype', 'value': 'discovery'}, {'key': 'procname', 'value': 'discovery1'}, {'key': 'jsonlogs', 'value': 'true'}, {'key': 'noredirect', 'value': 'true'}, {'key': 's', 'value': '2'}]</td>\n",
" <td>Created with create_all notebook</td>\n",
" <td>2024-12-03 20:16:25.763000+00:00</td>\n",
" <td>2024-12-03 20:01:35.746000+00:00</td>\n",
" <td>finspace-database</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>feed1</td>\n",
" <td>RUNNING</td>\n",
" <td>GP</td>\n",
" <td>None</td>\n",
" <td>[{'key': 'proctype', 'value': 'tradeFeed'}, {'key': 'procname', 'value': 'tradeFeed1'}, {'key': 'jsonlogs', 'value': 'true'}, {'key': 'noredirect', 'value': 'true'}, {'key': 's', 'value': '2'}]</td>\n",
" <td>Created with create_all notebook</td>\n",
" <td>2024-12-03 20:30:55.001000+00:00</td>\n",
" <td>2024-12-03 20:18:27.837000+00:00</td>\n",
" <td>finspace-database</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>gateway1</td>\n",
" <td>RUNNING</td>\n",
" <td>GP</td>\n",
" <td>None</td>\n",
" <td>[{'key': 'proctype', 'value': 'gateway'}, {'key': 'procname', 'value': 'gateway1'}, {'key': 'jsonlogs', 'value': 'true'}, {'key': 'noredirect', 'value': 'true'}, {'key': 's', 'value': '2'}]</td>\n",
" <td>Created with create_all notebook</td>\n",
" <td>2024-12-03 20:30:55.616000+00:00</td>\n",
" <td>2024-12-03 20:18:24.649000+00:00</td>\n",
" <td>finspace-database</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>hdb1</td>\n",
" <td>RUNNING</td>\n",
" <td>GP</td>\n",
" <td>None</td>\n",
" <td>[{'key': 'proctype', 'value': 'hdb'}, {'key': 'procname', 'value': 'hdb1'}, {'key': 'jsonlogs', 'value': 'true'}, {'key': 'noredirect', 'value': 'true'}, {'key': 's', 'value': '4'}]</td>\n",
" <td>Created with create_all notebook</td>\n",
" <td>2024-12-03 20:17:05.878000+00:00</td>\n",
" <td>2024-12-03 20:01:41.735000+00:00</td>\n",
" <td>finspace-database</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>monitor1</td>\n",
" <td>RUNNING</td>\n",
" <td>GP</td>\n",
" <td>None</td>\n",
" <td>[{'key': 'proctype', 'value': 'monitor'}, {'key': 'procname', 'value': 'monitor1'}, {'key': 'jsonlogs', 'value': 'true'}, {'key': 'noredirect', 'value': 'true'}, {'key': 's', 'value': '1'}]</td>\n",
" <td>Created with create_all notebook</td>\n",
" <td>2024-12-03 20:29:55.787000+00:00</td>\n",
" <td>2024-12-03 20:18:30.995000+00:00</td>\n",
" <td>finspace-database</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>rdb1</td>\n",
" <td>RUNNING</td>\n",
" <td>GP</td>\n",
" <td>None</td>\n",
" <td>[{'key': 'proctype', 'value': 'rdb'}, {'key': 'procname', 'value': 'rdb1'}, {'key': 'jsonlogs', 'value': 'true'}, {'key': 'noredirect', 'value': 'true'}, {'key': 's', 'value': '2'}]</td>\n",
" <td>Created with create_all notebook</td>\n",
" <td>2024-12-03 20:18:04.815000+00:00</td>\n",
" <td>2024-12-03 20:01:39.007000+00:00</td>\n",
" <td>finspace-database</td>\n",
" <td>None</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" clusterName status clusterType capacityConfiguration \\\n",
"3 discovery1 RUNNING GP None \n",
"4 feed1 RUNNING GP None \n",
"5 gateway1 RUNNING GP None \n",
"6 hdb1 RUNNING GP None \n",
"7 monitor1 RUNNING GP None \n",
"8 rdb1 RUNNING GP None \n",
"\n",
" commandLineArguments \\\n",
"3 [{'key': 'proctype', 'value': 'discovery'}, {'key': 'procname', 'value': 'discovery1'}, {'key': 'jsonlogs', 'value': 'true'}, {'key': 'noredirect', 'value': 'true'}, {'key': 's', 'value': '2'}] \n",
"4 [{'key': 'proctype', 'value': 'tradeFeed'}, {'key': 'procname', 'value': 'tradeFeed1'}, {'key': 'jsonlogs', 'value': 'true'}, {'key': 'noredirect', 'value': 'true'}, {'key': 's', 'value': '2'}] \n",
"5 [{'key': 'proctype', 'value': 'gateway'}, {'key': 'procname', 'value': 'gateway1'}, {'key': 'jsonlogs', 'value': 'true'}, {'key': 'noredirect', 'value': 'true'}, {'key': 's', 'value': '2'}] \n",
"6 [{'key': 'proctype', 'value': 'hdb'}, {'key': 'procname', 'value': 'hdb1'}, {'key': 'jsonlogs', 'value': 'true'}, {'key': 'noredirect', 'value': 'true'}, {'key': 's', 'value': '4'}] \n",
"7 [{'key': 'proctype', 'value': 'monitor'}, {'key': 'procname', 'value': 'monitor1'}, {'key': 'jsonlogs', 'value': 'true'}, {'key': 'noredirect', 'value': 'true'}, {'key': 's', 'value': '1'}] \n",
"8 [{'key': 'proctype', 'value': 'rdb'}, {'key': 'procname', 'value': 'rdb1'}, {'key': 'jsonlogs', 'value': 'true'}, {'key': 'noredirect', 'value': 'true'}, {'key': 's', 'value': '2'}] \n",
"\n",
" clusterDescription lastModifiedTimestamp \\\n",
"3 Created with create_all notebook 2024-12-03 20:16:25.763000+00:00 \n",
"4 Created with create_all notebook 2024-12-03 20:30:55.001000+00:00 \n",
"5 Created with create_all notebook 2024-12-03 20:30:55.616000+00:00 \n",
"6 Created with create_all notebook 2024-12-03 20:17:05.878000+00:00 \n",
"7 Created with create_all notebook 2024-12-03 20:29:55.787000+00:00 \n",
"8 Created with create_all notebook 2024-12-03 20:18:04.815000+00:00 \n",
"\n",
" createdTimestamp databaseName cacheConfigurations \n",
"3 2024-12-03 20:01:35.746000+00:00 finspace-database None \n",
"4 2024-12-03 20:18:27.837000+00:00 finspace-database None \n",
"5 2024-12-03 20:18:24.649000+00:00 finspace-database None \n",
"6 2024-12-03 20:01:41.735000+00:00 finspace-database None \n",
"7 2024-12-03 20:18:30.995000+00:00 finspace-database None \n",
"8 2024-12-03 20:01:39.007000+00:00 finspace-database None "
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"cdf = get_clusters(client, environmentId=ENV_ID)\n",
"\n",
"all_clusters = [d['name'] for d in clusters if 'name' in d]\n",
"\n",
"if cdf is not None:\n",
" cdf = cdf[cdf['clusterName'].isin(all_clusters)]\n",
"# cdf = cdf[cdf['status'].isin(['RUNNING'])]\n",
"\n",
"display(cdf)"
]
},
{
"cell_type": "markdown",
"id": "a8f820e4-5768-4f1b-9629-6c15982579a5",
"metadata": {},
"source": [
"# Query the system through the Gateway"
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "a889a926-3379-470f-bd51-6cce954bd461",
"metadata": {
"tags": []
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"====================================================================================================\n",
"Query: .gw.syncexec[\"meta trades\";`rdb]\n",
"----------------------------------------------------------------------------------------------------\n"
]
},
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>t</th>\n",
" <th>f</th>\n",
" <th>a</th>\n",
" </tr>\n",
" <tr>\n",
" <th>c</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>sym</th>\n",
" <td>b's'</td>\n",
" <td></td>\n",
" <td>g</td>\n",
" </tr>\n",
" <tr>\n",
" <th>time</th>\n",
" <td>b'p'</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>src</th>\n",
" <td>b's'</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>price</th>\n",
" <td>b'f'</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>size</th>\n",
" <td>b'i'</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" t f a\n",
"c \n",
"sym b's' g\n",
"time b'p' \n",
"src b's' \n",
"price b'f' \n",
"size b'i' "
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"====================================================================================================\n",
"Query: .gw.syncexec[\"meta trades\";`hdb]\n",
"----------------------------------------------------------------------------------------------------\n"
]
},
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>t</th>\n",
" <th>f</th>\n",
" <th>a</th>\n",
" </tr>\n",
" <tr>\n",
" <th>c</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>date</th>\n",
" <td>b'd'</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>sym</th>\n",
" <td>b's'</td>\n",
" <td></td>\n",
" <td>p</td>\n",
" </tr>\n",
" <tr>\n",
" <th>time</th>\n",
" <td>b'p'</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>src</th>\n",
" <td>b's'</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>price</th>\n",
" <td>b'f'</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>size</th>\n",
" <td>b'i'</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" t f a\n",
"c \n",
"date b'd' \n",
"sym b's' p\n",
"time b'p' \n",
"src b's' \n",
"price b'f' \n",
"size b'i' "
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"====================================================================================================\n",
"Query: .gw.syncexec[\"select sum size by sym from trades\";`rdb]\n",
"----------------------------------------------------------------------------------------------------\n"
]
},
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>size</th>\n",
" </tr>\n",
" <tr>\n",
" <th>sym</th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>AAPL</th>\n",
" <td>888108</td>\n",
" </tr>\n",
" <tr>\n",
" <th>CSCO</th>\n",
" <td>1002668</td>\n",
" </tr>\n",
" <tr>\n",
" <th>DELL</th>\n",
" <td>1041400</td>\n",
" </tr>\n",
" <tr>\n",
" <th>GOOG</th>\n",
" <td>895262</td>\n",
" </tr>\n",
" <tr>\n",
" <th>IBM</th>\n",
" <td>1119756</td>\n",
" </tr>\n",
" <tr>\n",
" <th>MSFT</th>\n",
" <td>1177569</td>\n",
" </tr>\n",
" <tr>\n",
" <th>NOK</th>\n",
" <td>1184374</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ORCL</th>\n",
" <td>1160260</td>\n",
" </tr>\n",
" <tr>\n",
" <th>YHOO</th>\n",
" <td>920506</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" size\n",
"sym \n",
"AAPL 888108\n",
"CSCO 1002668\n",
"DELL 1041400\n",
"GOOG 895262\n",
"IBM 1119756\n",
"MSFT 1177569\n",
"NOK 1184374\n",
"ORCL 1160260\n",
"YHOO 920506"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"====================================================================================================\n",
"Query: .gw.syncexec[\"count trades\";`rdb]\n",
"----------------------------------------------------------------------------------------------------\n"
]
},
{
"data": {
"text/plain": [
"0 3500\n",
"dtype: int64"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"====================================================================================================\n",
"Query: .gw.syncexec[\"select [5] from trades\";`rdb]\n",
"----------------------------------------------------------------------------------------------------\n"
]
},
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>sym</th>\n",
" <th>time</th>\n",
" <th>src</th>\n",
" <th>price</th>\n",
" <th>size</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>MSFT</td>\n",
" <td>2024-12-03 20:28:29.976639175</td>\n",
" <td>N</td>\n",
" <td>30.27</td>\n",
" <td>5982</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>GOOG</td>\n",
" <td>2024-12-03 20:28:29.976639175</td>\n",
" <td>N</td>\n",
" <td>46.50</td>\n",
" <td>886</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>ORCL</td>\n",
" <td>2024-12-03 20:28:29.976639175</td>\n",
" <td>L</td>\n",
" <td>36.60</td>\n",
" <td>4526</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>AAPL</td>\n",
" <td>2024-12-03 20:28:29.976639175</td>\n",
" <td>L</td>\n",
" <td>21.24</td>\n",
" <td>416</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>MSFT</td>\n",
" <td>2024-12-03 20:28:29.976639175</td>\n",
" <td>N</td>\n",
" <td>30.21</td>\n",
" <td>6343</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" sym time src price size\n",
"0 MSFT 2024-12-03 20:28:29.976639175 N 30.27 5982\n",
"1 GOOG 2024-12-03 20:28:29.976639175 N 46.50 886\n",
"2 ORCL 2024-12-03 20:28:29.976639175 L 36.60 4526\n",
"3 AAPL 2024-12-03 20:28:29.976639175 L 21.24 416\n",
"4 MSFT 2024-12-03 20:28:29.976639175 N 30.21 6343"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"====================================================================================================\n",
"Query: .gw.syncexec[\"select [-5] from trades\";`rdb]\n",
"----------------------------------------------------------------------------------------------------\n"
]
},
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>sym</th>\n",
" <th>time</th>\n",
" <th>src</th>\n",
" <th>price</th>\n",
" <th>size</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>YHOO</td>\n",
" <td>2024-12-03 21:02:36.776577891</td>\n",
" <td>L</td>\n",
" <td>35.43</td>\n",
" <td>117</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>DELL</td>\n",
" <td>2024-12-03 21:02:36.776577891</td>\n",
" <td>O</td>\n",
" <td>29.46</td>\n",
" <td>197</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>MSFT</td>\n",
" <td>2024-12-03 21:02:36.776577891</td>\n",
" <td>O</td>\n",
" <td>41.11</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>GOOG</td>\n",
" <td>2024-12-03 21:02:36.776577891</td>\n",
" <td>N</td>\n",
" <td>41.33</td>\n",
" <td>1885</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>MSFT</td>\n",
" <td>2024-12-03 21:02:36.776577891</td>\n",
" <td>O</td>\n",
" <td>41.11</td>\n",
" <td>1981</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" sym time src price size\n",
"0 YHOO 2024-12-03 21:02:36.776577891 L 35.43 117\n",
"1 DELL 2024-12-03 21:02:36.776577891 O 29.46 197\n",
"2 MSFT 2024-12-03 21:02:36.776577891 O 41.11 5\n",
"3 GOOG 2024-12-03 21:02:36.776577891 N 41.33 1885\n",
"4 MSFT 2024-12-03 21:02:36.776577891 O 41.11 1981"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"====================================================================================================\n",
"Query: .gw.syncexec[\"select count i by date from trades\";`hdb]\n",
"----------------------------------------------------------------------------------------------------\n"
]
},
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>x</th>\n",
" </tr>\n",
" <tr>\n",
" <th>date</th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2014-04-21</th>\n",
" <td>29229</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2014-04-22</th>\n",
" <td>26305</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2014-04-23</th>\n",
" <td>32186</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" x\n",
"date \n",
"2014-04-21 29229\n",
"2014-04-22 26305\n",
"2014-04-23 32186"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"====================================================================================================\n",
"Query: .gw.syncexec[\"select sum size, max price by 0D00:05 xbar time from trades where sym=`IBM\";`hdb`rdb]\n",
"----------------------------------------------------------------------------------------------------\n"
]
},
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>size</th>\n",
" <th>price</th>\n",
" </tr>\n",
" <tr>\n",
" <th>time</th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2014-04-21 08:00:00</th>\n",
" <td>106759</td>\n",
" <td>41.18</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2014-04-21 08:05:00</th>\n",
" <td>105275</td>\n",
" <td>41.31</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2014-04-21 08:10:00</th>\n",
" <td>53978</td>\n",
" <td>41.18</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2014-04-21 08:15:00</th>\n",
" <td>76514</td>\n",
" <td>41.29</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2014-04-21 08:20:00</th>\n",
" <td>114316</td>\n",
" <td>41.03</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-12-03 20:40:00</th>\n",
" <td>137914</td>\n",
" <td>43.67</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-12-03 20:45:00</th>\n",
" <td>193739</td>\n",
" <td>43.63</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-12-03 20:50:00</th>\n",
" <td>163706</td>\n",
" <td>48.88</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-12-03 20:55:00</th>\n",
" <td>128982</td>\n",
" <td>45.92</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-12-03 21:00:00</th>\n",
" <td>116366</td>\n",
" <td>35.80</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>314 rows × 2 columns</p>\n",
"</div>"
],
"text/plain": [
" size price\n",
"time \n",
"2014-04-21 08:00:00 106759 41.18\n",
"2014-04-21 08:05:00 105275 41.31\n",
"2014-04-21 08:10:00 53978 41.18\n",
"2014-04-21 08:15:00 76514 41.29\n",
"2014-04-21 08:20:00 114316 41.03\n",
"... ... ...\n",
"2024-12-03 20:40:00 137914 43.67\n",
"2024-12-03 20:45:00 193739 43.63\n",
"2024-12-03 20:50:00 163706 48.88\n",
"2024-12-03 20:55:00 128982 45.92\n",
"2024-12-03 21:00:00 116366 35.80\n",
"\n",
"[314 rows x 2 columns]"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"====================================================================================================\n",
"Query: .gw.syncexecj[\"select sum size by sym from trades\";`rdb`hdb;sum]\n",
"----------------------------------------------------------------------------------------------------\n"
]
},
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>size</th>\n",
" </tr>\n",
" <tr>\n",
" <th>sym</th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>AAPL</th>\n",
" <td>26086384</td>\n",
" </tr>\n",
" <tr>\n",
" <th>CSCO</th>\n",
" <td>26351004</td>\n",
" </tr>\n",
" <tr>\n",
" <th>DELL</th>\n",
" <td>26313317</td>\n",
" </tr>\n",
" <tr>\n",
" <th>GOOG</th>\n",
" <td>26651612</td>\n",
" </tr>\n",
" <tr>\n",
" <th>IBM</th>\n",
" <td>26626676</td>\n",
" </tr>\n",
" <tr>\n",
" <th>MSFT</th>\n",
" <td>27009623</td>\n",
" </tr>\n",
" <tr>\n",
" <th>NOK</th>\n",
" <td>26886988</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ORCL</th>\n",
" <td>26593744</td>\n",
" </tr>\n",
" <tr>\n",
" <th>YHOO</th>\n",
" <td>26736205</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" size\n",
"sym \n",
"AAPL 26086384\n",
"CSCO 26351004\n",
"DELL 26313317\n",
"GOOG 26651612\n",
"IBM 26626676\n",
"MSFT 27009623\n",
"NOK 26886988\n",
"ORCL 26593744\n",
"YHOO 26736205"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"conn = get_pykx_connection(client, \n",
" environmentId=ENV_ID, clusterName=GATEWAY, \n",
" userName=KDB_USERNAME, boto_session=session)\n",
"\n",
"queries = [\n",
" '.gw.syncexec[\"meta trades\";`rdb]',\n",
" '.gw.syncexec[\"meta trades\";`hdb]',\n",
" '.gw.syncexec[\"select sum size by sym from trades\";`rdb]',\n",
" '.gw.syncexec[\"count trades\";`rdb]',\n",
" '.gw.syncexec[\"select [5] from trades\";`rdb]',\n",
" '.gw.syncexec[\"select [-5] from trades\";`rdb]',\n",
" '.gw.syncexec[\"select count i by date from trades\";`hdb]', # FIXED\n",
" '.gw.syncexec[\"select sum size, max price by 0D00:05 xbar time from trades where sym=`IBM\";`hdb`rdb]',\n",
" '.gw.syncexecj[\"select sum size by sym from trades\";`rdb`hdb;sum]',\n",
"]\n",
"\n",
"for q in queries:\n",
" print(100*\"=\")\n",
" print(f\"Query: {q}\")\n",
" print(100*\"-\")\n",
" display( conn(q).pd() )"
]
},
{
"cell_type": "code",
"execution_count": 5,
"id": "18789f15-e759-4728-9fb8-e2fcbd8a15c1",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Last Run: 2024-12-03 21:03:21.125277\n"
]
}
],
"source": [
"print( f\"Last Run: {datetime.datetime.now()}\" )"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "c051383b-045d-4b82-b84a-2be9530bf69b",
"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
}