Repository: google/cloud-forensics-utils

Issue:
Add BigQuery Functionalities to GCP
expected 07/08 2020

Discussion:
giovannt0: Thanks @dianakramer, can you fix the tests before we merge this?
codecov-commenter: # [Codecov](https://codecov.io/gh/google/cloud-forensics-utils/pull/424?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=google) Report
> :exclamation: No coverage uploaded for pull request base (`main@37a9de7`). [Click here to learn what that means](https://docs.codecov.io/docs/error-reference?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=google#section-missing-base-commit).
> The diff coverage is `n/a`.

[![Impacted file tree graph](https://codecov.io/gh/google/cloud-forensics-utils/pull/424/graphs/tree.svg?width=650&height=150&src=pr&token=zZkPno3ah0&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=google)](https://codecov.io/gh/google/cloud-forensics-utils/pull/424?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=google)

```diff
@@           Coverage Diff           @@
##             main     #424   +/-   ##
=======================================
  Coverage        ?   59.08%           
=======================================
  Files           ?       49           
  Lines           ?     4001           
  Branches        ?        0           
=======================================
  Hits            ?     2364           
  Misses          ?     1637           
  Partials        ?        0           
```

| Flag | Coverage Δ | |
|---|---|---|
| nosetests | `59.08% <0.00%> (?)` | |

Flags with carried forward coverage won't be shown. [Click here](https://docs.codecov.io/docs/carryforward-flags?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=google#carryforward-flags-in-the-pull-request-comment) to find out more.


------

[Continue to review full report at Codecov](https://codecov.io/gh/google/cloud-forensics-utils/pull/424?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=google).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=google)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/google/cloud-forensics-utils/pull/424?src=pr&el=footer&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=google). Last update [37a9de7...6546325](https://codecov.io/gh/google/cloud-forensics-utils/pull/424?src=pr&el=lastupdated&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=google). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=google).


Patch:
diff --git a/libcloudforensics/providers/gcp/internal/bigquery.py b/libcloudforensics/providers/gcp/internal/bigquery.py
new file mode 100644
index 0000000..12e8673
--- /dev/null
+++ b/libcloudforensics/providers/gcp/internal/bigquery.py
@@ -0,0 +1,59 @@
+# -*- coding: utf-8 -*-
+# Copyright 2021 Google Inc.
+#
+# 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 BigQuery functionalities."""
+
+from typing import TYPE_CHECKING, List, Dict, Any, Optional
+from libcloudforensics.providers.gcp.internal import common
+
+if TYPE_CHECKING:
+  import googleapiclient.discovery
+
+_BIGQUERY_API_VERSION = 'v2'
+
+class GoogleBigQuery:
+  """Class to call Google BigQuery APIs.
+
+  Attributes:
+    project_id: Google Cloud project ID.
+  """
+
+  def __init__(self, project_id: Optional[str] = None) -> None:
+    """Initialize the GoogleBigQuery object.
+
+    Args:
+      project_id: Optional. Google Cloud project ID.
+    """
+
+    self.project_id = project_id
+
+  def GoogleBigQueryApi(self) -> 'googleapiclient.discovery.Resource':
+    """Get a Google BigQuery service object.
+
+    Returns:
+      A Google BigQuery service object.
+    """
+
+    return common.CreateService('bigquery', _BIGQUERY_API_VERSION)
+
+  def ListBigQueryJobs(self) -> List[Dict[str, Any]]:
+    """List jobs of Google BigQuery within a project.
+
+    Returns:
+      List of jobs.
+    """
+    bq_jobs = self.GoogleBigQueryApi().jobs()  # pylint: disable=no-member
+    request = bq_jobs.list(projectId=self.project_id, projection='full')
+    jobs = request.execute()  # type: Dict[str, Any]
+    return jobs.get('jobs', [])
diff --git a/libcloudforensics/providers/gcp/internal/project.py b/libcloudforensics/providers/gcp/internal/project.py
index 8d90810..617deff 100644
--- a/libcloudforensics/providers/gcp/internal/project.py
+++ b/libcloudforensics/providers/gcp/internal/project.py
@@ -33,6 +33,7 @@ import libcloudforensics.providers.gcp.internal.cloudresourcemanager\
   as cloudresourcemanager_module
 import libcloudforensics.providers.gcp.internal.serviceusage\
   as serviceusage_module
+import libcloudforensics.providers.gcp.internal.bigquery as bigquery_module
 
 class GoogleCloudProject:
   """Class representing a Google Cloud Project.
@@ -86,6 +87,8 @@ class GoogleCloudProject:
     self._cloudresourcemanager = None  # type: Optional[cloudresourcemanager_module.GoogleCloudResourceManager]
     self._serviceusage = None  # type: Optional[serviceusage_module.GoogleServiceUsage]
     # pylint: enable=line-too-long
+    self._bigquery = None  # type: Optional[bigquery_module.GoogleBigQuery]
+
 
   def Delete(self) -> Dict[str, Any]:
     """Delete a GCP project.
@@ -253,3 +256,18 @@ class GoogleCloudProject:
     self._serviceusage = serviceusage_module.GoogleServiceUsage(
         self.project_id)
     return self._serviceusage
+
+  @property
+  def bigquery(self) -> bigquery_module.GoogleBigQuery:
+    """Get a GoogleBigQuery object for the project.
+
+    Returns:
+      GoogleBigQuery: Object that represents Google BigQuery.
+    """
+
+    if self._bigquery:
+      return self._bigquery
+    self._bigquery = bigquery_module.GoogleBigQuery(
+      self.project_id
+    )
+    return self._bigquery
diff --git a/tools/cli.py b/tools/cli.py
index 956917a..31954bc 100644
--- a/tools/cli.py
+++ b/tools/cli.py
@@ -58,6 +58,7 @@ PROVIDER_TO_FUNC = {
         'createbucket': gcp_cli.CreateBucket,
         'gkequarantine': gcp_cli.GKEWorkloadQuarantine,
         'gkeenumerate': gcp_cli.GKEEnumerate,
+        'listbigqueryjobs': gcp_cli.ListBigQueryJobs,
         'listbuckets': gcp_cli.ListBuckets,
         'listcloudsqlinstances': gcp_cli.ListCloudSqlInstances,
         'listdisks': gcp_cli.ListDisks,
@@ -508,6 +509,8 @@ def Main() -> None:
                     None),
                 ('--as_json', 'Output in JSON format.', False)
             ])
+  AddParser('gcp', gcp_subparsers, 'listbigqueryjobs',
+            'List BigQuery jobs for a project.')
 
   if len(sys.argv) == 1:
     parser.print_help()
diff --git a/tools/gcp_cli.py b/tools/gcp_cli.py
index b72c5ea..8a01707 100644
--- a/tools/gcp_cli.py
+++ b/tools/gcp_cli.py
@@ -22,6 +22,7 @@ from google.auth import default
 
 # pylint: disable=line-too-long
 from libcloudforensics import errors
+from libcloudforensics.providers.gcp.internal import bigquery as gcp_bigquery
 from libcloudforensics.providers.gcp.internal import compute as gcp_compute
 from libcloudforensics.providers.gcp.internal import gke
 from libcloudforensics.providers.gcp.internal import log as gcp_log
@@ -588,3 +589,30 @@ def GKEEnumerate(args: 'argparse.Namespace') -> None:
         enumeration.ToJson(namespace=args.namespace), sys.stdout, indent=2)
   else:
     enumeration.Enumerate(namespace=args.namespace)
+
+
+def ListBigQueryJobs(args: 'argparse.Namespace') -> None:
+  """List the BigQuery jobs of a Project.
+
+  Args:
+    args: Arguments from ArgumentParser.
+
+  Raises:
+    AttributeError: If no project_id was provided and none was inferred
+        from the gcloud environment.
+  """
+
+  AssignProjectID(args)
+
+  bq = gcp_bigquery.GoogleBigQuery(args.project)
+  jobs = bq.ListBigQueryJobs()
+  for job in jobs:
+    job_config = job['configuration']
+    if job_config['jobType'] == "QUERY":
+      logger.info(
+          '{0:s} {1:s} [{2:s}]'.format(
+              datetime.fromtimestamp(
+                  int(job['statistics']['startTime']) /
+                  1000).strftime('%Y-%m-%dT%H:%M:%SZ'),
+              job['user_email'],
+              job_config['query']['query']))


Test patch:
diff --git a/tests/providers/gcp/gcp_mocks.py b/tests/providers/gcp/gcp_mocks.py
index bb74768..96fa871 100644
--- a/tests/providers/gcp/gcp_mocks.py
+++ b/tests/providers/gcp/gcp_mocks.py
@@ -27,6 +27,7 @@ from libcloudforensics.providers.gcp.internal import storagetransfer as gcp_stor
 from libcloudforensics.providers.gcp.internal import cloudsql as gcp_cloudsql
 from libcloudforensics.providers.gcp.internal import cloudresourcemanager as gcp_resourcemanager
 from libcloudforensics.providers.gcp.internal import serviceusage as gcp_serviceusage
+from libcloudforensics.providers.gcp.internal import bigquery as gcp_bigquery
 # pylint: enable=line-too-long
 
 FAKE_ANALYSIS_PROJECT = gcp_project.GoogleCloudProject(
@@ -89,6 +90,7 @@ FAKE_MONITORING = gcp_monitoring.GoogleCloudMonitoring('fake-target-project')
 FAKE_CLOUDSQLINSTANCE = gcp_cloudsql.GoogleCloudSQL('fake-target-project')
 FAKE_CLOUD_RESOURCE_MANAGER = gcp_resourcemanager.GoogleCloudResourceManager('fake-project')
 FAKE_SERVICE_USAGE = gcp_serviceusage.GoogleServiceUsage('fake-project')
+FAKE_BIGQUERY = gcp_bigquery.GoogleBigQuery('fake-target-project')
 # pylint: enable=line-too-long
 
 # Mock struct to mimic GCP's API responses
@@ -908,3 +910,48 @@ MOCK_COMPUTE_IMAGE = {
   ],
   "kind": "compute#image"
 }
+
+MOCK_BIGQUERY_JOBS = {
+	"etag": "ABCde1FGHiJklmn23op4rs==",
+	"kind": "bigquery#jobList",
+	"jobs": [{
+		"id": "fake-target-project:europe-west1.bquxjob_12345678_abcdefghij1k",
+		"kind": "bigquery#job",
+		"jobReference": {
+			"projectId": "fake-target-project",
+			"jobId": "bquxjob_12345678_abcdefghij1k",
+			"location": "europe-west1"
+		},
+		"state": "DONE",
+		"statistics": {
+			"creationTime": "1640804415278",
+			"startTime": "1640804415351",
+			"endTime": "1640804415457",
+			"totalBytesProcessed": "0",
+			"query": {
+				"totalBytesProcessed": "0",
+				"totalBytesBilled": "0",
+				"cacheHit": True,
+				"statementType": "SELECT"
+			}
+		},
+		"configuration": {
+			"query": {
+				"query": "SELECT * FROM `fake-target-project.fake-target-project-dataset.fake-target-project-table`",
+				"destinationTable": {
+					"projectId": "fake-target-project",
+					"datasetId": "_1a2b34c567890d1efghi2j345678kl9012mn34c5",
+					"tableId": "anona1234c5d67890123efg45678hij90kl23mnoprst"
+				},
+				"writeDisposition": "WRITE_TRUNCATE",
+				"priority": "INTERACTIVE",
+				"useLegacySql": False
+			},
+			"jobType": "QUERY"
+		},
+		"status": {
+			"state": "DONE"
+		},
+		"user_email": "fake-user-email@test.com"
+	}]
+}
diff --git a/tests/providers/gcp/internal/test_bigquery.py b/tests/providers/gcp/internal/test_bigquery.py
new file mode 100644
index 0000000..5c15fd5
--- /dev/null
+++ b/tests/providers/gcp/internal/test_bigquery.py
@@ -0,0 +1,43 @@
+# -*- coding: utf-8 -*-
+# Copyright 2021 Google Inc.
+#
+# 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.
+"""Tests for the gcp module - bigquery.py"""
+
+import typing
+import unittest
+import mock
+
+from tests.providers.gcp import gcp_mocks
+
+
+class GoogleBigQueryTest(unittest.TestCase):
+  """Test Google BigQuery class."""
+  # pylint: disable=line-too-long
+
+  @typing.no_type_check
+  @mock.patch(
+      'libcloudforensics.providers.gcp.internal.bigquery.GoogleBigQuery.GoogleBigQueryApi'
+  )
+  def testListBigQueryJobs(self, mock_bigquery_api):
+    """Test BigQuery Jobs List operation."""
+    api_list_jobs = mock_bigquery_api.return_value.jobs.return_value.list
+    api_list_jobs.return_value.execute.return_value = gcp_mocks.MOCK_BIGQUERY_JOBS
+    list_results = gcp_mocks.FAKE_BIGQUERY.ListBigQueryJobs()
+    self.assertEqual(1, len(list_results))
+    self.assertEqual(
+        'bquxjob_12345678_abcdefghij1k',
+        list_results[0]['jobReference']['jobId'])
+    self.assertEqual(
+        'SELECT * FROM `fake-target-project.fake-target-project-dataset.fake-target-project-table`',
+        list_results[0]['configuration']['query']['query'])