Getting Started with Talend Open Studio: Building Your First Job
In the previous blog, we walked through the installation and set-up of Talend Open Studio and briefly demonstrated key features to familiarize you with the Studio interface. In this blog, we will build a simple job to load data from a local file into Snowflake, a cloud data warehouse technology. More specifically, we will build a new job that takes customer data from your local machine and maps it to a target table within Snowflake.
To follow along in this tutorial, you will need Talend Open Studio for Data Integration (download here), some customer data (either use your own customer data or generate some dummy data), and a Snowflake data warehouse with a database already created. If you don’t have access to a Snowflake data warehouse, you can use another relational database technology.
To see a video of this tutorial, please feel free to see our step-by-step webinar—just skip to the third video.
To get started, right-click within the Job Designs folder in the repository and create a new folder titled “Data Integration” to house your job. Next, dive into that new folder and choose "create a job" and name your job Customer_Load. As a best practice, enter the job's intended purpose and a general description of its overall function. Once you click finish, the new job will be available within your new folder.
Bringing Data from a CSV into a Talend Job
Before building out your flow, bring your customer data into the Repository. To do this, create a new file delimited element within your Metadata folder by right-clicking on the File Delimited button and choosing “Create File Delimited.” Then, name the file “Customer” and click Next.
From there, browse to locate your customer data file. Once selected, the data is visible within the File Viewer. To define the settings and your data elements, click "Next". In the next window, choose to use a comma as a field separator. Because we selected a CSV file, set the Escape Char setting to CSV and the Text Enclosure to be double quotes. Make sure to check "Set heading row as column names" before proceeding.
Now the customer data is imported and organized. One final time, click "Next" to confirm your data schema. Talend Open Studio will guess each column's type based on each column's contents—be sure to double check that everything is correct.
After checking this data set, you can see that Talend Open Studio guessed the “phone2” column was a date, which is incorrect, so instead, change it to String and then click Finish.
Next, you can drag your Customers delimited file onto the Design window as a tFileInputDelimited component. This brings your customer data into the Talend job.
Creating Your Snowflake Connection
Next, you need to create a new connection to your existing Snowflake table. First, find the Snowflake heading in the Repository and right-click to “Create a new connection”. Give your new connection a name, and then enter your account name (so if your Snowflake URL is talend.snowflake.com, your account name would be talend), User ID, and password. Also identify the Snowflake warehouse, schema and database you will be moving your data to.
After you input all of the necessary information, test the connection and make sure it is successful. Following a successful connection to Snowflake, select from the listed tables those you want to be added to the Talend Repository for this connection then click finish. This will import the schema of those tables from Snowflake into the Repository. From there, you can now choose your table of interest from the repository (in this case, Customers) and drag and drop it into the Design window as a tSnowflakeOutput component. As a side note, we have chosen to use an existing table in this tutorial; however, you can also use Talend to create a table in an existing database.
To map the source data (customer file) to the target table (Snowflake), add a tMap component by clicking within the Design window and searching for “tMap”. The tMap component is a very robust component that can be used for a wide range of functions, but for now, we will be using it to simply link the fields between two tables (to learn more about tMap, stay tuned for the next blog in the series). To start using the tMap, connect the CSV file to tMap by dragging the orange arrow from the file delimited component to the tMap.
Next, to connect the tMap to your Snowflake output, right click on tMap and select Row, and click *New Output* to create a new output connection and give it a name like “Customers”. Then, select “Yes” when asked whether you would like to get the schema of the target component.
Your Design window should now look like this:
To configure the tMap component, double-click on the component itself within the Design Window to reveal the input and output tables. Here you must link both table columns together. You can either drag and drop to link each corresponding field individually, or select Automap, which works great in this case to link the fields between the two tables. Make any adjustments necessary. Once you have ensured the types have been properly auto-selected, click Ok to save this configuration.
If you haven't installed the additional licenses yet, this Snowflake output component will flash an error. If that's the case, simply select to install the additional packages which are located within the Help drop-down.
You’re now ready to run the job and populate the data tables within Snowflake. Within the Run tab, simply click Run. You can watch the process run from start to finish within Studio, pushing 500 rows out to Snowflake.
Once the run has been completed successfully, you can head to your Snowflake account. In this example, you can see that 500 records were successfully processed through Talend Studio and loaded into your Snowflake Cloud Data Warehouse.
And that's how to build your first job within Talend Open Studio. In our next blog, we will go through some more complex functionalities of tMap, and we will also give a few tips on running and debugging your Talend jobs. Please leave a comment and let us know if there are any other things that would help you get started on Talend Open Studio.