Vertica and CodeNOW: Technical Exploration

About this Document

The goal of this document is to integrate Vertica with CodeNOW and show how you can build back-end application components to view data from Vertica as a JSON endpoint.

CodeNOW Overview

CodeNow is a cloud native DevOps value stream delivery platform which helps manage the entire development lifecycle. Any skill leveled developer can get started with CodeNow and develop applications with microservice architecture. Register for a free trial for 14 days on the CodeNOW website.

Test Environment

  • CodeNOW 6.7
  • Vertica JDBC Driver 11.1.0 (Imported from Maven Central Repository)
  • Vertica Analytical Database 11.1.0

Creating a CodeNOW Project and Connecting to Vertica

  1. Log in to the demo environment using the link provided in your email.

  2. The CodeNOW dashboard opens.

  3. Click Create New Application.
    The Configure New Application window opens.

  4. Provide the application name, description, select demo-env from the Production Environment drop-down, and click Confirm.

  5. The Create Component page opens where you will be creating the first component for the application. Enter the component name and description.

  6. Scroll down to the Setup your Stack section.
    Select Standard stack and select Java in the Language section.
    Select MAVEN in the Build Tool section and select Blank in the Framework section.

  7. Click Confirm.

  8. The BackendComponent page opens with the details of the component:

  9. Scroll down to the Clone your Repository section. This displays a link to where the application code is hosted in GitLab.

  10. Navigate to the GitLab link to view the generated bootstrap code.

  11. Edit the pom.xml code to include the following dependency code snippet:

    <dependency>
    <groupId>com.vertica.jdbc</groupId>
 <artifactId>vertica-jdbc</artifactId>
 <version>11.1.0-0</version>
   </dependency>

  12. Edit Application.Java in src/main/java/org/example/service by replacing it with the following code to execute a query. Save the results you get from Vertica.

    package org.example.service;

import io.javalin.Javalin;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;


//Added to test vertica connection
import java.beans.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLInvalidAuthorizationSpecException;
import java.sql.SQLTransientConnectionException;
import java.util.Properties;

import org.json.simple.JSONObject;
import org.json.simple.JSONArray;
//Above added to test vertioca connection





public class Application {

    public static void main(String[] args) throws IOException {
        Logger logger = LoggerFactory.getLogger(Application.class);
        logger.info("Hello World!");

        
        
        int serverPort;
        if (System.getenv("SERVER_PORT") != null) {
            serverPort = Integer.parseInt(System.getenv("SERVER_PORT"));
        } else {
            serverPort = 7000;
            logger.warn("The SERVER_PORT environment variable was not set. The default port is set to " + serverPort + ".");
        }

        if (System.getProperty("config.file") != null) {
            String configFilePath = System.getProperty("config.file");
            if (new File(configFilePath).isFile()) {
                Path fileName = Path.of(configFilePath);
                String actual = Files.readString(fileName);
                logger.info(actual);
            } else {
                logger.error("A configuration file doesn't exist!");
            }
        } else {
            logger.info("Do you know, that you can use your configuration file with -Dconfig.file flag?");
        }

        Javalin app = Javalin.create().start(serverPort);
        app.get("/", ctx -> ctx.result("Hello World"));
        app.get("/health", ctx -> ctx.result("OK"));
        app.get("/vertica_storeschema_object", ctx -> {
             //testing vertica connection
            Properties myProp = new Properties();
            myProp.put("user", "codenow_user");
            myProp.put("password", "<dbadmin_pwd>");
            myProp.put("loginTimeout", "35");
            myProp.put("KeystorePath", "c:/keystore/keystore.jks");
            myProp.put("KeystorePassword", "keypwd");
            myProp.put("TrustStorePath", "c:/truststore/localstore.jks");
            myProp.put("TrustStorePassword", "trustpwd");
            Connection conn;
            //comment

            try {
                conn = DriverManager.getConnection(
                    "jdbc:vertica://<Vertica_DB_IP>:5433/<Vertica_DB_Name>", myProp);
                    ctx.result("connected to Vertica?");
                    System.out.println("Connected!");
                    logger.info("Connected");
                    java.sql.Statement stmt = conn.createStatement();
                    //stmt.execute("select * from OLDEcoDB.issue limit 10");
                    ResultSet rs = null;
                    rs = stmt.executeQuery("SELECT store_city, store_region FROM store.store_dimension");
                    int x = 1;
                    JSONObject records = new JSONObject();
                    JSONArray array = new JSONArray();
                    while(rs.next()){
                        JSONObject single_record = new JSONObject();
                        System.out.println(x + ". " + rs.getString(1).trim() + " " + rs.getString(2).trim());
                        //result = result + rs.getString(1).trim();
                        single_record.put("store_city", rs.getString("store_city"));
                        single_record.put("store_region", rs.getString("store_region"));
                        array.add(single_record);

                        logger.info(x + ". " + rs.getString(1).trim() + " " + rs.getString(2).trim());
                        x++;
                        }

                    conn.close();
                    records.put("store_data", array);
                    ctx.json(records);
                    //ctx.result("end of try");
                    //ctx.result(rs.getString(1));
            }catch (SQLTransientConnectionException connException) {

            // There was a potentially temporary network error
            // Could automatically retry a number of times here, but
            // instead just report error and exit.

            System.out.print("Network connection issue: ");
            System.out.print(connException.getMessage());
            System.out.println(" Try again later!");
            return;
            } catch (SQLInvalidAuthorizationSpecException authException) {
            // Either the username or password was wrong
            System.out.print("Could not log into database: ");
            System.out.print(authException.getMessage());
            System.out.println(" Check the login credentials and try again.");
            return;
            } catch (SQLException e) {
            // Catch-all for other exceptions
            e.printStackTrace();
            }

            // above code for testing vertica connection

         //ctx.result(rs);
         });
    }
}

  13. Navigate to the CodeNOW dashboard. Click the application that has been created to view the application detail window for VerticaTestApplication.

  14. In the Components section, you can view the component created under the application. Click Build to build the BackendComponent.
    The Build Component window opens.

  15. Select Release Build, enter the Version, select Next Major as it is the first major release, and click Confirm.
    The Build History page opens where you can see the build progress of the component.


  16. After a successful build, in the application details window, click Create New Application package in the Packages section.

  17. Enter the Version number and the Label for the package. Select the version number to be packaged and click Confirm.

  18. In the Application details window, in Deployment configurations, click Create new Configuration. Select demo-env from the Select Environment drop-down, select the application package to deploy and click Confirm.

  19. Select the Deployment Target, Deployment Configuration, Assigned Domain, Runtime Configuration, and connect Managed Services or you can select the default value and click Deploy.

  20. After a successful deployment of the backend component, you can see the package being deployed in the Deployed Applications section in the application details page.

  21. Click Open in Browser in the Deployed Applications section to view the results.

  22. Append “/vertica_storeschema_object” to the URL to view the query results from Vertica as JSON endpoint.

For More Information