Skip to Content
Technical Articles
Author's profile photo Vikas Kumar Singh

SnowFlake Falling from SAP Cloud – CPI Integration with Snowflake with OpenConnector

The cloud universe seems to expanding faster than The Universe. Innovation is not more an option to survive and we are seeing an explosion of wonderful cloud apps for various enterprise solutions.

In this blog, I explored Integrating Snowflake with SAP Cloud Platform using SAP Cloud Platform Integration and SAP Open Connectors. So let us get started

 

1. Get a little familiar with Snowflake

There is obviously Google and your own interest which can make you dive deep into the world of Snowflake, its usage, architecture and capabilities. But if you just want a short Intro you can refer the below short video .

Disclaimer – I do not know the author and creator of this video and i just referred it for understanding the concept. Readers are encouraged to watch contents of their preferred choice.

 

 

2. Enable Open Connectors on SAP Cloud Trial

Divya Mary  has written many blogs on SAP Open Connectors and holding the flag high from SAP in this area. Please refer the below blog

Enable Open Connectors on SAP Cloud Trial

 

NOTE – The open connector feature was made available on Cloud Foundry, but as of today there are some issues with it. Divya has also confirmed it in one of the answers to the question on SCN. Thus I have used Neo Trial for my development

 

3. Get Inside Snowflake & create a table & warehouse

As the content would have become too big, created a video demo for it.

 

 

4. Now we are ready with our set up at app side let us get started with configuring the Open Connector

  • Once you are inside the Open Connectors application/service.
  • Navigate to Connectors -> Catalog -> Search for SnowFlake & Click Authenticate

 

  • Now enter parameters & click create Instance
    • Name – Of your choice
    • Database Host – Host from the Snowflake URL
    • Database Schema Name – we set it to PUBLIC in our case
    • Table Names – The table we created EMPDETAILS
    • Warehouse – We created SAPCFWH
    • Username – you set it at the time of registration
    • Password – you set it at the time of registration
    • Database Name – we created SAPCLOUDTEST

 

  • Navigate to the Instance Tab and you will be able to see your new Snowflake instance

 

  • Let us click on the API Docs for the Snowflake Instance. With this we can do a test
  • Go the below /query and click Try it yourself
  • Copy the authorization string, which will contain User, Organization and Element for CPI security material

  • Click Execute

 

 

5. Now it is time to try this out in CPI

  • Create User Credentials in CPI Security Materials

 

  • Save the JSON body with query in Content Modifier. I am creating HTTPs as sender to try from Postman

 

  • Open Connector Adapter setting as below

 

 

  • Now deploy the Iflow , take the HTTP end point and hit from postman

 

 

 

This is it. I was happy to see this working and I am really positive towards the Open Connectors initiative of SAP. The catalog has many popular open connectors to choose from and try out.

I feel on the part of OpenConnector adapter in CPI , I do feel certain improvements could be done in terms of setting up query parameters, but with time I am sure it will be a part of the tool soon.

 

Keep reading, Keep learning!

 

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Bhaskar mamilla
      Bhaskar mamilla

      Good one!:)

      Author's profile photo Muni M
      Muni M

      Hi Vikas,

      Thank you for the blog!

      Recently i have worked on integrating SAP PO with snowflake.It was done using jdbc adapter.

      When you call snowflake using open connector, do you see jdbc call entry in the history like this? I am just curios to know if the interaction is via any api or jdbc from open connector.

       

      Regards,

      Muni

       

      Author's profile photo Muni M
      Muni M

      I have tested with connector. Looks like connector is connecting to db via latest jdbc driver. Interesting.

      Author's profile photo Vikas Kumar Singh
      Vikas Kumar Singh
      Blog Post Author

      Hi Yes,

      With the connector it is connecting using JDBC 3.9.1.

       

      Regards,

      Vikas

      Author's profile photo Sabarna Chatterjee
      Sabarna Chatterjee

      Nice blog. I have query here. I am trying to execute the below query from the Snowflake Instance

      {
         "script": "CREATE TABLE ZEMPDETAILS ( \"EMPID\" VARCHAR (10), \"EMPNAME\" VARCHAR (40))"
      }

      reference images are attached:

       

       

      Author's profile photo Jacques Otto
      Jacques Otto

      Have you had the issue that it only returns a maximum of 50 records when you do a post to the /query api, is there a way to remove that restriction?

      Author's profile photo Nachiket Sonawane
      Nachiket Sonawane

      Hello Vikas,

      Thank you for this informative blog!
      I had one query regarding the same, Is it possible to connect to Snowflake Database Table without the open connectors functionality?
      If yes, Could you help us with the parameters setting such as Role passing, Warehouse, Database, Schema?

      Regards,
      Nachiket.

      Author's profile photo Muni M
      Muni M

      Hi Nachiket,

      yes. it is possible to connect to snowflake without open connectors. You have to use jdbc connection.

      I have connected snowflake using sap po. setup is very similar to other jdbc connection. Add jdbc jars of snowflake and use the connection string as per the documentation.

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