Develop with the Gemini CLI

This page explains how to interact with BigQuery data in your terminal by using natural-language prompts with the Gemini CLI. Using the dedicated BigQuery extensions with the Gemini CLI enables AI-driven data analysis and insights directly within your command line. You can analyze data from your BigQuery tables, or from tables in the BigQuery public datasets.

These first-party extensions provide some common tools for data analysis and insights. To build your own tools, see Use BigQuery with MCP, Gemini CLI, and other agents.

About Gemini CLI and extensions

The Gemini CLI is an open-source conversational AI agent from Google that accelerates development workflows and assists with coding, debugging, data exploration, and content creation. It offers an agent-driven experience to interact with Data Cloud services, such as BigQuery, and other popular Google Cloud databases and open-source databases.

For more information about the Gemini CLI, see the Gemini CLI documentation.

How extensions work

Extensions expand the capabilities of the Gemini CLI, letting it connect to and control specific Google Cloud services and other tools. The extensions provide Gemini with context and API understanding, enabling conversational interaction. You can load the extensions from GitHub URLs, local directories, or registries. These extensions offer tools, slash commands, and prompts. These are separate from IDE extensions, such as Gemini Code Assist, which integrate using the MCP Toolbox.

About the BigQuery extensions

BigQuery offers the following extensions to work with BigQuery data through the Gemini CLI:

BigQuery Data Analytics extension
Use this extension to discover data, learn more about BigQuery tables and datasets, and ask natural language questions about your datasets. You can also generate forecasts or run a contribution analysis using built-in advanced tools. The tools in this extension help the Gemini CLI write SQL queries based on your natural language questions, and then execute the queries to provide answers for your questions.
BigQuery Conversational Analytics extension
Use this extension to get advanced insights from your BigQuery data using a pre-hosted, server-side analytics agent. The tools in this extension help the Gemini CLI send your natural language question to a built-in, hosted agent and respond with deeper insights from your data.

BigQuery Data Analytics tools

The BigQuery Data Analytics extension provides the following tools:

Category Tool Example natural language prompt
Dataset and table information get_dataset_info Get information about dataset my_dataset
get_table_info Get information about table my_dataset.my_table
list_dataset_ids List all datasets in project my_project
list_table_ids List all tables in dataset my_dataset
Search search_catalog Find tables related to PyPi downloads
SQL execution execute_sql Using bigquery-public-data.pypi.file_downloads show me top 10 downloaded pypi packages this month
Time-series forecasting bigquery_forecast Forecast sales for 10 periods from table my_table
Contribution analysis analyze_contribution Analyze contribution of country to revenue in table my_table

Conversational Analytics tools

The Conversational Analytics extension provides the following tools:

Category Tool Example natural language prompt
Search search_catalog Find tables related to PyPi downloads
Conversational analytics ask_data_insights Using ask_data_insights and using the tables under bigquery-public-data.google_analytics_sample , tell me the channels I should focus on and why

Before you begin

Before you begin, you need the following:

  • Access to at least one Google Cloud project.

  • For the Data Analytics extension, enable the following APIs:

    • BigQuery
    • To use the search_catalog tool: Dataplex Universal Catalog

  • For the Conversational Analytics extension, enable the following APIs:

    • BigQuery
    • Dataplex Universal Catalog
    • To use the ask_data_insights tool: Gemini for Google Cloud API, Gemini, and Data Analytics API with Gemini.

Required roles

To get the permissions that you need to use the BigQuery extensions with Gemini CLI, ask your administrator to grant you the following IAM roles on Resource Manager resources (projects, folders, and organizations) or lower-level resources like datasets:

For more information about granting roles, see Manage access to projects, folders, and organizations.

You might also be able to get the required permissions through custom roles or other predefined roles.

Install the BigQuery extensions

Install the BigQuery extensions and then use them to explore and analyze your data.

  1. Install the Gemini CLI.

  2. Set the environment variables to connect to your BigQuery project:

    export BIGQUERY_PROJECT="PROJECT_ID"

    Replace PROJECT_ID with your Google Cloud project ID.

    You can also set these optional environment variables:

    • BIGQUERY_LOCATION: the dataset location—for example: export BIGQUERY_LOCATION="us"
    • BIGQUERY_USE_CLIENT_OAUTH: whether to use client-side OAuth—for example: export BIGQUERY_USE_CLIENT_OAUTH=true

  3. Install the BigQuery Data Analytics or Conversational Analytics extension using the gemini extension install command:

    • Optional: install the BigQuery Data Analytics extension:

      gemini extensions install https://github.com/gemini-cli-extensions/bigquery-data-analytics

    • Optional: install the BigQuery Conversational Analytics extension:

      gemini extensions install https://github.com/gemini-cli-extensions/bigquery-conversational-analytics

  4. Start the Gemini CLI in interactive mode to explore and analyze your data:

    gemini

    The CLI automatically loads the BigQuery extension and its tools, which you can use to interact with your data.

Example prompts to explore and analyze your data

Enter prompts in the Gemini CLI. You can use the following examples to get started.

Explore and analyze tables

The following example prompts let you explore and analyze your tables.

  • Example prompt to find tables related to PyPi downloads:

    Find tables related to PyPi downloads

    Example response

    The Gemini CLI returns a list of related tables:

    ✦ I found the following tables related to PyPi downloads:

    *   file_downloads: projects/bigquery-public-data/datasets/pypi/tables/file_downloads
    *   distribution_metadata: projects/bigquery-public-data/datasets/pypi/tables/distribution_metadata

  • Example prompt to show the top ten downloaded PyPI packages this month:

    Using bigquery-public-data.pypi.file_downloads show me top 10
downloaded pypi packages this month

    Example response

    The Gemini CLI returns the top 10 packages:

    ✦ Here are the top 10 most downloaded PyPI packages this month:

    1.  boto3: 685,007,866 downloads
    2.  botocore: 531,034,851 downloads
    3.  urllib3: 512,611,825 downloads
    4.  requests: 464,595,806 downloads
    5.  typing-extensions: 459,505,780 downloads
    6.  certifi: 451,929,759 downloads
    7.  charset-normalizer: 428,716,731 downloads
    8.  idna: 409,262,986 downloads
    9.  grpcio-status: 402,535,938 downloads
    10. aiobotocore: 399,650,559 downloads

Run deeper insights using the ask_data_insights tool

The ask_data_insights tool triggers an agent on the BigQuery Conversational Analytics API to answer your questions. The server-side agent gathers additional context about your data and offers more insights.

  • Example prompt to get insights on channels to focus on:

    Using ask_data_insights and using the tables under
bigquery-public-data.google_analytics_sample, tell me the channels I should
focus on and why

    Example response

    The Gemini CLI returns recommendations based on the analysis:

    Based on the analysis of the Google Analytics sample data, here are the
channels you should focus on and why:
  ┌─────────────────┬──────────────┬─────────────────┬────────────────────┬───────────────┐
  │ Channel         │ Total Visits │ Total Pageviews │ Total Transactions │ Total Revenue │
  ├─────────────────┼──────────────┼─────────────────┼────────────────────┼───────────────┤
  │ Referral        │ 104,838      │ 707,597         │ 5,543              │ $651,429.91   │
  │ Direct          │ 143,026      │ 584,840         │ 2,219              │ $434,840.55   │
  │ Organic Search  │ 381,561      │ 1,569,218       │ 3,581              │ $326,380.51   │
  │ Display         │ 6,262        │ 34,202          │ 152                │ $78,337.46    │
  │ Paid Search     │ 25,326       │ 136,060         │ 479                │ $43,558.90    │
  │ Social          │ 226,117      │ 398,690         │ 131                │ $4,916.54     │
  │ Affiliates      │ 16,403       │ 47,417          │ 9                  │ $597.38       │
  │ (Other)         │ 120          │ 442             │ 1                  │ $9.99         │
  └─────────────────┴──────────────┴─────────────────┴────────────────────┴───────────────┘

  Recommendations:

    1.  Prioritize referral traffic:
        *   Why: This is a highly valuable channel. It brings in the most revenue and has a high number of transactions, despite not having the highest number of visits. This indicates that traffic from referrals is highly qualified and
          converts well.
        *   Action: Identify your top referring websites and build stronger relationships with them. Look for similar websites to partner with.

    2.  Nurture your direct traffic:
        *   Why: Direct traffic is your second-highest revenue source. These are likely returning customers or people who know your brand well.

Pricing

There is no additional cost for using metadata tools (such as get_dataset_info, get_table_info, list_dataset_ids, and list_table_ids) or the search_catalog tool.

Tools that run queries, including execute_sql, bigquery_forecast, and analyze_contribution, are billed according to BigQuery on-demand pricing or BigQuery capacity pricing, depending on your project configuration.

During beta (pre-v1.0), the ask_data_insights tool is offered at no additional cost; however, any BigQuery queries that this tool triggers are billed according to standard BigQuery pricing.

What's next