Parquet Import – Column Mapping Support

Details

Detail name Value
Changelog Number 24691
Type New Feature
Status Resolved
Fix Versions Exasol 2025.1.5, Exasol 2025.2.0
Resolution Date 2025-12-05

Summary

Parquet Import now supports user-defined column mapping. This feature enables precise mapping of columns from Parquet files to target database tables, greatly simplifying the integration of schema-diverse datasets and supporting advanced ETL workflows.

Key Features & Enhancements

  1. Unified Import Query for Multiple Files
  2. User-Defined Column Mapping
  3. Robust Handling of Missing Columns
  4. Flexible Data Type Compatibility
  5. Efficient Processing for Large Data Sets
  6. Error Handling & Reporting:
  • Supports a single import query for loading data from multiple Parquet files into the target table.
  • No need for schema-specific grouping or queries.
  • Ensures consistent mapping of specified columns across all files.
  • Enables users to define specific column names for extraction from Parquet files, mapped to target table columns.
  • Column names are treated case sensitively during mapping. Users must ensure correctness in casing to avoid import NULL values as the column is not found.
  • Missing columns in Parquet files now default to NULL values during import into target table columns (non-configurable).
  • Ensures unified handling for all files.
  • Automatic type casting for compatible data types (e.g., int32 → int64, etc).
  • Resolves mixed datatype columns across files seamlessly during the import process.
  • Compatibility validated to prevent precision loss and truncation errors.
  • Optimized schema parsing and validation mechanisms minimize overhead.
  • Implements batching and caching for improved performance.
  • Detailed error messages for incompatible column datatypes or constraints violations (e.g., NOT NULL constraint).
  • Graceful query abortion for offending rows, providing diagnostic information on the impacted column, datatype, and source filename.

Best Practices and Known Constraints

  1. Case Sensitivity
  2. Runtime Validation Only
  3. Duplicate Columns in Parquet Files
  4. Column Count Mismatches
  5. Unsupported Parquet Column Types
  6. Unsupported Parquet to Target Data Conversion
  7. Constraints on SkipCols Parameter
  8. Data Truncation Handling
  9. Constraints Violations
  • Column names in Parquet files are treated as case sensitive during mapping. If there is a mismatch in case between the source file and the source column names, the system will fail to locate the column, inserting NULL for the corresponding values in the target table.
  • Schema mismatches, data type incompatibilities, and missing column issues are detected at runtime when the query is executed. There is no pre-validation mechanism to identify schema or type mismatches across files before query execution.
  • Parquet files containing duplicate column names are not supported, and an immediate error is thrown.
  • Exception
  • Allowed Scenario: Multiple imports of the same column name into distinct target table columns are supported.
