Efficient Import of Parquet Files from AWS S3 Buckets

Detail name	Value
Changelog Number	21562
Type	New Feature
Status	Resolved
Fix Versions	Exasol 2025.1.0
Resolution Date	2025-07-08

Description

Exasol introduces native support for importing Parquet files from AWS S3 buckets, designed to enhance data ingestion processes through improved performance, flexibility, and scalability. This feature offers advanced functionality for parallel file and record handling, supporting a broad range of use cases with configurable parameters for optimized performance.

Key Feature Capabilities

Parallel Import Functionality

• Parallel Processing Across Files: Multiple Parquet files can be imported in parallel across nodes, enabling higher throughput for larger datasets.
• Parallel File Reads: Single Parquet files can be read using multiple parallel operations for faster and more efficient data read times.

Configurable Import Behavior

Import operations can be customized using connection string parameters or corresponding database parameters. Connection string parameters override database parameters when both are provided, allowing greater control over individual queries.

AWS Authentication Options

• Authentication using Access ID and Secret Key is supported for seamless access to AWS resources.
• Additional support for Session Tokens allows secure short-lived credential-based authentication for temporary access to AWS.

Source Data Tracking

• Import functionality can now capture source row metadata to track the origin of rows during the import process, including:

• SHA256 Hash of File: Allows secure mapping of rows to their respective file origins.
• Original Row Number: Captures the row's position in the source file (0-based index).

Column Skipping During Import

The SkipCols parameter enables selective exclusion of columns from the imported Parquet data based on their index positions (0-based). This provides precise control over the columns to include or exclude while importing data.

Skipped columns are defined as part of the connection string using ranges or discrete column indices. For example:

SkipCols=1,3..8,11

This configuration excludes columns 1, 3, 4, 5, 6, 7, 8, and 11. All other columns remain part of the imported dataset.

Comprehensive Data Type Support

• Compatible with a wide range of Parquet data types.

• Integer Types: Int8, Int16, Int32, Int64
• Unsigned Integer Types: UInt8, UInt16, UInt32, UInt64
• Floating-point Types: Float, Double
• Additional Types: Decimal128, String, LargeString, Date32, Date64, Bool, Timestamp

SQL Syntax Extension

The SQL syntax is extended to include origin tracking options for rows and files. This functionality enables file tracking and row mapping during the import process.

AWS URL Format Support

The implementation provides support for the following AWS S3 URL formats, ensuring compatibility across variations:

• https://<Bucket Name>.s3.amazonaws.com/ (Example: https://exasol-bucket.s3.amazonaws.com/)
• https://<Bucket Name>.s3.<Region>.amazonaws.com/ (Example: https://exasol-bucket.s3.us-east-1.amazonaws.com/)
• s3://<Bucket Name>/ (Example: s3://exasol-bucket/)

If no region is explicitly provided, the region is automatically detected.

Configuration Parameters

The feature includes several configurable parameters that influence import behavior. These can be set using either the connection string for query-specific configurations or database-level parameters for global defaults.

Connection String Parameter	Description	Default Value	DB Parameter Equivalent
MaxConnections	Number of Parquet files imported in parallel on a single node.	8	-etlParquetMaxConnections
MaxConcurrentReads	Number of parallel read operations per file. Set 0 or 1 for sequential reads.	3	-etlParquetMaxConcurrentReads
MaxBatchFetchSize	Maximum simultaneous buffers fetched during import.	1	-etlParquetMaxBatchFetchSize
SkipCols	Skips specific column indexes during import. Example: SkipCols=1,3..8,11.	None	None

Connection string parameters are case-insensitive and override database parameter values when both are provided.

Example connection string usage:

CREATE OR REPLACE CONNECTION aws_conn 
TO 'https://xxxxx.s3.amazonaws.com/;MaxConcurrentReads=3;MaxBatchFetchSize=4' 
USER 'your_user' IDENTIFIED BY 'your_password';

SQL Syntax Extension

Additional SQL options enable row-origin tracking during imports. These options enhance row metadata tracking by appending information such as file SHA256 hash and source row numbers.

Example syntax:

IMPORT INTO STAGING_TABLE
  FROM PARQUET AT S3_BUCKET_CONNECTION
  FILE 'nested/path/to/test.parquet' FILE 'nested/path/to/test_2.parquet' 
  WITH
    SOURCE FILE HASH_SHA256 = "source_path" 
    SOURCE ROW NUMBER = "row_number";

Requirements:

• Ensure the target table contains suitable columns to store this metadata:

• source_path: File hash column (CHAR(64), VARCHAR(N) with N>=64, or HASHTYPE (256 BIT)).
• row_number: Row number column (DECIMAL(p,s)).

Examples

Schema and Table Creation with File Tracking

DROP SCHEMA IF EXISTS TEST_SCHEMA CASCADE;
CREATE SCHEMA TEST_SCHEMA;
CREATE TABLE TEST_SCHEMA.TEST_TB (sourcefile HASHTYPE (256 BIT), c1 INT, rownumber INT);

IMPORT INTO TEST_SCHEMA.TEST_TB
  FROM PARQUET AT 
       '{FileURL}' USER '{Access_Key}' IDENTIFIED BY '{Secret_Key}' 
       FILE '{FileName}'  
       WITH 
          SOURCE ROW NUMBER = rownumber 
          SOURCE FILE HASH_SHA256 = sourcefile;

Skipping Columns During Import

CREATE OR REPLACE CONNECTION aws_conn 
TO 'https://xxxxx.s3.amazonaws.com/;SkipCols=1,3..8,11' 
USER 'your_user' IDENTIFIED BY 'your_password';

IMPORT INTO DEST_TABLE
  FROM PARQUET AT aws_conn 
  FILE 'data.parquet';

In this example, columns 1, 3 through 8, and 11 would be excluded from the imported dataset.

Using Session Tokens

The following example demonstrates how to specify a session token for authentication while importing data:

CREATE OR REPLACE CONNECTION aws_conn 
TO 'https://xxxxx.s3.amazonaws.com/' 
USER 'your_access_key' 
IDENTIFIED BY 'your_secret_key' 
SESSION TOKEN 'your_session_token';

IMPORT INTO DEST_TABLE
  FROM PARQUET AT aws_conn 
  FILE 'path/to/data.parquet';

In this example:

• your_access_key: The AWS Access ID.
• your_secret_key: The AWS Secret Key.
• your_session_token: Temporary AWS session token for secure access to resources.

Remarks

• Data imported from Parquet files is not guaranteed to be sorted.
• Default values for connection string parameters are sourced from database parameters if not expressly set.

This feature enables efficient and flexible Parquet file imports with detailed control over parallel processing, origin tracking, and column inclusions/exclusions. For further details or assistance, refer to the official technical documentation.