Exporting data to Databricks using AWS S3 staging

Introduction

Data from a Darwinium node can be configured to export to Databricks tables. This page describes the steps required to followed to enable a data sink that represents a Databricks table.

High level architecture

Darwinium event data is exported into Databricks table by using a staging approach. The data is exported at regular intervals into an S3 bucket configured by the customer. This bucket is henceforth referred to as the staging bucket. The staging bucket is configured to be a Databricks external location and is processed at regular intervals to ingest data into the Databricks streaming table.

The following diagram depicts the high level architecture diagram for data export into a Databricks sink using AWS s3 as the staging location.

The following external links might be useful to read:

• Unity Catalogue
• Databricks Auto Loader

The following are the salient features of the export process:

• The data is exported using parquet format.
• The dataset is organized into prefix/folder structure using the following layout inside the s3 bucket.
  • A fixed folder called dataexport/events
  • A variable component (based on the portal configuration) - <node_id>/<dataset_name>. The value of the node id can be obtained from the node settings section of portal.
  • A hive style partitioning layout (described in the next point)
• The "folder" layout on S3 is a hive-partitioned style using an hourly format as the lowest dimension of granularity.
• The partition columns are based on time dimension and are fixed to be the following hierarchy: p_year, p_month, p_day, p_hour
  • An example partition folder structure will look like the following for all events generated between 4 PM and 5 PM UTC on the 25th of August 2025. p_year=2025/p_month=8/p_day=25/p_hour=16
• All times are UTC aligned on the file layouts.
• Assuming the node id is "eb1658c2-b947-49fe-b5b8-fbfe614b01ae" and the dataset was named "darwinium_all_events" in portal configuration, the data for the above example time frame can be located in the following path in the S3 bucket.

dataexport/events/eb1658c2-b947-49fe-b5b8-fbfe614b01ae/darwinium_all_events/p_year=2025/p_month=8/p_day=25/p_hour=16/

Points to note

The following points need to be considered while configuring the data sinks inside the portal.

• Data is expected to be visible in Databricks tables in hourly intervals.
• Currently only one sink type is supported with more sinks supported in the near future.
• There is a possibility of some duplicates occurring in some scenarios. Please consider this while designing downstream consumptions. The best way to resolve duplicates downstream is to use the "identifier" column and "update_ver" columns.
• It may be noted that only ASCII characters are allowed as dataset names in the portal configuration.
• A dataset name may not be repeated for a given node across different sinks.
• The BYOS storage bucket can be reused for the data export process. This is because the data export is written to its own prefix path.
• GDPR controls are not supported by Darwinium and has to be managed by the customers.
• The mechanism to expire staging data is to be managed by the customer teams.

High level overview of the configuration process

The following is a high level summary of the configuration steps that need to be followed. The steps assume there is an S3 bucket created. One can choose to use an existing bucket provided there is no prefix path collision across other apps. Also if you are already using BYOS S3 bucket for portal data , the same bucket can be used.

1. Create a Role and attach a policy for S3 Writes on the chosen bucket
2. Create a configuration in Darwinium portal to enable a Databricks export
3. Attach a trust policy to the role created in step 1 to allow Darwinium to assume role.
4. Create a new catalogue in Databricks
5. Register an external location in Databricks
6. Wait for some data to be exported into staging s3 bucket.
7. Create a new Databricks table definition

Step 1 - Create a Role and attach s3 write policy

First we create an AWS role and attach an S3 write policy on the staging bucket.

• The ARN of the S3staging bucket which will be used to stage the data export. The ARN is referred to as "S3BUCKETARN" in the example below. An example S3 bucket ARN looks like this: arn:aws:s3:::dwn-customer-databricks-bucket

Create an AWS policy that looks similar to the following. This policy is allowing anyone who assumes this role to write to the S3 bucket. Replace the value S3BUCKETARN in the snippet below with the s3 bucket ARN of the staging bucket.

{
    "Statement": [
        {
            "Action": [
                "s3:PutObject"
            ],
            "Effect": "Allow",
            "Resource": "S3BUCKETARN/dataexport/*"
        },
        {
            "Action": [
                "s3:GetBucketLocation"
            ],
            "Effect": "Allow",
            "Resource": "S3BUCKETARN"
        }
    ],
    "Version": "2012-10-17"
}

Every role has an ARN associated with it. An example role ARN looks like the following: arn:aws:iam::123456789012:role/iamrole-darwinium-data-export. Please note the ARN of the role as created in this step.This role ARN is required in the next step.

