Working with Databricks and Cube is a great way to ensure your data engineering team can build and maintain a [unified semantic layer](https://cube.dev/use-cases/semantic-layer) for your organization. At a higher level, it is a better means to make cloud data accessible and consistent to every data consumer in and outside of your company. 

By combining Databricks’ unified analytics and data store with Cube’s data modeling, access control, caching, and APIs, it’s now possible to deliver consistent metrics securely to business intelligence tools, embedded analytics use cases, and AI agents.

## Why a Semantic Layer?

A semantic layer functions as a middleware component between all of your data sources, including cloud-based data lakehouses, such as Databricks, and downstream applications. Its primary role is to abstract complicated physical data models and present them in a more user-friendly manner with improved field labeling, field descriptions, and dynamically calculated metrics. It serves as a contextual bridge between the data sources and the diverse analytical tools businesses employ for data manipulation, slicing, dicing, and analysis.

![](https://ucarecdn.com/5bcadadb-60ee-4698-bc6e-1f1e2ad8aa71/)

## Why Cube and Databricks?

**Unify your data stack**. Databricks established itself as a high-performance Lakehouse for a **modern data stack**, unifying all data types. Combined with Cube, you will have consistently modeled metrics and performant queries for all your downstream data consumers. 

**Modern data stack** follows several foundational principles and architecture designs: 
* Using a Lakehouse like Databricks as a central data platform
* ELT vs. [ETL](https://www.databricks.com/glossary/extract-transform-load)
* Applying software engineering best practices to data management 

The Cube platform embodies software engineering best practices, empowering data engineers to [handle data models through code](https://cube.dev/docs/product/getting-started/databricks/create-data-model). This capability unlocks the potential to harness version control systems for secure data model evolution management, fostering collaborative efforts and streamlined code reviews. Additionally, it facilitates the integration of a semantic layer into the CI/CD pipeline and the creation of distinct development, staging, and production environments.

**Harness the capabilities and scalability offered by Databricks with Cube.** Cube seamlessly integrates with Databricks, enabling direct queries without the need for data extraction. This ensures that your data remains within the Databricks environment. Cube optimizes performance by leveraging the computing power of your Databricks instance, fully maximizing the scalability of your cloud data lakehouse. Additionally, you have the option to create [pre-aggregations](https://cube.dev/docs/product/caching/getting-started-pre-aggregations) within Cube, reducing the need for small, repetitive queries on your Databricks instance and allowing for background aggregate updates instead. Our aggregate-aware pre-aggregations are fundamentally different from common table or view materializing strategies often employed for performance reasons. With Cube pre-aggregations, users do not need to be aware of objects materialized for performance reasons; instead, they simply issue their queries, and Cube automatically chooses the optimal way to answer, from cache, from a pre-aggregate, or the source.

**Use your Databricks Lakehouse data in any application.** Cube supports your current and future use cases with first-class application programming interfaces (APIs). In addition to our SQL API, Cube also speaks REST and GraphQL, which are often preferred by application developers who are working with common front-end frameworks to develop embedded analytic applications. With Cube, you can take your investment in your Databricks ecosystem and build anything you can dream of.

## A step-by-step guide to building semantic layer with Cube and Databricks

In the below section, we’ll cover a quickstart step-by-step tutorial on how to start with Cube and Databricks. Remember: Cube Cloud is free to start (there is a sign-up button, up and to the right^). No credit card or calls with salespeople are required! :) 

Find a detailed getting started with [Cube Cloud and Databricks](https://cube.dev/docs/product/getting-started/databricks) guide in Cube documentation. 

### Create Databricks and Cube accounts
First, create Cube Cloud and Databricks accounts.

If you don’t have a dataset that you would like to use, you can follow [this guide](https://cube.dev/docs/product/getting-started/databricks/load-data) to create a test dataset in your Databricks instance.

### Connect Databricks to Cube

Please follow these steps to create your first deployment in Cube Cloud and connect it to your Databricks instance.

1. After you [sign in to your Cube Cloud account](https://cubecloud.dev/auth), click “Create Deployment”.

2. Give the deployment a name, select the cloud provider and region of your choice, and click “Next”.

3. Next, click Create to create a new project from scratch.

4. Select Databricks from the list of supported data sources and enter your Databricks credentials to connect to it. Get a [free Databricks account](https://www.databricks.com/try-databricks?utm=cube#account)

5. Once Databricks is connected, Cube can generate a basic data model from your data warehouse schema, which helps you get started with data modeling faster. We'll inspect these generated files in the next section and start changing them.

### Create your data model
Cube follows a dataset-oriented data modeling approach, which is inspired by and expands upon dimensional modeling. 

When building a [data mode](https://cube.dev/docs/product/getting-started/databricks/create-data-model) in Cube, you work with two dataset-centric objects: cubes and views. Cubes usually represent business entities such as customers, line items, and orders. In cubes, you define all the calculations within the measures and dimensions of these entities. 

To begin building your data model, click on “Enter Development Mode” in Cube Cloud. This will take you to your personal developer space.

Once you are in developer mode, navigate to the “Data Model” and click on any file located in the left sidebar to open it.

You can follow [this detailed guide](https://cube.dev/docs/product/getting-started/databricks/create-data-model) on how to make changes to your data model and build new measures and dimensions.

When you are ready to test updates to your data model, you can navigate to [Cube’s Playground](https://cube.dev/docs/product/workspace/playground). The Playground is a web-based tool that allows you to query your data without connecting any tools or writing any code.

### Query your data
You can query Cube using a BI or visualization tool through the Cube SQL API. For a better experience, we recommend mapping the BI's data model to Cube's semantic layer. This can be done automatically with Semantic Layer Sync or manually.

**Semantic Layer Sync**
[Semantic Layer Sync](https://cube.dev/docs/product/getting-started/databricks/query-from-bi) will synchronize all public cubes and views with connected BI tools.
1. Create a new sync by navigating to the “Semantic Layer Sync” tab on the “BI Integrations” page and clicking “+ Create Sync”. 
2. Follow the steps in the wizard to create a sync
3. Replace the fields for user, password, and URL with your BI tool credentials, then click on “Save All”. You can now go to the “BI Integrations” page and trigger the synchronization of your newly created semantic layer.
4. After running the sync, navigate to your BI tool instance. You should see the orders_view dataset that was created in the tool. Cube automatically maps all metrics and dimensions in the BI tool to measures and dimensions in the Cube data model.

**Manual setup**
Alternatively, you can connect to Cube and create all the mappings manually. 
1. You can find the credentials to connect to Cube on the “BI Integrations” page under the “SQL API Connection” tab.
2. After connecting, create a new dataset in the BI tool and select "orders_view" as a table.
3. Now you can map the BI tool metrics and columns to Cube's measures and dimensions.

That was a high level preview. You can create both you Databricks and Cube instances for free to try it out in your own environment.

Great work!

### More resources

Recorded workshop [Getting Started with Cube on Databricks](https://cube.dev/events/unlock-data-cube-snowflake)

Detail on [connecting to Databricks](https://cube.dev/docs/product/getting-started/databricks)


Artyom Keydunov

Deep Dives

Getting Started with a Databricks Semantic Layer

Today, we're happy to announce Cube's integrations with [DuckDB](https://duckdb.org), an extremely potent in-process SQL OLAP database management system, also known as "the most hyped thing in data in 2023 prior to the AI craze", and [MotherDuck](https://motherduck.com), a cloud service providing hybrid execution for DuckDB, i.e., offloading some computations to the cloud and merging the results locally.

We see vast advantages for users of Cube, the semantic layer, and DuckDB, the super-fast OLAP database, in using them together. Let's unpack the use cases and explore the benefits.

## Built-in query engine

Since DuckDB is an in-process OLAP DBMS, the integration with Cube implies bundling DuckDB together with Cube. Indeed, now every Cube Core or [Cube Cloud](https://cube.dev/product/why-cube-cloud) deployment comes with a built-in and instantly available DuckDB which has the [HTTPFS](https://duckdb.org/docs/extensions/httpfs) extension installed and loaded by default. With this extension, DuckDB can directly query Parquet, CSV, and JSON files over HTTPS, including files on S3-compatible object storage servers like AWS S3, Google Cloud Storage, and Cloudflare R2.

![](https://ucarecdn.com/d3e42677-83ce-4ad8-8ac9-c39d3b74e252/)

With built-in DuckDB, your semantic layer now has direct access to semi-structured data in a data lake or object storage without the need for any intermediary tools, e.g., an additional [query engine](https://cube.dev/docs/product/configuration/data-sources#query-engines) between your data and your semantic layer. You still can do this in complex scenarios, but DuckDB helps get you started in almost no time.

![](https://ucarecdn.com/4fbb7247-379c-48a3-b298-0d87922e2531/)

You can also connect DuckDB to MotherDuck and get hybrid execution for your queries, so that MotherDuck will do all the heavy lifting querying the data from remote storage.

## Value-add semantic layer

In lots of excitement about DuckDB, one could always spot a bit of scepticism about its intended production use. No doubt DuckDB is a fantastic and super-fast tool for local data exploration and experimentation—but what should be the next step to share the results with the team or stakeholders and put the insights into use? (We know one can always send an Excel sheet over email but it's so ad-hoc and so *not* 2023, right?)

Now, you can go from a quick analysis in a local DuckDB instance accessible to you and you only to a governed metric in a semantic layer accessible across the whole organization in a single step. As soon as you're happy with the metric definition and the results it yields, you can move the definition into Cube's code-first data model. It will take seconds to enter the [development mode](https://cube.dev/docs/product/workspace/data-model), update the data model, test the result, and roll out an updated metric into production without any disruption to existing data consumers.

<video width="100%" controls><source src="https://ucarecdn.com/27491295-18eb-4d79-9b12-2db055a1b43f/video.mp4" type="video/mp4"/></video>

In the demo video above, a Parquet file with 16 million rows and 250 MB in size, residing in a Google Cloud Storage bucket, is queried using a local DuckDB (left) and MotherDuck (right). You can see that MotherDuck drastically outperforms local DuckDB when querying remote files. You can also see that moving from an analytical query against DuckDB or MotherDuck to a metric defined in the semantic layer and available to all data consumers is a matter of seconds.

## Next steps

Just like the MotherDuck team, we'd love to see how you'd put the power of DuckDB and MotherDuck into a production use with Cube:

<Quote authorName="Ryan Boyd" authorAvatar="https://ucarecdn.com/fde84267-5173-4577-80a4-b9291f205f3e/" authorRole="Co-Founder at MotherDuck">
Pairing the universal semantic layer of Cube with the hybrid execution of MotherDuck allows users to easily go from local development to building code-first data models and consistent metrics that keep everyone in an organization on the same page.
</Quote>

**In October, we’re co-hosting a webinar with the MotherDuck team.** Please stay tuned for the webinar registration; we'd love to show you live how Cube and DuckDB work together.

**DuckDB and MotherDuck integrations are available in Cube Core and Cube Cloud, and you can [try them today](https://cubecloud.dev/auth/signup).** Join our Slack community at [slack.cube.dev](https://slack.cube.dev) to share your thoughts and opinions about this update.

Igor Lukanin

Query Engines

Semantic layer meets a duck on top of a data lake

Introducing DuckDB and MotherDuck integrations

Today, we're happily announcing that Cube now works with [Trino](https://trino.io), the fast distributed SQL query engine for big data analytics, formerly known as PrestoSQL.

> [Meet the Cube team at Trino Summit](https://cube.dev/events/cube-at-trino-summit) on November 10, 2022. We'll be happy to chat and explore how Cube can augment your Trino experience.

## What is Trino?

Trino is an [open-source](https://github.com/trinodb/trino) query engine designed to work with big data.

It's fast, scalable, SQL-compliant, and has almost universal connectivity to all kinds of data sources and business intelligence tools. Let's explore these in more detail:

* **Scale.** Trino is capable of querying exabyte-scale data lakes and massive data warehouses, as confirmed by [Trino installations](https://trino.io/users.html) at companies such as LinkedIn, Netflix, and Shopify.
* **Performance.** Trino is a highly parallel and distributed query engine with two types of [cluster nodes](https://trino.io/docs/current/overview/concepts.html#server-types): coordinator nodes, responsible for query planning and execution, and worker nodes, responsible for fetching and processing data. You can fine-tune your Trino cluster to allow for efficient, low latency analytics.
* **Connectivity.** Trino can natively query data from a plethora of [data sources](https://trino.io/docs/current/connector.html) without the need to extract and load the data into a data warehouse, even if you'd like to do a cross-database join. Also, Trino is an ANSI SQL-compliant query engine that works with BI tools such as Tableau, Power BI, and Superset as well as headless BI tools such as Cube.

Most common use cases for Trino include ad-hoc analytics at interactive speeds, massive multi-hour batch queries, and high volume apps that perform sub-second queries.

Trino was originally [designed and developed at Facebook](https://trino.io/blog/2020/12/27/announcing-trino.html) in 2013 and beared the name *Presto* at that time. In 2019, Presto development forked and two query engines emerged: PrestoDB maintained by Facebook and PrestoSQL maintained by the Presto Software Foundation and the original creators. Later in 2020, PrestoSQL was renamed to Trino.

If you're not a Trino user yet, consider [self-hosting](https://trino.io/docs/current/installation.html) or using a managed platform provided by [Starburst](https://www.starburst.io) or AWS (as [Amazon Athena](https://aws.amazon.com/athena/), serverless interactive query service). 

## What is Cube?

Cube is the [headless BI](https://cube.dev/blog/headless-bi) platform for accessing data from modern data stores (including Trino), organizing it into consistent metrics definitions, and delivering them to downstream applications.

![Cube schema](https://ucarecdn.com/5f6287b5-fd33-47c8-95be-2dffd84ccfe2/)

Cube is designed to take the central part in the data pipeline, delivering consistent data to all downstream teams and data consumers. It serves as a source of truth for the metrics definitions, access control rules, and caching settings. Regardless of how many data consumers you have (e.g., front-end applications with embedded analytics or BI tools), Cube will deliver consistent data to all of them with its [REST API](https://cube.dev/docs/rest-api), [GraphQL API](https://cube.dev/docs/backend/graphql), or [SQL API](https://cube.dev/docs/backend/sql).

## How Cube works with Trino

In a typical data pipeline, Trino is placed upstream of Cube, providing unrestricted access to all data sources as well as data federation capabilities. You can use Cube to create an additional [semantic layer](https://cube.dev/blog/open-source-data-modeling) or a last-mile [caching layer](https://cube.dev/docs/caching) on top of Trino.

More importantly, you can use the set of APIs that Cube provides, including REST API and GraphQL API, to deliver the data directly to custom-built front-end applications, retaining low latency and high concurrency.

![Cube and Trino architecture](https://ucarecdn.com/cc996b6f-5f7d-4efb-83e6-960fc63546cd/)

Also, you can leverage Cube's [multitenancy](https://cube.dev/docs/product/configuration/advanced/multitenancy) and [access control](https://cube.dev/docs/security) features to fully control and customize how every user, role, or data consumer accesses the data in Trino and underlying data sources.

## Try Cube with Trino

Cube is [open source](https://github.com/cube-js/cube.js), so you can feel free to [self-host and deploy](https://cube.dev/docs/deployment/overview) it. You can also run fully managed Cube in [Cube Cloud](https://cube.dev/cloud) which is the fastest and most convenient way to use Cube.

Start by signing up for a free [Cube Cloud account](https://cubecloud.dev/auth/signup). You'll be prompted to select the cloud provider (AWS, GCP, or Azure) and a region for your deployment:

![Cube Cloud: 1st step](https://ucarecdn.com/7ae7b4ae-57a1-4fb9-9e3d-bfa3fc011a67/)

Then, pick Trino from the data source options:

![Cube Cloud: 2nd step](https://ucarecdn.com/9dcf732a-eb10-4be9-bb8b-685e9060c5f0/)

Lastly, provide the credentials for the Trino connection:

![Cube Cloud: 3rd step](https://ucarecdn.com/aa796b76-ee85-4aae-ad66-c2a7e8ced05a/)

In a few seconds, you'll get your Cube Cloud deployment up and running, ready to query your Trino installation and deliver data to downstream applications.

## What's next?

Would you like to learn more about Cube and what it brings to the table? [Check the docs](https://cube.dev/docs/) and create a free [Cube Cloud account](https://cubecloud.dev/auth/signup) today.

Also, please feel free to [join our community](https://slack.cube.dev/) of almost 7000 data practitioners on Slack, give Cube a [star on GitHub](https://github.com/cube-js/cube.js), or [schedule a time](https://cube.dev/contact) to chat 1:1.

Trino

Perform interactive big data analytics with a fast and distributed query engine.

Announcing the Cube integration with Trino, the SQL query engine for big data 

Today, we’re announcing our official integration with [Databricks][1]. Here’s how it works—and what it means.

## What is Databricks?

Databricks is a unified, open, web-based platform for working with Spark. Its multicloud infrastructure provides automated cluster management and IPython-style notebooks to unify data warehousing, AI, and analytics in a single platform. 

The Databricks [Data Lakehouse][2] brings the best of both worlds: the reliability, governance, and performance of data warehouses with the openness, flexibility, and ML capabilities of data lakes. The Data Lakehouse efficiently handles all data types while applying a consistent security and governance approach. 

Unifying data in a single platform fulfills the needs of all your data teams—engineering, analytics, BI, data science, and ML—and beautifully simplifies your modern data stack. 

## What is Cube?

[Cube][3]’s universal semantic layer makes lakehouse data consistent, performant, and accessible to every downstream application. We do this with [data modeling that defines your metrics up-stack][4]. 

Cube’s advanced [pre-aggregation][5] capabilities powerfully condense frequently-used queries to optimize response times. Cube then provides this data to every application with GraphQL, REST, and SQL APIs so that every tool—and every data consumer—can work with the same data.

![cube-schema][30]

## The Cube and Databricks Integration

By combining Databricks’ unified analytics and data store with Cube’s data modeling, access control, caching, and APIs, it’s now possible to build a new dashboard or embed analytics features into customer-facing apps in minutes—not weeks. 

Select Cube and Databricks users already have been enjoying this integration in production for use cases like creating dashboards for internal analytics. Here’s one example: by consuming operational data with Kafka, transforming it into insights with Databricks, then using Cube as an API and caching layer to power visualization layers, users have been able to find actionable insights and identify opportunities to optimize their operations.

It’s easy to connect a workspace to Cube using Databricks’ SQL endpoint. Here’s how.

For starters, we recommend using Cube with the [Databricks SQL Endpoint][6]. Follow [Databricks’ docs to create a SQL Endpoint][7]. 

### Creating a new SQL endpoint

 - Log in to your Databricks Workspace account.
 - Navigate to the **SQL Console** on the left hand bar and select **SQL** from the drop-down menu.

![select-sql-tab-in-databricks][17]

 - Click __API Endpoints__, and then on the top right, select __+New SQL Endpoint.__

![click-API-endpoint][18]
![create-new-SQL-endpoint][19]

 - Next, name your endpoint and choose a __Cluster Size__. Make sure to set the __Auto Stop__ to at least 60-minutes of no activity, then click __Create__.

![name-new-endpoint][20]

After you’ve configured data access on the SQL Endpoint, it’s time to get your credentials for the SQL Endpoint. 

 - In the __Databricks SQL Console__, go to __Endpoints__ and click on the endpoint you created.

![go-to-endpoints-in-console][21]
![click-on-endpoint-created][22]

 - Navigate to __Connection Details__ and copy the HTTP Path, Port, and Server Hostname:

![connection-details][23]

Once you have your credentials, [generate a Personal Token][8]. 

 - Navigate to the __Admin Console__, click on __Settings__, and select __User Settings__.

![user-settings][24]

 - Toggle over __Personal Access Tokens__ and click on __+Generate New Token__.

![personal-access-token][25]

 - Name your token and set the expiration to __“Lifetime”__, then click __Generate__.

![name-your-token][26]

 - Copy the generated JDBC URL.
 - Attach your newly-generated personal token to the JDBC URL you copied in step 6, e.g. `jdbc:spark://dbc-xxxxxxxx-3957.cloud.databricks.com:443:443/default;transportMode=http;ssl=1;AuthMech=3;httpPath=/sql/1.0/endpoints/0b27a1dfc18f82ae;UID=token;PWD=PERSON-TOKEN`
 - Finally, select the database you’d like to query with Cube. In our example, we’ll use “default”.

![select-database][27]

### Connecting Databricks to Cube

 - Sign into [Cube Cloud][9] and use the wizard to create a new deployment. Select Databricks from the database connection list.

![sign-into-Cube-Cloud][28]

 - Enter your Databricks JDBC URL and personal token as well as your database name to connect.

![databricks-jdbc-url][29]

 - If needed, copy Cube Cloud’s IP addresses and add them to your Databricks allow-list. ([Databricks’ documentation for IP access lists is here][10].)

That’s it! Now you are ready to use Cube on top of your Databricks to model your data, manage security rules, apply caching, and expose your data definitions to any downstream application via Cube’s REST, GraphQL, or SQL APIs. 

You can check out [more examples of integrations with visualization tools on our website][11].

## Try the Cube and Databricks Integration for yourself

To follow the tutorial above and experience everything this integration offers, check out our [docs][12]—and, if you don’t already have one, sign up for [a free Cube Cloud account][13].

## Tell us what you think

We’d love to hear from you, learn what you’re building, and strategize how to help. Find us via Cube’s [Slack][14] and [GitHub][15] communities—or [schedule a time][16] to chat 1:1.

[1]: https://databricks.com/
[2]: https://databricks.com/product/data-lakehouse
[3]: https://cube.dev/
[4]: https://cube.dev/blog/headless-bi
[5]: https://cube.dev/docs/schema/reference/pre-aggregations
[6]: https://docs.databricks.com/sql/admin/sql-endpoints.html
[7]: https://docs.databricks.com/sql/admin/sql-endpoints.html#create-a-sql-endpoint
[8]: https://docs.databricks.com/administration-guide/access-control/tokens.html#enable-or-disable-token-based-authentication-for-the-workspace
[9]: https://cube.dev/cloud
[10]: https://docs.databricks.com/security/network/ip-access-list.html#add-an-ip-access-list
[11]: https://cube.dev/examples
[12]: https://cube.dev/docs/config/databases/databricks/jdbc#databricks-jdbc
[13]: https://cube.dev/cloud/
[14]: https://slack.cube.dev/
[15]: https://github.com/cube-js/cube.js
[16]: https://cube.dev/contact
[17]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/39cfa0b3-c6b9-44dd-80a2-41bae6dea1a3.png
[18]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/38cfe617-ae91-4a85-a610-12eaba6c9dfe.png
[19]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/02a29df7-26dd-41ba-a442-7fe46cf58246.png
[20]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f64ca517-e89e-437b-92e2-1c48752b8ca4.png
[21]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/5b5644d7-d5e1-4bff-87cd-abf40bbe459d.png
[22]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f8bd03ad-4edf-4452-a866-9d3c3140dc42.png
[23]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/6acf6cd1-0423-45f9-8bb2-884e3115e689.png
[24]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/8fd9c163-ebae-4b1e-b846-6178df628781.png
[25]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/7877ce4f-0e2d-49c1-97f9-56769fef46c7.png
[26]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9dd552af-3dd4-40b4-9b9a-b3fd6c2bc8d2.png
[27]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/580253c6-1077-4291-a4a3-13303d383cda.png
[28]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/cd5ae87c-f9d0-432b-9a98-b378580d8d1d.png
[29]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/7f3f30a5-33a4-4073-8669-08bc7082c651.png
[30]: https://cubedev-blog-images.s3.us-east-2.amazonaws.com/e758755f-d633-45da-8ae3-92bc510cc9a1.png
[31]:https://databricks.com/dataaisummit/?itm_data=mainnav-promo-dais2022
[32]:https://cube.dev/events/data-ai-summit/

Tamar Barlev

News

Announcing the Cube and Databricks integration

Bartosz Mikulski

Ahana

See how Ahana and Cube work together to help you set up a Presto cluster and build a single source of truth for metrics without spending days reading cryptic docs.

Cube integration with Ahana: Querying multiple data sources with managed Presto and Cube

About 6 months ago, we witnessed what Andy Pavlo [wittily characterised](https://twitter.com/andy_pavlo/status/1459251061085118465) as an "old school database benchmark gang war". The timeline of events goes like this:

* [Databricks](https://databricks.com), a data lakehouse company founded by the creators of Apache Spark, published a [blog post](https://databricks.com/blog/2021/11/02/databricks-sets-official-data-warehousing-performance-record.html) claiming that it set a new data warehousing performance record in 100 TB [TPC-DS benchmark](https://www.tpc.org/tpcds/). It was also mentioned that Databricks was 2.7x faster and 12x better in terms of price performance compared to Snowflake.
* [Snowflake](https://snowflake.com), a data warehousing company founded by ex-Oracle and ex-VectorWise experts, responded with a [blog post](https://www.snowflake.com/blog/industry-benchmarks-and-competing-with-integrity/) that critically reviewed Databricks' findings, reported different results for the same benchmark, and claimed comparable price/performance to Databricks.
* After two days, Databricks followed up with another [blog post](https://databricks.com/blog/2021/11/15/snowflake-claims-similar-price-performance-to-databricks-but-not-so-fast.html) that confirmed that they stand by the initially reported results.

While this back-and-forth between respected data vendors was informative and amusing, its collateral damage was even more substantial. Databricks shared that they have [eliminated the anti-competitive *DeWitt clause*](https://databricks.com/blog/2021/11/08/eliminating-the-dewitt-clause-for-database-benchmarking.html) from their service terms. Snowflake followed up with a [similar update](https://www.snowflake.com/blog/industry-benchmarks-and-competing-with-integrity/) to their acceptable use policy.

But what is the *DeWitt clause*?

More importantly, why would you need to know if a DeWitt clause is included in terms of service in case you'd like to do and publish a performance benchmark? Let's explore.

## Backstory of the DeWitt Clause

In 1982, at the very inception of relational databases, [David DeWitt](https://www.linkedin.com/in/david-dewitt-30189b50/), a researcher at the Department of Computer Sciences at the University of Wisconsin-Madison, was working on measuring database performance. His team wrote the Wisconsin Benchmark, tested a number of databases, and [published the results](https://pages.cs.wisc.edu/~dewitt/includes/benchmarking/vldb83.pdf).

Apparently, some folks were displeased with the results. [Michael Stonebraker](https://en.wikipedia.org/wiki/Michael_Stonebraker), a researcher at the UC Berkeley and co-creator of Ingres, called DeWitt and expressed how upset he was. However, they remained friends anyway as DeWitt recounts: in his [talk on YouTube](https://www.youtube.com/watch?v=-TIUGC4X2q8&t=420s):

<iframe width="800" height="500" src="https://www.youtube.com/embed/-TIUGC4X2q8?start=420" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>

Oracle also didn’t perform very well and reacted hastily. [According to DeWitt](https://www.eweek.com/development/db-test-pioneer-makes-history/), Oracle CEO Larry Ellison was so displeased that he called the department chair and insisted, "You have to fire this guy.” Oracle also inserted a clause in their terms of use that boiled down to the fact that one can’t publish benchmarks without getting an explicit approval from Oracle.

## DeWitt Clause vs. DeWitt Embrace Clause

Nowadays, the DeWitt clause is a common provision in end-user license agreements for proprietary software that prevents from publishing information about the software without an explicit approval from the vendor. Over time, it have become widespread in the software industry and its implications are drastic:

* People avoid publishing benchmarks about software products with the DeWitt clause.
* Moreover, some won’t even bother doing a benchmark knowing they won’t be able to publish the results.

There are debates whether DeWitt clauses are [moral or legal](https://www.dwheeler.com/essays/dewitt-clause.html) because of the implications above as well as the overall corrosive effect they have on the industry. Once one supplier adds a DeWitt clause, the others can feel that they are at a disadvantage without one. We're lucky it can also work the other way round, like in the Databricks and Snowflake case. However, open source software (OSS) licenses don’t and can't have DeWitt clauses, meaning that the OSS can be legally critiqued, but not their proprietary competitors.

At the same time, some vendors do quite the opposite to the inclusion of a DeWitt clause. Instead, they change their license to the DeWitt Embrace Clause meaning that if you disclose benchmark results for them, you automatically allow them to do the same with you. In a way, DeWitt Embrace Clause is like copyleft for the DeWitt Clause. 

## Can You Benchmark %DATABASE%?

Now, do you wonder whether some vendor has or hasn't a DeWitt clause?

Shortly after the Databricks and Snowflake debate, [Mark Callaghan](https://twitter.com/MarkCallaghanDB), an ex-MongoDB and ex-Rockset database expert, checked if some database and public cloud vendors have DeWitt clauses in their terms of use.

In this blog post, I try to extend [his research](http://smalldatum.blogspot.com/2021/11/dewitt-clause-vs-public-cloud.html) and maintain an ever-green list of database and similar vendors with regard to the DeWitt clause. (If you spot any inaccuracy, which is totally possible, please get in touch via [igor@cube.dev](mailto:igor@cube.dev?subject=DeWitt), I'll be happy to update the post.)

*Last updated: June 2, 2022.*

### 😇 Open-source vendors (without the DeWitt clause)

Open source licenses usually grant users permission to [use open source software for any purpose](https://opensource.org/docs/osd). So, by definition, open source software can't contain DeWitt clauses (because if it did, it wouldn't be open source).

Yes, you can benchmark any of these tools:

* Apache [Drill](https://github.com/apache/drill), [Druid](https://github.com/apache/druid/), [Flink](https://github.com/apache/flink), [Hive](https://github.com/apache/hive), [Kafka](https://kafka.apache.org), [Spark](https://github.com/apache/spark)
* [appwrite](https://github.com/appwrite/appwrite)
* [ClickHouse](https://github.com/ClickHouse/ClickHouse) (they even maintain a [list of benchmarks](https://github.com/ClickHouse/ClickHouse/issues/22398) on GitHub)
* [CockroachDB](https://github.com/cockroachdb/cockroach) (licensed under BSL, not [OSI-approved](https://opensource.org/licenses/alphabetical))
* [Dremio](https://github.com/dremio/dremio-oss)
* [Elasticsearch](https://github.com/elastic/elasticsearch) (licensed under SSPL, not OSI-approved)
* [ksqlDB](https://ksqldb.io)
* [Materialize](https://github.com/MaterializeInc/materialize) (licensed under BSL, not OSI-approved)
* [MongoDB](https://github.com/mongodb/mongo) (licensed under SSPL, not OSI-approved)
* [PostgreSQL](https://www.postgresql.org)
* [Presto](https://github.com/prestodb/presto)
* [Redpanda](https://github.com/redpanda-data/redpanda) (licensed under BSL, not OSI-approved)
* [QuestDB](https://github.com/questdb/questdb)
* [TimescaleDB](https://github.com/timescale/timescaledb)
* [Trino](https://github.com/trinodb/trino)

Also, some open-source vendors collaboratively maintain benchmarking suites such as [Time Series Benchmark Suite](https://github.com/timescale/tsbs) to help choose the best tools for particular workloads.

### 😇 Vendors without the DeWitt clause

Cloud vendors can restrict you from benchmarking their services but sometimes they choose not to have the DeWitt clause.

Yes, you can benchmark any of these services:

* [Ahana Cloud](https://ahana.io/terms-of-service/)
* [Altinity Cloud](https://altinity.com/legal/)
* [appwrite Cloud](https://appwrite.io) (not launched to GA yet)
* [ClickHouse Cloud](https://clickhouse.com/cloud/) (not launched to GA yet)
* [Confluent Cloud](https://assets.confluent.io/m/103033bdcb9b3fcd/original/20220201-Confluent_Cloud_Services_Agreement.pdf)
* [Firebolt](https://d7umqicpi7263.cloudfront.net/eula/product/28a79f78-8008-4c02-a971-54ef6d8ed904/898eb1bc-7c2a-4829-b479-67f5a18a51ff.pdf)
* [Materialize Cloud](https://materialize.com/terms-and-conditions/)
* [MongoDB Cloud](https://www.mongodb.com/cloud-terms-and-conditions)
* [QuestDB Cloud](https://questdb.io/terms/) (not launched to GA yet)
* [Redpanda Cloud](https://redpanda.com/cloud/)
* [Snowflake](https://www.snowflake.com/legal/acceptable-use-policy/)
* [Starburst Galaxy](https://www.starburst.io/terms/galaxy-terms-of-service/)
* [Supabase](https://supabase.com/docs/company/terms)
* [Timescale Cloud](https://www.timescale.com/legal/timescale-cloud-terms-of-service)

However, many of these cloud vendors disallow *abusive benchmarking* in their acceptable use policies. So be careful and please don't get banned.

### 🙃 Vendors with the DeWitt Embrace clause

Some cloud vendors permit you to benchmark their service but require reciprocity: you must make the benchmark reproducible and allow benchmarking of your own service or tool in response.

Yes, you can benchmark any of these services and probably get away with it:

* [Amazon Web Services](https://aws.amazon.com/service-terms/), including [Athena](https://aws.amazon.com/athena/), [Aurora](https://aws.amazon.com/rds/aurora/), and [Redshift](https://aws.amazon.com/redshift/). *You may perform benchmarks or comparative tests or evaluations (each, a “Benchmark”) of the Services. If you perform or disclose, or direct or permit any third party to perform or disclose, any Benchmark of any of the Services, you (i) will include in any disclosure, and will disclose to us, all information necessary to replicate such Benchmark, and (ii) agree that we may perform and disclose the results of Benchmarks of your products or services, irrespective of any restrictions on Benchmarks in the terms governing your products or services.*
* [CockroachDB Serverless](https://www.cockroachlabs.com/acceptable-use-policy/). *You can only disclose benchmarking tests if you (i) provide us the necessary information to replicate the tests, (ii) disclose the methodology for the benchmarking tests along with the results, and (iii) allow us to run our own benchmarking tests against your products and services at our discretion.*
* [Databricks](https://databricks.com/blog/2021/11/08/eliminating-the-dewitt-clause-for-database-benchmarking.html). *You may perform benchmarks or comparative tests or evaluations (each, a “Benchmark”) of the Platform Services and may disclose the results of the Benchmark other than for Beta Services. If you perform or disclose, or direct or permit any third party to perform or disclose, any Benchmark of any of the Platform Services, you (i) will include in any disclosure, and will disclose to us, all information necessary to replicate such Benchmark, and (ii) agree that we may perform and disclose the results of Benchmarks of your products or services, irrespective of any restrictions on Benchmarks in the terms governing your products or services.*
* [SingleStore](https://www.singlestore.com/blog/eliminating-the-dewitt-clause-for-greater-transparency-in-benchmarking/). *Customer may perform industry standard benchmarks, comparative tests or evaluations (each, a "Performance Report") of the Managed Service. If Customer performs or discloses, or directs or permits any third party to perform or disclose, any Performance Report of any of the Managed Service, Customer (i) will include in any disclosure, and will disclose to SingleStore all information necessary to replicate such Performance Report and the data from the Performance Report, and (il) agree that SingleStore may perform and disclose the results of the Performance Report of Customer's products or services, irrespective of any restrictions on Performance Report.*

### 🤬 Vendors with the DeWitt clause

These vendors chose to restrict benchmarks with DeWitt clauses.

No, you can't benchmark any of these tools and get away with it:

* [Cloudera Data Platform](https://www.cloudera.com/legal/terms-and-conditions/cloudera-online-services-terms.html). *Restrictions. Customer may not: [...] access or use the Services for purposes of monitoring availability, performance or functionality of the Services, or for any benchmarking or competitive purposes.*
* [Decodable](https://www.decodable.co/legal/tos). *Restrictions. Customer may not, and may not cause or permit others to: [...] disclose results of any benchmark tests or performance tests of the Decodable services hereunder without Decodable’s prior written consent.*
* [Dremio Cloud](https://www.dremio.com/legal/terms-of-service/). *Restrictions. Customer shall not, and shall not cause or allow any Authorized User or third party to: [...] except with Dremio’s prior written permission, publish any performance or benchmark tests or analysis relating to Dremio Cloud.*
* [Elastic Cloud](https://www.elastic.co/agreements/global/cloud-services-monthly). *Restrictions [...] You shall not: [...] access or use any Cloud Service for purposes of monitoring its availability, performance or functionality, or for any other benchmarking or competitive purposes, including, without limitation, for the purpose of designing and/or developing any competitive services.*
* [Google Cloud Platform](https://cloud.google.com/terms/service-terms), including [BigQuery](https://cloud.google.com/bigquery) & [Firebase](https://firebase.google.com). *Benchmarking. Customer may conduct benchmark tests of the Services (each a "Test"). Customer may only publicly disclose the results of such Tests if it (a) obtains Google's prior written consent, (b) provides Google all necessary information to replicate the Tests, and (c) allows Google to conduct benchmark tests of Customer's publicly available products or services and publicly disclose the results of such tests. Notwithstanding the foregoing, Customer may not do either of the following on behalf of a hyperscale public cloud provider without Google's prior written consent: (i) conduct (directly or through a third party) any Test of the Services or (ii) disclose the results of any such Test.*
* [InfluxDB Cloud](https://www.influxdata.com/legal/terms-of-use/). *Restrictions. Customer will not: [...] publish or provide any benchmark, comparison or performance test results.*
* [MariaDB SkySQL](https://legal.mariadb.com/agreements/skysql/SkySQL_Terms_of_Service_V2_2020-12-16.pdf) and [MariaDB Xpand](https://legal.mariadb.com/agreements/xpand/xpand-license-terms-v2-2021-02-24.pdf). *Benchmarking. Customer may perform benchmarks or comparative tests or evaluations (each, a "Benchmark") of the SkySQL Services. However, Customer must obtain MariaDB's prior written approval to disclose to a third party the results of any Benchmark of the SkySQL Services.*
* [Microsoft SQL Server](https://www.microsoft.com/en-us/sql-server/). *BENCHMARK TESTING. You must obtain Microsoft’s prior written approval to disclose to a third party the results of any benchmark test of the software.*
* [Oracle](https://www.oracle.com/us/corporate/contracts/lic-online-toma-ph-de-eng-2817945.pdf). *RESTRICTIONS. [...] You may not: [...] disclose results of any Program benchmark tests without Oracle's prior written consent.*
* [PlanetScale](https://planetscale.com/legal/aup). *Customer will not, and will ensure that its End Users do not: [...] access any portion of the Product for the purpose of building a similar or competitive product or service, or monitor the Product for any benchmarking or competitive purpose.*

### 😶 Vendors for which the author didn't find the data

I wasn't able to identify where a few vendors stand with regard to the DeWitt clause:

* [MySQL HeatWave](https://www.mysql.com/cloud/)
* [Yellowbrick Data](https://www.yellowbrick.com/)

If you can provide any pointers, please get in touch via [igor@cube.dev](mailto:igor@cube.dev?subject=DeWitt), I'll be happy to update the post. Also, please reach out if you'd like more databases to be featured here.

## What about Cube?

[Cube](https://cube.dev) is an open-source [headless BI](https://cube.dev/blog/headless-bi) platform that [integrates with a lot of data sources](https://cube.dev/docs/config/databases): cloud data warehouses, query engines, and databases. Many of them are designed for low latency and some of them can provide high concurrency. However, sometimes they don't; you can read our [blog post](https://cube.dev/blog/data-warehouse-performance-and-how-cube-can-help) about that.

In any case, Cube will have no problem delivering your data, with sub-second latency and high concurrency, to any [data consumer](https://cube.dev/docs/config/downstream) out there: BI tools, data notebooks, front-end apps, etc. Thanks to [Cube Store](https://cube.dev/blog/introducing-cubestore), Cube's REST, GraphQL, and SQL APIs are able to respond to any request within 200-300 milliseconds and allow for 100+ concurrent requests—and that can scale.

Please explore how Cube integrates with [databases and other tools](https://cube.dev/blog/category/data) in the data space in our blog. Also, don't hesitate to try [Cube Cloud](https://cubecloud.dev/auth/signup), join our large [Slack community](http://slack.cube.dev), and give Cube a [star on GitHub](https://github.com/cube-js/cube.js) 🌟

Performance

All database vendors who will either praise or punish you for doing a benchmark as well as an entertaining digression into the recent history of computer science

DeWitt clause, or Can you benchmark a database and get away with it

Amazon Athena

Building an Open Source Web Analytics Platform

Sometimes, existing commercial or out-of-the-box open-source tools like Grafana don’t fit requirements for Nginx log analytics. Whether it is pricing, privacy, or customization issues, it is always good to know how to build such a system internally. 

In the following tutorial, I’ll show you how to build your own [Nginx](https://www.nginx.com/) log analytics with [Fluentd](https://www.fluentd.org/), [Kinesis Data Firehose](https://aws.amazon.com/kinesis/data-firehose/), [Glue](https://aws.amazon.com/glue/), [Athena](https://aws.amazon.com/athena/), and [Cube.js](https://cube.dev). This stack also makes it easy to add data from other sources, such as Snowplow events, into the same S3 bucket and merge results in Athena. I’ll walk you through the whole pipeline from data collection to the visualization.

[Here is the live demo of the final dashboard](https://statsbotco.github.io/cubejs/nginx-analytics-dashboard/).

Here’s a sample architecture of the application we’re going to build:
![Architecture](https://media.graphcms.com/output=format:jpg/ACbpDPQRKCakOMHIUVXu)

## Collecting Nginx Logs

By default, Nginx writes logs in a plain text format like this:

```bash
4/9/2019 12:58:17 PM1.1.1.1 - - [09/Apr/2019:09:58:17 +0000] "GET /sign-up HTTP/2.0" 200 9168 "https://example.com/sign-in" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.86 Safari/537.36" "-"
4/9/2019 12:58:17 PM1.1.1.1 - - [09/Apr/2019:09:58:17 +0000] "GET /sign-in HTTP/2.0" 200 9168 "https://example.com/sign-up" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.86 Safari/537.36" "-"
```

Although we can parse that, it would be much easier to change Nginx configuration to use the JSON format. 

```conf
log_format json_combined escape=json '{ "created_at": "$msec", '
			'"remote_addr": "$remote_addr", '
			'"remote_user": "$remote_user", '
			'"request": "$request", '
			'"status": $status, '
			'"bytes_sent": $bytes_sent, '
			'"request_length": $request_length, '
			'"request_time": $request_time, '
			'"http_referrer": "$http_referer", '
			'"http_x_forwarded_for": "$http_x_forwarded_for", '
			'"http_user_agent": "$http_user_agent" }';

access_log  /var/log/nginx/access.log  json_combined;
```

### Create an S3 Bucket
Create a new S3 bucket for the log files. Note that logs should be placed in the root of the bucket and no other data can be stored in that bucket. Also, consider using the same region as your Athena because Athena is not available in some regions.

### Define a Table Schema via the Athena Console

Open your Athena console and select the database you'd be using. To create a table, you can use AWS UI or just run create statement in the console.

```sql
CREATE EXTERNAL TABLE `kinesis_logs_nginx`(
  `created_at` double, 
  `remote_addr` string, 
  `remote_user` string, 
  `request` string, 
  `status` int, 
  `bytes_sent` int, 
  `request_length` int, 
  `request_time` double, 
  `http_referrer` string, 
  `http_x_forwarded_for` string, 
  `http_user_agent` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  's3://<YOUR-S3-BUCKET>'
TBLPROPERTIES ('has_encrypted_data'='false');
```

### Create a Kinesis Firehose Stream

Open up the Kinesis Firehose console and click "Create delivery stream." Enter a name for the stream and set delivery to `direct PUT`.

![Create delivery stream](https://media.graphcms.com/output=format:jpg/KtIhwoRVmR1qUGrSoFAg)

Click next. Select `Record format conversion` to `Enabled`, `Output format` to `Apache ORC`, and select the database you created as the schema source.

![Set record format](https://media.graphcms.com/output=format:jpg/DupZUDNgTa6UgOc9Vvai)

Select S3 as a storage format. Select the S3 bucket you created for log files and leave `S3 Prefix` empty.

![S3 settings](https://media.graphcms.com/output=format:jpg/JI3GLcGNQCKi09bD1BKL)

You can change other options depending on your load, but we'll continue with the default ones. S3 compression is not available since the ORC format includes compression.
Now you've created the Kinesis Firehose stream!

### Fluentd

There are many different ways to install and use [Fluentd](https://www.fluentd.org/). We'll stick with the Docker version.

First, create a fluent.conf file and add a new Fluentd source to collect logs:

```xml
<source>
  @type forward
  port 24224
  bind 0.0.0.0
</source>
```

Now we can run a Fluentd server with this configuration. Here's a basic configuration; you can check out other options on [Docker Hub](https://hub.docker.com/r/fluent/fluentd/).

```bash
$ docker run \
  -d \
  -p 24224:24224 \
  -p 24224:24224/udp \
  -v /data:/fluentd/log \
  -v <PATH-TO-FLUENT-CONF>:/fluentd/etc fluentd \
  -c /fluentd/etc/fluent.conf
  fluent/fluentd:stable
```

This configuration uses the `/fluentd/log` path for cache files. You can run Fluentd in Docker without mounting the data directory, but in the case of a restart, you can lose all cached logs. Also, you can change the default `24224` port to any other unused port. Now, as we have a running Fluentd server, we can stream Nginx logs to it.

Since we run Nginx in Docker, we can run it with the built-in docker Fluentd log driver:

```bash
$ docker run \
--log-driver=fluentd \
--log-opt fluentd-address=<FLUENTD-SERVER-ADDRESS>\
--log-opt tag=\"{{.Name}}\" \
-v /some/content:/usr/share/nginx/html:ro \
-d \
nginx
```

We'll use the [Amazon Kinesis Output Plugin](https://github.com/awslabs/aws-fluent-plugin-kinesis). It can store logs captured from Nginx as ORC files.

Next, select the Nginx log using tag prefix match and parse JSON:

```xml
<filter YOUR-NGINX-TAG.*>
  @type parser
  key_name log
  emit_invalid_record_to_error false
  <parse>
    @type json
  </parse>
</filter>
```

We’re using the [kinesis_firehose](https://github.com/awslabs/aws-fluent-plugin-kinesis) output plugin to send parsed logs to Kinesis Firehose:

```xml
<match YOUR-NGINX-TAG.*>
	@type kinesis_firehose
	region region
	delivery_stream_name <YOUR-KINESIS-STREAM-NAME>
	aws_key_id <YOUR-AWS-KEY-ID>
	aws_sec_key <YOUR_AWS-SEC_KEY>
</match>
```

## Athena

Now you can query Nginx logs in Athena with SQL. Let's find some recent errors:

```sql
SELECT * FROM "db_name"."table_name" WHERE status > 499 ORDER BY created_at DESC limit 10;
```

### Full Scan for Each Request

Now we have logs parsed and delivered to S3 in the ORC format, which is compressed and efficient to query. Also, Kinesis Firehose partitions logs by date and hour, but querying and filtering them requires Athena to scan all files. This is a problem, because the more logs you store, the more data gets scanned per request. It is slow and also pricey, because Athena pricing depends on scanned data volume.

To solve this, we'll use AWS Glue Crawler, which gathers partition data from S3 and writes it to the Glue Metastore. Once data is partitioned, Athena will only scan data in selected partitions. It makes querying much more efficient in terms of time and cost.

### Setting an Amazon Glue Crawler

Amazon Glue Crawler can scan the data in the bucket and create a partitioned table for that data. 

Create a Glue Crawler and add the bucket you use to store logs from Kinesis. You can add multiple buckets to be scanned on each run, and the crawler will create separate tables for each bucket. Make sure to schedule this crawler to run periodically depending on your needs. We usually run a single crawler for all buckets every hour. Also, it's easier to use a separate database for all crawled tables.

### Partitioned Tables

Open your Athena console and choose the database you selected in the crawler configuration. After the first run of the crawler, you should see a table named the same as the S3 bucket where all log files are located. You can query results for some hours and filter the data by partitions.

```sql
SELECT * FROM "default"."part_demo_kinesis_bucket"
WHERE(
  partition_0 = '2019' AND
  partition_1 = '04' AND
  partition_2 = '08' AND
  partition_3 = '06'
  );
```

This query will select all events that came from 6 AM to 7 AM on April 8, 2019. 

Did that help? Let's run the same request without a partition filter.

![Query without partition filter](https://media.graphcms.com/output=format:jpg/dfCkPCTiSI6PDqZCssZ4)

You can see that the query took 3.59 seconds to run and processed 244.34 megabytes of data. Let's use partition helpers:

![Query with partition filter](https://media.graphcms.com/output=format:jpg/gqN32aeQUWKgz3wnD9pi)

This query is a bit faster and, more importantly, took only 1.23 megabytes of scanned data. On larger datasets, the difference grows dramatically.

## Building a Dashboard with Cube.js

We will use Cube.js to query Athena and build a dashboard. It has plenty of powerful features, but the main reasons we would want to use it, in this case, are automatic handling of partitions and pre-aggregations.

Cube.js can dynamically generate SQL, taking care of partitioning. It uses [data schema](https://cube.dev/docs/reference/frontend/cubejs-client-core), which is written in Javascript, to generate SQL. We can put instructions on how to filter partitions in the data schema. 

Let’s create a new Cube.js app with a serverless deployment option. Since we’re using Athena, serverless is the easiest way to deploy. If you prefer other deployment environments, such as Docker or Heroku, you can check the documentation on deployment options [here](https://cube.dev/docs/deployment).

```bash
$ npm install -g cubejs-cli
$ cubejs create nginx-log-analytics -t serverless -d athena
```

Cube.js uses environment variables for database credentials. On new app creation, the CLI generates the `.env` file with placeholders in the project directory. Fill it with your [Athena credentials](https://cube.dev/docs/connecting-to-the-database#notes-aws-athena).

Now, we can create a [data schema](https://cube.dev/docs/guides/recipes/code-reusability/schema-generation) file, where we’ll define how Cube.js should query the Athena logs table, as well as measures and dimensions for our dashboard. If you are just getting started with Cube.js, I recommend checking [this](https://cube.dev/blog/cubejs-open-source-dashboard-framework-ultimate-guide/) or [that](https://cube.dev/docs/product/data-modeling/overview) tutorial to learn more about data schemas and how Cube.js generates SQL.

In the `schema` folder, create the file `Logs.js` with the following content:

```javascript
const partitionFilter = (from, to) => `
    date(from_iso8601_timestamp(${from})) <= date_parse(partition_0 || partition_1 || partition_2, '%Y%m%d') AND
    date(from_iso8601_timestamp(${to})) >= date_parse(partition_0 || partition_1 || partition_2, '%Y%m%d')
    `

cube(`Logs`, {
  sql: `
  select * from part_demo_kinesis_bucket
  WHERE ${FILTER_PARAMS.Logs.createdAt.filter(partitionFilter)}
  `,

  measures: {
    count: {
      type: `count`,
    },

    errorCount: {
      type: `count`,
      filters: [
        { sql: `${CUBE.isError} = 'Yes'` }
      ]
    },

    errorRate: {
      type: `number`,
      sql: `100.0 * ${errorCount} / ${count}`,
      format: `percent`
    }
  },

  dimensions: {
    status: {
      sql: `status`,
      type: `number`
    },

    isError: {
      type: `string`,
      case: {
        when: [{
          sql: `${CUBE}.status >= 400`, label: `Yes`
        }],
        else: { label: `No` }
      }
    },

    createdAt: {
      sql: `from_unixtime(created_at)`,
      type: `time`
    }
  }
});
```

In the top level `sql` expression for the `Logs` cube, we are using the [FILTER_PARAMS](https://cube.dev/docs/cube#context-variables-filter-params) feature of Cube.js to dynamically generate SQL based on the passed filter parameters.

We also define measures and dimensions we’ll use in our dashboard. One last thing to do before building a frontend is to set up pre-aggregations. The Cube.js pre-aggregation engine builds a layer of aggregated data in your database during the runtime and keeps it up-to-date. It can significantly speed up the performance and also in the case of Athena reduce billing as well. [This guide](https://cube.dev/blog/high-performance-data-analytics-with-cubejs-pre-aggregations/) covers using pre-aggregations in more details.

To add it to our schema, add the following block to the end of the cube definition.

```javascript
preAggregations: {
  main: {
    type: `rollup`,
    measureReferences: [count, errorCount],
    dimensionReferences: [isError, status],
    timeDimensionReference: createdAt,
    granularity: `day`,
    partitionGranularity: `month`,
    refreshKey: {
      sql: FILTER_PARAMS.Logs.createdAt.filter((from, to) => 
        `select
           CASE WHEN from_iso8601_timestamp(${to}) + interval '3' day > now()
           THEN date_trunc('hour', now()) END`
      )
    }
  }
}
```

We’re pre-aggregating all the measures and dimensions we’ll use and also making this pre-aggregation to be partitioned by month. [Partitioning pre-aggregations](https://cube.dev/docs/pre-aggregations#rollup-time-partitioning) can dramatically increase background refresh time.

Now, we are ready to build our frontend dashboard.

Cube.js provides [REST API](https://cube.dev/docs/rest-api), a [Javascript client](hhttps://cube.dev/docs/reference/frontend/cubejs-client-core), and bindings for popular frameworks, such as React and Vue. The clients are visualization agnostic and take care of API calls and data formatting, letting developers use any visualization library.

The Cube.js server accepts a query in a [JSON format](https://cube.dev/docs/query-format) with measures and dimensions. It then generates and executes SQL against Athena, and sends the result back. For example, to load the count of requests with an error over time by day, we can use the following request:

```json
{
  "measures": ["Logs.errorCount"],
  "timeDimensions": [
    {
      "dimension": "Logs.createdAt",
      "dateRange": ["2019-01-01", "2019-01-07"],
      "granularity": "day"
    }
  ]
}
```

You can install the Cube.js Javascript Client and React binding with NPM.

```bash
$ npm i --save @cubejs-client/core @cubejs-client/react
```

Then import the `cubejs` and `QueryRenderer` components, and use them to fetch the data. In the example below, we use Recharts to visualize data.

```javascript
import React from 'react';
import { LineChart, Line, XAxis, YAxis } from 'recharts';
import cubejs from '@cubejs-client/core';
import { QueryRenderer } from '@cubejs-client/react';

const cubejsApi = cubejs(
  'YOUR-CUBEJS-API-TOKEN',
  { apiUrl: 'http://localhost:4000/cubejs-api/v1' },
);

export default () => {
  return (
    <QueryRenderer
      query={{
        measures: ['Logs.errorCount'],
        timeDimensions: [{
            dimension: 'Logs.createdAt',
            dateRange: ['2019-01-01', '2019-01-07'],
            granularity: 'day'
        }]
      }}
      cubejsApi={cubejsApi}
      render={({ resultSet }) => {
        if (!resultSet) {
          return 'Loading...';
        }

        return (
          <LineChart data={resultSet.rawData()}>
            <XAxis dataKey="Logs.createdAt"/>
            <YAxis/>
            <Line type="monotone" dataKey="Logs.errorCount" stroke="#8884d8"/>
          </LineChart>
        );
      }}
    />
  )
}
```

[This tutorial](https://cube.dev/blog/cubejs-open-source-dashboard-framework-ultimate-guide/) goes into great detail on how to build dashboards with Cube.js.

You can find a CodeSandbox with a demo dashboard built with React and Cube.js below.

<iframe src="https://codesandbox.io/embed/l5qyrzlw07?fontsize=14" css="width:100%; height:500px; border:0; border-radius: 4px; overflow:hidden;" sandbox="allow-modals allow-forms allow-popups allow-scripts allow-same-origin"></iframe>

Timur Minulin

Use Cases

Nginx Log Analytics with AWS Athena and Cube.js

_This is the second part of a tutorial series on building an analytical web application with [Cube.js](https://github.com/cube-js/cube.js). You can find [the first part here](https://statsbot.co/blog/building-an-open-source-mixpanel-alternative-1/). It expects the reader to be familiar with Javascript, Node.js, React, and have basic knowledge of SQL. [The final source code is available here](https://github.com/cube-js/cube.js/tree/master/examples/event-analytics) and [the live demo is here](https://d1ygcqhosay4lt.cloudfront.net/). The example app is serverless and running on AWS Lambda. It displays data about its own usage._

In this part, we are going to add Funnel Analysis to our application. Funnel Analysis, alongside with Retention Analysis, is vital to analyze behavior across the customer journey.  A funnel is a series of events that a user goes through within the app, such as completing an onboarding flow. A user is considered converted through a step in the funnel if she performs the event in the specified order. Calculating how many unique users made each event could show you a conversion rate between each step. It helps you to localize a problem down to a certain stage.

Since our application tracks its own usage, we'll build funnels to show how well users navigate through the funnels usage. Quite meta, right?

Here’s how it looks. You [check the live demo here](https://d1ygcqhosay4lt.cloudfront.net/funnels).

![](https://media.graphcms.com/yN9dB53UTkOFUwIsjwLT)

## Building SQL for Funnels

Just a quick recap of part I—we are collecting data with the Snowplow tracker, storing it in S3, and querying with Athena and Cube.js. Athena is built on Presto, and supports standard SQL. So to build a funnel, we need to write a SQL code. Real-world funnel SQL could be quite complex and slow from a performance perspective. Since we are using Cube.js to organize data schema and generate SQL, we can solve both of these problems.

Cube.js allows the building of packages, which are a collection of reusable data schemas. Some of them are specific for datasets, such as the [Stripe Package](https://github.com/cube-js/stripe-schema). Others provide helpful macros for common data transformations. And one of them we're going to use—the Funnels package.

_If you are new to Cube.js Data Schema, I strongly recommend you check [this](https://cube.dev/docs/guides/recipes/code-reusability/schema-generation) or [that](https://statsbot.co/blog/building-an-open-source-mixpanel-alternative-1/) tutorial first and then come back to learn about the funnels package._

The best way to organize funnels is to create a separate cube for each funnel. We'll use `eventFunnel` from the Funnel package. All we need to do is to pass an object with the required properties to the `eventFunnel` function. 
[Check the Funnels package documentation for detailed information about its configuration.](https://cube.dev/docs/funnels)

Here is how this config could look.
_In production applications, you're most likely going to generate Cubes.js schema dynamically. [You can read more about how to do it here.](https://cube.dev/docs/schema-generation)_

```javascript
import Funnels from "Funnels";

import { eventsSQl, PAGE_VIEW_EVENT, CUSTOM_EVENT } from "./Events.js";

cube("FunnelsUsageFunnel", {
  extends: Funnels.eventFunnel({
    userId: {
      sql: `user_id`
    },
    time: {
      sql: `time`
    },
    steps: [
      {
        name: `viewAnyPage`,
        eventsView: {
          sql: `select * from (${eventsSQl}) WHERE event = '${PAGE_VIEW_EVENT}`
        }
      },
      {
        name: `viewFunnelsPage`,
        eventsView: {
          sql: `select * from (${eventsSQl}) WHERE event = '${PAGE_VIEW_EVENT} AND page_title = 'Funnels'`
        },
        timeToConvert: "30 day"
      },
      {
        name: `funnelSelected`,
        eventsView: {
          sql: `select * from (${eventsSQl}) WHERE event = '${CUSTOM_EVENT} AND se_category = 'Funnels' AND se_action = 'Funnel Selected'`
        },
        timeToConvert: "30 day"
      }
    ]
  })
});

```

The above, 3-step funnel, describes the user flow from viewing any page, such as the home page, to going to Funnels and then eventually selecting a funnel from the dropdown. We're setting `timeToConvert` to 30 days for the 2nd and 3rd steps. This means we give a user a 30 day window to let her complete the target action to make it to the funnel.

In our example app, we generate these configs dynamically. [You can check the code on Github here.](https://github.com/cube-js/cube.js/blob/master/examples/event-analytics/backend/schema/Funnels.js)

## Materialize Funnels SQL with Pre-Aggregations

As I mentioned before, there is a built-in way in Cube.js to accelerate queries’ performance. 
Cube.js can materialize query results in a table. It keeps them up to date and queries them instead of raw data. Pre-Aggregations can be quite complex, including multi-stage and dependency management. But for our case, the simplest `originalSql` pre-aggregation should be enough. It materializes the base SQL for the cube.

[Learn more about pre-aggregations here.](https://cube.dev/docs/pre-aggregations)

```javascript
import Funnels from 'Funnels';
import { eventsSQl, PAGE_VIEW_EVENT, CUSTOM_EVENT } from './Events.js';

cube('FunnelsUsageFunnel', {
  extends: Funnels.eventFunnel({ ... }),
  preAggregations: {
    main: {
      type: `originalSql`
    }
  }
});
```
## Visualize

There are a lot of way to visualize a funnel. Cube.js is visualization-agnostic, so pick one that works for you and fits well into your app design. In our example app, we use a bar chart from the Recharts library.

The Funnels package generates a cube with `conversions` and `conversionsPercent` measures, and `steps` and `time` dimensions. To build a bar chart funnel, we need to query the `conversions` measure grouped by the `step` dimension. The `time` dimension should be used in the filter to allow users to select a specific date range of the funnel.

Here is the code (we are using React and the Cube.js React Client):

```jsx
import React from "react";
import cubejs from "@cubejs-client/core";
import { QueryRenderer } from "@cubejs-client/react";
import CircularProgress from "@material-ui/core/CircularProgress";
import { BarChart, Bar, XAxis, YAxis, CartesianGrid, Tooltip } from "recharts";

const cubejsApi = cubejs(
  "YOUR-API-KEI",
  { apiUrl: "http://localhost:4000/cubejs-api/v1" }
);

const Funnel = ({ dateRange, funnelId }) => (
  <QueryRenderer
    cubejsApi={cubejsApi}
    query={{
      measures: [`${funnelId}.conversions`],
      dimensions: [`${funnelId}.step`],
      filters: [
        {
          dimension: `${funnelId}.time`,
          operator: `inDateRange`,
          values: dateRange
        }
      ]
    }}
    render={({ resultSet, error }) => {
      if (resultSet) {
        return (
          <BarChart
            width={600}
            height={300}
            margin={{ top: 20 }}
            data={resultSet.chartPivot()}
          >
            <CartesianGrid strokeDasharray="3 3" />
            <XAxis dataKey="x" minTickGap={20} />
            <YAxis />
            <Tooltip />
            <Bar dataKey={`${funnelId}.conversions`} fill="#7A77FF"/>
          </BarChart>
        );
      }

      return "Loading...";
    }}
  />
);

export default Funnel;
```

If you run this code in CodeSandbox, you should see something like this.

<iframe src="https://codesandbox.io/embed/nw87w1nnjm?fontsize=14" css="width:100%; height:500px; border:0; border-radius: 4px; overflow:hidden;" sandbox="allow-modals allow-forms allow-popups allow-scripts allow-same-origin"></iframe>

The above example is connected to the Cube.js backend from our event analytics app. 

In the next part, we’ll walk through how to build a dashboard and dynamic query builder, like one in Mixpanel or Amplitude. Part 4 will cover the Retention Analysis. In the final part, we will discuss how to deploy the whole application in the serverless mode to AWS Lambda.

[You can check out the full source code of the application here.](https://github.com/cube-js/cube.js/tree/master/examples/event-analytics)

[And the live demo is available here.](https://d1ygcqhosay4lt.cloudfront.net/)

Building an Open Source Mixpanel Alternative. Part 2: Conversion Funnels

_This is the first part of a tutorial series on building an analytical web application with [Cube.js](https://github.com/cube-js/cube.js). It expects the reader to be familiar with Javascript, Node.js, React, and have basic knowledge of SQL. [The final source code is available here](https://github.com/cube-js/cube.js/tree/master/examples/event-analytics) and [the live demo is here](https://d1ygcqhosay4lt.cloudfront.net/). The example app is serverless and running on AWS Lambda. It displays data about its own usage._

![](https://media.graphcms.com/8iQ3g6FUTDqvC73peT6q)

There is a category of analytics tools like Mixpanel or Amplitude, which are good at working with events data. They are ideal for measuring product or engagement metrics, such as activation funnels or retention. They are also very useful for measuring A/B tests. 

Although all these tools do a job, they are proprietary and cloud-based. That could be a problem when privacy is a concern. Or if one wants to customize how funnels or retention work under the hood. While traditional BI tools, like Tableau or Power BI, could potentially be used to run the same analysis, they can not offer the same level of user experience. The problem is that they are designed to be general business intelligence tools, and not specific for funnels, retention, A/B tests, etc.

With recent advancements in frontend development, it became possible to rapidly develop complex user interfaces. Things which took a week to build five years ago could be built in an afternoon nowadays. On the backend and infrastructure side, cloud-based MPP databases, such as BigQuery and Athena, are dramatically changing the landscape. The ELT approach, when data is transformed inside the database, is getting more and more popular, replacing traditional ETL. Serverless architecture makes it possible to easily deploy and scale applications.

All of these made it possible to build internal alternatives to established services like Mixpanel, Amplitude, or Kissmetrics. In this series of tutorials, we’re going to build a full-featured open-source event analytics system.

It will include the following features:

- Data collection;
- Dashboarding;
- Ad hoc analysis with query builder;
- Funnel analysis;
- Retention analysis;
- Serverless deployment;
- A/B tests;
- Real-time events monitoring;

The diagram below shows the architecture of our application:

![](https://media.graphcms.com/Q37GPFyT72clApTI6gDg)

In the first part of our tutorial, we’ll focus more on how to collect and store data. And briefly cover how to make a simple chart based on this data. The following parts focus more on querying data and building various analytics reporting features.

## Collecting Events
We’re going to use Snowplow Cloudfront Collector and Javascript Tracker. We need to upload a tracking pixel to Amazon CloudFront CDN. The Snowplow Tracker sends data to the collector by making a GET request for the pixel and passing data as a query string parameter. The CloudFront Collector uses CloudFront logging to record the request (including the query string) to an S3 bucket.

Next, we need to install Javascript Tracker. [Here is the full guide](https://github.com/snowplow/snowplow/wiki/javascript-tracker-setup).

But, in short, it is similar to Google Analytics’s tracking code or Mixpanel’s, so we need to just embed it into our HTML page.

```javascript
<script type="text/javascript">      
  ;(function(p,l,o,w,i,n,g){if(!p[i]){p.GlobalSnowplowNamespace=p.GlobalSnowplowNamespace||[];
   p.GlobalSnowplowNamespace.push(i);p[i]=function(){(p[i].q=p[i].q||[]).push(arguments)
   };p[i].q=p[i].q||[];n=l.createElement(o);g=l.getElementsByTagName(o)[0];n.async=1;
   n.src=w;g.parentNode.insertBefore(n,g)}} .  (window,document,"script","//d1fc8wv8zag5ca.cloudfront.net/2.10.2/sp.js","snowplow"));

  window.snowplow('newTracker', 'cf', '<YOUR_CLOUDFRONT_DISTRIBUTION_URL>’, { post: false });
</script>
```
[Here you can find how it is embedded into our example application.](https://github.com/cube-js/cube.js/blob/master/examples/event-analytics/frontend/public/index.html#L26-L33)

Once we have our data, which is CloudFront logs, in the S3 bucket, we can query it with Athena. All we need to do is create a table for CloudFront logs.

Copy and paste the following DDL statement into the Athena console. Modify the LOCATION for the S3 bucket that stores your logs.

```sql
CREATE EXTERNAL TABLE IF NOT EXISTS default.cloudfront_logs (
  `date` DATE,
  time STRING,
  location STRING,
  bytes BIGINT,
  requestip STRING,
  method STRING,
  host STRING,
  uri STRING,
  status INT,
  referrer STRING,
  useragent STRING,
  querystring STRING,
  cookie STRING,
  resulttype STRING,
  requestid STRING,
  hostheader STRING,
  requestprotocol STRING,
  requestbytes BIGINT,
  timetaken FLOAT,
  xforwardedfor STRING,
  sslprotocol STRING,
  sslcipher STRING,
  responseresulttype STRING,
  httpversion STRING,
  filestatus STRING,
  encryptedfields INT
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
LOCATION 's3://CloudFront_bucket_name/AWSLogs/Account_ID/'
TBLPROPERTIES ( 'skip.header.line.count'='2' )
```
Now we are ready to connect Cube.js to Athena and start building our first dashboard.

## Building Our First Chart

First, install Cube.js CLI. It is used for various Cube.js workflows.

```bash
$ npm install -g cubejs-cli
```

Next, сreate a new Cube.js service by running the following command. Note, we are specifying Athena as a database here (-d athena) and template as serveless (-t serverless). Cube.js supports different configurations, but for this tutorial, we will use the serverless one.

```bash
$ cubejs create event-analytics-backend -d athena -t serverless
```

Once run, the `create` command will create a new project directory that contains the scaffolding for your new Cube.js project. This includes all the files necessary to spin up the Cube.js backend, example frontend code for displaying the results of Cube.js queries in a React app, and some example schema files to highlight the format of the Cube.js Data Schema layer.

The `.env` file in this project directory contains placeholders for the relevant database credentials.  For Athena, you'll need to specify the AWS access and secret keys with the [access necessary to run Athena queries](https://docs.aws.amazon.com/athena/latest/ug/access.html), and the target AWS region and [S3 output location](https://docs.aws.amazon.com/athena/latest/ug/querying.html) where query results are stored.

```bash
CUBEJS_DB_TYPE=athena
CUBEJS_AWS_KEY=<YOUR ATHENA AWS KEY HERE>
CUBEJS_AWS_SECRET=<YOUR ATHENA SECRET KEY HERE>
CUBEJS_AWS_REGION=<AWS REGION STRING, e.g. us-east-1>
# You can find the Athena S3 Output location here: https://docs.aws.amazon.com/athena/latest/ug/querying.html
CUBEJS_AWS_S3_OUTPUT_LOCATION=<S3 OUTPUT LOCATION>
```
Now, let’s create a basic Cube.js Schema for our events model. Cube.js uses Data Schema to generate and execute SQL; [you can read more about it here.](https://statsbot.co/docs/getting-started-cubejs)

Create a `schema/Events.js` file with the following content.

```javascript
const regexp = (key) => `&${key}=([^&]+)`;
const parameters = {
  event: regexp('e'),
  event_id: regexp('eid'),
  page_title: regexp('page')
}

cube(`Events`, {
  sql:
    `SELECT
      from_iso8601_timestamp(to_iso8601(date) || 'T' || "time") as time,
      ${Object.keys(parameters).map((key) => ( `url_decode(url_decode(regexp_extract(querystring, '${parameters[key]}', 1))) as ${key}` )).join(", ")}
    FROM cloudfront_logs
    WHERE length(querystring) > 1
    `,

  measures: {
    pageView: {
      type: `count`,
      filters: [
        { sql: `${CUBE}.event = 'pv'` }
      ]
    },
  },

  dimensions: {
    pageTitle: {
      sql: `page_title`,
      type: `string`
    }
  }
});
```
In the schema file, we create an Events cube. It is going to contain all the information about our events. In the base SQL statement, we’re extracting values from the query string sent by the tracker by using the `regexp` function. Cube.js is good at running transformations such this and it could also materialize some of them for performance optimization. We’ll talk about it in the next parts of our tutorial.

With this schema in place, we can run our dev server and build the first chart. 

Spin up the development server by running the following command.

```bash
$ npm dev
```

Visit [http://localhost:4000](http://localhost:4000), it should open a CodeSandbox with an example. Change the `renderChart` function and the `query` variable to the following.

```jsx
const renderChart = resultSet => (
  <Chart height={400} data={resultSet.chartPivot()} forceFit>
    <Coord type="theta" radius={0.75} />
    <Axis name="Events.pageView" />
    <Legend position="right" name="category" />
    <Tooltip showTitle={false} />
    <Geom type="intervalStack" position="Events.pageView" color="x" />
  </Chart>
);

const query = {
  measures: ["Events.pageView"],
  dimensions: ["Events.pageTitle"]
};
```

Now, you should be able to see the pie chart, depending on what data you have in your S3.

![](https://media.graphcms.com/MPZmJlTBQKeDk4260AEg)

In the next part, we’ll walk through how to build Conversion Funnels. In the third part we will make a dashboard and dynamic query builder, like one in Mixpanel or Amplitude. Part 3 will cover Retention Analysis. In the final part, we will discuss how to deploy the whole application in the serverless mode to AWS Lambda.

[You can check out the full source code of the application here.](https://github.com/cube-js/cube.js/tree/master/examples/event-analytics)

[And the live demo is available here.](https://d1ygcqhosay4lt.cloudfront.net)