Skip to Content

Create a Flow Graph to Replicate Data

Requires Customer/Partner License
Create a flow graph using SAP HANA service smart data integration for SAP BTP to replicate data into SAP HANA service for SAP BTP.
You will learn
  • How to Create a Virtual Table in SAP Web IDE
  • How to create a flowgraph to load data from the virtual table
jung-thomasThomas JungJanuary 27, 2021
Created by
Lsubatin
February 20, 2019
Contributors
jung-thomas
Lsubatin

Prerequisites

  • This tutorial is designed for SAP HANA service for SAP Business Technology Platform. Consider newer tutorials designed for SAP HANA Cloud.
  • You have configured SAP HANA service smart data integration for SAP BTP as explained in this tutorial.
  • You have created a remote source and configured the access as explained in this tutorial.

This tutorial cannot be completed with a trial account.

This tutorial cannot currently be completed with a trial account.

  • Step 1

    Create a new virtual table in db/data.

    Create Flowgraph

    Call it vt_salary_data and flag Generate configuration file

    Create Flowgraph

    Fill the Properties as follows:

    • Remote Source Name: LocalFile
    • Database and Schema name: <NULL>
    • Object Name: salarydata
    Create Flowgraph

    Save and open the File Format Editor

    Create Flowgraph

    Here you can adapt the inbound file format. Change Skipped Header Lines to 1.

    Create Flowgraph

    Copy the following lines to the simulation and run it. These have been taken from the salarydata.csv file

    text
    Copy
    ID,SALARY,START_YEAR,GENDER,REGION,T-LEVEL
    100001,139171,1998,m,APJ,T5
    
    Create Flowgraph

    Use the Copy function and then use the pencil to edit the data types

    Create Flowgraph

    Change the types as follows:

    Create Flowgraph

    Save and Build the database module

    Create Flowgraph

    As a reference, here is the code for the virtual table. You can access this mode by right-clicking on the virtual table artifact and choosing Open Code Editor.

    text
    Copy
    VIRTUAL TABLE "vt_salary_data" AT "LocalFile"."<NULL>"."<NULL>"."salarydata"
    REMOTE PROPERTY 'dataprovisioning_parameters'='<?xml version="1.0"  encoding="UTF-8" standalone="yes"?>
    <Parameters>
    <Parameter name="FORMAT">CSV</Parameter>
    <Parameter name="FORCE_FILENAME_PATTERN">%.csv</Parameter>
    <Parameter name="PARTITIONS">0</Parameter>
    <Parameter name="CODEPAGE">utf-8</Parameter>
    <Parameter name="LOCALE">en_US</Parameter>
    <Parameter name="SKIP_HEADER_LINES">1</Parameter>
    <Parameter name="ROW_DELIMITER">\n</Parameter>
    <Parameter name="QUOTED_TEXT_CONTAIN_ROW_DELIMITER">false</Parameter>
    <Parameter name="COLUMN_DELIMITER">,</Parameter>
    <Parameter name="EXPONENTIAL">E</Parameter>
    <Parameter name="LENIENT">true</Parameter>
    <Parameter name="COLUMN">ID;INTEGER;</Parameter>
    <Parameter name="COLUMN">SALARY;FLOAT;</Parameter>
    <Parameter name="COLUMN">START_YEAR;INTEGER;</Parameter>
    <Parameter name="COLUMN">GENDER;NVARCHAR(1);</Parameter>
    <Parameter name="COLUMN">REGION;NVARCHAR(4);</Parameter>
    <Parameter name="COLUMN">T-LEVEL;NVARCHAR(200);</Parameter>
    </Parameters>';
    
  • Step 2

    Create a new Flowgraph in db/data

    Create Flowgraph

    Call it salary_flow

    Create Flowgraph
  • Step 3

    Click on the + button to add a node

    Create Flowgraph

    Choose Data Source

    Create Flowgraph

    Click on the canvas to drop the node and then click configure

    Create Flowgraph

    Choose HANA Object

    Create Flowgraph

    Look for the virtual table vt_salary_data. Choose it and click Create.

    Create Flowgraph

    Mark all the control fields and delete them

    Create Flowgraph

    Click Apply

  • Step 4

    Use the + sign to create a data target and drag the arrow from the source to the target

    Create Flowgraph

    Click Configure on the target node

    Create Flowgraph

    The target mapping should be populated automatically using the source. Use TGT_SALARIES as the name of the target table.

    Create Flowgraph

    Save and build the Flowgraph.

    Create Flowgraph
  • Step 5

    Click on Execute in the Flowgraph.

    Create Flowgraph

    Confirm the warning

    Create Flowgraph

    You will see a success message in the console

    Create Flowgraph
  • Step 6

    Right-click on the database module and choose Open HDI Container

    Create Flowgraph

    In the HDI container, navigate to Tables and you will find the table created by the flowgraph. Right-click on it and choose Open Data

    Create Flowgraph

    Use

    Create Flowgraph
    Edit SQL statement to modify the select statement for a SELECT COUNT(*). Count the total records to complete the validation below

    How many records is the count returning?

Back to top