Query table function (table-valued-function, tvf) can be used to transparently transmit query statements directly to a catalog for data query
Supported by Doris version 2.1.3, currently only transparent query jdbc catalog is supported. You need to create the corresponding catalog in Doris first.
QUERY( "catalog" = "<catalog>", "query" = "<query_sql>" );
Each parameter in the query table function tvf is a "key"="value" pair.
| Field | Description |
|---|---|
catalog | Catalog name, which needs to be filled in according to the name of the catalog. |
query | The query statement to be executed. |
Can be used with desc function
desc function query("catalog" = "jdbc", "query" = "select * from test.student");
+-------+------+------+-------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-------+---------+-------+ | id | int | Yes | true | NULL | | | name | text | Yes | false | NULL | NONE | +-------+------+------+-------+---------+-------+
Transparent query for tables in jdbc catalog data source
select * from query("catalog" = "jdbc", "query" = "select * from test.student");
+------+---------+ | id | name | +------+---------+ | 1 | alice | | 2 | bob | | 3 | jack | +------+---------+
select * from query("catalog" = "jdbc", "query" = "select * from test.score");
+------+---------+ | id | score | +------+---------+ | 1 | 100 | | 2 | 90 | | 3 | 80 | +------+---------+
Transparent join query for tables in jdbc catalog data source
select * from query("catalog" = "jdbc", "query" = "select a.id, a.name, b.score from test.student a join test.score b on a.id = b.id");
+------+---------+---------+ | id | name | score | +------+---------+---------+ | 1 | alice | 100 | | 2 | bob | 90 | | 3 | jack | 80 | +------+---------+---------+