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!
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.
I have tested with connector. Looks like connector is connecting to db via latest jdbc driver. Interesting.
With the connector it is connecting using JDBC 3.9.1.
Nice blog. I have query here. I am trying to execute the below query from the Snowflake Instance
reference images are attached:
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?
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?
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.