(SM-2508) Communication Between SAP and Snowflake
The connection to Snowflake is provided by the SNP software Kyano 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. The user type Internal Stage is used for SAP communication. In Kyano Storage Management, it is covered by the referenced binary storage.
During communication between SAP and Snowflake, the Snowflake Internal Stage is used as a temporary storage when transferring data from SAP to Snowflake. For more information, see the description of the DML sequence diagram below.
Communication with Snowflake Internal Stage
Communication with Snowflake Internal Stage is provided via the SNP Kyano Java Connector (extended JDBC API for 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 queries.
For data transfer to Snowflake DB, Snowflake Internal Stage storage is also used. Firstly, the data is moved in the 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, the SNP Kyano Java Connector is used (implemented by SNP). This connector wraps SQL queries using JDBC jars and forwards them to the engine.
In more detail, the Snowflake Java Connector acts as an intermediary between a Java application and the Snowflake DB. It wraps the SQL queries (i.e. processes and prepares them for Snowflake) and uses JDBC JARs (Java packages designed for database communication) to send those queries to the Snowflake DB engine. This allows the application to interact with Snowflake through the familiar Java-based JDBC framework, while handling Snowflake-specific protocols and optimizations behind the scenes.
Detailed Communication Diagram for Snowflake (batch load)
Detailed Communication Diagram for Snowpipe streaming
Authentication
The Java JDBC service uses dedicated database user credentials. By default, the Snowflake user also creates a binary storage in Snowflake Internal Stage. The database credentials of the user can have special permissions at the database level for accessing dedicated schemes, reading and writing dedicated objects, etc. The Java JDBC service is responsible for handling security when communicating with the Snowflake ecosystem.
Prerequisites
The customer has signed up for Snowflake Internal Stage and the Snowflake DB ecosystem
The customer has the Snowflake DB cluster set up via console, including roles.
The customer has provided the details needed for connecting to the Snowflake DB, including database credentials.
The customer grants permissions to certain system tables in Snowflake to enable a specific SAP Kyano Storage Management data computation (table size: TABLE_STORAGE_METRICS).
The customer makes sure that SNP users have the required permissions to execute SELECT statements on the table INFORMATION_SCHEMA. INFORMATION_SCHEMA is a is a pre-existing system schema provided by Snowflake that holds metadata about the database.