Want to Do ETL With Python?

摘要： Find the best Python ETL tool that fits your use case

▲來源： ThisisEngineering RAEng on Unsplash

Extraction, Transformation, and Loading

Modern organizations rely on enormous pools of data gathered using the best-in-class tools and techniques to extract data-driven insights, that help in making smarter decisions. Thanks to the improvements brought over by the now industry-standard technological advancements, organizations now have much easier access to these pools of data.

But before these corporations can actually use that data, it needs to go through a process called ETL, short for Extraction, Transformation, and Loading.

ETL is responsible for not only making the data available to these organizations but also makes sure that the data is in the right structure to be used efficiently by their business applications. Businesses today have loads of options while picking the right ETL tool, such as the ones built with Python, Java, Ruby, GO, and more but for this write-up, we’ll be focussing more on the Python-based ETL tools.

● ● ●

What is ETL?

A core component of data warehousing, the ETL pipeline is a combination of three interrelated steps called Extraction, Transformation & Loading. Organizations use the ETL process to unify data collected from several sources to build Data Warehouses, Data Hubs, or Data Lakes for their enterprise applications, like Business Intelligence tools.

You can think of the entire ETL process as an integration process that helps businesses set up a data pipeline and start ingesting data into the end system. A brief explanation of ETL is below.

● Extraction:Involves everything from selecting the right data source from many formats like CSV, XML, and JSON, extraction of data, and measuring its accuracy.

● Transformation:It is where all the transformation functions including data cleansing are applied to that data while it waits in a temporary or staging area for the final step.

● Loading:Involves the actual loading of the transformed data into the data store or a data warehouse.

● ● ●

Python ETL tools for 2021

Python is now taking the world by storm with its simplicity and efficiency. It’s now being used to develop a plethora of applications for a range of domains. What’s more interesting is that the enthusiastic developer community of Python is actively churning out new libraries and tools making Python one of the most exciting and versatile programming languages.

Since it has now become the top choice of programming language for data analysis and data science projects, Python-built ETL tools are all the craze right now. Why?

It’s because they leverage the benefits of Python to offer an ETL tool that can not only satisfy the simplest of your requirements but also your most complex ones too.

Below are the top 10 Python ETL tools that are making a noise in the ETL industry right now.

● ● ●

1. Petl

Short for Python ETL, petl is a tool that is built purely with Python and is designed to be extremely straightforward. It offers all standard features of an ETL tool, like reading and writing data to and from databases, files, and other sources, as well as an extensive list of data transformation functions.

petl is also powerful enough to extract data from multiple data sources and comes with support for a plethora of file formats like CSV, XML, JSON, XLS, HTML, and more.

It also offers a handy set of utility functions that can let you visualize tables, lookup data structures, count rows, occurrences of values, and more. As a quick and easy ETL tool, petl is perfect for creating small ETL pipelines.

Though petl is an all-in-one ETL tool, there are certain functions that can only be achieved by installing third-party packages.

● ● ●

2. Pandas

Pandas has become an immensely popular Python library for data analysis and manipulation, making it an all-time favorite among the data science community. It’s an extremely easy to use and intuitive tool that is filled with convenient features. To hold the data in memory, pandas brings the highly efficient dataframe object from the R programming language to Python.

For your ETL needs, it supports several commonly used data file formats like JSON, XML, HTML, MS Excel, HDF5, SQL, and many more file formats.

Pandas offers everything that a standard ETL tool offers, making it a perfect tool for rapidly extracting, cleansing, transforming, and writing data to end systems. Pandas also plays well with other tools, such as visualization tools, and more to make things easier.

One thing you should keep in mind while using pandas is that it puts everything into memory and problems might occur if you’re running low on memory.

● ● ●

3. Luigi

Spotify’s Luigi is a Workflow Management System that can be used to create and manage an extensive list of batch job pipelines. Luigi allows users to chain and automate thousands of tasks while conveniently providing real-time updates of all the pipelines via a web dashboard. It offers plenty of templates to let you instantly create hundreds of tasks very quickly.

Luigi also offers a dependency graph which gives you a visual representation of the various tasks in the pipeline and their dependencies.

One of the best things about Luigi is that it ensures that all file system operations are atomic. Thanks to its proven track record, Luigi offers some of the best and most powerful ETL pipeline creation capabilities of any other ETL tool on this list. This is why enterprises like Spotify, Foursquare, Stripe, Buffer, and more continue to rely on Luigi.

● ● ●

4. Apache Airflow

Apache Airflow is an incredibly easy-to-use Workflow Management System that allows users to seamlessly create, schedule, and monitor all workflow pipelines, even the ETL ones. It was first developed by Airbnb but was later added to the Apache Software Foundation’s repertoire.

To keep you updated on the progress of the job pipelines, Airflow comes with an intuitive user interface called Airflow WebUI.

Airflow can smoothly scale up and down across varying levels of workloads. The main thing to note here is that Airflow doesn’t do the ETL itself, instead, it gives you the power to oversee the pipeline processing in one place.

You can extend Airflow with other libraries and operators of your choice. Airflow also integrates well with a range of cloud service providers like Microsoft, Google, and Amazon, with simple plug-and-play operators.

● ● ●

5. Beautiful Soup

