DOCSEARCHbyAlgolia
Submit Docs

Sharp ETL Documentation

website·Indexed 29 days ago

https://sharpdata.github.io/SharpETL

A data engineering framework for organizing ETL tasks into workflows using familiar SQL dialects. It features log-driven execution, support for UDFs and custom transformers, and a wide range of data sources including Hive, JDBC, Kudu, Impala, BigQuery, FTP, and HDFS. The tool supports both single-job and batch-job execution via spark-submit and provides configuration templates for ODS and DWD layers.

Tokens
21.4K
Snippets
96
Records
125
Agent score
75%

What's inside Sharp ETL

  1. Overview of Sharp ETL Data Engineering Practices

    Sharp ETL implements data engineering practices focused on standardization and extensibility. Key capabilities include:

    • Workflow Orchestration: Combining individual steps into comprehensive workflows.
    • Extensibility: Supporting custom code logic to extend core functionality.
    • Engineering Code Generation: Standardizing data engineering practices through automated code generation, specifically for:
      • Validating associations between fact tables and dimension tables.
      • Tracking unknown or inapplicable values during table joins.
      • Categorizing and grading data quality issues.

  3. Overview of Sharp ETL and its design goals

    Sharp ETL is a data engineering framework designed to standardize ETL/ELT processes by combining the strengths of code-based, visual (drag-and-drop), and SQL-based approaches while mitigating their weaknesses. It aims to provide a high-quality, low-maintenance environment by embedding essential data engineering practices directly into the framework.

    Key design goals include:

    • Semantic Expression: Leveraging SQL for readability.
    • Extensibility: Supporting custom code logic for complex transformations.
    • Developer Experience: Prioritizing code-first development and version control, while still supporting visual construction tools.
    • Manageability: Breaking down long SQL scripts to reduce complexity.
    • Built-in Engineering Standards: Automating consistency checks between fact and dimension tables, recording unknown/inapplicable values during joins, and implementing graded data quality logging.

  4. Implement custom logic using SharpETL Transformers

    Transformers in SharpETL allow users to execute custom code blocks within a job step to meet specific extension requirements. They are loaded via reflection from JAR files or text files containing Scala code. To use a transformer, you must specify the className, methodName, and transformerType in the job step configuration. Any additional parameters provided in the step are passed as custom arguments to the transformer class.
    -- step=1
-- source=transformation
--  className=com.github.sharpdata.sharpetl.spark.transformation.HttpTransformer
--  methodName=transform
--  transformerType=object
--  url=https:xxxx
--  connectionName=connection_demo
--  fieldName=centerIds
--  jsonPath=$.centers[*].id
--  splitBy=,
-- target=variables
-- checkPoint=false
-- dateRangeInterval=0

  5. Develop ETL processes using standard SQL

    SharpETL allows you to write ETL (Extract, Transform, Load) processes using standard SQL dialects without requiring any custom SQL syntax.

  7. Implement Log-Driven task scheduling for ETL stability

    Log-Driven scheduling decouples the ETL task logic from the underlying scheduling framework (e.g., Airflow). Instead of relying on the scheduler's internal history, the system maintains its own structured logs of task execution (recording which task was scheduled at what time and its status). This approach acts similarly to 'breakpoint resume' for task scheduling, ensuring that tasks are executed in order and that failures are handled consistently regardless of the scheduling tool used.

  8. Generate and run DWD layer ETL tasks

    To move data from the ODS layer to the DWD (Data Warehouse Detail) layer, generate the task scripts using the ODS-to-DWD template and execute the job after creating the necessary DWD/DIM tables.
    # 1. Generate task scripts from the ODS->DWD template
./gradlew :spark:run --args="generate-dwd-sql -f ~/Desktop/postgres-dwd.xlsx --output ~/Downloads/SharpETL/spark/src/main/resources/tasks/"

# 2. Run the DWD job
./gradlew :spark:run --args="single-job --name=ods.t_order_dwd.t_fact_order --period=1440 --local"

  9. Implement upstream-driven task dependencies

    Upstream-driven tasks are used when a task (e.g., in the DWD layer) must follow the configuration and execution of a preceding task (e.g., in the ODS layer).

    Key benefits:

    • The downstream task automatically triggers once the upstream task succeeds.
    • If the upstream task is re-run (refreshed), the downstream task will also be re-run automatically, ensuring they remain synchronized without requiring manual reconfiguration of time windows.

  10. Set up the development environment for SharpETL

    To contribute code to SharpETL, ensure your development environment meets the following requirements:

    • Operating System: Linux or macOS
    • Java: JDK 8 installed
    • Containerization: Docker and Docker Compose installed (required for running demos, integration tests, and building the website)
    • Version Control: A GitHub account

  11. Read files from a mounted share folder

    The mount source reads files from a mounted network share folder and can upload them to HDFS. It supports filename regex matching and optional modification time filtering.
    -- step=1
  -- source=mount
  --  fileNamePattern=[\s\S]*.csv.\w*
  --  sourceDir=/mnt/ltg/Archive
  --  tempDestinationDir=/data1/shared_folder/tmp
  --  hdfsDir=hdfs:///data/ltg
  -- target=variables
  -- checkPoint=false
  -- dateRangeInterval=0

  12. Implement a custom data source in SharpETL

    To create a custom data source in SharpETL, you must implement the Source and/or Sink traits.

    • Source[DataFrame, Context]: Used for reading data. The read method returns a DataFrame based on the provided workflow step, job log, execution context, and variables.
    • Sink[DataFrame]: Used for writing data. The write method defines how the DataFrame is persisted.

    A single class can implement both traits or just one, depending on whether the data source needs to support both reading and writing.

    trait Source[DataFrame, Context] extends Serializable {
  def read(step: WorkflowStep, jobLog: JobLog, executionContext: Context, variables: Variables): DataFrame
}

trait Sink[DataFrame] extends Serializable {
  def write(df: DataFrame, step: WorkflowStep, variables: Variables): Unit
}