Suggest an Idea

[junior_favaro]

Currently, Azure Synapse target endpoint does not allow flexibility over changing the table data type. When replicating data from Oracle number column (no precision and scale) to the target, by default it casts the data into an external table/temp table before loading it into a target decimal column. 

When the target decimal column is changed to BIGINT/SMALLINT/INT, Replicate cannot load the data because it fails to cast the external table accordingly.

A customer is looking to see whether the external table creation behaviour (for polybase) to utilise the default data types rather than target table data types. 

e.g. Oracle (Number) -> Replicate (External Table use Decimal(38,10) as per Replicate settings) -> Target (INT/BIGINT on INSERT)

1. Replicate is set to expose NUMBER as DECIMAL(38,10)…
2. External table created with DECIMAL(38,10) as per initial creation/load instead of customised target table data types
3. Insert into target table (any error to insert into target table could be caught – there is no issue inserting decimal into an int column)

[MarinaEmbon]

Thank you for this question.

I want to make sure I fully understand the scenario - 

1. you have a Number column in Oracle (no precision/scale is defined).

2. you know that all the data in this column is integer (do you know what's the maximum value?)

2. you use the default configuration for "expose number as" - Numeric(38,10). please note that if the data has no scale you can use Numeric (38,0).

3. The column is created in Synapse as Decimal (38,10), would you like to have it as int?

4. altering the table in the target to be int/small int/bigint didn't help.

Did I capture it right?

Did you try to use Replicate transformation capabilities?

if not, please try to change the numeric type in the table settings to be int2/4/8 according to the data range.

please let me know if it works for you or if I can assist with anything else.

Marina.

[junior_favaro]

The data as shown by the customer so far only contains integer.

The target data type default is set by Replicate to Decimal, and customer requires it to be smallint, int, and bigint (for different tables specifically).

Altering the target table's data type to smallint/int/bigint did not help because the external table created by Replicate does not suit the data type, giving out this example error:

Column ordinal: 1, Expected data type: BIGINT, Offending value: 124515639.0000000000  (Column Conversion Error), Error: Error converting data type NVARCHAR to BIGINT., Reject file location: /attunity/rejected/_rejectedrows/20201022_054958/QID2595791_1.txt

Customer do not want transformations to be used, mainly because there are a ton of tables that this needs to be done to on the Replicate side, meaning it is difficult to monitor. They seem happy to adjust the target table by themselves, but Replicate external table process does not allow them to do that either.

[MarinaEmbon]

thank you for your answer.

I was able to reproduce the issue and fix it by changing the "expose Number as"  parameter of the Oracle source to be NUMERIC(38,0).

Could you please try to change it and let me know if it works?

Marina.

[junior_favaro]

Hi Marina,

Thanks for the response. It is not fixable that way. Doing it means customer lose the decimal values and therefore risk losing data. For that reason it is not acceptable by any organisation requiring accurate replication, especially given this customer is coming from the government health sector.

External tables created by QR are using the data types from the target tables. If an option is provided to just change the external table data type to use the same data type as the 'expose number as' setting for these number fields, it can solve the issue.

[Shelley_Brennan]

Here are the recommended options to replicate different table with different target table field data type for source’s NUMBER type:

1. Define multi-endpoint/tasks to expose number type as different target type e.g. INT, BIGINT, DECIMAL (38,10).
2.  Using one endpoint, and expose NUMBER type as VARCHAR on Oracle EDP, and create target table manually.