topic Re: Create table dynamically in Talend Studio

Create table dynamically

Anonymous — Sat, 16 Nov 2024 14:20:26 GMT

Hello,
I need to create table dynamically by reading a txt file(Tab delimited). The first line of the text file will have the column names and all datatypes are Char.
Database: MySQL
example:
File name:test.txt
Organization Name IP Domain
xxxxxxxxxxxxxxx xxxxxxxxx xxxxxxxxx
Table sturcture should be
Name: test
Column Name Datatype
Organization Name varchar
IP varchar
Domain varchar

Is there any way to do this?
Thanks,
Sangi

Re: Create table dynamically

Anonymous — Wed, 11 Jun 2008 08:34:29 GMT

Hello Sangi,
What do you mean by "dynamically" ?
When you read an input file, the number of column is never fixed ? Names of field aren't the same ?

Re: Create table dynamically

Anonymous — Wed, 11 Jun 2008 09:51:44 GMT

Number of the columns and name of the columns are not fixed.
Any solution?

Re: Create table dynamically

Anonymous — Wed, 11 Jun 2008 10:36:35 GMT

I think you have to develop a routine to perform this task.
Create a method reading the input file:
- When you read the first line (the header line), open a DB connection.
Then write a "create table" query. The SQL field description corresponds to your header columns in your file.
- Then fetch the file to insert data in your new table (SQL insert query).

Re: Create table dynamically

Anonymous — Thu, 12 Jun 2008 05:45:29 GMT

as you said,
I wrote a routine to do the task. But I have question. How can execute this routine in a job. Which Component should I use to call the routine as a part of a job.
Thanks,
sangi

Re: Create table dynamically

Anonymous — Thu, 12 Jun 2008 06:39:36 GMT

Hello

I wrote a routine to do the task. But I have question. How can execute this routine in a job. Which Component should I use to call the routine as a part of a job.

You can call the routine on tJava/tJavaRow/tMap or any text filed. For example, there is a routine called: TalendDate, supplied by Talend, then you call it on tJava like this:
Date date=TalendDate.getCurrentDate();
In your case, you can only create the tables dynamically with the same schema, but different table name, as the schema should be created on the design time, not the run time.
Best regards

shong

Re: Create table dynamically

Anonymous — Thu, 12 Jun 2008 08:07:18 GMT

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
public class BuildQuery {
public static String test_query() throws IOException {
BufferedReader in = new BufferedReader(new FileReader("D:\\TOS-All-r12707-V2.3.2\\workspace\\File\\test.tsv"));
String str;
while ((str = in.readLine()) != null) {
System.out.println("FirstLine : " + str);
break;
}
String Col[]=str.split("\\t");

StringBuilder sb = new StringBuilder("create table demo(");
for(int i = 0 ; i< Col.length ; i++){
sb.append(Col );
sb.append(" ");
sb.append("varchar(100)");
sb.append(",");
}
sb.delete(sb.lastIndexOf(","),sb.lastIndexOf(",")+1 );
sb.append("); ");
return sb.toString() ;

}

public static String main(String[] args) throws IOException {
return test_query();
}
}

This is my routine.
and uploading the image. I tried calling the method as you said. but giving following error.
String str=BuildQuery.main();
Exception in thread "main" java.lang.Error: Unresolved compilation problem:
BuildQuery cannot be resolved
at test.test.test.tJava_1Process(test.java:127)
at test.test.test.runJobInTOS(test.java:257)
at test.test.test.main(test.java:176)
I am not a professional java guy.
and I am sure about mapping. I am using only one tJava component in the mapping. which calls a routine and the routine returns a String (Query). I need to capture the Query and pass it to the MySQL database to create the table.
What will be the probable mapping?

Please help me!!!

Re: Create table dynamically

Anonymous — Thu, 12 Jun 2008 08:25:32 GMT

Hello
add the package name:
package routines;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
Best regards
shong

Re: Create table dynamically

Anonymous — Thu, 12 Jun 2008 10:22:42 GMT

