Microsoft Azure Synapse SQL Connector > Pushdown optimization > Pushdown using an ODBC connection
  

Pushdown using an ODBC connection

Use an ODBC connection to enable full or source pushdown optimization when you want to read data from a Microsoft Azure Synapse SQL source, write to a Microsoft Azure Synapse SQL target, and if you want to run the mapping logic entirely within Microsoft Azure Synapse SQL.
After you create an ODBC connection, select the value of the Pushdown Optimization property as Full or To Source in the advanced session properties. You can check the session log to verify that the pushdown optimization has taken place.
When you run a task configured for pushdown optimization, the task converts the transformation logic to an SQL query.
Note: You cannot configure a Microsoft Azure Synapse SQL ODBC connection on SUSE Linux Enterprise version 12.0.

Install the ODBC driver

Before you establish an ODBC connection to connect to Microsoft Azure Synapse SQL, you must install the Microsoft ODBC Driver 13 for SQL Server for Windows. Install the Microsoft ODBC Driver 13 for SQL Server on the machine where the Secure Agent is installed.
Download the Microsoft ODBC driver from the following link:
https://www.microsoft.com/en-us/download/details.aspx?id=53339

Create a system DSN

Before creating an ODBC connection, create a system DSN.
Perform the following steps to create a system DSN on the machine where the Secure Agent is installed:
    1. Double-click the odbcad32.exe file under C:\WINDOWS\System32.
    The ODBC Data Sources Administrator box appears.
    2. Click System DSN.
    The System DSN tab appears.
    3. Click Add.
    The Create New Data Source box appears.
    4. Select ODBC Driver 13 for SQL Server and click Finish.
    The Create a New Data Source to SQL Server box appears.
    5. Specify the name, description, and the server you want to connect to.
    6. Click Next and provide the user name and password for SQL Server Authentication.
    7. Click Next and change the default database setting to the database you want to connect to.
    8. Click Next.
    9. Click Finish and then, click Test Data Source. Ensure that the test connection completes successfully.
    10. Click OK.

Create an ODBC connection

Create ODBC connections to access Microsoft Azure Synapse SQL source and target objects.
Perform the following steps to create an ODBC connection on the Connections page:
    1. On the Connections page, click New Connection. The New Connection page appears.
    2. Configure the following connection details in the Connection Details section:
    Property
    Description
    Connection Name
    Name of the ODBC connection.
    Description
    Description of the connection.
    Type
    Type of the connection. Select the type of the connection as ODBC.
    3. Configure the following connection details in the Connection Properties section:
    Property
    Description
    Runtime Environment
    The name of the runtime environment where you want to run the tasks.
    User Name
    User name of the Microsoft Azure Synapse SQL account.
    Password
    Password for the Microsoft Azure Synapse SQL account.
    Data Source Name
    Enter the name of the ODBC data source name that you created for the Microsoft Azure Synapse SQL database.
    Schema
    Microsoft Azure Synapse SQL schema name.
    Code Page
    Select the code page that the Secure Agent must use to read or write data.
    ODBC Subtype
    Enter the value of the ODBC Subtype field as Azure DW.
    4. Click OK.
    The Microsoft Azure Synapse SQL ODBC connection is created successfully.

Cross-schema pushdown optimization

You can use cross-schema pushdown optimization for a mapping to read or write data based on different schemas within the same database.
To use cross-schema pushdown optimization, you must create two Microsoft Azure Synapse SQL ODBC connections and specify different schemas for each of the connections. Select the Enable cross-schema pushdown optimization check box in the advanced session properties of the mapping. By default, the check box is selected.
Note: Ensure that the schemas you specify in both of the Microsoft Azure Synapse SQL ODBC connections belong to the same database.
Consider the following steps to use cross-schema pushdown optimization:
  1. 1. Create an Azure_DW1 ODBC connection and specify DW_schema1 schema in the connection properties.
  2. 2. Create another Azure_DW2 ODBC connection and specify DW_schema2 schema in the connection properties.
  3. 3. Create a mapping and perform the following tasks:
    1. a. Select the Azure_DW1 connection as source to read data from the source object using the DW_schema1 schema.
    2. b. Select the Azure_DW2 connection as target to write data to the target object using the DW_schema2 schema.
    3. c. Select the Enable cross-schema pushdown optimization check box in the advanced session properties.
    4. The following image shows the Enable cross-schema pushdown optimization property:
    5. d. Run the mapping.
When you run the mapping, the Secure Agent reads data from the source object using DW_schema1 and writes data to the target object using DW_schema2.

Pushdown optimization functions

Microsoft Azure Synapse SQL supports full pushdown and source pushdown optimization.
The following table summarizes the availability of pushdown functions in a Microsoft Azure Synapse SQL database. Columns marked with an X indicate that the function can be pushed to the Microsoft Azure Synapse SQL by using full pushdown and source pushdown optimization. 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()
-
REPLACECHR()
X
AES_DECRYPT()
-
IS_NUMBER()
-
REPLACESTR()
X
AES_ENCRYPT()
-
IS_SPACES()
-
REVERSE()
-
ASCII()
X
ISNULL()
X
ROUND(DATE)
-
AVG()
X
LAST()
X
ROUND(NUMBER)
X
CEIL()
X
LAST_DAY()
-
RPAD()
-
CHOOSE()
-
LEAST()
-
RTRIM()
X
CHR()
X
LENGTH()
X
SET_DATE_PART()
X
CHRCODE()
-
LN()
-
SIGN()
X
COMPRESS()
-
LOG()
X
SIN()
X
CONCAT()
X
LOOKUP
-
SINH()
X
COS()
X
LOWER()
X
SOUNDEX()
X
COSH()
-
LPAD()
-
SQRT()
X
COUNT()
X
LTRIM()
X
STDDEV()
X
CRC32()
-
MAKE_DATE_TIME()
X
SUBSTR()
X
CUME()
-
MAX()
X
SUM()
X
DATE_COMPARE()
-
MD5()
X
SYSDATE()
X
DATE_DIFF()
X
MEDIAN()
-
SYSTIMESTAMP()
X
DECODE()
X
METAPHONE()
-
TAN()
X
DECODE_BASE64()
-
MIN()
X
TANH()
X
DECOMPRESS()
-
MOD()
X
TO_BIGINT
X
ENCODE_BASE64()
-
MOVINGAVG()
-
TO_CHAR(DATE)
X
EXP()
X
MOVINGSUM()
-
TO_CHAR(NUMBER)
X
FIRST()
X
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 lists the pushdown operators that can be used in a Microsoft Azure Synapse SQL database. Columns marked with an X indicate that the operator can be pushed to Microsoft Azure Synapse SQL by using full pushdown and source pushdown optimization. Columns marked with a dash (-) symbol indicate that the operator cannot be pushed to the database.
Operator
Pushdown
+ - * /
X
%
X
||
-
= > < >= <= <>
X
!=
X
^=
X
NOT AND OR
X
The following table summarizes the availability of variables in Microsoft Azure Synapse SQL. Columns marked with an X indicate that the variable can be pushed to Microsoft Azure Synapse SQL by using full pushdown and source pushdown optimization. Columns marked with a dash (-) symbol indicate that the variable cannot be pushed to the Microsoft Azure Synapse SQL.
Variable
Pushdown
SESSSTARTTIME
-
SYSDATE
X
WORKFLOWSTARTTIME
-
Microsoft Azure Synapse SQL supports the following transformations for full pushdown and source pushdown:

Rules and guidelines for functions in pushdown optimization

Use the following rules and guidelines when pushing functions to a Microsoft Azure Synapse SQL database: