Ravi Writes

2. SQL for Data Transformation

5 min read

SQL for Data Transformation

In the traditional, on-prem ETL world, SQL was mainly used to extract and load data — the heavy lifting of transformation happened inside the ETL tool itself (an Ab Initio graph, an Informatica mapping, and so on). That changed with Hadoop and Hive: SQL expanded from being purely a DQL (Data Query Language) into also being a DTL (Data Transformation Language). With Spark, you can now write your entire transformation logic in SQL-like syntax.

Same Ab Initio graph example — Input File → Filter By Expression → Reformat → Output File — this time written as SQL inside a PySpark .py script instead of PySpark’s DataFrame API.

Ab Initio Graph Skeleton

Ab Initio Graph = Graph Components connected logically + each Component configured using logic written in DML. DML (Data Manipulation Language) is Ab Initio’s proprietary language to describe, parse, and manipulate data.

graph LR
    A[Input File] --> B[Filter By Expression]
    B --> C[Reformat]
    C --> D[Output File]

Note: this is a simplified representation of the actual Ab Initio graph, not the graph itself — the real graph is the .mp file opened in GDE.

# Input File Component is configured for
# 1. Location of dataset
# 2. Schema of dataset
 
# Input File (Record Format - DML)
record
utf8 string("|") id = NULL("");
utf8 string("|") name = NULL("");
utf8 string("|") amount = NULL("");
utf8 string("\n") status = NULL("");
end;
# Filter By Expression Component is configured for
# Filter Logic (DML Logic)
straing_upcase(string_lrtrim(status)) == "ACTIVE"
 
# Input File Record format = FBE Input Record format
# FBE Input Record format = FBE Output Record format
# Reformat Component is configured for 
# Renaming fields, cleaning data and changing data-type (DML Logic)
out :: reformat(in)=
begin
out.customer_id :: string_lrtrim(id);
out.customer_name :: string_lrtrim(name);
out.amount :: (decimal("|")) string_lrtrim(amount);
end;
 
# FBE Output Record format = Reformat Input Record format
# Reformat Output Record format = Output File Record format
# Output File Component is configured for
# 1. Location of dataset
# 2. Schema of dataset
 
# Ouput File (Record Format - DML)
record
utf8 string("|") customer_id = NULL("");
utf8 string("|") customer_name = NULL("");
decimal("|") amount = NULL("");
end;

Equivalent PySpark Script (SQL inside .py)

PySpark Script (SQL-style) = SQL statements — views and a final query — run from Python via spark.sql(...), chained together logically. No separate DML: from wiring the pipeline together to expressing per-column logic, it’s plain SQL, just called from a .py file instead of living in its own .sql file.

from pyspark.sql import SparkSession
 
# Spark Entry Point
spark = SparkSession.builder.appName("AbInitioEquivalent").getOrCreate()
# Input File (Record Format - DML)
spark.sql("""
    CREATE OR REPLACE TEMPORARY VIEW input_file (
        id STRING,
        name STRING,
        amount STRING,
        status STRING
    )
    USING csv
    OPTIONS (
        path "/volumes/data/input.csv",
        sep "|",
        header "false"
    )
""")
# Filter By Expression
spark.sql("""
    CREATE OR REPLACE TEMPORARY VIEW filtered AS
    SELECT *
    FROM input_file
    WHERE UPPER(TRIM(status)) = 'ACTIVE'
""")
# Reformat (Function - DML)
spark.sql("""
    CREATE OR REPLACE TEMPORARY VIEW reformatted AS
    SELECT
        TRIM(id)   AS customer_id,
        TRIM(name) AS customer_name,
        CAST(TRIM(amount) AS DECIMAL(18, 2)) AS amount
    FROM filtered
""")
# Output File (Record Format - DML)
spark.sql("""
    INSERT OVERWRITE DIRECTORY '/volumes/data/output.csv'
    USING csv
    OPTIONS (sep '|', header 'false')
    SELECT customer_id, customer_name, amount
    FROM reformatted
""")
 
spark.stop()

Like the standalone-PySpark version, this is illustrative — it captures the shape of the transformation, not a guaranteed-to-run script (exact CREATE VIEW ... USING csv behaviour can vary slightly by Spark version).

Third Option: The Same SQL as a Standalone .sql File

The SQL inside those spark.sql(...) calls doesn’t have to live in Python at all — the exact same statements can be saved as their own .sql file and submitted directly (e.g. via spark-sql -f script.sql), with no Python wrapper.

-- Input File (Record Format - DML)
-- record
-- utf8 string("|") id = NULL("");
-- utf8 string("|") name = NULL("");
-- utf8 string("|") amount = NULL("");
-- utf8 string("\n") status = NULL("");
-- end;
CREATE OR REPLACE TEMPORARY VIEW input_file (
    id STRING,
    name STRING,
    amount STRING,
    status STRING
)
USING csv
OPTIONS (
    path "/volumes/data/input.csv",
    sep "|",
    header "false"
);
-- Filter By Expression
-- straing_upcase(string_lrtrim(status)) == "ACTIVE"
CREATE OR REPLACE TEMPORARY VIEW filtered AS
SELECT *
FROM input_file
WHERE UPPER(TRIM(status)) = 'ACTIVE';
-- Reformat (Function - DML)
-- out.customer_id :: string_lrtrim(id);
-- out.customer_name :: string_lrtrim(name);
-- out.amount :: (decimal("|")) string_lrtrim(amount);
CREATE OR REPLACE TEMPORARY VIEW reformatted AS
SELECT
    TRIM(id)   AS customer_id,
    TRIM(name) AS customer_name,
    CAST(TRIM(amount) AS DECIMAL(18, 2)) AS amount
FROM filtered;
-- Output File (Record Format - DML)
-- record
-- utf8 string("|") customer_id = NULL("");
-- utf8 string("|") customer_name = NULL("");
-- decimal("|") amount = NULL("");
-- end;
INSERT OVERWRITE DIRECTORY '/volumes/data/output.csv'
USING csv
OPTIONS (sep '|', header 'false')
SELECT customer_id, customer_name, amount
FROM reformatted;
-- /volumes/data/output.csv is a directory (one file per partition), same as the PySpark write

This is a third option alongside the two already covered in Ab Initio Graph vs PySpark Script: pure DataFrame API, SQL embedded inside Python, or — as above — a standalone Spark SQL script. All three run on the same Spark engine; which one to reach for is a style and team-convention choice, not a capability one.

This means the core skillset shifts:

  • Traditional stack: ETL tool (Ab Initio / Informatica) + Shell Scripting + SQL + Scheduling (Control-M / AutoSys / Control-Center)
  • Modern stack: PySpark + Shell Scripting + Python (core) + Advanced SQL + Scheduling (Airflow)