ETL-2231: Parquet file (<Parquet File Url>) contains duplicate column names (<Parquet File Column Name>). Only columns with unique names can be imported.
Example
ETL-2231: Parquet file (s3://test/import_file.parquet) contains duplicate column names (id). Only columns with unique names can be imported.
  • The number of columns specified for the import list must match precisely with the number of columns in the target schema:
  • Overspecified Source Column Names: Specifying more columns in the SOURCE COLUMN NAMES list than present in the import query causes errors.
  • Underspecified Source Column Names: Specifying fewer columns than required by the target schema triggers query termination. 
Specified more columns in SOURCE COLUMN NAMES than in import list.
Specified more columns import list than in SOURCE COLUMN NAMES.
  • Certain Parquet column types are unsupported, and queries containing these types are aborted. Unsupported types include:
  • Exception
  • Nested/Complex Data: Structs, arrays, or lists of values.
  • Incompatible Numeric Conversions: For example, float to INTEGER.
ETL-2226: Column (<Parquet File Column Name>) in Parquet file (<Parquet File Url>) has an unsupported data type (<Parquet File Nested Column Data Type>).
Example
ETL-2226: Column (ID) in Parquet file (s3://test/import_file.parquet) has an unsupported data type (list).
  • Import operations fail when the Parquet column type cannot be converted to the corresponding target table type (e.g., floating-point types like DOUBLE cannot be converted to DECIMAL).
  • Exception 
ETL-2228: [Column=<Column Name>] [Unsupported Parquet column data conversion from <Parquet Column Type> to <Target Table Column Type>.] [File: <Parquet File Url>]
Example:
ETL-2228: [Column=id] [Unsupported Parquet column data conversion from Double to Decimal(36,0).] [File: s3://test/import_file.parquet]
  • The SkipCols connection string parameter cannot be used alongside user-defined column mapping. If both are specified in the query, it results in an immediate error.
  • Exception 
ETL-2230: Either column name mapping or skip column functionality can be used, but not both simultaneously.
  • If data values in the Parquet file exceed the maximum allowed size or length for the corresponding target table column, data truncation errors occur:
  • Example: A VARCHAR(100) target column cannot store a string of length 101 from Parquet files.
  • Behavior Based on REJECT LIMIT Clause:
  • With REJECT LIMIT: The query continues execution until the specified limit of rejected rows is reached. Valid rows are still loaded.
  • Without REJECT LIMIT: The query terminates immediately upon the first truncation error.
  • A constraint violation occurs when imported data does not adhere to the rules defined in the target database table schema. Common constraints include:
  • When data being imported from Parquet files fails to meet these constraints, the import operation is rejected for the affected row or the entire query is aborted, according to the error and the import configuration.
  • Example
  • NOT NULL: The column does not allow NULL values.
  • UNIQUE: All values in the column must be unique.
  • PRIMARY KEY: Combines uniqueness and NOT NULL for a column.
  • Consider a table defined as follows:
  • An import query may look like this:
  • No data violating constraints is loaded into the table.
CREATE TABLE employees (
    id INT,
    name VARCHAR(100) NOT NULL,   -- NOT NULL constraint
    department VARCHAR(50)
);
IMPORT INTO employees
FROM PARQUET AT 's3://test'
FILE 'import_file.parquet'
WITH SOURCE COLUMN NAMES = ('id', 'name', 'department');

If the column name is missing in the Parquet file, the system inserts NULL for those rows. Given the NOT NULL constraint, this action is not permitted and results in an error. The corresponding error message typically states:

ETL-4101: Inserting data failed with error 'constraint violation - not null (column NAME in table EMPLOYEES)'

Detailed Data Type Compatibility

Target Column Type Supported Parquet Types Unsupported Types
Integer / Decimal int8, int16, int32, int64, uint8, uint16, uint32, uint64, decimal128, boolean float, double, string, timestamp, etc
Double int8, int16, int32, int64, uint8, uint16, uint32, uint64, float, double decimal128, string, etc.
(Varchar, Char, Hash) string, large_string numeric, binary, bool, timestamp
Date date32, date64 string, numeric, timestamp, etc.
Boolean boolean numeric, string, date, timestamp
Timestamp timestamp, date32, date64 numeric, string, bool

SQL Syntax Extension

IMPORT INTO ( <target_column_list> )
    FROM PARQUET AT '<bucket_or_path>'
    USER '<username>' IDENTIFIED BY '<password>'
    FILE '<file1.parquet>'
    FILE '<file2.parquet>'
    ...
WITH 
    SOURCE COLUMN NAMES = (<source_column_name1>, <source_column_name2>, ...);
  • <target_column_list>: Comma-separated list of target columns with datatype, matching table schema.
  • <bucket_or_path>: Location of Parquet files (S3 bucket, file path, etc.).
  • <username> and <password>: Authentication credentials.
  • FILE '<file>': Each file from which data is imported.
  • WITH SOURCE COLUMN NAMES = (...): Explicit mapping of source column names in the same order as the target columns.

Example 

IMPORT INTO (
    NAME VARCHAR(128),
    ID INT,
    Amount FLOAT,
    ValueC DECIMAL(32, 4),
    Rate DOUBLE,
    Category VARCHAR(128)
)
FROM PARQUET AT 's3://test/'
USER 'xxxxxx' IDENTIFIED BY 'xxxxxx'
FILE 'dataset_1.parquet'
FILE 'dataset_2.parquet'
FILE 'dataset_3.parquet'
WITH 
SOURCE COLUMN NAMES = ('name', 'id', 'amount', 'value', 'rate', 'category');

This syntax allows precise control over mapping Parquet columns to the target table, supporting schema diversity and ensuring correct import behavior.

SQL Syntax Extension with Source Tracking

IMPORT INTO (
    <target_column_list>,
    <row_number_column>,         -- Optional: column to store source row number
    <file_hash_column>           -- Optional: column to store source file hash/path
)
FROM PARQUET AT '<bucket_or_path>'
USER '<username>' IDENTIFIED BY '<password>'
FILE '<file1.parquet>'
FILE '<file2.parquet>'
...
WITH
    SOURCE ROW NUMBER = <row_number_column>
    SOURCE FILE HASH_SHA256 = <file_hash_column>
    SOURCE COLUMN NAMES = (<source_column_name1>, <source_column_name2>, ...);
  • <row_number_column>: Name of the column for storing the Parquet row number.
  • <file_hash_column>: Name of the column for storing the Parquet file path or SHA256 hash.

Example 

CREATE OR REPLACE TABLE BASIC_TYPES (
    ID DECIMAL(10,0),
    NAME VARCHAR(256),
    EXA_ROW_NUM DECIMAL(36, 0),          -- Column for row number tracking
    EXA_SOURCE_PATH VARCHAR(64)          -- Column for source file info
);

IMPORT INTO TEST_SCHEMA.BASIC_TYPES
FROM PARQUET AT 's3://test/'
USER 'xxxxxx' IDENTIFIED BY 'xxxxxx'
FILE 'dataset_1.parquet'
FILE 'dataset_2.parquet'
FILE 'dataset_3.parquet'
WITH
    SOURCE ROW NUMBER = EXA_ROW_NUM
    SOURCE FILE HASH_SHA256 = EXA_SOURCE_PATH
    SOURCE COLUMN NAMES = ('ID', 'NAME');

Conclusion

The Parquet Import Column Mapping feature is now fully implemented. The new capabilities enable flexible and robust integration of diverse Parquet datasets into structured database tables, with user-controlled column mapping, comprehensive data type compatibility checks, and support for detailed source tracking. Enhanced error handling—including behavior for data truncation based on the REJECT LIMIT clause—ensures reliability in ETL workflows.

With these improvements, large-scale data imports are streamlined, and auditability is strengthened.