Datafactory : Oracle datatime mapping to parquet

Question

Datafactory : Oracle datatime mapping to parquet

asked

sakuraime 2,271

I have a oracle table field with Date , and while I copy the table to parquet , and import the schema , it help me to convert to int_96 .... why is that? parquet should have date type for data type .

my oracle date time field is

and I would like to dynamically explicitly specify the sink data type mapping (not just the column name at sink ) . how can I do that?

ShaikMaheer-MSFT 28,096 Reputation points Microsoft Employee

2021-11-09T11:12:39.45+00:00

Hi @sakuraime ,

Just checking in to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.
ShaikMaheer-MSFT 28,096 Reputation points Microsoft Employee

2021-11-11T12:10:31.287+00:00

Hi @sakuraime ,

Following to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.

ShaikMaheer-MSFT 28,096 Reputation points Microsoft Employee

2021-11-09T11:12:39.45+00:00

Hi @sakuraime ,

Just checking in to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.
ShaikMaheer-MSFT 28,096 Reputation points Microsoft Employee

2021-11-11T12:10:31.287+00:00

Hi @sakuraime ,

Following to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.

Answer 1

answered

ShaikMaheer-MSFT 28,096 Microsoft Employee

Hi @sakuraime ,

Thank you for posting your Query.

It seems, when we use Copy activity, we cannot control the data types of Sink. Its going to be automatically mapped by system.

If we would like to have control on datatype of Sink too, then we can go with data flows.

We can make use of derived column transformation in data flows, to convert datatype of any input column.
For example, lets say we have ID column in source with type string and we want convert its type to int, then we can use expression toInteger(ID) inside derived column transformation

ShaikMaheer-MSFT 28,096 Reputation points Microsoft Employee

2021-11-03T06:36:47.193+00:00

Hi @sakuraime ,

Your ask: so data flow can use dynamic mapping for datatype ? and derived columns seems to add a new column, which is not quite optimal

Data flow derived column will allow adding new column or changing data type of column or transforming data in an column. Please click here for more details.

Below screenshot tries to convert data type of column:

Hope this will help. Please consider accepting answer. Accepted answers helps community as well.
sakuraime 2,271 Reputation points

2021-11-03T08:13:01.853+00:00

good .. but it doesn't support 'dynamic' content inside derive column expression ? so all the convertion function toString, toInt... need to key in manually .
ShaikMaheer-MSFT 28,096 Reputation points Microsoft Employee

2021-11-03T12:11:28.027+00:00

Hi @sakuraime ,

You can either key in your expressions manually or you can pass your expressions dynamically also from external source. Below video explains passing expressions dynamically in to data flow transformations.
https://www.youtube.com/watch?v=Dssph_O5cVQ
sakuraime 2,271 Reputation points

2021-11-14T14:52:05.433+00:00

in COPY activity can type the translator in the mapping tape .

name
type
phyiscaltype (not sure the translator still recognize this key value pair ) .

but where can I find all the possible data type value of "tyoe" , I try to type 'UTF8' on sink , but I can't allow me to do that .

like Double -> String is OK .

Are their full mapping of all Oracle datatype to Parquet ?
ShaikMaheer-MSFT 28,096 Reputation points Microsoft Employee

2021-11-22T04:24:40.133+00:00

@sakuraime ,

Data type mapping for Oracle documented at below link. Thank you.
https://learn.microsoft.com/en-us/azure/data-factory/connector-oracle?tabs=data-factory#data-type-mapping-for-oracle

Datafactory : Oracle datatime mapping to parquet

1 answer

Activity