Vertica and GitLab: Technical Exploration

About this Document

The goal of this exploration was to check the connection of Vertica with GitLab. We used the trial version of GitLab and tested this integration with GitLab CI/CD. You need to follow the initial set up before you push and pull the Vertica SQL file from the GitLab remote directory. You can then execute the Vertica SQL script from GitLab CI/CD in the GitLab web interface. We also tested this integration using Linux and a Git supported database IDE (DataGrip). You can use any Git supported IDE. The connection using Linux and DataGrip is documented later in this article.

Test Environment

We used the following versions to test the different scenarios:

  • Git 2.27.0
  • Vertica Analytic Database 11.0.2
  • Windows Server 2019 Standard
  • Linux Redhat 8.3
  • DataGrip 2021.3

Connecting Using GitLab CI/CD

Prerequisites

  1. Install the Vertica ODBC client and follow the installation steps.
  2. On the Linux machine, install Git from the Git repository using yum install git.
  3. Create an account in GitLab. Create a new project in the GitLab repository.
  4. Configure Git global parameters for Git operations.
  5. Create the SSH file on the Linux machine:

  6. In the Gitlab web environment, click Edit Profile > SSH Keys. Paste the SSH key and click Add key.

  7. Install the GitLab runner on the Linux machine using yum install gitlab-runner and check the gitlab-runner version.

  8. Stop the gitlab-runner using the gitlab-runner stop command.
  9. Register the gitlab-runner using the gitlab-runner register command. Enter the following details after you invoke the register command:
    • GitLab Instance URL: Enter the GitLab instance URL, for example https://gitlab.com/.
    • Registration Token: On the GitLab web environment for your project, click Settings > CI/CD. Expand the Runners section and copy the registration token. On the Linux machine, paste the copied token.

    • Description: Enter a description of the runner.
    • Tags: Enter the required tags, for example ssh, ci, and so on.
    • Executor: Enter the required executor. In this example we used shell.
  10. Clone the GitLab remote directory on the Linux machine using the Git clone command.

Pushing Vertica SQL File on GitLab Remote Directory

  1. Create a Vertica SQL file factorial.sql in the local GitLab repository on the Linux machine. Add the SQL below to the factorial.sql file.

    CREATE or REPLACE PROCEDURE public.factorialSP(input int) LANGUAGE PLvSQL AS $$
DECLARE
    i int := 1;
    output int := 1;
BEGIN
    WHILE i <= input loop
        output := output * i;
        i := i + 1;
    END LOOP;
    RAISE INFO '%! = %', input, output;
END;
$$;

  2. Check the local Git repository status after adding the Vertica SQL file.

  3. Add the factorial.sql file to git repository and commit the changes.

  4. Push the changes to the GitLab remote directory by using the Git Push command:

  5. Refresh the GitLab web environment to view the newly added file.

Pulling Vertica SQL File from GitLab Remote Directory

  1. Create a Vertica SQL file callfactorial.sql in the remote GitLab repository on the GitLab web environment.

    Call public.factorialSP (5);

  2. On the Linux terminal where git local repository is installed, navigate to the git local repository, and execute the git pull <remote > command.

Executing Vertica SQL Script from GitLab CI/CD

  1. On the Linux machine, edit the file /etc/systemd/system/gitlab-runner.service and remove the text "--user" "gitlab-runner".
  2. Restart the gitlab-runner using gitlab-runner restart.
  3. On the GitLab web environment project homepage, click CI/CD in the left navigation pane.
  4. Click Use template to create a .gilab-ci.yml file.
  5. GitLab provides the sample .yml file with all the stages, that is, Build, Test, and Deploy. This sample has taken only the build stage to depict Vertica SQL scripts execution.
  6. Edit the .yml file and enter the commands to execute the scripts on the GitLab remote repository. For example:
    • /home/opt/vertica/bin/vsql -h <IP Address> -p 5433 -d vertica_db -U dbadmin -w vdb -f ./factorial.sql
    • /home/opt/vertica/bin/vsql -h <IP Address> -p 5433 -d vertica_db -U dbadmin -w vdb -f ./callfactorial.sql

    Note These commands have the paths required by the gitlab-runner to the vsql binary file along with the database related flags and the name of the files kept on the remote repository.

  7. Save the .yml file by providing the commit message and click Commit changes.

    The CI/CD pipeline gets started as soon as you commit the changes. Click View pipeline.

    The job log displays the actions performed by the commands written in the .yml file.

Connecting Using Linux

Prerequisites

Follow steps 1 to 6 in the Prerequisites section in Connecting using GitLab CI/CD and clone the GitLab remote directory on the Linux machine using the Git clone command.

Pushing Vertica SQL File on GitLab Remote Directory

Follow the steps in Pushing Vertica SQL File on GitLab Remote Directory.

Pulling Vertica SQL File from GitLab Remote directory

Follow the first two steps in Pulling Vertica SQL File from GitLab Remote Directory.

Executing Vertica SQL Script from Linux

Invoke vsql to execute the scripts:

  1. To create the factorial stored procedure

    2. /home/opt/vertica/bin/vsql -h <IP Address> -p 5433 -d vertica_db -U dbadmin -w vdb -f /home/vertica_test/factorial.sql

  2. To execute the factorial stored procedure

    3. /home/opt/vertica/bin/vsql -h <IP Address> -p 5433 -d vertica_db -U dbadmin -w vdb -f /home/vertica_test/factorial.sql


Connecting Using DataGrip IDE

Prerequisites

  1. Create an account in GitLab. Create a new project in GitLab repository.
  2. Install DataGrip on a Windows machine from their website.
  3. Download the Vertica JDBC/ODBC client based on the IDE that you are using.

Cloning Git Remote Repository to Local Repository

  1. Launch DataGrip on the Windows machine. Click New Project, enter the project name and click OK.
  2. Click File > Data Sources. Click the Drivers tab. Scroll down and select Vertica. Click the ‘+’ sign and select Vertica. Enter the database details, click Test Connection and click OK.

    The default driver bundled with the tool is 9.2.0. If you want to use any other version, you can import the JDBC JAR file from the Drivers tab in Data Sources.

  3. In the toolbar menu, click Git > Create Git Repository. Enter the name and location and click OK.


  4. Click Git > Enable Version Control Integration to enable the Git operations from the remote repository to local and vice versa.

  5. Select Git from the drop-down and click OK.

  6. Click Git > Clone. Provide the Git remote and local repository details and click Clone.

  7. Enter the username and password and click Log In.

Pushing Vertica SQL File to GitLab Remote Directory

  1. Create a Vertica SQL file factorial.sql in the local GitLab repository on the Windows machine. 
    CREATE or REPLACE PROCEDURE public.factorialSP(input int) LANGUAGE PLvSQL AS $$
DECLARE
    i int := 1;
    output int := 1;
BEGIN
    WHILE i <= input loop
        output := output * i;
        i := i + 1;
    END LOOP;
    RAISE INFO '%! = %', input, output;
END;
$$;
  2. In the toolbar menu, Click Git > Commit. Select the required file among the Unversioned files, type the commit message and click Commit if you want to commit only else click Commit and Push if you want to push the changes to the remote repository. Alternatively, Click Git > Push to push the changes to the remote repository.

Pulling Vertica SQL File from GitLab Remote Directory

  1. Create a Vertica SQL file callfactorial.sql in the remote GitLab repository on the GitLab web environment.

    2. Call public.factorialSP (5);

  2. Click Git > Pull to fetch the changes from the remote repository into the local repository. Select the local Git repository branch, click Pull.

  3. Provide the Git login credentials and click Log In.

For More Information