Team Exasol
Team Exasol

Background

Exasol comes with a variety of programming languages. Especially for new users, it can sometimes be hard to understand, when to use which programming language. So here's a little guide for helping you choose the right language for your task.

Explanation

This article focuses mainly on the possibilities of using programming languages inside the database.

There are mainly three different usecases where you want to write code in a programming language inside the database: Scripting, UDFs and Functions.

exa-Valerie_1-1590487368869.png

Scripting

For Scripts that run inside the database you will use the programming language Lua. You can use them to execute multiple SQL commands sequentially and to handle errors during the executions. You can run control jobs within the database (for example, complex loading processes) and simplify the repetitive jobs by parameterized scripts, such as, creating a user with its password and privileges.

UDFs

For UDFs, you have the choice between a whole bunch of different programming languages. Each language offers it's own benefits.

Python for example scores with a very large number of libraries, which users can also add themselves. This makes it very comfortable to work with complex tasks as the comlexity can be handled inside of the library. Examples here are collecting data from a REST-API, applying data science models or parsing HTML data.

R is a great language for people that are used to writing their analysis in R already. So this is often used for projects regarding data science and statistics.

Lua differs a little from the others here as it is very natively integrated into Exasol and has the lowest overhead compared to other languages, so it is often a good choice if you want to optimize that last bit of performance. For this purpose, users cannot add their own libraries to Lua.


Functions

Exasol also offers the possibility to write basic functions in SQL. Functions are more limited than UDFs in terms of what you can achieve with them, but they can be a good choice if you want to do some kind of mapping, like recreating a function that you have used in another database where Exasol's syntax is a little different. A set of SQL functions can be found in our Github Repository exa-toolbox . There we collected some functions that can be useful when migrating from SQL-Server to Exasol.

 

Additional References

Links to our documentation on:

Comments
Contributor

Hello Valerie

Many thanks. This makes it easier to select the right tool/language for the right purpose. As we are about to introduce EXASOL in our company I was asked a lot which language we should choose.

Kind Regards

Gallus

Team Exasol
Team Exasol

Hello Gallus,

I'm glad the article is helpful for you and your colleagues 🙂 I'm sure my colleagues have mentioned it already, but if your colleagues are just starting with Exasol, I can highly recommend the Exacademy courses Database Development and Advanced Analytics to get a deeper knowledge about how to use Scripting, UDFs and Functions.

Best regards,

Valerie