Snowflake

This page shows how to set up and use the Snowflake destination. The Snowflake destination loads data into a Snowflake environment.

Requirements

The connection to the Snowflake target environment is made via the ODBC driver for Windows 64-bit architectures. No additional installations are required to use the Snowflake destination.

• Download and install the SnowflakeDSIIDriver.
• To connect through a proxy server, configure the following environment variables: http_proxy, https_proxy, no_proxy.
  For more information, see Snowflake: ODBC Configuration and Connection Parameters.
• The ODBC default port (443) for HTTPS is enabled and allows outbound traffic from the network to Snowflake's services.
• The Snowflake account used to upload data to Snowflake needs the corresponding access and role privileges, see Snowflake Documentation: Overview of Access Control - Roles. The following privileges are required:
  • PUT command
  • COPY command
  • TABLE

The Snowflake destination only supports Snowflake managed data stages. To write SAP data to external stages in Snowflake, refer to the Azure Storage, AWS S3 or Google Cloud destination.

Create a new Snowflake Destination

Follow the steps below to add a new Snowflake destination to Xtract Universal:

1. In the main window of the Designer, navigate to Server > Manage Destinations. The window “Manage Destinations” opens.
   
2. Click [Add] to create a new destination. The window "Destination Details" opens.
   
3. Enter a Name for the destination.
4. Select the destination type Snowflake from the drop-down menu. A list of connection details opens.
5. Fill out the destination details to connect to the destination.
6. Click [OK] to confirm your input.

The destination can now be assigned to extractions.

Destination Details

The destination details define the connection to the destination.

General

Output directory

Enter an existing local directory in which the extracted data is written as a csv file.
Process during an extraction:

• A csv file is created in the output directory.
• Once the file has reached a certain size, it is zipped (gzip), see File Splitting.
• The zipped file is uploaded via PUT command to the Snowflake staging area.
• The data is then copied via COPY command to the target table in Snowflake.

This process (gzip + PUT command) repeats itself until the extraction is finished. While running an extraction, the csv files in the local directory and the staging area are deleted.

Account Name

Enter the Snowflake authentication account.
The account name can be derived from the connection URL.

• URL for organization account identifier:
  https://[organization]-[account_name].snowflakecomputing.com/console#/
• URL for region account identifier (legacy):
  https://[account_name].[region].[cloud].snowflakecomputing.com/console#/

Database

Enter the name of the Snowflake database.

Schema

Enter the schema of the Snowflake database.

Role

Enter a Snowflake user role. If no user role is entered, the default user role is used for the connection.

Account Identifier

Organization (preferred)

Enter the name of your organization. Authentication via the account name in your organization is the preferred way of identifying youself against Snowflake, see Snowflake Documentation: Account Name in Your Organization

Region (legacy)

Select the region of the Snowflake environment from the drop-down-menu.
In the example above, the region AWS - EU (Frankfurt) is selected. The selected region must match the information in the assigned account.

Authentication

Username

Enter the Snowflake authentication user name.

Basic

If this option is active, basic authentication is used for authentication. Enter the Snowflake authentication password of the user in the field Password.

Key Pair

If this option is active, key pairs are used for authentication, see Snowflake Documentation: Key Pair Authentication & Key Pair Rotation. Select the path to your private key in the field Private Key Path. Both encrypted and non-encrypted private keys are supported. If you use encrypted private key for authentication, enter the password that is used by snowflake to decrypt it in the field Key password.

Stages

Click [Test Connection] to fetch all stages and warehouses from Snowflake. The stages and warehouses then become available for selection.

Stage name

Select a Snowflake Stage. Note that only Snowflake managed data stages are displayed.

Warehouse

Select a Snowflake Data Warehouse.

Assign the Snowflake Destination to an Extraction

Extractions write data to their assigned destination. Follow the steps below to assign a destination to an extraction:

1. In the main window of the Designer, select an extraction.
2. Click [ Destination]. The window “Destination Settings” opens.
   
3. In the “Destination Settings” window, select a destination from the dropdown list.
   
4. Optional: edit the destination settings.
5. Click [OK] to confirm your input.

When running the extraction, the extracted SAP data is now written to the destination.

Destination Settings

The destination settings only affect the extraction that the destination is assigned to. To open the destination settings, select an extraction in the main window of the Designer and click [ Destination]. The window "Destination Settings" opens.

File Name

Determines the name of the target table. The following options are available:

Append timestamp

Add the timestamp in the UTC format (_YYYY_MM_DD_hh_mm_ss_fff) to the file name of the extraction

Use Script Expressions as Dynamic File Names

Script expressions can be used to generate a dynamic file name. This allows generating file names that are composed of an extraction's properties, e.g. extraction name, SAP source object. This scenario supports script expressions based on .NET and the following XU-specific custom script expressions:

#{Source.Name}#

#{Extraction.ExtractionName}#

#{Extraction.FullyQualifiedExtractionName}#

#{Extraction.Type}#

#{Extraction.SapObjectName}#

#{Extraction.Timestamp}#

