You can translate the document:

Introduction

This document illustrates a sample CI/CD Pipeline implemented using Bitbucket, and the Denodo Solution Manager. The subsequent diagram will act as a guide for the steps we are about to implement:

Prerequisites

This document presupposes that your Version Control System (VCS) is already configured, with Bitbucket being the example in our case. Additional details regarding the integration with your VCS can be found in the Version Control Systems Integration section of the Virtual DataPort Administration Guide.

Furthermore, it is assumed that you already possess access to a Bitbucket account linked to the primary repository of your VCS.

Bitbucket configuration

Register on Bitbucket and establish a new repository. Ensure that you have installed Git on your system and configured it accurately.

To link Bitbucket with Denodo, it is necessary to set up an SSH Key. Refer to this documentation link for further information.

Set up a Bitbucket repository

After successfully creating a repository in Bitbucket, we need to configure VCS in Denodo to point to this repository:

Set up the Bitbucket Pipeline

Once the bitbucket repository is configured and synced with the local repository, we need to create a Bitbucket Pipeline. Configuring a Bitbucket Pipeline involves defining a set of steps in a bitbucket-pipelines.yml file in your repository. Refer to this document for additional information on Pipelines.

Set up a runner in the local machine

In the bitbucket-pipelines.yml file, there is a series of steps outlined for execution. To enable the pipeline for building on our infrastructure, the configuration of a runner is necessary. Further details on this process are provided in this document.

To configure a runner, follow these steps:

  1. Login to your Bitbucket repository.
  2. At the bottom of the left menu, navigate to the “Runners” section under “Features” and click on it.
  3. Click on “Add runner”.
  4. Fill out the required fields and pick the dedicated environment.


  1. Execute the provided commands in a command-line editor:


  1. Once the runner is up we should be able to see the following in the output of the last command :


        

 Step by step overview of the reference procedure

The objective of this process, given the presence of two distinct environments (referred to as DEV and TEST), both governed by a unified Solution Manager and utilized by a team of developers, is to automate the steps that follow immediately after a team member has pushed a change. When broken down, this process will include the following steps:

  1. Developers work on a private Virtual Database.
  2. A change is pushed to the VCS repository (in our case Bitbucket Repository).
  3. The push to the VCS notifies Bitbucket Pipelines.
  4. Bitbucket Pipelines triggers the pipeline to execute the defined jobs and tasks.
  5. A revision is created from a VQL using the Solution Manager REST API.
  6. The Revision is deployed in the target environment.

Optionally, we can include an additional step to implement the Denodo Testing tool.

The upcoming sections will demonstrate the implementation of the final steps within the Bitbucket Pipeline, utilizing powershell commands and a Python script.

Bitbucket initiates a request to the VDP Server to retrieve data from the shared VCS repository

To trigger a pull in the master database from Bitbucket, you can utilize the Denodo import Script, incorporating a VQL File containing the DVCSPULL command for the primary database.

Note: Since Bitbucket Pipelines does not run locally, it is necessary to set up a runner as mentioned earlier in this document.

  • Create a VQL file containing the DVCSPULL command:

touch vcscommand.vql #creates the vql file

echo "DVCSPULL <DATABASE>;" > vcscommand.vql #adds the command to the VQL file

  • Specify the Import Script command

bash .//import.sh --singleuser --file "<FILE_LOCATION>/vcscommand.vql" --server <VDP_SERVER_IP>:9999/<DATABASE>?<USERNAME>@<PASSWORD>

These commands aim to synchronize our local database with the version control system (VCS) repository.

In the bitbucket repository, the bitbucket-pipelines.yml file will consist of two primary steps, and below is the initial step that will execute this operation:

step:

       name: 'Create VQL'

       runs-on:

         - self.hosted

         - windows

       script:

         # Setting up parameters for scripts and repository path

         - $importBatPath = "C:\Denodo\DenodoPlatform\bin"

         - $scriptsPath = "C:\scripts"

         - $localrepoPath = "C:\Denodo\DenodoPlatform\work\vdp\repository\dev_bb1"

         # Create VQL file with DVCSPULL command

         - echo "DVCSPULL dev_bb1" > $scriptsPath"\vcscommand.vql"

         # Use import script to execute PULL

         - Start-Process -FilePath $importBatPath"\import.bat" -ArgumentList "--singleuser", "--file", "C:\scripts\vcscommand.vql", "--server", "localhost:9999/dev_bb1?admin@admin" -NoNewWindow -Wait

         # Create the VQL export

         - Start-Process -FilePath $importBatPath"\import.bat" -ArgumentList "--singleuser", "--repository", $localrepoPath, "--file", $localrepoPath"\deploymentvql.vql" -NoNewWindow -Wait

Let’s break down the upper code:

  • Step name: This sets the name of the step as ‘Create VQL’.
  • Runs-on: Specifies that the step should run on a self-hosted runner and is specifically designed for a Windows environment.
  • Script section:
    - Initializes PowerShell variables to store paths for the Denodo import script, script files and the local repository.
    - Creates a VQL script named
    vcscommand.vql in the specified scripts path and writes the command DVCSPULL dev_bb1 into it.
    - Use import script to execute PULL: Synchronizing local repository with the VCS one.
    - Create the VQL export using the Denodo import script.

Create a revision from VQL and deploy it using the Solution Manager REST API

Once the VQL script is ready, we can proceed with the next step which is creating a revision.

To achieve this, we can leverage the Solution Manager REST API to create a revision from VQL.

Note: By generating a unified VQL file through the import script, the file will contain the following VQL lines:

  • First line : ENTER SINGLE USER MODE
  • Last line : EXIT SINGLE USER MODE

One of the guidelines outlined in the previously mentioned Create Revision from VQL documentation emphasizes the necessity of removing these lines from the VQL script. Failure to comply with this requirement may lead to an unsuccessful deployment of the revision, as illustrated in the screenshot below:

This can be accomplished using different methods, in our case we will use a Python script. The code snippet below executes the deletion:

import os

input_file = r'C:\Denodo\DenodoPlatform\work\vdp\repository\dev_bb1\deploymentvql.vql'

output_file = r'C:\Denodo\DenodoPlatform\work\vdp\repository\dev_bb1\deploymentvql_final.vql'

# Read content from the input file

with open(input_file, 'r') as infile:

    lines = infile.readlines()

# Remove the first and last lines

filtered_lines = lines[1:-1]

# Write filtered lines to the output file

with open(output_file, 'w') as outfile:

    outfile.writelines(filtered_lines)

   

# Delete the input file

os.remove(input_file)

# Rename the output file to the original input file name

os.rename(output_file, input_file)

The subsequent action involves initiating the /revisions/loadFromVQL POST request within the Solution Manager REST API.

The request body for this POST request follows this format:

{

    "name": <text>, /* descriptive name for the revision*/

    "description": <text>,  /* optional. Extensive description about the revision. */

    "content": <text> /* vql file content as **xsd:base64Binary** encoded in **UTF-8** */

}

Note: The “content” field should contain the content of the VQL file encoded in UTF-8 using xsd:base64Binary encoding.

Ultimately, you can initiate the deployment of the revision by utilizing the /deployments POST request in the Solution Manager REST API.

The request body for this POST request follows this format:

{

    "revisionIds": [ <number>, ... ],

    "environmentId": <number>,

    "description": <text>

}

A potential approach for executing the /revisions/loadFromVQL POST request, encoding the VQL file in UTF-8, and initiating the /deployments POST request is to consolidate these actions into a unified script. This script would take the necessary parameters for the POST requests as input, offering a streamlined representation of complexity within the Bitbucket Pipeline. Furthermore, the script could be employed across various CI/CD jobs, necessitating only a modification in input parameters during its invocation.

In the following example, you will find the Python code for a script that consolidates these functionalities:

import json

import argparse

import requests

import base64

import csv

import pdb

# data for calling the solution manager APIs

parser = argparse.ArgumentParser()

parser.add_argument("-s", "--host", type=str)

parser.add_argument("-p", "--port", type=str)

parser.add_argument("-v", "--vqlfile", type=str)  # vql file location

