Table-valued function (TVF) invocation
Applies to: Databricks SQL
Databricks Runtime
Invokes a function which returns a relation or a set of rows as a [table-reference](sql-ref
A TVF can be a:
SQL user-defined table function.
The range table-valued function.
Any table-valued generator function, such as explode.
Applies to:
Databricks SQL
Databricks Runtime 12.2 LTS and above.
Syntax
function_name ( [ expression [, ...] ] ) [ table_alias ]
Parameters
-
A table-valued function.
-
A combination of one or more values, operators, and SQL functions that results in a value.
-
An optional label to reference the function result and its columns.
Examples
-- range call with end
> SELECT * FROM range(6 + cos(3));
0
1
2
3
4
-- range call with start and end
> SELECT * FROM range(5, 10);
5
6
7
8
9
-- range call with numPartitions
> SELECT * FROM range(0, 10, 2, 200);
0
2
4
6
8
-- range call with a table alias
> SELECT * FROM range(5, 8) AS test;
5
6
7
-- Create a SQL UDTF and invoke it
> CREATE OR REPLACE FUNCTION table_func(a INT) RETURNS TABLE
RETURN SELECT a * c1 AS res FROM VALUES(1), (2), (3), (4) AS T(c1)
> SELECT * FROM table_func(5);
5
10
15
20
-- Using lateral correlation
> SELECT table_func.res FROM VALUES(10), (20) AS S(c1), LATERAL table_func(c1);
10
20
20
40
30
60
40
80
-- Scalar fucntion are not allowed in the FROM clause
> SELECT * FROM trim('hello ');
Error
On Databricks SQL amd Databricks Runtime 12.2 LTS and above:
> SELECT * FROM explode(array(10, 20));
10
20
> SELECT * FROM inline(array(struct(1, 'a'), struct(2, 'b')));
col1 col2
---- ----
1 a
2 b
> SELECT * FROM posexplode(array(10,20));
pos col
--- ---
0 10
1 20
> SELECT * FROM stack(2, 1, 2, 3);
col0 col1
---- ----
1 2
3 null
> SELECT * FROM json_tuple('{"a":1, "b":2}', 'a', 'b');
c0 c1
--- ---
1 2
> SELECT * FROM parse_url('http://spark.apache.org/path?query=1', 'HOST');
spark.apache.org
> SELECT * FROM VALUES(1), (2) AS t1(c1), LATERAL explode (ARRAY(3,4)) AS t2(c2);
c1 c2
-- --
1 3
1 4
2 3
2 4
Related articles
प्रतिक्रिया
https://aka.ms/ContentUserFeedback.
जल्द आ रहा है: 2024 के दौरान हम सामग्री के लिए फीडबैक तंत्र के रूप में GitHub मुद्दों को चरणबद्ध तरीके से समाप्त कर देंगे और इसे एक नई फीडबैक प्रणाली से बदल देंगे. अधिक जानकारी के लिए, देखें:के लिए प्रतिक्रिया सबमिट करें और देखें