DataWorks:SQL Server

Supported capabilities

Supported regions

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

Prerequisites

Before you begin, make sure you have:

• A Business Flow in DataStudio. DataStudio organizes development by Business Flows. For more information, see Create a workflow.

• A SQL Server data source configured in JDBC connection string mode. For more information, see Data source management.

• (RAM users only) Your RAM user added to the workspace with the Develop or Workspace Administrator role. Grant the Workspace Administrator role with caution due to its elevated privileges. For more information, see Add members to a workspace.

Step 1: Create a SQL Server node

1. Log on to the DataWorks console. In the top navigation bar, select the target region. In the left-side navigation pane, choose Data Development and O&M > Data Development. Select the target workspace and click Go to Data Development.

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

3. In the Create Node dialog box, enter a node Name and click Confirm.

Step 2: Write SQL code

SELECT * FROM sys.tables;

Use scheduling parameters

Scheduling parameters let DataWorks pass dynamic values into your SQL at runtime — useful for date-windowed queries in periodic scheduling. Define variables in your code using ${variable_name} syntax, then assign values in the Properties > Scheduling Parameter section.

The following example queries user-defined tables created within the last year. The scheduling parameter a is set to $[add_months(yyyymmdd,-12)], which resolves to the date one year before the current run date.

SELECT name, create_date
FROM sys.tables
WHERE type = 'U' AND create_date >= '${a}';

For supported formats and configuration details, see Supported formats for scheduling parameters and Configure and use scheduling parameters.

Step 3: Configure task scheduling

Click Scheduling Configuration on the right panel and set the scheduling properties. For a full reference, see Overview.

Step 4: Debug the node

1. (Optional) Click the icon in the toolbar to open the Parameters dialog box. Select a resource group and assign values to any scheduling parameters for the debug run. For parameter assignment logic, see Task debugging process.

2. Click the icon to save, then click the icon to run the node.

3. (Optional) Run a smoke test to verify execution in the development environment. For more information, see Perform smoke testing.

Step 5: Submit and publish the node

1. Click the icon to save the node.

2. Click the icon to submit the node. In the Submit dialog box, enter a Change Description and select code review options.

   Note

   If code review is enabled, a reviewer must approve the code before it can be published. For more information, see Code review.

3. In standard mode workspaces, click Publish in the upper-right corner to deploy the node to production. For more information, see Publish tasks.

What's next

After the node is published, it runs on a recurring basis according to the configured schedule. Click O&M in the upper-right corner of the node configuration tab to open Operation Center, where you can monitor the scheduling status and run history of the recurring task. For more information, see Manage recurring tasks.