Connecting to Google BigQuery

For information on the available WHL files for supported environments, and how to install the appropriate file for your Python distribution, see Package Installation.

For information on the module to import, and how to configure the necessary connection properties in a connection string, see Establishing a Connection.

Other available connection properties can be used to configure other aspects of the connector capabilities.

Python Version Support

The CData Python Connector for Google BigQuery can be installed and used in Python 3.10 or newer.

Google BigQuery Version Support

The connector enables read/write SQL-92 access to the BigQuery tables in your Google account or Google Apps domain. The complete aggregate and join syntax in BigQuery is supported. Additionally, statements in the BigQuery syntax can be passed through. The connector uses version 2.0 of the BigQuery Web services API: You must enable this API by creating a project in the Google Developers Console. See Connecting to Google for a guide to creating a project and authenticating to this API.

See Also

• Using the Connector: Establish connections and query Google BigQuery through Python code.
• From SQLAlchemy: Use SQLAlchemy to establish a connection with dialect URL, and interact with Google BigQuery data using mapped classes and Sessions.

Dependencies

The Python connectors require that Python 3.10 or newer be installed.

Installation

The CData Python Connector for Google BigQuery is available as a WHL file for Windows, Linux, and Mac. Each connector is built using the Python 3.10 Stable ABI (indicated by the abi3 tag in the filename), so a single wheel supports any Python 3.10 or newer installation — there is no need to match your exact Python minor version. Use the "pip install" command with the appropriate WHL file for your platform.

Windows:

pip install cdata_googlebigquery_connector-26.0.9655-cp310-abi3-win_amd64.whl

Linux:

pip install cdata_googlebigquery_connector-26.0.9655-cp310-abi3-linux_x86_64.whl

macOS:

pip install cdata_googlebigquery_connector-26.0.9655-cp310-abi3-macosx_12_0_arm64.whl

The macOS wheel supports arm64 (Apple Silicon) architectures only on macOS 12 and newer.

Regardless of the environment, certain distributions might require that the "pip3 install" command be used instead, to differentiate from a Python 2 distribution that might exist already. After installation, confirm whether the connector is successfully installed by running the "pip list" command. If "cdata_googlebigquery_connector" is present in the list output by the command, then the installation was successful.

Upgrading

When upgrading, "pip install" does not automatically clean up old JRE files. To avoid leftover files that could cause JVM errors, uninstall the previous version before installing the new one.

Licensing

After the installation is complete, a separate step is needed to activate a license for the connector. Among the CData assets in the distribution's site packages, there is an install-license tool that activates this license. From within the distribution's site-packages folder, after navigating to the "cdata/installlic_googlebigquery" folder, simply use a command like the below to activate the license. Omitting the <key> argument activates a trial license:

• Windows:
  

  ./install-license.exe <key>

• Linux / Mac:
  

  ./install-license.sh <key>

Sometimes, file access issues may cause pip to install the connector in a fallback file path that is not the python distribution's main or primary site-packages location. This can make it difficult to find where the connector was installed, and from there, the license activator. In that event, this python script below will print out the full file path of the connector's native file. This file will be stored in the mentioned cdata folder, from which the installlic_googlebigquery folder is trivial to find:

import os
import cdata.googlebigquery
path = os.path.abspath(cdata.googlebigquery.__file__)
print(path)

Uninstallation

If the connector needs to be uninstalled for any reason, do so by running the pip uninstall command, as in the example below:

pip uninstall cdata-googlebigquery-connector

The objects available within our connector are accessible from the "cdata.googlebigquery" module. To use the module's objects directly:

1. Import the module as follows:
   

   import cdata.googlebigquery as mod

2. To establish a connection string, call the connect() method from the connector object using an appropriate connection string, such as:
   

   mod.connect("InitiateOAuth=GETANDREFRESH;ProjectId=NameOfProject;DatasetId=NameOfDataset;")

Connecting to Google BigQuery

By default, the CData connector connects to all available projects in your database. To limit the scope of your connection, set combinations of the following properties:

• ProjectId: specifies which projects the driver connects to
• BillingProjectId: specifies which projects are billed
• DatasetId: specifies which datasets the driver accesses

Authenticating to Google BigQuery

The connector supports using user accounts, service accounts and GCP instance accounts for authentication.

The following sections discuss the available authentication schemes for Google BigQuery:

• User Accounts (OAuth)
• Service Account (OAuthJWT)
• GCP Instance Account

User Accounts (OAuth)

AuthScheme must be set to OAuth in all user account flows.

Desktop Applications

CData provides an embedded OAuth application that simplifies OAuth desktop Authentication. Alternatively, you can create a custom OAuth application. See Creating a Custom OAuth App for information about creating custom applications and reasons for doing so.

For authentication, the only difference between the two methods is that you must set two additional connection properties when using custom OAuth applications.

After setting the following connection properties, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH, which instructs the connector to automatically attempt to get and refresh the OAuth access token.
• OAuthClientId: (custom applications only) Set this to the Client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the Client Secret in the custom OAuth application settings.

When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process as follows:

• Extracts the access token from the callback URL.
• Obtains a new access token when the old one expires.
• Saves OAuth values in OAuthSettingsLocation that persist across connections.

Web Applications

When connecting via a Web application, you need to create and register a custom OAuth application with Google BigQuery. You can then use the connector to acquire and manage the OAuth token values. See Creating a Custom OAuth App for more information about custom applications.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Callback URL you specified in your application settings. The stored procedure returns the URL to the OAuth endpoint.
2. Navigate to the URL that the stored procedure returned in Step 1. Log in to the custom OAuth application and authorize the web application. Once authenticated, the browser redirects you to the callback URL.
3. Call the GetOAuthAccessToken stored procedure. Set AuthMode to WEB and the Verifier input to the "code" parameter in the query string of the callback URL.

Once you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the driver automatically refresh the OAuth access token, set the following on the first data connection:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where the connector saves the OAuth token values, which persist across connections.

On subsequent data connections, the values for OAuthAccessToken and OAuthRefreshToken are taken from OAuthSettingsLocation.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token.

Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the Client Id in your application settings.
• OAuthClientSecret: Set this to the Client Secret in your application settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Headless Machines

To configure the driver to use OAuth with a user account on a headless machine, you need to authenticate on another device that has an internet browser.

1. Choose one of two options:
   • Option 1: Obtain the OAuthVerifier value as described in "Obtain and Exchange a Verifier Code" below.
   • Option 2: Install the connector on a machine with an Internet browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow, as described in "Transfer OAuth Settings" below.
2. Then configure the connector to automatically refresh the access token on the headless machine.

Option 1: Obtain and Exchange a Verifier Code

To obtain a verifier code, you must authenticate at the OAuth authorization URL.

Follow the steps below to authenticate from the machine with an Internet browser and obtain the OAuthVerifier connection property.

1. Choose one of these options:
   • If you are using the Embedded OAuth Application click Google BigQuery OAuth endpoint to open the endpoint in your browser.
   • If you are using a custom OAuth application, create the Authorization URL by setting the following properties:
     • InitiateOAuth: Set to OFF.
     • OAuthClientId: Set to the client Id assigned when you registered your application.
     • OAuthClientSecret: Set to the client secret assigned when you registered your application.
     Then call the GetOAuthAuthorizationURL stored procedure with the appropriate CallbackURL. Open the URL returned by the stored procedure in a browser.
2. Log in and grant permissions to the connector. You are then redirected to the callback URL, which contains the verifier code.
3. Save the value of the verifier code. Later you will set this in the OAuthVerifier connection property.

Next, you need to exchange the OAuth verifier code for OAuth refresh and access tokens. Set the following properties:

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• InitiateOAuth: Set this to REFRESH.
• OAuthVerifier: Set this to the verifier code.
• OAuthClientId: (custom applications only) Set this to the Client Id in your custom OAuth application settings.
• OAuthClientSecret: (custom applications only) Set this to the Client Secret in the custom OAuth application settings.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified location.

After the OAuth settings file is generated, you need to re-set the following properties to connect:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location containing the encrypted OAuth authentication values. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Option 2: Transfer OAuth Settings

Prior to connecting on a headless machine, you need to create and install a connection with the driver on a device that supports an Internet browser. Set the connection properties as described in "Desktop Applications" above.

After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the location specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt.

Once you have successfully tested the connection, copy the OAuth settings file to your headless machine.

On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
• OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
• OAuthSettingsLocation: Set this to the location of your OAuth settings file. Make sure this location gives read and write permissions to the connector to enable the automatic refreshing of the access token.

Service Accounts (OAuthJWT)

To authenticate using a service account, you must create a new service account and have a copy of the accounts certificate. If you do not already have a service account, you can create one by following the procedure in Creating a Custom OAuth App.

For a JSON file, set these properties:

• AuthScheme: Set this to OAuthJWT.
• InitiateOAuth: Set this to GETANDREFRESH.
• OAuthJWTCertType: Set this to GOOGLEJSON.
• OAuthJWTCert: Set this to the path to the .json file provided by Google.
• OAuthJWTSubject: (optional) Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.

For a PFX file, set these properties instead:

• AuthScheme: Set this to OAuthJWT.
• InitiateOAuth: Set this to GETANDREFRESH.
• OAuthJWTCertType: Set this to PFXFILE.
• OAuthJWTCert: Set this to the path to the .pfx file provided by Google.
• OAuthJWTCertPassword: (optional) Set this to the .pfx file password. In most cases you must provide this since Google encrypts PFX certificates.
• OAuthJWTCertSubject: (optional) Set this only if you are using a OAuthJWTCertType which stores multiple certificates. Should not be set for PFX certificates generated by Google.
• OAuthJWTIssuer: Set this to the email address of the service account. This address will usually include the domain iam.gserviceaccount.com.
• OAuthJWTSubject: (optional) Only set this value if the service account is part of a GSuite domain and you want to enable delegation. The value of this property should be the email address of the user whose data you want to access.

GCP Instance Accounts

When running on a GCP virtual machine, the connector can authenticate using a service account tied to the virtual machine. To use this mode, set AuthScheme to GCPInstanceAccount.

Workload Identity Federation

When Workload Identity Federation is set up, the driver authenticates to an identity provider and provides the Google Security Token Service with an authentication token. The Google STS validates this token and produces an OAuth token that can access Google services.

The following identity providers are currently supported:

• To use AWS, set AuthScheme to AWSWorkloadIdentity and configure AWSWorkloadIdentityConfig to connect to the identity provider.
• To use Azure, set AuthScheme to AzureWorkloadIdentity and configure AzureWorkloadIdentityConfig to connect to the identity provider.

Optionally, service account impersonation can also be configured by setting RequestingServiceAccount to the service account that will impersonate the credentials.

Java Native Interface (JNI) is a standard programming interface for writing Java native methods and embedding the Java virtual machine into native applications.

The connector leverages the JNI for improved performance on Mac and Linux.

Configure the Config INI File

The Linux and Mac editions of the Google BigQuery python connector are configured with an ini file. This file is used to set several parameters, including JNI behavior. This file is to be located in:

{path_to_distribution_site-packages}/cdata/config.ini

Ensure that any configuration properties you set in the ini file fall under the following section name (adjust the 311 number if you are using an different python version from 3.11):

• For Linux:
  

  [googlebigquery.cpython-311-x86_64-linux-gnu.so]

• For Mac:
  

  [googlebigquery.cpython-311-darwin.so]

Configure the JNI connector's behavior by editing the properties in the connector's config.ini file. The connector can be configured as follows:

• LOGFILE: Set this the same way as the CDATA_LOGFILE envrionment variable below.
• JAVA_HOME: Configure the path to the JVM library location used to launch the JVM.
• CLASS_PATH: Use a colon-separated list to configure the paths to the third-party jar libraries.

Configure Environment Variables

Additionally, set the following environment variables:

• CDATA_JAVA_HOME: Configure the path to the JVM library location used to launch the JVM.
• CDATA_JVM_OPTIONS: Place JVM options here.
• CDATA_LOGFILE: Set this in the following scheme: <SCHEME>://<TAG>[|<LEVEL>]
  • SCHEME: The options are STDOUT, FILE.
    • STDOUT: Both the native wrapper and odbc core log into stdout. The Logfile and Verbosity properties can override the behavior of ODBC core.
    • FILE: The native wrapper logs into <FILENAME> while the odbc core logs into <FILENAME>.driver.log. The Logfile and Verbosity properties can override the behavior of ODBC core.
  • TAG
    • For STDOUT, set this to 1. For FILE, set this to the filename.
  • LEVEL
    • Set to one of: FATAL | ERROR | WARNING | INFO | DEBUG

The following are some examples of this syntax:

• STDOUT://1|DEBUG
• FILE:///tmp/my_py.log|DEBUG

Custom Logger

The Python connector supports a custom logging mechanism for redirecting log output to any destination, such as a cloud storage service or logging framework. Use setCustomLoggerFactory() to register a factory function that creates a logger instance for each connection.

