Add JSONB Support for Snowflake

Create a new project with the Snowflake connector enabled

If you haven’t done so already, complete the steps in our guide for connecting Snowflake to PromptQL.

Create a native query

Native queries enable you to write your own SQL and expose it as a root-level field in your GraphQL API. This field will then also be available to PromptQL for retrieving data from your Snowflake instance. We can use the existing JSON operator built into Snowflake to transform a column’s value into structured JSON.

As an example, imagine we have a table called USERS in an database called ECOMM; if the user_details are stored as a string — but are in fact JSON — we can use a native query to return the structured output to our API:

--- This will return the user_id and email in their existing format, while transforming the user_details to JSON:
select user_id, email, TO_JSON(user_details) as user_details, created_at from PUBLIC.ECOMM.USERS

We can then include this SQL in a native query by adding it to the nativeQueries object of our configuration.json found in our connector’s directory:

  "nativeQueries": {
  "ECOMM_USERS": {
    "sql": {
      "parts": [
        {
          "type": "text",
          "value": "select user_id, email, TO_JSON(user_details) as user_details, created_at from PUBLIC.ECOMM.USERS"
        }
      ]
    },
    "columns": {
      "USER_DETAILS": {
        "type": "named",
        "name": "STRING"
      },
      "USER_ID": {
        "type": "named",
        "name": "BIGINTEGER"
      },
      "CREATED_AT": {
        "type": "named",
        "name": "TIMESTAMP"
      },
      "EMAIL": {
        "type": "named",
        "name": "STRING"
      }
    },
    "arguments": {},
    "description": null,
    "isProcedure": false
  }
}

The key of the new object is the name which will be exposed as a top-level field in your API. After adding your custom SQL, include the returned columns along with their types.

Re-introspect your connector

As you’ve made a new field available in your connector’s configuration, you’ll need to re-introspect your connector:

ddn connector introspect <connector-name>

Add the model

Then, add the new model using the name you provided in your native query’s key:

# E.g., ddn model add my_snowflake "ECOMM_USERS"
ddn model add <connector-name> "<model-name>"

This will generate an HML file, which you’ll use in the next step, representing your model.

Augment the metadata description

The more context afforded to PromptQL, the better it understands your data and the ways it can interact with it. If we add a description to the metadata object, PromptQL will infer that structured JSON will be returned.

For example:

kind: ObjectType
version: v1
definition:
  name: EcommUsers
  fields:
    - name: createdAt
      type: Timestamp!
    - name: email
      type: String_1!
    - name: userDetails
      type: String_1!
      description:
        "This is stringified JSON it has the following fields: address, shipping_state, phone, preferences is an object
        which has newsletter and theme {address: '123 Main St', shipping_state: 'CA', phone: '555-555-5555',
        preferences: {newsletter: true, theme: 'dark'}}"
    - name: userId
      type: Biginteger!
  graphql:
    typeName: EcommUsers
    inputTypeName: EcommUsersInput
  dataConnectorTypeMapping:
    - dataConnectorName: snow
      dataConnectorObjectType: ECOMM_USERS
      fieldMapping:
        createdAt:
          column:
            name: CREATED_AT
        email:
          column:
            name: EMAIL
        userDetails:
          column:
            name: USER_DETAILS
        userId:
          column:
            name: USER_ID

Here, we’re not only explaining that this is stringified JSON, but also an example of what can be expected to be returned.

Create a new build and start your services

First, create a new build:

ddn supergraph build local

Then, start your services and open the development console:

ddn run docker-start

ddn console --local

Talk to your data

Ask any questions you wish about your Snowflake data…including those that utilize your structured JSON directly from Snowflake!

If you run into issues, first try executing a query in the GraphiQL explorer and ensure your native query is behaving as expected.