12 ms·

Mara: A lightweight ETL framework, halfway between plain scripts and Airflow

276 points by stadeschuldt 8y ago

shadowmint 8y ago
...but why? Just use airflow. Things I want in an ETL: [x] works at scale. [x] simple to use. [x] not written in python (eg. in go or rust) [x] easy to scale (eg. in docker) [ ] this.
- lasermike026 8y ago
  Go is nice, Rust is interesting, and Python is fine.
  - shadowmint 8y ago
    python is slow and single threaded, which is a tangible problem for an ETL, and airflow specifically. ...airflow is good enough for other reasons, but that doesn’t mean ‘not python’ gets off the wish list.
    - occams_chainsaw 8y ago
      Are you not using celery with airflow?
    - SmirkingRevenge 8y ago
      Generally the workflow framework ought to be doing very little compared to the jobs it orchestrates and executes. In the common use cases, airflow is often only a little more than an (distributed) executor/scheduler for bash commands. Wouldn't the value of optimization at the framework level, be generally... very little?
      - samuell 8y ago
        Not always. When the workflow tool is a central scheduling point for thousands of tasks fired off to other compute nodes (via some distribution mechanism), you can start to experience pain. There is also a problem with python that it does not do thread-based concurrency, but only process-based ditto, where the processes need to communicate in a less robust way with each other, e.g. via HTTP. Because of this, in our work (scheduling machine learning workflows on HPC clusters), we started see lost HTTP-connections between workers with Luigi when going over 64 workers, even though these workers were doing nothing else than keeping track of a job running on another compute node. This lead us to use Go instead, with SciPipe, and it has none of these problems. Go being compiled also means we're catching a lot more stupid typos and such at compile time rather than at run time (like 7 days into a 7 day HPC job), which also makes our life easier. Python is definitely quite a lot easier to write and read, but the robustness of compiled Go code is hard to beat, and I don't wish to go back to a python-based tool.
        baq 8y ago
        > There is also a problem with python that it does not do thread-based concurrency, but only process-based ditto, where the processes need to communicate in a less robust way with each other, e.g. via HTTP. this is half true, and importantly, in a workflow tool, the part where python is perfectly capable of being multithreaded while waiting on IO (which releases the GIL) should be quite sufficient. there are problems with python like its memory usage and with airflow specifically like its at times flaky scheduler (maybe that's fixed in newer versions), but multithreading shouldn't be one in this particular context.
        artwr 8y ago
        The scheduler has seen a lot of improvements in the past couple of releases, and release 1.10 should be coming out soon. (full disclosure: I work on Airflow)
        SmirkingRevenge 8y ago
        Great points. Scaling indefinitely will inevitably expose inefficiencies, even tiny ones. And even tiny inefficiencies will place a hard limit on your ability to scale.
    - cdancette 8y ago
      python in airflow is just used to define your graph and trigger your jobs. I think it's a wonderful language to do this. You can (and should) program your batch jobs in other languages than python if you have large amount of data of course.
    - lasermike026 8y ago
      Performance issues can be overcome with Cython or Pypy. "Python is slow." What do you mean by "slow" and what have you done to make Python code run faster? I find for most things Python is fast enough or performance can be improved to meet most compute and data demands. In the future we will all be using Javascript. Language performance debates are moot.
      - riku_iki 8y ago
        some other languages (e.g. java) don't need much untrivial effort from you to work fast.
- samuell 8y ago
  There are a few pipeline tools/frameworks written in Go: http://gopherdata.io/post/more_go_based_workflow_tools_in_bioinformatics http://gopherdata.io/post/more_go_based_workflow_tools_in_bi... (Full disclosure: I'm developing SciPipe)
- liveoneggs 8y ago
  [ ] has a way to test data connections before trying to use them [ ] has a clean way to do releases [ ] can handle more complex network setups (like found in docker envs)
- busterarm 8y ago
  https://github.com/thbar/kiba https://github.com/thbar/kiba This is still my workhorse and has never let me down. I do a ton of ETL. https://github.com/thbar/kiba-ex https://github.com/thbar/kiba-ex This looks interesting though.
  - thibaut_barrere 8y ago
    Kiba author here - your comment made my day, so thanks!
    - busterarm 8y ago
      :D Thank you!!!
- stadeschuldt 8y ago
  Python is more a glue in this case. Most operations are basically unix pipes pushing data in and out of Postgres
- JustSomeNobody 8y ago
  > ...but why? You ask this generally, but then you give a bunch of specific reasons why you don't want to use it.
  - wokwokwok 8y ago
    yes, but it doesn't do any of the things on the list.
- nostalgeek 8y ago
  > [x] not written in python (eg. in go or rust) Don't you need some kind of scripting capabilities though to create custom pipelines without having to recompile all the app each time you had a new one?
- kenhwang 8y ago
  You hit my wishlist almost exactly. A strongly typed, not java, ETL framework would be my dream. Plus, works at scale and easy to scale almost becomes a non-issue because we wouldn't be choked by the slowness and bloat of python.
  - CMCDragonkai 8y ago
    Funflow is written in Haskell https://www.tweag.io/posts/2018-04-25-funflow.html https://www.tweag.io/posts/2018-04-25-funflow.html
jmartrican 8y ago
Does not seem lightweight. Maybe its light compared to the other solutions?
notamy 8y ago
What exactly IS an "ETL framework"? I looked at both this project and Apache Airflow, and I'm not quite sure I understand...
- Diederich 8y ago
  https://en.wikipedia.org/wiki/Extract,_transform,_load https://en.wikipedia.org/wiki/Extract,_transform,_load
- abd12 8y ago
  To me, it's easiest to think of it as "Make, for data". Your ETL pipelines are often a complex graph of dependencies. If some step halfway down the chain fails, you don't want to restart at the very beginning -- you want to restart where it failed and keep moving. The ETL frameworks (Airflow, Luigi, now Mara) help with this, allowing you to build dependency graphs in code, determine which dependencies are already satisfied, and process those which are not. They'll usually contain helper code for common ETL tasks, such as interacting with a database, writing to/reading from S3, or running shell scripts.
  - martin_loetzsch 8y ago
    (author here). Mara data integration is indeed a glorified version of Make (with cost based scheduling and lots of visualizations)
- shadowmint 8y ago
  Basically software for batch processing a tonne of data. eg. importing a SAP feed into a database, or loading a bunch of csv files, or like processing a bunch of images... ...anything where you have to convert data from some source through a series of steps (typically a DAG) into some useful output. However, its often misused. For example, if you have a trivial amount of data, or trivial process an ETL is over engineered cruft where a simple script would do. So there are many (rubbish) ‘simple ETL frameworks’ which offer zero value and technical complexity for no benefit. Unless you need multiple servers processing data through multiple steps and you need the auditing and process control... you probably don’t need an ETL, just a simple script.
samuell 8y ago
Send a pull request to add it to https://github.com/pditommaso/awesome-pipeline/blob/master/README.md https://github.com/pditommaso/awesome-pipeline/blob/master/R...
- stadeschuldt 8y ago
  done
leblancfg 8y ago
That looks very interesting indeed, and love to see some more development in that space. Would anyone care to explain how it differs from Airflow? I had dismissed Airflow some months ago for my use case (Windows, and large number of dependencies were an issue at the time), but would still like to eventually migrate my ETL scripts to a solid framework sometime in the future.
- stadeschuldt 8y ago
  Mara is designed to be simpler than Airflow. It positions itself between plain scripts and Airflow.
  - huac 8y ago
    What features does it forgo to be 'simpler'?
    - artwr 8y ago
      The main feature which jumps out at me, I think it schedules on a single node, where Airflow can orchestrate across multiple nodes. Airflow is also starting to implement more enterprise-y features, like role-based access control, which should be coming to the next release.
      - martin_loetzsch 8y ago
        (author here) That's absolutely correct. Mara uses Python's multiprocessing [1] to parallelize pipeline execution [2] on a single node so it doesn't need a distributed task queue. Beyond that (and visualization) it can't do much. In fact it doesn't even have a scheduler (you can use jenkins or cron for that). [1] https://docs.python.org/3.6/library/multiprocessing.html https://docs.python.org/3.6/library/multiprocessing.html [2] https://github.com/mara/data-integration/blob/master/data_integration/execution.py https://github.com/mara/data-integration/blob/master/data_in...
- artwr 8y ago
  With a little bit of work you could probably trigger tasks on a Windows Machine, in particular if the work you need to do is mostly script and/or SQL based. The main limit is that Gunicorn for the Airflow webserver is not currently compatible with Windows, but the scheduler should work fine. I believe it might be able to run in the Windows Subsystem for Linux, but I don't think anyone has tested it as of yet. (Source: I am an Apache Airflow committer)
cosmie 8y ago
I really like this! I bootstrapped the ETL and data pipeline infrastructure at my last company with a combination of Bash, Python, and Node scripts duct-taped together. Super fragile, but effective[3]. It wasn't until about 3 years in (and 5x the initial revenue and volume) that it started having growing pains. Every time I tried to evaluate solutions like Airflow[1] or Luigi[2], there was just so much involved with getting it going reliably and migrating things over that it just wasn't worth the effort[4]. This seems like a refreshingly opinionated solution that would have fit my use case perfectly. [1] https://airflow.apache.org/ https://airflow.apache.org/ [2] https://github.com/spotify/luigi https://github.com/spotify/luigi [3] The operational complexity of real-time, distributed architectures is non-trivial. You'd be amazed how far some basic bash scripts running on cron jobs will take you. [4] I was a one man data management/analytics/BI team for the first two years, not a dedicated ETL resource with time to spend weeks getting a PoC based on Airflow or Luigi running. When I finally got our engineering team to spend some time on making the data pipelines less fragile, instead of using one of these open source solutions they took it as an opportunity to create a fancy scalable, distributed, asynchronous data pipeline system built on ECS, AWS Lambda, DynamoDB, and NodeJS. That system was never able to be used in production, as my fragile duct-taped solution turned out to be more robust.
- BerislavLopac 8y ago
  This might be useful: https://cloud.google.com/blog/big-data/2018/05/cloud-composer-is-now-in-beta-build-and-run-practical-workflows-with-minimal-effort https://cloud.google.com/blog/big-data/2018/05/cloud-compose...
  - cosmie 8y ago
    That looks like a nifty service, but we were an AWS shop and the bandwidth intensity of ETL would have made a GCP-hosted service cost prohibitive. That said, at least once a month I tried to convince our CTO to let me move our data workloads over to GCP due to all the nifty managed services they have available. I was primarily munging data between FTP drops, S3, RDS, and Redshift, which mainly fell into free buckets for internal data transfer.
mistrial9 8y ago
the docs animations inline add to casual-browsing fun https://github.com/mara/mara-example-project https://github.com/mara/mara-example-project
occams_chainsaw 8y ago
Seems interesting, but I'll have to dismiss it for now due the total lack of tests
foolinaround 8y ago
if this had a scheduler and an alerting mechanism when SLAs are breached, that would be great! maybe future features?
- martin_loetzsch 8y ago
  (author here) It intentionally doesn't have a scheduler, just definition and parallel execution of pipelines. For scheduling, use Jenkins, cron or Airflow. Currently you can get notifications for failed runs in slack. Alerting itself is not really in the scope of this project, but it should be easy to implement in a project.
mooreds 8y ago
I'd love this, except for ruby. Anyone know of anything like this?
- busterarm 8y ago
  https://github.com/thbar/kiba https://github.com/thbar/kiba This has been the standard for years
- kakoni 8y ago
  Embulk (https://www.embulk.org https://www.embulk.org) - Define your datapipelines with yaml + ruby (using ruby proc filter) - Functionality can be extended with ruby(or java) based plugins. Been using this for few years, works beautifully.
- thibaut_barrere 8y ago
  I wrote Kiba specifically for this. I will share a good bunch of current use cases at Kaigi 2018 if you are interested!
stadeschuldt 8y ago
Here is a conference talk presenting the framework and the ideas behind: https://youtu.be/GdtFuOah-5c https://youtu.be/GdtFuOah-5c
porker 8y ago
What is there in the ETL space with bi-directional sync? I don't usually run into problems where "transfer data from X to Y" is it. Usually it's "there's data in CRM X and data in Event system Y, merge the two keeping X as the master source" There's Mulesoft et al but they seem overkill for small deployments, as well as being stupidly expensive [1]. 1. I'm sure they're good value if you're an enterprise company. But if you don't need the UI builder and only 2-3 sources kept in sync, they are expensive. And with my reading of the documentation, conflict resolution isn't great either.
- ptrott2017 8y ago
  Kettle - the open source component of Hitachi Pentaho Data Integration is worth looking at, has some functionality for this (you can join sources and insert joined data back into master) and its pretty easy to extend to meet requirements. Its Apache licensed, with great commercial support if you need it, and can be found here: https://github.com/pentaho/pentaho-kettle https://github.com/pentaho/pentaho-kettle We are small shop but it is a key part of our data workflows. We also found spoon the UI workflow builder to be very helpful for building workflows - since its allowed team members who were not strong in Java to build workflows that they need. Last but not least and a key decider in our adoption is the community is super friendly and very helpful. Something we thought would take months got implemented in a weekend thanks to quick feedback. Perhaps worth a look.
  - karambahh 8y ago
    As a long time Kettle user (probably close to 10 years) I must warn potential users that the learning curve is steep and that (as any large body of code) it contains code that sometimes can run unpredictably. I got good at diagnosing user induced bugs in PDI transformation via reading the stacktrace but it is not to everyone liking. To me, a very strong regression is the "new" UI which switched from meaningful icons to a blue & white scheme that makes reading/discovering new transformation a real pain: all is a blur of blue without the past color cues that you learned in the past ("ok, this is the icon for a merge from a source file & a database sent to an ES cluster" became "some stuff is read from blue sources and sent to some blue output") I recently learned about the capability to run transformation into a spark cluster that replace the original engine by a new spark implementation, bring obvious compute optimization for large enough dataset but I don't have enough experience with it to speak of it positively or negatively.
thalesmello 8y ago
As the one who implemented Airflow at my company, I understand how overwhelming it can be, with the DAGs, Operators, Hooks and other terminologies. This looks like a good enough mid-term alternative. However, I have a few questions (which I couldn't find easily in the homepage, sorry if I skipped something): - Do you have a way of persisting connection information? I saw an example of how to create a connection, but it isn't clear if the piece of code has to be loaded every time you execute the ETL - How easy it is to implement new computation engines? - Plans of creating a command line to make it easier to execute operations?
- martin_loetzsch 8y ago
  (author here) Connection information is configured in code through [1], see [2] for an example. It's very easy to run other workloads. Either by directly invoking Python functions from tasks or by writing own commands (operators)[3]. There is a command line. It's the interface for running from external schedulers (jenkins, cron)[4] & [5] [1] https://github.com/mara/mara-db https://github.com/mara/mara-db [2] https://github.com/mara/mara-example-project/blob/master/app/local_setup.py.example https://github.com/mara/mara-example-project/blob/master/app... [3] https://github.com/mara/data-integration/blob/master/data_integration/pipelines.py#L47 https://github.com/mara/data-integration/blob/master/data_in... [4] https://github.com/mara/data-integration/raw/master/docs/example-run-cli-1.gif https://github.com/mara/data-integration/raw/master/docs/exa... [5] https://github.com/mara/data-integration/raw/master/docs/example-run-cli-2.gif https://github.com/mara/data-integration/raw/master/docs/exa...
  - neuromantik8086 8y ago
    Perhaps this is addressed elsewhere, but do you have any plans to support Common Workflow Language?
pgwhalen 8y ago
Is anyone out there doing (or considering) streaming ETL as opposed to batch?
- artwr 8y ago
  I think a lot of people are considering it. The ideas behind Apache Beam are supposed to make the transition easier, with the ability to run in either mode. I think in practice it really depends on your use case. For a large enough amount of data, especially on the ingest side, running it streaming makes sense. Now for that daily report from aggregated table, do you really need streaming ETL/aggregation is another question. The operational cost is non trivial with streaming.
  - pgwhalen 8y ago
    Can you expand on that last sentence? Not that I’d disagree, I’m just curious about what specifically you’re referring to. My team is currently underwater supporting a decades old system of batch ETL, where it’s a major challenge to understand dependencies between jobs, and therefore what to rerun when something fails. We’re exploring a move to streaming ETL (Kafka streams being the leading candidate) as a way of making the dependencies explicit: rather than having to rerun stored procs or Talend jobs that we depend on certain data, we run a fleet of 24/7 microservices that transform and move data downstream whenever it is available.
- woqe 8y ago
  Apache nifi (https://nifi.apache.org/ https://nifi.apache.org/) is a software which handles streaming ETL processing. From a previous post I made about it: "We have HTTP endpoints set up to receive data from our ERP's accounting system to send data to Concur and to update customers' Lawson punchout ordering systems with shipment information. The 'E' is an HTTP post with an XML payload. The 'T' consists of using the payload to query other databases to build the 'L' payload, and the 'L' is an HTTP post to the consumer's endpoints."
  - chrisjc 8y ago
    There's often a misconception about Nifi being batch or file based and not streaming. This probably originates as result of data in Nifi being represented as a FlowFile, which is kind of a misnomer. As woqe stated above, Nifi can certainly doing streaming.
antoncohen 8y ago
I'm sure this is right for someone, everyone has different requirements, but I don't really want a lighter-weight Airflow. I want an Airflow that runs and scales in the cloud, has extensive observability (monitoring, tracing), has a full API, and maybe some clear way to test workflows. I was looking into how Google's Cloud Composer is run, which is a managed Airflow service. They use gcsfuse to mount a directory for logs, because Airflow insists on writing logs to local disk with no cleanup system, even if you configure logs to be sent to S3/GCS. To health check the scheduler they query Stackdriver Logging to see if has logged anything in the last five minutes, because the scheduler has no /healthz or other way to check health. There is no built it way to monitor workflows, so you can't easily do something like graph failures by workflow, email on failure is about all you get. A GUI-first app that requires local storage is not what I expect these days.
- phunge 8y ago
  +1 to this, I've had multiple teams that have shied away from airflow because of its operability & deployment story, despite needing something that has all its features. In terms of radically different takes on workflow engines, I'm very interested in reflow. I haven't used it enough to know if the rough edges are a deal breaker.
- occams_chainsaw 8y ago
  There is no built it way to monitor workflows, so you can't easily do something like graph failures by workflow Not sure if this meets your criteria, but the tree view allows you to see a historical graph of job runs and status
- mariusae 8y ago
  > I want an Airflow that runs and scales in the cloud I'd encourage you to look at Reflow [1] which takes a different approach: it's entirely self-managing: you run Reflow like you would a normal programming language interpreter ("reflow run myjob.rf") and Reflow creates ephemeral nodes that scale elastically and that tear themselves down, only for the purpose of running the program. > has extensive observability (monitoring, tracing) Reflow includes a good amount of observability tools out of the box; we're also working on integrating tracing facilities (e.g., reporting progress to Amazon x-ray). > has a full API, and maybe some clear way to test workflows. Reflow's approach to testing is exactly like any other programming language: you write modules that can either be used in a "main" program, or else be used in tests. [1] https://github.com/grailbio/reflow https://github.com/grailbio/reflow
samuell 8y ago
I really like the interactive terminal-based menu, that's neat!
frugalmail 8y ago
Just thought I would add: If you want something more serious that supports better scale, realtime/streaming, is written in a statically typed language check out https://kylo.io/ https://kylo.io/ it's added on to Apache NiFi (which was developed by our friends at the NSA)
mariusae 8y ago
Reflow [1] is also well-suited for ETL workloads. It takes a different tack: it presents a DSL with data-flow semantics and first-class integration with Docker. The result is that you don't write graphs, instead you just write programs that, due to their semantics, can be automatically parallelized and distributed widely, all intermediate evaluations are memoized, and programs are evaluated in a fully incremental fashion: [1] https://github.com/grailbio/reflow https://github.com/grailbio/reflow
endlessvoid94 8y ago
In my experience one of the places where every framework breaks down is when you must combine / reconcile multiple rows from multiple data sources to produce one row in a fact table. Does there exist a "framework" that lets me do this simply?
- martin_loetzsch 8y ago
  (author here) The mara example project [1] does exactly that. It combines PuPI download stats with Github repo activity data. [1] https://github.com/mara/mara-example-project https://github.com/mara/mara-example-project
  - endlessvoid94 8y ago
    Thanks! Just took a look. The file directory structure is a bit confusing -- could you point me to the file that performs this transformation?
    - martin_loetzsch 8y ago
      For example the PyPI download stats pipeline is here: https://github.com/mara/mara-example-project/tree/master/app/data_integration/pipelines/pypi https://github.com/mara/mara-example-project/tree/master/app... The __init__.py contains the pipeline, and the rest is the SQL files that do the transformations
      - endlessvoid94 8y ago
        Thank you!
larsf 8y ago
Composable is amazing at ETL - https://composableanalytics.com https://composableanalytics.com It blows things like Alteryx, NiFi, Airflow out of the water.
- mattbillenstein 8y ago
  Please add a disclaimer if you work for these guys... I personally generally dislike enterprise solutions because I hate talking to sales people - but that's just me.
  - larsf 8y ago
    Yes - I'm the founder. And you don't have to talk to me if you don't want to :)
groodt 8y ago
I'm interested in hearing thoughts from people who've used digdag (https://www.digdag.io/ https://www.digdag.io/) or pachyderm (http://www.pachyderm.io/ http://www.pachyderm.io/). Pachyderm is the most interesting to me. It seems to be focussing on the data as well as the data processing.
- jdoliner 8y ago
  I'm the founder and a core developer of Pachyderm so I can weigh in on how it compares (there is, of course, some potential bias here). I also was at Airbnb around the time we released Airflow, so I got to see it being built up close and used the system it was replacing quite a bit as well. I think it's fair to say that Mara and Airflow are both in the same category of DAG (directed acyclic graph) schedulers for Python; Python makes a ton of sense as the language to focus on as it's the de facto lingua franca for data science. I'd also put Luigi in that bucket, although I think Airflow has degraded its mind-share quite a bit. All of them are targeting the data pipeline use case, which is very well represented as a DAG, but the actual management of the data is left up to the user. They (Mara, Airflow, or Luigi) schedule tasks for you after all the tasks they depended on have completed, but you have to figure out where to store your data so that downstream tasks can find the data their upstream tasks outputted. At Airbnb we used HDFS as this storage layer, often with Hive or Presto on top. Storing in s3 is also a common pattern. Pachyderm is also a DAG scheduler but we're a lot more prescriptive about where you store the data, and a lot less prescriptive about what languages and tools you use. Pachyderm ships with its own distributed filesystem (pfs) that we use for storage, it does a few things that other storage solutions can't do. In particular, it version controls your data and records "provenance" i.e. where data comes from. For example if you train a machine learning model then its provenance will be the data you used to train it. In terms of processing we're much less prescriptive, because we let users express their code as a Docker container rather than only having bindings for one language. So you can use anything that you can fit in a Docker container. Data is exposed to your code via the local filesystem, so regardless of language you have a very natural interface to your data: system calls on files. Hope this helps understand the differences between the various systems and thanks for your interest in Pachyderm. Swing by our users slack channel [0] if you'd like some help getting started with it. [0] http://slack.pachyderm.io/ http://slack.pachyderm.io/
soobrosa 8y ago
What's the least verbose/boilerplate-heavy tool in your experience? We couldn't make it leaner than this (works well in production in scale). https://github.com/wunderlist/night-shift https://github.com/wunderlist/night-shift If we could get rid of Ruby in there (super useful for scripting) and fly with only Python I'd be the happiest person on Earth. Also we started to go cloud agnostic, it handles both AWS and Azure. Do you something that does AWS, Azure _and_ Google Cloud also?
- martin_loetzsch 8y ago
  GNU Make is indeed the least verbose/ boilerplate-heavy tool and I use it for a lot of things. The problem with Make is lacking acceptance amongst younger programmers who always want to work with the latest technologies.
nikolay 8y ago
How is this better than Singer [0]? [0]: https://www.singer.io/ https://www.singer.io/
tjr225 8y ago
This is slightly weird...but I named my dog Mara: https://flic.kr/p/FtdRNX https://flic.kr/p/FtdRNX
- martin_loetzsch 8y ago
  Also weird: it's the name of a giant ugly guinea pig: https://en.wikipedia.org/wiki/Mara_(mammal) https://en.wikipedia.org/wiki/Mara_(mammal)
reinhardt 8y ago
Why PostgreSQL only? The mara-DB dependency [1] claims to support more. [1] https://github.com/mara/mara-db https://github.com/mara/mara-db
- martin_loetzsch 8y ago
  (author here) Currently there is a hard dependency to Postgres for the bookkeeping tables of mara. I'm working on dockerizing the example project to make the setup easier. For ETL, Mysql, Postgres & SQL Server are supported (and it's easy to add more).
  - random4369 8y ago
    I'm a bit confused about this. What if the target is HDFS? Why this dependency on SQL databases for ETL?

276 points by stadeschuldt 8y ago

...but why? Just use airflow. Things I want in an ETL: [x] works at scale. [x] simple to use. [x] not written in python (eg. in go or rust) [x] easy to scale (eg. in docker) [ ] this.

lasermike026 8y ago
Go is nice, Rust is interesting, and Python is fine.
- shadowmint 8y ago
  python is slow and single threaded, which is a tangible problem for an ETL, and airflow specifically. ...airflow is good enough for other reasons, but that doesn’t mean ‘not python’ gets off the wish list.
  - occams_chainsaw 8y ago
    Are you not using celery with airflow?
  - SmirkingRevenge 8y ago
    Generally the workflow framework ought to be doing very little compared to the jobs it orchestrates and executes. In the common use cases, airflow is often only a little more than an (distributed) executor/scheduler for bash commands. Wouldn't the value of optimization at the framework level, be generally... very little?
    - samuell 8y ago
      Not always. When the workflow tool is a central scheduling point for thousands of tasks fired off to other compute nodes (via some distribution mechanism), you can start to experience pain. There is also a problem with python that it does not do thread-based concurrency, but only process-based ditto, where the processes need to communicate in a less robust way with each other, e.g. via HTTP. Because of this, in our work (scheduling machine learning workflows on HPC clusters), we started see lost HTTP-connections between workers with Luigi when going over 64 workers, even though these workers were doing nothing else than keeping track of a job running on another compute node. This lead us to use Go instead, with SciPipe, and it has none of these problems. Go being compiled also means we're catching a lot more stupid typos and such at compile time rather than at run time (like 7 days into a 7 day HPC job), which also makes our life easier. Python is definitely quite a lot easier to write and read, but the robustness of compiled Go code is hard to beat, and I don't wish to go back to a python-based tool.
      - baq 8y ago
        > There is also a problem with python that it does not do thread-based concurrency, but only process-based ditto, where the processes need to communicate in a less robust way with each other, e.g. via HTTP. this is half true, and importantly, in a workflow tool, the part where python is perfectly capable of being multithreaded while waiting on IO (which releases the GIL) should be quite sufficient. there are problems with python like its memory usage and with airflow specifically like its at times flaky scheduler (maybe that's fixed in newer versions), but multithreading shouldn't be one in this particular context.
        artwr 8y ago
        The scheduler has seen a lot of improvements in the past couple of releases, and release 1.10 should be coming out soon. (full disclosure: I work on Airflow)
      - SmirkingRevenge 8y ago
        Great points. Scaling indefinitely will inevitably expose inefficiencies, even tiny ones. And even tiny inefficiencies will place a hard limit on your ability to scale.
  - cdancette 8y ago
    python in airflow is just used to define your graph and trigger your jobs. I think it's a wonderful language to do this. You can (and should) program your batch jobs in other languages than python if you have large amount of data of course.
  - lasermike026 8y ago
    Performance issues can be overcome with Cython or Pypy. "Python is slow." What do you mean by "slow" and what have you done to make Python code run faster? I find for most things Python is fast enough or performance can be improved to meet most compute and data demands. In the future we will all be using Javascript. Language performance debates are moot.
    - riku_iki 8y ago
      some other languages (e.g. java) don't need much untrivial effort from you to work fast.
samuell 8y ago
There are a few pipeline tools/frameworks written in Go: http://gopherdata.io/post/more_go_based_workflow_tools_in_bioinformatics http://gopherdata.io/post/more_go_based_workflow_tools_in_bi... (Full disclosure: I'm developing SciPipe)
liveoneggs 8y ago
[ ] has a way to test data connections before trying to use them [ ] has a clean way to do releases [ ] can handle more complex network setups (like found in docker envs)
busterarm 8y ago
https://github.com/thbar/kiba https://github.com/thbar/kiba This is still my workhorse and has never let me down. I do a ton of ETL. https://github.com/thbar/kiba-ex https://github.com/thbar/kiba-ex This looks interesting though.
- thibaut_barrere 8y ago
  Kiba author here - your comment made my day, so thanks!
  - busterarm 8y ago
    :D Thank you!!!
stadeschuldt 8y ago
Python is more a glue in this case. Most operations are basically unix pipes pushing data in and out of Postgres
JustSomeNobody 8y ago
> ...but why? You ask this generally, but then you give a bunch of specific reasons why you don't want to use it.
- wokwokwok 8y ago
  yes, but it doesn't do any of the things on the list.
nostalgeek 8y ago
> [x] not written in python (eg. in go or rust) Don't you need some kind of scripting capabilities though to create custom pipelines without having to recompile all the app each time you had a new one?
kenhwang 8y ago
You hit my wishlist almost exactly. A strongly typed, not java, ETL framework would be my dream. Plus, works at scale and easy to scale almost becomes a non-issue because we wouldn't be choked by the slowness and bloat of python.
- CMCDragonkai 8y ago
  Funflow is written in Haskell https://www.tweag.io/posts/2018-04-25-funflow.html https://www.tweag.io/posts/2018-04-25-funflow.html

jmartrican 8y ago

Does not seem lightweight. Maybe its light compared to the other solutions?

notamy 8y ago

What exactly IS an "ETL framework"? I looked at both this project and Apache Airflow, and I'm not quite sure I understand...

Diederich 8y ago
https://en.wikipedia.org/wiki/Extract,_transform,_load https://en.wikipedia.org/wiki/Extract,_transform,_load
abd12 8y ago
To me, it's easiest to think of it as "Make, for data". Your ETL pipelines are often a complex graph of dependencies. If some step halfway down the chain fails, you don't want to restart at the very beginning -- you want to restart where it failed and keep moving. The ETL frameworks (Airflow, Luigi, now Mara) help with this, allowing you to build dependency graphs in code, determine which dependencies are already satisfied, and process those which are not. They'll usually contain helper code for common ETL tasks, such as interacting with a database, writing to/reading from S3, or running shell scripts.
- martin_loetzsch 8y ago
  (author here). Mara data integration is indeed a glorified version of Make (with cost based scheduling and lots of visualizations)
shadowmint 8y ago
Basically software for batch processing a tonne of data. eg. importing a SAP feed into a database, or loading a bunch of csv files, or like processing a bunch of images... ...anything where you have to convert data from some source through a series of steps (typically a DAG) into some useful output. However, its often misused. For example, if you have a trivial amount of data, or trivial process an ETL is over engineered cruft where a simple script would do. So there are many (rubbish) ‘simple ETL frameworks’ which offer zero value and technical complexity for no benefit. Unless you need multiple servers processing data through multiple steps and you need the auditing and process control... you probably don’t need an ETL, just a simple script.

samuell 8y ago

Send a pull request to add it to https://github.com/pditommaso/awesome-pipeline/blob/master/README.md https://github.com/pditommaso/awesome-pipeline/blob/master/R...

stadeschuldt 8y ago
done

leblancfg 8y ago

That looks very interesting indeed, and love to see some more development in that space. Would anyone care to explain how it differs from Airflow? I had dismissed Airflow some months ago for my use case (Windows, and large number of dependencies were an issue at the time), but would still like to eventually migrate my ETL scripts to a solid framework sometime in the future.

stadeschuldt 8y ago
Mara is designed to be simpler than Airflow. It positions itself between plain scripts and Airflow.
- huac 8y ago
  What features does it forgo to be 'simpler'?
  - artwr 8y ago
    The main feature which jumps out at me, I think it schedules on a single node, where Airflow can orchestrate across multiple nodes. Airflow is also starting to implement more enterprise-y features, like role-based access control, which should be coming to the next release.
    - martin_loetzsch 8y ago
      (author here) That's absolutely correct. Mara uses Python's multiprocessing [1] to parallelize pipeline execution [2] on a single node so it doesn't need a distributed task queue. Beyond that (and visualization) it can't do much. In fact it doesn't even have a scheduler (you can use jenkins or cron for that). [1] https://docs.python.org/3.6/library/multiprocessing.html https://docs.python.org/3.6/library/multiprocessing.html [2] https://github.com/mara/data-integration/blob/master/data_integration/execution.py https://github.com/mara/data-integration/blob/master/data_in...
artwr 8y ago
With a little bit of work you could probably trigger tasks on a Windows Machine, in particular if the work you need to do is mostly script and/or SQL based. The main limit is that Gunicorn for the Airflow webserver is not currently compatible with Windows, but the scheduler should work fine. I believe it might be able to run in the Windows Subsystem for Linux, but I don't think anyone has tested it as of yet. (Source: I am an Apache Airflow committer)

cosmie 8y ago

I really like this! I bootstrapped the ETL and data pipeline infrastructure at my last company with a combination of Bash, Python, and Node scripts duct-taped together. Super fragile, but effective[3]. It wasn't until about 3 years in (and 5x the initial revenue and volume) that it started having growing pains. Every time I tried to evaluate solutions like Airflow[1] or Luigi[2], there was just so much involved with getting it going reliably and migrating things over that it just wasn't worth the effort[4]. This seems like a refreshingly opinionated solution that would have fit my use case perfectly. [1] https://airflow.apache.org/ https://airflow.apache.org/ [2] https://github.com/spotify/luigi https://github.com/spotify/luigi [3] The operational complexity of real-time, distributed architectures is non-trivial. You'd be amazed how far some basic bash scripts running on cron jobs will take you. [4] I was a one man data management/analytics/BI team for the first two years, not a dedicated ETL resource with time to spend weeks getting a PoC based on Airflow or Luigi running. When I finally got our engineering team to spend some time on making the data pipelines less fragile, instead of using one of these open source solutions they took it as an opportunity to create a fancy scalable, distributed, asynchronous data pipeline system built on ECS, AWS Lambda, DynamoDB, and NodeJS. That system was never able to be used in production, as my fragile duct-taped solution turned out to be more robust.

BerislavLopac 8y ago
This might be useful: https://cloud.google.com/blog/big-data/2018/05/cloud-composer-is-now-in-beta-build-and-run-practical-workflows-with-minimal-effort https://cloud.google.com/blog/big-data/2018/05/cloud-compose...
- cosmie 8y ago
  That looks like a nifty service, but we were an AWS shop and the bandwidth intensity of ETL would have made a GCP-hosted service cost prohibitive. That said, at least once a month I tried to convince our CTO to let me move our data workloads over to GCP due to all the nifty managed services they have available. I was primarily munging data between FTP drops, S3, RDS, and Redshift, which mainly fell into free buckets for internal data transfer.

mistrial9 8y ago

the docs animations inline add to casual-browsing fun https://github.com/mara/mara-example-project https://github.com/mara/mara-example-project

occams_chainsaw 8y ago

Seems interesting, but I'll have to dismiss it for now due the total lack of tests

foolinaround 8y ago

if this had a scheduler and an alerting mechanism when SLAs are breached, that would be great! maybe future features?

martin_loetzsch 8y ago
(author here) It intentionally doesn't have a scheduler, just definition and parallel execution of pipelines. For scheduling, use Jenkins, cron or Airflow. Currently you can get notifications for failed runs in slack. Alerting itself is not really in the scope of this project, but it should be easy to implement in a project.

mooreds 8y ago

I'd love this, except for ruby. Anyone know of anything like this?

busterarm 8y ago
https://github.com/thbar/kiba https://github.com/thbar/kiba This has been the standard for years
kakoni 8y ago
Embulk (https://www.embulk.org https://www.embulk.org) - Define your datapipelines with yaml + ruby (using ruby proc filter) - Functionality can be extended with ruby(or java) based plugins. Been using this for few years, works beautifully.
thibaut_barrere 8y ago
I wrote Kiba specifically for this. I will share a good bunch of current use cases at Kaigi 2018 if you are interested!

stadeschuldt 8y ago

Here is a conference talk presenting the framework and the ideas behind: https://youtu.be/GdtFuOah-5c https://youtu.be/GdtFuOah-5c

porker 8y ago

What is there in the ETL space with bi-directional sync? I don't usually run into problems where "transfer data from X to Y" is it. Usually it's "there's data in CRM X and data in Event system Y, merge the two keeping X as the master source" There's Mulesoft et al but they seem overkill for small deployments, as well as being stupidly expensive [1]. 1. I'm sure they're good value if you're an enterprise company. But if you don't need the UI builder and only 2-3 sources kept in sync, they are expensive. And with my reading of the documentation, conflict resolution isn't great either.

ptrott2017 8y ago
Kettle - the open source component of Hitachi Pentaho Data Integration is worth looking at, has some functionality for this (you can join sources and insert joined data back into master) and its pretty easy to extend to meet requirements. Its Apache licensed, with great commercial support if you need it, and can be found here: https://github.com/pentaho/pentaho-kettle https://github.com/pentaho/pentaho-kettle We are small shop but it is a key part of our data workflows. We also found spoon the UI workflow builder to be very helpful for building workflows - since its allowed team members who were not strong in Java to build workflows that they need. Last but not least and a key decider in our adoption is the community is super friendly and very helpful. Something we thought would take months got implemented in a weekend thanks to quick feedback. Perhaps worth a look.
- karambahh 8y ago
  As a long time Kettle user (probably close to 10 years) I must warn potential users that the learning curve is steep and that (as any large body of code) it contains code that sometimes can run unpredictably. I got good at diagnosing user induced bugs in PDI transformation via reading the stacktrace but it is not to everyone liking. To me, a very strong regression is the "new" UI which switched from meaningful icons to a blue & white scheme that makes reading/discovering new transformation a real pain: all is a blur of blue without the past color cues that you learned in the past ("ok, this is the icon for a merge from a source file & a database sent to an ES cluster" became "some stuff is read from blue sources and sent to some blue output") I recently learned about the capability to run transformation into a spark cluster that replace the original engine by a new spark implementation, bring obvious compute optimization for large enough dataset but I don't have enough experience with it to speak of it positively or negatively.

thalesmello 8y ago

As the one who implemented Airflow at my company, I understand how overwhelming it can be, with the DAGs, Operators, Hooks and other terminologies. This looks like a good enough mid-term alternative. However, I have a few questions (which I couldn't find easily in the homepage, sorry if I skipped something): - Do you have a way of persisting connection information? I saw an example of how to create a connection, but it isn't clear if the piece of code has to be loaded every time you execute the ETL - How easy it is to implement new computation engines? - Plans of creating a command line to make it easier to execute operations?

martin_loetzsch 8y ago
(author here) Connection information is configured in code through [1], see [2] for an example. It's very easy to run other workloads. Either by directly invoking Python functions from tasks or by writing own commands (operators)[3]. There is a command line. It's the interface for running from external schedulers (jenkins, cron)[4] & [5] [1] https://github.com/mara/mara-db https://github.com/mara/mara-db [2] https://github.com/mara/mara-example-project/blob/master/app/local_setup.py.example https://github.com/mara/mara-example-project/blob/master/app... [3] https://github.com/mara/data-integration/blob/master/data_integration/pipelines.py#L47 https://github.com/mara/data-integration/blob/master/data_in... [4] https://github.com/mara/data-integration/raw/master/docs/example-run-cli-1.gif https://github.com/mara/data-integration/raw/master/docs/exa... [5] https://github.com/mara/data-integration/raw/master/docs/example-run-cli-2.gif https://github.com/mara/data-integration/raw/master/docs/exa...
- neuromantik8086 8y ago
  Perhaps this is addressed elsewhere, but do you have any plans to support Common Workflow Language?

pgwhalen 8y ago

Is anyone out there doing (or considering) streaming ETL as opposed to batch?

artwr 8y ago
I think a lot of people are considering it. The ideas behind Apache Beam are supposed to make the transition easier, with the ability to run in either mode. I think in practice it really depends on your use case. For a large enough amount of data, especially on the ingest side, running it streaming makes sense. Now for that daily report from aggregated table, do you really need streaming ETL/aggregation is another question. The operational cost is non trivial with streaming.
- pgwhalen 8y ago
  Can you expand on that last sentence? Not that I’d disagree, I’m just curious about what specifically you’re referring to. My team is currently underwater supporting a decades old system of batch ETL, where it’s a major challenge to understand dependencies between jobs, and therefore what to rerun when something fails. We’re exploring a move to streaming ETL (Kafka streams being the leading candidate) as a way of making the dependencies explicit: rather than having to rerun stored procs or Talend jobs that we depend on certain data, we run a fleet of 24/7 microservices that transform and move data downstream whenever it is available.
woqe 8y ago
Apache nifi (https://nifi.apache.org/ https://nifi.apache.org/) is a software which handles streaming ETL processing. From a previous post I made about it: "We have HTTP endpoints set up to receive data from our ERP's accounting system to send data to Concur and to update customers' Lawson punchout ordering systems with shipment information. The 'E' is an HTTP post with an XML payload. The 'T' consists of using the payload to query other databases to build the 'L' payload, and the 'L' is an HTTP post to the consumer's endpoints."
- chrisjc 8y ago
  There's often a misconception about Nifi being batch or file based and not streaming. This probably originates as result of data in Nifi being represented as a FlowFile, which is kind of a misnomer. As woqe stated above, Nifi can certainly doing streaming.

antoncohen 8y ago

I'm sure this is right for someone, everyone has different requirements, but I don't really want a lighter-weight Airflow. I want an Airflow that runs and scales in the cloud, has extensive observability (monitoring, tracing), has a full API, and maybe some clear way to test workflows. I was looking into how Google's Cloud Composer is run, which is a managed Airflow service. They use gcsfuse to mount a directory for logs, because Airflow insists on writing logs to local disk with no cleanup system, even if you configure logs to be sent to S3/GCS. To health check the scheduler they query Stackdriver Logging to see if has logged anything in the last five minutes, because the scheduler has no /healthz or other way to check health. There is no built it way to monitor workflows, so you can't easily do something like graph failures by workflow, email on failure is about all you get. A GUI-first app that requires local storage is not what I expect these days.

phunge 8y ago
+1 to this, I've had multiple teams that have shied away from airflow because of its operability & deployment story, despite needing something that has all its features. In terms of radically different takes on workflow engines, I'm very interested in reflow. I haven't used it enough to know if the rough edges are a deal breaker.
occams_chainsaw 8y ago
There is no built it way to monitor workflows, so you can't easily do something like graph failures by workflow Not sure if this meets your criteria, but the tree view allows you to see a historical graph of job runs and status
mariusae 8y ago
> I want an Airflow that runs and scales in the cloud I'd encourage you to look at Reflow [1] which takes a different approach: it's entirely self-managing: you run Reflow like you would a normal programming language interpreter ("reflow run myjob.rf") and Reflow creates ephemeral nodes that scale elastically and that tear themselves down, only for the purpose of running the program. > has extensive observability (monitoring, tracing) Reflow includes a good amount of observability tools out of the box; we're also working on integrating tracing facilities (e.g., reporting progress to Amazon x-ray). > has a full API, and maybe some clear way to test workflows. Reflow's approach to testing is exactly like any other programming language: you write modules that can either be used in a "main" program, or else be used in tests. [1] https://github.com/grailbio/reflow https://github.com/grailbio/reflow

samuell 8y ago

I really like the interactive terminal-based menu, that's neat!

frugalmail 8y ago

Just thought I would add: If you want something more serious that supports better scale, realtime/streaming, is written in a statically typed language check out https://kylo.io/ https://kylo.io/ it's added on to Apache NiFi (which was developed by our friends at the NSA)

mariusae 8y ago

Reflow [1] is also well-suited for ETL workloads. It takes a different tack: it presents a DSL with data-flow semantics and first-class integration with Docker. The result is that you don't write graphs, instead you just write programs that, due to their semantics, can be automatically parallelized and distributed widely, all intermediate evaluations are memoized, and programs are evaluated in a fully incremental fashion: [1] https://github.com/grailbio/reflow https://github.com/grailbio/reflow

endlessvoid94 8y ago

In my experience one of the places where every framework breaks down is when you must combine / reconcile multiple rows from multiple data sources to produce one row in a fact table. Does there exist a "framework" that lets me do this simply?

martin_loetzsch 8y ago
(author here) The mara example project [1] does exactly that. It combines PuPI download stats with Github repo activity data. [1] https://github.com/mara/mara-example-project https://github.com/mara/mara-example-project
- endlessvoid94 8y ago
  Thanks! Just took a look. The file directory structure is a bit confusing -- could you point me to the file that performs this transformation?
  - martin_loetzsch 8y ago
    For example the PyPI download stats pipeline is here: https://github.com/mara/mara-example-project/tree/master/app/data_integration/pipelines/pypi https://github.com/mara/mara-example-project/tree/master/app... The __init__.py contains the pipeline, and the rest is the SQL files that do the transformations
    - endlessvoid94 8y ago
      Thank you!

larsf 8y ago

Composable is amazing at ETL - https://composableanalytics.com https://composableanalytics.com It blows things like Alteryx, NiFi, Airflow out of the water.

mattbillenstein 8y ago
Please add a disclaimer if you work for these guys... I personally generally dislike enterprise solutions because I hate talking to sales people - but that's just me.
- larsf 8y ago
  Yes - I'm the founder. And you don't have to talk to me if you don't want to :)

groodt 8y ago

I'm interested in hearing thoughts from people who've used digdag (https://www.digdag.io/ https://www.digdag.io/) or pachyderm (http://www.pachyderm.io/ http://www.pachyderm.io/). Pachyderm is the most interesting to me. It seems to be focussing on the data as well as the data processing.

jdoliner 8y ago
I'm the founder and a core developer of Pachyderm so I can weigh in on how it compares (there is, of course, some potential bias here). I also was at Airbnb around the time we released Airflow, so I got to see it being built up close and used the system it was replacing quite a bit as well. I think it's fair to say that Mara and Airflow are both in the same category of DAG (directed acyclic graph) schedulers for Python; Python makes a ton of sense as the language to focus on as it's the de facto lingua franca for data science. I'd also put Luigi in that bucket, although I think Airflow has degraded its mind-share quite a bit. All of them are targeting the data pipeline use case, which is very well represented as a DAG, but the actual management of the data is left up to the user. They (Mara, Airflow, or Luigi) schedule tasks for you after all the tasks they depended on have completed, but you have to figure out where to store your data so that downstream tasks can find the data their upstream tasks outputted. At Airbnb we used HDFS as this storage layer, often with Hive or Presto on top. Storing in s3 is also a common pattern. Pachyderm is also a DAG scheduler but we're a lot more prescriptive about where you store the data, and a lot less prescriptive about what languages and tools you use. Pachyderm ships with its own distributed filesystem (pfs) that we use for storage, it does a few things that other storage solutions can't do. In particular, it version controls your data and records "provenance" i.e. where data comes from. For example if you train a machine learning model then its provenance will be the data you used to train it. In terms of processing we're much less prescriptive, because we let users express their code as a Docker container rather than only having bindings for one language. So you can use anything that you can fit in a Docker container. Data is exposed to your code via the local filesystem, so regardless of language you have a very natural interface to your data: system calls on files. Hope this helps understand the differences between the various systems and thanks for your interest in Pachyderm. Swing by our users slack channel [0] if you'd like some help getting started with it. [0] http://slack.pachyderm.io/ http://slack.pachyderm.io/

soobrosa 8y ago

What's the least verbose/boilerplate-heavy tool in your experience? We couldn't make it leaner than this (works well in production in scale). https://github.com/wunderlist/night-shift https://github.com/wunderlist/night-shift If we could get rid of Ruby in there (super useful for scripting) and fly with only Python I'd be the happiest person on Earth. Also we started to go cloud agnostic, it handles both AWS and Azure. Do you something that does AWS, Azure _and_ Google Cloud also?

martin_loetzsch 8y ago
GNU Make is indeed the least verbose/ boilerplate-heavy tool and I use it for a lot of things. The problem with Make is lacking acceptance amongst younger programmers who always want to work with the latest technologies.

nikolay 8y ago

How is this better than Singer [0]? [0]: https://www.singer.io/ https://www.singer.io/

tjr225 8y ago

This is slightly weird...but I named my dog Mara: https://flic.kr/p/FtdRNX https://flic.kr/p/FtdRNX

martin_loetzsch 8y ago
Also weird: it's the name of a giant ugly guinea pig: https://en.wikipedia.org/wiki/Mara_(mammal) https://en.wikipedia.org/wiki/Mara_(mammal)

reinhardt 8y ago

Why PostgreSQL only? The mara-DB dependency [1] claims to support more. [1] https://github.com/mara/mara-db https://github.com/mara/mara-db

martin_loetzsch 8y ago
(author here) Currently there is a hard dependency to Postgres for the bookkeeping tables of mara. I'm working on dockerizing the example project to make the setup easier. For ETL, Mysql, Postgres & SQL Server are supported (and it's easy to add more).
- random4369 8y ago
  I'm a bit confused about this. What if the target is HDFS? Why this dependency on SQL databases for ETL?