topic Create new columns dynamically in Talend Studio

Create new columns dynamically

Karthik_Kommarraju — Sat, 16 Nov 2024 01:37:28 GMT

Hi All,

I am pretty new to talend and I had an issue where I stuck. We receive CSV files from a server and we just load them into Oracle database. But I am trying to develop a job where it creates a new column into the table if it not exist.

For example: If I received a file with 30 columns for the first time and then I loaded into database by creating it. In the next run if I receive a file with 40 columns, then I want to create that extra 10 columns into the table with out dropping and creating the table.

Can anyone of let me know if this is possible.

Thanks,

Karthik

Re: Create new columns dynamically

evansdar — Fri, 21 Aug 2020 13:58:20 GMT

Curious why you'd choose a relational db system to house dynamically changing schema. Sounds better suited for some NoSQL variant. This is definitely possible, but it seems like a lot of overhead. You'll have to get the column names from the db + the new file, compare them and find any differences, alter the table to add the new columns then load the new file after committing. Try looking into dynamic schema. I've done something similar before, but it wasn't pretty.

Re: Create new columns dynamically

Karthik_Kommarraju — Fri, 21 Aug 2020 14:41:26 GMT

I have only one such kind of file and I want that data to connect to other tables in oracle. So I don't want to change the db for this. As you said it is lot of overhead to change columns dynamically. I am trying to find out best option available. Thanks.

Re: Create new columns dynamically

Jesperrekuh — Sat, 22 Aug 2020 22:23:38 GMT

Yes is possible, although wouldnt recommend, but I get it.

Your idea... more or less

Some conceptual steps to prepare/before actualy creating columns:

1 - Read from sys tables the columns of your table.

2A - from csv-file read only first line (header.) as a string, headerString.split it by your seperator. So you can loop through this array. Or keep it as a string to use in a where statement.

2B - Normalize your headerString so columns are in correct formatted column names.

3 - User your headerString in a query to get the delta/not existing columns, ALL_TAB_COLUMNS in Oracle.

4 - result of step 3, tricky part is datatype, first check number (int, decimal or float), string is XML or JSON if not normal string , do some custom sjizzle here.

5 - generate your ALTER TABLE ADD COLUMN statement.

6 - generate your INSERT statement.

Alternatively

1 - Read every file with the same columnschema into the same table based on fingerprint (generate a column header hash to identify) and use it as tablename.

2 - Use the ALL_TAB_COLUMNS and generate a SQL query or VIEW to MERGE, UNION, APPEND assuming the filesizes are not to big.

Alternatively:

Dont change structure
Convert your file into a json or xml in a blob
Store it with a header fingerprint and all columnnames as string/list, and also the filename.
Append each file to this table, pretty straight forward
write sql to handle json/xml with different columnames/filtetypes.

Have fun.