Google BigQuery
Integration for Google BigQuery, a data warehouse for querying and analyzing large databases. In all commands, for any argument not specified, the BigQuery default value for that argument will be applied.
Database · Google BigQuery
Details
| ID | Google BigQuery |
|---|---|
| Provider | |
| Category | Database |
| From Version | 5.0.0 |
| Docker Image | demisto/bigquery:1.0.0.10408886 |
| Supported Modules | Agentix XSIAM |
README
Google BigQuery is a data warehouse for querying and analyzing large databases.
Configure Google BigQuery on Cortex XSOAR
- Navigate to Settings > Integrations > Servers & Services.
- Search for Google BigQuery.
- 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)
- Region: The geographic location where the connectivity test should run.
- Click Test to validate the URLs, token, and connection.
Commands
You can execute these commands from the Cortex XSOAR 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 |
Configuration parameters
google_service_creds— Google service account JSON (a credentials JSON generated from Google API Manager or from GCP console)credentials_google_service—region— Regionfirst_fetch— First fetch timestamp (<number> <time unit>. For example, 12 hours, 7 days)incidentType— Incident typeincidentFetchInterval— Incidents Fetch Intervalmax_fetch— Fetch Limit (Max.- 200, Recommended less than 50)isFetch— Fetch incidentsfetch_query— Fetch queryfetch_time_field— Fetch time field
Commands (1)
-
bigquery-queryPerforms a query on BigQuery.
import datetime import json import demistomock as demisto def test_convert_to_string(): from GoogleBigQuery import convert_to_string test_conversion_for_none = convert_to_string(None) assert test_conversion_for_none is None now = datetime.datetime.now() convert_to_string(now) test_conversion_for_empty_string = convert_to_string("") assert test_conversion_for_empty_string == "" today = datetime.date.today() convert_to_string(today) test_conversion_for_empty_string = convert_to_string("") assert test_conversion_for_empty_string == "" assert convert_to_string(b"test") == "test" def test_convert_to_string_datetime(): """ Given: - A datetime object. - A datetime format string. When: - Calling convert_to_string. Then: - The datetime object is converted to a string according to the format. """ from GoogleBigQuery import convert_to_string dt = datetime.datetime(2023, 1, 30, 12, 0, 0) assert convert_to_string(dt, "%Y-%m-%d %H:%M:%S") == "2023-01-30 12:00:00" def test_convert_to_string_datetime_default_format(): """ Given: - A datetime object. - No datetime format string. When: - Calling convert_to_string. Then: - The datetime object is converted to a string using the default format "%m/%d/%Y %H:%M:%S". """ from GoogleBigQuery import convert_to_string dt = datetime.datetime(2023, 1, 30, 12, 0, 0) assert convert_to_string(dt) == "01/30/2023 12:00:00" def test_convert_to_string_date(): """ Given: - A date object. - A datetime format string. When: - Calling convert_to_string. Then: - The date object is converted to a string according to the format. """ from GoogleBigQuery import convert_to_string d = datetime.date(2023, 1, 15) assert convert_to_string(d, date_only_format="%Y-%m-%d") == "2023-01-15" def test_convert_to_string_date_default_format(): """ Given: - A date object. - No datetime format string. When: - Calling convert_to_string. Then: - The date object is converted to a string using the default format "%m/%d/%Y". """ from GoogleBigQuery import convert_to_string d = datetime.date(2023, 1, 15) assert convert_to_string(d) == "01/15/2023" def test_convert_to_string_other(): """ Given: - A value that is not datetime, date, or bytes (e.g., a string or None). When: - Calling convert_to_string. Then: - The value is returned as is. """ from GoogleBigQuery import convert_to_string assert convert_to_string("test") == "test" assert convert_to_string(None) is None assert convert_to_string(123) == 123 def test_remove_outdated_incident_ids_keep_equal(): """ Given: - Several incidents with different occurrence times. - A start time that is equal to the occurrence time of one of the incidents. When: - Using the remove_outdated_incident_ids function to remove outdated incidents. Then: - Incidents that date before the start time will be removed. - Incidents that date after it will remain. """ from GoogleBigQuery import remove_outdated_incident_ids found_incidents_ids = { "aaa": "2020-05-05 07:07:07.000", "bbb": "2020-05-05 08:08:08.000", "ccc": "2020-05-05 08:08:09.000", "ddd": "2020-05-05 08:08:09.001", } latest_incident_time_str = "2020-05-05 08:08:09.000" res = remove_outdated_incident_ids(found_incidents_ids, latest_incident_time_str) assert "aaa" not in res assert "bbb" not in res assert "ddd" in res def test_remove_outdated_incident_ids_keep_equal_one_incident(): """ Given: - A start time of the current run. - One incident with a more recent occurrence time. When: - Using the remove_outdated_incident_ids function to remove outdated incidents. Then: - The incident will remain in the result. """ from GoogleBigQuery import remove_outdated_incident_ids found_incidents_ids = {"ddd": "2020-05-05 08:08:09.001"} latest_incident_time_str = "2020-05-05 08:08:09.000" res = remove_outdated_incident_ids(found_incidents_ids, latest_incident_time_str) assert "ddd" in res def test_remove_outdated_incident_ids_keep_equal_no_incidents(): """ Given: - A start time of the current run. - An empty list of incidents When: - Using the remove_outdated_incident_ids function to remove outdated incidents. Then: - The function will work as expected and will successfully handle the case. """ from GoogleBigQuery import remove_outdated_incident_ids found_incidents_ids = {} latest_incident_time_str = "2020-05-05 08:08:09.000" res = remove_outdated_incident_ids(found_incidents_ids, latest_incident_time_str) assert "aaa" not in res assert "bbb" not in res def test_verify_params_all_existing(mocker): """ Given: - Demisto params that include the first_fetch, fetch_query and fetch_time_field params. When: - Activating the verify_params function. Then: - No error will be returned. """ from GoogleBigQuery import verify_params mock_params = {"first_fetch": "1 days", "fetch_query": "test", "fetch_time_field": "test"} mocker.patch.object(demisto, "params", return_value=mock_params) return_error_target = "GoogleBigQuery.return_error" return_error_mock = mocker.patch(return_error_target) verify_params() assert return_error_mock.call_count == 0 def test_verify_params_first_fetch_time_missing(mocker): """ Given: - Demisto params that don't include the first_fetch param. When: - Activating the verify_params function. Then: - An error will be returned. """ from GoogleBigQuery import verify_params mock_params = {"fetch_query": "test", "fetch_time_field": "test"} mocker.patch.object(demisto, "params", return_value=mock_params) return_error_target = "GoogleBigQuery.return_error" return_error_mock = mocker.patch(return_error_target) verify_params() assert return_error_mock.call_count == 1 mock_params = {"first_fetch_time": "", "fetch_query": "test", "fetch_time_field": "test"} mocker.patch.object(demisto, "params", return_value=mock_params) verify_params() assert return_error_mock.call_count == 2 def test_verify_params_fetch_query_missing(mocker): """ Given: - Demisto params that don't include the fetch_query param. When: - Activating the verify_params function. Then: - An error will be returned. """ from GoogleBigQuery import verify_params mock_params = {"first_fetch": "1 days", "fetch_time_field": "test"} mocker.patch.object(demisto, "params", return_value=mock_params) return_error_target = "GoogleBigQuery.return_error" return_error_mock = mocker.patch(return_error_target) verify_params() assert return_error_mock.call_count == 1 mock_params = {"first_fetch": "1 days", "fetch_query": "", "fetch_time_field": "test"} mocker.patch.object(demisto, "params", return_value=mock_params) verify_params() assert return_error_mock.call_count == 2 def test_verify_params_fetch_time_field_missing(mocker): """ Given: - Demisto params that don't include the fetch_time_field param. When: - Activating the verify_params function. Then: - An error will be returned. """ from GoogleBigQuery import verify_params mock_params = { "first_fetch": "1 days", "fetch_query": "test", } mocker.patch.object(demisto, "params", return_value=mock_params) return_error_target = "GoogleBigQuery.return_error" return_error_mock = mocker.patch(return_error_target) verify_params() assert return_error_mock.call_count == 1 mock_params = {"first_fetch": "1 days", "fetch_query": "test", "fetch_time_field": ""} mocker.patch.object(demisto, "params", return_value=mock_params) verify_params() assert return_error_mock.call_count == 2 def test_get_max_incident_time_single_incident(): """ Given: - Several incidents with different occurrence times. When: - Activating the get_max_incident_time function. Then: - The time of the incident with the maximal time will be returned. """ from GoogleBigQuery import get_max_incident_time incident = {"rawJSON": {"CreationTime": "2020-05-05 08:08:09"}} incident["rawJSON"] = json.dumps(incident["rawJSON"]) incidents = [incident] assert get_max_incident_time(incidents) == "2020-05-05 08:08:09.000000" def test_get_max_incident_time_several_incidents(): """ Given: - One incident with an occurrence time. When: - Activating the get_max_incident_time function. Then: - The case will be handled successfully. """ from GoogleBigQuery import get_max_incident_time incident_a = {"rawJSON": {"CreationTime": "2020-05-05 08:08:09"}} incident_b = {"rawJSON": {"CreationTime": "2020-05-06 08:08:09"}} incident_c = {"rawJSON": {"CreationTime": "2020-05-06 09:08:09"}} incident_d = {"rawJSON": {"CreationTime": "2020-05-06 09:09:09"}} incident_a["rawJSON"] = json.dumps(incident_d["rawJSON"]) incident_b["rawJSON"] = json.dumps(incident_d["rawJSON"]) incident_c["rawJSON"] = json.dumps(incident_d["rawJSON"]) incident_d["rawJSON"] = json.dumps(incident_d["rawJSON"]) incidents = [incident_d, incident_a, incident_c, incident_b] assert get_max_incident_time(incidents) == "2020-05-06 09:09:09.000000" def test_query_command_dry_run(mocker): """ Given: - A query to run. - dry_run argument set to "true". When: - Calling query_command. Then: - Ensure return_outputs is called with the expected dry run message. """ from GoogleBigQuery import query_command import GoogleBigQuery mock_query_results = mocker.Mock() mock_query_results.total_bytes_processed = 1024 mocker.patch.object(GoogleBigQuery, "get_query_results", return_value=mock_query_results) mocker.patch.object(demisto, "args", return_value={"query": "SELECT 1", "dry_run": "true"}) return_outputs_mock = mocker.patch("GoogleBigQuery.return_outputs") query_command("SELECT 1") assert return_outputs_mock.call_count == 1 _, kwargs = return_outputs_mock.call_args assert "This query will process 1024 bytes" in kwargs["readable_output"] def test_query_command_with_results(mocker): """ Given: - A query to run. - dry_run argument set to "false". - Query returns results. When: - Calling query_command. Then: - Ensure return_outputs is called with the expected markdown table and context. """ from GoogleBigQuery import query_command import GoogleBigQuery mock_row = {"user_id": 1, "user_name": "test"} mocker.patch.object(GoogleBigQuery, "get_query_results", return_value=[mock_row]) mocker.patch.object(demisto, "args", return_value={"query": "SELECT 1", "dry_run": "false"}) return_outputs_mock = mocker.patch("GoogleBigQuery.return_outputs") query_command("SELECT 1") assert return_outputs_mock.call_count == 1 _, kwargs = return_outputs_mock.call_args assert kwargs["outputs"]["BigQuery(val.Query && val.Query == obj.Query)"]["Row"][0]["UserId"] == 1 assert kwargs["outputs"]["BigQuery(val.Query && val.Query == obj.Query)"]["Row"][0]["UserName"] == "test" def test_query_command_with_underscore_format(mocker): """ Given: - A query to run. - context_key_format argument set to "underscore". - Query returns results. When: - Calling query_command. Then: - Ensure return_outputs is called with keys in underscore format. """ from GoogleBigQuery import query_command import GoogleBigQuery mock_row = {"user_id": 1, "user_name": "test"} mocker.patch.object(GoogleBigQuery, "get_query_results", return_value=[mock_row]) mocker.patch.object( demisto, "args", return_value={"query": "SELECT 1", "dry_run": "false", "context_key_format": "underscore"} ) return_outputs_mock = mocker.patch("GoogleBigQuery.return_outputs") query_command("SELECT 1") assert return_outputs_mock.call_count == 1 _, kwargs = return_outputs_mock.call_args assert kwargs["outputs"]["BigQuery(val.Query && val.Query == obj.Query)"]["Row"][0]["user_id"] == 1 assert kwargs["outputs"]["BigQuery(val.Query && val.Query == obj.Query)"]["Row"][0]["user_name"] == "test" def test_query_command_with_datetime_format(mocker): """ Given: - A query to run. - A datetime_format argument set to "%Y-%m-%d". - Query returns results with a datetime object. When: - Calling query_command. Then: - Ensure return_outputs is called with the datetime field formatted according to the datetime_format. """ from GoogleBigQuery import query_command import GoogleBigQuery dt = datetime.datetime(2023, 1, 30, 12, 0, 0) mock_row = {"time": dt} mocker.patch.object(GoogleBigQuery, "get_query_results", return_value=[mock_row]) mocker.patch.object( demisto, "args", return_value={"query": "SELECT 1", "dry_run": "false", "datetime_format": "%Y-%m-%d %H:%M:%S"} ) return_outputs_mock = mocker.patch("GoogleBigQuery.return_outputs") query_command("SELECT 1") assert return_outputs_mock.call_count == 1 _, kwargs = return_outputs_mock.call_args assert kwargs["outputs"]["BigQuery(val.Query && val.Query == obj.Query)"]["Row"][0]["Time"] == "2023-01-30 12:00:00" def test_query_command_with_date_only_format(mocker): """ Given: - A query to run. - A date_only_format argument set to "%Y-%m-%d". - Query returns results with a date object. When: - Calling query_command. Then: - Ensure return_outputs is called with the date field formatted according to the date_only_format. """ from GoogleBigQuery import query_command import GoogleBigQuery d = datetime.date(2023, 1, 30) mock_row = {"date": d} mocker.patch.object(GoogleBigQuery, "get_query_results", return_value=[mock_row]) mocker.patch.object(demisto, "args", return_value={"query": "SELECT 1", "dry_run": "false", "date_only_format": "%Y-%m-%d"}) return_outputs_mock = mocker.patch("GoogleBigQuery.return_outputs") query_command("SELECT 1") assert return_outputs_mock.call_count == 1 _, kwargs = return_outputs_mock.call_args assert kwargs["outputs"]["BigQuery(val.Query && val.Query == obj.Query)"]["Row"][0]["Date"] == "2023-01-30" def test_test_module_success(mocker): """ Given: - Valid credentials and a region in the integration parameters. When: - Calling test_module. Then: - Ensure the bigquery client is initialized. - Ensure the query is called with the correct SQL and location (region). - Ensure the result is "ok". """ from GoogleBigQuery import test_module # Mock demisto.params mock_params = {"credentials_google_service": {"password": '{"project_id": "test-project"}'}, "region": "us-central1"} mocker.patch.object(demisto, "params", return_value=mock_params) # Mock bigquery client and its methods mock_client = mocker.Mock() mock_query_job = mocker.Mock() mock_query_results = ["row1"] # Iterator will return this mocker.patch("GoogleBigQuery.bigquery.Client", return_value=mock_client) mock_client.query.return_value = mock_query_job mock_query_job.result.return_value = mock_query_results # Mock demisto.results results_mock = mocker.patch.object(demisto, "results") # Mock start_and_return_bigquery_client to avoid actual file operations mocker.patch("GoogleBigQuery.start_and_return_bigquery_client", return_value=mock_client) test_module() # Verify query was called with correct parameters mock_client.query.assert_called_once_with("SELECT 1", location="us-central1") # Verify results results_mock.assert_called_once_with("ok") def test_test_module_no_region_success(mocker): """ Given: - Valid credentials in the integration parameters and no region. When: - Calling test_module. Then: - Ensure the bigquery client is initialized. - Ensure the query is called with the correct SQL and location is None. - Ensure the result is "ok". """ from GoogleBigQuery import test_module # Mock demisto.params mock_params = {"credentials_google_service": {"password": '{"project_id": "test-project"}'}} mocker.patch.object(demisto, "params", return_value=mock_params) # Mock bigquery client and its methods mock_client = mocker.Mock() mock_query_job = mocker.Mock() mock_query_results = ["row1"] # Iterator will return this mocker.patch("GoogleBigQuery.bigquery.Client", return_value=mock_client) mock_client.query.return_value = mock_query_job mock_query_job.result.return_value = mock_query_results # Mock demisto.results results_mock = mocker.patch.object(demisto, "results") # Mock start_and_return_bigquery_client to avoid actual file operations mocker.patch("GoogleBigQuery.start_and_return_bigquery_client", return_value=mock_client) test_module() # Verify query was called with correct parameters mock_client.query.assert_called_once_with("SELECT 1", location=None) # Verify results results_mock.assert_called_once_with("ok")