About Hyper-Q emulation for simple UPDATE statements on Azure Synapse

Datometry Hyper-Q implements the semantics of the source system on the target system. Operations that exist natively on the target system may differ in semantics, requiring emulation to achieve semantically identical behavior.

Teradata UPDATE semantics differ from Azure Synapse UPDATE semantics when multiple source rows update the same target row. On Teradata, this results in an error, failing the UPDATE statement. In Azure Synapse, this UPDATE goes through and non-deterministically updates the target row with any of the source rows.

To provide Teradata semantics on Azure Synapse and error out when multiple source rows update the same target row, Hyper-Q generally emulates UPDATE statements on Azure Synapse. This emulation can be disabled upon request to pass through simple UPDATE statements directly to Azure Synapse, implying Azure Synapse semantics when multiple source rows update the same target row.

To learn more, refer to the example below and to How does Hyper-Q implement complex UPDATE statements on SQL DW?

Example

Consider the following two tables:

CREATE TABLE employees (empid INTEGER, empname VARCHAR(100), empphone VARCHAR(20));
CREATE TABLE empdepts (empid INTEGER, depname VARCHAR(100), empphone VARCHAR(20));

INSERT INTO employees VALUES (1, 'Francine Smith', NULL);

INSERT INTO empdepts VALUES (1, 'Marketing', '555-111-1111');
INSERT INTO empdepts VALUES (1, 'Engineering', '555-333-3333');

When updating the employee's phone number in the table employees from the table empdepts by joining on empid, two source rows from empdepts are candidates to update the same target row in employees.

On Teradata, this operation fails:

UPDATE E
FROM employees E, empdepts D
SET empphone = D.empphone
WHERE E.empid = D.empid;

[Teradata Database] [TeraJDBC 15.10.00.37] [Error 7547] [SQLState HY000] Target row updated by multiple source rows. [SQL State=HY000, DB Errorcode=7547]

On Azure Synapse, the operation succeeds:

UPDATE employees
SET empphone = D.empphone
FROM empdepts D
WHERE employees.empid = D.empid;

UPDATE employees successful
1 row affected

Enabling Native Synapse UPDATE Semantics

To improve performance it may be desirable to skip Hyper-Q emulation steps and instead execute UPDATE statements with native Azure Synapse semantics. If the workload does not exhibit UPDATEs with multiple source rows updating the same target row, and/or does not rely on the detection of such a condition, this change can be made without affecting the result of the UPDATE statements.

Native Azure Synapse update semantics can be enabled by adding the option update_duplicate_check=none to the [gateway] section if the Hyper-Q configuration file dtm.ini.

To learn more, see Gateway Configuration Parameters in the Hyper-Q for Azure Synapse documentaTION.