ADS Integrator

ADS Integrator is a component of the data preparation and distribution pipeline (see Data Preparation and Distribution Pipeline). ADS Integrator takes the data that has been downloaded by a downloader (see Downloaders) and integrates it into Agile Data Warehousing Service (ADS) (see Data Warehouse Reference).

How ADS Integrator Works

The following picture shows how data for one entity is integrated into ADS when the default settings are used:

When integrating data downloaded by a downloader, ADS Integrator relies on the information stored in the S3 files' metadata and the S3 metadata folder. If you want to change any parameter in a downloader’s configuration, do so in the metadata files manually.

Data Tables

Source Table

The source tables are at the beginning of the data integration process. COPY commands integrate the prepared data from the BDS to the source tables.

The naming convention of the source tables is the following: src_(downloader_name)_(entity_name)_merge

In some cases, the source tables can be used directly. However, when ADS Integrator runs with the default settings, data should be read from the stage tables. ADS Integrator adds system fields to each row.

The source tables have the following system fields:

Stage Table

After successful integration, the data is loaded to the stage tables. Then, these tables are ready for use and the ETL can be connected here.

The naming convention of the stage tables is the following: stg_(downloader_name)_(entity_name)_merge

ADS Integrator adds system fields to each row.

The stage tables have the following system fields:

Flex Table

If your source files are JSON files (see “Use JSON files as source files” in CSV Downloader), ADS Integrator first integrates the JSON source files from the BDS into flex tables before copying them to the source tables. When integrating the JSON files into the flex tables, ADS Integrator uses the Vertica fjsonparser parser to process the JSON files, either with or without flattening the nested data. Then, ADS Integrator processes the data from the flex tables according to the XPath notations specified in the feed file (see Feed File) and integrates the data into the source tables.

The naming convention of the flex tables is the following: src_(downloader_name)_(entity_name)_flex

The flex tables have the following system fields:

Rejected Table

If the ‘enable_rejected_table’ parameter is set to ‘true’, a special table is created for rejected records for each entity.

The naming convention of the rejected tables is the following: rejected_(downloader_name)_(entity_name)

In the rejected tables, you can use only SELECT queries. DML and DDL queries are not supported.

The maximum number of rows in a rejected table is to 100000. When this limit is reached, all the data is deleted from the table within the next run of ADS Integrator. To change the maximum allowed number of the rows in rejected tables, set the ‘rejected_table_cleanup_rows_limit’ parameter.

The rejected table contains the following fields:

System Tables

Batch Table

The batch table is used only when ADS Integrator is running in batch mode.

The batch table contains information about the processed batches. If a batch is processed correctly, the batch table contains the STARTED event and the FINISHED event.

The batch table contains the following fields:

Load Table

The load table is used in both batch mode and entity mode.

The load table contains information about the progress of the load for each entity.

The load table contains the following fields:

File Table

The file table is used in both batch mode and entity mode.

The file table contains information about the integrated files and additional details on each of them.

The file table contains the following fields:

Configuration File

Creating and setting up the integrator’s configuration file is the third phase of building your data pipeline (see Build Your Data Pipeline).

For more information about configuring a brick, see Configure a Brick.

Minimum Layout of the Configuration File

The following JSON samples are the minimum layout of the configuration file that ADS Integrator can use. The samples differ in load mode that ADS Integrator use:

• Batch mode
• Entity mode

Choose the mode that is compatible with the downloader that you are using:

• Choose batch mode when you want to process the data downloaded by CSV Downloader.
• Choose entity mode when you want to process the data downloaded by any downloader except for CSV Downloader.

Copy the appropriate sample and replace the placeholders with your values.

You can later enrich the sample with more parameters depending on your business and technical requirements (see Customize the Configuration File).

Batch Mode

Batch mode is supported by CSV Downloader only.

In batch mode, the data for all entities is put to the source tables first. Then, if all data is correctly copied to the source tables, the process continues. If an error occurred during integrating any of the entities into the batch, the whole process stops, and no data is integrated into the stage tables.

By default, ADS Integrator processes only one batch, but you can change this (see Specify the number of batches to process in one run).

{
  "integrators": {
    "ads_integrator_id": {
      "type": "ads_storage",
      "batches": ["csv_downloader_1","csv_downloader_2"]
    }
  },
  "ads_storage": {
    "instance_id": "data_warehouse_instance_id",
    "username": "dw_email@address.com",
    "options": {}
  }
}

Entity Mode

Entity mode is supported by all downloaders except for CSV Downloader.

