Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://developers.google.com/bigquery/docs/write-sql-gemini below:

Write queries with Gemini assistance | BigQuery

Stay organized with collections Save and categorize content based on your preferences.

This document describes how to use AI-powered assistance in Gemini in BigQuery to help you query your data with SQL queries and Python code. Gemini in BigQuery can generate and explain queries and code, complete queries and code while you type, and fix code errors.

To follow step-by-step guidance for this task directly in the Google Cloud console, click Guide me:

Guide me

Gemini for Google Cloud doesn't use your prompts or its responses as data to train its models without your express permission. For more information about how Google uses your data, see How Gemini for Google Cloud uses your data.

Only English language prompts are supported for Gemini in BigQuery.

This document is intended for data analysts, data scientists, and data developers who work with SQL queries and Colab Enterprise notebooks in BigQuery. It assumes that you know how to query data in the BigQuery Studio environment or how to work with Python notebooks to analyze BigQuery data.

1. Ensure that Gemini in BigQuery is set up for your Google Cloud project. This step is normally done by an administrator. Gemini in BigQuery features might be turned off or unavailable until you complete the remaining steps in this section.
2. To use Gemini Cloud Assist, to write code in the Gemini Cloud Assist pane, you must also follow the steps in Set up Gemini Cloud Assist.

3. To use Gemini to explain and fix Python code in your Colab Enterprise notebooks in BigQuery, you must also follow the steps in Set up Gemini in Colab Enterprise for a project.

4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

5. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

6. In the BigQuery toolbar, click pen_sparkarrow_drop_downGemini.

7. In the list of features, ensure the following features are selected:

   • Gemini in SQL query list:

     • Auto-completion (Preview). As you type in the query editor, Gemini can suggest logical next steps that are relevant to your current query's context, or it can help you iterate on a query.
     • Auto-generation. You can prompt Gemini in BigQuery using a natural language comment in the BigQuery query editor to generate a SQL query.
     • SQL generation tool. You can enter natural language text in a tool to generate a SQL query, with options to refine query results, choose table sources, and compare results.
     • Explanation. You can prompt Gemini in BigQuery to explain a SQL query using natural language.

   • Gemini in Python notebook list:

     • Code completion (Preview). Gemini provides contextually appropriate recommendations that are based on content in the notebook.
     • Code generation. You can prompt Gemini using a natural language statement or question to generate Python code.

8. To complete the tasks in this document, ensure that you have the required Identity and Access Management (IAM) permissions.

To get the permissions that you need to write queries with Gemini assistance, ask your administrator to grant you the Gemini for Google Cloud User (roles/cloudaicompanion.user) IAM role on the project. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains the permissions required to write queries with Gemini assistance. To see the exact permissions that are required, expand the Required permissions section:

The following permissions are required to write queries with Gemini assistance:

• cloudaicompanion.entitlements.get
• cloudaicompanion.instances.completeTask
• Explain SQL queries: cloudaicompanion.companions.generateChat
• Complete SQL or Python code: cloudaicompanion.instances.completeCode
• Generate SQL or Python code: cloudaicompanion.instances.generateCode

You might also be able to get these permissions with custom roles or other predefined roles.

For more information about IAM roles and permissions in BigQuery, see Introduction to IAM.

As an early-stage technology, Gemini for Google Cloud products can generate output that seems plausible but is factually incorrect. We recommend that you validate all output from Gemini for Google Cloud products before you use it. For more information, see Gemini for Google Cloud and responsible AI.

To generate a SQL query based on your data's schema, you can provide Gemini in BigQuery with a natural language statement or question, also known as a prompt. Even if you're starting with no code, a limited knowledge of the data schema, or only a basic knowledge of GoogleSQL syntax, Gemini in BigQuery can generate SQL that can help you explore your data.

The SQL generation tool lets you use natural language to generate a SQL query about your recently viewed or queried tables. You can also use the tool to modify an existing query, and to manually specify the tables for which you want to generate SQL.

To use the SQL generation tool, follow these steps:

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. Next to the query editor, click pen_spark SQL generation tool.

3. In the Generate SQL with Gemini dialog, enter a natural language prompt about a table that you recently viewed or queried. For example, if you recently viewed bigquery-public-data.austin_bikeshare.bikeshare_trips table, you might enter the following:

    Show me the duration and subscriber type for the ten longest trips.
   

4. Click Generate.

   The generated SQL query is similar to the following:

   SELECT
       subscriber_type,
       duration_sec
     FROM
         `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`
   ORDER BY
       duration_sec DESC
   LIMIT 10;
   

   Note: Gemini in BigQuery might suggest different syntax each time that you enter the same prompt.

5. Review the generated SQL query and take any of the following actions:

   • To accept the generated SQL query, click Insert to insert the statement into the query editor. You can then click Run to execute the suggested SQL query.
   • To edit your prompt, click Edit and then modify or replace your initial prompt. After you've edited your prompt, click Update to generate a new query.
   • To update the table sources that were used as context to generate the suggested SQL query, click Edit Table Sources, select the appropriate checkboxes, and then click Apply.
   • To view a natural language summary of the generated query, click Query Summary.
   • To refine the suggested SQL query, enter any refinements in the Refine field, and then click send Refine. For example, enter limit to 1000 to limit the number of query results. To compare the changes to your query, select the Show diff checkbox.
   • To dismiss a suggested query, close the SQL generation tool.

To learn how to turn off the SQL generation tool, see Turn off Gemini query assistant features.

You can generate SQL in the query editor by describing the query that you want in a comment.

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the query editor, click add_box SQL query .

3. In the query editor, write a SQL comment about a table you have recently viewed or queried. For example, if you recently viewed the bigquery-public-data.austin_bikeshare.bikeshare_trips table, then you might write the following comment:

   # Show me the duration and subscriber type for the ten longest trips.
   

4. Press Enter (Return on macOS).

   The suggested SQL query is similar to the following:

   # Show me the duration and subscriber type for the ten longest trips
   
   SELECT
     duration_sec,
     subscriber_type
     AVG(duration_minutes) AS average_trip_length
   FROM
     `bigquery-public-data.austin_bikeshare.bikeshare_trips`
   ORDER BY
     duration_sec
   LIMIT 10;
   

5. To accept the suggestion, press Tab.

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

You can generate a SQL query in BigQuery by using the Cloud Assist panel in the Google Cloud console.

Before you can use Gemini Cloud Assist chat to generate SQL, you must enable Gemini Cloud Assist. For more information, see Set up Gemini Cloud Assist.

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the query editor, click add_box SQL query to open a new SQL query.

3. In the Google Cloud toolbar, click spark Open or close Gemini AI chat to open Gemini Cloud Assist chat.

4. In the Enter a prompt field, enter a prompt to generate a SQL query. For example:

   Generate a SQL query to show me the duration and subscriber type for the ten longest trips.
   

5. Click Send prompt. The response includes a SQL query similar to the following:

   SELECT
        subscriber_type,
        duration_sec
    FROM
        `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`
    ORDER BY
        duration_sec DESC
    LIMIT 10;
    ```
   

6. Review the generated SQL query.

7. To run the generated SQL query, click content_copy Copy to clipboard, paste the generated code in the query editor, and then click play_circle Run.

The following tips can improve suggestions that Gemini in BigQuery provides:

• To manually specify which tables to use, you can include the fully qualified table name in backticks (`), such as `PROJECT.DATASET.TABLE`.
• If the column names or their semantic relationships are unclear or complex, then you can provide context in the prompt to guide Gemini towards the answer that you want. For example, to encourage a generated query to reference a column name, describe the column name and its relevance to the answer that you want. To encourage an answer that references complex terms like lifetime value or gross margin, describe the concept and its relevance to your data to improve SQL generation results.
• When you generate SQL from a comment, you can format your prompt over multiple lines by prefixing each line with the # character.
• Column descriptions are considered when you generate SQL queries. To improve accuracy, add column descriptions to your schema. For more information about column descriptions, see Column descriptions in "Specify a schema."

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

SQL completion attempts to provide contextually appropriate recommendations that are based on content in the query editor. As you type, Gemini can suggest logical next steps that are relevant to your current query's context, or it can help you iterate on a query.

To try SQL completion with Gemini in BigQuery, follow these steps:

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the query editor, copy the following:

   SELECT
     subscriber_type
     , EXTRACT(HOUR FROM start_time) AS hour_of_day
     , AVG(duration_minutes) AS avg_trip_length
   FROM
     `bigquery-public-data.austin_bikeshare.bikeshare_trips`
   

   An error message states that subscriber_type isn't grouped or aggregated. It's not uncommon to need some help getting a query just right.

3. At the end of the line for subscriber_type, press Space.

   The suggested refinements to the query might end in text that's similar to the following:

   GROUP BY
     subscriber_type, hour_of_day;
   

   You can also press Enter (Return on macOS) to generate suggestions.

4. To accept the suggestion, press Tab, or hold the pointer over the suggested text and click through alternate suggestions. To dismiss a suggestion, press ESC or continue typing.

You can prompt Gemini in BigQuery to explain a SQL query in natural language. This explanation can help you understand a query whose syntax, underlying schema, and business context might be difficult to assess due to the length or complexity of the query.

To get an explanation for a SQL query, follow these steps:

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the query editor, open or paste a query that you want explained.

3. Highlight the query that you want Gemini in BigQuery to explain.

4. Click astrophotography_mode Gemini, and then click Explain this query.

   The SQL explanation appears in the Cloud panel.

You can ask Gemini in BigQuery to generate Python code with a natural language statement or question. Gemini in BigQuery responds with one or more Python code suggestions, pulling in relevant table names directly from your BigQuery project, resulting in personalized, executable Python code.

In the following example, you generate code for a BigQuery public dataset, bigquery-public-data.ml_datasets.penguins.

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the tab bar of the query editor, click the arrow_drop_down drop-down arrow next to add_box SQL query, and then click Notebook.

   The new notebook opens, containing cells that show example queries against the bigquery-public-data.ml_datasets.penguins public dataset.

3. To insert a new code cell, in the toolbar, click add Code. The new code cell contains the message Start coding or generate with AI.

4. In the new code cell, click generate.

5. In the Generate editor, enter the following natural language prompt:

   Using bigquery magics, query the `bigquery-public-data.ml_datasets.penguins` table
   

6. Press Enter (Return on macOS).

   The suggested Python code is similar to the following:

   %%bigquery
   SELECT *
   FROM `bigquery-public-data.ml_datasets.penguins`
   LIMIT 10
   

7. To run the code, press play_circle Run cell.

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

You can use Gemini Cloud Assist in the Google Cloud console to generate Python code in BigQuery. Before you can use Gemini Cloud Assist to generate code, you must enable Gemini Cloud Assist. For more information, see Set up Gemini Cloud Assist.

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the tab bar of the query editor, click the arrow_drop_down drop-down arrow next to add_box SQL query, and then click Notebook.

3. In the Google Cloud toolbar, click spark Open or close Gemini AI chat to open Gemini Cloud Assist chat.

4. In the Enter a prompt field, enter a prompt to generate Python code. For example:

   Generate python code to query the `bigquery-public-data.ml_datasets.penguins`
   table using bigquery magics
   
   

5. Click send Send prompt. Gemini returns Python code similar to the following:

   %%bigquery
   SELECT *
   FROM `bigquery-public-data.ml_datasets.penguins`
   LIMIT 10
   

6. Review the generated Python code.

7. To run the Python code, click content_copy Copy to clipboard and then paste the generated code in the query editor, and then click play_circle Run.

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

You can generate BigQuery DataFrames code with Gemini in BigQuery. To ask Gemini to use BigQuery DataFrames in the generated code, express your intent in your prompt. For example, you can start your prompt with "using bigframes" or "utilizing BigQuery DataFrames".

BigQuery DataFrames provides two libraries:

• bigframes.pandas, which provides a pandas-compatible API for analytics.
• bigframes.ml, which provides a scikit-learn-like API for machine learning (ML).

Gemini code generation is optimized for the bigframes.pandas library.

To learn more about BigQuery DataFrames and the permissions that are required to use BigQuery DataFrames, see BigQuery DataFrames permissions. BigQuery DataFrames is an open-source package. You can run pip install --upgrade bigframes to install the latest version.

In the following example, you generate code for a BigQuery public dataset, bigquery-public-data.ml_datasets.penguins.

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the tab bar of the query editor, click the arrow_drop_down drop-down arrow next to add_box SQL query, and then click Notebook.

   A new notebook opens.

3. To insert a new code cell, in the toolbar, click add Code.

4. The new code cell contains the message Start coding or generate with AI. In the new code cell, click generate.

5. In the Generate editor, enter the following natural language prompt:

   Read the penguins table from the BigQuery public data using bigframes
   

6. Press Enter (Return on macOS).

   The suggested Python code is similar to the following:

   import bigframes.pandas as bpd
   
   # Read the penguins table from the BigQuery public data using bigframes
   result = bpd.read_gbd("bigquery-public-data.ml_datasets.penguins")
   

7. To run the code, press play_circle Run cell.

8. To preview the results, in the toolbar, click add Code to insert a new code cell.

9. In the new cell, call the peek() method—for example, result.peek()—and press play_circle Run cell. A number of rows of data are displayed.

Python code completion attempts to provide contextually appropriate recommendations that are based on content in the query editor. As you type, Gemini in BigQuery can suggest logical next steps that are relevant to your current code's context, or it can help you iterate on your code.

To try Python code completion with Gemini in BigQuery, follow these steps:

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the tab bar of the query editor, click the arrow_drop_down drop-down arrow next to add_box SQL query, and then click Notebook.

   A new notebook opens, containing cells that show example queries against the bigquery-public-data.ml_datasets.penguins public dataset.

3. In the editor, begin typing Python code. For example %%bigquery. Gemini in BigQuery suggests code inline while you type.

4. To accept the suggestion, press Tab.

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

You can use Gemini in BigQuery to explain Python code in your Colab Enterprise notebooks.

After getting an explanation, you can ask more questions in the prompt dialog to understand the code better.

To get an explanation for Python code in your notebook, follow these steps:

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the Explorer pane, expand your project and the Notebooks folder.

3. Click the notebook that you want to open.

4. Highlight the Python cell that you want to understand.

5. Click spark Gemini, and then click Explain code.

   The code explanation appears in a panel next to the cell.

6. Optional: To understand your code better, ask questions in the Enter prompt here field.

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

You can use Gemini in BigQuery to fix and explain Python code errors in your Colab Enterprise notebooks.

To fix or understand the code errors with Gemini assistance, follow these steps:

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the Explorer pane, expand your project and the Notebooks folder.

3. Click the notebook that you want to open.

4. In a code cell of your notebook, enter code that contains an error, and then run the cell. For example, you might enter print(1, which is missing a closing parenthesis.

   After your code cell runs, the notebook prints an error message below your code cell. If you have Gemini in Python notebooks turned on and Gemini has a suggestion to fix or explain the error, one of the following options appears:

   • For Python syntax errors, a Fix error option appears.
   • For all other types of errors, an Explain error option appears.

5. To fix a syntax error, do the following:

   1. Click Fix error.

      Gemini suggests how to fix the error.

   2. Evaluate the suggestion, and then do one of the following:

      • To accept the suggestion, click check Accept suggestion.
      • To reject the suggestion, click close Reject suggestion.

6. To fix all other types of errors, do the following:

   1. Click Explain error.

      A panel opens, explaining the error and suggesting changes.

   2. Optional: To understand the error better, ask questions in the Enter prompt here field.

   3. To accept a suggested change, click library_add Add code cell.

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

You can ask Gemini Code Assist to generate PySpark code in your notebook. Gemini Code Assist fetches and uses relevant BigQuery and Dataproc Metastore tables and their schemas to generate a code response. With its schema knowledge, Gemini Code Assist avoids hallucinations, and suggests join keys and column types.

To generate Gemini Code Assist code in your notebook, do the following:

1. Insert a new code cell by clicking + Code in the toolbar. The new code cell displays Start coding or generate with AI. Click generate.

2. In the Generate editor, enter a natural language prompt, and then click enter. Make sure to include the keyword spark or pyspark in your prompt..

   Sample prompt:

   create a spark dataframe from order_items and filter to orders created in 2024
   

   Sample output:

   spark.read.format("bigquery").option("table", "sqlgen-testing.pysparkeval_ecommerce.order_items").load().filter("year(created_at) = 2024").createOrReplaceTempView("order_items")
   df = spark.sql("SELECT * FROM order_items")
   

• To let Gemini Code Assist fetch relevant tables and schemas, turn on Data Catalog sync for Dataproc Metastore instances.

• Make sure your user account has access to Data Catalog the query tables. To do this, assign the DataCatalog.Viewer role.

To turn off specific features in Gemini in BigQuery, do the following:

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the BigQuery toolbar, click pen_spark arrow_drop_down Gemini .

3. In the list, clear the query assistant features that you want to turn off.

To learn how to turn off Gemini in BigQuery, see Turn off Gemini in BigQuery.

To turn off Gemini in Colab Enterprise for a Google Cloud project, an administrator must turn off the Gemini for Google Cloud API. See Disabling services.

To turn off Gemini in Colab Enterprise for a specific user, an administrator needs to revoke the Gemini for Google Cloud User (roles/cloudaicompanion.user) role for that user. See Revoke a single IAM role.

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the BigQuery toolbar, click pen_spark arrow_drop_down Gemini .

3. Click Send feedback.

You can help improve Gemini suggestions by sharing with Google the prompt data that you submit to features in Preview.

To share your prompt data, follow these steps:

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the BigQuery toolbar, click pen_spark arrow_drop_down Gemini .

3. Select Share data to improve Gemini in BigQuery.

4. In the Data Use Settings dialog, update your data use settings.

Data sharing settings apply to the entire project and can only be set by a project administrator with the serviceusage.services.enable and serviceusage.services.list IAM permissions. For more information about data use in the Trusted Tester Program, see Gemini for Google Cloud Trusted Tester Program.

In order to provide accurate results, Gemini in BigQuery requires access to both your Customer Data and metadata in BigQuery for enhanced features. For more information, see How Gemini in BigQuery uses your data.

For details about pricing for this feature, see Gemini in BigQuery pricing overview.

For information about quotas and limits for this feature, see Quotas for Gemini in BigQuery.

• Read Gemini for Google Cloud overview.
• Learn how Gemini for Google Cloud uses your data.
• Learn how to explore your data by generating data insights.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4