Databricks Data Engineer Associate Question 193
Single answerYou are working on a Databricks SQL project and need to define a SQL User-Defined Function (UDF) that takes a string as input, converts it to uppercase, and returns the updated string. Which of the following correctly defines a SQL UDF in Databricks?
- A
CREATE FUNCTION to_uppercase(input STRING) RETURNS STRING RETURN UPPER(input);
- B
CREATE OR REPLACE FUNCTION to_uppercase(input STRING) RETURNS STRING AS 'UPPER(input)';
- C
CREATE FUNCTION to_uppercase AS (input STRING) -> UPPER(input);
- D
CREATE OR REPLACE FUNCTION to_uppercase(input STRING) RETURNS STRING COMMENT 'Converts string to uppercase' RETURN UPPER(input);
Show answer and explanation
Correct answer: D
Explanation
In Databricks, SQL User-Defined Functions (UDFs) are created using the 'CREATE OR REPLACE FUNCTION' statement. The function definition includes the name of the function, input parameters with their data types, the return type, and the logic for the function. Option 4 adheres to the correct syntax for defining a SQL UDF in Databricks.
- A. Incorrect.
This option is incorrect because it uses an invalid syntax for defining a SQL UDF in Databricks. Specifically, 'RETURN' is misplaced and 'CREATE FUNCTION' is incomplete.
- B. Incorrect.
This option is incorrect because it uses an invalid SQL syntax for defining a UDF. The 'AS' keyword is not used correctly in this context.
- C. Incorrect.
This option is incorrect because it uses a syntax closer to Python or other programming languages but is not valid SQL syntax for UDF creation in Databricks.
- D. Correct.
This option is correct because it uses the correct SQL syntax to define or replace a SQL UDF in Databricks, including the 'CREATE OR REPLACE FUNCTION', the input type declaration, the return type declaration, and an optional COMMENT.