Transforms Excel Parser

transforms-excel-parser는 Java 변환에서 Excel 파일을 읽어 하나 이상의 표 형식 데이터셋으로 만드는 라이브러리입니다. Apache POI를 사용하며, 스키마가 없는 입력 데이터셋의 파일들을 Dataset<PortableFile>로 받아 ParseResult를 반환합니다.

Excel 추출에는 Pipeline Builder의 Excel 행 추출, Python 파일 변환과 openpyxl, Java 파일 변환과 Apache POI도 사용할 수 있습니다. Java 저장소에서 여러 시트와 양식을 함께 처리하면서 기본 파서 설정을 재사용하려면 transforms-excel-parser를 선택합니다. 스프레드시트를 원본으로 관리하거나 비정형 파일로 다루는 경우에는 미디어 집합을 검토합니다.

처리 범위

• 테이블 헤더로 열을 식별하고, 파일 사이에 일부 열만 겹치거나 스키마가 다른 입력을 처리합니다.
• 파일 하나를 메모리로 읽어 여러 시트 또는 같은 시트의 여러 표를 추출하고 여러 출력으로 보냅니다.
• 레이블 위, 아래나 옆에 값이 있는 양식에는 FormParser와 필드 추출 규칙을 사용합니다.
• Apache POI의 zip bomb 오탐과 최대 바이트 배열 크기 문제에 대응하는 전역 설정을 적용합니다.
• 오류를 확인하여 즉시 작업을 실패시키거나 오류 데이터프레임을 별도 출력에 저장할 수 있습니다.
• 증분 배치 사이의 열 차이를 처리하는 설정과 출력 유틸리티를 제공합니다.

지원 형식은 xls, xlt, xltm, xltx, xlsx, xlsm이며 xlsb는 지원하지 않습니다.

저장소 설정

Java 변환 저장소의 Maven 라이브러리 패널에서 transforms-excel-parser를 검색합니다.

환경에서 제공하는 최신 버전을 선택합니다.

추가 backing repository로 transforms-excel-parser-bundle을 가져오는 대화상자에서 Add library를 선택합니다.

eddie-spark-module-bundle 또는 ri.eddie.artifacts.repository.bundles가 함께 보이더라도 Java 저장소에는 transforms-excel-parser-bundle을 선택합니다. eddie 이름의 backing repository는 Pipeline Builder 전용입니다. 필요한 번들이 보이지 않으면 Palantir 담당자에게 설치를 요청합니다.

Show hidden files를 켜고 transforms-java/build.gradle의 dependencies에 라이브러리를 추가합니다. 다음은 0.155.0을 선택한 설정 예시이며, 실제로 선택한 버전에 맞춥니다.

dependencies {
    implementation 'com.palantir.workshop:transforms-excel-parser:0.155.0'
}

0.155.0 API 참조 ZIP을 내려받아 압축을 풀면 HTML API를 오프라인으로 읽을 수 있습니다. com/palantir/transforms/excel/package-summary.html에서 시작합니다.

파서와 결과 계약

단일 출력은 TransformsExcelParser.of(parser)로 구성하고 ParseResult.singleResult()를 사용합니다. 여러 출력은 builder의 putKeyToParser(key, parser)로 등록한 뒤 dataframeForKey(key)로 가져옵니다. 키는 출력 경로와 별개인 문자열이며, 하나의 구성에 FormParser와 TableParser를 섞을 수도 있습니다.

오류 데이터프레임을 확인하지 않으면 오류가 난 데이터가 빠진 사실을 놓칠 수 있습니다. 암호 해제 결과에는 시도한 비밀번호와 성공한 비밀번호가 들어가므로 저장 및 접근 범위를 제한합니다.

0.155.0의 includeFileModifiedTimestamp 기본값은 true입니다. 데이터 출력에는 _file_modified_timestamp를, 오류 및 암호 해제 결과에는 file_modified_timestamp를 추가합니다. 데이터의 _file_path와 수정 시각을 함께 사용하면 어느 파일 버전에서 나온 행인지 구분할 수 있습니다.

