0

We are in the process of designing an ETL process, where we’ll be getting a daily account file (maybe half a million records, could grow) from client and we’ll be loading that file to our database.

Our current process splits the file into smaller files and load it to staging...sometime or if the process fails, we try to figure out how many records we have processed and then start again from that point. Is there any other better alternative to this problem?

We are thinking about using Kafka. I’m pretty new to Kafka. I would really appreciate some feedback if kafka is the way to go or we’re just over-killing a simple ETL process where we just load the data to a staging table and finally to destination table.

Himan
  • 379
  • 1
  • 7
  • You should understand what you are trying to achieve before you apply a tool to solve a problem: Kafka is an event streaming platform. What events are you looking to process? When these events occur, what processing do you need to perform? – Kevin Hooke Mar 12 '20 at 23:14
  • well the goal is to simply process and load the daily file. I'm just curious to know if Kafka is the way to go. It's not live data, or any sort of stream data...just simple account data in a daily file to update all the accounts. – Himan Mar 13 '20 at 17:30
  • 1
    Instead of a daily file, if you can receive updates for each individual event as they occur then you could process them in near real time, moving the approach from a once a day batch job to a near real time streaming approach - this would be a good use case for Kafka – Kevin Hooke Mar 13 '20 at 17:46

2 Answers2

0

Apache Kafka® is a distributed streaming platform. What exactly does that mean?

A streaming platform has three key capabilities:

Publish and subscribe to streams of records, similar to a message queue or enterprise messaging system.

Store streams of records in a fault-tolerant durable way.

Process streams of records as they occur.

Kafka is generally used for two broad classes of applications:

Building real-time streaming data pipelines that reliably get data between systems or applications

Building real-time streaming applications that transform or react to the streams of data

https://kafka.apache.org/intro

If you encounter errors which make you check the last commited record to your staging database and need system to auto manage this stuff, Kafka can help you ease the process.
Though Kafka is built to work with massive data loads and spread across a cluster, you certainly can use it for smaller problems and utilize it's queuing functionalities and offset management, even with one broker (server) and low number of partitions (level of parallelism).
If you don't anticipate any scale at all, I would suggest you to consider RabbitMQ.

RabbitMQ is a message-queueing software also known as a message broker or queue manager. Simply said; it is software where queues are defined, to which applications connect in order to transfer a message or messages.

https://www.cloudamqp.com/blog/2015-05-18-part1-rabbitmq-for-beginners-what-is-rabbitmq.html

“How to know if Apache Kafka is right for you” by Amit Rathi
https://link.medium.com/enGzNaNvT4

In case you chose Kafka:
When you receive a file, create a process which iterates all over it's lines and sends them to Kafka (Kafka Producer).
Create another process which continuously receive events from kafka (Kafka Consumer) and writes them in mini batches to the database (similar to your small files).

Setup Kafka:
https://dzone.com/articles/kafka-setup
Kafka Consumer/Producer simple example:
http://www.stackframelayout.com/programowanie/kafka-simple-producer-consumer-example/

Ofek Hod
  • 3,544
  • 2
  • 15
  • 26
0

Don't assume importing data is as easy as dumping it in your database and having the computer handle all the processing work. As you've discovered, an automated load can have problems.

First, database ELT processes depreciate the hard drive. Do not stage the data into one table prior to inserting it in its native table. Your process should only import the data one time to its native table to protect hardware.

Second, you don't need third-party software to middle-man the work. You need control so you're not manually inspecting what was inserted. This means your process is to first clean / transform the data prior to import. You want to prevent all problems prior to load by cleaning and structuring and even processing the data. The load should only be an SQL insert script. I have torn apart many T-SQL scripts where someone thought it convenient to integrate processing with database commands. Don't do it.

Here's how I manage imports from spreadsheet reports. Excel formulas are better than learning ETL tools like SSIS. I use cell formulas to validate whether the record is valid to go into our system. This result is its own column, and then if that column is true, a concatentation column displays an insert script.

    =if(J1, concatenate("('", A1, "', ", B1, "),"), "")

If the column is false, the concat column shows nothing. This allows me to copy/paste the inserts into SSMS and conduct mass inserts via "insert into table values" scripts.

If this is actually updating existing records, as your comment appears to suggest, then you need to master the data, organizing what's changed in logs for your users.

Synchronization steps:

  1. Log what is there before you update

  2. Download and compare local vs remote copies for differences; you cannot compare the two without a) having them both in the same physical location or b) controlling the other system

  3. Log what you're updating with, and timestamp when you're updating it

  4. Save and close the logs

  5. Only when 1-4 are done should you post an update to production

My guide to synchronizing data sources and handling Creates/Updates/Deletes: sync local files with server files

RBJ
  • 128
  • 6