<jupyter_start><jupyter_text>Working with BigQueryThis library offers wrappers around BigQuery connectors through the official Google client library and Pandas<jupyter_code>import os
import pandas as pd
from prediction_utils.extraction_utils.database import BQDatabase<jupyter_output><empty_output><jupyter_text>The primary class that we are going to work with is `BQDatabase`.The only keyword arguments that this class takes are `gcloud_project` and `google_application_credentials`.If not provided, they default to `som-nero-phi-nigam-starr` and `os.path.expanduser("~/.config/gcloud/application_default_credentials.json")` respectively.Let's create a database object called `db` using the defaults:<jupyter_code>db = BQDatabase()<jupyter_output>/share/pi/nigam/envs/anaconda/envs/prediction_utils/lib/python3.7/site-packages/google/auth/_default.py:69: UserWarning: Your application has authenticated using end user credentials from Google Cloud SDK. We recommend that most server applications use service accounts instead. If your application continues to use end user credentials from Cloud SDK, you might receive a "quota exceeded" or "API not enabled" error. For more information about service accounts, see https://cloud.google.com/docs/authentication/
  warnings.warn(_CLOUD_SDK_CREDENTIALS_WARNING)<jupyter_text>With this database object, extracting the results of a query to a pandas dataframe is as simple as calling `db.read_sql_query(...)`. `db.read_sql_query` takes a boolean argument `use_bqstorage_api` that defaults to True. This argument toggles whether the query should leverage the BigQuery Storage API. When True, the results will be downloaded significantly faster, but incurs more cost. For small datasets, it may be better to set this argument to False.This function further passes all additional keyword arguments to `pandas.read_gbq()`For example:<jupyter_code>query = """
    SELECT * 
    FROM som-rit-phi-starr-prod.starr_omop_cdm5_deid_lite_latest.person
    LIMIT 1000
"""
df = db.read_sql_query(query=query, use_bqstorage_api=False)
df.head()<jupyter_output><empty_output><jupyter_text>To execute arbitrary SQL, use `db.execute_sql`, which internally calls `client.query(query).result()`.<jupyter_code>df = db.execute_sql(query=query).to_dataframe()<jupyter_output><empty_output><jupyter_text>To write query results to a table in BigQuery without returning the results in Pandas, you can use `db.execute_sql_to_destination_table`. This method requires you to fully specify the destination<jupyter_code>destination='som-nero-phi-nigam-starr.temp_dataset.vignette_table'
db.execute_sql_to_destination_table(query=query, destination=destination)<jupyter_output><empty_output><jupyter_text>Let's query the destination to confirm that the results were written<jupyter_code>df2 = db.read_sql_query('SELECT * FROM {destination}'.format(destination=destination))<jupyter_output>Downloading:   0%|          | 0/1000 [00:00<?, ?rows/s]/share/pi/nigam/envs/anaconda/envs/prediction_utils/lib/python3.7/site-packages/pyarrow/util.py:39: FutureWarning: pyarrow.read_schema is deprecated as of 0.17.0, please use pyarrow.ipc.read_schema instead
  warnings.warn(msg, FutureWarning)
/share/pi/nigam/envs/anaconda/envs/prediction_utils/lib/python3.7/site-packages/pyarrow/util.py:39: FutureWarning: pyarrow.read_record_batch is deprecated as of 0.17.0, please use pyarrow.ipc.read_record_batch instead
  warnings.warn(msg, FutureWarning)
Downloading: 100%|██████████| 1000/1000 [00:01<00:00, 611.99rows/s]<jupyter_text>Check whether it's the same data (note that the order of the rows is not preserved)<jupyter_code>assert (
    df
    .sort_values('person_id')
    .reset_index(drop=True)
    .equals(
        df2
        .sort_values('person_id')
        .reset_index(drop=True)
    )
)<jupyter_output><empty_output><jupyter_text>We can also write pandas dataframes to tables in BigQuery using the `to_sql` method.This method takes a `mode` argument with valid values `"gbq"` and `"client"` that determine whether `pandas.DataFrame.to_gbq` or `client.load_table_from_dataframe` will be used. There are tradeoffs between these two methods, with the primary difference being that the interface to `to_gbq` is more straightforward, but writes all `DATE` columns as `TIMESTAMP`, and serializes data to CSV internally. The `client` approach allows for date columns to be written and uses Parquet to serialize data, but has a more verbose and complex interface. There are also some differences in how the destination table must be formatted.<jupyter_code># gbq method
destination='temp_dataset.vignette_table'
project_id='som-nero-phi-nigam-starr'
db.to_sql(df=df, destination_table=destination, project_id=project_id, mode='gbq')
# client method
destination='som-nero-phi-nigam-starr.temp_dataset.vignette_table'
db.to_sql(df=df, destination_table=destination, mode='client')<jupyter_output>/share/pi/nigam/envs/anaconda/envs/prediction_utils/lib/python3.7/site-packages/google/cloud/bigquery/_pandas_helpers.py:391: UserWarning: Pyarrow could not determine the type of columns: person_source_value.
  ", ".join(field.name for field in unknown_type_fields)
{'fraction_non_alphanumeric': 0.09150706132714258, 'fraction_numerical': 0.012768427161926872, 'mean_word_length': 6.2510518934081345, 'pattern_counts': {'":': 0, '<': 32, '<?xml version=': 0, '>': 30, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '158054', 'license': 'MIT', 'n_tokens_mistral': 1633, 'n_tokens_neox': 1571, 'n_words': 451, 'path': 'prediction_utils/vignettes/01-database.ipynb', 'repo_name': 'som-shahlab/fairness_benchmark', 'size': '5169'}