This is an automated email from the ASF dual-hosted git repository.
alamb pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/arrow-datafusion.git
The following commit(s) were added to refs/heads/master by this push:
new d11e28a33 feat(catalog): Implement information_schema.views (#2934)
d11e28a33 is described below
commit d11e28a333992ffe71303a19a4891a354c8af240
Author: BaymaxHWY <[email protected]>
AuthorDate: Wed Jul 20 21:23:33 2022 +0800
feat(catalog): Implement information_schema.views (#2934)
---
datafusion/core/src/catalog/information_schema.rs | 126 +++++++++++++++++---
datafusion/core/tests/sql/information_schema.rs | 138 ++++++++++++----------
datafusion/sql/src/planner.rs | 4 +-
3 files changed, 183 insertions(+), 85 deletions(-)
diff --git a/datafusion/core/src/catalog/information_schema.rs
b/datafusion/core/src/catalog/information_schema.rs
index fd8e250fe..0467dffa7 100644
--- a/datafusion/core/src/catalog/information_schema.rs
+++ b/datafusion/core/src/catalog/information_schema.rs
@@ -41,6 +41,7 @@ use super::{
const INFORMATION_SCHEMA: &str = "information_schema";
const TABLES: &str = "tables";
+const VIEWS: &str = "views";
const COLUMNS: &str = "columns";
/// Wraps another [`CatalogProvider`] and adds a "information_schema"
@@ -124,28 +125,21 @@ impl InformationSchemaProvider {
builder.add_table(
&catalog_name,
&schema_name,
- table_name,
+ &table_name,
table.table_type(),
- table.get_table_definition(),
);
}
}
}
// Add a final list for the information schema tables themselves
- builder.add_table(
- &catalog_name,
- INFORMATION_SCHEMA,
- TABLES,
- TableType::View,
- None::<&str>,
- );
+ builder.add_table(&catalog_name, INFORMATION_SCHEMA, TABLES,
TableType::View);
+ builder.add_table(&catalog_name, INFORMATION_SCHEMA, VIEWS,
TableType::View);
builder.add_table(
&catalog_name,
INFORMATION_SCHEMA,
COLUMNS,
TableType::View,
- None::<&str>,
);
}
@@ -154,6 +148,32 @@ impl InformationSchemaProvider {
Arc::new(mem_table)
}
+ fn make_views(&self) -> Arc<dyn TableProvider> {
+ let mut builder = InformationSchemaViewBuilder::new();
+
+ for catalog_name in self.catalog_list.catalog_names() {
+ let catalog = self.catalog_list.catalog(&catalog_name).unwrap();
+
+ for schema_name in catalog.schema_names() {
+ if schema_name != INFORMATION_SCHEMA {
+ let schema = catalog.schema(&schema_name).unwrap();
+ for table_name in schema.table_names() {
+ let table = schema.table(&table_name).unwrap();
+ builder.add_view(
+ &catalog_name,
+ &schema_name,
+ &table_name,
+ table.get_table_definition(),
+ )
+ }
+ }
+ }
+ }
+
+ let mem_table: MemTable = builder.into();
+ Arc::new(mem_table)
+ }
+
/// Construct the `information_schema.columns` virtual table
fn make_columns(&self) -> Arc<dyn TableProvider> {
let mut builder = InformationSchemaColumnsBuilder::new();
@@ -194,7 +214,7 @@ impl SchemaProvider for InformationSchemaProvider {
}
fn table_names(&self) -> Vec<String> {
- vec![TABLES.to_string(), COLUMNS.to_string()]
+ vec![TABLES.to_string(), VIEWS.to_string(), COLUMNS.to_string()]
}
fn table(&self, name: &str) -> Option<Arc<dyn TableProvider>> {
@@ -202,13 +222,15 @@ impl SchemaProvider for InformationSchemaProvider {
Some(self.make_tables())
} else if name.eq_ignore_ascii_case("columns") {
Some(self.make_columns())
+ } else if name.eq_ignore_ascii_case("views") {
+ Some(self.make_views())
} else {
None
}
}
fn table_exist(&self, name: &str) -> bool {
- return matches!(name.to_ascii_lowercase().as_str(), TABLES | COLUMNS);
+ return matches!(name.to_ascii_lowercase().as_str(), TABLES | VIEWS |
COLUMNS);
}
}
@@ -220,7 +242,6 @@ struct InformationSchemaTablesBuilder {
schema_names: StringBuilder,
table_names: StringBuilder,
table_types: StringBuilder,
- definitions: StringBuilder,
}
impl InformationSchemaTablesBuilder {
@@ -234,7 +255,6 @@ impl InformationSchemaTablesBuilder {
schema_names: StringBuilder::new(default_capacity),
table_names: StringBuilder::new(default_capacity),
table_types: StringBuilder::new(default_capacity),
- definitions: StringBuilder::new(default_capacity),
}
}
@@ -244,7 +264,6 @@ impl InformationSchemaTablesBuilder {
schema_name: impl AsRef<str>,
table_name: impl AsRef<str>,
table_type: TableType,
- definition: Option<impl AsRef<str>>,
) {
// Note: append_value is actually infallable.
self.catalog_names
@@ -261,7 +280,6 @@ impl InformationSchemaTablesBuilder {
TableType::Temporary => "LOCAL TEMPORARY",
})
.unwrap();
- self.definitions.append_option(definition.as_ref()).unwrap();
}
}
@@ -272,7 +290,6 @@ impl From<InformationSchemaTablesBuilder> for MemTable {
Field::new("table_schema", DataType::Utf8, false),
Field::new("table_name", DataType::Utf8, false),
Field::new("table_type", DataType::Utf8, false),
- Field::new("definition", DataType::Utf8, true),
]);
let InformationSchemaTablesBuilder {
@@ -280,7 +297,6 @@ impl From<InformationSchemaTablesBuilder> for MemTable {
mut schema_names,
mut table_names,
mut table_types,
- mut definitions,
} = value;
let schema = Arc::new(schema);
@@ -291,6 +307,80 @@ impl From<InformationSchemaTablesBuilder> for MemTable {
Arc::new(schema_names.finish()),
Arc::new(table_names.finish()),
Arc::new(table_types.finish()),
+ ],
+ )
+ .unwrap();
+
+ MemTable::try_new(schema, vec![vec![batch]]).unwrap()
+ }
+}
+
+/// Builds the `information_schema.VIEWS` table row by row
+///
+/// Columns are based on
<https://www.postgresql.org/docs/current/infoschema-columns.html>
+struct InformationSchemaViewBuilder {
+ catalog_names: StringBuilder,
+ schema_names: StringBuilder,
+ table_names: StringBuilder,
+ definitions: StringBuilder,
+}
+
+impl InformationSchemaViewBuilder {
+ fn new() -> Self {
+ // StringBuilder requires providing an initial capacity, so
+ // pick 10 here arbitrarily as this is not performance
+ // critical code and the number of tables is unavailable here.
+ let default_capacity = 10;
+ Self {
+ catalog_names: StringBuilder::new(default_capacity),
+ schema_names: StringBuilder::new(default_capacity),
+ table_names: StringBuilder::new(default_capacity),
+ definitions: StringBuilder::new(default_capacity),
+ }
+ }
+
+ fn add_view(
+ &mut self,
+ catalog_name: impl AsRef<str>,
+ schema_name: impl AsRef<str>,
+ table_name: impl AsRef<str>,
+ definition: Option<impl AsRef<str>>,
+ ) {
+ // Note: append_value is actually infallable.
+ self.catalog_names
+ .append_value(catalog_name.as_ref())
+ .unwrap();
+ self.schema_names
+ .append_value(schema_name.as_ref())
+ .unwrap();
+ self.table_names.append_value(table_name.as_ref()).unwrap();
+ self.definitions.append_option(definition.as_ref()).unwrap();
+ }
+}
+
+impl From<InformationSchemaViewBuilder> for MemTable {
+ fn from(value: InformationSchemaViewBuilder) -> Self {
+ let schema = Schema::new(vec![
+ Field::new("table_catalog", DataType::Utf8, false),
+ Field::new("table_schema", DataType::Utf8, false),
+ Field::new("table_name", DataType::Utf8, false),
+ Field::new("definition", DataType::Utf8, true),
+ ]);
+
+ let InformationSchemaViewBuilder {
+ mut catalog_names,
+ mut schema_names,
+ mut table_names,
+ mut definitions,
+ } = value;
+
+ let schema = Arc::new(schema);
+ let batch = RecordBatch::try_new(
+ schema.clone(),
+ vec![
+ Arc::new(catalog_names.finish()),
+ Arc::new(schema_names.finish()),
+ Arc::new(table_names.finish()),
Arc::new(definitions.finish()),
],
)
diff --git a/datafusion/core/tests/sql/information_schema.rs
b/datafusion/core/tests/sql/information_schema.rs
index f528c3c3c..5a4aa297f 100644
--- a/datafusion/core/tests/sql/information_schema.rs
+++ b/datafusion/core/tests/sql/information_schema.rs
@@ -52,12 +52,13 @@ async fn information_schema_tables_no_tables() {
.unwrap();
let expected = vec![
-
"+---------------+--------------------+------------+------------+------------+",
- "| table_catalog | table_schema | table_name | table_type |
definition |",
-
"+---------------+--------------------+------------+------------+------------+",
- "| datafusion | information_schema | columns | VIEW |
|",
- "| datafusion | information_schema | tables | VIEW |
|",
-
"+---------------+--------------------+------------+------------+------------+",
+ "+---------------+--------------------+------------+------------+",
+ "| table_catalog | table_schema | table_name | table_type |",
+ "+---------------+--------------------+------------+------------+",
+ "| datafusion | information_schema | columns | VIEW |",
+ "| datafusion | information_schema | tables | VIEW |",
+ "| datafusion | information_schema | views | VIEW |",
+ "+---------------+--------------------+------------+------------+",
];
assert_batches_sorted_eq!(expected, &result);
}
@@ -76,13 +77,14 @@ async fn information_schema_tables_tables_default_catalog()
{
.unwrap();
let expected = vec![
-
"+---------------+--------------------+------------+------------+------------+",
- "| table_catalog | table_schema | table_name | table_type |
definition |",
-
"+---------------+--------------------+------------+------------+------------+",
- "| datafusion | information_schema | columns | VIEW |
|",
- "| datafusion | information_schema | tables | VIEW |
|",
- "| datafusion | public | t | BASE TABLE |
|",
-
"+---------------+--------------------+------------+------------+------------+",
+ "+---------------+--------------------+------------+------------+",
+ "| table_catalog | table_schema | table_name | table_type |",
+ "+---------------+--------------------+------------+------------+",
+ "| datafusion | information_schema | columns | VIEW |",
+ "| datafusion | information_schema | tables | VIEW |",
+ "| datafusion | information_schema | views | VIEW |",
+ "| datafusion | public | t | BASE TABLE |",
+ "+---------------+--------------------+------------+------------+",
];
assert_batches_sorted_eq!(expected, &result);
@@ -95,14 +97,15 @@ async fn information_schema_tables_tables_default_catalog()
{
.unwrap();
let expected = vec![
-
"+---------------+--------------------+------------+------------+------------+",
- "| table_catalog | table_schema | table_name | table_type |
definition |",
-
"+---------------+--------------------+------------+------------+------------+",
- "| datafusion | information_schema | columns | VIEW |
|",
- "| datafusion | information_schema | tables | VIEW |
|",
- "| datafusion | public | t | BASE TABLE |
|",
- "| datafusion | public | t2 | BASE TABLE |
|",
-
"+---------------+--------------------+------------+------------+------------+",
+ "+---------------+--------------------+------------+------------+",
+ "| table_catalog | table_schema | table_name | table_type |",
+ "+---------------+--------------------+------------+------------+",
+ "| datafusion | information_schema | columns | VIEW |",
+ "| datafusion | information_schema | tables | VIEW |",
+ "| datafusion | information_schema | views | VIEW |",
+ "| datafusion | public | t | BASE TABLE |",
+ "| datafusion | public | t2 | BASE TABLE |",
+ "+---------------+--------------------+------------+------------+",
];
assert_batches_sorted_eq!(expected, &result);
}
@@ -139,19 +142,22 @@ async fn
information_schema_tables_tables_with_multiple_catalogs() {
.unwrap();
let expected = vec![
-
"+------------------+--------------------+------------+------------+------------+",
- "| table_catalog | table_schema | table_name | table_type |
definition |",
-
"+------------------+--------------------+------------+------------+------------+",
- "| datafusion | information_schema | columns | VIEW |
|",
- "| datafusion | information_schema | tables | VIEW |
|",
- "| my_catalog | information_schema | columns | VIEW |
|",
- "| my_catalog | information_schema | tables | VIEW |
|",
- "| my_catalog | my_schema | t1 | BASE TABLE |
|",
- "| my_catalog | my_schema | t2 | BASE TABLE |
|",
- "| my_other_catalog | information_schema | columns | VIEW |
|",
- "| my_other_catalog | information_schema | tables | VIEW |
|",
- "| my_other_catalog | my_other_schema | t3 | BASE TABLE |
|",
-
"+------------------+--------------------+------------+------------+------------+",
+ "+------------------+--------------------+------------+------------+",
+ "| table_catalog | table_schema | table_name | table_type |",
+ "+------------------+--------------------+------------+------------+",
+ "| datafusion | information_schema | columns | VIEW |",
+ "| datafusion | information_schema | tables | VIEW |",
+ "| datafusion | information_schema | views | VIEW |",
+ "| my_catalog | information_schema | columns | VIEW |",
+ "| my_catalog | information_schema | tables | VIEW |",
+ "| my_catalog | information_schema | views | VIEW |",
+ "| my_catalog | my_schema | t1 | BASE TABLE |",
+ "| my_catalog | my_schema | t2 | BASE TABLE |",
+ "| my_other_catalog | information_schema | columns | VIEW |",
+ "| my_other_catalog | information_schema | tables | VIEW |",
+ "| my_other_catalog | information_schema | views | VIEW |",
+ "| my_other_catalog | my_other_schema | t3 | BASE TABLE |",
+ "+------------------+--------------------+------------+------------+",
];
assert_batches_sorted_eq!(expected, &result);
}
@@ -200,15 +206,16 @@ async fn information_schema_tables_table_types() {
.unwrap();
let expected = vec![
-
"+---------------+--------------------+------------+-----------------+------------+",
- "| table_catalog | table_schema | table_name | table_type |
definition |",
-
"+---------------+--------------------+------------+-----------------+------------+",
- "| datafusion | information_schema | columns | VIEW |
|",
- "| datafusion | information_schema | tables | VIEW |
|",
- "| datafusion | public | physical | BASE TABLE |
|",
- "| datafusion | public | query | VIEW |
|",
- "| datafusion | public | temp | LOCAL TEMPORARY |
|",
-
"+---------------+--------------------+------------+-----------------+------------+",
+
"+---------------+--------------------+------------+-----------------+",
+ "| table_catalog | table_schema | table_name | table_type
|",
+
"+---------------+--------------------+------------+-----------------+",
+ "| datafusion | information_schema | columns | VIEW
|",
+ "| datafusion | information_schema | tables | VIEW
|",
+ "| datafusion | information_schema | views | VIEW
|",
+ "| datafusion | public | physical | BASE TABLE
|",
+ "| datafusion | public | query | VIEW
|",
+ "| datafusion | public | temp | LOCAL TEMPORARY
|",
+
"+---------------+--------------------+------------+-----------------+",
];
assert_batches_sorted_eq!(expected, &result);
}
@@ -273,13 +280,14 @@ async fn information_schema_show_tables() {
let result = plan_and_collect(&ctx, "SHOW TABLES").await.unwrap();
let expected = vec![
-
"+---------------+--------------------+------------+------------+------------+",
- "| table_catalog | table_schema | table_name | table_type |
definition |",
-
"+---------------+--------------------+------------+------------+------------+",
- "| datafusion | information_schema | columns | VIEW |
|",
- "| datafusion | information_schema | tables | VIEW |
|",
- "| datafusion | public | t | BASE TABLE |
|",
-
"+---------------+--------------------+------------+------------+------------+",
+ "+---------------+--------------------+------------+------------+",
+ "| table_catalog | table_schema | table_name | table_type |",
+ "+---------------+--------------------+------------+------------+",
+ "| datafusion | information_schema | columns | VIEW |",
+ "| datafusion | information_schema | tables | VIEW |",
+ "| datafusion | information_schema | views | VIEW |",
+ "| datafusion | public | t | BASE TABLE |",
+ "+---------------+--------------------+------------+------------+",
];
assert_batches_sorted_eq!(expected, &result);
@@ -542,11 +550,11 @@ async fn show_create_view() {
assert_eq!(results[0].num_rows(), 1);
let expected = vec![
- "+------+--------------------------------------+",
- "| name | definition |",
- "+------+--------------------------------------+",
- "| xyz | CREATE VIEW xyz AS SELECT * FROM abc |",
- "+------+--------------------------------------+",
+
"+---------------+--------------+------------+--------------------------------------+",
+ "| table_catalog | table_schema | table_name | definition
|",
+
"+---------------+--------------+------------+--------------------------------------+",
+ "| datafusion | public | xyz | CREATE VIEW xyz AS
SELECT * FROM abc |",
+
"+---------------+--------------+------------+--------------------------------------+",
];
assert_batches_eq!(expected, &results);
}
@@ -567,11 +575,11 @@ async fn show_create_view_in_catalog() {
assert_eq!(results[0].num_rows(), 1);
let expected = vec![
- "+----------+-------------------------------------------+",
- "| name | definition |",
- "+----------+-------------------------------------------+",
- "| test.xyz | CREATE VIEW test.xyz AS SELECT * FROM abc |",
- "+----------+-------------------------------------------+",
+
"+---------------+--------------+------------+-------------------------------------------+",
+ "| table_catalog | table_schema | table_name | definition
|",
+
"+---------------+--------------+------------+-------------------------------------------+",
+ "| datafusion | test | xyz | CREATE VIEW test.xyz AS
SELECT * FROM abc |",
+
"+---------------+--------------+------------+-------------------------------------------+",
];
assert_batches_eq!(expected, &results);
}
@@ -587,11 +595,11 @@ async fn show_create_table() {
let results = plan_and_collect(&ctx, result_sql).await.unwrap();
let expected = vec![
- "+------+------------+",
- "| name | definition |",
- "+------+------------+",
- "| abc | |",
- "+------+------------+",
+ "+---------------+--------------+------------+------------+",
+ "| table_catalog | table_schema | table_name | definition |",
+ "+---------------+--------------+------------+------------+",
+ "| datafusion | public | abc | |",
+ "+---------------+--------------+------------+------------+",
];
assert_batches_eq!(expected, &results);
diff --git a/datafusion/sql/src/planner.rs b/datafusion/sql/src/planner.rs
index 2dfd24777..9bab96927 100644
--- a/datafusion/sql/src/planner.rs
+++ b/datafusion/sql/src/planner.rs
@@ -2463,8 +2463,8 @@ impl<'a, S: ContextProvider> SqlToRel<'a, S> {
.join(" AND ");
let query = format!(
- "SELECT '{}' as name, definition FROM information_schema.tables
WHERE {}",
- table_name, where_clause
+ "SELECT table_catalog, table_schema, table_name, definition FROM
information_schema.views WHERE {}",
+ where_clause
);
let mut rewrite = DFParser::parse_sql(&query)?;
