Load data from Parquet files in Amazon S3 on AWS

Column skipping

The SkipCols configuration parameter enables selective exclusion of columns from the imported Parquet data based on their index positions (0-based). This provides precise control over which columns to include or exclude while importing data. Skipped columns are defined as part of the connection string using ranges or discrete column indices.

SkipCols cannot be used together with the SOURCE COLUMN NAMES option. If both are specified in the same query, the import operation fails with the exception ETL-2230.

For more information and examples of use, see Configuration parameters and Examples.

Configuration parameters

Configuration parameters can be set to be query-specific using the connection string, and as global defaults using database-level parameters. If a parameter is set in the connection string, it will override the default database parameter.

Usage notes

• Connection string parameters are case-insensitive.

• Connection string parameters override database parameter values when both are provided.

• Default values for connection string parameters are sourced from the database parameters if not expressly set.

• If an exception is thrown for an invalid database parameter, the error message will be printed only on the first node.

Overview of configuration parameters

The following table is an overview of the available connection string parameters and the corresponding database parameters. For more details about each parameter, see Configuration parameter details.

Configuration parameter details

Log levels

The log level determines the granularity and amount of information logged by the system. Select a log level to tailor the logging output based on the use case, such as error debugging, performance monitoring, or high-level event tracking.

To enable structured logging for AWS, both -etlLogLevel and -traceLevel must be set to at least 2.

Create a connection

We recommend that you always create a connection object using CREATE CONNECTION and authenticate on AWS using the saved connection instead of providing authentication details directly in the IMPORT command.

CREATE OR REPLACE CONNECTION <my_connection>
    TO 'https://<bucketname>.s3[.<aws_region>].amazonaws.com'
    USER '<access_key>' IDENTIFIED BY '<secret_key>'
    [SESSION TOKEN '<session_token>']
;

Examples

Example 1 - Create schema and table with file tracking

In this example we create a connection to the S3 bucket, create a new schema and table, then load data from two Parquet files, using SQL options to store source metadata for row-origin tracking.

CREATE OR REPLACE CONNECTION my_s3_bucket
    TO 'https://my_s3_bucket.s3.eu-west-1.amazonaws.com'
    USER 'my_user' IDENTIFIED BY 'my_secret_key'
;

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

IMPORT INTO MY_SCHEMA.MY_TABLE
    FROM PARQUET AT my_s3_bucket 
    FILE 'nested/path/to/test_1.parquet' FILE 'nested/path/to/test_2.parquet' 
    WITH 
      SOURCE FILE HASH_SHA256 = sourcefile
      SOURCE ROW NUMBER = rownumber 
;

Example 2 - Using configuration parameters

In this example the connection string includes configuration parameters that will do the following:

• Limit the number of files to import concurrently to 2
• Increase the number of buffers to fetch during the import to 4
• Exclude columns 1, 3, 4, 5, 6, 7, 8, and 11 from the imported dataset

CREATE OR REPLACE CONNECTION my_s3_bucket
    TO 'https://my_s3_bucket.s3.eu-west-1.amazonaws.com;MaxConnections=2;MaxBatchFetchSize=4;SkipCols=1,3..8,11'
    USER 'my_user' IDENTIFIED BY 'my_secret_key'
;

Example 3 - Set dynamic batch size in the connection string

CREATE OR REPLACE CONNECTION my_s3_bucket 
    TO 'https://my_s3_bucket.s3.eu-west-1.amazonaws.com/;MaxRows=2048' 
    USER 'my_user' IDENTIFIED BY 'my_secret_key'
;

Example 4 - Use explicit column mapping

CREATE OR REPLACE CONNECTION my_s3_bucket
  TO 'https://my_s3_bucket.s3.eu-west-1.amazonaws.com';

CREATE SCHEMA IF NOT EXISTS MY_SCHEMA;
CREATE TABLE MY_SCHEMA.MY_TABLE (
  c1 INT,
  name VARCHAR(256),
  mapped_column VARCHAR(256),
  sourcefile HASHTYPE (256 BIT),
  rownumber INT
);

IMPORT INTO MY_SCHEMA.MY_TABLE
  FROM PARQUET AT my_s3_bucket
  FILE 'file_1.parquet'
  FILE 'file_2.parquet'
  WITH
    SOURCE COLUMN NAMES = ('col1', 'col2', 'renamed_col')
    SOURCE FILE HASH_SHA256 = sourcefile
    SOURCE ROW NUMBER = rownumber;