In this lesson you will
- Use the DBOutputTable component to output data into a relational database (MS SQL)
- Learn about the Reformat component for changing record structure & simple computations
Prerequisites
- Access to a database (hostname, username, password, DB name). Any common database will do: Microsoft SQL, MySQL, Oracle, Postres, etc…
- A database table already created. In our example we’re using Microsoft SQL and the following create script:
USE [quickstart] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[transactions]( [trans_time] [datetime] NOT NULL, [trans_id] [bigint] NOT NULL, [account_id] [bigint] NOT NULL, [trans_type] [char](1) NOT NULL, [amount] [decimal](20, 3) NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
Writing to a database table 1:00
For this lesson we’ll be using the DBOutputTable component which allows you to write to a database table in 1-to-1 mapping (field to field) or it can be configured for a custom SQL insert statement.
DBOutputTable
Let’s provide the DBOutputTable component with the connection information for the target database.
1 DB connection > create new connection...
A Database Connection specifies attributes for connecting to a specific database that all database components in your graphs can share.
Name: name your connection as you wish; we’re using “QuickStart-MSSQL” in this example
User: Provide DB username
Password: Provide DB password
URL: Once you select a driver (Available drivers) you will be able to put a hostname, port and database name here.
2 Mapping fields
CloverETL needs to know how to map fields from the input onto fields in the target database table.
There are several ways of doing this:
- Exactly match input metadata with database structure before the DBOutputTable component (demonstrated here using Reformat)
- Use the “Field mapping” parameter of the component
- Use SQL query with placeholders
Inspecting Runtime Errors 3:12
If you Run ( ) a graph and there’s an error, there are several ways that you can debug the problem.
Inspecting Runtime Errors
1 Look into the Console
If you see Finished Status: ERROR, there is a listing of the problem and its cause right at the end of the execution log.
2 Find the component where the error has occured
Components causing the failure are marked with a red error icon ()
Check status of other components too. You’ll see () for components that have finished processing, () for components currently running and () for unstarted ones.