Connection String OptionsBack To Top

AuthScheme
ChannelId
ContentOwnerId
InitiateOAuth
OAuthClientId
OAuthClientSecret
OAuthAccessToken
OAuthSettingsLocation
OAuthVerifier
OAuthRefreshToken
OAuthExpiresIn
OAuthTokenTimestamp
OAuthJWTCert
OAuthJWTCertType
OAuthJWTCertPassword
OAuthJWTCertSubject
OAuthJWTIssuer
OAuthJWTSubject
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
BatchSize
ConnectionLifeTime
ConnectOnOpen
DefaultFilter
MaxRows
Other
PoolIdleTimeout
PoolMaxSize
PoolMinSize
PoolWaitTime
PseudoColumns
Readonly
RTK
SupportEnhancedSQL
Timeout
UseConnectionPooling

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 this to perform OAuth authentication using a standard user account.
OAuthJWT: Set this to perform OAuth authentication using an OAuth service account.

The Id of a channel belonging to the authenticated user. You can find this on the advanced account settings page for your channel. If not specified, MINE will be used; this returns data for the authenticated user's YouTube channel.

ContentOwnerId

Data Type

string

Default Value

Remarks

The Id of the copyright holder for content in YouTube's rights management system. The content owner is the person or organization that claims videos and sets their monetization policy.

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

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.

OAuthJWTCert

Data Type

string

Default Value

Remarks

The name of the certificate store for the client certificate.

The OAuthJWTCertType field specifies the type of the certificate store specified by OAuthJWTCert. If the store is password protected, specify the password in OAuthJWTCertPassword.

OAuthJWTCert is used in conjunction with the OAuthJWTCertSubject field in order to specify client certificates. If OAuthJWTCert has a value, and OAuthJWTCertSubject is set, a search for a certificate is initiated. Please refer to the OAuthJWTCertSubject field for details.

Designations of certificate stores are platform-dependent.

The following are designations of the most common User and Machine certificate stores in Windows:


MY	A certificate store holding personal certificates with their associated private keys.
CA	Certifying authority certificates.
ROOT	Root certificates.
SPC	Software publisher certificates.

In Java, the certificate store normally is a file containing certificates and optional private keys.

When the certificate store type is PFXFile, this property must be set to the name of the file. When the type is PFXBlob, the property must be set to the binary contents of a PFX file (i.e. PKCS12 certificate store).

OAuthJWTCertType

Data Type

string

Default Value

"USER"

Remarks

This property can take one of the following values:


USER	For Windows, this specifies that the certificate store is a certificate store owned by the current user. Note: This store type is not available in Java.
MACHINE	For Windows, this specifies that the certificate store is a machine store. Note: this store type is not available in Java.
PFXFILE	The certificate store is the name of a PFX (PKCS12) file containing certificates.
PFXBLOB	The certificate store is a string (base-64-encoded) representing a certificate store in PFX (PKCS12) format.
JKSFILE	The certificate store is the name of a Java key store (JKS) file containing certificates. Note: this store type is only available in Java.
JKSBLOB	The certificate store is a string (base-64-encoded) representing a certificate store in Java key store (JKS) format. Note: this store type is only available in Java.
PEMKEY_FILE	The certificate store is the name of a PEM-encoded file that contains a private key and an optional certificate.
PEMKEY_BLOB	The certificate store is a string (base64-encoded) that contains a private key and an optional certificate.
PUBLIC_KEY_FILE	The certificate store is the name of a file that contains a PEM- or DER-encoded public key certificate.
PUBLIC_KEY_BLOB	The certificate store is a string (base-64-encoded) that contains a PEM- or DER-encoded public key certificate.
SSHPUBLIC_KEY_FILE	The certificate store is the name of a file that contains an SSH-style public key.
SSHPUBLIC_KEY_BLOB	The certificate store is a string (base-64-encoded) that contains an SSH-style public key.
P7BFILE	The certificate store is the name of a PKCS7 file containing certificates.
PPKFILE	The certificate store is the name of a file that contains a PPK (PuTTY Private Key).
XMLFILE	The certificate store is the name of a file that contains a certificate in XML format.
XMLBLOB	The certificate store is a string that contains a certificate in XML format.
GOOGLEJSON	The certificate store is the name of a JSON file containing the service account information. Only valid when connecting to a Google service.
GOOGLEJSONBLOB	The certificate store is a string that contains the service account JSON. Only valid when connecting to a Google service.

OAuthJWTCertPassword

Data Type

string

Default Value

Remarks

If the certificate store is of a type that requires a password, this property is used to specify that password in order to open the certificate store.

This is not required when using the GOOGLEJSON OAuthJWTCertType. Google JSON keys are not encrypted.

OAuthJWTCertSubject

Data Type

string

Default Value

"*"

Remarks

When loading a certificate the subject is used to locate the certificate in the store.

If an exact match is not found, the store is searched for subjects containing the value of the property.

If a match is still not found, the property is set to an empty string, and no certificate is selected.

The special value "*" picks the first certificate in the certificate store.

The certificate subject is a comma separated list of distinguished name fields and values. For instance "CN=www.server.com, OU=test, C=US, E=support@cdata.com". Common fields and their meanings are displayed below.


Field	Meaning
CN	Common Name. This is commonly a host name like www.server.com.
O	Organization
OU	Organizational Unit
L	Locality
S	State
C	Country
E	Email Address

If a field value contains a comma it must be quoted.

OAuthJWTIssuer

Data Type

string

Default Value

Remarks

The issuer of the Java Web Token. This is typically either the Client Id or Email Address of the OAuth Application.

This is not required when using the GOOGLEJSON OAuthJWTCertType. Google JSON keys contain a copy of the issuer account.

OAuthJWTSubject

Data Type

string

Default Value

Remarks

The user subject for which the application is requesting delegated access. Typically, the user account name or email address.

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 YouTube Analytics 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\\YouTubeAnalytics 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.

If left unspecified, the default location is "%APPDATA%\\CData\\YouTubeAnalytics 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.

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:youtubeanalytics:CacheLocation='c:/Temp/cachedir';InitiateOAuth=GETANDREFRESH;ContentOwnerId=MyContentOwnerId;ChannelId=ChannelId;

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:youtubeanalytics:CacheDriver=org.apache.derby.jdbc.EmbeddedDriver;CacheConnection='jdbc:derby:memory';InitiateOAuth=GETANDREFRESH;ContentOwnerId=MyContentOwnerId;ChannelId=ChannelId;

SQLite

The following is a JDBC URL for the SQLite JDBC driver:

jdbc:youtubeanalytics:CacheDriver=org.sqlite.JDBC;CacheConnection='jdbc:sqlite:C:/Temp/sqlite.db';InitiateOAuth=GETANDREFRESH;ContentOwnerId=MyContentOwnerId;ChannelId=ChannelId;

MySQL

The following is a JDBC URL for the included CData JDBC Driver for MySQL:

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

SQL Server

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

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

Oracle

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

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

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:youtubeanalytics:CacheDriver=cdata.jdbc.postgresql.PostgreSQLDriver;CacheConnection='jdbc:postgresql:User=postgres;Password=admin;Database=postgres;Server=localhost;Port=5432;';InitiateOAuth=GETANDREFRESH;ContentOwnerId=MyContentOwnerId;ChannelId=ChannelId;

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\\YouTubeAnalytics 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\\YouTubeAnalytics 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 YouTube Analytics 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 CACHE Statements to cache explicitly.

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.

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

DefaultFilter

Data Type

string

Default Value

"EndDate=Today"

Remarks

A default filter to be applied to all queries. Convenient for setting default Dimensions, StartDate, EndDate, and Segments for all queries. These values will be overridden if set in the query. Example value: Dimensions=Year AND EndDate=Today

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.

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.

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

Use QueryCache to cache memory for a given time in seconds or to disable in-memory caching.

When SupportEnhancedSQL = true, the driver offloads as much of the SELECT statement processing as possible to YouTube Analytics 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 YouTube Analytics 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.

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.

GroupItems

Create, delete, and query Items that compose a Group.

Table-Specific Information

Select

The following query returns the items that compose the YouTube Analytics groups:

SELECT * FROM GroupItems

The following query returns the items that compose a specific YouTube Analytics group:

SELECT * FROM GroupItems WHERE GroupId='239_65GrkKS6kRL'

Additionally, OnBehalfOfContentOwner can optionally be used in the WHERE clause.

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	True	The Id that YouTube uses to uniquely identify the channel, video, playlist, or asset that is included in the group. Note that this Id refers specifically to the inclusion of that entity in a particular group and is different than the channel Id, video Id, playlist Id, or asset Id that uniquely identifies the entity itself.
Kind	String	False	Identifies the type of the group item. The value will be youtube#groupItem.
Etag	String	False	The Etag of this item.
GroupId	String	False	The Id that YouTube uses to uniquely identify the group that contains the item.
Resource_Kind	String	False	The type of the group item. The allowed values are youtube#channel, youtube#playlist, youtube#video, youtubePartner#asset.
Resource_Id	String	False	The channel, video, playlist, or asset Id that YouTube uses to uniquely identify the group item.
OnBehalfOfContentOwner	String	False	This parameter is intended exclusively for YouTube content partners that own and manage many different YouTube channels. The OnBehalfOfContentOwner parameter indicates that the authorization credentials in the request identify a YouTube user who is acting on behalf of the content owner specified in the parameter value.

Groups

Create, update, delete, and query YouTube Analytics Groups.

Table-Specific Information

A Group is a custom collection of up to 200 channels, videos, playlists, or assets. All of the Items in a Group must represent the same type of entity. For example, you cannot create a Group that contains 100 videos and 100 playlists. An Analytics Group can only contain entities that you have uploaded or claimed or that are linked to a channel that you administer. As a result, content owners can create groups of videos, playlists, channels, or assets. Channel owners can create groups of videos and playlists.

Select

The following columns can be used in the WHERE clause: OnBehalfOfContentOwner and Mine.

Columns


Name	Type	ReadOnly	Description
Id [KEY]	String	False	The Id that YouTube uses to uniquely identify the group.
Kind	String	False	Identifies the type of the YouTube Analytics entity. The value will be youtube#group.
Etag	String	False	The Etag of this resource.
Snippet_PublishedAt	DateTime	True	The date and time that the group was created.
Snippet_Title	String	False	The group name. The value must be a nonempty string.
ContentDetails_ItemCount	String	False	The number of items in the group.
ContentDetails_ItemType	String	False	The type of entities that the group contains. The allowed values are youtube#channel, youtube#playlist, youtube#video, youtubePartner#asset.
Mine	String	False	Set this to true to retrieve all groups owned by the authenticated user.
PageToken	String	False	The PageToken parameter identifies a specific page in the result set that should be returned.
OnBehalfOfContentOwner	String	False	This parameter is intended exclusively for YouTube content partners that own and manage many different YouTube channels. The OnBehalfOfContentOwner parameter indicates that the authorization credentials in the request identify a YouTube user who is acting on behalf of the content owner specified in the parameter value.

ViewsBack To Top

Activities
AdPerformanceReports
Captions
Channels
ChannelSections
Comments
CommentThreads
Demographics
Devices
EngagementReports
Languages
PlayListItems
PlayLists
Regions
Subscriptions
TimeBasedReports
TopVideos
TrafficSources
VideoAbuseReportReasons
VideoCategories
Videos

