Generative AI Google Brain

gemini-use-cases-applying-llms-to-data-bigquery-dataframes-ml-drug-name-generation

3 min read

BigQuery DataFrames ML: Prescription Drug Name Generation

Source notebook

Repo path: gemini/use-cases/applying-llms-to-data/bigquery_dataframes_ml_drug_name_generation.ipynb · Open on GitHub · intermediate

Generates pharmaceutical brand name ideas with BigQuery DataFrames ML and Gemini.

Summary

This notebook demonstrates an enterprise generative AI workflow for prescription drug name generation. It uses BigQuery DataFrames to query and filter the public FDA drug label dataset, builds zero-shot and few-shot prompts, and calls Gemini through BigFrames ML. It also shows batch generation by creating per-row prompts for drugs whose generic and brand names match.

Key code patterns

Configure BigFrames

bpd.options.bigquery.project = PROJECT_ID
bpd.options.bigquery.location = LOCATION
session = bpd.get_global_session()

Sets the BigQuery project, location, and session used by BigFrames operations.

Create GeminiTextGenerator

model = GeminiTextGenerator(
    model_name=MODEL_ID,
    session=session,
    connection_name=connection_name,
)

Creates a BigFrames ML Gemini model bound to a BigQuery connection.

Single Prompt Prediction

def predict(model: GeminiTextGenerator, prompt: str) -> str:
    input = bpd.DataFrame({"prompt": [prompt]})
    return model.predict(input).ml_generate_text_llm_result.iloc[0]

Wraps one prompt in a BigFrames DataFrame and extracts the generated text.

FDA Dataset Query

df = bpd.read_gbq(
    "bigquery-public-data.fda_drug.drug_label",
    col_order=["openfda_generic_name", "openfda_brand_name", "indications_and_usage"],
)
df = df.dropna().drop_duplicates()

Loads the FDA drug label fields used to build few-shot examples.

Example Filtering

df = df[df["openfda_brand_name"].str.find(" ") == -1]
df = df[df["openfda_brand_name"].str.len() > 5]
df = df[df["openfda_generic_name"].str.lower() != df["openfda_brand_name"].str.lower()]

Filters for cleaner brand-name examples before prompt construction.

Batch Prompt Column

df_missing["prompt"] = (
    "Provide a unique and modern brand name related to this pharmaceutical drug."
    + "Don't use English words directly; use variants or invented words. The generic name is: "
    + df_missing["openfda_generic_name"]
    + ". The indications and usage are: "
    + df_missing["indications_and_usage"]
    + "."
)

Uses dataframe string operations to generate many row-specific prompts.

Models & APIs used

  • Models: gemini-2.5-flash
  • APIs / services: BigQuery, BigQuery ML, Vertex AI
  • SDKs / libraries: bigframes, google-cloud-bigquery-connection

When to use this

Use this pattern when generating LLM text over BigQuery-backed tabular data with prompt examples derived from the same dataset.

Gotchas & caveats

  • Requires a Google Cloud project with billing enabled.
  • Requires BigQuery API to be enabled.
  • Local JupyterLab requires gcloud auth login and Cloud SDK installation.
  • The BigQuery connection service account is granted roles/bigquery.connectionUser, roles/aiplatform.user, and roles/run.invoker.
  • BigQuery compute and BigQuery ML are billable components.
  • The notebook sets LOCATION to us and connection name to bigframes-ml.
  • The few-shot call shown as predict(few_shot_prompt) does not match the earlier predict(model, prompt) signature.

Best practices

  • Use PROJECT_ID from user input or GOOGLE_CLOUD_PROJECT environment variable.
  • Create or reuse a BigQuery connection before invoking GeminiTextGenerator.
  • Filter out missing, duplicate, short, spaced, and generic-matching brand names before using examples.
  • Use random_state when sampling examples for reproducible few-shot prompts.
  • Limit batch rows for demonstration purposes.
  • Clean up by deleting the BigQuery connection or deleting the project.

Graph View

Backlinks