Connection String OptionsBack To Top

AuthScheme
APIKey
Schema
OAuthOptionalScopes
OAuthRequiredScopes
InitiateOAuth
OAuthClientId
OAuthClientSecret
OAuthAccessToken
OAuthSettingsLocation
CallbackURL
OAuthVerifier
OAuthRefreshToken
OAuthExpiresIn
OAuthTokenTimestamp
SSLServerCert
FirewallType
FirewallServer
FirewallPort
FirewallUser
FirewallPassword
ProxyAutoDetect
ProxyServer
ProxyPort
ProxyAuthScheme
ProxyUser
ProxyPassword
ProxySSLType
ProxyExceptions
Logfile
Verbosity
LogModules
MaxLogFileSize
MaxLogFileCount
Location
BrowsableSchemas
Tables
Views
AutoCache
CacheDriver
CacheConnection
CacheLocation
CacheTolerance
Offline
CacheMetadata
Archived
BatchSize
ColumnSizes
CompanyPropertiesFile
ConnectionLifeTime
ConnectOnOpen
ContactPropertiesFile
DealPropertiesFile
ExpandAssociations
GenerateSchemaFiles
MaxRows
NumberColumnMode
Other
Pagesize
PoolIdleTimeout
PoolMaxSize
PoolMinSize
PoolWaitTime
PseudoColumns
Readonly
RTK
SupportEnhancedSQL
TicketPropertiesFile
Timeout
UseConnectionPooling
UseDisplayNames
UseDropdownLabels
UseSimpleNames

AuthScheme

Data Type

string

Default Value

"Auto"

Remarks

Auto: Lets the driver decide automatically based on the other connection properties you have set.
OAuth: Set to this to perform the OAuth 2.0 authentication.
Token: Set this to perform Token Based Authentication via the RSBHubspot_p_APIKey property.

APIKey

Data Type

string

Default Value

Remarks

If you have admin access in your HubSpot account, you can manage your HubSpot API key from your integrations page.

Schema

Data Type

string

Default Value

"HubSpot"

Remarks

Set this property to HUBSPOT to use the HubSpot CRM API v2 or HUBSPOTV3 to use the HubSpot CRM API v3.

"content social automation files forms timeline e-commerce business-intelligence integration-sync tickets sales-email-read crm.objects.custom.read crm.schemas.custom.read crm.objects.feedback_submissions.read crm.schemas.deals.read crm.objects.owners.read crm.objects.contacts.write crm.objects.companies.write crm.lists.write crm.objects.companies.read crm.lists.read crm.objects.deals.read crm.schemas.contacts.read crm.objects.deals.write crm.schemas.companies.read"

Remarks

A comma-separated set of optional scopes that give you permissions to your HubSpot account.

OAuthRequiredScopes

Data Type

string

Default Value

"crm.lists.read crm.lists.write crm.objects.companies.read crm.objects.companies.write crm.objects.contacts.read crm.objects.contacts.write crm.objects.deals.read crm.objects.deals.write crm.objects.owners.read crm.schemas.deals.read crm.schemas.deals.write crm.schemas.companies.read crm.schemas.companies.write crm.schemas.contacts.read crm.schemas.contacts.write"

Remarks

A comma-separated set of permissions that your Hubspot app needs access to. Any scopes that you've checked off in your app's Auth settings will be treated as required, and you'll need to include them in this parameter or the authorization page will display an error.

InitiateOAuth

Data Type

string

Default Value

"OFF"

Remarks

The following options are available:

OFF: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.
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.
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.

OAuthClientId

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.

OAuthClientSecret

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.

OAuthAccessToken

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 user name and password. The access token protects your credentials by keeping them on the server.

OAuthSettingsLocation

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.

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.

If left unspecified, the default location is "%APPDATA%\\CData\\HubSpot Data Provider\\OAuthSettings.txt" with %APPDATA% being set to the user's configuration directory:


Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Mac	~/Library/Application Support
Linux	~/.config

CallbackURL

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.

OAuthVerifier

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 Establishing a Connection 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.

OAuthRefreshToken

Data Type

string

Default Value

Remarks

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

OAuthExpiresIn

Data Type

string

Default Value

Remarks

Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.

OAuthTokenTimestamp

Data Type

string

Default Value

Remarks

Pair with OAuthExpiresIn to determine when the AccessToken will expire.

SSLServerCert

Data Type

string

Default Value

Remarks

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

This property can take the following forms:


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 is accepted.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

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

FirewallType

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.

FirewallServer

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.

FirewallPort

Data Type

int

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.

FirewallUser

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.

FirewallPassword

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.

ProxyAutoDetect

Data Type

bool

Default Value

false

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

NOTE: When this property is set to True, the proxy used is determined as follows:

A search from the JVM properties (http.proxy, https.proxy, socksProxy, etc.) is performed.
In the case that the JVM properties don't exist, a search from java.home/lib/net.properties is performed.
In the case that java.net.useSystemProxies is set to True, a search from the SystemProxy is performed.
In Windows only, an attempt is made to retrieve these properties from the Internet Options in the registry.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

ProxyServer

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.

ProxyPort

Data Type

int

Default Value

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.

ProxyAuthScheme

Data Type

string

Default Value

"BASIC"

Remarks

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

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

The authentication type can be one of the following:

BASIC: The driver performs HTTP BASIC authentication.
DIGEST: The driver performs HTTP DIGEST authentication.
NEGOTIATE: The driver retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
PROPRIETARY: The driver does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

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

ProxyUser

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 user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain

domain\user

ProxyPassword

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.

ProxySSLType

Data Type

string

Default Value

"AUTO"

Remarks

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


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

ProxyExceptions

Data Type

string

Default Value

Remarks

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

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

Logfile

Data Type

string

Default Value

Remarks

Once this property is set, the driver will populate the log file as it carries out various tasks, such as when authentication is performed or queries are executed. If the specified file doesn't already exist, it will be created.

Connection strings and version information are also logged, though connection properties containing sensitive information are masked automatically.

If a relative filepath is supplied, the location of the log file will be resolved based on the path found in the Location connection property.

For more control over what is written to the log file, you can adjust the Verbosity property.

Log contents are categorized into several modules. You can show/hide individual modules using the LogModules property.

To edit the maximum size of a single logfile before a new one is created, see MaxLogFileSize.

If you would like to place a cap on the number of logfiles generated, use MaxLogFileCount.

Java Logging

Java logging is also supported. To enable Java logging, set Logfile to:

Logfile=JAVALOG://myloggername

