Talend community forum / Create and fill multiple databases (datamarts)

Mesje · 2012-05-07 17:00:36

Hello,

I'm quite new with Talend and i'm wondering if (and how) it's possible to create multiple datamarts on a (mysql) server, using on a SQL-statement.

We currently using a signle database for multiple customers. The data is separated by a domain-guid (each customer has his own guid). We want to create multiple datamarts and fill them based on the domain-guid. So is it possible to:
- create multiple databases by Talend ETL (the source-database and the datamarts will be placed on 1 server)
- fill every customer specific datamart (iterate?) (select * from database.... where domain = .....) ??

I'm curious if this can be automated using the ETL-schema/process.

p.s. I'm using the open source version and i've got a fully working ETL-process (everything hard-coded)

Regards,
Mesje

phobucket · 2012-05-07 22:54:40

Hi Mesje,

Yes, but you will need at least two jobs, one for db creation, and another one-n for db population.

Use the guid as part of the text string to "CREATE database <domain-guid> ...."
and use a tMySQLRow to execute the create database statements.

In the database population jobs, the domain-guid will be a context.variable that is passed in as a connection parameter.

Thanks,
Ben

Mesje · 2012-05-10 10:22:58

Hi Ben,

Thanks for your reaction. I'm glad that this is even possible, but i've got a few more question.
1. How do you use the created databases as a connection in Talend?
So first you you select every domain in the database -> (iterate) -> create database (and user) -> use that db as a new connection -> fill db.
2. Does the output component understand that he needs to use a different connection every iteration? You can use an existing connection but that is predefined.. He needs to understand that in every iteration he needs to switch between connections.

gr,
Mesje

phobucket · 2012-05-16 00:02:17

Hi Mesje,

If your scenario is that each time a job is performed, it must iterate through all database connections, the design could start with a loading the databases and user connection info from the user table

I think this will work.

"SELECT db, user, password from user" send that to a tFlowToIterate
In tFlowToIterate uncheck use default... and set the db_name key to the database from tMySQLInput, user to user and so on.

then in the db components in the main part of the job, use (String)globalMap.get("db_name") for the database parameter, (String)globalMap.get("user_name") for the user..etc.

What I am unclear about is if the pw will be unencrypted when it is pulled from the db via a select statement. You may have to solve for that.

Announcement

Unanswered posts

#1 2012-05-07 17:00:36

Create and fill multiple databases (datamarts)

#2 2012-05-07 22:54:40

Re: Create and fill multiple databases (datamarts)

#3 2012-05-10 10:22:58

Re: Create and fill multiple databases (datamarts)

#4 2012-05-16 00:02:17

Re: Create and fill multiple databases (datamarts)

Board footer