Qlik Replicate and GBQ target endpoint: Data type NUMERIC(p,s) Precision and Scale

john_wang · Sep 17, 2024 9:28:47 AM

When working with Google Cloud BigQuery as the target endpoint, the NUMERIC data type in the source database (e.g., IBM DB2 for iSeries) is mapped to the NUMERIC data type in Google Cloud BigQuery by default. Specifically, it defaults to NUMERIC(38,9), where the precision is 38 and the scale is 9. This allows for the exact representation of decimal fractions, making it suitable for financial calculations.

However, there are scenarios where optimizing data type mapping can help save storage space. This article is useful if you want to modify the default precision and scale. For example, instead of mapping the source NUMERIC(18,2) to the default NUMERIC(38,9) in Google Cloud BigQuery, you might prefer to map it directly to NUMERIC(18,2).

Additionally, the CHAR and VARCHAR data types in the source database map to STRING in BigQuery. By default, it maps to STRING, which implicitly means STRING(65535). This can be adjusted as needed to further optimize storage. In this sample, it maps to STRING($LENGTH). For example the CHAR(20) in source database maps to STRING(20) in GBQ.

Environment

Qlik Replicate All versions
Google BigQuery All Versions

Detailed Steps

From the Qlik Replicate computer where you want to import the task, open the Qlik Replicate command line console by doing the following:

From the Start menu, expand Qlik Replicate and then select Qlik Replicate Command Line.

A command-line console is displayed with the correct prompt for Qlik Replicate.

Alternatively, open a Windows Command Prompt using As Adinistrator and change to "<product dir>\Attunity\Replicate\bin" (default location)
Run the following command:
```
repctl.exe getprovidersyntax syntax_name=BigQuery >> BigQuery_2024_5_NumericStringLength.json
```
If the DATA folder is a non-default location, add option -d data_directory in the command.

Edit the BigQuery_2024_5_NumericStringLength.json as outlined below, or download the example copy attached to this article:

Remove the first line and the latest line and include:

command getprovidersyntax response:

[getprovidersyntax command] Succeeded

Modify the top few lines:

Original:

"provider_syntax": {
 "name": "BigQuery",
 "query_syntax": {

Modified:

"provider_syntax": {
"name": "BigQueryNumeric", 
"repository.provider_syntax": { 
"name": "BigQueryNumeric",
"query_syntax": {

Original:

"rep_type":    "kAR_DATA_TYPE_NUMERIC",
"provider_data_type":    "NUMERIC",
"has_precision_condition":    true,
"from_precision":    1,
"to_precision":    38,

Modified:

"rep_type":    "kAR_DATA_TYPE_NUMERIC",
"provider_data_type":    "NUMERIC(${PRECISION},${SCALE})",
"has_precision_condition":    true,
"from_precision":    1,
"to_precision":    38,

Original:

"rep_type":    "kAR_DATA_TYPE_STR",
"provider_data_type":    "STRING"

Modified:

"rep_type":    "kAR_DATA_TYPE_STR",
"provider_data_type":    "STRING(${LENGTH})",

Save changes and move the file to "C:\Program Files\Attunity\Replicate\data\imports", or your DATA folder.
Run the following command:
```
repctl putobject data=BigQuery_2024_5_NumericStringLength
```
Do not add the additional suffix ".json" in the end of the command as this will cause the command to fail.
Add the Internal Parameter syntax to the target endpoint:
1. Go to the Endpoint connection
2. Switch to the Advanced tab
3. Click Internal Parameters
4. Add the parameter: syntax
5. Add the value: BigQueryNumeric
  
  For more information about Internal Parameter, see Qlik Replicate: How to set Internal Parameters and what are they for?
Resume or Reload the task.

Qlik Replicate and GBQ target endpoint: Data type NUMERIC(p,s) Precision and Scale