As in the above sample, JAVALOG:// is a required prefix to use Java logging, and you will substitute your own Logger.

The supplied Logger's getLogger method is then called, using the supplied value to create the Logger instance. If a logging instance already exists, it will reference the existing instance.

When Java logging is enabled, the Verbosity will now correspond to specific logging levels.

Verbosity

Data Type

string

Default Value

"1"

Remarks

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

LogModules

Data Type

string

Default Value

Remarks

Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.

See the Logging page for an overview.

MaxLogFileSize

Data Type

string

Default Value

"100MB"

Remarks

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

Adjust the maximum number of logfiles generated with MaxLogFileCount.

MaxLogFileCount

Data Type

int

Default Value

-1

Remarks

When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted.

The minimum supported value is 2. A value of 0 or a negative value indicates no limit on the count.

Adjust the maximum size of the logfiles generated with MaxLogFileSize.

Location

Data Type

string

Default Value

"%APPDATA%\\CData\\HubSpot Data Provider\\Schema"

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 folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

Note: Given that this driver supports multiple schemas, the structure for HubSpot custom schema files is as follows:

Each schema is given a folder corresponding to that schema name.
These schema folders are contained in a parent folder.
The parent folder should be set as the Location, not an individual schema's folder.

If left unspecified, the default location is "%APPDATA%\\CData\\HubSpot Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:


Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Mac	~/Library/Application Support
Linux	~/.config

BrowsableSchemas

Data Type

string

Default Value

Remarks

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

Tables

Data Type

string

Default Value

Remarks

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

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

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Data Type

string

Default Value

Remarks

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

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

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

AutoCache

Data Type

bool

Default Value

false

Remarks

When AutoCache = true, 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.

Setting the Caching Database

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

CacheLocation: Specifies the path to the file store.
CacheDriver and CacheConnection: Specifies a driver to a database and the connection string.

CacheDriver

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 want to use to cache data.

Note that you must also add the CacheDriver JAR file 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 file is shipped in the JDK. You can find the JAR file, 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

The following 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

The following 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;

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

The following is a JDBC URL for the Oracle Thin Client:

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

NOTE: If using a version of Oracle older than 9i, the cache driver will instead be oracle.jdbc.driver.OracleDriver .

PostgreSQL

The following JDBC URL uses the official PostgreSQL JDBC driver:

jdbc:hubspot:CacheDriver=cdata.jdbc.postgresql.PostgreSQLDriver;CacheConnection='jdbc:postgresql:User=postgres;Password=admin;Database=postgres;Server=localhost;Port=5432;';InitiateOAuth=GETANDREFRESH;

CacheConnection

Data Type

string

Default Value

Remarks

The cache database is determined based on the CacheDriver and CacheConnection properties. Both properties 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 CacheDriver and CacheConnection. The following 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. The following is the syntax of the JDBC URL:

jdbc:sqlite:dataSource

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

MySQL

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

jdbc:mysql:User=root;Password=root;Server=localhost;Port=3306;Database=cache

The following are typical connection properties:

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

SQL Server

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

jdbc:sqlserver://[serverName[\instance][:port]][;database=databaseName][;property=value[;property=value] ... ]

For example:

jdbc:sqlserver://localhost\sqlexpress:1433;integratedSecurity=true

The following are typical SQL Server connection properties:

Server: The name or network address of the computer running SQL Server. To connect to a named instance instead of the default instance, this property can be used to specify the host name and the instance, separated by a backslash.
Port: The port SQL Server is running on.
Database: The name of the SQL Server database.
Integrated Security: Set this option to true to use the current Windows account for authentication. Set this option to false if you are setting the User and Password in the connection.
To use integrated security, you will also need to add sqljdbc_auth.dll to a folder on the Windows system path. This file is located in the auth subfolder of the Microsoft JDBC Driver for SQL Server installation. The bitness of the assembly must match the bitness of your JVM.
User Id: The user name provided for authentication with SQL Server. This property is only needed if you are not using integrated security.
Password: The password provided for authentication with SQL Server. This property is only needed if you are not using integrated security.

Oracle

The following 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

The following are typical connection properties:

Data Source: The connect descriptor that identifies the Oracle database. This can be a TNS connect descriptor, an Oracle Net Services name that resolves to a connect descriptor, or, after version 11g, an Easy Connect naming (the host name of the Oracle server with an optional port and service name).
Password: The password provided for authentication with the Oracle database.
User Id: The user Id provided for authentication with the Oracle database.

PostgreSQL

The following 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

The following are typical connection properties:

Host: The address of the server hosting the PostgreSQL database.
Port: The port used to connect to the server hosting the PostgreSQL database.
Database: The name of the database.
User name: The user Id provided for authentication with the PostgreSQL database. You can specify this in the JDBC URL with the "user" parameter.
Password: The password provided for authentication with the PostgreSQL database.

CacheLocation

Data Type

string

Default Value

"%APPDATA%\\CData\\HubSpot Data Provider"

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 file to the classpath. The JAR file, derby.jar, is shipped in the JDK and located in the db subfolder of the JDK installation.

If left unspecified, the default location is "%APPDATA%\\CData\\HubSpot Data Provider" with %APPDATA% being set to the user's configuration directory:


Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Mac	~/Library/Application Support
Linux	~/.config

CacheTolerance

Data Type

int

Default Value

600

Remarks

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

Offline

Data Type

bool

Default Value

false

Remarks

When Offline = 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.

CacheMetadata

Data Type

bool

Default Value

false

Remarks

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

When to Use CacheMetadata

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

When Not to Use CacheMetadata

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

Archived

Data Type

bool

Default Value

false

Remarks

This property is only valid for schema "HubSpotV3" and for tables Contacts, Companies, Deals, Tickets, Products, Quotes and LineItems of HubSpotV3.

BatchSize

Data Type

int

Default Value

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.

ColumnSizes

Data Type

string

Default Value

Remarks

Some fields may requre a larger column size than the default. These can be entered as a comma separated list of tablename.columnname=value or columnname=value pairs. For instance: Table1.Col1=4000,Table1.Col2=5000,Col1=13. The values entered will override the default for any detected fields. The column specified with a tablename takes priority over the same column name without the tablename in front of it. In this example, the size of Col1 in Table1 is 4000, and in all the other tables it is 13.

CompanyPropertiesFile

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 CompanyProperties table. For instance: dealname,amount,description.

ConnectionLifeTime

Data Type

int

Default Value

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.

ConnectOnOpen

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).

ContactPropertiesFile

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.

DealPropertiesFile

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.

ExpandAssociations

Data Type

bool

Default Value

false

Remarks

