Adding a Connection to Google BigQuery

To add a connection to Google BigQuery:

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the Google BigQuery icon is not available, click the Add More icon to download and install the Google BigQuery connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

Connecting to Google BigQuery

By default, the CData Sync App 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 Sync App supports using user 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.

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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App. 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 Sync App 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 Sync App to enable the automatic refreshing of the access token.

GCP Instance Accounts

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

The following sections detail Sync App 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 Sync App automatically create destination tables when needed. If a query result is too large to fit the BigQuery query cache, the Sync App 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 Sync App. For example, if multiple users are using the Sync App and do not have permissions to write to datasets created by the other users. See TempTableDataset for details on how to do this.

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 Sync App 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 Sync App 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 Sync App will upload the inserted rows to Google-managed storage and then create a load job for them. This job will execute and the Sync App 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 Sync App 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 Sync App also supports performing bulk UPDATE and DELETE operations. This requires the Sync App 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 Sync App 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 Sync App 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

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

User Defined Views

The Sync App 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 Sync App 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 Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App 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.

Customizing the SSL Configuration

By default, the Sync App 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

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 Sync App 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.

The CData Sync App models the data as defined within Google BigQuery for the ProjectId and DatasetId configured.

Views

Views are client-side tables that cannot be modified. The Sync App uses these to report metadata about the Google BigQuery projects and datsets it is connected to.

In addition, the Sync App supports server-side views defined within Google BigQuery. These views may be used in SELECT statements the same way as tables. However, view schemas can easily become out of date and require the Sync App to refresh them. Please see RefreshViewSchemas for more details.

External Data Sources

Google BigQuery allows creating external datasets that store data in Amazon S3 regions (like aws-us-east-1) or Azure Storage regions (like azure-useast2). The Sync App supports these datasets with two major limitations:

1. Google BigQuery treats external tables as read-only. You cannot execute INSERT, UPDATE or DELETE queries on them.
2. Google BigQuery does not support the Storage API for external datasets. You must disable the UseStorageApi option in order to query them. This limits the read throughput of the Sync App, so if you are executing large queries it is recommended that you copy your data into Google BigQuery for the best performance.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. The Sync App 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 Sync App, there is also support for server-side stored procedures defined in Google BigQuery. The Sync App supports both CALL and EXEC using the procedure's parameter names. Note that Sync App 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.GetSockedValue productId = 24, discountRate = 0.75

Additional Metadata

Table Descriptions

Google BigQuery supports setting descriptions on tables but the Sync App does not report these by default. ShowTableDescriptions can be used to report table descriptions.

Primary Keys

Google BigQuery does not support primary keys natively, but the Sync App allows you to define them so they can be used in environments that require primary keys to modify data. Primary keys can be defined using the PrimaryKeyIdentifiers option.

Policy Tags

If policy tags from the Data Catalog service are defined on a table, they can be retrieved from the system tables using the PolicyTags column:

SELECT ColumnName, PolicyTags FROM sys_tablecolumns
WHERE CatalogName = 'psychic-valve-137816'
AND SchemaName = 'Northwind'
AND TableName = 'Customers

Tables

Table definitions are dynamically generated based on the table definitions within Google BigQuery for the Project and Dataset specified in the connection string options.

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.

Google BigQuery Connector for CData Sync 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, unique, opaque Id of the dataset.
Kind	String	The resource type.
FriendlyName	String	A descriptive name for the dataset
DatasetReference_ProjectId	String	A unique reference to the container project.
DatasetReference_DatasetId	String	A unique reference to the dataset, without the project name.

Lists the partitioning definitions for tables

Columns

Name	Type	Description
Id [KEY]	String	A unique identifier for the partition.
ProjectId	String	The project that the table belongs to.
DatasetId	String	The dataset that the table belongs to.
TableName	String	The name of the table.
ColumnName	String	The name of the column used for partitioning.
ColumnType	String	The type of the partitioning column.
Kind	String	The type of partitioning used by the table. One of DATE, RANGE or INGESTION.
RequireFilter	Boolean	Whether a filter on the partition column is required to query the table.

Lists the partitioning ranges for tables

Columns

Name	Type	Description
Id	String	A unique identifier for the partition.
RangeLow	String	The lowest value of the partition column. Either an integer when Kind is RANGE, or a date otherwise.
RangeHigh	String	The highest value of the partition column. Either an integer when Kind is RANGE, or a date otherwise.
RangeInterval	String	The range of values which are included in each partition. Only valid when Kind is RANGE
DateResolution	String	How much of the date is significant to a TIME or INGESTION partition column. One of DAY, HOUR, MONTH or YEAR.

Lists all the projects for the authorized user.

Columns

Name	Type	Description
Id [KEY]	String	The unique identifier of the Project
Kind	String	The resource type.
FriendlyName	String	A descriptive name for the project.
NumericId	String	The numeric Id of the project.
ProjectReference_ProjectId	String	A unique reference to the project.

Data Type Mappings

The Sync App maps types from the data source to the corresponding data type available in the schema. The table below documents these mappings.

Google BigQuery	CData Schema
STRING	string
BYTES	binary
INTEGER	long
FLOAT	double
NUMERIC	decimal
BIGNUMERIC	decimal
BOOLEAN	bool
DATE	date
TIME	time
DATETIME	datetime
TIMESTAMP	datetime
STRUCT	See below
ARRAY	See below
GEOGRAPHY	string
JSON	string
INTERVAL	string

Note that the NUMERIC type supports 38 digits of precision and the BIGDECIMAL type supports 76 digits of precision. Most platforms do not have a decimal type that supports the full precision of these values (.NET decimal supports 28 digits, and Java BigDecimal supports 38 by default). If this is the case, then you can cast these columns to a string when queried, or the connection can be configured to ignore them by setting IgnoreTypes=decimal.

STRUCT and ARRAY Types

Google BigQuery supports two kinds of types for storing compound values in a single row, STRUCT and ARRAY. In some places within Google BigQuery these are also known as RECORD and REPEATED types.

A STRUCT is a fixed-size group of values that are accessed by name and can have different types. The Sync App flattens structs so their individual fields can be accessed using dotted names. Note that these dotted names must be quoted.

-- trade_value STRUCT<currency STRING, value FLOAT>
SELECT CONCAT([trade_value.value], ' ', NULLIF([trade_value.currency], 'USD'))
FROM trades

An ARRAY is a group of values with the same type that can have any size. The Sync App treats the array as a single compound value and reports it as a JSON aggregate.

These types may be combined such that a STRUCT type contains an ARRAY field, or an ARRAY field is a list of STRUCT values. The outer type takes precedence in how the field is processed:

/* Table contains fields: 
  stocks STRUCT<symbol STRING, prices ARRAY<FLOAT>>
  offers: ARRAY<STRUCT<currency STRING, value FLOAT>> 
*/

SELECT [stocks.symbol], /* ARRAY field can be read from STRUCT, but is converted to JSON */
       [stocks.prices], 
       [offers]         /* STRUCT fields in an ARRAY cannot be accessed */
FROM market

INTERVAL Types

The Sync App represents INTERVAL types as strings. Whenever a query requires an INTERVAL type, it must specify the INTERVAL using the BigQuery SQL INTERVAL format:

YEAR-MONTH DAY HOUR:MINUTE:SECOND.FRACTION

. All queries that return INTERVAL values use this format unless they appear in an ARRAY aggregate, where the format depends upon how the Sync App reads the data.

For example, the value "5 years and 11 months, minus 10 days and 3 hours and 2.5 seconds" in the correct format is:

5-11 -10 -3:0:0.2.5

Type Parameters

The Sync App exposes parameters on the following types. In each case the type parameters are optional, Google BigQuery has default values for types that are not parameterized.

• STRING(length)
• BYTES(length)
• NUMERIC(precision) or NUMERIC(precision, scale)
• BIGNUMERIC(precision) or BIGNUMERIC(precision, scale)

These parameters are primarily for restricting the data written to the table. They are included in the table metadata as the column size for STRING and BYTES, and the numeric precision and scale for NUMERIC and BIGNUMERIC.

Type parameters have no effect on queries and are not reported within query metadata. For example, in the example below the output of CONCAT is a plain STRING even though its inputs are a STRING(100) and b STRING(100).

SELECT CONCAT(a, b) FROM table_with_length_params

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

Authentication

---

Property	Description
AuthScheme	The type of authentication to use when connecting to Google BigQuery.
ProjectId	The ProjectId used to resolve unqualified tables and execute jobs.
DatasetId	The DatasetId used to resolve unqualified tables.

BigQuery

---

Property	Description
AllowLargeResultSets	Whether or not to allow large datasets to be stored in temporary tables for large datasets.
UseQueryCache	Specifies whether to use Google BigQuery's built-in query cache.
PageSize	The number of results to return per page from Google BigQuery.
PollingInterval	This determines how long to wait in seconds, between checks to see if a job has completed.
AllowUpdatesWithoutKey	Whether or not to allow update without primary keys.
FilterColumns	Please set `AllowUpdatesWithoutKey` to true before you could use this property.
UseLegacySQL	Specifies whether to use BigQuery's legacy SQL dialect for this query. By default, Standard SQL will be used.

Storage API

---

Property	Description
UseStorageAPI	Specifies whether to use BigQuery's Storage API for bulk data reads.
UseArrowFormat	Specifies whether to use the Arrow format with BigQuery's Storage API.
StorageThreshold	The minimum number of rows a query must return to invoke the Storage API.
StoragePageSize	Specifies the page size to use for Storage API queries.

Uploading

---

Property	Description
InsertMode	Specifies what kind of method to use when inserting data. By default streaming INSERTs are used.
WaitForBatchResults	Whether to wait for the job to complete when using the bulk upload API. Only active when InsertMode is set to Upload.
GCSBucket	Specifies the name of a GCS bucket to upload bulk data for staging.
GCSBucketFolder	Specifies the name of the folder in GCSBucket to upload bulk data for staging.
TempTableDataset	The prefix of the dataset that will contain temporary tables when performing bulk UPDATE or DELETE operations.

OAuth

---

Property	Description
OAuthClientId	Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server.
OAuthClientSecret	Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the OAuth authorization server.
DelegatedServiceAccounts	A space-delimited list of service account emails for delegated requests.
RequestingServiceAccount	A service account email to make a delegated request.

JWT OAuth

---

Property	Description
OAuthJWTCert	The JWT Certificate store.
OAuthJWTCertType	The type of key store containing the JWT Certificate.
OAuthJWTCertPassword	The password for the OAuth JWT certificate used to access a certificate store that requires a password. If the certificate store does not require a password, leave this property blank.
OAuthJWTCertSubject	The subject of the OAuth JWT certificate used to locate a matching certificate in the store. Supports partial matches and the wildcard '*' to select the first certificate.
OAuthJWTIssuer	The issuer of the Java Web Token.
OAuthJWTSubject	The user subject for which the application is requesting delegated access.

SSL

---

Property	Description
SSLServerCert	Specifies the certificate to be accepted from the server when connecting using TLS/SSL.

Firewall

---

Property	Description
FirewallType	Specifies the protocol the provider uses to tunnel traffic through a proxy-based firewall.
FirewallServer	Identifies the IP address, DNS name, or host name of a proxy used to traverse a firewall and relay user queries to network resources.
FirewallPort	Specifies the TCP port to be used for a proxy-based firewall.
FirewallUser	Identifies the user ID of the account authenticating to a proxy-based firewall.
FirewallPassword	Specifies the password of the user account authenticating to a proxy-based firewall.

Proxy

---

Property	Description
ProxyAutoDetect	Specifies whether the provider checks your system proxy settings for existing proxy server configurations, rather than using a manually specified proxy server.
ProxyServer	The hostname or IP address of the proxy server that you want to route HTTP traffic through.
ProxyPort	The TCP port on your specified proxy server (set in the ProxyServer connection property) that has been reserved for routing HTTP traffic to and from the client.
ProxyAuthScheme	Specifies the authentication method the provider uses when authenticating to the proxy server specified in the ProxyServer connection property.
ProxyUser	The username of a user account registered with the proxy server specified in the ProxyServer connection property.
ProxyPassword	The password associated with the user specified in the ProxyUser connection property.
ProxySSLType	The SSL type to use when connecting to the proxy server specified in the ProxyServer connection property.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the proxy server set in the ProxyServer connection property.

Logging

---

Property	Description
LogModules	Specifies the core modules to include in the log file. Use a semicolon-separated list of module names. By default, all modules are logged.

Schema

---

Property	Description
Location	Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.
BrowsableSchemas	Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .
Tables	Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC .
Views	Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC .
RefreshViewSchemas	Allows the provider to determine up-to-date view schemas automatically.
ShowTableDescriptions	Controls whether table descriptions are returned via the platform metadata APIs and sys_tables / sys_views.
PrimaryKeyIdentifiers	Set this property to define primary keys.
AllowedTableTypes	Specifies what kinds of tables will be visible.
FlattenObjects	Determines whether the provider flattens STRUCT fields into top-level columns.

Miscellaneous

---

Property	Description
StorageTimeout	How long a Storage API connection may remain active before the provider reconnects.
EmptyArraysAsNull	Whether empty arrays are represented as 'null' or as '[]'.
HidePartitionColumns	Whether partition tables will show the columns _PARTITIONDATE and _PARTITIONTIME.
AllowAggregateParameters	Allows raw aggregates to be used in parameters when QueryPassthrough is enabled.
ApplicationName	An application name in the form application/version. For example, AcmeReporting/1.0.
AuditLimit	The maximum number of rows which will be stored within an audit table.
AuditMode	What provider actions should be recorded to audit tables.
AWSWorkloadIdentityConfig	Configuration properties to provide when using Workload Identity Federation via AWS.
BigQueryOptions	A comma separated list of Google BigQuery options.
GenerateSchemaFiles	Indicates the user preference as to when schemas should be generated and saved.
MaximumBillingTier	The MaximumBillingTier is a positive integer that serves as a multiplier of the basic price per TB. For example, if you set MaximumBillingTier to 2, the maximum cost for that query will be 2x basic price per TB.
MaximumBytesBilled	Limits how many bytes BigQuery will allow a job to consume before it is cancelled.
MaxRows	Specifies the maximum rows returned for queries without aggregation or GROUP BY.
Other	Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.
PseudoColumns	Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.
QueryPassthrough	This option passes the query to the Google BigQuery server as is.
SupportCaseSensitiveTables	By default, the provider treats table names as case-insensitive, so if multiple tables have the same name but different casing, only one will be reported in the metadata.
TableSamplePercent	This determines what percent of a table is sampled with the TABLESAMPLE operator.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews	Specifies a filepath to a JSON configuration file defining custom views. The provider automatically detects and uses the views specified in this file.
WorkloadPoolId	The ID of your Workload Identity Federation pool.
WorkloadProjectId	The ID of the Google Cloud project that hosts your Workload Identity Federation pool.
WorkloadProviderId	The ID of your Workload Identity Federation pool provider.

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

---

Property	Description
AuthScheme	The type of authentication to use when connecting to Google BigQuery.
ProjectId	The ProjectId used to resolve unqualified tables and execute jobs.
DatasetId	The DatasetId used to resolve unqualified tables.

The type of authentication to use when connecting to Google BigQuery.

Remarks

• OAuth: Set this to perform OAuth authentication using a standard user account.
• OAuthJWT: Set this to perform OAuth authentication using an OAuth service account.
• GCPInstanceAccount: Set this to get Access Token from Google Cloud Platform instance.
• AWSWorkloadIdentity: Set this to authenticate using Workload Identity Federation. The Sync App authenticates to AWS according to the AWSWorkloadIdentityConfig and provides Google Security Token Serivce with an authentication token. The Google STS validates this token and produces an OAuth token that can access Google services.

The ProjectId used to resolve unqualified tables and execute jobs.

Remarks

This property and BillingProjectId are used to determine billing for jobs and resolve unqualified table names.

Job Execution

The Sync App must create a job within Google BigQuery to execute certain kinds of queries. For example, complex SELECT statements, UPDATE and DELETE statements, and INSERT statements (when InsertMode is DML) are all executed using jobs. The project where a job executes determines how the job is billed.

The Sync App determines the billing project using these rules. Note that only the first two rules apply when QueryPassthrough is enabled. Either this property or BillingProjectId must be set to execute passthrough queries.

1. The BillingProjectId is used if that property is not empty.
2. Then this property is used.
3. If both properties are empty, the project is determined from the catalog of the first table in the query. The job created for the following query executes in the psychic-valve-137816 project.

SELECT FirstName, LastName FROM `psychic-valve-137816`.`Northwind`.`customers`

Table Resolution

In addition to setting the billing project, the Sync App also uses this property to determine the default data project. The data project is used to resolve tables included in queries when they are not fully qualified:

/* Unqualified, resolved against connection properties */
SELECT FirstName, LastName FROM `Northwind`.`customers`

/* Qualified, project specified as catalog */
SELECT FirstName, LastName FROM `psychic-valve-137816`.`Northwind`.`customers`

Any unqualified table references in the query are resolved using the following rules. Note that only methods 1 and 2 are supported when QueryPassthrough is enabled. This means that any tables outside the default data project must be explicitly qualified.

1. This property is used if it is not empty.
2. Then the BillingProjectId property is used.
3. If both properties are empty, the catalog from the first table in the query is used. In the following query the `Northwind`.`orders` table is treated as if it comes from the psychic-valve-137186 project.

SELECT ... FROM `psychic-valve-137816`.`Northwind`.`customers`
INNER JOIN `Northwind`.`orders`
ON ...

The DatasetId used to resolve unqualified tables.

Remarks

When a query refers to a table it can leave the dataset implicit, or qualify the dataset directly as the schema portion of the table:

/* Implicit, resolved against connection string */
SELECT FirstName, LastName FROM `customers`

/* Explicit, dataset specified as schema */
SELECT FirstName, LastName FROM `psychic-valve-137816`.`Northwind`.`customers`

Any unqualified table references in the query are resolved using the following rules. Note that only method 1 is supported when QueryPassthrough is enabled. This means that passthrough queries must set this property or qualify all tables.

1. If this property is set then the specified dataset is used.
2. Otherwise the schema from the first table in the query is used. In the following query the `orders` table is treated as if it comes from the Northwind dataset.

SELECT ... FROM `psychic-valve-137816`.`Northwind`.`customers`
INNER JOIN `orders`
ON ...

This section provides a complete list of the BigQuery properties you can configure in the connection string for this provider.

---

Property	Description
AllowLargeResultSets	Whether or not to allow large datasets to be stored in temporary tables for large datasets.
UseQueryCache	Specifies whether to use Google BigQuery's built-in query cache.
PageSize	The number of results to return per page from Google BigQuery.
PollingInterval	This determines how long to wait in seconds, between checks to see if a job has completed.
AllowUpdatesWithoutKey	Whether or not to allow update without primary keys.
FilterColumns	Please set `AllowUpdatesWithoutKey` to true before you could use this property.
UseLegacySQL	Specifies whether to use BigQuery's legacy SQL dialect for this query. By default, Standard SQL will be used.

Whether or not to allow large datasets to be stored in temporary tables for large datasets.

Remarks

Whether or not to allow large datasets to be stored in temporary tables for large datasets.

Specifies whether to use Google BigQuery's built-in query cache.

Remarks

Google BigQuery will cache the results of recent queries, and will use this cache for queries by default. Google BigQuery automatically updates the cache when a table is modified, so performance is generally better without any risk of queries returning stale data.

If this is set to false, the query is always run against the table directly.

The number of results to return per page from Google BigQuery.

Remarks

The pagesize can control the number of results returned per page from Google BigQuery. Setting a higher pagesize will cause more data to come back in a single HTTP request, but may take longer to execute. Setting a smaller pagesize will increase the number of HTTP requests to get all the data, but is generally recommended to ensure timeout exceptions do not occur.

Note that this option does not have an effect if UseStorageApi is enabled and the queries being executed can be executed on the Storage API. See StoragePageSize for more information.

This determines how long to wait in seconds, between checks to see if a job has completed.

Remarks

This only applies to queries which are stored to a table instead of streamed directly to the Sync App. This applies in only three cases:

• DestinationTable is set.
• AllowLargeResultSets is true and the query takes longer than Timeout seconds.
• UseStorageApi is enabled and the query is complex.

This property determines how long to wait between checking whether or not the query's results are ready. Very large resultsets or complex queries may take longer to process, and a low polling interval may result in may unnecessary requests being made to check the query status.

Whether or not to allow update without primary keys.

Remarks

Whether or not to allow update without primary keys.

Please set `AllowUpdatesWithoutKey` to true before you could use this property.

Remarks

Remember setting `AllowUpdatesWithoutKey` to true before you could use this property:

Set the property like this:

`filterColumns=col1[,col2[,col3]];`

Specifies whether to use BigQuery's legacy SQL dialect for this query. By default, Standard SQL will be used.

Remarks

If set to true, the query will use BigQuery's Legacy SQL dialect to rebuild the query. If set to false, the query will use BigQuery's standard SQL: https://cloud.google.com/bigquery/sql-reference/.
When UseLegacySQL is set to false, the values of AllowLargeResultSets is ignored. The query will be run as if AllowLargeResultSets is true.

This section provides a complete list of the Storage API properties you can configure in the connection string for this provider.

---

Property	Description
UseStorageAPI	Specifies whether to use BigQuery's Storage API for bulk data reads.
UseArrowFormat	Specifies whether to use the Arrow format with BigQuery's Storage API.
StorageThreshold	The minimum number of rows a query must return to invoke the Storage API.
StoragePageSize	Specifies the page size to use for Storage API queries.

Specifies whether to use BigQuery's Storage API for bulk data reads.

Remarks

By default the Sync App will use the Storage API instead of the default REST API. Depending upon the complexity of the query, the Sync App may execute the query in one of two ways:

• Simple queries that read all columns from only one table, and have no extra clauses except LIMIT, are executed directly within the Storage API.
• All other queries are executed as a query job which writes to a temporary table. Once the query is complete, the results are read from the temporary table using the Storage API.

The BigQuery Storage API can read data faster and more efficiently than the REST API (accessible by setting this option to false), but is priced differently and requires extra OAuth permissions when using your own OAuth app. It also uses the separate StoragePageSize property instead of PageSize.

The BigQuery REST API requires no extra permissions and uses standard pricing, but is slower than the Storage API.

Specifies whether to use the Arrow format with BigQuery's Storage API.

Remarks

This property only has an effect when UseStorageApi is enabled. When performing reads against the Storage API, the Sync App can request data in different formats. By default it uses Avro but enabling this option makes it use Arrow.

This option should be enabled when working with time series data or other datasets that have many date, time, datetime or timestamp fields. For these datasets using Arrow can have noticeable improvements over using Avro. Otherwise Avro and Arrow read times are very close and switching between them is unlikely to make a significant difference.

The minimum number of rows a query must return to invoke the Storage API.

Remarks

When the Sync App receives a query too complex to be run directly in the Storage API, it creates a query job and uses the Storage API to read from the query results table. If the query job returns fewer than the number of rows provided in this option, then the results are returned directly and the Storage API is not used.

This value should be set between 1 and 100000. Higher values will use the Storage API only for large resultsets, but will be delayed by reading more results from the query job. Lower values will result in smaller delays but will use the Storage API for more queries.

Note that this option only has an effect if UseStorageApi is enabled and the queries being executed cannot be executed directly on the Storage API. Queries which run directly on Storage never create query jobs.

Specifies the page size to use for Storage API queries.

Remarks

When UseStorageApi is enabled and the query being executed can be run on the Storage API, this option controls how many rows the Sync App is allowed to buffer on the client.

A higher value will generally make queries faster at the expense of consuming more memory, while lower values will conserve memory but make queries slower.

This section provides a complete list of the Uploading properties you can configure in the connection string for this provider.

---

Property	Description
InsertMode	Specifies what kind of method to use when inserting data. By default streaming INSERTs are used.
WaitForBatchResults	Whether to wait for the job to complete when using the bulk upload API. Only active when InsertMode is set to Upload.
GCSBucket	Specifies the name of a GCS bucket to upload bulk data for staging.
GCSBucketFolder	Specifies the name of the folder in GCSBucket to upload bulk data for staging.
TempTableDataset	The prefix of the dataset that will contain temporary tables when performing bulk UPDATE or DELETE operations.

Specifies what kind of method to use when inserting data. By default streaming INSERTs are used.

Remarks

This section provides only a summary of the mechanisms that each of these modes use. Please see Advanced Integrations for more details on how to use each of these modes.

• Streaming uses the Google BigQuery streaming API (also called insertAll).
• DML uses the Google BigQuery query API to generate INSERT SQL statements which insert individual rows.
• Upload uses the Google BigQuery upload API to create a load job which copies the rows from temporary server-side storage.
• GCSStaging is similar to the Upload mode except that it uses your Google Cloud Storage account instead of public storage.

When UseLegacySQL is true only Streaming and Upload modes are allowed. The Legacy SQL dialect does not support DML statements.

Whether to wait for the job to complete when using the bulk upload API. Only active when InsertMode is set to Upload.

Remarks

This property determines whether the Sync App will wait for batch jobs to report their status. By default property is true and INSERT queries will complete only once Google BigQuery has finished executed them. When this property is false the INSERT query will complete as soon as a job is submitted for it.

The default mode is recommended for reliability:

1. INSERTs will never fail silently. If the Sync App does not wait for the job to finish, it will never receive an error if the job failed to execute.
2. If the INSERT batch size is small enough, the Sync App may submit jobs quickly enough that it hits Google BigQuery's load job limits. This does not happen when waiting for batch results because the Sync App will not allow more than one job to execute at the same time on the same connection.

You can disable this option to achieve lower delays when inserting, but you must also make sure to obey the Google BigQuery rate limits and check the status of each job to track their status and determine whether they have succeeded or failed.

Specifies the name of a GCS bucket to upload bulk data for staging.

Remarks

Only applies when InsertMode is set to GCSStaging, and if that option is set to use staging then this option is required.

Specifies the name of the folder in GCSBucket to upload bulk data for staging.

Remarks

Only applies when InsertMode is set to GCSStaging. If not set the Sync App defaults to writing to the root of the bucket.

The prefix of the dataset that will contain temporary tables when performing bulk UPDATE or DELETE operations.

