ManagedkdbInsights/dbmaint/dbmaint.ipynb (961 lines of code) (raw):
{
"cells": [
{
"cell_type": "markdown",
"id": "d75a16ed-6a5e-425d-a4df-c1b3e7d70d90",
"metadata": {},
"source": [
"# DBMaint\n",
"Will show how to perform some remote dbmaint operations. This notebook will use the two clsuters created in the [create_all](create_all.ipynb) notebook to add a column to a table in the database, then use the second cluster to show the table's state (before and after).\n",
"\n",
"## Outline\n",
"1. Have the dbmaint_cluster_maint cluster load the database \n",
"2. Add a Price column to the table example using the dbmaint function addcol \n",
"3. Commit the changes using the cluster function .aws.commit_kx_database \n",
"4. Update the dataview to present the latest version of the database \n",
"5. Show the before and after state of the table on the query cluster \n",
" a. Schema before updating the dataview \n",
" b. Update the cluster's database (use updated view) \n",
" c. Schema after using the updated view \n",
"\n",
"## Architecture\n",
"<img src=\"images/Deepdive Diagrams-dbmaint.drawio.png\" width=\"50%\">\n",
"\n",
"## References\n",
"[FinSpace DBMaint Documentation](https://docs.aws.amazon.com/finspace/latest/userguide/finspace-managed-kdb-databases-dbmaint.html) \n",
"[DBMaint (github)](https://github.com/KxSystems/kdb/blob/master/utils/dbmaint.md)\n"
]
},
{
"cell_type": "code",
"execution_count": 1,
"id": "fec78d75-7f3e-4176-b039-a9f32e3dcc27",
"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 env import *\n",
"from config import *\n",
"from managed_kx import *\n",
"\n",
"# set q console width and height\n",
"kx.q.system.display_size = [50, 1000]\n",
"\n",
"# ----------------------------------------------------------------"
]
},
{
"cell_type": "code",
"execution_count": 2,
"id": "99c5d160-672c-4e3b-b91f-261a6e77ba68",
"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": "67237f31-44f0-4942-b34b-f334ec306650",
"metadata": {},
"source": [
"# Clusters Used in this Example\n",
"These are the cluseter we will use. the maint cluster makes the changes, the query cluster is used to show the before/after table states once dbamint is performed."
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "f980ee35-ac62-4af8-9186-776f063d6aff",
"metadata": {
"tags": []
},
"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>1</th>\n",
" <td>dbmaint_cluster_maint</td>\n",
" <td>RUNNING</td>\n",
" <td>GP</td>\n",
" <td>None</td>\n",
" <td>[{'key': 's', 'value': '4'}, {'key': 'AWS_ZIP_DEFAULT', 'value': '17,2,6'}]</td>\n",
" <td>dbmaint_cluster_maint cluster created with create_all notebook</td>\n",
" <td>2024-11-26 19:14:13.480000+00:00</td>\n",
" <td>2024-11-26 18:58:06.340000+00:00</td>\n",
" <td>dbmaintdb</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>dbmaint_cluster_query</td>\n",
" <td>RUNNING</td>\n",
" <td>GP</td>\n",
" <td>None</td>\n",
" <td>[{'key': 's', 'value': '4'}, {'key': 'AWS_ZIP_DEFAULT', 'value': '17,2,6'}]</td>\n",
" <td>dbmaint_cluster_query cluster created with create_all notebook</td>\n",
" <td>2024-11-26 19:15:01.724000+00:00</td>\n",
" <td>2024-11-26 18:58:09.368000+00:00</td>\n",
" <td>dbmaintdb</td>\n",
" <td>None</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" clusterName status clusterType capacityConfiguration \\\n",
"1 dbmaint_cluster_maint RUNNING GP None \n",
"2 dbmaint_cluster_query RUNNING GP None \n",
"\n",
" commandLineArguments \\\n",
"1 [{'key': 's', 'value': '4'}, {'key': 'AWS_ZIP_DEFAULT', 'value': '17,2,6'}] \n",
"2 [{'key': 's', 'value': '4'}, {'key': 'AWS_ZIP_DEFAULT', 'value': '17,2,6'}] \n",
"\n",
" clusterDescription \\\n",
"1 dbmaint_cluster_maint cluster created with create_all notebook \n",
"2 dbmaint_cluster_query cluster created with create_all notebook \n",
"\n",
" lastModifiedTimestamp createdTimestamp \\\n",
"1 2024-11-26 19:14:13.480000+00:00 2024-11-26 18:58:06.340000+00:00 \n",
"2 2024-11-26 19:15:01.724000+00:00 2024-11-26 18:58:09.368000+00:00 \n",
"\n",
" databaseName cacheConfigurations \n",
"1 dbmaintdb None \n",
"2 dbmaintdb None "
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"cdf = get_clusters(client, environmentId=ENV_ID)\n",
"\n",
"if cdf is not None:\n",
" cdf = cdf[cdf['clusterName'].isin(all_clusters)]\n",
"\n",
"display(cdf)"
]
},
{
"cell_type": "markdown",
"id": "ecc0438b-3b4c-48f9-b6f6-ce3838c0a799",
"metadata": {},
"source": [
"# Add a Price Column\n",
"Using a dbmaint function (addcol), add a column to the example table of the database."
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "f027f5ee-aa0b-490e-9cc8-0d9bdeb6f4f3",
"metadata": {
"tags": []
},
"outputs": [],
"source": [
"# connection to dbmaint cluster\n",
"conn = get_pykx_connection(client, \n",
" environmentId=ENV_ID, clusterName=MAINT_CLUSTER_NAME, \n",
" userName=KDB_USERNAME, boto_session=session)"
]
},
{
"cell_type": "code",
"execution_count": 5,
"id": "03e4fcaa-4dd2-457e-b797-ba0e2a208e9d",
"metadata": {
"tags": []
},
"outputs": [],
"source": [
"# get the connection string to the cluster\n",
"conn_str = get_kx_connection_string(client, \n",
" environmentId=ENV_ID, clusterName=MAINT_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": "75e20401-49cd-479f-8fd1-260afc130808",
"metadata": {},
"source": [
"## Current Schema\n",
"This is the schema of the example table before the column is added."
]
},
{
"cell_type": "code",
"execution_count": 6,
"id": "dc04b437-ac53-4f50-b351-21c4684359ac",
"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",
"/ load the database\n",
".Q.l `$.aws.akdbp, \"/\", .aws.akdb\n",
"\n",
"/ before schema\n",
"meta example"
]
},
{
"cell_type": "markdown",
"id": "c5cfcfa1-bf5c-44bb-830d-c318cda42d9b",
"metadata": {},
"source": [
"## Add a Price Column\n",
"Using the addcol function from dbmaint, add a price column to the table, set the default value of price to 0. \n",
"\n",
".aws.akdbp: database path \n",
".aws.akdb: database name. "
]
},
{
"cell_type": "code",
"execution_count": 7,
"id": "bf88e33a-f8aa-410c-b872-a7502e557259",
"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",
"price | h \n"
]
}
],
"source": [
"%%q --host $host --port $port --user $username --pass $password\n",
"\\cd /opt/kx/app/db\n",
"\n",
"/ add the column\n",
"addcol[`:dbmaintdb;`example;`price;0h]\n",
"\n",
"meta example"
]
},
{
"cell_type": "markdown",
"id": "77939905-7632-4061-8c4e-ee2aff80f428",
"metadata": {},
"source": [
"## Commit Changes\n",
"Using the .aws.commit_kx_database function (available from kdb clusters), commit changes made to the table to the managed database. This function will sweep the local database for all changes and add them to the managed database as one changeset. "
]
},
{
"cell_type": "code",
"execution_count": 8,
"id": "5e037c02-8fbd-4eb4-8a12-bdd1030f85db",
"metadata": {
"tags": []
},
"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",
"Status is IN_PROGRESS, total wait 0:00:50, waiting 10 sec ...\n",
"** Done **\n"
]
}
],
"source": [
"# commit changes, use pykx so we can capture the new chanegtset ID and poll for its status\n",
"res = conn(f'.aws.commit_kx_database[\"{DB_NAME}\"]')\n",
"\n",
"# was there an error or not?\n",
"CHANGESET_ID = res.get(\"id\", None)\n",
"\n",
"if CHANGESET_ID is None:\n",
" display(res)\n",
"else:\n",
" # get the changeset, wait for it to finish loading\n",
" CHANGESET_ID = str(CHANGESET_ID)\n",
" # wait for ingestion of changeset\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": "ba52f826-07aa-4cf8-b70f-ed4c87534d12",
"metadata": {
"tags": []
},
"source": [
"## DB Schema on Query Cluster\n",
"Al this while, the query cluster has been serviing up the database before changes (static view, initial changeset/version). \n",
"This is the schema still on the query cluster, this is before updating to the new changes."
]
},
{
"cell_type": "code",
"execution_count": 9,
"id": "e200e18a-8132-42c0-8539-4e66da88a613",
"metadata": {
"tags": []
},
"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>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>number</th>\n",
" <td>b'j'</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",
"number b'j' "
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# Connect to the query cluster\n",
"conn = get_pykx_connection(client, \n",
" environmentId=ENV_ID, clusterName=QUERY_CLUSTER_NAME, \n",
" userName=KDB_USERNAME, boto_session=session)\n",
"\n",
"# schema of table in query cluster\n",
"display( conn(\"meta example\").pd() )"
]
},
{
"cell_type": "markdown",
"id": "86210008-738f-492e-98e2-3f2d6ffbc9a1",
"metadata": {},
"source": [
"## Update Query Cluster's Dataview\n",
"Now update the query cluster's dataview to the latest changeset_id added from the dbmaint cluster. This new version of the database will include the change made to the example table. \n",
"\n",
"When updating the clsuter to the new version (changeset_id) there are two steps to execute: \n",
"1. Update the dataview to use the new version\n",
"2. Update the database/dataview used by the query cluster."
]
},
{
"cell_type": "code",
"execution_count": 10,
"id": "4d4c8604-0c1e-4659-8d08-f0c6dbb21368",
"metadata": {
"tags": []
},
"outputs": [],
"source": [
"# update the query cluster's dataview to include the newly added changeset.\n",
"resp = client.update_kx_dataview(environmentId=ENV_ID, \n",
" databaseName=DB_NAME, \n",
" dataviewName=QUERY_DBVIEW_NAME, \n",
" changesetId=CHANGESET_ID, \n",
" segmentConfigurations=[\n",
" {'dbPaths': ['/*'], 'volumeName': VOLUME_NAME}\n",
" ]\n",
")"
]
},
{
"cell_type": "code",
"execution_count": 11,
"id": "cc9c62d6-4dfc-4df3-8756-29279dc02e77",
"metadata": {
"tags": []
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Dataview: dbmaintdb_DBVIEW_QUERY status is UPDATING, total wait 0:00:00, waiting 30 sec ...\n",
"Dataview: dbmaintdb_DBVIEW_QUERY status is UPDATING, total wait 0:00:30, waiting 30 sec ...\n",
"Dataview: dbmaintdb_DBVIEW_QUERY status is UPDATING, total wait 0:01:00, waiting 30 sec ...\n",
"Dataview: dbmaintdb_DBVIEW_QUERY status is UPDATING, total wait 0:01:30, waiting 30 sec ...\n",
"Dataview: dbmaintdb_DBVIEW_QUERY status is UPDATING, total wait 0:02:00, waiting 30 sec ...\n",
"Dataview: dbmaintdb_DBVIEW_QUERY status is UPDATING, total wait 0:02:30, waiting 30 sec ...\n",
"Dataview: dbmaintdb_DBVIEW_QUERY status is UPDATING, total wait 0:03:00, waiting 30 sec ...\n",
"Dataview: dbmaintdb_DBVIEW_QUERY status is UPDATING, total wait 0:03:30, waiting 30 sec ...\n",
"Dataview: dbmaintdb_DBVIEW_QUERY status is UPDATING, total wait 0:04:00, waiting 30 sec ...\n",
"Dataview: dbmaintdb_DBVIEW_QUERY status is UPDATING, total wait 0:04:30, waiting 30 sec ...\n",
"Dataview: dbmaintdb_DBVIEW_QUERY status is UPDATING, total wait 0:05:00, waiting 30 sec ...\n",
"Dataview: dbmaintdb_DBVIEW_QUERY status is UPDATING, total wait 0:05:30, waiting 30 sec ...\n",
"Dataview: dbmaintdb_DBVIEW_QUERY status is now ACTIVE, total wait 0:06:00\n"
]
},
{
"data": {
"text/plain": [
"{'databaseName': 'dbmaintdb',\n",
" 'dataviewName': 'dbmaintdb_DBVIEW_QUERY',\n",
" 'azMode': 'SINGLE',\n",
" 'availabilityZoneId': 'use1-az6',\n",
" 'changesetId': 'qMm081xLWeumK4WzmV0Rhg',\n",
" 'segmentConfigurations': [{'dbPaths': ['/*'],\n",
" 'volumeName': 'DBMAINT_VOLUME',\n",
" 'onDemand': False}],\n",
" 'activeVersions': [{'changesetId': 'qMm081xLWeumK4WzmV0Rhg',\n",
" 'segmentConfigurations': [{'dbPaths': ['/*'],\n",
" 'volumeName': 'DBMAINT_VOLUME',\n",
" 'onDemand': False}],\n",
" 'attachedClusters': [],\n",
" 'createdTimestamp': datetime.datetime(2024, 11, 26, 19, 16, 53, 9000, tzinfo=tzlocal()),\n",
" 'versionId': 'hsm089pIdULeLPmBDohHUQ'},\n",
" {'changesetId': 'Lsm05H0USQBBNX8sIskNPA',\n",
" 'segmentConfigurations': [{'dbPaths': ['/*'],\n",
" 'volumeName': 'DBMAINT_VOLUME',\n",
" 'onDemand': False}],\n",
" 'attachedClusters': ['dbmaint_cluster_query'],\n",
" 'createdTimestamp': datetime.datetime(2024, 11, 26, 18, 51, 26, 797000, tzinfo=tzlocal()),\n",
" 'versionId': 'nMm06DVmwEYdD8ON3EBYkQ'}],\n",
" 'description': 'Dataview of database dbmaintdb',\n",
" 'autoUpdate': False,\n",
" 'readWrite': False,\n",
" 'environmentId': 'jlcenjvtkgzrdek2qqv7ic',\n",
" 'createdTimestamp': datetime.datetime(2024, 11, 26, 18, 51, 26, 684000, tzinfo=tzlocal()),\n",
" 'lastModifiedTimestamp': datetime.datetime(2024, 11, 26, 19, 22, 37, 973000, tzinfo=tzlocal()),\n",
" 'status': 'ACTIVE'}"
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Dataview will go from UPDATING to ACTIVE once updates are complete\n",
"wait_for_dataview_status(client=client, environmentId=ENV_ID, databaseName=DB_NAME, dataviewName=QUERY_DBVIEW_NAME, show_wait=True)"
]
},
{
"cell_type": "code",
"execution_count": 12,
"id": "380e6130-1e8a-4c74-9240-e382222cc82a",
"metadata": {
"tags": []
},
"outputs": [],
"source": [
"# Update the database/dataview on the cluster to use the new one\n",
"resp=client.update_kx_cluster_databases(environmentId=ENV_ID, \n",
" clusterName=QUERY_CLUSTER_NAME, \n",
" databases=[\n",
" {'databaseName': DB_NAME, 'dataviewName': QUERY_DBVIEW_NAME}\n",
" ],\n",
" deploymentConfiguration={\n",
" 'deploymentStrategy': 'NO_RESTART'\n",
" }\n",
")"
]
},
{
"cell_type": "code",
"execution_count": 13,
"id": "5e1e2139-2c8e-4ad7-8200-bf81c9d1eeb8",
"metadata": {
"tags": []
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Cluster: dbmaint_cluster_query status is UPDATING, total wait 0:00:00, waiting 30 sec ...\n",
"Cluster: dbmaint_cluster_query status is UPDATING, total wait 0:00:30, waiting 30 sec ...\n",
"Cluster: dbmaint_cluster_query status is now RUNNING, total wait 0:01:00\n"
]
},
{
"data": {
"text/plain": [
"{'status': 'RUNNING',\n",
" 'clusterName': 'dbmaint_cluster_query',\n",
" 'clusterType': 'GP',\n",
" 'volumes': [{'volumeName': 'DBMAINT_VOLUME', 'volumeType': 'NAS_1'}],\n",
" 'databases': [{'databaseName': 'dbmaintdb',\n",
" 'dataviewConfiguration': {'dataviewName': 'dbmaintdb_DBVIEW_QUERY',\n",
" 'dataviewVersionId': 'hsm089pIdULeLPmBDohHUQ',\n",
" 'changesetId': 'qMm081xLWeumK4WzmV0Rhg',\n",
" 'segmentConfigurations': [{'dbPaths': ['/*'],\n",
" 'volumeName': 'DBMAINT_VOLUME',\n",
" 'onDemand': False}]}}],\n",
" 'clusterDescription': 'dbmaint_cluster_query cluster created with create_all notebook',\n",
" 'releaseLabel': '1.0',\n",
" 'vpcConfiguration': {'vpcId': 'vpc-0fe2b9c50f3ad382f',\n",
" 'securityGroupIds': ['sg-0c99f1cfb9c3c7fd9'],\n",
" 'subnetIds': ['subnet-04052219ec25b062b'],\n",
" 'ipAddressType': 'IP_V4'},\n",
" 'initializationScript': 'initdb.q',\n",
" 'commandLineArguments': [{'key': 's', 'value': '4'},\n",
" {'key': 'AWS_ZIP_DEFAULT', 'value': '17,2,6'}],\n",
" 'code': {'s3Bucket': 'kdb-demo-829845998889-kms',\n",
" 's3Key': 'code/dbmaint.zip'},\n",
" 'executionRole': 'arn:aws:iam::829845998889:role/kdb-all-user',\n",
" 'lastModifiedTimestamp': datetime.datetime(2024, 11, 26, 19, 23, 57, 643000, tzinfo=tzlocal()),\n",
" 'azMode': 'SINGLE',\n",
" 'availabilityZoneId': 'use1-az6',\n",
" 'createdTimestamp': datetime.datetime(2024, 11, 26, 18, 58, 9, 368000, tzinfo=tzlocal()),\n",
" 'scalingGroupConfiguration': {'scalingGroupName': 'SCALING_GROUP_dbmaint',\n",
" 'memoryReservation': 6,\n",
" 'nodeCount': 1}}"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Cluster will go from UPDATING to RUNNING once updates are complete\n",
"wait_for_cluster_status(client, environmentId=ENV_ID, clusterName=QUERY_CLUSTER_NAME, show_wait=True)"
]
},
{
"cell_type": "markdown",
"id": "cbf73690-f95d-45c4-89fb-77d92c446d75",
"metadata": {},
"source": [
"## Query Cluster with Latest Schema\n",
"Now demonstrate the query cluster has the updated schema of the example table."
]
},
{
"cell_type": "code",
"execution_count": 14,
"id": "cae934df-2772-4eed-8958-b654c86aeeba",
"metadata": {
"tags": []
},
"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>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>number</th>\n",
" <td>b'j'</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>price</th>\n",
" <td>b'h'</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",
"number b'j' \n",
"price b'h' "
]
},
"metadata": {},
"output_type": "display_data"
},
{
"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>date</th>\n",
" <th>sym</th>\n",
" <th>time</th>\n",
" <th>number</th>\n",
" <th>price</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2024-11-16</td>\n",
" <td>aaa</td>\n",
" <td>2024-11-16 14:44:14.737</td>\n",
" <td>53231</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2024-11-16</td>\n",
" <td>aaa</td>\n",
" <td>2024-11-16 14:44:14.737</td>\n",
" <td>153560</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2024-11-16</td>\n",
" <td>aaa</td>\n",
" <td>2024-11-16 14:44:14.737</td>\n",
" <td>449428</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>2024-11-16</td>\n",
" <td>aaa</td>\n",
" <td>2024-11-16 14:44:14.737</td>\n",
" <td>631966</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>2024-11-16</td>\n",
" <td>aaa</td>\n",
" <td>2024-11-16 14:44:14.737</td>\n",
" <td>941566</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9999995</th>\n",
" <td>2024-11-25</td>\n",
" <td>ppp</td>\n",
" <td>2024-11-25 14:44:00.926</td>\n",
" <td>946617</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9999996</th>\n",
" <td>2024-11-25</td>\n",
" <td>ppp</td>\n",
" <td>2024-11-25 14:44:00.926</td>\n",
" <td>249468</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9999997</th>\n",
" <td>2024-11-25</td>\n",
" <td>ppp</td>\n",
" <td>2024-11-25 14:44:00.926</td>\n",
" <td>634620</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9999998</th>\n",
" <td>2024-11-25</td>\n",
" <td>ppp</td>\n",
" <td>2024-11-25 14:44:00.926</td>\n",
" <td>855402</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9999999</th>\n",
" <td>2024-11-25</td>\n",
" <td>ppp</td>\n",
" <td>2024-11-25 14:44:00.926</td>\n",
" <td>168336</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>10000000 rows × 5 columns</p>\n",
"</div>"
],
"text/plain": [
" date sym time number price\n",
"0 2024-11-16 aaa 2024-11-16 14:44:14.737 53231 0\n",
"1 2024-11-16 aaa 2024-11-16 14:44:14.737 153560 0\n",
"2 2024-11-16 aaa 2024-11-16 14:44:14.737 449428 0\n",
"3 2024-11-16 aaa 2024-11-16 14:44:14.737 631966 0\n",
"4 2024-11-16 aaa 2024-11-16 14:44:14.737 941566 0\n",
"... ... ... ... ... ...\n",
"9999995 2024-11-25 ppp 2024-11-25 14:44:00.926 946617 0\n",
"9999996 2024-11-25 ppp 2024-11-25 14:44:00.926 249468 0\n",
"9999997 2024-11-25 ppp 2024-11-25 14:44:00.926 634620 0\n",
"9999998 2024-11-25 ppp 2024-11-25 14:44:00.926 855402 0\n",
"9999999 2024-11-25 ppp 2024-11-25 14:44:00.926 168336 0\n",
"\n",
"[10000000 rows x 5 columns]"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# Re-Connect to the query cluster\n",
"conn = get_pykx_connection(client, \n",
" environmentId=ENV_ID, clusterName=QUERY_CLUSTER_NAME, \n",
" userName=KDB_USERNAME, boto_session=session)\n",
"\n",
"# re-load the database\n",
"conn('.Q.l `$.aws.akdbp,\"/\",.aws.akdb')\n",
"\n",
"# new schema in query\n",
"display( conn(\"meta example\").pd() )\n",
"\n",
"# sample\n",
"display( conn(\"select from example\").pd() )\n"
]
},
{
"cell_type": "code",
"execution_count": 15,
"id": "39da58ac-d6de-48f5-a18b-5cb573daea11",
"metadata": {
"tags": []
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Last Run: 2024-11-26 19:24:03.661745\n"
]
}
],
"source": [
"print( f\"Last Run: {datetime.datetime.now()}\" )"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "56547de3-8323-45dd-bd8d-0523c6f27db3",
"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
}