Open Table Connector > Mappings and mapping tasks with Open Table Connector > Open Table sources in mappings
  

Open Table sources in mappings

When you configure a mapping to use an Open Table source, you can configure the source properties.
Specify the name and description of the Open Table source. Configure the source and advanced source properties for the Open Table object.
The following table describes the Open Table source properties that you can configure in a Source transformation:
Property
Description
Connection
Name of the source connection.
You can select an existing connection, create a new connection, or define parameter values for the source connection property.
If you want to overwrite the source connection properties at run time, select the Allow parameter to be overridden at run time option.
Object Type
Type of the Open Table source object.
You can choose from the following source types:
  • - Single Object. Select to specify a single Open Table object.
  • - Parameter. Select to specify a parameter name. You can configure the source object in a mapping task associated with a mapping that uses this Source transformation.
  • - Query*. Select to perform a complicated join of multiple tables or to reduce the number of fields that enter the data flow in a very large source.
Parameter
A parameter file where you define values that you want to update without the need to edit the task.
Select an existing parameter for the source object or click New Parameter to define a new parameter for the source object. The Parameter property appears only if you select parameter as the source type.
If you want to overwrite the parameter at run time, select the Allow parameter to be overridden at run time option.
When the task runs, the Secure Agent uses the parameters from the file that you specify in the advanced session properties.
Object
Source object for the mapping.
Query*
This property appears only if you select Query as the object type.
Click Define Query and enter a valid custom query.
You can parameterize a custom query object at run time in a mapping.
*Doesn't apply to mappings in advanced mode.
The following table describes the Open Table query options that you can configure in a Source transformation:
Property
Description
Filter
Filter value in a read operation. Click Configure to add conditions to filter records and reduce the number of rows that the Secure Agent reads from the source.
You can specify the following filter conditions:
  • - Not Parameterized. Use a basic filter to specify the object, field, operator, and value to select specific records.
  • - Completely Parameterized. Use a parameter to represent the field mapping.
  • - Advanced. Use an advanced filter to define a more complex filter condition that uses the Open Table query format.
Sort
Not applicable
The following table describes the Open Table source advanced properties that you can configure in a Source transformation:
Property
Description
Iceberg Advanced Properties
The properties that you want to configure for the Iceberg tables.
Enter the properties in the following format:
<parameter name>=<parameter value>
If you enter more than one property, enter each property in a new line.
In a mapping, when you use AWS Glue Catalog with Amazon S3 and they are in different regions, you must specify the Amazon S3 bucket region property in the following format:
BucketRegion=<bucket-region-name>
In a mapping in advanced mode, when you use AWS Glue Catalog with Amazon S3 and they are in different regions, you must specify the Amazon S3 bucket ARN property in the following format:
s3.access-points.<bucket-name>=<S3-bucket-ARN>
For more information about Amazon S3 bucket ARN, see the Create the ARN for your AWS S3 bucket Knowledge Base article.
When you use Hive metastore with Amazon S3, you must specify the Amazon S3 bucket region property in the following format:
BucketRegion=<Amazon-S3-bucket-region-name>
When you use REST Catalog with Amazon S3, you must specify the Amazon S3 bucket region property in the following format:
restcatalog.iceberg.s3.client.region=<Amazon-S3-bucket-region-name>
Delta Spark Properties*
The properties that you want to configure for the Delta Lake tables.
Enter the properties in the following format:
<parameter name>=<parameter value>
If you enter more than one property, enter each property in a new line.
When you use AWS Glue Catalog with Amazon S3 and they are in different regions, you must specify the Amazon S3 bucket region property in the following format:
BucketRegion=<bucket-region-name>
Pre-SQL
Pre-SQL queries to run before reading data from Apache Iceberg Open Table formats.
You can enter multiple queries separated by a semicolon.
  • - For mappings, ensure that the SQL queries use a valid Athena SQL syntax.
  • - For mappings in advanced mode, ensure that the SQL queries use a valid Spark SQL syntax.
    • You must prefix the table name with the <OpenTableCatalog> string and the database name.
    For example, <OpenTableCatalog>.databasename.tablename
    The database name and table name identifiers in the queries must be in lowercase.
If the pre-SQL query fails, the mapping also fails.
Post-SQL
Post-SQL queries to run after reading data from Apache Iceberg Open Table formats.
You can enter multiple queries separated by a semicolon.
  • - For mappings, ensure that the SQL queries use a valid Athena SQL syntax.
  • - For mappings in advanced mode, ensure that the SQL queries use a valid Spark SQL syntax.
    • You must prefix the table name with the <OpenTableCatalog> string and the database name.
    For example, <OpenTableCatalog>.databasename.tablename
    The database name and table name identifiers in the queries must be in lowercase.
If the post-SQL query fails, the mapping also fails. If the mapping fails, the post-SQL query is not executed.
Time Travel Query
Time travel query to fetch historical data of a table based on a timestamp or snapshot ID you specify.
Enter a value for snapshot ID or timestamp in the following format:
SnapshotVersion=<snapshot value, latest, or latest-N> OR TimestampValue=<YYYY-MM-DD HH:mm:ss.SSS UTC, latest, or latest-N>
For example, you can specify latest as your timestamp value to fetch the recent generated snapshot:
SnapshotVersion=latest
You can partially parameterize the time travel query using an input parameter and resolve the parameter in the parameter file.
For more information about configuring Time Travel Query, see Time Travel Query.
Tracing Level
Sets the amount of detail that appears in the log file.
You can choose terse, normal, verbose initialization, or verbose data. Default is normal.
*Applies only to mappings in advanced mode.

Time Travel Query

You can use the time travel query to roll back historical data of a table.
When you add or delete any data in Apache Iceberg table items, it automatically generates a snapshot and replaces the old data with the snapshot data. You can utilize these snapshots to perform time travel queries and roll back data as it existed at a specific point in time or at a specific snapshot.
When you configure a source transformation in a mapping, you can configure the Time Travel Query in the advanced source properties. The query fetches the data based on a timestamp or snapshot ID that you specify.
You can use one of the following queries to retrieve the data:
Query by Timestamp
You can query an Iceberg table as it existed at a particular timestamp. You can specify an absolute timestamp or a relative timestamp reference, such as latest or latest-N.
For example, use the following queries:
For an absolute timestamp, if you want to time travel to July 10, 1986 at 04:20:09, use the following time travel query:
TimestampValue=1986-07-10 04:20:09.000 UTC
For a relative timestamp, if you want to query based on the second latest recorded timestamp, use the following time travel query:
TimestampValue=latest-1
Query by Snapshot ID
You can query an Iceberg table by specifying a snapshot ID. Each snapshot has a unique identifier. You can specify an absolute snapshot ID or a relative snapshot version reference, such as latest or latest-N.
For example, use the following queries:
For an absolute snapshot ID, if you want to time travel to a snapshot with ID 33444444553321, use the following time travel query:
SnapshotVersion=33444444553321
For a relative snapshot version, if you want to query based on the most recent snapshot, use the following time travel query:
SnapshotVersion=latest
You can also use the advanced filter option to configure a query for Apache Iceberg or Delta Lake tables. For example, use one of the following queries:
Note:
When both the Time Travel Query advanced property and the advanced filter are configured, the Time Travel Query advanced property takes precedence.