Activities

Query YouTube Activities. An Activity resource contains information about an action that a particular Channel, or User, has taken on YouTube.

Table Specific Information

Select

YouTube Analytics allows only a subset of columns to be used as filter criteria. You can specify exactly one of the following columns: ChannelId or Home. Additionally, extra optional columns supported are PublishedAt and Region.

All columns support the '=' operator. PublishedAt also supports '>', '>=', '<', and '<='.

By default, if no filters are specified, the driver will return activities generated by authenticated user.

SELECT * FROM Activities WHERE Home = true LIMIT 2



SELECT * FROM Activities WHERE ChannelId = "abc123" AND PublishedAt < '2016-01-01T10:00:00Z'

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The Id that YouTube uses to uniquely identify the activity.
PublishedAt	Datetime				The date and time that the activity occurred. The value is in ISO 8601 (YYYY-MM-DDThh:mm:ss.sZ) format.
ChannelId	String				The Id that YouTube uses to uniquely identify the channel associated with the activity.
Title	String				The title of the resource primarily associated with the activity.
Description	String				The description of the resource primarily associated with the activity.
ChannelTitle	String				Channel title for the channel responsible for this activity.
Type	String				The type of activity that the resource describes. Valid values: channelItem, comment (not currently returned), favorite, like, playlistItem, recommendation, social, subscription, and upload.
GroupId	String				The group Id associated with the activity.
ThumbnailsDefaultUrl	String				The URL of the default image thumbnail.
UploadVideoId	String				The Id that YouTube uses to uniquely identify the uploaded video.
LikeResourceIdKind	String				The type of the resource that received a positive (like) rating.
LikeResourceIdVideoId	String				The Id that YouTube uses to uniquely identify the video, if the rated resource is a video. This column is only present if the ResourceIdKind is youtube#video.
FavoriteResourceIdKind	String				The type of the YouTube entity marked as a favorite.
FavoriteResourceIdVideoId	String				The Id that YouTube uses to uniquely identify the favorite video.
CommentResourceIdKind	String				The comment on the API resource.
CommentResourceIdVideoId	String				The Id that YouTube uses to uniquely identify the video associated with a comment.
CommentResourceIdChannelId	String				The Id that YouTube uses to uniquely identify the channel associated with a comment.
SubscriptionResourceIdKind	String				The type of the subscription. A subscription is a channel that a user subscribed to.
SubscriptionResourceIdChannelId	String				The Id that YouTube uses to uniquely identify the channel that the user subscribed to.
PlaylistItemResourceIdKind	String				The type of the playlist item.
PlaylistItemResourceIdVideoId	String				The Id that YouTube uses to uniquely identify the video that was added to the playlist.
PlaylistItemPlaylistId	String				The value that YouTube uses to uniquely identify the playlist.
PlaylistItemPlaylistItemId	String				The value that YouTube uses to uniquely identify the item in the playlist.
RecommendationResourceIdKind	String				The type of the recommendation.
RecommendationResourceIdVideoId	String				The Id that YouTube uses to uniquely identify the video, if the recommended resource is a video.
RecommendationResourceIdChannelId	String				The Id that YouTube uses to uniquely identify the channel, if the recommended resource is a channel.
RecommendationReason	String				The reason that the YouTube entity was recommended to the user. Valid values for this column : unspecified, videoFavorited, videoLiked, and videoWatched.
RecommendationSeedResourceIdKind	String				The type of the YouTube entity that caused the recommendation.
RecommendationSeedResourceIdVideoId	String				The Id that YouTube uses to uniquely identify the video, if the recommendation was caused by a particular video.
RecommendationSeedResourceIdChannelId	String				The Id that YouTube uses to uniquely identify the channel, if the recommendation was caused by a particular channel.
RecommendationSeedResourceIdPlaylistId	String				The Id that YouTube uses to uniquely identify the playlist, if the recommendation was caused by a particular playlist.
SocialType	String				The name of the social network post. Valid values for this column: facebook, googlePlus, twitter, and unspecified.
SocialResourceIdKind	String				The type of the social network post.
SocialResourceIdVideoId	String				The Id that YouTube uses to uniquely identify the video featured in a social network post, if the post refers to a video.
SocialResourceIdChannelId	String				The Id that YouTube uses to uniquely identify the channel featured in a social network post, if the post refers to a channel.
SocialResourceIdPlaylistId	String				The Id that YouTube uses to uniquely identify the playlist featured in a social network post.
SocialAuthor	String				The author of the social network post.
SocialReferenceUrl	String				The URL of the social network post.
SocialImageUrl	String				An image of the author of the post.
ChannelItemResourceId	String				The Id of the YouTube entity that was added to the channel.

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.


Name	Type	Description
Region	String	Instructs the API to filter by region. The parameter value is an ISO 3166-1 alpha-2 country code.

AdPerformanceReports

Query Ad Performance Reports. Ad Performance Reports provide impression-based metrics for ads that ran during video playbacks. These metrics account for each ad impression, and each video playback can yield multiple impressions.

Table Specific Information

Select

Get GrossRevenue for a video in a specific country grouped by ad type and day:

SELECT Country, AdType, Day, GrossRevenue, StartDate, EndDate FROM AdPerformanceReports WHERE video='vid123' AND continent = '019'

Select Rules

Ad performance reports for content owners:


Dimensions	Required	adType
	Optional	day
Metrics	Use 1 or more	grossRevenue,adImpressions,cpm
Filter	Use exactly one	video, group, or a supported combination of uploaderType and claimedStatus
	Use 0 or one	country,continent,subContinent

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Video	String	True			The Id of a YouTube video.
Group	String	True			(filter only) The Id of a YouTube Analytics group.
Country	String				The country associated with the metrics in the report row. The dimension value is a two-letter ISO-3166-1 country code, such as US, CN (China), or FR (France). The country code ZZ is used to report metrics for which YouTube could not identify the associated country.
Continent	String				(filter only) A United Nations (U.N.) statistical region code. The allowed values are 002 Africa, 019 Americas (Northern America, Latin America, South America, and the Caribbean), 142 Asia, 150 Europe, 009 Oceania. This dimension can only be used to filter data.
SubContinent	String				(filter only) A U.N. statistical region code that identifies a geographical subregion.
AdType	String	True			The AdType dimension is used in ad performance reports and aggregates the requested metrics based on the types of ads that ran during video playbacks: auctionDisplay, auctionInstream, auctionTrueviewInslate, auctionTrueviewInstream, auctionUnknown, reservedClickToPlay, reservedDisplay, reservedInstream, reservedInstreamSelect, reservedMasthead, reservedUnknown.
Day	String	True			When you use this dimension, data in the report is aggregated on a daily basis and each row contains data for one day.
ClaimedStatus	String				(only used in content owner reports) This dimension lets you indicate that a result set should only contain metrics for claimed content. The allowed value is claimed.
UploaderType	String				(only used in content owner reports) This dimension lets you indicate whether a result set should contain metrics for content uploaded by the specified content owner or content uploaded by third parties, such as user-uploaded videos. The allowed values are self and thirdParty.
GrossRevenue	Decimal		False		The estimated gross revenue, in USD, from all Google-sold or DoubleClick-partner-sold advertising for the selected date range and region. Gross revenue is subject to month-end adjustment and does not include partner-served advertising. Gross revenue should not be confused with earnings, or net revenue, which factors in your share of ownership and revenue-sharing agreements.
Cpm	Decimal		False		The estimated gross revenue per thousand ad impressions.
AdImpressions	Double		False		The number of verified ad impressions served.
StartDate	Date				Start date for fetching Analytics data.
EndDate	Date				End date for fetching Analytics data.

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.


Name	Type	Description
Segments	String	Segments the data returned for your request. Either a SegmentId or a custom segment.

Captions

Query YouTube Captions. A Caption resource represents a YouTube caption track.

Table Specific Information

Select

YouTube Analytics allows only a subset of columns to be used as filter criteria. The VideoId column must be specified. Extra optional columns supported are Id and OnBehalfOfContentOwner.

All columns support the '=' operator.

SELECT * FROM Captions WHERE VideoId = '123456789'

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The Id that YouTube uses to uniquely identify the caption track.
VideoId	String				The Id that YouTube uses to uniquely identify the video associated with the caption track.
LastUpdated	Datetime				The date and time when the caption track was last updated. The value is specified in ISO 8601 (YYYY-MM-DDThh:mm:ss.sZ) format.
TrackKind	String				The type of the caption track. Valid values for this column: ASR (a caption track generated using automatic speech recognition); Forced (a caption track that plays when no other track is selected in the player. For example, a video that shows aliens speaking in an alien language might have a forced caption track to only show subtitles for the alien language); standard (a regular caption track. This is the default value).
Language	String				The language of the caption track. The column value is a BCP-47 language tag.
Name	String				The name of the caption track. The name is intended to be visible to the user as an option during playback.
AudioTrackType	String				The type of audio track associated with the caption track. Valid values for this column: commentary (the caption track corresponds to an alternate audio track that includes commentary, such as director commentary); descriptive (the caption track corresponds to an alternate audio track that includes additional descriptive audio); primary (the caption track corresponds to the primary audio track for the video, which is the audio track normally associated with the video); unknown (this is the default value).
IsCC	Boolean				Indicates whether the track contains closed captions for the deaf and hard of hearing. The default value is false.
IsLarge	Boolean				Indicates whether the caption track uses large text for the vision-impaired. The default value is false.
IsEasyReader	Boolean				Indicates whether the caption track is formatted for easy reader, meaning it is at a third-grade level for language learners. The default value is false.
IsDraft	Boolean				Indicates whether the caption track is a draft. If the value is true, then the track is not publicly visible. The default value is false.
IsAutoSynced	Boolean				Indicates whether YouTube synchronized the caption track to the audio track in the video. The value will be true if a sync was explicitly requested when the caption track was uploaded. If the value is false, YouTube uses the time codes in the uploaded caption track to determine when to display captions.
Status	String				The status of the caption track. Valid values for this column: failed, serving, syncing.
FailureReason	String				The reason that YouTube failed to process the caption track. Valid values: processingFailed (YouTube failed to process the uploaded caption track); unknownFormat (the format of the caption track was not recognized); unsupportedFormat (the format of the caption track is not supported).

Channels

Query YouTube Channels.

Table Specific Information

Select

By default, SupportEnhancedSQL is set to true, and the following will be passed to YouTube Analytics if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

By default, if no filters are specified, the driver will return all channels owned by the authenticated user.

YouTube Analytics allows only a subset of columns to be used as filter criteria. The channels can be filtered by the Id. Extra optional columns supported are Language and ChannelId.

A search against all channels in YouTube Analytics may be triggered by specifying one or more of SearchTerms, ChannelId, Region, SafeSearch, Rating, or Relevance to search all channels.

All columns support the '=' operator. 'PublishedAt', also supports '>', '>=', '<', and '<='.

Order by is supported for the Rating, Relevance, and Title columns.

SELECT * FROM Channels WHERE Id = 'abc123'



SELECT * FROM Channels WHERE ChannelId = 'abc123' AND SearchTerms = 'music|movies-horror' ORDER BY Rating LIMIT 10

Note: Ordering is not supported when specifying one of the colums used for searching channels such as SearchTerms.

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The Id that YouTube uses to uniquely identify the channel.
Title	String				The title of the channel.
Description	String				The description of the channel. The value of the column has a maximum length of 1000 characters.
CustomUrl	String				The channel's custom URL.
PublishedAt	Datetime				The date and time that the channel was created. The value is specified in ISO 8601 (YYYY-MM-DDThh:mm:ss.sZ) format.
ThumbnailsDefaultUrl	String				The URL of the default image thumbnail.
DefaultLanguage	String				This column is confidential to trusted testers.
LiveBroadcastContent	String				An indication of whether a video or channel resource has live broadcast content.
LocalizedTitle	String				This column is confidential to trusted testers. The localized video title.
LocalizedDescription	String				This column is confidential to trusted testers. The localized video description.
Country	String				The country with which the channel is associated.
ViewCount	Long				The number of times the channel has been viewed.
SubscriberCount	Long				The number of subscribers that the channel has. This value is rounded down to three significant figures.
HiddenSubscriberCount	Boolean				Indicates whether the subscriber count of the channel is publicly visible.
VideoCount	Long				The number of videos uploaded to the channel.
RelatedPlaylistsLikes	String				The Id of the playlist that contains the liked videos of the channel.
RelatedPlaylistsUploads	String				The Id of the playlist that contains the uploaded videos of the channel.
PrivacyStatus	String				Privacy status of the channel. Valid values for this column: private, public, unlisted.
IsLinked	Boolean				Indicates whether the channel data identifies a user who is already linked to either a YouTube username or a Google+ account. A user who has one of these links already has a public YouTube identity, which is a prerequisite for several actions, such as uploading videos.
LongUploadsStatus	String				Indicates whether the channel is eligible to upload videos that are more than 15 minutes long. This column is only returned if the channel owner authorized the API request. See the YouTube Help Center for more information about this feature.
MadeForKids	Boolean				This value indicates whether the channel is designated as child-directed, and it contains the current 'made for kids' status of the channel.
Keywords	String				Keywords associated with your channel. The value is a space-separated list of strings.
TrackingAnalyticsAccountId	String				The Id for a Google Analytics account that you want to use to track and measure traffic to your channel.
ModerateComments	Boolean				This setting determines whether user-submitted comments left on the channel page need to be approved by the channel owner to be publicly visible. The default value is false.
SearchTerms	String				The SearchTerms parameter specifies the query term to search for.

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.


Name	Type	Description
ForUsername	String	The username associated with the channel.
Language	String	Retrieve localized resource metadata for a specific application language that the YouTube website supports.
Language	String	Retrieve localized resource metadata for a specific application language that the YouTube website supports.
ManagedByMe	String	This parameter is intended exclusively for YouTube content partners. Set this parameter's value to true to instruct the API to only return channels managed by the content owner that the onBehalfOfContentOwner parameter specifies. The user must be authenticated as a CMS account linked to the specified content owner and onBehalfOfContentOwner must be provided.
OnBehalfOfContentOwner	String	This parameter is intended exclusively for YouTube content partners. The onBehalfOfContentOwner parameter indicates that the request's authorization credentials identify a YouTube CMS user who is acting on behalf of the content owner specified in the parameter value. This parameter is intended for YouTube content partners that own and manage many different YouTube channels. It allows content owners to authenticate once and get access to all their video and channel data, without having to provide authentication credentials for each individual channel. The CMS account that the user authenticates with must be linked to the specified YouTube content owner.
ChannelId	String	The Id that YouTube uses to uniquely identify a channel.
Region	String	The country associated with the channel.
SafeSearch	String	Indicates whether the search results should include restricted content as well as standard content. Acceptable values: moderate, none, or strict.
Rating	String	Resources are sorted from highest to lowest rating.
Relevance	String	Resources are sorted based on their relevance to the search query. This is the default value for this parameter.

ChannelSections

Query YouTube ChannelSections. A ChannelSection contains information about a set of Videos that a Channel has chosen to feature.

Table Specific Information

Select

YouTube Analytics allows only a subset of columns to be used as filter criteria. You can specify exactly one of the following columns: ChannelId or Id. Extra optional columns supported are Language and OnBehalfOfContentOwner.

All columns support the '=' operator.

By default, if no channel id is specified, the driver will return channel sections for the channel owned by authenticated user.

SELECT * FROM ChannelSections WHERE ChannelId = '123456789'

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The Id that YouTube uses to uniquely identify the channel section.
Type	String				The type of the channel section.
ChannelId	String				The Id that YouTube uses to uniquely identify the channel.
Title	String				The title of the section.
Position	Integer				The position of the section on the channel page.
PlaylistsAggregate	String				A list of one or more playlist Ids that are featured in a channel section. You must specify a list of playlist Ids if the type of the ChannelSection is either singlePlaylist or multiplePlaylists, and this column should not be specified for other types of sections. If the type is singlePlaylist, this list must specify exactly one playlist Id.
ChannelsAggregate	String				A list of one or more channel Ids that are featured in a channel section. You must specify a list of channel Ids if the Type of the ChannelSection is multipleChannels, and this column should not be specified for other types of sections. You cannot include your own channel in the list.

Pseudo-Columns

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


Name	Type	Description
Language	String	Retrieve localized resource metadata for a specific application language that the YouTube website supports.
OnBehalfOfContentOwner	String	This parameter is intended exclusively for YouTube content partners.

Comments

Query YouTube Comments.

Table Specific Information

Select

YouTube Analytics allows only a subset of columns to be used as filter criteria. You must specify exactly one of the following columns: Id or ParentId. Extra optional columns supported are TextFormat.

All columns support the '=' operator.

SELECT * FROM Comments WHERE Id = '123456789'

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The Id that YouTube uses to uniquely identify the comment.
ChannelId	String				The Id of the YouTube channel associated with the comment. If the comment is a video comment, then this column identifies the channel of the video, and the VideoId column identifies the video. If the comment is a channel comment, then this column identifies the channel that the comment is about.
VideoId	String				The Id of the video that the comment refers to.
TextDisplay	String				The text of the comment. The text can be retrieved in either plaintext or HTML. The TextFormat pseudo column specifies the desired text format.
TextOriginal	String				The original, raw text of the comment as it was initially posted or last updated. The original text is only returned if it is accessible to the authenticated user, which is only guaranteed if the user is the author of the comment.
ParentId	String				The unique Id of the parent comment. This column value is only set if the comment was submitted as a reply to another comment.
AuthorDisplayName	String				The display name of the user who posted the comment.
AuthorProfileImageUrl	String				The URL for the avatar of the user who posted the comment.
AuthorChannelUrl	String				The URL of the YouTube channel of the comment author, if available.
AuthorChannelIdValue	String				The Id of the YouTube channel of the comment author, if available.
AuthorGoogleplusProfileUrl	String				The URL of the Google+ profile of the comment author, if available.
CanRate	Boolean				This setting indicates whether the current viewer can rate the comment.
ViewerRating	String				The rating the viewer has given to this comment. Valid values for this column: like, none. Note that this column does not currently identify dislike ratings. The column value is like if the viewer has rated the comment positively. The value is none in all other cases, including the user having given the comment a negative rating or not having rated the comment.
LikeCount	Integer				The total number of likes (positive ratings) the comment has received.
ModerationStatus	String				The moderation status of the comment. This value is only returned if the API request was authorized by the owner of the channel or the video on which the requested comments were made. In addition, note that this value is not set if the query filtered on the Id column. Valid values: heldForReview, likelySpam, published, rejected.
PublishedAt	Datetime				The date and time when the comment was orignally published. The value is specified in ISO 8601 (YYYY-MM-DDThh:mm:ss.sZ) format.
UpdatedAt	Datetime				The date and time when the comment was last updated. The value is specified in ISO 8601 (YYYY-MM-DDThh:mm:ss.sZ) format.

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.


Name	Type	Description
TextFormat	String	This parameter indicates whether the API should return comments formatted as HTML or as plaintext.

CommentThreads

Query YouTube CommentThreads. A CommentThread record contains information about a YouTube comment thread, a top-level comment and replies, if any exist, to that comment.

Table Specific Information

Select

YouTube Analytics allows only a subset of columns to be used as filter criteria. You must specify exactly one of the following columns: AllThreadsRelatedToChannelId, ChannelId, Id, or VideoId.

Extra optional columns supported are ModerationStatus, SearchTerms, and TextFormat. All columns support the '=' operator.

Order is supported for CommentThreads. Relevance are the supported order columns.

SELECT * FROM CommentThreads WHERE VideoId = 'abcdef123456' ORDER BY Relevance DESC LIMIT 10

Note: Ordering is not supported for use in conjunction with the Id parameter.

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The Id that YouTube uses to uniquely identify the comment thread.
ChannelId	String				The YouTube channel that is associated with the comments in the thread. The VideoId column identifies the video. If the comments are about a video, then the value identifies the channel that uploaded the video. If the comments refer to the channel itself, the VideoId column will not have a value.
VideoId	String				The Id of the video that the comments refer to, if any. If this column is not present or does not have a value, then the thread applies to the channel and not to a specific video.
TopCommentId	String				The Id of the top-level comment of the thread.
PublishedAt	Datetime				The publish date of the top-level comment of the thread.
UpdatedAt	Datetime				The last update date of the top-level comment of the thread.
TopCommentChannelId	String				The Id of the channel associated with the top-level comment of the thread.
TopCommentVideoId	String				The Id of the video associated with the top-level comment of the thread.
TopCommentTextDisplay	String				The display text of the top-level comment of the thread.
TopCommentTextOriginal	String				The original text of the top-level comment of the thread.
TopCommentParentId	String				The Id of the parent of the top-level comment of the thread.
TopCommentAuthorDisplayName	String				The display name of the author of the top-level comment of the thread.
TopCommentAuthorChannelIdValue	String				The Id of the channel of the author of the top-level comment of the thread.
TopCommentCanRate	Boolean				Whether the current user can rate the top-level comment of the thread.
TopCommentViewerRating	String				The viewer rating of the top-level comment of the thread.
TopCommentLikeCount	Integer				The like count of the top-level comment of the thread.
TopCommentModerationStatus	String				The moderation status of the top-level comment of the thread.
CanReply	Boolean				This setting indicates whether the current viewer can reply to the thread.
TotalReplyCount	Integer				The total number of replies that have been submitted in response to the top-level comment.
IsPublic	Boolean				This setting indicates whether the thread, including all of its comments and comment replies, is visible to all YouTube users.
CommentsAggregate	String				A list of one or more replies to the top-level comment.

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.


Name	Type	Description
AllThreadsRelatedToChannelId	String	Instructs the API to return all comment threads associated with the specified channel.
ModerationStatus	String	Set this parameter to limit the returned comment threads to a particular moderation state.
TextFormat	String	Set the value of this parameter to HTML or plainText.

Demographics

Query YouTubeAnalytics Demographics grouped by age groups and gender.

Table Specific Information

Select

Aggregate viewing statistics for videos based on viewers' age group and gender:

SELECT * FROM Demographics

Trim down Demographics for just one Video:

SELECT * FROM Demographics WHERE Video = 'video_Id'

Aggregate viewing statistics for playlists based on viewers' age group and gender:

SELECT * FROM Demographics WHERE IsCurated=true

Select Rules

You can use the following tables to build other queries to Top Videos.

Demographics for videos:


Dimensions	Use 1 or more	ageGroup,gender,video
	Use 0 or more	liveOrOnDemand,subscribedStatus,youtubeProduct
Metrics	Use 1 or more	viewerPercentage
Filter	Use 0 or one	country,province,continent,subContinent
	Use 0 or one	video,group
	Use 0 or more	liveOrOnDemand,subscribedStatus,youtubeProduct

Demographics for playlists:


Dimensions	Use 1 or more	ageGroup,gender
	Use 0 or more	subscribedStatus,youtubeProduct
Metrics	Required	viewerPercentage
Filter	Required	isCurated=true
	Use 0 or one	country,province,continent,subContinent
	Use 0 or one	video,group
	Use 0 or more	subscribedStatus,youtubeProduct

Demographics for content owners:


Dimensions	Use 1 or more	ageGroup,gender
	Use 0 or more	liveOrOnDemand,subscribedStatus,youtubeProduct
Metrics	Use 1 or more	viewerPercentage
Filter	Use exactly one	video,channel,group
	Use 0 or one	country,province,continent,subContinent
	Use 0 or more	liveOrOnDemand,subscribedStatus,youtubeProduct

Demographics for content owner playlists:


Dimensions	Use 1 or more	ageGroup,gender
	Use 0 or more	subscribedStatus,youtubeProduct
Metrics	Required	viewerPercentage
Filter	Required	isCurated=true
	Use 0 or one	country,province,continent,subContinent
	Use 0 or one	playlist,channel,group
	Use 0 or more	subscribedStatus,youtubeProduct

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Video	String	True		False	The Id of a YouTube video.
Playlist	String	True			The Id of a YouTube playlist.
Group	String	True			(filter only) The Id of a YouTube Analytics group.
Country	String	True			The country associated with the metrics in the report row. The dimension value is a two-letter ISO-3166-1 country code, such as US, CN (China), or FR (France). The country code ZZ is used to report metrics for which YouTube could not identify the associated country.
Province	String	True			The U.S. state or territory associated with the metrics in the report row. The dimension value is an ISO 3166-2 code that identifies a U.S. state or the District of Columbia, such as US-MI (Michigan) or US-TX (Texas). The province code US-ZZ is used to report metrics for which YouTube could not identify the associated U.S. state. If the Province column is specified, Country must be set to US in the WHERE clause.
Continent	String	True			(filter only) A United Nations (U.N.) statistical region code. The API supports the following values: 002 Africa, 019 Americas (Northern America, Latin America, South America, and the Caribbean), 142 Asia, 150 Europe, 009 Oceania.
SubContinent	String	True			(filter only) A U.N. statistical region code that identifies a geographical subregion.
LiveOrOnDemand	String	True			This dimension indicates whether the user activity metrics in the data row are associated with views of a live broadcast. Data for this dimension is available for dates beginning April 1, 2014. The allowed values are LIVE, ON_DEMAND.
SubscribedStatus	String	True			This dimension indicates whether the user activity metrics in the data row are associated with viewers who were subscribed to the video or playlist channel. The allowed values are SUBSCRIBED and UNSUBSCRIBED. Note that the dimension value is accurate as of the time that the user activity occurs.
YoutubeProduct	String	True			This dimension identifies the YouTube service on which the user activity occurred. Data for this dimension is available as of July 18, 2015. The allowed values are CORE, GAMING, KIDS, UNKNOWN.
IsCurated	Boolean	True			This filter indicates that the request is retrieving data about video views that occurred in the context of a playlist. This filter is required for any playlist report and its value must be set to 1.
ClaimedStatus	String	True			(only used in content owner reports) This dimension lets you indicate that a result set will only contain metrics for claimed content. The allowed value is claimed.
UploaderType	String	True			(only used in content owner reports) This dimension lets you indicate whether a result set will contain metrics for content uploaded by the specified content owner or content uploaded by third parties, such as user-uploaded videos. The allowed values are self and thirdParty.
AgeGroup	String	True		True	This dimension identifies the age group of the logged-in users associated with the report data. The allowed values are age13-17, age18-24, age25-34, age35-44, age45-54, age55-64, age65-
Gender	String	True		False	This dimension identifies the gender of the logged-in users associated with the query. The allowed values are female and male.
ViewerPercentage	Double	False	True		The percentage of viewers who were logged in when watching the video or playlist. The viewerPercentage values in this report are not normalized for different values or combinations of values for playback detail dimensions (subscribedStatus and youtubeProduct). For example, a report that uses the subscribedStatus dimension returns viewerPercentage data adding up to 100 percent for subscribed views and viewerPercentage data adding up to 100 percent for unsubscribed views. (The total value of all viewerPercentage fields in the report is 200 percent.) You can use filters to ensure that the report only contains viewerPercentage data for one value (or combination of values) for playback detail dimensions.
StartDate	Date				Start date for fetching Analytics data.
EndDate	Date				End date for fetching Analytics data.

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.


Name	Type	Description
Segments	String	Segments the data returned for your request. Either a SegmentId or a custom segment.

Devices

Query YouTubeAnalytics Devices or Operating System statistics. This table allows you to aggregate viewing statistics based on the manner in which viewers reached your video content. For example, you can identify the number of views that occurred on mobile devices or game consoles.

Table Specific Information

Select

By default, grouping is done by Device Type:

SELECT * FROM Devices

However, you can group views by other dimensions such as Operating System:

SELECT OperatingSystem, Views, EstimatedMinutesWatched FROM Devices

Notice that you cannot mix both Device Type and Operating System in the same query due to limitations in the YouTube Analytics API.

Select Rules

You can use the following tables to build other queries to Devices:

Devices for videos:


Dimensions	Use exactly one	deviceType,operatingSystem
	Use 0 or more	day,liveOrOnDemand,subscribedStatus,youtubeProduct
Metrics	Use 1 or more	views,estimatedMinutesWatched
Filter	Use 0 or one	country,province,continent,subContinent
	Use 0 or one	video,group
	Use 0 or more	operatingSystem,liveOrOnDemand,subscribedStatus,youtubeProduct

Device data for playlists:


Dimensions	Use exactly one	deviceType,operatingSystem
	Use 0 or more	day,subscribedStatus,youtubeProduct
Metrics	Use 1 or more	views,estimatedMinutesWatched,playlistStarts,viewsPerPlaylistStart,averageTimeInPlaylist
Filter	Required	isCurated=true
	Use 0 or one	country,province,continent,subContinent
	Use 0 or one	playlist,channel,group
	Use 0 or more	operatingSystem,subscribedStatus,youtubeProduct

Device data for content owners:


Dimensions	Required	deviceType
	Use 0 or more	day,liveOrOnDemand,subscribedStatus,youtubeProduct
Metrics	Use 1 or more	views,estimatedMinutesWatched
Filter	Use exactly one	video,channel,group
	Use 0 or one	country,province,continent,subContinent
	Use 0 or more	operatingSystem,liveOrOnDemand,subscribedStatus,youtubeProduct

Device data for content owner playlists:


Dimensions	Required	deviceType
	Use 0 or more	day,subscribedStatus,youtubeProduct
Metrics	Use 1 or more	views,estimatedMinutesWatched,playlistStarts,viewsPerPlaylistStart,averageTimeInPlaylist
Filters	Required	isCurated=true
	Use 0 or one	country,province,continent,subContinent
	Use 0 or one	playlist,channel,group
	Use 0 or more	operatingSystem,subscribedStatus,youtubeProduct

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Video	String	True			The Id of a YouTube video.
Group	String	True			(filter only) The Id of a YouTube Analytics group.
Channel	String	True			(only used in content owner reports) The Id for a YouTube channel. The channel dimension is frequently used in content owner reports because those reports typically aggregate data for multiple channels.
Playlist	String	True			The Id of a YouTube playlist.
Country	String	True			The country associated with the metrics in the report row. The dimension value is a two-letter ISO-3166-1 country code, such as US, CN (China), or FR (France). The country code ZZ is used to report metrics for which YouTube could not identify the associated country.
Province	String	True			The U.S. state or territory associated with the metrics in the report row. The dimension value is an ISO 3166-2 code that identifies a U.S. state or the District of Columbia, such as US-MI (Michigan) or US-TX (Texas). The province code US-ZZ is used to report metrics for which YouTube could not identify the associated U.S. state. If the Province column is specified, Country must be set to US in the WHERE clause.
Continent	String	True			(filter only) A United Nations (U.N.) statistical region code: 002 Africa, 019 Americas (Northern America, Latin America, South America, and the Caribbean), 142 Asia, 150 Europe, 009 Oceania. This dimension can only be used to filter data.
SubContinent	String	True			(filter only) A U.N. statistical region code that identifies a geographical subregion.
Day	String	True			When you use this dimension, data in the report is aggregated on a daily basis and each row contains data for one day.
LiveOrOnDemand	String	True			This dimension indicates whether the user activity metrics in the data row are associated with views of a live broadcast. Data for this dimension is available for dates beginning April 1, 2014. The allowed values are LIVE, ON_DEMAND.
SubscribedStatus	String	True			This dimension indicates whether the user activity metrics in the data row are associated with viewers who were subscribed to the video or playlist channel. The allowed values are SUBSCRIBED, UNSUBSCRIBED. Note that the dimension value is accurate as of the time that the user activity occurs.
YoutubeProduct	String	True			This dimension identifies the YouTube service on which the user activity occurred. Data for this dimension is available as of July 18, 2015. The allowed values are CORE, GAMING, KIDS, UNKNOWN.
DeviceType	String	True		True	This dimension identifies the physical form factor of the device on which the view occurred. The allowed values are DESKTOP, GAME_CONSOLE, MOBILE, TABLET, TV, UNKNOWN_PLATFORM.
OperatingSystem	String	True		False	This dimension identifies the software system of the device on which the view occurred. The allowed values are ANDROID, BADA, BLACKBERRY, DOCOMO, HIPTOP, IOS, LINUX, MACINTOSH, MEEGO, NINTENDO_3DS, OTHER, PLAYSTATION, PLAYSTATION_VITA, SMART_TV, SYMBIAN, WEBOS.
IsCurated	Boolean	True			This filter indicates that the request is retrieving data about video views that occurred in the context of a playlist. This filter is required for any playlist report and its value must be set to 1.
ClaimedStatus	String	True			(only used in content owner reports) This dimension lets you indicate that the result set will only contain metrics for claimed content. The allowed value is claimed.
UploaderType	String	True			(only used in content owner reports) This dimension lets you indicate whether the result set will contain metrics for content uploaded by the specified content owner or content uploaded by third parties, such as user-uploaded videos. The allowed values are self and thirdParty.
Views	Double	False	True		The number of times that a video was viewed.
EstimatedMinutesWatched	Double	False	True		The number of minutes that users watched videos for the specified channel, content owner, video, or playlist.
PlaylistStarts	Double	False	False		The number of times that viewers initiated playback of a playlist.
ViewsPerPlaylistStart	Double	False	False		The average number of video views that occurred each time a playlist was initiated.
AverageTimeInPlaylist	Double	False	False		The estimated average amount of time, in minutes, that a viewer viewed videos in a playlist after the playlist was initiated.
StartDate	Date				Start date for fetching Analytics data.
EndDate	Date				End date for fetching Analytics data.

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.


