CODEWITHSUNDEEP
pythonsqluser-defined-functionsmonetdb
mkersten
mkersten

Reputation: 714

How to debug a SQL/Python UDF in MonetDB

Using native Python code in SQL UDFs in Monetdb is really powerful. BUT, debugging such UDFs could benefit from more support. In particular, if I use the old-fashioned print('debugging info') it disappears in the big black void.

create function dummy() 
returns string
language python{
    print('Entering the dummy UDF')
    return 'hello';
};

How to retrieve this information from the server or MonetDB client.

Upvotes: 1

Views: 337

Answers (2)

Pedro Holanda
Pedro Holanda

Reputation: 301

In case someone is still interested in this problem. There are two novel ways of debugging MonetDB's Python/UDFs.

1) Using the python client pymonetdb (https://github.com/gijzelaerr/pymonetdb). You can install it throw pip

pip install numpy

To use it, think of the following setting with a table that holds an integer and a UDF that computes the mean absolute deviation of a given column.

CREATE TABLE integers(i INTEGER);
INSERT INTO integers VALUES (1), (3), (6), (8), (10);

CREATE OR REPLACE FUNCTION mean_deviation(column INTEGER)
RETURNS DOUBLE LANGUAGE PYTHON {
  mean = 0.0
  for i in range (0, len(column)):
    mean += column[I]
  mean = mean / len(column)
  distance = 0.0
  for i in range (0, len(column)):
    distance += column[i] - mean
  deviation = distance/len(column)
  return deviation;
};

To debug your function using terminal debugging (i.e., pdb) you just need to open a database connection using pymonetdb.connect(), later you get a cursor object from the connection, and through the cursor object you call the debug() function, sending as parameters the SQL you want to examine and the UDF name you wish to debug.

import pymonetdb
conn = pymonetdb.connect(database='demo') #Open Database connection
c = conn.cursor()
sql = 'select mean_deviation(i) from integers;'
c.debug(sql, 'mean_deviation') #Console Debugging

There is an optional sampling step that only transfers a uniform random sample of the data instead of the full input data set. If you wish to sample you just need to send the number of elements you wish to get from the sampling (e.g., c.debug(sql, 'mean_deviation', 10) in case you desire the subset of 10 elements)

2) Using a POC plugin for PyCharm called devudf, which you can install throw the plugin page of pycharm, or by directly going to the JetBrains page: https://plugins.jetbrains.com/plugin/12063-devudf. It adds an option to the main menu called "UDF Development" and allows for you do directly import and export UDFs from your database directly to pycharm, and enjoy the IDE's debugging capabilities.

Upvotes: 1

Jennie
Jennie

Reputation: 405

I was debugging some Python UDF last week :)

Step 1: first make sure your Python code at least works in a Python interpreter.

Step 2: in a Python UDF, write your debugging info. to a file, e.g.:

    f = open('/tmp/debug.out', 'w')
    f.write('my debugging info\n')
    f.close()

This isn't ideal, but it works. Also, I used this to export the parameter values of my Python UDF. In this way, I can run the body of my Python UDF in a Python interpreter with the exact data I receive from MonetDB.

Upvotes: 1

Related Questions