Important queries

vsql -U dbadmin -f test200agg_8_6.sql &

SELECT p.PROJECTION_NAME, p.NODE_id,p.IS_UP_TO_DATE, p.HAS_STATISTICS, p.is_super_projection,
ps.USED_BYTES, ps.wos_row_count , ps.ros_row_count ,
ps.wos_used_bytes , ps.ros_used_bytes, ps.ROS_COUNT
FROM tables t
JOIN projections p ON t.table_id = p.anchor_table_id
JOIN projection_storage ps on p.projection_name = ps.projection_name
WHERE t.table_name ilike 'subscription';

drop table if exists test.tt1;
create external table  test.tt1(
col1 int
)
AS COPY SOURCE Hdfs(url='hdfs://sjvqnhdpl010.mcafee.int:50070/user/smohapa1/testfile.txt', username = 'smohapa1',password='smohapa1');


COPY testTable SOURCE Hdfs(url='http://sjvqnhdpl010.mcafee.int:8888/user/smohapa1/testfile.txt',   username='dbadmin');





DROP PROJECTION IF EXISTS sales_p1;
CREATE PROJECTION sales_p1(code,amount,product_id,sales_date)
as
(
select code,amount,product_id,sales_date
from sales
where sales_date>now()-30
) UNSEGMENTED ALL NODES ;
select refresh('sales');
 [Error Code: 5600, SQL State: 42601]  [Vertica][VJDBC](5600) ERROR: Invalid predicate in projection-select. Only PK=FK equijoins are allowed

DROP PROJECTION IF EXISTS sales_p1;

CREATE PROJECTION sales_p1(code,amount,product_id,sales_date)

as

(

select sales_date, count(code) ,sum(amount) 

from sales

group by sales_date

) UNSEGMENTED ALL NODES ;

 ERROR: SEGMENTED BY / UNSEGMENTED is not allowed in aggregate projection. The aggregate projection is automatically segmented on group by columns

Exporting the Sales table script

dbadmin=> select export_tables('/home/dbadmin/scr/sql_tables_empty.sql','sales');

Exporting the Sales table script

dbadmin=> select export_tables('/home/dbadmin/scr/sql_tables_empty.sql','');

Check the table script 

SELECT query_profiles.transaction_id, 
          query_profiles.statement_id, 
          query_profiles.query,
          resource_acquisitions.open_file_handle_count,
          resource_acquisitions.thread_count,
          resource_acquisitions.memory_inuse_kb,
          resource_acquisitions.duration_ms
   FROM query_profiles, resource_acquisitions 
   WHERE (
         resource_acquisitions.transaction_id = query_profiles.transaction_id AND
         resource_acquisitions.statement_id = query_profiles.statement_id
         ) 
         AND 
         (
         query_profiles.transaction_id =  49539595901078494 AND
         query_profiles.statement_id = 1
         );

SELECT transaction_id, statement_id, io_type, projection_name FROM projection_usage ORDER BY query_start_timestamp DESC;