Qlik Replicate - how to rename a source table to an existing target table during replication?

[Karun]

I'm trying to replicate data from source (Oracle) to target (Postgres). While replicating, I'd like to rename some source tables to existing target tables. I'm unable to figure out a way to do this. Whether I try to use the Global Rules transformation or single-table setting (Map to target table), it seems Replicate is trying to create the target table, and fails because the target  table already exists.

In short, is there a way to copy table A from source to table B in target, given that table B already exists in target?

[john_wang]

Hello @Karun ,

I think you are almost there. Please change the task setting "Full Load Settings" --> "If the target table already exists" to "TRUNCATE before loading" as below:

In the meanwhile please keep your transformation (Global level, or table level). Let me know if it works for you.

Good luck and Best Regards,

John.

[Karun]

Thanks for the quick reply @john_wang! But I already have the setting to "Truncate before loading". It is still trying to create the table, which is what confuses me. 

[Dineshan]

Hello @Karun,

Please review the option 2 of the article below.

https://community.qlik.com/t5/Official-Support-Articles/How-to-get-two-tables-archive-and-non-archiv...

Thank you,

Dinesh

[john_wang]

Hello @Karun ,

If you set to TRUNCATE already then it's some weird, you may set TARGET_LOAD to Verbose and check the task log file , eg search keyword "create table" to see why Replicate try to create the table again rather than use/write the table straightly. A sample:

Hope this helps.

Regards,

John.

[Karun]

The log file lines (with verbose turned on) that corresponds to table creation in the target schema are these (schema/table names replaced with placeholders):

00006914: 2022-11-16T08:36:35:55758 [TARGET_LOAD ]V: Event received: source id '0' operation 'LOAD_START_OF_TABLE (31)' event id '1' event subid '0' table id '1' context '(null)' timestamp '1668605794715622' commit timestamp '0' (streamcomponent.c:2281)
00006914: 2022-11-16T08:36:35:55777 [TARGET_LOAD ]T: First event id 1. Last was 0 (streamcomponent.c:2286)
00006914: 2022-11-16T08:36:35:55801 [TARGET_LOAD ]T: Going to create table 'targetschema'.'target_table' (endpointshell.c:2817)
00006914: 2022-11-16T08:36:35:58037 [TARGET_LOAD ]T: Getting all tables that match following pattern: owner: 'targetschema', table: 'target\_table' (ar_odbc_conn.c:1496)
00006914: 2022-11-16T08:36:35:59282 [TARGET_LOAD ]T: Get tables returned 0 tables (ar_odbc_conn.c:1718)
00006914: 2022-11-16T08:36:35:59334 [TARGET_LOAD ]T: Create primary key statement: ALTER TABLE "targetschema"."target_table" ADD PRIMARY KEY (<PK list>) (provider_syntax_manager.c:2041)
00006914: 2022-11-16T08:36:35:59357 [TARGET_LOAD ]T: Create table statement: CREATE TABLE "targetschema"."target_table" (<column list>) (provider_syntax_manager.c:1717)
00006914: 2022-11-16T08:36:35:59364 [TARGET_LOAD ]V: Execute immediate: 'CREATE TABLE "targetschema"."target_table" (<column list>)' (ar_odbc_stmt.c:4908)
00006914: 2022-11-16T08:37:35:61235 [TARGET_LOAD ]T: RetCode: SQL_ERROR SqlState: 57014 NativeError: 1 Message: ERROR: canceling statement due to statement timeout;
Error while executing the query [1022502] (ar_odbc_stmt.c:4992)

I am not able to see any lines before that which indicate there is a specific setting in the task that's causing this table creation.

Any pointers please? 

[john_wang]

Hello @Karun ,

I'm afraid it's hard to tell from the piece of the error lines this time. If the correct target database and schema are used then maybe a privilege issue? eg the table does exist however the account you are using in Replicate has not the privilege to see/access it; so Replicate is trying to create it again then you get error.

You may connect to the database by the same user and try to access the table to see what's the result.

Best Regards,

John.

[Karun]

Hi @john_wang, the account I'm using in Replicate is the same account that I'm using in the target database. That account is also the table owner, so I don't think there are privilege issues.

I don't understand why, in these two lines, the result says "returned 0 tables":

00006914: 2022-11-16T08:36:35:58037 [TARGET_LOAD ]T: Getting all tables that match following pattern: owner: 'targetschema', table: 'target\_table' (ar_odbc_conn.c:1496)
00006914: 2022-11-16T08:36:35:59282 [TARGET_LOAD ]T: Get tables returned 0 tables (ar_odbc_conn.c:1718)

I have used the same account to query for the presence of the table using: pg_tables, pg_catalog.pg_class, information_schema.tables. In all these cases, I'm able to see the presence of the table in the target database. 

However, it appears, from the above two lines, Replicate is not able to see the table present in the system. 

How is Replicate querying the catalog of tables in PostgreSQL? If privileges are not an issue, what else could it be? 

[john_wang]

Hello @Karun ,

Noticed you opened a support ticket (#00060322), thanks for that. please  set TARGET_LOAD/TARGET_APPLY to Verbose, reproduce the behaivor and attach the task Diag Packages Downloading a diagnostics package . We are gladly to help you solve the issue.

Thank you,

John.

[john_wang · Accepted solution]

Hello @Karun ,

Per the support ticket, we finally make it env ready and all works fine now:

1. IN the beginning, there is 2 versions of PostgreSQL client installed, v9.2 & v12.01. PostgreSQL client v9.2 cannot detect the table existence correctly (while connect to PostgreSQL v14 server) and leads problems (reported in this article);

2. Even we change something in the env, and Replicate can use PostgreSQL client v12.1, however the v9.2 impact PSQL LOAD utility still, it leads new error:

2022-11-24T02:39:04:491649 [TARGET_LOAD     ]T:  Command failed to load data with exit error code 127, Command output: psql: symbol lookup error: psql: undefined symbol: PQsetErrorContextVisibility [1020403]

3. Remove the v9.2 unnecessary drivers/libraries installation, then the problem solved, Replicate works fine now.

Thank you for your great support Karun.

Best Regards,

John.