Name	Type	Description
Segments	String	Segments the data returned for your request. Either a SegmentId or a custom segment.

EngagementReports

Query Engagement Reports info. For example, how many times a user shared a video.

Table Specific Information

Select

By default, results are grouped by Day. The following query returns Comments, Likes, Dislikes, etc., grouped by Day:

SELECT * FROM EngagementReports

Select Rules

The following query returns how frequently the channel's videos were shared on different social platforms:

SELECT SharingService, Shares FROM EngagementReports

You can use the following tables to build other queries to Engagement Reports:

Engagement Reports for videos:


Dimensions	Required	sharingService
	Optional	subscribedStatus
Metrics	Use 1 or more	shares
Filter	Use 0 or one	country,continent,subContinent
	Use 0 or one	video,group
	Optional	subscribedStatus

Engagement Reports for content owners:


Dimensions	Required	sharingService
	Optional	subscribedStatus
Metrics	Use 1 or more	shares
Filter	Use exactly one	video,channel,group
	Use 0 or one	country,continent,subContinent
	Optional	subscribedStatus

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
SharingService	String	True			This dimension identifies the service that was used to share videos. Videos can be shared on YouTube (or via the YouTube player) using the Share button.
Channel	String	True			(only used in content owner reports) The Id for a YouTube channel. The channel dimension is frequently used in content owner reports because those reports typically aggregate data for multiple channels.
Video	String	True			The Id of a YouTube video.
Group	String	True			(filter only) The Id of a YouTube Analytics group.
Country	String	True			The country associated with the metrics in the report row. The dimension value is a two-letter ISO-3166-1 country code, such as US, CN (China), or FR (France). The country code ZZ is used to report metrics for which YouTube could not identify the associated country.
Continent	String	True			(filter only) A United Nations (U.N.) statistical region code: 002 Africa, 019 Americas (Northern America, Latin America, South America, and the Caribbean), 142 Asia, 150 Europe, 009 Oceania. This dimension can only be used to filter data.
SubContinent	String	True			(filter only) A U.N. statistical region code that identifies a geographical subregion.
SubscribedStatus	String	True			This dimension indicates whether the user activity metrics in the data row are associated with viewers who were subscribed to the video or playlist channel. Possible values are SUBSCRIBED and UNSUBSCRIBED. Note that the dimension value is accurate as of the time that the user activity occurs.
ClaimedStatus	String	True			(only used in content owner reports) This dimension lets you indicate that a result set will only contain metrics for claimed content. The allowed value is claimed.
UploaderType	String	True			(only used in content owner reports) This dimension lets you indicate whether a result set will contain metrics for content uploaded by the specified content owner or content uploaded by third parties, such as user-uploaded videos. The allowed values are self and thirdParty.
Shares	Double	False	True		The number of times that users shared a video through the Share button.
StartDate	Date				Start date for fetching Analytics data.
EndDate	Date				End date for fetching Analytics data.

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.


Name	Type	Description
Segments	String	Segments the data returned for your request. Either a SegmentId or a custom segment.

Languages

Query YouTube i18nLanguages. An i18nLanguage resource identifies an application language that the YouTube website supports. The application language can also be referred to as a UI language.

Table Specific Information

Select

There are no required filters for this table. An optional column supported is Language.

All columns support the '=' operator.

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The Id that YouTube uses to uniquely identify the i18n language.
Language	String				A BCP-47 code that uniquely identifies a language.
Name	String				The name of the language as it is written in that language.

PlayListItems

Query YouTube PlayListItems. A PlayListItem resource identifies another YouTube entity, such as a Video, that is included in a PlayList. In addition, the PlayListItem record contains details about how that entity is used in that PlayList.

Table Specific Information

Select

You must specify exactly one of the following columns in the WHERE clause: Id or PlayListId. Extra optional columns supported are OnBehalfOfContentOwner and VideoId.

All columns support the '=' operator.

SELECT * FROM PlaylistItems WHERE PlaylistId = 'abcdef123456'

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The Id that YouTube uses to uniquely identify the playlist item.
PublishedAt	Datetime				The date and time that the item was added to the playlist. The value is specified in ISO 8601 (YYYY-MM-DDThh:mm:ss.sZ) format.
ChannelId	String				The Id that YouTube uses to uniquely identify the user that added the item to the playlist.
Title	String				The title of the item.
Description	String				The description of the item.
ThumbnailsDefaultUrl	String				The URL of the item.
ChannelTitle	String				The channel title of the channel that the playlist item belongs to.
VideoOwnerChannelTitle	String				The channel title of the channel that uploaded this video.
VideoOwnerChannelId	String				The channel ID of the channel that uploaded this video.
PlaylistId	String				The Id that YouTube uses to uniquely identify the playlist that the playlist item is in.
Position	String				The order in which the item appears in the playlist. The value uses a zero-based index, so the first item has a position of 0, the second item has a position of 1, and so forth.
ResourceIdKind	String				The kind, or type, of the referred resource.
ResourceIdVideoId	String				If the ResourceIdKind value of the column is youtube#video, then this column will be present and its value will contain the Id that YouTube uses to uniquely identify the video in the playlist.
VideoId	String				The Id that YouTube uses to uniquely identify a video. To retrieve the video resource, set the Id column to this value.
Note	String				A user-generated note for this item.
VideoOwnerPublishedAt	Datetime				The date and time that the video was published to YouTube. The value is specified in ISO 8601 (YYYY-MM-DDThh:mm:ss.sZ) format.
PrivacyStatus	String				The privacy status of the playlist item.

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.


Name	Type	Description
OnBehalfOfContentOwner	String	This parameter is intended exclusively for YouTube content partners.

PlayLists

Query YouTube PlayLists. A PlayList is a collection of videos that can be viewed sequentially and shared with other users.

Table Specific Information

Select

By default, if no filters are specified, the CData JDBC Driver for YouTube Analytics will return all playlists for the channel owned by authenticated user.

A search against all channels in YouTube Analytics may be triggered by specifying one or more of SearchTerms, ForContentOwner, Region, SafeSearch, Rating and Rating to search all playlists.

All columns support the '=' operator. PublishedAt also supports these additional operators: '>', '>=', '<', and '<='.

Order by is supported for the Rating, Relevance, and Title columns.

SELECT * FROM PlayLists WHERE Id = 'abcdef123456'



SELECT title FROM PlayLists WHERE PublishedAt >= '2016-01-01' ORDER BY Title LIMIT 10

Note: Ordering is not supported when specifying one of the colums used for searching playlists such as SearchTerms.

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The Id that YouTube uses to uniquely identify the playlist.
PublishedAt	Datetime				The date and time that the playlist was created. The value is specified in ISO 8601 (YYYY-MM-DDThh:mm:ss.sZ) format.
ChannelId	String				The Id that YouTube uses to uniquely identify the channel that published the playlist.
Title	String				The title of the playlist.
Description	String				The description of the playlist.
ThumbnailsDefaultUrl	String				The URL of the image.
ChannelTitle	String				Channel title of the channel that the video belongs to.
DefaultLanguage	String				This column is confidential to trusted testers. The language of the text in the Title and Description columns.
LocalizedTitle	String				This column is confidential to trusted testers. The localized playlist title.
LocalizedDescription	String				This column is confidential to trusted testers. The localized playlist description.
PrivacyStatus	String				The privacy status of the playlist. Valid values for this column: private, public, unlisted.
VideoCount	Integer				The number of videos in the playlist.
EmbedHtml	String				An iframe tag that embeds a player that will play the playlist.
SearchTerms	String				The SearchTerms parameter specifies the query term to search for.

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.


Name	Type	Description
Language	String	A value of hl instructs the API to retrieve localized resource metadata for a specific application language that the YouTube supports.
OnBehalfOfContentOwner	String	This parameter is intended exclusively for YouTube content partners.
OnBehalfOfContentOwnerChannel	String	Specifies the YouTube channel Id of the channel to which a video is being added.
ForContentOwner	String	Restricts the search to only retrieve playlists owned by the content owner specified by the OnBehalfOfContentOwner column.
Region	String	Instructs the API to return search results for the specified country.
SafeSearch	String	Indicates whether the search results should include restricted content as well as standard content. Valid values: moderate, none, or strict.
Rating	String	Specifies that playlists are sorted from highest to lowest rating.

Regions

Query YouTube i18nRegions. An i18nRegion resource identifies a geographic area that a YouTube user can select as the preferred content region. The content region can also be referred to as a content locale.

Table Specific Information

Select

There are no required filters for this table. An optional column supported is Language.

All columns support the '=' operator.

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The Id that YouTube uses to uniquely identify the i18n region.
Country	String				The two-letter ISO country code that identifies the region.
Name	String				The name of the region.

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.


Name	Type	Description
Language	String	The hl parameter specifies the language that should be used for text values in the API response. The default value is en_US.

Subscriptions

Query YouTube User Subscriptions. A Subscription notifies a User when new Videos are added to a Channel, or when another user takes one of several actions on YouTube, such as uploading a Video, rating a Video, or commenting on a Video.

Table Specific Information

Select

You can specify exactly one of the following columns: ChannelId, Id, MyRecentSubscribers, or MySubscribers.

Extra optional columns supported are ForChannelId, OnBehalfOfContentOwner, and OnBehalfOfContentOwnerChannel. All columns support the '=' operator.

Order is supported for Subscriptions. 'Alphabetical', 'Relevance', and 'Unread' are the supported order columns.

By default, if no filters are specified, the driver will return a list of channels subscribed by authenticated user.

SELECT Title FROM Subscriptions WHERE ChannelId = 'abc123' ORDER BY Unread DESC

The default value is SUBSCRIPTION_ORDER_RELEVANCE.

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The Id that YouTube uses to uniquely identify the subscription.
PublishedAt	Datetime				The date and time that the subscription was created. The value is specified in ISO 8601 (YYYY-MM-DDThh:mm:ss.sZ) format.
ChannelTitle	String				The title of the channel that the subscription belongs to.
Title	String				The title of the subscription.
Description	String				The details of the subscription.
ResourceIdKind	String				The type of the API resource.
ResourceIdChannelId	String				The value that YouTube uses to uniquely identify the channel that the user subscribed to.
ChannelId	String				The Id that YouTube uses to uniquely identify the channel of the subscriber.
ThumbnailsDefaultUrl	String				The URL of the default image.
TotalItemCount	Integer				The approximate number of items that the subscription points to.
NewItemCount	Integer				The number of new items in the subscription since its content was last read.
ActivityType	String				The type of activity this subscription is for. Valid values: all, uploads.
SubscriberTitle	String				The title of the channel of the subscriber.
SubscriberDescription	String				The description of the channel of the subscriber.
SubscriberChannelId	String				The Id that YouTube assigns to uniquely identify the channel of the subscriber.
SubscriberThumbnailsDefaultUrl	String				The URL of the default image.

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.


Name	Type	Description
MySubscribers	String	Set this value of this parameter to true to retrieve a feed of the subscribers of the authenticated user.
ForChannelId	String	The ForChannelId column specifies a comma-separated list of channel Ids.
OnBehalfOfContentOwner	String	This column is intended exclusively for YouTube content partners.
OnBehalfOfContentOwnerChannel	String	Specifies the YouTube ChannelId of the channel to which a video is being added.
Alphabetical	String	Sort alphabetically
Relevance	String	Sort by relevance.
Unread	String	Sort by order of activity.

TimeBasedReports

Query time-based info on views, subscribers, etc. For example, how many new subscribers were gained at a specific time.

Table-Specific Information

Select Rules

You can use the following tables to build queries to TimeBased Reports: TimeBased Reports for videos:


Dimensions	Use 0 or more	day, month, video
Metrics	Use 1 or more	views, comments, likes, dislikes, videosAddedToPlaylists, videosRemovedFromPlaylists, shares, estimatedMinutesWatched, averageViewDuration, averageViewPercentage, annotationClickThroughRate, annotationCloseRate, annotationImpressions, annotationClickableImpressions, annotationClosableImpressions, annotationClicks, annotationCloses, subscribersGained, subscribersLost, uniques, earnings, adEarnings, grossRevenue, redPartnerRevenue, monetizedPlaybacks, playbackBasedCpm, impressions, impressionBasedCpm
Filter	Use 0 or one	country, continent, subContinent
	Use 0 or one	video, group

Timebased Reports for playlist rules:


Dimensions	Use exactly one	day, month
	Use 0 or more	subscribedStatus, youtubeProduct
Metrics	Use 1 or more	views, estimatedMinutesWatched, averageViewDuration, playlistStarts, viewsPerPlaylistStart, averageTimeInPlaylist
Filter	Required	isCurated=true
	Use 0 or one	country, province, continent, subContinent
	Use 0 or one	playlist, channel, group
	Use 0 or more	subscribedStatus, youtubeProduct

Timebased Reports for content owner rules:


Dimensions	Use exactly one	day, month
Metrics	Use 1 or more	views, comments, likes, dislikes, videosAddedToPlaylists, videosRemovedFromPlaylists, shares, estimatedMinutesWatched, averageViewDuration, averageViewPercentage, annotationClickThroughRate, annotationCloseRate, annotationImpressions, annotationClickableImpressions, annotationClosableImpressions, annotationClicks, annotationCloses, subscribersGained, subscribersLost, uniques, earnings, adEarnings, grossRevenue, redPartnerRevenue, monetizedPlaybacks, playbackBasedCpm, impressions, impressionBasedCpm
Filter	Use exactly one	video, channel, group
	Use 0 or one	country, continent, subContinent

Timebased Reports for content owner playlist rules:


Dimensions	Use exactly one	day, month
	Use 0 or more	subscribedStatus, youtubeProduct
Metrics	Use 1 or more	views, estimatedMinutesWatched, averageViewDuration, playlistStarts, viewsPerPlaylistStart, averageTimeInPlaylis
Filter	Required	isCurated=true
	Use 0 or one	country, province, continent, subContinent
	Use 0 or one	playlist, channel, group
	Use 0 or more	subscribedStatus, youtubeProduct

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Channel	String	True			(only used in content owner reports) The Id for a YouTube channel. The channel dimension is frequently used in content owner reports because those reports typically aggregate data for multiple channels.
Video	String	True			The Id of a YouTube video.
Group	String	True			(filter only) The Id of a YouTube Analytics group.
Playlist	String	True			The Id of a YouTube playlist.
Country	String	True			The country associated with the metrics in the report row. The dimension value is a two-letter ISO-3166-1 country code, such as US, CN (China), or FR (France). The country code ZZ is used to report metrics for which YouTube could not identify the associated country.
Province	String	True			The U.S. state or territory associated with the metrics in the report row. The dimension value is an ISO 3166-2 code that identifies a U.S. state or the District of Columbia, such as US-MI (Michigan) or US-TX (Texas). The province code US-ZZ is used to report metrics for which YouTube could not identify the associated U.S. state. If the Province column is specified, Country must be set to US in the WHERE clause.
Continent	String	True			(filter only) A United Nations (U.N.) statistical region code. The API supports the following values: 002 Africa, 019 Americas (Northern America, Latin America, South America, and the Caribbean), 142 Asia, 150 Europe, 009 Oceania. This dimension can only be used to filter data.
SubContinent	String	True			(filter only) A U.N. statistical region code that identifies a geographical subregion.
Day	String	True		True	When you use this dimension, data in the report is aggregated on a daily basis and each row contains data for one day.
Month	String	True			Data in the report is aggregated by calendar month. In the report, dates are listed in YYYY-MM format. Note: If your SQL query uses the Month dimension, then the StartDate and EndDate parameters must both be set to the first day of the month.
IsCurated	Boolean	True			This filter indicates that the request is retrieving data about video views that occurred in the context of a playlist. This filter is required for any playlist report and its value must be set to 1.
ClaimedStatus	String	True			(only used in content owner reports) This dimension lets you indicate that a result set will only contain metrics for claimed content. The allowed value is claimed.
UploaderType	String	True			(only used in content owner reports) This dimension lets you indicate whether a result set will contain metrics for content uploaded by the specified content owner or content uploaded by third parties, such as user-uploaded videos. The allowed values are self and thirdParty.
SubscribedStatus	String	True			This dimension indicates whether the user activity metrics in the data row are associated with viewers who were subscribed to the video or playlist channel. Possible values are SUBSCRIBED and UNSUBSCRIBED. Note that the dimension value is accurate as of the time that the user activity occurs.
YoutubeProduct	String	True			This dimension identifies the YouTube service on which the user activity occurred. Data for this dimension is available as of July 18, 2015. The allowed values are CORE, GAMING, KIDS, UNKNOWN.
Views	Double	False	True		The number of times that a video was viewed. In a playlist report, the metric indicates the number of times that a video was viewed in the context of a playlist.
ViewerPercentage	Double	False	False		The percentage of viewers who were logged in when watching the video or playlist.
EstimatedMinutesWatched	Double	False	True		The number of minutes that users watched videos for the specified channel, content owner, video, or playlist.
AverageViewDuration	Double	False	True		The average length, in seconds, of video playbacks. In a playlist report, the metric indicates the average length, in seconds, of video playbacks that occurred in the context of a playlist.
AverageViewPercentage	Double	False	True		The average percentage of a video watched during a video playback.
Comments	Double	False	True		The number of times that users commented on a video.
Likes	Double	False	True		The number of times that users indicated that they liked a video by giving it a positive rating.
Dislikes	Double	False	True		The number of times that users indicated that they disliked a video by giving it a negative rating. This is a core metric and is subject to the Deprecation Policy.
Shares	Double	False	True		The number of times that users shared a video through the Share button.
SubscribersGained	Double	False	True		The number of times that users subscribed to a channel or video.
SubscribersLost	Double	False	True		The number of times that users unsubscribed from a channel or video.
VideosAddedToPlaylists	Double	False	True		The number of times that videos were added to the video owner playlist or other channel playlists like the Watch Later playlist. Channel uploads playlists and user watch history are not reported. This metric returns an absolute number, meaning that if a user adds a video to a playlist, removes it, and then adds it again, the metric value reflects the video being added to a playlist twice. Data for this metric is not available for dates before October 1, 2014.
VideosRemovedFromPlaylists	Double	False	True		The number of times that videos were removed from the video owner playlist and other YouTube playlists like the Watch Later playlist. This metric returns an absolute number, meaning that if a user adds a video to a playlist, removes it, adds it again, and then removes it again, the metric value reflects the video being removed from a playlist twice. Data for this metric is not available for dates before October 1, 2014.
PlaylistStarts	Double	False	False		The number of times that viewers initiated playback of a playlist.
ViewsPerPlaylistStart	Double	False	False		The average number of video views that occurred each time a playlist was initiated.
AverageTimeInPlaylist	Double	False	False		The estimated average amount of time, in minutes, that a viewer viewed videos in a playlist after the playlist was initiated.
AnnotationImpressions	Double	False	True		The total number of annotation impressions.
AnnotationClickableImpressions	Double	False	True		The number of annotations that appeared and could be clicked.
AnnotationClicks	Double	False	True		The number of clicked annotations.
AnnotationClickThroughRate	Double	False	True		The ratio of annotations that viewers clicked to the total number of clickable annotation impressions.
AnnotationClosableImpressions	Double	False	True		The number of annotations that appeared and could be closed.
AnnotationCloses	Double	False	True		The number of closed annotations.
AnnotationCloseRate	Double	False	True		The ratio of annotations that viewers closed to the total number of annotation impressions.
EstimatedRevenue	Decimal	False	False		The total estimated earnings (net revenue) from all Google-sold advertising sources as well as from nonadvertising sources for the selected date range and region. Estimated earnings are subject to month-end adjustment and do not include partner-sold and partner-served advertising.
EstimatedAdRevenue	Decimal	False	False		The total estimated earnings (net revenue) from all Google-sold advertising sources for the selected date range and region. Estimated earnings are subject to month-end adjustment and do not include partner-sold and partner-served advertising.
EstimatedRedPartnerRevenue	Decimal	False	False		The total estimated revenue earned from YouTube Red subscriptions for the selected report dimensions. The value of the metric reflects earnings from both music and nonmusic content and is subject to month-end adjustment.
GrossRevenue	Decimal	False	False		The estimated gross revenue, in USD, from all Google-sold or DoubleClick-partner-sold advertising for the selected date range and region. Gross revenue is subject to month-end adjustment and does not include partner-served advertising. Gross revenue should not be confused with earnings, or net revenue, which factors in your share of ownership and revenue-sharing agreements.
Cpm	Decimal	False	False		The estimated gross revenue per thousand ad impressions.
AdImpressions	Double	False	False		The number of verified ad impressions served.
MonetizedPlaybacks	Double	False	False		The number of instances when a viewer played your video and was shown at least one ad impression. A monetized playback is counted if a viewer is shown a preroll ad but quits watching the ad before your video ever starts. The expected estimated error for this figure is �2.0%.
PlaybackBasedCpm	Decimal	False	False		The estimated gross revenue per thousand playbacks.
StartDate	Date				Start date for fetching Analytics data.
EndDate	Date				End date for fetching Analytics data.

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.


Name	Type	Description
Segments	String	Segments the data returned for your request. Either a SegmentId or a custom segment.

TopVideos

Query the TopVideos for a channel or the TopPlaylists if you are a content owner. You can also use this view to query playback detail and geographic filters.

Table Specific Information

Select

The following query returns a channel's top videos by most minutes watched:

SELECT * FROM TopVideos

The following query returns a channel's top videos by most minutes watched in a specific country:

SELECT Video, Views, EstimatedMinutesWatched, AverageViewDuration, AverageViewPercentage FROM TopVideos WHERE Country = 'US'

The following query returns a channel's top playlists by most minutes watched:

SELECT Playlist, Views, EstimatedMinutesWatched, PlaylistStarts, AverageViewDuration FROM TopVideos WHERE IsCurated=true

Select Rules

