Microsoft Certified: Fabric Analytics Engineer AssociatePrepare and transform data (20-25%)Medium
A manufacturing company uses a legacy SCADA system that exports daily production logs as text files with a fixed-width format. Each record has specific fields like 'Timestamp' (14 chars), 'Product ID' (10 chars), and 'Quantity' (5 chars), always occupying the same character positions. A data engineer needs to ingest these files into a Lakehouse using a Spark Notebook in Microsoft Fabric, ensuring correct parsing of each field into its respective column. Which PySpark DataFrame function or method is best suited for efficiently parsing this fixed-width data?
- A```python df.withColumn('Timestamp', regexp_extract(col('value'), '^(.*){14}', 1)) ```
- B```python df.withColumn('Timestamp', substring(col('value'), 1, 14)) ```
- C```python df.withColumn('Timestamp', split(col('value'), ' ')[0]) ```
- D```python df.withColumn('Timestamp', translate(col('value'), ' ', '')) ```
Show answer & explanationAnswer & explanation
Correct answer: B. ```python df.withColumn('Timestamp', substring(col('value'), 1, 14)) ```
Fixed-width data parsing directly involves extracting substrings based on their start position and length. The `substring` function in PySpark is specifically designed for this purpose, allowing precise extraction of fields without relying on delimiters or complex regular expressions, making it the most efficient and straightforward method for fixed-width formats.
Why the other options are wrong
- A. `regexp_extract` uses regular expressions, which can be overly complex and less performant than `substring` for simple fixed-width parsing, and the regex provided is incomplete for full extraction.
- C. `split` is used for delimiter-separated values, which is not the case for fixed-width data.
- D. `translate` is used for character-by-character replacement, not for extracting specific segments of a string based on position.
PySpark Fixed-Width Parsing
The process of extracting specific data fields from a string where each field occupies a predefined number of characters at a fixed position.
- Uses `substring` function in PySpark.
- Requires knowledge of start position and length of each field.
- Efficient for structured text files without delimiters.
Memory trick: Substring is like a precise ruler, cutting out exactly what you need from a fixed-width line.