Remarks

Internally bulk UPDATE and DELETE use Google BigQuery MERGE queries, which require creating a table to hold all the update operations. This option is used along with the target table's region to determine the name of the dataset where these temporary tables are created. Each region must have its own temporary dataset so that the temporary table and the MERGE table can be stored in the same project/dataset. This avoids unnecessary data transfer charges.

For example, the Sync App would create a dataset called "_CDataTempTableDataset_US" for tables in the US region and a dataset called "_CDataTempTableDataset_asia_southeast_1" for tables in the Singapore region.

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

---

Property	Description
OAuthClientId	Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server.
OAuthClientSecret	Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the OAuth authorization server.
DelegatedServiceAccounts	A space-delimited list of service account emails for delegated requests.
RequestingServiceAccount	A service account email to make a delegated request.

Specifies the client Id that was assigned the custom OAuth application was created. (Also known as the consumer key.) This ID registers the custom application with the OAuth authorization server.

Remarks

OAuthClientId is one of a handful of connection parameters that need to be set before users can authenticate via OAuth. For details, see Establishing a Connection.

Specifies the client secret that was assigned when the custom OAuth application was created. (Also known as the consumer secret ). This secret registers the custom application with the OAuth authorization server.

Remarks

OAuthClientSecret is one of a handful of connection parameters that need to be set before users can authenticate via OAuth. For details, see Establishing a Connection.

A space-delimited list of service account emails for delegated requests.

Remarks

The service account emails must be specified in a space-delimited list.

Each service account must be granted the roles/iam.serviceAccountTokenCreator role on its next service account in the chain.

The last service account in the chain must be granted the roles/iam.serviceAccountTokenCreator role on the requesting service account. The requesting service account is the one specified in the RequestingServiceAccount property.

Note that for delegated requests, the requesting service account must have the permission iam.serviceAccounts.getAccessToken, which can also be granted through the serviceAccountTokenCreator role.

A service account email to make a delegated request.

Remarks

The service account email of the account for which the credentials are requested in a delegated request. With the list of delegated service accounts in DelegatedServiceAccounts, this property is used to make a delegated request.

You must have the IAM permission iam.serviceAccounts.getAccessToken on this service account.

This section provides a complete list of the JWT OAuth properties you can configure in the connection string for this provider.

---

Property	Description
OAuthJWTCert	The JWT Certificate store.
OAuthJWTCertType	The type of key store containing the JWT Certificate.
OAuthJWTCertPassword	The password for the OAuth JWT certificate used to access a certificate store that requires a password. If the certificate store does not require a password, leave this property blank.
OAuthJWTCertSubject	The subject of the OAuth JWT certificate used to locate a matching certificate in the store. Supports partial matches and the wildcard '*' to select the first certificate.
OAuthJWTIssuer	The issuer of the Java Web Token.
OAuthJWTSubject	The user subject for which the application is requesting delegated access.

The JWT Certificate store.

Remarks

The name of the certificate store for the client certificate.

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

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

Designations of certificate stores are platform-dependent.

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

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

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

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

The type of key store containing the JWT Certificate.

Remarks

This property can take one of the following values:

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

The password for the OAuth JWT certificate used to access a certificate store that requires a password. If the certificate store does not require a password, leave this property blank.

Remarks

This property specifies the password needed to open the certificate store, but only if the store type requires one. To determine if a password is necessary, refer to the documentation or configuration for your specific certificate store.

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

The subject of the OAuth JWT certificate used to locate a matching certificate in the store. Supports partial matches and the wildcard '*' to select the first certificate.

Remarks

The value of this property is used to locate a matching certificate in the store. The search process works as follows:

• If an exact match for the subject is found, the corresponding certificate is selected.
• If no exact match is found, the store is searched for certificates whose subjects contain the property value.
• If no match is found, no certificate is selected.

You can set the value to '*' to automatically select the first certificate in the store. The certificate subject is a comma-separated list of distinguished name fields and values. For example: CN=www.server.com, OU=test, C=US, [email protected]. Common fields include:

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

If a field value contains a comma, enclose it in quotes. For example: "O=ACME, Inc.".

The issuer of the Java Web Token.

Remarks

The issuer of the Java Web Token. Enter the value of the service account email address.

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

The user subject for which the application is requesting delegated access.

Remarks

The user subject for which the application is requesting delegated access. Enter the email address of the user for which the application is requesting delegated access.

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

---

Property	Description
SSLServerCert	Specifies the certificate to be accepted from the server when connecting using TLS/SSL.

Specifies the certificate to be accepted from the server when connecting using TLS/SSL.

Remarks

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

This property can take the following forms:

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

If not specified, any certificate trusted by the machine is accepted.

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

This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.

---

Property	Description
FirewallType	Specifies the protocol the provider uses to tunnel traffic through a proxy-based firewall.
FirewallServer	Identifies the IP address, DNS name, or host name of a proxy used to traverse a firewall and relay user queries to network resources.
FirewallPort	Specifies the TCP port to be used for a proxy-based firewall.
FirewallUser	Identifies the user ID of the account authenticating to a proxy-based firewall.
FirewallPassword	Specifies the password of the user account authenticating to a proxy-based firewall.

Specifies the protocol the provider uses to tunnel traffic through a proxy-based firewall.

Remarks

A proxy-based firewall (or proxy firewall) is a network security device that acts as an intermediary between user requests and the resources they access. The proxy accepts the request of an authenticated user, tunnels through the firewall, and transmits the request to the appropriate server.

Because the proxy evaluates and transfers data backets on behalf of the requesting users, the users never connect directly with the servers, only with the proxy.

Note: By default, the Sync App connects to the system proxy. To disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.

The following table provides port number information for each of the supported protocols.

Protocol	Default Port	Description
TUNNEL	80	The port where the Sync App opens a connection to Google BigQuery. Traffic flows back and forth via the proxy at this location.
SOCKS4	1080	The port where the Sync App opens a connection to Google BigQuery. SOCKS 4 then passes theFirewallUser value to the proxy, which determines whether the connection request should be granted.
SOCKS5	1080	The port where the Sync App sends data to Google BigQuery. If the SOCKS 5 proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

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

Identifies the IP address, DNS name, or host name of a proxy used to traverse a firewall and relay user queries to network resources.

Remarks

A proxy-based firewall (or proxy firewall) is a network security device that acts as an intermediary between user requests and the resources they access. The proxy accepts the request of an authenticated user, tunnels through the firewall, and transmits the request to the appropriate server.

Because the proxy evaluates and transfers data backets on behalf of the requesting users, the users never connect directly with the servers, only with the proxy.

Specifies the TCP port to be used for a proxy-based firewall.

Remarks

A proxy-based firewall (or proxy firewall) is a network security device that acts as an intermediary between user requests and the resources they access. The proxy accepts the request of an authenticated user, tunnels through the firewall, and transmits the request to the appropriate server.

Because the proxy evaluates and transfers data backets on behalf of the requesting users, the users never connect directly with the servers, only with the proxy.

Identifies the user ID of the account authenticating to a proxy-based firewall.

Remarks

A proxy-based firewall (or proxy firewall) is a network security device that acts as an intermediary between user requests and the resources they access. The proxy accepts the request of an authenticated user, tunnels through the firewall, and transmits the request to the appropriate server.

Because the proxy evaluates and transfers data backets on behalf of the requesting users, the users never connect directly with the servers, only with the proxy.

Specifies the password of the user account authenticating to a proxy-based firewall.

Remarks

A proxy-based firewall (or proxy firewall) is a network security device that acts as an intermediary between user requests and the resources they access. The proxy accepts the request of an authenticated user, tunnels through the firewall, and transmits the request to the appropriate server.

Because the proxy evaluates and transfers data backets on behalf of the requesting users, the users never connect directly with the servers, only with the proxy.

This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.

---

Property	Description
ProxyAutoDetect	Specifies whether the provider checks your system proxy settings for existing proxy server configurations, rather than using a manually specified proxy server.
ProxyServer	The hostname or IP address of the proxy server that you want to route HTTP traffic through.
ProxyPort	The TCP port on your specified proxy server (set in the ProxyServer connection property) that has been reserved for routing HTTP traffic to and from the client.
ProxyAuthScheme	Specifies the authentication method the provider uses when authenticating to the proxy server specified in the ProxyServer connection property.
ProxyUser	The username of a user account registered with the proxy server specified in the ProxyServer connection property.
ProxyPassword	The password associated with the user specified in the ProxyUser connection property.
ProxySSLType	The SSL type to use when connecting to the proxy server specified in the ProxyServer connection property.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the proxy server set in the ProxyServer connection property.

Specifies whether the provider checks your system proxy settings for existing proxy server configurations, rather than using a manually specified proxy server.

Remarks

When this connection property is set to True, the Sync App checks your system proxy settings for existing proxy server configurations (no need to manually supply proxy server details).