The factory function receives the context string from the Logfile connection property (the portion after CUSTOM://) and must return an object with a writeLog(verbosity, message) method.

To enable custom logging:

1. Call setCustomLoggerFactory() with your factory function before opening connections.
2. Set Logfile to CUSTOM:// followed by a context string to identify the connection.
3. Set Verbosity to the desired log level.

The following example demonstrates a custom logger factory that creates a separate logger instance per connection:

import cdata.googlebigquery as mod
import time

class MyLogger:
    def __init__(self, loggerId):
        self.loggerId = loggerId
    def writeLog(self, verbosity, message):
        print("[MyLogger " + self.loggerId + "] " + message)

def createLogger(context):
    return MyLogger(context[len("MyLoggerId="):])

mod.setCustomLoggerFactory(createLogger)

conn1 = mod.connect("...;Logfile=CUSTOM://MyLoggerId=1;Verbosity=2;")
# do something with conn1
time.sleep(1)  # Wait for logs to flush from conn1

conn2 = mod.connect("...;Logfile=CUSTOM://MyLoggerId=2;Verbosity=2;")
# do something with conn2
time.sleep(1)  # Wait for logs to flush from conn2

Creating a Custom OAuth Application

CData embeds OAuth Application Credentials with CData branding that can be used when connecting to Google BigQuery via a desktop application or a headless machine.

(For information on getting and setting the OAuthAccessToken and other configuration parameters, see the Desktop Authentication section of "Connecting to Google BigQuery".)

However, you must create a custom OAuth application to connect to Google BigQuery via the Web. And since custom OAuth applications seamlessly support all three commonly-used auth flows, you might want to create custom OAuth applications (use your own OAuth Application Credentials) for those auth flows anyway.

Custom OAuth applications are useful if you want to:

• control branding of the authentication dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

The following sections describe how to enable the Directory API and create custom OAuth applications for user accounts (OAuth) and Service Accounts (OAuth/JWT).

Enable the BigQuery API

Follow these steps to enable the BigQuery API:

1. Navigate to the Google Cloud Console.
2. Select Library from the left-hand navigation menu. This opens the Library page.
3. In the search field, enter "BigQuery API" and select BigQuery API from the search results.
4. On the BigQuery API page, ENABLE both APIs.

Create an OAuth Application

To create custom OAuth applications that retrieve the necessary OAuth connection properties, follow these procedures.

User Accounts (OAuth)

For users whose AuthScheme is OAuth and who need to authenticate over a web application, you must always create a custom OAuth application. (For desktop and headless flows, creating a custom OAuth application is optional.)

Do the following:

1. Navigate to the Google Cloud Console.
2. Create a new project or select an existing project.
3. At the left-hand navigation menu, select Credentials.
4. If this project does not already have a consent screen configured, click CONFIGURE CONSENT SCREEN to create one. If you are not using a Google Workspace account, you are restricted to creating an External-type Consent Screen, which requires specifying a support email and developer contact email. Additional info is optional.
5. On the Credentials page, select Create Credentials > OAuth Client ID.
6. In the Application Type menu, select Web application.
7. Specify a name for your custom OAuth application.
8. Under Authorized redirect URIs, click ADD URI and enter a redirect URI.
9. Click Enter, then CREATE. The Cloud Console returns you to the Credentials page.
   A window opens that displays your client Id and client secret.
10. Record the client Id and Client Secret for later use as the OAuthClientId and OAuthClientSecret connection properties.

Note: The client secret remains accessible from from the Google Cloud Console.

Service Accounts (OAuthJWT)

Service accounts (AuthScheme OAuthJWT) can be used in an OAuth flow to access Google APIs on behalf of users in a domain. A domain administrator can delegate domain-wide access to the service account.

To create a new service account:

1. Navigate to the Google Cloud Console.
2. Create a new project or select an existing project.
3. At the left-hand navigation menu, select Credentials.
4. Select Create Credentials > Service account.
5. On the Create service account page, enter the service account name, ID, and an optional description.
6. Click DONE. The Cloud Console redisplays the Credentials page.
7. In the Service Accounts section, select the service account you just created.
8. Click the Advanced Settings section and enable Domain-Wide Delegation.
9. Record the Client ID shown for domain-wide delegation. You'll use this in the Admin Console.
10. In a new tab, navigate to the Google Admin Console.
11. Go to Security > API Controls > Domain-Wide Delegation.
12. Click Manage Domain-Wide Delegation, then Add new.
13. Enter the recorded Client ID and the list of required scopes. See OAuth Scopes and Endpoints for more details.
14. Back in the Cloud Console, select the KEYS tab for the service account.
15. Click ADD KEY > Create new key.
16. Select a supported key type (see OAuthJWTCert and OAuthJWTCertType).
17. Click CREATE. The key is automatically downloaded to your device.
18. Record the additional information for later use.

In the service account flow, the connector exchanges a JSON Web Token (JWT) for the OAuthAccessToken. The private key downloaded in the steps above is used to sign the JWT. The connector inherits the permissions granted to the service account, including any scopes configured through domain-wide delegation.

The following sections detail connector settings that may be needed in advanced integrations.

Saving Result Sets

Large result sets must be saved in a temporary or permanent table. You can use the following properties to control table persistence:

Automatic Result Tables

Enable the AllowLargeResultSets property to make the connector automatically create destination tables when needed. If a query result is too large to fit the BigQuery query cache, the connector creates a hidden dataset within the data project and re-executes the query with a destination table in that dataset. The dataset is configured so that all tables created within it expire in 24 hours.

In some situations you may want to change the name of the dataset created by the connector. For example, if multiple users are using the connector and do not have permissions to write to datasets created by the other users. See TempTableDataset for details on how to do this.

Explicit Result Tables

Enable the DestinationTable property to make the connector write query results to the given table. Writing query results to a single table imposes several limitations that you should keep in mind when using this option:

• Two query results cannot be read at the same time on the same connection. If two queries are executed and their results are read at the same time, the last query to finish executing overwrites the data from the other query.
• The dataset must be created in the same region as your tables. BigQuery does not support writing a destination table in a different region than where a query was executed.
• Do not rely on the connector to create a temporary table for every query. Some queries are processed internally or read directly from a table without executing a query job on BigQuery.

Limiting Billing

Set MaximumBillingTier to override your project limits on the maximum cost for any given query in a connection.

Bulk Modes

Google BigQuery provides several interfaces for operating on batches of rows. The connector supports these methods through the InsertMode option, each of which are specialized to different use cases:

• The Streaming API is intended for use where the most important factor is being able to insert quickly. However, rows which are inserted via the API are queued and only appear in the table after a delay. Sometimes this delay can be as high as 20-30 minutes which makes this API incompatible with cases where you want to insert data and then run other operations on it immediately. You should avoid modifying the table while any rows are in the streaming queue: Google BigQuery prevents DML operations from running on the table while any rows are in the streaming queue, and changing the table's metadata (name, schema, etc.) may cause streamed rows that haven't been committed to be lost.
• The DML mode API uses Standard SQL INSERT queries to upload data. This is by the most robust method of uploading data because any errors in the uploaded rows will be reported immediately. The connector also uses this API in a synchronous way so once the INSERT is processed, any rows can be used by other operations without waiting. However, it is by far the slowest insert method and should only be used for small data volumes.
• The Upload mode uses the multipart upload API for uploading data. This method is intended for performing low-cost medium to large data loads within a reasonable time. When using this mode the connector will upload the inserted rows to Google-managed storage and then create a load job for them. This job will execute and the connector can either wait for it (see WaitForBatchResults) or let it run asyncronously. Waiting for the job will report any errors that the job enconters but will take more time. Determining if the job failed without waiting for it requires manually checking the job status via the job stored procedures.
• The GCSStaging mode is the same as Upload except that it uses your Google Cloud Storage acccount to store staged data instead of Google-managed storage. The connector cannot act asynchronously in this mode because it must delete the file after the load is complete, which means that WaitForBatchResults has no effect.
  Because this depends on external data, you must set the GCSBucket to the name of your bucket and ensure that Scope (a space delimited set of scopes) contains at least the scopes https://www.googleapis.com/auth/bigquery and https://www.googleapis.com/auth/devstorage.read_write. The devstorage scope used for GCS also requires that you connect using a service account because Google BigQuery does not allow user accounts to use this scope.

In addition to bulk INSERTs, the connector also supports performing bulk UPDATE and DELETE operations. This requires the connector to upload the data containing the filters and rows to set into a new table in BigQuery, then perform a MERGE between the two tables and drop the temporary table. InsertMode determines how the rows are inserted into the temporary table but the Streaming and DML modes are not supported.

In most cases the connector can determine what columns need to be part of the SET vs. WHERE clauses of a bulk update. If you receive an error like "Primary keys must be defined for bulk UPDATE support," you can use PrimaryKeyIdentifiers to tell the connector what columns to treat as keys. In an update the values of key columns are used only to find matching rows and cannot be updated.

Minimum Required Roles for Service Accounts

The following roles allow SELECT queries to work with a service account:

• BigQuery Data Viewer (roles/bigquery.dataViewer): read data and metadata
• BigQuery Filtered Data Viewer (roles/bigquery.filteredDataViewer): view filtered table data
• BigQuery Job User (roles/bigquery.jobUser): run jobs, including queries, within the project

General Changes

Date	Version	Source	Category	Type	Description
2026-05-28	26.0.9644	Google BigQuery	Metadata	Changed	Changed the data type of the PageSize connection property from string to integer.
2026-05-27	26.0.9643	General	Connection	Removed	Removed the deprecated ReplaceInvalidTypesWithNull connection property. Use the ReplaceInvalidValuesWithNull property instead.
2026-05-27	26.0.9643	Google BigQuery	Connection	Removed	Removed the UseStreamingInserts connection property, which was deprecated in favor of the InsertMode property. InsertMode now fully controls insert behavior (streaming, DML, upload, and GCS).
2026-05-22	26.0.9638	Python		Removed	Remove support for Intel x64 architecture on macOS
2026-05-07	26.0.9623	General	Data Model	Added	Added the ColumnCapabilities column to the sys_tablecolumns system table. This column is a bit mask denoting the column's write capabilities.
2026-05-07	26.0.9623	Python		Changed	Updated embedded JRE to jre-17.0.19+10 (Linux x64 / MacOs x64).
2026-04-15	26.0.9601	General	Query Exec	Changed	String comparisons using GREATER, LESS, and CONTAINS operators are now case-insensitive by default.
2026-03-06	25.0.9561	Google BigQuery	Query Exec	Changed	Due to a change in the Google Big Query API, Legacy SQL will no longer available for new organizations after June 1, 2026. Therefore we have changed our recommendation for setting the UseLegacySQL property to "no longer recommended". The property has not been deprecated since it may still be available in older organizations.
2026-02-05	25.0.9532	Google BigQuery		Added	Added the PassthroughParameters connection property. When used with QueryPassthrough set to true, parameters are pushed to the server instead of being resolved client-side.
2026-01-13	25.0.9509	General		Added	Added support for the REGEXP_REPLACE() string function.
2025-12-21	25.0.9486	Python		Added	Added support for custom loggers in Python connectors on Linux and macOS.
2025-12-05	25.0.9470	General		Added	Added support for the INSERT INTO SELECT statement, with driver-side execution for providers that do not support the operation natively.
2025-11-05	25.0.9440	Google BigQuery		Added	Added the Location column to the Datasets view.
2025-10-30	25.0.9434	Python		Changed	Updated embedded JRE to jre-17.0.17+10 (Linux x64 / MacOs x64).
2025-10-06	25.0.9410	General		Added	Support for parsing datetime formats using ".S" and ",S" for milliseconds and nanoseconds.
2025-09-12	25.0.9386	General		Added	Added the IsInsertable, IsUpdateable, and IsDeleteable columns to the sys_tables table.
2025-09-10	25.0.9384	General		Changed	All columns in statically defined Views are now reported as read-only.
2025-09-10	25.0.9384	Google BigQuery		Added	Added support for the AzureWorkloadIdentity AuthScheme via Azure.
2025-09-03	25.0.9377	General		Changed	Corrected the behavior when IN criteria with NULL values are used in the projection part. It now returns NULL instead of 0. For example, "NULL IN (1,2)" returns "NULL".
2025-09-01	25.0.9375	General		Added	Added support for using the CAST function with infinity values. This function can cast "inf" and "-inf" to DOUBLE, FLOAT, or REAL.
2025-08-21	25.0.9364	General		Changed	Report behavior change: Fixed inconsistent string value comparisons in non-table queries. For example, "SELECT 'A' = 'a'" previously returned false, but it now returns true.
2025-08-13	25.0.9356	General		Changed	Changed the maximum number of pages held in memory from 15 to 5 for the page providers to decrease heap usage.
2025-07-24	25.0.9336	Google BigQuery		Changed	Replaced the PrivateEndpointName connection property with connection properties corresponding to each endpoint: PrivateEndpointNameAccessTokenUrl, PrivateEndpointNameAuthUrl, PrivateEndpointNameBigQuery, PrivateEndpointNameCloudStorage, PrivateEndpointNameStorage, and PrivateEndpointNameSts.
2025-07-07	25.0.9319	Python		Removed	Removed the 32-bit version of Windows Python.
2025-07-02	25.0.9314	Python		Removed	Removed support for Python 3.9.
2025-06-25	25.0.9307	General		Removed	Removed the "ADLS Gen 1" value from the ConnectionType property.
2025-06-25	25.0.9307	Python		Added	Added support for Python 3.13 in Windows, Linux, and Mac editions.
2025-06-25	25.0.9307	Python		Removed	Removed support for Python 3.8 as it is no longer supported.
2025-06-20	25.0.9302	General		Added	Created the following functions: TEXT_ENCODE: encodes a string into a different charset (UTF8 → UTF7 and returns a binary array as the result). TEXT_DECODE: takes a binary array and decodes it back into a string when provided the charset. BASE64_ENCODE: takes a binary array and encodes it as a base64 string (varchar). BASE64_DECODE: takes a base 64-encoded string and decodes it into a binary array.
2025-06-18	25.0.9300	General		Changed	The internal code for exception handling has been refactored. Exception messages returned during certain error conditions may now have different wording or formatting.
2025-06-03	25.0.9285	Google BigQuery		Removed	The deprecated stored procedure CreateSchema has been removed. We recommend using server-side views in BigQuery.
2025-05-27	25.0.9278	General		Removed	Removed the "Proprietary" enum option from ProxyAuthscheme.
2025-05-12	25.0.9263	Python		Changed	Updated embedded JRE to jre-17.0.15+6 (Linux x64 / MacOS x64) and jre-17.0.15+6 (MacOS aarch64).
2025-04-29	25.0.9250	Google BigQuery		Changed	The Projects, Datasets, PartitionsList, and PartitionsValues views have moved back to the CData catalog.
2025-02-15	24.0.9177	General		Added	Added support for converting unsigned integer types to the nearest signed data type that has enough precision to hold the unsigned value.This is done for JDBC only because it does not have support for unsigned data types.
2025-01-29	24.0.9160	Google BigQuery		Added	Added the SupportCaseSensitiveTables connection property. Enable this property to allow multiple tables with the same name, but different casing in the listed tables.
2024-11-27	24.0.9097	General		Added	Added ThreadId to LogModule output. Logfile lines now include the Thread ID associated with the action being performed.
2024-10-17	24.0.9056	Google BigQuery		Added	Added support for Workload Identity Federation using AWS accounts.
2024-09-04	24.0.9013	Google BigQuery		Added	Added the DelegatedServiceAccounts connection property. When used in combination with the AuthScheme GCPInstanceAccounts, this property allows you to specify a space-delimited list of delegated service accounts. The default service account email assigned to the project will act as the requesting service account.
2024-06-05	24.0.8922	Python		Added	Added support for Python 3.12.
2024-05-09	24.0.8895	General		Changed	The ROUND function previously did not accept negative precision values. That feature has now been restored.
2024-03-15	23.0.8840	General		Added	Created a new SQL function called STRING_COMPARE that provides java's String.compare() ability to SQL queries. Returns a number representative of the compared value of two strings
2023-11-29	23.0.8733	General		Changed	The ROUND function doesn't accept the negative precision values anymore.
2023-11-29	23.0.8733	General		Changed	The returning types of the FDMonth, FDQuarter, FDWeek, LDMonth, LDQuarter, LDWeek functions are changed from Timestamp to Date. The return type of the ABS function will be consistent with the parameter value type.
2023-11-28	23.0.8732	General		Added	Added the HMACSHA256 formatter to allow for secrets to be decoded if it is in base64 format
2023-08-29	23.0.8641	Python		Added	Added support for SQLAlchemy 2.0.
2023-06-20	23.0.8571	General		Added	Added the new sys_lastresultinfo system table.
2023-05-19	23.0.8539	Python		Added	Added support for Python 3.11 on Windows, Linux and Mac.
2023-05-16	23.0.8536	Python		Removed	Removed support for Python 3.7 on Windows and Linux
2023-04-25	23.0.8515	General		Removed	Removed support for the SELECT INTO CSV statement. The core code doesn't support it anymore.
2023-03-24	22.0.8483	Google BigQuery		Added	Added support for the INTERVAL data type within all query types. INTERVAL values are represented as text and given to the driver in BigQuery SQL's native format.
2023-01-04	22.0.8404	Google BigQuery		Added	Added support for the JSON data type, both within queries as well as DML insert operations. It has most of the same limitations as other aggregate types like array. It is also supported as a type name for CAST(col AS JSON) as well as CREATE TABLE t(col JSON)
2022-12-14	22.0.8383	General		Changed	Added the Default column to the sys_procedureparameters table.
2022-11-15	22.0.8354	Python		Changed	Updated embedded JRE to jre8u345-b01(Linux x64 / MacOS x64) and jre-17.0.5+8(MacOS aarch64).
2022-11-03	22.0.8342	Google BigQuery		Added	Added support for performing a bulk UPDATE without having to specify primary keys via PrimaryKeyIdentifiers or schema files. As long as the platform native bulk interface is used (e.g. addBatch / executeBatch in JDBC) the provider can determine what goes into the SET vs. the WHERE clause.
2022-09-30	22.0.8308	General		Changed	Added the IsPath column to the sys_procedureparameters table.
2022-09-28	22.0.8306	Google BigQuery		Added	Added support for the FlattenObjects option. By default it is enabled and STRUCT values are expanded as normal, but when disabled STRUCT values are reported as JSON aggregates in the same way ARRAY values are.
2022-06-06	22.0.8192	Google BigQuery		Added	Added support for MERGE statements. The syntax is a subset of what BigQuery natively supports, including everything except the MATCHED BY SOURCE, DEFAULT and WHEN MATCHED AND clauses.
2022-05-18	22.0.8173	Python		Added	Added support for Python 3.10 on Windows, Linux, and Mac Added support for Python 3.9 on Mac Added support for Mac M1
2022-05-18	22.0.8173	Python		Removed	Removed support for Python 3.6 on Windows and Linux
2022-04-27	22.0.8152	Google BigQuery		Added	Added support for the SAFE_CAST function. Due to syntax limitations, it must be written this way when passthrough mode is disabled (the default): SAFE_CAST(somecol, 'INTEGER') . It supports SQL type names (VARCHAR, INT, etc.) as well as BigQuery type names (STRING, INT64, etc.)
2022-04-11	21.0.8136	Google BigQuery		Changed	Changed what views are retrieved depending on the connection property UseLegacySQL. Connections using standard SQL will not report views that are only available in legacy SQL, and the same goes for standard views in legacy SQL connections.
2022-03-01	21.0.8095	Google BigQuery		Added	Added support for CREATE VIEW AS statements.
2022-02-18	21.0.8084	Google BigQuery		Added	Added support for ALTER TABLE. Due to BigQuery limitations, the only supported operations are ADD COLUMN, DROP COLUMN, ALTER COLUMN and (table) RENAME TO.
2022-01-26	21.0.8061	Google BigQuery		Added	Added support for querying snapshot tables. Append it to the AllowedTableTypes connection property (along with other allowed table types) to make them show up.
2022-01-13	21.0.8048	Google BigQuery		Added	Added support for the NATIVEQUERY table function. This function can be used after a FROM to execute a query using BigQuery-native SQL. For example, SELECT * FROM NATIVEQUERY('SELECT * FROM UNNEST([1,2,3]) AS a') will execute the inner query in BigQuery directly and return the results. This will work even in tools which are not normally compatible with QueryPassthrough=true.
2022-01-04	21.0.8039	Google BigQuery		Added	Added support for querying materialized views.
2021-12-20	21.0.8024	Google BigQuery		Added	Added support for using the BigQuery TABLESAMPLE without enabling QueryPassthrough. If TableSamplePercent is set then the random row selection happens server-side for each table used in a query.
2021-12-10	21.0.8014	Google BigQuery		Changed	Migrate to the latest version of the Storage API.
2021-10-21	21.0.7964	Google BigQuery		Added	Added support for reading policy tags from the Data Catalog service. These are exposed using the PolicyTags column on sys_tablecolumns.
2021-10-20	21.0.7963	Google BigQuery		Added	Added support for parameterized data types, both on the read side with and without UseStorageAPI as well as on the metadata side. The metadata is only reported on the table itself and not in queries, since BigQuery only enforces length/precision/scale when the data is written.
2021-09-02	21.0.7915	General		Added	Added support for the STRING_SPLIT table-valued function in the CROSS APPLY clause.
2021-09-02	21.0.7915	Google BigQuery		Added	Added support for bulk UPDATE and DELETE operations. These make better use of BigQuery's DML query limits because each batch is just one MERGE operation. They are also faster because they use the bulk upload methods to send the list of rows to be changed. Primary keys must be defined on the tables to use DELETE.
2021-08-09	21.0.7891	Google BigQuery		Added	Support for staging data in all formats allowed by BigQuery. JSON and Avro were already supported, this adds options that allow uploading data stored in the CSV, Parquet and ORC formats.
2021-08-07	21.0.7889	General		Changed	Added the KeySeq column to the sys_foreignkeys table.
2021-08-06	21.0.7888	General		Changed	Added the new sys_primarykeys system table.
2021-07-28	21.0.7879	Google BigQuery		Added	Added support for reading TIME, DATETIME and TIMESTAMP data at the full precision supported by BigQuery. We now report microsecond precision in .NET-based editions and millisecond precision in Java-based editions.
2021-07-23	21.0.7874	General		Changed	Updated the Literal Function Names for relative date/datetime functions. Previously, relative date/datetime functions resolved to a different value when used in the projection as opposed to the predicate. For example: SELECT LAST_MONTH() AS lm, Col FROM Table WHERE Col > LAST_MONTH(). Formerly, the two LAST_MONTH() methods would resolve to different datetimes. Now, they will match. As a replacement for the previous behavior, the relative date/datetime functions in the criteria may have an 'L' appended to them. For example: WHERE col > L_LAST_MONTH(). This will continue to resolve to the same values that were previously calculated in the criteria. Note that the "L_" prefix will only work in the predicate - it not available for the projection.
2021-07-20	21.0.7871	Google BigQuery		Added	Added support for staging data with Google Cloud Storage when performing inserts. This is similar to the existing Upload insert mode, but transfers the row data into GCS and then creates a load job referencing it instead of performing the upload directly into BigQuery.
2021-06-18	21.0.7839	Google BigQuery		Added	Added support for the GOOGLEJSONBLOB JWT certificate type. This works like the existing GOOGLEJSON certificate type except that the certificate is provided as JSON text instead of as a file path.
2021-05-27	21.0.7817	Google BigQuery		Added	Added support for automatically reconnecting on Storage connections that have long idle times. This is useful most for ETL applications and other scenarios where the consumer reading data out of the driver is expected to be slower than the driver itself. In these cases the connection can be dropped by the other side (either by the Storage API itself or network appliances like firewalls and proxies) leading to timeouts. To avoid this the driver will restart the read from the same position using a new connection.
2021-04-25	21.0.7785	General		Added	Added support for handling client side formulas during insert / update. For example: UPDATE Table SET Col1 = CONCAT(Col1, " - ", Col2) WHERE Col2 LIKE 'A%'
2021-04-23	21.0.7783	General		Changed	Updated how display sizes are determined for varchar primary key and foreign key columns so they will match the reported length of the column.
2021-04-16	21.0.7776	General		Added	Non-conditional updates between two columns is now available to all drivers. For example: UPDATE Table SET Col1=Col2
2021-04-16	21.0.7776	General		Changed	Reduced the length to 255 for varchar primary key and foreign key columns.
2021-04-16	21.0.7776	General		Changed	Updated implicit and metadata caching to improve performance and support for multiple connections. Old metadata caches are not compatible - you need to generate new metadata caches if you are currently using CacheMetadata.
2021-04-16	21.0.7776	General		Changed	Updated index naming convention to avoid duplicates.
2021-04-05	21.0.7765	Google BigQuery		Added	Added support for data unnesting. This mode performs client-side expansion of array data across multiple rows in a way that emulates the compact preview in the BigQuery UI. Server-side support of some options is limited in this mode (LIMIT and OFFSET) but otherwise behaves like the nested mode with the exception that it always outputs flat data and never outputs aggregates.
2021-04-05	21.0.7765	Google BigQuery		Changed	Updated the default value for WaitForBatchResults to true since not checking the job status at smaller batch sizes can lead to lost jobs.
2021-04-05	21.0.7765	Google BigQuery		Deprecated	Deprecated the UseStreamingInserts connection property in favor of InsertMode. The default for InsertMode is Streaming.
2021-03-04	21.0.7733	Google BigQuery		Removed	Removed the TempTableDataset and TempTableExpirationTime connection properties. These properties have been defunct since we moved to using the BigQuery-managed query cache for large resultsets.
2021-01-29	20.0.7699	Google BigQuery		Added	Added support for the BIGNUMERIC type, which is twice the size of the existing numeric type. JDBC supports this type at full precision while .NET requires using the connection property IgnoreTypes=decimal because the full precision of BIGNUMERIC (as with regular NUMERIC) is too high for System.Decimal.
2021-01-08	20.0.7678	Google BigQuery		Added	Added support for using the upload API for batch inserts. This is an API that is slower than the streaming API for large volumes of data, but is free to use and doesn't have the same buffering delays that streaming does. It is also async by default although the option to wait on the batch is available via the new WaitForBatchResults connection property.
2020-12-23	20.0.7662	Google BigQuery		Added	Added support for unnesting metadata with the Storage API.
2020-12-21	20.0.7660	Google BigQuery		Added	Added support for unnesting metadata with the REST API. By enabling UnnestArrays, metadata for fields within REPEATED RECORD types is returned as separate columns instead of generating JSON aggregates. These values can be selected within queries but are currently treated as NULL.
2020-12-07	20.0.7646	Google BigQuery		Added	Added support for injecting partition filters into queries that require them but do not have them, if the user enables the InsertPartitionFilterOption. This modifies queries against partitioned tables that require a partition filter so that they always contain a valid partition filter, which allows queries like simple SELECT * FROM t to work. Currently all partitions are selected by the generated filter.

This section provides a walk-through for writing Google BigQuery data access code in Python script.

For more information on the available data source entities and how to query them with SQL, see Data Model. For the SQL syntax, see SQL Compliance.

Connecting from Code

For information on how to deploy the connector and configure the connection to Google BigQuery, see Package Installation and Establishing a Connection.

For information on how to connect with the googlebigquery.connector module and its related classes, see Connecting.

Executing SQL

The connection's cursor object is used to directly execute SQL queries. For information on how to execute SELECT statements and process the returned result sets, see Querying Data. For information on to modify the data in Google BigQuery with INSERT, UPDATE, and DELETE statements, see Modifying Data .

Executing Stored Procedures

You can call stored procedures by using the EXECUTE statement. For further information, see Calling Stored Procedures.

Batch Processing

For information about how to modify several rows of Google BigQuery data at once using parameterized INSERT, UPDATE, and DELETE statements, see Batch Processing.

Connecting with the cdata.googlebigquery Module:

The connector's module is used directly to establish a connection with the data source. It does this by using a connection string as its argument. For example:

import cdata.googlebigquery as mod
conn = mod.connect("InitiateOAuth=GETANDREFRESH;ProjectId=NameOfProject;DatasetId=NameOfDataset;")

Once the connection is created, you can use it to execute subsequent SQL queries.

After connecting as described in Connecting, you can use the open connection to execute SQL statements.

Executing Queries

To execute SQL statements that return data, use the execute() method. Once a query is executed, the result set is fetched from the cursor. This result set can then be iterated over to process the records individually.

For example:

cur = conn.execute("SELECT actor.attributes.email, repository.name FROM [publicdata].[samples].github_nested")
rs = cur.fetchall()
for row in rs:
	print(row)

Parameterized Queries

Various Python collections, such as arrays and tuples, can act as additional arguments for the execute() method. This enables you to parameterize the queries executed and help to prevent SQL Injection.

For example:

cmd = "SELECT actor.attributes.email, repository.name FROM [publicdata].[samples].github_nested WHERE repository.name = ?"
params = ["EntityFramework"]
cur = conn.execute(cmd, params)
rs = cur.fetchall()
for row in rs:
	print(row)

The connection is also used to issue INSERT, UPDATE, and DELETE commands to the data source. Parameters can be used with these statements if desired.

Note that the connector does not support transactions. As with normal write operations, all SQL statements executed by this connector affect the data source immediately. Call the connection's commit() method following the execution.

Insert

The following example adds a new record to the table:

cmd = "INSERT INTO [publicdata].[samples].github_nested (actor.attributes.email, repository.name) VALUES (?, ?)"
params = ["EntityFramework", "CoreCLR"]
cur = conn.execute(cmd, params)
print("Records affected: ", cur.rowcount)

Update

The following example modifies an existing record in the table:

cmd = "UPDATE [publicdata].[samples].github_nested SET repository.name = ? WHERE Id = ?"
params = ["CoreCLR", "1"]
cur = conn.execute(cmd, params)
print("Records affected: ", cur.rowcount)

Delete

The following example removes an existing record from the table:

cmd = "DELETE FROM [publicdata].[samples].github_nested WHERE Id = ?"
params = ["1"]
cur = conn.execute(cmd, params)
print("Records affected: ", cur.rowcount)

You can execute stored procedures using either the execute() or callproc() method of the connection.

Calling Stored Procedures Using Execute()

When you call stored procedures by issuing EXECUTE commands, the stored procedure arguments are parameterized. For example:

cmd = "EXECUTE RefreshOAuthAccessToken OAuthRefreshToken = ?"
params = ["39EKAH6QTB82LZDGOKEX2FY1IJSBF6"]
conn.execute(cmd, params)

Calling Stored Procedures Using Callproc()

When you call stored procedured by issuing the callproc() method, the stored procedure arguments are a procedure name and a list of parameters. For example:

cur = conn.cursor()
params = ["39EKAH6QTB82LZDGOKEX2FY1IJSBF6"]
cur.callproc("RefreshOAuthAccessToken", params)

This Python connector also supports writing to the data source via batch processing, using the cursor object's executemany() method. This requires both a SQL statement string and a data frame of values that act as a series of parameters for executing the SQL statement.

Note that the connector does not support transactions. As with normal write operations, all SQL statements executed by this connector affect the data source immediately. Call the connection's commit() method following the execution.

Insert

The following example adds new records to the table:

cur = conn.cursor()
cmd = "INSERT INTO [publicdata].[samples].github_nested (actor.attributes.email, repository.name) VALUES (?, ?)"
params = [["EntityFramework", "CoreCLR"], ["EntityFramework", "CoreCLR"]]
cur.executemany(cmd, params)
print("Records affected: ", cur.rowcount)

Update

The following example modifies existing records in the table:

cur = conn.cursor()
cmd = "UPDATE [publicdata].[samples].github_nested SET repository.name = ? WHERE Id = ?"
params = [["CoreCLR", "1"], ["CoreCLR", "1"]]
cur.executemany(cmd, params)
print("Records affected: ", cur.rowcount)

Delete

The following example removes existing records from the table:

cur = conn.cursor()
cmd = "DELETE FROM [publicdata].[samples].github_nested WHERE Id = ?"
params = [["1"], ["1"]]
cur.executemany(cmd, params)
print("Records affected: ", cur.rowcount)

The connector is integrated with other tools and packages within Python.

Python Integration Guides

The following sections show how to create and use connections with the connector in common packages in Python:

• From SQLAlchemy
• From Pandas
• From Matplotlib
• From Petl

Complete List of Google BigQuery Integration Quickstarts

For information on connecting from other applications, see Google BigQuery integration guides.

The CData Python Connector for Google BigQuery includes a Dialect class that enables integration with SQLAlchemy. Bear in mind that several aspects of connector functionality are not currently supported in SQLAlchemy 2.0 or above. If necessary, downgrade SQLAlchemy to version 1.4 or 1.3 before using this connector.

The following sections detail various aspects of this integration:

Connecting From SQLAlchemy

To construct a URL with which SQLAlchemy loads and uses the appropriate connector automatically, see Connecting

Reflecting Metadata With SQLAlchemy

To learn how to model Google BigQuery tables with mapped classes, see Reflecting Metadata.

Querying Data From SQLAlchemy

To learn how to use mapped classes to query the associated tables, see Querying Data.

Modifying Data From SQLAlchemy

The connector provides INSERT/UPDATE/DELETE functionality in SQLAlchemy. To learn how to call the session's execute() method to affect the data in the data source, see Modifying Data.

Connecting With a Dialect URL

Establishing a connection using SQLAlchemy requires a specific URL format.

from sqlalchemy import create_engine
engine = create_engine("googlebigquery:///?InitiateOAuth=GETANDREFRESH;ProjectId=NameOfProject;DatasetId=NameOfDataset;")

For SQLAlchemy 2.0, the dialect name is googlebigquery_2. To establish a connection, use the following URL format:

from sqlalchemy import create_engine
engine = create_engine("googlebigquery_2:///?InitiateOAuth=GETANDREFRESH;ProjectId=NameOfProject;DatasetId=NameOfDataset;")

SQLAlchemy can act as an Object-relational Map (ORM). This enables you to treat records of a database table as instantiable records. To leverage this functionality, you must reflect the underlying metadata in one of the following ways.

Note: The following examples employ SQLAlchemy 1.4.

Modeling Data Using a Mapping Class

Use "sqlalchemy.ext.declarative.declarative_base" to declare a mapping class for the table you wish to model in the ORM. A known table in the data model is modeled either partially or completely, as shown in the following example:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class [publicdata].[samples].github_nested(Base):
	__tablename__ = "[publicdata].[samples].github_nested"
	Id = Column(String, primary_key=True)
	actor.attributes.email = Column(String)
	repository.name = Column(String)

Automatically Reflecting Metadata

Rather than mapping tables manually, SQLAlchemy can discover the metadata for one or more tables automatically. To accomplish this across the entire data model, use automap_base:

from sqlalchemy import MetaData
from sqlalchemy.ext.automap import automap_base
meta = MetaData()
abase = automap_base(metadata=meta)
abase.prepare(autoload_with=engine)
[publicdata].[samples].github_nested = abase.classes.[publicdata].[samples].github_nested

You can also reflect a single table with an inspector. When reflecting this way, providing a list of specific columns to map is optional:

from sqlalchemy import MetaData, Table
from sqlalchemy import inspect
meta = MetaData()
insp = inspect(engine)
[publicdata].[samples].github_nested_table = Table("[publicdata].[samples].github_nested", meta)
insp.reflect_table([publicdata].[samples].github_nested_table, ["Id","repository.name"])

After you use the steps in Connecting to connect, and use one of the methods in Reflecting Metadata to reflect some of the metadata, you can use a session object to query data.

Querying Data Using the Query Method

If the mapping class has been prepared, use it with a session object to query the data source. After binding the engine to the session, provide the mapping class to the session's query method.

For example:

engine = create_engine("googlebigquery:///?InitiateOAuth=GETANDREFRESH;ProjectId=NameOfProject;DatasetId=NameOfDataset;")
factory = sessionmaker(bind=engine)
session = factory()
for instance in session.query([publicdata].[samples].github_nested).filter_by(repository.name="EntityFramework"):
	print("Id: ", instance.Id)
	print("actor.attributes.email: ", instance.actor.attributes.email)
	print("repository.name: ", instance.repository.name)
	print("---------")

Querying Data Using the Execute Method

The session object can also run the query with the execute() method alongside the appropriate Table object. Assuming you have an active session, the following is just as viable:

[publicdata].[samples].github_nested_table = [publicdata].[samples].github_nested.metadata.tables["[publicdata].[samples].github_nested"]
for instance in session.execute([publicdata].[samples].github_nested_table.select().where([publicdata].[samples].github_nested_table.c.repository.name == "EntityFramework")):
	print("Id: ", instance.Id)
	print("FullName: ", instance.Name)
	print("City: ", instance.BillingCity)
	print("---------")

Implicit Joining

If mapped classes of related Google BigQuery objects have a singular foreign key relationship, the classes are implicitly joined. After importing the necessary objects, a relationship is established between your two mapped classes, as in the example below:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, DateTime, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship

Base = declarative_base()
class Contact(Base):
	__tablename__ = "Contact"
	Id = Column(Integer, primary_key=True)
	Name = Column(String)
	Email = Column(String)
	BirthDate = Column(DateTime)
	AccountId = Column(String, ForeignKey("Account.Id"))
	Account_Link = relationship("Account", back_populates="Contact_Link")

class Account(Base):
	__tablename__ = "Account"
	Id = Column(String, primary_key=True)
	Name = Column(String)
	BillingCity = Column(String)
	NumberOfEmployees = Column(Integer)
	Contact_Link = relationship("Contact", order_by=Contact.Id, back_populates="Account_Link")

Once the relationship is established, the tables are queried simultaneously using the session's query() method. For example:

rs = session.query(Account, Contact).filter(Account.Id == Contact.AccountId)
for Ac, Ct in rs:
  print("AccountId: ", Ac.Id)
  print("AccountName: ", Ac.Name)
  print("ContactId: ", Ct.Id)
  print("ContactName: ", Ct.Name)

Other Join Forms

In situations where mapped classes have either no foreign keys or multiple foreign keys, you may need different forms of the JOIN query to accommodate them. Using the earlier classes as examples, the following JOIN queries are possible as well:

• Explicit condition (necessary if there are no foreign keys in your mapped classes):
  

  rs = session.query(Account, Contact).join(Contact, Account.Id == Contact.AccountId)
  for Ac, Ct in rs:

• Left-to-right relationship:
  

  rs = session.query(Account, Contact).join(Account.Contact_Link)
  for Ac, Ct in rs:

• Left-to-right relationship with explicit target:
  

  rs = session.query(Account, Contact).join(Contact, Account.Contact_Link)
  for Ac, Ct in rs:

• String form of a left-to-right relationship:
  

  rs = session.query(Account, Contact).join("Contact_Link")
  for Ac, Ct in rs:

SQLAlchemy ORM also exposes support for other clauses in SQL, such as ORDER BY, GROUP BY, LIMIT, and OFFSET. All of these are supported by this connector:

ORDER BY

The following example sorts by a specified column using the session object's query() method:

rs = session.query([publicdata].[samples].github_nested).order_by([publicdata].[samples].github_nested.repository.watchers)
for instance in rs:
	print("Id: ", instance.Id)
	print("actor.attributes.email: ", instance.actor.attributes.email)
	print("repository.name: ", instance.repository.name)
	print("---------")

You can also use the session object's execute() method perform an ORDER BY. For example:

rs = session.execute([publicdata].[samples].github_nested_table.select().order_by([publicdata].[samples].github_nested_table.c.repository.watchers))
for instance in rs:

GROUP BY

The following example uses the session object's query() method to group records with a specified column:

rs = session.query(func.count([publicdata].[samples].github_nested.Id).label("CustomCount"), [publicdata].[samples].github_nested.actor.attributes.email).group_by([publicdata].[samples].github_nested.actor.attributes.email)
for instance in rs:
	print("Count: ", instance.CustomCount)
	print("actor.attributes.email: ", instance.actor.attributes.email)
	print("---------")

You can also use the session object's execute() method to perform a GROUP BY:

rs = session.execute([publicdata].[samples].github_nested_table.select().with_only_columns([func.count([publicdata].[samples].github_nested_table.c.Id).label("CustomCount"), [publicdata].[samples].github_nested_table.c.actor.attributes.email]).group_by([publicdata].[samples].github_nested_table.c.actor.attributes.email))
for instance in rs:

LIMIT and OFFSET

The following example uses the session object's query() method to skip the first 100 records and fetch the following 25:

rs = session.query([publicdata].[samples].github_nested).limit(25).offset(100)
for instance in rs:
	print("Id: ", instance.Id)
	print("actor.attributes.email: ", instance.actor.attributes.email)
	print("repository.name: ", instance.repository.name)
	print("---------")

You can also use the session object's execute() method to set a LIMIT or OFFSET:

rs = session.execute([publicdata].[samples].github_nested_table.select().limit(25).offset(100))
for instance in rs:

Certain aggregate functions can also be used within SQLAlchemy by using the func module.

To import this module, execute:

from sqlalchemy.sql import func

Once func is imported, the following aggregate functions are available:

COUNT

The following example counts the number of records in a set of groups using the session object's query() method.

rs = session.query(func.count([publicdata].[samples].github_nested.Id).label("CustomCount"), [publicdata].[samples].github_nested.actor.attributes.email).group_by([publicdata].[samples].github_nested.actor.attributes.email)
for instance in rs:
	print("Count: ", instance.CustomCount)
	print("actor.attributes.email: ", instance.actor.attributes.email)
	print("---------")

You can also execute COUNT using the session object's execute() method:

rs = session.execute([publicdata].[samples].github_nested_table.select().with_only_columns([func.count([publicdata].[samples].github_nested_table.c.Id).label("CustomCount"), [publicdata].[samples].github_nested_table.c.actor.attributes.email])group_by([publicdata].[samples].github_nested_table.c.actor.attributes.email))
for instance in rs:

SUM

This example calculates the cumulative amount of a numeric column in a set of groups.

rs = session.query(func.sum([publicdata].[samples].github_nested.repository.watchers).label("CustomSum"), [publicdata].[samples].github_nested.actor.attributes.email).group_by([publicdata].[samples].github_nested.actor.attributes.email)
for instance in rs:
	print("Sum: ", instance.CustomSum)
	print("actor.attributes.email: ", instance.actor.attributes.email)
	print("---------")

You can also invoke SUM using the session object's execute() method.

rs = session.execute([publicdata].[samples].github_nested_table.select().with_only_columns([func.sum([publicdata].[samples].github_nested_table.c.repository.watchers).label("CustomSum"), [publicdata].[samples].github_nested_table.c.actor.attributes.email]).group_by([publicdata].[samples].github_nested_table.c.actor.attributes.email))
for instance in rs:

AVG

This example uses the session object's query() method to calculate the average amount of a numeric column in a set of groups:

rs = session.query(func.avg([publicdata].[samples].github_nested.repository.watchers).label("CustomAvg"), [publicdata].[samples].github_nested.actor.attributes.email).group_by([publicdata].[samples].github_nested.actor.attributes.email)
for instance in rs:
	print("Avg: ", instance.CustomAvg)
	print("actor.attributes.email: ", instance.actor.attributes.email)
	print("---------")

You can also use the session object's execute() method to invoke AVG:

rs = session.execute([publicdata].[samples].github_nested_table.select().with_only_columns([func.avg([publicdata].[samples].github_nested_table.c.repository.watchers).label("CustomAvg"), [publicdata].[samples].github_nested_table.c.actor.attributes.email]).group_by([publicdata].[samples].github_nested_table.c.actor.attributes.email))
for instance in rs:

MAX and MIN

This example finds the maximum value and minimum value of a numeric column in a set of groups.

rs = session.query(func.max([publicdata].[samples].github_nested.repository.watchers).label("CustomMax"), func.min([publicdata].[samples].github_nested.repository.watchers).label("CustomMin"), [publicdata].[samples].github_nested.actor.attributes.email).group_by([publicdata].[samples].github_nested.actor.attributes.email)
for instance in rs:
	print("Max: ", instance.CustomMax)
	print("Min: ", instance.CustomMin)
	print("actor.attributes.email: ", instance.actor.attributes.email)
	print("---------")

You can also use the session object's execute() method to invoke MAX and MIN:

rs = session.execute([publicdata].[samples].github_nested_table.select().with_only_columns([func.max([publicdata].[samples].github_nested_table.c.repository.watchers).label("CustomMax"), func.min([publicdata].[samples].github_nested_table.c.repository.watchers).label("CustomMin"), [publicdata].[samples].github_nested_table.c.actor.attributes.email]).group_by([publicdata].[samples].github_nested_table.c.actor.attributes.email))
for instance in rs:

Commands can be executed individually by the session with a call to "execute()".

Obtaining the Table Object

The query supplied to this method is constructed using the associated Table object of a mapped class. This Table object is obtained from the mapped class's metadata field, as below:

[publicdata].[samples].github_nested_table = [publicdata].[samples].github_nested.metadata.tables["[publicdata].[samples].github_nested"]

Once the table object is obtained, the write operations are executed in the following ways. The queries are executed immediately without the need for a call to "commit()":

Insert

The following example adds a new record to the table:

session.execute([publicdata].[samples].github_nested_table.insert(), {"actor.attributes.email": "EntityFramework", "repository.name": "CoreCLR"})

Update

The following example modifies an existing record in the table:

session.execute([publicdata].[samples].github_nested_table.update().where([publicdata].[samples].github_nested_table.c.Id == "1").values(actor.attributes.email="EntityFramework", repository.name="CoreCLR"))

Delete

The following example removes an existing record from the table:

session.execute([publicdata].[samples].github_nested_table.delete().where([publicdata].[samples].github_nested_table.c.Id == "1"))

When combined with the connector, Pandas can be used to generate data frames that contain your Google BigQuery data. Once created, a data frame can be passed to various other Python packages.

Connecting

Pandas relies on an SQLAlchemy engine to execute queries. Before you can use Pandas you must import it:

import pandas as pd
from sqlalchemy import create_engine
engine = create_engine("googlebigquery:///?InitiateOAuth=GETANDREFRESH;ProjectId=NameOfProject;DatasetId=NameOfDataset;")

Querying Data

In Pandas, SELECT queries are provided in a call to the read_sql() method, alongside a relevant connection object. Pandas executes the query on that connection, and returns the results in the form of a data frame, which can be used for a variety of purposes.

df = pd.read_sql("""
	SELECT
	   actor.attributes.email,
	   repository.name,
     $exNumericCol;
	FROM [publicdata].[samples].github_nested;""", engine)
print(df)

Modifying Data

To insert new records into a table, create a new data frame, and define its fields accordingly. When that is done, call to_sql() on the data frame to perform the INSERT operation with the connector, as shown in the example below. You must set the "if _exists" argument to "append" to prevent Pandas from attempting building the table from scratch. To prevent Pandas from writing the data frame index as a column, set index=False.

df = pd.DataFrame({"actor.attributes.email": ["EntityFramework"], "repository.name": ["CoreCLR"]})
df.to_sql("[publicdata].[samples].github_nested", con=engine, if_exists="append", index=False)

Matplotlib contains a number of tools that can graphically model Google BigQuery data after being fed a data frame From Pandas.

Using PyPlot

Before any Matplotlib tool, such as pyplot, can be used, it must be imported:

from matplotlib import pyplot as plt

Once a Pandas data frame is obtained, it can be used to create a plot visualizing Google BigQuery data. For example, the following plot generates and displays a bar graph relating actor.attributes.email and repository.watchers values:

df.plot(kind="bar", x="actor.attributes.email", y=["repository.watchers"])
plt.show()

The connector can be used to create ETL applications and pipelines for CSV data in Python using Petl.

Install Required Modules

Install the Petl modules using the pip utility.

pip install petl

Connecting

After you import the modules, including the CData Python Connector for Google BigQuery, you can use the connector's connect function to create a connection using a valid Google BigQuery connection string. If you prefer not to use a direct connection, you can use a SQLAlchemy engine.

import petl as etl
import cdata.googlebigquery as mod
cnxn = mod.connect("InitiateOAuth=GETANDREFRESH;ProjectId=NameOfProject;DatasetId=NameOfDataset;")

Extract, Transform, and Load the Google BigQuery Data

Create a SQL query string and store the query results in a DataFrame.

sql = "SELECT	actor.attributes.email, repository.name FROM [publicdata].[samples].github_nested "
table1 = etl.fromdb(cnxn,sql)

Loading Data

With the query results stored in a DataFrame, you can load your data into any supported Petl destination. The following example loads the data into a CSV file.

etl.tocsv(table1,'output.csv')

Modifying Data

Insert new rows into Google BigQuery tables using Petl's appenddb function.

table1 = [['actor.attributes.email','repository.name'],['EntityFramework','CoreCLR']]
etl.appenddb(table1,cnxn,'[publicdata].[samples].github_nested')

The extension supports schema discovery by using SQL queries to available System Tables.

Using SQL

The following sections describe the discovery of metadata through several System Tables:

• Tables and Views
• Columns
• Procedures

The connector possesses system tables that are used to discover the tables and views available in the data model. Of these system tables, "sys_tables" and "sys_views" are used to fetch information about the available tables and views respectively:

Tables

import cdata.googlebigquery as mod
conn = mod.connect("InitiateOAuth=GETANDREFRESH;ProjectId=NameOfProject;DatasetId=NameOfDataset;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_tables"
cur.execute(cmd)
rs = cur.fetchall()
for row in rs:
	print(row)

Views

import cdata.googlebigquery as mod
conn = mod.connect("InitiateOAuth=GETANDREFRESH;ProjectId=NameOfProject;DatasetId=NameOfDataset;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_views"
cur.execute(cmd, params)
rs = cur.fetchall()
for row in rs:
	print(row)

The available columns for any given table are fetched from a system table called "sys_tablecolumns". A specific table name is provided in the WHERE criteria to restrict the table from which the column information is fetched:

import cdata.googlebigquery as mod
conn = mod.connect("InitiateOAuth=GETANDREFRESH;ProjectId=NameOfProject;DatasetId=NameOfDataset;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_tablecolumns WHERE TableName = '[publicdata].[samples].github_nested'"
cur.execute(cmd)
rs = cur.fetchall()
for row in rs:
	print(row)

Procedures

A system table called "sys_procedures" is queried to obtain the available stored procedures that are executed:

import cdata.googlebigquery as mod
conn = mod.connect("InitiateOAuth=GETANDREFRESH;ProjectId=NameOfProject;DatasetId=NameOfDataset;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_procedures"
cur.execute(cmd)
rs = cur.fetchall()
for row in rs:
	print(row)

Parameters

The input parameters of any stored procedure are similarly obtained from the "sys_procedureparameters" system table:

import cdata.googlebigquery as mod
conn = mod.connect("InitiateOAuth=GETANDREFRESH;ProjectId=NameOfProject;DatasetId=NameOfDataset;")
cur = conn.cursor()
cmd = "SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'RefreshOAuthAccessToken'"
cur.execute(cmd)
rs = cur.fetchall()
for row in rs:
	print(row)

This section details a selection of advanced features of the Google BigQuery connector.

User Defined Views

The connector supports the use of user defined views, virtual tables whose contents are decided by a pre-configured user defined query. These views are useful when you cannot directly control queries being issued to the drivers. For an overview of creating and configuring custom views, see User Defined Views .

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats;. For further information, see the SSLServerCert property under "Connection String Options" .

Firewall and Proxy

Configure the connector for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Caching Data

Caching Data enables faster access to data and reduces the number of API calls, improving performance. The connector supports a simple caching model where multiple connections can also share the cache over time. When configuring the cache connection, you can specify automatic or explicit data caching.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Google BigQuery and then processes the rest of the query in memory (client-side).

For further information, see Query Processing.

Logging

For an overview of configuration settings that can be used to refine CData logging, see Logging. Only two connection properties are required for basic logging, but there are numerous features that support more refined logging, which enables you to use the LogModules connection property to specify subsets of information to be logged.

Exception Handling

For an overview of how exceptions are reported and the components of an exception, see Exception Handling.

The CData Python Connector for Google BigQuery supports the use of user defined views: user-defined virtual tables whose contents are decided by a preconfigured query. User defined views are useful in situations where you cannot directly control the query being issued to the driver; for example, when using the driver from a tool.

Use a user defined view to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Defining Views Using a Configuration File

User defined views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This user defined view configuration file is formatted so that each root element defines the name of a view, and includes a child element, called query, which contains the custom SQL query for the view.

For example:

{
	"MyView": {
		"query": "SELECT * FROM [publicdata].[samples].github_nested WHERE MyColumn = 'value'"
	},
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
	}
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\\Users\\yourusername\\Desktop\\tmp\\UserDefinedViews.json"

Defining Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

In order to avoid a view's name clashing with an actual entity in the data model, user defined views are exposed in the UserViews schema by default. To change the name of the schema used for UserViews, reset the UserViewsSchemaName property.

Working with User Defined Views

For example, a SQL statement with a user defined view called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a user defined view that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

Customizing the SSL Configuration

By default, the connector attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

Note: The connector uses the system proxy settings by default, without further configuration needed. If you want to connect to other proxies, set ProxyAutoDetect to False and read further.

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the connector uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

Caching Data

Caching data provides several benefits, including faster access to data and reducing the number of API calls, which improve performance. The connector supports a simple caching model where multiple connections can also share the cache over time. You can enable and configure caching features by setting the necessary connection properties.

Contents

The sections in this chapter detail the connector's caching functionality and link to the corresponding connection properties, as well as SQL statements.

Configuring the Cache Connection

Configuring the Cache Connection describes the properties that you can set when configuring the cache database.

Caching Metadata

Caching Metadata describes the CacheMetadata property. This property determines whether or not to cache the table metadata to a file store.

Automatically Caching Data

Automatically Caching Data describes how the connector automatically refreshes the cache when the AutoCache property is set.

Explicitly Caching Data

Explicitly Caching Data describes how you can decide what data is stored in the cache and when it is updated.

Data Type Mapping

Data Type Mapping shows the mappings between the data types configured in the schema and the data types in the database.

Configuring the Caching Database

This section describes the properties for caching data to the persistent store of your choice.

CacheLocation

The CacheLocation property species the path to a file-system-based database. When caching is enabled, a file-system-based database is used by default. If CacheLocation is not specified, this database is stored at the path in Location. If neither of these connection properties are specified, the connector uses a platform-dependent default location.

CacheConnection

The CacheConnection property specifies a database driver and the connection string to the caching database.

CacheDriver and CacheProvider

Both the CacheDriver and CacheProvider properties are supported. Each specifies a database driver and the connection string to the caching database. CacheDriver is designed for Linux and MacOS; CacheProvider is Windows-based.

This section describes how to enable caching metadata and how to update the metadata cache.

Before being able to query data, the connector requires relevant metadata to be retrieved. By default, metadata is cached in memory and shared across connections. But if you want to persist across processes, or if metadata requests are expensive, the solution is to cache the metadata to disk.

Enable Caching Metadata

To enable caching of metadata, set CacheMetadata = true and see Configuring the Cache Connection for instructions on how to configure your connection string. The connector caches the metadata the first time it is needed and uses the metadata cache for subsequent requests.

Update the Metadata Cache

Because metadata is cached, changes to metadata on the live source, for example, adding or removing a column or attribute, are not automatically reflected in the metadata cache. To get updates to the live metadata, you need to delete or drop the cached data.

Automatically caching data is useful when you do not want to rebuild the cache for each query. When you query data for the first time, the connector automatically initializes and builds a cache in the background. When AutoCache = true, the connector uses the cache for subsequent query executions, resulting in faster response times.

Configuring Automatic Caching

Caching the github_nested Table

The following example caches the github_nested table in the file specified by the CacheLocation property of the connection string.

SELECT actor.attributes.email, repository.name FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'

Common Use Case

A common use for automatically caching data is to improve driver performance when making repeated requests to a live data source, such as building a report or creating a visualization. With auto caching enabled, repeated requests to the same data may be executed in a short period of time, but within an allowable tolerance (CacheTolerance) of what is considered "live" data.

With explicit caching (AutoCache = false), you decide exactly what data is cached and when to query the cache instead of the live data. Explicit caching gives you full control over the cache contents by using CACHE Statements. This section describes some strategies to use the caching features offered by the connector.

Creating the Cache

To load data in the cache, issue the following statement.

CACHE SELECT * FROM tableName WHERE ...

Once the statement is issued, any matching data in tableName is loaded into the corresponding table.

Updating the Cache

This section describes two ways to update the cache.

Updating with the SELECT Statement

The following example shows a statement that can update modified rows and add missing rows in the cached table. However, this statement does not delete extra rows that are already in the cache. This statement only merges the new rows or updates the existing rows.

CACHE SELECT * FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'

Updating with the TRUNCATE Statement

The following example shows a statement that can update modified rows and add missing rows in the cached table. This statement can also delete rows in the cache table that are not present in the live data source.

  CACHE WITH TRUNCATE SELECT * FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'
  

Query the Data in Online or Offline Mode

This section describes how to query the data in online or offline mode.

Online: Select Cached Tables

You can use the tableName#CACHE syntax to explicitly execute queries to the cache while still online, as shown in the following example.

SELECT * FROM [publicdata].[samples].github_nested#CACHE

Offline: Select Cached Tables

With Offline = true, SELECT statements always execute against the local cache database, regardless of whether you explicitly specify the cached table or not. Modification of the cache is disabled in Offline mode to prevent accidentally updating only the cached data. Executing a DELETE/UPDATE/INSERT statement while in Offline mode results in an exception.

The following example selects from the local cache but not the live data source because Offline = true.

SELECT * FROM [publicdata].[samples].github_nested WHERE repository.name='EntityFramework' ORDER BY repository.name ASC

Delete Data from the Cache

You can delete data from the cache by building a direct connection to the database. Note that the connector does not support manually deleting data from the cache.

Common Use Case

A common use for caching is to have an application always query the cached data and only update the cache at set intervals, such as once every day or every two hours. There are two ways in which this can be implemented:

• AutoCache = false and Offline = false. All queries issued by the application explicitly reference the tableName#CACHE table. When the cache needs to be updated, the application executes a tableName#CACHE ... statement to bring the cached data up to date.
• Offline = true. Caching is transparent to the application. All queries are executed against the table as normal, so most application code does not need to be aware that caching is done. To update the cached data, simply create a separate connection with Offline = false and execute a tableName#CACHE ... statement.

The connector maps types from the data source to the corresponding data type available in the chosen cache database. The following table shows the mappings between the data types configured in the schema and the data types in the database. Some schema types have synonyms which are all listed in the Schema column.

Data Type Mapping

Note: String columns can map to different data types depending on their length.

Schema	.NET	JDBC	SQL Server	Derby	MySQL	Oracle	SQLite	Access
int, integer, int32	Int32	int	int	INTEGER	INT	NUMBER	integer	LONG
smallint, short, int16	Int16	short	smallint	SMALLINT	SMALLINT	NUMBER	integer	SHORT
double, float, real	Double	double	float	DOUBLE	DOUBLE	NUMBER	double	DOUBLE
date	DateTime	java.sql.Date	date	DATE	DATE	DATE	date	DATETIME
datetime, timestamp	DateTime	java.sql.Date	datetime	TIMESTAMP	DATETIME	TIMESTAMP	datetime	DATETIME
time, timespan	TimeSpan	java.sql.Time	time	TIME	TIME	TIMESTAMP	datetime	DATETIME
string, varchar	String	java.lang.String	If length > 4000: nvarchar(max), Otherwise: nvarchar(length)	If length > 32672: LONG VARCHAR, Otherwise VARCHAR(length)	If length > 255: LONGTEXT, Otherwise: VARCHAR(length)	If length > 4000: CLOB, Otherwise: VARCHAR2(length)	nvarchar(length)	If length > 255: LONGTEXT, Otherwise: VARCHAR(length)
long, int64, bigint	Int64	long	bigint	BIGINT	BIGINT	NUMBER	bigint	LONG
boolean, bool	Boolean	boolean	tinyint	SMALLINT	BIT	NUMBER	tinyint	BIT
decimal, numeric	Decimal	java.math.BigDecimal	decimal	DECIMAL	DECIMAL	DECIMAL	decimal	CURRENCY
uuid	Guid	java.util.UUID	nvarchar(length)	VARCHAR(length)	VARCHAR(length)	VARCHAR2(length)	nvarchar(length)	VARCHAR(length)
binary, varbinary, longvarbinary	byte[]	byte[]	binary(1000) or varbinary(max) after SQL Server 2000, image otherwise	BLOB	LONGBLOB	BLOB	BLOB	LONGBINARY

Query Processing

CData has a client-side SQL engine built into the connector library. This enables support for the full capabilities that SQL-92 offers, including filters, aggregations, functions, etc.

For sources that do not support SQL-92, the connector offloads as much of SQL statement processing as possible to Google BigQuery and then processes the rest of the query in memory (client-side). This results in optimal performance.

For data sources with limited query capabilities, the connector handles transformations of the SQL query to make it simpler for the connector. The goal is to make smart decisions based on the query capabilities of the data source to push down as much of the computation as possible. The Google BigQuery Query Evaluation component examines SQL queries and returns information indicating what parts of the query the connector is not capable of executing natively.

The Google BigQuery Query Slicer component is used in more specific cases to separate a single query into multiple independent queries. The client-side Query Engine makes decisions about simplifying queries, breaking queries into multiple queries, and pushing down or computing aggregations on the client-side while minimizing the size of the result set.

There's a significant trade-off in evaluating queries, even partially, client-side. There are always queries that are impossible to execute efficiently in this model, and some can be particularly expensive to compute in this manner. CData always pushes down as much of the query as is feasible for the data source to generate the most efficient query possible and provide the most flexible query capabilities.

More Information

For a full discussion of how CData handles query processing, see CData Architecture: Query Execution.

Logging

Capturing connector logging can be very helpful when diagnosing error messages or other unexpected behavior.

Basic Logging

To begin capturing connector logging, set these properties:

• Logfile: A filepath that designates the name and location of the log file.
• Verbosity: A numerical value (1-5) that determines the amount of detail in the log. See the page in the Connection Properties section for an explanation of the five levels.
• MaxLogFileSize: 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.
• MaxLogFileCount: A string specifying the maximum file count of log files. 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. Minimum supported value is 2. A value of 0 or a negative value indicates no limit on the count.

Once these properties are set, the connector populates 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 is created.

Log Verbosity

The verbosity level determines the amount of detail that the connector reports to the Logfile. Supported Verbosity levels range from 1 to 5.

The following list describes each level:

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

For normal operations, Verbosity should not be set to greater than 1. At higher verbosities you can log substantial amounts of data, which can delay execution times.

To refine the logged content further by showing/hiding specific categories of information, see LogModules.

Sensitive Data

Verbosity levels of 3 and higher may capture information that you do not want shared outside of your organization. The following lists information of concern for each level:

• Verbosity 3: The full body of the request and the response, which includes all the data returned by the connector
• Verbosity 4: SSL certificates
• Verbosity 5: Any extra transfer data not included at Verbosity 3, such as non human-readable binary transfer data

Note: Although we mask sensitive values, such as passwords, in the connection string and any request in the log, it is always best practice to review the logs for any sensitive information before sharing outside your organization.

Advanced Logging

You may want to refine the exact information that is recorded to the log file. This can be accomplished using the LogModules property. This property allows you to filter the logging using a semicolon-separated list of logging modules.

Example property value:

LogModules=INFO;EXEC;SSL;SQL;META;

Note that the logfile filtering triggered by the Verbosity connection property takes precedence over the filtering imposed by this connection property. This means that operations of a higher verbosity level than the level specified in the Verbosity connection property are not printed in the logfile, even if they belong to one of the modules specified in this connection property.

The available modules and submodules are:

Module Name	Module Description	Submodules
INFO	General Information. Includes the connection string, product version (build number), and initial connection messages.	Connec – Information related to creating or destroying connections. Messag – Generic label for messages pertaining to connections, the connection string, and product version. These messages are typically specific to the connector, rather than being received and passed along directly from the service.
EXEC	Query Execution. Includes execution messages for user-written SQL queries, parsed SQL queries, and normalized SQL queries. Success/failure messages for queries and query pages appear here as well.	Messag – Messages pertaining to query execution. These messages are typically specific to the connector, rather than being received and passed along directly from the service. Normlz – Query normalization steps. Query normalization is when the product takes the user-submitted query and rewrites the query to get the same results with optimal performance. Origin – This label applies to any messages recording a user's original query (the exact, unaltered, non-normalized query executed by the user). Page – Messages related to query paging. Parsed – Query parsing steps. Parsing is the process of converting the user-submitted query into a standardized format for easier processing.
HTTP	HTTP protocol messages. Includes HTTP requests/responses (including POST messages), as well as Kerberos related messages.	KERB – HTTP requests related to Kerberos. Messag – Messages pertaining to HTTP protocols. These messages are typically specific to the connector, rather than being received and passed along directly from the service. Unpack – This label applies to messages about zipped data being returned from the service API and unpacked by the product. Res – Messages containing HTTP responses. Req – Messages containing HTTP requests.
WSDL	Messages pertaining to the generation of WSDL/XSD files.	—
SSL	SSL certificate messages.	Certif – Messages pertaining to SSL certificates.
AUTH	Authentication related failure/success messages.	Messag – Messages pertaining to authentication. These messages are typically specific to the connector, rather than being received and passed along directly from the service. OAuth – Messages related to OAuth authentication. Krbros – Kerberos-related authentication messages.
SQL	Includes SQL transactions, SQL bulk transfer messages, and SQL result set messages.	Bulk – Messages pertaining to bulk query execution. Cache – Messages related to reading row data from and writing row data to the product's cache for better performance. Messag – Messages pertaining to SQL transactions. These messages are typically specific to the connector, rather than being received and passed along directly from the service. ResSet – Query resultsets. Transc – Messages related to handling transactions, including information about the number of jobs executed and backup table handling.
META	Metadata cache and schema messages.	Cache – Messages related to reading from and modifying column and table definitions in the product's cache for better performance. Schema – Messages related to retrieving metadata from or modifying the service schema. MemSto – Messages related to writing to or reading from in-memory metadata cache. Storag – Messages relating to storing metadata on disk or in an external data store, rather than in memory.
FUNC	Information related to executing SQL functions.	Errmsg – Error messages related to executing SQL functions.
TCP	Incoming and outgoing raw bytes on TCP transport layer messages.	Send – Raw data sent via the TCP protocol. Receiv – Raw data received via the TCP protocol.
FTP	Messages pertaining to the File Transfer Protocol.	Info – Status messages related to communication in the FTP protocol. Client – Messages related to actions taken by the FTP client (the product) during FTP communication. Server – Messages related to actions taken by the FTP server during FTP communication.
SFTP	Messages pertaining to the Secure File Transfer Protocol.	Info – Status messages related to communication in the SFTP protocol. To_Server – Messages related to actions taken by the SFTP client (the product) during SFTP communication. From_Server – Messages related to actions taken by the SFTP server during SFTP communication.
POP	Messages pertaining to data transferred via the Post Office Protocol.	Client – Messages related to actions taken by the POP client (the product) during POP communication. Server – Messages related to actions taken by the POP server during POP communication. Status – Status messages related to communication in the POP protocol.
SMTP	Messages pertaining to data transferred via the Simple Mail Transfer Protocol.	Client – Messages related to actions taken by the SMTP client (the product) during SMTP communication. Server – Messages related to actions taken by the SMTP server during SMTP communication. Status – Status messages related to communication in the SMTP protocol.
CORE	Messages relating to various internal product operations not covered by other modules.	—
DEMN	Messages related to SQL remoting.	—
CLJB	Messages about bulk data uploads (cloud job).	Commit – Submissions for bulk data uploads.
SRCE	Miscellaneous messages produced by the product that don't belong in any other module.	—
TRANCE	Advanced messages concerning low-level product operations.	—

Exception Handling

Exceptions can be surfaced from either the API or the CData Python Connector for Google BigQuery. Each exception will have an error code, an error message, and a SQL state.

Error Codes

The error code classifies the type of error.

0	NONE	Used for unclassified errors and internally handled errors. This code also covers data source-specific errors that do not fit in any specific category.
65537	TCP_UNKNOWN_HOST	Unable to resolve a hostname (DNS failure).
65538	TCP_CONNECTION_REFUSED	Could not connect to the remote port.
65539	TCP_AUTH_FAILED	Login failed when using a binary authentication protocol. Use this for auth errors when the protocol is not HTTP (LDAP, SASL, Kerberos, ...).
65540	TCP_TIMEOUT	Did not receive a response after sending a request to the server.
65541	TCP_PROTOCOL	For wire protocol drivers. Either the server sent a bad packet that we are unable to process, or we cannot construct a packet to send.
131073	TLS_SERVER_UNTRUSTED	Could not verify SSL server certificate.
131074	TLS_CLIENT_UNTRUSTED	Server did not accept the client certificate we sent.
196609	OAUTH_DECRYPT_FAILED	OAuthEncryptKey did not decrypt the OAuthSettings file.
196610	OAUTH_MISSING_CLIENT_INFO	OAuthClientId / OAuthClientSecret / OAuthJWTCert is missing.
196611	OAUTH_MISSING_PROP	General OAuth property missing. OAUTH_MISSING_CLIENT_INFO is used for missing client ID/secret and JWT cert.
196612	OAUTH_NO_ACCESS_TOKEN	Unable to retrieve access token. Only use this when getting a token in GetOAuthAccessToken / RefreshOAuthAccessToken.
196613	OAUTH_TOKEN_EXPIRED	The access token expired. Normally used with a RefreshOAuth/OAuthException behavior.
196614	OAUTH_INVALID_PROP	OAuth property has an invalid value. OAUTH_MISSING_CLIENT_INFO / OAUTH_MISSING_PROP is used if the value is not set.
262145	HTTP_REQUEST_TIMEOUT	Did not receive a response from the HTTP server.
262146	HTTP_CLIENT_ERROR	Generic HTTP 4xx error. Only use for 4xx errors not covered by other codes.
262147	HTTP_AUTH_FAILED	HTTP 401 error.
262148	HTTP_LIMIT_EXCEEDED	HTTP 429 error.
262149	HTTP_SERVER_ERROR	HTTP 5xx error.
262150	HTTP_NOT_FOUND_ERROR	HTTP 404 error.
327681	CORE_TIMEOUT	General timeout. Not related to a specific network request.
327682	CORE_OP_NOT_ALLOWED	Operation blocked by provider permissions.
327683	CORE_CONNECTION_CONFIG	Connection configuration is not valid.
327684	CORE_SERIALIZE	Failed to encode data into a specific format (XML, JSON, CSV, ...).
327685	CORE_DESERIALIZE	Failed to decode data from a specific format (XML, JSON, CSV, ...).
393217	SQL_SYNTAX_ERROR	Unable to parse a SQL query.
393218	SQL_MISSING_COLUMNS	Query did not include required columns.
393219	SQL_MISSING_PARAMS	Stored procedure call did not include required parameters.
393220	SQL_QUERY_NOT_SUPPORTED	A part of the query is not allowed in the current context.
458753	SSH_SERVER_UNTRUSTED	Could not verify SSH server.
524289	STORAGE_LIST_EXCEPTION	Issue listing storage resources.
524290	STORAGE_RESOURCE_NOT_FOUND	Issue finding storage resources.
524291	STORAGE_ROOT_RESOURCE_NOT_FOUND	The root resource (bucket/share/drive) was not found; cannot create it in flat file drivers.
524292	STORAGE_RESOURCE_NOT_A_DIRECTORY	Storage resource is not a directory.
524293	STORAGE_RESOURCE_NOT_A_FILE	Storage resource is not a file.
524294	STORAGE_PERMISSIONS_DENIED	Storage permissions denied.

SQL State

The SQL state is used when throwing generic provider errors to the wrapper and indicates the success or failure of a call.

Some of the common SQL states are listed below:

07007	REQUIRED_CLAUSE	Class Code 07: Dynamic SQL Error.
08001	OPEN_CONNECTION	Class Code 08: Connection Exception. The connection was unable to be established to the application server or other server.
08004	REJECT_CONNECTION	The application server rejected establishment of the connection.
42501	PRIVILEGE_IDENTIFIED_OBJECT	Class Code 42: Syntax Error or Access Rule Violation. The authorization ID does not have the privilege to perform the specified operation on the identified object.
42506	AUTH_FAILED	Owner authorization failure occurred.
42601	SQL_SYNTAX	A character, token, or clause is invalid or missing.

Error Message

The error message provides more detailed reasoning about why the error occurred. It provides an explanation of the issue, and may include steps on how to resolve it.

The CData Python Connector for Google BigQuery supports several operations on data, including querying, deleting, modifying, and inserting.

SELECT Statements

See SELECT Statements for a syntax reference and examples.

See Data Model for information on the capabilities of the Google BigQuery API.

INSERT Statements

See INSERT Statements for a syntax reference and examples, as well as retrieving the new records' Ids.

UPDATE Statements

The primary key Id is required to update a record. See UPDATE Statements for a syntax reference and examples.

DELETE Statements

The primary key Id is required to delete a record. See DELETE Statements for a syntax reference and examples.

CACHE Statements

CACHE statements allow granular control over the connector's caching functionality. For a syntax reference and examples, see CACHE Statements.

For more information on the caching feature, see Caching Data.

EXECUTE Statements

Use EXECUTE or EXEC statements to execute stored procedures. See EXECUTE Statements for a syntax reference and examples.

Names and Quoting

• Table and column names are considered identifier names; as such, they are restricted to the following characters: [A-Z, a-z, 0-9, _:@].
• To use a table or column name with characters not listed above, the name must be quoted using square brackets ([name]) in any SQL statement.
• Parameter names can optionally start with the @ symbol (e.g., @p1 or @CustomerName) and cannot be quoted.
• Strings must be quoted using single quotes (e.g., 'John Doe').

The connector provides functions that are similar to those that are available with most standard databases. These functions are implemented in the CData provider engine and thus are available across all data sources with the same consistent API. Three categories of functions are available: string, date, and math.

The connector interprets all SQL function inputs as either strings or column identifiers, so you need to escape all literals as strings, with single quotes. For example, contrast the SQL Server syntax and connector syntax for the DATENAME function:

• SQL Server:
  

  SELECT DATENAME(yy,GETDATE())

• connector:
  

  SELECT DATENAME('yy',GETDATE())

Projection Functions

These functions can be used to refine projections in your SQL query. See Projection Functions for more details.

Predicate Functions

These functions can be used to specify criteria in the WHERE clause of your SQL query. See Predicate Functions for more details.

ANY_VALUE(expression)

Returns any value from the input or NULL if there are zero input rows. The value returned is non-deterministic, which means you might receive a different result each time you use this function.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to retrieve a value from.

APPROX_COUNT_DISTINCT(expression)

Returns the approximate result for COUNT(DISTINCT expression). The value returned is a statistical estimate-not necessarily the actual value. This function is less accurate than COUNT(DISTINCT expression), but performs better on huge input.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to perform the approximate count distinct on.

APPROX_QUANTILES(expression, number)

Returns the approximate boundaries for a group of expression values, where number represents the number of quantiles to create. This function returns an array of number + 1 elements, where the first element is the approximate minimum and the last element is the approximate maximum.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to perform the approximate quantiles on.
• number: The number of quantiles to create.

APPROX_TOP_COUNT(expression, number)

Returns the approximate top elements of expression. The number parameter specifies the number of elements returned.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to perform the approximate top count on.
• number: The number of elements to be returned.

APPROX_TOP_SUM(expression, weight, number)

Returns the approximate top elements of expression, based on the sum of an assigned weight. The number parameter specifies the number of elements returned. If the weight input is negative or NaN, this function returns an error.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to perform the approximate top sum on.
• weight: The assigned weight.
• number: The number of elements to be returned.

ARRAY(subquery)

The ARRAY function returns an ARRAY with one element for each row in a subquery.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• subquery: The subquery to execute.

ARRAY_CONCAT(array_expr1 [, array_expr2 [, ...]])

Concatenates one or more arrays with the same element type into a single array.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• array_expr1: The first array to concatenate.
• array_expr2: The second array to concatenate.

ARRAY_LENGTH(array_expr)

Returns the size of the array. Returns 0 for an empty array. Returns NULL if the array_expression is NULL.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• array_expr: The array expression to get the size of.

ARRAY_TO_STRING(array_expr, delimiter [, null_text])

Returns a concatenation of the elements in array_expression as a STRING. The value for array_expression can either be an array of STRING or BYTES data types.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• array_expr: The array expression to convert to string.
• delimiter: The delimiter string used to delimit the array values.
• null_text: If the null_text parameter is used, the function replaces any NULL values in the array with the value of null_text. If the null_text parameter is not used, the function omits the NULL value and its preceding delimiter.

GENERATE_ARRAY(start_expr, end_expr [, step_expr])

Returns an array of values. The start_expression and end_expression parameters determine the inclusive start and end of the array.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• start_expr: The starting value.
• end_expr: The ending value.
• step_expr: The increment used to generate array values.

GENERATE_DATE_ARRAY(start_date, end_date [, INTERVAL int_expr date_part])

Returns an array of dates. The start_date and end_date parameters determine the inclusive start and end of the array.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• start_date: The starting date.
• end_date: The ending date.
• int_expr: The increment used to generate dates.
• date_part: The date part used to increment the generated dates. Valid values are: DAY, WEEK, MONTH, QUARTER, and YEAR.

ARRAY_REVERSE(array_expr)

Returns the input ARRAY with elements in reverse order.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• array_expr: The array to reverse.

ARRAY_AGG(expression)

Returns an ARRAY of expression values.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression values to generate an array from.

ARRAY_CONCAT_AGG(expression1[, expression2 [,...]])

Concatenates elements from expression of type ARRAY, returning a single ARRAY as a result. This function ignores NULL input arrays, but respects the NULL elements in non-NULL input arrays (an error is raised, however, if an array in the final query result contains a NULL element).
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression1: The first expression to concatenate.
• expression2: The first expression to concatenate.

AVG([DISTINCT] expression)

Returns the average on non-null values. Each distinct value of expression is aggregated only once into the result.

• expression: The expression to use to compute the average.

BIT_AND(numeric_expression)

Returns the result of a bitwise AND operation between each instance of numeric_expr across all rows. NULL values are ignored. This function returns NULL if all instances of numeric_expr evaluate to NULL.

• numeric_expression: The numeric expression to perform the bitwise operation.

BIT_COUNT(expression)

The input, expression, must be an integer or BYTES. Returns the number of bits that are set in the input expression. For integers, this is the number of bits in two's complement form.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to perform the bit count operation on.

BIT_OR(numeric_expression)

Returns the result of a bitwise OR operation between each instance of numeric_expr across all rows. NULL values are ignored. This function returns NULL if all instances of numeric_expr evaluate to NULL.

• numeric_expression: The numeric expression to perform the bitwise operation.

BIT_XOR(numeric_expression)

Returns the result of a bitwise XOR operation between each instance of numeric_expr across all rows. NULL values are ignored. This function returns NULL if all instances of numeric_expr evaluate to NULL.

• numeric_expression: The numeric expression to perform the bitwise operation.

CORR(numeric_expression1, numeric_expression2)

Returns the Pearson correlation coefficient of a set of number pairs.

• numeric_expression1: The first series.
• numeric_expression2: The second series.

COUNTIF(expression)

Returns the count of TRUE values for expression. Returns 0 if there are zero input rows or expression evaluates to FALSE for all rows.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to evaluate.

COVAR_POP(numeric_expression1, numeric_expression2)

Computes the population covariance of the values computed by numeric_expression1 and numeric_expression2.

• numeric_expression: The first series.
• numeric_expression: The second series.

COVAR_SAMP(numeric_expression1, numeric_expression2)

Computes the sample covariance of the values computed by numeric_expression1 and numeric_expression2.

• numeric_expression: The first series.
• numeric_expression: The second series.

FIRST(column)

Returns the first sequential value in the scope of the function.
Note: this function is only available when UseLegacySQL=True.

• column: Any column expression.

FIRST_VALUE(value_expression [(IGNORE/RESPECT) NULLS])

Returns the value of the value_expression for the first row in the current window frame.
Note: this function only supports [IGNORE NULLS] when using Standard SQL (UseLegacySQL=False).

• value_expression: Any value expression

GROUP_CONCAT(string_expression [, separator])

Concatenates multiple strings into a single string, where each value is separated by the optional separator parameter. If separator is omitted, returns a comma-separated string.
Note: this function is only available when UseLegacySQL=True.

• string_expression: The string expression to concat.
• separator: The separator.

GROUP_CONCAT_UNQUOTED(string_expression [, separator])

Concatenates multiple strings into a single string, where each value is separated by the optional separator parameter. If separator is omitted, BigQuery returns a comma-separated string. Unlike GROUP_CONCAT, this function will not add double quotes to returned values that include a double quote character. For example, the string a"b would return as a"b.
Note: this function is only available when UseLegacySQL=True.

• string_expression: The string expression to concat.
• separator: The separator.

LAST(column)

Returns the last sequential value in the scope of the function.
Note: this function is only available when UseLegacySQL=True.

• column: Any column expression

LAST_VALUE(value_expression [(IGNORE/RESPECT) NULLS])

Returns the value of the value_expression for the last row in the current window frame.
Note: this function only supports [IGNORE NULLS] when using Standard SQL (UseLegacySQL=False).

• value_expression: Any value expression

LOGICAL_AND(expression)

Returns the logical AND of all non-NULL expressions. Returns NULL if there are zero input rows or expression evaluates to NULL for all rows.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to perform the logical AND on.

LOGICAL_OR(expression)

Returns the logical OR of all non-NULL expressions. Returns NULL if there are zero input rows or expression evaluates to NULL for all rows.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to perform the logical OR on.

NEST(expression)

Aggregates all values in the current aggregation scope into a repeated field. For example, the query SELECT x, NEST(y) FROM ... GROUP BY x returns one output record for each distinct x value, and contains a repeated field for all y values paired with x in the query input. The NEST function requires a GROUP BY clause.
Note: this function is only available when UseLegacySQL=True.

• expression: Any expression.

NOW()

Returns the current UNIX timestamp in microseconds.
Note: this function is only available when UseLegacySQL=True.

NTH(n, field)

Returns the nth sequential value in the scope of the function, where n is a constant. The NTH function starts counting at 1, so there is no zeroth term. If the scope of the function has less than n values, the function returns NULL.
Note: this function is only available when UseLegacySQL=True.

• n: The nth sequential value.
• field: The column name.

NTH_VALUE(value_expression, constant_integer_expression)

Returns the value of value_expression at the Nth row of the current window frame, where Nth is defined by constant_integer_expression. Returns NULL if there is no such row.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• value_expression: Any value expression.
• constant_integer_expression: The nth sequential value.

QUANTILES(expression [, buckets])

Computes approximate minimum, maximum, and quantiles for the input expression. NULL input values are ignored. Empty or exclusively-NULL input results in NULL output. The number of quantiles computed is controlled with the optional buckets parameter, which includes the minimum and maximum in the count.
Note: this function is only available when UseLegacySQL=True.

• expression: The numeric expression to compute quantiles on.
• buckets: The number of buckets.

STDDEV(numeric_expression)

Returns the standard deviation of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.

• numeric_expression: The series to calculate STDDEV on.

STDDEV_POP(numeric_expression)

Computes the population standard deviation of the value computed by numeric_expr.

• numeric_expression: The series to calculate STDDEV on.

STDDEV_SAMP([DISTINCT] numeric_expression)

Computes the sample standard deviation of the value computed by numeric_expr.

• numeric_expression: The series to calculate STDDEV on.

STRING_AGG(expression[, delimiter])

Returns a value (either STRING or BYTES) obtained by concatenating non-null values. If a delimiter is specified, concatenated values are separated by that delimiter; otherwise, a comma is used as a delimiter.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The string expression to concatenate.
• delimiter: The delimiter to separate concatenated values.

SUM([DISTINCT] expression)

Returns the sum on non-null values. Each distinct value of expression is aggregated only once into the result.

• expression: The expression to use to compute the sum.

TOP(column [, max_values][, multiplier])

TOP is a function that is an alternative to the GROUP BY clause. It is used as simplified syntax for GROUP BY ... ORDER BY ... LIMIT .... Generally, the TOP function performs faster than the full ... GROUP BY ... ORDER BY ... LIMIT ... query, but may only return approximate results.
Note: this function is only available when UseLegacySQL=True.

• numeric_expression: The series to calculate STDDEV on.
• max_values: The maximum number of results to return. Default is 20.
• multiplier: A positive integer that increases the value(s) returned by COUNT(*) by the multiple specified.

UNIQUE(expression)

Returns the set of unique, non-NULL values in the scope of the function in an undefined order.
Note: this function is only available when UseLegacySQL=True.

• expression: Any expression.

VARIANCE(numeric_expression)

Computes the variance of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.

• numeric_expression: The series to calculate VARIANCE on.

VAR_POP(numeric_expression)

Computes the population variance of the values computed by numeric_expr.

• numeric_expression: The series to calculate VARIANCE on.

VAR_SAMP([DISTINCT] numeric_expression)

Computes the sample variance of the values computed by numeric_expr.

• numeric_expression: The series to calculate VARIANCE on.

RANK()

Returns the ordinal (1-based) rank of each row within the ordered partition. All peer rows receive the same rank value. The next row or set of peer rows receives a rank value which increments by the number of peers with the previous rank value, instead of a rank value which always increments by 1.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

PERCENT_RANK()

Return the percentile rank of a row defined as (RK-1)/(NR-1), where RK is the RANK of the row and NR is the number of rows in the partition. Returns 0 if NR=1.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

NTILE(constant_integer_expression)

This function divides the rows into constant_integer_expression buckets based on row ordering and returns the 1-based bucket number that is assigned to each row. The number of rows in the buckets can differ by at most 1. The remainder values (the remainder of number of rows divided by buckets) are distributed one for each bucket, starting with bucket 1. If constant_integer_expression evaluates to NULL, 0 or negative, an error is provided.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• constant_integer_expression: The number of buckets to divide the rows into.

LEAD(value_expression [, offset [, default_expression]])

Returns the value of the value_expression on a subsequent row. Changing the offset value changes which subsequent row is returned; the default value is 1, indicating the next row in the window frame. An error occurs if offset is NULL or a negative value.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• value_expression: The value expression.
• offset: The offset to use. Must be a non-negative integer.
• default_expression: The default expression. Must be compatible with the value_expression type.

LAG(value_expression [, offset [, default_expression]])

Returns the value of the value_expression on a subsequent row. Changing the offset value changes which subsequent row is returned; the default value is 1, indicating the next row in the window frame. An error occurs if offset is NULL or a negative value.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• value_expression: The value expression.
• offset: The offset to use. Must be a non-negative integer.
• default_expression: The default expression. Must be compatible with the value_expression type.

PERCENTILE_CONT(value_expression [, percentile [{RESPECT | IGNORE} NULLS]])

Computes the specified percentile value for the value_expression, with linear interpolation.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• value_expression: A numeric expression.
• percentile: A literal in the range [0, 1].

PERCENTILE_DISC(value_expression, percentile [{RESPECT | IGNORE} NULLS])

Computes the specified percentile value for a discrete value_expression. The returned value is the first sorted value of value_expression with cumulative distribution greater than or equal to the given percentile value.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• value_expression: Any orderable type.
• percentile: A literal in the range [0, 1].

COALESCE(expr1 [, expr2 [, ...]])

Returns the value of the first non-null expression. The remaining expressions are not evaluated. All input expressions must be implicitly coercible to a common supertype.
Note: this function currently accepts up to 9 expressions.

• expr1: Any expression
• expr2: Any expression

NULLIF(expression, expression_to_match)

Returns NULL if expression = expression_to_match is true, otherwise returns expression. expression and expression_to_match must be implicitly coercible to a common supertype; equality comparison is done on coerced values.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: Any expression
• expression_to_match: Any expression to be matched

CUME_DIST()

Return the relative rank of a row defined as NP/NR. NP is defined to be the number of rows that either precede or are peers with the current row. NR is the number of rows in the partition.
Note: this function returns a double when using Legacy SQL (UseLegacySQL=True).

DENSE_RANK()

Returns the ordinal (1-based) rank of each row within the window partition. All peer rows receive the same rank value, and the subsequent rank value is incremented by one.

ROW_NUMBER()

Does not require the ORDER BY clause. Returns the sequential row ordinal (1-based) of each row for each ordered partition. If the ORDER BY clause is unspecified then the result is non-deterministic.

IFNULL(expr, null_result)

If expr is NULL, return null_result. Otherwise, return expr. If expr is not NULL, null_result is not evaluated. expr and null_result must be implicitly coercible to a common supertype. Synonym for COALESCE(expr, null_result)

• expr: Any expression
• null_result: The result to return if expr is null

CAST(expression AS type)

Cast is used in a query to indicate that the result type of an expression should be converted to some other type.

• expression: The expression to cast.
• type: The type to cast the expression to.

SAFE_CAST(expression, type)

Cast is used in a query to indicate that the result type of an expression should be converted to some other type.SAFE_CAST is identical to CAST, except it returns NULL instead of raising an error.

• expression: The expression to cast.
• type: The type to cast the expression to.

CURRENT_DATE()

Returns a human-readable string of the current date in the format %Y-%m-%d.

DATE(timestamp [, timezone])

Converts a timestamp_expression to a DATE data type.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp from which to return the date.
• timezone: The timezone to use when converting the timestamp. If not specified, the default timezone, UTC, is used.

DATEDIFF(timestamp1, timestamp2)

Returns the number of days between two TIMESTAMP data types. The result is positive if the first TIMESTAMP data type comes after the second TIMESTAMP data type, and otherwise the result is negative.
Note: this function is only available when UseLegacySQL=True.

• timestamp1: The first timestamp.
• timestamp2: The second timestamp.

DATE_DIFF(date1, date2, date_part)

Computes the number of specified date_part differences between two date expressions. This can be thought of as the number of date_part boundaries crossed between the two dates. If the first date occurs before the second date, then the result is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• date1: The first date.
• date2: The second date.
• date_part: The date part. Supported values are: DAY, MONTH, QUARTER, YEAR.

DATE_TRUNC(date, date_part)

Truncates the date to the specified granularity.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• date: The date to truncate.
• date_part: The date part. Supported values are: DAY, WEEK, ISOWEEK, MONTH, QUARTER, YEAR, ISOYEAR.

FORMAT_DATE(format_string, date_expr)

Formats the date_expr according to the specified format_string.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to format the date_expr.
• date_expr: The date to format.

PARSE_DATE(format_string, date_string)

Uses a format_string and a string representation of a date to return a DATE object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to parse the date_string.
• date_string: The date string to parse.

CURRENT_DATETIME([timezone])

Returns the current time as a DATETIME object.

• timezone: The timezone to use when retrieving the current datetime object.

DATETIME(timestamp [, timezone])

Constructs a DATETIME object using a TIMESTAMP object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp from which to return the datetime.
• timezone: The timezone to use when converting the timestamp. If not specified, the default timezone, UTC, is used.

DATETIME_DIFF(datetime1, datetime2, date_part)

Computes the number of specified date_part differences between two date expressions. This can be thought of as the number of date_part boundaries crossed between the two dates. If the first date occurs before the second date, then the result is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• datetime1: The first datetime.
• datetime2: The second datetime.
• date_part: The date part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR.

DATETIME_TRUNC(datetime, part)

Truncates the datetime to the specified granularity.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• date: The datetime to truncate.
• part: The date part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, ISOWEEK, MONTH, QUARTER, YEAR, and ISOYEAR.

FORMAT_DATETIME(format_string, datetime_expr)

Formats the date_expr according to the specified format_string.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to format the date_expr.
• datetime_expr: The datetime to format.

PARSE_DATETIME(format_string, datetime_string)

Uses a format_string and a string representation of a date to return a DATETIME object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to parse the date_string.
• datetime_string: The datetime string to parse.

CURRENT_TIME()

Returns a human-readable string of the server's current time in the format %H:%M:%S.

TIME(timestamp [, timezone])

Constructs a DATETIME object using a TIMESTAMP object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp from which to return the datetime.
• timezone: The timezone to use when converting the timestamp. If not specified, the default timezone, UTC, is used.

TIME_DIFF(time1, time2, time_part)

Computes the number of specified time_part differences between two time expressions. This can be thought of as the number of time_part boundaries crossed between the two times. If the first time occurs before the second time, then the result is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• time1: The first time.
• time2: The second time.
• time_part: The time part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR.

TIME_TRUNC(time, part)

Truncates the time to the specified granularity.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• time: The time to truncate.
• part: The time part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR.

FORMAT_TIME(format_string, time_expr)

Formats the date_expr according to the specified format_string.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to format the date_expr.
• time_expr: The time to format.

PARSE_TIME(format_string, time_string)

Uses a format_string and a string representation of a time to return a TIME object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to parse the time_string.
• time_string: The time string to parse.

CURRENT_TIMESTAMP()

Returns a TIMESTAMP data type of the server's current time in the format %Y-%m-%d %H:%M:%S.

TIMESTAMP_DIFF(timestamp1, timestamp2, time_part)

Computes the number of specified time_part differences between two timestamp expressions. This can be thought of as the number of time_part boundaries crossed between the two timestamp. If the first timestamp occurs before the second timestamp, then the result is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp1: The first timestamp.
• timestamp2: The second timestamp.
• time_part: The timestamp part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR.

FORMAT_TIMESTAMP(format_string, timestamp_expr)

Formats the date_expr according to the specified format_string.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to format the date_expr.
• timestamp_expr: The timestamp to format.

PARSE_TIMESTAMP(format_string, timestamp_string)

Uses a format_string and a string representation of a timestamp to return a TIMESTAMP object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to parse the timestamp_string.
• timestamp_string: The timestamp string to parse.

DAY(timestamp)

Returns the day of the month of a TIMESTAMP data type as an integer between 1 and 31, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the day of the month.

DAYOFWEEK(timestamp)

Returns the day of the week of a TIMESTAMP data type as an integer between 1 (Sunday) and 7 (Saturday), inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the day of the week.

DAYOFYEAR(timestamp)

Returns the day of the year of a TIMESTAMP data type as an integer between 1 and 366, inclusively. The integer 1 refers to January 1.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the day of the year.

FORMAT_UTC_USEC(unix_timestamp)

Returns a human-readable string representation of a UNIX timestamp in the format YYYY-MM-DD HH:MM:SS.uuuuuu.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The unix timestamp to format.

HOUR(timestamp)

Returns the hour of a TIMESTAMP data type as an integer between 0 and 23, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the hour as an integer.

MINUTE(timestamp)

Returns the minutes of a TIMESTAMP data type as an integer between 0 and 59, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the minutes as an integer.

MONTH(timestamp)

Returns the month of a TIMESTAMP data type as an integer between 1 and 12, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the month as an integer.

MSEC_TO_TIMESTAMP(unix_timestamp)

Converts a UNIX timestamp in milliseconds to a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The unix timestamp to convert.

PARSE_UTC_USEC(date_string)

Converts a date string to a UNIX timestamp in microseconds. date_string must have the format YYYY-MM-DD HH:MM:SS[.uuuuuu]. The fractional part of the second can be up to 6 digits long or can be omitted.
Note: this function is only available when UseLegacySQL=True.

• date_string: The date string to convert.

QUARTER(timestamp)

Returns the quarter of the year of a TIMESTAMP data type as an integer between 1 and 4, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the quarter as an integer.

SEC_TO_TIMESTAMP(unix_timestamp)

Converts a UNIX timestamp in seconds to a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to convert.

SECOND(timestamp)

Returns the seconds of a TIMESTAMP data type as an integer between 0 and 59, inclusively. During a leap second, the integer range is between 0 and 60, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the second as an integer.

STRFTIME_UTC_USEC(unix_timestamp, date_format_str)

Returns a human-readable date string in the format date_format_str.date_format_str can include date-related punctuation characters (such as / and -) and special characters accepted by the strftime function in C++ (such as %d for day of month).
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to convert.
• date_format_str: The date format string.

TIMESTAMP_SECONDS(unix_timestamp)

Interprets INT64_expression as the number of seconds since 1970-01-01 00:00:00 UTC.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The unix timestamp to convert.

TIMESTAMP_MILLIS(unix_timestamp)

Interprets INT64_expression as the number of milliseconds since 1970-01-01 00:00:00 UTC.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The unix timestamp to convert.

TIMESTAMP_MICROS(unix_timestamp)

Interprets INT64_expression as the number of microseconds since 1970-01-01 00:00:00 UTC.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The unix timestamp to convert.

TIMESTAMP_TO_MSEC(timestamp)

Converts a TIMESTAMP data type to a UNIX timestamp in milliseconds.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp to convert.

TIMESTAMP_TO_SEC(timestamp)

Converts a TIMESTAMP data type to a UNIX timestamp in seconds.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp to convert.

TIMESTAMP_TO_USEC(timestamp)

Converts a TIMESTAMP data type to a UNIX timestamp in microseconds.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp to convert.

UNIX_DATE(date_string)

Returns the number of days since 1970-01-01.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• date_string: The date string to convert.

UNIX_SECONDS(timestamp)

Returns the number of seconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp to convert.

UNIX_MILLIS(timestamp)

Returns the number of milliseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp to convert.

UNIX_MICROS(timestamp)

Returns the number of microseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp to convert.

USEC_TO_TIMESTAMP(unix_timestamp)

Converts a UNIX timestamp in microseconds to a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to convert.

UTC_USEC_TO_DAY(unix_timestamp)

Shifts a UNIX timestamp in microseconds to the beginning of the day it occurs in.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to shift.

UTC_USEC_TO_HOUR(unix_timestamp)

Shifts a UNIX timestamp in microseconds to the beginning of the hour it occurs in.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to shift.

UTC_USEC_TO_MONTH(unix_timestamp)

Shifts a UNIX timestamp in microseconds to the beginning of the month it occurs in.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to shift.

UTC_USEC_TO_WEEK(unix_timestamp, day_of_week)

Returns a UNIX timestamp in microseconds that represents a day in the week of the unix_timestamp argument.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to shift.
• day_of_week: A day of the week from 0 (Sunday) to 6 (Saturday).

UTC_USEC_TO_YEAR(unix_timestamp)

Returns a UNIX timestamp in microseconds that represents the year of the unix_timestamp argument.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to convert.

WEEK(timestamp)

Returns the week of a TIMESTAMP data type as an integer between 1 and 53, inclusively. Weeks begin on Sunday, so if January 1 is on a day other than Sunday, week 1 has fewer than 7 days and the first Sunday of the year is the first day of week 2.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the week as an integer.

YEAR(timestamp)

Returns the year of a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the year as an integer.

ABS(expression)

Returns the absolute value of the argument.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

ACOS(expression)

Returns the arc cosine of the argument.

• expression: Any column or literal expression.

ACOSH(expression)

Returns the arc hyperbolic cosine of the argument.

• expression: Any column or literal expression.

ASIN(expression)

Returns arcsine in radians.

• expression: Any column or literal expression.

ASINH(expression)

Returns the arc hyperbolic sine of the argument.

• expression: Any column or literal expression.

ATAN(expression)

Returns arc tangent of the argument.

• expression: Any column or literal expression.

ATANH(expression)

Returns the arc hyperbolic tangent of the argument.

• expression: Any column or literal expression.

ATAN2(expression1, expression2)

Returns the arc tangent of the two arguments.

• expression1: Any column or literal expression.
• expression2: Any column or literal expression.

CEIL(expression)

Rounds the argument up to the nearest whole number and returns the rounded value.

• expression: Any column or literal expression.

CEILING(expression)

Synonym for CEIL function.

• expression: Any column or literal expression.

COS(expression)

Returns the cosine of the argument.

• expression: Any column or literal expression.

COSH(expression)

Returns the hyperbolic cosine of the argument.

• expression: Any column or literal expression.

DEGREES(expression)

Returns expression, converted from radians to degrees.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

EXP(expression)

Returns the result of raising the constant "e" - the base of the natural logarithm - to the power of expression.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

FLOOR(expression)

Rounds the argument down to the nearest whole number and returns the rounded value.

• expression: Any column or literal expression.

LN(expression)

Returns the natural logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

LOG(expression)

Returns the natural logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

LOG2(expression)

Returns the Base-2 logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

LOG10(expression)

Returns the Base-10 logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

PI()

Returns PI.
Note: this function is only available when UseLegacySQL=True.

POW(expression1, expression2)

Returns the result of raising expression1 to the power of expression2.

• expression1: Any column or literal expression.
• expression2: Any column or literal expression.

POWER(expression1, expression2)

Synonym of POW function.

• expression1: Any column or literal expression.
• expression2: Any column or literal expression.

RADIANS(expression)

Returns expression, converted from degrees to radians.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

RAND([expression])

Returns a random float value in the range 0.0 >= value < 1.0. Each int32_seed value always generates the same sequence of random numbers within a given query, as long as you don't use a LIMIT clause. If int32_seed is not specified, BigQuery uses the current timestamp as the seed value.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

ROUND(expression [, integer_digits])

Rounds the argument either up or down to the nearest whole number (or if specified, to the specified number of digits) and returns the rounded value.

• expression: Any column or literal expression.
• integer_digits: The number of digits to round to.

GREATEST(value1[, value2 [, ...]])

Returns NULL if any of the inputs is NULL. Otherwise, returns NaN if any of the inputs is NaN. Otherwise, returns the largest value among X1,...,XN according to the < comparison.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• value1: The first value to compare.
• value2: The second value to compare.

LEAST(value1[, value2 [, ...]])

Returns NULL if any of the inputs is NULL. Otherwise, returns NaN if any of the inputs is NaN. Otherwise, returns the smallest value among X1,...,XN according to the > comparison.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• value1: The first value to compare.
• value2: The second value to compare.

SIN(expression)

Returns the sine of the argument.

• expression: Any column or literal expression.

SINH(expression)

Returns the hyperbolic sine of the argument.

• expression: Any column or literal expression.

SQRT(expression)

Returns the square root of the expression.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

TAN(expression)

Returns the tangent of the argument.

• expression: Any column or literal expression.

TANH(expression)

Returns the hyperbolic tangent of the argument.

• expression: Any column or literal expression.

TRUNC(expression [, integer_digits])

Rounds X to the nearest integer whose absolute value is not greater than Xs. When the integer_digits parameter is specified this function is similar to ROUND(X, N) but always rounds towards zero. Unlike ROUND(X, N) it never overflows.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: Any column or literal expression.
• integer_digits: The number of digits to round to.

BYTE_LENGTH(str)

Returns the length of the value in bytes, regardless of whether the type of the value is STRING or BYTES.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str: The string to calculate the length on.

CHAR_LENGTH(str)

Returns the length of the STRING in characters.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str: The string to calculate the length on.

CONCAT(str1, str2 [, str3] [, ...])

Returns the concatenation of two or more strings, or NULL if any of the values are NULL.

• str1: The first string to concatenate.
• str2: The second string to concatenate.
• str3: The third string to concatenate.

ENDS_WITH(str1, str2)

Takes two values. Returns TRUE if the second value is a suffix of the first.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str1: The string to search in.
• str2: The string to search for.

FROM_BASE64(string_expr)

Converts the base64-encoded input string_expr into BYTES format. To convert BYTES to a base64-encoded STRING, use TO_BASE64.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• string_expr: The string to convert from base64 encoding.

FROM_HEX(string_expr)

Converts a hexadecimal-encoded STRING into BYTES format. Returns an error if the input STRING contains characters outside the range (0..9, A..F, a..f). The lettercase of the characters does not matter. To convert BYTES to a hexadecimal-encoded STRING, use TO_HEX.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• string_expr: The string to convert from hexadecimal encoding.

INSTR(str1, str2)

Returns the one-based index of the first occurrence of str2 in str1, or returns 0 if str2 does not occur in str1.
Note: this function is only available when UseLegacySQL=True.

• str1: The string to search in.
• str2: The string to search for.

LEFT(str, numeric_expression)

Returns the leftmost numeric_expr characters of str. If the number is longer than str, the full string will be returned. Example: LEFT('seattle', 3) returns sea.
Note: this function is only available when UseLegacySQL=True.

• str: The string to perform the LEFT operation on.
• numeric_expression: The number of characters to return.

LENGTH(str)

Returns a numerical value for the length of the string. Example: if str is '123456', LENGTH returns 6.

• str: The string to calculate the length on.

LOWER(str)

Returns the original string with all characters in lower case.

• str: The string to lower.

LPAD(str1, numeric_expression[, str2])

Pads str1 on the left with str2, repeating str2 until the result string is exactly numeric_expr characters. Example: LPAD('1', 7, '?') returns ??????1.

• str1: The string to pad.
• numeric_expression: The number of str2 instances to pad.
• str2: The pad characters.

LTRIM(str1 [, str2])

Removes characters from the left side of str1. If str2 is omitted, LTRIM removes spaces from the left side of str1. Otherwise, LTRIM removes any characters in str2 from the left side of str1 (case-sensitive).

• str1: The string to trim.
• str2: The characters to trim from str1.

REPEAT(str, repetitions)

Returns a value that consists of original_value, repeated. The repetitions parameter specifies the number of times to repeat original_value. Returns NULL if either original_value or repetitions are NULL. This function return an error if the repetitions value is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str: The string to repeat.
• str2: The number of repititions.

REPLACE(original_value, from_value, to_value)

Replaces all instances of from_value within original_value with to_value.

• original_value: The string to search in.
• from_value: The string to search for.
• to_value: The string to replace instances of from_value.

REVERSE(str)

Returns the reverse of the input STRING or BYTES.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str: The string to reverse.

RIGHT(str, numeric_expression)

Returns the rightmost numeric_expr characters of str. If the number is longer than the string, it will return the whole string. Example: RIGHT('kirkland', 4) returns land.
Note: this function is only available when UseLegacySQL=True.

• str: The string to perform the RIGHT operation on.
• numeric_expression: The number of characters to return.

RPAD(str1, numeric_expression, str2)

Pads str1 on the right with str2, repeating str2 until the result string is exactly numeric_expr characters. Example: RPAD('1', 7, '?') returns 1??????.

• str1: The string to pad.
• numeric_expression: The number of str2 instances to pad.
• str2: The pad characters.

RTRIM(str1 [, str2])

Removes trailing characters from the right side of str1. If str2 is omitted, RTRIM removes trailing spaces from str1. Otherwise, RTRIM removes any characters in str2 from the right side of str1 (case-sensitive).

• str1: The string to trim.
• str2: The characters to trim from str1.

SPLIT(str [, delimiter])

Splits a string into repeated substrings. If delimiter is specified, the SPLIT function breaks str into substrings, using delimiter as the delimiter.

• str: The string to split.
• delimiter: The delimiter to split the string on. Default delimiter is a comma (,).

STARTS_WITH(str1, str2)

Takes two values. Returns TRUE if the second value is a prefix of the first.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str1: The string to search in.
• str2: The string to search for.

STRPOS(str1, str2)

Returns the 1-based index of the first occurrence of value2 inside value1. Returns 0 if value2 is not found.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str1: The string to search in.
• str2: The string to search for.

SUBSTR(str, index [, max_len])

Returns a substring of str, starting at index. If the optional max_len parameter is used, the returned string is a maximum of max_len characters long. Counting starts at 1, so the first character in the string is in position 1 (not zero). If index is 5, the substring begins with the 5th character from the left in str. If index is -4, the substring begins with the 4th character from the right in str. Example: SUBSTR('awesome', -4, 4) returns the substring some.

• str: The original string.
• index: The starting index.
• max_len: The maximum length of the return string.

TO_BASE64(string_expr)

Converts a sequence of BYTES into a base64-encoded STRING. To convert a base64-encoded STRING into BYTES, use FROM_BASE64.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• string_expr: The string to convert to base64 encoding.

TO_HEX(string_expr)

Converts a sequence of BYTES into a hexadecimal STRING. Converts each byte in the STRING as two hexadecimal characters in the range (0..9, a..f). To convert a hexadecimal-encoded STRING to BYTES, use FROM_HEX.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• string_expr: The string to convert to hexadecimal encoding.

TRIM(str1 [, str2])

Removes all leading and trailing characters that match value2. If value2 is not specified, all leading and trailing whitespace characters (as defined by the Unicode standard) are removed. If the first argument is of type BYTES, the second argument is required. If value2 contains more than one character or byte, the function removes all leading or trailing characters or bytes contained in value2.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str1: The string to trim.
• str2: The optional string characters to trim from str1.

UPPER(str)

Returns the original string with all characters in upper case.

• str: The string to upper.

JSON_EXTRACT(json, json_path)

Selects a value in json according to the JSONPath expression json_path. json_path must be a string constant. Returns the value in JSON string format.

• json: The JSON to select a value from.
• json_path: The JSON path of the value contained in json.

JSON_EXTRACT_SCALAR(json, json_path)

Selects a value in json according to the JSONPath expression json_path. json_path must be a string constant, and bracket notation is not supported. Returns a scalar JSON value.

• json: The JSON to select a value from.
• json_path: The JSON path of the value contained in json.

REGEXP_CONTAINS(str, reg_exp)

Returns TRUE if value is a partial match for the regular expression, regex. You can search for a full match by using ^ (beginning of text) and $ (end of text). If the regex argument is invalid, the function returns an error.
Note: this function is only available when UseLegacySQL=True.

• str: The string to match in the regular expression.
• reg_exp: The regular expression to match.

REGEXP_EXTRACT(str, reg_exp)

Returns the portion of str that matches the capturing group within the regular expression.

• str: The string to match in the regular expression.
• reg_exp: The regular expression to match.

REGEXP_EXTRACT_ALL(str, reg_exp)

Returns an array of all substrings of value that match the regular expression, regex. The REGEXP_EXTRACT_ALL function only returns non-overlapping matches. For example, using this function to extract ana from banana returns only one substring, not two.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str: The string to match in the regular expression.
• reg_exp: The regular expression to match.

REGEXP_REPLACE(orig_str, reg_exp, replace_str)

Returns a string where any substring of orig_str that matches reg_exp is replaced with replace_str. For example, REGEXP_REPLACE ('Hello', 'lo', 'p') returns Help.

• orig_str: The original string to match in the regular expression.
• reg_exp: The regular expression to match.
• replace_str: The replacement for the matched orig_str in the regular expression.

FORMAT_IP(integer_value)

Converts 32 least significant bits of integer_value to human-readable IPv4 address string.
Note: this function is only available when UseLegacySQL=True.

• integer_value: The integer value to convert to an IPv4 address.

PARSE_IP(readable_ip)

Converts a string representing IPv4 address to unsigned integer value. For example, PARSE_IP('0.0.0.1') will return 1. If string is not a valid IPv4 address, PARSE_IP will return NULL.
Note: this function is only available when UseLegacySQL=True.

• readable_ip: The IPv4 address to convert to an integer.

NET.IPV4_FROM_INT64(integer_value)

Converts an IPv4 address from integer format to binary (BYTES) format in network byte order. In the integer input, the least significant bit of the IP address is stored in the least significant bit of the integer, regardless of host or client architecture. For example, 1 means 0.0.0.1, and 0x1FF means 0.0.1.255.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• integer_value: The integer value to convert to an IPv4 address.

NET.IPV4_TO_INT64(readable_ip)

Converts an IPv4 address from binary (BYTES) format in network byte order to integer format. In the integer output, the least significant bit of the IP address is stored in the least significant bit of the integer, regardless of host or client architecture. For example, 1 means 0.0.0.1, and 0x1FF means 0.0.1.255. The output is in the range [0, 0xFFFFFFFF]. If the input length is not 4, this function throws an error. This function does not support IPv6.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• readable_ip: The IPv4 address to convert to an integer.

FARM_FINGERPRINT(expression)

Computes the fingerprint of the STRING or BYTES input using the Fingerprint64 function from the open-source FarmHash library. The output of this function for a particular input will never change.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to use to compute the fingerprint.

MD5(expression)

Computes the hash of the input using the MD5 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 16 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to use to compute the hash.

SHA1(expression)

Computes the hash of the input using the SHA-1 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 20 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to use to compute the hash.

SHA256(expression)

Computes the hash of the input using the SHA-256 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 32 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to use to compute the hash.

SHA512(expression)

Computes the hash of the input using the SHA-512 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 64 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to use to compute the hash.

TIMESTAMP(datetime_expression[, timezone])

Convert a date, datetime, or string to a TIMESTAMP data type.
Note: this function does not support the timezone parameter and requires datetime_expression to be a string when using Legacy SQL (UseLegacySQL=True).

• datetime_expression: The expression to be converted to a timestamp
• timezone: The timezone to be used. If no timezone is specified, the default timezone, UTC, is used

REGEXP_MATCH(str, reg_exp)

Returns true if str matches the regular expression. For string matching without regular expressions, use CONTAINS instead of REGEXP_MATCH.
Note: this function is only available when UseLegacySQL=True.

• str: The string to match in the regular expression.
• reg_exp: The regular expression to match.

CAST(expression AS type)

Cast is used in a query to indicate that the result type of an expression should be converted to some other type.

• expression: The expression to cast.
• type: The type to cast the expression to.

SAFE_CAST(expression, type)

Cast is used in a query to indicate that the result type of an expression should be converted to some other type.SAFE_CAST is identical to CAST, except it returns NULL instead of raising an error.

• expression: The expression to cast.
• type: The type to cast the expression to.

CURRENT_DATE()

Returns a human-readable string of the current date in the format %Y-%m-%d.

DATE(timestamp [, timezone])

Converts a timestamp_expression to a DATE data type.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp from which to return the date.
• timezone: The timezone to use when converting the timestamp. If not specified, the default timezone, UTC, is used.

DATEDIFF(timestamp1, timestamp2)

Returns the number of days between two TIMESTAMP data types. The result is positive if the first TIMESTAMP data type comes after the second TIMESTAMP data type, and otherwise the result is negative.
Note: this function is only available when UseLegacySQL=True.

• timestamp1: The first timestamp.
• timestamp2: The second timestamp.

DATE_DIFF(date1, date2, date_part)

Computes the number of specified date_part differences between two date expressions. This can be thought of as the number of date_part boundaries crossed between the two dates. If the first date occurs before the second date, then the result is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• date1: The first date.
• date2: The second date.
• date_part: The date part. Supported values are: DAY, MONTH, QUARTER, YEAR.

DATE_TRUNC(date, date_part)

Truncates the date to the specified granularity.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• date: The date to truncate.
• date_part: The date part. Supported values are: DAY, WEEK, ISOWEEK, MONTH, QUARTER, YEAR, ISOYEAR.

FORMAT_DATE(format_string, date_expr)

Formats the date_expr according to the specified format_string.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to format the date_expr.
• date_expr: The date to format.

PARSE_DATE(format_string, date_string)

Uses a format_string and a string representation of a date to return a DATE object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to parse the date_string.
• date_string: The date string to parse.

CURRENT_DATETIME([timezone])

Returns the current time as a DATETIME object.

• timezone: The timezone to use when retrieving the current datetime object.

DATETIME(timestamp [, timezone])

Constructs a DATETIME object using a TIMESTAMP object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp from which to return the datetime.
• timezone: The timezone to use when converting the timestamp. If not specified, the default timezone, UTC, is used.

DATETIME_DIFF(datetime1, datetime2, date_part)

Computes the number of specified date_part differences between two date expressions. This can be thought of as the number of date_part boundaries crossed between the two dates. If the first date occurs before the second date, then the result is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• datetime1: The first datetime.
• datetime2: The second datetime.
• date_part: The date part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR.

DATETIME_TRUNC(datetime, part)

Truncates the datetime to the specified granularity.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• date: The datetime to truncate.
• part: The date part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, ISOWEEK, MONTH, QUARTER, YEAR, and ISOYEAR.

FORMAT_DATETIME(format_string, datetime_expr)

Formats the date_expr according to the specified format_string.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to format the date_expr.
• datetime_expr: The datetime to format.

PARSE_DATETIME(format_string, datetime_string)

Uses a format_string and a string representation of a date to return a DATETIME object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to parse the date_string.
• datetime_string: The datetime string to parse.

CURRENT_TIME()

Returns a human-readable string of the server's current time in the format %H:%M:%S.

TIME(timestamp [, timezone])

Constructs a DATETIME object using a TIMESTAMP object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp from which to return the datetime.
• timezone: The timezone to use when converting the timestamp. If not specified, the default timezone, UTC, is used.

TIME_DIFF(time1, time2, time_part)

Computes the number of specified time_part differences between two time expressions. This can be thought of as the number of time_part boundaries crossed between the two times. If the first time occurs before the second time, then the result is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• time1: The first time.
• time2: The second time.
• time_part: The time part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR.

TIME_TRUNC(time, part)

Truncates the time to the specified granularity.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• time: The time to truncate.
• part: The time part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR.

FORMAT_TIME(format_string, time_expr)

Formats the date_expr according to the specified format_string.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to format the date_expr.
• time_expr: The time to format.

PARSE_TIME(format_string, time_string)

Uses a format_string and a string representation of a time to return a TIME object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to parse the time_string.
• time_string: The time string to parse.

CURRENT_TIMESTAMP()

Returns a TIMESTAMP data type of the server's current time in the format %Y-%m-%d %H:%M:%S.

TIMESTAMP_DIFF(timestamp1, timestamp2, time_part)

Computes the number of specified time_part differences between two timestamp expressions. This can be thought of as the number of time_part boundaries crossed between the two timestamp. If the first timestamp occurs before the second timestamp, then the result is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp1: The first timestamp.
• timestamp2: The second timestamp.
• time_part: The timestamp part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR.

FORMAT_TIMESTAMP(format_string, timestamp_expr)

Formats the date_expr according to the specified format_string.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to format the date_expr.
• timestamp_expr: The timestamp to format.

PARSE_TIMESTAMP(format_string, timestamp_string)

Uses a format_string and a string representation of a timestamp to return a TIMESTAMP object.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• format_string: The format string used to parse the timestamp_string.
• timestamp_string: The timestamp string to parse.

DAY(timestamp)

Returns the day of the month of a TIMESTAMP data type as an integer between 1 and 31, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the day of the month.

DAYOFWEEK(timestamp)

Returns the day of the week of a TIMESTAMP data type as an integer between 1 (Sunday) and 7 (Saturday), inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the day of the week.

DAYOFYEAR(timestamp)

Returns the day of the year of a TIMESTAMP data type as an integer between 1 and 366, inclusively. The integer 1 refers to January 1.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the day of the year.

FORMAT_UTC_USEC(unix_timestamp)

Returns a human-readable string representation of a UNIX timestamp in the format YYYY-MM-DD HH:MM:SS.uuuuuu.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The unix timestamp to format.

HOUR(timestamp)

Returns the hour of a TIMESTAMP data type as an integer between 0 and 23, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the hour as an integer.

MINUTE(timestamp)

Returns the minutes of a TIMESTAMP data type as an integer between 0 and 59, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the minutes as an integer.

MONTH(timestamp)

Returns the month of a TIMESTAMP data type as an integer between 1 and 12, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the month as an integer.

MSEC_TO_TIMESTAMP(unix_timestamp)

Converts a UNIX timestamp in milliseconds to a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The unix timestamp to convert.

PARSE_UTC_USEC(date_string)

Converts a date string to a UNIX timestamp in microseconds. date_string must have the format YYYY-MM-DD HH:MM:SS[.uuuuuu]. The fractional part of the second can be up to 6 digits long or can be omitted.
Note: this function is only available when UseLegacySQL=True.

• date_string: The date string to convert.

QUARTER(timestamp)

Returns the quarter of the year of a TIMESTAMP data type as an integer between 1 and 4, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the quarter as an integer.

SEC_TO_TIMESTAMP(unix_timestamp)

Converts a UNIX timestamp in seconds to a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to convert.

SECOND(timestamp)

Returns the seconds of a TIMESTAMP data type as an integer between 0 and 59, inclusively. During a leap second, the integer range is between 0 and 60, inclusively.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the second as an integer.

STRFTIME_UTC_USEC(unix_timestamp, date_format_str)

Returns a human-readable date string in the format date_format_str.date_format_str can include date-related punctuation characters (such as / and -) and special characters accepted by the strftime function in C++ (such as %d for day of month).
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to convert.
• date_format_str: The date format string.

TIMESTAMP_SECONDS(unix_timestamp)

Interprets INT64_expression as the number of seconds since 1970-01-01 00:00:00 UTC.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The unix timestamp to convert.

TIMESTAMP_MILLIS(unix_timestamp)

Interprets INT64_expression as the number of milliseconds since 1970-01-01 00:00:00 UTC.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The unix timestamp to convert.

TIMESTAMP_MICROS(unix_timestamp)

Interprets INT64_expression as the number of microseconds since 1970-01-01 00:00:00 UTC.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The unix timestamp to convert.

TIMESTAMP_TO_MSEC(timestamp)

Converts a TIMESTAMP data type to a UNIX timestamp in milliseconds.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp to convert.

TIMESTAMP_TO_SEC(timestamp)

Converts a TIMESTAMP data type to a UNIX timestamp in seconds.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp to convert.

TIMESTAMP_TO_USEC(timestamp)

Converts a TIMESTAMP data type to a UNIX timestamp in microseconds.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp to convert.

UNIX_DATE(date_string)

Returns the number of days since 1970-01-01.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• date_string: The date string to convert.

UNIX_SECONDS(timestamp)

Returns the number of seconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp to convert.

UNIX_MILLIS(timestamp)

Returns the number of milliseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp to convert.

UNIX_MICROS(timestamp)

Returns the number of microseconds since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• timestamp: The timestamp to convert.

USEC_TO_TIMESTAMP(unix_timestamp)

Converts a UNIX timestamp in microseconds to a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to convert.

UTC_USEC_TO_DAY(unix_timestamp)

Shifts a UNIX timestamp in microseconds to the beginning of the day it occurs in.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to shift.

UTC_USEC_TO_HOUR(unix_timestamp)

Shifts a UNIX timestamp in microseconds to the beginning of the hour it occurs in.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to shift.

UTC_USEC_TO_MONTH(unix_timestamp)

Shifts a UNIX timestamp in microseconds to the beginning of the month it occurs in.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to shift.

UTC_USEC_TO_WEEK(unix_timestamp, day_of_week)

Returns a UNIX timestamp in microseconds that represents a day in the week of the unix_timestamp argument.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to shift.
• day_of_week: A day of the week from 0 (Sunday) to 6 (Saturday).

UTC_USEC_TO_YEAR(unix_timestamp)

Returns a UNIX timestamp in microseconds that represents the year of the unix_timestamp argument.
Note: this function is only available when UseLegacySQL=True.

• unix_timestamp: The unix timestamp to convert.

WEEK(timestamp)

Returns the week of a TIMESTAMP data type as an integer between 1 and 53, inclusively. Weeks begin on Sunday, so if January 1 is on a day other than Sunday, week 1 has fewer than 7 days and the first Sunday of the year is the first day of week 2.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the week as an integer.

YEAR(timestamp)

Returns the year of a TIMESTAMP data type.
Note: this function is only available when UseLegacySQL=True.

• timestamp: The timestamp from which to return the year as an integer.

ABS(expression)

Returns the absolute value of the argument.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

ACOS(expression)

Returns the arc cosine of the argument.

• expression: Any column or literal expression.

ACOSH(expression)

Returns the arc hyperbolic cosine of the argument.

• expression: Any column or literal expression.

ASIN(expression)

Returns arcsine in radians.

• expression: Any column or literal expression.

ASINH(expression)

Returns the arc hyperbolic sine of the argument.

• expression: Any column or literal expression.

ATAN(expression)

Returns arc tangent of the argument.

• expression: Any column or literal expression.

ATANH(expression)

Returns the arc hyperbolic tangent of the argument.

• expression: Any column or literal expression.

ATAN2(expression1, expression2)

Returns the arc tangent of the two arguments.

• expression1: Any column or literal expression.
• expression2: Any column or literal expression.

CEIL(expression)

Rounds the argument up to the nearest whole number and returns the rounded value.

• expression: Any column or literal expression.

CEILING(expression)

Synonym for CEIL function.

• expression: Any column or literal expression.

COS(expression)

Returns the cosine of the argument.

• expression: Any column or literal expression.

COSH(expression)

Returns the hyperbolic cosine of the argument.

• expression: Any column or literal expression.

DEGREES(expression)

Returns expression, converted from radians to degrees.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

EXP(expression)

Returns the result of raising the constant "e" - the base of the natural logarithm - to the power of expression.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

FLOOR(expression)

Rounds the argument down to the nearest whole number and returns the rounded value.

• expression: Any column or literal expression.

LN(expression)

Returns the natural logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

LOG(expression)

Returns the natural logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

LOG2(expression)

Returns the Base-2 logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

LOG10(expression)

Returns the Base-10 logarithm of the argument.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

PI()

Returns PI.
Note: this function is only available when UseLegacySQL=True.

POW(expression1, expression2)

Returns the result of raising expression1 to the power of expression2.

• expression1: Any column or literal expression.
• expression2: Any column or literal expression.

POWER(expression1, expression2)

Synonym of POW function.

• expression1: Any column or literal expression.
• expression2: Any column or literal expression.

RADIANS(expression)

Returns expression, converted from degrees to radians.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

RAND([expression])

Returns a random float value in the range 0.0 >= value < 1.0. Each int32_seed value always generates the same sequence of random numbers within a given query, as long as you don't use a LIMIT clause. If int32_seed is not specified, BigQuery uses the current timestamp as the seed value.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

ROUND(expression [, integer_digits])

Rounds the argument either up or down to the nearest whole number (or if specified, to the specified number of digits) and returns the rounded value.

• expression: Any column or literal expression.
• integer_digits: The number of digits to round to.

GREATEST(value1[, value2 [, ...]])

Returns NULL if any of the inputs is NULL. Otherwise, returns NaN if any of the inputs is NaN. Otherwise, returns the largest value among X1,...,XN according to the < comparison.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• value1: The first value to compare.
• value2: The second value to compare.

LEAST(value1[, value2 [, ...]])

Returns NULL if any of the inputs is NULL. Otherwise, returns NaN if any of the inputs is NaN. Otherwise, returns the smallest value among X1,...,XN according to the > comparison.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• value1: The first value to compare.
• value2: The second value to compare.

SIN(expression)

Returns the sine of the argument.

• expression: Any column or literal expression.

SINH(expression)

Returns the hyperbolic sine of the argument.

• expression: Any column or literal expression.

SQRT(expression)

Returns the square root of the expression.
Note: this function is only available when UseLegacySQL=True.

• expression: Any column or literal expression.

TAN(expression)

Returns the tangent of the argument.

• expression: Any column or literal expression.

TANH(expression)

Returns the hyperbolic tangent of the argument.

• expression: Any column or literal expression.

TRUNC(expression [, integer_digits])

Rounds X to the nearest integer whose absolute value is not greater than Xs. When the integer_digits parameter is specified this function is similar to ROUND(X, N) but always rounds towards zero. Unlike ROUND(X, N) it never overflows.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: Any column or literal expression.
• integer_digits: The number of digits to round to.

BYTE_LENGTH(str)

Returns the length of the value in bytes, regardless of whether the type of the value is STRING or BYTES.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str: The string to calculate the length on.

CHAR_LENGTH(str)

Returns the length of the STRING in characters.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str: The string to calculate the length on.

CONCAT(str1, str2 [, str3] [, ...])

Returns the concatenation of two or more strings, or NULL if any of the values are NULL.

• str1: The first string to concatenate.
• str2: The second string to concatenate.
• str3: The third string to concatenate.

ENDS_WITH(str1, str2)

Takes two values. Returns TRUE if the second value is a suffix of the first.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str1: The string to search in.
• str2: The string to search for.

FROM_BASE64(string_expr)

Converts the base64-encoded input string_expr into BYTES format. To convert BYTES to a base64-encoded STRING, use TO_BASE64.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• string_expr: The string to convert from base64 encoding.

FROM_HEX(string_expr)

Converts a hexadecimal-encoded STRING into BYTES format. Returns an error if the input STRING contains characters outside the range (0..9, A..F, a..f). The lettercase of the characters does not matter. To convert BYTES to a hexadecimal-encoded STRING, use TO_HEX.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• string_expr: The string to convert from hexadecimal encoding.

INSTR(str1, str2)

Returns the one-based index of the first occurrence of str2 in str1, or returns 0 if str2 does not occur in str1.
Note: this function is only available when UseLegacySQL=True.

• str1: The string to search in.
• str2: The string to search for.

LEFT(str, numeric_expression)

Returns the leftmost numeric_expr characters of str. If the number is longer than str, the full string will be returned. Example: LEFT('seattle', 3) returns sea.
Note: this function is only available when UseLegacySQL=True.

• str: The string to perform the LEFT operation on.
• numeric_expression: The number of characters to return.

LENGTH(str)

Returns a numerical value for the length of the string. Example: if str is '123456', LENGTH returns 6.

• str: The string to calculate the length on.

LOWER(str)

Returns the original string with all characters in lower case.

• str: The string to lower.

LPAD(str1, numeric_expression[, str2])

Pads str1 on the left with str2, repeating str2 until the result string is exactly numeric_expr characters. Example: LPAD('1', 7, '?') returns ??????1.

• str1: The string to pad.
• numeric_expression: The number of str2 instances to pad.
• str2: The pad characters.

LTRIM(str1 [, str2])

Removes characters from the left side of str1. If str2 is omitted, LTRIM removes spaces from the left side of str1. Otherwise, LTRIM removes any characters in str2 from the left side of str1 (case-sensitive).

• str1: The string to trim.
• str2: The characters to trim from str1.

REPEAT(str, repetitions)

Returns a value that consists of original_value, repeated. The repetitions parameter specifies the number of times to repeat original_value. Returns NULL if either original_value or repetitions are NULL. This function return an error if the repetitions value is negative.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str: The string to repeat.
• str2: The number of repititions.

REPLACE(original_value, from_value, to_value)

Replaces all instances of from_value within original_value with to_value.

• original_value: The string to search in.
• from_value: The string to search for.
• to_value: The string to replace instances of from_value.

REVERSE(str)

Returns the reverse of the input STRING or BYTES.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str: The string to reverse.

RIGHT(str, numeric_expression)

Returns the rightmost numeric_expr characters of str. If the number is longer than the string, it will return the whole string. Example: RIGHT('kirkland', 4) returns land.
Note: this function is only available when UseLegacySQL=True.

• str: The string to perform the RIGHT operation on.
• numeric_expression: The number of characters to return.

RPAD(str1, numeric_expression, str2)

Pads str1 on the right with str2, repeating str2 until the result string is exactly numeric_expr characters. Example: RPAD('1', 7, '?') returns 1??????.

• str1: The string to pad.
• numeric_expression: The number of str2 instances to pad.
• str2: The pad characters.

RTRIM(str1 [, str2])

Removes trailing characters from the right side of str1. If str2 is omitted, RTRIM removes trailing spaces from str1. Otherwise, RTRIM removes any characters in str2 from the right side of str1 (case-sensitive).

• str1: The string to trim.
• str2: The characters to trim from str1.

SPLIT(str [, delimiter])

Splits a string into repeated substrings. If delimiter is specified, the SPLIT function breaks str into substrings, using delimiter as the delimiter.

• str: The string to split.
• delimiter: The delimiter to split the string on. Default delimiter is a comma (,).

STARTS_WITH(str1, str2)

Takes two values. Returns TRUE if the second value is a prefix of the first.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str1: The string to search in.
• str2: The string to search for.

STRPOS(str1, str2)

Returns the 1-based index of the first occurrence of value2 inside value1. Returns 0 if value2 is not found.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str1: The string to search in.
• str2: The string to search for.

SUBSTR(str, index [, max_len])

Returns a substring of str, starting at index. If the optional max_len parameter is used, the returned string is a maximum of max_len characters long. Counting starts at 1, so the first character in the string is in position 1 (not zero). If index is 5, the substring begins with the 5th character from the left in str. If index is -4, the substring begins with the 4th character from the right in str. Example: SUBSTR('awesome', -4, 4) returns the substring some.

• str: The original string.
• index: The starting index.
• max_len: The maximum length of the return string.

TO_BASE64(string_expr)

Converts a sequence of BYTES into a base64-encoded STRING. To convert a base64-encoded STRING into BYTES, use FROM_BASE64.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• string_expr: The string to convert to base64 encoding.

TO_HEX(string_expr)

Converts a sequence of BYTES into a hexadecimal STRING. Converts each byte in the STRING as two hexadecimal characters in the range (0..9, a..f). To convert a hexadecimal-encoded STRING to BYTES, use FROM_HEX.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• string_expr: The string to convert to hexadecimal encoding.

TRIM(str1 [, str2])

Removes all leading and trailing characters that match value2. If value2 is not specified, all leading and trailing whitespace characters (as defined by the Unicode standard) are removed. If the first argument is of type BYTES, the second argument is required. If value2 contains more than one character or byte, the function removes all leading or trailing characters or bytes contained in value2.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str1: The string to trim.
• str2: The optional string characters to trim from str1.

UPPER(str)

Returns the original string with all characters in upper case.

• str: The string to upper.

JSON_EXTRACT(json, json_path)

Selects a value in json according to the JSONPath expression json_path. json_path must be a string constant. Returns the value in JSON string format.

• json: The JSON to select a value from.
• json_path: The JSON path of the value contained in json.

JSON_EXTRACT_SCALAR(json, json_path)

Selects a value in json according to the JSONPath expression json_path. json_path must be a string constant, and bracket notation is not supported. Returns a scalar JSON value.

• json: The JSON to select a value from.
• json_path: The JSON path of the value contained in json.

REGEXP_CONTAINS(str, reg_exp)

Returns TRUE if value is a partial match for the regular expression, regex. You can search for a full match by using ^ (beginning of text) and $ (end of text). If the regex argument is invalid, the function returns an error.
Note: this function is only available when UseLegacySQL=True.

• str: The string to match in the regular expression.
• reg_exp: The regular expression to match.

REGEXP_EXTRACT(str, reg_exp)

Returns the portion of str that matches the capturing group within the regular expression.

• str: The string to match in the regular expression.
• reg_exp: The regular expression to match.

REGEXP_EXTRACT_ALL(str, reg_exp)

Returns an array of all substrings of value that match the regular expression, regex. The REGEXP_EXTRACT_ALL function only returns non-overlapping matches. For example, using this function to extract ana from banana returns only one substring, not two.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• str: The string to match in the regular expression.
• reg_exp: The regular expression to match.

REGEXP_REPLACE(orig_str, reg_exp, replace_str)

Returns a string where any substring of orig_str that matches reg_exp is replaced with replace_str. For example, REGEXP_REPLACE ('Hello', 'lo', 'p') returns Help.

• orig_str: The original string to match in the regular expression.
• reg_exp: The regular expression to match.
• replace_str: The replacement for the matched orig_str in the regular expression.

FORMAT_IP(integer_value)

Converts 32 least significant bits of integer_value to human-readable IPv4 address string.
Note: this function is only available when UseLegacySQL=True.

• integer_value: The integer value to convert to an IPv4 address.

PARSE_IP(readable_ip)

Converts a string representing IPv4 address to unsigned integer value. For example, PARSE_IP('0.0.0.1') will return 1. If string is not a valid IPv4 address, PARSE_IP will return NULL.
Note: this function is only available when UseLegacySQL=True.

• readable_ip: The IPv4 address to convert to an integer.

NET.IPV4_FROM_INT64(integer_value)

Converts an IPv4 address from integer format to binary (BYTES) format in network byte order. In the integer input, the least significant bit of the IP address is stored in the least significant bit of the integer, regardless of host or client architecture. For example, 1 means 0.0.0.1, and 0x1FF means 0.0.1.255.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• integer_value: The integer value to convert to an IPv4 address.

NET.IPV4_TO_INT64(readable_ip)

Converts an IPv4 address from binary (BYTES) format in network byte order to integer format. In the integer output, the least significant bit of the IP address is stored in the least significant bit of the integer, regardless of host or client architecture. For example, 1 means 0.0.0.1, and 0x1FF means 0.0.1.255. The output is in the range [0, 0xFFFFFFFF]. If the input length is not 4, this function throws an error. This function does not support IPv6.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• readable_ip: The IPv4 address to convert to an integer.

FARM_FINGERPRINT(expression)

Computes the fingerprint of the STRING or BYTES input using the Fingerprint64 function from the open-source FarmHash library. The output of this function for a particular input will never change.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to use to compute the fingerprint.

MD5(expression)

Computes the hash of the input using the MD5 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 16 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to use to compute the hash.

SHA1(expression)

Computes the hash of the input using the SHA-1 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 20 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to use to compute the hash.

SHA256(expression)

Computes the hash of the input using the SHA-256 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 32 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to use to compute the hash.

SHA512(expression)

Computes the hash of the input using the SHA-512 algorithm. The input can either be STRING or BYTES. The string version treats the input as an array of bytes. This function returns 64 bytes.
Note: this function is only available when using Standard SQL (UseLegacySQL=False).

• expression: The expression to use to compute the hash.

TIMESTAMP(datetime_expression[, timezone])

Convert a date, datetime, or string to a TIMESTAMP data type.
Note: this function does not support the timezone parameter and requires datetime_expression to be a string when using Legacy SQL (UseLegacySQL=True).

• datetime_expression: The expression to be converted to a timestamp
• timezone: The timezone to be used. If no timezone is specified, the default timezone, UTC, is used

Google BigQuery API Syntax

The Google BigQuery API offers additional SQL operators and functions. A complete list of the available syntax is located at: https://cloud.google.com/bigquery/query-reference

A SELECT statement can consist of the following basic clauses.

• SELECT
• INTO
• FROM
• JOIN
• WHERE
• GROUP BY
• HAVING
• UNION
• ORDER BY
• LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the connector:

SELECT {
  [ TOP <numeric_literal> | DISTINCT ]
  { 
    * 
    | { 
        <expression> [ [ AS ] <column_reference> ] 
        | { <table_name> | <correlation_name> } .* 
      } [ , ... ] 
  }
  { 
    FROM <table_reference> [ [ AS ] <identifier> ] 
  } [ , ... ]
  [ [  
      INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } 
    ] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ] 
  ] [ ... ] 
  [ WHERE <search_condition> ]
  [ GROUP BY <column_reference> [ , ... ]
  [ HAVING <search_condition> ]
  [ UNION [ ALL ] <select_statement> ]
  [ 
    ORDER BY 
    <column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
  ]
  [ 
    LIMIT <expression>
    [ 
      { OFFSET | , }
      <expression> 
    ]
  ] 
} | SCOPE_IDENTITY() 

<expression> ::=
  | <column_reference>
  | @ <parameter> 
  | ?
  | COUNT( * | { [ DISTINCT ] <expression> } )
  | { AVG | MAX | MIN | SUM | COUNT } ( <expression> ) 
  | NULLIF ( <expression> , <expression> ) 
  | COALESCE ( <expression> , ... ) 
  | CASE <expression>
      WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]
    [ ELSE { <expression> | NULL } ]
    END 
  | {RANK() | DENSE_RANK()} OVER ([PARTITION BY <column_reference>] {ORDER BY <column_reference>})
  | <literal>
  | <sql_function> 

<search_condition> ::= 
  {
    <expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN | IS DISTINCT FROM | IS NOT DISTINCT FROM } [ <expression> ]
  } [ { AND | OR } ... ] 

Examples

1. Return all columns:
   

   SELECT * FROM [publicdata].[samples].github_nested

2. Rename a column:
   

   SELECT [repository.name] AS MY_repository.name FROM [publicdata].[samples].github_nested

3. Cast a column's data as a different data type:
   

   SELECT CAST(repository.watchers AS VARCHAR) AS Str_repository.watchers FROM [publicdata].[samples].github_nested

4. Search data:
   

   SELECT * FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'

5. The Google BigQuery APIs support the following operators in the WHERE clause: =, >, <, >=, <=, <>, !=, EXISTS, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR, +, -, *, /, %, ||.
   

   SELECT * FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework';

6. Return the number of items matching the query criteria:
   

   SELECT COUNT(*) AS MyCount FROM [publicdata].[samples].github_nested 

7. Return the number of unique items matching the query criteria:
   

   SELECT COUNT(DISTINCT repository.name) FROM [publicdata].[samples].github_nested 

8. Return the unique items matching the query criteria:
   

   SELECT DISTINCT repository.name FROM [publicdata].[samples].github_nested 

9. Sort a result set in ascending order:
   

   SELECT actor.attributes.email, repository.name FROM [publicdata].[samples].github_nested  ORDER BY repository.name ASC

10. Restrict a result set to the specified number of rows:
    

    SELECT actor.attributes.email, repository.name FROM [publicdata].[samples].github_nested LIMIT 10 

11. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
    

    SELECT * FROM [publicdata].[samples].github_nested WHERE repository.name = @param

See Explicitly Caching Data for information on using the SELECT statement in offline mode.

Pseudo Columns

Some input-only fields are available in SELECT statements. These fields, called pseudo columns, do not appear as regular columns in the results, yet may be specified as part of the WHERE clause. You can use pseudo columns to access additional features from Google BigQuery.

    SELECT * FROM [publicdata].[samples].github_nested WHERE Pseudo = '@Pseudo'
    

Aggregate Functions

For SELECT examples using aggregate functions, see Aggregate Functions.

JOIN Queries

See JOIN Queries for SELECT query examples using JOINs.

Projection Functions

See Projection Functions for SELECT examples with projection functions.

Predicate Functions

For SELECT examples using predicate functions, see Predicate Functions.

Google BigQuery API Syntax

The Google BigQuery API offers additional SQL operators and functions. A complete list of the available syntax is located at: https://cloud.google.com/bigquery/query-reference

Examples of Aggregate Functions

Below are several examples of SQL aggregate functions. You can use these with a GROUP BY clause to aggregate rows based on the specified GROUP BY criterion. This can be a reporting tool.

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'

COUNT(DISTINCT)

Returns the number of distinct, non-null field values matching the query criteria.

SELECT COUNT(DISTINCT actor.attributes.email) AS DistinctValues FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'

AVG

Returns the average of the column values.

SELECT repository.name, AVG(repository.watchers) FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name

MIN

Returns the minimum column value.

SELECT MIN(repository.watchers), repository.name FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name

MAX

Returns the maximum column value.

SELECT repository.name, MAX(repository.watchers) FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name

SUM

Returns the total sum of the column values.

SELECT SUM(repository.watchers) FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'

COUNT(DISTINCT)

Returns the number of distinct, non-null field values matching the query criteria.

SELECT COUNT(DISTINCT actor.attributes.email) AS DistinctValues FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'

AVG

Returns the average of the column values.

SELECT repository.name, AVG(repository.watchers) FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'  GROUP BY repository.name

MIN

Returns the minimum column value.

SELECT MIN(repository.watchers), repository.name FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name

MAX

Returns the maximum column value.

SELECT repository.name, MAX(repository.watchers) FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name

SUM

Returns the total sum of the column values.

SELECT SUM(repository.watchers) FROM [publicdata].[samples].github_nested WHERE repository.name = 'EntityFramework'

CORR

Returns the Pearson correlation coefficient of a set of number pairs.

SELECT repository.name, CORR(repository.watchers, repository.size) FROM [publicdata].[samples].github_nested

COVAR_POP

Computes the population covariance of the values computed by a set of number pairs.

SELECT repository.name, COVAR_POP(repository.watchers, repository.size) FROM [publicdata].[samples].github_nested

COVAR_SAMP

Computes the sample covariance of the values computed by a set of number pairs.

SELECT repository.name, COVAR_SAMP(repository.watchers, repository.size) FROM [publicdata].[samples].github_nested

NTH

Returns the nth sequential value in the scope of the function, where n is a constant. The NTH function starts counting at 1, so there is no zeroth term. If the scope of the function has less than n values, the function returns NULL.

SELECT repository.name, NTH(n, actor.attributes.email) FROM [publicdata].[samples].github_nested

STDDEV

Returns the standard deviation of the computed values. Rows with a NULL value are not included in the calculation.

SELECT repository.name, STDDEV(repository.watchers) FROM [publicdata].[samples].github_nested

The connector supports the complete join syntax in Google BigQuery. Google BigQuery supports inner joins, outer joins, and cross joins. The default is inner. Multiple join operations are supported.

SELECT field_1 [..., field_n] FROM
   table_1 [[AS] alias_1]
 [[INNER|[FULL|RIGHT|LEFT] OUTER|CROSS] JOIN [EACH]
   table_2 [[AS] alias_2]
  [ON join_condition_1 [... AND join_condition_n]]
 ]+
 

Note that the default join is an inner join. The following limitations exist on joins in Google BigQuery:

• Cross joins must not contain an ON clause.
• Normal joins require that the right-side table must contain less than 8 MB of compressed data. If you are working with tables larger than 8 MB, use the EACH modifier. Note that EACH cannot be used in cross joins.

To create new records, use INSERT statements.

INSERT Syntax

The INSERT statement specifies the columns to be inserted and the new column values. You can specify the column values in a comma-separated list in the VALUES clause, as shown in the following example:

INSERT INTO <table_name> 
( <column_reference> [ , ... ] )
VALUES 
( { <expression> | NULL } [ , ... ] ) 
  

<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

The following is an example query:

INSERT INTO [publicdata].[samples].github_nested (repository.name) VALUES ('CoreCLR')

To modify existing records, use UPDATE statements.

Update Syntax

The UPDATE statement takes as input a comma-separated list of columns and new column values as name-value pairs in the SET clause, as shown in the following example:

UPDATE <table_name> SET <select_statement> | {<column_reference> = <expression> [ , ... ]} WHERE { Id = <expression>  } [ { AND | OR } ... ] 

<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

The following is an example query:

UPDATE [publicdata].[samples].github_nested SET repository.name='CoreCLR' WHERE Id = @myId

To delete information from a table, use DELETE statements.

DELETE Syntax

The DELETE statement requires the table name in the FROM clause and the row's primary key in the WHERE clause, as shown in the following example:

<delete_statement> ::= DELETE FROM <table_name> WHERE { Id = <expression> } [ { AND | OR } ... ]

<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

The following is an example query:

DELETE FROM [publicdata].[samples].github_nested WHERE Id = @myId

When caching is enabled, CACHE statements provide complete control over the data that is cached and the table to which it is cached. The CACHE statement executes the SELECT statement specified and caches its results to a table with the same name in the cache database or to table specified in <cached_table_name>. The connector updates or inserts rows to the cache depending on whether or not they already exist in the cache, so the primary key, which is used to identify existing rows, must be included in the selected columns.

See Caching Data for more information on different caching strategies.

CACHE Statement Syntax

The cache statement may include the following options that alter its behavior:

CACHE [ <cached_table_name> ] [ WITH TRUNCATE | AUTOCOMMIT | SCHEMA ONLY | DROP EXISTING | ALTER SCHEMA ] <select_statement> 

WITH TRUNCATE

If this option is set, the connector removes existing rows in the cache table before adding the selected rows. Use this option if you want to refresh the entire cache table but keep its existing schema.

AUTOCOMMIT

If this option is set, the connector commits each row individually. Use this option if you want to ignore the rows that could not be cached due to some reason. By default, the entire result set is cached as a single transaction.

DROP EXISTING

If this option is set, the connector drops the existing cache table before caching the new results. Use this option if you want to refresh the entire cache table, including its schema.

SCHEMA ONLY

If this option is set, the connector creates the cache table based on the SELECT statement without executing the query.

ALTER SCHEMA

If this option is set, the connector alters the schema of the existing table in the cache if it does not match the schema of the SELECT statement. This option results in new columns or dropped columns, if the schema of the SELECT statement does not match the cached table.

Common Queries

Use the following cache statement to cache all rows of a table:

CACHE SELECT * FROM [publicdata].[samples].github_nested

Use the following cache statement to cache all rows of a table into the cache table Cached[publicdata].[samples].github_nested:

CACHE Cachedgithub_nested SELECT * FROM [publicdata].[samples].github_nested

Use the following cache statement for incremental caching. The DateModified column may not exist in all tables. The cache statement shows how incremental caching would work if there were such a column. Also, notice that, in this case, the WITH TRUNCATE and DROP EXISTING options are specifically omitted, which would have deleted all existing rows.

CACHE Cachedgithub_nested SELECT * FROM [publicdata].[samples].github_nested WHERE DateModified > '2013-04-04'

Use the following cache statements to create a table with all available columns that will then cache only a few of them. The sequence of statements cache only actor.attributes.email and repository.name even though the cache table Cached[publicdata].[samples].github_nested has all the columns in [publicdata].[samples].github_nested.

CACHE Cachedgithub_nested SCHEMA ONLY SELECT * FROM [publicdata].[samples].github_nested
CACHE Cachedgithub_nested SELECT actor.attributes.email, repository.name FROM [publicdata].[samples].github_nested

To execute stored procedures, you can use EXECUTE or EXEC statements.

EXEC and EXECUTE assign stored procedure inputs, referenced by name, to values or parameter names.

Stored Procedure Syntax

To execute a stored procedure as an SQL statement, use the following syntax:

 
{ EXECUTE | EXEC } <stored_proc_name> 
{
  [ @ ] <input_name> = <expression>
} [ , ... ]

<expression> ::=
  | @ <parameter> 
  | ?
  | <literal>

Example Statements

Reference stored procedure inputs by name:

EXECUTE my_proc @second = 2, @first = 1, @third = 3;

Execute a parameterized stored procedure statement:

EXECUTE my_proc second = @p1, first = @p2, third = @p3; 

PIVOT and UNPIVOT can be used to change a table-valued expression into another table.

PIVOT

PIVOT rotates a table-value expression by turning unique values from one column into multiple columns in the output. PIVOT can run aggregations where required on any column value.

PIVOT Synax

 
"SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4]
FROM
(
SELECT DaysToManufacture, StandardCost
FROM Production.Product
) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;"

UNPIVOT

UNPIVOT carries out nearly the opposite to PIVOT by rotating columns of a table-valued expressions into column values.

UNPIVOT Sytax

 
"SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;"

For further information on PIVOT and UNPIVOT, see FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)

Use INSERT INTO SELECT queries to select a list of records from one table and insert those same records into another table as a group. Inserting batches of records in this way may result in improved query performance compared to using many individual INSERT INTO queries.

The table whose records are selected for insertion into another table can be either a real table or a user-defined temporary table.

Inserting Records from Real Tables

To insert a group of records from one real, non-temporary, source table into another destination table, you can use an INSERT INTO SELECT query. This type of query is formatted similarly to a standard INSERT INTO query, except the VALUES clause is substituted with a SELECT query targeting the source table. All records matched by the embedded SELECT query are inserted into the destination table.

If the source table and destination tables have different column names, you must map columns from the source table to the corresponding columns in the destination table you want to insert them into. Perform this mapping by specifying the destination table columns in the same order as the source table columns you want to match them with. For example:

INSERT INTO DestinationTable (A,B,C,D) SELECT Q,R,S,T FROM SourceTable

In this example, the first source column (Q) is inserted into the first destination column (A), the second source column (R) is inserted into the second destination column (B), and so on.

If the source table and destination table both have the same column list with the same names, you can use a streamlined query.

INSERT INTO DestinationTableWithSameColumns SELECT * FROM SourceTable

In this example, there is no need to specify a list of columns for either the source or destination table, because their metadata already matches.

Inserting Records from Temporary Tables

You can manually define and populate temporary tables to hold a list of records for later bulk insertion.

Populate the Temporary Table

To create a temporary table, you must give it a name ending in "#TEMP" and execute an INSERT INTO query using that name, as if that table already existed in the database. After executing the first INSERT INTO, the temporary table exists and can receive subsequent INSERTs. For example:

INSERT INTO [publicdata].[samples].github_nested#TEMP (repository.name, MyCustomField__c) VALUES ('New github_nested', '9000');
INSERT INTO [publicdata].[samples].github_nested#TEMP (repository.name, MyCustomField__c) VALUES ('New github_nested 2', '9001');
INSERT INTO [publicdata].[samples].github_nested#TEMP (repository.name, MyCustomField__c) VALUES ('New github_nested 3', '9002');

This creates a temporary table called [publicdata].[samples].github_nested#TEMP with two columns and three rows of data. Since type cannot be determined on the temporary table itself, all values are considered strings and later converted to the proper type when they are inserted together into the real (non-temporary) table of interest.

Insert Temporary Table Contents into Real Tables

Once your temporary table is populated, execute an INSERT INTO SELECT query targeting the real (non-temporary) table you want to insert the temporary table's records into. This is formatted similarly to a standard INSERT INTO query, except the VALUES clause is substituted with a SELECT query targeting the matching columns in the temporary table. For example:

INSERT INTO [publicdata].[samples].github_nested (repository.name, MyCustomField__c) SELECT repository.name, MyCustomField__c FROM [publicdata].[samples].github_nested#TEMP

In this example, the full contents of [publicdata].[samples].github_nested#TEMP are inserted into the [publicdata].[samples].github_nested.

Results

The LastResultInfo#TEMP temporary table contains details about the most recently executed query that uses the contents of a temporary table in an embedded SELECT clause, as is the case for INSERT INTO SELECT queries that use a temporary table as the source of records. This table is cleared and repopulated each time such a query is executed. LastResultInfo#TEMP includes information such as whether the query in question succeeded, and how many rows were affected by the query.

Temporary Table Lifespan

Temporary tables only last as long as the connection remains open. When the connection to Google BigQuery is closed, all temporary tables are cleared, including the LastResultInfo#TEMP table.

To perform multiple updates in a single request to Google BigQuery,first use the INSERT INTO syntax to insert a temporary table of data into Google BigQuery. This works by first populating a temporary table with the data you are going to submit to Google BigQuery. Once you have all of the data you want to update, use UPDATE SELECT FROM to pass the temporary table data into the table in Google BigQuery.

Populate the Temporary Table

The temporary table you are populating is dynamic and is created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to update, the temporary table must be named in the format [TableName]#TEMP, where TableName is the name of the table you are inserting to. For example:

INSERT INTO [publicdata].[samples].github_nested#TEMP (Id, Name, MyCustomField__c) VALUES ('AX1000001', 'New github_nested', '9000');
INSERT INTO [publicdata].[samples].github_nested#TEMP (Id, Name, MyCustomField__c) VALUES ('AX1000002', 'New github_nested 2', '9001');
INSERT INTO [publicdata].[samples].github_nested#TEMP (Id, Name, MyCustomField__c) VALUES ('AX1000003', 'New github_nested 3', '9002');

This creates a temporary table called [publicdata].[samples].github_nested#TEMP with three columns and three rows of data. Since type cannot be determined on the temporary table itself, all values are stored in memory as strings. The values are later converted to the proper type when they are submitted to the [publicdata].[samples].github_nested table.

Update the Actual Table

Once your temporary table is populated, it is now time to update the actual table in Google BigQuery. You can do this by performing an UPDATE to the actual table and selecting the input data from the temporary table. For example:

UPDATE [publicdata].[samples].github_nested (Id, repository.name, MyCustomField__c) SELECT Id, repository.name, MyCustomField__c FROM [publicdata].[samples].github_nested#TEMP

In this example, the full contents of the [publicdata].[samples].github_nested#TEMP table are passed into the [publicdata].[samples].github_nested table. This results in fewer requests being submitted to Google BigQuery since multiple updates may be submitted with each request, which is much better for performance if you have many records to update.

Results

The results of the query are stored in the LastResultInfo#TEMP temporary table. This table is cleared and repopulated the next time data is modified by passing in a temporary table. Please be aware that the LastResultInfo#TEMP table has no predefined schema. You need to check its metadata at run time before reading data.

Temporary Table Life Span

Temporary tables only last as long as the connection remains open. When the connection to Google BigQuery is closed, all temporary tables are cleared, including the LastResultInfo#TEMP table.

To perform multiple deletes in a single request to Google BigQuery, first use the INSERT INTO syntax to create an in-memory temporary table of data to be deleted. Once you have all of the data you want to delete added to temporary table, use DELETE FROM syntax to delete data from the live table in Google BigQuery. This functionality is also available via the standard Batch Processing API available in JDBC.

Populate the Temporary Table

The temporary table you are populating is dynamic and is created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to delete, the temporary table must be named in the format [TableName]#TEMP, where TableName is the name of the table you are inserting to. For example:

INSERT INTO [publicdata].[samples].github_nested#TEMP (Id) VALUES ('AX1000001');
INSERT INTO [publicdata].[samples].github_nested#TEMP (Id) VALUES ('AX1000002');
INSERT INTO [publicdata].[samples].github_nested#TEMP (Id) VALUES ('AX1000003');

This creates a temporary table called [publicdata].[samples].github_nested#TEMP with one column and three rows of data. Since type cannot be determined on the temporary table itself, all values are stored in memory as strings. They are later converted to the proper type when they are submitted to the [publicdata].[samples].github_nested table.

Delete from the Actual Table

Once your temporary table is populated, it is now time to insert to the actual table in Google BigQuery. You can do this by performing a DELETE from the actual table and selecting the input data from the temporary table. For example:

DELETE FROM [publicdata].[samples].github_nested WHERE EXISTS SELECT Id FROM [publicdata].[samples].github_nested#TEMP

In this example, the full contents of the [publicdata].[samples].github_nested#TEMP table are passed into the [publicdata].[samples].github_nested table. This results in fewer requests being submitted to Google BigQuery since multiple deletes may be submitted with each request, which is much better for performance if you have many records to delete.

Results

The results of the query are stored in the LastResultInfo#TEMP temporary table. This table is cleared and repopulated the next time data is modified by passing in a temporary table. Please be aware that the LastResultInfo#TEMP table has no predefined schema. You need to check its metadata at run time before reading data.

Temporary Table Life Span

Temporary tables only last as long as the connection remains opened. When the connection to Google BigQuery is closed, all temporary tables are cleared, including the LastResultInfo#TEMP table.

Overview

The connector models the Google BigQuery data defined within your Google Cloud organization. It can be further refined to retrieve data from a specified project and dataset.

Once connected, the connector mimics the hierarchy in Google BigQuery by modeling each project in Google BigQuery as its own catalog. Within a catalog, the datasets in the corresponding project are modeled as individual schemas. The tables and views within a dataset are modeled as tables and views within the respective schema. Additionally, the connector includes a static 'CData' catalog, containing a static 'Google BigQuery' schema, which contains information found outside the Google BigQuery hierarchy.

Catalogs

Each project in Google BigQuery is modeled as a catalog. For instance, if you have two projects in Google Cloud which have the IDs 'test-project' and 'cloud-data', then the Google BigQuery connector will show two catalogs, one named 'test-project' and the other named 'cloud-data'. The catalog may be specified in either the ProjectId connection property or the fully qualified table name.

Additionally, the data model contains a single static 'CData' catalog, which contains data on client-side views. Details on how to use it will be discussed further in the next section.

Schemas

Each dataset in a project is modeled as a schema in the project's corresponding catalog. For example, if the project 'test-project' has two datasets, 'DatasetTest' and 'BusinessData', then the 'test-project' catalog will have the schemas 'DatasetTest' and 'BusinessData'. The schema may be specified in either the DatasetId connection property or the fully qualified table name.

The 'CData' catalog contains one static 'Google BigQuery' schema. This schema contains client-side views such as 'PartitionsList' and 'PartitionsValues'. These client-side views can be accessed by setting catalog to 'CData' and schema to 'Google BigQuery'. For instance:

SELECT * FROM [CData].[Google BigQuery].PartitionsList

Tables

Tables are retrieved dynamically from Google BigQuery. For instance, if the table 'Accounts' is in the project 'test-project' and dataset 'BusinessData', then it can be queried as follows:

SELECT * FROM [test-project].[BusinessData].Accounts

By setting the ProjectId and DatasetId properties, a connection can be configured to retrieve data from a specific project and dataset so these do not need to be included in the query. For instance, if ProjectId is set to 'test-project' and DatasetId is set to 'BusinessData', then the query only needs to contain the table name, as shown below.

SELECT * FROM Accounts

Views

Views are client-side tables that cannot be modified. The connector uses these to report metadata about the Google BigQuery projects and datsets it is connected to. The following views are included with the connector:

Table	Description
Datasets	Lists all the accessible datasets for a given project.
PartitionsList	Lists the partitioning definitions for tables.
PartitionsValues	Lists the partitioning ranges for tables.
Projects	Lists all the projects for the authorized user.

The connector also supports server-side views defined within Google BigQuery. These views can be used in SELECT statements the same way as tables. However, view schemas can easily become out of date and the connector must refresh them. See RefreshViewSchemas for details.

Stored Procedures

Stored Procedures are actions that are invoked via SQL queries. The connector uses these to manage Google BigQuery tables and jobs and to perform OAuth operations.

In addition to the client-side stored procedures offered by the connector, support is also provided for server-side stored procedures defined in Google BigQuery. The connector supports both CALL and EXEC using the procedure's parameter names.

Note: The connector only supports IN parameters and resultset return values.

CALL `psychic-valve-137816`.Northwind.MostPopularProduct()
CALL `psychic-valve-137816`.Northwind.GetStockedValue(24, 0.75)

EXEC `psychic-valve-137816`.Northwind.MostPopularProduct
EXEC `psychic-valve-137816`.Northwind.GetStockedValue productId = 24, discountRate = 0.75

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Python Connector for Google BigQuery Views

Name	Description
Datasets	Lists all the accessible datasets for a given project.
PartitionsList	Lists the partitioning definitions for tables.
PartitionsValues	Lists the partitioning ranges for tables.
Projects	Lists all the projects for the authorized user.

Lists all the accessible datasets for a given project.

Columns

Name	Type	Description
Id [KEY]	String	The fully qualified and unique identifier for the dataset, used internally by BigQuery to reference the dataset across projects and regions.
Kind	String	The type of resource this record represents. For datasets, this typically returns 'bigquery#dataset'.
FriendlyName	String	A human-readable, descriptive name for the dataset. This name does not need to be unique and is often used in user interfaces.
DatasetReference_ProjectId	String	The ID of the project that contains the dataset. This serves as the container for the dataset and its resources.
DatasetReference_DatasetId	String	The ID of the dataset within the specified project. This is a unique name scoped to the project, excluding the project name itself.
Location	String	The geographic location where the dataset resides.

Lists the partitioning definitions for tables.

Columns

Name	Type	Description
Id [KEY]	String	A unique identifier for the table partition, which typically includes the partition key and the partition value. This helps distinguish each partition within the table.
ProjectId	String	The ID of the Google Cloud project that owns the table containing the partitioned data.
DatasetId	String	The ID of the BigQuery dataset where the partitioned table is located.
TableName	String	The name of the BigQuery table that is partitioned. This table contains multiple partitions based on the specified column.
ColumnName	String	The name of the column that is used to define partitions in the table. This is typically a date or integer field.
ColumnType	String	The data type of the column used for partitioning. Common values include DATE, INTEGER, or TIMESTAMP depending on the partitioning strategy.
Kind	String	The method of partitioning applied to the table. Options include DATE (partitioned by date field), RANGE (partitioned by numeric ranges), or INGESTION (partitioned by data load time).
RequireFilter	Boolean	If the value is 'true', queries must include a filter on the partition column to avoid full table scans. If the value is 'false', filters are not mandatory when querying the table.

Lists the partitioning ranges for tables.

Columns

Name	Type	Description
Id	String	The unique identifier of the partition, which distinguishes it from other partitions in the same table.
RangeLow	String	The starting boundary of the partition’s value range. This is expressed as an integer for RANGE partitioning or a date for TIME or INGESTION partitioning.
RangeHigh	String	The ending boundary of the partition’s value range. This is expressed as an integer for RANGE partitioning or a date for TIME or INGESTION partitioning.
RangeInterval	String	The size of each partitioned range. Applies only to RANGE partitioning and defines how values are grouped into partitions.
DateResolution	String	The level of granularity applied to TIME or INGESTION partitioning. Valid values include DAY, HOUR, MONTH, and YEAR.
ProjectId	String	The ID of the Google Cloud project that owns the table associated with the partition.
DatasetId	String	The ID of the dataset that contains the partitioned table.
TableName	String	The name of the table that is partitioned and to which this partition belongs.

Lists all the projects for the authorized user.

Columns

Name	Type	Description
Id [KEY]	String	The globally unique identifier of the Google Cloud project, typically used in Application Programming Interface (API) requests and resource naming.
Kind	String	The type of resource represented by this entry. For example, 'bigquery#project'.
FriendlyName	String	The human-readable display name assigned to the project, often used for easier identification in the User Interface (UI).
NumericId	String	The numeric identifier automatically assigned to the project by Google Cloud. This ID is unique across all projects.
ProjectReference_ProjectId	String	A reference value that uniquely identifies the project, commonly used in API calls and schema definitions.

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with Google BigQuery.

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

CData Python Connector for Google BigQuery Stored Procedures

Name	Description
CancelJob	Cancels a running BigQuery job.
DeleteObject	Deletes an object from a bucket.
DeleteTable	Deletes the specified table from Google BigQuery.
GetJob	Retrieves the configuration information and execution state for an existing job.
GetOAuthAccessToken	Obtains the OAuth access token to be used for authentication with various Google services.
GetOAuthAuthorizationURL	Obtains the OAuth authorization URL for authentication with various Google services.
InsertJob	Inserts a Google BigQuery job, which can then be selected later to retrieve the query results.
InsertLoadJob	Inserts a Google BigQuery load job, which adds data from Google Cloud Storage into an existing table.
RefreshOAuthAccessToken	Obtains the OAuth access token to be used for authentication with various Google services.
UploadObject	Uploads objects in a single operation. Use the SimpleUploadLimit connection property to adjust the threshold in bytes in order to perform a multipart upload.

Cancels a running BigQuery job.

Stored Procedure-Specific Information

To execute this stored procedure, enter:

    EXEC CancelJob JobId='your_job_id', Region='asia-southeast1'

Input

Name	Type	Description
JobId	String	The unique identifier of the BigQuery job you want to cancel.
Region	String	The geographic location where the job is running. Required for jobs outside the default US or EU multi-regions.

Result Set Columns

Name	Type	Description
JobId	String	The unique identifier of the job that was cancelled.
Region	String	The geographic location where the job was executing when it was cancelled.
Configuration_query_query	String	The SQL query text associated with the job that was cancelled.
Configuration_query_destinationTable_tableId	String	The table ID of the destination table that the cancelled job was configured to write results to.
Configuration_query_destinationTable_projectId	String	The project ID of the destination table that was specified in the cancelled job's configuration.
Configuration_query_destinationTable_datasetId	String	The dataset ID of the destination table that was specified in the cancelled job's configuration.
Status_State	String	The final state of the job, such as 'DONE' or 'CANCELLED'.
Status_errorResult_reason	String	A brief code indicating the reason the job failed or was cancelled, such as 'jobCancelled' or 'accessDenied'.
Status_errorResult_message	String	A detailed, human-readable message describing the error that occurred during job execution or cancellation.

Deletes an object from a bucket.

Stored Procedure-Specific Information

To execute this stored procedure, enter:

    EXEC DeleteObject RemotePath='gs://cdata_test_bucket/temp.csv'

Input

Name	Type	Description
RemotePath	String	Path from which the object will be deleted, such as 'gs://cdata_test_bucket/temp.csv'.

Result Set Columns

Name	Type	Description
Success	String	Indicator if the stored procedure was successful or not.

Deletes the specified table from Google BigQuery.

Stored Procedure-Specific Information

To execute this stored procedure, enter:

    EXEC DeleteTable TableId='my_table_id'

Input

Name	Type	Description
TableId	String	Specifies the ID of the table to delete. The Project ID and Dataset ID can be sourced from the connection properties or overridden using the format projectId:datasetId.TableId.

Result Set Columns

Name	Type	Description
Success	String	Returns 'true' if the table was successfully deleted. If the deletion fails, an exception is thrown instead of returning 'false'.

Retrieves the configuration information and execution state for an existing job.

Stored Procedure-Specific Information

To execute this stored procedure, enter:

    EXEC GetJob JobId='CData20190603T124459621_1', Region='asia-southeast1'

Input

Name	Type	Description
JobId	String	Specifies the unique identifier of the BigQuery job to retrieve. This is typically assigned when the job is created.
Region	String	Identifies the geographic location where the job is executing. This value is required for non-US and non-EU regions.

Result Set Columns

Name	Type	Description
JobId	String	Returns the unique identifier of the retrieved job. Matches the job ID specified in the input.
Region	String	Returns the region where the job is or was executing. Useful for region-specific configurations and troubleshooting.
Configuration_query_query	String	Returns the full SQL query string that was executed by the job.
Configuration_query_destinationTable_tableId	String	Returns the table ID where the query results were stored, if applicable.
Configuration_query_destinationTable_projectId	String	Returns the project ID that contains the destination table for the job results.
Configuration_query_destinationTable_datasetId	String	Returns the dataset ID that contains the destination table for the job results.
Status_State	String	Indicates the current lifecycle state of the job. Possible values include 'PENDING', 'RUNNING', and 'DONE'.
Status_errorResult_reason	String	Provides a concise error code representing the reason for job failure, if an error occurred.
Status_errorResult_message	String	Provides a detailed message describing the error encountered during job execution, if applicable.

Obtains the OAuth access token to be used for authentication with various Google services.

NOTE: If, after running this stored procedure, the OAuthRefreshToken was not returned as part of the result set, change the Prompt value to CONSENT and run the procedure again. This forces the app to reauthenticate and send new token information.

Input

Name	Type	Description
AuthMode	String	The type of authentication mode to use. The allowed values are APP, WEB. The default value is WEB.
Verifier	String	The verifier code returned by Google after permissions have been granted for the app to connect. WEB Authmode only.
Scope	String	The scope of access to Google APIs. By default, access to all APIs used by this data provider will be specified.
CallbackURL	String	Determines where the response is sent. The value of this parameter must exactly match one of the values registered in the APIs Console (including the http or https schemes, case, and trailing '/').
Prompt	String	This field indicates the prompt to present the user. It accepts one of the following values: NONE, CONSENT, SELECT ACCOUNT. The default is SELECT_ACCOUNT, so a given user will be prompted to select the account to connect to. If it is set to CONSENT, the user will see a consent page every time, even if they have previously given consent to the application for a given set of scopes. Lastly, if it is set to NONE, no authentication or consent screens will be displayed to the user. The default value is SELECT_ACCOUNT.
AccessType	String	Indicates if your application needs to access a Google API when the user is not present at the browser. This parameter defaults to offline. If your application needs to refresh access tokens when the user is not present at the browser, then use offline. This will result in your application obtaining a refresh token the first time your application exchanges an authorization code for a user. The allowed values are ONLINE, OFFLINE. The default value is OFFLINE.
State	String	Indicates any state which may be useful to your application upon receipt of the response. The Google Authorization Server roundtrips this parameter, so your application receives the same value it sent. Possible uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations.

Result Set Columns

Name	Type	Description
OAuthAccessToken	String	The authentication token returned from Google. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken	String	A token that may be used to obtain a new access token.
ExpiresIn	String	The remaining lifetime on the access token.