#{Extraction.SapObjectName.TrimStart("/".ToCharArray())}#

#{Extraction.SapObjectName.Replace('/', '_')}#

#{Extraction.Fields["FISCPER"].RangeSelections[0].LowerValue}#

#{Extraction.Fields["FISCPER"].RangeSelections[0].UpperValue}#

#{Extraction.Context}#

#{Extraction.Fields["[NameSelectionFiels]"].Selections[0].Value}#

#{Odp.UpdateMode}#

#{TableExtraction.WhereClause}#

Column Name Style

Defines the style of the column name. Following options are available:

Convert dates

Converts the character-type SAP date (YYYYMMDD, e.g., 19900101) to a special date format (YYYY-MM-DD, e.g., 1990-01-01). Target data uses a real date data-type and not the string data-type to store dates.

Year 0

Converts the SAP date 00000000 to the entered value.

Year 9999

Converts the SAP date 9999XXXX to the entered value.

Invalid values

If an SAP date cannot be converted to a valid date format, the invalid date is converted to the entered value. NULL is supported as a value. When converting the SAP date the two special cases 00000000 and 9999XXXX are checked first.

File Splitting

Writes extraction data of a single extraction to multiple files. Each filename is appended by _part[nnn].

Max. file size

The value set in Max. file size determines the maximum size of each file.

Preparation

Defines the action on the target database before the data is inserted into the target table.

To only create the table in the first step and not insert any data, you have two options:

1. Copy the SQL statement and execute it directly on the target data database.
2. Select the None option for Row Processing and execute the extraction.

Once the table is created, it is up to you to change the table definition, e.g., by creating corresponding key fields and indexes or additional fields.

Row Processing

Defines how the data is inserted into the target table.

Finalization

Defines the action on the target database after the data has been successfully inserted into the target table.

Transaction style

One Transaction

Preparation, Row Processing and Finalization are all performed in a single transaction.

• Advantage: clean rollback of all changes.
  
• Disadvantage: possibly extensive locking during the entire extraction period.

RowProcessingOnly

Only Row Processing is executed in a transaction. Preparation and Finalization without an explicit transaction (implicit commits).

• Advantage: DDL in 'Preparation and Finalization* for DBMS that do not allow DDL in explicit transactions (e.g. AzureDWH)
  
• Disadvantage: no rollback of Preparation/Finalization.

No Transaction

No explicit transactions.

• Advantage: no transaction management required by DBMS (locking, DB transaction log, etc.). This means no locking and possible performance advantages.
  
• Disadvantage: no rollback

Empty Values

This option controls the Snowflake file format parameter EMPTY_FIELD_AS_NULL. When Replace empty values with SQL NULL is active, empty strings are converted to NULL values when uploading data to Snowflake.

Append Extraction Timestamp

When Add timestamp column to result set is active, an additional column ExtractionTimestamp is added to the output of the extraction. The timestamp uses the format yyyy-MM-dd'T'HH:mm:ss.SSS'Z' (UTC).

Merge Data

The following example depicts the update of the existing data records in a database by running an extraction to merge data. In this case, merging means changing a value of a field or inserting a new data row or updating an existing record in SAP.

With a merge command, the updated value is written to the destination database table. The merge command ensures delta processing: new records are inserted into the database and / or existing records are updated.

Prerequisites

You need a table with existing SAP data, in which new data can be merged.
Ideally, the table with existing data is created in the initial load with the corresponding Preparation option and filled with data with the Row Processing option Insert.

After the table is created, open SAP and change a field value in the SAP table that is used for the data merge.

Merge Command

The merge process is performed using a staging table and takes place in three steps:

1. A temporary table is created.
2. The data is inserted in the temporary table.
3. The temporary table is merged with the target table and then the temporary table is deleted.

Follow the steps below to set up the merge process in Xtract Universal:

1. In the main window of the Xtract Universal Designer, select an extraction from the list of extractions.
2. Click [ Destination]. The window "Destination Settings" opens.
3. Make sure to assign Snowflake destination to the extraction.
4. Apply the following destination settings:
   
5. Click [OK] and run the extraction.

More information about the updated fields can be found in the SQL statement.
It is possible to edit the SQL statement if necessary, e.g., to exclude certain columns from the update.
Fields that do not appear in the SQL statement are not affected by changes.

Custom SQL Statements

The Snowflake destination supports the use of custom SQl statements in the staging steps of the database. Follow the steps below to create custom SQL statements in the Snowflake destination:

1. In the main window of the Xtract Universal Designer, select an extraction from the list of extractions..
2. Click [ Destination], the window "Destination Settings" opens.
3. Make sure to assign the Snowflake destination to the extraction.

4. Select the option Custom SQL from the drop-down list in one of the following sections:

   • Preparation
   • Row Processing
   • Finalization

   

5. Click [Edit SQL] . The window "Edit SQL" opens.

6. Enter your custom SQL statement and click [OK] to confirm your input.

Use Templates