This connection property takes precedence over other proxy settings. Set to False if you want to manually configure the Sync App to connect to a specific proxy server.

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

The hostname or IP address of the proxy server that you want to route HTTP traffic through.

Remarks

The Sync App only routes HTTP traffic through the proxy server specified in this connection property when ProxyAutoDetect is set to False. If ProxyAutoDetect is set to True, which is the default, the Sync App instead routes HTTP traffic through the proxy server specified in your system proxy settings.

The TCP port on your specified proxy server (set in the ProxyServer connection property) that has been reserved for routing HTTP traffic to and from the client.

Remarks

The Sync App only routes HTTP traffic through the proxy server port specified in this connection property when ProxyAutoDetect is set to False. If ProxyAutoDetect is set to True, which is the default, the Sync App instead routes HTTP traffic through the proxy server port specified in your system proxy settings.

For other proxy types, see FirewallType.

Specifies the authentication method the provider uses when authenticating to the proxy server specified in the ProxyServer connection property.

Remarks

The authentication type can be one of the following:

• BASIC: The Sync App performs HTTP BASIC authentication.
• DIGEST: The Sync App performs HTTP DIGEST authentication.
• NTLM: The Sync App retrieves an NTLM token.
• NEGOTIATE: The Sync App retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
• NONE: Set this when the ProxyServer does not require authentication.

For all values other than "NONE", you must also set the ProxyUser and ProxyPassword connection properties.

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

The username of a user account registered with the proxy server specified in the ProxyServer connection property.

Remarks

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

After selecting one of the available authentication types in ProxyAuthScheme, set this property as follows:

ProxyAuthScheme Value	Value to set for ProxyUser
BASIC	The user name of a user registered with the proxy server.
DIGEST	The user name of a user registered with the proxy server.
NEGOTIATE	The username of a Windows user who is a valid user in the domain or trusted domain that the proxy server is part of, in the format user@domain or domain\user.
NTLM	The username of a Windows user who is a valid user in the domain or trusted domain that the proxy server is part of, in the format user@domain or domain\user.
NONE	Do not set the ProxyPassword connection property.

The Sync App only uses this username if ProxyAutoDetect is set to False. If ProxyAutoDetect is set to True, which is the default, the Sync App instead uses the username specified in your system proxy settings.

The password associated with the user specified in the ProxyUser connection property.

Remarks

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

After selecting one of the available authentication types in ProxyAuthScheme, set this property as follows:

ProxyAuthScheme Value	Value to set for ProxyPassword
BASIC	The password associated with the proxy server user specified in ProxyUser.
DIGEST	The password associated with the proxy server user specified in ProxyUser.
NEGOTIATE	The password associated with the Windows user account specified in ProxyUser.
NTLM	The password associated with the Windows user account specified in ProxyUser.
NONE	Do not set the ProxyPassword connection property.

For SOCKS 5 authentication or tunneling, see FirewallType.

The Sync App only uses this password if ProxyAutoDetect is set to False. If ProxyAutoDetect is set to True, which is the default, the Sync App instead uses the password specified in your system proxy settings.

The SSL type to use when connecting to the proxy server specified in the ProxyServer connection property.

Remarks

This property determines when to use SSL for the connection to the HTTP proxy specified by ProxyServer. You can set this connection property to the following values :

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

A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the proxy server set in the ProxyServer connection property.

Remarks

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

Note that the Sync App uses the system proxy settings by default, without further configuration needed. If you want to explicitly configure proxy exceptions for this connection, set ProxyAutoDetect to False.

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

---

Property	Description
LogModules	Specifies the core modules to include in the log file. Use a semicolon-separated list of module names. By default, all modules are logged.

Specifies the core modules to include in the log file. Use a semicolon-separated list of module names. By default, all modules are logged.

Remarks

This property lets you customize the log file content by specifying the logging modules to include. Logging modules categorize logged information into distinct areas, such as query execution, metadata, or SSL communication. Each module is represented by a four-character code, with some requiring a trailing space for three-letter names.

For example, EXEC logs query execution, and INFO logs general provider messages. To include multiple modules, separate their names with semicolons as follows: INFO;EXEC;SSL.

The Verbosity connection property takes precedence over the module-based filtering specified by this property. Only log entries that meet the verbosity level and belong to the specified modules are logged. Leave this property blank to include all available modules in the log file.

For a complete list of available modules and detailed guidance on configuring logging, refer to the Advanced Logging section in Logging.

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

---

Property	Description
Location	Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.
BrowsableSchemas	Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .
Tables	Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC .
Views	Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC .
RefreshViewSchemas	Allows the provider to determine up-to-date view schemas automatically.
ShowTableDescriptions	Controls whether table descriptions are returned via the platform metadata APIs and sys_tables / sys_views.
PrimaryKeyIdentifiers	Set this property to define primary keys.
AllowedTableTypes	Specifies what kinds of tables will be visible.
FlattenObjects	Determines whether the provider flattens STRUCT fields into top-level columns.

Specifies the location of a directory containing schema files that define tables, views, and stored procedures. Depending on your service's requirements, this may be expressed as either an absolute path or a relative path.

Remarks

The Location property is only needed if you want to either customize definitions (for example, change a column name, ignore a column, etc.) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is %APPDATA%\\CData\\GoogleBigQuery Data Provider\\Schema, where %APPDATA% is set to the user's configuration directory:

Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Linux	~/.config

Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .

Remarks

Listing all available database schemas can take extra time, thus degrading performance. Providing a list of schemas in the connection string saves time and improves performance.

Optional setting that restricts the tables reported to a subset of all available tables. For example, Tables=TableA,TableB,TableC .

Remarks

Listing all available tables from some databases can take extra time, thus degrading performance. Providing a list of tables in the connection string saves time and improves performance.

If there are lots of tables available and you already know which ones you want to work with, you can use this property to restrict your viewing to only those tables. To do this, specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note: If you are connecting to a data source with multiple schemas or catalogs, you must specify each table you want to view by its fully qualified name. This avoids ambiguity between tables that may exist in multiple catalogs or schemas.

Optional setting that restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC .

Remarks

Listing all available views from some databases can take extra time, thus degrading performance. Providing a list of views in the connection string saves time and improves performance.

If there are lots of views available and you already know which ones you want to work with, you can use this property to restrict your viewing to only those views. To do this, specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note: If you are connecting to a data source with multiple schemas or catalogs, you must specify each view you want to examine by its fully qualified name. This avoids ambiguity between views that may exist in multiple catalogs or schemas.

Allows the provider to determine up-to-date view schemas automatically.

Remarks

When using BigQuery views, BigQuery stores a copy of the view schema with the view itself. However, these stored view schemas are not updated when the tables used by the view change. This means that the stored view schema can easily become out of date and cause queries using the view to fail.

By default, the Sync App will not use the stored view schema and will instead query the view to determine the available columns. This guarantees that the schema will be up to date although it requires the Sync App to start a query job.

You can disable this option to force the Sync App to use the stored view schemas. This prevents the Sync App from running any queries when getting a view schema, but also means that queries using the view will fail if the schema is out of date.

Controls whether table descriptions are returned via the platform metadata APIs and sys_tables / sys_views.

Remarks

By default table descriptions are not shown, since the Google BigQuery API requires an extra request beyond what is usually required for reading tables.

Enabling this option will show table descriptions, but will cost an extra API request for every table when a table list is fetched. This can slow down metadata operations on large datasets.

Set this property to define primary keys.

Remarks

Google BigQuery does not natively support primary keys, but for certain DML operations or database tools you may need to define them. By default this option is disabled and no tables will have primary keys except for the ones defined in schema files (if you set Location).

Primary keys are defined using a list of rules which match tables and provide a list of key columns. For example, PrimaryKeyIdentifiers="*=key;transactions=tx_date,tx_serial;user_comments=" has three rules separated by semicolons:

1. The first rule *=key means that every table without a more specific rule will have one primary key column called key. Tables that do not have a key column will not have any primary keys.
2. The second rule transactions=tx_date,tx_serial means that the transactions table will have the two primary key columns tx_date and tx_serial. If any of those columns are missing from the table then they will be ignored.
3. The third rule user_comments= means that the user_comments table will have no primary keys. The only use that empty key lists have is in overriding the default rule. If there is no default rule present then the only tables with primary keys would be the ones explicitly listed.

Note that the table names can include just the table, the table and dataset or the table, dataset and project. Both column and table names may be quoted using SQL quotes:

/* Rules with just table names use the connection ProjectId (or DataProjectId) and DatasetId. 
   All these rules refer to the same table with a connection where ProjectId=someProject;DatasetId=someDataset */
someTable=a,b,c
someDataset.someTable=a,b,c
someProject.someDataset.someTable=a,b,c

/* Any table or column name may be quoted */
`someProject`."someDataset".[someTable]=`a`,[b],"c"

Specifies what kinds of tables will be visible.