You can use the following tables to build other queries to Top Videos:

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Channel	String	True			(only used in content owner reports) The Id for a YouTube channel. The channel dimension is frequently used in content owner reports because those reports typically aggregate data for multiple channels.
Video	String	True		True	The Id of a YouTube video.
Playlist	String	True			The Id of a YouTube playlist.
Group	String	True			(filter only) The Id of a YouTube Analytics group.
Country	String	True			The country associated with the metrics in the report row. The dimension value is a two-letter ISO-3166-1 country code, such as US, CN (China), or FR (France). The country code ZZ is used to report metrics for which YouTube could not identify the associated country.
Province	String	True			The U.S. state or territory associated with the metrics in the report row. The dimension value is an ISO 3166-2 code that identifies a U.S. state or the District of Columbia, such as US-MI (Michigan) or US-TX (Texas). The province code US-ZZ is used to report metrics for which YouTube could not identify the associated U.S. state. If the Province column is specified, Country must be set to US in the WHERE clause.
Continent	String	True			(filter only) A United Nations (U.N.) statistical region code. The API supports the following values: 002 Africa, 019 Americas (Northern America, Latin America, South America, and the Caribbean), 142 Asia, 150 Europe, 009 Oceania. This dimension can only be used to filter data.
SubContinent	String	True			(filter only) A U.N. statistical region code that identifies a geographical subregion.
IsCurated	Boolean	True			This filter indicates that the request is retrieving data about video views that occurred in the context of a playlist. This filter is required for any playlist report and its value must be set to 1.
ClaimedStatus	String	True			(only used in content owner reports) This dimension lets you indicate that a result set will only contain metrics for claimed content. The allowed value is claimed.
UploaderType	String	True			(only used in content owner reports) This dimension lets you indicate whether a result set will contain metrics for content uploaded by the specified content owner or content uploaded by third parties, such as user-uploaded videos. The allowed values are self and thirdParty.
SubscribedStatus	String	True			This dimension indicates whether the user activity metrics in the data row are associated with viewers who were subscribed to the video or playlist channel. Possible values are SUBSCRIBED and UNSUBSCRIBED. Note that the dimension value is accurate as of the time that the user activity occurs.
YoutubeProduct	String	True			This dimension identifies the YouTube service on which the user activity occurred. Data for this dimension is available as of July 18, 2015. The allowed values are CORE, GAMING, KIDS, UNKNOWN.
Views	Double	False	True		The number of times that a video was viewed. In a playlist report, the metric indicates the number of times that a video was viewed in the context of a playlist.
EstimatedMinutesWatched	Double	False	True		The number of minutes that users watched videos for the specified channel, content owner, video, or playlist.
AverageViewDuration	Double	False	True		The average length, in seconds, of video playbacks. In a playlist report, the metric indicates the average length, in seconds, of video playbacks that occurred in the context of a playlist.
AverageViewPercentage	Double	False	False		The average percentage of a video watched during a video playback.
PlaylistStarts	Double	False	False		The number of times that viewers initiated playback of a playlist.
Likes	Double	False	False		The number of times that users indicated that they liked a video by giving it a positive rating.
ViewerPercentage	Double	False	False		The percentage of viewers who were logged in when watching the video or playlist.
Comments	Double	False	True		The number of times that users commented on a video.
Dislikes	Double	False	True		The number of times that users indicated that they disliked a video by giving it a negative rating. This is a core metric and is subject to the Deprecation Policy.
Shares	Double	False	True		The number of times that users shared a video through the Share button.
SubscribersGained	Double	False	True		The number of times that users subscribed to a channel or video.
SubscribersLost	Double	False	True		The number of times that users unsubscribed from a channel or video.
VideosAddedToPlaylists	Double	False	True		The number of times that videos were added to the video owner playlist or other channel playlists like the Watch Later playlist. Channel uploads playlists and user watch history are not reported. This metric returns an absolute number, meaning that if a user adds a video to a playlist, removes it, and then adds it again, the metric value reflects the video being added to a playlist twice. Data for this metric is not available for dates before October 1, 2014.
VideosRemovedFromPlaylists	Double	False	True		The number of times that videos were removed from the video owner playlist and other YouTube playlists like the Watch Later playlist. This metric returns an absolute number, meaning that if a user adds a video to a playlist, removes it, adds it again, and then removes it again, the metric value reflects the video being removed from a playlist twice. Data for this metric is not available for dates before October 1, 2014.
ViewsPerPlaylistStart	Double	False	False		The average number of video views that occurred each time a playlist was initiated.
AverageTimeInPlaylist	Double	False	False		The estimated average amount of time, in minutes, that a viewer viewed videos in a playlist after the playlist was initiated.
AnnotationImpressions	Double	False	True		The total number of annotation impressions.
AnnotationClickableImpressions	Double	False	True		The number of annotations that appeared and could be clicked.
AnnotationClicks	Double	False	True		The number of clicked annotations.
AnnotationClickThroughRate	Double	False	True		The ratio of annotations that viewers clicked to the total number of clickable annotation impressions.
AnnotationClosableImpressions	Double	False	True		The number of annotations that appeared and could be closed.
AnnotationCloses	Double	False	True		The number of closed annotations.
AnnotationCloseRate	Double	False	True		The ratio of annotations that viewers closed to the total number of annotation impressions.
EstimatedRevenue	Decimal	False	False		The total estimated earnings (net revenue) from all Google-sold advertising sources as well as from nonadvertising sources for the selected date range and region. Estimated earnings are subject to month-end adjustment and do not include partner-sold and partner-served advertising.
EstimatedAdRevenue	Decimal	False	False		The total estimated earnings (net revenue) from all Google-sold advertising sources for the selected date range and region. Estimated earnings are subject to month-end adjustment and do not include partner-sold and partner-served advertising.
EstimatedRedPartnerRevenue	Decimal	False	False		The total estimated revenue earned from YouTube Red subscriptions for the selected report dimensions. The value of the metric reflects earnings from both music and nonmusic content and is subject to month-end adjustment.
GrossRevenue	Decimal	False	False		The estimated gross revenue, in USD, from all Google-sold or DoubleClick-partner-sold advertising for the selected date range and region. Gross revenue is subject to month-end adjustment and does not include partner-served advertising. Gross revenue should not be confused with earnings, or net revenue, which factors in your share of ownership and revenue-sharing agreements.
Cpm	Decimal	False	False		The estimated gross revenue per thousand ad impressions.
AdImpressions	Double	False	False		The number of verified ad impressions served.
MonetizedPlaybacks	Double	False	False		The number of instances when a viewer played your video and was shown at least one ad impression. A monetized playback is counted if a viewer is shown a preroll ad but quits watching the ad before your video ever starts. The expected estimated error for this figure is �2.0%.
PlaybackBasedCpm	Decimal	False	False		The estimated gross revenue per thousand playbacks.
StartDate	Date				Start date for fetching Analytics data. Either a date string or a relative date (e.g., today, yesterday, or #daysAgo).
EndDate	Date				End date for fetching Analytics data. Either a date string or a relative date (e.g., today, yesterday, or #daysAgo).

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.


Name	Type	Description
Segments	String	Segments the data returned for your request. Either a SegmentId or a custom segment.

TrafficSources

Query TrafficSources. Statistics are based on the manner in which viewers reached your video and playlist content.

Table Specific Information

Select

The following query aggregates viewing statistics based on the manner in which viewers reached your video content; for example, to identify the number of views that stemmed from a Google search or from a link to a related video.

SELECT * FROM TrafficSources

The following query displays the Views and EstimatedMinutesWatched metrics grouped by the Day and InsightTrafficSourceType dimensions:

SELECT Day, InsightTrafficSourceType, Views, EstimatedMinutesWatched FROM TrafficSources

The following query filters the statistics to be compiled for a specific Video and Country:

SELECT Day, InsightTrafficSourceType, Views, EstimatedMinutesWatched FROM TrafficSources WHERE Video = 'video_id' AND Country = 'US'

The following query aggregates viewing statistics based on the manner in which viewers reached your playlist content; for example, to identify the number of views that stemmed from a Google search.

SELECT InsightTrafficSourceType, Views, EstimatedMinutesWatched, PlaylistStarts, ViewsPerPlaylistStart, AverageTimeInPlaylist FROM TrafficSources WHERE IsCurated=true

The following query returns search terms that generated the most views from YouTube search results for videos located in playlists. Notice that ordering and limiting are required for such a query due to restrictions in the YouTube Analytics API.

SELECT InsightTrafficSourceDetail, Views, EstimatedMinutesWatched, PlaylistStarts, ViewsPerPlaylistStart, AverageTimeInPlaylist FROM TrafficSources WHERE IsCurated=true AND InsightTrafficSourceType='YT_SEARCH' ORDER BY Views DESC LIMIT 25

Select Rules

You can use the following tables to build other queries to Traffic Sources:

Traffic Sources for videos:


Dimensions	Use exactly one	insightTrafficSourceType,insightTrafficSourceDetail
	Use 0 or more	day,liveOrOnDemand,subscribedStatus
Metrics	Use 1 or more	views,estimatedMinutesWatched
Filter	Use 0 or one	country,province,continent,subContinent
	Use 0 or one	video,group
	Use 0 or more	liveOrOnDemand,subscribedStatus
Sort	Use 0 or more(only descending order)	views,estimatedMinutesWatched

Traffic Sources for playlists:


Dimensions	Use exactly one	insightTrafficSourceType,insightTrafficSourceDetail
	Use 0 or more	day,subscribedStatus
Metrics	Use 1 or more	views,estimatedMinutesWatched,playlistStarts,viewsPerPlaylistStart,averageTimeInPlaylist
Filter	Required	isCurated=true
	Use 0 or one	country,province,continent,subContinent
	Use 0 or one	playlist,channel,group
	Optional	subscribedStatus
Sort	Use 0 or more(only descending order)	views,estimatedMinutesWatched,playlistStarts

Traffic Sources for content owners:


Dimensions	Required	insightTrafficSourceType
	Use 0 or more	day,liveOrOnDemand,subscribedStatus
Metrics	Use 1 or more	views,estimatedMinutesWatched
Filter	Use exactly one	video,channel,group
	Use 0 or one	country,province,continent,subContinent
	Use 0 or more	liveOrOnDemand,subscribedStatus

Traffic Sources for content owner playlists:


Dimensions	Required	insightTrafficSourceType
	Use 0 or more	day,subscribedStatus
Metrics	Use 1 or more	views,estimatedMinutesWatched,playlistStarts,viewsPerPlaylistStart,averageTimeInPlaylist
Filter	Required	isCurated=true
	Use 0 or one	country,province,continent,subContinent
	Use 0 or one	playlist,channel,group
	Optional	subscribedStatus

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Channel	String	True			(only used in content owner reports) The Id for a YouTube channel. The channel dimension is frequently used in content owner reports because those reports typically aggregate data for multiple channels.
Video	String	True			The Id of a YouTube video.
Playlist	String	True			The Id of a YouTube playlist.
Group	String	True			(filter only) The Id of a YouTube Analytics group.
Country	String	True			The country associated with the metrics in the report row. The dimension value is a two-letter ISO-3166-1 country code, such as US, CN (China), or FR (France). The country code ZZ is used to report metrics for which YouTube could not identify the associated country.
Province	String	True			The U.S. state or territory associated with the metrics in the report row. The dimension value is an ISO 3166-2 code that identifies a U.S. state or the District of Columbia, such as US-MI (Michigan) or US-TX (Texas). The province code US-ZZ is used to report metrics for which YouTube could not identify the associated U.S. state. If the Province column is specified, Country must be set to US in the WHERE clause.
Continent	String	True			(filter only) A United Nations (U.N.) statistical region code. The allowed values are 002 Africa, 019 Americas (Northern America, Latin America, South America, and the Caribbean), 142 Asia, 150 Europe, 009 Oceania. This dimension can only be used to filter data.
SubContinent	String	True			(filter only) A U.N. statistical region code that identifies a geographical subregion.
Day	String	True			When you use this dimension, data in the report is aggregated on a daily basis and each row contains data for one day.
LiveOrOnDemand	String	True			This dimension indicates whether the user activity metrics in the data row are associated with views of a live broadcast. Data for this dimension is available for dates beginning April 1, 2014. The allowed values are LIVE, ONDEMAND.
SubscribedStatus	String	True			This dimension indicates whether the user activity metrics in the data row are associated with viewers who were subscribed to the video or playlist channel. The allowed values are SUBSCRIBED, UNSUBSCRIBED. Note that the dimension value is accurate as of the time that the user activity occurs.
InsightTrafficSourceType	String	True		True	Data in the report is aggregated based on the referrer type, which describes the manner in which users reached the video. The allowed values are ADVERTISING, ANNOTATION, EXT_URL, NO_LINK_EMBEDDED, NO_LINK_OTHER, NOTIFICATION, PLAYLIST, PROMOTED, RELATED_VIDEO, SUBSCRIBER, YT_CHANNEL, YT_OTHER_PAGE, YT_SEARCH.
InsightTrafficSourceDetail	String	True			Details about the InsightTrafficSourceType.
IsCurated	Boolean	True			This filter indicates that the request is retrieving data about video views that occurred in the context of a playlist. This filter is required for any playlist report and its value must be set to 1.
ClaimedStatus	String	True			(only used in content owner reports) This dimension lets you indicate that a result set will only contain metrics for claimed content. The allowed value is claimed.
UploaderType	String	True			(only used in content owner reports) This dimension lets you indicate whether a result set will contain metrics for content uploaded by the specified content owner or content uploaded by third parties, such as user-uploaded videos. The allowed values are self and thirdParty.
Views	Double	False	True		The number of times that a video was viewed. In a playlist report, the metric indicates the number of times that a video was viewed in the context of a playlist.
EstimatedMinutesWatched	Double	False	True		The number of minutes that users watched videos for the specified channel, content owner, video, or playlist.
PlaylistStarts	Double	False	False		The number of times that viewers initiated playback of a playlist.
ViewsPerPlaylistStart	Double	False	False		The average number of video views that occurred each time a playlist was initiated.
AverageTimeInPlaylist	Double	False	False		The estimated average amount of time, in minutes, that a viewer viewed videos in a playlist after the playlist was initiated.
StartDate	Date				Start date for fetching Analytics data.
EndDate	Date				End date for fetching Analytics data.

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.


Name	Type	Description
Segments	String	Segments the data returned for your request. Either a SegmentId or a custom segment.

VideoAbuseReportReasons

Query YouTube VideoAbuseReportReasons. A VideoAbuseReportReason resource contains information about a reason that a Video would be flagged for containing abusive content.

Table Specific Information

Select

There are no required filters for this table. An optional column is Language.

The '=' operator is supported for this column.

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The Id that YouTube uses to identify the reason.
Label	String				The label text for the reason.
SecondaryReasonsAggregate	String				A list of secondary reasons associated with the reason, if any available. There might be 0 or more.

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.


Name	Type	Description
Language	String	The hl parameter specifies the language that should be used for text values in the API response. The default value is en_US.

VideoCategories

Query YouTube VideoCategories. A VideoCategory resource identifies a category that has been or could be associated with uploaded Videos.

Table Specific Information

Select

You can specify exactly one of the following columns: Id or Region. If you do not specify any filtering criteria, by default you will get results for the US Region. An extra optional parameter is Language.

SELECT * FROM VideoCategories WHERE Region = 'JP'

The IN operator can be used to filter on multiple Ids:

SELECT Id, Kind, Etag, ChannelId, Title, Assignable

FROM   VideoCategories

WHERE  (ID IN ('1', '2', '10'))

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The unique identifier for the video category.
ChannelId	String				The YouTube channel that created the video category.
Title	String				The title of the video category.
Assignable	Boolean				Indicates whether videos can be associated with the category.
Region	String				The country from which to retrieve video categories.

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.


Name	Type	Description
Language	String	Specifies the language that should be used for text values in the API response. The default value is en_US.

Videos

Query YouTube Videos.

Table Specific Information

Select

By default, if no filters are specified, the driver will return all videos owned by the authenticated user.

Only one of the following columns may be filtered by at a time: Id, MyRating, ChannelId, or Chart. Extra columns supported are ForMine, Language, ForContentOwner, OnBehalfOfContentOwner.

A search against all videos on Youtube may be executed by specifying SearchTerms, RelatedToVideoId, VideoDuration, VideoCategoryId, or Region.

All columns support the '=' operator. PublishedAt also supports these additional operators: '>', '>=', '<', and '<='. Id and ChannelId supports IN.

Order by is supported for the Date, Rating, Relevance and Title columns.

SELECT * FROM Videos WHERE Id = '12345679'



SELECT * FROM Videos WHERE PublishedAt >= '2016-01-01T20:30:00Z' LIMIT 10



SELECT Description FROM Videos WHERE ForMine = True ORDER BY Date

Note: Ordering is not supported when specifying one of the colums used for searching channels such as SearchTerms.

The IN operator can be used to specify a list of channels. This call returns a list of videos for each specified channel.

SELECT * FROM Videos WHERE ChannelId IN (a, b, c)

SELECT * FROM Videos WHERE ChannelId IN (SELECT Id FROM Channels)

IN operator can also be used for getting data for multiple videos.

SELECT * FROM Videos WHERE Id IN (a, b, c)

Columns


Name	Type	Dimension	DefaultMetric	DefaultDimension	Description
Id [KEY]	String				The Id that YouTube uses to uniquely identify the video.
ChannelId	String				The Id that YouTube uses to uniquely identify the channel that the video was uploaded to.
CategoryId	String				The YouTube video category associated with the video.
Title	String				The title of the video.
ChannelTitle	String				Channel title for the channel that the video belongs to.
PublishedAt	Datetime				The date and time that the video was published. Note that this time might be different than the time that the video was uploaded. The value is specified in ISO 8601 (YYYY-MM-DDThh:mm:ss.sZ) format.
Description	String				The description of the video.
Duration	String				The length of the video. The property value is an ISO 8601 duration.
ViewCount	Long				The number of times the video has been viewed.
LikeCount	Long				The number of users who have indicated that they liked the video.
DislikeCount	Long				The number of users who have indicated that they disliked the video.
CommentCount	Long				The number of comments for the video.
EmbedHtml	String				An iframe tag that embeds a player that will play the video.
PublicStatsViewable	Boolean				This value indicates whether the extended video statistics on the watch page are publicly viewable. By default, those statistics are viewable, and statistics like the viewcount and ratings will still be publicly visible even if this value is set to false.
TagsAggregate	String				A list of keyword tags associated with the video.
LiveBroadcastContent	String				Indicates if the video is an upcoming/active live broadcast. A value of none indicates the video is not an upcoming/active live broadcast. Valid values for this column: live, none, upcoming.
Dimension	String				Indicates whether the video is available in 3D or in 2D.
Definition	String				Indicates whether the video is available in high definition HD or only in standard definition. Valid values for this column: hd,sd.
Caption	String				Indicates whether captions are available for the video. Valid values for this column: false, true.
LicensedContent	Boolean				Indicates whether the video represents licensed content, which means that the content was uploaded to a channel linked to a YouTube content partner and then claimed by that partner.
AllowedAggregate	String				A list of region codes that identify countries where the video is viewable.
BlockedAggregate	String				A list of region codes that identify countries where the video is blocked.
Projection	String				Specifies the projection format of the video. Valid values: 360, rectangular.
PrivacyStatus	String				The privacy status of the playlist. Valid values: private, public, unlisted.
MadeForKids	Boolean				This value indicates whether the video is Made For Kids.
License	String				The license. Valid values: creativeCommon, youtube.
Embeddable	Boolean				This value indicates whether the video can be embedded on another website.
ThumbnailsDefaultUrl	String				The URL of the default image.
DefaultLanguage	String				This column is confidential to trusted testers. The language of the text in the Title and Description columns.
DefaultAudioLanguage	String				The default_audio_language column specifies the language spoken in the default audio track of the video.
UploadStatus	String				The status of the uploaded video. Valid values for this column: deleted, failed, processed, rejected, uploaded.
FailureReason	String				This value explains why a video failed to upload. This column only has a value if the UploadStatus column indicates that the upload failed. Valid values for this column: codec, conversion, emptyFile, invalidFile, tooSmall, uploadAborted.
RejectionReason	String				This value explains why YouTube rejected an uploaded video. This column only has a value if the UploadStatus column indicates that the upload was rejected. Valid values for this column: claim, copyright, duplicate, inappropriate, length, termsOfUse, trademark, uploaderAccountClosed, uploaderAccountSuspended.
RecordingDate	Datetime				The date and time when the video was recorded. The value is specified in ISO 8601 (YYYY-MM-DDThh:mm:ss.sssZ) format.
SearchTerms	String				The query term to search for.

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.


Name	Type	Description
SafeSearch	String	Indicates whether the search results should include restricted content as well as standard content. Acceptable values: moderate, none, or strict.
EventType	String	The eventType parameter restricts a search to broadcast events. Acceptable values are: COMPLETED, LIVE, UPCOMING. The allowed values are COMPLETED, LIVE, UPCOMING.
RelatedToVideoId	String	Retrieves a list of videos that are related to the video that the column value identifies.
VideoDuration	String	Filters video search results based on their duration. Acceptable values: any, long, medium or short.
VideoCategoryId	String	The videoCategoryId parameter identifies the video category for which the chart should be retrieved.
Region	String	Instructs the API to select a video chart available in the specified region. This column can only be used in conjunction with the chart column. The parameter value is an ISO 3166-1 alpha-2 country code.
ForMine	String	Restricts the search to only retrieve videos owned by the authenticated user. It is false by default.
Chart	String	The chart parameter identifies the chart that you want to retrieve. Acceptable values are: mostPopular - Return the most popular videos for the specified content region and video category. The allowed values are mostPopular.
MyRating	String	The rating of the authenticated user. Acceptable values: like or dislike. The allowed values are like, dislike.
Language	String	Retrieve localized resource metadata for a specific application language that the YouTube website supports.
ForContentOwner	String	Restricts the search to only retrieve videos owned by the content owner specified by the OnBehalfOfContentOwner parameter.
OnBehalfOfContentOwner	String	This parameter is intended exclusively for YouTube content partners.

Matillion Data Model for YouTube AnalyticsVersion - 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

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

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Data Type

Default Value

Remarks

Matillion Data Model for YouTube Analytics
Version - 21.0.8137.0