(SM-2102) SAP - Snowflake communication

Owned by SNP
Last updated: Feb 17, 2021 by Marian Vidra
2 min read

Connection to Snowflake is provided by Datavard component - Storage Management (SM). 

Snowflake Internal Stage

Snowflake Internal Stage is a cloud computing storage service offered by Snowflake. A stage in Snowflake is an intermediate space where you can upload the files so that you can use the COPY command to load or unload tables.  User type Internal Stage is used for SAP communication and the in Datavard SM it is covered by referenced binary storage.

During SAP-Snowflake communication, Snowflake Internal Stage is used as temporary storage when transferring data from SAP to Snowflake (see the description of the DML sequence diagram below).

Communication with Snowflake Internal Stage

Communication with Snowflake Internal Stage is provided via Java connector (extended JDBC API form upload/download streams).

Snowflake Database (DB)

Snowflake is an analytic data warehouse provided as Software-as-a-Service (SaaS). Snowflake provides a data warehouse that is faster, easier to use, and far more flexible than traditional data warehouse offerings. Snowflake is a cloud data platform with a data warehouse that supports the most common standardized version of SQL (ANSI) for powerful relational database querying.

For purpose of data transfer to Snowflake DB, also Snowflake Internal Stage storage is used. Firstly, the data is moved in form of a .csv file to Snowflake Internal Stage, and afterward, the engine loads the transferred data. 

High-level Communication Diagram

Communication with Snowflake

When communicating with the Snowflake DB engine, a Java connector (implemented by Datavard) is used. This connector wraps SQL queries using JDBC jars and forwards them to the engine.


Detailed Communication Diagram

Authentication

Java JDBC Service - uses dedicated DB user's credentials. The snowflake user by default has also created Snowflake Internal Stage used as binary storage. The user's DB credentials may have specials grants on DB level for accessing dedicated schemes, read/write dedicated objects and so on. Java JDBC Service is responsible for handling security when communicating with the Snowflake ecosystem.

Prerequisites

  • Customer has signed up for Snowflake Internal Stage and Snowflake DB ecosystem
  • Customer has Snowflake DB cluster (including roles) set up (via console)
  • Customer has to provide details needed for connection into Snowflake DB Service including DB's credentials.
  • Customer grant permissions to some system tables in Snowflake for some SAP SM data computation ( table size: "TABLE_STORAGE_METRICS")
  • Customer make sure, that DVD user can run select on table INFORMATION_SCHEMA ( table exists: "INFORMATION_SCHEMA" )