7 Replies Latest reply: Nov 29, 2017 12:12 PM by Juan Moreno RSS

    QVD file, data concatenation

    Juan Moreno

      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 [D:\Temp\data_traffic\traffic.qvd] (qvd);

      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 [D:\JTMC\data_traffic\traffic.qvd]