Remarks

This option is a comma-separated list of the table type values that the Sync App displays. Any table-like or view-like entity that doesn't have a matching type will not be reported when listing tables.

• TABLE Standard tables
• EXTERNAL Read-only table stored on another service (like GCS or Drive)
• SNAPSHOT A read-only table that preserves the data of another table at a specific point in time
• VIEW Standard views
• MATERIALIZED_VIEW A view that is recalculated and cached each time its base table changes

For example, to restrict the Sync App to listing only simple tables and views, this option would be set to TABLE,VIEW

Determines whether the provider flattens STRUCT fields into top-level columns.

Remarks

By default the Sync App reports each field in a STRUCT column as its own column while the STRUCT column itself is hidden. This process is recursively applied to nested STRUCT values. For example, if the following table is defined in Google BigQuery then the Sync App reports 3 columns: location.coords.lat, location.coords.lon and location.country:

CREATE TABLE t(location STRUCT<coords STRUCT<lat FLOAT64, lon FLOAT64>, country STRING>);

If this property is disabled, then the top-level STRUCT is not expanded and is left as its own column. The value of this column is reported as a JSON aggregate. In the above example, the Sync App reports only the location column when flattening is disabled.

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

---

Property	Description
StorageTimeout	How long a Storage API connection may remain active before the provider reconnects.
EmptyArraysAsNull	Whether empty arrays are represented as 'null' or as '[]'.
HidePartitionColumns	Whether partition tables will show the columns _PARTITIONDATE and _PARTITIONTIME.
AllowAggregateParameters	Allows raw aggregates to be used in parameters when QueryPassthrough is enabled.
ApplicationName	An application name in the form application/version. For example, AcmeReporting/1.0.
AuditLimit	The maximum number of rows which will be stored within an audit table.
AuditMode	What provider actions should be recorded to audit tables.
AWSWorkloadIdentityConfig	Configuration properties to provide when using Workload Identity Federation via AWS.
BigQueryOptions	A comma separated list of Google BigQuery options.
GenerateSchemaFiles	Indicates the user preference as to when schemas should be generated and saved.
MaximumBillingTier	The MaximumBillingTier is a positive integer that serves as a multiplier of the basic price per TB. For example, if you set MaximumBillingTier to 2, the maximum cost for that query will be 2x basic price per TB.
MaximumBytesBilled	Limits how many bytes BigQuery will allow a job to consume before it is cancelled.
MaxRows	Specifies the maximum rows returned for queries without aggregation or GROUP BY.
Other	Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.
PseudoColumns	Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.
QueryPassthrough	This option passes the query to the Google BigQuery server as is.
SupportCaseSensitiveTables	By default, the provider treats table names as case-insensitive, so if multiple tables have the same name but different casing, only one will be reported in the metadata.
TableSamplePercent	This determines what percent of a table is sampled with the TABLESAMPLE operator.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews	Specifies a filepath to a JSON configuration file defining custom views. The provider automatically detects and uses the views specified in this file.
WorkloadPoolId	The ID of your Workload Identity Federation pool.
WorkloadProjectId	The ID of the Google Cloud project that hosts your Workload Identity Federation pool.
WorkloadProviderId	The ID of your Workload Identity Federation pool provider.

How long a Storage API connection may remain active before the provider reconnects.

Remarks

Google BigQuery and many proxies/firewalls restrict the amount of time that idle connections stay alive before they are forcibly closed. This can be a problem when using the Storage API because the Sync App may stream data faster than it can be consumed. While the consumer is catching up, the Sync App does not use its connection and it may be closed by the next time the Sync App uses it.

To avoid this the Sync App will automatically close and reopen the connection if it has been active for too long. This property controls how many seconds the connection has to be active for the Sync App to reset it. To disable these resets this property can also be set to 0 or a negative value.

Whether empty arrays are represented as 'null' or as '[]'.

Remarks

This property is enabled by default, so empty arrays are represented as 'null' for consistency with representing empty aggregates. To mimic the native driver and represente empty arrays as '[]', this property can be disabled.

Whether partition tables will show the columns _PARTITIONDATE and _PARTITIONTIME.

Remarks

This property is disabled by default, so partition tables will show the pseudocolumns _PARTITIONDATE and _PARTITIONTIME. To hide these columns, as is done in the native driver and the BigQuery console, this property can be enabled.

Allows raw aggregates to be used in parameters when QueryPassthrough is enabled.

Remarks

This option affects how string parameters are handled when using direct queries through QueryPassthrough. For example, consider this query:

INSERT INTO proj.data.tbl(x) VALUES (@x)

By default, this option is disabled and string parameters are quoted and escaped into SQL strings. That means that any value can be safely used as a string parameter, but it also means that parameters cannot be used as raw aggregate values:

/*
 * If @x is set to: test value ' contains quote
 *
 * Result is a valid query
*/
INSERT INTO proj.data.tbl(x) VALUES ('test value \' contains quote')

/*
 * If @x is set to: ['valid', ('aggregate', 'value')]
 *
 * Result contains string instead of aggregate:
*/
INSERT INTO proj.data.tbl(x) VALUES ('[\'valid\', (\'aggregate\', \'value\')]')

When this option is enabled, string parameters are inserted directly into the query. This means that raw aggregates can be used as parameters, but it also means that all simple strings must be escaped:

/*
 * If @x is set to: test value ' contains quote
 *
 * Result is an invalid query
*/
INSERT INTO proj.data.tbl(x) VALUES (test value ' contains quote)

/*
 * If @x is set to: ['valid', ('aggregate', 'value')]
 *
 * Result is an aggregate
*/
INSERT INTO proj.data.tbl(x) VALUES (['valid', ('aggregate', 'value')])

An application name in the form application/version. For example, AcmeReporting/1.0.

Remarks

The Sync App identifies itself to BigQuery using a Google partner User-Agent header. The first part of the User-Agent is fixed and identifies the client as a specific build of the CData Sync App. The last portion reports the specific application using the Sync App.

The maximum number of rows which will be stored within an audit table.

Remarks

When auditing is enabled with the AuditMode option, this property is used to determine how many rows will be allowed in the audit table at once.

By default this property is 1000, meaning that only the 1000 most recent audit events will be available within the audit table.

This property can also be set to -1, which places no limits on the size of the audit table. In this mode, the audit table should be periodically cleared to prevent the Sync App from using excessive memory.

DELETE FROM AuditJobs#TEMP

What provider actions should be recorded to audit tables.

Remarks

The Sync App can record certain internal actions taken when it runs queries. For each of those actions listed in this option, the Sync App will create a temproary audit table which logs when the action took place, what query caused the action and any other relevant information.

By default this option is set to 'none' and the Sync App does not record any audit information. This option can also be set to a comma-separated list of the following actions:

Mode Name	Audit Table	Description	Columns
start-jobs	AuditJobs#TEMP	Records all jobs started by the Sync App	Timestamp,Query,ProjectId,Location,JobId

Refer to AuditLimit for more information on how to limit the size of these tables.

Configuration properties to provide when using Workload Identity Federation via AWS.

Remarks

The properties are formatted as a semicolon-separated list of Key=Value properties, where the value is optionally quoted. For example, this setting authenticates in AWS using a user's root keys:

AWSWorkloadIdentityConfig="AuhtScheme=AwsRootKeys;AccessKey='AKIAABCDEF123456';SecretKey=...;Region=us-east-1"

A comma separated list of Google BigQuery options.

Remarks

A list of Google BigQuery options:

Option	Description
gbqoImplicitJoinAsUnion	This option will prevent the driver from converting an IMPLICIT JOIN into a CROSS JOIN as expected by SQL92. Instead, it will leave it as an IMPLICIT JOIN, which Google BigQuery will execute as a UNION ALL.

Indicates the user preference as to when schemas should be generated and saved.

Remarks

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

Available settings are the following:

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

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

Generate Schemas with SQL

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

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

Generate Schemas on Connection

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

The MaximumBillingTier is a positive integer that serves as a multiplier of the basic price per TB. For example, if you set MaximumBillingTier to 2, the maximum cost for that query will be 2x basic price per TB.

Remarks

Limits the billing tier for this job. Queries that have resource usage beyond this tier will fail (without incurring a charge). If unspecified, this will be set to your project default. If your query is too compute intensive for BigQuery to complete at the standard per TB pricing tier, BigQuery returns a billingTierLimitExceeded error and an estimate of how much the query would cost. To run the query at a higher pricing tier, pass a new value for maximumBillingTier as part of the query request. The maximumBillingTier is a positive integer that serves as a multiplier of the basic price per TB. For example, if you set maximumBillingTier to 2, the maximum cost for that query will be 2x basic price per TB.

Limits how many bytes BigQuery will allow a job to consume before it is cancelled.

Remarks

When this value is provided, all jobs will use this value as their default billing cap. If a job uses more than this many bytes, BigQuery will cancel it and it will not be billed. By default there is no cap and all jobs will be billed for however many bytes they consume.

This only has an effect when using DestinationTable or when using the InsertJob stored procedure. BigQuery does not allow standard query jobs to have byte limits.

Specifies the maximum rows returned for queries without aggregation or GROUP BY.

Remarks

This property sets an upper limit on the number of rows the Sync App returns for queries that do not include aggregation or GROUP BY clauses. This limit ensures that queries do not return excessively large result sets by default.

When a query includes a LIMIT clause, the value specified in the query takes precedence over the MaxRows setting. If MaxRows is set to "-1", no row limit is enforced unless a LIMIT clause is explicitly included in the query.

This property is useful for optimizing performance and preventing excessive resource consumption when executing queries that could otherwise return very large datasets.

Specifies additional hidden properties for specific use cases. These are not required for typical provider functionality. Use a semicolon-separated list to define multiple properties.

Remarks

This property allows advanced users to configure hidden properties for specialized scenarios. These settings are not required for normal use cases but can address unique requirements or provide additional functionality. Multiple properties can be defined in a semicolon-separated list.

Note: It is strongly recommended to set these properties only when advised by the support team to address specific scenarios or issues.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting

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

Specifies the pseudocolumns to expose as table columns. Use the format 'TableName=ColumnName;TableName=ColumnName'. The default is an empty string, which disables this property.

Remarks

This property allows you to define which pseudocolumns the Sync App exposes as table columns.

To specify individual pseudocolumns, use the following format: "Table1=Column1;Table1=Column2;Table2=Column3"

To include all pseudocolumns for all tables use: "*=*"

This option passes the query to the Google BigQuery server as is.

Remarks

When this is set, queries are passed through directly to Google BigQuery.

By default, the provider treats table names as case-insensitive, so if multiple tables have the same name but different casing, only one will be reported in the metadata.

Remarks

When this property is set to true, tables with the same name but different casing will be renamed so they are all reported in the metadata.

This determines what percent of a table is sampled with the TABLESAMPLE operator.

Remarks

This option can be set to make the Sync App use the TABLESAMPLE for each table referenced by a query. The value determines what percent is provided to the PERCENT clause. That clause will only be generated if this property's value is above zero.

-- Input SQL
SELECT * FROM `tbl`

-- Generated Google BigQuery SQL when TableSamplePercent=10
SELECT * FROM `tbl` TABLESAMPLE SYSTEM (10 PERCENT)

This option is subject to a few limitations:

• It is applied during query converison and has no effect when QueryPassthrough is set.
• More rows may be returned than expected due to how the server implements TABLESAMPLE. Please see the Google BigQuery documentation for more information.
• TABLESAMPLE is not supported on views. If a view is queried in sampling mode, the Sync App will omit the TABLESAMPLE clause for the view.

The value in seconds until the timeout error is thrown, canceling the operation.

Remarks

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

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

Specifies a filepath to a JSON configuration file defining custom views. The provider automatically detects and uses the views specified in this file.

Remarks

This property allows you to define and manage custom views through a JSON-formatted configuration file called UserDefinedViews.json. These views are automatically recognized by the Sync App and enable you to execute custom SQL queries as if they were standard database views. The JSON file defines each view as a root element with a child element called "query", which contains the 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)"
	}
}

You can define multiple views in a single file and specify the filepath using this property. For example: UserDefinedViews=C:\Path\To\UserDefinedViews.json. When you use this property, only the specified views are seen by the Sync App.

Refer to User Defined Views for more information.

The ID of your Workload Identity Federation pool.

Remarks

The ID of your Workload Identity Federation pool.

The ID of the Google Cloud project that hosts your Workload Identity Federation pool.

Remarks

The ID of the Google Cloud project that hosts your Workload Identity Federation pool.

The ID of your Workload Identity Federation pool provider.

Remarks

The ID of your Workload Identity Federation pool provider.

protobuf v. 3.5.1

Copyright 2008 Google Inc. All rights reserved. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

* Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. * Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. * Neither the name of Google Inc. nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Code generated by the Protocol Buffer compiler is owned by the owner of the input file used when generating it. This code is not standalone and requires a support library to be linked with it. This support library is itself covered by the above license.

Google API Protobuf Definitions (Arrow)

Apache License Version 2.0, January 2004
http://www.apache.org/licenses/

TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION

1. Definitions.

"License" shall mean the terms and conditions for use, reproduction, and distribution as defined by Sections 1 through 9 of this document.

"Licensor" shall mean the copyright owner or entity authorized by the copyright owner that is granting the License.

"Legal Entity" shall mean the union of the acting entity and all other entities that control, are controlled by, or are under common control with that entity. For the purposes of this definition, "control" means (i) the power, direct or indirect, to cause the direction or management of such entity, whether by contract or otherwise, or (ii) ownership of fifty percent (50%) or more of the outstanding shares, or (iii) beneficial ownership of such entity.

"You" (or "Your") shall mean an individual or Legal Entity exercising permissions granted by this License.

"Source" form shall mean the preferred form for making modifications, including but not limited to software source code, documentation source, and configuration files.

"Object" form shall mean any form resulting from mechanical transformation or translation of a Source form, including but not limited to compiled object code, generated documentation, and conversions to other media types.

"Work" shall mean the work of authorship, whether in Source or Object form, made available under the License, as indicated by a copyright notice that is included in or attached to the work (an example is provided in the Appendix below).

"Derivative Works" shall mean any work, whether in Source or Object form, that is based on (or derived from) the Work and for which the editorial revisions, annotations, elaborations, or other modifications represent, as a whole, an original work of authorship. For the purposes of this License, Derivative Works shall not include works that remain separable from, or merely link (or bind by name) to the interfaces of, the Work and Derivative Works thereof.

"Contribution" shall mean any work of authorship, including the original version of the Work and any modifications or additions to that Work or Derivative Works thereof, that is intentionally submitted to Licensor for inclusion in the Work by the copyright owner or by an individual or Legal Entity authorized to submit on behalf of the copyright owner. For the purposes of this definition, "submitted" means any form of electronic, verbal, or written communication sent to the Licensor or its representatives, including but not limited to communication on electronic mailing lists, source code control systems, and issue tracking systems that are managed by, or on behalf of, the Licensor for the purpose of discussing and improving the Work, but excluding communication that is conspicuously marked or otherwise designated in writing by the copyright owner as "Not a Contribution."

"Contributor" shall mean Licensor and any individual or Legal Entity on behalf of whom a Contribution has been received by Licensor and subsequently incorporated within the Work.

2. Grant of Copyright License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display, publicly perform, sublicense, and distribute the Work and such Derivative Works in Source or Object form.

3. Grant of Patent License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable (except as stated in this section) patent license to make, have made, use, offer to sell, sell, import, and otherwise transfer the Work, where such license applies only to those patent claims licensable by such Contributor that are necessarily infringed by their Contribution(s) alone or by combination of their Contribution(s) with the Work to which such Contribution(s) was submitted. If You institute patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Work or a Contribution incorporated within the Work constitutes direct or contributory patent infringement, then any patent licenses granted to You under this License for that Work shall terminate as of the date such litigation is filed.

4. Redistribution. You may reproduce and distribute copies of the Work or Derivative Works thereof in any medium, with or without modifications, and in Source or Object form, provided that You meet the following conditions:

(a) You must give any other recipients of the Work or Derivative Works a copy of this License; and

(b) You must cause any modified files to carry prominent notices stating that You changed the files; and

(c) You must retain, in the Source form of any Derivative Works that You distribute, all copyright, patent, trademark, and attribution notices from the Source form of the Work, excluding those notices that do not pertain to any part of the Derivative Works; and

(d) If the Work includes a "NOTICE" text file as part of its distribution, then any Derivative Works that You distribute must include a readable copy of the attribution notices contained within such NOTICE file, excluding those notices that do not pertain to any part of the Derivative Works, in at least one of the following places: within a NOTICE text file distributed as part of the Derivative Works; within the Source form or documentation, if provided along with the Derivative Works; or, within a display generated by the Derivative Works, if and wherever such third-party notices normally appear. The contents of the NOTICE file are for informational purposes only and do not modify the License. You may add Your own attribution notices within Derivative Works that You distribute, alongside or as an addendum to the NOTICE text from the Work, provided that such additional attribution notices cannot be construed as modifying the License.

You may add Your own copyright statement to Your modifications and may provide additional or different license terms and conditions for use, reproduction, or distribution of Your modifications, or for any such Derivative Works as a whole, provided Your use, reproduction, and distribution of the Work otherwise complies with the conditions stated in this License.

5. Submission of Contributions. Unless You explicitly state otherwise, any Contribution intentionally submitted for inclusion in the Work by You to the Licensor shall be under the terms and conditions of this License, without any additional terms or conditions. Notwithstanding the above, nothing herein shall supersede or modify the terms of any separate license agreement you may have executed with Licensor regarding such Contributions.

