API-CSV, the new and scalable way of getting data on EBS-50/ESL Web Server
Publish date: 11-07-2022
Opticon has integrated a new way of getting product data on the EBS-50/ESL Web Server. This new feature was designed with scalability and efficiency in mind. Let’s take a look at how API-CSV achieves this, and how it compares to the existing options.
Scalibility of existing solutions
SQL – The most well-known way of getting data is through an SQL connection to a database server. SQL has been around for decades and is known for its stability. It does have some drawbacks; by default a database can only handle one transaction (data change) at a time to ensure the data is consistent, so scaling becomes an issue if you have many transactions going. If ESL Web Server wants to find out if there are changes you have two options; download the entire product table and compare it with the copy you currently have (which consumes a lot of data) or use a staging table, which only lists changes and saves on data massively. However, the staging table is emptied afterwards, as the changes have been processed. This means that each ESL Web Server needs its own staging table to ensure it stays up to date. Each extra table in the database is work that has to be done on the client’s side. Conclusion: scaling is possible but requires extra setup work on both database side and ESL Web Server configuration if you want to save data.
CSV – A very easy and portable way of handling data, a CSV file is a simple text file representing lines of data in a database table. Most databases have a function to output tables as CSV files; after exporting you simply hand the CSV file over to the ESL Web Server. This way you don’t need a fully-fledged connection between your database and the ESL Web Server. Scaling becomes an issue, as with each change to the original database you need to send over files to each EBS-50/ESL Web Server that depends on the data. Whenever you add a new EBS-50 to the list, the customer needs to make sure that it’s added to the update list. Conclusion: scaling is possible, but every database change and every new EBS-50 needs to be updated from the client’s side.
SFTP – Instead of sending CSV files to the ESL Web Server, you instruct the ESL Web Server to connect to a server, and where on that server it can find the CSV database file. With the help of the configuration backup, every EBS-50/ESL Web Server you add can simply copy this way of working. Scaling is made super easy! The downside is that you only synchronize on a single file; the one containing the entire database. This means that the data use is a lot higher, since each synchronization will download everything, even if only a handful of lines have actually changed. Conclusion: scaling is easy, but data use is high. Please note that this functionality will soon receive an update as well!
API-CSV – With API-CSV you tell the EBS-50 the location of a web server (an endpoint) that supplies CSV files to be used as a database. This sounds similar to SFTP, right? The innovation is in the way the request for data is sent. If you want to get the entire database, ESL Web Server simply says “give me the data”. If a recent copy is already present, ESL Web Server says “I have a copy dated at July 1st, send me the changes since that time”, greatly reducing the amount of data sent. Now you get the best of both worlds; you save data and scaling is super-easy as each individual EBS-50 will keep track of their own database and its age, requesting only the data that it needs. Conclusion: scaling becomes easy, and data saving is built in. The only investment for the customer is setting up the endpoint, and once it’s up and running getting data on lots of ESL Web Servers/EBS-50’s is a piece of cake!
Read the full document here!