Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I'm bringing a lot of records (about 50 million) from a table in a Vertica DB. The table is expected to keep growing, so I want to use a concatenated load script and a qvd file to avoid having to spend too much time for each load.
I' ve been trying to replicate what is explained on this post Incremental reload and store into QVD file but in the end, I keep getting the same error:
The following error occurred:
Connector reply error: ***
The error occurred here:
?
Would you have a look on my script and let me know if there's something I can fix?
Thanks!
Juan
---
LET lastReloadTime = Date(ReloadTime(),'YYYY-MM-DD HH:MM:SS');
// If traffic.qvd already exists, we load its data
IF qvdcreatetime("D:\Temp\data_traffic\traffic.qvd") Then
[traffic]:
LOAD "start_date" as link_timestamp,
"item_id" as [link.id],
speed,
"speed_mph",
traveltime
FROM
ELSE
// If traffic.qvd does not exist, we create it with a SQL query
[traffic]:
LOAD "start_date" as link_timestamp,
"item_id" as [link.id],
speed,
"speed_mph",
traveltime;
SQL SELECT "start_date",
"item_id",
speed,
"speed_mph",
traveltime
FROM pems."public"."measure_link_15min";
END IF
// We concatenate any new data (data whose link_timestamp is later than the lastReloadTime)
CONCATENATE ([traffic])
LOAD "start_date" as link_timestamp,
"item_id" as [link.id],
speed,
"speed_mph",
traveltime;
SQL SELECT "start_date",
"item_id",
speed,
"speed_mph",
traveltime
FROM pems."public"."measure_link_15min"
WHERE (link_timestamp > $(lastReloadTime));
STORE [traffic] INTO
Issue is in connector not in the script.
So part SQL Select can not be executed properly.
Right, I changed the WHERE clause:
WHERE ("start_date" > $(lastReloadTime));
I was using link_timestamp by mistake. However, I'm getting the same error....
Hi Juan, note that the query passed will be:
WHERE ("start_date"> 2017-11-29 16:32:30);
¿Can this work on Vertica DB? Syntaxis will depend on database, not QlikView
Hi Ruben. I've checked and in Vertica I need apostrophes for the date:
WHERE ("start_date"> '2017-11-29 16:32:30');
I've added them in the script, but unfortunately I'm getting the same error
WHERE (start_date > '$(lastReloadTime'));
Hi Juan, I don't know if it's a typo, but it should be:
WHERE (start_date > '$(lastReloadTime)'); //closing apostrophe moved
Wow, good catch! Thank you Ruben!
Now I got a different error, I'm going to try to solve it and I'll post here the conclusions.
Thanks!
I solved some other issues non related to the connector and it worked.
Thank you all for your support.