Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How Can An Query for Getting Metadata Cost 9G memory?

edited December 2017 in General Discussion

We use Sqoop to pull the data from Vertica. Sqoop generates a query with condition (1=0) to get the field type information and the query does not need to return any records. However, the query needs 9G memory allocations, just as the second query Sqoop fired for returning the records. We think this is wrong... could anyone help on this? Do we have any trick to let the trivial query (with condition 1=0) to use minimum memory?

Comments

  • skeswaniskeswani Employee
    edited December 2017

    the following three are equivalent, #3 is best and uses least memory
    1. select * from t where 1=0;
    2. select * from t where false;
    3. select * from t limit 0;

    but what you really want to run is the following.

    SELECT quote_ident(c.table_schema), quote_ident(c.table_name), quote_ident(c.column_name),
    c.data_type, c.data_type_length, c.column_default, not c.is_nullable,
    p.constraint_id is not null,
    f.rts || '.' || f.rtn || '(' || f.rcn || ')' || case when f.c < 2 then '' else ', ...' end
    FROM v_catalog.columns AS c
    LEFT OUTER JOIN v_catalog.primary_keys p ON c.table_schema=p.table_schema
    AND c.table_name=p.table_name AND c.column_name=p.column_name
    LEFT OUTER JOIN (SELECT table_schema, table_name, column_name,
    quote_ident(MAX(reference_table_schema)) as rts,
    quote_ident(MAX(reference_table_name)) as rtn,
    quote_ident(MAX(reference_column_name)) as rcn,
    count(*) as c
    FROM v_catalog.foreign_keys
    WHERE table_schema ILIKE E'%' ESCAPE E'\' AND table_name ILIKE E't' ESCAPE E'\'
    GROUP BY table_schema, table_name, column_name
    ) AS f
    ON c.table_schema=f.table_schema AND c.table_name=f.table_name
    AND c.column_name=f.column_name
    WHERE c.table_schema ILIKE E'%' ESCAPE E'\' AND c.table_name ILIKE E't' ESCAPE E'\'
    ORDER BY c.table_schema, c.table_name, c.ordinal_position;

  • skeswaniskeswani Employee
    edited December 2017

    can you provide the query, with the profile information.
    my internal test show it take 4MB of memory

    skeswani=> profile select * from t t1 natural join t t2 where 1=0;
    NOTICE 4788: Statement is being profiled
    HINT: Select * from v_monitor.execution_engine_profiles where transaction_id=45035996273715386 and statement_id=12;
    NOTICE 3557: Initiator memory for query: [on pool general: 4289 KB, minimum: 4289 KB]
    NOTICE 5077: Total memory required by query: [4289 KB]

  • edited December 2017

    Our query includes some group by. The query itself if it does not include group by, the returned records will be 17million. With group by, the result set is about 1.4M. It is something like as below. Please note that $CONDITIONS is what sqoop needs (it will be"1=0" for the metadata).

    SELECT
    f1,
    f2,
    TO_CHAR(datetime,'YYYYMMDDHH24MI') dt1,
    TO_CHAR(datetime,'YYYYMMDDHH24MI') dt2 ,
    SUM(NVL(period, 0)) period ,
    NVL(SUM (m1) ) :: VARCHAR,'') n3 ,
    NVL(SUM( m2 ) :: VARCHAR,'') n4
    FROM Table
    WHERE $CONDITIONS
    datetime >= TO_TIMESTAMP('2017112908','YYYYMMDDHH24')
    AND datetime < TO_TIMESTAMP('2017112916','YYYYMMDDHH24')
    AND datetimeutc >= TO_TIMESTAMP('2017112919','YYYYMMDDHH24')
    AND datetimeutc < TO_TIMESTAMP('2017112920','YYYYMMDDHH24')
    AND eutrancellfdd NOT LIKE '%TOY%'
    GROUP By
    f1,
    f2,
    TO_CHAR(datetime,'YYYYMMDDHH24MI'),
    TO_CHAR(datetimeutc,'YYYYMMDDHH24MI')
    -- limit 1

  • can you run this query with profile in front. also please replace the condition with what the actual condition is. its fair and correct for the query to take 9GB, unless the predicate reliably folds to a "false". i need to know for sure if that is indeed the case.

    if you prefer i can run it if you give me the table sql ddl

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.