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

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;