prepareTargetTable

このプロシージャは、ターゲットテーブルが存在しない場合は作成し、増分フィールドの最大値を検索します。

Parameters

Parameter	Description
`sourceObject`	Fully-qualified name of the source. Can be an existing table or a stored procedure returning data. Can also be an arbitrary table specification in the form of string returning data: `$column1\|dataType1,column2\|dataType3,column3[\|string]`; mandatory
`isSourceATable`	`TRUE` or `NULL` if the source is a table, `FALSE` if it is a procedure
`additionalColumns`	Columns to add to the source
`excludedColumns`	Columns to exclude from the source
`target_table`	Fully qualified name of the target table
`cleanupMethod`	Cleanup method: `DELETE` or `DROP`. Any other value will throw an error. `NULL` or Whitespace will not perform any cleanup
`preview`	Preview only, without creating a table
`incrementalField`	Optional way to search for the maximum value in the target table
`incrementalFilter`	Optional target table filter to search for the maximum incremental value, e.g. due to partitioning
`incrementalMinimum`	If `TRUE`, requests the minimum value instead of the maximum one from the target table
`incrementalAlternateField`	Field to return based on the extreme value of another incremental field (e.g., to return the ID of a record with maximum/minimum timestamp, specify ID here)

Attributes

Attribute	Type	Description
`quoted_target_table`	string	Quoted fully-qualified name of the target table
`datasourceName`	string	Target data source name
`tableShortName`	string	Simple name of the target table
`template`	string	Template of the target data source
`writeData`	boolean	Shows whether data can be written (i.e. target table specified and not preview)
`tableCreated`	boolean	Shows whether the table was created
`maxValue`	object	Maximum incremental value in the target table
`source_columns`	clob	Comma-separated list of source columns
`target_columns`	clob	Comma-separated list of target columns

このプロシージャは、ほとんどのコネクタに適用可能な初期ターゲットテーブル設定ワークフローを実装します。テーブル、ビュー、データを返すストアドプロシージャなど、既存のオブジェクトの構造を持つ物理テーブルを生成します。コネクタのほとんどのプロシージャと同様に、このプロシージャは特定の API エンドポイントまたはメソッドに適用可能な形式のデータを返します。完全修飾オブジェクト名がsourceObject 引数に与えられます。追加のパラメータ isSourceATableは、テーブル、ビュー、プロシージャというオブジェクトの種類を決定するために使用され、最終的に SYS.Columns または SYS.ProcedureParams に内部的にメタデータを問い合わせます。

以下は、そのような手順の例です：

/* Procedure */
CREATE VIRTUAL PROCEDURE views.p1()
RETURNS (
    x integer,
    y string
) 
AS
BEGIN
 SELECT 1, 'One';
END;;
 
CALL UTILS.prepareTargetTable(
    sourceObject => 'views.p1',
    isSourceATable => FALSE,
    target_table => 'dwh.TableBasedOnViewsP1',
    preview => FALSE,
    cleanupMethod => 'DELETE'
);;
 
/* View or table */
CREATE VIEW views.v1 AS 
 SELECT * FROM (CALL views.p1()) x;;
 
CALL UTILS.prepareTargetTable(
    sourceObject => 'views.v1',
    isSourceATable => TRUE,
    target_table => 'dwh.TableBasedOnViewsV1',
    preview => FALSE,
    cleanupMethod => 'DROP'
);;

追加のカラムを指定したい場合は、 additionalColumns パラメータに値を指定することで可能です。このパラメータは、結果セットの先頭、末尾、または両端の列を指定することができ、書式は正確な位置に依存します：

BEGIN: カンマ区切りのリスト；
END：このリストはカンマで始めてください；
BOUBLE：カンマ区切りのリストを指定しますが、チャンクの最初と最後をダブル・カンマで区切ります。

デフォルトのデータ型は文字列で、縦棒で区切られたカラム名の後にデータ型を指定することで変更できます。

場合によっては、プロシージャはターゲットテーブルにあるべき列よりも多くの列を返します。1つの例は、プロシージャの出力にリストされたメインオブジェクトに対応する列と子オブジェクトに対応する列がある場合に、データを平坦化することです。この場合、excludedColumns パラメータを使用し、ターゲットテーブルに作成されるべきでないカラムをカンマで区切ります。

CREATE VIRTUAL PROCEDURE views.p2()
RETURNS (
    x integer,
    y string,
    z xml,
    z_inner1 string,
    z_inner2 string
)
AS
BEGIN
 SELECT 1, 'One', XmlParse(Document '<root />'), 'inner1', 'inner2';
END;;
 
CALL UTILS.prepareTargetTable(
    sourceObject => 'views.p2',
    isSourceATable => FALSE,
    additionalColumns => 'id|integer,author,,created_at|timestamp'
    excludedColumns => 'z,z_inner1,z_inner2',
    target_table => 'dwh.TableBasedOnViewsP2_withExcluded',
    preview => FALSE,
    cleanupMethod => 'DELETE'
);;

target_table および preview パラメータは、物理的な書き込みを制御するために使用されます。target_table がNULLに設定されている場合、または preview がTRUEに設定されている場合、テーブルは作成されず、このプロシージャは事実上スキップされます。これらのパラメータは、返される writeData フィールドの値にも影響し、 previewが無効で target_table が設定されている場合はTRUEを返し、そうでない場合はFALSEを返します。呼び出しているコネクタ・プロシージャで writeData を読み取る場合、 target_table および preview変数のチェックは不要になります。追加で返されるフィールド tableCreated は、このプロシージャの呼び出し中にターゲットテーブルが作成されたかどうかを示します。FALSEを返します。

このフィールドは、例えば、複数の入れ子を持つ複雑なオブジェクトをダウンロードする場合に便利です。この場合、プロシージャは最初の実行でTRUEを返し、コネクタはネストされたオブジェクトのために追加のテーブルを作成します。追加のTablesが不要な場合は、Dropしてもかまいません。コネクタ操作中、次回以降の実行時にこれらの追加テーブルを再作成する必要はありません。それらが存在せず、ターゲットテーブルが今回の実行で作成されなかった場合、存在しないテーブルは母集団からスキップされます。

このワークフローの追加出力パラメータはdatasourceName および tableShortNameです。これらには、ターゲットデータソースとテーブルの引用符で囲まれていないプレーンな名前が含まれます。Tablesには、便宜上、従属オブジェクトの接尾辞を付加することができます。

INSERT INTO #__LOCAL__temp_table
 SELECT 
        quoted_target_table, 
        datasourceName,
        tableShortName,
        writeData, 
        tableCreated,
 Cast(maxValue as timestamp) as maxValue, 
        source_columns, 
        target_columns 
 FROM (
        CALL UTILS.prepareTargetTable(
            sourceObject => '${vsname}.ProcedureWithComplexOutput',
            isSourceATable => FALSE,
            target_table => target_table,
            preview => preview,
            incrementalField => 'retrievedTimestamp',
            excludedColumns => 'XmlField1,ClobField1,XmlField2'
        )
    ) x;
 
DECLARE boolean writeData = SELECT writeData FROM #__LOCAL__temp_table;
DECLARE boolean tableCreated = SELECT tableCreated FROM #__LOCAL__temp_table;
DECLARE timestamp maxRetrievedTimestamp = SELECT maxValue FROM #__LOCAL__temp_table;
...
DECLARE string internal_target_table_Additional = '"' || datasourceName || '"."' || tableShortName || '_XmlField1"';
...
IF (
 Not Exists (
 SELECT * FROM SYS.Tables t 
 WHERE 
            LCase (t.VDBName) = LCase (Variables.vdb_name) 
 AND LCASE('"' || t.SchemaName || '"."' || t.name || '"') = LCASE(internal_target_table_Additional)
    )
)
BEGIN
    IF (tableCreated)
 BEGIN
 EXECUTE IMMEDIATE '
            CREATE TABLE ' || internal_target_table_Additional || ' (
                KeyID integer,
                NestedField1 integer,
                NestedField2 string
            );' Without Return;
 
        write_Additional = TRUE;
 END
