UDX questions on migrating stored procedures

Forum|Forum|8 years ago
May 16, 2018
2 replies
6 views

+2

Bryan_H
Participating Frequently

Hi, we are onboarding a new customer who wants to migrate stored procedures into Vertica, so they have several questions on UDx behavior:

Can we use pyodbc inside a Python UDx? This would allow us to port scripts into Python and simply return the error code as a scalar.
In general, can UDx interact with Vertica via SQL? Can we create temp tables, for example?
Can any UDx type return multiple result sets? Or would we need to use temp tables to accomplish this, if supported as above?

MaciejP
New Participant
Forum|Forum|8 years ago
May 16, 2018

It is hard to answer to question formulated like this.
I my opinion that during themigration process you need to review every SP and analyse the code, and then:
1. Some SP are written as workarround for technology limitation. Hence you need to rethnik how to redesign same functionality in VERTICA
2. Some can be converted into UDX
3. Some you can get rid-of by redesigning the query.

Regarding the questions in bullets:
Ad 1). I dont know, never tried - but as it seems to be not a good idea, as UDX process block by block of data in a loop this and in case of scalar fumctio expect to return single value for every row. Connecting to external DS does not make sense (maybe you should use external tabls then)
Ad 2) UDX behaves as regular SQL function. U can store data as an output to a temp table, but i haven't seen APIs to manipulate SQL (see SDK reference)
Ad 3) that is why you can use "transform" type of UDX and you have to define ReturnType function that handles all possible variants of returned sets of columns

M.