Skip to main content

Staging for Databricks

Justin Stevenson
Updated

Staging for Databricks

While running or , Fivetran Local Data Processing can put data into a temporary directory (‘staging file') before loading data into a target database. For best performance, Local Data Processing performs and into Databricks using staging files. Local Data Processing implements the file staging on Databricks as follows:

  1. Local Data Processing first writes data into the staging directory on the configured platform (AWS or Azure).
  2. Local Data Processing then uses the Databricks SQL command copy into to ingest data into the target tables from the staging directory.

Configuring Staging

Local Data Processing must be configured to stage the data on a staging platform (AWS or Azure) before loading it into Databricks.

Databricks on AWS

Since v6.1.0/13

For staging the data on AWS S3 and performing or , the following are required:

  1. An AWS S3 location (bucket) to store temporary data to be loaded into Databricks. For more information about creating and configuring an S3 bucket, refer to the .

  2. An AWS user with AmazonS3FullAccess permission policy to access the S3 bucket. Alternatively, an AWS user with minimal set of permission can also be used:

    Click here for minimal set of permissions.
    • s3:GetBucketLocation
    • s3:ListBucket
    • s3:ListBucketMultipartUploads
    • s3:AbortMultipartUpload
    • s3:GetObject
    • s3:PutObject
    • s3:DeleteObject
    Sample JSON with a user role permission policy for S3 location
    {
    "Statement": [
        {
            "Sid": <identifier>,
            "Effect": "Allow",
            "Principal": {
                    "AWS": "arn:aws:iam::<account_id>:<user>/<username>",
            },
            "Action": [
                    "s3:GetObject",
                    "s3:GetObjectVersion",
                    "s3:PutObject",
                    "s3:DeleteObject",
                    "s3:DeleteObjectVersion",
                    "s3:AbortMultipartUpload"
            ],
            "Resource": "arn:aws:s3:::<bucket_name>/*"
        },
        {
            "Sid": <identifier>,
            "Effect": "Allow",
            "Principal": {
                    "AWS": "arn:aws:iam::<account_id>:<user>/<username>"
            },
            "Action": [
                    "s3:ListBucket",
                    "s3:GetBucketLocation",
                    "s3:ListBucketMultipartUploads"
            ],
            "Resource": "arn:aws:s3:::<bucket_name>"
        }
    ]
}
content_copy

    For minimal permission, Local Data Processing also supports the . There are two ways to request for the AWS Security Token Service (STS) temporary credentials:

    • Using a combination of AWS STS Role ARN, AWS Access Key Id, and AWS Secret Access Key

      Sample JSON

      {
     "Version": "2012-10-17", 
     "Statement": [ 
         {
             "Sid": "", 
             "Effect": "Allow", 
             "Principal": { 
                     "AWS": "arn:aws:iam::<account_id>:<user>/<username>"
              },
             "Action": "sts:AssumeRole"
         } 
     ] 
}
content_copy

    • Using a combination of AWS STS Role ARN and AWS IAM Role (a role that has access to an EC2 machine)

      Sample JSON

      { 
     "Version": "2012-10-17",
      "Statement": [ 
          { 
               "Sid": "",
               "Effect": "Allow",
               "Principal": {
                        "AWS": [ 
                              "arn:aws:iam::<account_id>:<user>/<username>", 
                              "arn:aws:iam::<account_id>:<role>/<username>"
                         ] 
                },
               "Action": "sts:AssumeRole" 
          } 
     ]
 }
content_copy

    For more information on the Amazon S3 permissions policy, refer to the .

    For more information, refer to the following AWS documentation:

  3. Define the following location properties (while or by ) for the Databricks location:

    The equivalent to the UI field is shown inside (brackets) below.

    1. STORAGE (): protocol for connecting Local Data Processing to S3.

      • Select Amazon S3 (HTTPS).

    2. BUCKET (): Name or IP address of the Amazon S3 bucket.

    3. Credentials or Instance Profile: AWS security credentials. For more information about getting your AWS credentials or Instance Profile Role, refer to the .

      • If Credentials is selected, the location property KEY ID () and SECRET KEY () must be supplied.

        • KEY ID: Access key ID of IAM user for connecting Local Data Processing to Amazon S3.

        • SECRET KEY: Secret access key of IAM user for connecting Local Data Processing to Amazon S3.

      • If Instance Profile is selected, the location property IAM ROLE () must be supplied.
        • IAM ROLE: AWS IAM role name for connecting Local Data Processing to Amazon S3.

    4. STAGING DIRECTORY (): location where Local Data Processing will create the temporary staging files inside the S3 bucket (e.g. /my_staging_dir).

    5. Create Delta Tables in an External Location: Select this option if you want to create external tables, and then define the following location properties available under this option -

      1. EXTERNAL LOCATION (): Path for the external tables in Databricks. This can be a mount path /mnt/... (optionally prefixed with dbfs:) or an s3:// URL. If a path is defined without specifying the dbfs:/ or s3://, it is assumed to be a mount path beginning with dbfs:/.

      2. EXTERNAL LOCATION S3S URL (): URL (s3s://) for the external tables in Databricks. This is required only if the EXTERNAL LOCATION is set to a mount path (/mnt/.. or dbfs:/).

        s3s is same as accessing Amazon S3 using HTTPS.

By default, Local Data Processing connects to us-east-1 once for determining your . If a firewall restriction or a service such as Amazon Private Link is preventing the determination of your S3 bucket region, you can change this region (us-east-1) to the region where your S3 bucket is located by defining the following action:

GroupTableAction/Parameters
Databricks* -
=HVR_S3_BOOTSTRAP_REGION,
=s3_bucket_region

Databricks on Azure

For staging the data on Azure (ADLS) and performing or , the following are required:

  1. An ADLS storage location (container) to store temporary data that are to be loaded into Databricks on Azure.

  2. An ADLS user (storage account) to access the container.

  3. Define the following location properties (while or by ) for the Databricks on Azure location:

    The equivalent to the UI field is shown inside (brackets) below.

    1. STORAGE (): Protocol for connecting Local Data Processing to Databricks on Azure.
      • Select Azure Data Lake Storage Gen2 (HTTPS).
    2. ACCOUNT (): Name of the ADLS account.
    3. CONTAINER (): Name of the container available within the ADLS account.
    4. AUTHENTICATION METHOD (): Authentication method for connecting Local Data Processing to the ADLS server. Available option is
      • Client Credentials
    5. OAUTH2 ENDPOINT (): URL used for obtaining the bearer token with credential token.
    6. CLIENT ID (): Client ID (or application ID) used for obtaining the Azure AD access token.
    7. CLIENT SECRET KEY (): Secret key of the CLIENT ID.
    8. STAGING DIRECTORY (): Location where Local Data Processing will create the temporary staging files inside the Google Cloud Storage bucket (e.g. /my_staging_dir).
    9. Create Delta Tables in an External Location: If you want to create external tables, then define the following location properties available under this option -
      1. EXTERNAL LOCATION (): Path for the external tables in Databricks. This can be a mount path /mnt/... (optionally prefixed with dbfs:) or an abfss://... URL. If a path is defined without specifying the dbfs:/ or s3://, it is assumed to be a mount path beginning with dbfs:/.
      2. EXTERNAL LOCATION ABFSS (): URL (abfss://) for the external tables in Databricks. This is required only if the EXTERNAL LOCATION is set to a mount path (/mnt/... or dbfs:/...).

Comments

0 comments

Please sign in to leave a comment.

Powered by Zendesk