Step 2 - Create Data Sink configuration in Portal

The following information is needed to complete this step.

• A name that you have chosen to represent the exported dataset. Ex: darwinium_all_events
• The name of the staging s3 bucket. Ex: s3-bucket-for-darwinium-data-exports
• The region of the staging bucket. Ex: us-east-2
• The role ARN that Darwinium will be allowed to assume (and is obtained in the previous step).. Ex: arn:aws:iam::123456789012:role/iamrole-darwinium-data-export

In the Darwinium portal, navigate to Admin > Nodes. Then select the "Data Storage" tab at the top of the screen.

Fill in the values by using the information mentioned above. The configuration screen creates an external ID. Please treat this external ID as a secret.

**The external ID will not be visible once the configuration is saved and hence needs to be noted down in a secure location. **

Step 3 - Attach trust policy

We will need to attach a trust relationship to the role defined in step 1. The trust relationship will allow Darwinium account ID roles to assume the role created in step 1.

The following information is needed to complete this step.

• The external ID as obtained in the previous step. Ex: 76e7ec0e-152b-40cc-b88b-579357b84754
• The Role ARN that was used in Step 2 of the configuration screen.
• Darwinium Account ID. Please get this from your Darwinium Customer Service contact. Ex: 123456789012

Here is an example Trust attachment policy that attaches to the above role (created in step 1). Please remove the DWNACCOUNTID (E.g. 123456789012) and the EXTERNALID (E.g: 76e7ec0e-152b-40cc-b88b-579357b84754) in the example below with the values that you have.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::DWNACCOUNTID:root"
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "sts:ExternalId": [
                        "EXTERNALID"
                    ]
                }
            }
        }
    ]
}

Step 4- Create a Unity catalog (Optional)

Please note that this step is optional and is not required if there is a unity catalogue that is already available for use.
Please note that the unity catalogue needs to be compatible with the Databricks Autoloader process.

The following link provides some useful information to create a unity catalogue. Please get in touch with your organization's Databricks administrator to execute this step for you if you are not familiar with unity catalogue.

• Getting started with Unity Catalogue

Please note that the User permissions and access controls are not controlled by Darwinium and is to be entirely managed by the Databricks administrators of your organization.

Please note that schemas are managed by the autoloader and a schema need not be explicitly created in this step.

Step 5 - Register AWS S3 staging bucket as an external location

We now make the catalogue in Databricks aware of the AWS s3 that represents the staging location.

There are many ways to associate an external staging location for a Databricks table. Please contact your organization's Databricks administrator to execute this step. The following Databricks documentation could be useful to define an external location for your catalogue.

• Define an external location

Step 6 - Accumulate some data

Ensure that there is atleast a couple of hours difference after completing step 3 (Role is created and verified to be working) and the next step. We do this so that there is some data accumulated in the staging bucket using which the schema generation step becomes trivial.

Please traverse to S3 staging location and see if there are some parquet files in the staging bucket to confirm this.

We do this so that the schema generation step is a smooth process.

Step 7 - Create the streaming table definition

The following information is needed to complete this step.

• A database name into which the streaming table will be provisioned into.

• The s3 staging bucket name Ex: darwinium-staging-bucket

• The node id for which the data export is configured for. The node id can be obtained by traversing to Admin > nodes section in the portal and looking at the first stab in that screen. The node ID that is being used in the example below is "eb1658c2-b947-49fe-b5b8-fbfe614b01ae".

• The name of the dataset that was configured in step 2. Ex: darwinium_all_events.

• Login to the Databricks workspace.

• Choose the workspace that has access to the Unity Catalogue.

• Navigate to the SQL editor menu.

• Ensure you are in the right catalogue and database.

• Optionally create the database if the desired database does not exist.

• Run the following to create a streaming table definition.

**Please replace the values in the below example relevant to your configuration.
**Note that the partitioning clause is static and cannot be changed. The partitioning column in this case is (p_year, p_month, p_day, p_hour)

CREATE OR REFRESH STREAMING TABLE events PARTITIONED BY (p_year, p_month, p_day, p_hour) SCHEDULE EVERY 1 HOUR AS SELECT * FROM STREAM read_files ('s3://darwinium-staging-bucket/dataexport/events/eb1658c2-b947-49fe-b5b8-fbfe614b01ae/darwinium_all_events',  format => 'parquet') 

This completes all the configuration steps to create a streaming databricks table that will contain Darwinium processed events at hourly intervals. Please note that 1 HOUR is the smallest interval possible in Databricks.