6. Trademarks. This License does not grant permission to use the trade names, trademarks, service marks, or product names of the Licensor, except as required for reasonable and customary use in describing the origin of the Work and reproducing the content of the NOTICE file.

7. Disclaimer of Warranty. Unless required by applicable law or agreed to in writing, Licensor provides the Work (and each Contributor provides its Contributions) on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied, including, without limitation, any warranties or conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULAR PURPOSE. You are solely responsible for determining the appropriateness of using or redistributing the Work and assume any risks associated with Your exercise of permissions under this License.

8. Limitation of Liability. In no event and under no legal theory, whether in tort (including negligence), contract, or otherwise, unless required by applicable law (such as deliberate and grossly negligent acts) or agreed to in writing, shall any Contributor be liable to You for damages, including any direct, indirect, special, incidental, or consequential damages of any character arising as a result of this License or out of the use or inability to use the Work (including but not limited to damages for loss of goodwill, work stoppage, computer failure or malfunction, or any and all other commercial damages or losses), even if such Contributor has been advised of the possibility of such damages.

9. Accepting Warranty or Additional Liability. While redistributing the Work or Derivative Works thereof, You may choose to offer, and charge a fee for, acceptance of support, warranty, indemnity, or other liability obligations and/or rights consistent with this License. However, in accepting such obligations, You may act only on Your own behalf and on Your sole responsibility, not on behalf of any other Contributor, and only if You agree to indemnify, defend, and hold each Contributor harmless for any liability incurred by, or claims asserted against, such Contributor by reason of your accepting any such warranty or additional liability.

END OF TERMS AND CONDITIONS

APPENDIX: How to apply the Apache License to your work.

To apply the Apache License to your work, attach the following boilerplate notice, with the fields enclosed by brackets "[]" replaced with your own identifying information. (Don't include the brackets!) The text should be enclosed in the appropriate comment syntax for the file format. We also recommend that a file or class name and description of purpose be included on the same "printed page" as the copyright notice for easier identification within third-party archives.

Copyright [yyyy] [name of copyright owner]

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

Google API Protobuf Definitions (Avro)

Apache License Version 2.0, January 2004
http://www.apache.org/licenses/v

vro TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION

1. Definitions.

"License" shall mean the terms and conditions for use, reproduction, and distribution as defined by Sections 1 through 9 of this document.

"Licensor" shall mean the copyright owner or entity authorized by the copyright owner that is granting the License.

"Legal Entity" shall mean the union of the acting entity and all other entities that control, are controlled by, or are under common control with that entity. For the purposes of this definition, "control" means (i) the power, direct or indirect, to cause the direction or management of such entity, whether by contract or otherwise, or (ii) ownership of fifty percent (50%) or more of the outstanding shares, or (iii) beneficial ownership of such entity.

"You" (or "Your") shall mean an individual or Legal Entity exercising permissions granted by this License.

"Source" form shall mean the preferred form for making modifications, including but not limited to software source code, documentation source, and configuration files.

"Object" form shall mean any form resulting from mechanical transformation or translation of a Source form, including but not limited to compiled object code, generated documentation, and conversions to other media types.

"Work" shall mean the work of authorship, whether in Source or Object form, made available under the License, as indicated by a copyright notice that is included in or attached to the work (an example is provided in the Appendix below).

"Derivative Works" shall mean any work, whether in Source or Object form, that is based on (or derived from) the Work and for which the editorial revisions, annotations, elaborations, or other modifications represent, as a whole, an original work of authorship. For the purposes of this License, Derivative Works shall not include works that remain separable from, or merely link (or bind by name) to the interfaces of, the Work and Derivative Works thereof.

"Contribution" shall mean any work of authorship, including the original version of the Work and any modifications or additions to that Work or Derivative Works thereof, that is intentionally submitted to Licensor for inclusion in the Work by the copyright owner or by an individual or Legal Entity authorized to submit on behalf of the copyright owner. For the purposes of this definition, "submitted" means any form of electronic, verbal, or written communication sent to the Licensor or its representatives, including but not limited to communication on electronic mailing lists, source code control systems, and issue tracking systems that are managed by, or on behalf of, the Licensor for the purpose of discussing and improving the Work, but excluding communication that is conspicuously marked or otherwise designated in writing by the copyright owner as "Not a Contribution."

"Contributor" shall mean Licensor and any individual or Legal Entity on behalf of whom a Contribution has been received by Licensor and subsequently incorporated within the Work.

2. Grant of Copyright License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display, publicly perform, sublicense, and distribute the Work and such Derivative Works in Source or Object form.

3. Grant of Patent License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable (except as stated in this section) patent license to make, have made, use, offer to sell, sell, import, and otherwise transfer the Work, where such license applies only to those patent claims licensable by such Contributor that are necessarily infringed by their Contribution(s) alone or by combination of their Contribution(s) with the Work to which such Contribution(s) was submitted. If You institute patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Work or a Contribution incorporated within the Work constitutes direct or contributory patent infringement, then any patent licenses granted to You under this License for that Work shall terminate as of the date such litigation is filed.

4. Redistribution. You may reproduce and distribute copies of the Work or Derivative Works thereof in any medium, with or without modifications, and in Source or Object form, provided that You meet the following conditions:

(a) You must give any other recipients of the Work or Derivative Works a copy of this License; and

(b) You must cause any modified files to carry prominent notices stating that You changed the files; and

(c) You must retain, in the Source form of any Derivative Works that You distribute, all copyright, patent, trademark, and attribution notices from the Source form of the Work, excluding those notices that do not pertain to any part of the Derivative Works; and

(d) If the Work includes a "NOTICE" text file as part of its distribution, then any Derivative Works that You distribute must include a readable copy of the attribution notices contained within such NOTICE file, excluding those notices that do not pertain to any part of the Derivative Works, in at least one of the following places: within a NOTICE text file distributed as part of the Derivative Works; within the Source form or documentation, if provided along with the Derivative Works; or, within a display generated by the Derivative Works, if and wherever such third-party notices normally appear. The contents of the NOTICE file are for informational purposes only and do not modify the License. You may add Your own attribution notices within Derivative Works that You distribute, alongside or as an addendum to the NOTICE text from the Work, provided that such additional attribution notices cannot be construed as modifying the License.

You may add Your own copyright statement to Your modifications and may provide additional or different license terms and conditions for use, reproduction, or distribution of Your modifications, or for any such Derivative Works as a whole, provided Your use, reproduction, and distribution of the Work otherwise complies with the conditions stated in this License.

5. Submission of Contributions. Unless You explicitly state otherwise, any Contribution intentionally submitted for inclusion in the Work by You to the Licensor shall be under the terms and conditions of this License, without any additional terms or conditions. Notwithstanding the above, nothing herein shall supersede or modify the terms of any separate license agreement you may have executed with Licensor regarding such Contributions.

6. Trademarks. This License does not grant permission to use the trade names, trademarks, service marks, or product names of the Licensor, except as required for reasonable and customary use in describing the origin of the Work and reproducing the content of the NOTICE file.

7. Disclaimer of Warranty. Unless required by applicable law or agreed to in writing, Licensor provides the Work (and each Contributor provides its Contributions) on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied, including, without limitation, any warranties or conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULAR PURPOSE. You are solely responsible for determining the appropriateness of using or redistributing the Work and assume any risks associated with Your exercise of permissions under this License.

8. Limitation of Liability. In no event and under no legal theory, whether in tort (including negligence), contract, or otherwise, unless required by applicable law (such as deliberate and grossly negligent acts) or agreed to in writing, shall any Contributor be liable to You for damages, including any direct, indirect, special, incidental, or consequential damages of any character arising as a result of this License or out of the use or inability to use the Work (including but not limited to damages for loss of goodwill, work stoppage, computer failure or malfunction, or any and all other commercial damages or losses), even if such Contributor has been advised of the possibility of such damages.

9. Accepting Warranty or Additional Liability. While redistributing the Work or Derivative Works thereof, You may choose to offer, and charge a fee for, acceptance of support, warranty, indemnity, or other liability obligations and/or rights consistent with this License. However, in accepting such obligations, You may act only on Your own behalf and on Your sole responsibility, not on behalf of any other Contributor, and only if You agree to indemnify, defend, and hold each Contributor harmless for any liability incurred by, or claims asserted against, such Contributor by reason of your accepting any such warranty or additional liability.

END OF TERMS AND CONDITIONS

APPENDIX: How to apply the Apache License to your work.

To apply the Apache License to your work, attach the following boilerplate notice, with the fields enclosed by brackets "[]" replaced with your own identifying information. (Don't include the brackets!) The text should be enclosed in the appropriate comment syntax for the file format. We also recommend that a file or class name and description of purpose be included on the same "printed page" as the copyright notice for easier identification within third-party archives.

Copyright [yyyy] [name of copyright owner]

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.