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

Connecting to Twitter Ads

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

Accessing Data from CData Cloud Services

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

Connect to Twitter Ads 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.

Connecting to Twitter Ads

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

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 CData Cloud models Twitter Ads entities in relational Tables, Views, and Stored Procedures.

API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Views

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

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

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

CData Cloud - Twitter Ads Tables

Retrieve details for some or all permissions associated with the specified tailored audience.

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.

Retrieve details for some or all Tailored Audiences associated with the current account.

View Specific Information

Select

Only the Id filter is supported.

SELECT * FROM AdAudiences WHERE Id = 'TXR1'

Insert

This table supports simple Insert and batch Insert operations.

Simple Insert:

INSERT INTO [AdAudiences] (name) VALUES ('test')

Batch Insert:

Batch inserts are used to generate a new audience based on existing audiences and by setting a couple of conditions on them.

For example: To create a new audience based on the most active users of X1 AND (X2 or X3) audience the structure would be like this:

INSERT INTO a#TEMP (CustomAudienceId, frequency, frequencyComparator, lookbackWindow, negate) VALUES ('TYIF', 1, 'NUM_GT', 30, true)

INSERT INTO b#TEMP (CustomAudienceId, lookbackWindow) VALUES ('TXR1', 30)

INSERT INTO c#TEMP (CustomAudienceId, frequency, frequencyComparator, lookbackWindow, negate) VALUES ('TYFO', 1, 'NUM_GT', 30, true)

INSERT INTO AdAudiences#TEMP (audienceType, name, segments) VALUES ('FLEXIBLE', 'Test1', 'a#TEMP AND (b#TEMP OR c#TEMP)')

INSERT INTO [AdAudiences] (audienceType, name, segments) SELECT audienceType, name, segments FROM [AdAudiences#TEMP]

Make sure to set the required columns on initial temp tables, for example a#TEMP, which are: CustomAudienceId and LookbackWindow.

Values that are set in these temp table will be used by AdAudiences#TEMP in the Segments column, which will be used as a criteria, combined with AND/OR logical operators.

Update

Simple Update:

UPDATE [AdAudiences] SET Name = 'testModified' WHERE Name = 'test'

Batch Update:

Batch updates are used to modify an existing audience by setting a couple of new conditions on it.

For example: To modify an audience based on the most active users of X1 AND (X2 or X3) audience the structure would be like this:

INSERT INTO a#TEMP (CustomAudienceId, frequency, frequencyComparator, lookbackWindow, negate) VALUES ('TYIF', 1, 'NUM_GT', 30, true)

INSERT INTO b#TEMP (CustomAudienceId, lookbackWindow) VALUES ('TXR1', 30)

INSERT INTO c#TEMP (CustomAudienceId, frequency, frequencyComparator, lookbackWindow, negate) VALUES ('TYFO', 1, 'NUM_GT', 30, true)

INSERT INTO AdAudiences#TEMP (audienceType, name, segments) VALUES ('FLEXIBLE', 'Test1', 'a#TEMP AND (b#TEMP OR c#TEMP)')

UPDATE [AdAudiences] (audienceType, name, segments) SELECT audienceType, name, segments FROM [AdAudiences#TEMP]

Delete

Simple Delete:

DELETE FROM [AdAudiences] WHERE Name = 'test'

Batch Delete:

INSERT INTO a#TEMP (CustomAudienceId, frequency, frequencyComparator, lookbackWindow, negate) VALUES ('TYIF', 1, 'NUM_GT', 30, true)

INSERT INTO b#TEMP (CustomAudienceId, lookbackWindow) VALUES ('TXR1', 30)

INSERT INTO c#TEMP (CustomAudienceId, frequency, frequencyComparator, lookbackWindow, negate) VALUES ('TYFO', 1, 'NUM_GT', 30, true)

INSERT INTO AdAudiences#TEMP (audienceType, name, segments) VALUES ('FLEXIBLE', 'Test1', 'a#TEMP AND (b#TEMP OR c#TEMP)')

DELETE FROM [AdAudiences] WHERE EXISTS SELECT audienceType, name, segments FROM [AdAudiences#TEMP]

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 - Twitter Ads Views

Retrieve all of the advertising-enabled accounts the authenticating user has access to.

View Specific Information

Select

There are no available filters for this view.

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.

Retrieve details about which entities' analytics metrics have changed in a given time period.

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.

Retrieve reach and average frequency analytics for specified campaigns.

Columns

Retrieve details for some or all campaigns associated with the current account.

View Specific Information

Select

Only the AccountId filter is supported. This is a common required filter for all views related to analytics. If specified, it will override the value of the AccountId connection property. Otherwise, if no value is specified, the Cloud will try to get the first account from the list returned by the AdAccount view.

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.

Retrieve details for some or all cards associated with the current account.

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.

Retrieve some or all funding instruments associated with the account specified in the path.

View Specific Information

Select

Only the AccountId filter is supported. This is a common required filter for all views related to analytics. If specified, it will override the value of the AccountId connection property. Otherwise, if no value is specified, the Cloud will try to get the first account from the list returned by the AdAccount view.

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.

Retrieve reach and average frequency analytics for specified funding instruments.

Columns

Retrieve some or all funding instruments associated with the account specified.

View Specific Information

Select

Only the AccountId filter is supported. This is a common required filter for all views related to analytics. If specified, it will override the value of the AccountId connection property. Otherwise, if no value is specified, the Cloud will try to get the first account from the list returned by the AdAccount view.

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.

Retrieve all of the advertising-enabled Tweets the authenticating user has access to.

View Specific Information

Select

Only the AccountId filter is supported. This is a common required filter for all views related to analytics. If specified, it will override the value of the AccountId connection property. Otherwise, if no value is specified, the Cloud will try to get the first account from the list returned by the AdAccount view.

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 information about all available metrics for a certain entity.

View Specific Information

AdStats contain all available analytic metrics for all types of entities. When querying this view only the metrics supported by the specified entity will be populated with values.

Support for Twitter Ads Asynchronous API

Select

Below are the fields that can be specified to filter the results.

SELECT * FROM AdStats WHERE StartTime = '12/02/2016' AND EndTime = '12/09/2016'

SELECT * FROM AdStats WHERE EntityId = '807000624822882304' AND Entity = 'ORGANIC_TWEET'

SELECT * FROM AdStats WHERE Granularity = 'HOUR'

SELECT * FROM AdStats WHERE Placement = 'PUBLISHER_NETWORK'

The listed filters are optional, but it is recommended to specify at least the Entity, EntityId, and the time window.

SELECT * FROM AdStats WHERE EntityId = '807000624822882304' AND Entity = 'ORGANIC_TWEET' AND StartTime = '12/02/2016' AND EndTime = '12/09/2016' 

Columns

Retrieve all of the advertising-enabled Tweets the authenticating user has access to.

View Specific Information

Select

The AccountId field is filterable server-side.

SELECT * FROM AdTweets WHERE AccountId = '1234'

SELECT * FROM AdTweets WHERE AccountId IN (SELECT Id FROM AdAccounts)

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.

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

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

CData Cloud - Twitter Ads Stored Procedures

Add, update and remove users from a given custom audience id.

Input

Result Set Columns

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for Twitter Ads:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the Cloud can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries, including batch operations::

• sys_identity: Returns information about batch operations or single updates.

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

Lists the available schemas.

The following query retrieves all available schemas:

          SELECT * FROM sys_schemas
          

Columns

Lists the available tables.

The following query retrieves the available tables and views:

          SELECT * FROM sys_tables
          

Columns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the AdAudiences table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='AdAudiences' 

Columns

Lists the available stored procedures.

The following query retrieves the available stored procedures:

          SELECT * FROM sys_procedures
          

Columns

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the GetOAuthAuthorizationUrl stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='GetOAuthAuthorizationUrl' AND Direction=1 OR Direction=2

Columns

Describes the primary and foreign keys.

The following query retrieves the primary key for the AdAudiences table:

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='AdAudiences' 
          

Columns

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
          

Columns

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

         SELECT * FROM sys_primarykeys
          

Columns

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

          SELECT * FROM sys_indexes WHERE IsPrimary='false'
          

Columns

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:twitterads:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

Describes the SELECT query processing that the Cloud can offload to the data source.

See SQL Compliance for SQL syntax details.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Columns

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

         SELECT * FROM sys_identity
          

Columns

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

Authentication

OAuth

SSL

Logging

Schema

Miscellaneous

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

Identifier of the advertising account. This Id is used to get analytic stats for the account that is identified by this Id. The AddAccounts view can be used to list available accounts with their specific Ids.

Data Type

string

Default Value

""

Remarks

Identifier of the advertising account. This Id is used to get analytic stats for the account that is identified by this Id. The AddAccounts view can be used to list available accounts with their specific Ids.

The current version of Twitter API.

Data Type

string

Default Value

"11"

Remarks

The current version of Twitter API.

A boolean that indicates whether or not you would like to use app-only authentication.

Data Type

bool

Default Value

false

Remarks

Set this to true to have your Twitter app log in to Twitter instead of a user.

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

The client Id assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.

The client secret assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.

The authentication token used to request and obtain the OAuth Access Token.

Data Type

string

Default Value

""

Remarks

This property is required only when performing headless authentication in OAuth 1.0. It can be obtained from the GetOAuthAuthorizationUrl stored procedure.

It can be supplied alongside the AuthKey in the GetOAuthAccessToken stored procedure to obtain the OAuthAccessToken.

The authentication key used to request and obtain the OAuth Access Token.

Data Type

string

Default Value

""

Remarks

This property is required only when performing headless authentication in OAuth 1.0. It can be obtained from the GetOAuthAuthorizationUrl stored procedure.

It can be supplied alongside the AuthToken in the GetOAuthAccessToken stored procedure to obtain the OAuthAccessToken.

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

The verbosity level that determines the amount of detail included in the log file.

Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the Cloud reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are detailed in the Logging page.

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

Specifies if is Twitter Premium account. This is false by default.

Data Type

bool

Default Value

false

Remarks

Specifies if is Twitter Premium account. This is false by default.

The maximum amount of time to delay (in seconds) before submitting a request if it would be rate limited.

Data Type

string

Default Value

"60"

Remarks

Twitter uses different rate limits for total number of requests for different endpoints. These can range from as few as 15 per 15 minute window, up to 900 for a 15 minute window. Internally the CData Cloud keeps track of if a given request would result in a rate limit exception. If a rate limit would occur, the CData Cloud can internally delay submitting a request until the limit is up. However, this could also result in waiting for several minutes before requesting data, which is also not a good behavior.

The MaxRateLimitDelay gives control over the maximum amount of time the CData Cloud will wait once it detects a rate limit would occur. Since the amount of time the CData Cloud needs to wait can be calculated, if it would have to wait longer than the MaxRateLimitDelay, it will simply error immediately when it sees the time would take too long.

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

Default SearchTerms if none are specified. Used for some tables such as Tweets where SearchTerms may be specified.

Data Type

string

Default Value

""

Remarks

Default SearchTerms if none are specified. Used for some tables such as Tweets where SearchTerms may be specified.

The number of results to return per page of data retrieved from the Twitter stream.

Data Type

string

Default Value

"50"

Remarks

The number of results to return per page of data retrieved from the Twitter stream.

This property represents the maximum time to read streaming data, in seconds.

Data Type

int

Default Value

0

Remarks

Set StreamReadDuration property to consume streaming data for a specific time period. When StreamReadDuration is reached and StreamTimeout is 0, then the connection will be closed.

The maximum number of seconds to continue waiting for results while streaming. When this value is reached and no tweets are returned, then the connection will be closed.

Data Type

string

Default Value

"0"

Remarks

Set the value of StreamTimeout to 0 in order to keep the connection open indefinitely. Note that, if the value of this property is greater than zero, the value of the StreamPageSize property, will be overwriten and will be set to one(1).

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

int

Default Value

60

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the Cloud throws an exception.

This field sets whether the Twitter Ads Asynchronous API is enabled.

Data Type

bool

Default Value

false

Remarks

AdStats is the only table impacted.

Note that when you use the Async API, the Twitter Ads driver polls for a response. How long it waits before timing out is governed by the Timeout connection property. The default is timeout value is 60 seconds, so if you receive a timeout error, increase the value.