3 ms·

couple questions re airflow from a guy coming from Informatica/SSIS world: 1. Does airflow have native (read high speed) connectors to destination databases (o

couple questions re airflow from a guy coming from Informatica/SSIS world: 1. Does airflow have native (read high speed) connectors to destination databases (oracle, mysql, mssql) ? 2. How the typical ETL in Airflow compares to one in Informatica/SSIS in terms of speed of development, performance (throughput and latency), memory consumptipon? Is it the same speed, or slower due to using Python interpreter? 3. Is it easy or hard to use parallel transformations with processes/threads/async ? For example, ingest data from your source in 20 threads at once, as opposed to serial processing

oxfordmale 6y ago
1. Airflow uses the "default" connectors for destination databases, for example psycopg2 for Postgres. You can easily write your own hooks using whatever connector you fancy 2. It depends on your set up. I move most of the heavy lifting to SQL or Spark, so it is as performance as Informatica/SSIS 3. I have written multi threaded ETL processes using the PythonOperator. That basically starts off any Python script you want, allowing you the full flexibility of Python.
- somurzakov 6y ago
  can I ask a question about your #2. It seems you do ELT with spark/sql doing the T part after loading. Is your loading part high performance or do people even care whether it is fast or not? In my experience, when I extract and load data as is (for example into SQL Server) - it is kinda slow, because the columns have to be wide and generic, to accommodate all the crap that can come in. For example, I noticed that loading 1M rows into nvarchar(2048) is way slower, than into varchar(50). Let's say you have one column that usually does not exceed 50 chars, but sometimes it can be crap data and be 2000 chars. What is the best scenario to ELT it quickly? What I found is that if data is high quality - then ELT is totally fine, often times it ends up being just EL without much T. But if the data is crap, and you have a lot of wide columns, then even loading it takes time, before we even get to processing stage. In this scenario ETL works much faster.
  - oxfordmale 6y ago
    There is two approaches we follow. The first approach, which is quite slow, bulk extract the data to S3 and then run our transforms on top of that. If we need high performance, we write a delta streamer that only streams modified records.

oxfordmale 6y ago

1. Airflow uses the "default" connectors for destination databases, for example psycopg2 for Postgres. You can easily write your own hooks using whatever connector you fancy 2. It depends on your set up. I move most of the heavy lifting to SQL or Spark, so it is as performance as Informatica/SSIS 3. I have written multi threaded ETL processes using the PythonOperator. That basically starts off any Python script you want, allowing you the full flexibility of Python.

somurzakov 6y ago
can I ask a question about your #2. It seems you do ELT with spark/sql doing the T part after loading. Is your loading part high performance or do people even care whether it is fast or not? In my experience, when I extract and load data as is (for example into SQL Server) - it is kinda slow, because the columns have to be wide and generic, to accommodate all the crap that can come in. For example, I noticed that loading 1M rows into nvarchar(2048) is way slower, than into varchar(50). Let's say you have one column that usually does not exceed 50 chars, but sometimes it can be crap data and be 2000 chars. What is the best scenario to ELT it quickly? What I found is that if data is high quality - then ELT is totally fine, often times it ends up being just EL without much T. But if the data is crap, and you have a lot of wide columns, then even loading it takes time, before we even get to processing stage. In this scenario ETL works much faster.
- oxfordmale 6y ago
  There is two approaches we follow. The first approach, which is quite slow, bulk extract the data to S3 and then run our transforms on top of that. If we need high performance, we write a delta streamer that only streams modified records.