Share via

Unable to load timestamp (Hexadecimal) from Azure SQL to Azure Synapse

BIFlake 1 Reputation point
2020-07-24T17:15:58.913+00:00

Hi,

I have been trying to load table from Azure SQL to Azure synapse (formerly Azure DW) using Azure Data Factory. However, it is failing with a timestamp column, which has hexadecimal values. Timestamp is not supported in Azure Synapse and it mentioned to use datetime2 as supported data type but the values of existing timestamp is not date values as they are hexadecimal values.

How do we convert or handle this timestamp (Hexadecimal) values in Azure Synapse?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,504 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,096 Reputation points
    2020-07-25T07:24:13.667+00:00

    Good day,

    Something in your description make no sense. How can you convert the existing timestamp into datetime2, when these are totally different data types?!?

    timestamp or in the newer name rowversion, is a unique binary incrementing number and it does not preserve any information about a date or a time.

    You should not use datetime2 INSTEAD of rowversion in your new table. First of all, this means that your will probably need to change all your applications which use the database and adjust them to use datetime2. Moreover, The problem is that datetime2 does not guarantee unique value and you might have duplicate values. This is not a good solution for the same uses!

    If you need to preserve the old values from a timestamp typed column, the you should create a column type BINARY(8) (for NOT NULL column) or or VARBINARY(8) (for NULL column).

    From the Doc: A nonnullable rowversion column is semantically equivalent to a binary(8) column. A nullable rowversion column is semantically equivalent to a varbinary(8) column.

    Note! It is NOT trivial to switch between Azure Database (or on-premises) to the Azure Data Warehouse! These are different product behind the scenes. The Azure Database is based on the SQL Server while the Azure Data warehouse is based on the Parallel Data Warehouse. These two product does not use exactly the same T-SQL command, and by moving to the Azure DW you will probably need to adjust your application or at least test everything VERY carefully!

    You should remember that Azure DW is not necessarily a single machine and it can be using multiple physical machines behind the scenes. There are things that simply does not work the same, and rowversion is one of these. Maybe in the future Microsoft will implement something like rowversion but even if they will, then it will not be using the same exact mechanism behind the scenes. This is basically the same with IDENTITY for example, but in this case Microsoft developed a mechanism to support it as you can read in this very interesting document.