Prepare data with Gemini

This document describes how to generate and manage SQL code suggestions for yourdata preparations in BigQuery.

For more information, seeIntroduction to BigQuery data preparation.

Before you begin

• Set up Gemini in BigQuery.

• Give therequired Identity and Access Management (IAM) roles and permissions.

Open the data preparation editor in BigQuery

You can open the data preparation editor in BigQuery by creating a newdata preparation, creating one from an existing table or Cloud Storage file,or opening an existing data preparation. For more information about what happenswhen you create a data preparation, seeData preparation entry points.

On theBigQuery page, you can go to the data preparation editor inthe following ways:

Add data preparation steps

You prepare data in steps. You can preview or apply steps suggested byGemini. You can also improve the suggestions, or apply your ownsteps.

Apply and improve suggestions by Gemini

When you open the data preparation editor for your table, Geminiinspects the data and schema from the table you loaded and generates filter andtransformation suggestions. The suggestions appear on cards in theStepslist.

The following image shows where you can apply and improve steps suggested byGemini:

To apply a suggestion by Gemini as a data preparation step, dothe following:

1. In the data view, click a column name or a particular cell.Gemini generates suggestions for filtering and transformingthe data.

2. Optional: To improve the suggestions, edit the values of one to three cellsin the table to demonstrate what the values in a column should look like.For example, enter a date the way you want to format all dates.Gemini generates new suggestions based on your changes.

   Note: Your example change to the data isn't saved.

   The following image shows how you can edit values to improve the stepssuggested by Gemini:

   

3. Select a suggestion card.

   1. Optional: To preview the result of the suggestion card, clickPreview.
   2. Optional: To modify the suggestion card using natural language, clickEdit.

4. ClickApply.

Add steps with natural language or SQL expressions

If existing suggestions don't meet your needs, add a step. Choose columns or astep type, then describe what you want using natural language.

Add a transformation

1. In the data or schema view, choose theTransform option. You can alsochoose columns or add examples to help Gemini understand yourdata transformation.
2. In theDescription field, enter a prompt, such asConvert the statecolumn to uppercase.

3. ClicksendSend.

   Gemini generates a SQL expression and a new description basedon your prompt.

4. In theTarget column list, select or enter a column name.

5. Optional: To update the SQL expression, revise the prompt and clicksendSend, or manually enter a SQL expression.

6. Optional: ClickPreview and review the step.

7. ClickApply.

Flatten JSON columns

To make key-value pairs easier to access and analyze, flatten JSON columns. Forexample, if you have a JSON column nameduser_properties that contains thekeyscountry anddevice_type, flattening this column extractscountry anddevice_type into their own top-level columns so you can use them directly inyour analysis.

Gemini for BigQuery suggests operations thatextract fields only from the top level of the JSON. If these extracted fieldscontain more JSON objects, you can flatten them in additional steps to accesstheir contents.

1. In the data view for a JSON source table, choose a column or cells.
2. ClickFlatten to generate suggestions.
3. Optional: To update the SQL expression, you can manually enter a SQLexpression.
4. Optional: ClickPreview and review the step.
5. ClickApply.

Flattening has the following behaviors:

• TheFlatten option appears in the data view after you select cells orcolumns containing JSON. It doesn't appear by default when you clickAddstep.
• If a JSON key isn't present in the selected rows, the generated suggestiondoesn't contain that key. This issue might cause some columns to be left outwhen data is flattened.
• If column names collide during flattening, the repeated column names end inthis format:_<i>. For example, if there's already a column namedaddress, the new flattened column name isaddress_1.
• Flattened column names follow the BigQuerycolumn naming conventions.
• If you leave the JSON key field empty, the default column name format isf<i>_.

FlattenRECORD orSTRUCT columns

To make nested fields easier to access and analyze, flatten columns with theRECORD orSTRUCT data type. For example, if you have anevent_log recordthat contains the fieldstimestamp andaction, flattening this recordextractstimestamp andaction into their own top-level columns so you cantransform them directly.

This process extracts all nested columns from the record, up to 10 levels deep,and creates a new column for each. The new column names are created by combiningthe parent column's name with the nested field name, separated by an underscore(for example,PARENT-COLUMN-NAME_FIELD-NAME). The originalcolumn is dropped. To keep the original column, you candelete theDrop column step from theAppliedsteps list.

To flatten records, follow these steps:

1. In the data view for a source table, choose a record column.
2. ClickFlatten to generate suggestions.
3. Optional: To update the SQL expression, you can manually enter a SQLexpression.
4. Optional: ClickPreview and review the step.
5. ClickApply.

When you unnest an array, a new column is created that contains the unnestedelements. By default, the original array column is dropped. To keep the originalcolumn,delete theDrop column step from theApplied steps list.

To unnest arrays, follow these steps:

1. In the data view for a source table, choose anARRAY column.
2. ClickUnnest to generate suggestions.
3. Optional: To update the SQL expression, you can manually enter a SQLexpression.
4. Optional: ClickPreview and review the step.
5. ClickApply.

Expressions must follow the BigQuery SQL syntax for theWHERE clause.

Deduplicate data

To remove duplicate rows from your data, follow these steps:

1. In the data or schema view, choose theDeduplicate option.Gemini provides an initial deduplication suggestion.
2. Optional: To refine the suggestion, enter a new description and clicksendSend.
3. Optional: To manually configure the deduplication step, use the followingoptions:
   • In theRecord choosing list, select one of the following strategies:
     • First: For each group of rows with the same deduplication keyvalues, this strategy chooses the first row based on theORDER BYexpression and removes the rest.
     • Last: For each group of rows with the same deduplication keyvalues, this strategy chooses the last row based on theORDER BYexpression and removes the rest.
     • Any: For each group of rows with the same deduplication keyvalues, this strategy chooses any row from that group and removesthe rest.
     • Distinct: Removes all duplicate rows across all columns in thetable.
   • In theDeduplication keys field, choose one or more columns orexpressions to identify duplicate rows. This field is applicablewhen the record choosing strategy isFirst,Last, orAny.
   • In theOrder by expression field, enter an expression that definesthe row order. For example, to choose the most recent row, enterdatetime DESC. To choose the first row alphabetically by name, enter acolumn name likelast_name. The expression follows the same rules asthe standardORDER BY clause in BigQuery. This fieldis only applicable when the record choosing strategy isFirst orLast.
4. Optional: ClickPreview and review the step.
5. ClickApply.

Delete a column

To delete one or more columns from a data preparation, follow these steps:

1. In the data or schema view, select the columns you want to drop.
2. ClickDrop. A new applied step is added for the deleted columns.

Add a join operation with Gemini

To add a join operation step between two sources in your data preparation,follow these steps:

1. In the data view for a node in your data preparation, go to theSuggestions list, and click theJoin option.
2. In theAdd join dialog, clickBrowse, and then select the othertable involved in the join operation (referred to as the right side of thejoin).
3. Optional: Select the type ofjoin operation that you want to perform, such asInner join.

4. Review the Gemini-generated join key information in thefollowing fields:

   • Join description: The natural language description of the SQLexpression for the join operation. When you edit this description andclicksendSend, Gemini suggests new SQL join conditions.

   • Join conditions: The SQL expressions within theON clause for thejoin operation. You can use theL andR qualifiers to refer to theleft and right source tables, respectively. For example, to join thecustomer_id column from the left table to thecustomer_id column fromthe right table, enterL.customerId = R.customerId. These qualifiersaren't case-sensitive.

     Note: If theJoin conditions field is empty, the join operation typeis automatically set toCross join, even if you selected a differentjoin type in the previous step.

5. Optional: To refine the suggestions from Gemini, edit theJoin description field, and then clicksendSend.

6. Optional: To preview the join operation settings of your data preparation,clickPreview.

7. ClickApply.

   The join operation step is created. The source table that you selected (theright side of the join) and the join operation are reflected in the list ofapplied steps and in the nodes in the graph view of your data preparation.

Aggregate data

1. In the data or schema view, choose theAggregate option.
2. In theDescription field, enter a prompt, such asFind the totalrevenue for a region.

3. ClickSend.

   Gemini generates grouping keys and aggregation expressionsbased on your prompt.

4. Optional: Edit the generated grouping keys or aggregation expressions,if needed.

5. Optional: You can manually add grouping keys and aggregation expressions.

   • In theGrouping keys field, enter a column name or expression. If youleave it blank, the resulting table has one row. If you enter anexpression, it must have an alias (anAS clause)—for exampleEXTRACT(YEAR FROM order_date) AS order_year. No duplicates areallowed.
   • In theAggregation expressions field, enter an aggregationexpression that has an alias (anAS clause)—for exampleSUM(quantity) AS total_quantity. You can enter multiple,comma-separated expressions. No duplicates are allowed. For a list ofthe supported aggregation expressions, seeAggregate functions.

6. Optional: ClickPreview and review the step.

7. ClickApply.

Configure the error table and add a validation rule

You can add a filter that creates a validation rule, which sends errors to anerror table or fails the data preparation run.

Configure the error table

To configure your error table, follow these steps:

1. In the data preparation editor, go to the toolbar and clickMore> Error table.
2. ClickEnable error table.
3. Define the table location.
4. Optional: Define a maximum duration for keeping errors.
5. ClickSave.

Add a validation rule

To add a validation rule, follow these steps:

1. In the data or schema view, click theFilter option. You can also choosecolumns to help Gemini understand your data filter.
2. Enter a description for the step.
3. Enter a SQL expression, in the form of aWHERE clause.
4. Optional: If you want the SQL expression to act as a validation rule, selecttheFailed validation rows go to error table checkbox. You can alsochange a filter to a validation in the data preparation toolbar by clickingMore> Error table.
5. Optional: ClickPreview and review the step.
6. ClickApply.

Add or change a destination table

A destination table is required to run or schedule your data preparation. Toadd or change a destination table for the output of your data preparation,follow these steps:

1. In the data or schema view, clickDestination in theSuggestionslist.
2. Select the project where the destination table is stored.
3. Select one of the datasets, or load a new dataset.
4. Enter a destination table. If the table doesn't exist, the data preparationcreates a new table on the first run. For more information, seeWrite mode.
5. Select your dataset as the destination dataset.
6. ClickSave.

View the data sample and schema for an applied step

To view sample and schema details at a particular step in the data preparation,do the following:

1. In the data preparation editor, go to theSteps list and clickAppliedsteps.
2. Select a step. TheData andSchema tabs appear, displaying the datasample and schema as of this particular step.

Edit an applied step

To edit an applied step, do the following:

1. In the data preparation editor, go to theSteps list and clickAppliedsteps.
2. Select a step.
3. Next to the step, clickmore_vertMenu> Edit.
4. In theEdit Applied Step dialog, you can do the following:
   • Edit the description of the step.
   • Get suggestions from Gemini by editing the descriptionand clickingsendSend.
   • Edit the SQL expression.
5. In theTarget column field, select a column.
6. Optional: ClickPreview and review the step.
7. ClickApply.

Delete an applied step

To delete an applied step, do the following:

1. In the data preparation editor, go to theSteps list and clickAppliedsteps.
2. Select a step.
3. Clickmore_vertMenu> Delete.

Run the data preparation

After you've added your data preparation steps,configured the destination,and fixed any validation errors, you can perform test runs on a sample of thedata, or deploy the steps and schedule data preparation runs. For moreinformation, seeSchedule data preparations.

Refresh data preparation samples

Data in the sample isn't automatically refreshed. If data in the source tablesfor the data preparation has changed, but the changes aren't reflected in thedata sample of the preparation, clickMore> Refresh sample.

What's next

• Learn how toschedule data preparations.
• Learn aboutmanaging data preparations.
• Learn aboutquotas and limits for Gemini in BigQuery.
• ReviewGemini in BigQuery pricing.