Forem: Jerod Johnson

Analyze Live Salesforce Data in Infragistics Reveal

Jerod Johnson — Fri, 12 Nov 2021 16:44:35 +0000

Infragistics Reveal is a data visualization solution that simplifies the way you add embedded analytics and visualizations to your apps. Reveal supports connecting to OData APIs, but many enterprise APIs are not built using the OData standard. By combining Reveal with CData solutions, you can instantly build dynamic dashboards from live enterprise data sources like Salesforce.

With access to live data, you can build real-time, responsive dashboards that retrieve new data points with every refresh. CData Connect Cloud and API Server let you generate standard data APIs like OData for all your data, offering out-of-the-box connectivity to tools like Reveal.

In this article, we walk through how to connect to Salesforce to Infragistics Reveal to create a simple dashboard, leveraging CData Connect Cloud and CData API server to facilitate the connection.

Why CData?

Both CData Connect Cloud and CData API Server provide a single point of contact for all your enterprise data through standard data interfaces like SQL and OData. Instead of migrating data from dozens of sources to a static data store or writing your own integrations, simply point your tools to Connect Cloud or API Server and get instant, live access to all your data.

Why Connect Cloud?

As a cloud-based integration platform, Connect Cloud is ideal for working with cloud apps that have embedded Reveal visualizations. With no servers to configure or data proxies to set up, you can simply use the Web-based UI to create a virtual database for Salesforce, generate an OData API, and connect from Reveal to start building dashboards based on live Salesforce data.

Why CData API Server?

CData API Server can be installed on-prem or in a self-hosted environment enabling you to connect to data from on-prem or self-hosted apps built on Reveal dashboards. Create an OData endpoint for Salesforce using the straightforward point-and-click interface and connect from Reveal to create dynamic Salesforce visualizations.

Getting Started: Salesforce Connection Properties

To work with live Salesforce data in Reveal, you need to authenticate with Salesforce. There are several authentication methods available for connecting to Salesforce: Login, OAuth, and SSO. The Login method requires you to have the username, password, and security token of the user.

If you do not have access to the username and password or do not wish to require them, you can use OAuth authentication.

Use SSO (single sign-on) by setting the SSOProperties, SSOLoginUrl, and TokenUrl connection properties, which allow you to authenticate to an identity provider. See the "Getting Started" chapter in the help documentation for more information.

Using CData Connect Cloud

