Kylo工具数据清洗方法问询：除错误校验外的功能咨询

Great question! Kylo absolutely has built-in capabilities to handle those exact data cleaning tasks you mentioned—deleting empty records and detecting/removing duplicate columns, alongside the data validation rules you’re already leveraging. Here’s how to implement each:

Deleting Empty Records #

Kylo offers straightforward ways to filter out empty (all-null or partially null) records depending on your needs:

• Using the Filter Processor:
  1. In your Kylo data pipeline, add a Filter transformation component after your data source.
  2. Define a filter condition to exclude records where all fields are null, or target specific critical fields that can’t be empty. For example, if user_id is a required field, set the condition to user_id IS NOT NULL.
• Using Spark SQL:
  If you prefer writing SQL, add a SQL Transformation component and use a WHERE clause to filter out empty records. For all-null records:

  SELECT * FROM input_table 
  WHERE NOT (column1 IS NULL AND column2 IS NULL AND column3 IS NULL)
  

  For targeting specific fields:

  SELECT * FROM input_table WHERE required_column IS NOT NULL
  

Detecting and Removing Duplicate Columns #

Kylo combines data profiling and transformation tools to handle duplicate columns effectively:

• Step 1: Detect Duplicate Columns via Data Profiling
  Run Kylo’s built-in Data Profiler on your dataset. The profiler will generate statistics for each column, including:
  • Matching column names (an obvious red flag for duplicates)
  • Identical value distributions and data types
    You can review these results in the Kylo UI to pinpoint which columns are duplicates.
• Step 2: Remove Duplicate Columns
  • Using the Select Processor:
    Add a Select transformation component and explicitly choose only the unique columns you want to retain. This is simple for small datasets with few duplicates.
  • Using Spark SQL:
    For larger datasets or batch removal, use an ALTER TABLE statement or a SELECT query that excludes duplicate columns:

    -- Drop duplicate columns directly
    ALTER TABLE input_table DROP COLUMN duplicate_col_1, duplicate_col_2;
    
    -- Or select only unique columns
    SELECT unique_col_1, unique_col_2, unique_col_3 FROM input_table
    

  • Custom Groovy Script (for bulk handling):
    If you have many duplicate columns, you can use a Groovy Script processor to automate detection and removal. For example, a script that checks for duplicate column names or identical value sets and filters them out dynamically.

Bonus: Integrate with Your Existing Validation Rules #

You can chain these cleaning steps right after your data validation pipeline. For example:

1. Run your existing data validation rules to flag errors
2. Filter out empty records
3. Remove duplicate columns
4. Proceed with further transformation or loading

内容的提问来源于stack exchange，提问作者Nitin Tolani