같은 버전의 maxByteArraySize 기본값은 Integer.MAX_VALUE이며 minInflateRatio는 0.000000000000001입니다. 후자는 Apache POI의 일반 기본값 0.01보다 높은 압축률을 허용합니다. 압축률이 높은 정상 파일을 처리하기 위한 설정이며, 입력 파일의 신뢰성과 메모리 사용 범위도 함께 관리해야 합니다.

실행 예제의 입력과 출력

각 코드는 같은 이름의 Java 파일에 넣는 독립적인 변환 클래스입니다. /Examples/Excel 아래 경로는 가상 데이터셋 경로이며 실제 입력, 출력과 오류 출력의 경로 또는 RID로 바꿉니다. 입력 파일이 있어도 시트 선택이나 헤더 추출 결과가 없으면 Optional이 비어 있을 수 있습니다. 예제는 결과가 있는 키에만 데이터 쓰기를 수행하며 오류 출력은 별도로 기록합니다.

한 줄 헤더의 테이블

SimpleHeaderExtractor의 rowsToSkip 기본값은 0이므로 첫 행이 헤더이면 TableParser.builder().build()만으로 구성할 수 있습니다. 다음 예제는 두 번째 행이 헤더이므로 첫 행을 건너뜁니다.

TableParser는 기본적으로 모든 시트를 선택하고 헤더 아래부터 시트 끝까지 추출합니다. SimpleHeaderExtractor는 병합된 헤더 셀도 처리하며 중복 열 이름은 column_name, column_name_2처럼 구분합니다.

package myproject.datasets;

import com.palantir.transforms.excel.ParseResult;
import com.palantir.transforms.excel.Parser;
import com.palantir.transforms.excel.TransformsExcelParser;
import com.palantir.transforms.excel.table.SimpleHeaderExtractor;
import com.palantir.transforms.excel.table.TableParser;
import com.palantir.transforms.lang.java.api.Compute;
import com.palantir.transforms.lang.java.api.FoundryInput;
import com.palantir.transforms.lang.java.api.FoundryOutput;
import com.palantir.transforms.lang.java.api.Input;
import com.palantir.transforms.lang.java.api.Output;
import java.util.Optional;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;

public final class SimpleTabularExcel {

    @Compute
    public void myComputeFunction(
            @Input("/Examples/Excel/simple_input") FoundryInput myInput,
            @Output("/Examples/Excel/simple_table") FoundryOutput myOutput,
            @Output("/Examples/Excel/simple_errors") FoundryOutput errorOutput
        ) {
        Parser tableParser = TableParser.builder()
                .headerExtractor(
                        SimpleHeaderExtractor.builder().rowsToSkip(1).build())
                .build();

        TransformsExcelParser transformsParser = TransformsExcelParser.of(tableParser);

        ParseResult result =
                transformsParser.parse(myInput.asFiles().getFileSystem().filesAsDataset());

        Optional<Dataset<Row>> maybeDf = result.singleResult();

        maybeDf.ifPresent(df -> myOutput.getDataFrameWriter(df).write());

        errorOutput.getDataFrameWriter(result.errorDataframe()).write();
    }
}

여러 행으로 이루어진 병합 헤더

다음 예제의 헤더는 A1부터 D2까지입니다. MultilayerMergedHeaderExtractor에 헤더 사각형의 왼쪽 위와 오른쪽 아래 셀을 지정합니다.

package myproject.datasets;

import com.palantir.transforms.excel.ParseResult;
import com.palantir.transforms.excel.Parser;
import com.palantir.transforms.excel.TransformsExcelParser;
import com.palantir.transforms.excel.table.MultilayerMergedHeaderExtractor;
import com.palantir.transforms.excel.table.TableParser;
import com.palantir.transforms.lang.java.api.Compute;
import com.palantir.transforms.lang.java.api.FoundryInput;
import com.palantir.transforms.lang.java.api.FoundryOutput;
import com.palantir.transforms.lang.java.api.Input;
import com.palantir.transforms.lang.java.api.Output;
import java.util.Optional;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;

