Skip to main content
This tutorial walks through the options Kodda supports for loading delimited files from Amazon S3. It covers setting up the S3 connection for ungrouped and grouped files.
  • Ungrouped files (basic) — files that are loaded as they are and do not need to be combined with any other files in the bucket. Each file becomes a table.
  • Grouped files (advanced) — files that share a structure and a naming convention, so that many files are loaded into a single table. This tutorial shows how to group files by entity name and by key path.
All three examples read from the same bucket, kodda-labs-inc, under the key path /FlatFiles/.

​
Creating the S3 Connection (Ungrouped Files)

The simplest case is a key path that holds one file per table. In the bucket, /FlatFiles/basic/ contains plain CSV files:
The FlatFiles/basic/ folder in the AWS S3 console
Open Manage > Connections and click + Add Connection. Create the connection with the following values (see Manage Connections for every parameter):
Expand the S3 section and enter the AWS credentials: the AWS Access Key, AWS Secret Access Key, AWS Region Name and Bucket Name.
Click Create. In Browse Data, select the new connection: Explore Objects lists one table for each file in the key path.
Select a table to preview its contents.
You can now generate a mapping from any of these tables to any target connection, exactly as you would from a database table.

​
Creating the S3 Connection (Grouping Files Based on Entity Name)

Often many files with the same structure need to be loaded into one table — for example a new extract every hour — and you want to read only the files that have not been loaded yet rather than scanning every file every time. In this example /FlatFiles/standard/ holds two kinds of file, named with the convention:
The FlatFiles/standard/ folder in the AWS S3 console
Every file that shares an entity name should be loaded by a single mapping. For this to work all of those files must have an identical structure, so that Kodda needs to parse and read each file as few times as possible. Create a second connection as in the first example, with Directory set to /FlatFiles/standard/:
Then expand Advanced Settings:
  • Turn on Group Files.
  • Set the File Tail Pattern. This is a regular expression that matches the end of the file name. All files whose names are identical up to the match are treated as a single table, and every capture group in the expression becomes a column in that table. For this naming convention use:
    The connection in the screenshot uses doubled backslashes; single backslashes (_(\d{4}-\d{2}-\d{2}_\d{2}:\d{2}:\d{2})) work the same way.
  • Set the File Tail Column Names — one name per capture group. Here the single group holds the file’s timestamp, so name it file_date.
The pattern matches only the end of each file name, just before .csv. Everything before the match (client or customer) becomes the table name, so files must be named with the entity name first and the varying part last. See Grouping Files for more on writing the pattern.
Click Create. Browse Data now shows two tables, client and customer, each made up of every file that shares that prefix:
Select one and scroll to the right: the file_date column, parsed from the file names, has been added to the table.
The same two tables are available in Generate Mapping:
Because file_date is a column, it can be used as the Incremental Column of the mapping. Each run then reads only the files whose timestamp is later than the last one loaded.

​
Entity Name in Key Path (Grouping Files Based on Key Path)

Another common layout stores files under a key path that contains a date hierarchy, which makes it easy to find the files for a given day:
The FlatFiles/date_hier/ key path in the AWS S3 console
Here the entity (table) name is not in the file name at all, and the information needed for an incremental load — the date — is in the key path. A different File Tail Pattern handles this. Create a third connection named S3_Grouped_Hier with Directory set to /FlatFiles/date_hier/:
Under Advanced Settings turn on Group Files and set the File Tail Pattern to:
This pattern has two capture groups: the first is the date portion of the key path (year/month/day/hour) and the second is the GUID file name. Two groups need two File Tail Column Names, entered separated by a comma: file_system_date,guid. Everything before the match — the entity name directory — identifies the table. Click Create. Browse Data again lists two tables:
Select one: the two file-tail columns, file_system_date and guid, have been added to the table.
These columns are available in any mapping built on the connection and, as in the previous example, file_system_date can drive an incremental load.