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

Open Table lookups in mappings

In a mapping, you can configure a Lookup transformation to represent an Open Table object.
You can retrieve data from an Open Table lookup object based on the specified lookup condition.
The following table describes the Open Table lookup properties that you can configure in a Lookup transformation:
Property
Description
Connection
Name of the lookup connection.
You can select an existing connection, create a new connection, or define parameter values for the lookup connection property.
If you want to overwrite the target connection properties at run time, select the Allow parameter to be overridden at run time option.
Source Type
Type of the Open Table lookup object.
Select one of the following lookup object types:
  • - Single Object. Select to specify a single Open Table object.
  • - Parameter. Select to specify a parameter name. You can configure the lookup object in a mapping task associated with a mapping that uses this Lookup transformation.
Parameter
A parameter file where you define values that you want to update without having to edit the task.
Select an existing parameter for the lookup object or click New Parameter to define a new parameter for the lookup object.
The Parameter property appears only if you select parameter as the source type.
If you want to overwrite the lookup object 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.
Lookup Object
Name of the Open Table lookup object.
Multiple Matches
The behavior when the lookup condition returns multiple matches.
You can select one of the following options:
  • - Return any row
  • - Return all rows
  • - Report error
For more information about configuring lookups, see the Transformations guide in the Data Integration documentation.
The following table describes the Open Table lookup advanced properties that you can configure in a Lookup transformation:
Property
Description
Iceberg Spark 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 Catlog 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 to 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 to 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.
*Applies only to mappings in advanced mode.