Extract_in_1M_Chunks

    In a  recent project, I ran into an issue of the ODBC connection getting dropped when connecting to Amazon RedShift Cloud.

    The data volume was about 31M records - we could not trace it to a specific reason as to why the connection was being.

    Here's what I did to resolve it.

     

    //Step1:

    I first performed a select on the table I was extracting from to get a total count of the number of records:

    store the count in a variable.

     

    $(QVDatabaseTable_count):

    SQL SELECT     count(*)FROM $(DB).$(DBSchema).$(DatabaseTable);

    Let vCount =peek('count');


    //Step2:

    //Loop through the table in extract chunks of 1M  records.

     

    For counter = 0 to $(vCount) step 1000000

    ODBC CONNECT TO PostgreSQL30

     

    $(QVDatabaseTable):

    SQL SELECT

        "fieldA",

        "fieldB",

    FROM $(DB).$(DBSchema).$(DatabaseTable)

    where "row" >= $(counter)+1 and "row" <= $(counter)+1000000;

     

    DISCONNECT

    next;


    // "row" (indicating row number) is a field that had to be added to the table, for  this fix to work as designed.

    This has been working perfectly.

    base.document test.qvs (650 bytes) View  Download