If False, the driver will expose one associations table for each HubSpot object (ex. DealAssociations, CompanyAssociations, ContactAssociations, etc.). These tables will return the Id-s of all the objects associated to that specific object type.
If True, the driver will expose one associations table for each association between two HubSpot object types (ex. CompaniesToContactsAssociations, ContactsToDealsAssociations, DealsToLineitemsAssociations, etc.). Each of these tables will return the Id pairs of the associated records from the two object types.

Note: This property is only valid for schema "HubSpotV3".

GenerateSchemaFiles

Data Type

string

Default Value

"Never"

Remarks

This property outputs schemas to .rsd files in the path specified by Location.

Available settings are the following:

Never: A schema file will never be generated.
OnUse: A schema file will be generated the first time a table is referenced, provided the schema file for the table does not already exist.
OnStart: A schema file will be generated at connection time for any tables that do not currently have a schema file.
OnCreate: A schema file will be generated by when running a CREATE TABLE SQL query.

Note that if you want to regenerate a file, you will first need to delete it.

Generate Schemas with SQL

When you set GenerateSchemaFiles to OnUse, the driver generates schemas as you execute SELECT queries. Schemas are generated for each table referenced in the query.

When you set GenerateSchemaFiles to OnCreate, schemas are only generated when a CREATE TABLE query is executed.

Generate Schemas on Connection

Another way to use this property is to obtain schemas for every table in your database when you connect. To do so, set GenerateSchemaFiles to OnStart and connect.

MaxRows

Data Type

int

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.

NumberColumnMode

Data Type

string

Default Value

"DECIMAL"

Remarks

HubSpot reports all numeric data type fields (ex. integer, double, decimal, etc.) as 'number' type. For this reason, there isn't a reliable way for the driver to determinate the exact numeric type for each detected numeric field.

By default, all numbers will be specified as decimal. However, depending on your needs, you may wish to handle number columns as either double or decimal. In addition, you can also set the value to string to report all number columns as strings.

Note: This connection property is applicable only for 'HUBSPOTV3' Schema.

Other

Data Type

string

Default Value

Remarks

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

Specify multiple properties in a semicolon-separated list.

Caching Configuration


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

Integration and Formatting


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

Pagesize

Data Type

int

Default Value

100

Remarks

The Pagesize property affects the maximum number of results to return per page from HubSpot. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

PoolIdleTimeout

Data Type

int

Default Value

Remarks

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

PoolMaxSize

Data Type

int

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.

PoolMinSize

Data Type

int

Default Value

Remarks

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

PoolWaitTime

Data Type

int

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.

PseudoColumns

Data Type

string

Default Value

Remarks

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

Readonly

Data Type

bool

Default Value

false

Remarks

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

RTK

Data Type

string

Default Value

Remarks

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

SupportEnhancedSQL

Data Type

bool

Default Value

true

Remarks

When SupportEnhancedSQL = 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 = 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.

TicketPropertiesFile

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 TicketPropertiesFile 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 TicketProperties table. For instance: dealname,amount,description.

Timeout

Data Type

int

Default Value

Remarks

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

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

UseConnectionPooling

Data Type

bool

Default Value

false

Remarks

This property enables connection pooling. The default is false. See Connection Pooling for information on using connection pools.

UseDisplayNames

Data Type

bool

Default Value

true

Remarks

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

UseDropdownLabels

Data Type

bool

Default Value

true

Remarks

Boolean determining if dropdown labels should be used instead of internal values for enums. This will only affect custom fields.

UseDropdownLabels will default to True. If set to false, the internal values will be displayed for dropdown values instead of the labels..

UseSimpleNames

Data Type

bool

Default Value

false

Remarks

HubSpot tables and columns can use special characters in names that are normally not allowed in standard databases. UseSimpleNames makes the driver easier to use with traditional database tools.

Setting UseSimpleNames to true will simplify the names of tables and columns returned. It will enforce a naming scheme such that only alphanumeric characters and the underscore are valid for the displayed table and column names. Any nonalphanumeric characters will be converted to an underscore.

TablesBack To Top

BlogAuthors
BlogPosts
BlogTopics
Companies
CompanyProperties
CompanyPropertyGroups
ContactLists
ContactProperties
ContactPropertyGroups
Contacts
ContactsInList
CrmAssociations
DealAssociations
DealPipelines
DealProperties
DealPropertyGroups
Deals
EcommerceSettings
EmailCampaigns
EmailSubscriptions
Engagements
Folders
Forms
LineItems
MarketingEmails
Pages
Products
SocialMediaMessages
TicketProperties
TicketPropertyGroups
Tickets
Workflows

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	ReadOnly	References	Description
Id [KEY]	Long	True		The id of the author.
FullName	String	False		The first and last name of the blog author.
CreatedAt	Datetime	True		When the blog author was first created.
Slug	String	True		The path appended to the blog URL at which the author page will live.
DeletedAt	Datetime	True		When the author was deleted.
Email	String	False		The email address of the blog author.
FacebookProfile	String	False		The blog author's Facebook page.
GooglePlusProfile	String	False		The blog author's Google Plus profile.
LinkedInProfile	String	False		The blog author's LinkedIn profile
TwitterProfile	String	False		The blog author's twitter handle.
AvatarUrl	String	True		A small photo or image of the blog author.
UserId	Integer	False		The HubSpot user id of the blog author.
Username	String	False		The HubSpot username of the blog author.
Website	String	False		The blog author's web site.
UpdatedAt	Datetime	True		When the blog author was last updated.
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'.

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	ReadOnly	References	Description
Id [KEY]	Long	True		The id of the blog post.
AuthorUserId	Long	False		The HubSpot user id of the blog author.
BlogAuthorId	Long	False	BlogAuthors.Id	The integer id of the blog author, look up via the blog authors end.
CampaignId	String	False	EmailCampaigns.Id	The guid of the marketing campaign this post is associated with.
ContentGroupId	Long	False		The id of the blog that this post belongs to. Get the id by looking at the blog API.
TopicIds	String	False		The individual topics the post is associated with.
CreatedAt	Datetime	True		When the post was first created.
DeletedAt	Datetime	True		When the post was deleted.
IsArchived	Boolean	True		If True, the post will not show up in your dashboard, although the post will still be live.
Name	String	False		The internal name of the blog post.
Slug	String	False		The path of the URL on which the post will live.
UpdatedAt	Datetime	True		When the post was last updated.
PostSummary	String	False		The summary of the blog post that will appear on the main listing page.
PostBody	String	False		The HTML of the main post body.
Url	String	True		The full URL with domain and scheme to the blog post. Will return a 404 if the post is not yet published.
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'.

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	ReadOnly	References	Description
Id [KEY]	Long	True		The blog topic id.
CreatedAt	Datetime	True		When the topic was created
DeletedAt	Datetime	True		When the topic was deleted.
Description	String	False		A description of the topic.
Name	String	False		The name of the topic.
Slug	String	True		How the topic will appear in the url.
UpdatedAt	Datetime	True		When the topic was last updated.
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'.

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.

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'.

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'.

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'.

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'.

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'.

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'

Update

You can update contacts, for example, Additional email addresses:

UPDATE [Contacts] SET [Additional email addresses] = 'testwork40@example.com;testwork41@example.com', [last name] = 'last name 5' WHERE VID = 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.

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.

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.

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.

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	ReadOnly	References	Description
PipelineId [KEY]	String	True		The id of the pipeline.
PipelineName	String	False		The name of the pipeline.
IsActive	Boolean	False		A boolean indicating if the pipeline is active or not.
StagesAggregate	String	False		The stages in the pipeline.

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'.

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'.

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.

EcommerceSettings

Retrieve the current ecommerce settings for your portal or app.

Table Specific Information

The Ecommerce Settings table is where you set up the property mappings for your use of the Ecommerce Bridge API.

Select

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

SELECT * FROM EcommerceSettings

SELECT * FROM EcommerceSettings WHERE AppId='123456'

Columns


Name	Type	ReadOnly	References	Description
PropertyName	String	True		Name of the property.
DataType	String	True		Data type of the property. The allowed values are STRING, NUMBER, DATETIME, AVATAR_IMAGE.
TargetHubspotProperty	String	True		Name of the target property on Hubspot that this property represents.
Enabled	Boolean	True		Boolean value that shows if this representation of this property is enabled or not.
ImportOnInstall	Boolean	True		Boolean value that shows if this property should be imported during installation procedure.
SettingType	String	True		The type of the property. It can be: Product, Deal, LineItem or Contact The allowed values are PRODUCT, DEAL, CONTACT, LINE_ITEM.
AppId	String	True		The app ID in which the property is or will be imported.

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'.

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. When deleting an email subscription the contact will be opted out and will not receive emails from the specified subscription type(mailing group). 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'.

Engagements

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

Table Specific Information

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

Select

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

SELECT * FROM Engagements WHERE Id = 12345

Insert and Update

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

CALL: ToNumber, FromNumber, Body, DurationMilliseconds, Status, ExternalId, ExternalAccountId, RecordingUrl, Disposition
EMAIL: FromEmail, FromFirstName, FromLastName, ToEmail, Cc, Bcc, Subject, EmailHtml, EmailText
MEETING: Body, StartTime, EndTime, Title
PUBLISHING_TASK: Body, CampaignGuid, Category, CategoryId, State, Name
TASK: Body, Status, ForObjectType

In addition, there are some common fields available when inserting or updating any type of Engagement. These include DateTime, AssociatedContacts, AssociatedCompanies, AssociatedDeals, AssociatedOwners, and AssociatedWorkflows. For instance:

INSERT INTO Engagements (DateTime, Type, Body, CampaignGuid, Category, CategoryId, ContentId, State, Name, AssociatedContacts) VALUES ('1/1/2011', 'PUBLISHING_TASK', 'Cool Post with Topics', 'f43fe9fd-4082-4a45-93d8-cb8a88f01654', 'BLOG_POST', 3, '2682673052', 'TODO', 'Test Blog Task With topics 3', '234,567')

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The id of the engagement.
PortalId	Integer	True		The portal id the engagement is associated with.
IsActive	Boolean	True		Boolean indicating if the engagement is active or not.
CreatedAt	Datetime	True		When the engagement was created.
UpdatedAt	Datetime	True		When the engagement was last updated.
CreatedBy	Integer	True		User id of the user who created the engagement.
ModifiedBy	Integer	True		User id of the user who lastmodified the engagement.
OwnerId	Integer	False	Owners.OwnerId	The owner id of the engagement.
Type	String	False		The type of engagement. Required on inserts.
ActivityType	String	False		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	False		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.
AssociatedTickets	String	False		A comma separated list of ticket ids associated with the engagement.
Attachments	String	False		The attachments associated with this 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, MEETING.
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.
MeetingOutcome	String	False		Meeting outcome. Used when TYPE = MEETING. The allowed values are: SCHEDULED, COMPLETED, RESCHEDULED, NO SHOW, CANCELED.
TaskType	String	False		Task type. Used when TYPE = TASK. The allowed values are: TODO, EMAIL, 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'.

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	ReadOnly	References	Description
Id [KEY]	Long	False		The id of the folder.
Category	Integer	True		The category of the folder.
CreatedAt	Datetime	True		When the folder was created.
DeletedAt	Datetime	True		When the folder was deleted.
FullPath	String	True		The full path to the folder.
Name	String	True		The name of the folder.
ParentFolderId	Long	False	Folders.Id	The id of the parent folder for this folder if available.
UpdatedAt	Datetime	True		When the folder was last updated.
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'.

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'.

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

MarketingEmails

