FME Transformers: 2025.2 BETA

Categories
Web

Web

Related Transformers
ComprehendConnector
GoogleBigQueryConnector
RekognitionConnector
S3Connector
SQSConnector
Categories
Web

Web

Related Transformers
ComprehendConnector
GoogleBigQueryConnector
RekognitionConnector
S3Connector
SQSConnector

AmazonAthenaConnector

Connects to Amazon Athena to query Amazon S3 datasets.

Jump to Configuration

Typical Uses

  • Running SQL queries against datasets stored on Amazon S3

How does it work?

The AmazonAthenaConnector uses your AWS account credentials to perform SQL queries against Amazon S3 datasets.

Queries may be saved and re-used.

Query results are both output from the transformer and saved on S3 as a CSV file with accompanying metadata, and may be optionally saved as a table. Note that results are not cleaned up by the transformer, and must be deleted manually when desired.

Athena is very efficient at querying large datasets to retrieve subsets, but is not recommended for retrieving whole datasets.

Note that AWS charges for the following:

  • The amount of data scanned. All data is scanned for every query, unless the underlying data supports columnar storage.
  • The storage of the source data in S3.
  • The storage of the results of each query in S3.

See Amazon Athena pricing.

Configuration

Input Ports

This transformer accepts any feature.

Output Ports

For SELECT queries, one feature per returned row is output here, including any attributes specified in the Exposed Attributes parameter, corresponding to the columns included in the query.

For other types of query where no rows are returned, one feature is output with _bucket and _path attributes added.

Input features are output here unmodified, in addition to any other output locations (Output or <Rejected>).

Features that cause the operation to fail are output through this port. An fme_rejection_code attribute describing the category of the error will be added, along with a more descriptive fme_rejection_message which contains more specific details as to the reason for the failure.

If an Input feature already has a value for fme_rejection_code, this value will be removed.

Rejected Feature Handling: can be set to either terminate the translation or continue running when it encounters a rejected feature. This setting is available both as a default FME option and as a workspace parameter.

Parameters

Credential Source

Select the type of credentials to use:

  • Web Connection (Recommended): Use an Amazon Web Services web connection.
  • Embedded: Embed an access key ID and secret access key as parameters in the transformer.
  • System: Use credentials already configured on the system in the environment variables AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY. For more information about configuring AWS credentials system-wide, see Getting started with the AWS CLI .

Account

When Credential Source is Web Connection, select or create a Web Connection connecting to an Amazon Web Services Web Service.

See Using Web Connections.

Region

(Optional) Select the AWS Region in which the S3 dataset resides. This setting will override the region set in the web connection.

AWS CA Bundle

(Optional) Provide an AWS CA Bundle specifying the path to a certificate bundle to use for certificate validation.

If set, this has precedence over the value of the AWS_CA_BUNDLE environment variable.

Embedded Credentials

When Credential Source is Embedded:

Access Key ID

Provide S3 account credentials.

Secret Access Key

Provide S3 account credentials.

Use Named Query

Select a query provision method:

  • Yes: Use a previously-saved query.
  • No: Enter a new query, which may be optionally saved for reuse.

Named Query

When Use Named Query is Yes, select a previously-saved query to run.

Query

When Use Named Query is No, enter a complete SQL query to run.

Query Timeout

(Optional) Specify the number of seconds to wait for a query to successfully complete.

If incomplete, the query will be canceled. The default and maximum is 1800 seconds (30 minutes).

Result Has Column Names

Select an option for the first row:

  • Yes (default): The first row of the query result is not returned.
  • No: The first row of the query result is output.

Athena generates both a CSV file of the results and an accompanying metadata file.

Bucket

Specify the S3 bucket in which to save the query results.

Path

Specify the full path within the bucket to save the query results.

It is not recommended to save results in the same location as the source data.

Query results are not deleted by the transformer - they may be deleted manually or saved to a location with default Object Expiration.

