How To: Replicate from a Table to a File replication
Scenario:
User wants to replicate data from a database table into a csv file.
Overview:
This article will describe how to create a channel to move data from a database table to a csv file.
Prerequisites:
- Oracle database location 'orcl' is already defined in Location Configuration
- A table named file_a has been created in the orcl database with columns c1,c2. Below are the contents of table file_a
3. Channel 'tabletofile' is already created with location SRC pointing to 'orcl'.
Steps:
The following are steps to replicate data from a source database table into a target csv file.
Step 1) Create a file location 'file1' under Location Configuration.
A] Right click on Location Configuration and select New Location.
B]In the opened New Location window, enter the location name as 'file1' and select option 'File /FTP /Sharepoint' under Class section. This step will enable the below options for the File location.
C]Protocol: Leave it as default 'Local' which indicates that the file is created on localhost.
D]Directory: Click on the browser button next to this field to select the directory you want the file to be created in. In this case, it is E:Samplefile_demo.
E]Click on Test Connection to see if it is working and create the location by clicking OK.
Step 2) Create a TGT Group for file location 'file1'.
A] Under the 'tabletofile' channel Location Groups, right-click on New Group. Enter Group Name 'TGT', select Location 'file1' and click OK.
Step 3) Add the table file_a to the channel using Table Explorer.
A] Right-click on the Tables under the channel.
B] After clicking on Table Explore, you will see a pop-up window listing the source database from which to select the table.
C] Double-click on the database location 'orcl' to see the list of tables, and select 'file_a' to add it to channel.
Step 4) Add Capture action to the SRC group in channel 'tabletofile'.
Step 5) Add Action Integrate to the channel on the group target.
A] Right-click on the TGT group and select New Action Integrate.
B] In the pop-up window for New Action Integrate, select option /RenameExpression to name the new file on the target. In the dialogue box for this option, enter '{hvr_tbl_name}/{hvr_integ_tstamp}.csv'. This action directs Local Data Processing to create a directory structure on the target location with the table name, and under that a folder which will be named with the timestamp of when it was created. For example, in our case a folder named 'file_a' will be created under the directory structure E:Samplefile_demo . Under folder 'file_a', a file will be created with the timestamp of when the file was integrated to the target.
Step 6) Add action FileFormat to the TGT group to specify the format in which file should be created.
A] Right-click on the target group to select New Action FileFormat.
B] In the FileFormat window, select options /Csv and /HeaderLine.
C] Below is a summary of all the actions configured on the channel.
Step 7) Perform Initialize to generate and load the objects needed to enable replication of the channel.
A] Right-click on the channel and select Initialize.
B] On the window 'HVR initialize for channel tabletofile', click Initialize. A confirmation message is displayed showing that 2 jobs have been created.
Step 8) Perform Refresh to load the data from table to file.
A] Right-click on the channel 'tabletofile' and select Refresh.
B] After clicking on Refresh, the table data will be refreshed to the file location.
C] Below is the directory structure for the created file. Under E:Samplefile_demo, the folder file_a has been created.
D] Within the file_a folder is the file containing the table data, with a name corresponding to the time of the Refresh (e.g., 20180518171405195.csv).
E] Open the csv file 20180518171405195.csv, and you will see the contents displayed as below.
