About Copying View Data - Teradata Data Mover

Teradata Data Mover User Guide

Product
Teradata Data Mover
Release Number
16.00
Published
December 2016
Language
English (United States)
Last Update
2018-03-29
dita:mapPath
rdo1467305237457.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-4101
lifecycle
previous
Product Category
Analytical Ecosystem

Data Mover can copy underlying data from a view to a table or view on the target system. When copying data from a view, specify the copyData="true" attribute along with the view element.

The copyData attribute is optional, with a default value of false. When copyData is set to true, it may be followed with the optional view_data_table element.

If view_data_table is not specified, Data Mover copies the view data to a target view. The element view selection = ”included” must be specified to avoid an exception at job creation time. For example:
<views>
<view selection="included" copyData="true">
<name>deptsals</name>
<database>MyDb</database>
</view>
</views>
In the example above, the data from the view deptSals is selected using "SELECT *from MyDb.deptSals" and loaded to the view "MyDb"."deptSals" on the target. If the view MyDB.deptSals does not exist on the target, it is created using the source view definition. If the view MyDB.deptSals exists on the target, it is overwritten using the source view definition.
If view_data_table is specified, target_table indicates the table to which the underlying view data is copied and target_database indicates the database in which the target table exists. If the table does not already exist on the target, Data Mover creates the table. If the table already exists on the target, the view data is loaded to it. For example:
<views>
	<view selection="included" copyData="true">
		<name>deptsals</name>
		<database>MyDb</database>
		<view_data_table>
			<target_table>TargetTable</target_table>
			<target_database>TargetDb</target_database>
		</view_data_table>
	</view>
</views>

In the example above, the data from the view deptSals is selected using "SELECT * from MyDb.deptSals" and loaded to the table "TargetDb"."TargetTable". If "TargetDb"."TargetTable" does not exist on the target, Data Mover creates the target table.

When Data Mover creates a target table, the table has the following characteristics:
  • Is a multiset table
  • Is created with the same column names and column types as the view
  • Is a no-fallback table without journaling
  • Does not contain a secondary index
In addition, if the view whose data is being copied contains a Primary Index column, that column is chosen as the Primary Index of the target table. If the view does not contain a Primary Index column, the first column in the view is chosen as the Primary Index.
When copying view data to a target view, the following apply:
  • The source view must not reference more than one table. If the view has multiple reference tables, a runtime exception occurs.
  • Any compare_ddl element is ignored because Data Mover creates the target view definition using the source view definition.
  • The table referenced by the source view must exist on the target or must be moved with the job.
When copying view data to a target view, the source view data is first copied to a staging table and the target view, if one exists, is overwritten with the source view definition. Then the source view data is copied from the staging table to the target view. It is possible that the target reference table could include null values if the view does not include all of the columns of that reference table. Therefore, exercise care when using this feature.
The following rules apply to the use of load utilities when copying view data to either a target table or view:
  • ARC cannot be used as the load utility.
  • Specifying ARC or TPTAPI_LOAD as the <force_utility> value when copying view data results in a create time error.
  • View data is copied using the TPTAPI_UPDATE operator unless <force_utility> is specified as TPTAPI_STREAM or JDBC.
  • When view data is being copied along with other tables, the view data is copied using the TPTAPI_UPDATE operator but the other data tables can be copied using the TPTAPI_LOAD operator. This would happen if <force_utility> is unspecified or specified as TPTAPI.