I am using Vertica and I have some functions written in Python that use numpy linear algebra capabilities/scipy optimization and are too complicated to write in SQL.
Based on benchmarks which of the options will perform better -
1- Vertica UDF - where the udf of the original python functions will be applied on every record.
2- Using Pyspark and apply the functions directly on every record.
Which is faster for applying python functions on every record - Spark or Vertica UDF ?
The bottleneck is always data travelling from and to the database.
So bring the functionality to where the data is rather than bringing the data to the functionality - always.
Have you actually explored all the functionality you have at your disposal with a DBMS actually complying with the ANSI 99 or even the ANSI 2003 standard of SQL?
You'll actually be surprised how little is left that is too complicated to do in SQL.
I have long since come to the point where I say:
If you can't do it in SQL, do it in C (or C++). If you can't do it in C, it's not worth doing. Search the net for "Real Programmers Don't Use Pascal" to see what I mean.
First, check the DBMS's documentation. Especially Vertica's, which is really detailled.
Then, create SQL functions. And - only as a last resort, code a User Defined Extension.
The best architecture you can build is simple.
The best code is the one you don't have to develop.
Ansering to your comment you posted in the meantime:
And for whatever might be missing- try User Defined Extensions - in Python, in your case:
and:
