HubSpot Data Model

Connection String Options

  1. Auto Cache
  2. Batch Size
  3. Cache Connection
  4. Cache Driver
  5. Cache Location
  6. Cache Metadata
  7. Cache Query Result
  8. Cache Tolerance
  9. Callback URL
  10. Column Sizes
  11. Company Properties File
  12. Connection Life Time
  13. Connect On Open
  14. Contact Properties File
  15. Deal Properties File
  16. Firewall Password
  17. Firewall Port
  18. Firewall Server
  19. Firewall Type
  20. Firewall User
  21. Initiate OAuth
  22. Location
  23. Logfile
  24. Max Log File Size
  25. OAuth Access Token
  26. OAuth Client Id
  27. OAuth Client Secret
  28. OAuth Refresh Token
  29. OAuth Settings Location
  30. OAuth Verifier
  31. Offline
  32. Other
  33. Pool Idle Timeout
  34. Pool Max Size
  35. Pool Min Size
  36. Pool Wait Time
  37. Proxy Auth Scheme
  38. Proxy Auto Detect
  39. Proxy Exceptions
  40. Proxy Password
  41. Proxy Port
  42. Proxy Server
  43. Proxy SSL Type
  44. Proxy User
  45. Pseudo Columns
  46. Readonly
  47. RTK
  48. SSL Server Cert
  49. Support Enhanced SQL
  50. Tables
  51. Timeout
  52. Use Connection Pooling
  53. Use Display Names
  54. Use Simple Names
  55. Verbosity
  56. Views

Auto Cache

Data Type

bool

Default Value

false

Remarks

When AutoCache is set, the driver automatically maintains a cache of your table's data in the database of your choice. By default, the driver incrementally updates the cache, retrieving only changes since the last SELECT query was run if the length of time since the last run has exceeded the CacheTolerance. After the cache is updated, the query is executed against the cached data. With CacheQueryResult additionally set, the driver updates the cache when you execute a SELECT query and returns the live results from the HubSpot data.

 

Explicitly Caching SELECT Results

 

CacheQueryResult is a way to query HubSpot in real time while maintaining a cache for offline use. Set CacheQueryResult to update the cache whenever you execute a SELECT statement. When you execute a SELECT statement with AutoCache and CacheQueryResult set, the driver executes the query to the remote data and caches the results; rows that already exist are overwritten. That is, SELECT statements are used to create and refresh the cache, not to query it. Data manipulation commands are executed to the remote data as well.

To query the cached data, set the Offline property. If you need to query the cached data in an online connection, you can append #CACHE to the table name. For example:

SELECT * FROM [Contacts#CACHE]

 

 

Setting the Caching Database

 

When AutoCache is set, the driver caches to a simple, file-based cache. You can configure its location or cache to a different database with the following properties:

 

 

See Also

 

 

  • CacheTolerance: Control the tolerance for stale data in the cache.
  • CacheQueryResult: Insert or update each row returned into the corresponding table in the cache.
  • Caching: Best Practices: This section provides more examples of using AutoCache with Offline, as well as information on determining a caching strategy.
  • CacheMetadata: This property reduces the amount of metadata that crosses the network by persisting table schemas retrieved from the HubSpot metadata. Metadata then needs to be retrieved only once instead of every connection.
  • REPLICATE Statements: When you execute a SELECT statement with AutoCache set, the driver internally calls the REPLICATE statement. The REPLICATE statement enables incremental updates instead of first dropping the cached table.
  • CACHE Statements: You can use the CACHE statement to persist any SELECT query, as well as manage the cache; for example, refreshing schemas.

 

 

 

Batch Size

Data Type

int

Default Value

0

Remarks

When BatchSize is set to a value greater than 0, the batch operation will split the entire batch into separate batches of size BatchSize. The split batches will then be submitted to the server individually. This is useful when the server has limitations on the size of the request that can be submitted.

Setting BatchSize to 0 will submit the entire batch as specified.

 

Cache Connection

Data Type

string

Default Value

""

Remarks

 

The cache database is determined based on the CacheDriver and CacheConnection properties. Both properies are required to use the cache database. Examples of common cache database settings can be found below. For more information on setting the caching database's driver, refer to CacheDriver.

The connection string specified in the CacheConnection property is passed directly to the underlying CacheDriver. Consult the documentation for the specific JDBC driver for more information on the available properties. Make sure to include the JDBC driver in your application's classpath.

 

Derby and Java DB

 

The driver simplifies caching to Derby, only requiring you to set the CacheLocation property to make a basic connection.

Alternatively, you can configure the connection to Derby manually using CacheProvider and CacheConnection. Below is the Derby JDBC URL syntax:

jdbc:derby:[subsubprotocol:][databaseName][;attribute=value[;attribute=value] ... ]
For example, to cache to an in-memory database, use the following:
jdbc:derby:memory

 

 

SQLite

 

To cache to SQLite, you can use the SQLite JDBC driver. Below is the syntax of the JDBC URL:

jdbc:sqlite:dataSource
  • Data Source: The path to an SQLite database file. Or, use a value of :memory to cache in memory.

 

 

MySQL

 

The installation includes the CData JDBC Driver for MySQL. Below is an example JDBC URL:

jdbc:mysql:User=root;Password=root;Server=localhost;Port=3306;Database=cache
Below are typical connection properties:

 

 

  • Server: The IP address or domain name of the server you want to connect to.
  • Port: The port that the server is running on.
  • User: The username provided for authentication to the database.
  • Password: The password provided for authentication to the database.
  • Database: The name of the database.
SQL Server

 

The JDBC URL for the Microsoft JDBC Driver for SQL Server has the following syntax:

jdbc:sqlserver://[serverName[\instance][:port]][;database=databaseName][;property=value[;property=value] ... ]
For example:
jdbc:sqlserver://localhost\sqlexpress:1433;integratedSecurity=true
Below are typical SQL Server connection properties:
  • Server: The name or network address of the computer running SQL Server. To connect to a named instance instead of the default instance, this property can be used to specify the host name and the instance, separated by a backslash.
  • Port: The port SQL Server is running on.
  • Database: The name of the SQL Server database.
  • Integrated Security: Set this option to true to use the current Windows account for authentication. Set this option to false if you are setting the User and Password in the connection.

    To use integrated security, you will also need to add sqljdbc_auth.dll to a folder on the Windows system path. This file is located in the auth subfolder of the Microsoft JDBC Driver for SQL Server installation. The bitness of the assembly must match the bitness of your JVM.

  • User ID: The username provided for authentication with SQL Server. Only needed if you are not using integrated security.
  • Password: The password provided for authentication with SQL Server. Only needed if you are not using integrated security.
Oracle

 

Below is the conventional JDBC URL syntax for the Oracle JDBC Thin driver:

jdbc:oracle:thin:[userId/password]@[//]host[[:port][:sid]]
For example:
jdbc:oracle:thin:scott/tiger@myhost:1521:orcl
Below are typical connection properties:
  • Data Source: The connect descriptor that identifies the Oracle database. This can be a TNS connect descriptor, an Oracle Net Services name that resolves to a connect descriptor, or, after version 11g, an Easy Connect naming (the host name of the Oracle server with an optional port and service name).

  • Password: The password provided for authentication with the Oracle database.
  • User Id: The user Id provided for authentication with the Oracle database.
PostgreSQL

 

Below is the JDBC URL syntax for the official PostgreSQL JDBC driver:

jdbc:postgresql:[//[host[:port]]/]database[[?option=value][[&option=value][&option=value] ... ]]
For example, the following connection string connects to a database on the default host (localhost) and port (5432):
jdbc:postgresql:postgres
Below are typical connection properties:
  • Host: The address of the server hosting the PostgreSQL database.
  • Port: The port used to connect to the server hosting the PostgreSQL database.
  • Database: The name of the database.
  • Username: The user Id provided for authentication with the PostgreSQL database. You can specify this in the JDBC URL with the "user" parameter.
  • Password: The password provided for authentication with the PostgreSQL database.

 

 

Cache Driver

Data Type

string

Default Value

""

Remarks

 

You can cache to any database for which you have a JDBC driver, including CData JDBC drivers.

The cache database is determined based on the CacheDriver and CacheConnection properties. The CacheDriver is the name of the JDBC driver class that you would like to use to cache data.

Note that you must also add the CacheDriver JAR to the classpath.

The following examples show how to cache to several major databases. Refer to CacheConnection for more information on the JDBC URL syntax and typical connection properties.

Derby and Java DB

 

 

The driver simplifies Derby configuration. Java DB is the Oracle distribution of Derby. The JAR is shipped in the JDK. You can find the JAR, derby.jar, in the db subfolder of the JDK installation. In most caching scenarios, you need to specify only the following, after adding derby.jar to the classpath.

jdbc:hubspot:CacheLocation='c:/Temp/cachedir';InitiateOAuth=GETANDREFRESH;
To customize the Derby JDBC URL, use CacheDriver and CacheConnection. For example, to cache to an in-memory database, use a JDBC URL like the following:
jdbc:hubspot:CacheDriver=org.apache.derby.jdbc.EmbeddedDriver;CacheConnection='jdbc:derby:memory';InitiateOAuth=GETANDREFRESH;
SQLite

 

Below is a JDBC URL for the SQLite JDBC driver:

jdbc:hubspot:CacheDriver=org.sqlite.JDBC;CacheConnection='jdbc:sqlite:C:/Temp/sqlite.db';InitiateOAuth=GETANDREFRESH;
MySQL

 

Below is a JDBC URL for the included CData JDBC Driver for MySQL:

  jdbc:hubspot:Cache Driver=cdata.jdbc.mysql.MySQLDriver;Cache Connection='jdbc:mysql:Server=localhost;Port=3306;Database=cache;User=root;Password=123456';InitiateOAuth=GETANDREFRESH;

  
The CData JDBC Driver for MySQL is located in the lib subfolder of the CData JDBC Driver for HubSpot 2018 installation directory.
SQL Server

 

The following JDBC URL uses the Microsoft JDBC Driver for SQL Server:

jdbc:hubspot:Cache Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver;Cache Connection='jdbc:sqlserver://localhost\sqlexpress:7437;user=sa;password=123456;databaseName=Cache';InitiateOAuth=GETANDREFRESH;
Oracle

 

Below is a JDBC URL for the Oracle Thin Client:

jdbc:hubspot:Cache Driver=oracle.jdbc.driver.OracleDriver;CacheConnection='jdbc:oracle:thin:scott/tiger@localhost:1521:orcldb';InitiateOAuth=GETANDREFRESH;
PostgreSQL

 

The following JDBC URL uses the official PostgreSQL JDBC driver:

jdbc:hubspot:CacheDriver=org.postgresql.Driver;CacheConnection='jdbc:postgresql://localhost:5433/postgres?user=postgres&password=admin';InitiateOAuth=GETANDREFRESH;

 

 

Cache Location

Data Type

string

Default Value

""

Remarks

The CacheLocation is a simple, file-based cache. The driver uses Java DB, Oracle's distribution of the Derby database. To cache to Java DB, you will need to add the Java DB JAR to the classpath. The JAR, derby.jar, is shipped in the JDK and located in the db subfolder of the JDK installation.

CacheLocation defaults to the directory specified by the Location setting.

See Also

 

 

  • AutoCache: Set this to implicitly create and maintain a cache for later offline use.
  • CacheMetadata: Set this to persist the HubSpot catalog in CacheLocation.

 

 

Cache Metadata

Data Type

bool

Default Value

false

Remarks

As you execute queries with this property set, table metadata in the HubSpot catalog are cached to the file store specified by CacheLocation if set or the user's home directory otherwise. A table's metadata will be retrieved only once, when the table is queried for the first time.

When to Use CacheMetadata

 

The driver automatically persists metadata in memory for up to two hours when you first discover the metadata for a table or view and therefore, CacheMetadata is generally not required. CacheMetadata becomes useful when metadata operations are expensive such as when you are working with large amounts of metadata or when you have many short-lived connections.

When Not to Use CacheMetadata

 

 

  • When you are working with volatile metadata: Metadata for a table is only retrieved the first time the connection to the table is made. To pick up new, changed, or deleted columns, you would need to delete and rebuild the metadata cache. Therefore, it is best to rely on the in-memory caching for cases where metdata will change often.
  • When you are caching to a database: CacheMetadata can only be used with CacheLocation. If you are caching to another database with the CacheDriver and CacheConnection properties, use AutoCache to cache implicitly. Or, use REPLICATE Statements or CACHE Statements to cache explicitly.

 

 

Cache Query Result

Data Type

bool

Default Value

false

Remarks

When CacheQueryResult and AutoCache are set, the rows returned from a SELECT query are cached in the cache database. The driver handles caching in a streaming fashion with each row being processed into the cache database from the original result set as you read the row from the returned ResultSet object. This ensures that the live data is not queried twice. Note that any rows you do not read from the returned ResultSet will not be updated in the cache.

 

Cache Tolerance

Data Type

string

Default Value

"600"

Remarks

The tolerance for stale data in the cache specified in seconds. This only applies when AutoCache is used. The driver will check with the data source for newer records after the tolerance interval has expired. Otherwise it will return the data directly from the cache.

 

Callback URL

Data Type

string

Default Value

""

Remarks

 

During the authentication process, the OAuth authorization server redirects the user to this URL. This value must match the callback URL you specify in your app settings.

 

Column Sizes

Data Type

string

Default Value

""

Remarks

Some custom fields may requre a larger column size than the default. These can be entered as name=value pairs. For instance: Col1=4000,Col2=5000,Col3=13. The values entered will override the default for any detected custom fields.

 

Company Properties File

Data Type

string

Default Value

""

Remarks

In HubSpot, you can potentially have so many custom fields that attempting to select all of them at a time will cause HubSpot to throw an error. In a situation where you do not have direct control over the SQL Statement but still need to get custom field data back, you set the CompanyPropertiesFile to the full location of a file that lists which custom fields to select. For instance, C:\users\public\documents\customfields.txt.

The CustomFields document itself is just a comma separated list of the names of which custom fields to select. Note that these must be the API names of the custom fields. These names can be retrieved from the Name column of the DealProperties table. For instance: dealname,amount,description.

 

Connection Life Time

Data Type

string

Default Value

"0"

Remarks

The maximum lifetime of a connection in seconds. Once the time has elapsed, the connection object is disposed. The default is 0 which indicates there is no limit to the connection lifetime.

 

Connect On Open

Data Type

bool

Default Value

false

Remarks

When set to 'true', a connection will be made to HubSpot when the connection is opened. This property enables the 'Test Connection' feature available in various database tools.

This feature acts as a NOOP command as it is used to verify a connection can be made to HubSpot and nothing from this initial connection is maintained.

Setting this property to 'false' may provide performance improvements (depending upon the number of times a connection is opened).

 

Contact Properties File

Data Type

string

Default Value

""

Remarks

In HubSpot, you can potentially have so many custom fields that attempting to select all of them at a time will cause HubSpot to throw an error. In a situation where you do not have direct control over the SQL Statement but still need to get custom field data back, you set the ContactPropertiesFile to the full location of a file that lists which custom fields to select. For instance, C:\users\public\documents\customfields.txt.

The CustomFields document itself is just a comma separated list of the names of which custom fields to select. Note that these must be the API names of the custom fields. These names can be retrieved from the Name column of the ContactProperties table. For instance: firstname,lastname,lastmodifieddate.

 

Deal Properties File

Data Type

string

Default Value

""

Remarks

In HubSpot, you can potentially have so many custom fields that attempting to select all of them at a time will cause HubSpot to throw an error. In a situation where you do not have direct control over the SQL Statement but still need to get custom field data back, you set the DealPropertiesFile to the full location of a file that lists which custom fields to select. For instance, C:\users\public\documents\customfields.txt.

The CustomFields document itself is just a comma separated list of the names of which custom fields to select. Note that these must be the API names of the custom fields. These names can be retrieved from the Name column of the DealProperties table. For instance: dealname,amount,description.

 

Firewall Password

Data Type

string

Default Value

""

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

 

Firewall Port

Data Type

string

Default Value

""

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

 

Firewall Server

Data Type

string

Default Value

""

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the driver uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

 

Firewall Type

Data Type

string

Default Value

"NONE"

Remarks

This property specifies the protocol that the driver will use to tunnel traffic through the FirewallServer proxy. Note that by default the driver connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.

 

   
Type Default Port Description
TUNNEL 80 When this is set, the driver opens a connection to HubSpot and traffic flows back and forth through the proxy.
SOCKS4 1080 When this is set, the driver sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted.
SOCKS5 1080 When this is set, the driver sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

 

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

 

 

Firewall User

Data Type

string

Default Value

""

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

 

Initiate OAuth

Data Type

string

Default Value

"OFF"

Remarks

The following options are available:

  1. OFF: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.
  2. GETANDREFRESH: Indicates that the entire OAuth Flow will be handled by the driver. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.
  3. REFRESH: Indicates that the driver will only handle refreshing the OAuthAccessToken. The user will never be prompted by the driver to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.

 

 

Location

Data Type

string

Default Value

""

Remarks

The path to a directory which contains the schema files for the driver (.rsd files for tables and views, .rsb files for stored procedures). The Location property is only needed if you would like to customize definitions (e.g., change a column name, ignore a column, etc.) or extend the data model with new tables, views, or stored procedures.

The schema files are deployed alongside the driver assemblies. You must also ensure that Location points to the folder that contains the schema files. The folder location can be a relative path from the location of the executable.

 

Logfile

Data Type

string

Default Value

""

Remarks

For more control over what is written to the log file, take a look at Verbosity.

 

Max Log File Size

Data Type

string

Default Value

"20MB"

Remarks

A string specifying the maximum size in bytes for a log file (ex: 10MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end. The default limit is 20MB. Values lower than 100kB will use 100kB as the value instead.

 

OAuth Access Token

Data Type

string

Default Value

""

Remarks

The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.

The access token is used in place of your username and password. The access token protects your credentials by keeping them on the server.

 

OAuth Client Id

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.

 

OAuth Client Secret

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.

 

OAuth Refresh Token

Data Type

string

Default Value

""

Remarks

The OAuthRefreshToken property is used to refresh the OAuthAccessToken when using OAuth authentication.

 

OAuth Settings Location

Data Type

string

Default Value

"%APPDATA%\\CData\\HubSpot Data Provider\\OAuthSettings.txt"

Remarks

When InitiateOAuth is set to GETANDREFRESH or REFRESH, the driver saves OAuth values to a settings file to avoid requiring the user to manually enter OAuth connection properties. The default OAuthSettingsLocation is a settings file located in the %AppData%\CData folder.

 

OAuth Verifier

Data Type

string

Default Value

""

Remarks

The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems. The verifier will only be used when InitiateOAuth is set to GETANDREFRESH. Once the OAuth settings file has been generated, the verifier can be removed from the connection properties.

 

 

Offline

Data Type

bool

Default Value

false

Remarks

When Offline is set to TRUE, all queries execute against the cache as opposed to the live data source. In this mode, certain queries like INSERT, UPDATE, DELETE, and CACHE are not allowed.

 

Other

Data Type

string

Default Value

""

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Caching Configuration

 

   
CachePartial=True Caches only a subset of columns, which you can specify in your query.
QueryPassthrough=True Passes the specified query to the cache database instead of using the SQL parser of the driver.

 

Integration and Formatting

 

   
DefaultColumnSize Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT Whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename Records the underlying socket data transfer to the specified file.

 

 

Pool Idle Timeout

Data Type

string

Default Value

""

Remarks

The allowed idle time from when the connection is free to when the connection is released and returned to the pool. The default is 60 seconds.

 

Pool Max Size

Data Type

string

Default Value

"100"

Remarks

The maximum connections in the pool. The default is 100. To disable this property, set the property value to 0 or less.

 

Pool Min Size

Data Type

string

Default Value

"1"

Remarks

The minimum number of connections in the pool. The default is 1.

 

Pool Wait Time

Data Type

string

Default Value

""

Remarks

The max seconds to wait for a connection to become available. If a new connection request is waiting for an available connection and exceeds this time, an error is thrown. By default, new requests wait forever for an available connection.

 

Proxy Auth Scheme

Data Type

string

Default Value

"BASIC"

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the driver will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

  • BASIC: The driver performs HTTP BASIC authentication.
  • DIGEST: The driver performs HTTP DIGEST authentication.
  • NEGOTIATE: The driver retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
  • PROPRIETARY: The driver does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.
If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

 

 

Proxy Auto Detect

Data Type

bool

Default Value

true

Remarks

By default, the driver uses the system HTTP proxy. Set this to false if you want to connect to another proxy.

To connect to an HTTP proxy, see ProxyServer.

For other proxies, such as SOCKS or tunneling, see FirewallType.

 

Proxy Exceptions

Data Type

string

Default Value

""

Remarks

The ProxyServer will be used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the driver will use the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you will need to set ProxyAutoDetect to false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

 

Proxy Password

Data Type

string

Default Value

""

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the driver uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

 

Proxy Port

Data Type

string

Default Value

"80"

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

 

Proxy Server

Data Type

string

Default Value

""

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The driver can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the driver uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

 

Proxy SSL Type

Data Type

string

Default Value

"AUTO"

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:

 

   
AUTO Default setting. If the URL is an HTTPS URL, the driver will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.
ALWAYS The connection is always SSL enabled.
NEVER The connection is not SSL enabled.
TUNNEL The connection is through a tunneling proxy: The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.

 

 

Proxy User

Data Type

string

Default Value

""

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the username of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a username in one of the following formats:

user@domain

domain\user

 

 

Pseudo Columns

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; i.e., "*=*".

 

Readonly

Data Type

bool

Default Value

false

Remarks

If this property is set to true, the driver will allow only SELECT queries. INSERT, UPDATE, DELETE, and stored procedure queries will cause an error to be thrown.

 

RTK

Data Type

string

Default Value

""

Remarks

 

The RTK property may be used to license a build. Please see the included licensing file to see how to set this property. The runtime key is only available if you purchased an OEM license.

 

SSL Server Cert

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 will be rejected.

This property can take the forms:

 

   
Description Example
A full PEM Certificate (example shortened for brevity) -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate C:\cert.cer
The public key (example shortened for brevity) -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated) ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated) 34a929226ae0819f2ec14b4a3d904f801cbb150d

 

If not specified, any certificate trusted by the machine will be accepted. Use '*' to signify to accept all certificates (not recommended for security concerns).

 

Support Enhanced SQL

Data Type

bool

Default Value

true

Remarks

When SupportEnhancedSQL is set to true, the driver offloads as much of the SELECT statement processing as possible to HubSpot and then processes the rest of the query in memory. In this way the driver can execute unsupported predicates, joins, and aggregation.

When SupportEnhancedSQL is set to false, the driver limits SQL execution to what is supported by the HubSpot API.

Execution of Predicates

 

The driver determines which of the clauses are supported by the data source and then pushes them to the source to get the smallest superset of rows that would satisfy the query. It then filters the rest of the rows locally. The filter operation is streamed, which enables the driver to filter effectively for even very large datasets.

Execution of Joins

 

The driver uses various techniques to join in memory. The driver trades off memory utilization against the requirement of reading the same table more than once.

Execution of Aggregates

 

The driver retrieves all rows necessary to process the aggregation in memory.

 

Tables

Data Type

string

Default Value

""

Remarks

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

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. For example: Tables=TableA,TableB,TableC

 

Timeout

Data Type

string

Default Value

"60"

Remarks

If the Timeout property is set to 0, operations do not time out: They run until they complete successfully or encounter an error condition.

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

 

Use Connection Pooling

Data Type

bool

Default Value

false

Remarks

Enables connection pooling. The default is false. See Connection Pooling for information on using connection pools.

 

Use Display Names

Data Type

bool

Default Value

true

Remarks

Boolean determining if the display names for the columns should be used instead of the API names.

 

Use Simple Names

Data Type

bool

Default Value

false

Remarks

Boolean determining if simple names should be used for tables and columns. This will only affect custom fields, which may have non-standard SQL characters in them. UseSimpleNames makes the driver easier to use with traditional database tools.

Setting UseSimpleNames to true will simplify the names of custom field columns returned. If set to false, the custom field columns will appear as they do in HubSpot.

 

Verbosity

Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the driver reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are described below:

 

   
1 Setting Verbosity to 1 will log the query, the number of rows returned by it, the start of execution and the time taken, and any errors.
2 Setting Verbosity to 2 will log everything included in Verbosity 1, cache queries, and additional information about the request, if applicable, such as HTTP headers.
3 Setting Verbosity to 3 will additionally log the body of the request and the response.
4 Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation.
5 Setting Verbosity to 5 will additionally log communication with the data source and additional details that may be helpful in troubleshooting problems. This includes interface commands.

The Verbosity should not be set to greater than 1 for normal operation. Substantial amounts of data can be logged at higher verbosities, which can delay execution times.

 

Views

Data Type

string

Default Value

""

Remarks

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

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. For example: For example: Views=ViewsA,ViewsB,ViewsC




 

Tables

  1. Companies
  2. CompanyProperties
  3. CompanyPropertyGroups
  4. ContactLists
  5. ContactProperties
  6. ContactPropertyGroups
  7. Contacts
  8. ContactsInList
  9. DealAssociations
  10. DealProperties
  11. DealPropertyGroups
  12. Deals
  13. EmailCampaigns
  14. EmailSubscriptions
  15. Engagements
  16. Forms
  17. Products
  18. Settings
  19. SocialMediaMessages
  20. Workflows

Companies

Companies may be kept track of in HubSpot just like contacts. They can be added to, updated, and removed from this table.

 

Table Specific Information

 

Companies in HubSpot represent different organizations you are interested in keeping track of for potential marketing opportunities. They are treated similarly like contacts and have a number of custom properties that will be dynamically determined and added to the Companies table from your HubSpot Hub.

Select

 

When selecting companies, they can only be filtered by the CompanyId and only one Company at a time. Otherwise they can be selected without a filter, which will cause all companies in your HubSpot account to be listed. For example:

SELECT * FROM Companies WHERE CompanyId = '123456789'
Columns

 

 

 

   
Name Type ReadOnly References Description
CopmanyId [KEY] String True  

The id of the company in HubSpot.

IsDeleted Boolean True  

A boolean indicating if the company is deleted.

OtherProperties String True  

Other properties about the company will dynamically obtained at runtime and will vary depending on the company properties specific to your HubSpot HUB.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

CompanyProperties

Company properties are the standard and custom pieces of field data that appear in HubSpot.

 

Table Specific Information

 

Company properties are a number of custom properties that are available to store data about the companies in your HubSpot Hub. Some of these properties are added by HubSpot and cannot be deleted. They will be automatically added to the Companies on a new connection.

Select

 

Company properties can only be filtered by the unique company property name. For example:

SELECT * FROM CompanyProperties WHERE Name = 'property_name'
Columns

 

 

 

   
Name Type ReadOnly References Description
Name [KEY] String False  

The name of the contact property. The name must contain only lowercase alphabetical characters and numbers and must start with a lowercase alphabetical character.

Label String False  

The human readable label for the company property that will display in the HubSpot UI.

FieldType String False  

The type of field that will display on the screen for the company property.

The allowed values are textarea, select, text, date, file, number, radio, booleancheckbox, checkbox.

Type String False  

The stored machine type for the company property.

The allowed values are string, number, bool, datetime, enumeration.

GroupName String False

ContactPropertyGroups.Name

The group the company property is a part of.

Description String False  

A description of what the property is for.

DisplayMode String False  

How the property will be displayed.

DisplayOrder Integer False  

The order in which the property should be displayed in relation to other properties in the same group.

ExternalOptions Boolean False  

Boolean indicating if there are external options associate with the company property.

IsFormField Boolean False  

Boolean indicating if the property is a field that is part of a form.

IsCalculated Boolean False  

Boolean indicating if the property is calculated.

IsHidden Boolean False  

Boolean indicating if the property is hidden.

IsHubspotDefined Boolean False  

Boolean indicating if the property is defined by HubSpot.

MutableDefinitionNotDeletable Boolean False  

Boolean indicating if the property can be modified but not deleted.

ReadOnlyDefinition Boolean False  

Boolean indicating if the definition for the property is read only.

ReadOnlyValue Boolean False  

Boolean indicating if the value of the property is read only.

OptionsAggregate String False  

An aggregate of additional options for the property. Will have a value if the FieldType is radio, checkbox, or booleancheckbox.

ExtraUrlParameters String True  

An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

CompanyPropertyGroups

Company property groups in HubSpot offer a way of organizing individual types of properties for companies. Each company property must belong to a property group.

 

Table Specific Information

 

Company property groups offer a means of organizing the various custom properties that are available for defining properties about a given company. Company property groups may be selected, inserted, updated, or deleted from this table.

Select

 

Company property groups may only be filtered by the unique company property group name. For example:

SELECT * FROM CompanyPropertyGroups WHERE Name = 'property_group_name'
Columns

 

 

 

   
Name Type ReadOnly References Description
Name [KEY] String False  

The name of the contact property group.

DisplayName String False  

The display name of the contact group.

DisplayOrder Integer False  

The numerical order of the contact group with respect to other contact groups.

ExtraUrlParameters String True  

An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

ContactLists

Contact lists in HubSpot can be used to group together contacts with similar characteristics.

 

Table Specific Information

 

Contact lists represent lists that contacts can be added to in order to more easily sort them. They can be either manual lists or dynamic lists. Contacts will be added to the list automatically when they are added to HubSpot based on some criteria.

Select

 

Contact lists can be filtered by ListId or by whether or not they are dynamic. For example:

SELECT * FROM ContactLists WHERE ListId = '123456789'

SELECT * FROM ContactLists WHERE IsDynamic = 'false'

 

Multiple ListIds may be specified together with an IN operator:

SELECT * FROM ContactLists WHERE ListId IN (1,2,3)
Columns

 

 

 

   
Name Type ReadOnly References Description
ListId [KEY] Long True  

The unique id of the list.

Name String False  

The name of the contact list.

CreatedAt Datetime True  

When the contact list was created.

UpdatedAt Datetime True  

When the contact list was last updated.

IsDynamic Boolean False  

A boolean indicating if the contact list is dynamic.

FiltersAggregate String False  

An aggregate of filters that a dynamic contact list uses to determine if a newly created contact should be added to the list.

InternalListId Long True  

An internal id for the contact list.

LastProcessingStateChangeAt Datetime True  

Metadata describing when the last processing state was changed.

ProcessingState String True  

Metadata indicating the current stage of processing.

LastSizeChangeAt Datetime True  

Metadata describing when the last time the contact list size was changed.

ListSize Integer True  

Metadata indicating the size of the contact list.

ExtraUrlParameters String True  

An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

ContactProperties

Contact properties are the standard and custom pieces of field data that appear in HubSpot.

 

Table Specific Information

 

Contact properties are a number of custom properties that are available to store data about the contacts in your HubSpot Hub. Some of these properties are added by HubSpot and cannot be deleted. They will be automatically added to the Contacts on a new connection.

Select

 

Contact properties can only be filtered by the unique contact property name or group name. For example:

SELECT * FROM ContactProperties WHERE Name = 'property_name'

SELECT * FROM ContactProperties WHERE GroupName = 'group_name'
Columns

 

 

 

   
Name Type ReadOnly References Description
Name [KEY] String False  

The name of the contact property. The name must contain only lowercase alphabetical characters and numbers and must start with a lowercase alphabetical character.

Label String False  

The human readable label for the contact property that will display in the HubSpot UI.

FieldType String False  

The type of field that will display on the screen for the company property.

The allowed values are textarea, select, text, date, file, number, radio, checkbox.

Type String False  

The stored machine type for the company property.

The allowed values are string, number, bool, datetime, enumeration.

GroupName String False

ContactPropertyGroups.Name

The group the company property is a part of.

Description String False  

A description of what the property is for.

DisplayMode String False  

How the property will be displayed.

DisplayOrder Integer False  

The order in which the property should be displayed in relation to other properties in the same group.

ExternalOptions Boolean False  

Boolean indicating if there are external options associate with the company property.

IsFormField Boolean False  

Boolean indicating if the property is a field that is part of a form.

IsCalculated Boolean False  

Boolean indicating if the property is calculated.

IsHidden Boolean False  

Boolean indicating if the property is hidden.

IsFavorited Boolean False  

Boolean indicating if the property is favorited.

FavoritedOrder Integer False  

The favorited order for this property.

MutableDefinitionNotDeletable Boolean False  

Boolean indicating if the property can be modified but not deleted.

ReadOnlyDefinition Boolean False  

Boolean indicating if the definition for the property is read only.

ReadOnlyValue Boolean False  

Boolean indicating if the value of the property is read only.

OptionsAggregate String False  

An aggregate of additional options for the property. Will have a value if the FieldType is radio, checkbox, or booleancheckbox.

ExtraUrlParameters String True  

An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

ContactPropertyGroups

Contact property groups in HubSpot offer a way of organizing individual types of properties for contacts. Each contact property must belong to a property group.

 

Table Specific Information

 

Contact property groups offer a means of organizing the various custom properties that are available for defining properties about a given contact. Contact property groups can be selected, inserted, updated, or deleted from this table.

Select

 

Contact property groups can only be filtered by the unique contact property group name. For example:

SELECT * FROM ContactPropertyGroups WHERE Name = 'property_group_name'
Columns

 

 

 

   
Name Type ReadOnly References Description
Name [KEY] String True  

The name of the contact property group.

DisplayName String False  

The display name of the contact group.

DisplayOrder Integer False  

The numerical order of the contact group with respect to other contact groups.

ExtraUrlParameters String True  

An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

Contacts

Your contacts in HubSpot may be used for marketing campaigns and can be retrieved, inserted, updated, and deleted from this table.

 

Table Specific Information

 

Contacts in HubSpot represent different contacts you are interested in keeping track of for potential marketing opportunities. Contacts are similar to companies: They both have a number of custom properties that will be dynamically determined and added to the table from your HubSpot Hub.

Select

 

When selecting contacts, they can only be filtered by the VID, Email, UserToken, ListId, or AssociatedCompanyId. However, most of these filters cannot be used at the same time. The accepted filters are illustrated below:

SELECT * FROM Contacts WHERE VID = 123456789

SELECT * FROM Contacts WHERE VID IN (123, 456)

SELECT * FROM Contacts WHERE VID > 123456789

SELECT * FROM Contacts WHERE Email = 'username@email.com'

SELECT * FROM Contacts WHERE Email IN ('username@email.com', 'user2@email.com')

SELECT * FROM Contacts WHERE UserToken = 'ABC123456'

SELECT * FROM Contacts WHERE SearchTerms = 'Contact Name'

SELECT * FROM Contacts WHERE ListId = '123456789'

SELECT * FROM Contacts WHERE AssociatedCompanyId = '123456789'
Columns

 

 

 

   
Name Type ReadOnly References Description
VID [KEY] String True  

The unique id for the contact.

ListId Integer True  

The list id the contact is a part of.

SearchTerms Integer True  

An input only field representing potential searchterms you would like to specify when selecting contacts.

CanonicalVid Integer True  

The cannonical vid associated with the contact.

ProfileToken String True  

A token for the profile of the contact.

ProfileUrl String True  

A url to the profile of the contact.

IdentityProfilesAggregate String True  

An aggregate of identities associated with this contact if any.

FormSubmissionsAggregate String True  

An aggregate of forms submitted by this contact if any.

MergeAuditsAggregate String True  

An aggregate of merged audits associated with this contact.

MergedVidsAggregate String True  

An aggregate of merged vids associated with this contact.

OtherProperties String True  

Other properties about the contact will dynamically obtained at runtime and will vary depending on the contact properties specific to your HubSpot HUB.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

ContactsInList

Retrieves the contacts in a given list in HubSpot. New contacts may be added to the list or removed from it. A ListId must be specified to retrieve the current contacts in the list.

 

Table Specific Information

 

Contacts in a given list can be selected, inserted, and removed from that list using this table.

Select

 

When selecting from ContactsInList, the ListId must be specified in order to retrieve any results. For example:

SELECT * FROM ContactsInList WHERE ListId = '123456789'
Columns

 

 

 

   
Name Type ReadOnly References Description
VID [KEY] String False  

The unique id for the contact.

ListId [KEY] Integer False  

The list id the contact is a part of.

CanonicalVid Integer True  

The cannonical vid associated with the contact.

ProfileToken String True  

A token for the profile of the contact.

ProfileUrl String True  

A url to the profile of the contact.

IdentityProfilesAggregate String True  

An aggregate of identities associated with this contact if any.

FormSubmissionsAggregate String True  

An aggregate of forms submitted by this contact if any.

MergeAuditsAggregate String True  

An aggregate of merged audits associated with this contact.

MergedVidsAggregate String True  

An aggregate of merged vids associated with this contact.

OtherProperties String True  

Other properties about the contact will dynamically obtained at runtime and will vary depending on the contact properties specific to your HubSpot HUB.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

DealAssociations

Deal associations can be used to insert or delete individual associations with Deals.

 

Table Specific Information

 

Deal associations represent Company and Contact associations made with existing deals that may be modified.

Select

 

When selecting deal associations, they can only be filtered by the Id. For example:

SELECT * FROM DealAssociations WHERE DealId = 12345
Insert

 

New associations may be inserted by specifying the DealId, AssociationId, and the AssociationType during an INSERT statement. For example:

INSERT INTO DealAssociations (DealId, AssociationId, AssociationType) VALUES (12345, 6789, 'COMPANY')
Delete

 

Associations may be removed by specifying the DealId, AssociationId, and the AssociationType during a DELETE statement. For example:

DELETE FROM DealAssociations WHERE DealId = 12345 AND AssociationId = 6789 AND AssociationType = 'COMPANY'
Columns

 

 

 

   
Name Type ReadOnly References Description
DealId [KEY] Long False

Deals.DealId

The Id of the deal.

AssociatedId [KEY] Long False  

The Id of the associated entity.

AssociationType [KEY] String False  

The type of association such as Company, or Contact.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

DealProperties

Deal properties are the standard and custom pieces of field data that appear in HubSpot.

 

Table Specific Information

 

Deal properties are a number of custom properties that are available to store data about the deals in your HubSpot Hub. Some of these properties are added by HubSpot and cannot be deleted. They will be automatically added to the Deals on a new connection.

Select

 

Deal properties can only be filtered by the unique deal property name. For example:

SELECT * FROM DealProperties WHERE Name = 'property_name'
Columns

 

 

 

   
Name Type ReadOnly References Description
Name [KEY] String False  

The name of the contact property. The name must contain only lowercase alphabetical characters and numbers and must start with a lowercase alphabetical character.

Label String False  

The human readable label for the contact property that will display in the HubSpot UI.

FieldType String False  

The type of field that will display on the screen for the company property.

The allowed values are textarea, select, text, date, file, number, radio, checkbox.

Type String False  

The stored machine type for the company property.

The allowed values are string, number, bool, datetime, enumeration.

GroupName String False

DealPropertyGroups.Name

The group the company property is a part of.

Description String False  

A description of what the property is for.

DisplayMode String False  

How the property will be displayed.

DisplayOrder Integer False  

The order in which the property should be displayed in relation to other properties in the same group.

ExternalOptions Boolean False  

Boolean indicating if there are external options associate with the company property.

IsFormField Boolean False  

Boolean indicating if the property is a field that is part of a form.

IsCalculated Boolean False  

Boolean indicating if the property is calculated.

IsHidden Boolean False  

Boolean indicating if the property is hidden.

IsDeleted Boolean True  

Boolean indicating if the property has been deleted.

IsHubspotDefined Boolean True  

Boolean indicating if the property is defined by HubSpot.

ShowCurrencySymbol Boolean False  

Boolean indicating if the currency symbol should be shown. Only applicable for numbers.

MutableDefinitionNotDeletable Boolean False  

Boolean indicating if the property can be modified but not deleted.

ReadOnlyDefinition Boolean False  

Boolean indicating if the definition for the property is read only.

ReadOnlyValue Boolean False  

Boolean indicating if the value of the property is read only.

OptionsUpdatable Boolean False  

Boolean indicating if the options can be modified.

OptionsAggregate String False  

An aggregate of additional options for the property. Will have a value if the FieldType is radio, checkbox, or booleancheckbox.

ExtraUrlParameters String True  

An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

DealPropertyGroups

Contact property groups in HubSpot offer a way of organizing individual types of properties for contacts. Each contact property must belong to a property group.

 

Table Specific Information

 

Deal property groups offer a means of organizing the various custom properties that are available for defining properties about a given deal. Deal property groups may be selected, inserted, updated, or deleted from this table.

Select

 

Deal property groups may only be filtered by the unique deal property group name. For example:

SELECT * FROM DealPropertyGroups WHERE Name = 'property_group_name'
Columns

 

 

 

   
Name Type ReadOnly References Description
Name [KEY] String False  

The name of the contact property group.

DisplayName String False  

The display name of the contact group.

DisplayOrder Integer False  

The numerical order of the contact group with respect to other contact groups.

ExtraUrlParameters String True  

An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

Deals

Your deals in HubSpot which may be used for making offers to potential customers.

 

Table Specific Information

 

Deals in HubSpot represent different organizations you are interested in keeping track of for potential marketing opportunities. They are treated similarly like contacts and have a number of custom properties that will be dynamically determined and added to the Deals table from your HubSpot Hub.

Select

 

When selecting deals, they can only be filtered by the DealId and only one Deal at a time. Otherwise they can be selected without a filter, which will cause all deals in your HubSpot account to be listed. For example:

SELECT * FROM Deals WHERE DealId = '123456789'
Columns

 

 

 

   
Name Type ReadOnly References Description
DealId [KEY] Long True  

The unique id for the deal.

DealName String True  

The name of the deal.

IsDeleted Boolean True  

A boolean indicating if the deal has been deleted.

AssociatedCompanyIds String True  

A comma separated list of company ids associated with the deal.

AssociatedDealIds String True  

A comma separated list of other deals associated with this deal.

AssociatedVids String True  

A comma separated list of contact ids associated with this deal.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

EmailCampaigns

Email campaigns in HubSpot allow you to keep track of and update email marketing campaigns. This table allows you to create, update, and delete your email campaigns in HubSpot.

 

Table Specific Information

 

Email campaigns in HubSpot represent email marketing campaigns you may send to many different contacts.

Select

 

Email campaign data can only be retrieved from HubSpot via a direct request for a specific HubSpot id. To retrieve all campaigns, first all campaign ids must be retrieved. Then one request for individual campaign data at a time must be submitted. These requests are done automatically, but because of the number of individual requests made for data, this can cause for slow response times.

When selecting email campaigns, may be filtered by the Id. For example:

SELECT * FROM EmailCampaigns WHERE Id = '123456789'

 

The LastUpdatedTime will not normally be return with a value. It will only come back when specifying WHERE LastUpdatedTime > 'value'. This will trigger a request to HubSpot for recently modified EmailCampaigns. However, be aware that HubSpot only provides a way to retrieve recently modified EmailCampaigns. Not all campaigns that match the criteria may be returned.

Columns

 

 

 

   
Name Type ReadOnly References Description
Id [KEY] Long True  

The Id of the email campaign.

AppName String False  

The name of the app associated with the email campaign.

AppId Long True  

The Id of the app associated with the email campaign.

LastUpdatedTime Datetime True  

When the email campaign was lasted updated.

Name String True  

The name of the campaign.

ContentId Long True  

The ContentId of the email campaign.

NumberBounced Long True  

The number bounced.

NumberClick Long True  

The number of clicks.

NumberDeferred Long True  

The number deferred.

NumberDelivered Long True  

The number delivered.

NumberDropped Long True  

The number dropped.

NumberIncluded Long True  

The number included.

NumberMTADropped Long True  

The number mta dropped.

NumberOpen Long True  

The number open.

NumberProcessed Long True  

The number processed.

NumberQueued Long True  

The number queued.

NumberSent Long True  

The number sent.

NumberStatusChanged Long True  

The number where the status was changed.

NumberUnsubscribed Long True  

The number unsubscribed.

ProcessingState String True  

The processing state of the email campaign.

Type String True  

The type of email campaign.

SubType String True  

The subtype of the email campaign.

Subject String True  

The subject of the email campaign.

LastProcessingStartedAt Datetime True  

Last date the email campaign last began processing at.

LastProcessingFinishedAt Datetime True  

When the email campaign last finished processing at.

LastProcessingStateChangeAt Datetime True  

The last time the email campaign's processing state changed.

ExtraUrlParameters String True  

An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

EmailSubscriptions

The subscription types a given email is subscribed to. An email must be specified to return results. Subscriptions may be removed by deleting them.

 

Table Specific Information

 

Email subscriptions in HubSpot represent different types of subscriptions a given contact email may be subscribed to. EmailSubscriptions lists the subscriptions a given email address is subscribed to.

Select

 

When selecting email subscriptions, an email must be provided. For example:

SELECT * FROM EmailSubscriptions WHERE Email = 'user@email.com'
Columns

 

 

 

   
Name Type ReadOnly References Description
Email [KEY] String True  

The email address which has been subscribed.

SubscriptionId [KEY] Long False  

The id of the subscription type.

IsSubscribed Boolean True  

Boolean indicating if the customer is subscribed.

ExtraUrlParameters String True  

An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

Engagements

Engagements represent any of a number of different types of engagements you have in HubSpot.

 

Table Specific Information

 

Engagements represent any of a number of different types of engagements you have in HubSpot. These can very from simple Tasks, to Emails, Calls, Meetings, or others. Due to the different types of engagements available, any individual Engagement will have several columns that come back null due to its type.

Select

 

When selecting engagements, they can only be filtered by the Id. For instance:

SELECT * FROM Engagements WHERE Id = 12345
Insert and Update

 

When inserting Engagements, a Type must be specified. In addition, different fields are available on insert depending on the type used. The available fields for each type are as follows: CALL,EMAIL,MEETING,NOTE,PUBLISHING_TASK,TASK

  • CALL: ToNumber, FromNumber, Body, DurationMilliseconds, Status, ExternalId, ExternalAccountId, RecordingUrl, Disposition
  • EMAIL: FromEmail, FromFirstName, FromLastName, ToEmail, Cc, Bcc, Subject, EmailHtml, EmailText
  • MEETING: Body, StartTime, EndTime, Title
  • PUBLISHING_TASK: Body, CampaignGuid, Category, CategoryId, State, Name
  • TASK: Body, Status, ForObjectType
In addition, there are some common fields available when inserting or updating any type of Engagement. These include DateTime, AssociatedContacts, AssociatedCompanies, AssociatedDeals, AssociatedOwners, and AssociatedWorkflows. For instance:
INSERT INTO Engagements (DateTime, Type, Body, CampaignGuid, Category, CategoryId, ContentId, State, Name, AssociatedContacts) VALUES ('1/1/2011', 'PUBLISHING_TASK', 'Cool Post with Topics', 'f43fe9fd-4082-4a45-93d8-cb8a88f01654', 'BLOG_POST', 3, '2682673052', 'TODO', 'Test Blog Task With topics 3', '234,567')
Columns

 

 

 

   
Name Type ReadOnly References Description
Id [KEY] Long True  

The id of the engagement.

PortalId Integer True  

The portal id the engagement is associated with.

IsActive Boolean True  

Boolean indicating if the engagement is active or not.

CreatedAt Datetime True  

When the engagement was created.

UpdatedAt Datetime True  

When the engagement was last updated.

CreatedBy Integer True  

User id of the user who created the engagement.

ModifiedBy Integer True  

User id of the user who lastmodified the engagement.

OwnerId Integer True

Owners.OwnerId

The owner id of the engagement.

Type String False  

The type of engagement. Required on inserts.

The allowed values are CALL, EMAIL, MEETING, NOTE, PUBLISHING_TASK, TASK.

ActivityType String True  

The activity type of engagement.

DateTime Datetime False  

A custom datetime that can be specified when inserting the engagement. This can be used for keeping track of something related to the engagement such as when a call was made.

AssociatedContacts String False  

A comma separated list of contact ids associated with the engagement.

AssociatedCompanies String False  

A comma separated list of company ids associated with the engagement.

AssociatedDeals String True  

A comma separated list of deal ids associated with the engagement.

AssociatedOwners String True  

A comma separated list of owner ids associated with the engagement.

AssociatedWorkflows String False  

A comma separated list of workflow ids associated with the engagement.

MessageId String False  

The messageId of an engagement.

ThreadId String False  

The threadId of an engagement.

Body String False  

The body of an engagement. Only used when Type = NOTE, TASK, PUBLISHING_TASK, CALL, or MEETING.

Status String False  

The status of the task. Only used when Type = TASK, CALL.

ForObjectType String False  

The object type the task is for. For instance, CONTACT. Only used when Type = TASK.

StartTime String False  

The start date time for the meeting. Only used when Type = MEETING.

EndTime String False  

The end date time for the meeting. Only used when Type = MEETING.

Title String False  

The title of the meeting. Only used when Type = MEETING.

FromEmail String False  

The from email in the engagement. Only used when Type = EMAIL.

FromFirstName String False  

The first name the email was from in the engagement. Only used when Type = EMAIL.

FromLastName String False  

The last name the email was from in the engagement. Only used when Type = EMAIL.

ToEmail String False  

A comma separated list of emails the message was sent to. Only used when Type = EMAIL.

Cc String False  

A comma separated list of cc'd email addresses. Only used when Type = EMAIL.

Bcc String False  

A comma separated list of bcc'd email addresses. Only used when Type = EMAIL.

Subject String False  

The subject of the email. Only used when Type = EMAIL.

EmailHtml String False  

Html content consisting of the body of the email. Only used when Type = EMAIL.

EmailText String False  

Plain text content consisting of the body of the email. Only used when Type = EMAIL.

CampaignGuid String False  

The campaign guid of an engagement. Only used when Type = PUBLISHING_TASK.

Category String False  

The category of the engagement. Used when Type = PUBLISHING_TASK.

CategoryId Integer False  

The category id of the engagement. Used when Type = PUBLISHING_TASK.

ContentId String False  

The content id of the engagement. Used when Type = PUBLISHING_TASK.

State String False  

The state of the engagement. Used when Type = PUBLISHING_TASK.

Name String False  

The name of the engagement. Used when Type = PUBLISHING_TASK.

ToNumber String False  

The phone number that was called. Used when Type = CALL.

FromNumber String False  

The phone number that was used as the from number. Used when Type = CALL.

ExternalId String False  

For calls made in HubSpot, this will be the internal ID of the call. Used when Type = CALL.

DurationMilliseconds Integer False  

The duration of the call in milliseconds. Used when Type = CALL.

ExternalAccountId String False  

For calls made in HubSpot, this will be the internal ID of the account used to make the call. Used when Type = CALL.

RecordingUrl String False  

The URL of the recording file . Used when Type = CALL.

Disposition String False  

Internal GUID that corresponds to the Call Outcome. Used when Type = CALL.

ExtraUrlParameters String True  

An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

Forms

Create and manage HubSpot Forms.

 

Table Specific Information

 

Forms represent the forms on your website that a user or contact can fill out, such as a survey.

Select

 

When selecting forms, they can only be filtered by GUID. For example:

SELECT * FROM Forms WHERE GUID='123456789'
Columns

 

 

 

   
Name Type ReadOnly References Description
GUID [KEY] String True  

The unique key for the form.

Name String False  

The name of the form.

Action String False  

An action to execute when the form is submitted.

CSSClass String False  

The CSS class associated with the form.

IsDeletable Boolean False  

A boolean indicating if the form is deletable.

EmbeddedCode String False  

Embedded javascript code included with the form.

FollowUpId String False  

An Id to follow up with if available.

IgnoreCurrentValues Boolean False  

A boolean indicating if current or default values should be ignored when submitting the form.

Method String False  

The HTTP method to use when submitting the form.

MigratedFrom String True  

Information about where the form was migrated from if available.

NotifyRecipients String False  

Email address of recipients that should notified when the form is submitted.

PerformableHTML String False  

HTML that should be performed on the form.

Redirect String False  

A url to redirect the user to once the form has been submitted.

SubmitText String False  

The submit button text.

CreatedAt Datetime True  

When the form was created.

UpdatedAt Datetime True  

When the form was last updated.

FieldsAggregate String False  

A collection of the fields available in the form.

FormFieldGroupsAggregate String False  

A collection of the groups of fields available in the form.

MetaDataAggregate String True  

A collection of metadata about the form.

ExtraUrlParameters String True  

An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

Products

Query the available products in HubSpot.

 

Table Specific Information

 

Products are a foundational object in HubSpot CRM. Products represent goods or services sold by your company.

Select

 

When selecting products, they can be filtered by ID or by ID and IncludeDeleted. For example:

SELECT * FROM Products WHERE Id='123456789'

SELECT * FROM Products WHERE Id='123456789' AND IncludeDeleted='true'
Insert

 

When creating products, you can specify Name, Description and Price. For example:

INSERT INTO Products (Name, Description, Price) VALUES ('NameExample', 'This is a description', 1324)
Update

 

When updating products, you can change Name, Description and Price by specifying the product Id. For example:

UPDATE Products Set Name='UpdatedName', Description='Updated desciption', Price=123 WHERE id = '123' 
Delete

 

You can delete products singularly by Id, or in batches. For example:

DELETE FROM Products WHERE Id = 123

DELETE FROM Products WHERE Id IN ( '123', '234')
Columns

 

 

 

   
Name Type ReadOnly References Description
Id [KEY] Integer True  

The internal ID for this product.

Name String False  

The name of the product.

Price String False  

The price of the product.

Description String False  

The description of the product.

Version Integer True  

The current version of the product. This is incremented each time the product is updated.

IsDeleted Boolean False  

Boolean indicating whether or not the product is deleted. Deleted records will not be included unless you specifically request that deleted records be included.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
IncludeDeleted String

By default, deleted records will not be returned by the API. When selecting by Id, you can include this parameter to make sure that records are returned even when they are deleted

rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

Settings

Select, insert, update, and delete HubSpot settings.

 

Table Specific Information

 

Settings represent your available HubSpot settings that change the behavior of your HubSpot Hub.

Select

 

When selecting settings, data cannot be filtered.

Columns

 

 

 

   
Name Type ReadOnly References Description
Name [KEY] String True  

The name of the setting.

Value String False  

The value for the setting.

CreatedAt Datetime True  

When the setting was created.

IsInternal Boolean True  

A boolean indicating if the setting is internal.

ExtraUrlParameters String True  

An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

SocialMediaMessages

Create and manage HubSpot social media messages.

 

Table Specific Information

 

Social media messages can be submitted to your social media channels to make announcements about your products or company.

Select

 

When selecting social media messages, data can be filtered by the MessageGUID or by a combination of Status, ChannelGUID, and TriggerAt. For example:

SELECT * FROM SocialMediaMessages WHERE MessageGUID='123456789'

SELECT * FROM SocialMediaMessages WHERE Status='WAITING' AND ChannelGUID='123456789' AND TriggerAt > '1/31/2015'

 

Note that TriggerAt can only be used with the > or >= comparison.

Columns

 

 

 

   
Name Type ReadOnly References Description
MessageGUID [KEY] String True  

The Id of the social media message.

ContentBody String False  

The body of the content for the message.

ContentOriginalBody String False  

The original body of the content for the message if it has been altered.

ContentPhotoUrl String False  

The photo associatedwith the message.

ContentUncompressedLinks String False  

Any uncompressed links associated with the message.

TriggerAt Datetime False  

When to trigger the social media message.

Channel String True  

The social media channel the message will be posted to.

ChannelGUID String False  

The social media channel GUID.

CampaignGUID String True  

The campaign GUID associated with the social media message.

CampaignName String True  

The campaign name associated with the social media message.

Clicks Integer True  

The number of clicks HubSpot has recorded for the links in the social media message.

ClientTag String True  

Client tags associated with the social media message.

CreatedAt Datetime True  

When the social media message was created.

CreatedBy Integer True  

An integer indicating which user created the social mdia message.

FinishedAt Datetime True  

When the social media message was finished posting.

ForeignId String True  

An optional ForeignId associated with the social media message.

GroupGUID String True  

The group GUID associated with the social media message.

InteractionsCount Integer True  

The number of interactions associated with the social media message as recorded by HubSpot.

IsFailed Boolean True  

A boolean indicating if the message has failed to post.

IsPending Boolean True  

A boolean indicating if the message is pending a post.

IsPublished Boolean True  

A boolean indicating if the message has been posted.

IsRetry Boolean True  

A boolean indicating if the message is being retried.

Likes Integer True  

The number of likes the social media message has.

LinkGUID String True  

A GUID for the link associated with the message.

LinkTaskQueueId String True  

The link task queue id associated with the included link.

Message String True  

The message that came back from the social media site if any. This may contain an error message if the social media message failed if the status is ERROR_FATAL.

MessageUrl String True  

A url associated with the message that came back from the social media site. This may simply be a link to the social media posting if the status is SUCCESS.

RemoteContentId String True  

A remote content id if any.

RemoteContentType String True  

The remote content type if any.

Replies Integer True  

The number of replies to the social media message.

Retweets Integer True  

The number of retweets of the social media message.

Status String True  

The current status of the social media message.

The allowed values are SUCCESS, WAITING, CANCELED, ERROR_FATAL.

TaskQueueId String True  

The task queue id associated with submitting the social media message.

UpdatedBy Integer True  

An integer indicating who last updated the social media message.

ExtraUrlParameters String True  

An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 

 

Workflows

Select and manage HubSpot workflows.

 

Table Specific Information

 

Workflows are automated tasks in HubSpot. You can perform automated tasks with contacts by enrolling contacts in workflows.

Select

 

When selecting workflows, data may be filtered by the WorkflowId. For example:

SELECT * FROM Workflows WHERE WorkflowId='123456789'
Columns

 

 

 

   
Name Type ReadOnly References Description
Id [KEY] Long True  

The Id of the workflow.

Name String False  

The name of the workflow.

Description String False  

A description of the workflow.

IsEnabled Boolean False  

A boolean indicating if this workflow is enabled.

CreatedAt Datetime True  

When the workflow was created.

UpdatedAt Datetime True  

When the workflow was last updated.

AllowContactToTriggerMultipleTimes Boolean False  

A boolean indicating if the workflow can be triggered for the same contact multiple times.

CanEnrollFromSalesforce Boolean False  

A boolean indicating if contacts can be enrolled to this workflow from Salesforce.

IsInternal Boolean False  

A boolean indicating if the workflow is internal.

LegacyCampaignId Long False  

The campaign id associated with the workflow if available.

LegacyMigration Boolean False  

A boolean indicating if the workflow was migrated from the legacy lead nurturing tool.

IsListening Boolean False  

A boolean indicating if the workflow is listening or active.

ContactListIdsCompleted Integer True  

The number of contacts that have been completed.

ContactListIdsEnrolled Integer True  

The number of contacts that have been enrolled.

ContactListIdsFailed Integer True  

The number of contacts that have failed in the workflow.

ContactListIdsSucceeded Integer True  

The number of contacts that have successfully completed the workflow.

NurtureTimeRangeEnabled Boolean False  

A boolean indicating if this workflow is enabled for a nurture time range.

NurtureTimeRangeStartHour Integer False  

The hour of day nurturing begins for this workflow.

NurtureTimeRangeStopHour Integer False  

The hour of day nurturing ends for this workflow.

OnlyExecOnBizDays Boolean False  

A boolean indicating if this workflow should only execute on standard business days.

StepsAggregate String False  

An aggregate of the steps to take when executing this workflow.

SupresssionSettingsAggregate String False  

An aggregate of supression properties for this workflow.

TriggerSetsAggregate String False  

An aggregate of trigger sets for this workflow.

TriggersAggregate String False  

An aggregate of triggers for this workflow.

UnenrollmentSettingType String False  

The type of unenrollment setting for this workflow.

UnenrollmentSettingExcludedWorkflowsAggregate String False  

An workflows to exclude a contact from if they enroll in this workflow.

GoalListAggregate String False  

An aggregate of goal ids for this workflow.

ExtraUrlParameters String True  

An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String

This is used to page through multiple pages of results and should not be set manually.

 

 




 

Views

  1. BlogAuthors
  2. BlogPosts
  3. Blogs
  4. BlogTopics
  5. Comments
  6. ContactFormSubmissions
  7. ContactIdentityProfiles
  8. ContactListMemberships
  9. ContactPropertiesHistory
  10. DealPipelines
  11. DealPipelineStages
  12. DealStages
  13. Domains
  14. EmailCampaignEvents
  15. EmailSubscriptionTypes
  16. EngagementScheduledTasks
  17. EngagementsScheduledTasks
  18. Files
  19. Folders
  20. Owners
  21. Pages
  22. ProspectActivity
  23. Prospects
  24. SocialMediaChannels
  25. Tasks
  26. Templates
  27. UrlMappings

BlogAuthors

Retrieve the available blog authors in HubSpot.

 

Table Specific Information

 

Blog authors represent the users in your HubSpot Hub that can make posts on your HubSpot blog.

Select

 

When selecting blog authors, they can only be filtered by the Id, FullName, CreatedAt, or Slug. CreatedAt can be used to specify a range while the others must be used with an exact comparison. For example:

SELECT * FROM BlogAuthors WHERE CreatedAt >= '1/1/2014' AND CreatedAt <= '10/31/2014'

SELECT * FROM BlogAuthors WHERE Slug = 'abc123'
Columns

 

 

 

   
Name Type References Description
Id [KEY] Long   The id of the author.
FullName String   The first and last name of the blog author.
CreatedAt Datetime   When the blog author was first created.
Slug String   The path appended to the blog URL at which the author page will live.
DeletedAt Datetime   When the author was deleted.
Email String   The email address of the blog author.
FacebookProfile String   The blog author's Facebook page.
GooglePlusProfile String   The blog author's Google Plus profile.
LinkedInProfile String   The blog author's LinkedIn profile
TwitterProfile String   The blog author's twitter handle.
AvatarUrl String   A small photo or image of the blog author.
UserId Integer   The HubSpot user id of the blog author.
Username String   The HubSpot username of the blog author.
Website String   The blog author's web site.
UpdatedAt Datetime   When the blog author was last updated.
ExtraUrlParameters String   An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

BlogPosts

Retrieve the available blog posts in HubSpot.

 

Table Specific Information

 

Blog posts represent the individual posts in your HubSpot blog.

Select

 

When selecting blog posts, they can only be filtered by the Id, BlogAuthorId, IsArchived, CampaignId, ContentGroupId, CreatedAt, DeletedAt, Name, Slug, and UpdatedAt columns. CreatedAt, DeletedAt, and UpdatedAt can be used with > and < where a range can be formed with CreatedAt and UpdatedAt. In addition, Name can be used with the LIKE comparison. All others must be used with an exact comparison. For example:

SELECT * FROM BlogPosts WHERE CreatedAt >= '1/1/2014' AND CreatedAt <= '10/31/2014'

SELECT * FROM BlogPosts WHERE UpdatedAt >= '1/1/2014' AND UpdatedAt <= '10/31/2014'

SELECT * FROM BlogPosts WHERE DeletedAt >= '1/1/2014'

SELECT * FROM BlogPosts WHERE Name LIKE 'My Post'
Columns

 

 

 

   
Name Type References Description
Id [KEY] Long   The id of the blog post.
AuthorUserId Long   The HubSpot user id of the blog author.
BlogAuthorId Long

BlogAuthors.Id

The integer id of the blog author, look up via the blog authors end.
CampaignId String

EmailCampaigns.Id

The guid of the marketing campaign this post is associated with.
ContentGroupId Long   The id of the blog that this post belongs to. Get the id by looking at the blog API.
TopicIds String   The individual topics the post is associated with.
CreatedAt Datetime   When the post was first created.
DeletedAt Datetime   When the post was deleted.
IsArchived Boolean   If True, the post will not show up in your dashboard, although the post will still be live.
Name String   The internal name of the blog post.
Slug String   The path of the URL on which the post will live.
UpdatedAt Datetime   When the post was last updated.
PostSummary String   The summary of the blog post that will appear on the main listing page.
PostBody String   The HTML of the main post body.
Url String   The full URL with domain and scheme to the blog post. Will return a 404 if the post is not yet published.
Views Integer   The number of views this post has.
ExtraUrlParameters String   An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

Blogs

Retrieve the available blogs in HubSpot.

 

Table Specific Information
Select

 

When selecting blogs, they can only be filtered by the Id, Name, and CreatedAt. CreatedAt can be used to specify a range. For example:

SELECT * FROM Blogs WHERE CreatedAt >= '1/1/2014' AND CreatedAt <= '10/31/2014'

SELECT * FROM Blogs WHERE Name = 'myblog'
Columns

 

 

 

   
Name Type References Description
Id [KEY] Long   The id of the blog.
Name String   The internal name of the blog.
AllowComments Boolean   Are comments enabled for the blog.
CommentShouldCreateContact Boolean   Boolean indicating if an email address that is not listed in your HubSpot contacts creates a comment, should this user be automatically added to your HubSpot contacts.
CreatedAt Datetime   When the post was first created.
HTMLTitle String   The title in the title attribute of the page, shows up in the browsers title bar and as the title in Google search results.
Language String   The language of the blog.
PostsPerListingPage Integer   The number of posts listed per page in the HTML viewable blog.
PostsPerRSSFeed Integer   The number of posts listed per page in the RSS feed for the blog.
PublicTitle String   The header of the blog.
RootURL String   The full URL with domain and scheme to the blog post.
ShowSocialLinkFacebook Boolean   Boolean indicating if a social link for Facebook should be displayed on the blog.
ShowSocialLinkGoogle Boolean   Boolean indicating if a social link for Google Plus should be displayed on the blog.
ShowSocialLinkLinkedIn Boolean   Boolean indicating if a social link for LinkedIn should be displayed on the blog.
ShowSocialLinkTwitter Boolean   Boolean indicating if a social link for Twitter should be displayed on the blog.
Slug String   The path of the URL on which the post will live.
UpdatedAt Datetime   When the post was last updated.
ExtraUrlParameters String   An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

BlogTopics

Retrieve the available blog topics in HubSpot.

 

Table Specific Information

 

Blog topics represent any number of topics a blog post can be posted under.

Select

 

When selecting blogs, they can only be filtered by the Id, Name, Slug, and CreatedAt. CreatedAt can be used to specify a range. For example:

SELECT * FROM BlogTopics WHERE CreatedAt >= '1/1/2014' AND CreatedAt <= '10/31/2014'

SELECT * FROM BlogTopics WHERE Name = 'mytopic'
Columns

 

 

 

   
Name Type References Description
Id [KEY] Long   The blog topic id.
CreatedAt Datetime   When the topic was created
DeletedAt Datetime   When the topic was deleted.
Description String   A description of the topic.
Name String   The name of the topic.
Slug String   How the topic will appear in the url.
UpdatedAt Datetime   When the topic was last updated.
ExtraUrlParameters String   An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

Comments

Retrieve the available comments from your blog in HubSpot.

 

Table Specific Information

 

Comments represent any comments that can be made on a given blog post.

Select

 

When selecting comments, they can only be filtered by the Id, State, PostId, and Comment. Comment can be used with the LIKE comparison. For example:

SELECT * FROM Comments WHERE Comment LIKE 'comment text'

SELECT * FROM Comments WHERE Id = '123456789'
Columns

 

 

 

   
Name Type References Description
Id [KEY] Long   The id of the comment.
State String   The current state of the comment.

The allowed values are APPROVED, SPAM, REJECTED, PENDING_MODERATION.

PostId Long

BlogPosts.Id

The id of the parent blog post.
Comment String   The full text of the comment.
CommentAuthorEmail String   Email address of the user submitting the comment.
CommentAuthorName String   Name of the user submitting the comment.
ContentPermalink String   A permanent link for the parent post of the comment.
ContentTitle String   The title of the parent post for the comment.
CreatedAt Datetime   When the comment was made.
DeletedAt Datetime   When the comment was deleted.
FirstName String   The first name of the user who made the comment.
LastName String   The last name of the user who made the comment.
UserEmail String   Email address of the user submitting the comment.
UserUrl String   A url to the user's website if available.
ExtraUrlParameters String   An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

ContactFormSubmissions

List of Contact's Form Submissions.

 

Table Specific Information

 

A list of form submissions for the contact. This list will be empty for records with no form submissions.

Select

 

Contact form submissions can only be filtered by the unique contact id. For example:

SELECT * FROM ContactFormSubmissions  

SELECT * FROM ContactFormSubmissions WHERE ContactVID = '123456'
Columns

 

 

 

   
Name Type References Description
ContactVID Integer

Contacts.VID

The unique id of the task.
FormId String

Forms.GUID

The GUID of the form that the subission belongs to.
ConversionId String   A Unique ID for the specific form conversion.
PortalId Integer   The Portal ID (Hub ID) that the submission belongs to
Timestamp Datetime   The time the submission occurred.
Title String   The title of the page that the form was submitted on.
PageId String

Pages.Id

Id of the page that the form was submitted on.
PageTitle String   Title of the page that the form was submitted on.
PageURL String   The URL that the form was submitted on.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

ContactIdentityProfiles

List of Contact's Identity profiles

 

Table Specific Information

 

A list of objects representing the identities of the contact. Each identity represents an identifier for the object, many records will only have a single identity, but merged records may have multiple.

Select

 

Contact identity profiles can only be filtered by the unique contact id. For example:

SELECT * FROM ContactIdentityProfiles  

SELECT * FROM ContactIdentityProfiles WHERE ContactVID = '123456'
Columns

 

 

 

   
Name Type References Description
ContactVID Integer

Contacts.VID

The unique id for the contact.
Type String   The type of the identity, one of EMAIL or LEAD_GUID.
Value String   The value of the identity.
Timestamp Datetime   Time when the identity was created.
SavedAt Datetime   Time when the identity was last updated.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

ContactListMemberships

Returns the list memberships of contacts in HubSpot.

 

Columns

 

 

 

   
Name Type References Description
VID [KEY] Long

Contacts.VID

The id of the contact on the list.
ListId [KEY] Long

ContactLists.ListId

The static id of the list.
Email String   The static id of the list.
TimeAddedToList Datetime   The datetime when the contact was added to the list.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

ContactPropertiesHistory

A list of the historical values of the property.

 

Columns

 

 

 

   
Name Type References Description
VID Integer

Contacts.VID

The unique id for the contact.
PropertyName String   The name of the contact property.
Value String   The historical value of the property.
Timestamp Datetime   Datetime when the property was updated
SourceType String   The method by which the property was changed
SourceId String   Additional data related to the source-type. May not be populated for all source-types.
SourceLabel String   Additional data related to the source-type. May not be populated for all source-types.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

DealPipelines

Deal pipelines may be used to organize what stages a given deal is on.

 

Table Specific Information

 

Deal pipelines represent different types of pipelines deals and be placed into to track their progress.

Select

 

When selecting deal pipelines, they can only be filtered by the PipelineId. For example:

SELECT * FROM DealPipelines WHERE PipelineId = 12345
Columns

 

 

 

   
Name Type References Description
PipelineId [KEY] String   The id of the pipeline.
PipelineName String   The name of the pipeline.
IsActive Boolean   A boolean indicating if the pipeline is active or not.
StagesAggregate String   The stages in the pipeline.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

DealPipelineStages

The stages for a given Deal Pipeline.

 

Table Specific Information

 

Deal pipeline stages represent the individual stages of a given pipeline that a Deal may currently be on.

Select

 

When selecting deal pipeline stages, they can only be filtered by the PipelineId. For example:

SELECT * FROM DealPipelineStages WHERE PipelineId = 12345
Columns

 

 

 

   
Name Type References Description
PipelineId [KEY] String

DealPipelines.PipelineId

The id of the pipeline.
StageId [KEY] String   The id of the stage.
PipelineName String   The name of the pipeline.
PipelineIsActive Boolean   A boolean indicating if the pipeline is active or not.
StageName String   The name of the stage.
StageIsActive Boolean   A boolean indicating if the stage is active or not.
StageClosedWon Boolean   A boolean indicating if the stage indicates that the deal was closed and won.
StageDisplayOrder Integer   The display order of the stage in the deal.
StageProbability Double   The estimated probability of closing the deal at this stage.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

DealStages

The stages for a given Deal.

 

Columns

 

 

 

   
Name Type References Description
DealId [KEY] String

Deals.DealId

The id of the deal.
StageCreated [KEY] Datetime   When the deal stage was created.
StageName String   The name of the deal stage.
StageValue String   The value of the deal stage.
StageSource String   The source of the deal stage.
StageSourceId String   The source id of the deal stage.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

Domains

Retrieve the available domains in HubSpot.

 

Table Specific Information

 

Domains represent any domains you have registered with HubSpot to host your HubSpot blogs and content optimization system on.

Select

 

When selecting domains, they can only be filtered by the Id, CreatedAt, Domain, IsResolving, and PrimarySitePage columns. CreatedAt can be used with the > and < comparisons to form a range. For example:

SELECT * FROM Domains WHERE CreatedAt >= '1/1/2014' AND CreatedAt <= '10/31/2014'

SELECT * FROM Domains WHERE Id = '123456789'
Columns

 

 

 

   
Name Type References Description
Id [KEY] Long   The id of the domain.
CreatedAt Datetime   When the domain was first created.
Domain String   The actual domain or subdomain.
IsAnyPrimary Boolean   True if this domain is primary for any category.
IsDNSCorrect Boolean   True if DNS for this domain is optimally configured for use with HubSpot.
IsLegacyDomain Boolean   True is this domain is setup for use with the classic CMS.
IsResolving Boolean   True if this domain is pointing to HubSpot servers.
ManuallyMarkedAsResolving Boolean   True if a user manually marked this domain as resolving. This is needed when their is some unique setup or proxy server involved, and the COS can not automatically detect if the domain is properly resolving.
PrimaryBlogPost Boolean   True if this domain is primary for COS blog posts.
PrimaryEmail Boolean   True if this domain is primary for viewing emails as web page.
PrimaryLandingPage Boolean   True if this domain is primary for COS landing pages.
PrimaryLegacyPage Boolean   True if this domain is primary for the classic CMS.
PrimarySitePage Boolean   True if this domain is primary for COS site pages.
SecondaryToDomain String   The name of the domain that this domain redirects to. Only set for non-primary domains.
UpdatedAt Datetime   When the domain was last updated.
ExtraUrlParameters String   An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

EmailCampaignEvents

The events associated with an email campaign or a recipient.

 

Table Specific Information

 

Email campaign events represent individual events that occurred during an email campaign. These are generally events such as a contact clicking on a link that was included in the email.

Select

 

Email campaign events may be filtered by RecipientEmail, AppId, CampaignId, Type, and CreatedAt. Email campaign events can also be retrieved one at a time by specifying both the Id and CreatedAt, but the CreatedAt datetime will need to be correct to the millisecond. For example:

SELECT * FROM EmailCampaignEvents

SELECT * FROM EmailCampaignEvents WHERE CreatedAt='9/23/2014 5:28:00.280 PM' AND Id='123456789'

SELECT * FROM EmailCampaignEvents WHERE CampaignId='14229773' AND AppId = '113' AND Type='CLICK' AND CreatedAt > '9/23/2014 1:28:00 PM' AND CreatedAt < '9/23/2014 7:29:00 PM'
Columns

 

 

 

   
Name Type References Description
Id [KEY] String   The Id of the email campaign event.
RecipientEmail String   Email address of the recipient associated with the event.
CampaignId Long

EmailCampaigns.Id

Campaign id of the email campaign associated with the event.
AppId Long   An Id referencing the HubSpot Application which sent the email message.
AppName String   The name of the HubSpot Application which sent the email message. Note that this is a derived value, and may be modified at any time.
CreatedAt Datetime   When this event was created.
DeviceType String   The type of device used that triggered the event if avialable.
HMID String   A randomly-generated Id which corresponds to the header 'X-HubSpot-MID' in the email message.
IPAddress String   The IP address where the event originated.
Referer String   The URL of the webpage that linked to the URL clicked. Whether this is provided, and what its value is, is determined by the recipient's email client.
Type String   The type of event.

The allowed values are SENT, DROPPED, PROCESSED, DELIVERED, DEFERRED, BOUNC, OPEN, CLICK, PRINT, FORWARD, STATUSCHANGE, SPAMREPORT.

Url String   The URL within the message that the recipient clicked.
UserAgent String   The user agent responsible for the event.
BrowserFamily String   The family of the browser that serviced the event.
BrowserName String   The name of browser that serviced the event.
BrowserProducer String   The producer of browser that serviced the event.
BrowserProducerUrl String   A url to the producer of the browser if available.
BrowserType String   The type of browser that produced the event.
BrowserUrl String   A url to an entry describing the browser if available.
BrowserVersion String   The versionof browser used.
LocationCity String   The city where the event occurred.
LocationCountry String   The country where the event occurred.
LocationState String   The state where the event occurred.
SentByCreatedAt Datetime   When the email was sent.
SentById String   The Id which uniquely identifies the email message's SENT event.
ExtraUrlParameters String   An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

EmailSubscriptionTypes

A list of email subscription types for a HubSpot hub.

 

Table Specific Information

 

Email subscription types in HubSpot represent different types of subscriptions a contact could be subscribed to.

Select

 

When selecting email subscriptions types, there are no filters that may be used. It is simply a list of all of the available subscription types. For example:

SELECT * FROM EmailSubscriptionTypes
Columns

 

 

 

   
Name Type References Description
Id [KEY] Long   The Id of the email subscription type.
Name String   The name of the email subscription type.
IsActive Boolean   Whether or not the email subscription type is active.
Description String   A description for the email subscription type.
ExtraUrlParameters String   An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

EngagementScheduledTasks

List of tasks scheduled for an engagement.

 

Table Specific Information

 

EngagementScheduledTasks represent a list of scheduled tasks for the engagements you have in HubSpot.

Select

 

When selecting engagement scheduled tasks, they can only be filtered by the EngagementId. For instance:

SELECT * FROM EngagementScheduledTasks

SELECT * FROM EngagementScheduledTasks WHERE EngagementId = 12345
Columns

 

 

 

   
Name Type References Description
EngagementId Long   The id of the engagement.
UUID String   The scheduled task's uniqe Id.
PortalId Integer   The portal id the engagement is associated with.
EngagementType String   The type of engagement.
TaskType String   The type of the scheduled task.
Timestamp Datetime   Time when the task was scheduled.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

EngagementsScheduledTasks

List of tasks scheduled for an engagement.

 

Table Specific Information

 

EngagementsScheduledTasks represent a list of scheduled tasks for the engagements you have in HubSpot.

Select

 

When selecting engagements' scheduled tasks, they can only be filtered by the EngagementId. For instance:

SELECT * FROM EngagementsScheduledTasks

SELECT * FROM EngagementsScheduledTasks WHERE EngagementId = 12345
Columns

 

 

 

   
Name Type References Description
EngagementId Long   The id of the engagement.
UUID String   The scheduled task's uniqe Id.
PortalId Integer   The portal id the engagement is associated with.
EngagementType String   The type of engagement.
TaskType String   The type of the scheduled task.
Timestamp Datetime   Time when the task was scheduled.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

Files

Retrieves information about the available files in HubSpot.

 

Table Specific Information

 

Files represent any files you have uploaded with HubSpot to your content optimization system.

Select

 

When selecting files, they can only be filtered by the Id, Name, AltKey, IsArchived, CreatedAt, DeletedAt, Extension, FolderId, and Type. Name can be used with the LIKE comparison. Type can be used with both = and <>. CreatedAt and DeletedAt can be used with the > and < comparisons but cannot form a range. For example:

SELECT * FROM Files WHERE CreatedAt >= '1/1/2014'

SELECT * FROM Files WHERE DeletedAt <= '1/1/2014'

SELECT * FROM Files WHERE Type <> 'IMG'

SELECT * FROM Files WHERE Name LIKE 'filename'
Columns

 

 

 

   
Name Type References Description
Id [KEY] Long   The unique id of the file.
Name String   The internal name of the file.
FriendlyUrl String   A full url to the file that can be used from a web browser to view or download the file.
AltKey String   An alternative file key. This is used for creationg the 'alt_url', which is a url for the file that does not include the file id. This is useful if you need to upload a collection of files where their relative location needs to preserved.
AltUrl String   The alternative file URL, without the auto-generated file id in it. This is generated by concatenating the alt_key to the base CDN url.
IsArchived Boolean   If True, the file will not show up in your dashboard, although the file will still be live.
CreatedAt Datetime   When the file was first created.
DeletedAt Datetime   When the file was deleted.
Extension String   The extension of the file.
FolderId Long

Folders.Id

The id of the folder this file is in.
RSUploadedAt Datetime   When the file was uploaded via RS.
S3UploadedAt Datetime   When the file was uploaded via S3.
Size Integer   The size in bytes of the file.
IsSynced Boolean   A boolean indicating if this is a CTA image.
Title String   The title of the file.
Type String   A string enum for type of the file.

The allowed values are IMG, TEXT, DOCUMENT, OTHER.

UpdatedAt Datetime   When the file was last updated.
Version Double   The version of the file.
Height Integer   For images only, the height in pixels of the image.
Width Integer   For images only, the width in pixels of the image.
ExtraUrlParameters String   An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

Folders

Retrieves information about the available folders in HubSpot.

 

Table Specific Information

 

Folders represent any folders you can upload files to in your content optimization system within HubSpot.

Select

 

When selecting folders, they can only be filtered by the Id, DeletedAt, Name, and ParentFolderId. Name can be used with the LIKE comparison. DeletedAt can be used with the > and < comparisons but cannot form a range. For example:

SELECT * FROM Folders WHERE DeletedAt >= '1/1/2014' AND DeletedAt <= '10/1/2014'

SELECT * FROM Folders WHERE Name LIKE 'foldername'

SELECT * FROM Folders WHERE Id = '123456798'
Columns

 

 

 

   
Name Type References Description
Id [KEY] Long   The id of the folder.
Category Integer   The category of the folder.
CreatedAt Datetime   When the folder was created.
DeletedAt Datetime   When the folder was deleted.
FullPath String   The full path to the folder.
Name String   The name of the folder.
ParentFolderId Long

Folders.Id

The id of the parent folder for this folder if available.
UpdatedAt Datetime   When the folder was last updated.
ExtraUrlParameters String   An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

Owners

Retrieve the owners in HubSpot.

 

Columns

 

 

 

   
Name Type References Description
OwnerId [KEY] Integer   The id of the owner.
PortalId Long   The portal id the owner is associated with.
Type String   The type of user.
FirstName String   The first name of the owner.
LastName String   The last name of the owner.
Email String   The email address for the owner.
CreatedAt Datetime   The when the owner was created.
UpdatedAt Datetime   The when the owner was last updated.
RemoteListAggregate String   Remote list information for the owner.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

Pages

Retrieve the available pages in HubSpot.

 

Table Specific Information

 

Pages represent any pages you have published with the HubSpot content optimization system.

Select

 

When selecting pages, they can only be filtered by the Id, IsArchived, CampaignId, CreatedAt, DeletedAt, IsDraft, Name, PublishDate, Slug, Subcategory, and UpdatedAt. CreatedAt, UpdatedAt and PublishDate may be used with > and < to form a range. DeletedAt can be used with the > and < comparisons but cannot form a range. For example:

SELECT * FROM Pages WHERE CreatedAt >= '1/1/2014' AND CreatedAt <= '10/1/2014'

SELECT * FROM Pages WHERE UpdatedAt >= '1/1/2014' AND UpdatedAt <= '10/1/2014'

SELECT * FROM Pages WHERE PublishDate >= '1/1/2014' AND PublishDate <= '10/1/2014'

SELECT * FROM Pages WHERE DeletedAt >= '1/1/2014'
Columns

 

 

 

   
Name Type References Description
Id [KEY] Long   The id of the page.
IsArchived Boolean   If True, the page will not show up in your dashboard, although the page will still be live.
CampaignId String

EmailCampaigns.Id

The guid of the marketing campaign this page is associated with.
CampaignName String

EmailCampaigns.Name

The name of the marketing campaign this page is associated with.
CreatedAt Datetime   When the page was created.
CurrentLiveDomain String   The domain this page is currently located on.
DeletedAt Datetime   When the page was deleted.
FooterHTML String   Custom HTML for embed codes, javascript that should be placed before the body tag of the page
HeaderHTML String   Custom HTML for embed codes, javascript, etc. that goes in the head tag of the page
IsDraft Boolean   True if the post is still a draft, invisible to the public. Gets changed when the /publish-action API endpoint is called.
MetaDescription String   A description that goes in meta tag on the page
MetaKeywords String   Keywords for the meta tag.
Name String   The internal name of the page.
Password String   Set this to create a password protected page. Entering the password will be required to view the page.
PublishDate Datetime   The date the page is to be published at in milliseconds since the unix epoch.
Slug String   The path of the URL on which the page will live. Changing this will change the URL.
StyleOverrideId String   The ID of the style to use for this page, set this to use a different style than the default style for the site.
Subcategory String   This is set to empty or to
UpdatedAt Datetime   When the page was last updated.
Url String   The full URL with domain and scheme to the page. Will return a 404 if the page is not yet published.
ExtraUrlParameters String   An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

ProspectActivity

Select activity events for HubSpot prospects.

 

Columns

 

 

 

   
Name Type References Description
Slug [KEY] String   The unique slug for the prospect.
UUID [KEY] String   The universally unique id for the activity.
ChildId Long   The child id for the activity.
ContentType String   The type of content the prospect accessed to trigger the activity.
Country String   The originating country for the prospect activity.
Region String   The region the prospect is located in.
Latitude Double   The latitude associated with the prospect activity.
Longitude Double   The longitude associated with the prospect activity.
Organization String   The organization associated with the prospect.
OrganizationDomain String   The domain for the organization if available.
Referrer String   The referring url that triggered the activity if available.
ActivityTime Datetime   When the activity was recorded.
Title String   The title of the page that triggered the activity..
Type Integer   An integer identify the type of activity.
Uri String   Uri associated with the activity.
Url String   The url associated with the activity.
UserAgent String   The user agent in use by the prospect during the activity.
IdentitiesAggregate String   An aggregate of identities associated with the prospect that triggered the activity.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

Prospects

List and filter available HubSpot prospects.

 

Columns

 

 

 

   
Name Type References Description
Slug [KEY] String   The unique identifying slug for the prospect.
City String   The city the prospect is in as recorded by HubSpot.
Country String   The country the prospect is in as recorded by HubSpot.
Region String   The region the prospect is in as recorded by HubSpot.
IpAddress String   IP address associated with the prospect.
Latitude Double   Latitude associated with the prospect.
Longitude Double   Longitude associated with the prospect.
Organization String   The originating organization associated with the IP address.
PageViews Integer   The number of page views recorded from the prospect.
LastVisit Datetime   The last time a visit from the prospect was recorded.
Url String   A url associated with the prospect.
Visitors Integer   The number of unique visitors from the prospect.
ReferalAggregate String   An aggregate of referals that lead the visitor to your site.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

SocialMediaChannels

List available social media channels in HubSpot

 

Table Specific Information

 

Social media channels in HubSpot represent any social media accounts you have connected for the purposes of posting marketing messages to. Messages can be posted to your social media channels via SocialMediaMessages.

Select

 

When selecting social media channels, data can only be filtered by the ChannelGUID. For example:

SELECT * FROM SocialMediaChannels WHERE ChannelGUID='123456789'
Columns

 

 

 

   
Name Type References Description
ChannelGUID [KEY] String   The social media channel GUID.
AccountGUID String   The account GUID associated with the social media channel.
AccountSlug String   The account slug.
AccountType String   The type of account.
IsActive Boolean   A boolean indicating if the channel is active.
AutoPublish Boolean   A boolean indicating if messages should be automatically published to the channel.
AvatarUrl String   A url to the avatar for your account on the social medial channel.
ChannelId String   The id for the channel on the social media site.
ChannelKey String   A HubSpot key associated with the channel.
ChannelSlug String   The slug associated with the channel.
CreatedAt Datetime   When the channel was created.
DisplayName String   The display for the social media channel.
FollowMe Boolean   A boolean indicating if the channel should be followed.
IsHidden Boolean   A boolean indicating if the channel is hidden.
Monitoring Boolean   A boolean indicating if the channel should be monitored.
Name String   The name of the channel.
ProfileUrl String   The url to the profile on the social media site.
Reach Boolean   A boolean indicating if tracking the growth of followers on this account over time should be enabled.
ReachType String   The type of reach for this social media channel.
IsShared Boolean   A boolean indicating if this social media channel is shared.
Type String   The type of social media channel.
UpdatedAt Datetime   When this social media channel was last updated.
UserName String   The user name for the social media channel.
DataMapAggregate String   An aggregate of data mapped for this social media channel if available.
ExtraUrlParameters String   An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

Tasks

Retrieve task events for Calendar.

 

Table Specific Information

 

Retrieve task events for Calendar. A shortcut of the standard events call for finer-grained control.

Select

 

You can filter through results with StartDate, EndDate, EmailCampaignId, IncludeNoCampaigns. In case StartDate is not specified its default value will be 01/01/2010. In case EndDate is not specified its default value will be the actual date. For example:

The following query will return results starting from 01/01/2010 till actual date.

SELECT * FROM Tasks

 

The following query will return results starting from 01/01/2015 till actual date.

SELECT * FROM Tasks Where StartDate = '2015-01-01 12:00:00'

 

The following query will return results in the range of the specified dates satisfying other conditions as well:

SELECT * FROM Tasks Where StartDate = '2014-01-01 12:00:00' And EndDate = '2019-01-01 12:00:00' And IncludeNoCampaigns = 'true'

SELECT * FROM Tasks Where EmailCampaignId IN ('12345678', '2345689') and StartDate = '2014-01-01 12:00:00Z' And EndDate = '2019-01-01 12:00:00'
Columns

 

 

 

   
Name Type References Description
Id [KEY] String   The unique id of the task.
Name String   Name of Task.
Description String   Description of Task.
EventType String   Type of calendar event; for tasks this is always PUBLISHING_TASK.
EventDate Datetime   When the task is set to be due.
Category String   Type of task; one of BLOG_POST, EMAIL, LANDING_PAGE, CUSTOM.
CategoryId Integer   Numeric value corresponding to the type of task; one of 3 (BLOG_POST), 2 (EMAIL), 1 (LANDING_PAGE), 0 (CUSTOM).
ContentId Long   Id value of the COS content object associated with the task, null if nothing associated.
ContentGroupId Long   The ID of the content group (aka blog) that the associated Blog Post belongs to, if any. Otherwise null. Only populated for single task GETs and for Blog Post Tasks..
PortalId Integer   The hub id.
State String   Value of TODO or DONE.
EmailCampaignId String

EmailCampaigns.Id

Value of campaign GUID associated with Task.
Url String   URL of the task.
OwnerId Long

Owners.OwnerId

HubSpot id of the user that the task is assigned to.
CreatedBy Long

Owners.OwnerId

HubSpot id of the user that the task was created by.
PreviewKey String   The preview key.
SocialUsername String   The username.
SocialDisplayName String   The display name.
AvatarUrl String   User avatar url.
TopicIds String

BlogPosts.Id

The list of ids of topics associated with the associated Blog Post, if any. Otherwise null. Only populated for single task GETs and for Blog Post Tasks..
IsRecurring Boolean   Boolean indicating whether is the task recurring.

 

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. For more information, see the WHERE clause section.

 

 

   
Name Type Description
IncludeNoCampaigns Boolean   The beginning value of the query range.
StartDate Datetime   The beginning value of the query range.
EndDate Datetime   Include tasks without a campaign specified (true, false). Defaults to false.
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

Templates

Retrieve the available templates in HubSpot.

 

Table Specific Information

 

Templates represent any templates you have saved in the HubSpot content optimization system. The templates allow you to easily create new pages with the same look of other pages on your site.

Select

 

When selecting templates, they can only be filtered by the Id, CategoryId, DeletedAt, Folder, IsAvailableForNewContent, Label, and Path. DeletedAt can be used with the > and < comparisons but cannot form a range. For example:

SELECT * FROM Templates WHERE DeletedAt >= '1/1/2014'

SELECT * FROM Templates WHERE Id = '123456789'
Columns

 

 

 

   
Name Type References Description
Id [KEY] Long   The id of the template.
CategoryId Long   The category of content this template can be used for. 1 for landing page, 2 for email, 3 for site page.
CDNMinifiedUrl String   For javascript and css, this is the URL of the version of the content that has been rendered, minified, and uploaded to our Content Delivery Network.
CDNUrl String   For non-html templates, the URL to the version of the template that has been rendered and uploaded to the HubSpot CDN.
DeletedAt Datetime   When the template was deleted.
Folder String   The folder this template lives in.
GeneratedFromLayoutId String   The id of the layout that generated this template.
IsAvailableForNewContent Boolean   True if this template will show up in the content creation screen.
IsFromLayout Boolean   True if template was generated by publishing a layout.
IsReadOnly Boolean   True if the template can only be read.
Label String   The label of the template as it shows up in the template builder.
Path String   The path of the template, as should be used for HubL include statements.
Source String   The markup of the template.
ThumbnailPath String   The thumbnail image of the template.
UpdatedAt Datetime   When the template was last updated.
ExtraUrlParameters String   An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.

 

 

 

UrlMappings

Retrieve the available url mappings in HubSpot.

 

Table Specific Information

URL mappings represent any number of redirects you have specified in your HubSpot content optimization system.
 

Select

When selecting URL mappings, they can only be filtered by the Id, CreatedAt, DeletedAt, Destination, IsOnlyAfterNotFound, RoutePrefix, and UpdatedAt. DeletedAt, CreatedAt, and UpdatedAt can be used with the > and < comparisons to form a range. For example:

SELECT * FROM UrlMappings WHERE DeletedAt >= '1/1/2014' AND DeletedAt <= '10/1/2014'

SELECT * FROM UrlMappings WHERE Id = '123456789'

Columns
   
Name Type References Description
Id [KEY] Long   The id of the url mapping.
CreatedAt Datetime   When the url mapping was created.
DeletedAt Datetime   When the url mapping was deleted.
Destination String   The URL to redirect to.
IsMatchFullUrl Boolean   If true, the 'route_prefix' should match on the entire URL including the domain.
IsMatchQueryString Boolean   If true, the 'route_prefix' should match on the entire URL path including the query string.
IsOnlyAfterNotFound Boolean   If True, the URL mapping will only be applied if a live page matching the URL is not found. If False, the URL mapping will take precedence over any existing page.
Precedence Integer   If a URL matches more than one mapping, the one with the lower precedence applies.
RoutePrefix String   The incoming URL to match.
UpdatedAt Datetime   When the url mapping was last updated.
ExtraUrlParameters String   An input only property for specifying additional parameters when selecting data from HubSpot. Specify the parameters as name=value pairs in a comma separated list. For instance, 'param1=value1,param2=value2,param3=value3'.

 

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. For more information, see the WHERE clause section.

 

   
Name Type Description
rows@next String   This is used to page through multiple pages of results and should not be set manually.