Connecting Analytics Cloud to Snowflake (Import mode)

[miguel_peredozurcher]

 

 

If you need to combine inside SAP Analytics Cloud (SAC) data from sources like SAP and Snowflake or load data from Snowflake into SAC, you need to set up an import connection between SAC and Snowflake.

Here is an overview of the different types of connections supported by SAC.

At this moment, there is no native cloud connector between SAC and Snowflake; you need to install the: SAP Analytics Cloud Agent Simple Deployment Kit to be able to connect.

Here are the steps needed to perform this type of connection:

Steps

You need to follow these steps:

1. Download the Agent (you need a PC or virtual machine to run this)


2. Install and Configure the Agent and setup SAC Tenant


3. Download and install Snowflake JDBC Driver

(The details for the step are below)

After you have done all these steps, in SAC you can create a new connection to your Snowflake account:

 

In connections press the "+"

Then choose "SQL Database"

Then select the location:

Then Snowflake as Connection Type, then just fill your Snowflake connections settings 1 to 5

 

Download Agent

Follow the instructions from here. At the moment of writing this blog, the version of the Agent is SAP Analytics Cloud agent kit 1.0.275

 

Install and Configure the Agent

Uncompress the file downloaded in the previous step. Locate the file Readme.txt and follow instructions from there.

In the Readme.txt one of the steps is:

"* In Windows Explorer, right-click on "setup.ps1" then click

"Run with PowerShell" to execute."

For this to work in my PC I needed, to run this command:

Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope CurrentUser

I found information about the command here, after running this command try again executing "setup.ps1".

You will see the installation start:

Then the instructions pages will open in a browser, follow the instructions from there:

Tip: When your are on step 4, and something is not working, open the SAP notes suggested by the error message, those are very good source of clear information.

 

Step 8 from this guide needs to be done in the SAC tenant:

Download and install Snowflake JDBC Driver

Download the driver from Snowflake Repo:

https://docs.snowflake.com/en/user-guide/jdbc-download.html

Copy jar file to:

C:\Program Files\SAP\SACAgentKit\drivers

Edit file:

C:\Program Files\SAP\SACAgentKit\config\c4a_agent_drivers.properties

Add the line:

Snowflake=C:\Program Files\SAP\SACAgentKit\drivers\snowflake-jdbc-3.8.6.jar

The file, will look like:

At this point, I restarted the PC.

 

[sainath]

Excellent Blog ! Thank you for sharing

[miguel_peredozurcher]

When I wrote this blog installed the SAP Analytics Cloud Agent Simple Deployment Kit on my laptop.

I decided I will move it to a VM.

I followed the same steps; but for the installation in the VM, I do not see the Snowflake connection. In the old Installation I do see it.

This is how it looks in the original installation:

In the new installation:

The Snowflake connection does not appear.

I found this note, that describes the symptom: https://launchpad.support.sap.com/#/notes/2789361

But, does not mention anything about Generic JDBCs, because Snowflake is not on the list.

In the original installation it is working, and I have loaded data.

Anyone can comment on this?

Thanks!

[miguel_peredozurcher]

Hi,

I could not find the root cause why it was not working, I only noticed that in file:

 

C:\Program Files\SAP\SACAgentKit\tomcat\logs\catalina.yyyy_mm_dd.log there was a line that said something like: "Starting without JDBC support"

I un-installed the whole thing, and started from scratch, now it is working.

Cheers,

 

[regysm21]

Hello,

thanks for the great blog. We have installed the SAP Analytics Cloud Agent in a Linux machine  and I placed the jar file of snowflake ( snowflake-jdbc-3.12.7.jar) in the following path: /opt/tomcat/webapps/C4A_AGENT/WEB-INF/lib, where also other .jar Connection Files are placed.

We restarted the Tomcat server and adding the file in the above path, howerver wenn I add an Import Connection with SQL-Databases in SAC I do not find there the Snowflake Connector (as in the screenshot of yours above).

Furthermore, I cannot find the file c4a_agent_drivers.properties in any of my Tomcat folders (in Linux), where I could add the snowflake driver and can point to it (as you have done above).

Am I missing something here or perhaps you have a Tipp for me where to find the above Properties file with the list of drivers in Linux?

Thanks in Advance,

Regys

[simon_kussmann]

Dear Miguel,

we were able to install the Snowflake drivers and can select the corresponding connection type in SAC.

SAC Connection

But we get the following error when selecting the conenction type:

 

Do you have any ideas?

Thanks!

Best,

Simon

[miguel_peredozurcher]

Hello Simon,

Not sure what can be the issue.

have you checked the logs in the agent?

Cheers!

[simon_kussmann]

Hi Miguel,

unfortunately we didn't have any logs in the agent.

How did you insert the Snowflake DB into the .properties file of the agent?

https://help.sap.com/viewer/00f68c2e08b941f081002fd3691d86a7/release/en-US/0d5ffbb6951b45778c1a90ffd...

Thanks!

 

[former_member714594]

Hi miguel.peredozurcher

 

I'm still facing issue how to install driver on windows or linux machine,

 

[mfoeken]

Hi DharmeshN,

Did you restart the Tomcat? And did you specified the entry of Snowflake correctly in the .properties file?

Kind regards,

Martijn van Foeken | Interdobs

[mfoeken]

Hi miguel.peredozurcher,

First of all great blog! Unfortunately I'm facing an issue. I can select the Snowflake driver but when I try to create the connection I get the following response from the UI in SAC: unable to connect to the data source.

I checked the Tomcat logs and found this: Error validating connection definition, missing required parameter: WAREHOUSE

When I use the same connection details to connect from MS Excel via a 32-bit ODBC driver it works without any issues.

Any thoughts on this?

Kind regards,

Martijn van Foeken | Interdobs