{ "cells": [ { "cell_type": "markdown", "id": "e13084db-c69f-4e70-aa79-acfd2159093a", "metadata": {}, "source": [ "# BasicTick: End of Day (EOD) Processing\n", "This notebook an example of an end of day (EOD) process that adds the contents of an RDB as a changeset to an HDB.\n", "\n", "Instead of moving between python and q code this notebook makes use of PyKX to do all its work with the RDB, HDB, and Gateway for end of day processing.\n", "\n", "**RDB: Save Day's Data**\n", "1. Save table and sym locally \n", "2. Savedown: add changeset to database \n", "\n", "**HDB: Update**\n", "1. Update the Cluster's Database to New Changeset ID\n", "\n", "**Gateway: Re-Connect**\n", "1. Update the Database Connections" ] }, { "cell_type": "code", "execution_count": 1, "id": "b058c1df-087e-4856-828c-e296ecd1a95c", "metadata": {}, "outputs": [], "source": [ "# scratch location on RDB\n", "scratch_path = \"/opt/kx/app/scratch\"\n", "\n", "# clean rdb?\n", "clear_rdb = True" ] }, { "cell_type": "code", "execution_count": 2, "id": "3698017d-42ed-4280-afa1-723f19ae398c", "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": 3, "id": "ed241efe-523e-4d2f-8e1b-94a09adf42e7", "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": "deb06187-17ea-4934-a142-910e1aace504", "metadata": {}, "source": [ "# Current State of HDB" ] }, { "cell_type": "code", "execution_count": 4, "id": "19f6d351-7a93-4c33-b7fe-1fe43cbc71b6", "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", "before_update_pdf = hdb(\"select counts:count i by date from example\").pd()\n", "\n", "# Number of Rows\n", "before_rows = hdb(\"count example\").py()" ] }, { "cell_type": "markdown", "id": "200a3ab3-f225-4215-8369-5ea9c068aa97", "metadata": {}, "source": [ "# RDB: Save Day's Data\n", "1. Save table and sym locally \n", "2. Savedown: add changeset to database" ] }, { "cell_type": "code", "execution_count": 5, "id": "1ce04969-eb7d-4352-862a-b27a93a4f343", "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
0aoe2023-07-26 19:23:44.67538459450
1igl2023-07-26 19:23:44.67538459479
2nfp2023-07-26 19:23:44.67538459458
3oop2023-07-26 19:23:44.6853720937
4fac2023-07-26 19:23:44.68537209369
\n", "
" ], "text/plain": [ " sym time number\n", "0 aoe 2023-07-26 19:23:44.675384594 50\n", "1 igl 2023-07-26 19:23:44.675384594 79\n", "2 nfp 2023-07-26 19:23:44.675384594 58\n", "3 oop 2023-07-26 19:23:44.685372093 7\n", "4 fac 2023-07-26 19:23:44.685372093 69" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "Rows: 3,258,921\n" ] } ], "source": [ "# Connect to the RDB\n", "rdb = get_pykx_connection(client, \n", " environmentId=ENV_ID, clusterName=RDB_CLUSTER_NAME, \n", " userName=KDB_USERNAME, boto_session=session)\n", "# Dates and Counts\n", "rdb_sample_pdf = rdb(\"select [-5] from example\").pd()\n", "rdb_rows = rdb(\"count example\").py()\n", "\n", "display(rdb_sample_pdf)\n", "print(f\"Rows: {rdb_rows:,}\")" ] }, { "cell_type": "markdown", "id": "1b1b9caa-bd25-4084-b797-6063f2abd9dd", "metadata": { "tags": [] }, "source": [ "## Step 1: Save table and sym locally" ] }, { "cell_type": "code", "execution_count": 6, "id": "ace112e5-9ab5-4802-bbaa-95973e84e9c6", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Saving to: 2023.07.26\n", "Saved to: /opt/kx/app/scratch\n" ] } ], "source": [ "# date directory\n", "today = datetime.date.today()\n", "\n", "date_dir = today.strftime(\"%Y.%m.%d\")\n", "\n", "print( f\"Saving to: {date_dir}\" )\n", "rdb( f\".Q.dpfts[`:{scratch_path};{date_dir};`sym;`example;`sym]\" )\n", "print( f\"Saved to: {scratch_path}\" )\n" ] }, { "cell_type": "markdown", "id": "e8842d71-42d6-4aba-bae9-920bd42fde1b", "metadata": {}, "source": [ "## Step 2: Savedown: add changeset to database\n", "\n", "a. Table of changes for the changset \n", "b. Create Changeset \n", "c. Wait for Changeset to be added" ] }, { "cell_type": "code", "execution_count": 7, "id": "2b6034c5-0d52-489f-8822-a87cb76e9a43", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[{'input_path': '/opt/kx/app/scratch/2023.07.26',\n", " 'database_path': '/2023.07.26/',\n", " 'change_type': 'PUT'},\n", " {'input_path': '/opt/kx/app/scratch/sym',\n", " 'database_path': '/',\n", " 'change_type': 'PUT'}]" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# pandas table that catalogs the changeset's contents\n", "cr = [\n", " {'input_path': f'{scratch_path}/{date_dir}', 'database_path': f'/{date_dir}/', 'change_type':'PUT'},\n", " {'input_path': f'{scratch_path}/sym', 'database_path': f'/', 'change_type':'PUT'},\n", "]\n", "\n", "display(cr)\n", "\n", "# send to rdb\n", "rdb['c_r'] = cr" ] }, { "cell_type": "code", "execution_count": 8, "id": "2c84f17c-1db6-4023-9651-840e94c7c6a9", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Creating changeset for: basictickdb\n", "ChangesetID: OMTJ1CPJNAvq0RQF3o7xFQ\n" ] } ], "source": [ "print(f\"Creating changeset for: {DB_NAME}\")\n", "\n", "res = rdb(f\".aws.create_changeset[\\\"{DB_NAME}\\\";c_r]\")\n", "\n", "CHANGESET_ID = str(res.get(\"id\"))\n", "print(f\"ChangesetID: {CHANGESET_ID}\")" ] }, { "cell_type": "code", "execution_count": 9, "id": "d0fd43be-7d53-49f5-a006-d285cd5cfeef", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Status is IN_PROGRESS, total wait 0:00:00, waiting 10 sec ...\n", "Status is IN_PROGRESS, total wait 0:00:10, waiting 10 sec ...\n", "Status is IN_PROGRESS, total wait 0:00:20, waiting 10 sec ...\n", "Status is IN_PROGRESS, total wait 0:00:30, waiting 10 sec ...\n", "Status is IN_PROGRESS, total wait 0:00:40, waiting 10 sec ...\n", "** Done **\n" ] } ], "source": [ "# wait for ingestion\n", "wait_for_changeset_status(client, environmentId=ENV_ID, databaseName=DB_NAME, changesetId=CHANGESET_ID, show_wait=True)\n", "print(\"** Done **\")" ] }, { "cell_type": "markdown", "id": "6ed32b22-2c02-48d0-9530-ca73eded8233", "metadata": {}, "source": [ "### Optional: Clean up RDB\n", "Optionally clean up by deleting files created and clear the example table." ] }, { "cell_type": "code", "execution_count": 10, "id": "0f8c8296-b840-415f-83dd-f0ebd6f85708", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Cleaning: /opt/kx/app/scratch\n" ] } ], "source": [ "# clear the RDB\n", "if clear_rdb:\n", " print(f\"Cleaning: {scratch_path}\")\n", "\n", " rdb(f\"system \\\" rm -rf {scratch_path}/*\\\"\")\n", "\n", " # remove tables\n", " rdb(\"delete from `example\")\n", " rdb(\"delete c_r from `.\")" ] }, { "cell_type": "markdown", "id": "fe9de679-e109-41de-b61b-dc22ab7f5a56", "metadata": {}, "source": [ "# HDB: Update\n", "Update the cluster's database to new changeset.\n" ] }, { "cell_type": "code", "execution_count": 11, "id": "d308bf8e-3fcf-4bd7-a8bd-6f7e8ebee864", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "** Done **\n" ] } ], "source": [ "DB_CONFIG=[{'databaseName': DB_NAME,\n", " 'cacheConfigurations': [{'cacheType': 'CACHE_1000', 'dbPaths': ['/']}],\n", " 'changesetId': CHANGESET_ID}]\n", "\n", "client.update_kx_cluster_databases(environmentId=ENV_ID, clusterName=HDB_CLUSTER_NAME, databases=DB_CONFIG)\n", "\n", "wait_for_cluster_status(client, environmentId=ENV_ID, clusterName=HDB_CLUSTER_NAME)\n", "print(\"** Done **\")" ] }, { "cell_type": "markdown", "id": "e3f1a530-e64f-4734-ab5a-edb82829755e", "metadata": {}, "source": [ "# Gateway: Re-Connect\n", "Using PyKX, connect to the Gateway cluster and have it re-connect to its Databases. Connection to the HDB would have been lost during the HDB update process.\n" ] }, { "cell_type": "code", "execution_count": 12, "id": "e0ba7b05-b634-4ea0-b11f-ed32b591c6eb", "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
0rdb21True:ip-192-168-7-230.ec2.internal:5000:GATEWAY_basictickdb:Host=ip-192-168-7-230.ec2.internal&Port=5000&User=GATEWAY_basictickdb&Action=finspace%3AConnectKxCluster&X-Amz-Security-Token=IQoJb3JpZ2luX2VjELT%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FwEaCXVzLWVhc3QtMSJGMEQCICoYJWSJDTdFTxl7%2FxTem%2FjbeZWBzVNZ2nX%2Fjy3jhXWxAiA%2FCya%2BSKa4uMPqWQGRQz%2F9esmOpoROQcid4PznrD0sTir3AghNEAAaDDgyOTg0NTk5ODg4OSIMOOM%2FRAfKEm0ivdJcKtQCkKEzUkVaZ%2BAUhv3wueF6fVku8TEU%2BQBSOoI9kxkSqn07rR8iwMHTgtFbwljpClitcOo%2BT4y7nlVR0cEgPpN8RE%2FUddnpiRRP227%2BvtKUw%2B9Nu4hi9dh046qRMYyQciN%2F234Ve3q7xbucSU0ZX3byCRN3KOfvZfSWjcyNsiteQm%2F57zdXqIKf6ou9jbyw96PBmxp5PtS5MV5Zjo42%2FWNR8QiYeTuRUYbQP8zU38tInY7F3gLUV4M8RTqD34iETO0CF2pXKQ0jXigHPx%2BKGmSAxvRaNTBffnc7MiouBvhywdZS8%2FopWyy8Z2j4vTAk77NlLuT2QaKHDZ1L%2FB2QrjfpxR0Gb6mSPHJoF8cy3f%2F%2Bvki2sLrYJTZLXr6sxH6QJg%2FIKUQzt%2FcuseWFX0hKMUfnxayFviRJtOTwOcXzfXeF%2F02jGpUOfAaUCUB5ktql3JXdxpkOKjCz7IWmBjrAAQu3c8A%2BlKR7KnODdxnQD8ABH9qnClcj%2BBlU7ugIkJwh7O0qwXtZSSUpUL%2BsxiAvyXq79%2BT77Ycx7cxRemmJw7QVM8lqkspWm9bcMNkELspZiIQdflUmXnKvqWXgM%2Fdrjt0YDwcR0u7k1Ht4sB1QPL%2Fmx6lPEqAIEYfQCQu2RuairfTDjGN42WOsZ97hGjtya2M%2F7KUsRZDzsXlXTBE9A4cQ2KVJGu40ZtzFocp8947jUsH9UgvqrrTJjitudfjPJA%3D%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Date=20230726T193827Z&X-Amz-SignedHeaders=host&X-Amz-Expires=900&X-Amz-Credential=ASIA4CNVNBUU52PW3OOT%2F20230726%2Fus-east-1%2Ffinspace-apricot%2Faws4_request&X-Amz-Signature=467499e2a5fb509565b6c7467fce2f31bdf05c074999b4debc4af2e06cd24309
1hdb22True:ip-192-168-0-64.ec2.internal:5000:GATEWAY_basictickdb:Host=ip-192-168-0-64.ec2.internal&Port=5000&User=GATEWAY_basictickdb&Action=finspace%3AConnectKxCluster&X-Amz-Security-Token=IQoJb3JpZ2luX2VjELT%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FwEaCXVzLWVhc3QtMSJHMEUCIAeD0ZNIa4IlUo6JTW5rQHMckntQOd8JPGCY1O%2F8nNZ%2FAiEAyE9OBQcX3750NPcRAwmQ%2BY9Jyh81r3c4P1qNWOPjAYwq9wIITRAAGgw4Mjk4NDU5OTg4ODkiDEbg8fzsF40QnAOxOyrUAr38G%2BOA6j3aPYGNUzX7NcEQ6XVJQoVU%2B0Q%2FUkT%2B3bPH%2Fa%2F1QiTfqu7fzmPoUMzaK9upuErbKT3Zz%2FaRZuDPdunUUxFLXQTDvzLkjIgJJxSgQH5KJNtrSG2ibfTXKPivYUbcEmb1awIsD%2FhUTLwE2WPBSileku13X7MbFPbDSWiz3%2FiTjqszL4Bdy7gi0rkCqM5188aipGieWZRdnDWWjTV%2BlTxNFN%2FgKk%2BuZeIiahBtXau38lHKXj22lx8aeLwEC%2BNx7IF0vAtUOzbtZFbsYHKQjzPZ6g1R13PDo9GEhkMUsoolO51ET2v1CwTIAoX4yonOEbVuS%2Ful4tvWlqxVFkts8Qg1jdK9LKcZxMd%2BeMuCpCia%2BdT%2BuGdk3r3sc0BuC0xnF5zgOxexq6xW1A6hc1Tm%2FrhUJzppqORWNGily7R8qITlvPGh3AZoLh%2BJ%2BuCtK39JvhkwtOyFpgY6vwEDo4VFyhjCVrdngBLlP6X1S59cwPb0AXC4g7YhpqNugeEZg2hNLyqrSFxMlnDam5EMfust9x%2F1bFI%2Ft2NeixHQmlSY6bXtiJXUmPY%2Bm%2BC14uveeWySpp2ZFeYSUi1yl1o78B7B7S8KB%2BUu6KNu%2FQ80Xr%2BjYXgHuLSu%2BoJ6pO6YpF6UEja5IXMneKzmTFZkFqm%2FxExfZoufrwkW%2BY%2Bbr1lZRtKOl%2FXeGStGhCPCkXf0dTEFzL1f07zVna6GwhkJ8w%3D%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Date=20230726T193828Z&X-Amz-SignedHeaders=host&X-Amz-Expires=900&X-Amz-Credential=ASIA4CNVNBUUSMD4MC4A%2F20230726%2Fus-east-1%2Ffinspace-apricot%2Faws4_request&X-Amz-Signature=a13aa88ade2cee1b5f71bb4da94d8d1b017601adca89e548c86291dfdea3187d
2hdb23True:ip-192-168-5-75.ec2.internal:5000:GATEWAY_basictickdb:Host=ip-192-168-5-75.ec2.internal&Port=5000&User=GATEWAY_basictickdb&Action=finspace%3AConnectKxCluster&X-Amz-Security-Token=IQoJb3JpZ2luX2VjELT%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FwEaCXVzLWVhc3QtMSJIMEYCIQDUrl9KuaLg7uUKtcEpFdf770YBI024i6NQppRuzmKbsgIhAPHt%2Fpdq7bdVXKZ3epOwaBUq9wZSWx31yx3VQhwDr65PKvcCCE0QABoMODI5ODQ1OTk4ODg5Igzh5fk6lck86lrg4TYq1ALag7fx1Kqn06m%2BzYNhEs64LnecDDSxnOmaCqq%2Fr%2BbtKUikWIty2rawg4YXsz6u3gq5sb9aZ7Ynq4GhNy1qrWasiQHSq2khGlQvcSlNiNemUXjGehj1ZVsIZo7QvWmt1YdRLrY%2F8c4GMO8OslrovONLHSnggrk7iFf6lJJhdqsNy8OmF6mMK0g6YkLx3DTJfC6P3%2FiZkqrBR%2FTBlalnD9kckrUvXBC5tRoR6WSvtba2esLfGa9r907Da47Gsu0t2vZtLhm8nIqckV8AJILdEzsw72UbAXBw8cax41zNosqTsXilo93atcnDsF30jor6DwR0MWls0OpkRZ196snJX5TG7ii55ZiydC2oZx%2BfiOfCKqXWzf27ZozRSWsUiVYmhg6U4FflS2BPdbcGQtYt9k%2B66ZwR9N6Y1sET5OzVYDBQ40TNY4sCUOfVFA%2FKny1uB4209V9bMLTshaYGOr4BsdHZLS90vtoseTV1jd2Mtcaxa6qXvySJrJEdFBOSXM7H4S8fpLkKLI8FoZViU8IlGcLNzIQ%2FzLaVn799yKOAy0kvtYp1xZzGw9CBwkdkjclWsLubq%2FSRJdUKDz2RAvNShWGSOgmbVkJFvDYS9oP07YCET8X52vGUOUTjjkAQqa8VkIMqch4qW2GHQXCDuYC3I%2BhgJwBDxaDyQ%2Bs4kYc5VhvnHPNtp3HQli5xi8hwqGaW%2B3aV3JAB1Bb56AAzFQ%3D%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Date=20230726T193828Z&X-Amz-SignedHeaders=host&X-Amz-Expires=900&X-Amz-Credential=ASIA4CNVNBUU2ELVJGLT%2F20230726%2Fus-east-1%2Ffinspace-apricot%2Faws4_request&X-Amz-Signature=4478101265a2e2ee9c97c6658eb9c33f5944b7d7ec7d825b5aa64afe051ae709
\n", "
" ], "text/plain": [ " process handle connected \\\n", "0 rdb 21 True \n", "1 hdb 22 True \n", "2 hdb 23 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=GATEWAY_basictickdb&Action=finspace%3AConnectKxCluster&X-Amz-Security-Token=IQoJb3JpZ2luX2VjELT%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FwEaCXVzLWVhc3QtMSJGMEQCICoYJWSJDTdFTxl7%2FxTem%2FjbeZWBzVNZ2nX%2Fjy3jhXWxAiA%2FCya%2BSKa4uMPqWQGRQz%2F9esmOpoROQcid4PznrD0sTir3AghNEAAaDDgyOTg0NTk5ODg4OSIMOOM%2FRAfKEm0ivdJcKtQCkKEzUkVaZ%2BAUhv3wueF6fVku8TEU%2BQBSOoI9kxkSqn07rR8iwMHTgtFbwljpClitcOo%2BT4y7nlVR0cEgPpN8RE%2FUddnpiRRP227%2BvtKUw%2B9Nu4hi9dh046qRMYyQciN%2F234Ve3q7xbucSU0ZX3byCRN3KOfvZfSWjcyNsiteQm%2F57zdXqIKf6ou9jbyw96PBmxp5PtS5MV5Zjo42%2FWNR8QiYeTuRUYbQP8zU38tInY7F3gLUV4M8RTqD34iETO0CF2pXKQ0jXigHPx%2BKGmSAxvRaNTBffnc7MiouBvhywdZS8%2FopWyy8Z2j4vTAk77NlLuT2QaKHDZ1L%2FB2QrjfpxR0Gb6mSPHJoF8cy3f%2F%2Bvki2sLrYJTZLXr6sxH6QJg%2FIKUQzt%2FcuseWFX0hKMUfnxayFviRJtOTwOcXzfXeF%2F02jGpUOfAaUCUB5ktql3JXdxpkOKjCz7IWmBjrAAQu3c8A%2BlKR7KnODdxnQD8ABH9qnClcj%2BBlU7ugIkJwh7O0qwXtZSSUpUL%2BsxiAvyXq79%2BT77Ycx7cxRemmJw7QVM8lqkspWm9bcMNkELspZiIQdflUmXnKvqWXgM%2Fdrjt0YDwcR0u7k1Ht4sB1QPL%2Fmx6lPEqAIEYfQCQu2RuairfTDjGN42WOsZ97hGjtya2M%2F7KUsRZDzsXlXTBE9A4cQ2KVJGu40ZtzFocp8947jUsH9UgvqrrTJjitudfjPJA%3D%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Date=20230726T193827Z&X-Amz-SignedHeaders=host&X-Amz-Expires=900&X-Amz-Credential=ASIA4CNVNBUU52PW3OOT%2F20230726%2Fus-east-1%2Ffinspace-apricot%2Faws4_request&X-Amz-Signature=467499e2a5fb509565b6c7467fce2f31bdf05c074999b4debc4af2e06cd24309 \n", "1 :ip-192-168-0-64.ec2.internal:5000:GATEWAY_basictickdb:Host=ip-192-168-0-64.ec2.internal&Port=5000&User=GATEWAY_basictickdb&Action=finspace%3AConnectKxCluster&X-Amz-Security-Token=IQoJb3JpZ2luX2VjELT%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FwEaCXVzLWVhc3QtMSJHMEUCIAeD0ZNIa4IlUo6JTW5rQHMckntQOd8JPGCY1O%2F8nNZ%2FAiEAyE9OBQcX3750NPcRAwmQ%2BY9Jyh81r3c4P1qNWOPjAYwq9wIITRAAGgw4Mjk4NDU5OTg4ODkiDEbg8fzsF40QnAOxOyrUAr38G%2BOA6j3aPYGNUzX7NcEQ6XVJQoVU%2B0Q%2FUkT%2B3bPH%2Fa%2F1QiTfqu7fzmPoUMzaK9upuErbKT3Zz%2FaRZuDPdunUUxFLXQTDvzLkjIgJJxSgQH5KJNtrSG2ibfTXKPivYUbcEmb1awIsD%2FhUTLwE2WPBSileku13X7MbFPbDSWiz3%2FiTjqszL4Bdy7gi0rkCqM5188aipGieWZRdnDWWjTV%2BlTxNFN%2FgKk%2BuZeIiahBtXau38lHKXj22lx8aeLwEC%2BNx7IF0vAtUOzbtZFbsYHKQjzPZ6g1R13PDo9GEhkMUsoolO51ET2v1CwTIAoX4yonOEbVuS%2Ful4tvWlqxVFkts8Qg1jdK9LKcZxMd%2BeMuCpCia%2BdT%2BuGdk3r3sc0BuC0xnF5zgOxexq6xW1A6hc1Tm%2FrhUJzppqORWNGily7R8qITlvPGh3AZoLh%2BJ%2BuCtK39JvhkwtOyFpgY6vwEDo4VFyhjCVrdngBLlP6X1S59cwPb0AXC4g7YhpqNugeEZg2hNLyqrSFxMlnDam5EMfust9x%2F1bFI%2Ft2NeixHQmlSY6bXtiJXUmPY%2Bm%2BC14uveeWySpp2ZFeYSUi1yl1o78B7B7S8KB%2BUu6KNu%2FQ80Xr%2BjYXgHuLSu%2BoJ6pO6YpF6UEja5IXMneKzmTFZkFqm%2FxExfZoufrwkW%2BY%2Bbr1lZRtKOl%2FXeGStGhCPCkXf0dTEFzL1f07zVna6GwhkJ8w%3D%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Date=20230726T193828Z&X-Amz-SignedHeaders=host&X-Amz-Expires=900&X-Amz-Credential=ASIA4CNVNBUUSMD4MC4A%2F20230726%2Fus-east-1%2Ffinspace-apricot%2Faws4_request&X-Amz-Signature=a13aa88ade2cee1b5f71bb4da94d8d1b017601adca89e548c86291dfdea3187d \n", "2 :ip-192-168-5-75.ec2.internal:5000:GATEWAY_basictickdb:Host=ip-192-168-5-75.ec2.internal&Port=5000&User=GATEWAY_basictickdb&Action=finspace%3AConnectKxCluster&X-Amz-Security-Token=IQoJb3JpZ2luX2VjELT%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FwEaCXVzLWVhc3QtMSJIMEYCIQDUrl9KuaLg7uUKtcEpFdf770YBI024i6NQppRuzmKbsgIhAPHt%2Fpdq7bdVXKZ3epOwaBUq9wZSWx31yx3VQhwDr65PKvcCCE0QABoMODI5ODQ1OTk4ODg5Igzh5fk6lck86lrg4TYq1ALag7fx1Kqn06m%2BzYNhEs64LnecDDSxnOmaCqq%2Fr%2BbtKUikWIty2rawg4YXsz6u3gq5sb9aZ7Ynq4GhNy1qrWasiQHSq2khGlQvcSlNiNemUXjGehj1ZVsIZo7QvWmt1YdRLrY%2F8c4GMO8OslrovONLHSnggrk7iFf6lJJhdqsNy8OmF6mMK0g6YkLx3DTJfC6P3%2FiZkqrBR%2FTBlalnD9kckrUvXBC5tRoR6WSvtba2esLfGa9r907Da47Gsu0t2vZtLhm8nIqckV8AJILdEzsw72UbAXBw8cax41zNosqTsXilo93atcnDsF30jor6DwR0MWls0OpkRZ196snJX5TG7ii55ZiydC2oZx%2BfiOfCKqXWzf27ZozRSWsUiVYmhg6U4FflS2BPdbcGQtYt9k%2B66ZwR9N6Y1sET5OzVYDBQ40TNY4sCUOfVFA%2FKny1uB4209V9bMLTshaYGOr4BsdHZLS90vtoseTV1jd2Mtcaxa6qXvySJrJEdFBOSXM7H4S8fpLkKLI8FoZViU8IlGcLNzIQ%2FzLaVn799yKOAy0kvtYp1xZzGw9CBwkdkjclWsLubq%2FSRJdUKDz2RAvNShWGSOgmbVkJFvDYS9oP07YCET8X52vGUOUTjjkAQqa8VkIMqch4qW2GHQXCDuYC3I%2BhgJwBDxaDyQ%2Bs4kYc5VhvnHPNtp3HQli5xi8hwqGaW%2B3aV3JAB1Bb56AAzFQ%3D%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Date=20230726T193828Z&X-Amz-SignedHeaders=host&X-Amz-Expires=900&X-Amz-Credential=ASIA4CNVNBUU2ELVJGLT%2F20230726%2Fus-east-1%2Ffinspace-apricot%2Faws4_request&X-Amz-Signature=4478101265a2e2ee9c97c6658eb9c33f5944b7d7ec7d825b5aa64afe051ae709 " ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "# Connect to the Gateway with PyKX\n", "gw = get_pykx_connection(client, \n", " environmentId=ENV_ID, clusterName=GW_CLUSTER_NAME, \n", " userName=KDB_USERNAME, boto_session=session)\n", "\n", "# reinit the gateway, will re-connect to databases\n", "gw(\"reinit[hdb_name; rdb_name]\")\n", "\n", "# State of connected processes\n", "display( gw(\"select process, handle, connected, address from .conn.procs\").pd() )" ] }, { "cell_type": "markdown", "id": "600173b9-6e31-4d24-aeda-c99fd8b236bd", "metadata": {}, "source": [ "# HDB: Before and After\n", "Dates and counts of the HDB before update and after." ] }, { "cell_type": "code", "execution_count": 13, "id": "1ba998a9-150e-43db-9a50-196d6ec3ad8d", "metadata": {}, "outputs": [], "source": [ "# Query the HDB for after state\n", "hdb = get_pykx_connection(client, \n", " environmentId=ENV_ID, clusterName=HDB_CLUSTER_NAME, \n", " userName=KDB_USERNAME, boto_session=session)\n", "\n", "# Latest Dates and Counts\n", "after_update_pdf = hdb(\"select counts:count i by date from example\").pd()\n", "after_rows = hdb(\"count example\").py()" ] }, { "cell_type": "markdown", "id": "9f1cccb9-56e5-4eca-87a0-0c06655b3a40", "metadata": {}, "source": [ "### Before" ] }, { "cell_type": "code", "execution_count": 14, "id": "b29ad355-bfc9-418d-b829-985b58d00262", "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(before_update_pdf)\n", "\n", "# Number of Rows\n", "print(f\"Rows: {before_rows:,}\")" ] }, { "cell_type": "markdown", "id": "ab5ab6d5-690d-457e-b500-9c5756f34ca6", "metadata": {}, "source": [ "### After" ] }, { "cell_type": "code", "execution_count": 15, "id": "12bd615a-c70c-4da6-aa11-7a5bbf0eedce", "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", " \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
2023-07-263258921
\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\n", "2023-07-26 3258921" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "Rows: 46,320,522\n" ] } ], "source": [ "display(after_update_pdf)\n", "\n", "# Number of Rows\n", "print(f\"Rows: {after_rows:,}\")" ] }, { "cell_type": "code", "execution_count": 16, "id": "b419d15a-08f6-4c90-bbfc-5f2e5433c9c2", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Last Run: 2023-07-26 19:38:29.470993\n" ] } ], "source": [ "print( f\"Last Run: {datetime.datetime.now()}\" )" ] }, { "cell_type": "code", "execution_count": null, "id": "ae4e0c76-e42f-4239-a29e-75721abeff6f", "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 }