Oracle Cloud Infrastructure Documentation
Try Free Tier

Prepare Data Using Table AI Assist Tool

Data Augmentation helps you to add data to your source tables from your target tables. The Table AI Assist allows you to add, rename, and remove columns in a table using natural language prompts.

The Table AI Assist tool uses an AI service to generate SQL. You can choose to use the generated SQL to alter the table, create a view, or create a SELECT statement. It enables you to try suggested prompts generated by Artificial Intelligence (AI) and receive a query that is run where you can preview the data.

What is a Recipe?

A recipe is a set of steps. Each step lets you add, replace, remove and rename columns without making any changes to your base table. You add as many steps as you want to a recipe. You can save a recipe to your schema or export it as JSON to a file. You can return back to your saved recipe and keep working on it.

Different modes of Recipe
The following are different modes of a recipe:

  • Create or Replace View

  • Create a new table

  • Alter table

Once you are satisfied with a recipe you can use it to Create or replace a View, Create a new Table or Alter the Table.

You can choose how to use the recipe at any time. The choices are different modes of recipe.

Let's say you have a table with four columns, and then add two new columns that are derived from the original data. The Table AI Assist tool can then:
  • Alter the table to add the two new columns.
  • Create either a new table, or a view on the table, with any of the four original columns and the two new columns.

Consider a table with EMP details. The table contains columns such as HIREDATE, ENAME, JOB, MGR, SAL, COMM and DEPTNO.

Using Table AI Assist, you can use natural prompts such as:

add month in format year-month from order_date

add year from order_date

The Table AI Assist tool generates SQL expressions for each prompt and gathers all changes into a recipe.

The following article demonstrates augmenting data using the Table AI Assist menu.
  1. Select the table to be modified.

    1. From Launchpad, select Table AI Assist menu from the Data Studio suite of tools.

    2. On the Table AI Assist page, you can either search for the existing table from the search field at the top of the page or view it on the list of tables. In this example, let us select the EMP table.

  2. Create a new Recipe.
    1. Click + New Recipe to create a new recipe.


      Description of emp-table.png follows
      Description of the illustration emp-table.png

      You can build a Recipe using a set of steps to Add, Update, Remove and Rename Columns without modifying your source Table.

    2. Select the Target Type and Target Name for viewing the changes you make to the source data.
      On the right panel of the Table AI Assist page, specify the following field values:
      • Source Table Name: This is the name of the source table. In this example, it is EMP.
      • Target Type: You can select one of the following options:
        • Create View: This option creates a view using steps in the recipe and updates the column values using column expressions in the view. Views contain only selected columns and expressions. Views do not store data.
        • Create Table: This option creates a new table using steps in the recipe. Data is stored in all columns, including columns added by using the recipe. It stores the updated column values.
        • Alter Table: This option adds, removes, and renames columns in the source table and stores the updated column values using steps in the recipe. The new columns are added as virtual columns using expressions (they do not store data).
        • Query: This option creates a query using steps in the recipe.

        In this example, you will Create Table.

      • Target Name: Enter the name of the newly created table or view. It is a table or a view depends on the selection in Target Type drop-down field.

        In this example, you will view EMP_1.


      Description of emp-table.png follows
      Description of the illustration emp-table.png

  3. Add Steps to build the recipe that is comprised of various column actions.
    1. Click Add Step to update the source tabular data.


      Description of edit-recipe.png follows
      Description of the illustration edit-recipe.png

    2. Specify the following fields in the Add Step dialog box:
      On the Column Action, you can select one of the following available actions:
      • Add or Replace: You can add or replace the data in the source column based on the SQL code the tool generates in the prompt field. See Add a Column or Replace a Column. For example, if you replace SALES_DATE with YEAR, (assume SALES_DATE as a date data type) with prompt such, you will get a SALE_DATE column with years.
      • Remove: You can remove a column from the source data. See Remove a Column.
      • Rename: You can rename a source column. See Rename a Column.

      The fields generated when you add steps varies on the column action you select.


      Description of add-step.png follows
      Description of the illustration add-step.png

  4. Save Recipe.
    1. Click Save Recipe on the left panel of the Table AI assist page to save this recipe.


      Description of recipe.png follows
      Description of the illustration recipe.png

    2. Click the default Recipe name besides the Save Recipe to rename the Recipe. On clicking the Recipe name, you will view a Rename Recipe dialog.
      Specify the following field values on the Rename Recipe dialog:
      • New recipe name: Enter the recipe's name that you created.
      • Description: Enter any description. This field is optional.
  5. View the results
    1. You can view the newly added recipe in the main recipe panel on the Table AI Assist page.
    2. You can progressively keep adding steps, modifying the source data, storing it on the target table or target view and view the updated data in the Preview Data tab.


      Description of view-results-recipe.png follows
      Description of the illustration view-results-recipe.png

      If the SQL or the Preview is not correct specify a different prompt you an also just type in the SQL code.

  6. Create a new target table, a new target view or Alter the target table with modified column source data.
    1. After you have modified the source data, you can create a new table, create a new view or Alter table based on the option you select in the Target Type drop-down.
    2. Select Create Table, Create View or Alter Table on the side panel of the Table AI Assist page based on the Target Type you select.

      The tool creates the target data upon confirmation.

    3. You will receive a successful notification message that says the creation of the target type is successful.

    After you have created the recipe, you can view it under the Table AI Assist SQL Recipes on the Table AI Assist page.

Edit Recipe

You can edit an existing recipe from the Table AI Assist tool.
  1. On the Table AI Assist page, click on the Edit Recipe (pencil) icon besides the recipe name. This opens the Edit Recipe page.
  2. You can add steps or delete the existing steps to edit the existing recipe.
  3. Click Save Recipe to save the updated recipe.

Export Recipe

You can export the specific recipe.
  1. On the Edit Recipe page.
  2. Click the Export Recipe icon besides the Recipe name.
  3. Select the Export icon on the to generate the JSON file for download.

Load or Import Recipe

You can load or import recipe.
  1. Click Load Recipe on the Edit Recipe page. Selecting Load Recipe opens a Load Recipe Dialog which displays the following options:
    • Load
      • Select Load to load an existing recipe.
      • Specify the following field values:
        • Table: Select the table from the drop-down field you want to load the recipe from.
        • Recipe: Select the recipe you want to load from the drop-down.
        • Under Steps, you can view the Steps involved in creating the recipe.
    • Import
      • Select Import to import a recipe file you download. This file is in JSON format.
      • Click OK to proceed and save the changes. Click Cancel to cancel the ongoing process of loading or importing.
  • Add a Column
    You can view the following fields when you select Add or Replace from Column Action.
  • Replace a Column
    You can view the following fields when you select Add or Replace from Column Action.
  • Remove a Column
    You can view the following fields when you select Remove from the Column Action field.
  • Rename a Column
    You can view the following fields when you select Rename from the Column Action field.
  • View Statistical Details about the Table you Load after you Create a Recipe
    After you have created a recipe using the Table AI Assist tool, you can view additional information such as statistics about the table you create. You can generate statistics that measure the data distribution and storage characteristics of the table.

Parent topic: The Table AI Assist Tool

Was this article helpful?