By: Koen Verbeeck | Updated: 2023-12-21 | Comments | Related: More > Import and Export
Problem
In today's cloud era, everything is increasingly online. Not so long ago, everything was on-premises: the databases, the CRM system, the ERP system, the web server. You name it; it was all installed on your servers. When you needed data from a system, you had to obtain the security credentials, connection information, and maybe open a port in the firewall. Sometimes, you needed to install software on your machine to make the connection work, an ODBC connection, or an OLE DB provider (and figure out if you needed 32-bit or 64-bit).
But nowadays, most services are in the cloud. They're not on your servers; they're somewhere else. With most software-as-a-service (SAAS) offerings, you don't have many options to configure connectivity. What if you need data from one SaaS service into another? For example, you need to connect Salesforce to Google Sheets, but there isn't a native connector. Where do you install the drivers? If you're lucky, there might be some APIs, but to work with them, technical expertise is required. With the rapid nature of the cloud, new data sources pop up constantly, and business units are using those new products that need data integration. IT can't keep up.
Solution
CData Connect Cloud answers those problems by offering a "cloud-based data virtualization" solution that is fully integrated with SQL Server via Linked Servers. Using a Linked Server means you can connect to the data sources using 4-part names (server – database – schema – table), just as in SQL Server. This is accomplished without having to install or manage a client tool to access cloud data directly from SQL Server.
With an easy-to-use interface from CData, you can create a data virtualization layer between the data sources and the consumers. This layer acts as a middleware where you can centrally manage your connections, users, and their credentials. You can then query those data sources using the familiar SQL language via a Linked Server. It's even possible to write queries spanning multiple data sources. And if the data source supports it (along with the correct permissions), you can execute INSERT, UPDATE, and DELETE statements as well as execute stored procedures.
CData Cloud Connect Use Cases
CData Cloud Connect provides the infrastructure to access data without the need to install and manage any software and executes familiar SQL queries across multiple clouds, with the ability to return a single result set. With this functionality, Developers and DBAs can use the product for:
SQL Server Integration
- Query cloud data directly using SQL Server queries with a Linked Server
- Ability to retrieve data from multiple cloud applications with a single query and result set
- Use familiar T-SQL logic to JOIN data across multiple clouds
- Time savings to access data across multiple clouds without the need to perform ETL \ ELT operations
- No additional software management needed, CData centralizes the management and auditing for data access
SQL Server Linked Server Capabilities
Using the SQL language, interesting features become available with CData Cloud Connect via a Linked Server:
- Use the 4-part name to access the data from a source
- Create federated queries that join data across data sources (and thus over
different clouds)
- For example, you can join data from Hubspot (where your leads are) with data from NetSuite (where your accounts are)
- Here is an example of querying with Salesforce and Snowflake after the CData Cloud Connect data sources have been setup:
SELECT
[Salesforce1].[Salesforce].[Account].[Name]
,[Snowflake1].[Website].[LoginHistory].[LastLogin]
FROM [Salesforce1].[Salesforce].[Account]
JOIN [Snowflake1].[Website].[LoginHistory]
ON [Salesforce1].[Salesforce].[Account].[Id] = [Snowflake1].[Website].[LoginHistory].[SFAccountId]
- Advanced Filtering
- If the filter can be pushed down to the source – for example, the API supports it – then the filtering will occur on the server side, which leads to better performance
- If it is not supported, CData will do the filtering and aggregation in memory
- Aggregations with GROUP BY logic are supported
- CDATA query API has built-in functions available to help write business logic
- Create views or stored procedures. For example, you can create a view on top of Facebook data to simplify logic and hide complexity from users. Views can be federated queries as well.
- Supports DDL commands such as CREATE/ALTER/DROP table.
- If supported (and with the correct permissions), data can be modified with
UPDATE, DELETE, or INSERT.
- The CData SQL syntax also supports UPSERT and GETDELETED
- A complete overview of possibilities is available in the SQL reference doc page.
ETL Projects
- CData Connect Cloud doesn't replace ETL processes but complements them. Connect Cloud is ideally suited for the ingestion phase of the data pipeline.
- If you want more complex ETL processing, you can use SSIS or ADF. You can use Connect Cloud in both as a source. There are connectors for both ADF and SSIS available, so your ETL tool of choice can use Connect Cloud to gain access to your cloud data. This is good news for SSIS developers since there haven't been many new connections made available in the past few years.
- If you want real-time ad-hoc access to a data source – typically not supported by ETL – then you can use Connect Cloud instead.
Power BI
- When you need Power BI to access data, but you don't have a gateway (or you don't want to install one) CData Connect Cloud can deliver the data.
- There's a certified connection in Power BI for CData Connect Cloud from Microsoft. This is an OData connection from Power BI to CData Connect Cloud.
Power Apps
- You can use CData Connect Cloud in Power Apps, Power Automate, or Azure Logic Apps (through the virtual SQL Server). Build new applications using your cloud data sources using the low code/ no code platforms.
- The use case "Snowflake to Power Apps" with CData is demonstrated in this YouTube video.
Cloud to Cloud Connectivity
- Access data from AWS, Office 365, or Google Cloud
Excel or Google Sheets
- You can download an Excel or Google Sheets plugin to access the data directly.
- With this plugin, you can connect your workbook to the CData Connect Cloud environment and access your cloud data sources live
- You have the option to build the query graphically or to write SQL code
- Write-back in Excel is supported; data can be pushed back into the source system
- You can get a walkthrough of this CData feature on YouTube, where they connect Google Sheets with Salesforce.
Configuring CData Connect Cloud Data Sources Example
Let's walk through a specific use case to see how the product works.
We're going to connect an Azure Logic App to a Snowflake database. With CData Connect Cloud, this problem is easily solved. To follow along with this example, use your existing accounts or download free trials for Snowflake, Azure, and CData Connect Cloud.
After registering and creating an account, a wizard will appear to guide you through the first connection.
Select Data Source
In Data Sources, select Snowflake, or type Snowflake in the Search field, then select it, as seen in the image below.
Add Connection
Next, provide the connection information for the Snowflake database.
You need to specify a name for the connection as well. This is important, as we will use it later in our SQL queries. As the authentication schema, we use Password since, in our use case, Snowflake doesn't utilize Azure AD (another authentication provider such as Okta might be implemented). After filling in and saving the information, Connect Cloud will automatically test the connection as it proceeds to the next step.
Select and Configure Client
Now, we need to select our client. Azure Logic Apps is currently not supported (but it is coming soon as a client tool in Connect Cloud), so let's use the virtual SQL Server to connect to Snowflake instead.
A personal access token (PAT) is generated in the last step. You will need to save this as it is necessary to create the connection later. You can always create new PATs using the Connect Cloud portal:
CData Management Interface
Once the connection is created, you'll proceed to the portal.
Overview
In the Overview tab, a dashboard is available at a glance to determine who has been querying which systems.
Connections
In the Connections tab, you can find the Snowflake connection just created:
Data Explorer
In the Data Explorer tab, you can preview the data using the SQL query language:
Logging
Another useful tab is Logs, where you can view all the executed queries:
The Audit Log provides more information about actions that have taken place in Connect Cloud itself, such as users that have been added, tokens that have been created, and so on.
Connect to Snowflake with CData SQL Server Linked Server Example
Now that we setup the CData data source as shown above (i.e. tds.cdata.com,14333), now let's connect our Logic App to the Snowflake database using the SQL Server Linked Server.
In the Logic App, add a new SQL Server action.
If we want to read data, there are two options: the Get Rows (V2) or the Execute a SQL query (V2).
Connecting to the CData SQL Server Linked Server
No matter the choice, you'll need to create a connection via the Linked Server that was setup in the prior section.
We need to use SQL Server authentication:
- Server name: tds.cdata.com,14333 (the main virtual SQL Server we mentioned earlier).
- Database name: The name of the connection,
- Username: Your CData Connect Cloud username
- Password: The token we generated earlier when the connection was created.
There's an option to use a gateway, but leave this blank.
When using the Get Rows action, select the table name (reuse the server and database name from the connection):
When the Logic App runs, it will fetch a single page with records from the table (pagination must be used if more is needed).
The output is a JSON file with data returned from the OData protocol.
Another option is to specify a SQL query. This allows more control over what is returned, like excluding columns or performing calculations. For demonstration purposes, it is wise to use a TOP clause to limit the number of rows returned.
In this case, the JSON output can be viewed directly in the action. While viewing the query history in Snowflake, we can see all the queries that Azure Logic Apps has sent to the database:
As you might have noticed, the query was run once without the TOP clause, which returned almost 85 million rows. When you use the TOP clause, CData Connect Cloud translates it to a Snowflake query with the LIMIT operator (since TOP is not supported in Snowflake):
CData Connect Cloud Value
The CData Connect Cloud offers many benefits:
- Data Access: All data access is via a familiar SQL Server Linked Server.
- Centralized Governance: Manage all your cloud connections in 1 place.
- Familiar SQL Language: Data sources can be accessed with SQL code without knowing the table structures of the source system or the API
- Connectivity:
There are three types of connectivity options based on the data source functionality:
- REST API
- OData Web Service
- Virtual SQL Server
- Time Savings by Simplification: Some data sources have APIs, and learning to work with each one is time-consuming. When you only want to move raw data, go through an ETL process and report on that data, CData standardizes this process regardless of the source system.
- Real Time: Real-time data access
- Multiple Data Sources: Access multiple sources and return a single result set
- Security: Multi-level security
- Centralized Logging of All Issued Queries: A dashboard shows the queries by connection, the number of queries over time, the number of rows transferred, etc.
- Software-as-a-Service: There are no software updates to run; it is taken care of for you. Everything is centralized and managed in one place.
- Data Sources: Below is an image with some of the available data sources:
Conclusion
CData Connect Cloud offers a centralized and secure environment for managing all your cloud data sources. It provides a direct connection from the client tool to the data source via a SQL Server Linked Server, such as a live connection from Google Sheets to Salesforce. You can use the familiar SQL language to query cloud data sources, creating a single data set over different clouds.
With CData Connect Cloud, managing all your cloud connections is simplified, including the users who need access to these sources and their credentials. The CData portal offers a user-friendly dashboard to track how many queries were issued and how many rows of data were transferred.
Additionally, CData Connect Cloud is a software-as-a-service product, providing stress-free software or driver updates handled for you.
Download the free trial to test drive CData Connect Cloud.
Next Steps
- To learn more about CData Connect Cloud, check out their Getting Started guide.
- Check out another YouTube video where they integrate Tableau Online with Couchbase.
- Learn about Universal Data Integration Tools from CData
MSSQLTips.com Product Spotlight sponsored by CData.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2023-12-21
