CopyOver() methodに加え、Data VirtualTuality Server では、CopyOverテクニックを2つの手順でデータソースに使用できます: 

  • createCopyOverSourceTableJob()

  • createCopyOverSQLJob()

既存のCopyOverジョブを変更するには、さらに2つの手順があります:

  • changeCopyOverSourceTableJob()

  • changeCopyOverSQLJob()

これらのプロシージャはそれぞれのジョブを作成するだけで、その後は手動で開始する必要があることに注意してください。

createCopyOverSourceTableJob

このプロシージャは、指定されたソース テーブルから指定されたターゲット テーブルにデータをコピーするジョブを作成し、作成されたジョブのjobIDを返します。以下のパラメータを取ります:

Parameter

Type

Meaning

sourceTable

string

Source table the data will come from. Format: {schemaname}.{tableName}

targetTable

string

Target table the data will be put into. Format: {schemaname}.{tableName}

cleanup_method

string

Specifies the cleanup method. Valid values: drop, delete, truncate .
This method can work only if the translator property supportsNativeQueries=TRUE is provided on the target data source

description

string

Description of the job to be created

uuid

string

Custom UUID of the job to be created

uuid parameter in SYSADMIN.createCopyOverSourceTableJob is available since v4.1

Examples

1. DROPクリーンアップ・メソッドを使用して、ソース・テーブルからジョブを作成します:

SELECT jobID FROM (
CALL "SYSADMIN.createCopyOverSourceTableJob"(
    "sourceTable" => 'test_tables.test_a',
    "targetTable" => 'dwh.ttt',
    "cleanupMethod" => 'DROP',
    "description" => 'Description job 1',
"uuid" => '3c25f20a-6682-11ee-8c99-0242ac120002'
))s;;

2. DELETEクリーンアップ・メソッドを使用して、ソース・テーブルからジョブを作成します:

SELECT jobID FROM (
CALL "SYSADMIN.createCopyOverSourceTableJob"(
    "sourceTable" => 'test_tables.test_a',
    "targetTable" => 'dwh.ttt1',
    "cleanupMethod" => 'DELETE',
    "description" => 'Description job 2'
))s;;

createCopyOverSQLJob

このプロシージャは、指定されたソース テーブルから指定されたターゲット テーブルにデータをコピーするジョブを作成し、作成されたジョブのjobIDを返します。以下のパラメータを取ります:

Parameter

Type

Meaning

sqlStatement

string

SQL statement that queries the data to be put into a target table

targetTable

string

Target table the data will be put into. Format: {schemaname}.{tableName}

cleanup_method

string

Specifies the cleanup method. Valid values: drop, delete, truncate .
This method can work only if the translator property supportsNativeQueries=TRUE is provided on the target data source

description

string

Description of the job to be created

uuid

string

Custom UUID of the job to be created

uuid parameter in SYSADMIN.createCopyOverSQLJob is available since v4.1

Examples

1. DROP クリーンアップ・メソッドを使用した SQL 文によるジョブの作成:

CALL "SYSADMIN.createCopyOverSQLJob"(
    "sqlStatement" => 'SELECT a, sum(b) AS sumOfColB FROM test_tables.test_a WHERE a > 1 GROUP BY a',
    "targetTable" => 'dwh.ttt2',
    "cleanupMethod" => 'DROP',
"uuid" => '3c25f5ac-6682-11ee-8c99-0242ac120002'
);;

2. DELETE クリーンアップ・メソッドを使用した SQL 文によるジョブの作成:

CALL "SYSADMIN.createCopyOverSQLJob"(
    "sqlStatement" => 'SELECT a FROM test_tables.test_a',
    "targetTable" => 'dwh.ttt2',
    "cleanupMethod" => 'DELETE'
);;

changeCopyOverSourceTableJob

このプロシージャでは、CopyOverSourceTableJobを変更できます。以下のパラメータを取ります:

Parameter

Type

Meaning

jobId

biginteger

ID of the job to be changed

sourceTable

string

Source table the data will come from. Format: {schemaname}.{tableName}

targetTable

string

Target table the data will be put into. Format: {schemaname}.{tableName}

cleanup_method

string

Specifies the cleanup method. Valid values: drop, delete, truncate .
This method can work only if the translator property supportsNativeQueries=TRUE is provided on the target data source

description

string

Description for the job to be changed

refreshTarget

boolean

Indicates if the target data source should be refreshed before running the job

allowsAlterTableStatements

boolean

Indicates if the target table should be altered in case the source has been altered

jobName

string

Job's unique identifier

retryCounter

integer

Number of retry attempts before the job is set to FAILED state

retryDelay

integer

Delay in seconds between the retry attempts

runTimeout

integer

Individual job timeout in minutes. If no runTimeout is set, configuration set via default value option JOB_RUN_TIMEOUT will be considered for the job; if runTimeout is reached before the job is finished, the job terminates with the INTERRUPTED state

jobUuid

string

UUID of the job to be changed

changeCopyOverSQLJob

このプロシージャでは、CopyOverSQLJobを変更できます。以下のパラメータを取ります:

Parameter

Type

Meaning

jobId

biginteger

ID of the job to be changed

SQLStatement

string

SQL statement that queries the data to be put into a target table

targetTable

string

Target table the data will be put into. Format: {schemaname}.{tableName}

cleanup_method

string

Specifies the cleanup method. Valid values: drop, delete, truncate .
This method can work only if the translator property supportsNativeQueries=TRUE is provided on the target data source

description

string

Description for the job to be changed

refreshTarget

boolean

Indicates if the target data source should be refreshed before running the job or not

allowsAlterTableStatements

boolean

Indicates if the target table should be altered in case the source has been altered

jobName

string

Job's unique identifier

retryCounter

integer

Number of retry attempts before the job is set to FAILED state

retryDelay

integer

Delay in seconds between the retry attempts

runTimeout

integer

Individual job timeout in minutes. If no runTimeout is set, configuration set via default value option JOB_RUN_TIMEOUT will be considered for the job; if runTimeout is reached before the job is finished, the job terminates with the INTERRUPTED state

jobUuid

string

UUID of the job to be changed

uuid parameter in SYSADMIN.changeCopyOverSQLJob and SYSADMIN.changeCopyOverSourceTableJob is available since v4.4