How to fetch the table or view metadata from KDB and save to a data-structure?

Question

I have been trying to fetch the metadata from a KDB+ Database using python, basically, I installed a library called qpython and using this library we connect and query the KDB+ Database.

I want to store the metadata for all the appropriate cols for a table/view in KDB+ Database using python. I am unable to separate the metadata part, despite trying myriad different approaches.

Namely a few to typecast the output to list/tuple, iterating using for, et cetera.

from qpython import qconnection

def fetch_metadata_from_kdb(params):
    try:
        kdb_connection_obj = qconnection.QConnection(host=params['host'], port=params['port'], username=params['username'], password=params['password'])
        kdb_connection_obj.open()
        PREDICATE = "meta[{}]".format(params['table'])
        metadata = kdb_connection_obj(PREDICATE)
        kdb_connection_obj.close()
        return metadata

    except Exception as error_msg: 
        return error_msg

def fetch_tables_from_kdb(params):
    try:
        kdb_connection_obj = qconnection.QConnection(host=params['host'], port=params['port'], username=params['username'], password=params['password'])
        kdb_connection_obj.open()
        tables = kdb_connection_obj("tables[]")
        views = kdb_connection_obj("views[]")
        kdb_connection_obj.close()
        return [table.decode() for table in list(tables)], [view.decode() for view in list(views)]

    except Exception as error_msg:
        return error_msg

parms_q = {'host':'localhost', 'port':5010,
           'username':'kdb', 'password':'kdb', 'table':'testing'}

print("fetch_tables_from_kdb:", fetch_tables_from_kdb(parms_q), "\n")
print("fetch_metadata_from_kdb:", fetch_metadata_from_kdb(parms_q), "\n")

The output which I am currently getting is as follows;

fetch_tables_from_kdb: (['testing'], ['viewname']) 

fetch_metadata_from_kdb: [(b'time',) (b'sym',) (b'price',) (b'qty',)]![(b'p', b'', b'') (b's', b'', b'') (b'f', b'', b'') (b'j', b'', b'')] 

I am not able to separate the columns part and the metadata part. How to store only the metadata for the appropriate column for a table/view in KDB using python?

Answer 1

In the meantime, I have checked quite a bit of KBD documentation and found that the metadata provides the following as the output. You can see that here kdb metadata

c | t f a

c-columns t-symbol f-foreign key association a-attributes associated with the column

We can access the metadata object(<class 'qpython.qcollection.QKeyedTable'>) by interating over a for loop as shown below;

from qpython import qconnection

def fetch_metadata_from_kdb(params):
    try:
        col_list, metadata_list = [], []
        kdb_connection_obj = qconnection.QConnection(host=params['host'], port=params['port'], username=params['username'], password=params['password'])
        kdb_connection_obj.open()
        PREDICATE = "meta[{}]".format(params['table'])

        ############# FOR LOOP ##############
        for i,j in kdb_connection_obj(PREDICATE).items():
            col_list.append(i[0].decode())
            metadata_list.append(j[0].decode())

        kdb_connection_obj.close()
        return col_list, metadata_list

    except Exception as error_msg: 
        return error_msg
    
parms_q = {'host':'localhost', 'port':5010,
           'username':'kdb', 'password':'kdb', 'table':'testing'}
           
print(fetch_metadata_from_kdb(parms_q))

Output: ['time', 'sym', 'price', 'qty'], ['p', 's', 'f', 'j']

I also got the KDB char types / q data types from the documentation here. Below is the implementation for the same;

import pandas as pd
from qpython import qconnection

kdb_type_char_dict = dict()

df = pd.read_html('https://code.kx.com/q4m3/2_Basic_Data_Types_Atoms/')[1].iloc[:17, 0:3][['Type', 'CharType']]
for i, j in zip(df.iloc[:, 0], df.iloc[:, 1]): kdb_type_char_dict[str(j)] = str(i)

####### Q DATA TYPES DICTIONARY #######
print("Chat types/ q data types dictionary:", kdb_type_char_dict)

def fetch_metadata_from_kdb(params):
    try:
        col_list, metadata_list, temp_list = [], [], []
        kdb_connection_obj = qconnection.QConnection(host=params['host'], port=params['port'],
                                                     username=params['username'], password=params['password'])
        kdb_connection_obj.open()
        PREDICATE = "meta[{}]".format(params['table'])
        for i, j in kdb_connection_obj(PREDICATE).items():
            col_list.append(i[0].decode())
            temp_list.append(j[0].decode())
        for i in temp_list:
            metadata_list.append("{}".format(kdb_type_char_dict[i]))
        kdb_connection_obj.close()
        return col_list, metadata_list

    except Exception as error_msg:
        return error_msg


params = {'host': 'localhost', 'port': 5010,
           'username': 'kdb', 'password': 'kdb', 'table': 'testing'}

print(fetch_metadata_from_kdb(params))

Output:

Chat types/ q data types dictionary: {'b': 'boolean', 'x': 'byte', 'h': 'short', 'i': 'int', 'j': 'long', 'e': 'real', 'f': 'float', 'c': 'char', 's': 'symbol', 'p': 'timestamp', 'm': 'month', 'd': 'date', 'z': '(datetime)', 'n': 'timespan', 'u': 'minute', 'v': 'second', 't': 'time'}


(['time', 'sym', 'price', 'qty'], ['timestamp', 'symbol', 'float', 'long'])