In entity mode, entities are processed one by one. The data from one entity is loaded to the source tables and then integrated into the stage tables.

ADS Integrator processes all the files from one entity that are present in the BDS.

{
  "integrators": {
    "ads_integrator_id": {
      "type": "ads_storage",
      "entities": ["entity_1","entity_2","entity_3"]
    }
  },
  "ads_storage": {
    "instance_id": "data_warehouse_instance_id",
    "username": "dw_email@address.com",
    "options": {}
  }
}

The placeholders to replace with your values:

Customize the Configuration File

You can change default values of the parameters and set up additional parameters.

When you set up an optional parameter, it is saved in the S3 file’s metadata (not in the metadata folder, but in an attribute of the file itself). To change the parameter value later, edit the metadata files manually.

Set the data integration strategy

The integration strategy defines how data is integrated into the stage tables on ADS.

• merge: This strategy uses one key for all operations (uniqueness, merging, and so on). This key is computed during the COPY command as an MD5 of all the fields that are marked as primary key in the ‘hub’ setting of the entity (see the system field  ‘_sys_hub_id’ in the source table).
• merge_with_pk: This strategy uses all primary keys for all operations (uniqueness, merging, and so on).

The final output is the same for either strategy. The only difference is how the final projection created on stage tables is optimized.

• For the merge integration strategy, the projection is optimized for one ID field.

• For the merge_with_pk strategy, the projection is optimized for all primary keys in the order they are set up in the entity’s settings (the ‘hub’ parameter in the section of the configuration file that describes the downloader you are using):

  "entities": {
    "table_1_name": {
      "global": {
        "custom": {
          "hub": ["column_1_name","column_2_name","column_3_name"]
        }
      }
    }
  }
  

"ads_storage": {
  "instance_id": "data_warehouse_instance_id",
  "username": "dw_email@address.com",
  "options": {
    "default_strategy": "merge|merge_with_pk" // optional
  }
}

Add multiple ADS Integrators

You can use multiple instances of ADS Integrator to process your downloaders. Update the ‘integrators’ section to contain multiple subsections: a subsection per ADS Integrator instance. Each subsection must refer the ID of the corresponding ADS Integrator instance.

Depending on what downloaders you are using, you need to specify load mode for each ADS Integrator instance: batch mode or entity mode.

"integrators": {
  "ads_integrator_1_id": {
    "type": "ads_storage",
    "batches": ["csv_downloader_1","csv_downloader_2"]
  },
  "ads_integrator_2_id": {
    "type": "ads_storage",
    "batches": ["csv_downloader_3"]
  },
  {
  "ads_integrator_3_id": {
    "type": "ads_storage",
    "entities": ["entity_1","entity_2","entity_3"]
  },
  "ads_integrator_4_id": {
    "type": "ads_storage",
    "entities": ["entity_4","entity_5","entity_6"]
  }
}

Use a different server

By default, ADS Integrator uses secure.gooddata.com to access to the ADS instance. You can specify a different URL (for example, you are a white-labeled customer).

"ads_storage": {
  "instance_id": "data_warehouse_instance_id",
  "username": "dw_email@address.com",
  "server": "your.server.url", // optional
  "options": {}
}

Use COPY FROM S3 instead of COPY FROM LOCAL

If your source files are stored in an S3 location (see ‘Use an S3 location’ in CSV Downloader), the files are first downloaded from the S3 location and then loaded using the COPY FROM LOCAL command. Using COPY FROM S3 loads the files directly from the S3 location which may be faster.

"ads_storage": {
  "instance_id": "data_warehouse_instance_id",
  "username": "dw_email@address.com",
  "options": {
    "copy_from_s3": true|false
  }
}

Specify how to proceed in case of errors and rejected records

By default, if an error occurs and some data cannot be integrated into ADS, ADS Integrator fails. To make ADS Integrator ignore errors, set the ‘abort_on_error’ parameter to ‘false’. This disables the ABORT ON ERROR option in each COPY command.

To additionally specify how to treat the records that have not been parsed during the process and have been rejected, use the optional ‘enable_rejected_table’ parameter.

"ads_storage": {
  "instance_id": "data_warehouse_instance_id",
  "username": "dw_email@address.com",
  "options": {
    "abort_on_error": true|false, // optional
    "enable_rejected_table": true|false // optional
  }
}

Add optional parameters

Depending on your business and technical requirements, you can set one or more optional parameters affecting the data integration process.

"ads_storage": {
  "instance_id": "data_warehouse_instance_id",
  "username": "dw_email@address.com",
  "disable_file_level_logging": true|false, // optional
  "options": {
    "integration_group_size": number_of_files, // optional
    "remove_deleted_records": true|false, // optional
    "group_by_version": true|false // optional
  }
}

Configure additional settings in the source table and stage table

To extend the source table and stage table with additional settings, configure computed fields. For more details, see Computed Fields.

Add system fields to the load table or file table

To add the system fields to the load table or file table, configure system fields. Data can populate these fields only from the runtime metadata. The runtime metadata is filled in by downloaders. To find out what metadata a downloader creates, see the corresponding downloader’s article in Downloaders.

The ‘system_table_fields’ section contains arrays of parameters for the load table and file table.

"system_table_fields": {
  "file_table": [
    {
      "name": "custom_parameter",
      "type": "parameter_data_type",
      "function": "function_name"
    }
  ],
  "load_table": [
    {
      "name": "custom_parameter",
      "type": "parameter_data_type",
      "function": "function_name"
    }
  ]
}

Example:

"system_table_fields": {
  "file_table": [
    {
      "name": "load_param",
      "type": "string-255",
      "function": "runtime_metadata|load_param"
    }
  ]
}

Specify the number of batches to process in one run

When working with batch mode, you can specify how many batches ADS Integrator should process in one run.

[Example]{.ul}: Entity data in all batches is in incremental load mode  You have 10 batches to process, and the number of batches in one run is set to 5.

The 10 batches will be processed in the next two runs of ADS Integrator.

ADS Integrator downloads all the data for the number of the batches that you have specified and checks whether all entities have only one version of metadata.

• If yes, ADS Integrator will process all data in one run: copy all the data to the source table and then use MERGE to put it to the stage table.
• If not, ADS Integrator will split the data into multiple parts and process them independently. The order of the batches will be preserved.

[Example]{.ul}: Some entity data is in full load mode, the rest is in incremental load mode  You have 10 batches to process, and the number of batches in one run is set to 5. The entity data in Batch 5 and Batch 8 are marked as ‘full’.

In the first run, ADS Integrator takes the batches from 1 through 5. Batch 5 is the one with full load mode, so ADS Integrator ignores all incremental batches before this batch and only processes Batch 5.

In the second run, ADS Integrator ignores Batch 6 and Batch 7 (incremental mode), then processes Batch 8 (full mode), and then processes incremental batches: Batch 9 and Batch 10.

"ads_storage": {
  "instance_id": "data_warehouse_instance_id",
  "username": "dw_email@address.com",
  "options": {
    "number_of_batches_in_one_run": number_of_batches // optional
  }
}

Use link files

When you are using CSV Downloader and chose to use link files (see ‘Use link files’ in CSV Downloader), set ADS Integrator that processes this instance of CSV Downloader to use the link files.

"ads_storage": {
  "instance_id": "data_warehouse_instance_id",
  "username": "dw_email@address.com",
  "options": {
    "remote": {
      "type": "s3",
      "access_key": "access_key",
      "bucket": "bucket_name"
    }
  }
}

Enable processing of PGP-encrypted source files when using link files

When you are using link files in CSV Downloader (see ‘Use link files’ in CSV Downloader) and your source files are PGP-encrypted, enable ADS Integrator that processes this instance of CSV Downloader to process encrypted files.

Steps:

1. Set ADS Integrator that processes this instance of CSV Downloader to use the link files.

   "ads_storage": {
     "instance_id": "data_warehouse_instance_id",
     "username": "dw_email@address.com",
     "options": {
       "remote": {
         "type": "s3",
         "access_key": "access_key",
         "bucket": "bucket_name"
       }
     }
   }
   

   Name	Type	Mandatory?	Default	Description
   access_key	string	yes	n/a	The access key to the S3 bucket where the source data is stored
   bucket	string	yes	n/a	The name of the S3 bucket where the source data is stored

2. Add the ‘encryption’ parameter under ‘ads_storage’ -> ‘options’, and give it a unique name (for example, ‘encryption_1’).

   "ads_storage": {
     "instance_id": "data_warehouse_instance_id",
     "username": "dw_email@address.com",
     "options": {
       "remote": {
         "type": "s3",
         "access_key": "access_key",
         "bucket": "bucket_name"
       },
       "encryption": "encryption_1"
     }
   }
   

3. Add a new section to the configuration file and name it with the same unique name that you used for the ‘encryption’ parameter. In this example, it is ‘encryption_1’. This section should contain one parameter, ‘type’, set to ‘pgp’:

   "encryption_1": {
     "type": "pgp"
   }
   

4. When later scheduling this ADS Integrator instance in the Data Integration Console, provide the ‘ads_integrator|options|remote|secret_key’ parameter (see General Parameters) and the parameters that are related to the decryption of the PGP-encrypted source files (see Encryption-Specific Parameters).

Adjust advanced settings

ADS Integrator allows you to adjust various advanced settings, which, for example, can help improve performance.

"ads_storage": {
  "instance_id": "data_warehouse_instance_id",
  "username": "dw_email@address.com",
  "options": {
    "resource_pool": "resource_pull", // optional
    "pool_timeout": number_of_seconds, // optional
    "integrate_without_sys_hash": true|false, // optional
    "keep_values_when_deleted": true|false, // optional
    "default_strategy_override": {"entity_1": "strategy_name","entity_2": "strategy_name"}, // optional
    "max_connections": number_of_connections, // optional
    "connection_refresh_time": number_of_minutes, // optional
    "multi_load": true|false, // optional
    "use_direct_hint_in_copy": true|false, // optional
    "truncate_source_tables": true|false, // optional
    "disable_statistics_stg": true|false, // optional
    "disable_sys_load_id_logging": true|false, // optional
    "disable_sys_id": true|false, // optional
    "use_uniq_table": true|false, // optional
    "insert_to_stage": true|false, // optional
    "skip_projection_check": true|false, // optional
    "segmentation": segmented|unsegmented, // optional
    "partition_by": "field_name", // optional
    "drop_source_partition": true|false, // optional
    "source_projection": { <custom_segmentation> }, // optional
    "ignored_system_fields": "["sys_field_1","sys_field_2"]", // optional
    "join_files": true|false, // optional
    "finish_in_source": true|false, // optional
    "recreate_source_tables": true|false, // optional
    "number_of_parallel_queries": number_of_copy_commands, // optional
    "number_of_parallel_entities_integrations": number_of_entities_to_source, // optional
    "number_of_parallel_integrations": number_of_entities_to_stage // optional
  }
}

"default_strategy_override": {
  "entity_1" : "merge_with_pk",
  "entity_2" : "merge_with_pk"
}

"source_projection": {
  "segmentation": ["name"],
  "order_by": ["id"]
}

Schedule Parameters

When scheduling ADS Integrator (see Phases of Building the Data Pipeline -> Production Implementation), provide the parameters from this section in the schedule.

General Parameters

Some parameters must be entered as secure parameters (see Configure Schedule Parameters).

Some parameters contain the names of the nesting parameters, which specifies where they belong in the configuration hierarchy. For example, a parameter entered as ads|options|password is resolved as the following:

"ads": {
  "options": {
    "password": ""
  }
}

Enter such parameters as is (see Schedule Example).

Encryption-Specific Parameters

If you enable processing of PGP-encrypted source files when using link files, provide the encryption-specific parameters as secure schedule parameters (see Configure Schedule Parameters).

Some parameters contain the names of the nesting parameters, which specifies where they belong in the configuration hierarchy. For example, a parameter entered as csv|options|password is resolved as the following:

"csv": {
  "options": {
    "password": ""
  }
}

Enter such parameters as is (see Schedule Example).

Schedule Example

The following is an example of how you can specify schedule parameters:

Best Practices

For better performance, we recommend that you apply the following best practices when using ADS Integrator:

• Keep the total size of the files in one integration group below 1 GB. An integration group is usually a part of the manifest file containing x files, where x is the value of the ‘integration_group_size’ parameter (see Add optional parameters). The default of this parameter is 1, therefore unless you change it, the files are integrated one by one.
• When processing many smaller files, set the ‘integration_group_size’ parameter to a higher value to copy all the files in a single COPY command (see Add optional parameters). Note, however, that setting this parameter to a higher value will stop loading new rows to the file table.
• Do not use the ‘integration_group_size’ parameter if you have multiple versions of a data record in your source data.
• For better performance on big tables, set the ‘disable_statistics_stg’ parameter to ‘true’ (see Adjust advanced settings).
• Use the ‘skip_projection_check’ parameter (see Adjust advanced settings) only as a last resort or a temporary solution.
• Use the ‘integrate_without_sys_hash’ parameter (see Adjust advanced settings) carefully. Suppressing the ‘_sys_hash’ field may result in reduced integration performance due to potentially costly hash joins.
• Make sure that the source table and stage table have the same columns so that optimized MERGE is used.