How to Build and Manage Databases Using PyKX

Introduction

For many years, KX and its partners have looked to provide solutions that simplify database management in kdb+, this has seen the launch of several products and frameworks, including kdb Insights Enterprise, kdb Insights Database, and Data Intellects, TorQ.

With the introduction of PyKX nearly 2 years ago, we delivered an initial new wave of capability, increasing access to our powerful analytic tooling, via a familiar python interface and opening kdb+ data estates to a new wave of developers. Over the years this has continued to evolve and harden capabilities.

Earlier this week, we released the next phase in this journey as part of PyKX 2.3. Specifically, through the beta release of a new feature; centred around “database management”, providing greater flexibility in the provisioning and management of kdb+ databases via Python.

With the initial release, developers will be able to:

• Create a database and tables from any historical data.
• Add new partitions and tables to a database.
• Add and rename tables and columns.
• Create copies of table columns.
• Apply functions to columns.
• Update data types of columns
• Add & fix missing partitions from tables.
• Interrogate the content/structure of your database.

Click here for a full breakdown of the API.

Walkthrough

Let’s explore some of these features further by walking through example management tasks.

To begin, you will need to enable PyKX beta features:

import os
os.environ['PYKX_BETA_FEATURES'] = 'True'
import pykx as kx

Once initialised, you can validate what features are enabled by running the following command.

print(kx.config.beta_features)
print(kx.beta_features)

Next, create a new directory to host your database and initialise the DB class.

os.makedirs('/tmp/my_database')
db = kx.DB(path='/tmp/my_database')

From here, you can generate a new dataset which will be saved to the database.

from datetime import date 
N = 100000 
dataset = kx.Table(data={ 
 'date': kx.random.random(N, [date(2020, 1, 1), date(2020, 1, 2)]), 
 'sym': kx.random.random(N, ['AAPL', 'GOOG', 'MSFT']), 
 'price': kx.random.random(N, 10.0)  
 }) 
dataset

With the dataset created, save it to disk by partitioning(splitting) the data into sub-directories based on date.

db.create(dataset, table_name='db_table', partition='date')

Congratulations, you have now created your first kdb+ database. You can access this table as a property of the database.

db.db_table

Now, let’s explore how to add data into an existing database table by using the db.create function.

new_data = kx.Table(data={ 
 'sym': kx.random.random(3333, ['AAPL', 'GOOG', 'MSFT']), 
 'price': kx.random.random(3333, 10.0)  
 }) 

db.create(new_data, 'db_table', date(2020, 1, 3))

Next, you can rename your table to ‘finance_data’ and the column ‘sym’ to a more descriptive name.

db.rename_table('db_table', 'finance_data')
db.rename_column('finance_data', 'sym', 'ticker')

With more significant editing tasks, you may want to perform the operation on a practice column and protect your original dataset. To demonstrate, create a copy of the ‘price’ column and then perform a function to double the content values. Finally, modify the data type of your new column to be less memory intensive.

db.copy_column('finance_data', 'price', 'price_copy') 
db.apply_function('finance_data', 'price_copy', lambda x:2*x) 
db.set_column_type('finance_data', 'price_copy', kx.RealAtom)

We can now look at the content of finance_data to see how these changes have modified the database.

db.finance_data

Let’s also check the number of data rows, per partition within the database.

db.partition_count()

Finally, using the create function that you used earlier, and the fill_database function, you can add the missing partitions to a newly onboarded table.

orders = kx.Table(data = { 
    'id': kx.random.random(100, kx.GUIDAtom.null), 
    'type': kx.random.random(100, ['Buy', 'Sell'])}) 

db.create(orders, 'orders', date(2020, 1, 3)) 
db.fill_database()

You can see that the orders table has now been onboarded and is accessible.

print(db.tables)
db.orders

Conclusions

Creating and managing databases at scale is a tough challenge and one where mistakes in operations can have a meaningful impact on business performance and use-case efficiency. While the Database Management feature does not solve all problems in this area, it is intended to provide you with an initial grounding. Allowing you, in Python, to create and perform basic operations on kdb+ databases to make this task easier.

As mentioned throughout the blog, this is presently a beta feature and subject to updates prior to a general availability release later this year. If you run into difficulties with this feature, or have suggestions for additional functionality, please open an issue on our public GitHub or alternatively open a pull request.