Load Data into Delta Lake on Databricks Lakehouse

Name: Airbyte — Load Data into Delta Lake on Databricks Lakehouse
Author: Airbyte

Learn how to load data to a Databricks Lakehouse and run simple analytics.

Advanced

Simon Späti

updatedJune 18, 2022

connectors used

Databricks Lakehouse

Let's look at what files Airbyte created behind the scenes on the S3 data storage.

I used CyberDuck to connect to S3, but you can connect via cmd-line or the web console. Below is an image that illustrates how that looks:

An illustration of a Databricks Lakehouse destination on AWS S3

Airbyte created several files. Most notably, the `*.snappy.parquet` and the `_delta_log` contain the above-mentioned delta lake transaction log. The content of your source data resides in the parquet files consolidated in a delta table represented as a single folder in the `tutorial_cities` and `tutorial_users` tables. Each table folder contains the delta-log with detailed information about each transaction.

There are other internal folders that Databricks created, such as `copy_into_log` and SSTs produced by the COPY INTO command.

The Airbyte Databricks Lakehouse connector does at its core two following steps:

`CREATE OR REPLACE TABLE AS SELECT * FROM LIMIT 0`
`COPY INTO `

These two transactions are also represented in the transaction log with `00000000000000000000.json/crs` and `00000000000000000001.json/crc`. If you open the transaction log represented by the two JSON and CRC files, you should see a protocol for each transaction that happened and some metadata.

00000000000000000000.json:


{
    "commitInfo": {
        "timestamp": 1654879959245,
        "operation": "CREATE TABLE AS SELECT",
        "operationParameters": {
            "isManaged": "false",
            "description": "Created from stream tutorial_cities",
            "partitionBy": "[]",
            "properties": "{"delta.autoOptimize.autoCompact":"true","delta.autoOptimize.optimizeWrite":"true","airbyte.destinationSyncMode":"append"}"
        },
        "isolationLevel": "WriteSerializable",
        "isBlindAppend": true,
        "operationMetrics": {
            "numFiles": "1",
            "numOutputBytes": "927",
            "numOutputRows": "0"
        }
    }
}

00000000000000000000.crc:


{"tableSizeBytes":927,"numFiles":1,"numMetadata":1,"numProtocol":1,"numTransactions":0}

‍

And the second transaction 00000000000000000001.json with `COPY INTO`:


{
    "commitInfo": {
        "timestamp": 1654879970063,
        "operation": "COPY INTO",
        "operationParameters": {},
        "readVersion": 0,
        "isolationLevel": "WriteSerializable",
        "isBlindAppend": true,
        "operationMetrics": {
            "numFiles": "1",
            "numOutputBytes": "2085",
            "numOutputRows": "3"
        }
    }
}

00000000000000000001.crc:


{"tableSizeBytes":3012,"numFiles":2,"numMetadata":1,"numProtocol":1,"numTransactions":1}

Step 6: Query data with Databricks Notebook

Wrapping Up

This tutorial taught you about the Databricks Lakehouse Platform and how to use Airbyte’s Databricks Lakehouse destination connector to synchronize data from PostgreSQL. Apart from the Postgre source, Airbyte can be used to connect to any of the supported data sources.

Now that you experienced how easy it is to move data with Airbyte, you might want to check our fully managed solution: Airbyte Cloud. The cloud solution allows you to unify your data integration pipelines under one fully managed platform.

We also invite you to ‍join the conversation on our community Slack Channel to share your ideas with thousands of data engineers and help make everyone’s project a success. With Airbyte, the integration possibilities are endless, and we can't wait to see what you will build.

Or, Check Out the Video

The above-detailed analysis helps you to understand what happened under the hood. But the real power becomes apparent when querying the data. To query the data, you can use a Collaborative Notebook in the Databricks Workspace you created earlier.

Below the `dbutils.fs.ls` files are listed as Delta Tables with a name and a schema, like in any relational database. Then you can use `df.printSchema() ` for printing the Schema. Based on that, you can now create extensive analytics with simple BI dashboarding or sophisticated Python or machine learning logic.

Querying the synced data from the Airbyte destination within a Databricks Notebook

In the image above, I used Python to read a delta lake table into a dataframe and displaying it with python. Another way is with SQL. You can run any SQL against the table if you create a temporary view called cities with `df.createOrReplaceTempView("cities")`. With the magic command `%sql`, you can write arbitrary SQL and visualize the response with an extensive choice of charts.

Example Visualization in Databricks Notebooks

Other charts and languages that are supported as of now in a Databricks notebook are:

Supported Charts and Programming Languages in Databricks Notebooks

The demo notebook used above you can download from airbyte-demo.ipynb or airbyte-demo.dbc.

Build agents on live data

The Free plan includes 1,000 Agent Operations and 50+ agent connectors.

Try it free