Adapter Online Help > TDV Google BigQuery Adapter > Data Model > Stored Procedures
 
Stored Procedures
Stored procedures are available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.
Google BigQuery Adapter Stored Procedures
Name
Description
CancelJob
Cancels a running BigQuery job.
CreateSchema
Creates a schema file for the specified table or view.
DeleteTable
Deletes the specified table from Google BigQuery.
GetJob
Inserts a Google BigQuery job, which can then be selected later to retrieve the query results.
GetOAuthAccessToken
Obtains the OAuth access token to be used for authentication with various Google services.
GetOAuthAuthorizationURL
Obtains the OAuth authorization URL for authentication with various Google services.
InsertJob
Inserts a Google BigQuery job, which can then be selected later to retrieve the query results.
RefreshOAuthAccessToken
Obtains the OAuth access token to be used for authentication with various Google services.
CancelJob
Cancels a running BigQuery job.
Input
Name
Type
Description
JobId
String
The Id of the job you wish to cancel.
Region
String
The region where the job is executing. Not required if the job is a US or EU region.
Result Set Columns
Name
Type
Description
JobId
String
The JobId of the cancelled Job.
Region
String
The region where the job was executing.
Configuration_query_query
String
The query of the cancelled job.
Configuration_query_destinationTable_tableId
String
The destination table tableId of the cancelled job.
Configuration_query_destinationTable_projectId
String
The destination table projectId of the newly inserted job.
Configuration_query_destinationTable_datasetId
String
The destination table datasetId of the newly inserted job.
Status_State
String
Running state of the job.
Status_errorResult_reason
String
A short error code that summarizes the error.
Status_errorResult_message
String
A human-readable description of the error.
 
CreateSchema
Creates a schema file for the specified table or view.
Input
Name
Type
Description
TableName
String
The name of the table or view.
FileName
String
The full file path and name of the schema to generate. Ex : 'C:\\Users\\User\\Desktop\\GoogleBigQuery\\table.rsd'
Result Set Columns
Name
Type
Description
Result
String
Returns Success or Failure.
DeleteTable
Deletes the specified table from Google BigQuery.
Input
Name
Type
Description
TableId
String
TableId of the table you wish to delete. ProjectId and DatasetId can come from connection properties, or to override them, the format projectId:datasetId.TableId.
Result Set Columns
Name
Type
Description
Success
String
Returns true if operation is successful, else an exception is returned.
GetJob
Inserts a Google BigQuery job, which can then be selected later to retrieve the query results.
Input
Name
Type
Description
JobId
String
The Id of the job you wish to return.
Region
String
The region where the job is executing. Not required if the job is a US or EU region.
Result Set Columns
Name
Type
Description
JobId
String
The JobId of the newly insert Job.
Region
String
The region where the job is executing.
Configuration_query_query
String
The query of the newly inserted Job.
Configuration_query_destinationTable_tableId
String
The destination table tableId of the newly inserted Job.
Configuration_query_destinationTable_projectId
String
The destination table projectId of the newly inserted Job.
Configuration_query_destinationTable_datasetId
String
The destination table datasetId of the newly inserted Job.
Status_State
String
Running state of the job.
Status_errorResult_reason
String
A short error code that summarizes the error.
Status_errorResult_message
String
A human-readable description of the error.
GetOAuthAccessToken
Obtains the OAuth access token to be used for authentication with various Google services.
Input
Name
Type
Description
AuthMode
String
The type of authentication mode to use.
The allowed values are APP, WEB.
The default value is WEB.
Verifier
String
The verifier code returned by Google after permissions have been granted for the app to connect. WEB Authmode only.
Scope
String
The scope of access to Google APIs. By default, access to all APIs used by this data provider will be specified.
The default value is https://www.googleapis.com/auth/bigquery.
CallbackURL
String
Determines where the response is sent. The value of this parameter must exactly match one of the values registered in the APIs Console (including the http or https schemes, case, and trailing '/').
ApprovalPrompt
String
Indicates if the user should be re-prompted for consent. The default is auto, so a given user should only see the consent page for a given set of scopes the first time through the sequence. If the value is force, then the user sees a consent page even if they have previously given consent to your application for a given set of scopes.
The allowed values are AUTO, FORCE.
The default value is AUTO.
AccessType
String
Indicates if your application needs to access a Google API when the user is not present at the browser. This parameter defaults to offline. If your application needs to refresh access tokens when the user is not present at the browser, then use offline. This will result in your application obtaining a refresh token the first time your application exchanges an authorization code for a user.
The allowed values are ONLINE, OFFLINE.
The default value is OFFLINE.
State
String
Indicates any state which may be useful to your application upon receipt of the response. The Google Authorization Server roundtrips this parameter, so your application receives the same value it sent. Possible uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations.
Result Set Columns
Name
Type
Description
OAuthAccessToken
String
The authentication token returned from Google. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken
String
A token that may be used to obtain a new access token.
ExpiresIn
String
The remaining lifetime on the access token.
GetOAuthAuthorizationURL
Obtains the OAuth authorization URL for authentication with various Google services.
Input
Name
Type
Description
Scope
String
The scope of access to Google APIs. By default, access to all APIs used by this data provider will be specified.
The default value is https://www.googleapis.com/auth/bigquery.
CallbackURL
String
Determines where the response is sent. The value of this parameter must exactly match one of the values registered in the APIs Console (including the http or https schemes, case, and trailing '/').
ApprovalPrompt
String
Indicates if the user should be re-prompted for consent. The default is auto, so a given user should only see the consent page for a given set of scopes the first time through the sequence. If the value is force, then the user sees a consent page even if they have previously given consent to your application for a given set of scopes.
The allowed values are AUTO, FORCE.
The default value is AUTO.
AccessType
String
Indicates if your application needs to access a Google API when the user is not present at the browser. This parameter defaults to offline. If your application needs to refresh access tokens when the user is not present at the browser, then use offline. This will result in your application obtaining a refresh token the first time your application exchanges an authorization code for a user.
The allowed values are ONLINE, OFFLINE.
The default value is OFFLINE.
State
String
Indicates any state which may be useful to your application upon receipt of the response. The Google Authorization Server roundtrips this parameter, so your application receives the same value it sent. Possible uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations.
Result Set Columns
Name
Type
Description
URL
String
The URL to complete user authentication.
InsertJob
Inserts a Google BigQuery job, which can then be selected later to retrieve the query results.
Input
Name
Type
Description
Query
String
The query to submit to Google BigQuery.
DestinationTable
String
The destination table for the query, in the format DestProjectId:DestDatasetId.DestTable
WriteDisposition
String
How to write data to the destination table, such as truncate existing results, appending existing results, or writing only when the table is empty.
The allowed values are WRITE_TRUNCATE, WRITE_APPEND, WRITE_EMPTY.
The default value is WRITE_TRUNCATE.
DryRun
String
Whether or not this is a dry run of the job.
Result Set Columns
Name
Type
Description
JobId
String
The JobId of the newly insert Job.
Region
String
The region where the job is executing.
Configuration_query_query
String
The query of the newly inserted Job.
Configuration_query_destinationTable_tableId
String
The destination table tableId of the newly inserted Job.
Configuration_query_destinationTable_projectId
String
The destination table projectId of the newly inserted Job.
Configuration_query_destinationTable_datasetId
String
The destination table datasetId of the newly inserted Job.
Status_State
String
Running state of the job.
Status_errorResult_reason
String
A short error code that summarizes the error.
Status_errorResult_message
String
A human-readable description of the error.
RefreshOAuthAccessToken
Obtains the OAuth access token to be used for authentication with various Google services.
Input
Name
Type
Description
OAuthRefreshToken
String
The refresh token returned from the original authorization code exchange.
Result Set Columns
Name
Type
Description
OAuthAuthToken
String
The authentication token returned from Google. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken
String
A token that may be used to obtain a new access token.
ExpiresIn
String
The remaining lifetime on the access token.