public final class ComplexHeaderExcel {

    @Compute
    public void myComputeFunction(
            @Input("/Examples/Excel/complex_input") FoundryInput myInput,
            @Output("/Examples/Excel/complex_table") FoundryOutput myOutput,
            @Output("/Examples/Excel/complex_errors") FoundryOutput errorOutput
        ) {
        Parser tableParser = TableParser.builder()
                .headerExtractor(MultilayerMergedHeaderExtractor.builder()
                        .topLeftCellName("A1")
                        .bottomRightCellName("D2")
                        .build())
                .build();

        TransformsExcelParser transformsParser = TransformsExcelParser.of(tableParser);

        ParseResult result =
                transformsParser.parse(myInput.asFiles().getFileSystem().filesAsDataset());

        Optional<Dataset<Row>> maybeDf = result.singleResult();

        maybeDf.ifPresent(df -> myOutput.getDataFrameWriter(df).write());

        errorOutput.getDataFrameWriter(result.errorDataframe()).write();
    }
}

양식 시트와 여러 출력

다음 예제는 이름에 Form_A 또는 Form_B가 매칭되는 시트를 각 FormParser로 처리합니다. RegexSubstringMatchingSheetSelector는 지정한 정규식이 시트 이름의 일부에 매칭되는 모든 시트를 선택합니다.

각 시트의 B1과 B2 값을 추출하기 전에 왼쪽 한 칸의 레이블이 해당 필드 이름과 같은지 검사합니다. 예를 들어 Form_A의 A1은 form_a_field_1이어야 합니다. CellValue의 기본 returnNullOnAssertionFailure는 false이므로 검사 실패는 오류 데이터프레임에 기록되며 해당 FormParser의 결과에는 그 파일의 행을 넣지 않습니다.

양식과 표가 섞인 파일은 같은 TransformsExcelParser에 FormParser와 TableParser를 함께 등록할 수 있습니다.

package myproject.datasets;

import com.palantir.transforms.excel.TransformsExcelParser;
import com.palantir.transforms.excel.ParseResult;
import com.palantir.transforms.excel.Parser;
import com.palantir.transforms.excel.form.FieldSpec;
import com.palantir.transforms.excel.form.FormParser;
import com.palantir.transforms.excel.form.Location;
import com.palantir.transforms.excel.form.cellvalue.AdjacentCellAssertion;
import com.palantir.transforms.excel.form.cellvalue.CellValue;
import com.palantir.transforms.excel.functions.RegexSubstringMatchingSheetSelector;
import com.palantir.transforms.lang.java.api.Compute;
import com.palantir.transforms.lang.java.api.FoundryInput;
import com.palantir.transforms.lang.java.api.FoundryOutput;
import com.palantir.transforms.lang.java.api.Input;
import com.palantir.transforms.lang.java.api.Output;

public final class FormStyleExcel {
    private static final String FORM_A_KEY = "FORM_A";
    private static final String FORM_B_KEY = "FORM_B";

    @Compute
    public void myComputeFunction(
            @Input("/Examples/Excel/forms_input") FoundryInput myInput,
            @Output("/Examples/Excel/form_a") FoundryOutput formAOutput,
            @Output("/Examples/Excel/form_b") FoundryOutput formBOutput,
            @Output("/Examples/Excel/form_errors") FoundryOutput errorOutput) {
        Parser formAParser = FormParser.builder()
                .sheetSelector(new RegexSubstringMatchingSheetSelector("Form_A"))
                .addFieldSpecs(createFieldSpec("form_a_field_1", "B1"))
                .addFieldSpecs(createFieldSpec("form_a_field_2", "B2"))
                .build();

        Parser formBParser = FormParser.builder()
                .sheetSelector(new RegexSubstringMatchingSheetSelector("Form_B"))
                .addFieldSpecs(createFieldSpec("form_b_field_1", "B1"))
                .addFieldSpecs(createFieldSpec("form_b_field_2", "B2"))
                .build();

        TransformsExcelParser transformsParser = TransformsExcelParser.builder()
                .putKeyToParser(FORM_A_KEY, formAParser)
                .putKeyToParser(FORM_B_KEY, formBParser)
                .build();

        ParseResult result =
                transformsParser.parse(myInput.asFiles().getFileSystem().filesAsDataset());

        result.dataframeForKey(FORM_A_KEY)
                .ifPresent(df -> formAOutput.getDataFrameWriter(df).write());
        result.dataframeForKey(FORM_B_KEY)
                .ifPresent(df -> formBOutput.getDataFrameWriter(df).write());

        errorOutput.getDataFrameWriter(result.errorDataframe()).write();
    }