Create and manage HubSpot Marketing Emails.

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The id of the email.
RssToEmailTimingRepeats	String	False		[ 'instant', 'daily', 'weekly', 'monthly' ]
RssToEmailTimingRepeatsOnMonthly	Integer	False		What day of the month should the monthly email be sent [1-31].
RssToEmailTimingRepeatsOnWeekly	Integer	False		What day of the week should the weekly email be sent [1=monday - 7=sunday].
RssToEmailTimingSummary	String	False		Descripton. Not used.
RssToEmailTimingTime	String	False		Time the email should be sent at.
Ab	Boolean	True		Whether or not the page is part of an AB test.
AbHoursToWait	Integer	False		On AB emails, if test results are inconclusive after the specified hours, variation A will be sent.
AbSampleSizeDefault	String	True		['MASTER', 'VARIANT'] if there are less than 1000 recipients, only one variation will be sent.
AbSamplingDefault	String	False		['MASTER', 'VARIANT'] if AB test results are inconclusive in the test group, choose a variation to send (resp. A or B) to the remaining contacts.
AbStatus	String	False		['MASTER', 'VARIANT'] determines if the current email is variation A or variation B.
AbSuccessMetric	String	False		[ CLICKS_BY_OPENS, CLICKS_BY_DELIVERED, OPENS_BY_DELIVERED ] metric that will be used to determine the winning variation.
AbTestId	String	False		ID shared between variation A and B
AbTestPercentage	Integer	False		The size of the test group.
AbVariation	Boolean	False		Determines whether the email is a variant or not.
AbsoluteUrl	String	False		The URL of the web version of the email.
AllEmailCampaignIds	String	False		A list of email IDs that are associated with the email.
AnalyticsPageId	String	False		The ID used to access the analytics page of the email.
AnalyticsPageType	String	True		Always 'email' for an email.
Archived	Boolean	False		Determines whether the email is archived or not.
Author	String	True		The email of the user who made the last update to the email.
AuthorAt	Datetime	True		Timestamp of the last update to the email in milliseconds.
AuthorEmail	String	True		The email of the user who made the last update to the email.
AuthorName	String	True		The name of the user who made the last update to the email.
AuthorUserId	Long	True		ID of the user who made the last update to the email.
BlogEmailType	String	False		['instant', 'daily', 'weekly', 'monthly'] the cadence for how often blog emails should be sent.
Campaign	String	False		The ID of an email's marketing campaign.
CampaignName	String	False		The name of the email's marketing campaign.
CanSpamSettingsId	Long	False		ID used to retrieve the company address, shown in the footer.
CategoryId	Integer	True		The category ID value, which is always 2 for emails (read only).
ClonedFrom	Long	True		If the email was cloned, ID of the email it was cloned from.
ContentTypeCategory	Integer	True		The category ID value, which is always 2 for emails (read only).
CreatePage	Boolean	False		Enables a web version of the email when set to true.
Created	Datetime	True		The timestamp of the email's creation, in milliseconds.
CurrentlyPublished	Boolean	False		Determines the publish status of the email.
Domain	String	True		The domain of the web version of the email. Defaults to the primary domain.
EmailBody	String	False		The main content of the email within the 'main email body' module.
EmailNote	String	False		Optional email notes, included in the details page of the email.
EmailType	String	False		Type of the email.
FeedbackEmailCategory	String	False		[ 'NPS', 'CES', 'CUSTOM' ] If the email is a feedback email, determines type of feedback email.
FeedbackSurveyId	Long	False		The id of the feedback survey that is linked to the email.
FolderId	Long	False		If the email is in a folder, id of that folder.
FreezeDate	Datetime	False		The publish date or updated date if the email is not published.
FromName	String	False		The sender name recipients will see (linked to the replyTo address).
HtmlTitle	String	False		The page title of the web version of the email.
IsGraymailSuppressionEnabled	Boolean	False		If true, the email will not send to unengaged contacts.
IsLocalTimezoneSend	Boolean	False		If true, the email will adjust its send time relative to the recipients timezone.
IsPublished	Boolean	False		If true, the email is in a published state.
LiveDomain	String	True		Domain actually used in the web version (read only)
MailingListsExcluded	String	False		A list of all contact lists to exclude from the email send.
MailingListsIncluded	String	False		A list of all contact lists included in the email send.
MaxRssEntries	Integer	False		In blog and recurring emails, the max number of entries to include.
MetaDescription	String	False		Meta description of the web version of the email, to drive search engine traffic to your page
Name	String	False		The name of the email, as displayed on the email dashboard.
PageExpiryDate	Datetime	False		The expiration date of the web version of an email, in milliseconds.
PageExpiryRedirectId	Long	False		The url of the page the user will be redirected to after the web version of the email expires.
PageRedirected	Boolean	False		Indicates if the email's web version has already been set to redirect
PortalId	Long	False		The id of the parent portal.
PreviewKey	String	False		The preview key used to generate the preview url before the email is published
ProcessingStatus	String	False		The email's processing status.
PublishDate	Datetime	False		The timestamp in milliseconds that the email has been published at, or scheduled to send at.
PublishImmediately	Boolean	False		True if the email is not scheduled but will send at publish time.
PublishedAt	Datetime	True		If the email has been published, the time when the publish button has been pressed.
PublishedById	Long	True		If the email has been published, email of the user that pressed the publish button.
PublishedByName	String	True		If the email has been published, name of the user that pressed the publish button.
PublishedUrl	String	True		AbsoluteUrl, only if the email is currentlyPublished
ReplyTo	String	False		The email address the recipient will see and reply to.
ResolvedDomain	String	True		The domain used in the web version: either the primary one or the one set in the domain field.
RssEmailAuthorLineTemplate	String	False		Text shown before the 'author_line' tag in blog and RSS email's items.
RssEmailBlogImageMaxWidth	Integer	False		The max width for blog post images in RSS emails.
RssEmailByText	String	False		If rssEmailAuthorLineTemplate is not set, word before the author name in blog and RSS email's items.
RssEmailClickThroughText	String	False		Text shown on the link to see the full post in blog and RSS email's items.
RssEmailCommentText	String	False		Text shown on the link to comment the post in blog and RSS email's items.
RssEmailEntryTemplate	String	False		Optional, custom template for every RSS entry.
RssEmailEntryTemplateEnabled	Boolean	False		Determines if the Entry Template is used for an RSS email.
RssEmailUrl	String	False		URL used for social sharing.
Slug	String	False		Path of the web version URL.
Subcategory	String	False		Subcategory.
Subject	String	False		The subject of the email.
Subscription	Long	False		The id of the email's subscription type.
SubscriptionBlogId	Long	False		For blog emails, id of the linked blog.
SubscriptionName	String	False		The name of the email's subscription type.
TemplatePath	String	False		The path of the email's body template within the design manager.
Transactional	Boolean	True		Determines whether the email is a transactional email or not.
UnpublishedAt	Datetime	False		The timestamp in milliseconds of when the email was unpublished.
Updated	Datetime	True		Timestamp of the last update in milliseconds.
UpdatedById	Long	True		The ID of the last user who updated the email.
Url	String	True		The web version URL
UseRssHeadlineAsSubject	Boolean	False		Setting for RSS emails, uses the latest RSS entry as the email subject.
Widgets	String	False		The content of layout sections of the email (widgets).
VidsExcluded	String	False		A list of contact IDs to exclude from being sent the email.
VidsIncluded	String	False		A list of contacts IDs to include in the email send.
WorkflowNames	String	False		A list of all linked workflows to this email.

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	ReadOnly	References	Description
Id [KEY]	Long	True		The id of the page.
IsArchived	Boolean	True		If True, the page will not show up in your dashboard, although the page will still be live.
CampaignId	String	False	EmailCampaigns.Id	The guid of the marketing campaign this page is associated with.
CampaignName	String	False	EmailCampaigns.Name	The name of the marketing campaign this page is associated with.
CreatedAt	Datetime	True		When the page was created.
CurrentLiveDomain	String	True		The domain this page is currently located on.
DeletedAt	Datetime	True		When the page was deleted.
FooterHTML	String	False		Custom HTML for embed codes, javascript that should be placed before the body tag of the page
HeaderHTML	String	False		Custom HTML for embed codes, javascript, etc. that goes in the head tag of the page
IsDraft	Boolean	False		True if the post is still a draft, invisible to the public. Gets changed when the /publish-action API endpoint is called.
MetaDescription	String	False		A description that goes in meta tag on the page
MetaKeywords	String	False		Keywords for the meta tag.
Name	String	False		The internal name of the page.
Password	String	False		Set this to create a password protected page. Entering the password will be required to view the page.
PublishDate	Datetime	False		The date the page is to be published at in milliseconds since the unix epoch.
Slug	String	False		The path of the URL on which the page will live. Changing this will change the URL.
StyleOverrideId	String	True		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	False		This is set to empty or to
UpdatedAt	Datetime	True		When the page was last updated.
Url	String	True		The full URL with domain and scheme to the page. Will return a 404 if the page is not yet published.
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'.

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


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

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/30/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'.