Existing SQL commands can be used as templates.
You can write your user-defined SQL expressions and adapt the loading of the data to your needs. You can additionally execute stored procedures that exist in the database. To do so, use the SQL templates provided in the following staging steps:

• Preparation, e.g., in Drop & Create or Create if Not Exists
• Row Processing, e.g., in Insert or Merge
• Finalization

Follow the steps below to generate a Custom SQL command from a template:

1. In one of the staging steps, select the Custom SQL option from the drop-down list .
2. Click [Edit SQL] . The dialogue "Edit SQL" opens.
   
3. Navigate to the drop-down menu and select an existing command .
4. Click [Generate Statement]. A new statement is generated.
   
5. Click [Copy] to copy the statement to the clipboard.
6. Click [OK] to confirm your input.

Check out the Microsoft SQL Server example for details on predefined expressions.

Use Script Expressions

You can use script expressions for Custom SQL commands. The following Xtract Universal specific custom script expressions are supported:

#{Extraction.ExtractionName}#

#{Extraction.FullyQualifiedExtractionName}#

#{Extraction.TableName }#

#{Extraction.RowsCount }#

#{Extraction.RunState}#

#{(int)Extraction.RunState}#

#{Extraction.Timestamp}#

For more information, see Script Expressions.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

#{
   iif
   (
      ExistsTable("MAKT"),
      "TRUNCATE TABLE \"MAKT\";",
      "
         CREATE TABLE \"MAKT\"(
            \"MATNR\" VARCHAR(18),
            \"SPRAS\" VARCHAR(2),
            \"MAKTX\" VARCHAR(40));
      "
   )
}#

Create a Status Overview

The table "ExtractionStatistics" provides an overview and status of the executed Xtract Universal extractions. To create the "ExtractionStatistics" table, create an SQL table according to the following example:

1
2
3
4
5
6
7

CREATE TABLE [dbo].[ExtractionStatistics](
    [TableName] [nchar](50) NULL,
    [RowsCount] [int] NULL,
    [Timestamp] [nchar](50) NULL,
    [RunState] [nchar](50) NULL
) ON [PRIMARY]
GO

The ExtractionStatistics table is filled in the Finalization process step, using the following SQL statement:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

INSERT INTO [ExtractionStatistics]
(
     [TableName], 
     [RowsCount], 
     [Timestamp],
     [RunState]
)
VALUES
(
     '#{Extraction.TableName}#', 
     '#{Extraction.RowsCount}#', 
     '#{Extraction.Timestamp}#',
     '#{Extraction.RunState}#'
);

Custom SQL Example

In the depicted example, the DataSource 0FI_AP_4 is extended by one column that is filled with the user-defined runtime parameter RUNTIMEPARAMETER. The filling of the new column is implemented dynamically in the Finalization section of the destination settings.

1. In Snowflake, deselect the Error on Column Count Mismatch option in the XTRACT_UNIVERSAL File Format.
   
2. Open the extraction and click Edit runtime parameter to create the runtime parameter RUNTIMEPARAMETER .
3. Choose a field in the sections Fields and click Edit to add a selection criterion that uses the runtime parameter .
4. Close the extraction and open the Destination Settings of the extraction.
5. Make sure to assign the Snowflake destination to the extraction.
6. In the staging step Preparation, select the option Custom SQL from the drop-down list and click [Edit SQL]. The window "Edit SQL" opens.
7. In the drop-down menu, select the option Drop & Create and click [Generate Statement] to use the template for Drop & Create.
   
8. Add the following line in the generated statement:
   

   "RUNTIMEPARAMETER" VARCHAR(4),
   

9. Click [OK] to confirm your input.
   
10. In the staging step Row Processing, select the option Copy file to table.
    At this point, no data from the SAP source system but NULL values are written to the newly created column RUNTIMEPARAMETER.
11. In the staging step Finalization, the NULL values can be filled by a custom SQL statement. Select the option Custom SQL from the drop-down list and click [Edit SQL]. The window "Edit SQL" opens.
12. Paste the following SQl statement into the editor:

    UPDATE "0FI_AP_4"
    SET RUNTIMEPARAMETER= '@RUNTIMEPARAMETER'
    WHERE RUNTIMEPARAMETER IS NULL;
    

    The NULL values are filled with the runtime parameter RUNTIMEPARAMETER and written into the SQL target table using the T-SQL command UPDATE.
13. Click [OK] to confirm your input and close the destination settings.
14. Open the "Run Extraction" window of the Designer and enter a value for the runtime parameter , before running the extraction .

Check the existence of the new column RUNTIMEPARAMETER in the Snowflake Console of the table 0FI_AP_4.

Related Links

• Requirements: .NET-Framework
• Requirements: 64-bit Architecture
• Snowflake Documentation: Installing and Configuring the ODBC Driver for Windows
• Snowflake Documentation: Snowflake Identifiers
• Knowledge Base Article: Integrate SAP Data into a Snowflake Data Warehouse
• Knowledge Base Article: SAP Integration with Matillion Data Loader
• Extraction Parameters