    private static FieldSpec createFieldSpec(String fieldName, String cellLocation) {
        return FieldSpec.of(
                fieldName,
                CellValue.builder()
                        .addAssertions(AdjacentCellAssertion.left(1, fieldName))
                        .location(Location.of(cellLocation))
                        .build());
    }
}

여러 출력을 증분 처리

Java 변환은 Python의 증분 데코레이터처럼 입력 범위를 자동으로 결정하지 않습니다. 다음 예제는 ReadRange.UNPROCESSED의 파일을 읽고 입력의 FilesModificationType을 출력 유틸리티에 전달합니다.

헤더의 CaseNormalizationOption은 CONVERT_TO_LOWERCASE 또는 CONVERT_TO_UPPERCASE로 맞추는 것이 중요합니다. 기본값 DO_NOT_NORMALIZE를 그대로 쓰면 배치마다 달라지는 대소문자가 스키마 차이를 만들 수 있습니다.

오류가 하나라도 있으면 쓰기 전에 실패시킵니다. 예외에 넣을 오류는 takeAsList(1)로 첫 행만 가져오므로 전체 오류를 드라이버 메모리에 모으지 않습니다. 오류 전체는 errorDataframe()으로 처리할 수 있습니다.

package myproject.datasets;

import com.palantir.transforms.excel.ParseResult;
import com.palantir.transforms.excel.Parser;
import com.palantir.transforms.excel.TransformsExcelParser;
import com.palantir.transforms.excel.functions.RegexSubstringMatchingSheetSelector;
import com.palantir.transforms.excel.table.CaseNormalizationOption;
import com.palantir.transforms.excel.table.SimpleHeaderExtractor;
import com.palantir.transforms.excel.table.TableParser;
import com.palantir.transforms.excel.utils.IncrementalUtils;
import com.palantir.transforms.lang.java.api.Compute;
import com.palantir.transforms.lang.java.api.FilesModificationType;
import com.palantir.transforms.lang.java.api.FoundryFiles;
import com.palantir.transforms.lang.java.api.FoundryInput;
import com.palantir.transforms.lang.java.api.FoundryOutput;
import com.palantir.transforms.lang.java.api.Input;
import com.palantir.transforms.lang.java.api.Output;
import com.palantir.transforms.lang.java.api.ReadRange;
import java.util.List;
import org.apache.spark.sql.Row;

public final class IncrementalTransform {

    @Compute
    public void myComputeFunction(
            @Input("/Examples/Excel/incremental_input") FoundryInput myInput,
            @Output("/Examples/Excel/sheet_1") FoundryOutput sheet1Output,
            @Output("/Examples/Excel/sheet_2") FoundryOutput sheet2Output) {
        Parser sheet1Parser = TableParser.builder()
                .headerExtractor(SimpleHeaderExtractor.builder()
                        .caseNormalizationOption(CaseNormalizationOption.CONVERT_TO_LOWERCASE).build())
                .sheetSelector(new RegexSubstringMatchingSheetSelector("Sheet1")).build();
        Parser sheet2Parser = TableParser.builder()
                .headerExtractor(SimpleHeaderExtractor.builder()
                        .caseNormalizationOption(CaseNormalizationOption.CONVERT_TO_LOWERCASE).build())
                .sheetSelector(new RegexSubstringMatchingSheetSelector("Sheet2")).build();
        TransformsExcelParser transformsParser = TransformsExcelParser.builder().putKeyToParser("Sheet1", sheet1Parser)
                .putKeyToParser("Sheet2", sheet2Parser).build();

        FoundryFiles foundryFiles = myInput.asFiles();
        ParseResult result = transformsParser.parse(foundryFiles.getFileSystem(ReadRange.UNPROCESSED).filesAsDataset());

        List<Row> errors = result.errorDataframe().takeAsList(1);
        if (!errors.isEmpty()) {
            throw new IllegalStateException("Excel parse error: " + errors.get(0));
        }

        FilesModificationType filesModificationType = foundryFiles.modificationType();
        result.dataframeForKey("Sheet1").ifPresent(
                dataframe -> IncrementalUtils.writeAppendingIfPossible(filesModificationType, dataframe, sheet1Output));
        result.dataframeForKey("Sheet2").ifPresent(
                dataframe -> IncrementalUtils.writeAppendingIfPossible(filesModificationType, dataframe, sheet2Output));
    }
}

