Create new columns dynamically

[Karthik_Kommarraju]

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

[evansdar]

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.

[Karthik_Kommarraju]

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.

[Jesperrekuh]

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.