Exporting RDBMS Table to A Flat File (CSV)

This article describes how to convert table data into a CSV file via ODI.

First, the source table preparation work 1.1 Define the physical architecture

• Define New Data Server
  Odi->topology->physical Architecture, right-click Oracle, select New Data Server, define a name in the Definition dialog box, enter the Oracle instance name, and the user information, Save.
  
  Then in the JDBC dialog box, select the JDBC driver and enter the JDBC connection URL, which can be saved by clicking on "Test Connection" and testing successfully.
  
• Create a new physical Schema
  Right click on "Data Server", select "New Physical Sever", select the corresponding schema in the Definition dialog box, other use the default value, save.
  1.2 Defining the logical architecture

  Odi->topology->logical Architecture, right-click Oracle, select "New Logical schema", define a name, select the physical schema created above, save.
  

  1.3 Defining the Oracle Table model
• Defining model Folders
  ODI studio->designer, select "New Model Folder", define a name, click Save.
  
• Defining the Model
  Right click on the New Model folder, select "New Model", in the Definition dialog box to define the name, technology select "Oracle", the logical scheme Select the above created, click "Save".
  
  After the model definition is complete, click on "Reverse engineering" above to synchronize the table structure. II. preparation of the target document 2.1 defining the physical architecture
• Define New Data Server
  ODI studio->topology->physical architecture->file, open file_generic, enter the relevant parameters in the Definition dialog box such as:
  
  Once the input is complete, click "Save" in the top left and click "Test Connection" to see if you can connect.
  JDBC dialog box, click on the "Find" icon in the back of JDBC Driver and select "ODI File jdbc Driver". Click the JDBC URL and select "Jdbc:snps:dbfile" to add the relevant parameter values according to the encoding format of the file. As shown in the following:
  
• Defining physical scenarios
  The physical scheme is then created. Right-click on "File_generic" and select "New physical Schema", such as:
  
  In the Definition dialog box, enter the directory of the scheme (the directory where the text files are located) and the working directory (which can be the same as the schema directory), the directory actually refers to the data file storage path, click "Save". 2.2 Defining the logical architecture

  ODI studio->topology->logical architecture->file, select "New Logical Schema", such as:
  

  2.3 Defining the file model
• Defining model Folders
  ODI studio->designer, select "New Model Folder", define a name, click Save.
  
• Defining the Model
  Right click on the New Model folder, select "New Model", in the Definition dialog box to define the name, technology select "File", the logical scheme Select the above created, click "Save".
  
• Define New Datastore
  Right click on the model name, select "New Datastore", arbitrarily define the name, Datastore type Select table, enter the source data file name, click Save.
  
  Next, in the file dialog box, set the delimiter for the field and record, such as:
  
  --the field delimiter is comma-delimited, hexadecimal is \002c;
  --The text delimiter uses double quotation marks;
  Finally, in the Properties dialog box, import the table structure by reverse engineering. The imported data types and field names need to be adjusted. Add fields directly here.
  III. mapping 3.1 Creating project design Engineering

  Odi->designer->projects->new project, define a name for the project, save it.
  

  3.2 Importing Knowledge modules

  Right-click the project name below the knowledge Modules, select Import Knowledge Modules, where the module/u01/oracle/middleware/oracle_home/odi/sdk/ Xml-reference path, select all the modules here.
  

  3.3 Creating mappings

  Designer > Projects > O_to_csv>first folder Right-click Mappings, select New Mapping, define a name, then drag the source and target files into the map and automatically associate them with the field names.
  
  Click on the target file in the logical structure, set Target's integration type to incremental Update (default is append), save.
  
  In the physical structure, click Default to set the loading knowledge module to lkm SQL to File.global save.
  

  Iv. Performing Mappings

  Right-click the map name, select Run,
  
  The running process can be seen in the operation->session,
  

Exporting RDBMS Table to A Flat File (CSV)