You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@arrow.apache.org by ag...@apache.org on 2022/06/04 20:57:27 UTC
[arrow-datafusion] branch master updated: MINOR: Add datafusion-sql example (#2693)
This is an automated email from the ASF dual-hosted git repository.
agrove 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 6396ff62d MINOR: Add datafusion-sql example (#2693)
6396ff62d is described below
commit 6396ff62d6c99aaa62e1d03859343b751d4a2d13
Author: Andy Grove <ag...@apache.org>
AuthorDate: Sat Jun 4 14:57:23 2022 -0600
MINOR: Add datafusion-sql example (#2693)
---
.github/workflows/rust.yml | 2 +
datafusion/expr/src/logical_plan/builder.rs | 12 ++-
datafusion/sql/README.md | 61 +++++++++++++-
datafusion/sql/examples/sql.rs | 123 ++++++++++++++++++++++++++++
4 files changed, 195 insertions(+), 3 deletions(-)
diff --git a/.github/workflows/rust.yml b/.github/workflows/rust.yml
index a9c6effd5..1cee5139e 100644
--- a/.github/workflows/rust.yml
+++ b/.github/workflows/rust.yml
@@ -116,6 +116,8 @@ jobs:
export ARROW_TEST_DATA=$(pwd)/testing/data
export PARQUET_TEST_DATA=$(pwd)/parquet-testing/data
cargo test --features avro
+ # test datafusion-sql examples
+ cargo run --example sql
# test datafusion examples
cd datafusion-examples
cargo run --example csv_sql
diff --git a/datafusion/expr/src/logical_plan/builder.rs b/datafusion/expr/src/logical_plan/builder.rs
index b40109ebb..c7307dd3d 100644
--- a/datafusion/expr/src/logical_plan/builder.rs
+++ b/datafusion/expr/src/logical_plan/builder.rs
@@ -954,10 +954,20 @@ pub fn table_scan(
LogicalPlanBuilder::scan(name.unwrap_or(UNNAMED_TABLE), table_source, projection)
}
-struct LogicalTableSource {
+/// Basic TableSource implementation intended for use in tests and documentation. It is expected
+/// that users will provide their own TableSource implementations or use DataFusion's
+/// DefaultTableSource.
+pub struct LogicalTableSource {
table_schema: SchemaRef,
}
+impl LogicalTableSource {
+ /// Create a new LogicalTableSource
+ pub fn new(table_schema: SchemaRef) -> Self {
+ Self { table_schema }
+ }
+}
+
impl TableSource for LogicalTableSource {
fn as_any(&self) -> &dyn Any {
self
diff --git a/datafusion/sql/README.md b/datafusion/sql/README.md
index 18abeb23e..c451d4bbe 100644
--- a/datafusion/sql/README.md
+++ b/datafusion/sql/README.md
@@ -19,8 +19,65 @@
# DataFusion SQL Query Planner
-[DataFusion](df) is an extensible query execution framework, written in Rust, that uses Apache Arrow as its in-memory format.
+This crate provides a general purpose SQL query planner that can parse SQL and translate queries into logical
+plans. Although this crate is used by the [DataFusion](df) query engine, it was designed to be easily usable from any
+project that requires a SQL query planner and does not make any assumptions about how the resulting logical plan
+will be translated to a physical plan. For example, there is no concept of row-based versus columnar execution in the
+logical plan.
-This crate is a submodule of DataFusion that provides a SQL query planner.
+## Example Usage
+
+See the [examples](examples) directory for fully working examples.
+
+Here is an example of producing a logical plan from a SQL string.
+
+```rust,ignore
+fn main() {
+ let sql = "SELECT \
+ c.id, c.first_name, c.last_name, \
+ COUNT(*) as num_orders, \
+ SUM(o.price) AS total_price, \
+ SUM(o.price * s.sales_tax) AS state_tax \
+ FROM customer c \
+ JOIN state s ON c.state = s.id \
+ JOIN orders o ON c.id = o.customer_id \
+ WHERE o.price > 0 \
+ AND c.last_name LIKE 'G%' \
+ GROUP BY 1, 2, 3 \
+ ORDER BY state_tax DESC";
+
+ // parse the SQL
+ let dialect = GenericDialect {}; // or AnsiDialect, or your own dialect ...
+ let ast = Parser::parse_sql(&dialect, sql).unwrap();
+ let statement = &ast[0];
+
+ // create a logical query plan
+ let schema_provider = MySchemaProvider::new();
+ let sql_to_rel = SqlToRel::new(&schema_provider);
+ let plan = sql_to_rel.sql_statement_to_plan(statement.clone()).unwrap();
+
+ // show the plan
+ println!("{:?}", plan);
+}
+```
+
+This is the logical plan that is produced from this example. Note that this is an **unoptimized**
+logical plan. The [datafusion-optimizer](https://crates.io/crates/datafusion-optimizer) crate provides a query
+optimizer that can be applied to plans produced by this crate.
+
+```
+Sort: #state_tax DESC NULLS FIRST
+ Projection: #c.id, #c.first_name, #c.last_name, #COUNT(UInt8(1)) AS num_orders, #SUM(o.price) AS total_price, #SUM(o.price * s.sales_tax) AS state_tax
+ Aggregate: groupBy=[[#c.id, #c.first_name, #c.last_name]], aggr=[[COUNT(UInt8(1)), SUM(#o.price), SUM(#o.price * #s.sales_tax)]]
+ Filter: #o.price > Int64(0) AND #c.last_name LIKE Utf8("G%")
+ Inner Join: #c.id = #o.customer_id
+ Inner Join: #c.state = #s.id
+ SubqueryAlias: c
+ TableScan: customer projection=None
+ SubqueryAlias: s
+ TableScan: state projection=None
+ SubqueryAlias: o
+ TableScan: orders projection=None
+```
[df]: https://crates.io/crates/datafusion
diff --git a/datafusion/sql/examples/sql.rs b/datafusion/sql/examples/sql.rs
new file mode 100644
index 000000000..2916fa802
--- /dev/null
+++ b/datafusion/sql/examples/sql.rs
@@ -0,0 +1,123 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+use arrow::datatypes::{DataType, Field, Schema};
+use datafusion_common::{DataFusionError, Result};
+use datafusion_expr::{
+ logical_plan::builder::LogicalTableSource, AggregateUDF, ScalarUDF, TableSource,
+};
+use datafusion_sql::{
+ planner::{ContextProvider, SqlToRel},
+ TableReference,
+};
+use sqlparser::{dialect::GenericDialect, parser::Parser};
+use std::{collections::HashMap, sync::Arc};
+
+fn main() {
+ let sql = "SELECT \
+ c.id, c.first_name, c.last_name, \
+ COUNT(*) as num_orders, \
+ SUM(o.price) AS total_price, \
+ SUM(o.price * s.sales_tax) AS state_tax \
+ FROM customer c \
+ JOIN state s ON c.state = s.id \
+ JOIN orders o ON c.id = o.customer_id \
+ WHERE o.price > 0 \
+ AND c.last_name LIKE 'G%' \
+ GROUP BY 1, 2, 3 \
+ ORDER BY state_tax DESC";
+
+ // parse the SQL
+ let dialect = GenericDialect {}; // or AnsiDialect, or your own dialect ...
+ let ast = Parser::parse_sql(&dialect, sql).unwrap();
+ let statement = &ast[0];
+
+ // create a logical query plan
+ let schema_provider = MySchemaProvider::new();
+ let sql_to_rel = SqlToRel::new(&schema_provider);
+ let plan = sql_to_rel.sql_statement_to_plan(statement.clone()).unwrap();
+
+ // show the plan
+ println!("{:?}", plan);
+}
+
+struct MySchemaProvider {
+ tables: HashMap<String, Arc<dyn TableSource>>,
+}
+
+impl MySchemaProvider {
+ fn new() -> Self {
+ let mut tables = HashMap::new();
+ tables.insert(
+ "customer".to_string(),
+ create_table_source(vec![
+ Field::new("id", DataType::Int32, false),
+ Field::new("first_name", DataType::Utf8, false),
+ Field::new("last_name", DataType::Utf8, false),
+ Field::new("state", DataType::Utf8, false),
+ ]),
+ );
+ tables.insert(
+ "state".to_string(),
+ create_table_source(vec![
+ Field::new("id", DataType::Int32, false),
+ Field::new("sales_tax", DataType::Decimal(10, 2), false),
+ ]),
+ );
+ tables.insert(
+ "orders".to_string(),
+ create_table_source(vec![
+ Field::new("id", DataType::Int32, false),
+ Field::new("customer_id", DataType::Int32, false),
+ Field::new("item_id", DataType::Int32, false),
+ Field::new("quantity", DataType::Int32, false),
+ Field::new("price", DataType::Decimal(10, 2), false),
+ ]),
+ );
+ Self { tables }
+ }
+}
+
+fn create_table_source(fields: Vec<Field>) -> Arc<dyn TableSource> {
+ Arc::new(LogicalTableSource::new(Arc::new(
+ Schema::new_with_metadata(fields, HashMap::new()),
+ )))
+}
+
+impl ContextProvider for MySchemaProvider {
+ fn get_table_provider(&self, name: TableReference) -> Result<Arc<dyn TableSource>> {
+ match self.tables.get(name.table()) {
+ Some(table) => Ok(table.clone()),
+ _ => Err(DataFusionError::Plan(format!(
+ "Table not found: {}",
+ name.table()
+ ))),
+ }
+ }
+
+ fn get_function_meta(&self, _name: &str) -> Option<Arc<ScalarUDF>> {
+ None
+ }
+
+ fn get_aggregate_meta(&self, _name: &str) -> Option<Arc<AggregateUDF>> {
+ None
+ }
+
+ fn get_variable_type(&self, _variable_names: &[String]) -> Option<DataType> {
+ None
+ }
+}