Hello Shong,
Its working fine. The routine is now giving the SQL Query (Create table) as a return. How can I use this query to create a table? Which component should I use?
Thanks,
sangi

Re: Create table dynamically

Anonymous — Thu, 12 Jun 2008 10:33:49 GMT

Hello
With the tDBxxxRow(like tMysqlRow), you can execute the standard SQL statement.
Best regards

shong

Re: Create table dynamically

Anonymous — Thu, 12 Jun 2008 11:14:29 GMT

Thank you!!! I am storing the query into a file. Now, this query has to go to tMysqlRow!!! how is this possible?

Re: Create table dynamically

Anonymous — Thu, 12 Jun 2008 12:53:48 GMT

Hello Sangi,
See the attachment.

More details :
In the input file, I create a column named "query", corresponding to the create table statement in your file.
Then Link your input file containing your query to the tMysqlRow component.
By default, the link called row1.
In tMysqlRow settings:
call the statement in query textbox : "linkName"."inputColumnName"

Re: Create table dynamically

Anonymous — Fri, 13 Jun 2008 06:54:51 GMT

Hi catounz,
Thank you so much!!! Job is done!!!

Re: Create table dynamically

Anonymous — Fri, 13 Jun 2008 08:00:20 GMT

Thanks to one and all...
I am almost done with the job.
Now job is reading the file, building the query and creating the table with the same name.
I need to improve the job to one level above, so that, it picks the file and runs the process as and when a tsv file creates in the folder. For this, I am using the tWaitForFile component, but its not picking the file from the specified folder.
Could you please tell me the tWaitForFile use, how can I use this component.
I will be having more than one file at a time, in that case the job should pick all the files and need to create the respective table.
How can I achieve this?
Many Thanks,
sangi

Re: Create table dynamically

Anonymous — Fri, 13 Jun 2008 08:51:26 GMT

Hello sangi
The tWaitForFile is used to scan one folder, if there is a new file created or delete a exist file in the folder, it will trigger one event. I have created two jobs: myJob1 and myJob2. In myJob1 job, I use the tFileList to iterate all the files in the folder if there are more than one new file created at a time. In myJob2 job, there is only one tJava component, it call the routines. You must run the myJob1 job first, then run the myJob2 Job.
Please see the screenshot.
Best regards

shong

Re: Create table dynamically

Anonymous — Fri, 13 Jun 2008 11:32:50 GMT

Hey shong,
Thanks for everything!!!
The task to be done in one go!!!
1. We will get the tsv file.
2. We need to create a query for every tsv file.
3. Execute each query to create a table in the database.
for this I created a single job!!!
I added tWaitForFile and tFileList to existing job and changed the code in tJava. Rest are same!!!
Please find the attachments.
Here is my final Java routine:
package routines;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
public class BuildQuery {
public static String test_query(String fn) throws IOException {
String filePath=new String("D:\\TOS-All-r12707-V2.3.2\\workspace\\File\\"+fn);
BufferedReader in = new BufferedReader(new FileReader(filePath));
String str;
String fileName=filePath.substring((filePath.lastIndexOf('\\')+1), filePath.lastIndexOf('.'));

while ((str = in.readLine()) != null) {
break;
}
String Col[]=str.split("\\t");

StringBuilder sb = new StringBuilder("create table "+ fileName+"(");
for(int i = 0 ; i< Col.length ; i++){
sb.append(Col );
sb.append(" ");
sb.append("varchar(100)");
sb.append(",");
}
sb.delete(sb.lastIndexOf(","),sb.lastIndexOf(",")+1 );
sb.append("); ");

BufferedWriter out = new BufferedWriter(new FileWriter("D:\\TOS-All-r12707-V2.3.2\\workspace\\File\\query.txt"));
out.write(sb.toString());
out.close();

return sb.toString() ;

}

public static void main(String[] args) throws IOException {

}
}

Hey shong and catounz once again thanks!!!
Today I will sleep happily!!!

bye
sangi