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
+    }
+}