S3

Overview

Features

File Compressions

Supported file formats

Getting started

Requirements and prerequisites

• Access to the S3 bucket containing the files to replicate (please ensure that the IAM user associated with the credentials has read and list permissions for the bucket)
  • AWS Access Key ID
  • AWS Secret Access Key
  • Bucket name

Set up guide

1. Create a new source, select S3 (Airbyte) from the Source type dropdown and enter a name for it.
2. On the source setup page, create a new S3 secret, enter a unique name for it.
3. Enter the Output Stream Name. This will be the name of the table in the DWH (can contain letters, numbers and underscores).
4. Enter the Pattern of files to replicate. This is a regular expression that allows to pattern match the specific files to replicate. If you are replicating all the files within your bucket, use ** as the pattern. For more precise pattern matching options, refer to the Path Patterns section below.
5. Enter the name of the Bucket containing your files to replicate.
6. If you are syncing from a private bucket, you must fill the AWS Access Key ID and AWS Secret Access Key fields with the appropriate credentials to authenticate the secret. All other fields are optional and can be left empty. Refer to the S3 Provider Settings section below for more information on each field.
7. In the File Format box, use the dropdown menu to select the format of the files you'd like to replicate. The supported formats are CSV, Parquet, Avro and JSONL. Toggling the Optional fields button within the File Format box will allow you to enter additional configurations based on the selected format. For a detailed breakdown of these settings, refer to the File Format section below.
8. (Optional) - If you want to enforce a specific schema, you can enter a Manually enforced data schema. By default, this value is set to {} and will automatically infer the schema from the file(s) you are replicating. For details on providing a custom schema, refer to the User Schema section.

How to configure AWS permissions

1. Log in to your Amazon AWS account and open the IAM console (opens in a new tab).

2. In the IAM dashboard, select Policies, then click Create Policy.

3. Select the JSON tab, then paste the following JSON into the Policy editor (be sure to substitute in your bucket name):

   aws_policy.json

   
   

   _16

   {

   _16

   "Version": "2012-10-17",

   _16

   "Statement": [

   _16

   {

   _16

   "Effect": "Allow",

   _16

   "Action": [

   _16

   "s3:GetObject",

   _16

   "s3:ListBucket"

   _16

   ],

   _16

   "Resource": [

   _16

   "arn:aws:s3:::{your-bucket-name}/*",

   _16

   "arn:aws:s3:::{your-bucket-name}"

   _16

   ]

   _16

   }

   _16

   ]

   _16

   }

   
   

4. Give your policy a descriptive name, then click Create policy.

5. In the IAM dashboard, click Users. Select an existing IAM user or create a new one by clicking Add users.

6. If you are using an existing IAM user, click the Add permissions dropdown menu and select Add permissions. If you are creating a new user, you will be taken to the Permissions screen after selecting a name.

7. Select Attach policies directly, then find and check the box for your new policy. Click Next, then Add permissions.

8. After successfully creating your user, select the Security credentials tab and click Create access key. You will be prompted to select a use case and add optional tags to your access key. Click Create access key to generate the keys.

Your Secret Access Key will only be visible once upon creation. Be sure to copy and store it securely for future use.

For more information on managing your access keys, please refer to the official AWS documentation (opens in a new tab).

Path Patterns

(tl;dr - path pattern syntax using wcmatch.glob (opens in a new tab). GLOBSTAR and SPLIT flags are enabled.)

This connector can sync multiple files by using glob-style patterns, rather than requiring a specific path for every file. This enables:

• Referencing many files with just one pattern, e.g. ** would indicate every file in the bucket.
• Referencing future files that don't exist yet (and therefore don't have a specific path).

You must provide a path pattern. You can also provide many patterns split with | for more complex directory layouts.

Each path pattern is a reference from the root of the bucket, so don't include the bucket name in the pattern(s).

Some example patterns:

• ** : match everything.
• **/*.csv : match all files with specific extension.
• myFolder/**/*.csv : match all csv files anywhere under myFolder.
• */** : match everything at least one folder deep.
• */*/*/** : match everything at least three folders deep.
• **/file.*|**/file : match every file called "file" with any extension (or no extension).
• x/*/y/* : match all files that sit in folder x -> any folder -> folder y.
• **/prefix*.csv : match all csv files with specific prefix.
• **/prefix*.parquet : match all parquet files with specific prefix.

Let's look at a specific example, matching the following bucket layout:

We want to pick up part1.csv, part2.csv and part3.csv (excluding another_part1.csv for now). We could do this a few different ways:

• We could pick up every csv file called "partX" with the single pattern **/part*.csv.
• To be a bit more robust, we could use the dual pattern some_table_files/*.csv|more_table_files/*.csv to pick up relevant files only from those exact folders.
• We could achieve the above in a single pattern by using the pattern *table_files/*.csv. This could however cause problems in the future if new unexpected folders started being created.
• We can also recursively wildcard, so adding the pattern extras/**/*.csv would pick up any csv files nested in folders below "extras", such as "extras/misc/another_part1.csv".

We recommend using a pattern that ensures clarity and is robust against future additions to the directory structure.

S3 Provider Settings

• AWS Access Key ID: One half of the required credentials (opens in a new tab) for accessing a private bucket.
• AWS Secret Access Key: The other half of the required credentials (opens in a new tab) for accessing a private bucket.
• Path Prefix: An optional string that limits the files returned by AWS when listing files to only those starting with the specified prefix. This is different than the Path Pattern, as the prefix is applied directly to the API call made to S3, rather than being filtered within a source. This is not a regular expression and does not accept pattern-style symbols like wildcards (*). We recommend using this filter to improve performance if your bucket has many folders and files that are unrelated to the data you want to replicate, and all the relevant files will always reside under the specified prefix.

  • Together with the Path Pattern, there are multiple ways to specify the files to sync. For example, all the following configurations are equivalent:

    • Prefix = empty, Pattern = path1/path2/myFolder/**/*
    • Prefix = path1/, Pattern = path2/myFolder/**/*.csv
    • Prefix = path1/path2/, Pattern = myFolder/**/*.csv
    • Prefix = path1/path2/myFolder/, Pattern = **/*.csv

  • The ability to individually configure the prefix and pattern has been included to accommodate situations where you do not want to replicate the majority of the files in the bucket. If you are unsure of the best approach, you can safely leave the Path Prefix field empty and just set the Path Pattern to meet your requirements.

• Endpoint: An optional parameter that enables the use of non-Amazon S3 compatible services. If you are using the default Amazon service, leave this field blank.
• Start Date: An optional parameter that marks a starting date and time in UTC for data replication. Any files that have not been modified since this specified date/time will not be replicated. Use the provided datepicker (recommended) or enter the desired date programmatically in the format YYYY-MM-DDTHH:mm:ssZ. Leaving this field blank will replicate data from all files that have not been excluded by the Path Pattern and Path Prefix.

File Format Settings

The Reader in charge of loading the file format is currently based on PyArrow (opens in a new tab) (Apache Arrow).

All files within one stream must adhere to the same read options for every provided format.

CSV

Since CSV files are effectively plain text, providing specific reader options is often required for correct parsing of the files. These settings are applied when a CSV is created or exported so please ensure that this process happens consistently over time.

• Delimiter: Even though CSV is an acronym for Comma Separated Values, it is used more generally as a term for flat file data that may or may not be comma separated. The delimiter field lets you specify which character acts as the separator. To use tab-delimiters (opens in a new tab), you can set this value to \t. By default, this value is set to ,.

• Infer Datatypes: This option determines whether a schema for the source should be inferred from the current data. When set to False and a custom schema is provided, the manually enforced schema takes precedence. If no custom schema is set and this option is set to False, all fields will be read as strings. Set to True by default.

• Quote Character: In some cases, data values may contain instances of reserved characters (like a comma, if that's the delimiter). CSVs can handle this by wrapping a value in defined quote characters so that on read it can parse it correctly. By default, this is set to ".

• Escape Character: An escape character can be used to prefix a reserved character and ensure correct parsing. A commonly used character is the backslash (\). For example, given the following data:

  
  

  _10

  Product,Description,Price

  _10

  Jeans,"Navy Blue, Bootcut, 34\"",49.99

  
  

  The backslash (\) is used directly before the second double quote (") to indicate that it is not the closing quote for the field, but rather a literal double quote character that should be included in the value (in this example, denoting the size of the jeans in inches: 34" ).

  Leaving this field blank (default option) will disallow escaping.

• Encoding: Some data may use a different character set (typically when different alphabets are involved). See the list of allowable encodings here (opens in a new tab). By default, this is set to utf8.

• Double Quote: This option determines whether two quotes in a quoted CSV value denote a single quote in the data. Set to True by default.

• Allow newlines in values: Also known as multiline, this option addresses situations where newline characters occur within text data. Typically, newline characters signify the end of a row in a CSV, but when this option is set to True, parsing correctly handles newlines within values. Set to False by default.

• Additional Reader Options: This allows for editing the less commonly used CSV ConvertOptions (opens in a new tab). The value must be a valid JSON string, e.g.:

  csv_reader_options.json

  
  

  _10

  {

  _10

  "timestamp_parsers": [

  _10

  "%m/%d/%Y %H:%M", "%Y/%m/%d %H:%M"

  _10

  ],

  _10

  "strings_can_be_null": true,

  _10

  "null_values": [

  _10

  "NA", "NULL"

  _10

  ]

  _10

  }

  
  

• Advanced Options: This allows for editing the less commonly used CSV ReadOptions (opens in a new tab). The value must be a valid JSON string. One use case for this is when your CSV has no header, or if you want to use custom column names. You can specify column_names using this option. For example:

  csv_advanced_options.json

  
  

  _10

  {

  _10

  "column_names": [

  _10

  "column1", "column2", "column3"

  _10

  ]

  _10

  }

  
  

• Block Size: This is the number of bytes to process in memory at a time while reading files. The default value of 10000 is usually suitable, but if your files are particularly wide (lots of columns, or the values in the columns are particularly large), increasing this might help avoid schema detection failures.

  Be cautious when raising this value too high, as it may result in Out Of Memory issues due to increased memory usage.

Parquet

Apache Parquet is a column-oriented data storage format of the Apache Hadoop ecosystem. It provides efficient data compression and encoding schemes with enhanced performance to handle complex data in bulk. At the moment, partitioned parquet datasets are unsupported. The following settings are available:

• Selected Columns: If you only want to sync a subset of the columns from the file(s), enter the desired columns here as a comma-delimited list. Leave this field empty to sync all columns.
• Record Batch Size: Sets the maximum number of records per batch. Batches may be smaller if there aren’t enough rows in the file. This option can help avoid out-of-memory errors if your data is particularly wide. Set to 65536 by default.
• Buffer Size: If set to a positive number, read buffering is performed during the deserializing of individual column chunks. Otherwise I/O calls are unbuffered. Set to 2 by default.

For more information on these fields, please refer to the Apache documentation (opens in a new tab).

Avro

The Avro parser uses the Fastavro library (opens in a new tab). Currently, no additional options are supported.

JSONL

The JSONL parser uses the PyArrow library, which only supports the line-delimited JSON format. For more detailed info, please refer to the official docs (opens in a new tab).

• Allow newlines in values: While JSONL typically has each JSON object on a separate line, there are cases where newline characters may appear within JSON values, such as multiline strings. This option enables the parser to correctly interpret and treat newline characters within values. Please note that setting this option to True may affect performance. Set to False by default.

• Unexpected field behavior: This parameter determines how any JSON fields outside of the explicit schema (if defined) are handled. Possible behaviors include:

  • ignore: Unexpected JSON fields are ignored.
  • error: Error out on unexpected JSON fields.
  • infer: Unexpected JSON fields are type-inferred and included in the output.

  Set to infer by default.

• Block Size: This sets the number of bytes to process in memory at a time while reading files. The default value of 10000 is usually suitable, but if your files are particularly wide (lots of columns or the values in the columns are particularly large), increasing this might help avoid schema detection failures.

  Be cautious when raising this value too high, as it may result in Out Of Memory issues due to increased memory usage.

User Schema

Providing a schema allows for more control over the output of this stream. Without a provided schema, columns and datatypes will be inferred from the first created file in the bucket matching your path pattern and suffix. This will probably be fine in most cases but there may be situations you want to enforce a schema instead, e.g.:

• You only care about a specific known subset of the columns. The other columns would all still be included, but packed into the _ab_additional_properties map.

• Your initial dataset is quite small (in terms of number of records), and you think the automatic type inference from this sample might not be representative of the data in the future.

• You want to purposely define types for every column.

• You know the names of columns that will be added to future data and want to include these in the core schema as columns rather than have them appear in the _ab_additional_properties map.

Or any other reason! The schema must be provided as valid JSON as a map of {"column": "datatype"} where each datatype is one of:

• string
• number
• integer
• object
• array
• boolean
• null

For example:

• { "id": "integer", "location": "string", "longitude": "number", "latitude": "number" }
• { "username": "string", "friends": "array", "information": "object" }

Please note, the S3 Source connector used to infer schemas from all the available files and then merge them to create a superset schema. Starting from version 2.0.0 the schema inference works based on the first file found only. The first file we consider is the oldest one written to the prefix.

Limitations

Please note that using cloud storage may incur egress costs. Egress refers to data that is transferred out of the cloud storage system, such as when you download files or access them from a different location. For detailed information on egress costs, please consult the Amazon S3 pricing guide (opens in a new tab).