This page provides a guide to Establishing a Connection to SendGrid in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to SendGrid

Establishing a Connection shows how to authenticate to SendGrid and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from SendGrid through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to SendGrid by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

You must use an API key to authenticate through the SendGrid Cloud.

Obtaining an API Key

To obtain an API key, follow the steps below:

1. Log into SendGrid.
2. Click Settings.
3. Click API keys.
4. Click Create API Key.
5. Choose the Permissions the API Key should have, either Full Access or Restricted Access. The API key is then displayed.

NOTE: If you want access to Email events, you must purchase the Email Activity History add-in of SendGrid. If using a Restricted Access key, you must give it the messages.read and email_activity.read scopes.

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Cloud uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The Cloud models SendGrid APIs as relational tables, views, and stored procedures. Most tables, views, and stored procedures are defined in schema files, which are simple, text-based configuration files.

However, the Recipients table is dynamic: The Cloud connects to SendGrid and gets the metadata for this table by calling the appropriate Web services. Any changes you make to the custom filters you define for Recipients are available when you connect; you can use these custom filters as columns in the WHERE clause. These custom filters are surfaced in the CustomFields table.

The Cloud uses the SendGrid API to process supported filters. The Cloud processes other filters client-side within the Cloud.

New Marketing Campaigns Data Model

See New Marketing Campaigns Data Model for the available entities in the New Marketing Campaigns API.

Legacy Data Model

See Legacy Data Model for the available entities in the Legacy API.

The CData Cloud models the New Marketing Campaigns API as relational tables, views, and stored procedures. Most tables, views, and stored procedures are defined in schema files, which are simple, text-based configuration files. The Cloud uses the SendGrid API to process supported filters. The Cloud processes other filters client-side within the Cloud.

To use the New Marketing Campaigns Data Model, simply set Schema to NMC.

Tables

Tables describes the available tables that are easy to query and update.

Views

Views are tables that cannot be modified. Typically, read-only data are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to Sendgrid. They can be used to search, update, and modify information.

The Cloud models the data in SendGrid as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - SendGrid Tables

Create, update, delete, and query Alerts in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM Alerts
SELECT * FROM Alerts WHERE Id = '148825'

Insert

The following columns are required in an insert for Stats Notification: Type, EmailTo, Frequency.

INSERT INTO Alerts (Type, EmailTo, Frequency) VALUES ('stats_notification', '[email protected]', 'daily')

The following columns are required in an insert for Usage Limit: Type, EmailTo, Percentage.

INSERT INTO Alerts (Type, EmailTo, Percentage) VALUES ('usage_limit', '[email protected]', 20)

Update

You can only update EmailTo by specifying an Id.

UPDATE Alerts SET EmailTo = '[email protected]' WHERE Id = 1310789

Delete

You can only delete from Alerts by specifying an Id.

DELETE FROM Alerts WHERE Id = 1310789

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query, Create, Update and Delete the available ApiKeys in SendGrid.

Table-Specific Information

Select

To get all the Api Keys

SELECT * FROM ApiKeys

To get information about a specific Api Key

SELECT * FROM Apikeys WHERE ApiKeyId = 'Dqdvdvdvdv4yLeuVQqO3rFxN5ktUxA'

Insert

The following columns are required in an insert: Name. VALUES for Scopes are: alerts.create, alerts.read, alerts.update, alerts.delete, asm.groups.create, asm.groups.read, asm.groups.update, asm.groups.delete, ips.pools.ips.read, mail.send, mail_settings.bcc.read, mail_settings.bcc.update, mail_settings.address_whitelist.read, mail_settings.address_whitelist.update, mail_settings.footer.read, mail_settings.footer.update, mail_settings.forward_spam.read, mail_settings.forward_spam.update, mail_settings.plain_content.read, mail_settings.plain_content.update, mail_settings.spam_check.read, mail_settings.spam_check.update, mail_settings.bounce_purge.read, mail_settings.bounce_purge.update, mail_settings.forward_bounce.read, mail_settings.forward_bounce.update, partner_settings.new_relic.read, partner_settings.new_relic.update, partner_settings.sendwithus.read, partner_settings.sendwithus.update, tracking_settings.click.read, tracking_settings.click.update, tracking_settings.subscription.read, tracking_settings.subscription.update, tracking_settings.open.read, tracking_settings.open.update, tracking_settings.google_analytics.read, tracking_settings.google_analytics.update, user.webhooks.event.settings.read, user.webhooks.event.settings.update, user.webhooks.event.test.create, user.webhooks.event.test.read, user.webhooks.event.test.update, user.webhooks.parse.settings.create, user.webhooks.parse.settings.read, user.webhooks.parse.settings.update, user.webhooks.parse.settings.delete, stats.read, stats.global.read, categories.stats.read, categories.stats.sums.read, devices.stats.read, clients.stats.read, clients.phone.stats.read, clients.tablet.stats.read, clients.webmail.stats.read, clients.desktop.stats.read, geo.stats.read, mailbox_providers.stats.read, browsers.stats.read, user.webhooks.parse.stats.read, templates.create, templates.read, templates.update, templates.delete, templates.versions.create, templates.versions.read, templates.versions.update, templates.versions.delete, templates.versions.activate.create, user.timezone.read, user.timezone.update, user.settings.enforced_tls.read, user.settings.enforced_tls.update, api_keys.create, api_keys.read, api_keys.update, api_keys.delete, email_activity.read, categories.create, categories.read, categories.update, categories.delete, mail_settings.template.read, mail_settings.template.update, marketing_campaigns.create, marketing_campaigns.read, marketing_campaigns.update, marketing_campaigns.delete, mail.batch.create, mail.batch.read, mail.batch.update, mail.batch.delete, user.scheduled_sends.create, user.scheduled_sends.read, user.scheduled_sends.update, user.scheduled_sends.delete, access_settings.whitelist.create, access_settings.whitelist.read, access_settings.whitelist.update, access_settings.whitelist.delete, access_settings.activity.read, whitelabel.create, whitelabel.read, whitelabel.update, whitelabel.delete, suppression.create, suppression.read, suppression.update, suppression.delete, messages.read

INSERT INTO Apikeys (Name) VALUES ('apiname')

INSERT INTO Apikeys (Name, Scopes, Sample) VALUES ('apiname', 'alerts.create,alerts.read', 'test')

Update

You can update ApiKeys by specifying an ApiKeyid.You need to specify both the name and the scopes during the UPDATE even if you don't want to update it.

UPDATE Apikeys SET Name = 'apiname2', Scopes = 'alerts.create,alerts.read' WHERE apikeyid = 'Dqdvdvdvdv4yLeuVQqO3rFxN5kscsctUxA-P_Q'

Delete

You can Delete ApiKeys by specifying an ApiKeyid.

DELETE FROM ApiKeys WHERE apikeyid = 'CMp4C8klscscscsQ0GxqvxtK2-P_Q'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query and Delete the available Blocks in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 120 days is used. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM Blocks WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

Delete

You can only delete from Blocks by specifying an Email.

DELETE FROM Blocks WHERE Email = '[email protected]'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query and Delete the available Bounces in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 120 days is used. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM Bounces WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

Delete

You can only delete from Bounces by specifying an Email.

DELETE FROM Bounces WHERE Email = '[email protected]'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Update, delete, and query Cancel Scheduled Sends in SendGrid.

Table-Specific Information

Select

This table only supports filtering by BatchId. The following are the only possible SELECT queries:

SELECT * FROM CancelScheduledSends
SELECT * FROM CancelScheduledSends WHERE BatchId = 148825

Update

You can update CancelScheduledSends by specifying an BatchId.

Allowed Values : cancel, pause

UPDATE CancelScheduledSends SET Status = 'cancel'  WHERE BatchId = 1310789

Delete

You can only delete from CancelScheduledSends by specifying an BatchId.

DELETE FROM CancelScheduledSends WHERE BatchId = 1310789

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query Global Unsubscribes in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. However this filter is optional. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM GlobalUnsubscribes WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

INSERT

Performing an Insert operation in this table you need to specify an email which will be added to the GlobalUnsubscribes list. For example:

INSERT INTO GlobalUnsubscribes (Email) VALUES ('[email protected]')

DELETE

Performing a Delete operation in this table you need to specify an email which will be removed from the GlobalUnsubscribes list. For example:

DELETE FROM GlobalUnsubscribes WHERE Email = '[email protected]'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query and Delete invalid emails in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Email. The following are the only possible SELECT queries:

SELECT * FROM InvalidEmails
SELECT * FROM InvalidEmails WHERE Email = '[email protected]'

Delete

You can only delete from InvalidEmails by specifying an Email.

DELETE FROM InvalidEmails WHERE Email = '[email protected]'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, delete, and query New Marketing Campaign Custom Fields in SendGrid.

Table Specific Information

New Marketing Campaign Custom fields are filters for a Contact that are defined by the user. NMC prefix stands for New Marketing Campaign.

Select

You can retrieve all the NMCCustomFields or filter them by Id:

SELECT * FROM NMCCustomFields WHERE Id = '1'

Use quotations to filter on NMC Custom Fields regardless of type.

Insert

To insert on NMCCustomFields specify the Name of the field and its Type. The Type can be text, date, or number.

INSERT INTO NMCCustomFields (name, type) VALUES ('age', 'number') 

Update

UPDATE NMCCustomFields SET name = 'updated name' WHERE Id = '123' 

Delete

You can only delete from NMCCustomFields by specifying an Id:

DELETE FROM NMCCustomFields WHERE Id = '1'

Columns

Create, update, delete, and query the available New Marketing Campaign Lists in SendGrid.

Table-Specific Information

NMC prefix stands for New Marketing Campaign.

Select

The NMCLists table can only filtered by its Id.

SELECT * FROM NMCLists WHERE Id = '123'

Insert

Only the Name for the NMCLists is required to insert into this table.

INSERT INTO NMCLists (Name) VALUES ('TestList')

Update

You can only update from NMCLists by specifying an Id.

UPDATE NMCLists SET Name = 'New Name' WHERE Id = '123'

Delete

You can only delete from NMCLists by specifying an Id.

DELETE FROM NMCLists WHERE Id = '123'

Columns

Create, update, delete, and query New Marketing Campaign Segments in SendGrid.

Table-Specific Information

NMC prefix stands for New Marketing Campaign.

Select

You can retrieve all the segments or filter them by Id.

SELECT * FROM NMCSegments WHERE Id = '1'

Insert

A Segment divides NMCContacts by certain criteria. To specify these criteria use the QueryDSL filter.

INSERT INTO NMCSegments (Name, QueryDSL) VALUES ("New Segment", "Segment Cdata", "first_name LIKE '%test%'"))

The statement above creates NMCSegments containing NMCContacts with FirstName containing the value "test".

Update

You can update the name or the conditions. Since the whole set will be overwritten, specify a full set.

UPDATE NMCSegments SET Name = "Name Updated", QueryDSL = "first_name LIKE '%test update%'" WHERE Id = '123'

Delete

You can only delete NMCSegments by Id:

DELETE FROM NMCSegments WHERE Id = '123'

Columns

Create, update, delete, and query New Marketing Campaign Senders in SendGrid.

Table-Specific Information

NMC stands for New Marketing Campaign.

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM NMCSenders
SELECT * FROM NMCSenders WHERE Id = '148825'

Insert

The following columns are required in an insert: Nickname, FromEmail, FromName, ReplytoEmail, Address, City, Country.

INSERT INTO NMCSenders (Nickname, FromEmail, FromName, ReplytoEmail, Address, City, Country) VALUES ('Nickname', 'FromEmail', 'FromName', 'ReplytoEmail', 'Address', 'City', 'Country')

Update

You can update NMCSenders by specifying an Id.

UPDATE NMCSenders SET Nickname = 'Nickname', FromEmail = 'FromEmail', FromName = 'FromName', ReplytoEmail = 'ReplytoEmail', Address = 'Address', City = 'City', Country = 'Country'  WHERE Id = 1310789

Delete

You can only delete from Alerts by specifying an Id.

DELETE FROM NMCSenders WHERE Id = 1310789

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query the available New Marketing Campaign Single Sends in SendGrid.

Table-Specific Information

NMC stands for New Marketing Campaign.

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM NMCSingleSends
SELECT * FROM NMCSingleSends WHERE Id = '148825'

Insert

The following columns are required in an insert: Name.

INSERT INTO NMCSingleSends (Name) VALUES ('SingleSend Name')

Update

You can update NMCSingleSends by specifying an Id.

UPDATE NMCSingleSends SET Name = 'SingleSend Updated' WHERE Id = 1310789

Delete

You can only delete from NMCSingleSends by specifying an Id.

DELETE FROM NMCSingleSends WHERE Id = 1310789

Columns

Query and delete Spam reports in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Email. The following are the only possible SELECT queries:

SELECT * FROM Spam
SELECT * FROM Spam WHERE Email = '[email protected]'

Delete

Delete from Spam 

You can delete from Spam by specifying an Email.

DELETE FROM Spam WHERE Email = '[email protected]'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query Teammates in SendGrid.

View-Specific Information

Select

• Username supports the '=,IN' comparisons.

For example, the following queries are processed server side:

SQL

SELECT * FROM Teammates WHERE Username = 'test'

SELECT * FROM Teammates WHERE Username IN ('test', 'test2')

Insert

The following columns are required in an insert: Email and Scopes. Scopes column accepts a comma separated list of values. The accepted values are: user.account.read, user.credits.read, user.email.create, user.email.read, user.email.update, user.email.delete, user.profile.create, user.profile.read, user.profile.update, user.profile.delete.

The IsAdmin is optional and by default is accepted as false from the API.

INSERT INTO Teammates (Email, Scopes) VALUES ('[email protected]', 'user.profile.read,user.profile.update')

INSERT INTO Teammates (Email, Scopes, IsAdmin) VALUES ('[email protected]', 'user.profile.read,user.profile.update', 'true')

Update

The following columns are required in an update: Username and Scopes. Scopes column accepts a comma separated list of values. The accepted values are: user.account.read, user.credits.read, user.email.create, user.email.read, user.email.update, user.email.delete, user.profile.create, user.profile.read, user.profile.update, user.profile.delete.

UPDATE Teammates SET Scopes = 'user.profile.read' WHERE Username = 'testuser'

Delete

Only the username column is required to delete a record from Teammates table. For example:

DELETE FROM Teammates WHERE Username = 'testuser'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query Unsubscribe Groups in SendGrid.

Table-Specific Information

Select

UnsubscribeGroups can only be filtered by Id.

SELECT * FROM UnsubscribeGroups WHERE Id = '1'

Insert

Provide a Name and a Description to insert into UnsubscribeGroups:

INSERT INTO UnsubscribeGroups (Name, Description, IsDefault) VALUES ('Unsubscribe1', 'General Unsubscribe', true)

Update

You can only update UnsubscribeGroups by specifying an Id.

UPDATE UnsubscribeGroups SET Name = 'New Name', Description = 'New Description' WHERE Id = '1'

Delete

You can only delete from UnsubscribeGroups by specifying an Id.

DELETE FROM UnsubscribeGroups WHERE Id = '1'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query the available Users in SendGrid.

View-Specific Information

Select

Query this table to retrieve your current profile details.

SELECT * FROM Users 

Update

You can update the all the columns marked as readonly false.

UPDATE Users Set Address='Milpitas', Address2='North Carolina' WHERE id='38713966'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - SendGrid Views

Get All Automation Stats

View-Specific Information

Select

The following is an example to query the AutomationStats view:

 
SELECT * FROM AutomationStats

Columns

Query the available Categories in SendGrid.

View-Specific Information

Select

Query this table to get available categories in SendGrid.

SELECT * FROM Categories

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query the available Category Stats in SendGrid.

View-Specific Information

Select

The following is an example to query the CategoryStats view:

 
SELECT * FROM CategoryStats WHERE Name = 'news'

You can also filter by Date. If you do not specify one, a default span of the past 1 year is used. The operators =, >=, <=, <, and > are supported for this column.

SELECT * FROM CategoryStats WHERE Name = 'news' AND Date > '2016-01-01' AND Date < '2016-01-30'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

List Designs

View-Specific Information

Select

The following is an example to query the Designs view:

 
SELECT * FROM Designs
SELECT * FROM Designs WHERE ID = 'd0ceee19-e773-403b-a72c-8d7c58ec6065'

Columns

Query Teammates in SendGrid.

Columns

Query the available Global Stats in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

SELECT * FROM GlobalStats 

SELECT * FROM GlobalStats WHERE Date > '2016-01-01' AND Date < '2016-03-30'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query group suppressions in SendGrid.