parser.add_argument("-n", "--revname", type=str)  # revisions/loadFromVQL parameter

parser.add_argument("-d", "--revdescription", type=str

)  # /revisions/loadFromVQL parameter

parser.add_argument("-e", "--environmentId", type=int)  # /deployments parameter

parser.add_argument("-c", "--deploymentDescription", type=str)  # /deployments parameter

parser.add_argument("-u", "--username", type=str)

parser.add_argument("-a", "--password", type=str)

revision_json = None

deployment_json = None

# place script arguments into variables:

args = parser.parse_args()

host = args.host

port = args.port

vqlfile = args.vqlfile

name = args.revname

rev_description = args.revdescription

environment_id = args.environmentId

deployment_description = args.deploymentDescription

username = args.username

password = args.password

deployment_url = "http://" + host + ":" + port + "/deployments"

revision_url = "http://" + host + ":" + port + "/revisions/loadFromVQL"

# parsing vql file as text for input into revision API

# enforce base64 UTF-8 encoding necessary to create the revision

def convert_to_base64_without_bom():

    try:

        with open(vqlfile, "r", encoding="utf-8-sig") as file:

            vql = file.read()

    except Exception as e:

        print(e)

    base64_bytes = vql.encode("utf-8")

    base64_encoded = base64.b64encode(base64_bytes)

    base64_string = base64_encoded.decode("utf-8")

    return base64_string

# Create a revision

def create_revision():

    request_body_revision = {

        "name": name,

        "description": rev_description,

        "content": convert_to_base64_without_bom(),

    }

    # calling create revision url

    try:

        revision_json = requests.post(

            revision_url, json=request_body_revision, auth=(username, password)

        )

    except Exception as e:

        print(e)

    revision_json = json.loads(revision_json.text)

    revision_id = revision_json[

        "id"

    ]  # save revision id as input for deployment request

    return revision_id

def deploy_revision():

    request_body_deployment = {

        "revisionIds": [create_revision()],

        "environmentId": environment_id,

        "description": deployment_description,

    }

    try:

        deployment_json = requests.post(

            deployment_url, json=request_body_deployment, auth=(username, password)

        )

    except Exception as e:

        print(e)

deploy_revision()

Once the python script is ready, it is time to write the Bitbucket yaml piece of code that calls the script through the building of a new step that will run right after the previous step that we have been through earlier:

step:

       name: 'Create and Deploy Revision'

       runs-on:

         - self.hosted

         - windows

       script:

         - $scriptsPath = "C:\scripts"

         - $localrepoPath = "C:\Denodo\DenodoPlatform\work\vdp\repository\dev_bb1"

         # Remove lines from the VQL export

         - python $scriptsPath"\rmvlinepy.py"

         # Run the python script to create the revision and deploy it

         - python $scriptsPath"\deploy_rev.py" -s localhost -p 10090 -v $localrepoPath"\deploymentvql.vql" -n 'revision_name' -d 'revision_description' -e 16 -c 'deployment_description' -u admin -a admin

The YAML code snippet above can be dissected in the following manner:

  • Step name: This sets the name of the step as ‘Create and Deploy Revision
  • Runs-on: Specifies that the step should run on a self-hosted runner and is specifically designed for a Windows environment.
  • Script section:
    - The first two lines set up variables to store the paths for the scripts and the local Denodo repository.
    - The third line executes the
    rmvlinepy.py script located in the specified script path to remove the ‘ENTER/EXIT SINGLE USER MODE lines from the VQL script.
    - Finally, a call to the
    deploy_rev.py script to create the revision from the VQL script and then deploy it in the target environment.

In conclusion, this document provides a comprehensive guide on seamlessly integrating Denodo with Bitbucket Pipelines, emphasizing efficient version control practices. By following the outlined steps, users can streamline their development workflows, ensure code consistency, and enhance collaboration within their Denodo projects. Leveraging the power of Bitbucket Pipelines facilitates automated build and deployment processes, contributing to a more robust and agile development environment for Denodo-based solutions.

Disclaimer
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.

Questions

Ask a question

You must sign in to ask a question. If you do not have an account, you can register here