Qlik Replicate and S3 target: How to rename output files LOAD######## to table name format (Replicate runs on Windows)

john_wang · Aug 15, 2023 11:24:53 AM

When using an Amazon S3 as a target in a Qlik Replicate task, the Full Load data are written to CSV, TEXT, or JSON files (depending on the endpoint settings). The Full Load Files are named using incremental counters e.g. LOAD00000001.csv, LOAD00000002.csv. This is the default behavior.

In some scenarios, you may want to use the table name as the file name rather than LOAD########.

This article describes how to rename the output files from LOAD######## to <schemaName>_<tableName>__######## format while Qlik Replicate running on a Windows platform.

In this article, we will focus on cloud types of target endpoint (ADLS, S3, etc...) The example uses Amazon S3 which locates remote cloud storage.

This customization is provided as is. Qlik Support cannot provide continued support for the solution. For assistance, reach out to Professional Services.

Resolution

Open and modify repctl.cfg

Default file location: C:\Program Files\Attunity\Replicate\bin

Set enable_execute_user_defined_commands from false to true

Example: "enable_execute_user_defined_commands": true,
Download and run the AWS CLI MSI installer for Windows (64-bit) (aws-cli/2.13.9 is used in this sample)
Set up the AWS CLI, and Configuring using AWS CLI commands. Sample steps of short-term credentials:
C:\Users\demo>aws configure
AWS Access Key ID [None] : DEMO~~~~~~~~~~~~UXEM
AWS Secret Access Key [None] : demo~~~~~~~~~~~~ciYW7pugMTv/0DemoSQtfw1m
Default region name [None] : us-east-1
Default output format [None] : json
Restart the Qlik Replicate Server service.
Create a text/batch file named myrename_S3.bat and save it in the Qlik Replicate Server folder: C:\Program Files\Attunity\Replicate\bin

Content of the file:
```
@Echo on
setx AWS_SHARED_CREDENTIALS_FILE C:\Users\demo\.aws\credentials
for  %%a in (%1) do set "fn=%%~na"
echo %fn%
set  sn=%fn:~4,8%
echo %sn%
aws s3 mv s3://%1 s3://qmi-bucket-1234567868c4deded132f4ca/APAC_Test/%2.%3/%2_%3__%sn%.csv
```
where C:\Users\demo\.aws\credentials is generated in above step 3. The values are obfuscated in the above sample.
Set the Amazon S3 Target endpoint and configure it with the following settings:

General
Bucket name : qmi-bucket-1234567868c4deded132f4ca
Bucket region : US East (N. Virginia)

Access options : Key pair
Access key : DEMO~~~~~~~~~~~~UXEM
Secret key : demo~~~~~~~~~~~~ciYW7pugMTv/0DemoSQtfw1m

Target folder : /APAC_Test

Advanced
Post Upload Processing, choose "Run command after upload"
Command name : myrename_S3.bat
Working directory: leave blank
Parameters : ${FILENAME} ${TABLE_OWNER} ${TABLE_NAME}

7. Startup or Reload the Full Load ONLY task and verify the file output.

By using command "aws s3 ls", it will typically looks like:

C:\Users\demo>>aws s3 ls s3://qmi-bucket-1234567868c4deded132f4ca/APAC_Test --recursive --human-readable --summarize
2023-08-14 11:20:36 0 Bytes APAC_Test/
2023-08-15 08:10:24 0 Bytes APAC_Test/SCOTT.KIT/
2023-08-15 08:10:28 9 Bytes APAC_Test/SCOTT.KIT/SCOTT_KIT__00000001.csv
2023-08-15 08:10:24 0 Bytes APAC_Test/SCOTT.KIT500K/
2023-08-15 08:10:34 4.0 MiB APAC_Test/SCOTT.KIT500K/SCOTT_KIT500K__00000001.csv
2023-08-15 08:10:44 4.0 MiB APAC_Test/SCOTT.KIT500K/SCOTT_KIT500K__00000002.csv
2023-08-15 08:10:54 4.0 MiB APAC_Test/SCOTT.KIT500K/SCOTT_KIT500K__00000003.csv
2023-08-15 08:11:05 4.0 MiB APAC_Test/SCOTT.KIT500K/SCOTT_KIT500K__00000004.csv
2023-08-15 08:11:15 4.0 MiB APAC_Test/SCOTT.KIT500K/SCOTT_KIT500K__00000005.csv
2023-08-15 08:11:24 2.7 MiB APAC_Test/SCOTT.KIT500K/SCOTT_KIT500K__00000006.csv

Total Objects: 10
Total Size: 22.7 MiB

Where SCOTT is the source table schema name, KIT/KIT500K are source table name, 0000000<n> is the unique incremental number.

You can tailor the myrename_S3.bat file for any particular needs.

Environment

Qlik Replicate
Amazon S3 target

Qlik Replicate and S3 target: How to rename output files LOAD######## to table name format (Replicate runs on Windows)