BigQuery: resource leaks in client and %%bigquery magics

As pointed out in https://github.com/googleapis/google-cloud-python/issues/9457, creating a GAPIC client and not closing the client's transport's channel before letting the client get garbage collected means we leak sockets / file descriptors.

#### Steps to reproduce

1. Start a Jupyter Notebook (or launch the code example with ipython).
2. Load the magics with `%load_ext google.cloud.bigquery`
3. Run a `%%bigquery` magic command.
4. Observe with `psutil` that open connections are not closed.

#### Code example

Notebook as Markdown:

```python
import psutil

from google.cloud import bigquery
```


```python
current_process = psutil.Process()
```


```python
num_conns = len(current_process.connections())
print("connections before loading magics: {}".format(num_conns))
```

    connections before loading magics: 12



```python
%load_ext google.cloud.bigquery
```


```python
num_conns = len(current_process.connections())
print("connections after loading magics: {}".format(num_conns))
```

    connections after loading magics: 12



```python
%%bigquery --use_bqstorage_api
SELECT
    source_year AS year,
    COUNT(is_male) AS birth_count
FROM `bigquery-public-data.samples.natality`
GROUP BY year
ORDER BY year DESC
LIMIT 15
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>year</th>
      <th>birth_count</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>2008</td>
      <td>4255156</td>
    </tr>
    <tr>
      <td>1</td>
      <td>2007</td>
      <td>4324008</td>
    </tr>
    <tr>
      <td>2</td>
      <td>2006</td>
      <td>4273225</td>
    </tr>
    <tr>
      <td>3</td>
      <td>2005</td>
      <td>4145619</td>
    </tr>
    <tr>
      <td>4</td>
      <td>2004</td>
      <td>4118907</td>
    </tr>
    <tr>
      <td>5</td>
      <td>2003</td>
      <td>4096092</td>
    </tr>
    <tr>
      <td>6</td>
      <td>2002</td>
      <td>4027376</td>
    </tr>
    <tr>
      <td>7</td>
      <td>2001</td>
      <td>4031531</td>
    </tr>
    <tr>
      <td>8</td>
      <td>2000</td>
      <td>4063823</td>
    </tr>
    <tr>
      <td>9</td>
      <td>1999</td>
      <td>3963465</td>
    </tr>
    <tr>
      <td>10</td>
      <td>1998</td>
      <td>3945192</td>
    </tr>
    <tr>
      <td>11</td>
      <td>1997</td>
      <td>3884329</td>
    </tr>
    <tr>
      <td>12</td>
      <td>1996</td>
      <td>3894874</td>
    </tr>
    <tr>
      <td>13</td>
      <td>1995</td>
      <td>3903012</td>
    </tr>
    <tr>
      <td>14</td>
      <td>1994</td>
      <td>3956925</td>
    </tr>
  </tbody>
</table>
</div>




```python
num_conns = len(current_process.connections())
print("connections after running magics: {}".format(num_conns))
```

    connections after running magics: 16



```python
%%bigquery --use_bqstorage_api
SELECT
    source_year AS year,
    COUNT(is_male) AS birth_count
FROM `bigquery-public-data.samples.natality`
GROUP BY year
ORDER BY year DESC
LIMIT 15
```




<div>
<table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th></th>
      <th>year</th>
      <th>birth_count</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>0</td>
      <td>2008</td>
      <td>4255156</td>
    </tr>
    <tr>
      <td>1</td>
      <td>2007</td>
      <td>4324008</td>
    </tr>
    <tr>
      <td>2</td>
      <td>2006</td>
      <td>4273225</td>
    </tr>
    <tr>
      <td>3</td>
      <td>2005</td>
      <td>4145619</td>
    </tr>
    <tr>
      <td>4</td>
      <td>2004</td>
      <td>4118907</td>
    </tr>
    <tr>
      <td>5</td>
      <td>2003</td>
      <td>4096092</td>
    </tr>
    <tr>
      <td>6</td>
      <td>2002</td>
      <td>4027376</td>
    </tr>
    <tr>
      <td>7</td>
      <td>2001</td>
      <td>4031531</td>
    </tr>
    <tr>
      <td>8</td>
      <td>2000</td>
      <td>4063823</td>
    </tr>
    <tr>
      <td>9</td>
      <td>1999</td>
      <td>3963465</td>
    </tr>
    <tr>
      <td>10</td>
      <td>1998</td>
      <td>3945192</td>
    </tr>
    <tr>
      <td>11</td>
      <td>1997</td>
      <td>3884329</td>
    </tr>
    <tr>
      <td>12</td>
      <td>1996</td>
      <td>3894874</td>
    </tr>
    <tr>
      <td>13</td>
      <td>1995</td>
      <td>3903012</td>
    </tr>
    <tr>
      <td>14</td>
      <td>1994</td>
      <td>3956925</td>
    </tr>
  </tbody>
</table>
</div>




```python
num_conns = len(current_process.connections())
print("connections after running magics: {}".format(num_conns))
```

    connections after running magics: 20



Full example:

```python
import psutil

from google.cloud import bigquery

current_process = psutil.Process()
num_conns = len(current_process.connections())
print("connections before loading magics: {}".format(num_conns))

get_ipython().run_line_magic('load_ext', 'google.cloud.bigquery')

num_conns = len(current_process.connections())
print("connections after loading magics: {}".format(num_conns))

get_ipython().run_cell_magic('bigquery', '--use_bqstorage_api', 'SELECT\n    source_year AS year,\n    COUNT(is_male) AS birth_count\nFROM `bigquery-public-data.samples.natality`\nGROUP BY year\nORDER BY year DESC\nLIMIT 15')

num_conns = len(current_process.connections())
print("connections after running magics: {}".format(num_conns))

get_ipython().run_cell_magic('bigquery', '--use_bqstorage_api', 'SELECT\n    source_year AS year,\n    COUNT(is_male) AS birth_count\nFROM `bigquery-public-data.samples.natality`\nGROUP BY year\nORDER BY year DESC\nLIMIT 15')

num_conns = len(current_process.connections())
print("connections after running magics: {}".format(num_conns))
```

#### Stack trace

N/A

#### Suggested fix

As identified in https://github.com/googleapis/google-cloud-python/issues/9457, we need to close the `bqstorage_client.transport.channel`, since we create a new BQ Storage client each time.

I suggest we also add `psutil` as a test-only dependency and verify in a system test of `google.cloud.bigquery.magics._cell_magic` that there are no additional open connections after running the cell magic.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

BigQuery: resource leaks in client and %%bigquery magics #9790

Steps to reproduce

Code example

Stack trace

Suggested fix

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	year	birth_count
0	2008	4255156
1	2007	4324008
2	2006	4273225
3	2005	4145619
4	2004	4118907
5	2003	4096092
6	2002	4027376
7	2001	4031531
8	2000	4063823
9	1999	3963465
10	1998	3945192
11	1997	3884329
12	1996	3894874
13	1995	3903012
14	1994	3956925

BigQuery: resource leaks in client and %%bigquery magics #9790

Description

Steps to reproduce

Code example

Stack trace

Suggested fix

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions