You work for a retail company that offers more than 50,000 products and the stores are distributed across the globe. The company ingests a large amount of customer engagement details from the transactional CRM system into Amazon S3.
The sales team wants to improve customer engagement and satisfaction at every touch point. To create a seamless customer experience and deliver personalized service across the various outlets, the retail company plans to load the data that is stored in the Amazon S3 bucket to Databricks.
You can create a mapping that runs on an advanced cluster to achieve faster performance when you read data from the Amazon S3 bucket and write data to the Databricks target.
You can choose to add transformations to process the raw data that you read from the Amazon S3 bucket and then write the curated data to Databricks.
The following example illustrates how to create a mapping in advanced mode to read from an Amazon S3 source and write to Databricks target:
1In Data Integration, click New > Mappings > Mapping.
2In the Mapping Designer, click Switch to Advanced.
The Mapping Designer updates the mapping canvas to display the transformations and functions that are available in advanced mode.
3 Enter a name, location, and description for the mapping.
4Add a Source transformation, and specify a name and description in the general properties.
5On the Source tab, perform the following steps to read data from the Amazon S3 source:
aIn the Connection field, select the Amazon S3 V2 connection.
bIn the Source Type field, select single object as the source type.
cIn the Object field, select the parquet file object that contains the customer details.
dIn the Advanced Properties section, specify the required parameters.
6On the Expression tab, define an expression to change the file name port of the customer parquet file to uppercase based on your business requirement before you write data to the Databricks target.
7Add a Target transformation, and specify a name and description in the general properties.
8On the Target tab, specify the details to write data to Databricks:
aIn the Connection field, select the Databricks target connection.
bIn the Target Type field, select single object.
cIn the Object field, select the Databricks object to which you want to write the curated customer engagement data.
dIn the Operation field, select the insert operation.
eIn the Advanced Properties section, specify the required advanced target properties.
9Click Save > Run to validate the mapping.
In Monitor, you can monitor the status of the logs after you run the task.
Rules and guidelines for mappings in advanced mode
Consider the following rules and guidelines for Databricks objects used as sources and targets in mappings in advanced mode:
•When you write data to multiple Databricks targets with the same table and configure different target operations for each target, the mapping throws a concurrent append exception.
•When mappings in advanced mode reads NULL values from the source and updates or upserts a column with NOT NULL constraint in the Databricks target table, the mapping fails and the Secure Agent fails to log an appropriate error message.
•When you read data from or write data to Databricks and the source or target object contains 5000 or more columns, the mapping fails.
•When you do a data type conversion from Float to Double or use create target at run time, data loss is encountered.
•If a mapping in advanced mode has a source column data type as String containing true or false value and a target data type as Boolean, the Secure Agent writes data as null to the target.
•Use the following formats when you import a Databricks source object containing Boolean, Date, or Timestamp data types with a simple source filter conditions:
- Boolean = 0 or 1
- Date = YYYY-MM-DD HH24:MM:SS.US
- Timestamp = YYYY-MM-DD HH24:MM:SS.US
•You cannot use the following features:
- View
- Multipipe
•After you create and run a mapping task, it is recommended to shut down the job cluster. If you modify a mapping task or edit the connection linked to a mapping task, metadata is fetched again and the job cluster restarts.
• When you do a data type conversion from Date or Timestamp to String, the Secure Agent writes the value only in the following default format for both Date and Timestamp:
MM/DD/YYYY HH24:MI:SS
•When you do a data type conversion from String to Date or Timestamp, the String value must be in the following format:
MM/DD/YYYY HH24:MI:SS
To use any other format, you must specify the format in the advanced session property of a mapping task for successful conversion. Null is populated in the target for the unmatched format.
•When you do a data type conversion from Bigint to Double, the target data is written in the exponential format.
•When you perform an update, upsert, or a data driven operation with an IIF condition that includes DD_DELETE or DD_UPDATE, ensure that the update column that you specified does not have duplicate rows. Otherwise, the mapping fails with the following error:
java.lang.UnsupportedOperationException: Cannot perform MERGE as multiple source rows matched and attempted to update the same target row in the Delta table.
•When you perform an insert, update, upsert operation, or DD_UPDATE and the range of the data in source column is greater than the range of the target column, the mapping does not fail and leads to data truncation.
•When you specify a single constant in the data driven condition, the mapping ignores the data driven condition and the Secure Agent performs insert, update, or delete operation based on the constant.
For example, if you specify the data driven condition as DD_INSERT, the mapping does not consider the update columns and depends on the Write Disposition property.
•When you specify a single constant with the IIF condition in the data driven condition such as IIF(COL_INT > 20 , DD_UPDATE), the Secure Agent inserts the data into the target even for those rows that do not satisfy the condition.
•When you specify the DD_REJECT constant in the data driven condition, the Secure Agent does not log the rejected rows in the error file or the session log.
•You can run mappings with hierarchical data types only on a Linux system.
•When you configure mappings with nested statements that contain hierarchical data types, the mapping fails if the nested field names contain the following special characters:
- ,
- ( )
- < >
•When you read and write data of hierarchical data type, ensure that the column names do not start with a number.
•When you run a Databricks mapping in advanced mode to write data to multiple Databricks targets and enable SQL ELT optimization, the mapping runs successfully but the data is written to only one target.
•When you read data of hierarchical data type, ensure that the column names do not contain special characters.
•When you write data to multiple Databricks targets and view the job results, the Individual Task Results section doesn't display results for each target separately. Instead, it displays a single consolidated result for all targets. However, the data is correctly written to all targets.