TicketProperties

Get all of the properties for the ticket object type, along with the property definitions.

Table Specific Information

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

Select

Ticket properties can only be filtered by the unique ticket property name. For example:

SELECT * FROM TicketProperties WHERE Name = 'property_name'

Columns


Name	Type	ReadOnly	References	Description
Name [KEY]	String	False		The name of the ticket 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 ticket 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		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'.

TicketPropertyGroups

Get all of the property groups for the specified Ticket object type.

Table Specific Information

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

Select

Ticket property groups can only be filtered by the unique ticket property group name. For example:

SELECT * FROM TicketPropertyGroups 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.
IsHubspotDefined	Boolean	True		Indicator whether or not this field is defined in the Hubspot.
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'.

Tickets

Get all tickets from a portal.

Table Specific Information

Tickets, along with contacts, companies and deals, are a foundational object in HubSpot CRM. A ticket represents a customer request for help, support, service, or a response, and the responses generated in reply to this request. Tickets 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 Tickets, they can only be filtered by the Id. The accepted filters are illustrated below:

SELECT * FROM Contacts WHERE Id = 123456789

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

Columns


Name	Type	ReadOnly	References	Description
Id [KEY]	Long	False		The internal ID for this ticket.
PortalId	Long	False		The Portal or Hub ID that this object belongs to.
Version	Long	False		Version of the ticket.
IsDeleted	Boolean	False		Whether or not the ticket is deleted. Deleted records will not be included unless you specifically request that deleted records be included.

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'.

ViewsBack To Top

AnalyticsBreakdowns
AnalyticsContents
AnalyticsEventCompletions
AnalyticsForms
AnalyticsSessions
AnalyticsSocialAssists
AnalyticsViews
Blogs
Comments
ContactFormSubmissions
ContactIdentityProfiles
ContactListMemberships
ContactPropertiesHistory
DealPipelineStages
DealPropertiesHistory
DealStages
Domains
EcommerceSyncErrors
EmailCampaignEvents
EmailSubscriptionTypes
EngagementScheduledTasks
EngagementsScheduledTasks
Files
FormFields
FormSubmissions
Owners
SocialMediaChannels
Tasks
Templates
UrlMappings

AnalyticsBreakdowns

Get analytics data broken down by the specified category.

Table Specific Information

Get analytics data broken down by the specified category.

Select

When selecting analytics by breakdown category, they can only be filtered by the StartDate, EndDate, Granularity, BreakdownBy, Filter, FirstDrilldown, SecondDrilldown, Exclude, FilterId, Sort or SortDirection. Defaults to the Totals dimension. The filters must use an exact comparison. For example:

SELECT Totals FROM AnalyticsBreakdowns where Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'

SELECT Sources FROM AnalyticsBreakdowns where Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'

SELECT Geolocation FROM AnalyticsBreakdowns where Granularity = 'monthly' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018' and Filter = 'hubspot'

Columns


Name	Type	References	Description
Totals	String		Data will be the totals rolled up.
Sources	String		Data broken down by traffic source.
Geolocation	String		Data broken down by geographic location.
UtmCampaigns	String		Data broken down by the standard UTM campaigns parameter.
UtmContents	String		Data broken down by the standard UTM contents parameter.
UtmMediums	String		Data broken down by the standard UTM mediums parameter.
UtmSources	String		Data broken down by the standard UTM sources parameter.
UtmTerms	String		Data broken down by the standard UTM terms parameterpages.
FirstDrilldown	String		Used to drilldown into the data. This parameter is designed to reflect the functionality of the HubSpot sources reports.
SecondDrilldown	String		Used to further drill down into the data. Similar to FirstDrilldown, this reflects the functionality of the sources report.
BounceRate	Double		The rate of bounces.
ContactToCustomerRate	Double		The rate of contacts to customers.
Contacts	Integer		The number of generated contacts.
Customers	Integer		The number of generated customers.
Leads	Integer		The number of leads.
MarketingQualifiedLeads	Integer		The number of leads which are more likely to become customers.
NewVisitorSessionRate	Double		The session rate for new visitors.
Opportunities	Integer		The number of opportunitites.
PageviewsPerSession	Double		The ratio of page views over sessions.
RawViews	Integer		The number of raw views.
SalesQualifiedLeads	Integer		The number of prospective customers that are considered ready for the sales process.
SessionToContactRate	Double		The ratio of sessions over generated contacts.
Subscribers	Integer		The number of subscribers.
TimePerSession	Double		The time per session.
Visitors	Integer		The number of visitors.
Visits	Integer		The number of visits.
BreakdownDate	Date		The start date of the time period for which the breakdown data are grouped. This column is applicable only for Granularity types: daily, weekly, monthly, summarize/daily, summarize/weekly and summarize/monthly.

Pseudo-Columns


Name	Type	Description
StartDate	Date		The beginning value of the query range.
EndDate	Date		The ending value of the query range.
Granularity	String		The time period used to group the data. Must be one of: total, daily, weekly, monthly, summarize/daily, summarize/weekly, summarize/monthly.
Filter	String		Filter the returned data to include only the specified breakdown. This parameter can be included multiple times to filter for multiple breakdowns.
Exclude	String		Exclude data for the specified breakdown. This parameter can be included multiple times to exclude multiple breakdowns.
FilterId	String		The ID of an Analytics view. If included, the returned data will only include data that matches the view.

AnalyticsContents

