# Analytics on TiDB Cloud with Databricks

2022-06-29 [Product](https://en.pingcap.com/blog/?category=product)

![tidbcloud-databricks](https://en.pingcap.com/wp-content/uploads/2022/06/tidbcloud-databricks-1024x341.jpg "tidbcloud-databricks")

Author: [Qiang Wu](http://github.com/Daemonxiao) (TiDB Cloud Engineer at PingCAP)  
Editors: [Calvin Weng](http://github.com/dcalvin), Tom Dewan

[**TiDB Cloud**](https://docs.pingcap.com/tidbcloud/public-preview?utm_source=ossinsight&utm_medium=referral) is a fully-managed Database-as-a-Service (DBaaS) for TiDB, an open source distributed SQL database.

[**Databricks**](https://docs.databricks.com/getting-started/introduction/index.html) is a web-based data analytics platform that works with Spark. It combines the best of data warehouses and data lakes into a lake-house architecture.

With a built-in JDBC driver in Databricks, you can now connect TiDB Cloud to Databricks in a few minutes and use Databricks to analyze the data in TiDB. In this article, we will walk you through how to create a TiDB Cloud Developer Tier cluster, connect TiDB to Databricks, and process TiDB data with Databricks.

## **Set up your TiDB Cloud Dev Tier cluster**

To get started with TiDB Cloud, do the following:

1. [**Sign up**](https://tidbcloud.com/free-trial?_ga=2.44480749.81743823.1655813347-1323813425.1649663190&_gac=1.62137694.1655195688.CjwKCAjw46CVBhB1EiwAgy6M4uPB5Z-WGqBSRdHFbQizHZMF--Hu8xSvHjX2F5nV17QRfBrI3kfyVhoC9u0QAvD_BwE) for a TiDB Cloud account and [**log in**](https://tidb.auth0.com/login?state=hKFo2SBTemR5aTNBQTBPMkdCUDlqT2wzX1BZQXhJVVJNOW9CaaFupWxvZ2luo3RpZNkgTDlRWmdXNEItaXBVTEZVVG1IVkRON0l2aUZWWXI5WmSjY2lk2SA2SVp0aENmbVJLSVBFblFTVDhhRGJ0TTdTR2RNbmlSbA&client=6IZthCfmRKIPEnQST8aDbtM7SGdMniRl&protocol=oauth2&response_type=token%20id_token&redirect_uri=https%3A%2F%2Ftidbcloud.com%2Fauth_redirect%3Fprev%3D%2Fconsole%2Fclusters%3Futm_source%3Dossinsight%26utm_medium%3Dreferral&scope=openid%20email&nonce=loJ9qTOSY7MDGQKoQ3Kbh7lKi2DFZ-9s&auth0Client=eyJuYW1lIjoiYXV0aDAuanMiLCJ2ZXJzaW9uIjoiOS4xOS4wIn0%3D).
2. Under **Create Cluster > Developer Tier**, select **1 year Free Trial**.
3. Set your cluster name and choose the region for your cluster.
4. Click **Create**. A TiDB Cloud cluster will be created in approximately 1 to 3 minutes.
5. In the **Overview** panel, click **Connect** and create the traffic filter. Here we add an IP address of 0.0.0.0/0 to allow access from any other IPs.
6. Take note of your JDBC URL, which you will use later in Databricks.

## **Import sample data to TiDB Cloud**

After you create a cluster, it’s time to [**migrate the sample data**](https://docs.pingcap.com/tidbcloud/import-sample-data) to TiDB Cloud. For demonstration purposes, we will use a sample system dataset from Capital Bikeshare, a bicycle-sharing platform. The sample data is released under the Capital Bikeshare Data License Agreement.

1. In the cluster information pane, click **Import**. The **Data Import Task** page is displayed.
2. Configure the import task as follows:
    - Data Source Type: `Amazon S3`
    - Bucket URL: `s3://tidbcloud-samples/data-ingestion/`
    - Data Format: `TiDB Dumpling`
    - Role-ARN: `arn:aws:iam::385595570414:role/import-sample-access`
3. For **Target Database**, enter the **Username** and **Password** of the TiDB cluster.
4. To start importing the sample data, click **Import**. The process takes about 3 minutes.
5. Return to the overview panel and click Connect to Get the MyCLI URL.
6. Use the MyCLI client to check your sample data import:

    ```{.bash .copyable}
    > mycli -u root -h tidb.xxxxxx.aws.tidbcloud.com -P 4000
    ```

## Connect to TiDB Cloud on Databricks

Before you continue, make sure you have logged into your workspace on Databricks with your own account. If you don’t have a Databricks account, sign up for a free one here. If you are an experienced Databricks user and want to import the notebook directly, you can skip to [(Optional) Import the TiDB Cloud example notebook to Databricks](#optional).

In this section, we will create a new notebook on Databricks, attach it to a Spark cluster, and then use the JDBC URL to connect it to TiDB Cloud.

1. In the Databricks workspace, create and attach a Spark cluster as shown below:
    ![Create Notebook Dialog](https://en.pingcap.com/wp-content/uploads/2022/06/databricks-workspace-1024x662.png)
2. Configure JDBC in the Databricks notebook. TiDB can use the default JDBC driver in Databricks, so we don’t need to configure the driver parameter:

    ```{.bash .copyable}
            %scala
            val url = "jdbc:mysql://tidb.xxxx.prod.aws.tidbcloud.com:4000"
            val table = "bikeshare.trips"
            val user = "root"
            val password = "xxxxxxxxxx"
    ```

    where  
             **url**: JDBC URL used to connect to TiDB Cloud  
             **table**: Specify the table, such as ${database}.${table}  
             **user**: The username to use to connect to TiDB Cloud  
             **password**: The password of the user

3. Check the connectivity to TiDB Cloud:

    ```{.bash .copyable}
            %scala
            import java.sql.DriverManager
            val connection = DriverManager.getConnection(url, user, password)
            connection.isClosed()
            res2: Boolean = false
    ```

## Analyze your data in Databricks

Once the connection is established, you can load TiDB data as a Spark DataFrame and analyze the data in Databricks.

1. Load TiDB data by creating a DataFrame for Spark. Here we will reference the variables we defined in the previous step:

    ```{.bash .copyable}
            %scala
            val remote_table = spark.read.format("jdbc")
            .option("url", url)
            .option("dbtable", table)
            .option("user", user)
            .option("password", password)
            .load()
    ```

2. Query the data. Databricks provides a powerful chart display function that customizes the type of chart you want:

    ```{.bash .copyable}
            %scala
            display(remote_table.select("*"))
    ```

    ![Spark Jobs](https://en.pingcap.com/wp-content/uploads/2022/06/databricks-chart-1024x520.png)

3. Create a view or a table for the DataFrame. In our example, we create a temporary view named “trips”:

    ```{.bash .copyable}
        %scala
        remote_table.createOrReplaceTempView("trips")
    ```

4. Query the data using SQL statements. The following statement will query the count of bikes per type:

    ```{.bash .copyable}
        %sql
        SELECT rideable_type, COUNT(*) count FROM trips GROUP BY rideable_type ORDER BY count DESC
    ```

5. Write the analytic results to TiDB Cloud:

    ```{.bash .copyable}
        %scala
        spark.table("type_count")
        .withColumnRenamed("type", "count")
        .write
        .format("jdbc")
        .option("url", url)
        .option("dbtable", "bikeshare.type_count")
        .option("user", user)
        .option("password", password)
        .option("isolationLevel", "NONE")
        .mode(SaveMode.Append)
        .save()
    ```

## (Optional) Import the TiDB Cloud example notebook to Databricks

This is a TiDB Cloud sample notebook that contains steps of [**Connect to TiDB Cloud on Databricks**](#connect) and [**Analyze your TiDB data in Databricks**](#analyze-data). You can import this directly to focus more on the analytic process.

1. In your Databricks workstation, click **Create** > **Import** and paste [TiDB Cloud example URL](https://databricks-prod-cloudfront.cloud.databricks.com/public/4027ec902e239c93eaaa8714f173bcfc/609149376287780/2904026767880673/444172601226222/latest.html) to download a notebook to your own Databricks workspace. ![Databricks Workspace](https://en.pingcap.com/wp-content/uploads/2022/06/DB-workspace-1-1024x572.png)
2. Attach this notebook to your Spark cluster.
3. Replace the JDBC configurations of the example with your own TiDB Cloud cluster.
4. Follow the steps in the notebook and try TiDB cloud with Databricks.

## Conclusion

This article shows how to use TiDB Cloud with Databricks. You can click [here](https://tidbcloud.com/free-trial?_ga=2.44480749.81743823.1655813347-1323813425.1649663190&_gac=1.62137694.1655195688.CjwKCAjw46CVBhB1EiwAgy6M4uPB5Z-WGqBSRdHFbQizHZMF--Hu8xSvHjX2F5nV17QRfBrI3kfyVhoC9u0QAvD_BwE) to try TiDB Cloud now in just a few minutes. In the meantime, we are working on another tutorial about how to connect TiDB from Databricks via [TiSpark](https://github.com/pingcap/tispark), a thin query layer built for running Apache Spark on top of TiDB/TiKV. Subscribe to our blog to stay tuned.

### Tags

[TiDB Cloud](https://en.pingcap.com/blog/?tag=tidb-cloud) | [Tutorial](https://en.pingcap.com/blog/?tag=tutorial) | Databricks | Analytics

[Original blog article](https://en.pingcap.com/blog/using-airbyte-to-migrate-data-from-tidb-cloud-to-snowflake/)