Table-Specific Information

Select

The following are examples to query the GroupSuppressions table:

SELECT * FROM GroupSuppressions WHERE GroupId = 391

SELECT * FROM GroupSuppressions WHERE GroupId IN (391, 392, 393)

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query Global Unsubscribes in SendGrid.

View-Specific Information

Select

Only the following query is processed server side:

SQL

SELECT * FROM IPAccessManagement

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query all the mail settings in SendGrid.

Table-Specific Information

Select

You can retrieve all the mail settings.

SELECT * FROM MailSettings

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

List of events related to email message in SendGrid.

NOTE: If you want access to Email events, you must purchase the Email Activity History add-in of SendGrid. If using a Restricted Access key, you must give it the messages.read and email_activity.read scopes.

View-Specific Information

Select

• MessageId supports the '=' comparisons.

SQL

SELECT * FROM MessageEvents WHERE MessageId = '1234'

NOTE: The query below will return message events only for the first 1000 message IDs retrieved from the messages endpoint.

SELECT * FROM MessageEvents;

Columns

Query Email Activities in SendGrid.

NOTE: If you want access to Email activities, you must purchase the Email Activity History add-in of SendGrid. If using a Restricted Access key, you must give it the messages.read and email_activity.read scopes.

View-Specific Information

Select

Note: The API will return only 1000 records. Please choose from the available filters to restrict the search.

• Id supports the '=,IN' comparisons.
• Status supports the '=,!=,LIKE,NOT LIKE' comparisons.
• Subject supports the '=,!=,LIKE,NOT LIKE' comparisons.
• FromEmail supports the '=,!=,LIKE,NOT LIKE' comparisons.
• ToEmail supports the '=,!=,LIKE,NOT LIKE' comparisons.
• Clicks supports the '>,<,<=,>=,=,!=' comparisons.
• LastEventTime supports the '>=, <=, BETWEEN' comparisons.
• ApiKeyId supports the '=,!=,LIKE,NOT LIKE' comparisons.
• AsmGroupId supports the '=,!=,LIKE,NOT LIKE' comparisons.
• MarketingCampaignId supports the '=,!=,LIKE,NOT LIKE' comparisons.
• MarketingCampaignName supports the '=,!=,LIKE,NOT LIKE' comparisons.
• OriginatingIp supports the '=,!=,LIKE,NOT LIKE' comparisons.
• OutboundIp supports the '=,!=,LIKE,NOT LIKE' comparisons.
• Teammate supports the '=,!=,LIKE,NOT LIKE' comparisons.
• TemplateId supports the '=,!=,LIKE,NOT LIKE' comparisons.

For example, the following queries are processed server side:

SQL

SELECT * FROM Messages WHERE Id = '1234'

SELECT * FROM Messages WHERE Id IN ('1234', '4567')

SELECT * FROM Messages WHERE Status = 'not_delivered'

SELECT * FROM Messages WHERE LastEventTime BETWEEN '2020-01-01' AND '2020-01-15'

NOTE: Pagination is not available on this view. Any query—whether filtered or not—will return a maximum of 1000 records. If your filtered query matches more than 1000 records, only the first 1000 will be returned.

Columns

Query New Marketing Campaign Contacts in SendGrid.

View-Specific Information

New Marketing Campaign contacts are recepients that are managed by the user. NMC prefix stands for New Marketing Campaign.

Select

You can retrieve all the NMCContacts or filter them by Id, ListId or SegmentId:

SELECT * FROM NMCCustomFields WHERE Id = 'pe37d765-ba38-46d9-be55-1e45a1ee8c84'
SELECT * FROM NMCCustomFields WHERE Id IN ('pe37d765-ba38-46d9-be55-1e45a1ee8c84', 'fe37d765-ba38-46d9-be55-1e45a1ee8c84')
SELECT * FROM NMCCustomFields WHERE ListId = 't6d51a34-3981-4fa6-bd3f-abdd4de8ff9f'
SELECT * FROM NMCCustomFields WHERE ListId IN ('j6d51a34-3981-4fa6-bd3f-abdd4de8ff9f', 'f6d51b34-3981-4fa6-bd3f-abdd4de8ff9f')
SELECT * FROM NMCCustomFields WHERE SegmentId = '82aeee2d-2870-41e4-b43c-0dcf63481589'
SELECT * FROM NMCCustomFields WHERE SegmentId IN ('82aeee2d-2870-41e4-b43c-0dcf63481589', 'j9d51a34-3981-4fa6-bd3f-abdd4de8ff9f')

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query the available New Marketing Campaign Reserved Fields in SendGrid.

View-Specific Information

Select

This table does not support filtering. NMC prefix stands for New Marketing Campaign.

SELECT * FROM NMCReservedFields

Columns

Query New Marketing Campaign Segments Contacts sample in SendGrid.

Columns

Query all the partner settings in SendGrid.

Table-Specific Information

Select

You can retrieve all the partner settings.

SELECT * FROM PartnerSettings

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Retrieves Single Send click tracking statistics by Id.

View-Specific Information

Select

Get Single Send Click Tracking statistics. To access the view, you need to provide the Single Send ID.

 
SELECT * FROM SingleSendClickTrackingStats where SingleSendId = '74550809-66a2-11ef-ad7f-460f539623db'

Columns

Get all Single Sends statistics.

View-Specific Information

Select

The Cloud uses the SendGrid API to process search criteria that refer to the Id column, while other filters are processed client side within the Cloud.

Get all Single Sends statistics:

SELECT * FROM SingleSendsStats 

Get Single Send statistics by ID:

SELECT * FROM SingleSendsStats where id = '25723e10-669e-11ef-b50f-0e4b6974e9ad'

Columns

Query Browser Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

If any date within the specified range has statistical data, the API will include that data in the resultset and return 0 for other dates. If no dates have non-zero stats within the specified date range, the API will return an empty result set.

SELECT * FROM StatsByBrowser WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Browser Statistics by name.

SELECT * FROM StatsByBrowser WHERE Name = 'Firefox'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query Client Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

If any date within the specified range has statistical data, the API will include that data in the resultset and return 0 for other dates. If no dates have non-zero stats within the specified date range, the API will return an empty result set.

SELECT * FROM StatsByClient WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Client Statistics by name.

SELECT * FROM StatsByClient WHERE Name = 'Other'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query Device Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

If any date within the specified range has statistical data, the API will include that data in the resultset and return 0 for other dates. If no dates have non-zero stats within the specified date range, the API will return an empty result set.

SELECT * FROM StatsByDevice WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Device Statistics by name.

SELECT * FROM StatsByDevice WHERE Name = 'Phone'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query Location Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

If any date within the specified range has statistical data, the API will include that data in the resultset and return 0 for other dates. If no dates have non-zero stats within the specified date range, the API will return an empty result set.

SELECT * FROM StatsByLocation WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Location Statistics by name.

SELECT * FROM StatsByLocation WHERE Name = 'US'

To query Location Statistics by type.

SELECT * FROM StatsByLocation WHERE Type = 'country'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query Mailbox Provider Statistics in SendGrid.

View-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 1 year is used. The Date column supports the >, <, =, <=, and >= operators.

If any date within the specified range has statistical data, the API will include that data in the resultset and return 0 for other dates. If no dates have non-zero stats within the specified date range, the API will return an empty result set.

SELECT * FROM StatsByMailboxProvider WHERE Date > '2016-01-01' AND Date < '2016-03-30'

To query Mailbox Provider Statistics by name.

SELECT * FROM StatsByMailboxProvider WHERE Name = 'Microsoft Outlook Live'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query all the subusers in sendgrid.

View-Specific Information

Select

Query this table to retrieve all subusers.

SELECT * FROM Subusers

You can filter by Username to retrieve a specific subuser.

SELECT * FROM Subusers WHERE Username = 'Example'

Columns

Query Subuser Statistics in SendGrid.

View-Specific Information

Select

You can filter by SubuserName to retrieve stats of a specific subuser.

SELECT * FROM SubuserStats WHERE SubuserName = 'Example'

You can also filter by Date. Operators =, >=, <=, <, and > are supported for this column. If you do not specify one, a default span of the past 1 year is used.

SELECT * FROM SubuserStats WHERE SubuserName = 'News' AND Date > '2016-01-01' AND Date < '2016-01-30'

Columns

Retrieve paged transactional templates

View-Specific Information

Select

The following is an example to query the Templates view:

 
SELECT * FROM Templates

The Cloud will use the SendGrid API to process WHERE clause conditions built with the following column and operator.

• Generation supports the '='.

 
SELECT * FROM Templates WHERE Generation = 'dynamic'

Columns

Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with SendGrid.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from SendGrid, along with an indication of whether the procedure succeeded or failed.

CData Cloud - SendGrid Stored Procedures

Adds a recipient to a list.

Input

Result Set Columns

Gets the Import contacts status

Stored Procedure Specific Information

The GetImportContactsStatus Stored Procedure requires JobId of the Import Contacts, which we can get by executing ImportMarketingContacts stored procedure.

Example:

EXEC GetImportContactsStatus JobId= '1234'

Input

Result Set Columns

Returns a list of all scopes that this user has access to.

Input

Result Set Columns

Import contacts to sendgrid account from CSV file.

Stored Procedure Specific Information

The ImportMarketingContacts Stored Procedure requires Field Mapping Ids and path of the File which contains contacts data.

We can Use custom_field ids or reserved_field ids for Field Mapping and We can get these ids using "Get All Field Definitions" endpoint.

NOTE:

1. Email field mapping is mandatory to import contacts from file.

2. As of now only csv file format is supported for this operation.

Example:

EXEC ImportMarketingContacts FileType= 'csv' , FieldMappings= '_rf2_T,_rf9_T' , FilePath = 'C:\Dev\Tests\v20\ProviderSendgrid\example.csv'

Input

Result Set Columns

Sends a mail.

Procedure-Specific Information

The DynamicTemplateData input requires information about dynamic data of the template. Value for the different variables put into templates.

Example: {"fullname":"Test","paymenttotal":"Test","description":"Test"}

NOTE: The TemplateId column is required when using Dynamic Templates.

Process of adding Persanalizations object to SendMail Stored Procedure

SendMail Requires atleast one personalizations object in the request body and it can be added using the aggregate function.

For Example:

Using TEMP Tables.

INSERT INTO To#TEMP (ToEmail, ToName) VALUES ('[email protected]', 'abc')

INSERT INTO To#TEMP (ToEmail, ToName) VALUES ('[email protected]', 'xyz')

INSERT INTO To#TEMP (ToEmail) VALUES ('[email protected]')

INSERT INTO ToCC#TEMP (ToCCName, ToCCEmail) VALUES ('[email protected]', 'xyz')

INSERT INTO ToBCC#TEMP (ToBCCEmail, ToBCCName) VALUES ('[email protected]', 'abc')

INSERT INTO Content#TEMP (ContentValue, ContentType) VALUES ('[email protected]', 'text/plain')

