hvrrefresh
Usages
hvrrefresh [-Rurl] [-options]... hub chn
Description
Command hvrrefresh copies tables (available in a channel) from source location(s) to target location(s). The source must be a database location, but the targets can be databases or file locations. For more information, see the Refresh concept page.
For consumption-based licensing, Fivetran offers a five-day troubleshooting window per table for performing free Refreshes in HVR. This window starts on the day you first perform a Refresh on a table in a given month. Any subsequent Refreshes done outside this window count towards paid MAR.
Refreshing from a source location is supported only on certain location types. For the list of supported source location types, see section Refresh and Compare in Capabilities.
Command hvrrefresh corresponds to the Refresh Data into Target dialog in the User Interface.
The effects of hvrrefresh can be customized by defining various actions in the channel. For example, defining the action Restrict with parameter RefreshCondition allows the refresh of only certain rows in the table. Parameter Context can be used with option
-C
to allow restrictions to be enabled dynamically. Another form of customization is to employ SQL views; Refresh can read data from a view in the source database and row-wise refresh can also select from a view in the target database, rather than a real table when comparing the incoming changes.If row-wise hvrrefresh is connecting between different DBMS types, then an ambiguity can occur because of certain data type coercions. For example, HVR coercion maps an empty string from other DBMS's into a null in an Oracle varchar. If Ingres location ing contains an empty string and Oracle location ora contains a null, then should HVR report that these tables are the same or different? Command hvrrefresh allow both behavior by applying the sensitivity of the 'write' location, not the 'read' location specified by option
-r
. This means that row-wise refreshing from location ing to location ora will report the tables were identical, but row-wise refreshing from ora to ing will say the tables were different.
Options
This section describes the options available for command hvrrefresh.
Parameter | Description |
---|---|
-amax_slices_per_tbl | Set the number of slices the table will be divided into. The This option cannot be combined with option |
-Arows_per_slice | Set the number of rows per slice. The |
-b | Run in the background: do not wait for the Refresh to complete. |
-Bslice_meths | Methods for slice suggestion (option Valid values for
Several |
| Create new tables. Only 'basic' tables are created, based on the information in the channel. A basic table just has the correct column names and data types without any extra indexes, constraints, triggers, or table spaces. Valid values for
Several Action DbObjectGeneration with parameter RefreshTableCreateClause can be used to add extra SQL to the Create Table statement which HVR will generate. In the User Interface, this option corresponds to different target table creation configurations and Advanced Table Creation Options. |
| Enable context. This option controls whether actions defined with parameter Context are effective or are ignored. For more information, see the Refresh and Compare Contexts concept page. Defining an action with parameter Context can have different uses. For example, if action Restrict with parameters RefreshCondition="{id}>22" Context=qqq is defined, then normally all data will be refreshed, but if context qqq is enabled (-Cqqq), then only rows where id>22 will be refreshed. Variables can also be used in the restrict condition, such as "{id}>{hvr_var_min}". This means that hvrrefresh -Cqqq -Vmin=99 will only Refresh rows with id>99. Action ColumnProperties with parameter Context can also be defined. This can be used to define CaptureExpression parameters which are only activated if a certain context is supplied. For example, to define a Bulk Refresh context where SQL expressions are performed on the source database (which would slow down capture) instead of the target database (which would slow down Bulk Refresh). In the User Interface, this option corresponds to the Contexts option. |
-d | Remove (drop) scripts and scheduler jobs & job groups generated by the previous hvrrefresh command. |
-D | Duplicate the last Refresh event. This option is used for repeating a Refresh operation, using the same arguments. Other command-line options supplied to hvrrefresh -D will overwrite those from the duplicated event. |
-e | Automatically make a duplicate of the refresh event when it is done. |
-Etime | Schedule time(s) time for the refresh job. Valid values for time are:
Cannot be combined with option For specific usages, see Examples. In the User Interface, this option corresponds to the Scheduling Options. |
-f | Fire database triggers/rules while applying SQL changes for Refresh. Normally for Oracle and SQL Server, HVR disables any triggers on the target tables before the Refresh and re-enables them afterward. On Ingres, the Refresh avoids firing databases rules using statement |
-Ffkops | Behavior for foreign key constraint in the target database which either reference or are referenced by a table which should be refreshed. Valid values for
Refreshing from a source location is supported only on certain location types. For the list of supported source location types, see section Refresh and Compare in Capabilities. |
-ggran | Granularity of refresh in database locations. For more information, see Refresh Types. Valid values for
|
-Isrange | Refresh event only performs a subset of slices implied by This option is only allowed with option Value
|
-Jtask | Job chaining. After the refresh job is completed, start the capture and/or integrate jobs. Valid values for
|
| Target location to refresh to. This means that data will be written into location If this option is not supplied then all other locations except the source location are considered as target location(s). Several
Valid values for
|
| Select data from each table from the same consistent moment in time. Valid values for
This parameter only affects the selects of the leftmost (source) database, not any selects on the rightmost (target) database. In the User Interface, this option corresponds to the Select moment with Oracle flashback query option. |
-n Since v6.1.5/2 | Add the timekey truncate record as the first record of each table that is refreshed from the source location. This implies all previous timekey records for this table can be ignored by the system or application that consumes the data in the target location. This option is only supported if the target is a file or Kafka location. This option can only be used for tables with a TimeKey column. This option cannot be used in combination with option In the User Interface, this option corresponds to the Add a Truncate Record Before First Rows option. |
-pjob_quota | Sets job_quota refresh job group attribute. It defines a number of jobs jobs_quota which can be run simultaneously. |
| Perform Refresh for different tables in parallel using |
-qpurge | Online refresh of data from a database that is continuously being changed. This requires that capture is enabled on the source database. The integration jobs are automatically suspended while the online refresh is running, and restarted afterward. The target database is not yet consistent after the online refresh has finished. Instead, it leaves instructions so that when the replication jobs are restarted, they skip all changes that occurred before the refresh and perform special handling for changes that occurred during the refresh. This means that after the next replication cycle consistency is restored in the target database. If the target database had foreign key constraints, then these will also be restored. Valid values for
Internally, the online refresh uses 'control files' to send instructions to other replication jobs (see command hvrcontrol). These files can be viewed using command hvrrouterview with option Online refresh (with option |
| Source location to refresh from. This means that data will be read from location |
| Remote hub server. Access the hub server running on a remote machine, via the REST interface. This option is required for remote CLI access. When using this option, command hvrlogin should be run first, for authentication. |
-s | Schedule invocation of a refresh script by leaving a refresh job in the SUSPEND state. Without this option, the hvrstart -u -w hub channel-refr-src-tgt unsuspends (moves to RUNNING state) the jobs and instructs the scheduler to run them. Output from the jobs is copied to the hvrstart command's stdout and the command finishes when all jobs have finished. Jobs created are cyclic which means that after they have run they go back to the PENDING state again. They are not generated by a trig_delay attribute which means that once they are complete they will stay in the PENDING state without getting retriggered. |
-Ssliceexpr | Refresh large tables using Slicing. Value The column used to slice a table must be 'stable', i.e., values in it should not change while the job is running. For example, customer_id is a stable column, while last_login is not. Otherwise, a row could 'move' from one slice to another while the job is running. As a result, the row could be processed in two slices (causing errors) or no slices (causing data loss). If the source database is Oracle, this problem can be avoided using a common select moment (option In the User Interface, this option corresponds to the Slicing section. For more information on slicing limitations, see Slicing Limitations. Valuesliceexpr must have one of the following forms:
|
| Only refresh the specified table(s) Several Valid values for
|
-Ttsk | Specify an alternative name for a refresh task to be used for naming scripts and jobs. The task name must begin with an 'r'. Thedefault task name is refr, so without this option, the Refresh jobs and scripts are named chn-refr-l1-l2. |
-u Since v6.1.5/2 | Perform upsert refresh. This option merges the source rows into the target without initially deleting or truncating the target rows. Similar to Burst Integrate, the data is initially loaded into burst tables named tbl_ _rb and then the burst table is merged with the base table. Changes are applied as inserts or updates, similar to Resilient Integrate.
This option is only supported for target locations that support the Burst integratemethod. This option cannot be used for tables with a TimeKey column. This option cannot be used in combination with option In the User Interface, this option corresponds to the Merge into Target (No Delete/Truncate on Target) option. |
-v | Verbose. This option creates binary diff files containing individual differences detected. The diff files can be viewed using command hvrrouterview. Section Analyzing Diff File explains how to view and interpret the contents of a diff file. This option must be used in combination with option |
| Supply variable for refresh restrict condition. This should be supplied if a RefreshCondition parameter of action Restrict contains string {hvr_var_name}. This string is replaced withvalue .In the User Interface, this option corresponds to the Variables option. |
-wprereads | File prereaders per table. Define the number of prereader subtasks per table while performing direct file compare. This option is only allowed if the source or target is a file location. |
Examples
This section provides examples of using the hvrrefresh command.
Example 1. Bulk refresh
The following command can be used for bulk refresh of table order from location src to location tgt:
hvrrefresh -r src -l tgt -t order myhub mychannel
Example 2. Row-by-row refresh
For row-by-row refresh (option -gr
) from location src to location tgt, use the following command:
hvrrefresh -r src -l tgt -gr myhub mychannel
Example 3. Schedule refresh
The following command is to schedule a bulk refresh at a specific time (option
-E
).hvrrefresh -r src -l tgt -gb -E 2022-04-12T12:50:53+0300 myhub mychannel
The following command schedules a row-by-row refresh to repeat at the 1st day of each month at 10.30 a.m. (option
-E
).hvrrefresh -r src -l tgt -gr -E '30 10 1 * *' myhub mychannel