Accio: Query Your Data Warehouse Like Exploring One Big View

Transcript

1. Accio Query Your Data Warehouse Like Exploring One Big View https://getaccio.ai/ #MIT #OSS
2. Cooper • • • •
3. •This is an introduction of Accio •The inspiration behind Accio •An overview of the benefits of Accio •Some simple example of it in use •An overview of Accio architecture •ChatGPT Plugin About this presentation
4. What is Accio ?
5. The inspiration behind Accio
6. © 2023 Canner, Inc. Data development at enterprise scale is really hard... Dimension Table Fact Table ERF 3rd Party Data Fact Table Wide Dimension Table Superset Tableau BI Tools… Logging DB Exports Dimension Table Raw Data Core Data Tables Increasingly Wider, More Complex Derived Tables Downstream Tools Fact Table with Many Dimensions Fact Table with Even Many Dimensions Fact Table with Dimensions …
7. © 2023 Canner, Inc. Data development at enterprise scale is really hard... The need for data development: ØSimplifying Complex Databases ØImproving Collaboration ØAdapting to Changes
8. © 2023 Canner, Inc. Data Development
9. © 2023 Canner, Inc. Web Development
10. © 2023 Canner, Inc. GraphQL: Facilitating Collaboration • Single endpoint to access data, One Request
11. © 2023 Canner, Inc. GraphQL: Facilitating Collaboration • Only the fields that are asked
12. © 2023 Canner, Inc. What about using the concept of GraphQL in data development ? Dimension Table Fact Table ERF 3rd Party Data Fact Table Wide Dimension Table Superset Tableau BI Tools… Logging DB Exports Dimension Table Raw Data Core Data Tables Increasingly Wider, More Complex Derived Tables Downstream Tools Fact Table with Many Dimensions Fact Table with Even Many Dimensions Fact Table with Dimensions …
13. © 2023 Canner, Inc. Accio • Model Definition Language • Model/Metric/Relation/View • Accio Query • Syntactic sugar • Close to standard SQL • Lambda/Array function
14. © 2023 Canner, Inc. Model @sql @expr @primaryKey Treat every model like a normal table
15. © 2023 Canner, Inc. Relation Supplier à Lineitem Lineitem à Supplier
16. © 2023 Canner, Inc. Query to-1 relation Accio Query Standard Query Lineitem Supplier Nation N:1 N:1
17. © 2023 Canner, Inc. Query to-N relation Accio Query Lineitem Supplier 1:N Standard Query
18. © 2023 Canner, Inc. Query to-N relation with array/lambda function Accio Query Lineitem Supplier 1:N Find the total revenue from all suppliers after the ship date of 1995-04-01 Just like functional programming
19. © 2023 Canner, Inc. Metric @preAgg: store in duckdb @model @measure @dim @time_grain Use roll_up to group by orderdate_grain by YEAR
20. © 2023 Canner, Inc. View Note that the order of views matters !
21. © 2023 Canner, Inc. Data Source Pre-Aggregation Accio Architecture Wire Protocol § pg message flow § bunch of pg tables Interface Layer SQL Rewrite Translate SQL to target dialect Check if all references is cached BI Tools… JDBC PSQL
22. © 2023 Canner, Inc. Accio Interface Layer Wire Protocol § pg message flow § bunch of pg tables Interface Layer https://www.youtube.com/watch?v=zX4NpklUZjI
23. © 2023 Canner, Inc. Metabase
24. © 2023 Canner, Inc. PSQL
25. © 2023 Canner, Inc. Interesting part: ChatGPT Plugin
26. •Structured Model Definition Language •On-Demand SQL Generation •Consistency and Reusability • Metric definition and model expression •Metric pre-aggregation in DuckDB •PG Wire Protocol Recap
27. © 2023 Canner, Inc. https://getaccio.ai/ twitter: @getaccio
28. © 2023 Canner, Inc. References • https://help.tableau.com/current/online/en-us/datasource_datamodel.htm • https://www.linkedin.com/pulse/headless-bi-flexible-scalable-approach-business-jarred- armstrong/ • https://docs.snowflake.com/en/user-guide/sample-data-tpch • https://medium.com/airbnb-engineering/how-airbnb-achieved-metric-consistency-at- scale-f23cc53dea70
29. © 2023 Canner, Inc. What about using BI ? • Performance limitation: Pull all specified tables to establish semantic model • Direct query mode: Can't apply semantic model • What about data sharing ?
30. © 2023 Canner, Inc. TPCH Schema