To connect to your Salesforce data in Connect Cloud and enable OData access, navigate to the web interface for your Connect Cloud instance (https://www.cdatacloud.net/myinstance). Follow the steps below to configure Connect Cloud. (Jump to building a dashboard.)

Add a Connect Cloud User

Create a User to connect to Salesforce from Reveal through Connect Cloud.

Click Users -> Add
Configure a User

Click Save Changes and make a note of the Authtoken for the new user

Connect to Salesforce from Connect Cloud

CData Connect Cloud uses a straightforward, point-and-click interface to connect to data sources and generate APIs.

Open Connect Cloud and click Databases

Select "Salesforce" from Available Data Sources
Enter the necessary authentication properties to connect to Salesforce (see above)

Click Test Database
Click Privileges -> Add and add the new user (or an existing user) with the appropriate permissions (SELECT is the only required permission for Reveal)

Add Salesforce OData Endpoints in Connect Cloud

After connecting to Salesforce, create OData Endpoint for the desired table(s).

Click OData -> Tables -> Add Tables

Select the Salesforce database

Select the table(s) you wish to work with and click Next

(Optional) Edit the resource to select specific fields and more
Save the settings

(Optional) Configure Cross-Origin Resource Sharing (CORS)

When accessing and connecting to multiple different domains from an application such as Ajax, there is a possibility of violating the limitations of cross-site scripting. In that case, configure the CORS settings in OData -> Settings.

Enable cross-origin resource sharing (CORS): ON
Allow all domains without '*': ON
Access-Control-Allow-Methods: GET, PUT, POST, OPTIONS
Access-Control-Allow-Headers: Authorization

Save the changes to the settings.

Using API Server

To connect to your Salesforce data in API Server and enable OData access, navigate to the web interface for your API Server instance (https://localhost:8153). Follow the steps below to configure the API Server. (Jump to building a dashboard.)

Connect to Salesforce from API Server

CData API Server uses a straightforward, point-and-click interface to connect to data sources and generate APIs.

Open API Server and click Settings -> Connection -> Add Connection

Select "Salesforce"

Enter the necessary authentication properties to connect to Salesforce (see above)

Add Salesforce Resource Definitions in API Server

After connecting to Salesforce, create Resources, which represent API endpoints for Salesforce data.

Click Settings -> Resources -> Add Resource

Select the Salesforce connection

Select the table you wish to retrieve and click Next

(Optional) Edit the resource to select specific fields and more
Save the settings

Add an API Server User

Create a User to connect to Salesforce from Reveal through API Server.

Click Settings -> Users
Click Add
Configure a User with access to the Salesforce Connection and Resource(s)

(Optional) Configure Cross-Origin Resource Sharing (CORS)

Enable cross-origin resource sharing (CORS): ON
Allow all domains without '*': ON
Access-Control-Allow-Methods: GET, PUT, POST, OPTIONS
Access-Control-Allow-Headers: Authorization

Save the changes to the settings.

Create a Dashboard in Reveal

With the API Server configured, we can visualize Salesforce data in Reveal.

Log into Reveal and click Dashboards -> New

Click Data Source -> OData Feed

Specify the API Server or Connect Cloud API endpoint URL, for example, https://serverurl/api.rsc

Select Generic Credentials and specify the API Server username and authentication token

Select the entity you wish to visualize

Select fields and choose a chart type

More Information

Companies everywhere are looking to leverage their data to make effective decisions and grow their businesses.

By providing industry-standard connectivity, both CData Connect Cloud and CData API Server democratize Salesforce access. Now everyone in the organization, from IT professionals & app developers to citizen analysts & data scientists can work with and find value from the entire Salesforce platform.

Editing API Driver API Profiles

Jerod Johnson — Fri, 12 Nov 2021 15:28:44 +0000

RESTful APIs offer a straightforward way for businesses to work with external data and offer access to their own data. With more than 24,000 public APIs providing access to limitless data and the typical enterprise leveraging more than 200 applications, teams and developers need more efficient ways to query API data. With the CData API Driver, it is easier than ever to integrate with APIs at scale for data analytics and application development.

Why use the API Driver?

Use simple SQL to access and work with data anywhere there’s an API. Like all CData Drivers, the CData API Driver makes it easier to work with your data. With the API Driver and the available API Profiles, you can instantly and codelessly query dozens of APIs (including any APIs built using the CData API Server) from BI, data integration, and custom applications.

Thanks to its extensible design, organizations and developers can modify API Profiles from API Server to customize integrations and even create their own API Profiles. Easily expand the connectivity offered by API Driver to any RESTful API.

Editing an API Profile

By default, existing API Profiles grant read access to APIs. We can edit the API Profiles to gain write access to APIs (where supported).

Unzip the Existing Profile

API Profiles (.apip files) are compressed folders that contain a collection of schema files representing the endpoints available for the API. When you decompress (unzip) the file, you can edit the schema files individually to extend the functionality.

Open the Schema File

Once the Profile is decompressed, open the schema file for the API endpoint you wish to modify (in your preferred text editor). Each schema file has several parts that define how SQL access is enabled for an API endpoint.

api:info: This keyword maps API fields to table columns through scripted column definitions.
attr: This element represents a column definition (details are below).
api:set attr="...": This keyword (attribute) sets various parameters for the API integration, including paging functionality and information on how to parse the API response (via the RepeatElement attribute).
api:script method="...": This keyword defines how read and write functionality is implemented for the API endpoint, including which internal operation is called and how specific functionality like filtering is managed.

Below are the full contents of a the schema file for retrieving Zoom Meetings.

Meetings.rsd

Update Column Definitions

If we are going to implement write functionality, we need to modify the schema to allow write permissions to the appropriate columns. Before we update a column definition for the Profile, let's examine a single column definition and discuss the different attributes.

Id Column Definition

Column Definition Attributes

name: The name of the column in the SQL interface for the API endpoint
xs:type: The data type associated with the column (e.g.: string, datetime, int, etc.)
readonly: Whether the column allows writes (by default, this is always true)
key: Whether a column is intended to be a unique identifier for the elements in a table/view
other:xPath: The path (exact or relative to theRepeatElement) in the API response to the column value

We can see from the schema file that most of the columns (which correspond to various API fields) are flagged as read-only. The first edit we need to make is to change those columns so the API Driver can write to them (based on the API specification) by removing the read-only flags:

...

Add INSERT Functionality

In this section, we walk through modifying the Schema file to support INSERT statements like the following:

INSERT INTO Meetings

(Topic,Type,StartTime,Duration,TimeZone,UserId)

VALUES

('Test Meeting',2,'2019-12-31T00:00:00',60,'EST','user@domain.com')

NOTE: Topic, Type, StartTime, Duration, and TimeZone are all columns in our schema. The UserId attr is a pseudocolumn, which behaves like a table column but is not actually stored in the table. In this case, the UserId is used to POST the new meeting to a specific user account.

In the schema file, the SELECT functionality is implemented in the element:

api:push/

/api:call

/api:script

To implement INSERT functionality, we need to modify the element, setting the method attribute value to "POST", setting the input values in a data attribute and calling the appropriate operation (apisadoExecuteJSONGet in this case).

Setting the method Attribute

/api:script

Setting the data Attribute

The Zoom Meetings API endpoint allows you to create a new meeting by submitting a JSON object with various fields for the new meeting. Values from an INSERT statement are mapped to corresponding fields in an _input object and are used to create the data attribute, which will be POSTed to the Zoom API.

{

"topic": "[_input.Topic]",

"type": [_input.Type],

"start_time": "[_input.StartTime]",

"duration": "[_input.Duration]",

"timezone": "[_input.TimeZone]"

}

/api:set

Calling the apisadoExecuteJSONGet Operation

Once the data attribute is set, call the apisadoExecuteJSONGet operation using an api:call keyword and push the operation to the API Driver with an api:push keyword.

...

api:push

/api:push /api:call

/api:script

Using the Edited Profile

With the edits made to the schema file, you are ready to start INSERTing Meetings to a Zoom account. Save the schema file, recompress (zip) the schema files, and change the extension of the compressed file to .apip. Use an application that supports standard connectivity to connect to the API Profile using the API Driver, then submit your INSERT statement.

INSERT Statement

INSERT INTO Meetings

(Topic,Type,StartTime,Duration,TimeZone,UserId)

VALUES

('Test Meeting (Edited Profile)',2,'2019-12-31T00:00:00',60,'EST','user@domain.com')

Meeting in Zoom

More Information

The CData API Drivers simplify data connectivity for a wide range of popular data tools. Connect BI, Reporting, & ETL tools to live data from any application, database, or Web API. Built on the same robust SQL engine that powers other CData Drivers, the CData API Driver enables simple codeless query access to APIs through a single client interface.

Creating API Driver API Profiles

Jerod Johnson — Fri, 12 Nov 2021 14:29:52 +0000

Why use the API Driver?

This article shows how to create a new API Profile to enable SQL access to a new API.

Creating an API Profile

API Profiles grant SQL access to APIs. While there are dozens of APIs supported out of the box, you are also able to create a new Profile to enable SQL access to any API.

Create a Schema File (.rsd)

API Profiles contain a collection of schema files that represent the endpoints available for the API. For this article, we will start a Profile for the TripPin OData v4 API by creating a schema file for the People endpoint. Each schema file has several parts that define how SQL access is enabled for an API endpoint.

api:info: This keyword maps API fields to table columns through scripted column definitions.
attr: This element represents a column definition (details are below).
api:set attr="...": This keyword (attribute) sets various parameters for the API integration, including paging functionality and information on how to parse the API response (via the RepeatElement attribute).
api:script method="...": This keyword defines how read and write functionality is implemented for the API endpoint, including which internal operation is called and how specific functionality like filtering is managed.

Start by creating a new text file called People.rsd. From here, we walk through creating each section of the schema file, based on the API specification.

Create Column Definitions

API Driver schema files enable SQL access to API endpoints, and this starts with creating column definitions for corresponding API fields for the given endpoint, using API Script keywords and other functionality. An api:script keyword contains the entire schema definition. An api:info keyword provides the table name & description and contains the column definitions, where each API field is mapped to a table with an attr (attribute) element.

The People endpoint of our API returns a series of people, where each entry is represent by a JSON object similar to the following:

{
"UserName" : "russellwhyte",
"FirstName" : "Russell",
"LastName" : "Whyte",
"MiddleName" : null,
"Gender" : "Male",
"Age" : null,
"Emails" : ["Russell@example.com","Russell@contoso.com"],
"FavoriteFeature" : "Feature1",
"Features" : ["Feature1","Feature2"],
"AddressInfo" : [
{ "Address" : "187 Suffolk Ln.",
"City":{
"Name" : "Boise",
"CountryRegion" : "United States",
"Region" : "ID"
}
}],
"HomeAddress" : null}

We can using path definitions based on the JSON structure to drill down into each of the values in the response, effectively flattening the response into a SQL table model. Create the column definitions based on the API specification and inferred information based on the response. An explanation of the column definition follows.

/api:info

...

Column Definition Attributes

name: The name of the column in the SQL interface for the API endpoint
xs:type: The data type associated with the column (e.g.: string, datetime, int, etc.)
readonly: Whether the column allows writes (by default, this is always true)
key: Whether a column is intended to be a unique identifier for the elements in a table/view

other:xPath: The path (exact or relative to theRepeatElement) in the API response to the column value

Specific Columns

Here, we examine specific column definitions and explain how the different attributes create the SQL mapping for the API fields.

Column	Featured Attribute
UserName	key
ALL	readonly
ALL	xs:type
Feature1	other:xPath
City	other:xPath

Meaning of key: Signifies UserName as a unique identifier for the table
Meaning of readonly: Determines whether a column can be modified or not
Meaning of xs:type: Sets the SQL datatype (based on API specification or data model)
Meaning of other:xPath: The array index [0] indicates to pull the first entry in the Features JSON array
Meaning of other:xPath: Drills into the AddressInfo JSON object to expose the city name

Add Global Parameters

After creating the column definitions, we need to set the global parameters for integrating with the API, including the API endpoint to request data from, any required values for connecting, specific fields or headers for the API request, and the repeated element in the API response that represents individual entries for the API endpoint.

...

Add Read/Write Functionality

With the columns and global parameters defined, we can complete the schema file by scripting the read and write functionality. SELECT functionality is implemented in the keyword, setting the HTTP method to GET and calling the apisadoExecuteJSONGet operation to retrieve and process the data.

api:push/

/api:call

/api:script

To implement INSERT / UPDATE / DELETE functionality, we need to add additional api:script elements with the POST, MERGE, and DELETE methods and implement the specific functionality with further scripting. For this article, we will only implement SELECT functionality and throw an error message if we try to write to the API endpoint.

Setting the method Attribute

...

/api:throw /api:script

...

With the API functionality implemented, we can use the profile with the API Driver in any tool or application that supports JDBC or ADO.NET connectivity, granting SQL access to the API.

Using the Profile in DbVisualizer

The API Driver has two connection properties used to connect to an API:

Profile: The folder containing the schema files for your API.
ProfileSettings: A semi-colon separated list of name-value pairs for connection properties required by your chosen Profile — since we are connecting to an open API, we can leave this property blank.

Because we are connecting in DbVisualizer, we will configure a JDBC connection string to connect to the API through the Profile:

jdbc:apis:Profile=/PATH/TO/TripPin/;

In DbVisualizer, we create a new Connection using the API Driver and set the Database URL to the connection string. From there, we can connect to the Profile, expand the data model, and explore the data in the People "table" (which represents the People API endpoint).

More Information

The CData API Drivers simplify data connectivity for a wide range of popular data tools. Connect BI, Reporting, & ETL tools to live data from any application, database, or Web API. Built on the same robust SQL engine that powers CData Drivers, the CData API Driver enables simple codeless query access to APIs through a single client interface.

Access All Your Data in Google Data Studio with CData Connect Cloud

Jerod Johnson — Fri, 12 Nov 2021 11:24:24 +0000

Google Data Studio helps you create comprehensive reports and data visualizations. When combined with CData Connect Cloud, you get instant, cloud-to-cloud access to data from supported SaaS, Big Data and NoSQL sources for visualizations, dashboards, and more.

CData Connect Cloud provides a live cloud-to-cloud interface for all your data, making it easy to build reports from live SaaS, Big Data and NoSQL sources in Google Data Studio — without replicating the data to a natively supported database. As you build visualizations, Google Data Studio generates SQL queries to gather data. Using optimized data processing out of the box, CData Connect Cloud pushes all supported SQL operations (filters, JOINs, etc) directly to the underlying data source, leveraging server-side processing to quickly return the requested data.

This article shows how to use the CData Connect Partner Connector to connect to your Connect Cloud instance from Google Data Studio.

User Guide

The CData Connect Partner Connector gives you the ability to connect directly data to Google Data Studio through the CData Connect Cloud.

Grant Access

Before using CData Connect Partner Connector, you will need to grant access to your Google Account. If you see this prompt, please click "Allow".

Connect to CData Connect Cloud

You will need your instance name, username, and password to establish a connection to Connect Cloud. Your instance name is found in the web UI address or MySQL/SQL Server host address for your Connect Cloud instance. For example, if your web UI address is https://www.cdatacloud.net/my_instance and MySQL/SQL Server host address is my_instance.cdatacloud.net, your instance name is my_instance.

Set username and password to the credentials for a Connect Cloud user for your instance with appropriate roles and privileges to access the desired data.

When finished setting up credentials in Connect Cloud, enter your credentials information in the connector authentication panel.

The format for Username here should be [instance name]/[user name]. For example, if your instance name is ustest1, and you have the user admin set in Connect Cloud, you should enter ustest1/admin. Password is be the user's password.

When you finish entering the information, click "SUBMIT."

Select a Database

After you've successfully established the connection, select a database you want to use and click "NEXT."

Select a Table

Then, select a table, and click "NEXT."

Explore the Data

If you see the message "Click "CONNECT" to continue," it means the configuration information is correct. You can hit the "CONNECT" button at the top right of the screen. You should not modify any configuration information at this time, or it may cause a connection error.

At this step, you can set the fields and their metadata. Common data types are automatically detected, but you can also change them to something else. When you are content with the schema, you can start generating your report.

Learn More

As more businesses move toward data-driven digital transformation initiatives, lines of business are increasingly demanding access to their data to acquire actionable insights into performance and business health.

CData Connect is a consolidated connectivity platform that lets you easily connect any application, on-prem or in the cloud, with real-time data from anywhere. Unify access to all the cloud applications, databases, APIs, and services you use across your organization.

Run Queries Across Many Data Sources at Once with the CData Query Federation Driver

Jerod Johnson — Fri, 12 Nov 2021 10:46:32 +0000

The CData Query Federation Driver, allows you to federate and aggregate data across drivers as a single database connection. You can combine the Query Federation Driver with other CData JDBC Drivers or CData ADO.NET Providers to access all your SaaS, Big Data, and NoSQL sources as if they were one database. You can query or update data from a single source and even execute complex queries that JOIN data across sources.

In this article, we will walk through the Query Federation Driver configuration to connect to multiple data sources. We will be looking at the JDBC drivers for Salesforce, MySQL, and Excel, but the principles for configuring the CData Query Federation Driver extend to all 250+ data sources that CData supports.

Download and Configure the Query Federation Driver

Start working with all your data from a single endpoint by installing the Query Federation Driver along with CData drivers for your specific data sources.

Like any other database driver, the Federation Driver connects to data using connection string properties. There are two key connection properties for the Query Federation Driver:

Database Configuration: Defines the location of the JSON document that defines which CData drivers the Query Federation Driver uses to connect to data. Typically: %AppData%/Roaming/CData/Query Federation Data Provider/DatabaseConfiguration.json
Default Schema: Which of the database schema defined in the Database Configuration document to use by default

The Database Configuration document contains JSON objects with fields for information about the drivers available to the Query Federation Driver. Each JSON object represents a database driver, and its name represents the schema name, like salesforcedb or mysqldb. The fields and values in the objects vary depending on whether you are using a Java-based Query Federation Driver (i.e. JDBC) or a C#-based Query Federation Driver (i.e. ADO.NET).

JDBC Schema Fields

driverClass: The name of the JDBC class for the driver, (e.g. cdata.jdbc.salesforce.SalesforceDriver)
connectionUri: The JDBC URL to connect to data, (e.g. jdbc:cdata:salesforce:User=user@domain.com;Password=password;)
driverPath: The path on disk to the location of the driver file, (e.g. C:\Program Files\CData\CData JDBC Driver for Salesforce\lib\cdata.jdbc.salesforce.jar)

Sample DatabaseConfiguration.json File (JDBC)

{

"salesforcedb": {

"driverClass": "cdata.jdbc.salesforce.SalesforceDriver",

"connectionUri": "jdbc:cdata:salesforce:User=user@domain.com;Password=password;",

"driverPath": "/PATH/TO/cdata.jdbc.salesforce.jar"

"exceldb": {

"driverClass": "cdata.jdbc.excel.ExcelDriver",

"connectionUri": "jdbc:cdata:excel:Excel File=/PATH/TO/MyExcelFile.xlsx;",

"driverPath": "/PATH/TO/cdata.jdbc.excel.jar"

"mysqldb": {

"driverClass": "cdata.jdbc.mysql.MySQLDriver",

"connectionUri": "jdbc:mysql:Server=localhost;Database=test;User=root;Password=pwd",

"driverPath": "/PATH/TO/cdata.jdbc.mysql.jar"

}

ADO.NET Schema Fields

providerName: The name of the ADO.NET class for the Provider, (e.g. System.Data.CData.Salesforce)
connectionString: The connection string used to connect to data, (e.g. User=user@domain.com;Password=password;)
providerPath: The path on disk to the location of the driver file, (e.g. C:\Program Files\CData\CData ADO.NET Driver for Salesforce\lib\System.Data.CData.Salesforce.dll)

Sample DatabaseConfiguration.json File (ADO)

{

"salesforcedb": {

"providerName": "System.Data.CData.Salesforce",

"connectionString": "User=user@domain.com;Password=password;",

"providerPath": "/PATH/TO/System.Data.CData.Salesforce.dll"

"exceldb": {

"providerName": "System.Data.CData.Excel",

"connectionString": "Excel File=/PATH/TO/MyExcelFile.xlsx;",

"providerPath": "/PATH/TO/System.Data.CData.Excel.dll"

"mysqldb": {

"providerName": "System.Data.CData.MySQL",

"connectionString": "Server=localhost;Database=test;User=root;Password=pwd",

"providerPath": "/PATH/TO/System.Data.CData.MySQL.dll"

}

Sample Query Federation Driver Connections

To connect to the Query Federation Driver from a JDBC-capable tool or application, simply create the JDBC URL for the Query Federation Driver:

JDBC URL

jdbc:queryfederation:DatabaseConfiguration=/PATH/TO/DatabaseConfiguration.json;DefaultSchema=salesforcedb

For an ADO.NET-capable tool, create the connection string for the Query Federation Driver:

ADO.NET Connection String

DatabaseConfiguration=/PATH/TO/DatabaseConfiguration.json;DefaultSchema=salesforcedb

With the Query Federation Driver configured, we can connect to and begin working with data from multiple sources as if they were in a single database. Below, you can see the database design based on the above schema settings file, as displayed in DBVisualizer.

Query Federation Driver Schema Layout (in DBVisualizer)

More Information

Working with data across sources, whether from SaaS, Big Data, NoSQL, or even flat-file sources is invaluable to the data-driven organization. Developers can pick multiple data processing systems and access all of them with a single SQL-based interface.

The CData Query Federation Driver offers a single, uniform experience with all your data, no matter where your data is housed or generated. The CData Query Federation Driver provides a universal data access layer that simplifies application development and data access. Using the Query Federation Driver, analysts can query data across systems through a common driver interface — all with standard SQL.

CData AWS Glue Connector for Salesforce Deployment Guide

Jerod Johnson — Fri, 05 Nov 2021 14:55:18 +0000

AWS Glue is an ETL service from Amazon that enables you to prepare and load your data for storage and analytics. With Glue Studio, you can create no-code and low-code ETL jobs that work with data through CData Glue Connectors.

In this article, we walk through configuring the CData Glue Connector for Salesforce to create and run an AWS Glue job using live Salesforce data.

Typical Customer Deployment

The CData AWS Glue Connector for Salesforce is a custom Glue Connector that makes it easy to transfer data from SaaS applications and custom data sources to your data lake in Amazon S3. Customers can subscribe to the Connector from the AWS Marketplace and use it in their AWS Glue jobs and deploy them into their product Apache Spark applications that run on AWS Glue.

The Glue Connector for Salesforce can be deployed to any region and can be subscribed to and deployed in an AWS Glue job in just a few minutes.

Prerequisites and Requirements

There are no external operating system, database type, or storage requirements for using the CData AWS Glue Connector for Salesforce. Customers will need familiarity with AWS Glue, AWS Glue Studio, and Python/Apache Spark to best utilize the Glue Connector for Salesforce. Customers will need an AWS account and a subscription to the AWS Glue Connector. AWS Glue and Glue Studio jobs run on Amazon EC2 instances; the CData AWS Glue Connector is a container image that runs on Amazon ECS; and the sample Glue job in this walkthrough stores data in Amazon S3.

Architecture

The CData AWS Glue Connector for Salesforce is an Amazon ECR image that is used from Amazon Glue Jobs to read and write data from the Salesforce service.

Update Permissions for your IAM Role

When you create the AWS Glue job, you specify an AWS Identity and Access Management (IAM) role for the job to use. The role must grant access to all resources used by the job, including Amazon S3 for any sources, targets, scripts, temporary directories, and AWS Glue Data Catalog objects. The role must also grant access to the CData Glue Connector for Salesforce from the AWS Glue Marketplace.

NOTE: Do not use the root user for any deployments or operations.

The following policies should be added to the IAM role for the AWS Glue job, at a minimum:

AWSGlueServiceRole (For accessing Glue Studio and Glue Jobs)
AmazonEC2ContainerRegistryReadOnly (For accessing the CData AWS Glue Connector for Salesforce)

If you will be accessing data found in Amazon S3, add:

AmazonS3FullAccess (For reading from and writing to Amazon S3)

And lastly, if you will be using AWS Secrets Manager to store confidential connection properties (see more below), you will need to add an inline policy similar to the following, granting access to the specific secrets needed for the Glue Job:

{

"Version": "2012-10-17",

"Statement": [

{

"Effect": "Allow",

"Action": [

"secretsmanager:GetResourcePolicy",

"secretsmanager:GetSecretValue",

"secretsmanager:DescribeSecret",

"secretsmanager:ListSecretVersionIds"

"Resource": [

"arn:aws:secretsmanager:us-west-2:111122223333㊙️aes128-1a2b3c",

"arn:aws:secretsmanager:us-west-2:111122223333㊙️aes192-4D5e6F",

"arn:aws:secretsmanager:us-west-2:111122223333㊙️aes256-7g8H9i"

]

}

]

}

For more information about granting access to AWS Glue Studio and Glue Jobs, see Setting up IAM Permissions for AWS Glue in the AWS Glue documentation.

For more information about granting access to the Amazon S3 buckets, see Identity and access management in the Amazon Simple Storage Service Developer Guide.

For more information on setting up access control for your secrets, see Authentication and Access Control for AWS Secrets Manager in the AWS Secrets Manager documentation and Limiting Access to Specific Secrets in the AWS Secrets Manager User Guide. The credential retrieved from AWS Secrets Manager (a string of key-value pairs) is used in the JDBC URL used by the CData Glue Connector when connecting to the data source, as shown above.

For more general information on IAM and IAM best practices, refer to theAWS IAM page.

Collect Salesforce Connection Properties

There are several authentication methods available for connecting to Salesforce: Login, OAuth, and SSO. The Login method requires you to have the username, password, and security token of the user.

If you do not have access to the username and password or do not wish to require them, you can use OAuth authentication.

SSO (single sign-on) can be used by setting the SSOProperties, SSOLoginUrl, and TokenUrl connection properties, which allow you to authenticate to an identity provider. See the "Getting Started" chapter in the help documentation for more information.

OAuth Verifier

Salesforce supports connecting via OAuth. To connect using OAuth, you need to follow the Headless OAuth instructions in the Help documentation for the Connector and save the OAuth Verifier code.

Make a note of the necessary properties for use with the CData Glue Connector for Salesforce.

(Optional) Store Salesforce Connection Properties Credentials in AWS Secrets Manager

To safely store and use your connection properties, you can save them in AWS Secrets Manager.

Note: You must host your AWS Glue ETL job and secret in the same region. Cross-region secret retrieval is not supported currently.

Sign in to the AWS Secrets Manager console.
On either the service introduction page or the Secrets list page, choose Store a new secret.
On the Store a new secret page, choose Other type of secret. This option means you must supply the structure and details of your secret.

You can read more about the required properties to connect to Salesforce in the "Activate" section below. Once you know which properties you wish to store, create a key-value pair for each property. For example:

Username: account user (for example, user@example.com)
Password: account password
Add any additional private credential key-value pairs required by the CData Glue Connector for Salesforce
For more information about creating secrets, see Creating and Managing Secrets with AWS Secrets Manager in the AWS Secrets Manager User Guide.

Record the secret name, which is used when configuring the connection in AWS Glue Studio.

Subscribe to the CData Glue Connector for Salesforce

To work with the CData Glue Connector for Salesforce in AWS Glue Studio, you need to subscribe to the Connector from the AWS Marketplace. If you have already subscribed to the CData Glue Connector for Salesforce, you can jump to the next section.

Pricing Information

Monthly Subscription Fee: $300.00

You are charged $300.00 once a month regardless of how many instances you launch after subscribing.

Use of Local Zones or WaveLength infrastructure deployment may alter your final pricing.

Navigate to AWS Glue Studio
Click Connectors
Click AWS Marketplace
Search for the Connector "CData Salesforce"

Click "Continue to Subscribe"
Accept the terms for the Connector and wait for the request to be processed
Click "Continue to Configuration"

Activate the CData Glue Connector for Salesforce in Glue Studio

To use the CData Glue Connector for Salesforce in AWS Glue, you need to activate the subscribed connector in AWS Glue Studio. The activation process creates a connector object and connection in your AWS account.

Once you subscribe to the connector, a new Config tab shows up in the AWS Marketplace connector page.
Choose the delivery options and click the "Continue to Launch" button.

On the launch tab, click "Usage Instructions" and follow the link that appears to create and configure the connection.

Under Connection access, select the JDBC URL format and configure the connection. Below you will find sample connection string(s) for the JDBC URL format(s) available for Salesforce. You can read more about authenticating with Salesforce in the Help documentation for the Connector.
If you opted to store properties in the AWS Secrets Manager, leave the placeholder values (e.g. ${Property1}), otherwise, the values you enter in the AWS Glue Connection interface will appear in the (read-only) JDBC URL below the properties.

Username & Password

jdbc:cdata:Salesforce:AuthScheme=BASIC;User=${Username};Password=${Password};SecurityToken=${SecurityToken}

OAuth

jdbc:cdata:Salesforce:AuthScheme=OAuth;OAuthSettingsLocation=${OAuthSettingsLocation};InitiateOAuth=REFRESH;OAuthVerifier=${OAuthVerifier};OAuthClientID=${OAuthClientID};OAuthClientSecret=${OAuthClientSecret}

OneLogin

jdbc:cdata:Salesforce:AuthScheme=OneLogin;SSOLoginUrl=${SSOLoginUrl};SSOTokenUrl=${SSOTokenUrl};SSOProperties='IdPName=OneLogin;APIKey=${OneLoginAPIKey}'

PingFederate

jdbc:cdata:Salesforce:AuthScheme=PingFederate;SSOLoginUrl=${SSOLoginUrl};SSOTokenUrl=${SSOTokenUrl};SSOProperties='IdPName=PingFederate;RelyingParty=${SalesforceDomain}'

OKTA

jdbc:cdata:Salesforce:AuthScheme=OKTA;SSOLoginUrl=${SSOLoginUrl};SSOTokenUrl=${SSOTokenUrl};SSO+Properties='idpname=okta;domain=${OrgDomain};apiToken=${OktaAPIKey};'

ADFS

jdbc:cdata:Salesforce:AuthScheme=ADFS;InitiateOAuth=REFRESH;OAuthClientId=${OAuthClientID};OauthClientSecret=${OAuthClientSecret};SSOProperties='IDPName=AzureAD;Resource=${SalesforceApplicationIDURI};Tenant=${AzureADTenant};';SSOTokenUrl=${SSOTokenURL};

(Optional): Enable logging for the Connector.
If you want to log the functionality from the CData Glue Connector for Salesforce you will need to append two properties to the JDBC URL:

Logfile: Set this to "STDOUT://"
Verbosity: Set this to an integer (1-5) for varying depths of logging. 1 is the default, 3 is recommended for most debugging scenarios.

Configure the Network options and click "Create Connection."

Configure the Amazon Glue Job

Once you have configured a Connection, you can build a Glue Job.

Create a Job that Uses the Connection

In Glue Studio, under "Your connections," select the connection you created

Click "Create job"
The visual job editor appears. A new Source node, derived from the connection, is displayed on the Job graph. In the node details panel on the right, the Source Properties tab is selected for user input.

Configure the Source Node properties:

You can configure the access options for your connection to the data source in the Source properties tab. Refer to the AWS Glue Studio documentation for more information. Here we provide a simple walk-through.

In the visual job editor, make sure the Source node for your connector is selected. Choose the Source properties tab in the node details panel on the right, if it is not already selected.
The Connection field is populated automatically with the name of the connection associated with the marketplace connector.
Enter information about the data location in the data source. Provide either a source table name or a query to use to retrieve data from the data source. An example of a query is SELECT Industry, AnnualRevenue FROM Account WHERE Name = 'GenePoint'.

To pass information from the data source to the transformation nodes, AWS Glue Studio must know the schema of the data. Select "Use Schema Builder" to specify the schema interactively.
Configure the remaining optional fields as needed. You can configure the following:

Partitioning information - for parallelizing the read operations from the data source
Data type mappings - to convert data types used in the source data to the data types supported by AWS Glue
Filter predicate - to select a subset of the data from the data source
See "Use the Connection in a Glue job using Glue Studio" for more information about these options.

You can view the schema generated by this node by choosing the Output schema tab in the node properties panel.

Edit, Save, & Run the Job

Edit the job by adding and editing the nodes in the job graph. See Editing ETL jobs in AWS Glue Studio for more information.

After you complete editing the job, enter the job properties.

Select the Job properties tab above the visual graph editor.
Configure the following job properties when using custom connectors:

Name: Provide a job name.
IAM Role: Choose (or create) an IAM role with the necessary permissions, as described previously.
Type: Choose "Spark."
Glue version: Choose "Glue 2.0 - Supports spark 2.4, Scala 2, Python 3."
Language: Choose "Python 3."

Use the default values for the other parameters. For more information about job parameters, see "Defining Job Properties" in the AWS Glue Developer Guide.

At the top of the page, choose "Save."
A green top banner appears with the message "Successfully created Job."
After you successfully save the job, you can choose "Run" to run the job.
To view the generated script for the job, choose the "Script" tab at the top of the visual editor. The "Job runs" tab shows the job run history for the job. For more information about job run details, see "View information for recent job runs."

Review the Generated Script

At any point in the job creation, you can click on the Script tab to review the script being created by Glue Studio. If you create a simple job to write Salesforce data to an Amazon S3 bucket, your script will look similar to the following:

Sample Script

import sys

from awsglue.transforms import *

from awsglue.utils import getResolvedOptions

from pyspark.context import SparkContext

from awsglue.context import GlueContext

from awsglue.job import Job

@params: [JOB_NAME]

args = getResolvedOptions(sys.argv, ['JOB_NAME'])

sc = SparkContext()

glueContext = GlueContext(sc)

spark = glueContext.spark_session

job = Job(glueContext)

job.init(args['JOB_NAME'], args)

@type: DataSource

@args: [connection_type = "marketplace.jdbc", connection_options = {"dbTable":"Account","connectionName":"cdata-[id]"}, transformation_ctx = "DataSource0"]

@return: DataSource0

@inputs: []

DataSource0 = glueContext.create_dynamic_frame.from_options(connection_type = "marketplace.jdbc", connection_options = {"dbTable":"Account","connectionName":"cdata-[id]"}, transformation_ctx = "DataSource0")

@type: DataSink

@args: [connection_type = "s3", format = "json", connection_options = {"path": "s3://PATH/TO/BUCKET/", "partitionKeys": []}, transformation_ctx = "DataSink0"]

@return: DataSink0

@inputs: [frame = DataSource0]

DataSink0 = glueContext.write_dynamic_frame.from_options(frame = DataSource0, connection_type = "s3", format = "json", connection_options = {"path": "s3://PATH/TO/BUCKET/", "partitionKeys": []}, transformation_ctx = "DataSink0")

job.commit()

Using the CData Glue Connector for Salesforce in AWS Glue Studio, you can easily create ETL jobs to load Salesforce data into an S3 bucket or any other destination. You can also use the Glue Connector to add, update, or delete Salesforce data in your Glue Jobs.

Health Check

The CData Glue Connector for Salesforce is used as part of AWS Glue jobs. As such, you can use CloudWatch and the built-in logging (see the optional logging instructions above) to monitor the health of the Glue job and the functionality of the Connector.

Backup & Recovery

The CData Glue Connector for Salesforce is a deployed container. Backup and recovery consists of simply resubscribing to the Connector in the event of a failure or corrupted deployment.

Routine Maintenance

There are several pieces of routine maintenance involved with the CData Glue Connectors:

Rotating credentials & keys: Follow the guidance of your IT administration for the rotation of any credentials & keys stored in the AWS Secrets Manager
Software patches & upgrades: The CData Glue Connector will be only be patched for breaking errors. Upgrades will be released quarterly. Monitor the "Latest Version" in the AWS Marketplace listing and simply re-subscribe if the Latest Version is greater than your currently subscribed version.
Managing license: Licenses can be managed (subscriptions discontinued as needed) in the AWS License Manager

Learn More

Organizations continue to evolve and use a variety of data stores that best fit their applications and data requirements.

The CData AWS Glue Connectors simplify the process of accessing enterprise data in real-time. Connect with accounting, CRM, ERP, marketing automation, and cloud solutions from AWS Glue & AWS Glue Studio.

Leading-Edge NoSQL Drivers for NoSQL Analytics & Integration

Jerod Johnson — Fri, 22 Oct 2021 13:10:16 +0000

NoSQL databases, such as MongoDB, are an increasingly important component of enterprise data strategy, handling high-volume, scalable, and agile data management. As organizations adopt emerging NoSQL solutions, there is high demand to integrate these data sources with existing infrastructure and familiar tools. Standards-based data access facilitates your expansion into MongoDB by leveraging existing skills using standards like ODBC, JDBC, and ADO.NET.

The CData Drivers allow users to interact with NoSQL databases as if they were a relational database, leveraging the flexibility of NoSQL data storage with the familiarity and utility of SQL-92 queries. In this article, we highlight the specific features and capabilities common across all CData NoSQL drivers, using the MongoDB Drivers as an example.

When working with NoSQL data, it is common for data structures to be returned as JSON objects, arrays, or any combination thereof, due to the flexibility of NoSQL. Although this is convenient for storing hierarchical data, it can be difficult to work with in BI, reporting, and ETL tools. The CData Drivers include several facilities for mapping or flattening these data structures to simplify integration with standard tooling.

Key Features

Free-Form Queries: requesting exactly the data you want from your tables.
Horizontal Flattening: drilling down into embedded data (sub-documents and arrays).
Vertical Flattening: treating embedded arrays of sub-documents as separate tables.
Custom Schema Definitions: defining how the drivers view the MongoDB data.
Client-Side JSON Functions: manipulating the data returned to perform client-side aggregation and transformation.

Below are examples of these features. For reference, these examples are based on the following MongoDB sample document:

Sample Document

{

"_id" : ObjectId("5780046cd5a397806c3dab38"),

"address" : {

"building" : "1007",

"coord" : [-73.856077, 40.848447],

"street" : "Morris Park Ave",

"zipcode" : "10462"

"borough" : "Bronx",

"cuisine" : "Bakery",

"grades" : [{

"date" : ISODate("2014-03-03T00:00:00Z"),

"grade" : "A",

"score" : 2

}, {

"date" : ISODate("2013-09-11T00:00:00Z"),

"grade" : "A",

"score" : 6

}, {

"date" : ISODate("2013-01-24T00:00:00Z"),

"grade" : "A",

"score" : 10

}, {

"date" : ISODate("2011-11-23T00:00:00Z"),

"grade" : "A",

"score" : 9

}, {

"date" : ISODate("2011-03-10T00:00:00Z"),

"grade" : "B",

"score" : 14

}],

"name" : "Morris Park Bake Shop",

"restaurant_id" : "30075445"

}

Free-Form Queries

The simplest way to access your MongoDB data is by issuing a query based on what you understand to be in the data source. This gives you the freedom to select exactly the data that you want, regardless of the existence of a strict table schema. Consider the sample document above.

If you know that you want the _id, address.street, and grades[0] fields from each document, you can freely query that data from the database:

SELECT

[_id],

[address.street],

[grades.0],

FROM restaurants;

The driver returns the value for those fields, given that the documents contain data. If the field does not exist in a given document, the driver simply returns a NULL for the field. This feature is useful whenever you have control over the SQL query being submitted to the CData driver, but obviously cannot be used when the opposite is true. There are other options available in that case.

Horizontal Flattening

The Flatten Arrays and Flatten Objects Connection Properties in the CData drivers allow you to control how objects and arrays in your MongoDB data are parsed to dynamically define the table schema for your MongoDB data. These properties allow you to configure how the data in a given document is horizontally flattened, creating a single table schema for all of the documents (including embedded data) in a given table. This is especially useful when you do not have granular control over the SQL queries being submitted.

In the examples below, we display the expected results, based on various values for Flatten Arrays and Flatten Objects, for the following query:

view source

SELECT *

FROM restaurants

FlattenArrays=0;FlattenObjects=False;

Without any horizontal flattening, the drivers discover seven columns for the table: _id, address, borough, cuisine, grades, name, and restaurant_id. Embedded data in the document is returned in a raw, aggregate form.

Result

_id : 5780046cd5a397806c3dab38
Address: { "building" : "1007", "coord" : [-73.856077, 40.848447], "street" : "Morris Park Ave", "zipcode" : "10462" }
Borough: Bronx
Cuisine: Bakery
Grades: [{"date" : ISODate("2014-03-03T00:00:00Z"), "grade" : "A", "score" : 2 }, { "date" : ISODate("2013-09-11T00:00:00Z"), "grade" : "A", "score" : 6 }, { "date" : ISODate("2013-01-24T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2011-11-23T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2011-03-10T00:00:00Z"), "grade" : "B", "score" : 14}]
Name: Morris Park Bake Shop
Restaurant Id: 30075445

FlattenArrays=0;FlattenObjects=True;

If you set Flatten Objects to "true", the number of columns expands as the embedded "address" sub-document is flattened. With Flatten Objects still set to "false" any arrays or arrays of documents will be returned as aggregates.

Result

_id : 5780046cd5a397806c3dab38

address.building : 1007

address.coord : [-73.856077, 40.848447]

address.street : Morris Park Ave

address.zipcode : 10462

borough : Bronx

cuisine : Bakery

grades : [{"date" : ISODate("2014-03-03T00:00:00Z"), "grade" : "A", "score" : 2 }, ... ]

name : Morris Park Bake Shop

restaurant_id : 30075445

FlattenArrays=2;FlattenObjects=False;

The Flatten Arrays property determines how many items in an embedded array of sub-documents to treat as individual columns. By setting Flatten Arrays to "2" (while leaving Flatten Objects = "false"), we extract the first two items in the embedded arrays of a document.

Result

_id : 5780046cd5a397806c3dab38

address : { "building" : "1007", "coord" : [-73.856077, 40.848447], "street" : "Morris Park Ave", "zipcode" : "10462" }

borough : Bronx

cuisine : Bakery

grades.0 : { "date" : ISODate("2014-03-03T00:00:00Z"), "grade" : "A", "score" : 2 }

grades.1 : { "date" : ISODate("2013-09-11T00:00:00Z"), "grade" : "A", "score" : 6 }

name : Morris Park Bake Shop

restaurant_id : 30075445

FlattenArrays=1;FlattenObjects=True;

With Flatten Arrays set to "1" and Flatten Objects = "true", we extract the first item in the embedded arrays of a document and flatten any embedded sub-documents.

Result

_id : 57800...

address.building : 1007

address.coord.0 : -73.856077

address.street : Morris Park Ave

address.zipcode : 10462

borough : Bronx

cuisine : Bakery

grades.0.date : 2014-03-03...

grades.0.grade : A

grades.0.score : 2

name : Morris Park Bake Shop

restaurant_id : 30075445

These columns are available for use in INSERT and UPDATE statements as well, allowing you to add or update individual fields within sub-documents and arrays.

Vertical Flattening

Documents in MongoDB frequently contain an array (or arrays) of sub-documents. While it is possible to drill down into these sub-documents using horizontal flattening (see above section), a common way of dealing with such arrays in NoSQL databases is to treat them as separate tables of data. This process is known as vertical flattening and doing so helps to build a relational model between the different 'types' of documents in a MongoDB instance.

Considering the sample document above, you could retrieve the grades array as a separate table:

SELECT

FROM [restaurants.grades];

This query returns the following data set:

Date	Grade	Score
2014-03-03T00:00:00Z	A	2
2013-09-11T00:00:00Z	A	6
2013-01-24T00:00:00Z	A	10
2011-11-23T00:00:00Z	A	9
2011-03-10T00:00:00Z	B	14

You may also want to include information from the base restaurants table. You can do this with a join. Flattened arrays can only be joined with the root document. The driver expects the left part of the join is the array document you want to flatten vertically. Set the SupportEnhancedSQL connection property to false to join nested MongoDB documents.

SELECT

[restaurants].[_id], [restaurants.grades].*

FROM

[restaurants.grades]

JOIN

[restaurants]

WHERE

[restaurants].name = 'Morris Park Bake Shop'

This query returns the following data set:

_id : 5780046cd5a397806c3dab38

date : 2014-03-03T00:00:00Z

grade : A

score : 2

_id : 5780046cd5a397806c3dab38

date : 2013-09-11T00:00:00Z

grade : A

score : 6

_id : 5780046cd5a397806c3dab38

date : 2013-01-24T00:00:00Z

grade : A

score : 10

_id : 5780046cd5a397806c3dab38

date : 2011-11-23T00:00:00Z

grade : A

score : 9

_id : 5780046cd5a397806c3dab38

date : 2011-03-10T00:00:00Z

grade : B

score : 14

Custom Schema Definitions

In order to treat your MongoDB data as a relational database, a table schema must exist. The schema can be created dynamically by using Connection properties or by defining the schema yourself. This is another option for drilling down into your data when you do not have full control of the SQL queries being constructed.

Given the document above, you could expose the _id (as the primary key), name, address.zipcode, and the first entry in the grades fields by creating the following schema:

/rsb:info

/rsb:script

Once you have created your custom schema files, save them to disk using ".rsd" as the file extension (typically in the db folder at the installation location) and set the Location Connection Property to the same location. The driver will expose the defined tables in any third party tools and apps based on the title attribute of rsb:info. You can also query the data explicitly by using the title as the table name in a SQL query:

SELECT

id, latest_grade

FROM

StaticRestaurants;

By defining the schema for your MongoDB data, you gain granular control over you data in a way that is not commonly supported in BI, reporting, and ETL tools, allowing you to leverage the data visualization, transformation, and extraction features of your favorite tools to work with your data in the way that you want. Custom schemas also allow you to define different views of the data stored in a single "table", meaning that you can take full advantage of the NoSQL nature of a MongoDB database where a given table can contain documents whose relevant fields are differentiated by something like a type field.

Client-Side JSON Functions

The documents in MongoDB data sets are essentially JSON structures. The CData Drivers support using standard SQL functions to work with JSON structures. These functions work by pulling in the MongoDB document and parsing the relevant information in the client. While there are many functions supported, we only highlight a few here. The examples below use the sample document, contained in the table 'Students':

{

id: 123456,

...,

grades: [

{ "grade": "A", "score": 96 },

{ "grade": "A", "score": 94 },

{ "grade": "A", "score": 92 },

{ "grade": "A", "score": 97 },

{ "grade": "B", "score": 84 }

...

}

JSON_EXTRACT

The JSON_EXTRACT function can extract individual values from a JSON object. The following query returns the values shown below based on the JSON path passed as the second argument to the function:

SELECT

JSON_EXTRACT(grades,'[0].grade') AS Grade,

JSON_EXTRACT(grades,'[0].score') AS Score

FROM Students;

This query returns the following data:

Grade

Score

JSON_SUM

The JSON_SUM function returns the sum of the numeric values of a JSON array within a JSON object. The following query returns the total of the values specified by the JSON path passed as the second argument to the function:

SELECT

Name,

JSON_SUM(score,'[x].score') AS TotalScore

FROM Students;

This query returns the following data:

TotalScore

463

DOCUMENT

The DOCUMENT function can be used to retrieve the entire document as a JSON string. See the following query and its result as an example:

SELECT

DOCUMENT(*)

FROM Students;

The query above returns each document in the table as a single string.

DOCUMENT

{ "_id" : ObjectId("5780046cd5a397806c3dab38"), "address" : { "building" : "1007", "coord" : [-73.856077, 40.848447], "street" : "Morris Park Ave", "zipcode" : "10462" }, "borough" : "Bronx", "cuisine" : "Bakery", "grades" : [{ "date" : ISODate("2014-03-03T00:00:00Z"), "grade" : "A", "score" : 2 }, { "date" : ISODate("2013-09-11T00:00:00Z"), "grade" : "A", "score" : 6 }, { "date" : ISODate("2013-01-24T00:00:00Z"), "grade" : "A", "score" : 10 }, { "date" : ISODate("2011-11-23T00:00:00Z"), "grade" : "A", "score" : 9 }, { "date" : ISODate("2011-03-10T00:00:00Z"), "grade" : "B", "score" : 14 }], "name" : "Morris Park Bake Shop", "restaurant_id" : "30075445" }

Get Started

Data is driving innovation and growth for business, but only for businesses prepared to handle data effectively.

While relational databases have their place, today's data complexity, volume and demand call for the implementation of NoSQL databases for certain data applications. Standards-based drivers provide a universal bridge between this unwieldy NoSQL data, and the world of BI & Analytics.

The NoSQL Drivers offer tremendous flexibility in working with NoSQL databases. Users can connect their BI, Analytics, ETL, & custom applications with any SaaS, NoSQL, or Big Data source.

SQL Access to Redis Data

Jerod Johnson — Thu, 21 Oct 2021 13:49:32 +0000

Traditionally, importing key-value data stores such as Redis into BI, reporting, and ETL tools is problematic, if not impossible. With the CData Drivers, there are several different ways to building a traditional database model to easily work with Redis data in the BI, reporting, ETL, and custom applications of your choice.

In this article, we discuss the way that CData standards-based drivers handle data stored in Redis and Redis Enterprise.

Redis Data Interpretation Approaches

Using a Redis Key as a Table Name
Using a Key Pattern as a Table Name
Using a Key Pattern in the SQL Query WHERE Clause
Using Connection Properties: DefineTables and TablePattern

The first three options are useful for working with Redis key-value pairs directly, just as if you were working in a traditional Redis environment. The option for configuring connection properties results in related Redis key-value pairs being pivoted into a more traditional data table model. Each approach is detailed below.

Redis Data Types

Redis data is stored in key-value pairs, but instead of the common limit of simple strings, Redis can assign any of several data structures to a given key. Below is a list of the supported data structures (think data types) that can be found in Redis

Binary-safe strings.
Lists: collections of string elements sorted according to the order of insertion. They are basically linked lists.
Sets: collections of unique, unsorted string elements.
Sorted sets (ZSets): similar to sets but where every string element is associated to a floating number value, called score. The elements are always taken sorted by their score, so unlike sets it is possible to retrieve a range of elements (for example you may ask: give me the top 10, or the bottom 10).
Hashes: maps composed of fields associated with values. Both the field and the value are strings. This is very similar to Ruby or Python hashes.

This article will discuss how the CData Software Drivers for Redis interact with the above Redis types and includes sample SQL queries for using the drivers to work with Redis data.

Using a Redis Key as a Table Name

The most direct way to work with Redis data with our drivers is to use a Redis key as a table name. Doing so will return a small table with five columns: RedisKey, Value, ValueIndex, RedisType, and ValueScore. The values in these columns are dependent upon the Redis data type associated with the Redis key being used as a table name.

RedisKey - the Redis key
Value - the string value associated with the RedisKey and ValueIndex
ValueIndex - varies by type: 1 for strings; the one-based index for sets, lists, and sorted sets; or the associated field name for hashes
RedisType - the Redis data type
ValueScore - varies by type: NULL for strings, lists, sets, and hashes; or the associated score for sorted sets

Below you will find sample data, queries, and results based on Redis data types.

Redis Strings

Create a string in Redis:

set mykey somevalueOKIf you perform a SELECT query on mykey the driver will return the following:

SELECT * FROM mykey

Redis Lists

Create a list in Redis:

rpush mylist A B C(integer) 3If you perform a SELECT query on mylist the driver will return the following:

SELECT * FROM mylist

Redis Sets

Create a set in Redis:

sadd myset 1 2 3(integer) 3If you perform a SELECT query on myset the driver will return the following (note that Redis can return the elements of a set in any order):

SELECT * FROM myset

Redis Sorted Sets

Create a ZSet (sorted set) in Redis:

zadd hackers 1940 "Alan Kay" 1957 "Sophie Wilson" 1953 "Richard Stallman" 1949 "Anita Borg"(integer) 9If you perform a SELECT query on hackers the driver will return the following:

SELECT * FROM hackers

Redis Hashes

Create a hash in Redis:

hmset user:1000 username antirez birthyear 1977 verified 1OKIf you perform a SELECT query on user:1000 the driver will return the following:

SELECT * FROM user:1000

Using a Key Pattern as a Table Name

If you have several Redis keys that match the same pattern (e.g., "user:*"), then you can use that pattern as a table name. This allows you to retrieve multiple Redis keys at once. Start by adding several keys to Redis that match a pattern:

hmset user:1000 name "John Smith" email "john.smith@example.com" password "s3cret"OK> hmset user:1001 name "Mary Jones" password "hidden" email "mjones@example.com"OK

If you use user:* as the table name, the driver will retrieve all Redis key-value pairs whose keys match the pattern. You can see the expected results below:

SELECT * FROM [user:*]

Using a Key Pattern in a SQL Query WHERE Clause

If you have several Redis keys that match a pattern and have more granular control over the SQL query, then you can use a key pattern (e.g., "user:*") as the criteria for the key column in a WHERE clause. The results will be the same as using a key pattern as the table name. This allows you to retrieve multiple Redis keys at once. Start by adding several keys that match a pattern:

hmset user:1000 name "John Smith" email "john.smith@example.com" password "s3cret"OK> hmset user:1001 name "Mary Jones" password "hidden" email "mjones@example.com"OK

If you use a table pattern as the criteria for the key column in the WHERE clause then you need to use "Redis" as the table name. The driver will retrieve all Redis key-value pairs whose keys match the pattern. You can see the expected results below:

SELECT * FROM Redis WHERE key = 'user:*'

Using Connection Properties

When it comes to connecting to data in third party tools and apps using drivers, you often have little control over how queries are formed and sent to the drivers. In these instances, it makes sense to configure the driver directly, using connection properties, to shape how the data is interpreted. For the Redis drivers, these two properties are DefineTables and TablePattern.

For these sections, we will create the following hashes in our Redis instance:

hmset user:1000 name "John Smith" email "john.smith@example.com" password "s3cret"OK> hmset user:1001 name "Mary Jones" email "mjones@example.com" password "hidden" OK> hmset user:1002 name "Sally Brown" email "sally.b@example.com" password "p4ssw0rd"OK> hmset customer:200 name "John Smith" account "123456" balance "543.21"OK> hmset customer:201 name "Mary Jones" account "123457" balance "654.32" OK> hmset customer:202 name "Sally Brown" account "123458" balance "765.43"OK

When these properties are used to define the driver's behavior, the Redis keys will be pivoted, so that each Redis key that matches the pattern in the definition is represented as a single row in the table. Each value associated with that Redis key becomes a column for the table. While this works differently for each Redis data type, this article will focus on hashes.

DefineTables Property

The DefineTables connection property allows you to explicitly define the names of the tables that will appear in various tools and apps by aggregating all of the Redis keys that match a given pattern. To do so, set the property to a comma-separated string of name-value pairs, where the name is the name of the table and the value is the pattern used to assign Redis keys to that table.

DefineTables=Users=user:,Customers=customer:;

With the property set as above, the Users and Customers tables will be exposed in the tool or app you are using. If you were to query the tables, you would see the following results:

SELECT * FROM Users

SELECT * FROM Customers

TablePattern Property

The TablePattern connection property allows you to define the separator(s) that determine how the drivers define tables. For the Redis keys described above, user and customer would be defined as tables if the separator is set to ":" since the unique piece of each Redis key appears after the ":". If you have a need to structure the tables differently, to drill down further, you can include multiple instances of the separator. Set the property to a pattern that includes the separator(s) needed to define your table structure. Below is the default value.

TablePattern=:;

With the property set as above, the tables user and customer will be exposed in the tool or app you are using. If you were to query the tables, you would see the following results:

SELECT * FROM user

SELECT * FROM customer

More Information

Modern data-driven applications require modern solutions to quickly process a massive volume, variety, and velocity of data and automate decision making.

With the CData Software Drivers for Redis, users can connect to live data cached in Redis from BI, analytics, and reporting tools through bi-directional data drivers.

Connecting to Amazon DocumentDB with MongoDB Drivers

Jerod Johnson — Wed, 20 Oct 2021 09:55:22 +0000

With Amazon DocumentDB developers and DBAs can balance availability, read scalability, and latency with five pre-configured consistency levels. The data in an Amazon DocumentDB cluster is accessible just like a MongoDB 3.6 cluster.

The CData Software Drivers for MongoDB provide a SQL-like interface to MongoDB data through standards-based drivers. This allows users to see their data in the BI, reporting, and ETL tools of their choice, as well as in custom tools and applications.

This article walks through configuring an Amazon DocumentDB cluster and connecting to the cluster through any of the available CData Software MongoDB drivers.

Creating a DocumentDB Cluster

To create a DocumentDB cluster, log in to your AWS Console, select Amazon DocumentDB, click Clusters and Create to add a new cluster. Once you create the cluster, you can connect to the cluster using the MongoDB client from an EC2 instance to populate the cluster. Once you have data in the Amazon DocumentDB cluster, you can connect using the CData MongoDB Drivers.

Connecting to a Amazon DocumentDB Cluster

With your cluster created, make note of the cluster details (found by clicking on the cluster name on the Clusters page).

The table below describes the mapping from the DocumentDB cluster details to the CData Driver properties:

CData Driver Property | Amazon DocumentDB Property

Server --> Cluster endpoint

Port --> Port

User --> Master username

Password --> (set when you create the cluster)

In addition to the cluster details, you need to set a few more additional properties in the CData drivers to connect to an Amazon DocumentDB cluster:

UseSSL: True
(Amazon DocumentDB Clusters have SSL enabled by default and this cannot be changed.)
SSLServerCert: *
(This automatically accepts the certificate presented by the DocumentDB Cluster, which is already hosted within the same region and security group as the EC2 instance.)
Other: "UseFindAPI=true"
(This is set to ensure compatibility with the DocumentDB version of the MongoDB API.)

With all of the properties, your standard connection string would look like the following:

Server=.cluster-xxxxxxxxxxxx..docdb.amazonaws.com; Port=27107; User=myMaster; Password=myPassword; UseSSL=True; SSLServerCert=*; Other='UseFindAPI=True';

Once the connection properties are set, you can quickly connect to your Amazon DocumentDB account using any of the MongoDB Drivers.

More Information

When developing modern applications, document databases like MongoDB are a popular choice for storing semi-structured data for use cases like product catalogs, user profiles, mobile applications, and content management.

The MongoDB ODBC Driver is a powerful tool that allows you to connect with live MongoDB document databases, directly from any applications that support ODBC connectivity. Access MongoDB like you would a database - read, write, and update through a standard ODBC Driver interface.

Perform API Operations Using Stored Procedures in CData SSIS Components

Jerod Johnson — Tue, 19 Oct 2021 14:18:42 +0000

APIs require up-to-date and strong documentation, increased security levels, comprehensive testing, routine versioning, and high-reliability to be appropriately utilized.

With the CData SSIS Data Flow components you can import and export data from various data sources. However, in some cases the type of operation you need does not fit the SSIS Source or the SSIS Destination model.

The CData SSIS Components surface additional functionality available in the underlying API as stored procedures. This article uses the CData SSIS Components for SharePoint to execute the DownloadDocument stored procedure, but you can follow the same process to execute the stored procedures of any CData SSIS Component to perform other actions like retrieving OAuth credentials, uploading attachments to emails, authoring reports from CRM applications, and more.

Connect to SharePoint from an SSIS Source Component

Open Visual Studio and create a new Integration Services Project.
Add a new Data Flow Task to the Control Flow screen and open the Data Flow Task.
Add a CData SharePoint Source to the Data Flow Task.

In the CData SharePoint Source, add a new Connection Manager and add your credentials for the SharePoint site. For this demo, you will want to set 'Show Hidden Columns' to True, as the sample code uses a hidden column.

In the Source Component editor, select SQL Statement to configure the Source Component to execute a stored procedure.
Set the SQL Query to an EXECUTE statement for the Stored Procedure, e.g.,
EXECUTE DownloadDocument @File = '', @Library = '', @RemoteFile = ''

Configuring a Parameterized Query

After you have set the SQL query in the Source component, click the Control Flow tab
Select Variables from the SSIS menu and add the appropriate variables for the Stored Procedure (e.g., User, Library, and RemoteFile)
In the Control Flow tab, select the Data Flow Task
In the Properties pane, click the expand button for the Expressions property
In the Property expressions list, select a blank row and choose the SQL Statement for the CData SharePoint Source component

Set the Expression to a parameterized EXECUTE statement for the Stored Procedure, including quotes, e.g.,

"EXECUTE DownloadDocument @File = '" + @[User::File] + "', @Library = '" + @[User::Library] + "', @RemoteFile = '" + @[User::RemoteFile] + "'"

After saving your Expression, you can execute the project and find the downloaded files in the download directory.

Learn More

APIs have the potential to transform businesses. Forward-thinking companies are reducing costs and time spent by “building their solutions with best-of-breed components, which they access via APIs.

With the CData SSIS Components you can simplify data movement, integration, & transformation with codeless connectivity. Synchronize or replicate cloud applications and databases, import & export to various files and cloud storage solutions, connect back-office processes through SSIS workflows, and so much more.

A Comparison of Database Drivers for MySQL

Jerod Johnson — Mon, 18 Oct 2021 14:56:59 +0000

MySQL is "The world's most popular open source database" and it is widely-used to store and access your data. In fact, there are many cloud and on-premise databases (like MemSQL and Google Cloud SQL) that use the MySQL interface. Several native, open-source drivers are available for connecting to your MySQL data from other applications.

In this article, we will compare the read and write performance of the native MySQL Connectors (JDBC and ODBC)1 and the CData Software JDBC and ODBC Drivers for MySQL2 when working with larger datasets. We show how the CData Driver is able to read large data sets approximately 20% faster than the native driver and write 1 million rows nearly 40% faster than the native drivers.

The Data

In order to provide a reproducible comparison for reading data, we copied the US Amazon book reviews from the Amazon Customer Reviews dataset3 into a MySQL database. The details for the amazon_book_reviews table are below:

Table Size : 9.7 GB

Table Number of Rows : 10,534,179

Number of Columns : 15

# JDBC Driver Read Performance

The main goal of this investigation was to compare the related performance of the JDBC drivers. We did this by running the same set of queries with each JDBC driver. We used LIMIT clauses to change the size of the dataset returned in each query, but we requested the same columns for each query.

Base Query

SELECT

marketplace,

customer_id,

review_id,

product_id,

product_parent,

product_title,

product_category,

star_rating,

helpful_votes,

total_votes,

vine,

verified_purchase,

review_headline,

review_body,

review_date,

FROM

cdata.amazon_book_reviews;

Limit by Query

LIMIT 1000000
Full Dataset

To test the drivers, we connected to MySQL using a basic Java application and executed the above queries repeatedly. The results were read and stored in a new variable (based on the datatype) for each column in each row.

Query Times by Driver (in seconds)

Query | CData JDBC Driver | MySQL Connector/J
1 (1,000,000 rows) 14.02 (+26%) 17.67
2 (~10,000,000 rows) 171.09 (+20.7%) 206.42

As can be seen in the results, the CData Driver regularly outperformed the native MySQL Driver, largely due to the way the CData JDBC Driver uses client-side resources.

JDBC Driver Resource Usage

While testing the read performance of the JDBC drivers, we also measured client-side resource usage, looking specifically at memory. The charts below were found by running a sample Java program and using Java VisualVM to capture the memory usage. We used Java OpenJDK version 12.0.2 with a maximum heap size of 8 Gigabytes.

For this comparison, we ran the query for the full dataset.

CData Driver

Native Driver*

Note the change in scale for the Heap graph.

The CData Driver averages approximately 150 MB of heap usage for the duration of the run. However, the native driver continues to use more and more client resources for the duration of the run. In fact, we had to set the max Heap size to 8 GB in order for the native MySQL Driver to complete its execution. Despite the fact that the native driver is using significantly more resources, it still takes longer to read the data than it takes the CData JDBC Driver.

ODBC Driver Read Performance

The main goal of this investigation was to compare the related performance of the ODBC Drivers. We did this by running the same set of queries with each ODBC Driver. We used LIMIT clauses to change the size of the dataset returned in each query, but we requested the same columns for each query.

Base Query

SELECT

marketplace,

customer_id,

review_id,

product_id,

product_parent,

product_title,

product_category,

star_rating,

helpful_votes,

total_votes,

vine,

verified_purchase,

review_headline,

review_body,

review_date,

FROM

cdata.amazon_book_reviews;

Limit by Query

LIMIT 1000000
Full Dataset

To test the drivers, we connected to MySQL using a basic C++ application and executed the above queries repeatedly. The results were read and stored in a new variable (based on the datatype) for each column in each row.

Query Times by Driver (in seconds)

Query | CData ODBC Driver | MySQL Connector/ODBC
1 (1,000,000 rows) 14.95 (+96.7%) 29.40
2 (~10,000,000 rows) 177.51 (+72.3%) 305.77

As can be seen in the results, the CData ODBC Driver regularly outperformed the MySQL Connector/ODBC, largely due to better use of available client resources.

ODBC Driver Resource Usage

While testing the read performance of the ODBC drivers, we also measured client-side resource usage, looking specifically at processing capacity and network bandwidth. The charts below were found by executing the simple C++ application and using the Windows Resource Monitor.

For this comparison, we ran the query for the full dataset.

CData Driver

Native Driver

Both drivers appear to use 20 - 25% of the available processing capacity, with CData using slightly more. The larger difference is in the network bandwidth used, where the CData Driver uses around 250Mbps compared to the 100Mbps used by the native driver.

Million Row Challenge

In addition to measuring read performance, we also want to compare the write performance of the drivers. In short, the CData MySQL JDBC Driver is able to write 1 million rows nearly 40% faster than the native MySQL Connector.

We used a simple Java program to add the rows to a copy of the amazon_book_reviews table referenced above.3. For our testing, we inserted the data in 100 batches of 10,000 rows.

Sample Code

//one batch

Connection connection = DriverManager.getConnection("jdbc:mysql:server=" + myServer + ";port=" + myPort + ";user=" + myUser + ";password=" + myPassword + ";database=" + myDatabse + ";");

String cmd = "INSERT INTO cdata.amazon_book_reviews_insert (marketplace, customer_id, review_id, product_id, product_parent, product_title, product_category, star_rating, helpful_votes, total_votes, vine, verified_purchase, review_headline, review_body, review_date) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

PreparedStatement pstmt = connection.prepareStatement(cmd);

for (int row = 0; row < rows.length; row++){

pstmt.setString(1, marketplace);

pstmt.setLong(2, customer_id);

pstmt.setString(3, review_id);

pstmt.setLong(4, product_id);

pstmt.setLong(5, product_parent);

pstmt.setString(6, product_title);

pstmt.setString(7, product_category);

pstmt.setLong(8, star_rating);

pstmt.setLong(9, helpful_votes);

pstmt.setLong(10, total_votes);

pstmt.setString(11, vine);

pstmt.setString(12, verified_purchase);

pstmt.setString(13, review_headline);

pstmt.setString(14, review_body);

pstmt.setDate(15, review_date);

pstmt.addBatch();

}

int[] affected = pstmt.executeBatch();

Results

Time (in Seconds) to Insert 1m Rows

CData JDBC Driver | MySQL Connector/J
77.6 127.9

Conclusion

Most technologies that emerge are all about being more efficient and providing more functionality in a smaller package. If you can meet your data management needs with fewer tools, then it’s a win-win for cost-effectiveness, efficiency, and ease of use.

The CData Driver offers better querying of large datasets over the native connector, processing the largest dataset at least 20% faster by making better use of the available client resources.

When it comes to inserting data, the CData Driver stands apart in its ability to rapidly insert large sets of data. The CData JDBC Driver is able to insert 1 million rows in under 80 seconds, nearly 40% faster than the native connector.

CData Connect: Derived Views and Query Federation

Jerod Johnson — Fri, 15 Oct 2021 13:17:02 +0000

Enterprise-scale data integration demands scalable tools to not only centralize and curate data at scale, but also efficiently explore and analyze data at large-scale.

CData Connect solves the challenge of data exploration and discovery through virtualized connectivity to data distributed across many data sources.

In this article, we explore the Derived Views and Query Federation features of CData Connect, allowing users to create custom interfaces, surfacing combined data from many sources and even simultaneously query data across data sources.

Derived Views

Getting a database model of your SaaS data, where each entity (Account, Contact, etc.) is represented by a database table is useful, but creating views of your enterprise data can help in a variety of ways:

Simplified access - Views provide access to a specific subset of the data available, and can feature built-in filtering, column selections, and more to create easier access to commonly used queries
Improved security - Views can select specific columns/rows and set the permissions on the view instead of the underlying table, giving data administrators more granular control over which data points are accessible by each user
Complex, real-time data - Views can roll up operations (summations, aggregations, etc.), JOINs, and more all while provide real-time access to data by performing live queries against the underlying table(s) each time the view itself is queried

The Derived View feature of CData Connect lets you do just that - create a "view" of your SaaS, Big Data, & NoSQL sources. The derived view behaves exactly like a user-defined view, but the definition of the view resides directly within the CData Connect instance. To create a view, you first need to create a virtual database for your data.

Navigate to the Data Model tab and click Data Sources
Select a source from the available Sources

Configure the connection using the data source-specific properties

Once connected to a data source, you can explore the virtual database in the Data Explorer. You can select your newly created database and drag tables into the query pane.

You can also create custom SQL queries based on the data model to request specific data. Click the Create Derived View button to save the query as a view. With the view saved, users will be able to easily access the results of the specific query saved. For example, you can use the view to visualize the results in popular BI and reporting tools.

Query Federation

In addition to creating Derived Views, you can also leverage the Query Federation capabilities of CData Connect to query related data across different sources. For example, you would be able to join data from your CRM solution and your accounting solution. The feature in CData Connect is built using our existing query federation technology. To query data across different sources, you will need multiple virtual databases in your CData Connect instance.

Once the databases are created, you can query data from both of them simultaneously just like you would be able to query data from two related databases. For example, you can connect to CData Connect from Power BI and visualize your data.

Query Federation in Derived Views

The Query Federation functionality can be used to create Derived Views across different data sources. This grants users and administrators all of the benefits of views on top of the benefits of querying data from multiple sources at the same time. Simply construct a query joining data from multiple virtual databases and save it as a derived view.

More Information

In order to transform the enormous amount of data into knowledge and actionable insights, teams must solve fundamental problems, such as data integration and query processing.

CData Connect is a consolidated connectivity platform that lets you easily connect any application, on-prem or in the cloud, with real-time data from anywhere. Now, analysts can finally gain unified access to vital data and deliver hard, data-driven insights for their teams.