Google BigQuery
11 min
the google bigquery connector allows users to perform synchronous sql queries directly from swimlane, harnessing bigquery's powerful data processing capabilities google bigquery is a fully managed, serverless data warehouse that enables scalable analysis over petabytes of data it is a platform as a service (paas) that supports querying using ansi sql it also has built in machine learning capabilities the google bigquery connector for swimlane turbine allows users to execute synchronous sql queries, manage datasets, and leverage bigquery's data analytics within the swimlane platform this integration empowers security teams to perform complex data analysis, enrich security event data, and automate responses based on insights derived from vast amounts of data prerequisites to utilize the google bigquery connector for turbine, ensure you have the following prerequisites google oauth2 credentials with the necessary permissions to access bigquery services the required parameters include url endpoint url for the bigquery api credentials a json object containing your service account's client id, client secret, and other oauth2 related information scopes specific access scopes required for the bigquery api, such as ' https //www googleapis com/auth/bigquery ' api key a unique identifier used to authenticate requests associated with your project on google cloud platform limitations none to date capabilities this connector provides the following capabilities jobs query jobs query runs a bigquery sql query synchronously and returns query results if the query completes within a specified timeout google bigquery documentation for this action can be found https //cloud google com/bigquery/docs/reference/rest/v2/jobs/query configurations google authentication authenticates using google credentials configuration parameters parameter description type required url a url to the target host string required credentials base64 encoded contents from service account json credentials file see readme for setup instructions string required scope list of permission scopes for this action array required apikey an optional api key to include as a query parameter string required verify ssl verify ssl certificate boolean optional http proxy a proxy to route requests through string optional actions jobs query executes a synchronous sql query on google bigquery and returns results within the specified timeout, requiring a 'projectid' and 'query' endpoint url {{projectid}}/queries method post input argument name type required description parameters $ xgafv number optional v1 error format parameters access token string optional access token parameters alt string optional data format for response parameters callback string optional callback response parameters fields string optional selector specifying which fields to include in a partial response parameters prettyprint boolean optional returns response with indentations and line breaks parameters quotauser string optional available to use for quota purposes for server side applications parameters uploadtype string optional type of the upload parameters upload protocol string optional upload protocol for media path parameters projectid string required project id of the query request kind string optional the resource type of the request query string optional a query string to execute, using google standard sql or legacy sql syntax maxresults number optional the maximum number of rows of data to return per page of results defaultdataset object optional specifies the default datasetid and projectid to assume for any unqualified table names in the query defaultdataset datasetid string required a unique id for this dataset, without the project name defaultdataset projectid string required the id of the project containing this dataset timeoutms number optional specifies the maximum amount of time, in milliseconds, that the client is willing to wait for the query to complete destinationencryptionconfiguration object optional custom encryption configuration destinationencryptionconfiguration kmskeyname string optional describes the cloud kms encryption key that will be used to protect destination bigquery table dryrun boolean optional if set to true, bigquery doesn't run the job preservenulls boolean optional this property is deprecated usequerycache boolean optional whether to look for the result in the query cache uselegacysql boolean optional specifies whether to use bigquery's legacy sql dialect for this query parametermode string optional specifies whether the query uses positional or named parameters queryparameters array optional jobs query parameters for googlesql queries input example {"parameters" {"$ xgafv" 1,"access token" "string","alt" "json","callback" "string","fields" "string","prettyprint"\ true,"quotauser" "string","uploadtype" "media","upload protocol" "raw"},"json body" {"kind" "bigquery#queryrequest","query" "select count(f1) from myprojectid mydatasetid mytableid","maxresults" 0,"defaultdataset" {"datasetid" "d12","projectid" "p12"},"timeoutms" 0,"destinationencryptionconfiguration" {"kmskeyname" "kms"},"dryrun"\ false,"preservenulls"\ false,"usequerycache"\ false,"uselegacysql"\ false,"queryparameters" \[{"name" "test","parametertype" {"type" "test","timestampprecision" 6},"parametervalue" {"value" "mynewval","rangevalue" {"start" {"value" "0"},"end" {"value" "100"}}}}],"location" "us","formatoptions" {"timestampoutputformat" "float64","useint64timestamp"\ false},"connectionproperties" \[{"key" "test key","value" "myval"}],"labels" {"test" "v1"},"maximumbytesbilled" 0,"requestid" "abc123","createsession"\ false,"jobcreationmode" "job creation mode unspecified","jobtimeoutms" 0,"reservation" "projects/1/locations/eu/reservations/2"},"path parameters" {"projectid" "abc123"}} output parameter type description status code number http status code of the response reason string response reason phrase kind string output field kind schema object output field schema schema fields array output field schema fields schema fields name string name of the resource schema fields type string type of the resource schema fields mode string output field schema fields mode schema fields fields array output field schema fields fields schema fields fields name string name of the resource schema fields fields type string type of the resource schema fields fields mode string output field schema fields fields mode schema fields description string output field schema fields description schema fields policytags object output field schema fields policytags schema fields policytags names array name of the resource schema fields datapolicies array response data schema fields datapolicies name string response data schema fields maxlength string output field schema fields maxlength schema fields precision string output field schema fields precision schema fields scale string output field schema fields scale schema fields roundingmode string output field schema fields roundingmode schema fields collation string output field schema fields collation schema fields defaultvalueexpression string value for the parameter schema fields rangeelementtype object type of the resource schema fields rangeelementtype type string type of the resource output example {"status code" 200,"reason" "ok","json body" {"kind" "bigquery#queryresponse","schema" {"fields" \[]},"jobreference" {"projectid" "x123","jobid" "abc123","location" "us"},"jobcreationreason" {"code" "requested"},"queryid" "xyz123","location" "us","totalrows" "100","pagetoken" "abc123","rows" \[{}],"totalbytesprocessed" "0","jobcomplete"\ true,"errors" \[{}],"cachehit"\ false,"numdmlaffectedrows" "10","sessioninfo" {"sessionid" "session 123"}}} response headers header description example content type the media type of the resource application/json date the date and time at which the message was originated thu, 01 jan 2024 00 00 00 gmt