CREATE FUNCTION (SQL)

Belangrijk

Deze functie is beschikbaar als openbare preview.

Notitie

Beschikbaar in Databricks Runtime 9.1 en hoger.

Hiermee maakt SQL scalaire of tabelfunctie die een set argumenten gebruikt en een scalaire waarde of een set rijen retourneert.

Syntaxis

CREATE [OR REPLACE] [TEMPORARY] FUNCTION [IF NOT EXISTS]
    [database_name.] function_name ([function_parameter[,...]])
    RETURNS {data_type | TABLE (function_parameter[,...])
    [characteristic...]
    RETURN { expression | query }

function_parameter:
    param_name data_type [COMMENT parameter_comment]

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | COMMENT function_comment
  | [CONTAINS SQL | READS SQL DATA]

Parameters

  • OF VERVANGEN

    Indien opgegeven, wordt de functie met dezelfde naam en handtekening (aantal parameters en parametertypen) vervangen. U kunt een bestaande functie niet vervangen door een andere handtekening. Dit is vooral handig om de functie-body en het retourtype van de functie bij te werken. U kunt deze parameter niet opgeven met IF NOT EXISTS .

  • TIJDELIJKE

    Het bereik van de functie die wordt gemaakt. Wanneer u TEMPORARY opgeeft, is de gemaakte functie geldig en zichtbaar in de huidige sessie. Er wordt geen permanente vermelding gemaakt in de catalogus.

  • ALS NIET BESTAAT

    Als deze is opgegeven, wordt de functie alleen gemaakt als deze niet bestaat. Het maken van de functie slaagt (er wordt geen fout opgetreden) als de opgegeven functie al in het systeem bestaat. U kunt deze parameter niet opgeven met OR REPLACE .

  • [database_name.] function_name

    De naam van de functie die moet worden gemaakt. Voor een permanente functie kunt u de functienaam eventueel kwalificeren met een databasenaam.

  • function_parameter

    De optionele lijst met functieparameters. Een functieparameter bestaat uit een naam, een geldig gegevenstype en een optionele opmerking. Als de functie geen parameter gebruikt, gebruikt u een lege () parameterlijst.

  • RETOURNEERT data_type

    Het retourgegevenstype van de scalaire functie.

  • RETOURNEERT TABEL (function_parameter[,...])

    De handtekening van het resultaat van de tabelfunctie.

  • RETURN { expression | query }

    De body van de functie. Voor een scalaire functie kan dit een query of een expressie zijn. Voor een tabelfunctie kan dit alleen een query zijn. De expressie mag niet het volgende bevatten:

    • Statistische functies en vensterfuncties
    • Rij die functies produceert, zoals explode

Optionele kenmerkclausules

Alle kenmerkclausules zijn optioneel. U kunt een aantal van deze in elke volgorde opgeven, maar u kunt elke component slechts één keer opgeven.

  • TAAL SQL

    De taal van de functie. SQL is de enige ondersteunde taal.

  • [NIET] DETERMINISTISCHE

    Of de functie deterministisch is. Een functie is deterministisch wanneer deze slechts één resultaat voor een bepaalde set parameters retourneert.

  • Opmerkingen function_comment

    Een opmerking voor de functie.

  • BEVAT SQL | LEEST SQL DATA

    Of een functie gegevens direct of indirect uit een tabel of weergave leest. Wanneer de functie de SQL leest, kunt u niet CONTAINS SQL opgeven. Als u geen van beide clausules opgeeft, wordt de eigenschap afgeleid van de functie-body.

Voorbeelden

Een scalaire SQL maken en gebruiken

CREATE VIEW t(c1, c2) AS VALUES (0, 1), (1, 2);
-- Create a temporary function with no parameter.
CREATE TEMPORARY FUNCTION hello() RETURNS STRING RETURN 'Hello World!';

SELECT hello();
+------------+
|hello()     |
+------------+
|Hello World!|
+------------+

-- Create a permanent function with parameters.
CREATE FUNCTION area(x DOUBLE, y DOUBLE) RETURNS DOUBLE RETURN x * y;

-- Use a SQL function in the SELECT clause of a query.
SELECT area(c1, c2) AS area FROM t;
+----+
|area|
+----+
|0.0 |
|2.0 |
+----+

-- Use a SQL function in the WHERE clause of a query.
SELECT * FROM t WHERE area(c1, c2) > 0;
+---+---+
|c1 |c2 |
+---+---+
|1  |2  |
+---+---+

-- Compose SQL functions.
CREATE FUNCTION square(x DOUBLE) RETURNS DOUBLE RETURN area(x, x);

SELECT c1, square(c1) AS square FROM t;
+---+------+
|c1 |square|
+---+------+
|0  |0.0   |
|1  |1.0   |
+---+------+

-- Create a non-deterministic function.
CREATE FUNCTION dice(n INT) RETURNS INT
NOT DETERMINISTIC
COMMENT 'a n-sided dice'
RETURN FLOOR((RAND() * n) + 1);

SELECT dice(6) AS dice_6;
+------+
|dice_6|
+------+
|3     |
+------+

-- Create a SQL function with a scalar subquery.
CREATE VIEW scores(player, score) AS VALUES (0, 1), (0, 2), (1, 2), (1, 5);

CREATE FUNCTION avg_score(p INT) RETURNS FLOAT
COMMENT 'get an average score of the player'
RETURN SELECT AVG(score) FROM scores WHERE player = p;

SELECT c1, avg_score(c1) AS avg_score FROM t;
+---+---------+
|c1 |avg_score|
+---+---------+
|0  |1.5      |
|1  |3.5      |
+---+---------+

Een tabelfunctie voor SQL maken en gebruiken

-- Create an employee table.
CREATE TABLE employee (id INT, name STRING, deptno INT);
INSERT INTO employee VALUES
    (101, 'John', 1),
    (102, 'Lisa', 2),
    (103, 'Paul', 3),
    (104, 'Evan', 4),
    (105, 'Chloe', 5),
    (106, 'Amy', 6);

-- Create a department table.
CREATE TABLE department (deptno INT, deptname STRING);
INSERT INTO department VALUES
    (1, 'Marketing'),
    (2, 'Sales'),
    (3, 'Engineering');

-- Create a SQL table function to get all employees with a given department number.
CREATE FUNCTION getemps(deptno INT)
RETURNS TABLE(id INT, name STRING)
COMMENT 'Get all employees with the department number'
RETURN SELECT id, name FROM employee e WHERE e.deptno = getemps.deptno

-- Use a SQL table function.
SELECT * FROM getemps(1);
+---+----+
|id |name|
+---+----+
|101|John|
+---+----+

-- Use a SQL table function with LATERAL.
SELECT getemps.* FROM department d, LATERAL getemps(d.deptno);
+---+----+
|id |name|
+---+----+
|101|John|
|102|Lisa|
|103|Paul|
+---+----+

Een functie SQL vervangen

-- Replace a SQL scalar function.
CREATE OR REPLACE FUNCTION square(x DOUBLE) RETURNS DOUBLE RETURN x * x;

-- Replace a SQL table function.
CREATE OR REPLACE FUNCTION getemps(deptno INT)
RETURNS TABLE (name STRING)
RETURN SELECT name FROM employee e WHERE e.deptno = getemps.deptno;

Notitie

U kunt een bestaande functie niet vervangen door een andere handtekening.

Een functie SQL beschrijven

DESCRIBE FUNCTION hello();
+----------------+
|function_desc   |
+----------------+
|Function: hello |
|Type:     SCALAR|
|Input:    ()    |
|Returns:  STRING|
+----------------+

DESCRIBE FUNCTION area;
+----------------------+
|function_desc         |
+----------------------+
|Function: default.area|
|Type:     SCALAR      |
|Input:    x DOUBLE    |
|          y DOUBLE    |
|Returns:  DOUBLE      |
+----------------------+

DESCRIBE FUNCTION dice;
+----------------------+
|function_desc         |
+----------------------+
|Function: default.dice|
|Type:     SCALAR      |
|Input:    n INT       |
|Returns:  INT         |
+----------------------+

DESCRIBE FUNCTION EXTENDED dice;
+-------------------------------------------+
|function_desc                              |
+-------------------------------------------+
|Function:      default.dice                |
|Type:          SCALAR                      |
|Input:         n INT                       |
|Returns:       INT                         |
|Comment:       a n-sided dice              |
|Deterministic: false                       |
|Owner:         user                        |
|Create Time:   Fri Apr 16 10:00:00 PDT 2021|
|Body:          FLOOR((RAND() * n) + 1)     |
+-------------------------------------------+

-- Describe a SQL table function.
DESCRIBE FUNCTION getemps;
+-------------------------+
|function_desc            |
+-------------------------+
|Function: default.getemps|
|Type:     TABLE          |
|Input:    deptno INT     |
|Returns:  id   INT       |
|          name STRING    |
+-------------------------+