Query snippets

It’s often easier to duplicate prior work and modify than to write something from scratch. This is particularly true for common JOIN statements or complex CASE expressions. As your list of queries grows, it can be difficult to remember which queries contain the statement you need.

Query snippets are segments of queries that you can share and trigger using auto complete.

Query snippet

Here’s an example of a simple snippet:

JOIN organizations org ON org.id = ${1:table}.org_id

Create a query snippet

  1. Click your username in the top bar of the workspace and select User Settings from the drop down.

  2. Click the Query Snippets tab.

  3. Click Create Query Snippet.

  4. In the Trigger field, enter the snippet trigger.

  5. Optionally enter a description.

  6. In the Snippet field, enter the snippet.

  7. Click Create.

Insertion points

${1:table} is an insertion point with placeholder text. When Databricks SQL renders the snippet, the dollar sign $ and curly braces {} are stripped away and the word table is highlighted for replacement.

Note

You can use the placeholder text as a desirable default value to override at runtime.

You designate insertion points by wrapping an integer tab order with a single dollar sign and curly braces ${}. A text placeholder preceded by a colon : is optional but useful for users unfamiliar with your snippet.

When Databricks SQL renders this snippet:

AND (invoices.complete IS NULL OR invoices.complete <> '${2}')
AND (invoices.canceled IS NULL OR invoices.canceled <> '${1}')
AND (invoices.modified IS NULL OR invoices.modified_date <> '${0: this_date}')

The text insertion carat jumps to the second line between the quote marks ''. When you press Tab, the carat will jump backwards onto the first line. When you press Tab again, the carat will jump to the third line and this_date will be highlighted to prompt for the desired value.

Note

An insertion point of zero ${0} is always the last point in the tab order.

Insert a query snippet

If you have Auto Complete enabled, you can invoke your snippet from the SQL editor by typing the trigger word that you defined in the Query Snippet editor. Auto Complete will suggest the snippet like any other keyword in your database.

Note

If Auto Complete is disabled, you can still invoke query snippets by pressing Ctrl + Space and typing the trigger word for your query snippet. This can be necessary if your schema exceeds 5000 tokens.

Here are some other ideas for snippets: