Numeric overflow error. Querying 27+ billion row table. Finding ranges.

Database
N/A

Numeric overflow error. Querying 27+ billion row table. Finding ranges.

Hi,

I am trying to find the PERSON_ID ranges by row count in a 27+ billion row table. This is so I can subset the data in smaller 'chunks' for processing.

I have tried a few methods but they all return a 'numeric overflow' error. I am coming to a conclusion these sql functions can't handle such a big number? I cannot find any documentation on how many rows these functions can handle.

I am looking for any solution at this stage. Any help is appreciated.

Below is a sample of the sql I am trying to run to find the range:

SELECT
PERSON_ID
,QUANTILE(100, PERSON_ID) AS QUANT
FROM
DATABASE.TABLE
QUALIFY
QUANT = 1
ORDER BY 1
;

------
/* I have tried a number of CASTING variations to no avail*/
SEL A.PERSON_ID,ROW_NUM, ROW_NUM MOD 400000000 AS PICK
FROM
(
SEL
PERSON_ID,
ROW_NUMBER() OVER(ORDER BY PERSON_ID ASC) AS ROW_NUM
FROM
DATABASE.TABLE
) AS A
WHERE PICK = 0

1 REPLY
N/A

Re: Numeric overflow error. Querying 27+ billion row table. Finding ranges.

I don't see any CASTing in your queries :-)

ROW_NUMBER is a kind of COUNT, in a Teradata mode session it returns an INT.
To do a count on a larger table you have to CAST(COUNT(*) AS BIGINT or DECIMAL).

Should be the same for ROW_NUMBER (but i can't try it right now).

Btw, this is a lot of work just for breaking the data into smaller pieces.
Couldn'd you just use PERSON_ID MOD 100 = 1 or is PERSON_ID non-numeric?

Dieter