Using Self hosted IR, In ADF Pipeline Can we change the column names of all tables in DB having special characters with underscore without altering the TABLE within pipeline ?

Question

Using Self hosted IR, In ADF Pipeline Can we change the column names of all tables in DB having special characters with underscore without altering the TABLE within pipeline ?

(Manasa) 0

Using Self hosted IR, In ADF Pipeline for on-premises data copy of sql server tables to Parquet format .Can we change the column names of all tables in DB having special characters with underscore Dynamically without altering the TABLE within pipeline. I hope we cannot use data flow in self hosted and please tell us other approach that we can use within ADF to handle this scenario.

PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2023-08-01T08:04:25.8766667+00:00

@(Manasa) - We received your feedback that the answer provided on the thread was not helpful.

Kindly let us know what we could have done better to improve the answer and make your engagement experience good. We are here to help you and strive to make your experience better and greatly value your feedback.

Our engineer has provided a detailed answer which has clear steps for which you are looking for. If you wish, you may re-surveying/rating for the engagement you received on the thread. Your feedback is very important to us.

Looking forward to you reply. Much appreciate your feedback!

Regards,

PRADEEPCHEEKATLA-MSFT

1 answer

Your answer

PRADEEPCHEEKATLA 90,641 Reputation points Moderator

2023-08-01T08:04:25.8766667+00:00

@(Manasa) - We received your feedback that the answer provided on the thread was not helpful.

Kindly let us know what we could have done better to improve the answer and make your engagement experience good. We are here to help you and strive to make your experience better and greatly value your feedback.

Our engineer has provided a detailed answer which has clear steps for which you are looking for. If you wish, you may re-surveying/rating for the engagement you received on the thread. Your feedback is very important to us.

Looking forward to you reply. Much appreciate your feedback!

Regards,

PRADEEPCHEEKATLA-MSFT

Answer 1

AnnuKumari-MSFT 34,556 Microsoft Employee Moderator

Hi (Manasa) ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

I understand that you are trying to look for the possibility to change the column names of all tables having special characters with underscores dynamically without altering the table while copying data from on-premise SQL server to parquet file using ADF pipeline.

You have correctly pointed out that self hosted IR can't be used in mapping dataflow. You can try using copy activity in ADF pipeline to achieve the requirement.

In copy activity, under mapping tab, you can change the column names for the destination datastore by typing in the desired columnnames.

User's image

In order to map the source and destination columns dynamically, you can iterate through the mapping json for multiple tables and update the column names dynamically, however, you need to store the json before hand. Kindly go through this video for more details: Dynamic Column mapping in Copy Activity in Azure Data Factory

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-07-25T07:29:12.95+00:00

Hi (Manasa) ,

Just checking in to see if the above answer helped. Please do consider clicking Accept Answer as accepted answers help community as well. Also, please click on Yes for the survey 'Was the answer helpful'
(Manasa) 0 Reputation points

2023-07-25T12:23:44.8066667+00:00

1.But here my table column names are not constant across the tables it keeps changing it and also have lots of tables more than 500 tables then how we can generate the column mapping json for all the tables.

2.Here we need to replace the special characters if present in the column names by underscore. Can we handle this specific scenario with this mapping json?
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-07-25T16:31:29.19+00:00

Hi (Manasa) ,

In case , the source table schema is not constant, it can't be handled in copy activity. You need to use 'allow schema drift' property in dataflow to handle it. However, in your case, since you are ingesting data from on-premise database, you need to use self hosted IR which is not supported in dataflow .

Either , you can perform full load (drop the sink table and reload) for all the tables for every run and use 'auto create' table in sink side so that it generates the sink table fetching latest schema from the source.

Or, you can write your own custom code in C#, python and run the script using custom activity in ADF.

Hope it helps. Kindly reach out to us if you have any further questions. If the above suggestion helped, kindly accept the answer by clicking on Accept answer button. Thankyou
(Manasa) 0 Reputation points

2023-07-26T06:31:35.7566667+00:00

1.if we perform full load as well since we are using parquet format we dont have option to autocreate table in sink side. So this option we cannot use

2.To write your own custom code in C#, python and run the script using custom activity this option if we try also finally it has to go to copy activity in which copy activity for the on-premises source dataset we have option to select either Table /Query/Stored Procedure option only so we cannot use this option as well..

3.one more other question I have is how the performance of copy activity and how it will fetch data from tables in adfcopy activity when we select options like table,query,stored procedure(screenshot attached)

2)if we select table option in source adfcopyactivity will it give more performance rather than query option

i am thinking to try below solution but since we have very large tables when we choose query option thinking it will hit performance as we are doing it using source tables. please let me know your thoughts on this.

https://davidalzamendi.com/parquet-files-column-names-with-spaces/
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-07-28T17:21:51.99+00:00

Hi (Manasa) ,

You need to first load the data from source to dummy/staging tables , while doing so you can take care of renaming the columns as I suggested by performing full load using auto create table option, after that , you can load those table data into parquet files

Note: You can't modify / append data in parquet file. So , for every run, or to store incremental changes from source, you need to have different files. Parquet is a columnar storage format that is designed for efficient read and write operations, but it does not support in-place updates or appends. Once a Parquet file is written, its contents are immutable, and you cannot modify or add data to it.

Query option provides the flexibility to copy specifically selected data/ data retrieved by joining multiple tables etc , however, copy performance could be affected by factors like the complexity of the query, indexes on the source table, and the efficiency of the query execution plan . Stored procedure is equally beneficial to use if it's optimized to retrieve the data. However, If you have to get all the records from table without any change then go for 'Table' option.

Additionally, you can try setting correct DIU(Data Integration Units) and DOP(Degree of parallelism) values in settings tab by following the best practice under Performance tuning steps of Copy activity performance and scalability guide

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-08-07T08:06:55.6833333+00:00

Hi (Manasa) ,

We haven't heard anything from your side on the updates regarding issue resolution. Kindly accept the above answer in case it was helpful. Thankyou.

Share via

Using Self hosted IR, In ADF Pipeline Can we change the column names of all tables in DB having special characters with underscore without altering the TABLE within pipeline ?

1 answer

Your answer