When enabled, the SQL query will be saved to a database and may be reused.

Database

Specify the database in which to store the query.

Title

Provide a name for the saved query.

Description

Provide a description of the saved query.

When enabled, the query results will also be saved to a new database table.

Database

Specify the database in which to store the table.

Table Name

Provide a table name.

Description

(Optional) Provide a description.

Output Bucket

Specify the S3 bucket in which to save the table.

Output Path

Specify the full path within the bucket to save the table.

This location must be empty.

Format

Select a format for the table data:

  • Parquet
  • ORC
  • Avro
  • CSV
  • JSON
  • TSV

When running a SELECT query, the column names are not automatically exposed as attributes on the output features.

Add the column names to this table and they will be exposed in table order.

Editing Transformer Parameters

Transformer parameters can be set by directly entering values, using expressions, or referencing other elements in the workspace such as attribute values or user parameters. Various editors and context menus are available to assist. To see what is available, click beside the applicable parameter.

Defining Values

There are several ways to define a value for use in a Transformer. The simplest is to simply type in a value or string, which can include functions of various types such as attribute references, math and string functions, and workspace parameters.

Using the Text Editor

The Text Editor provides a convenient way to construct text strings (including regular expressions) from various data sources, such as attributes, parameters, and constants, where the result is used directly inside a parameter.

Text Editor

Using the Arithmetic Editor

The Arithmetic Editor provides a convenient way to construct math expressions from various data sources, such as attributes, parameters, and feature functions, where the result is used directly inside a parameter.

Arithmetic Editor

Conditional Values

Set values depending on one or more test conditions that either pass or fail.

Parameter Condition Definition Dialog

Content

Expressions and strings can include a number of functions, characters, parameters, and more.

When setting values - whether entered directly in a parameter or constructed using one of the editors - strings and expressions containing String, Math, Date/Time or FME Feature Functions will have those functions evaluated. Therefore, the names of these functions (in the form @<function_name>) should not be used as literal string values.

String Functions

These functions manipulate and format strings.

Special Characters

 A set of control characters is available in the Text Editor.

Math Functions

Math functions are available in both editors.
Date/Time Functions Date and time functions are available in the Text Editor.

Math Operators

These operators are available in the Arithmetic Editor.

FME Feature Functions

These return primarily feature-specific values.

FME Parameters

FME and workspace-specific parameters may be used.
Creating and Modifying User Parameters Create your own editable parameters.

Table Tools

Transformers with table-style parameters have additional tools for populating and manipulating values.

Row Reordering

Enabled once you have clicked on a row item. Choices include:

  • Add a row
  • Remove a row
  • Move current row up one
  • Move current row down one
  • Move current row to top
  • Move current row to bottom

Cut, Copy, and Paste

Enabled once you have clicked on a row item. Choices include:

  • Cut a row - delete and copy to clipboard
  • Copy a row to the clipboard
  • Paste a row from the clipboard

Cut, copy, and paste may be used within a transformer, or between transformers.

Filter

Start typing a string, and the matrix will only display rows matching those characters. Searches all columns. This only affects the display of attributes within the transformer - it does not alter which attributes are output.

Import

Import populates the table with a set of new attributes read from a dataset. Specific application varies between transformers.

Reset/Refresh

Generally resets the table to its initial state, and may provide additional options to remove invalid entries. Behavior varies between transformers.

Note: Not all tools are available in all transformers.

For more information, see Transformer Parameter Menu Options.

Reference

Processing Behavior

Feature-Based

Feature Holding

No

Dependencies

Amazon Web Services account

Aliases

 

History

Released FME 2019.2

FME Online Resources

The FME Community and Support Center Knowledge Base have a wealth of information, including active forums with 35,000+ members and thousands of articles.

Search for all results about the AmazonAthenaConnector on the FME Community.

 

Examples may contain information licensed under the Open Government Licence – Vancouver, Open Government Licence - British Columbia, and/or Open Government Licence – Canada.