Airflow has recently become a common component in data engineering pipelines. Launched by Airbnb in 2014, Airflow is designed to orchestrate and schedule user tasks contained in workflows. Airflow is highly configurable, allowing users to add custom Airflow hooks/operators and other plugins to help them implement custom workflows for their own use cases.
Index
The purpose of this post is to help readers become familiar with and start using airflow hooks. By the end of this post, you will be able to.
- Work with the Airflow user interface.
- Use Airflow hooks and implement DAG.
- Create a workflow that retrieves data from PostgreSQL and saves it as a CSV file.
Read on to learn more about Airflow hooks and how to use them to pull data from different sources.
Index
- Requirements for setting up airflow hooks
- What is airflow?
- ¿Fue sind Airflow Hooks?
- How are airflow hooks made? Use these 5 steps to get started
- Airflow Hooks Parte 1: Prepare su entorno PostgreSQL
- Airflow Hooks Parte 2: Inicie el servidor web Airflow
- Airflow Hooks Part 3: Configure your PostgreSQL connection
- Airflow Hooks Parte 4: Implemente su DAG con Airflow PostgreSQL Hook
- Airflow Hooks Part 5: Run your DAG
- Restrictions on the use of airflow hooks
- Diploma
Requirements for setting up airflow hooks
Users must have the following applications installed on their system as a prerequisite to configure Airflow bindings:
- Python 3.6 or higher.
- Apache Airflow installed and configured for use.
- A PostgreSQL database version 9.6 or higher.
What is airflow?
Airflow is an open source workflow management platform for implementing data engineering pipelines. It helps to create, schedule, and monitor user workflows programmatically. It is often used to take data from multiple sources, transform it, and then send it to other sources.
Airflow represents your workflows asDirected Acyclic Graphs (DAGs). Airflow Scheduler runs tasks specified by your DAGs using a collection of workers. Airflow's intuitive interface helps you visualize your data pipelines running in different environments, monitor them, and troubleshoot as they arise.
If you want to learn more about Airflow Scheduler and the basics of scheduling your DAGs, visit this page:The Ultimate Guide for Airflow Controllers
¿Fue sind Airflow Hooks?
Airflow's primary function is to manage workflows that involve retrieving, transforming, and sending data to other systems. Airflow hooks help connect to external systems. It can help to connect to external systems like S3, HDFC, MySQL, PostgreSQL, etc.
You might be wondering what is the need for a new concept when all of these data sources provide client libraries that you can use to connect to them. airflow hookSummary a lot of boilerplate codein connection with their data sources and serve as a building block for airflow operators. Airflow operators then do the actual work of retrieving or transforming data.
Airflow hooks help you avoid wasting time with low-level API data sources. Airflow offers several built-in hooks that can connect to all popular data sources. It also provides an interface for custom development of Airflow hooks when working with a database for which the built-in hooks are not available.
Simplify your data analysis and ETL with Hevo’s codeless data pipeline
fully managedData pipeline without codeplatform likecube hevohelps you integrate and load data fromOver 100 different fonts (including over 40 free fonts)andPostgreSQLName,mysqlto a destination of your choice in real time and effortlessly.
With its minimal learning curve, Hevo can be set up in minutes, allowing users to upload data without impacting performance. Tight integration with numerous sources allows users to seamlessly input data of different types without having to code a single line.
Get started or Hevo for free
Take a look at some of Hevo's cool features:
- Completely automated: The Hevo platform takes a few minutes to set up and requires minimal maintenance.
- The transformation: Hevo provides preload transformations via Python code. You can also run transformation code for each event in configured pipelines. You must edit the properties of the event object received in the transformation method as a parameter to perform the transformation. Hevo also offers drag-and-drop transformations, such as date and control functions, JSON, and event handling, to name a few. These can be configured and tested before use.
- connections: Hevo supports 100+ integrations (including 40+ free sources) for SaaS platforms, files, databases, analytics, and BI tools. Supports multiple targets including Google BigQuery, Amazon Redshift, Snowflake Data Warehouses; Amazon S3 data lakes; MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL databases to name a few.
- Data transfer in real time: Hevo offers real-time data migration, so you always have data ready for analysis.
- 100% complete and accurate data transmission: The solid infrastructure of Hevo guarantees reliable data transmission without data loss.
- scalable infrastructure: Hevo has built-in integrations for 100+ fonts (including 40+ free fonts) to help you scale your data infrastructure as needed.
- Live support 24/7: The Hevo team is available 24 hours a day to provide you with exceptional support via chat, email and support calls.
- schema management: Hevo eliminates the tedious task of schema management and automatically detects the schema of the incoming data and assigns it to the target schema.
- live monitoring: Hevo allows you to monitor the flow of data, so you can always check where your data is.
Sign up for a 14-day free trial here!
How are airflow hooks made? Use these 5 steps to get started
As mentioned, Airflow offers several built-in Airflow hooks. For this tutorial, we'll use the PostgreSQL hook provided by Airflow to extract the contents of a table to a CSV file. To do this, follow these steps:
Airflow Hooks Parte 1: Prepare su entorno PostgreSQL
Paso 1: First you need to create a table in PostgreSQL and load some data. Gonnapsql shelland run the following command.
CREATE TABLE customer(serial number, first name VARCHAR(50), last name VARCHAR(50), email VARCHAR(50));Here we will create a customer table with four columns: ID, First Name, Last Name, and Email.
paso 2: Now create a CSV file in the following format:
seriennummer,vorname,nachname,email1,john,michael,john@gmail.com2,mary,cooper,mcooper@gmail.com3,sheldon,cooper,scopper@gmail.com4,john,michael,john@gmail.com5,mary, cooper,mcooper@gmail.com6,sheldon,cooper,scopper@gmail.comlevel 3: save asclient.csvy ve.
stage 4: Use the COPY command to paste data into the customer table.
COPY customer FROM '/home/data/customer.csv' DELIMITER ', 'CSV HEADER;Airflow Hooks Parte 2: Inicie el servidor web Airflow
You can use the following command to start the Airflow web server.
Serverweb Airflow -p 8080After configuring the Airflow web server, go to localhost:8080 to see your Airflow UI. You will be presented with a screen showing your previous or current DAGs.
Airflow Hooks Part 3: Configure your PostgreSQL connection
Paso 1: In the Airflow UI, go toAdministratorguide and clicklinksto see all the connection identifiers that are already configured in your Airflow.
Airflow has a built-in connection handler for PostgreSQL.
paso 2: Click Nopostgres_defaultConnection ID and enter your PostgreSQL connection details.
level 3: cliquesave to computerand your connection parameters will be saved.
Airflow Hooks Parte 4: Implemente su DAG con Airflow PostgreSQL Hook
Complete the following step to deploy your DAG with the PostgreSQL Airflow Hook:
Copy the code snippet below and save it aspg_extract.py.
from the Airflow Import DAG
from datetime import datetime, timedelta
de Airflow.providers.postgres.hooks.postgres import PostgresHook
de Airflow.operators.python import PythonOperator
# Change them to your identifiers if necessary.
POSTGRES_CONN_ID = „postgres_predeterminado“
def pg_extract(copiar_sql):
pg_hook = PostgresHook.get_hook(POSTGRES_CONN_ID)
Logging.info("Export query to file")
pg_hook.copy_expert(copy_sql, filename=”/home/user/Airflow/data/customer.csv”)
The day (
day_id="pg_extract",
start_date=datetime.datetime(2022, 2, 2),
programming_interval=timedelta(days=1),
catch up = false,
) as label:
t1 = Python operator (
task_id="pg_extract_task",
python_callable=pg_extract,
op_kwargs={
„copy_sql“: „COPY (SELECT * FROM CLIENT WHERE first_name=’john’ ) TO STDOUT WITH CSV HEADING“
}
)
t1
For further explanation seepg_extract functionto understand how PostgreSQL's Airflow hooks are used here. Using this Airflow hook avoids all the boilerplate code to connect to PostgreSQL. Only a login ID is required and no credentials are hardcoded. The connection identifier is configured in the Connection section of the administration panel.
Der PostgreSQL Airflow-Hook macht diecopy_expertA method that can take an SQL query and an output file to save the results. Here we use the query to output the results as a CSV file.
This sets up your DAG to run every day from a specific date.
Airflow Hooks Part 5: Run your DAG
After saving the Python file to your DAG directory, the file must be added to Airflow's index for it to be recognized as a DAG. The DAG directory is specified asfolder_dayparametersAirflow.cfgFile located in your installation directory. Run the following command to add the file to the list of recognized DAGs.
Initialize the Airflow databaseNow go to the Airflow UI and click on the DAG name.
You can then activate the DAG with the'spielen'button in the upper right corner.
After running successfully, go to the directory set to save the file, and you can find the output CSV file.
And that Airflow hooks are easy to master. We successfully used Airflow's PostgreSQL hook to implement a pull job.
Restrictions on the use of airflow hooks
Airflow is capable of handling much more complex DAGs and task relationships. But its extensive feature set also means that mastering Airflow requires a steep learning curve. The lack of proper examples in the documentation doesn't help either. These are some of the typical challenges developers face when it comes to airflow.
- Airflow workflows are based on hooks and operators. While there are many community-based built-in carriers and links available, Airflow's support for SaaS offerings is limited. If your team uses a lot of SaaS applications to run their business, developers need to develop various airflow hooks and plugins to handle it.
- Although Airflow has an intuitive user interface, it is designed to monitor jobs. The DAG definition is still based on code or configuration. On the other hand, ETL tools are likecube hevocan help you implement ETL jobs in a few clickswithout using any code.
- Airflow is a field installable solution. This means that developers must spend time managing and maintaining the Airflow installation. Tools like Hevo are completely cloud-based, relieving developers of all the headaches associated with infrastructure maintenance.
Diploma
In summary, this blog has provided a comprehensive overview of developing and maintaining airflow hooks using an example PostgreSQL airflow hook. Although using Airflow to manage and control your data pipelines is a daunting task, we encourage you to use it.Ferramentas ETL without Code, full Hevo.
holayou can connect your frequently used databases likeMySQL, PostgreSQL, and otherMore than 100 data sourcesinto a data warehouse with a few clicks. Not only can you export data from sources and load data to destinations, but you can also transform and enrich your data and prepare it for analysis, so you can focus only on your core business needs and perform detailed analysis with BI tools.
Using Hevo is easy, and you can set up a data pipeline in minutes without worrying about maintenance issues failing. Hevo also supports advanced data transformation and workflow features to bring your data into any format before loading it into the target database.
Visit our website to explore Hevo
With Hevo you can migrate your data from your favorite applications to any data store of your choice, e.g.Amazon Redshift, Snowflake, Google BigQuery o Firebolt,in a matter of minutes with a few clicks.
Want to take Hevo for a spin?Recordhere for one14 days free trialand experience first-hand the feature-rich Hevo suite.
Got a few more airflow hooks you want us to cover? Leave a comment below to let us know.