Google BigQuery

Google BigQuery is a data warehouse for querying and analyzing large databases.

Configure Google BigQuery on Demisto

  1. Navigate to Settings > Integrations > Servers & Services .
  2. Search for Google BigQuery.
  3. Click Add instance to create and configure a new integration instance.
    • Name : a textual name for the integration instance.
    • Google service account JSON (a credentials JSON generated from Google API Manager or from GCP console)
  4. Click Test to validate the URLs, token, and connection.

Commands

You can execute these commands from the Demisto CLI, as part of an automation, or in a playbook.
After you successfully execute a command, a DBot message appears in the War Room with the command details.

1. Perform a query in BigQuery


Performs a query on BigQuery.

Base Command

bigquery-query

Input
Argument Name Description Required
query A query string (in BigQuery query syntax). For more information about the standard syntax, see the BigQuery documentation . Required
location The geographic location where the job should run. Required for locations other than US and EU. Optional
allow_large_results Allow query results tables larger than 128 MB compressed (legacy SQL only) Optional
default_dataset A string of the fully-qualified dataset ID in standard SQL format. The value must include a project ID and dataset ID, separated by periods. Optional
destination_table The table to which the results are written. Default value is “None”. Optional
kms_key_name Custom encryption configuration for the destination table. Optional
dry_run If “true,” BigQuery doesn’t run the job. Instead, if the query is valid, BigQuery returns statistics about the job, such as how many bytes would be processed. If the query is invalid, an error is returned. The default value is “false”. Optional
priority Priority of the query (“INTERACTIVE” or “BATCH”). A query set as INTERACTIVE will be run on-demand, at the next possible time. A query set as BATCH will start as soon as idle resources are available, and changed to INTERACTIVE priority if it wasn’t started within 24 hours. The default value is “INTERACTIVE”. Optional
use_query_cache Whether to look for the query results in the cache. Optional
use_legacy_sql Whether to use legacy SQL syntax. Optional
job_id The ID of the job. The ID must contain only letters (a-z, A-Z), numbers (0-9), underscores (_), or dashes (-). The maximum length is 1,024 characters. Optional
write_disposition Specifies the action that occurs if the destination table already exists. Optional

Context Output
Path Type Description
BigQuery.Query String The query performed.
BigQuery.Row Unknown The table rows the given query returned.

Command Example
!bigquery-query query="SELECT * FROMbigquery-public-data.usa_names.usa_1910_2013WHERE state='TX' LIMIT 100"
Context Example
{
    "BigQuery": {
        "Query": "SELECT * FROM `bigquery-public-data.usa_names.usa_1910_2013` WHERE state='TX' LIMIT 100", 
        "Row": [
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Frances", 
                "Number": 197
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Alice", 
                "Number": 149
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Beatrice", 
                "Number": 123
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Ella", 
                "Number": 102
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Gertrude", 
                "Number": 97
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Josephine", 
                "Number": 86
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Lula", 
                "Number": 77
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Blanche", 
                "Number": 50
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Marjorie", 
                "Number": 40
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Christine", 
                "Number": 34
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Margarita", 
                "Number": 31
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Alta", 
                "Number": 29
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Sara", 
                "Number": 28
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Naomi", 
                "Number": 24
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Sofia", 
                "Number": 23
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Andrea", 
                "Number": 16
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Delfina", 
                "Number": 16
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Dominga", 
                "Number": 16
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Elnora", 
                "Number": 16
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Adele", 
                "Number": 15
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Rafaela", 
                "Number": 12
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Dixie", 
                "Number": 11
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Luisa", 
                "Number": 11
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Bess", 
                "Number": 10
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Ernestine", 
                "Number": 10
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Lorine", 
                "Number": 9
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Patsy", 
                "Number": 9
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Cecelia", 
                "Number": 8
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Enriqueta", 
                "Number": 8
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Lucinda", 
                "Number": 8
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Alyce", 
                "Number": 7
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Oneta", 
                "Number": 7
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Apolonia", 
                "Number": 6
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Gloria", 
                "Number": 6
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Rhoda", 
                "Number": 6
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Buna", 
                "Number": 5
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Maye", 
                "Number": 5
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Pansy", 
                "Number": 5
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Gladys", 
                "Number": 240
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Maria", 
                "Number": 223
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Helen", 
                "Number": 189
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Velma", 
                "Number": 133
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Clara", 
                "Number": 129
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Anna", 
                "Number": 117
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Evelyn", 
                "Number": 106
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Rosa", 
                "Number": 88
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Mae", 
                "Number": 83
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Bernice", 
                "Number": 77
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Stella", 
                "Number": 69
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Vivian", 
                "Number": 63
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Mable", 
                "Number": 62
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Maggie", 
                "Number": 54
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Betty", 
                "Number": 50
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Guadalupe", 
                "Number": 50
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Lorene", 
                "Number": 46
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Susie", 
                "Number": 44
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Sadie", 
                "Number": 42
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Erma", 
                "Number": 38
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Verna", 
                "Number": 37
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Amelia", 
                "Number": 33
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Estelle", 
                "Number": 31
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Nell", 
                "Number": 31
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Margie", 
                "Number": 28
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Celia", 
                "Number": 26
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Lessie", 
                "Number": 24
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Eloise", 
                "Number": 22
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Millie", 
                "Number": 22
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Olga", 
                "Number": 21
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Letha", 
                "Number": 20
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Rachel", 
                "Number": 19
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Luz", 
                "Number": 16
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Gussie", 
                "Number": 15
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Sylvia", 
                "Number": 14
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Aline", 
                "Number": 13
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Barbara", 
                "Number": 11
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Iris", 
                "Number": 11
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Leila", 
                "Number": 10
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Mozelle", 
                "Number": 10
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Simona", 
                "Number": 9
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Catalina", 
                "Number": 8
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Hester", 
                "Number": 8
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Willia", 
                "Number": 8
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Allene", 
                "Number": 7
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Avis", 
                "Number": 7
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "George", 
                "Number": 7
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Isabella", 
                "Number": 7
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Polly", 
                "Number": 7
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Syble", 
                "Number": 7
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Teodora", 
                "Number": 7
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Lennie", 
                "Number": 5
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Ricarda", 
                "Number": 5
            }, 
            {
                "Gender": "F", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Valerie", 
                "Number": 5
            }, 
            {
                "Gender": "M", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Robert", 
                "Number": 276
            }, 
            {
                "Gender": "M", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Willie", 
                "Number": 199
            }, 
            {
                "Gender": "M", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Fred", 
                "Number": 78
            }, 
            {
                "Gender": "M", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Raymond", 
                "Number": 78
            }, 
            {
                "Gender": "M", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Richard", 
                "Number": 75
            }, 
            {
                "Gender": "M", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Sam", 
                "Number": 56
            }, 
            {
                "Gender": "M", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Ernest", 
                "Number": 55
            }, 
            {
                "Gender": "M", 
                "State": "TX", 
                "Year": 1910, 
                "Name": "Leroy", 
                "Number": 29
            }
        ]
    }
}
Human Readable Output

BigQuery Query Results

State Gender Year Name Number
TX F 1910 Frances 197
TX F 1910 Alice 149
TX F 1910 Beatrice 123
TX F 1910 Ella 102
TX F 1910 Gertrude 97
TX F 1910 Josephine 86
TX F 1910 Lula 77
TX F 1910 Blanche 50
TX F 1910 Marjorie 40
TX F 1910 Christine 34
TX F 1910 Margarita 31
TX F 1910 Alta 29
TX F 1910 Sara 28
TX F 1910 Naomi 24
TX F 1910 Sofia 23
TX F 1910 Andrea 16
TX F 1910 Delfina 16
TX F 1910 Dominga 16
TX F 1910 Elnora 16
TX F 1910 Adele 15
TX F 1910 Rafaela 12
TX F 1910 Dixie 11
TX F 1910 Luisa 11
TX F 1910 Bess 10
TX F 1910 Ernestine 10
TX F 1910 Lorine 9
TX F 1910 Patsy 9
TX F 1910 Cecelia 8
TX F 1910 Enriqueta 8
TX F 1910 Lucinda 8
TX F 1910 Alyce 7
TX F 1910 Oneta 7
TX F 1910 Apolonia 6
TX F 1910 Gloria 6
TX F 1910 Rhoda 6
TX F 1910 Buna 5
TX F 1910 Maye 5
TX F 1910 Pansy 5
TX F 1910 Gladys 240
TX F 1910 Maria 223
TX F 1910 Helen 189
TX F 1910 Velma 133
TX F 1910 Clara 129
TX F 1910 Anna 117
TX F 1910 Evelyn 106
TX F 1910 Rosa 88
TX F 1910 Mae 83
TX F 1910 Bernice 77
TX F 1910 Stella 69
TX F 1910 Vivian 63
TX F 1910 Mable 62
TX F 1910 Maggie 54
TX F 1910 Betty 50
TX F 1910 Guadalupe 50
TX F 1910 Lorene 46
TX F 1910 Susie 44
TX F 1910 Sadie 42
TX F 1910 Erma 38
TX F 1910 Verna 37
TX F 1910 Amelia 33
TX F 1910 Estelle 31
TX F 1910 Nell 31
TX F 1910 Margie 28
TX F 1910 Celia 26
TX F 1910 Lessie 24
TX F 1910 Eloise 22
TX F 1910 Millie 22
TX F 1910 Olga 21
TX F 1910 Letha 20
TX F 1910 Rachel 19
TX F 1910 Luz 16
TX F 1910 Gussie 15
TX F 1910 Sylvia 14
TX F 1910 Aline 13
TX F 1910 Barbara 11
TX F 1910 Iris 11
TX F 1910 Leila 10
TX F 1910 Mozelle 10
TX F 1910 Simona 9
TX F 1910 Catalina 8
TX F 1910 Hester 8
TX F 1910 Willia 8
TX F 1910 Allene 7
TX F 1910 Avis 7
TX F 1910 George 7
TX F 1910 Isabella 7
TX F 1910 Polly 7
TX F 1910 Syble 7
TX F 1910 Teodora 7
TX F 1910 Lennie 5
TX F 1910 Ricarda 5
TX F 1910 Valerie 5
TX M 1910 Robert 276
TX M 1910 Willie 199
TX M 1910 Fred 78
TX M 1910 Raymond 78
TX M 1910 Richard 75
TX M 1910 Sam 56
TX M 1910 Ernest 55
TX M 1910 Leroy 29