Beautiful Soup is one of the most popular Python-based web scrapers available out there and when it comes to ETL, Beautiful Soup can help you extract data from virtually any website that you want.

If you don’t know what a web scraper is, it’s a small program that sits atop an HTML or XML parser and provides, in our case, Pythonic idioms for finding just the right data from the parse tree. Beautiful Soup allows you to not only scrape the data and store it as is, but it can also be used to give a defined structure to your data.

Using it, you can navigate, search and modify the parse tree of your document and extract whatever you need. It can also handle all the document encoding and decoding by default, so that’s one less thing to worry about. Built purely with Python, Beautiful Soup can even be integrated seamlessly with a wide range of products.

● ● ●

6. Bonobo

Bonobo is a fully-contained lightweight ETL framework that can do everything from extracting data, transforming it, and loading it onto end systems.

To create ETL pipelines, Bonobo uses graphs which makes it easier to structure and visualize everything about the nodes involved.

Bonobo also supports the parallel processing of the elements in the pipeline graph and ensures full atomicity during the transformations. If you want to squeeze more out of Bonobo, you can do that with its extensions.

Some of its popular extensions are SQLAlchemy, Selenium, Jupyter, Django, and Docker. What really makes Bonobo lightweight and quick is its ability to target small scale data, this makes it a good option for simple use cases.

If you know how to work with Python, you’ll have zero issues picking up Bonobo.

● ● ●

7. Odo

Odo is one of the five libraries of the Blaze ecosystem that is designed to help users in storing, describing, querying, and processing the data at hand.

Odo is listed here because it excels in migrating data from one container to another. Being a lightweight data migration tool, Odo can work wonders on both small, in-memory containers as well as larger, out-of-core containers.

Odo uses a network of small data conversion functions that convert data from one format to another. The list of data formats supported includes both in-memory structures like NumPy’s N-dimensional arrays, Pandas’ DataFrame objects, lists, and conventional data sources like JSON, CSV, SQL, AWS, and more.

Thanks to the extremely fast native CSV loading capabilities of supported databases, Odo claims that it can beat any other purely Python-based approaches to loading large datasets.

● ● ●

8. Pygrametl

Website/GitHub Repo: https://chrthomsen.github.io/pygrametl/

This open-source framework is very much similar to Bonobo and allows for the smooth development of ETL pipelines. When it comes to actually using the pygrametl tool, you must have some knowledge of Python as the tool requires the developers to code the entire ETL pipeline in it instead of using a graphical interface.

The tool provides abstractions for commonly used operations, such as interfacing with data from multiple sources, offering parallel data processing capabilities, maintaining slowly changing dimensions, creating snowflake schemas, and more.

One major benefit of using this approach is that it allows pygrametl to integrate with other Python code pretty seamlessly. This plays a key role in simplifying the development of such ETL processes and even facilitates the creation of more complex operations or pipelines when required.

● ● ●

9. Mara

If you don’t like writing all the code by yourself and think that Apache Airflow is too complex for your needs, you might find Mara to be the perfect fit for your ETL needs. You can think of Mara as the lightweight middle-ground between writing purely Python-based scripts and Apache Airflow. Why?

It’s because Mara works on a set of predefined principles that can help you create ETL pipelines. Those assumptions are explained below:

• Data integration pipelines are created using Python code.

• PostgreSQL will be used as the data processing engine.

• A web UI will be used for inspecting, running, and debugging ETL pipelines.

• Nodes rely on the completion of upstream nodes with no data dependencies or data flows.

• Command-line tools will be used as the main source of interaction with the data and databases.

• Single machine pipeline execution based on Python’s multiprocessing capabilities will be used.

• Nodes with higher costs will be run first.

These assumptions are the ones responsible for reducing the complexity of your pipelines but due to some technical issues, Mara is available only on Linux and Docker. Mara also offers a collection of tools and utilities to create data integration pipelines on their GitHub repo.

● ● ●

10. Bubbles

Bubbles is another Python-based framework that you can use to do ETL. But Bubbles isn’t just an ETL framework, it’s much more. Bubbles offers users a collection of tools that can do a number of operations on data, such as monitoring, auditing, cleaning, and integration.

Most ETL tools use scripts or graphs to describe their ETL pipelines but not Bubbles. At its core, Bubbles uses metadata to describe its pipelines, making the task of pipeline designer much easier.

One of the best reasons to use Bubbles is that it is technology agnostic, meaning you don’t necessarily need to worry about how to work with the data stores, you can simply focus on getting the data in a format of your choice. Due to its technology-agnostic nature, Bubbles provides an abstract ETL framework that can be quickly used on a variety of systems to perform all the necessary ETL operations.

● ● ●

Ending

Modern enterprises are much more dependent on data today to make informed decisions and ETL tools are playing a vital role in making that happen. Not only do they help you save time, but they also do that very cost-effectively. Looking at the ever-growing importance of ETL tools today, it has become a necessity for businesses today.

These are plenty of Python ETL tools in the market today that are built with a range of programming languages to satisfy all your ETL needs. Do keep in mind that not all ETL tools are built alike, and while some of them may offer an expansive feature set, some of them may be pretty straightforward.

● ● ●

詳文見： towardsdatascience.com

若喜歡本文，請關注我們的臉書 Please Like our Facebook Page： 　　　Big Data In Finance