DataWorks:Vertica

Background information

Vertica is a high-performance, column-oriented database management system (DBMS) designed for high-speed processing and querying of large-scale datasets. It is ideal for big data analytics and real-time queries. For more information, visit the official Vertica website.

Prerequisites

• Create a workflow.

  In DataStudio, you develop tasks for different engines within workflows. Therefore, you must create a workflow before you create a node. For more information, see Create a workflow.

• A Vertica data source is created.

  You must add your Vertica database as a Vertica data source in DataWorks to access its data. For more information about how to create a data source, see Manage data sources. For more information about how to use a Vertica data source in DataWorks, see Vertica data source.

  Note

  Vertica nodes support only Vertica data sources that are created using the Java Database Connectivity (JDBC) connection string method.

• A network connection is established between your data source and a resource group.

  You must make sure that the desired data source is connected to the resource group that you want to use. For more information about how to configure network connectivity, see Establish a network connection between a resource group and a data source.

• (Optional, for RAM users) Add the RAM user for task development to the workspace and grant them the Development or Workspace Administrator role. The Workspace Administrator role has extensive permissions, so assign it with caution. For more information about how to add members and grant roles, see Add members to a workspace.

Limits

Supported regions: China (Hangzhou), China (Shanghai), China (Beijing), China (Shenzhen), China (Chengdu), China (Hong Kong), Singapore, Malaysia (Kuala Lumpur), Germany (Frankfurt), US (Silicon Valley), and US (Virginia).

Step 1: Create a Vertica node

1. Go to the DataStudio page.

   Log on to the DataWorks console. In the top navigation bar, select the desired region. In the left-side navigation pane, choose Data Development and O&M > Data Development. On the page that appears, select the desired workspace from the drop-down list and click Go to Data Development.

2. Right-click the target workflow and choose Create Node > Database > Vertica.

3. In the Create Node dialog box, enter a Name for the node and click OK. After the node is created, you can develop and configure the task in the node.

Step 2: Develop Vertica task

(Optional) Select Vertica data source

If multiple Vertica data sources are created in your workspace, you must select the appropriate data source on the configuration tab of the Vertica node. If only one Vertica data source is created, it is used by default.

Develop SQL code: Simple example

In the code editor of the Vertica node, write the SQL code for your task. The following code provides an example.

SELECT * FROM usertablename;

Use scheduling parameters

DataWorks provides Scheduling Parameter that let you dynamically pass values to your code for periodic scheduling. You can define variables in your task code by using the ${variable_name} format. Then, in the right-side navigation pane of the node editing page, choose Properties > Scheduling Parameter and assign values to the variables. For more information about the supported formats and how to configure scheduling parameters, see Formats of scheduling parameters and Configure and use scheduling parameters.

The following code provides an example.

SELECT '${var}'; -- Example of using a scheduling parameter.

Step 3: Configure task scheduling

If you need to run the task on a schedule, click Scheduling in the right-side navigation pane of the node editor. Configure the scheduling information for the node based on your requirements. For more information, see Configure scheduling properties for a task.

Step 4: Debug the task

Debug the task to verify that it runs as expected:

1. Select a resource group to run the code and assign values to custom parameters.

   • Click the icon in the toolbar. In the Parameter dialog box, select the scheduling resource group to use for debugging.

   • If you use scheduling parameter variables in your code, assign debugging values to them here. For more information about the parameter assignment logic, see Debug a task.

2. Save and run the code.

   Click the icon in the toolbar to save the code. Then, click the icon to run the task.

3. (Optional) Perform smoke testing.

   To verify that a scheduled task runs as expected, you can perform smoke testing in the development environment when you submit the node or afterward. For more information, see Perform smoke testing.

Step 5: Submit and deploy the task

After you configure the node, you must submit and deploy it. The node will then run periodically based on its scheduling configuration.

1. Click the icon in the toolbar to save the node.

2. Click the icon in the toolbar to submit the node task.

   In the Submission dialog box, enter a Change Description, and select whether to request a code review after submitting the node.

   Note

   • You must set the Rerun attribute and Parent Nodes of a node to submit it.

   • The code review feature ensures code quality and prevents errors that can occur when unverified code is deployed to the production environment. If you enable this feature, a reviewer must approve the submitted code before deployment. For more information, see Code review.

If your workspace is in standard mode, submit the task and then click Deploy in the upper-right corner of the node editor page to deploy it to the production environment. For more information, see Deploy tasks.

Next steps

Task O&M: After the task is committed and deployed, it runs periodically based on its scheduling configuration. You can click Operation Center in the upper-right corner of the node editor to monitor its scheduling and operational status. For more information, see Manage auto-triggered tasks.