INSERT INTO Personalizations#TEMP (FromEmail, SendAt, FromName, Subject, To, ToCC, ToBCC) VALUES ('[email protected]', '2017-10-30T10:57:00+01:00', 'hi', 'Test Subject2', 'To#TEMP', 'ToCC#TEMP', 'ToBCC#TEMP')

EXEC SendMail Personalizations = 'Personalizations#TEMP', FromEmail = '[email protected]', Subject = 'hello', Content = 'Content#TEMP' 

Using Aggregate values.

Send Mail to single Mail Address.

INSERT INTO Personalizations#TEMP (FromEmail, SendAt, FromName, Subject, To, ToCC, ToBCC) VALUES ('[email protected]', '2017-10-30T10:57:00+01:00', 'hi', 'Test Subject2',
         '[
		   {
              "ToEmail": "[email protected]",
              "ToName": "abc"
           }
		  ]',
		  '[
            {
              "ToCCName": "xyz",
              "ToCCEmail": "[email protected]"
            }
          ]',
		  '[
            {
              "ToBCCEmail": "[email protected]",
              "ToBCCName": "abcd"
            }
          ]')

EXEC SendMail Personalizations = 'Personalizations#TEMP', FromEmail = '[email protected]', Subject = 'hello', Content =
'{
  "ContentValue":"content",
  "ContentType":"text/plain"
}'

Send Mail to Multiple Mail Addresses.

INSERT INTO Personalizations#TEMP (FromEmail, SendAt, FromName, Subject, To, ToCC, ToBCC) VALUES ('[email protected]', '2017-10-30T10:57:00+01:00', 'hi', 'Test Subject2',
          '[
		    {
              "ToEmail": "[email protected]",
              "ToName": "abc"
            },
            {
              "ToEmail": "[email protected]",
              "ToName": "xyz"
            },
            {
              "ToEmail": "[email protected]"
            }
          ]',
		  '[
            {
              "ToCCName": "sac",
              "ToCCEmail": "[email protected]"
            }
          ]',
		  '[
            {
              "ToBCCEmail": "xyz",
              "ToBCCName": "[email protected]"
            }
          ]')
		  
EXEC SendMail Personalizations = 'Personalizations#TEMP', FromEmail = '[email protected]', Subject = 'hello', Content =
'{
  "ContentValue":"content",
  "ContentType":"text/html"
}'

Send Mail using attachment.

INSERT INTO To#TEMP (ToEmail, ToName) VALUES ('[email protected]', 'abc')

INSERT INTO To#TEMP (ToEmail, ToName) VALUES ('[email protected]', 'xyz')

INSERT INTO To#TEMP (ToEmail) VALUES ('[email protected]')

INSERT INTO ToCC#TEMP (ToCCName, ToCCEmail) VALUES ('[email protected]', 'xyz')

INSERT INTO ToBCC#TEMP (ToBCCEmail, ToBCCName) VALUES ('[email protected]', 'abc')

INSERT INTO Content#TEMP (ContentValue, ContentType) VALUES ('[email protected]', 'text/plain')

INSERT INTO Attachments#TEMP (AttachmentContent, AttachmentFilename) VALUES ('abcdefghijklmn', 'Test2.png')

INSERT INTO Personalizations#TEMP (FromEmail, SendAt, FromName, Subject, To, ToCC, ToBCC) VALUES ('[email protected]', '2017-10-30T10:57:00+01:00', 'hi', 'Test Subject2', 'To#TEMP', 'ToCC#TEMP', 'ToBCC#TEMP')

EXEC SendMail Personalizations = 'Personalizations#TEMP', FromEmail = '[email protected]', Subject = 'hello', Content = 'Content#TEMP', Attachments = 'Attachments#TEMP'

Input

Result Set Columns

Sends a mail. This Stored Procedure is used to handle aggregates.

Procedure-Specific Information

SendMail2 Stored Procedure is specifically made for handling the aggregates. User has to provide the whole json structure in order to execute this Stored Procedure.

For Example:

EXECUTE SendMail2 Personalizations = '[{"to": [{"email": "[email protected]", "name": "John Doe"}, {"email": "[email protected]", "name": "Julia Doe"}], "cc": [{"email": "[email protected]", "name": "Jane Doe"}], "bcc": [{"email": "[email protected]", "name": "Jim Doe"}]}, {"from": {"email": "[email protected]", "name": "Example Sales Team"}, "to": [{"email": "[email protected]", "name": "Janice Doe"}], "bcc": [{"email": "[email protected]", "name": "Jordan Doe"}]}]', FromEmail = '[email protected]', FromName = 'Example Order Confirmation', Subject = 'Test Subject', Content = '[{"type": "text/html", "value": "test value"}]', Categories = '["cake", "pie"]'

Input

Result Set Columns

Sends a test campaign.

Input

Result Set Columns

The CData Cloud models the Legacy API as relational tables, views, and stored procedures. Most tables, views, and stored procedures are defined in schema files, which are simple, text-based configuration files.

However, the Recipients table is dynamic: The Cloud connects to SendGrid and gets the metadata for this table by calling the appropriate Web services. Any changes you make to the custom filters you define for Recipients are available when you connect; you can use these custom filters as columns in the WHERE clause. These custom filters are surfaced in the CustomFields table.

The Cloud uses the SendGrid API to process supported filters. The Cloud processes other filters client-side within the Cloud.

To use the Legacy Data Model, simply set Schema to Legacy.

Tables

Tables describes the available tables that are easy to query and update.

Views

Views are tables that cannot be modified. Typically, read-only data are shown as views.

Stored Procedures

Stored Procedures are function-like interfaces to Sendgrid. They can be used to search, update, and modify information.

The Cloud models the data in SendGrid as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - SendGrid Tables

