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.
5,373 questions
Data type conversion from Oracle 19c to Synapse Analytics SQL pool
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(115.19999999999999, 41%, 21%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
pmscorca
1,052
Reputation points
Hi,
in order to get data from an Oracle 19c database on-premise, I'd like to know if it exists any data type conversion/mapping from a such data source to a dedicated SQL pool of Synapse Analytics.
Thanks
Azure Synapse Analytics
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(12.8, 48%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EH%3C/text%3E%3C/svg%3E)
Harishga 6,000 Reputation points Microsoft External Staff2024-01-04T04:51:45.7566667+00:00 Hi pmscorca
Welcome to Microsoft Q&A platform and thanks for posting your question here.
Yes, there exists data type conversion/mapping from an Oracle 19c database on-premises to a dedicated SQL pool of Synapse Analytics. Azure Data Factory provides built-in connectors for Oracle and Synapse Analytics SQL pool, which can be used to move data between the two sources. During the copy operation, Azure Data Factory automatically maps the data types between the source and destination. You can also customize the data type mapping by using the Mapping Data Flows feature in Azure Data Factory.Reference link:
https://learn.microsoft.com/en-us/azure/data-factory/connector-oracle?tabs=data-factoryI hope this information helps you. Let me know if you have any further questions or concerns.
-
pmscorca 1,052 Reputation points
2024-01-04T11:13:27.1466667+00:00 Hi @Harishga , I'd like to understand better. I need to import some Oracle tables in order to ingest some corresponding SQL table in the SQL pool. There are differencies between Oracle data types and SQL data types, f.e. an Oracle NUMBER(5) matchs a SQL SMALLINT. Moreover, the SQL tables have been built based on the Oracle tables.
In a such scenario, how could it be possible mapping different data types dinamically?
-
pmscorca 1,052 Reputation points
2024-01-04T11:22:25.1266667+00:00 Hi @Harishga , thanks for your reply. I'd like to understand better. There are differencies between Oracle data types and SQL data types of a SQL pool, f.e. an Oracle NUMBER(5) corresponds to a SQL SMALLINT. I need to read some existing Oracle tables to ingest some existing SQL tables. The SQL tables are based on Oracle tables.
In a such scenario, is it possible to create a pipeline/mapping data flow dinamically, in order to mapping different data types? Or do I need to import the schema of the single Oracle table, then to map exactly each Oracle column with the corresponding SQL column, losing the schema drift?
-
Harishga 6,000 Reputation points Microsoft External Staff
2024-01-05T06:52:49.9233333+00:00 Hi @pmscorca
In such a scenario, you can create a pipeline/mapping data flow dynamically to map different data types. You can use the Data Flow feature in Azure Data Factory to map the data types dynamically. You can also use the Copy Data feature to copy data from Oracle to SQL Server.If you import the schema of the single Oracle table and map each Oracle column with the corresponding SQL column, you may lose the schema drift. However, if you want to maintain the schema drift, you can use the Mapping Data Flows feature in Azure Data Factory. This feature allows you to transform data at scale by building data transformation logic using a visual interface.
-
pmscorca 1,052 Reputation points
2024-01-05T09:24:04.7566667+00:00 Ok, but how using mapping data flows to mapping different data types dinamically, using the same data flow for all Oracle tables to import?
-
Harishga 6,000 Reputation points Microsoft External Staff
2024-01-05T10:26:40.55+00:00 Hi @pmscorca
Mapping data flows can be used to map different data types dynamically, using the same data flow for all Oracle tables to import. You can use map functions and column patterns to perform operations on map data types and match columns based on patterns. This allows for more efficient and consistent data mapping when copying data to and from Oracle.Reference link:
https://learn.microsoft.com/en-us/azure/data-factory/concepts-data-flow-column-pattern
https://learn.microsoft.com/en-us/azure/data-factory/data-flow-map-functionsI hope this information helps you. Let me know if you have any further questions or concerns.
-
Harishga 6,000 Reputation points Microsoft External Staff
2024-01-08T02:49:08.44+00:00 Hi @pmscorca
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help. -
Harishga 6,000 Reputation points Microsoft External Staff
2024-01-09T02:12:13.9833333+00:00 Hi @pmscorca
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sign in to comment
