Unifying Parameters Throughout Databricks | Databricks Weblog

As we speak, we’re excited to announce the assist for named parameter markers within the SQL editor. This function lets you write parameterized code within the SQL editor, that may then be copied and run straight in a dashboard or pocket book with none syntax modifications. This marks a major milestone on our journey to unify parameters throughout queries, notebooks, and dashboards.

Utilizing named parameter markers

Parameters allow you to substitute values into dataset queries at runtime, permitting you to filter information by standards equivalent to dates and product classes. This leads to extra environment friendly querying and exact evaluation earlier than information is aggregated in a SQL question.

Parameter markers are supported throughout queries, notebooks, dashboards, workflows, and the SQL Execution API. They’re strongly typed and extra resilient to SQL injection assaults by clearly separating offered values from the SQL statements. The named parameter marker syntax can be utilized by merely including a colon (:) to the start of an alphabetic phrase, for instance, :parameter_name or :`parameter with an area`.

To specify a key phrase like a column or desk identify, use the identifier() clause as a wrapper. For instance, identifier(:parameter_name).

We suggest updating current parameters utilizing the named parameter marker syntax.  We are going to quickly present an assistant motion to transform parameters mechanically.

Frequent use instances

Listed below are a number of use instances that parameters are helpful for: 

Add a parameterized date vary to a question to pick data inside a particular time-frame.

SELECT * FROM samples.nyctaxi.journeys the place tpep_pickup_datetime BETWEEN :start_date AND :end_date

Dynamically choose or create a catalog, schema, and desk.

SELECT * FROM IDENTIFIER(:catalog || '.' || :schema || '.' || :desk)

CREATE TABLE IDENTIFIER(:catalog || '.' || :schema || '.' || :desk) AS SELECT 1;

Outline the question schema with a parameter.

USE SCHEMA IDENTIFIER(:selected_schema)

Parameterize templated strings to format outputs, equivalent to cellphone quantity.

SELECT format_string("(%d) %d", :area_code, :phone_number) as phone_number

Parameterize rollups by day, month, or yr.

SELECT DATE_TRUNC(:date_granularity, tpep_pickup_datetime) AS date_rollup, COUNT(*) AS total_trips FROM journeys GROUP BY date_rollup

Choose a number of parameter values in a single question.

SELECT * FROM journeys WHERE

  array_contains(

    TRANSFORM(SPLIT(:list_parameter, ','), s -> TRIM(s)),

    dropoff_zip

  )

Coming quickly

We sit up for offering much more simplicity and adaptability in discipline and parameter filtering with date vary and multi-value parameters. For instance,

SELECT * FROM journeys the place tpep_pickup_datetime BETWEEN :date.min AND :date.max 

and

SELECT * FROM journeys the place WHERE array_contains(:zipcodes, dropoff_zip)

Get began with named parameter marker syntax

Named parameter marker syntax for queries, notebooks, dashboards, workflows, and the SQL Execution API is out there at present. When you have any suggestions or questions, please contact us at  [email protected]. Examine the documentation web page for extra detailed sources on getting began parameters on Databricks.

To be taught extra about Databricks SQL, go to our web site or learn the documentation. You may as well take a look at the product tour for Databricks SQL. If you wish to migrate your current warehouse to a high-performance, serverless information warehouse with a terrific consumer expertise and decrease whole price, then Databricks SQL is the answer — strive it totally free.