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 auto complete. 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

Use the following steps to create snippets using these snippet examples:

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

2. Click the Developer tab.

3. Next to SQL query snippets click Manage.

4. Click Create query snippet.

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

6. Optionally enter a description.

7. In the Snippet field, enter the snippet.

8. Click Create.

   

Use a query snippet in a query

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. Type the first 3 letters of the snippet name and then select a snippet from the autocomplete window. You can also manually open the window by pressing Option + 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 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 carat jumps to the first insertion point to prompt for the desired value. When you press Tab, the carat jumps to the next insertion point for the next value. When you press Tab again, the carat 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 3 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 carat jumps to the insertion point.

   

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

5. Optionally, execute 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.