Get analytics data for your HubSpot hosted content. This would include your website and landing pages, as well as any blog pages hosted on HubSpot.

Table Specific Information

Get analytics data for your HubSpot hosted content. This would include your website and landing pages, as well as any blog pages hosted on HubSpot.

Select

When selecting analytics by content type, they can only be filtered by the StartDate, EndDate, Granularity, ContentType, Filter, Exclude, FilterId, Sort or SortDirection. Defaults to the StandardPages dimension. The filters must use an exact comparison. For example:

SELECT LandingPages FROM AnalyticsContents where Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'

SELECT ListingPages FROM AnalyticsContents where Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'

SELECT BlogPosts FROM AnalyticsContents where Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018' AND Filter = 'hubspot'

Columns


Name	Type	References	Description
LandingPages	String		Pull data for landing pages.
StandardPages	String		Pull data for website pages.
BlogPosts	String		Pull data for individual posts.
ListingPages	String		Pull data for blog listing pages.
KnowledgeArticles	String		Pull data for knowledge base articles.
AllPages	String		Get data for all URLs with data collected by HubSpot tracking code. The results are broken down by URL.
Contacts	Integer		The total number of new contacts generated.
ContactsPerPageview	Double		The ratio of contacts to raw views.
CtaViews	Integer		The number of CallsToAction views.
Entrances	Integer		The number of entrances.
Exits	Integer		The number of exits.
ExitsPerPageview	Double		The ratio of exits over page views.
Leads	Integer		The number of leads.
PageBounceRate	Double		The rate of page bounces.
PageBounces	Integer		The number of page bounces.
PageTime	Integer		The time spent on page.
RawViews	Integer		The number of raw views.
Submissions	Integer		The number of submissions.
SubmissionsPerPageview	Double		The ratio of submissions over page views.
TimePerPageview	Double		The ratio of time over page views.
BreakdownDate	Date		The start date of the time period for which the breakdown data are grouped. This column is applicable only for Granularity types: daily, weekly, monthly, summarize/daily, summarize/weekly and summarize/monthly.

Pseudo-Columns


Name	Type	Description
StartDate	Date		The beginning value of the query range.
EndDate	Date		The ending value of the query range.
Granularity	String		The time period used to group the data. Must be one of: total, daily, weekly, monthly, summarize/daily, summarize/weekly, summarize/monthly.
Filter	String		Filter the returned data to include only the specified breakdown. This parameter can be included multiple times to filter for multiple breakdowns.
Exclude	String		Exclude data for the specified breakdown. This parameter can be included multiple times to exclude multiple breakdowns.
FilterId	String		The ID of an Analytics view. If included, the returned data will only include data that matches the view.

AnalyticsEventCompletions

Get analytics data for event completion objects.

Table Specific Information

Get analytics data for event completion objects.

Select

When selecting analytics for event completions, they can only be filtered by the StartDate, EndDate, Granularity, Filter, Exclude, FilterId, Sort or SortDirection. The filters must use an exact comparison. For example:

SELECT * FROM AnalyticsEventCompletions where Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'

SELECT * FROM AnalyticsEventCompletions where Granularity = 'summarize/weekly' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'

Columns


Name	Type	References	Description
EventCompletions	String		Analytics data for event completions.
Completions	Integer		The number of event completions.
BreakdownDate	Date		The start date of the time period for which the breakdown data are grouped. This column is applicable only for Granularity types: daily, weekly, monthly, summarize/daily, summarize/weekly and summarize/monthly.

Pseudo-Columns


Name	Type	Description
StartDate	Date		The beginning value of the query range.
EndDate	Date		The ending value of the query range.
Granularity	String		The time period used to group the data. Must be one of: total, daily, weekly, monthly, summarize/daily, summarize/weekly, summarize/monthly.
Filter	String		Filter the returned data to include only the specified breakdown. This parameter can be included multiple times to filter for multiple breakdowns.
Exclude	String		Exclude data for the specified breakdown. This parameter can be included multiple times to exclude multiple breakdowns.
FilterId	String		The ID of an Analytics view. If included, the returned data will only include data that matches the view.

AnalyticsForms

Get analytics data for form objects.

Table Specific Information

Get analytics data for form objects.

Select

When selecting analytics for forms, they can only be filtered by the StartDate, EndDate, Granularity, Filter, Exclude, FilterId, Sort or SortDirection. The filters must use an exact comparison. For example:

SELECT * FROM AnalyticsForms where Granularity = 'summarize/monthly' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'

SELECT * FROM AnalyticsForms where Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018 AND Sort = 'FormViews''

Columns


Name	Type	References	Description
Forms	String		Analytics data for forms.
Completions	Integer		The number of completions.
FormViews	Integer		The number of times the form has been viewed.
Installs	Integer		The number of installs.
Interactions	Integer		The number of interactions.
Submissions	Integer		The number of submissions.
Visibles	Integer		The number of times the form has been visible.
BreakdownDate	Date		The start date of the time period for which the breakdown data are grouped. This column is applicable only for Granularity types: daily, weekly, monthly, summarize/daily, summarize/weekly and summarize/monthly.

Pseudo-Columns


Name	Type	Description
StartDate	Date		The beginning value of the query range.
EndDate	Date		The ending value of the query range.
Granularity	String		The time period used to group the data. Must be one of: total, daily, weekly, monthly, summarize/daily, summarize/weekly, summarize/monthly.
Filter	String		Filter the returned data to include only the specified breakdown. This parameter can be included multiple times to filter for multiple breakdowns.
Exclude	String		Exclude data for the specified breakdown. This parameter can be included multiple times to exclude multiple breakdowns.
FilterId	String		The ID of an Analytics view. If included, the returned data will only include data that matches the view.

AnalyticsSessions

Get analytics data broken down by sessions.

Table Specific Information

Get analytics data broken down by sessions.

Select

When selecting analytics for event completions, they can only be filtered by the StartDate, EndDate, Granularity, Filter, FirstDrilldown, SecondDrilldown, Exclude, FilterId, Sort or SortDirection. The filters must use an exact comparison. For example:

SELECT * FROM AnalyticsSessions where Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'

SELECT * FROM AnalyticsSessions where Granularity = 'monthly' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018' and Filter = 'hubspot'

Columns


Name	Type	References	Description
Sessions	String		Analytics data for sessions.
Desktop	Long		Desktop sessions.
DirectTraffic	Long		Direct sessions.
EmailMarketing	Long		Email marketing sessions.
Mobile	Long		Mobile sessions.
OrganicSearch	Long		Organic search sessions.
OtherCampaigns	Long		Other campaigns sessions.
Others	Long		Other sessions.
PaidSearch	Long		Paid search sessions.
PaidSocial	Long		Paid social sessions.
Referrals	Long		Referrals sessions.
SocialMedia	Long		Social media sessions.
FirstDrilldown	String		Used to drilldown into the data. This parameter is designed to reflect the functionality of the HubSpot sources reports.
SecondDrilldown	String		Used to further drill down into the data. Similar to FirstDrilldown, this reflects the functionality of the sources report.
BreakdownDate	Date		The start date of the time period for which the breakdown data are grouped. This column is applicable only for Granularity types: daily, weekly, monthly, summarize/daily, summarize/weekly and summarize/monthly.

Pseudo-Columns


Name	Type	Description
StartDate	Date		The beginning value of the query range.
EndDate	Date		The ending value of the query range.
Granularity	String		The time period used to group the data. Must be one of: total, daily, weekly, monthly, summarize/daily, summarize/weekly, summarize/monthly.
Filter	String		Filter the returned data to include only the specified breakdown. This parameter can be included multiple times to filter for multiple breakdowns.
Exclude	String		Exclude data for the specified breakdown. This parameter can be included multiple times to exclude multiple breakdowns.
FilterId	String		The ID of an Analytics view. If included, the returned data will only include data that matches the view.

AnalyticsSocialAssists

Get analytics data for social assist objects.

Table Specific Information

Get analytics data for social assist objects.

Select

SELECT * FROM AnalyticsSocialAssists where Granularity = 'total' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'

SELECT * FROM AnalyticsSocialAssists where Granularity = 'summarize/weekly' AND StartDate = '12/1/2017' AND EndDate = '12/30/2018'

Columns


Name	Type	References	Description
SocialAssists	String		Analytics data for social assists.
RawViews	Integer		The number of raw views.
BreakdownDate	Date		The start date of the time period for which the breakdown data are grouped. This column is applicable only for Granularity types: daily, weekly, monthly, summarize/daily, summarize/weekly and summarize/monthly.

Pseudo-Columns


Name	Type	Description
StartDate	Date		The beginning value of the query range.
EndDate	Date		The ending value of the query range.
Granularity	String		The time period used to group the data. Must be one of: total, daily, weekly, monthly, summarize/daily, summarize/weekly, summarize/monthly.
Filter	String		Filter the returned data to include only the specified breakdown. This parameter can be included multiple times to filter for multiple breakdowns.
Exclude	String		Exclude data for the specified breakdown. This parameter can be included multiple times to exclude multiple breakdowns.
FilterId	String		The ID of an Analytics view. If included, the returned data will only include data that matches the view.

AnalyticsViews

Get the details for the analytics views set up in the portal.

Columns


Name	Type	References	Description
Id [KEY]	Integer		Id of the analytic view.
Title	String		Title of the analytic view.
CreatorId	Integer		Id of the creator.
CreatedAt	Date		Date of the creation.
UpdaterId	Integer		Id of the updater.
UpdatedDate	Date		Date of the update.
DeletedAt	Date		Date of the deletion.
ContainsLegacyReportProperties	Boolean		Determines if the view contains legacy report properties.
ReportPropertyFiltersAggregate	String		Report property filters of the analytic view.

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'.

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'.

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.

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.

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.

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.

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.

DealPropertiesHistory

A list of the historical values of the property.

Columns


Name	Type	References	Description
SourceId	String		Additional data realted to the source-type. May not be populated for all source-types.
SourceVid	String		A list of vids for related contacts. Only populated if related contacts caused the value to change.
PropertyName	String		The name of the deal property.
Value	String		The historical value of the property.
Timestamp	Datetime		Datetime when the property was updated
Source	String		The method by which the property was changed
DealId	Long		The Deal Id of the property.

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.

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'.

EcommerceSyncErrors

Get errors from previously processed sync messages.

Table Specific Information

Because sync messages are processed asynchronously, problems with processing are not surfaced at the time of submission. Instead, you can use the sync errors endpoint to retrieve all errors related to the processing of ecommerce data.

Select

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

SELECT * FROM EcommerceSyncErrors

SELECT * FROM EcommerceSyncErrors WHERE AppId='123456'

Columns


Name	Type	References	Description
PortalId	Integer		The Id of the portal for this sync error.
ObjectType	String		The specific object type.
IntegratorObjectId	String		The ID, from your system, of the object that is being created or updated.
ChangeOccurredTimestamp	Datetime		The timestamp of the last change occurred.
ErrorTimestamp	Datetime		The timestamp of the error when occurred.
Type	String		The type of the error which classifies the error.
Details	String		Detailed description of the error.
Status	String		Status of the sync error.
AppId	String		The app ID in which the error occurred.

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'.

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'.

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.

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.

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'.

FormFields

Get fields of HubSpot Forms.

Table Specific Information

FormFields represent the fields contained in a form on your website.

Select

When selecting form fields, they can only be filtered by FormGUID. For example:

SELECT * FROM FormFields WHERE FormGUID='123456789'

SELECT * FROM FormFields WHERE FormGUID IN ('123456789', '121314515')

Columns


Name	Type	References	Description
FormGUID	String		The unique key for the form.
Name	String		The name of the field.
DefaultValue	String		The default value of the field.
Required	Boolean		Boolean indicating wether the field is required in the form.
Enabled	Boolean		Boolean indicating wether the field is enabled.
IsSmartField	Boolean		Boolean indicating wether this is a smart field.
Label	String		Label of the field.
GroupName	String		Name of the group the field belongs to.
SelectedOptions	String		The selected options for the field.
FieldType	String		Type of the field.
Hidden	Boolean		Boolean indicating wether the field is hidden.
Validation	String
Type	String		Value type of the field.
Options	String		Field options
DisplayOrder	Int		Display order of the field

FormSubmissions

Get the submissions for the specified form.

Table Specific Information

Select

When selecting form submissions, they can only be filtered by FormGUID. For example:

SELECT * FROM FormSubmissions WHERE FormGUID='123456789'

SELECT * FROM FormSubmissions WHERE FormGUID IN ('123456789', '121314515')

Columns


Name	Type	References	Description
FormGUID	String		The unique key for the form.
PageUrl	String		Url of the page.
SubmittedAt	Datetime		Submitted time.
Name	String		Name of the submission.
Value	String		Value of the submission.

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.

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'.

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


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.

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'.

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'.

Matillion ETL Data Model for HubSpotVersion - 21.0.8137.0

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Authentication on Headless Machines

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Matillion ETL Data Model for HubSpot
Version - 21.0.8137.0