How-to: setup Azure DevOps repository

In this how-to, we setup an existing Azure DevOps repostory to with ADM. At the end of this how-to-guide, you will have an operational actions workflow with which you can provision your Snowflake cloud database.

We will use the following technologies

Technologies

  1. a Azure-DevOps-repository

  2. Azure DevOps Pipeline file, that holds the configuration for CI/CD

  3. add members/owners to the repository

Prerequisites

  1. Have an empty Azure DevOps repository where we can setup an ADM-configuration.

Connect Snowflake to ADM

  1. Clone the repository to your local machine

  2. Add the file environments/development.yml and fill it with the following contents. This will create an ADM-environment called development that ADM uses to connect to Snowflake.

Listing 1. Setup an ADM-environment called development
{
  "name": "development",(1)
  "description": "Snowflake environment called 'development'.",
  "connector": "snowflake",
  "security": {
    "connector": "variables",
    "endpoint_variable": "DEVELOPMENT_SNOWFLAKE_ENDPOINT",
    "username_variable": "DEVELOPMENT_SNOWFLAKE_USER",
    "password_variable": "DEVELOPMENT_SNOWFLAKE_PASS"
  }
}

Setup secrets

Next, we assign values to the environment variables that we defined in the previous step.

  1. Login to Azure DevOps, and go to your repository.

  2. Go to menu:Variables[Pipelines > Select Pipeline > Edit > Variables]and add the following variables.

Variable Key Variable Value Definition

DEVELOPMENT_SNOWFLAKE_ENDPOINT

development.eu-west-1

Name of the Snowflake account

DEVELOPMENT_SNOWFLAKE_USER

SF_ACHERON_AUTO_EXECUTER

The name of the Snowflake user created earlier

DEVELOPMENT_SNOWFLAKE_PASS

<developments-snowflake-password>

The password of the Snowflake user created earlier

AZURE_REPO_VERSION

:v3

SSH version that microsoft uses for the SSH url
Example screen Azure Devops variables
Figure 1. Example Screenshot of variables screen

Load SSH key

SSH public key authentication works with an asymmetric pair of generated encryption keys. The public key is shared with Azure DevOps and used to verify the initial ssh connection. The private key is kept as secure file in the pipeline library

  1. Generate a SSH public/private key, How SSH key authentication works

Load public key

Load your public key to your Azure Devops account, Set up SSH key authentication

Load private key

  1. Go to Pipelines:SecureFiles[Pipelines > Library > Secure Files]

Load your private key by clicking "+ Secure file", and upload the key:

Example screen Azure Devops Secure file
Figure 2. Example Screenshot of Secure file upload

Click "OK"

  1. After adding go to Secure file PipelinePermissions:SecureFile[Pipelines > Library > Secure Files > Secure file > Pipeline Permissions]

Add your pipeline by cliking the "+" and select your pipeline

Example screen Azure Devops Secure file pipeline permissions
Figure 3. Example Screenshot of Secure file upload
Example screen Azure Devops Secure file pipeline permissions selected
Figure 4. Example Screenshot of Secure file upload with pipeline selection
Example screen Azure Devops Secure file success
Figure 5. Example Screenshot of Secure file successful uploaded

Create pipeline setup

A preconfigured azure-pipelines.yml holds the configuration of the Azure DevOps pipeline. Add it to the root folder of your repository.

Define a pipeline

trigger:
  - '*'

variables:      # pipeline-level
  VMIMAGE: 'Ubuntu-20.04'
  GIT_COMMIT_COMMITTER_EMAIL: "rob@acheron.cloud"
  SSH_FILE: 'acheron_rsa'

resources:
  containers:
  - container: adm
    image: acheronbv/acheron-database-manager:latest-1.3.33 (1)

stages: (2)
- stage: lint (3)
  condition:  and(succeeded(),
              not(eq(variables['build.SourceVersionMessage'], 'Commit logs')))
  displayName: Lint stage
  jobs:
  - job: lint
    displayName: Lint stage
    container: adm
    pool:
      vmImage: '$(VMIMAGE)'
    steps:
    - bash: |
        echo "Linting ..."
        python $(ls -d "/usr/local/lib/"python*/ | tail -1)/site-packages/opt/acheron/lint.py "$@"

- stage: template (4)
  condition:  and(succeeded(),
              not(eq(variables['build.SourceVersionMessage'], 'Commit logs')))
  displayName: Template stage
  jobs:
  - job: template
    displayName: Template stage
    container: adm
    pool:
      vmImage: '$(VMIMAGE)'
    steps:
    - task: DownloadSecureFile@1
      name: ssh_key
      displayName: 'Download SSH key'
      inputs:
        secureFile: '$(SSH_FILE)'
    - bash: |
        echo "Templating ..."
        export SSH_PRIVATE_KEY=$(< $(ssh_key.secureFilePath))
        eval $(ssh-agent -s)
        python $(ls -d "/usr/local/lib/"python*/ | tail -1)/site-packages/opt/acheron/template.py "$@" adm-demo-environment --ssh-dir=$HOME/.ssh/
      env:
        System.CollectionUri:       $(System.CollectionUri)
        Build.RequestedForEmail:    $(Build.RequestedForEmail)
        Build.RequestedForId:       $(Build.RequestedForId)
        Build.SourceVersion:        $(Build.SourceVersion)
        System.TeamProject:         $(System.TeamProject)
        Build.Repository.Name:      $(Build.Repository.Name)
        GIT_COMMIT_COMMITTER_EMAIL: $(GIT_COMMIT_COMMITTER_EMAIL)
        AZURE_REPO_VERSION:         $(AZURE_REPO_VERSION)

- stage: execute (5)
  condition:  and(succeeded(),
              eq(variables['build.SourceVersionMessage'], 'Commit new Container run results'),
              eq(variables['Build.SourceBranch'], 'refs/heads/master'),
              not(eq(variables['build.SourceVersionMessage'], 'Commit logs')))
  displayName: Execute stage
  jobs:
  - job: execute
    displayName: Execute stage
    container: adm
    pool:
      vmImage: '$(VMIMAGE)'
    steps:
    - task: DownloadSecureFile@1
      name: ssh_key
      displayName: 'Download SSH key'
      inputs:
        secureFile: '$(SSH_FILE)'
    - bash: |
        echo "Executing to Snowflake"
        export SSH_PRIVATE_KEY=$(< $(ssh_key.secureFilePath))
        eval $(ssh-agent -s)
        python $(ls -d "/usr/local/lib/"python*/ | tail -1)/site-packages/opt/acheron/execute.py "$@" adm-demo-environment --ssh-dir=$HOME/.ssh/
      env:
        ACHERON_SNOWSQL_ENDPOINT:   $(ACHERON_SNOWSQL_ENDPOINT)
        ACHERON_SNOWSQL_PASS:       $(ACHERON_SNOWSQL_PASS)
        ACHERON_SNOWSQL_USER:       $(ACHERON_SNOWSQL_USER)
        System.CollectionUri:       $(System.CollectionUri)
        Build.RequestedForEmail:    $(Build.RequestedForEmail)
        Build.RequestedForId:       $(Build.RequestedForId)
        Build.SourceVersion:        $(Build.SourceVersion)
        System.TeamProject:         $(System.TeamProject)
        Build.Repository.Name:      $(Build.Repository.Name)
        GIT_COMMIT_COMMITTER_EMAIL: $(GIT_COMMIT_COMMITTER_EMAIL)
        AZURE_REPO_VERSION:         $(AZURE_REPO_VERSION)
1 Fill in the exact version number of the Acheron ADM container that you’re using. In case this is unclear, feel free to contact Acheron (info@acheron.cloud)
2 The stages that will be executed every time that a change is pushed to Azure DevOps - Azure DevOps pipeline
3 Lint-step evaluates the input files
4 Template-step renders templates to SQL-files
5 Execution-step, executes SQL-changes on the desired Snowflake Account

Run the pipeline for the first time

  1. Create a new branch

  2. Load the license

  3. Add a template

  4. Add a template-configuration to the input directory

  5. After a push to origin, the linter starts running

  6. Merge the branch

    1. This will trigger the Templater to make a commit

    2. The executer will execute your code to SF environment