SAP table batch source

The SAP table batch source plugin (SAP Table plugin) lets you read data from SAP standard and custom tables or views. It enables bulk data ingestion from SAP applications into BigQuery, or any other supported target system, using Cloud Data Fusion. The plugin has the following key features:

• Uses SAP ECC, SAP S4/HANA, or SAP BW as a source system
• Doesn't use SAP CDC (Change Data Capture) to track and extract new and delta data in the data sources
• Uses batch extraction mode
• Supports SAP tables and ABAP CDS views as data sources
• Accesses SAP data through the application layer

You can read data from:

• Transparent tables
• Cluster tables
• Pool tables
• ABAP CDS views
• SAP BW ADSO objects

For more information about SAP on Google Cloud, see the Overview of SAP on Google Cloud .

Before you begin

Set up the following systems and services that are used by the SAP Table plugin:

1. Configure the SAP ERP system . This process includes the following steps:
   • Install the SAP Transport files.
   • Set up the required SAP authorizations and roles.
   • Set up the SAP Java Connector.

2. Deploy the plugin in Cloud Data Fusion .

   • Important: choose a plugin version that's compatible with the Cloud Data Fusion version.
   • If you upgrade the version of your Cloud Data Fusion instance or plugin, evaluate the impact of the changes to the pipeline's functional scope and performance.

3. Establish RFC connectivity between Cloud Data Fusion and SAP.

   • Ensure that communication is enabled between the Cloud Data Fusion instance and the SAP server.
   • For private instances, set up VPC network peering .
   • Both the SAP system and the Cloud Data Fusion instance must be in the same project.

Configure the plugin

1. Go to the Cloud Data Fusion web interface and click Studio.
2. Check that Data Pipeline - Batchis selected (not Realtime).
3. In the Sourcemenu, click Sap Table. The SAP Table node appears in your pipeline.
4. To configure the source, go to the SAP Table node and click Properties.

5. Enter the following properties. For a complete list, see Properties .

   1. Enter a Labelfor the SAP Table node—for example, SAP tables .

   2. Enter the connection details. You can set up a new, one-time connection, or an existing, reusable connection.

      New connection

      To add a one-time connection to SAP, follow these steps:

      1. Keep Use connectionturned off.

      2. In the Connectionsection, enter the following information from the SAP account in these fields:

         1. In the Reference namefield, enter a name for the connection that identifies this source for lineage.
         2. In the SAP clientfield, enter the client name of a specific instance or environment within SAP. An SAP administrator can provide the client name.
         3. In the SAP languagefield, enter an SAP logon language. The default is EN(English).

         4. Select one of the following Connection types.

            • Direct (via SAP Application Server). If you choose this default type, enter information in the following fields: SAP application server host, SAP system number, and SAP router.
            • Load balanced (via SAP Message Server). If you choose this type, enter information in the following fields: SAP Message Server host, SAP Message Server service or port number, SAP System ID (SID), and SAP logon group name.

         5. In the SAP table/view namefield, enter the name of the table or view from your SAP system.

         6. Provide the SAP credentials: ask your SAP administrator for SAP logon usernameand Password.

         7. In the JCo Library Cloud Storage pathfield, enter the SAP Java Connector (SAP JCo) path in Cloud Storage that contains the SAP JCo library files you uploaded.

         8. To generate a schema based on the metadata from SAP that maps SAP data types to corresponding Cloud Data Fusion data types, click Get schema. For more information see, Data type mappings .

         9. Optional: to optimize the ingestion load from SAP, enter information in the following fields:

            1. Using Filter optionslets you extract records based on selection conditions, such as columns having a defined set of values. Define the conditions in OpenSQL syntax in a WHERE clause. For example, this query returns all rows from the Roster table where the SchoolID column has the value SchoolID > 52 :

                 SELECT 
                 
                * 
                 
                FROM 
                 
                Roster 
                 
                WHERE 
                 
                SchoolID 
                > 
                52 
                ; 
                
               

               For more information, see Supported filters .

            2. In the Number of rows to fetchfield, you can limit the data extracted by providing a positive, whole number of rows.

            3. In the Number of splitsfield, you can create partitions to extract data records in parallel, which improves performance. The number of splits can affect SAP work processes and must be selected carefully.

            4. In the Package sizefield, specify the number of records to extract in a Single SAP network call. The package size impacts performance and available resources and must be selected carefully.

      Reusable connection

      To reuse an existing connection, follow these steps:

      1. Turn on Use connection.
      2. Click Browse connections.

      3. Click the connection name.

      If a connection doesn't exist, to create a reusable connection, follow these steps:

      1. Click Add connection > SapTable.
      2. On the Create a SapTable connectionpage that opens, enter a connection name and description.
      3. In the SAP clientfield, enter the client name of a specific instance or environment in SAP. An SAP administrator can provide the client name.
      4. In the SAP languagefield, enter an SAP logon language. The default is EN(English).

      5. Select one of the following Connection types.

         • Direct (via SAP Application Server). If you choose this default type, enter information in the following fields: SAP application server host, SAP system number, and SAP router.
         • Load balanced (via SAP Message Server). If you choose this type, enter information in the following fields: SAP Message Server host, SAP Message Server service or port number, SAP System ID (SID), and SAP logon group name.

      6. Provide the SAP credentials: ask your SAP administrator for the SAP logon usernameand Passwordvalues.

      7. In the JCo Library Cloud Storage pathfield, enter the SAP Java Connector (SAP JCo) path in Cloud Storage that contains the SAP JCo library files that you uploaded.

      8. Optional: In the Additional SAP connection propertiesfield, enter key-value pairs that must override the SAP JCo defaults.

      9. Click Create.

Properties

  
 SELECT 
  
 * 
  
 FROM 
  
 table_name 
  
 WHERE 
  
 column_name 
  
 > 
  
 value 
 ; 
  
 \ 
  
 SELECT 
  
 * 
  
 FROM 
  
 table_name 
  
 WHERE 
  
 column_name 
  
 BETWEEN 
  
 value1 
  
 AND 
  
 value2 
 ; 
  

  
 SELECT 
  
 * 
  
 FROM 
  
 table_name 
  
 WHERE 
  
 column_name 
  
 LIKE 
  
 'pattern%' 
 ; 
  

  
 SELECT 
  
 * 
  
 FROM 
  
 table_name 
  
 WHERE 
  
 column_name 
  
 \ 
  
 IN 
  
 ( 
 'value1' 
 , 
  
 'value2' 
 , 
  
 'value3' 
 ); 
  

  
 SELECT 
  
 * 
  
 FROM 
  
 table_name 
  
 WHERE 
  
 condition1 
  
 AND 
  
 condition2 
 ; 
  
 \ 
  
 SELECT 
  
 * 
  
 FROM 
  
 table_name 
  
 WHERE 
  
 condition1 
  
 OR 
  
 condition2 
 ; 
  
 \ 
  
 SELECT 
  
 * 
  
 FROM 
  
 table_name 
  
 WHERE 
  
 NOT 
  
 condition 
 ; 
  

  
 SELECT 
  
 * 
  
 FROM 
  
 table_name 
  
 \ 
  
 WHERE 
  
 datetime_column 
  
 >= 
  
 'start_date' 
  
 AND 
  
 datetime_column 
  
 <= 
  
 'end_date' 
 ; 
  

  
 SELECT 
  
 * 
  
 FROM 
  
 table_name 
  
 WHERE 
  
 datetime_column 
  
 > 
  
 'target_date' 
 ; 
  

  
 SELECT 
  
 * 
  
 FROM 
  
 table_name 
  
 \ 
  
 WHERE 
  
 datetime_column 
  
 >= 
  
 'target_datetime' 
  
 \ 
  
 AND 
  
 datetime_column 
  
 <= 
  
 'target_datetime' 
 ; 
  

  
 SELECT 
  
 * 
  
 FROM 
  
 table_name 
  
 \ 
  
 WHERE 
  
 ( 
 column1 
  
 = 
  
 'value1' 
  
 AND 
  
 column2 
  
 > 
  
 value2 
 ) 
  
 \ 
  
 OR 
  
 ( 
 column3 
  
 = 
  
 'value3' 
  
 AND 
  
 column4 
  
 < 
  
 value4 
 ); 
  

  
 ( 
  
 ERNAM 
  
 EQ 
  
 'LEE' 
  
 AND 
  
 AUDAT 
  
 GT 
  
 '20220722' 
  
 ) 
  
 \ 
  
 OR 
  
 ( 
  
 BSTNK 
  
 EQ 
  
 'PO54321065' 
  
 AND 
  
 BSTDK 
  
 LT 
  
 '20220714' 
  
 ) 
  

  
 SELECT 
  
 * 
  
 FROM 
  
 table_name 
  
 \ 
  
 WHERE 
  
 ( 
 column1 
  
 = 
  
 'value1' 
  
 OR 
  
 ( 
 column2 
  
 = 
  
 'value2' 
  
 AND 
  
 column3 
  
 > 
  
 value3 
 )) 
  
 \ 
  
 AND 
  
 column4 
  
 = 
  
 'value4' 
 ; 
  

  
 ( 
  
 ERDAT 
  
 EQ 
  
 '20220722' 
  
 OR 
  
 ( 
  
 ERNAM 
  
 EQ 
  
 'LEE' 
  
 AND 
  
 VDATU 
  
 GT 
  
 '20210623' 
  
 ) 
  
 ) 
  
 \ 
  
 AND 
  
 FMBDAT 
  
 EQ 
  
 '20220722' 
  

  
 SELECT 
  
 * 
  
 FROM 
  
 table_name 
  
 \ 
  
 WHERE 
  
 column1 
  
 IN 
  
 ( 
 SELECT 
  
 related_column 
  
 FROM 
  
 other_table 
  
 WHERE 
  
 condition 
 ); 
  

  
 SELECT 
  
 * 
  
 FROM 
  
 table_name 
  
 \ 
  
 WHERE 
  
 ( 
 column1 
  
 = 
  
 'value1' 
  
 OR 
  
 column2 
  
 = 
  
 'value2' 
 ) 
  
 \ 
  
 AND 
  
 datetime_column 
  
 > 
  
 'target_datetime' 
 ; 
  

  
 ( 
  
 ERDAT 
  
 EQ 
  
 '20220722' 
  
 OR 
  
 ERNAM 
  
 EQ 
  
 'LEE' 
  
 ) 
  
 AND 
  
 VDATU 
  
 GT 
  
 '20210623' 
  

  
 SELECT 
  
 * 
  
 FROM 
  
 table_name 
  
 \ 
  
 WHERE 
  
 datetime_column 
  
 >= 
  
 'YYYY-MM-DD HH:MM:SS' 
  
 \ 
  
 AND 
  
 datetime_column 
  
 <= 
  
 ' 
 YYYY 
 - 
 M 
  

  
 SELECT 
  
 * 
  
 FROM 
  
 table_name 
  
 \ 
  
 WHERE 
  
 datetime_column 
  
 >= 
  
 NOW 
 () 
  
 - 
  
 INTERVAL 
  
 X 
  
 DAY 
 ; 
  

  
 WHERE 
  
 YEAR 
 + 
 0 
 ( 
 4 
 ) 
  
 = 
  
 2023 
  

Use cases

Two extraction contexts are supported:

• SAP Database Tables and Views
• SAP ABAP CDS

In each of the contexts, the SAP Table plugin supports standard and custom database tables and views.

Limitations

The SAP Table plugin has the following limitations:

• It doesn't support joining tables.
• It doesn't support CDS views with parameters.
• It doesn't support without key fields.
• If the Snapshots feature is enabled, using a package size exceeding 30,000 might cause pipeline failures. This package size can vary, depending on the number of columns in the SAP table. You can calculate an optimal package size for a table using this formula: row size (in number of characters) * 5.
• Connection Manager lets you browse a maximum of 1,000 SAP table names.
• When a pipeline error occurs, such as an error in the sink, the SAP Table plugin attempts to clean up any active SAP side processes related to the extraction by calling the custom RFM intended for cleanup: /GOOG/RFC_READ_TABLE_CLEANUP .

What's next

• Learn more about SAP on Google Cloud .