How to join Kafka input stream with big Oracle lookup table?

[EyalGonen]

Hi experts,

We have ESP 6.2 engine running and reading events from a Kafka topic. The incoming events need to be joined with a 5M row Oracle table for data enrichment.

Question is whether it is possible somehow to accomplish an efficient join in Oracle instead of pulling the 5M row Oracle table to an ESP window and then doing the join in ESP?

Thanks,

Eyal

[SASKiwi]

How many rows from ESP do you wish to join? How often does the Oracle table get updated? As a general rule I would copy the smaller table into the bigger table's environment.

[jbhattacharya]

Hi Eyal,

In 6.2, depending upon the performance level that you want, you can try calling SQL from Python or I think even from DS2 for querying in the DB itself. There may be a server level Lua support too but not sure which build of 6.2 you are in.

Are you trying for a functionality to lookup certain entry in DB based on key from ESP events? If yes then it may be good to know that in ESP for Viya 4 we have two more ways to do that. One way is through a couple of dedicated windows called StateDB windows which can integrate with a fast in-memory DB like Redis to give pretty good performance for such operations. You can get more info about it here: sassoftware/iot-sas-esp-integration-with-in-memory-databases: In this project, we present two new ES....

Another is through Lua code which supports database calls about which you can find in the ESP documentation.

Thanks,

Joydeep

[EyalGonen]

Hi @jbhattacharya 

Thanks for the detailed reply. The join with the Oracle table is by a key from ESP field (from Kafka) and used to enrich the incoming event data with additional info and then this Oracle info is used to filter out some events and in some ESP additional logic. The Oracle is updated/refreshed once a day in the DBMS.

 Can you point me to an example on how to use Python/DS2 in an ESP window? Specifically, how can I use the input event field to inject an SQL into Oracle using its value?

Thanks!

[jbhattacharya · Accepted solution]

Hi Eyal,

I am attaching an example with Python. To set your ESP to use Python you have to follow the documentation here: SAS Help Center: Overview

You will also need to install python modules related to Oracle which probably you can find here: https://www.geeksforgeeks.org/oracle-database-connection-in-python/

In this sample I have used Postgres database as I have access to one but the idea is very much similar. For that I installed pycopg2 Python module in the vm where ESP is running. The attachments contain a sample ESP model and an external Python code that will be called. You can also embed the Python code inside the model. This model is developed quickly for a sample so it may not adhere to some of the best practices.

Hope this is what you require.

Thanks,

Joydeep

[EyalGonen]

Hi @jbhattacharya 

Thanks! we are looking into your suggestion. In the meantime, quick question. Is it possible to have the embedded MAS (Python) maintain a pool of connections to the DB? In your example will Python open a new connection to the DB each time it is invoked by ESP?

[jbhattacharya]

In the example only the function is called for each event. The other part of the code gets called only once I believe at the time of the model initialization.