Today, we’re announcing our official integration with [Databricks][1]. Here’s how it works—and what it means. 

> __Want a personalized demo or some Cube swag? Join us at the Databricks Data + AI Summit in San Francisco, June 27-30! [Register here][32] for some 1:1 time with the Cube team.__



## 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 headless business intelligence platform 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 & Product Updates

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.

Announcing the 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/cloud) (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) 🌟

Igor Lukanin

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 %DATABASE% and get away with it

Artyom Keydunov

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/getting-started-cubejs-schema), 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/getting-started-cubejs-schema) 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/getting-started-cubejs-schema) 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](https://cube.dev/docs/@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/getting-started-cubejs-schema) 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, 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)

Building an Open Source Mixpanel Alternative. Part 1: Collecting and Displaying Events

Pavel Tiunov

Building Open Source Google Analytics from Scratch

Query Engines