{ "cells": [ { "cell_type": "markdown", "id": "28bea13b-67bd-4a0e-8eab-3b8ffd37259e", "metadata": {}, "source": [ "# Welcome Notebook\n", "This notebook walks through the process of creating and populating your first database with FinSpace Managed KX.\n", "\n", "## Before you start\n", "Before you start this notebook, it is assumed you have the following:\n", "- FinSpace Managed KX environment created in AWS account\n", "- S3 staging bucket for data and code\n", " - This notebook boto's profile and the Managed KX environment can access the bucket\n", "- Setup in ~/.aws directory\n", " - config is set (json and region)\n", " - default credentials are set (aws_access_key_id, aws_secret_access_key, aws_session_token)\n", "\n", "## Steps\n", "1. Untar hdb.tar.gz for the hdb data\n", "2. Upload hdb to staging S3 bucket\n", "3. Create database\n", "4. Add HDB data to database\n", "5. Create a Cluster\n", "6. Get the connectionString\n", "7. Query Cluster using PyKX\n", "\n", "## Managed kdb Insights Archtecture\n", "\n" ] }, { "cell_type": "code", "execution_count": 1, "id": "d9d543f3-1cd5-4a0e-8be7-a9eb0ac35878", "metadata": {}, "outputs": [], "source": [ "import os\n", "import boto3\n", "import json\n", "import datetime\n", "\n", "from managed_kx import *\n", "from env_2 import *\n" ] }, { "cell_type": "code", "execution_count": 2, "id": "d5265616-6aa4-4b7b-b038-8e26e71d19e7", "metadata": {}, "outputs": [], "source": [ "# Source data directory\n", "SOURCE_DATA_DIR=\"hdb\"\n", "\n", "# S3 bucket for external data and code\n", "S3_DEST=f\"s3://{S3_BUCKET}/data/{SOURCE_DATA_DIR}/\"\n", "CODEBASE=\"code\"\n", "CODE_PATH=f\"code/{CODEBASE}.zip\"\n", "\n", "# Managed KX Database and Cluster names to create\n", "DB_NAME=\"welcomedb\"\n", "DELETE_CLUSTER=False\n", "DELETE_DATABASE=False\n", "\n", "create_delete=True\n", "\n", "if create_delete:\n", " TODAY=datetime.datetime.now().strftime(\"%Y%m%d_%H%M\") \n", " DB_NAME=f\"create_delete_db_{TODAY}\"\n", " DELETE_CLUSTER=True\n", " DELETE_DATABASE=True\n", "\n", "CLUSTER_NAME=f\"cluster_{DB_NAME}\"\n" ] }, { "cell_type": "code", "execution_count": 3, "id": "7e054ddd-3313-4ac3-b0b3-3c93b55e977e", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Using variables ...\n" ] } ], "source": [ "# triggers credential get\n", "session=None\n", "\n", "try:\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": "849f954c-7cfa-4b29-be4f-0854aa7cbd06", "metadata": {}, "source": [ "# 0. Environment Check\n", "Be sure the infrastructure ID has been entitled to the bucket you will be staging the HDB to. The environment will also need access to the KMX key used when creating the environment.\n", "\n", "## Permission Templates\n", "\n", "### S3 Permission\n", "Example of code and data access to the same S3 bucket.\n", "\n", "```\n", "{\n", " \"Version\": \"2012-10-17\",\n", " \"Statement\": [\n", " {\n", " \"Effect\": \"Allow\",\n", " \"Principal\": {\n", " \"Service\": \"finspace.amazonaws.com\"\n", " },\n", " \"Action\": [\n", " \"s3:GetObject\",\n", " \"s3:GetObjectTagging\",\n", " \"s3:ListBucket\"\n", " ],\n", " \"Resource\": [\n", " \"arn:aws:s3:::S3_BUCKET/*\",\n", " \"arn:aws:s3:::S3_BUCKET\"\n", " ],\n", " \"Condition\": {\n", " \"StringEquals\": {\n", " \"aws:SourceAccount\": \"ACCOUNT_ID\"\n", " },\n", " \"ArnEquals\": {\n", " \"aws:SourceArn\": \"arn:aws:finspace:us-east-1:ACCOUNT_ID:kxEnvironment/ENV_ID/*\"\n", " }\n", " }\n", " }\n", " ]\n", "}\n", "\n", "```\n", "\n", "### KMS Key\n", "Be sure the environment has access to use the KMS key given in environment creation.\n", "\n", "```\n", "\"Statement\": [\n", " {\n", " \"Sid\": \"Enable Managed kdb Insights Access\",\n", " \"Effect\": \"Allow\",\n", " \"Principal\": {\n", " \"Service\": \"finspace.amazonaws.com\"\n", " },\n", " \"Action\": [\n", " \"kms:Encrypt\",\n", " \"kms:Decrypt\",\n", " \"kms:GenerateDataKey\"\n", " ],\n", " \"Resource\": \"arn:aws:kms:us-east-1:ACCOUNT_ID:key/KEY_ID\",\n", " \"Condition\": {\n", " \"StringEquals\": {\n", " \"aws:SourceAccount\": \"ACCOUNT_ID\"\n", " },\n", " \"ArnLike\": {\n", " \"aws:SourceArn\": \"arn:aws:finspace:us-east-1:ACCOUNT_ID:kxEnvironment/ENV_ID/*\"\n", " }\n", " }\n", " }\n", " ]\n", "```\n" ] }, { "cell_type": "code", "execution_count": 4, "id": "9d218119-1aa3-4485-a940-5dcde32b3fc7", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Environment Information\n", "{\n", " \"availabilityZoneIds\": [\n", " \"use1-az4\",\n", " \"use1-az1\",\n", " \"use1-az6\"\n", " ],\n", " \"awsAccountId\": \"612841383594\",\n", " \"certificateAuthorityArn\": \"arn:aws:acm-pca:us-east-1:356945598660:certificate-authority/64c67020-83e7-468b-ba7c-ebe23effde41\",\n", " \"creationTimestamp\": \"2023-06-06 01:01:40.400000+00:00\",\n", " \"dedicatedServiceAccountId\": \"356945598660\",\n", " \"description\": \"Managed kdb Insights environment\",\n", " \"dnsStatus\": \"NONE\",\n", " \"environmentArn\": \"arn:aws:finspace:us-east-1:612841383594:kxEnvironment/stceohfhtzkcdw4vyhodsi\",\n", " \"environmentId\": \"stceohfhtzkcdw4vyhodsi\",\n", " \"kmsKeyId\": \"arn:aws:kms:us-east-1:612841383594:key/bbfad1fa-9e38-47f1-986d-33fb976a9ec4\",\n", " \"name\": \"Managed_kdb_20230606\",\n", " \"status\": \"CREATED\",\n", " \"tgwStatus\": \"NONE\",\n", " \"updateTimestamp\": \"2023-06-06 01:12:31.310000+00:00\"\n", "}\n" ] } ], "source": [ "resp=get_kx_environment(client, environmentId=ENV_ID)\n", "\n", "print(\"Environment Information\")\n", "print(json.dumps(resp,sort_keys=True,indent=4,default=str))\n" ] }, { "cell_type": "markdown", "id": "bc29d8fc-c234-4c65-a633-bb9e16d6a772", "metadata": {}, "source": [ "## 1. Untar hdb.tar.gz\n", "hdb database will be found in hdb directory" ] }, { "cell_type": "code", "execution_count": 5, "id": "157b75f5-b582-490e-ae17-eb14eaafa21e", "metadata": {}, "outputs": [], "source": [ "!tar -xf hdb.tar.gz" ] }, { "cell_type": "code", "execution_count": 6, "id": "3fec4ecf-cba3-440f-a56e-4ec726c9f8a8", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "total 52\n", "drwxr-xr-x. 12 ec2-user ec2-user 16384 Apr 24 23:17 .\n", "drwxr-xr-x. 7 ec2-user ec2-user 16384 Jun 6 14:09 ..\n", "drwxr-xr-x. 3 ec2-user ec2-user 21 Apr 24 23:17 2023.04.14\n", "drwxr-xr-x. 3 ec2-user ec2-user 21 Apr 24 23:17 2023.04.15\n", "drwxr-xr-x. 3 ec2-user ec2-user 21 Apr 24 23:17 2023.04.16\n", "drwxr-xr-x. 3 ec2-user ec2-user 21 Apr 24 23:17 2023.04.17\n", "drwxr-xr-x. 3 ec2-user ec2-user 21 Apr 24 23:17 2023.04.18\n", "drwxr-xr-x. 3 ec2-user ec2-user 21 Apr 24 23:17 2023.04.19\n", "drwxr-xr-x. 3 ec2-user ec2-user 21 Apr 24 23:17 2023.04.20\n", "drwxr-xr-x. 3 ec2-user ec2-user 21 Apr 24 23:17 2023.04.21\n", "drwxr-xr-x. 3 ec2-user ec2-user 21 Apr 24 23:17 2023.04.22\n", "drwxr-xr-x. 3 ec2-user ec2-user 21 Apr 24 23:17 2023.04.23\n", "-rw-r--r--. 1 ec2-user ec2-user 16392 Apr 24 23:17 sym\n" ] } ], "source": [ "!ls -la hdb" ] }, { "cell_type": "markdown", "id": "b3c8cbbe-654e-4385-92bc-5c7b80b5f0f3", "metadata": {}, "source": [ "# 2. Upload hdb data\n", "using aws cli, copy hdb to staging bucket" ] }, { "cell_type": "code", "execution_count": 7, "id": "af169292-13fc-4b1b-863d-789d5a042d52", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " PRE 2023.01.29/\n", " PRE 2023.01.30/\n", " PRE 2023.01.31/\n", " PRE 2023.02.01/\n", " PRE 2023.02.02/\n", " PRE 2023.02.03/\n", " PRE 2023.02.04/\n", " PRE 2023.02.05/\n", " PRE 2023.02.06/\n", " PRE 2023.02.07/\n", " PRE 2023.04.14/\n", " PRE 2023.04.15/\n", " PRE 2023.04.16/\n", " PRE 2023.04.17/\n", " PRE 2023.04.18/\n", " PRE 2023.04.19/\n", " PRE 2023.04.20/\n", " PRE 2023.04.21/\n", " PRE 2023.04.22/\n", " PRE 2023.04.23/\n", "2023-04-27 17:52:41 16392 sym\n" ] }, { "data": { "text/plain": [ "0" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "if AWS_ACCESS_KEY_ID is not None:\n", " cp = f\"\"\"\n", "export AWS_ACCESS_KEY_ID={AWS_ACCESS_KEY_ID}\n", "export AWS_SECRET_ACCESS_KEY={AWS_SECRET_ACCESS_KEY}\n", "export AWS_SESSION_TOKEN={AWS_SESSION_TOKEN}\n", "\n", "aws s3 sync --exclude .DS_Store {SOURCE_DATA_DIR} {S3_DEST}\n", "aws s3 ls {S3_DEST}\n", "\"\"\"\n", "else:\n", " cp = f\"\"\"\n", "aws s3 sync --exclude .DS_Store {SOURCE_DATA_DIR} {S3_DEST}\n", "aws s3 ls {S3_DEST}\n", "\"\"\"\n", " \n", "# execute the S3 copy\n", "os.system(cp)" ] }, { "cell_type": "markdown", "id": "67476efe-d308-4158-9e24-8fbe71509f76", "metadata": {}, "source": [ "## 3. Create database" ] }, { "cell_type": "code", "execution_count": 8, "id": "83d00c39-876a-4bba-ab66-a3aa4fb9b65a", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "CREATING Database: create_delete_db_20230606_1409\n", "CREATED Database: create_delete_db_20230606_1409\n", "{\n", " \"createdTimestamp\": \"2023-06-06 14:09:57.071000+00:00\",\n", " \"databaseArn\": \"arn:aws:finspace:us-east-1:612841383594:kxEnvironment/stceohfhtzkcdw4vyhodsi/kxDatabase/create_delete_db_20230606_1409\",\n", " \"databaseName\": \"create_delete_db_20230606_1409\",\n", " \"description\": \"Welcome kdb database\",\n", " \"environmentId\": \"stceohfhtzkcdw4vyhodsi\",\n", " \"lastModifiedTimestamp\": \"2023-06-06 14:09:57.071000+00:00\"\n", "}\n" ] } ], "source": [ "# assume it exists\n", "create_db=False\n", "\n", "try:\n", " resp = client.get_kx_database(environmentId=ENV_ID, databaseName=DB_NAME)\n", " resp.pop('ResponseMetadata', None)\n", "except:\n", " # does not exist, will create\n", " create_db=True\n", "\n", "if create_db:\n", " print(f\"CREATING Database: {DB_NAME}\")\n", " resp = client.create_kx_database(environmentId=ENV_ID, databaseName=DB_NAME, description=\"Welcome kdb database\")\n", " resp.pop('ResponseMetadata', None)\n", "\n", " print(f\"CREATED Database: {DB_NAME}\")\n", "\n", "print(json.dumps(resp,sort_keys=True,indent=4,default=str))" ] }, { "cell_type": "markdown", "id": "a41c84b3-2243-4abb-9032-8ae77a5e31f7", "metadata": {}, "source": [ "## 4. Add HDB data to database" ] }, { "cell_type": "code", "execution_count": 9, "id": "60b3a8df-c7ed-4837-99e3-07d95e7fbac0", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Changeset...\n", "{\n", " \"changeRequests\": [\n", " {\n", " \"changeType\": \"PUT\",\n", " \"dbPath\": \"/2023.04.23/\",\n", " \"s3Path\": \"s3://kdb-demo-612841383594-kms/data/hdb/2023.04.23/\"\n", " },\n", " {\n", " \"changeType\": \"PUT\",\n", " \"dbPath\": \"/2023.04.15/\",\n", " \"s3Path\": \"s3://kdb-demo-612841383594-kms/data/hdb/2023.04.15/\"\n", " },\n", " {\n", " \"changeType\": \"PUT\",\n", " \"dbPath\": \"/2023.04.14/\",\n", " \"s3Path\": \"s3://kdb-demo-612841383594-kms/data/hdb/2023.04.14/\"\n", " },\n", " {\n", " \"changeType\": \"PUT\",\n", " \"dbPath\": \"/2023.04.22/\",\n", " \"s3Path\": \"s3://kdb-demo-612841383594-kms/data/hdb/2023.04.22/\"\n", " },\n", " {\n", " \"changeType\": \"PUT\",\n", " \"dbPath\": \"/2023.04.18/\",\n", " \"s3Path\": \"s3://kdb-demo-612841383594-kms/data/hdb/2023.04.18/\"\n", " },\n", " {\n", " \"changeType\": \"PUT\",\n", " \"dbPath\": \"/2023.04.20/\",\n", " \"s3Path\": \"s3://kdb-demo-612841383594-kms/data/hdb/2023.04.20/\"\n", " },\n", " {\n", " \"changeType\": \"PUT\",\n", " \"dbPath\": \"/2023.04.16/\",\n", " \"s3Path\": \"s3://kdb-demo-612841383594-kms/data/hdb/2023.04.16/\"\n", " },\n", " {\n", " \"changeType\": \"PUT\",\n", " \"dbPath\": \"/2023.04.17/\",\n", " \"s3Path\": \"s3://kdb-demo-612841383594-kms/data/hdb/2023.04.17/\"\n", " },\n", " {\n", " \"changeType\": \"PUT\",\n", " \"dbPath\": \"/2023.04.21/\",\n", " \"s3Path\": \"s3://kdb-demo-612841383594-kms/data/hdb/2023.04.21/\"\n", " },\n", " {\n", " \"changeType\": \"PUT\",\n", " \"dbPath\": \"/2023.04.19/\",\n", " \"s3Path\": \"s3://kdb-demo-612841383594-kms/data/hdb/2023.04.19/\"\n", " },\n", " {\n", " \"changeType\": \"PUT\",\n", " \"dbPath\": \"/\",\n", " \"s3Path\": \"s3://kdb-demo-612841383594-kms/data/hdb/sym\"\n", " }\n", " ],\n", " \"changesetId\": \"AMRIhYEM3NlF6o9QdE3i6A\",\n", " \"createdTimestamp\": \"2023-06-06 14:09:57.787000+00:00\",\n", " \"databaseName\": \"create_delete_db_20230606_1409\",\n", " \"environmentId\": \"stceohfhtzkcdw4vyhodsi\",\n", " \"lastModifiedTimestamp\": \"2023-06-06 14:09:57.787000+00:00\",\n", " \"status\": \"PENDING\"\n", "}\n" ] } ], "source": [ "changes=[]\n", "\n", "for f in os.listdir(\"hdb\"):\n", " if os.path.isdir(f\"hdb/{f}\"):\n", " changes.append( { 'changeType': 'PUT', 's3Path': f\"{S3_DEST}{f}/\", 'dbPath': f\"/{f}/\" } )\n", " else:\n", " changes.append( { 'changeType': 'PUT', 's3Path': f\"{S3_DEST}{f}\", 'dbPath': f\"/\" } )\n", " \n", "resp = client.create_kx_changeset(environmentId=ENV_ID, databaseName=DB_NAME, \n", " changeRequests=changes)\n", "\n", "resp.pop('ResponseMetadata', None)\n", "changeset_id = resp['changesetId']\n", "\n", "print(\"Changeset...\")\n", "print(json.dumps(resp,sort_keys=True,indent=4,default=str))" ] }, { "cell_type": "code", "execution_count": 10, "id": "4b344419-1261-43a3-89aa-f682ec54b0b2", "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" ] }, { "data": { "text/plain": [ "{'changesetId': 'AMRIhYEM3NlF6o9QdE3i6A',\n", " 'databaseName': 'create_delete_db_20230606_1409',\n", " 'environmentId': 'stceohfhtzkcdw4vyhodsi',\n", " 'changeRequests': [{'changeType': 'PUT',\n", " 's3Path': 's3://kdb-demo-612841383594-kms/data/hdb/2023.04.23/',\n", " 'dbPath': '/2023.04.23/'},\n", " {'changeType': 'PUT',\n", " 's3Path': 's3://kdb-demo-612841383594-kms/data/hdb/2023.04.15/',\n", " 'dbPath': '/2023.04.15/'},\n", " {'changeType': 'PUT',\n", " 's3Path': 's3://kdb-demo-612841383594-kms/data/hdb/2023.04.14/',\n", " 'dbPath': '/2023.04.14/'},\n", " {'changeType': 'PUT',\n", " 's3Path': 's3://kdb-demo-612841383594-kms/data/hdb/2023.04.22/',\n", " 'dbPath': '/2023.04.22/'},\n", " {'changeType': 'PUT',\n", " 's3Path': 's3://kdb-demo-612841383594-kms/data/hdb/2023.04.18/',\n", " 'dbPath': '/2023.04.18/'},\n", " {'changeType': 'PUT',\n", " 's3Path': 's3://kdb-demo-612841383594-kms/data/hdb/2023.04.20/',\n", " 'dbPath': '/2023.04.20/'},\n", " {'changeType': 'PUT',\n", " 's3Path': 's3://kdb-demo-612841383594-kms/data/hdb/2023.04.16/',\n", " 'dbPath': '/2023.04.16/'},\n", " {'changeType': 'PUT',\n", " 's3Path': 's3://kdb-demo-612841383594-kms/data/hdb/2023.04.17/',\n", " 'dbPath': '/2023.04.17/'},\n", " {'changeType': 'PUT',\n", " 's3Path': 's3://kdb-demo-612841383594-kms/data/hdb/2023.04.21/',\n", " 'dbPath': '/2023.04.21/'},\n", " {'changeType': 'PUT',\n", " 's3Path': 's3://kdb-demo-612841383594-kms/data/hdb/2023.04.19/',\n", " 'dbPath': '/2023.04.19/'},\n", " {'changeType': 'PUT',\n", " 's3Path': 's3://kdb-demo-612841383594-kms/data/hdb/sym',\n", " 'dbPath': '/'}],\n", " 'createdTimestamp': datetime.datetime(2023, 6, 6, 14, 9, 57, 787000, tzinfo=tzlocal()),\n", " 'activeFromTimestamp': datetime.datetime(2023, 6, 6, 14, 10, 39, 192000, tzinfo=tzlocal()),\n", " 'lastModifiedTimestamp': datetime.datetime(2023, 6, 6, 14, 9, 57, 787000, tzinfo=tzlocal()),\n", " 'status': 'COMPLETED'}" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "wait_for_changeset_status(client, ENV_ID, DB_NAME, changeset_id, show_wait=True)" ] }, { "cell_type": "code", "execution_count": 11, "id": "5c1d2691-fe9a-47a7-8b1c-55ee1283e702", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "====================================================================================================\n", "Database: create_delete_db_20230606_1409, Changesets: 1 \n", "====================================================================================================\n", " Changeset: AMRIhYEM3NlF6o9QdE3i6A: Created: 2023-06-06 14:09:57.787000+00:00 (COMPLETED)\n" ] }, { "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", "
changeTypes3PathdbPath
PUTs3://kdb-demo-612841383594-kms/data/hdb/2023.04.23//2023.04.23/
PUTs3://kdb-demo-612841383594-kms/data/hdb/2023.04.15//2023.04.15/
PUTs3://kdb-demo-612841383594-kms/data/hdb/2023.04.14//2023.04.14/
PUTs3://kdb-demo-612841383594-kms/data/hdb/2023.04.22//2023.04.22/
PUTs3://kdb-demo-612841383594-kms/data/hdb/2023.04.18//2023.04.18/
PUTs3://kdb-demo-612841383594-kms/data/hdb/2023.04.20//2023.04.20/
PUTs3://kdb-demo-612841383594-kms/data/hdb/2023.04.16//2023.04.16/
PUTs3://kdb-demo-612841383594-kms/data/hdb/2023.04.17//2023.04.17/
PUTs3://kdb-demo-612841383594-kms/data/hdb/2023.04.21//2023.04.21/
PUTs3://kdb-demo-612841383594-kms/data/hdb/2023.04.19//2023.04.19/
PUTs3://kdb-demo-612841383594-kms/data/hdb/sym/
\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "note_str = \"\"\n", "\n", "c_set_list = list_kx_changesets(client, ENV_ID, DB_NAME)\n", "\n", "if len(c_set_list) == 0:\n", " note_str = \"<>\"\n", " \n", "print(100*\"=\")\n", "print(f\"Database: {DB_NAME}, Changesets: {len(c_set_list)} {note_str}\")\n", "print(100*\"=\")\n", "\n", "# sort by create time\n", "c_set_list = sorted(c_set_list, key=lambda d: d['createdTimestamp']) \n", "\n", "for c in c_set_list:\n", " c_set_id = c['changesetId']\n", " print(f\" Changeset: {c_set_id}: Created: {c['createdTimestamp']} ({c['status']})\")\n", " c_rqs = client.get_kx_changeset(environmentId=ENV_ID, databaseName=DB_NAME, changesetId=c_set_id)['changeRequests']\n", "\n", " chs_pdf = pd.DataFrame.from_dict(c_rqs).style.hide(axis='index')\n", " display(chs_pdf)" ] }, { "cell_type": "markdown", "id": "9e42568f-5255-49d5-9bd9-0fd55298200d", "metadata": {}, "source": [ "## 5. Create a Cluster for the database" ] }, { "cell_type": "code", "execution_count": 12, "id": "a89c57ce-896b-47d4-b71e-5984c5e5a3fe", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "updating: code/ (stored 0%)\n", "updating: code/lib.q (stored 0%)\n", "updating: code/init.q (deflated 54%)\n", "upload: ./code.zip to s3://kdb-demo-612841383594-kms/code/code.zip\n", "2023-06-06 11:38:42 28372 basictick.zip\n", "2023-06-06 14:10:53 757 code.zip\n", "2023-06-06 12:40:02 652 taqcode.zip\n", "2023-05-21 22:39:46 785 welcomedb.zip\n" ] }, { "data": { "text/plain": [ "0" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# zip the code\n", "os.system(f\"zip -r -X {CODEBASE}.zip {CODEBASE} -x '*.ipynb_checkpoints*'\")\n", "\n", "# copy code to S3\n", "\n", "if AWS_ACCESS_KEY_ID is not None:\n", " cp = f\"\"\"\n", "export AWS_ACCESS_KEY_ID={AWS_ACCESS_KEY_ID}\n", "export AWS_SECRET_ACCESS_KEY={AWS_SECRET_ACCESS_KEY}\n", "export AWS_SESSION_TOKEN={AWS_SESSION_TOKEN}\n", "\n", "aws s3 cp --exclude .DS_Store {CODEBASE}.zip s3://{S3_BUCKET}/code/{CODEBASE}.zip\n", "aws s3 ls s3://{S3_BUCKET}/code/\n", "\"\"\"\n", "else:\n", " cp = f\"\"\"\n", "aws s3 cp --exclude .DS_Store {CODEBASE}.zip s3://{S3_BUCKET}/code/{CODEBASE}.zip\n", "aws s3 ls s3://{S3_BUCKET}/code/\n", "\"\"\"\n", " \n", "# execute the S3 copy\n", "os.system(cp)" ] }, { "cell_type": "code", "execution_count": 13, "id": "36142f08-5a68-4c22-993e-e169628133e2", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Creating: cluster_create_delete_db_20230606_1409\n" ] } ], "source": [ "print(f\"Creating: {CLUSTER_NAME}\")\n", "\n", "resp = client.create_kx_cluster(\n", " environmentId=ENV_ID, \n", " clusterName=CLUSTER_NAME,\n", " clusterDescription=f\"Demo Cluster for database {DB_NAME}\",\n", " clusterType='HDB',\n", " releaseLabel = '1.0',\n", " capacityConfiguration={ \"nodeType\": \"kx.s.xlarge\", \"nodeCount\": 2 },\n", " databases=[{ \n", " 'databaseName': DB_NAME, \n", " 'cacheConfigurations': [\n", " {'dbPaths':['/'], 'cacheType': 'CACHE_1000' }\n", " ] \n", " }],\n", " cacheStorageConfigurations=[{ 'type': 'CACHE_1000', 'size':1200 }],\n", " azMode=AZ_MODE,\n", " availabilityZoneId=AZ_ID,\n", " vpcConfiguration={ \n", " 'vpcId': VPC_ID,\n", " 'securityGroupIds': SECURITY_GROUPS,\n", " 'subnetIds': SUBNET_IDS,\n", " 'ipAddressType': 'IP_V4' },\n", " code={ 's3Bucket': S3_BUCKET, 's3Key': CODE_PATH },\n", " initializationScript=f\"{CODEBASE}/init.q\",\n", " commandLineArguments=[\n", " {'key': 's', 'value': '4'}, \n", " {'key': 'dbname', 'value': DB_NAME}, \n", " {'key': 'codebase', 'value': CODEBASE}\n", " ]\n", ")" ] }, { "cell_type": "code", "execution_count": 14, "id": "e32bdf1d-0f12-4d0e-8ac8-0c6a691d1896", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'ResponseMetadata': {'RequestId': 'c623bfa1-b2f8-40b8-b97b-41b637644489',\n", " 'HTTPStatusCode': 200,\n", " 'HTTPHeaders': {'content-type': 'application/json',\n", " 'content-length': '1188',\n", " 'connection': 'keep-alive',\n", " 'date': 'Tue, 06 Jun 2023 14:11:01 GMT',\n", " 'x-amzn-requestid': 'c623bfa1-b2f8-40b8-b97b-41b637644489',\n", " 'x-amz-apigw-id': 'GGaxGGr7oAMFxAg=',\n", " 'x-amzn-trace-id': 'Root=1-647f3e6d-5821843e78d6f4691b29ed20',\n", " 'x-cache': 'Miss from cloudfront',\n", " 'via': '1.1 5b2c25375d693d0fb882145cde66154e.cloudfront.net (CloudFront)',\n", " 'x-amz-cf-pop': 'IAD55-P1',\n", " 'x-amz-cf-id': '3zjaQmftl2c1h0PK_FGjAYonnpwlKifn1d3VM9UVTVo49hxtNVC23w=='},\n", " 'RetryAttempts': 0},\n", " 'status': 'PENDING',\n", " 'clusterName': 'cluster_create_delete_db_20230606_1409',\n", " 'clusterType': 'HDB',\n", " 'databases': [{'databaseName': 'create_delete_db_20230606_1409',\n", " 'cacheConfigurations': [{'cacheType': 'CACHE_1000', 'dbPaths': ['/']}],\n", " 'changesetId': 'AMRIhYEM3NlF6o9QdE3i6A'}],\n", " 'cacheStorageConfigurations': [{'type': 'CACHE_1000', 'size': 1200}],\n", " 'clusterDescription': 'Demo Cluster for database create_delete_db_20230606_1409',\n", " 'capacityConfiguration': {'nodeType': 'kx.s.xlarge', 'nodeCount': 2},\n", " 'releaseLabel': '1.0',\n", " 'vpcConfiguration': {'vpcId': 'vpc-0e702dec545865b11',\n", " 'securityGroupIds': ['sg-018111774e795682d'],\n", " 'subnetIds': ['subnet-0f97cae6600859c17'],\n", " 'ipAddressType': 'IP_V4'},\n", " 'initializationScript': 'code/init.q',\n", " 'commandLineArguments': [{'key': 's', 'value': '4'},\n", " {'key': 'dbname', 'value': 'create_delete_db_20230606_1409'},\n", " {'key': 'codebase', 'value': 'code'}],\n", " 'code': {'s3Bucket': 'kdb-demo-612841383594-kms', 's3Key': 'code/code.zip'},\n", " 'lastModifiedTimestamp': datetime.datetime(2023, 6, 6, 14, 11, 0, 223000, tzinfo=tzlocal()),\n", " 'azMode': 'SINGLE',\n", " 'availabilityZoneId': 'use1-az4',\n", " 'createdTimestamp': datetime.datetime(2023, 6, 6, 14, 11, 0, 148000, tzinfo=tzlocal())}" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "resp" ] }, { "cell_type": "code", "execution_count": 15, "id": "4c21dbb8-9878-441f-997b-f27164e1e6bf", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Cluster: cluster_create_delete_db_20230606_1409 status is PENDING, total wait 0:00:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:00:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:01:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:01:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:02:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:02:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:03:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:03:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:04:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:04:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:05:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:05:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:06:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:06:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:07:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:07:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:08:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:08:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:09:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:09:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:10:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:10:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:11:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:11:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:12:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:12:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:13:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:13:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:14:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:14:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:15:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:15:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:16:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:16:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:17:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:17:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is CREATING, total wait 0:18:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is now RUNNING, total wait 0:18:30\n", "\n", "** DONE **\n" ] } ], "source": [ "wait_for_cluster_status(client, environmentId=ENV_ID, clusterName=CLUSTER_NAME, show_wait=True)\n", "print()\n", "print(\"** DONE **\")" ] }, { "cell_type": "markdown", "id": "fb1a5cac-c0f6-4478-8f67-0c1060fe986a", "metadata": { "tags": [] }, "source": [ "## 6. Get the connectionString\n", "This assumes that the IAM role exists and the user (KDB_USERNAME) have beed already added as well." ] }, { "cell_type": "code", "execution_count": 16, "id": "477af774-ee69-4cd8-bb79-0bb983b6fb91", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Cluster: --------------------------------------------------------------------------------\n", "{\n", " \"availabilityZoneId\": \"use1-az4\",\n", " \"azMode\": \"SINGLE\",\n", " \"cacheStorageConfigurations\": [\n", " {\n", " \"size\": 1200,\n", " \"type\": \"CACHE_1000\"\n", " }\n", " ],\n", " \"capacityConfiguration\": {\n", " \"nodeCount\": 2,\n", " \"nodeType\": \"kx.s.xlarge\"\n", " },\n", " \"clusterDescription\": \"Demo Cluster for database create_delete_db_20230606_1409\",\n", " \"clusterName\": \"cluster_create_delete_db_20230606_1409\",\n", " \"clusterType\": \"HDB\",\n", " \"code\": {\n", " \"s3Bucket\": \"kdb-demo-612841383594-kms\",\n", " \"s3Key\": \"code/code.zip\"\n", " },\n", " \"commandLineArguments\": [\n", " {\n", " \"key\": \"s\",\n", " \"value\": \"4\"\n", " },\n", " {\n", " \"key\": \"dbname\",\n", " \"value\": \"create_delete_db_20230606_1409\"\n", " },\n", " {\n", " \"key\": \"codebase\",\n", " \"value\": \"code\"\n", " }\n", " ],\n", " \"createdTimestamp\": \"2023-06-06 14:11:00.148000+00:00\",\n", " \"databases\": [\n", " {\n", " \"cacheConfigurations\": [\n", " {\n", " \"cacheType\": \"CACHE_1000\",\n", " \"dbPaths\": [\n", " \"/\"\n", " ]\n", " }\n", " ],\n", " \"changesetId\": \"AMRIhYEM3NlF6o9QdE3i6A\",\n", " \"databaseName\": \"create_delete_db_20230606_1409\"\n", " }\n", " ],\n", " \"initializationScript\": \"code/init.q\",\n", " \"lastModifiedTimestamp\": \"2023-06-06 14:29:35.721000+00:00\",\n", " \"releaseLabel\": \"1.0\",\n", " \"status\": \"RUNNING\",\n", " \"vpcConfiguration\": {\n", " \"ipAddressType\": \"IP_V4\",\n", " \"securityGroupIds\": [\n", " \"sg-018111774e795682d\"\n", " ],\n", " \"subnetIds\": [\n", " \"subnet-0f97cae6600859c17\"\n", " ],\n", " \"vpcId\": \"vpc-0e702dec545865b11\"\n", " }\n", "}\n" ] } ], "source": [ "try:\n", " resp = client.get_kx_cluster(environmentId=ENV_ID, clusterName=CLUSTER_NAME)\n", "except client.exceptions.ResourceNotFoundException:\n", " print(F\"Cluster: {CLUSTER_NAME} did not create\")\n", " \n", "if resp['ResponseMetadata']['HTTPStatusCode'] != 200:\n", " sys.stderr.write(\"Error:\\n {resp}\")\n", "else:\n", " resp.pop('ResponseMetadata', None)\n", "\n", "kx_cluster = resp\n", "\n", "print(\"Cluster: \"+(\"-\"*80))\n", "print(json.dumps(kx_cluster, sort_keys=True, indent=4, default=str))\n" ] }, { "cell_type": "code", "execution_count": 17, "id": "7747eeb0-e0c4-43b0-8208-336fb3ea6286", "metadata": {}, "outputs": [], "source": [ "# Give permissions time to propogate after cluster creation....\n", "time.sleep(60)\n" ] }, { "cell_type": "code", "execution_count": 18, "id": "4c63c186-d837-4185-9280-2a486425f05c", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Copy into q: --------------------------------------------------------------------------------\n", "\n", "/ Cluster: cluster_create_delete_db_20230606_1409\n", "hdb_conn:\":tcps://vpce-041d8a433e38ad503-xc3rqdw9.vpce-svc-0ac7f31ce14d60c07.us-east-1.vpce.amazonaws.com:443:bob:Host=vpce-041d8a433e38ad503-xc3rqdw9.vpce-svc-0ac7f31ce14d60c07.us-east-1.vpce.amazonaws.com&Port=5000&User=bob&Action=finspace%3AConnectKxCluster&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEP%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FwEaCXVzLWVhc3QtMSJIMEYCIQDBqNu5RI62cEZmhoJcSayXqKsWjPIy7FlnFC%2FdoULqgwIhAIqhMnA1Lm4bDNx6NOyP8CT6EV3Tqr2yr2FWTiAwZOFVKvcCCEgQABoMNjEyODQxMzgzNTk0IgyNqVz7rtvdCVXbWogq1AKVBdrFVR91y8tBLqbCbHIE8%2FiNyEhHlEDuRGIHWROlaYMqTxmiC9HwMpVd3DaTfOq1Bu1eEVF0dEUB6oNIM5h7PV%2BoOg3B%2FvyXAzXnu9vU1wIksZ96D3KSqHtph5SAfzuTSiAMrOoXGJka%2FT1oTGX58pIrXQ%2BqUmnKv6GcFe0A4ubT5GDSfu%2F00KXkgSEgDFv5rXVa4d60vgXdCm57am2cVK3Xk3IMHvZmSzz39UIsSHih6lgaJYsJM4FG0ngXsuiiApiuzKdrZIOIDTnR%2FnkpZh3%2BYW5XRVL3VS3cXbW9DvTHtUuFrKiPhUoynsFdmxsocSVtixlUJXj6XGQNCAMq3Do%2FiF%2BYeCqxguD9ZOmfC1hWnmZzniBuS%2FMjQi2vsiU6vospgmRIFo%2FMsDmVaNbOemiBztwIJVpvEzgTgGQRzKbd5jso6PaYoU4XJd%2B%2BYS8XR%2FrGMJuG%2FaMGOr4BUmJT5wKBCt9rogyemk%2FUvIYkgrkeabP5NX7vZCqB%2BrJ4slAB%2FMtu5CQn8LQB%2FklGpqWjaWFareuYQVpV7h5Mw1m%2F90lM47H%2B2ebXHQsck7lBlvHgEU80mm6V%2BiJSYlPIz036XyhjwBc5yBTwAgnfWWBrw70eFvpOhULuQw0beZ1iemKPtd9cNkmRRCegSOkpws0Shq0GefEUIWhmvetgBHpeWSOSrFVHP601r2liD7slyhFVEvtgMOAibnVAww%3D%3D&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Date=20230606T143051Z&X-Amz-SignedHeaders=host&X-Amz-Expires=900&X-Amz-Credential=ASIAY5MBRM2VCQPANLMO%2F20230606%2Fus-east-1%2Ffinspace-apricot%2Faws4_request&X-Amz-Signature=971c19ccdc13bc641863454f42399e0b7c5e169bc26fd13da1f95ddb958d11a6\"\n", "\n" ] } ], "source": [ "\n", "conn_str = get_kx_connection_string(client, environmentId=ENV_ID, clusterName=CLUSTER_NAME, userName=KDB_USERNAME, boto_session=session)\n", "\n", "print (\"\")\n", "print(\"Copy into q: \"+(\"-\"*80))\n", "print(f\"\"\"\n", "/ Cluster: {CLUSTER_NAME}\n", "hdb_conn:\"{conn_str}\"\n", "\"\"\")" ] }, { "cell_type": "markdown", "id": "3f67a2fe-d592-43b8-868a-8219e603d77f", "metadata": {}, "source": [ "## 7. Query Cluster using PyKX" ] }, { "cell_type": "code", "execution_count": 19, "id": "b68d3ef3-6a39-4390-aeb4-723dcb5166d2", "metadata": {}, "outputs": [], "source": [ "# Query the HDB\n", "hdb = get_pykx_connection(client, \n", " environmentId=ENV_ID, clusterName=CLUSTER_NAME, \n", " userName=KDB_USERNAME, boto_session=session)" ] }, { "cell_type": "code", "execution_count": 20, "id": "06a4bdb0-0e77-4c2a-aade-3d4a112013cd", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Tables (1): ['example']\n" ] }, { "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", "
tfa
c
dateb'd'
symb's'p
timeb'p'
numberb'j'
\n", "
" ], "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": [ "# Tables\n", "tables = hdb(\"tables[]\").py()\n", "print(f\"Tables ({len(tables)}): {tables}\")\n", "\n", "# Schema\n", "schema_pdf = hdb(\"meta `example\").pd()\n", "display(schema_pdf)\n" ] }, { "cell_type": "code", "execution_count": 21, "id": "15469493-9105-480f-9a16-8a18eda7bc4c", "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", "
countsavg_numavg_sq_num
date
2023-04-141000000499800.4644053.331963e+11
2023-04-151000000499865.0081593.332692e+11
2023-04-161000000499912.3791273.332060e+11
2023-04-171000000500078.3933863.334004e+11
2023-04-181000000500264.6844123.336606e+11
2023-04-191000000499849.9629123.333436e+11
2023-04-201000000500082.2578293.334472e+11
2023-04-211000000500169.4923543.336027e+11
2023-04-221000000499903.0475773.331833e+11
2023-04-231000000500277.4927903.335924e+11
\n", "
" ], "text/plain": [ " counts avg_num avg_sq_num\n", "date \n", "2023-04-14 1000000 499800.464405 3.331963e+11\n", "2023-04-15 1000000 499865.008159 3.332692e+11\n", "2023-04-16 1000000 499912.379127 3.332060e+11\n", "2023-04-17 1000000 500078.393386 3.334004e+11\n", "2023-04-18 1000000 500264.684412 3.336606e+11\n", "2023-04-19 1000000 499849.962912 3.333436e+11\n", "2023-04-20 1000000 500082.257829 3.334472e+11\n", "2023-04-21 1000000 500169.492354 3.336027e+11\n", "2023-04-22 1000000 499903.047577 3.331833e+11\n", "2023-04-23 1000000 500277.492790 3.335924e+11" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "Rows: 10,000,000\n" ] } ], "source": [ "# Simple Query, uses function from lib\n", "res_table = hdb(\"select counts:count i, avg_num: avg number, avg_sq_num: avg sq number by date from example\").pd()\n", "display(res_table)\n", "\n", "# Number of Rows in Table\n", "rows = hdb(\"count example\").py()\n", "print(f\"Rows: {rows:,}\")" ] }, { "cell_type": "markdown", "id": "aaec5839-e519-42d2-ab62-a524cee0bdfc", "metadata": { "tags": [] }, "source": [ "# Clean Up" ] }, { "cell_type": "code", "execution_count": 22, "id": "941c540a-f4ed-46a1-a7ba-7e125b72501c", "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", "
databaseNamecreatedTimestamplastModifiedTimestamp
TAQ_2021H12023-06-06 11:31:46.353000+00:002023-06-06 11:31:46.353000+00:00
welcomedb2023-06-06 11:24:10.707000+00:002023-06-06 11:24:58.428000+00:00
create_delete_db_20230606_14092023-06-06 14:09:57.071000+00:002023-06-06 14:09:57.787000+00:00
basictickdb2023-06-06 11:34:08.545000+00:002023-06-06 11:34:09.347000+00:00
TAQ_2021_2D2023-06-06 12:39:56.136000+00:002023-06-06 12:39:56.136000+00:00
\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:00:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:00:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:01:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:01:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:02:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:02:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:03:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:03:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:04:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:04:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:05:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:05:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:06:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:06:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:07:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:07:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:08:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:08:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:09:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:09:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:10:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:10:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:11:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:11:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:12:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:12:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:13:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:13:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:14:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:14:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:15:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:15:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:16:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:16:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:17:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:17:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:18:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:18:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:19:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:19:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:20:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:20:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:21:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:21:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:22:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:22:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:23:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:23:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:24:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:24:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:25:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:25:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:26:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:26:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:27:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:27:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:28:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:28:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:29:00, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:29:30, waiting 30 sec ...\n", "Cluster: cluster_create_delete_db_20230606_1409 status is DELETING, total wait 0:30:00, waiting 30 sec ...\n", "\n", "** DONE **\n" ] } ], "source": [ "# Cluster Deletion\n", "# ------------------------------------------------------------\n", "db_list = list_kx_databases(client, environmentId=ENV_ID)\n", "db_list\n", "\n", "db_pdf = pd.DataFrame.from_dict(db_list).style.hide(axis='index')\n", "display(db_pdf)\n", "print(\"\")\n", "\n", "cluster_deleted=False\n", "\n", "if DELETE_CLUSTER: \n", " # list all clusters\n", " resp=client.get_kx_cluster(environmentId=ENV_ID, clusterName=CLUSTER_NAME)\n", " \n", " if resp['ResponseMetadata']['HTTPStatusCode'] != 200:\n", " sys.stderr.write(\"Error:\\n {resp}\")\n", " else:\n", " resp.pop('ResponseMetadata', None)\n", "\n", " if resp['status'] != 'DELETING':\n", " try:\n", " resp = client.delete_kx_cluster(environmentId=ENV_ID, clusterName=CLUSTER_NAME)\n", " if resp['ResponseMetadata']['HTTPStatusCode'] != 200:\n", " sys.stderr.write(\"Error:\\n {resp}\")\n", " else:\n", " resp.pop('ResponseMetadata', None)\n", " except Exception as e: \n", " sys.stderr.write(f\"Error deleting cluster: {CLUSTER_NAME}\\n{e}\")\n", " cluster_deleted = False\n", "\n", " try:\n", " wait_for_cluster_status(client, environmentId=ENV_ID, clusterName=CLUSTER_NAME, status='DELETED', show_wait=True)\n", " print()\n", " print(\"** DONE **\")\n", "\n", " cluster_deleted = True\n", " except client.exceptions.ResourceNotFoundException:\n", " cluster_deleted = True\n", "else:\n", " print(f\"DELETE_CLUSTER: {DELETE_CLUSTER}\")" ] }, { "cell_type": "code", "execution_count": 23, "id": "66da3a6c-c7ba-46f1-8908-198f36b3349d", "metadata": {}, "outputs": [], "source": [ "# Database Deletion\n", "# Requires cluster to have been deleted\n", "if DELETE_DATABASE:\n", " if cluster_deleted:\n", " # if the database exists, delete it\n", " if has_database(client, environmentId=ENV_ID, databaseName=DB_NAME):\n", " try:\n", " resp = client.delete_kx_database(environmentId=ENV_ID, databaseName=DB_NAME)\n", " if resp['ResponseMetadata']['HTTPStatusCode'] != 200:\n", " sys.stderr.write(\"Error:\\n {resp}\")\n", " else:\n", " resp.pop('ResponseMetadata', None)\n", "\n", " resp\n", " except Exception as e: \n", " sys.stderr.write(f\"Error: \\n{e}\")\n", " else:\n", " print(f\"Database already deleted: {DB_NAME} \")\n", " else:\n", " print(f\"Cluster deleted? {cluster_deleted}, will not delete database if cluster not deleted\")\n", "else:\n", " print(f\"DELETE_DATABASE: {DELETE_DATABASE}\")" ] }, { "cell_type": "code", "execution_count": 24, "id": "b2346146-b86a-4d95-9f82-5b4e4705acd8", "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", "
databaseNamecreatedTimestamplastModifiedTimestamp
TAQ_2021H12023-06-06 11:31:46.353000+00:002023-06-06 11:31:46.353000+00:00
TAQ_2021_2D2023-06-06 12:39:56.136000+00:002023-06-06 12:39:56.136000+00:00
basictickdb2023-06-06 11:34:08.545000+00:002023-06-06 11:34:09.347000+00:00
welcomedb2023-06-06 11:24:10.707000+00:002023-06-06 11:24:58.428000+00:00
\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "db_list = list_kx_databases(client, environmentId=ENV_ID)\n", "db_list=sorted(db_list, key=lambda d: d['databaseName']) \n", "\n", "db_pdf = pd.DataFrame.from_dict(db_list).style.hide(axis='index')\n", "display(db_pdf)" ] }, { "cell_type": "code", "execution_count": 25, "id": "8ae3d31a-c70d-4a4c-935f-5d86f700a07a", "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
clusterNamestatusclusterTypecapacityConfigurationcommandLineArgumentsclusterDescriptionlastModifiedTimestampcreatedTimestampdatabaseNamecacheConfigurations
HDB_TAQ_2021_2DRUNNINGHDB{'nodeType': 'kx.s.32xlarge', 'nodeCount': 2}[{'key': 's', 'value': '8'}, {'key': 'dbname', 'value': 'TAQ_2021_2D'}, {'key': 'codebase', 'value': 'taqcode'}]Created with create_cluster_TAQ_2D notebook2023-06-06 12:54:08.985000+00:002023-06-06 12:40:07.108000+00:00TAQ_2021_2D[{'cacheType': 'CACHE_1000', 'dbPaths': ['/2021.01.04/', '/2021.01.05/']}]
HDB_basictickdb_20230601RUNNINGHDB{'nodeType': 'kx.s.xlarge', 'nodeCount': 2}[{'key': 's', 'value': '8'}, {'key': 'dbname', 'value': 'basictickdb'}, {'key': 'codebase', 'value': 'basictick'}]Created with create_HDB for basic_tick notebook2023-06-06 11:52:52.544000+00:002023-06-06 11:35:14.234000+00:00basictickdb[{'cacheType': 'CACHE_1000', 'dbPaths': ['/']}]
HDB_welcomedbRUNNINGHDB{'nodeType': 'kx.s.2xlarge', 'nodeCount': 3}[{'key': 's', 'value': '4'}, {'key': 'dbname', 'value': 'welcomedb'}, {'key': 'codebase', 'value': 'code'}]Created with create_cluster_HDB notebook2023-06-06 11:50:28.453000+00:002023-06-06 11:32:13.700000+00:00welcomedb[{'cacheType': 'CACHE_1000', 'dbPaths': ['/']}]
RDB_basictickdb_20230601RUNNINGRDB{'nodeType': 'kx.s.2xlarge', 'nodeCount': 1}[{'key': 's', 'value': '8'}, {'key': 'dbname', 'value': 'basictickdb'}, {'key': 'codebase', 'value': 'basictick'}, {'key': 'tphostfile', 'value': 'tickerplant2'}]Created with create_RDB notebook2023-06-06 11:54:07.399000+00:002023-06-06 11:38:47.020000+00:00basictickdb[]
RDB_welcomedbRUNNINGRDB{'nodeType': 'kx.s.2xlarge', 'nodeCount': 1}[{'key': 's', 'value': '8'}, {'key': 'dbname', 'value': 'welcomedb'}, {'key': 'codebase', 'value': 'code'}]Created with create_cluster_RDB notebook2023-06-06 11:47:08.448000+00:002023-06-06 11:32:06.677000+00:00welcomedb[]
hdb-cluster-welcomedbRUNNINGHDB{'nodeType': 'kx.s.xlarge', 'nodeCount': 2}[{'key': 's', 'value': '4'}, {'key': 'dbname', 'value': 'welcomedb'}, {'key': 'codebase', 'value': 'code'}]None2023-06-06 11:44:19.914000+00:002023-06-06 11:26:51.517000+00:00welcomedb[{'cacheType': 'CACHE_1000', 'dbPaths': ['/']}]
\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "Last Run: 2023-06-06 15:02:00.945835\n" ] } ], "source": [ "cdf = get_clusters(client, ENV_ID)\n", "\n", "display(cdf)\n", "\n", "print( f\"Last Run: {datetime.datetime.now()}\" )" ] }, { "cell_type": "code", "execution_count": null, "id": "b7fe3a21-1bf9-47ba-b162-70ab5445b44f", "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 }