IncrementalUtils.writeAppendingIfPossible의 출력 선택은 다음과 같습니다.

기존 파일을 수정한 배치도 APPEND할 수 있으므로, _file_path로 파일을 묶고 _file_modified_timestamp로 버전을 구분하여 후속 처리에서 필요한 버전을 선택합니다. 이 두 열은 같은 파일 버전의 여러 행에 공통으로 들어가는 메타데이터입니다.

이 예제는 한 배치에서 일부 파서가 데이터를 찾지 못하는 상황을 허용합니다. 시트 선택 결과가 비었다는 사실 자체는 파싱 오류가 아닙니다. 업무 규칙상 모든 출력이 있어야 한다면 입력 파일이 있었는지와 각 결과의 존재 여부를 함께 확인하고 누락을 오류로 처리해야 합니다.

증분 작업에서 오류를 별도 출력에만 기록하고 계속 진행하면 실패한 파일이 다음 증분 입력에서 다시 처리되지 않을 수 있습니다. 재처리하려면 실패한 파일을 다시 업로드하거나 출력 하나에 수동 트랜잭션을 만들어 스냅샷 빌드를 유도해야 합니다. Java 변환의 외부 스냅샷 트리거는 provenance가 없는 빈 APPEND 트랜잭션이며, 일반 데이터 추가와 구분하여 데이터셋 변경 절차에 따라 사용합니다.

동적으로 헤더 위치 찾기

TableParser와 FormParser의 sheetSelector, headerExtractor 같은 설정은 인터페이스를 받습니다. 기본 구현이 맞지 않으면 사용자 구현을 전달할 수 있으며 단일 메서드 인터페이스에는 Java 람다를 사용할 수 있습니다.

다음 HeaderExtractor는 첫 번째 셀 값이 record_id인 행을 찾습니다. 헤더 높이는 두 행, 너비는 다섯 열이라는 입력 조건을 사용합니다. 높이나 너비도 파일마다 달라진다면 그 값을 결정하는 규칙이 필요합니다. getLastRowNum()은 마지막 행의 0 기반 인덱스이므로 그 인덱스까지 검색합니다. 헤더를 찾지 못하면 빈 Optional을 반환하고 그 시트에서 행을 추출하지 않습니다.

package myproject.datasets;

import com.palantir.transforms.excel.ParseResult;
import com.palantir.transforms.excel.Parser;
import com.palantir.transforms.excel.TransformsExcelParser;
import com.palantir.transforms.excel.table.HeaderExtractor;
import com.palantir.transforms.excel.table.MultilayerMergedHeaderExtractor;
import com.palantir.transforms.excel.table.TableParser;
import com.palantir.transforms.excel.utils.WorkbookUtils;
import com.palantir.transforms.lang.java.api.Compute;
import com.palantir.transforms.lang.java.api.FoundryInput;
import com.palantir.transforms.lang.java.api.FoundryOutput;
import com.palantir.transforms.lang.java.api.Input;
import com.palantir.transforms.lang.java.api.Output;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellReference;
import org.apache.spark.sql.Dataset;

import java.util.Optional;