END
ELSE 
    write_Additional = TRUE;
...
WHILE (pagination)
BEGIN
...
    IF (write_Additional)
 BEGIN
 INSERT INTO #__LOCAL__temp_table_Additional
 SELECT m.KeyID, l.* 
 FROM #__LOCAL__temp_table m
 CROSS JOIN XmlTable (
 '/root/XmlField1'
            PASSING Nvl(m.XmlField1, XmlElement(root))
            COLUMNS
                NestedField1 integer,
                NestedField2 string
        ) l;
 
 EXECUTE IMMEDIATE '
            INSERT INTO ' || internal_target_table_Additional || ' 
                SELECT *
                FROM #__LOCAL__temp_table_Additional;' WITHOUT RETURN;
 END
    ...
END

返される template フィールドには、ターゲットデータソースのテンプレート名が含まれます。これは、データソースが BigQuery の場合に重要です。このような場合には、DELETE。

incrementalField および incrementalFilter パラメータは、インクリメンタルダウンロードに使用されます。incrementalFieldが設定されている場合、プロシージャは物理テーブルのこのフィールドの最大値を返します。値は maxValue フィールドに objectとして返されます。 integer, long, date, or timestamp - max 関数をサポートするデータ型。、コネクタ内の各タイプを。ターゲット・テーブルにパーティショニングされたデータが含まれている場合、 incrementalFilter パラメータを使用して、このデータに対する追加のフィルタを指定することができ、このフィルタは集約クエリに追加されます。

以下は呼び出しと出力の例です：

Example 1

CREATE VIRTUAL PROCEDURE views.p3()
RETURNS (
    partiionId integer,
    incrementalValue integer
) 
AS
BEGIN
 SELECT 1, 1;
END;;
 
CALL UTILS.prepareTargetTable(
    sourceObject => 'views.p3',
    isSourceATable => FALSE,
    target_table => 'dwh.TestIncremental',
    preview => FALSE,
    incrementalField => 'incrementalValue',
    cleanupMethod => 'DROP' 
);; 

Example 2

INSERT INTO dwh.TestIncremental Values (1, 15);;
INSERT INTO dwh.TestIncremental Values (2, 25);;
 
CALL UTILS.prepareTargetTable(
    sourceObject => 'views.p3',
    isSourceATable => FALSE,
    target_table => 'dwh.TestIncremental',
    preview => FALSE,
    incrementalField => 'incrementalValue',
    cleanupMethod => 'DELETE' 
);; 

Example 3

CALL UTILS.prepareTargetTable(
    sourceObject => 'views.p3',
    isSourceATable => FALSE,
    target_table => 'dwh.TestIncremental',
    preview => FALSE,
    incrementalField => 'incrementalValue',
    incrementalFilter => 'WHERE partiionId = ' || 1,
    cleanupMethod => 'DELETE' 
);;  

また、追加のパラメータ incrementalMinimum および incrementalAlternateFieldを指定することもできます。 incrementalMinimum をTRUEに設定すると、ターゲットテーブルでは最大値ではなく最小値が検索されます。incrementalAlternateField内で代替フィールド名を指定すると、実際の最大値の代わりにその値が返されます。代替フィールドのフィールド値は、incrementalFieldの最大/最小値を持つ行から取得されます。サンプルシナリオは、最大タイムスタンプを持つレコードのISを返します。

以下は、このプロシージャ内でUTILS.getTableIntersectionを呼び出す例です。この呼び出しの出力は、 source_columns および target_columnsとして返されます。返された値は、ソースとターゲットのメタデータを適応させるためのカラム名をリストする動的SQLステートメントでさらに使用するために消費されることがあります：

EXECUTE IMMEDIATE'
    INSERT INTO ' || internal_target_table || target_columns || ' 
        SELECT ' || source_columns || ' 
        FROM #__LOCAL__temp_table;' WITHOUT RETURN;