Creating data assets by using SQL queries

To add a dynamic view of data from a connection to your project, create an SQL query to select specific data.

Some capabilities are not available in all plans. See IBM Knowledge Catalog legacy service plans and IBM watsonx.data intelligence plans.

To add a dynamic view:

  1. From the project page, click the Assets tab, and then click New asset > Create a dynamic view of data.

  2. Leave the data selection method Write an SQL query selected.

  3. Enter a name and optionally a description. The maximum number of characters that are allowed for the name or the description is 100 each. You can also add tags for retrievability as required.

  4. Select an existing connection asset as the source of the data. The connection must support SQL queries.

  5. Create your SQL query. You can craft the SQL query yourself, or enter a plain text query and have that converted to SQL by using one of the available models.

    The option to work with plain text queries is technology preview functionality. Technology preview functionality is available for use in development and testing, but is not meant for use in production.

    To work with plain text queries, the project must be enabled for natural language queries, but this is a one-time operation. For details, see Data intelligence tools settings.

    For more accurate and context-aware SQL output, you can provide additional context to the models by uploading query samples. For more information, see Providing additional context for text-to-SQL conversions.

    Remember: Generated queries are not guaranteed to be identical from run to run, so that you might see variations.

    1. In the free-form text area of the SQL tab, enter your text query, select one of the available models (Select model icon), and generate the SQL statement.

      Tip: The meta-llama/llama-3-3-70b model is better at following prompt instructions for converting text into SQL and thus might provide more accurate results compared to Granite models.

      A text query can look like these examples: “Get all transactions with payment type” or “Join conditions and patients, then take the first 100 rows.”

      When you enter your text query, you can provide additional information that serves as custom prompt to the model. Precede that additional information with the disclaimer Note: . For example:

      Show records in customer_spend where the spending score is less than 50 and age is between 30 and 50.
Note: include the relevant columns and do not include all the columns

    2. Copy the generated SQL statement to the SQL area. You can still update the SQL query as required.

    Alternatively, you can directly enter your SQL query for selecting the data for the dynamic view.

    Example:

    SELECT name, address, client_id FROM BANK1.BANK_CLIENTS WHERE account_type='CHECKING' AND online_access='YES'

    This query creates a view with the columns that contain the names, addresses, and client IDs of clients who have online access to their checking account. The source is the BANK_CLIENTS database table.

  6. Optional: If you create this asset for generating a data product, you can select a parameter set from your project to create a customizable query.

    If you work with a parameter set, connect each parameter with a data value. All parameter names in the query must match their corresponding names in the parameter set. To specify a parameter, use this syntax: '#<parameter_name>#'.

    Example:

    SELECT * FROM PUBLIC.COURSES WHERE PUBLIC.COURSES.SEMESTER='#TERM#' AND PUBLIC.COURSES.COURSE_NAME='#COURSE#'

  7. Optional: Test your query to validate the SQL. Otherwise, your query is validated when you click Create. You must fix any SQL errors before you can create the asset.

The asset is added to the project Assets page. To differentiate dynamic views from other data assets, such assets are marked with the reference data icon (reference data asset). If folders are enabled in the project, a dynamic view is always created in the root folder, but you can move it to any subdirectory to organize your dynamic views.

Next steps