6.2 Use Mage SQL UI

If your query is simple and on SQL statement you can use the Mage SQL Block UI without needing to write complex MERGE or INSERT statements. Check out the instructions below on how to create a simple, single SQL statement SQL query using the Mage UI.

Create your first automated Mage SQL block

Let’s begin by creating your first SQL Block within the Mage interface. After selecting the “SQL” block type, you’ll encounter three key components that form the foundation of your data transformation journey:

Configuring Connections

Mage provides a user-friendly interface for managing data connections. You can easily set up connections to various data sources, including data warehouses, databases, and other supported platforms. To set up a connection:

• Drop down the “connections” list in the top left of the SQL block and select your target connection

• Drop down the “profile” list and select the profile from the io_conflig.ymlfile you want associated with the SQL block

• Add a schema name and table name where the target data will be stored

• Once you add the schema and table the database text box will appear. Add the database where you intend to store the target data.

The target database, schema, and table will automatically be created if they do not already exist.

Writing SQL queries:

The SQL Block editor offers a clean and intuitive interface for composing your queries. It includes features like syntax highlighting and auto-completion to enhance your coding experience.

Utilizing write policies:

One of the standout features of Mage SQL Blocks is the flexible write policies. These policies give you granular control over how new data interacts with existing data in your target tables, helping prevent unintended data loss or duplication. You can choose from three options:

• Append: Adds new data to the existing table without modifying existing records.

• Replace: Overwrites the entire target table with the new data.

• Fail: Stops the operation if the target table already exists, preventing accidental overwrites.

Write policies in automated SQL blocks

One of the standout features of Mage SQL Blocks is their flexible write policies. Data engineers often grapple with the challenge of managing existing data when updating tables. Mage SQL Blocks offer three distinct write policies: append, replace, and fail. This level of control allows engineers to precisely manage how new data interacts with existing data, preventing unintended overwrites or duplications.

Moreover, the write policies offer granular control over how data is managed. This is particularly useful when dealing with incremental data loads or when maintaining historical data is crucial. The ability to choose between these policies on a per-block basis allows for more customized data management strategies.