public class CustomHeaderExtractorUsageExample {
    private static String getFirstCellValue(Sheet sheet, int rowIndex) {
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            return null;
        }
        Cell cell = row.getCell(0);
        return cell == null ? null : WorkbookUtils.getValueAsString(cell);
    }

    @Compute
    public void myComputeFunction(
            @Input("/Examples/Excel/dynamic_headers_input") FoundryInput myInput,
            @Output("/Examples/Excel/dynamic_header_table") FoundryOutput myOutput,
            @Output("/Examples/Excel/dynamic_header_errors") FoundryOutput errorOutput
    ) {
        HeaderExtractor headerExtractor = sheet -> {
            int lastRowIndex = sheet.getLastRowNum();
            int firstHeaderRow = -1;
            for (int currentRowIndex = 0; currentRowIndex <= lastRowIndex; currentRowIndex += 1) {
                if ("record_id".equals(getFirstCellValue(sheet, currentRowIndex))) {
                    firstHeaderRow = currentRowIndex;
                    break;
                }
            }
            if (firstHeaderRow < 0) {
                return Optional.empty();
            }
            int lastHeaderRow = firstHeaderRow + 1;
            return MultilayerMergedHeaderExtractor
                    .builder()
                    .topLeftCellName(new CellReference(firstHeaderRow, 0).formatAsString())
                    .bottomRightCellName(new CellReference(lastHeaderRow, 4).formatAsString())
                    .build()
                    .extractHeader(sheet);
        };

        Parser tableParser = TableParser.builder()
                .headerExtractor(headerExtractor)
                .build();

        TransformsExcelParser transformsParser = TransformsExcelParser.of(tableParser);

        ParseResult result =
                transformsParser.parse(myInput.asFiles().getFileSystem().filesAsDataset());

        Optional<Dataset<org.apache.spark.sql.Row>> maybeDf = result.singleResult();

        maybeDf.ifPresent(df -> myOutput.getDataFrameWriter(df).write());
        errorOutput.getDataFrameWriter(result.errorDataframe()).write();
    }
}

Code Assist와 메모리 문제

Code Assist 작업 공간을 시작한 뒤 첫 Preview는 성공하지만 두 번째 Preview에서 다음과 같은 오류가 날 수 있습니다.

java.lang.ClassCastException: class com.palantir.transforms.excel.KeyedParsedRecord cannot be cast to class com.palantir.transforms.excel.KeyedParsedRecord (com.palantir.transforms.excel.KeyedParsedRecord is in unnamed module of loader java.net.URLClassLoader @5a5d825a; com.palantir.transforms.excel.KeyedParsedRecord is in unnamed module of loader java.net.URLClassLoader @53dafc50)

이 현상은 Code Assist Preview에 한정되며 빌드에는 영향을 주지 않습니다. 브라우저 창을 새로 고치면 전체 Code Assist 작업 공간을 재빌드하지 않고 Preview를 다시 실행할 수 있습니다.

Apache POI는 작은 Excel 파일도 메모리에서 크게 펼칠 수 있습니다. 기본 Spark 프로필의 작업당 메모리가 부족하면 다음과 같은 오류로 작업이 종료되거나 완료도 실패도 하지 않은 채 오래 멈출 수 있습니다.

Spark module '{module_rid}' died while job '{job_rid}' was using it

파일 하나를 처리하는 데 10분 이상 걸리는지는 메모리 부족을 의심할 참고 기준입니다. 메모리가 충분해도 매우 큰 파일에는 약 8분이 걸릴 수 있고, Spark 작업 하나가 여러 입력 파일을 처리할 수 있으므로 이 시간을 고정된 실패 판정으로 사용하지 않습니다.

메모리가 부족한 경우 EXECUTOR_MEMORY_LARGE와 EXECUTOR_CORES_EXTRA_SMALL 조합처럼 작업당 메모리를 늘리는 Spark 프로필을 사용합니다. 실행기 메모리를 늘리고 동시 작업에 나누어 주는 코어 수를 줄이는 방향으로 조정합니다.