# Build a Real-time Analytics Application with TiDB Cloud

![TiDB Cloud Demo App](https://en.pingcap.com/wp-content/uploads/2022/01/Getting-started-with-TiDB-Cloud-using-Python-and-Flask-1024x342.jpg) "TiDB Cloud Demo App"

**Authors:** [Chenhao Huang](https://github.com/chenhao-huang), [Phoebe He](https://github.com/phxwhe)

**Editors:** [Yajing Wang](https://github.com/anotherrachel), Tom Dewan

_Disclaimer:_

- _PingExpress_DemoCorp is a dummy company. It does NOT reflect or imply any real company._
- _This tutorial is for demonstration purposes only. Do NOT use any material (including but not limited to code, and commands) from this tutorial in production environments._

PingExpress_DemoCorp is a supply chain management company in the United States. With more people shopping online due to the pandemic,  PingExpress_DemoCorp’s business has scaled rapidly. They’re delivering tens of billions of packages a year.

With so many deliveries, a key part of their success is accurate and efficient package tracking. Business managers need to know where packages are so they can identify potential traffic blocks and rearrange delivery routes. Customers need accurate delivery dates so they can plan ahead. Therefore, real-time tracking, status updates, and a detailed dashboard are very important to PingExpress_DemoCorp.

With the current technology infra, PingExpress_DemoCorp is facing growing pains:

- PingExpress_DemoCorp uses MySQL. For a real-time dashboard, they need to use both historical data and new data coming in. Data analytics rely on stored procedures. As business roars, more data needs to be stored. The MySQL sharding solution can’t meet their requirements, and the system is hard to scale and maintain.
- During peak hours, the performance on a standalone machine is poor. There is also high risk of a single-point failure.

PingExpress_DemoCorp considered two options:

- Option A: Add a dedicated column store to the existing data stack to separate the OLTP workload from the OLAP workload.
- Option B: Replace MySQL database with TiDB, which contains both the row store for daily transactions and the column store for analytical workloads.

PingExpress_DemoCorp chose option B. This is because adding another column storage for analysis workload makes the system more complicated. At the same time, data has to be synchronized from the row store to the column store via painful ETL processes over night. This means that choosing option A still does not enable PingExpress_DemoCorp to do real-time analytics.

[Subscribe to Blog](https://info.pingcap.com/cs/c/?cta_guid=43f3897a-052b-4b54-b433-6e35f4b4962b&signature=AAH58kESEuMzyLhwM5W2-t-rAMC84NXNfQ&placement_guid=1ca968e7-40c5-4aaf-b0c6-976446779a69&click=8a65ddb3-f762-4032-af50-a4c4cd1dfeab&hsutk=3d2f0243b7cb60e27f1683b8d9d0dc95&canon=https%3A%2F%2Fen.pingcap.com%2Fblog%2Fbuild-a-real-time-analytics-application-with-tidb-cloud%2F&utm_referrer=https%3A%2F%2Fen.pingcap.com%2Fblog%2F&portal_id=4466002&redirect_url=APefjpGXA97RZqePqofebSYe57ITGKVXsyTuoMSH_ZwK8t6sGt6_ihlA8ejrAstz84GaZ7CF7VOaF_cI1n4AzjzzBPywZQDlQDnvHQqxFhP0d_I2cAVmsNuuIpQasaLlLc_0W3u27KPqiN7UzhuPiH3Og2jF1S7mk6PY-ILK6Tk5EFxP91Cwkz8Cu2u0X_YxXqHPHB7v38SmtrSEAMDdFwC5XM90BcLbd0pCQri7BLM_KSi9NKWW7_fMHMT6cdil57opRo07WkuM "Subscribe to Blog")

On the other hand, switching to TiDB Cloud as the backend database is very attractive to PingExpress_DemoCorp：

- TiDB Cloud is MySQL compatible so it supports all of PingExpress_DemoCorp’s current applications.
- TiDB Cloud supports PingExpress_DemoCorp’s large, wide tables and multi-dimensional query analytics, in real-time. This is the highlight of TiDB Cloud since it supports both high-concurrency writes and fast queries.
- PingExpress_DemoCorp does not need to worry about the data load coming in any more. TiDB Cloud will auto scale-out or merge the data cluster based on the data load. So, no more sharding. And it supports strong consistency, which is critical for package tracking.
- TiDB is a Hybrid Transactional/Analytical Processing (HTAP) database. It has a row store engine (TiDB server) and a column store engine (TiFlash). Data in TiDB server is replicated to TiFlash synchronously, which enables real time data analysis. This also means there will be no painful ETL process.

In this tutorial, you will build a prototype for PingExpress_DemoCorp’s real-time analytics dashboard that runs on a TiDB Cloud Proof-of-Concept (PoC) cluster.

PingExpress_DemoCorp divides a package’s lifecycle into four stages:

- “1_pkg_in”: The package is collected by the company.
- “2_ori”: The package arrives at the processing center of the origin state.
- “3_des”: The package reaches the processing center of the destination state.
- “4_pkg_out”: The package is delivered to the end user.

## Before you begin

You should have the following software and packages installed:

- [Python](https://www.python.org/downloads/) (v. 3+)
- [MySQL connector for Python](https://github.com/mysql/mysql-connector-python)
- [SQLAlchemy](https://github.com/sqlalchemy/sqlalchemy)
- [sqlalchemy-tidb](https://github.com/pingcap/sqlalchemy-tidb)
- [Metabase](https://www.metabase.com/docs/latest/operations-guide/installing-metabase.html)

**Note:** It is recommended to use `pip3` to install packages, such as SQLAlchemy. We also suggest NOT to use the Mac application version for Metabase. It is gradually being phased out. You may use the jar version instead.

[Try TiDB Cloud for Free](https://info.pingcap.com/cs/c/?cta_guid=b9fef609-b6f9-4db1-b558-9ec698fd8a07&signature=AAH58kFJLjuBKTkCRI9_gqq5EsGiYwmDtA&placement_guid=dc83a601-b365-4d6a-82ad-8ba52e45dc0e&click=bdfc6676-01ab-4f91-901b-ba01b842ac47&hsutk=3d2f0243b7cb60e27f1683b8d9d0dc95&canon=https%3A%2F%2Fen.pingcap.com%2Fblog%2Fbuild-a-real-time-analytics-application-with-tidb-cloud%2F&utm_referrer=https%3A%2F%2Fen.pingcap.com%2Fblog%2F&portal_id=4466002&redirect_url=APefjpEPlkOK-4ZoPuEozRhOEwgcp6P0j1It3KCXeI4HBqwQYEkVVxtSZA5gfHiZgi8C-NrZ7axZdloNXWJoXtwsWxOPnlSCudO0FV2XhAXodkCV3291CK4tYPOgg-C2RTbez__dSM1DbRq8slBYZNbIBnXNwn8XEKsHxXQsd7ipxyhpHhnkh0KZQg_LUpcuqPrPHI_riiCfHEFIlImicsmgLVrL0NeLoJqv7Y2VAhW1FGrpW0UqruKg71CYSh8-ePu7nVTH3GNZYW8g5Ds3PK6gf1SZeZ-4q3JcWbOG4DW6tL3cqI0EryY "Try TiDB Cloud for Free")

## Create a TiDB PoC Cluster

1. [Log in](https://tidbcloud.com/) to your TiDB Cloud account.  
    If you do not have a TiDB Cloud account, click “Try TiDB Cloud for Free” above to sign up for an account.
2. Click **Create a Cluster** to go to the plan selection page.
3. Click **apply for a PoC Cluster**. It usually takes 24 ~ 48 hours to approve your request.
4. On the **Create a Cluster** page, set up **Cluster Name** and **Root Password**. (In this tutorial, we will call our cluster **PingExpressDB**).
5. Select the **Region** as **AP-Southeast-1 (Singapore)**.
6. Click **Next**.
7. On the second page of set one TiDB node, three TiKV nodes, and one TiFlash node.
8. Click **Next**.
9. On the third page, review your settings, and click **Create Cluster**.

Your TiDB Cloud cluster will be created in approximately 5 to 10 minutes.

## Connect to TiDB Cloud

1. Go to [https://tidbcloud.com/console/clusters](https://tidbcloud.com/console/clusters) and sign in to your cluster.
2. On the TiDB Cloud console, in the upper right of the pane, click **Connect**.  
    The **Connect to TiDB** dialog displays.
3. Create the traffic filter for the cluster.
    - Click **Allow Access from Anywhere** in Step 1.
    - Click **Create Filter**.  
    **Note:** For production environments, do not enable Allow Access from Anywhere. Note the information between the -h and -P parameters; you’ll need this for a later step. For example: `mysql -u root -h **tidb.xxx.xxxxx.us-west-prod.aws.tidbcloud.com** -P 4000 -p`.
4. Click the **Web SQL Shell** tab.
5. Click Open SQL Shell and enter the password for the cluster.

You are now able to write SQL commands.

[Request a Demo](https://info.pingcap.com/cs/c/?cta_guid=70c81b6f-6907-4326-9525-7784cc920061&signature=AAH58kFHB9WcgsCBrRBiW8fWeVbytDRQpw&placement_guid=eadb4c3a-ea3a-4f3d-82d1-ea29facc1db1&click=f46499c6-41da-44f9-8c4e-24796bb05c49&hsutk=3d2f0243b7cb60e27f1683b8d9d0dc95&canon=https%3A%2F%2Fen.pingcap.com%2Fblog%2Fbuild-a-real-time-analytics-application-with-tidb-cloud%2F&utm_referrer=https%3A%2F%2Fen.pingcap.com%2Fblog%2F&portal_id=4466002&redirect_url=APefjpFw0fcSNHGV-JVWm3Tr2UkGpwaVvxIrJO3PPNoeefIWmv2etA-E6J-ZEQ0i1pcZC_Hmqlw3K3663lvAMXkTu6e4J_QMEe-cp4-R6xFejHqZpr9nEe6axtL_P4JSw-9ZmThE6fnwLXFkMMnifCZXf6qUGS-m-uNg4Ul00Glxd4aI3wOQXVCtZ5Amt_8qbtFWHCQmjJCuec-e4bvUANmsdbKivvUb0XHcix-AkQaS2pSMH6t8KcD4-Y-rB5HektbexMB_kSwz "Request a Demo")

## Import the sample data

1. Create a database.

    ```{.sql .copyable}
            CREATE DATABASE PingExpressDB;
    ```

2. Create a user of the database. The user name is `'PingExpress_client'`, and you should set your own password to replace `'<pwd>'`, such as `'123'`.

    ```{.sql .copyable}
        CREATE USER 'PingExpress_client' IDENTIFIED BY '<pwd>';
    ```

3. Grant all privileges to the user you just created.

    ```{.sql .copyable}
        GRANT ALL PRIVILEGES ON PingExpressDB.* TO 'PingExpress_client';
    ```

4. Navigate to the TiDB Cloud Clusters page and find your cluster.
5. In the upper right corner of the pane, click **Import**.  
    The **Data Import Task** page is displayed.
6. Enter the following information, and click **Import** to import the sample data:
    - Data Source Type: Select **AWS S3**
    - Bucket URL: `s3://pingexpress-na/backup_dumpling2/`
    - Bucket Region: **Asia Pacific (Singapore)**
    - Data Format: Select **TiDB Dumpling**
    - Setup Credentials: `arn:aws:iam::577523860935:role/pingexpress-na-1g`
    - Target Database:
      - Username: root.
      - Password: Enter your root password.
    - DB/Tables Filter: Leave this field blank.  
      The data import process takes about 5 minutes. When the data import progress bar shows **Success**, you have successfully imported the sample data and the database schema in your database.  
      **Warning**: Do not manipulate the data until the importing process finishes. Otherwise, the importing process fails.
7. Check the imported data.
    1. Open **Web SQL Shell** and enter the cluster password. (Hint: On the TiDB Cloud console, in the upper right of the pane, click **Connect**, then click the **Web SQL Shell** tab.)
    2. Switch to PingExpressDB.

        ```{.sql .copyable}
        USE PingExpressDB;
        ```

    3. Check the number of records

        ```{.sql .copyable}
            SELECT COUNT(*) FROM packages;
        ```

        The result should be `13000000`.
8. Create TiFlash replicas for the table packages.

    ```{.sql .copyable}
        ALTER TABLE packages SET TiFlash REPLICA 1;
    ```

    Wait for a few minutes and then run the following query to check whether the TiFlash node is ready.

    ```{.sql .copyable}
        SELECT * FROM information_schema.TIFLASH_REPLICA;
    ```

When the TiFlash node is ready, the values of the “AVAILABLE” and “PROGRESS” columns turn to 1.

![TiFlash Status](https://en.pingcap.com/wp-content/uploads/2022/03/TiFlash-status-1024x117.png)

[Try TiDB Cloud for Free](https://info.pingcap.com/cs/c/?cta_guid=b9fef609-b6f9-4db1-b558-9ec698fd8a07&signature=AAH58kFJLjuBKTkCRI9_gqq5EsGiYwmDtA&placement_guid=dc83a601-b365-4d6a-82ad-8ba52e45dc0e&click=bdfc6676-01ab-4f91-901b-ba01b842ac47&hsutk=3d2f0243b7cb60e27f1683b8d9d0dc95&canon=https%3A%2F%2Fen.pingcap.com%2Fblog%2Fbuild-a-real-time-analytics-application-with-tidb-cloud%2F&utm_referrer=https%3A%2F%2Fen.pingcap.com%2Fblog%2F&portal_id=4466002&redirect_url=APefjpEPlkOK-4ZoPuEozRhOEwgcp6P0j1It3KCXeI4HBqwQYEkVVxtSZA5gfHiZgi8C-NrZ7axZdloNXWJoXtwsWxOPnlSCudO0FV2XhAXodkCV3291CK4tYPOgg-C2RTbez__dSM1DbRq8slBYZNbIBnXNwn8XEKsHxXQsd7ipxyhpHhnkh0KZQg_LUpcuqPrPHI_riiCfHEFIlImicsmgLVrL0NeLoJqv7Y2VAhW1FGrpW0UqruKg71CYSh8-ePu7nVTH3GNZYW8g5Ds3PK6gf1SZeZ-4q3JcWbOG4DW6tL3cqI0EryY "Try TiDB Cloud for Free")

## Connect to TiDB Cloud via Metabase

In this section, we walk you through the process of connecting to TiDB Cloud via Metabase. For more information, see [How to Connect TiDB Cloud to Metabase](https://en.pingcap.com/blog/how-to-connect-tidb-cloud-to-metabase).

1. Start the Metabase application and click **Let’s get started**.
2. Select your preferred language and click **Next**.
3. Enter your information and account details in Step 2 and click **Next**.
4. In the Add your data step, click the **Database type** drop down menu and select **MySQL**.
5. Specify the following settings:
    - Name: `PingExpressDB`
    - Host: `<your_tidb_cloud_ip_address_obtained_previously>`
    - Port: `4000`
    - Database name: `PingExpressDB`
    - Username: `root`
    - Password: `<password_of_your_tidb_cluster>`
6. Scroll down to the bottom and click **Next**.
7. For the Usage data preference step, change your preference if needed and click **Next**.
8. Click **Take me to Metabase**.

## Build your dashboards in Metabase

This section shows you how to build a Metabase dashboard. The dashboard contains five figures (or “questions” in Metabase terminology). We include the instructions to build the whole dashboard. However, building the first three of them is enough for the purpose of this tutorial. The last two figures are optional. The following figure shows the real-time Metabase dashboard for PingExpress_DemoCorp.

![Metabase dashboard](https://en.pingcap.com/wp-content/uploads/2022/03/Metabase-dashboard-overview-1024x433.png)

1. Create a dashboard.
    1. In the top right corner of the dashboard, click the **+** sign, and then choose **New Dashboard**.
    2. Enter the name as **PingExpress_dashboard**.
    3. Click **Create**.
2. Add a question.
    1. In the top right corner, click **Ask a question** on the top right corner, and then choose **Native query**.
    2. Select **PingExpressDB** as the database.
    3. Display the total number of packages delivered. Enter the following query and click the right side of the screen to run it:

    ```{.sql .copyable}
        SELECT COUNT(*) FROM packages WHERE transaction_kind="4_pkg_out";
    ```

3. Save the question.
    1. In the upper right corner, click **Save**.
    2. Enter the name **Total packages delivered**.
    3. When being asked if you would like to add this question to the dashboard, click **Yes please!**, and choose **PingExpress_dashboard**.  
        The result will now appear on the dashboard.
    4. Click **Save**.
4. Repeat steps 2 and 3 for the second question, “Number of packages on the way.” This is the query to use:

    ```{.sql .copyable}
        SELECT COUNT(*) FROM packages WHERE transaction_kind != "4_pkg_out";
    ```

5. Visualize the **Number of packages in process in each state**.
    1. Repeat step 3 and use the following query instead.

        ```{.sql .copyable}
            SELECT start_state, COUNT(package_id)
        ```

    2. After getting the result, click the **Visualization** button, and then choose **Map**. For the map options:
        - Map Type: Region map
        - Region Map: United States  
          Leave everything else as default.
    3. Repeat step 4 and add this question to the dashboard.  
        ![dashboard](https://en.pingcap.com/wp-content/uploads/2022/03/Add-questions-to-dashboard-1024x802.png)
6. (Optional) Repeat the previous steps to add two more queries:
    - Number of packages in each stage (pie chart):

    ```{.sql .copyable}
          SELECT transaction_kind, count(*)
    ```

    - Number of new packages per day (line chart):

    ```{.sql .copyable}
          SELECT DATE(start_time), count(*)
    ```

[**Request a Demo**](https://info.pingcap.com/cs/c/?cta_guid=70c81b6f-6907-4326-9525-7784cc920061&signature=AAH58kFHB9WcgsCBrRBiW8fWeVbytDRQpw&placement_guid=eadb4c3a-ea3a-4f3d-82d1-ea29facc1db1&click=f46499c6-41da-44f9-8c4e-24796bb05c49&hsutk=3d2f0243b7cb60e27f1683b8d9d0dc95&canon=https%3A%2F%2Fen.pingcap.com%2Fblog%2Fbuild-a-real-time-analytics-application-with-tidb-cloud%2F&utm_referrer=https%3A%2F%2Fen.pingcap.com%2Fblog%2F&portal_id=4466002&redirect_url=APefjpFw0fcSNHGV-JVWm3Tr2UkGpwaVvxIrJO3PPNoeefIWmv2etA-E6J-ZEQ0i1pcZC_Hmqlw3K3663lvAMXkTu6e4J_QMEe-cp4-R6xFejHqZpr9nEe6axtL_P4JSw-9ZmThE6fnwLXFkMMnifCZXf6qUGS-m-uNg4Ul00Glxd4aI3wOQXVCtZ5Amt_8qbtFWHCQmjJCuec-e4bvUANmsdbKivvUb0XHcix-AkQaS2pSMH6t8KcD4-Y-rB5HektbexMB_kSwz)

## Execute the program

1. Clone the program to your local machine, or any virtual machines provided by your cloud vendor.

    ```{.bash .copyable}
        git clone https://github.com/pingcap/education.git
    ```

2. Go to the directory for PingExpress_DemoCorp.

    ```{.bash .copyable}
        cd PingExpress_DemoCorp
    ```

3. Update the connection string in the main method of `PingExpress_client.py`. The connection string is in the format:

    ```{.python .copyable}
        tidb://<client_name>:<pwd>@xxx.xxxx.xxxx.xxxx.prod.aws.tidbcloud.com:4000/<DB_name>
    ```

    For example, if the client name is `PingExpress_client`,  the password is `123`, and the database name is `PingExpressDB`, the connection string is:

    ```{.python .copyable}
        tidb://PingExpress_client:123@xxx.xxxx.xxxx.xxxx.prod.aws.tidbcloud.com:4000/PingExpressDB
    ```

    You can find the host address for TiDB Cloud in **Connect to TiDB** – **Standard Connection** – **Step 2** in the TiDB Console, in the format: `xxx.xxxx.xxxx.xxxx.prod.aws.tidbcloud.com`.

4. Update the timestamp to match the current date:

    ```{.bash .copyable}
        python3 PingExpress_client.py --update_timestamp
    ```

    It may take 5 ~ 10 minutes.

5. Execute the program:

    ```{.python .copyable}
        python3 PingExpress_client.py --execute --clients 50 --duration 100
    ```

    The command assumes there will be 50 clients, and the program runs for 200 seconds.

6. Observe the results. When the program starts running, you can navigate to the Metabase dashboard and observe the dashboard being updated in real time.

## Want to learn more?

Ready to [give TiDB Cloud a try](https://tidbcloud.com/free-trial?_ga=2.179445796.1809300235.1637569164-1325493959.1627016874)? TiDB Cloud [Developer Tier](https://pingcap.com/blog/tidb-cloud-introduces-developer-tier) is now available! It lets you run a TiDB cluster for free for one year on Amazon Web Services. Make sure to follow us on [Twitter](https://twitter.com/PingCAP) to stay updated on TiDB Cloud news!

[How-to](https://en.pingcap.com/blog/?tag=how-to) | [Real-time analytics](https://en.pingcap.com/blog/?tag=real-time-analytics) | [TiDB Cloud](https://en.pingcap.com/blog/?tag=tidb-cloud) | [Tutorial](https://en.pingcap.com/blog/?tag=tutorial)