Microsoft Azure Synapse SQL Connector > Pushdown optimization > Pushdown optimization using a Microsoft Azure Synapse SQL connection
  

Pushdown optimization using a Microsoft Azure Synapse SQL connection

You can configure pushdown optimization for a mapping that contains a Microsoft Azure Synapse SQL connection. Pushdown optimization enhances the mapping performance.
Use the Pushdown Optimization advanced session property to configure pushdown optimization in a mapping task.
When you run a task that reads from Microsoft Azure Data Lake Storage Gen2 and writes to Microsoft Azure Synapse SQL with pushdown optimization, the task converts the transformation logic to a PolyBase query.
When you can configure pushdown optimization in a mapping to read from and write to Microsoft Azure Synapse SQL using a Microsoft Azure Synapse SQL connection, you can read data from tables, external tables, custom queries, and views.

Configuring optimization for a Microsoft Azure Synapse SQL mapping task

Perform the following steps to configure pushdown optimization for a Microsoft Azure Synapse SQL mapping task:
    1. Create a mapping based on your requirement:
    2. Create a mapping task.
    1. a. Select the configured mapping.
    2. b. In the Pushdown Optimization section on the Schedule tab, set the pushdown optimization value to Full or To Source.
    3. You can use Source pushdown optimization only when you read from a Microsoft Azure Synapse SQL source.
    4. c. Save the task and click Finish.
When you run the mapping task, the transformation logic is pushed to the Microsoft Azure Synapse SQL. To verify that the pushdown optimization has taken place, you can check the session log for the job. In Monitor, view the log for jobs.

Supported functions

When you use pushdown optimization, the Secure Agent converts the expression, filter, or aggregator in the transformation by determining equivalent functions in the database.
The following table summarizes the availability of pushdown functions when you configure pushdown optimization for a mapping that contains a Microsoft Azure Synapse SQL connection.
Columns marked with an X indicate that the function can be pushed to Microsoft Azure Synapse SQL. Columns marked with a dash (-) symbol indicate that the function cannot be pushed to the database.
Function
Pushdown
Function
Pushdown
Function
Pushdown
ABORT()
-
INITCAP()
-
REG_MATCH()
-
ABS()
X
INSTR()
X
REG_REPLACE
-
ADD_TO_DATE()
X
IS_DATE()
X
REPLACECHR()
X
AES_DECRYPT()
-
IS_NUMBER()
X
REPLACESTR()
X
AES_ENCRYPT()
-
IS_SPACES()
-
REVERSE()
-
ASCII()
-
ISNULL()
X
ROUND(DATE)
-
AVG()
X
LAST()
-
ROUND(NUMBER)
X
CEIL()
X
LAST_DAY()
X
RPAD()
-
CHOOSE()
-
LEAST()
-
RTRIM()
X
CHR()
X
LENGTH()
X
SET_DATE_PART()
-
CHRCODE()
-
LN()
X
SIGN()
-
COMPRESS()
-
LOG()
X
SIN()
X
CONCAT()
X
LOOKUP
-
SINH()
-
COS()
X
LOWER()
X
SOUNDEX()
-
COSH()
-
LPAD()
-
SQRT()
X
COUNT()
X
LTRIM()
X
STDDEV()
X
CRC32()
-
MAKE_DATE_TIME()
-
SUBSTR()
X
CUME()
-
MAX()
X
SUM()
X
DATE_COMPARE()
-
MD5()
-
SYSDATE()
X
DATE_DIFF()
X
MEDIAN()
-
SYSTIMESTAMP()
-
DECODE()
-
METAPHONE()
-
TAN()
X
DECODE_BASE64()
-
MIN()
X
TANH()
-
DECOMPRESS()
-
MOD()
-
TO_BIGINT
X
ENCODE_BASE64()
-
MOVINGAVG()
-
TO_CHAR(DATE)
X
EXP()
X
MOVINGSUM()
-
TO_CHAR(NUMBER)
X
FIRST()
-
NPER()
-
TO_DATE()
X
FLOOR()
X
PERCENTILE()
-
TO_DECIMAL()
X
FV()
-
PMT()
-
TO_FLOAT()
X
GET_DATE_PART()
X
POWER()
X
TO_INTEGER()
X
GREATEST()
-
PV()
-
TRUNC(DATE)
-
IIF()
X
RAND()
-
TRUNC(NUMBER)
X
IN()
-
RATE()
-
UPPER()
X
INDEXOF()
-
REG_EXTRACT()
-
VARIANCE()
X
The following table describes the syntax for the pushdown functions:
Function
Syntax
Description
ADD_TO_DATE
ADD_TO_DATE (date, format, amount)
Supported formats:
  • - yyyy
  • - mm
  • - dd
  • - hh
  • - mi
  • - ss
  • - ms
  • - us
  • - ns
DATE_DIFF
DATE_DIFF(date1, date2, format)
Supported formats:
  • - yyyy
  • - mm
  • - dd
  • - hh
  • - mi
  • - ss
  • - ms
  • - us
  • - ns
GET_DATE_PART
GET_DATE_PART(date, format)
Supported formats:
  • - yyyy
  • - mm
  • - dd
  • - hh
  • - mi
  • - ss
  • - ms
  • - us
  • - ns
