0

I'm trying to load data from a very large table (200 million rows) into a Presentation Datamart (PDM) for an Enterprise Data Warehouse using Teradata 14.10. I want to split this large table into several separate tables containing 30 million rows each.

I've simplified things down, but here's the table structure:

CREATE SET TABLE MYDB.LARGE_TABLE ,NO FALLBACK ,
    NO BEFORE JOURNAL,
    NO AFTER JOURNAL,
    CHECKSUM = DEFAULT,
    DEFAULT MERGEBLOCKRATIO
(
    CUSTOMERID INTEGER TITLE 'CUSTOMER IDENTIFIER' NOT NULL
    , FULLNAME VARCHAR(30) NOT CASESPECIFIC TITLE 'FULLNAME'
);

I've gotten as far as using ROW_NUMBER() so I know how many actual rows there are in the table:

SELECT
    ROW_NUMBER() OVER (PARTITION BY CUSTOMERID, ORDER BY CUSTOMERID) AS RANK_CUST
    , CUSTOMERID
    , FULLNAME
FROM
    MYDB.LARGE_TABLE AS MYTBL

Because of the SQL standards that are enforced we have to follow these restrictions:

  • Cannot use Stored Procedures
  • No single piece of code to contain more than 3 table joins
  • No single piece of code to result in more than 30 million rows of output

I'm preparing this SQL script for a very stringent review, and it definitely won't be approved unless I can find a way to split this large table into smaller rows of data. With these restrictions in mind, does anyone have any ideas?

  • "No single piece of code to contain more than 3 table joins" Why? – ypercubeᵀᴹ Feb 2 '15 at 22:09
1

You can use the QUALIFY statement to restrict the number of rows against a large table.

SELECT CUSTOMERID
     , FULLNAME
  FROM MYDB.LARGE_TABLE AS MYTBL
QUALIFY ROW_NUMBER() OVER (ORDER BY CUSTOMERID) 
        BETWEEN 1 and 30000000;

edit - corrected syntax of ROW_NUMBER()

  • There should be no comma before ORDER BY. But that's just a syntax issue, actually you shouldn't have a PARTITION BY clause there at all, because the restriction is simply "no more 30 million rows" rather than "no more 30 million rows per CUSTOMERID". – Andriy M Feb 4 '15 at 10:21
  • You're right. I took the OP's SQL and moved tit to the qualify without careful consideration for the syntax. – RobPaller Feb 4 '15 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.