Any way to get query data dynamically while its executing.

sahil_kumar

A client asked , if it is possible to get query data dynamically while it is executing, in batches for example.
instead of waiting until all the data is fetched and formated, client wants to get each time part of the data that has already been parsed.
for example select * from table limit 10000; (instead of waiting to all the 10000 rows get 1000 each time)
if it exists does it works with the order by clause?

marcothesane

This sounds to me as a front-end functionality rather than a Vertica sided one.
In a JDBC / ODBC front end application, you SQLPrepare and SQLExecute a query - returning maybe 10,000 rows - and then you set the rowset to , say, 100 rows ; then repeated calls of SQLFetch() will return 100 rows at a time.
Is that what you are looking for?

Abhishek_Rana

There is a way in vertica to fetch result sets in batches using OFFSET.
But there can be some tricks to accomplish this depending upon customer's use case too:
1) In client software like DBeaver we have an option to set number of records to fetch by setting "Resultset Fetch Size".
2) I can think of a another way to accomplish that using below trick with rownum:
Set below variables as per need:

Suppose I want batch of 10 rows at a time:
\set NumRows 10

Now I want 2nd resulset, means rows from 20-30
\set i 2

Now use below example query to fetch records: I am using eep as table name.

SELECT * FROM
(
SELECT
row_number() OVER(ORDER BY transaction_id) AS RW,
eep.*
FROM eep
) myAlias
WHERE myAlias.RW BETWEEN (:NumRows * :i) AND ((:NumRows * :i)+ :NumRows)

Hope it helps.

sahil_kumar

thanks @Abhishek_Rana for detailed explanation.

qinchaofeng

Do you want the paging function？
You can refer to OFFSETClause

sahil_kumar

@qinchaofeng yes the client referred paging function.

sahil_kumar

Not really sure if offsetclause will help to get query data dynamically.

sahil_kumar

thanks @marcothesane that actually makes sense.

mosheg

In addition maybe parallel insert select can provide a way to analyze portions of the result set.
Example 1:

    INSERT INTO MY_TABLE  
           SELECT a,b,c
           FROM OTHER_TABLE
           WHERE hash(a)/((9223372036854775807/YOUR_NODE_COUNT) + 1)= YOUR_SEGMENT;

Example 2:

    INSERT INTO foo_cp
    SELECT * from foo
    WHERE HASH( some_column ) % 4 = 0;

    INSERT INTO foo_cp
    SELECT * from foo
    WHERE HASH( some_column ) % 4 = 1;

    INSERT INTO foo_cp
    SELECT * from foo
    WHERE HASH( some_column ) % 4 = 2;

    INSERT INTO foo_cp
    SELECT * from foo
    WHERE HASH( some_column ) % 4 = 3;