One Star

Create schema from ODBC source

Hi
I am very new to Talend and would appreciate some guidance.
I am trying to extract an entire database from an ODBC data source and load it into Postgres. I do not have the schema for the ODBC database but was hoping there was a way for Talend to create and load the database in Postgres automatically based on the ODBC data/schema. Talend has no problem reading the ODBC source, seeing the schema and extracting the data into a flat file. However from what I can tell, Talend expects the Postgres database to already have the database and schema created for the load process.
Is this possible?
Any other suggestions on how to achieve this?
Thanks
O
4 REPLIES
One Star

Re: Create schema from ODBC source

Hi
After some more playing around with the Talend interface I have seen there is the option to create the table on the target database, which is what I am trying to achieve.
I first tested the load by creating the exact same table in postgres and setting Talend to just insert the data. This worked without any errors.
However, when I choose either "create table" or "drop and create table" for the postgres component, I get the following error.
Exception in component tPostgresqlOutput_1
org.postgresql.util.PSQLException: ERROR: length for type varchar must be at least 1
Any help would be appreciated.
Thanks
O
Community Manager

Re: Create schema from ODBC source

Hello
Exception in component tPostgresqlOutput_1
org.postgresql.util.PSQLException: ERROR: length for type varchar must be at least 1

When you define the schema, you must specify the length of field if you want to create the table.
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Create schema from ODBC source

Thanks for the reply shong.
I was hoping that Talend could create the target table on the fly based on the input schema. Correct me if I am wrong but what you are suggesting is that I create a schema for the target database which would negate what I am trying to achieve.
There is one other thing now that you mention field length. The fields from the input schema have no lengths. For example, the "STREET" column in the "ADDRESS" table has a DBTYPE=CHAR, TYPE=STRING and LENGTH=blank. Whould I need to edit each one of the Length fields in order for the target table to be created successfully?
Thanks again
O.
Community Manager

Re: Create schema from ODBC source

Hello
Whould I need to edit each one of the Length fields in order for the target table to be created successfully?

Yes, you must add a length for each field if want to create the target table on the fly based on the output schema.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business