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. You can also create a query snippet that contains an insertion point with placeholder text that a user can replace at runtime.

Query snippets are segments of queries that you can share and trigger using autocomplete. Use query snippets for:

• Frequent JOIN statements
• Complicated clauses like WITH or CASE.
• Conditional formatting

Here are examples of snippets:

--Simple snippet
WHERE fare_amount > 100

--Snippet with an insertion point for a value to be provided at runtime
WHERE fare_amount > ${1:value}

--Snippet with an insertion point for a value to be provided at runtime and containing a default value
WHERE fare_amount > ${1:100}

--Snippet with multiple insertion points
WHERE fare_amount > ${2:min_value} AND fare_amount < ${1:max_value} AND trip_distance < ${0:max_distance}

Create query snippets

You can create a query snippet from the SQL, notebook, or file editors. Follow these steps:

1. In the SQL editor, click the kebab menu.

   In the new SQL editor, click the kebab menu, then click View.

   In the notebook or file editors, click View.

2. Click Query snippets.

3. Click Create query snippet.

4. In the Replace field, enter the snippet name. You will use this name when writing a query that uses the snippet.

5. (Optional) Enter a description.

6. In the Snippet field, enter the snippet.

7. Click Create.

   

Use a query snippet

Query snippets can be used any SQL query. You can access query snippets in the SQL editor, notebook SQL cells, SQL files, and AI/BI dashboard queries.

Here's an example of a simple query with which you can use these query snippets:

--Simple query
SELECT * FROM samples.nyctaxi.trips

Use the following steps to use a query snippet with this query:

1. Open SQL Editor.

2. Type your query in the SQL editor query pane.

3. Start typing the name of your query snippet, then select it from the autocomplete window.

   In the SQL editor, you can also manually open the window with Control+Space and select a snippet.

   

4. Execute the query with the WHERE clause from the query snippet.

   

Working with insertion points in query snippets

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. In the query snippets with insertion points that you created previously, ${1:value} is an insertion point with a placeholder, and ${1:100} is an insertion point with a default value for the placeholder that you can override at runtime. When Databricks SQL renders the snippet, the dollar sign $ and curly braces {} are stripped away, and the word value or the default of 100 is highlighted for replacement.

When there are multiple insertion points, the text insertion caret jumps to the first insertion point to prompt for the desired value. When you press Tab, the caret jumps to the next insertion point for the next value. When you press Tab again, the caret will jump to the next insertion point line until it reaches the final insertion point.

note

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

Use the following steps to use the insertion point query snippets with the query:

1. Open SQL Editor.

2. Type your query in the SQL editor query pane.

3. Type the first three letters of the name of your query snippet and then select a query snippet with the insertion point without a default value.

   The query snippet is added to the query, and the text insertion caret jumps to the insertion point.

   

4. Enter a value for the WHERE clause, such as 200.

5. Optionally, run the query with the WHERE clause from the query snippet.

6. Repeat the previous steps, but select the query snippet with the insertion point using a default value.

   

7. Repeat the previous steps, but select the query snippet with multiple insertion points.

   

8. Enter a value for the first insertion point, tab to the next insertion point, and enter a value, and then tab to the final insertion point and enter a value.