Create, update, delete, and query Alerts in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM Alerts WHERE Id = '148825'

Insert

The following columns are required in an insert for Stats Notification: Type, EmailTo, Frequency.

INSERT INTO Alerts (Type, EmailTo, Frequency) VALUES ('stats_notification', '[email protected]', 'daily')

The following columns are required in an insert for Usage Limit: Type, EmailTo, Percentage.

INSERT INTO Alerts (Type, EmailTo, Percentage) VALUES ('usage_limit', '[email protected]', 20)

Update

You can only update EmailTo by specifying an Id.

UPDATE Alerts SET EmailTo = '[email protected]' WHERE Id = 1310789

Delete

You can only delete from Alerts by specifying an Id.

DELETE FROM Alerts WHERE Id = 1310789

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query, Create, Update and Delete the available ApiKeys in SendGrid.

Table-Specific Information

Select

To get all the Api Keys

SELECT * FROM ApiKeys

To get information about a specific Api Key

SELECT * FROM Apikeys WHERE ApiKeyId = 'Dqdvdvdvdv4yLeuVQqO3rFxN5ktUxA'

Insert

The following columns are required in an insert: Name. VALUES for Scopes are: alerts.create, alerts.read, alerts.update, alerts.delete, asm.groups.create, asm.groups.read, asm.groups.update, asm.groups.delete, ips.pools.ips.read, mail.send, mail_settings.bcc.read, mail_settings.bcc.update, mail_settings.address_whitelist.read, mail_settings.address_whitelist.update, mail_settings.footer.read, mail_settings.footer.update, mail_settings.forward_spam.read, mail_settings.forward_spam.update, mail_settings.plain_content.read, mail_settings.plain_content.update, mail_settings.spam_check.read, mail_settings.spam_check.update, mail_settings.bounce_purge.read, mail_settings.bounce_purge.update, mail_settings.forward_bounce.read, mail_settings.forward_bounce.update, partner_settings.new_relic.read, partner_settings.new_relic.update, partner_settings.sendwithus.read, partner_settings.sendwithus.update, tracking_settings.click.read, tracking_settings.click.update, tracking_settings.subscription.read, tracking_settings.subscription.update, tracking_settings.open.read, tracking_settings.open.update, tracking_settings.google_analytics.read, tracking_settings.google_analytics.update, user.webhooks.event.settings.read, user.webhooks.event.settings.update, user.webhooks.event.test.create, user.webhooks.event.test.read, user.webhooks.event.test.update, user.webhooks.parse.settings.create, user.webhooks.parse.settings.read, user.webhooks.parse.settings.update, user.webhooks.parse.settings.delete, stats.read, stats.global.read, categories.stats.read, categories.stats.sums.read, devices.stats.read, clients.stats.read, clients.phone.stats.read, clients.tablet.stats.read, clients.webmail.stats.read, clients.desktop.stats.read, geo.stats.read, mailbox_providers.stats.read, browsers.stats.read, user.webhooks.parse.stats.read, templates.create, templates.read, templates.update, templates.delete, templates.versions.create, templates.versions.read, templates.versions.update, templates.versions.delete, templates.versions.activate.create, user.timezone.read, user.timezone.update, user.settings.enforced_tls.read, user.settings.enforced_tls.update, api_keys.create, api_keys.read, api_keys.update, api_keys.delete, email_activity.read, categories.create, categories.read, categories.update, categories.delete, mail_settings.template.read, mail_settings.template.update, marketing_campaigns.create, marketing_campaigns.read, marketing_campaigns.update, marketing_campaigns.delete, mail.batch.create, mail.batch.read, mail.batch.update, mail.batch.delete, user.scheduled_sends.create, user.scheduled_sends.read, user.scheduled_sends.update, user.scheduled_sends.delete, access_settings.whitelist.create, access_settings.whitelist.read, access_settings.whitelist.update, access_settings.whitelist.delete, access_settings.activity.read, whitelabel.create, whitelabel.read, whitelabel.update, whitelabel.delete, suppression.create, suppression.read, suppression.update, suppression.delete, messages.read

INSERT INTO Apikeys (Name) VALUES ('apiname')

INSERT INTO Apikeys (Name, Scopes, Sample) VALUES ('apiname', 'alerts.create,alerts.read', 'test')

Update

You can update ApiKeys by specifying an ApiKeyid.You need to specify both the name and the scopes during the UPDATE even if you don't want to update it.

UPDATE Apikeys SET Name = 'apiname2', Scopes = 'alerts.create,alerts.read' WHERE apikeyid = 'Dqdvdvdvdv4yLeuVQqO3rFxN5kscsctUxA-P_Q'

Delete

You can Delete ApiKeys by specifying an ApiKeyid.

DELETE FROM ApiKeys WHERE apikeyid = 'CMp4C8klscscscsQ0GxqvxtK2-P_Q'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query and Delete the available Blocks in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 120 days is used. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM Blocks WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

Delete

You can only delete from Blocks by specifying an Email.

DELETE FROM Blocks WHERE Email = '[email protected]'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query and Delete the available Bounces in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. If you do not specify one, a default span of the past 120 days is used. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM Bounces WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

Delete

You can only delete from Bounces by specifying an Email.

DELETE FROM Bounces WHERE Email = '[email protected]'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Update, delete, and query Cancel Scheduled Sends in SendGrid.

Table-Specific Information

Select

This table only supports filtering by BatchId. The following are the only possible SELECT queries:

SELECT * FROM CancelScheduledSends
SELECT * FROM CancelScheduledSends WHERE BatchId = 148825

Update

You can update CancelScheduledSends by specifying an BatchId.

Allowed Values : cancel, pause

UPDATE CancelScheduledSends SET Status = 'cancel' WHERE BatchId = 1310789

Delete

You can only delete from CancelScheduledSends by specifying an BatchId.

DELETE FROM CancelScheduledSends WHERE BatchId = 1310789

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, delete, and query Custom Fields in SendGrid.

Table Specific Information

Custom fields are filters for a Recipient that are defined by the user.

Select

You can retrieve all the CustomFields or filter them by Id:

SELECT * FROM CustomFields WHERE Id = '1'

Use quotations to filter on Custom Fields regardless of type.

Insert

To insert on CustomFields specify the Name of the field and its Type. The Type can be text, date, or number.

INSERT INTO CustomFields (name, type) VALUES ('age', 'number') 

Update

This table does not support updating.

Delete

You can only delete from CustomFields by specifying an Id:

DELETE FROM CustomFields WHERE Id = '1'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query Global Unsubscribes in SendGrid.

Table-Specific Information

Select

You need to specify a time span to select from this table. However this filter is optional. The CreatedAt column supports the >, <, =, <=, and >= operators.

SELECT * FROM GlobalUnsubscribes WHERE CreatedAt > '2016-01-01' AND CreatedAt < '2016-01-30'

INSERT

Performing an Insert operation in this table you need to specify an email which will be added to the GlobalUnsubscribes list. For example:

INSERT INTO GlobalUnsubscribes (Email) VALUES ('[email protected]')

DELETE

Performing a Delete operation in this table you need to specify an email which will be removed from the GlobalUnsubscribes list. For example:

DELETE FROM GlobalUnsubscribes WHERE Email = '[email protected]'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Query and Delete invalid emails in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Email. The following are the only possible SELECT queries:

SELECT * FROM InvalidEmails
SELECT * FROM InvalidEmails WHERE Email = '[email protected]'

Delete

You can only delete from InvalidEmails by specifying an Email.

DELETE FROM InvalidEmails WHERE Email = '[email protected]'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query the available Lists in SendGrid.

Table-Specific Information

Select

The Lists table can only filtered by its Id.

SELECT * FROM Lists WHERE Id = '123'

Insert

Only the Name for the List is required to insert into this table.

INSERT INTO Lists (Name) VALUES ('TestList')

Update

You can only update from Lists by specifying an Id.

UPDATE Lists SET Name = 'New Name' WHERE Id = '123'

Delete

You can only delete from Lists by specifying an Id.

DELETE FROM Lists WHERE Id = '123'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Create, update, delete, and query Marketing Campaigns in SendGrid.

Table-Specific Information

Select

This table only supports filtering by Id. The following are the only possible SELECT queries:

SELECT * FROM MarketingCampaigns
SELECT * FROM MarketingCampaigns WHERE Id = '1'

Insert

The following columns are required in an insert: Title, Subject, SenderId, ListIds, SegmentIds, Categories, SuppressionGroupId, HtmlContent, and PlainContent.

Note that a CustomUnsubscribeUrl can be used in place of a SuppressionGroupId. However, SendGrid highly recommends to use SuppressionGroupId.

Use the check points below to find the SenderId and SuppressionGroupId inputs:

• To find the SenderId, navigate to Marketing -> MarketingCampaigns -> Senders. Create a new sender if you do not have one already. Then click the gear on the Action column and select Edit. The Id is displayed on the navigation bar after /senders/id.
• To find the SuppressionGroupId, insert into the UnsubscribeGroups table; the created Id is the SuppressionGroupId.

INSERT INTO MarketingCampaigns (Title, Subject, SenderId, ListIds, Categories, SuppressionGroupId, HtmlContent, PlainContent) VALUES ('Test Campaign', 'March Newsletter', '123', '1,2', 'fruits, health', '12', '<b>Eat Healthy</b>', 'Eat healthy')

Update

You can only update MarketingCampaigns by specifying an Id.

UPDATE MarketingCampaigns SET PlainContent = 'Eat healthier' WHERE Id = '123'

Delete

You can only delete from MarketingCampaigns by specifying an Id.

DELETE FROM MarketingCampaigns WHERE Id = '123'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

The SendGrid table Recipients.

Table Specific Information

SendGrid has the ability to categorize Recipients with filters defined by the user. Accordingly, you can execute statements to the Recipients table to search by and insert these filters. However, these filters are reported as attributes of the Custom Fields table.

Select

The following query retrieves only the default fields of the Recipients table, not the Custom Fields that you have created. To retrieve the values of the Custom Fields of a Recipient, query the Custom Fields table.

You can also retrieve all Recipients inside a certain List or a Segment, but it is not possible to filter by both. Use multiple queries instead:

SELECT * FROM Recipients WHERE SegmentId = '1'
SELECT * FROM Recipients WHERE ListId = '2'

You can build criteria depending on a Custom Field. When querying use quotations regardless of type.This kind of filtering is done client side. In this case Age is a CustomField created by the user.

SELECT * FROM Recipients WHERE Age = '30'

Insert

To insert into Recipients Email is required.

You can insert Custom Fields into the Recipients table. See the CustomFields section for more information about how to create custom fields.

INSERT INTO Recipients (Email, FirstName, LastName, Age, Department ... Other Custom Fields as necessary) VALUES ('[email protected]', 'name', 'surname', '30', 'Education' ... Other Custom Field values)

Update

You can only update from this table by specifying an Id. Additionally, the Email column cannot be updated.

UPDATE Recipients SET Age = '12' WHERE Id = '1'

Delete

You can only delete from this table by specifying an Id.

DELETE FROM Recipients WHERE Id = '1'

Columns