Hi There,

I'd like to better understand your requirement.

• What data format do you use for the files?
• What technology do you use to consume the data?

HVR does not provide the ability to re-order columns. By default we pick up the column order from the database. Then if columns are added with AdaptDDL enabled then columns are appended to the table definition.

Thanks,
Mark.

Hi,

We get the data in CSV format  to unix file location

Talend is used to process same replicated data from file system to snowflake(target).

We are looking for the feature that our existing CDC jobs doing it for nearly 200 tables as part of replication as we dont want to break the order of columns.

Thank you for providing that context.

I put this request on the backlog for future consideration (when we make other UI changes to the HVR product). Please do note though that I am pessimistic that we'll get to this in calendar year 2024.

Best regards,
Mark.

What if you used a header for the files, and drove the mapping by column names rather than column position? Would that be an option for you?

Note with HVR we have the option to add a header line to CSV. I.e. you can do this today rather than wait for an enhancement.

Thanks,
Mark.

Hi Mark,

Column position matter to us when sources are replicating for existing CDC tools in production and this is happening with our existing jobs.The approach headerline will not be an option for us and if we go for any new integration as an approach also through HVR we will be observing  perfomance degrade issue while integrating and lot of effort in enabling it.In either case for existing set of tables or new tables its not an option.Thanks!

Hi Somali,

Do you frequently have to reorder columns?

One approach to reorder columns is as follows:

1. Go to the tables in the channel definition.
2. Select the table(s) for which you want to reorder columns.
3. Export the table definitions. In the exported JSON you will notice that every column has "sequence" as an attribute.
4. Change the sequence making sure that you maintain a valid sequence from one to the number of columns, incremented by one.
5. Reimport the tables in the channel definition using the Import Table Definitions option, and choose Replace when prompted.
6. Re-activate the channel with option Scripts and Jobs, and resume replication.

Note that you must perform step 6 only when there are no outstanding changes. To be on the safe side you could stop the capture before you start step 1. However, if reordering takes some time then you could wait stopping replication until later. Integrate must be drained before you re-activate at step 6.

Maybe this is an acceptable workaround if you don't change order frequently?

It should be noted that for us to implement a column reordering feature in the UI is a major undertaking. We have not had this capability before.

Thanks,
Mark.

Hi Mark,

We have tried the workaround solution you suggested for changing the sequence of column in exported table definition, we dont see column order getting changed in replication files.We are using HVR 6.1.0/49 version .Though i see the column definition moved in table definition of that channel.Please provide us a document with steps if there is any workaround solution we can test it out.

Hi Somali,

Did you regenerate scripts and jobs after you made the change to the table definition? If not then please do so.

Thank you,
Mark.

Hi Mark,

I have followed the steps that you have suggested and the replication didnt work with ordering also walk through my testing process with TOM CHU from fivetran.

As discussed with Tom ,i have exported channel definition and sent it to him to replicate the scenario by fivetran.

Somali,

Thank you for your efforts, and apologies for the inconveniences.

Tom and I caught up this morning. I showed him how this approach works for me. Obviously it does not work for you. We found out - which I had not realized - that we recently addressed an issue with column ordering in software build 6.1.0/51.

Tom will follow up with you. Hopefully you can then move forward from there.

Thanks,
Mark.

Thanks Mark,I have tested this feature in 53 version and column reorder feature working for source metadata.We would request this feature to be more flexibe on integrate end as well.Suppose if we have any custom column integrating in target side as extra this feature also should give us reordereing the integrate fields on entire metdata which includes both source and extra custom integrated fields on target end.Thanks