What is the sql query in order to check the size of the particular sql query in hp vertica ?

UJJWAL_RANA

Hi

What is the query to check the size of the particular sql query in  hp vertica i.e mentioned below ? Please advise.

 

Say like i wanted to know the table size of the following query :-

 

select * from test.example where batch_load_date between date(sysdate-30) and date(sysdate);

 

 

PLEASE HELP

Adrian_Oprea_1

1 - Create the table (segmented vs replicated - will decide also the size of the ocupied space)

 

2 - Optimize encodings

SELECT distinct 
    'select DESIGNER_DESIGN_PROJECTION_ENCODINGS ('''|| projection_schema || '.'|| projection_basename||
    ''',''/tmp/'|| projection_schema|| '.'|| projection_basename|| '.sql'','|| '''$depoly true/false$'');'
FROM
    projections
WHERE
 anchor_table_name='$table name$'
 and projection_schema = '$schema name$';

- just execute the optput (also : replace the $ valiables with your tbl name schema, true/false)

 

3 - Get size of the table

SELECT
    anchor_table_name,
    ROUND(SUM(used_bytes)/1024^2,3.0) AS 'Size in Mb'
FROM
    projection_storage 
   where anchor_table_name='$table_name$'
GROUP BY
    1
ORDER BY
    2 DESC

 

 

UJJWAL_RANA

Hi Adrian

My confusion here is where do i define the above mentioned query (sysdate-30)

 

With Regards

 

Ujjwal

Adrian_Oprea_1

i am not sure i understand you question, can you add more to it.

 

UJJWAL_RANA

How can i see the size of the following query---->select * from test.example where batch_load_date between date(sysdate-30) and date(sysdate);

 

again like i wanted to see one months size only which is sysdate -  30

 

Assume like i wanted to see the size of this query ---->select * from test.example where batch_load_date between date(sysdate-30) and date(sysdate);

Adrian_Oprea_1

   I am at point blank hehehehe

 

 

   When you mean size , do you refer to

1 - the size of the data contained in the  where batch_load_date between date(sysdate-30) and date(sysdate); predicate ?

2 - how much memory you are required for this query to run ? 

 

3 - Or maybe you have concerns around you predicate build ? 

I would re write the query like this 

select * from test.example where batch_load_date 
between date_trunc('month', getdate()) and getdate()

-this is becouse not all months have 30 days so you do not want to roll into another month , this will not give you the right results. 

 

So what of those is ? 

 

 

  

 

UJJWAL_RANA

Please find my answer below in line

 

1 - the size of the data contained in the  where batch_load_date between date(sysdate-30) and date(sysdate); predicate ?

 

Answer : Yes. Via etl one of the job runs only on monthly basis and monthly records once in a month. Therefore , I wanted to check size / space occupied by one moths of data

2 - how much memory you are required for this query to run ? 

Answer : There is no issue with memory, again i only need to know what is the query to trigger or to find out the space occupied by 1 months of date as mentioned on your point 1.

 

3 - Or maybe you have concerns around you predicate build ? 

I would re write the query like this 

select * from test.example where batch_load_date 
between date_trunc('month', getdate()) and getdate()

-this is becouse not all months have 30 days so you do not want to roll into another month , this will not give you the right results. 

 

Answer :  My concerns is related with point 1 above onlye. Need to know the query to find the space occupied/disk usage by one months of data

 

 

 

ckotsidimos

You can use select count(1) from... 

to get the number of lines for this query if that is what you mean