IIF
IIF (condition, value1 [,value2])
INSTR
INSTR (string, %search_value%)
The search value must be a regular expression enclosed in %.
LAST_DAY
LAST_DAY (date)
LTRIM and RTRIM
LTRIM (string)
RTRIM (string)
You can pass only a single argument in the LTRIM or RTRIM function.
TO_BIGINT
TO_BIGINT (numeric value)
TO_BIGINT truncates the decimal portion. To avoid truncation, use the ROUND function.
TO_BIGINT (ROUND (numeric value [,0]))
You can pass only a single argument in the function.
TO_CHAR (Date)
TO_CHAR (date [,format])
Specify a supported format. The format defines the format of the return value, not the format for the values in the date argument.
Supported formats:
  • - dd/mm/yyyy
  • - dd.mm.yyyy
  • - dd-mm-yyyy
  • - dd mon yyyy
  • - mm/dd/yyyy
  • - hh:mi:ss
  • - yyyy.mm.dd
  • - yyyy/mm/dd
  • - yyyymmdd
  • - yyyy-mm-dd hh:mi:ss
TO_DATE
TO_DATE (string [,format])
The format must match the parts of the string argument.
Supported formats:
  • - dd/mm/yyyy
  • - dd.mm.yyyy
  • - dd-mm-yyyy
  • - dd mon yyyy
  • - mm/dd/yyyy
  • - hh:mi:ss
  • - yyyy.mm.dd
  • - yyyy/mm/dd
  • - yyyymmdd
  • - yyyy-mm-dd hh:mi:ss
SUBSTR
SUBSTR (string, start [,length ])
The start argument must be a positive number.
The length must be an integer greater than 0.

Supported operators

When you use pushdown optimization, the Secure Agent converts the expression in the transformation by determining equivalent operators in the database. If there is no equivalent operator, the Secure Agent processes the transformation logic.
The following table summarizes the availability of pushdown operators when you configure pushdown optimization for a mapping that contains a Microsoft Azure Synapse SQL connection.
Columns marked with an X indicate that the operator can be pushed to Microsoft Azure Synapse SQL.
Operator
Pushdown
+ - * /
X
%
X
||
-
= > < >= <= <>
X
!=
X
^=
-
NOT AND OR
X

Supported transformations

The following table lists the transformation logic that is supported by pushdown optimization.
Columns marked with an X indicate that the transformation can be pushed to Microsoft Azure Synapse SQL. Columns marked with a dash (-) symbol indicate that the transformation cannot be pushed to the database.
Transformation
Pushdown
Aggregator
X
Expression
X
Filter
X
Joiner
X
Lookup
X
Sorter
-
Union
X
Router
-
Sequence Generator
-

Supported features for Microsoft Azure Synapse SQL mappings

When you configure pushdown optimization, the mappings support the following Microsoft Azure Synapse SQL properties in the Source and Target transformations:
Source
When you configure pushdown optimization, the mappings support the following properties for a Microsoft Azure Synapse SQL source:
Target
When you configure pushdown optimization, the mappings support the following properties for a Microsoft Azure Synapse SQL target:
Lookup
When you configure pushdown optimization, the mappings support the following properties for Microsoft Azure Synapse SQL connected and unconnected lookup:
Note: If you configure properties that are not supported, the mapping runs with pushdown optimization but the properties are ignored. If the pushdown query generation fails, the mapping runs without pushdown optimization. Azure Blob Container Name or ADLS FileSystem Name is required when the mappings run in the Informatica runtime environment. Azure Blob Container Name is required when you run a mapping with Source pushdown optimization.

Supported features for Microsoft Azure Data Lake Storage Gen2 source

When you configure pushdown optimization, the mappings support the following properties for a Microsoft Azure Data Lake Storage Gen2 source:
Source
Note: You can also read data from a Microsoft Azure Data Lake Storage Gen2 case-sensitive database.
Lookup
When you configure pushdown optimization, the mappings support the following properties for Microsoft Azure Data Lake Storage Gen2 connected and unconnected lookup:

Configuring a custom query for the Microsoft Azure Synapse SQL source object

You can push down a custom query to Microsoft Azure Synapse SQL.
Before you run a task that contains a custom query as the source object, you can set the Create Temporary View property in the mapping task properties.
Perform the following task to set the property:
  1. 1. In the mapping task, navigate to the Pushdown Optimization section on the Schedule tab.
  2. 2. Set the Create Temporary View option based on your requirement.
  3. 3. Click Finish.

Rules and guidelines for pushdown optimization

Certain rules and guidelines apply for pushdown optimization to a Microsoft Azure Synapse SQL database.
General rules and guidelines
Consider the following rules and guidelines when you enable a mapping for pushdown optimization:
Mapping with Microsoft Azure Synapse SQL source and target
Use the following rules and guidelines when you configure pushdown optimization in a mapping that reads from and writes to Microsoft Azure Synapse SQL:
Mapping with Microsoft Azure Data Lake Storage Gen2 source and write to a Microsoft Azure Synapse SQL target
Use the following rules and guidelines when you configure pushdown optimization in a mapping that reads from a Microsoft Azure Data Lake Storage Gen2 source and write to a Microsoft Azure Synapse SQL target: