Skip to main content
Version: PromptQL

native-queries

Introduction​

Native Queries allow you to run custom SQL queries on your databricks database. These allow you to run queries that are not supported by PromptQL by default.

Structure​

A Native Query is a single SQL statement that returns results and can take arguments.

The SQL structure of a Native Query is specified in the Native Operation syntax page.

Create a Native Query​

To create a new Native Query, create a new SQL file inside the connector configuration directory, then use the CLI to add it to the connector configuration. For example:

  1. Create a new directory structure under the connector configuration:

    mkdir -p my_subgraph/connector/<connector-name>/native_operations/queries/

  2. Create a new SQL file my_subgraph/connector/<connector-name>/native_operations/queries/artist_by_name_between.sql with the following content:

    SELECT *
    FROM "Artist"
    WHERE "Name" LIKE :name
      AND "ArtistId" > :lower_bound
      AND "ArtistId" < :upper_bound

  3. Create a new entry in the connector configuration:

    ddn connector plugin --connector my_subgraph/connector/<connector-name>/connector.yaml -- \
      native-queries create --operation-path native_operations/queries/artist_by_name_between.sql --name artist_by_name_between
    Relative paths

    The operation-path provided should be relative to the configuration directory. It means that for the above case, the SQL file is expected to be present at <ddn-project-root>/my_subgraph/connector/<connector-name>/native-operations/queries/artist_by_name_between.sql

  4. If your data connector is running, run the following command to update your metadata to track the new native query:

    ddn connector-link update <connector-name> --add-all-resources

    If you get a connection refused error, it means that your data connector is not running. You can either start the data connector and try again, or, run the following commands to update your metadata to track the new native query:

    # start the connector and introspect your DB
    ddn connector introspect <connector-name>

    # update your metadata to track the new native query
    ddn model add <connector-name> "artist_by_name_between"

Update a Native Query​

A Native Query can be updated with the create command and setting the --overwrite option. For example:

ddn connector plugin --connector my_subgraph/connector/<connector-name>/connector.yaml -- \
  native-queries create --name artist_by_name_between --operation-path native_operations/queries/artist_by_name_between.sql  --overwrite