`

Matillion ETL Data Model for Hubspot
Version - 18.0.6886

Note: Data models are true for the latest version of Matillion ETL. If you are on an older version or using a component from an old job, your experience may differ.




Connection String OptionsBack To Top

  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. Max Rows
  26. OAuth Access Token
  27. OAuth Client Id
  28. OAuth Client Secret
  29. OAuth Refresh Token
  30. OAuth Settings Location
  31. OAuth Verifier
  32. Offline
  33. Other
  34. Pool Idle Timeout
  35. Pool Max Size
  36. Pool Min Size
  37. Pool Wait Time
  38. Proxy Auth Scheme
  39. Proxy Auto Detect
  40. Proxy Exceptions
  41. Proxy Password
  42. Proxy Port
  43. Proxy Server
  44. Proxy SSL Type
  45. Proxy User
  46. Pseudo Columns
  47. Readonly
  48. RTK
  49. SSL Server Cert
  50. Support Enhanced SQL
  51. Tables
  52. Timeout
  53. Use Connection Pooling
  54. Use Display Names
  55. Use Simple Names
  56. Verbosity
  57. Views



Auto Cache

Automatically caches the results of SELECT queries into a cache database specified by either CacheLocation or both of CacheConnection and CacheProvider . Configured with CacheQueryResult .
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





Batch Size

The maximum size of each batch operation to submit.
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

The connection string for the cache database. Always used in conjunction with CacheProvider . Setting both properties will override the value set for CacheLocation for caching data.
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

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:

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:
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:
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:





Cache Driver

The database driver to be used to cache data.
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

Specifies the path to the cache when caching to a file.
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





Cache Metadata

Whether or not to cache the table metadata to a file store.
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





Cache Query Result

With AutoCache set, caches each row read from a SELECT query's results. Without this, the provider will attempt to fully replicate the table before executing the actual query against the replication database.
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

The tolerance for stale data in the cache specified in seconds when using AutoCache .
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

The OAuth callback URL to return to when authenticating. This value must match the callback URL you specify in your app settings.
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

A file with a name=value comma separated list of column sizes. For use with custom fields.
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

A file with a comma separated list of properties to select from HubSpot for the Companies table.
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

The maximum lifetime of a connection in seconds. Once the time has elapsed, the connection object is disposed.
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

Species whether to connect to the HubSpot when the connection is opened.
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

A file with a comma separated list of properties to select from HubSpot for the Contacts table.
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

A file with a comma separated list of properties to select from HubSpot for the Deals table.
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

A password used to authenticate to a proxy-based firewall.
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

The TCP port for a proxy-based firewall.
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

The name or IP address of a proxy-based firewall.
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

The protocol used by a proxy-based firewall.
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

The user name to use to authenticate with a proxy-based firewall.
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

Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
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

A path to the directory that contains the schema files defining tables, views, and stored procedures.
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

A path to the log file.
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

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.
Data Type

string

Default Value

"100MB"

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 100MB. Values lower than 100kB will use 100kB as the value instead.





Max Rows

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
Data Type

string

Default Value

"-1"

Remarks

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.





OAuth Access Token

The access token for connecting using OAuth.
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

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

string

Default Value

""

Remarks

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





OAuth Client Secret

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

string

Default Value

""

Remarks

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





OAuth Refresh Token

The OAuth refresh token for the corresponding OAuth access token.
Data Type

string

Default Value

""

Remarks

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





OAuth Settings Location

The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH. Alternatively, this can be held in memory by specifying a value starting with memory://.
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 avoid requiring the user to manually enter OAuth connection properties and allowing the credentials to be shared across connections or processes. The default OAuthSettingsLocation is a settings file located in the %AppData%\CData folder. Alternatively to specifying a file path, memory storage can be used instead. Memory locations are specified by using a value starting with 'memory://' followed by a unique identifier for that set of credentials (ex: memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike with the file based storage, you must manually store the credentials when closing the connection with memory storage to be able to set them in the connection when the process is started again. The OAuth property values can be retrieved with a query to the sys_connection_props system table. If there are multiple connections using the same credentials, the properties should be read from the last connection to be closed.





OAuth Verifier

The verifier code returned from the OAuth authorization URL.
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.

Authentication on Headless Machines

See the Getting Started section to obtain the OAuthVerifier value.

Set OAuthSettingsLocation along with OAuthVerifier. When you connect, the driver exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified file. Set InitiateOAuth to GETANDREFRESH automate the exchange.

Once the OAuth settings file has been generated, you can remove OAuthVerifier from the connection properties and connect with OAuthSettingsLocation set.

To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.





Offline

Use offline mode to get the data from the cache instead of the live source.
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

Hidden properties needed only in specific use cases.
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=TrueCaches only a subset of columns, which you can specify in your query.
QueryPassthrough=TruePasses the specified query to the cache database instead of using the SQL parser of the driver.

Integration and Formatting

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





Pool Idle Timeout

The allowed idle time for a connection before it is closed.
Data Type

string

Default Value

""

Remarks

The allowed idle time a connection can remain in the pool until the connection is closed. The default is 60 seconds.





Pool Max Size

The maximum connections in the pool.
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

The minimum number of connections in the pool.
Data Type

string

Default Value

"1"

Remarks

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





Pool Wait Time

The max seconds to wait for an available connection.
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

The authentication type to use to authenticate to the ProxyServer proxy.
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:

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.





Proxy Auto Detect

This indicates whether to use the system proxy settings or not. Set ProxyAutoDetect to FALSE to use custom proxy settings. This takes precedence over other proxy settings.
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

A semicolon separated list of hosts or IPs that will be exempt from connecting through the ProxyServer .
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

A password to be used to authenticate to the ProxyServer proxy.
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

The TCP port the ProxyServer proxy is running on.
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

The hostname or IP address of a proxy to route HTTP traffic through.
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

The SSL type to use when connecting to the ProxyServer proxy.
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:

AUTODefault 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.
ALWAYSThe connection is always SSL enabled.
NEVERThe connection is not SSL enabled.
TUNNELThe 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

A user name to be used to authenticate to the ProxyServer proxy.
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

Indicates whether or not to include pseudo columns as columns to the table.
Data Type

string

Default Value

""

Remarks

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





Readonly

You can use this property to enforce read-only access to HubSpot from the provider.
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

The runtime key used for licensing.
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

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

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine 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

Enhances SQL functionality beyond what can be supported through the API directly, by enabling in-memory client-side processing.
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

Restrict the tables reported to a subset of the available tables. For example: Tables=TableA,TableB,TableC.
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

The value in seconds until the timeout error is thrown, canceling the operation.
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

Enables 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

Boolean determining if the display names for the columns should be used instead of the API 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

Boolean determining if simple names should be used for tables and columns.
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

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

string

Default Value

"1"

Remarks

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

1Setting 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.
2Setting Verbosity to 2 will log everything included in Verbosity 1, cache queries, and additional information about the request, if applicable, such as HTTP headers.
3Setting Verbosity to 3 will additionally log the body of the request and the response.
4Setting Verbosity to 4 will additionally log transport-level communication with the data source. This includes SSL negotiation.
5Setting 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

Restrict the views reported to a subset of the available tables. For example: Views=ViewsA,ViewsB,ViewsC.
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





TablesBack To Top

  1. Companies
  2. CompanyProperties
  3. CompanyPropertyGroups
  4. ContactLists
  5. ContactProperties
  6. ContactPropertyGroups
  7. Contacts
  8. ContactsInList
  9. CrmAssociations
  10. DealAssociations
  11. DealProperties
  12. DealPropertyGroups
  13. Deals
  14. EmailCampaigns
  15. EmailSubscriptions
  16. Engagements
  17. Forms
  18. LineItems
  19. Products
  20. Settings
  21. SocialMediaMessages
  22. 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
CompanyId [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.





CrmAssociations

Query the available CRM Associations in HubSpot.

Table Specific Information

The CrmAssociations table is used to manage associations between tickets, products, line items, and their related contacts, companies, and deals.

Select

Get the IDs of objects associated with the given object (FromObjectId), based on the specified association type (DefinitionId).See the HubSpot API documentation for a list of DefinitionIds. FromObjectId and DefinitionId are required. For example, to get the ticket IDs for all tickets associated with a contact record with vid 25, the FromobjectId would be 25, and the definitionId would be 15:

SELECT * FROM CrmAssociations WHERE FromObjectId='25' AND DefinitionId='15'
Insert

You would use insertion to associate a ticket with a contact, or to associate a line item object to a deal. When creating a new CRM Association FromObjectId, ToObjectId, Category (currently this must be 'HUBSPOT_DEFINED') and DefinitionId are required. For example:

INSERT INTO CrmAssociations (FromObjectId, ToObjectId, Category, DefinitionId) VALUES (496346, 176602, 'HUBSPOT_DEFINED',15)
Update

Updates are not supported for this table.

Delete

When deleting a CRM Association FromObjectId, ToObjectId, Category (currently this must be 'HUBSPOT_DEFINED') and DefinitionId are required. For example:

DELETE FROM CrmAssociations WHERE FromObjectId=496346 AND ToObjectId=176602 AND Category='HUBSPOT_DEFINED' AND DefinitionId=15
Columns

Name Type ReadOnly References Description
Results String True

The IDs of objects associated with the given object, based on the specified association type.

FromObjectId [KEY] Integer False

The ID of the object being associated.

ToObjectId [KEY] Integer False

The ID of the object the from object is being associated with.

Category [KEY] String False

The category of the association. Currently this must be 'HUBSPOT_DEFINED'.

DefinitionId [KEY] Integer False

The ID of the association definition.

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

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.





LineItems

Query the available line items in HubSpot.

Table Specific Information

A line item represents a line in an order, containing details such as the product, quantity, and price for each line of an order.

Select

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

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

When creating a new line item ProductId is required. You can also specify Name,Description,Price and Quantity. For example:

INSERT INTO LineItems (ProductId, Name,Description, Quantity,Price) VALUES ('123', 'NameExample', 'DescriptionExample', '50', '1324')
Update

When updating line items, you can change non readOnly field by specifying the line item Id. For example:

UPDATE LineItems Set Name='UpdatedName', Description='Updated description', Price=123 WHERE id = '123' 
Delete

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

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

Name Type ReadOnly References Description
Id [KEY] Integer True

The internal ID for this line item.

Name String False

The name for the product for this line item.

Price String False

The price of the line item.

Quantity String False

The quantity of the line item.

Description String False

The description of the line item.

ProductId String False

The objectId of a product object, and represents the product being sold. It is required when you create a new line item.

Version Integer True

The current version of this line item. This is incremented each time the line item is updated.

IsDeleted Boolean True

Boolean indicating whether or not the line item 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.





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.





ViewsBack To Top

  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.