-- Usage: vsql -qf vdraw.sql -- -- Description: -- VSQL is Vertica's character-based front-end utility that lets you type SQL statements and see the results. -- This demo will show how Vertica VSQL alone can visualize query results in color and graphics. -- The idea is to provide dynamic visuals, based only on Vertica VSQL built-in capabilities only. -- All the graph examples below drawn without Bash, Java, Python or any other programming language or BI dashboard tools. -- One exception is the group_concat.cpp UDX from Maurizio Felici used to produce a DB heat-map example. -- In addition, the last word-cloud example produced via pure SQL code followed by a JavaScript presentation for the same words sample. -- -- Version: 1.1 -- Author: Moshe Goldberg --- ------------------------------------------------------------------------ -- To see your terminal ascii characters uncoment the follwoing line --\! for i in {1..1000}; do printf "${i}:\u2${i} " ; done -- The symbol list is shown here: https://en.wikipedia.org/wiki/Dingbat ------------------------------------------------------------------------ DROP SCHEMA vertica_graph_demo CASCADE; CREATE SCHEMA vertica_graph_demo; SET SEARCH_PATH TO vertica_graph_demo; SELECT CURRENT_SCHEMA; ------------------------------------------------------------------------ \pset border 0 -- VSQL with no column delimiter \pset footer -- Toggles VSQL not to display the default footer: (int rows) \set LARGE_V U&'%2705' UESCAPE '''%''' \set SMALL_V U&'%2713' UESCAPE '''%''' \set LARGE_X U&'%2718' UESCAPE '''%''' \set ARROW_RIGHT U&'%2192' UESCAPE '''%''' \set ARROW_DOWN U&'%2193' UESCAPE '''%''' \set Y_AXIS U&'%2563' UESCAPE '''%''' \set X_AXIS0 U&'%2517' UESCAPE '''%''' \set X_AXIS1 U&'%2550' UESCAPE '''%''' \set X_AXIS2 U&'%2550%2564' UESCAPE '''%''' \set BL `echo -e "\e[00m"` -- WHITE on BLACK (default) \set WH `echo -e "\e[47;30m"` -- BLACK ON WHITE \set LB `echo -e "\e[46;30m"` -- LIGHT BLUE background \set PI `echo -e "\e[45;30m"` -- PINK background \set DB `echo -e "\e[44;30m"` -- DARK BLUE background \set BR `echo -e "\e[43;30m"` -- BROWN background \set GR `echo -e "\e[42;30m"` -- GREEN background \set RE `echo -e "\e[41;30m"` -- RED background \set GRonBL `echo -e "\e[40;32m"` -- GREEN font on BLACK background \set YLonBL `echo -e "\e[40;93m"` -- YELLOW font on BLACK background \set REonBL `echo -e "\e[40;31m"` -- RED font on BLACK background \set WHonBL `echo -e "\e[40;38m"` -- WHITE font on BLACK background \set LBonBL `echo -e "\e[40;94m"` -- LIGHT BLUE font on BLACK background \set BAR `printf "\u2591\u2588\u2593\u2592"` \set BAR '''':BAR'''' \set BAR1 :WH ' ' :BL \set BAR2 `printf "\u2588"` \set BAR3 :LB ' ' :BL \set BAR4 :DB ' ' :BL \set BAR5 `echo -e "\e[48;5;20m \e[48;5;21m \e[48;5;20m \e[48;5;19m \e[48;5;18m \e[48;5;17m \e[00m"` -- smooth bar \set FIRSTBAR1 ' ' :BAR1 \set FIRSTBAR3 ' ' :BAR3 \set FIRSTSPACE ' ' \set BAR1 '''':BAR1'''' \set BAR2 '''':BAR2'''' \set BAR3 '''':BAR3'''' \set BAR4 '''':BAR4'''' \set BAR5 '''':BAR5'''' \set FIRSTBAR1 '''':FIRSTBAR1'''' \set FIRSTBAR3 '''':FIRSTBAR3'''' \set FIRSTSPACE '''':FIRSTSPACE'''' \set TITLE :WH :BL RATIO \set TITLE '''':TITLE'''' \set TITLE_N :WH :BL Normalised Ratio \set TITLE_N '''':TITLE_N'''' \set BL '''':BL'''' \set WH '''':WH'''' \set MARK `printf "\u2323"` -- Small \__/ \set MARK '''':MARK'''' \set BU `tput smul` -- Begin underline mode \set EU `tput rmul` -- End underline mode \set BOLD `tput bold` \set UNBOLD `tput sgr0` \set BU '''':BU'''' \set EU '''':EU'''' \set LB '''':LB'''' \set PI '''':PI'''' \set DB '''':DB'''' \set BR '''':BR'''' \set GR '''':GR'''' \set RE '''':RE'''' \set GRonBL '''':GRonBL'''' \set YLonBL '''':YLonBL'''' \set REonBL '''':REonBL'''' \set WHonBL '''':WHonBL'''' -- \set LBonBL '''':LBonBL'''' -- used in echo without showing '' \set UNBOLD '''':UNBOLD'''' \set BOLD '''':BOLD'''' ----------------------------------- -- \set B_WHITE '[47;30m' -- White background, black font -- \set B_L_BLUE '[46;30m' -- Light blue background -- \set B_D_BLUE '[44;30m' -- Dark blue background \set DEFAULT_C '[00m' -- No background, default font color \set B_WHITE '[7;49;39m' -- White background, black font \set B_L_BLUE '[7;107;94m' -- Light blue background \set B_D_BLUE '[7;107;34m' -- Dark blue background \set B_BLACK '[7;107;30m' \set B_D_RED '[7;107;31m' \set B_D_GREEN '[7;107;32m' \set B_BROWN '[7;107;33m' \set B_PURPLE '[7;107;35m' \set B_TURQUOISE '[7;107;36m' \set B_L_GRAY '[7;107;37m' \set B_D_GRAY '[7;107;90m' \set B_L_RED '[7;107;91m' \set B_L_GREEN '[7;107;92m' \set B_YELLOW '[7;100;93m' \set B_PINK '[7;107;95m' \set B_VL_BLUE '[7;100;96m' \set DEFAULT_C '''':DEFAULT_C'''' -- Each valuse wrapped with apostrophe \set B_WHITE '''':B_WHITE'''' \set B_L_BLUE '''':B_L_BLUE'''' \set B_D_BLUE '''':B_D_BLUE'''' \set B_BLACK '''':B_BLACK'''' \set B_D_RED '''':B_D_RED'''' \set B_D_GREEN '''':B_D_GREEN'''' \set B_BROWN '''':B_BROWN'''' \set B_PURPLE '''':B_PURPLE'''' \set B_TURQUOISE '''':B_TURQUOISE'''' \set B_L_GRAY '''':B_L_GRAY'''' \set B_D_GRAY '''':B_D_GRAY'''' \set B_L_RED '''':B_L_RED'''' \set B_L_GREEN '''':B_L_GREEN'''' \set B_YELLOW '''':B_YELLOW'''' \set B_PINK '''':B_PINK'''' \set B_VL_BLUE '''':B_VL_BLUE'''' \echo :BL -- Start demo with black background \echo '################################################################################################################' \echo ' Demo start ' \echo '################################################################################################################' DROP TABLE IF EXISTS colors CASCADE; CREATE TABLE colors (cid int, bold varchar, unbold varchar, mark varchar, white varchar, red varchar, b_dark_blue varchar, b_light_blue varchar, b_d_red varchar, b_d_green varchar, b_brown varchar, b_purple varchar, b_turquoise varchar, b_l_gray varchar, b_d_gray varchar, b_l_red varchar, b_l_green varchar, b_yellow varchar, b_pink varchar, b_vl_blue varchar, green varchar, green_font varchar, red_font varchar, yellow_font varchar, black varchar, begin_underline varchar, end_underline varchar) UNSEGMENTED ALL NODES KSAFE 0; INSERT INTO colors (cid, bold, unbold, mark,white,red,b_dark_blue,b_light_blue,b_d_red, b_d_green, b_brown, b_purple, b_turquoise, b_l_gray, b_d_gray, b_l_red, b_l_green, b_yellow, b_pink, b_vl_blue, green,green_font,red_font,yellow_font,black,begin_underline,end_underline) values (1, :BOLD, :UNBOLD, :MARK, :WH, :RE, :B_D_BLUE, :B_L_BLUE, :B_D_RED, :B_D_GREEN, :B_BROWN, :B_PURPLE, :B_TURQUOISE, :B_L_GRAY, :B_D_GRAY, :B_L_RED, :B_L_GREEN, :B_YELLOW, :B_PINK, :B_VL_BLUE, :GR, :GRonBL, :REonBL, :YLonBL, :BL, :BU, :EU); CREATE LOCAL TEMPORARY TABLE resultset01 (result_version int, barid int, id int, normalised_id int, value varchar) ON COMMIT PRESERVE ROWS KSAFE 0; INSERT INTO resultset01 (result_version,id,value) values (1,0,'Zero'); INSERT INTO resultset01 (result_version,id,value) values (1,1,'One'); INSERT INTO resultset01 (result_version,id,value) values (1,10,'Ten'); INSERT INTO resultset01 (result_version,id,value) values (1,20,'Twenty'); INSERT INTO resultset01 (result_version,id,value) values (1,30,'Thirty'); INSERT INTO resultset01 (result_version,id,value) values (1,40,'Forty'); INSERT INTO resultset01 (result_version,id,value) values (1,50,'Fifty'); INSERT INTO resultset01 (result_version,id,value) values (1,60,'Sixty'); INSERT INTO resultset01 (result_version,id,value) values (1,70,'Seventy'); INSERT INTO resultset01 (result_version,id,value) values (1,80,'Eighty'); INSERT INTO resultset01 (result_version,id,value) values (1,90,'Ninety'); select * from resultset01; select id,INSERT ( INSERT( REPEAT ('_', id) || REPEAT(' ', 100-id),id+1,0,black),1,0,white) as :TITLE ,value FROM resultset01, colors WHERE colors.cid=1 ORDER BY resultset01.id DESC; select id,INSERT ( INSERT( REPEAT ('_',100) ,resultset01.id+1,0,black),1,0,white) as :TITLE ,value FROM resultset01, colors WHERE colors.cid=1 ORDER BY resultset01.id DESC; select id,INSERT ( INSERT( REPEAT ('_', 100),resultset01.id+1,length(value),black || value),1,0,white) as :TITLE ,value FROM resultset01, colors WHERE colors.cid=1 ORDER BY resultset01.id DESC; select id,INSERT ( INSERT( REPEAT ('1234567890', 10),resultset01.id+1,0,black),1,0,white) as :TITLE ,value FROM resultset01, colors WHERE colors.cid=1 ORDER BY resultset01.id DESC; select id, INSERT( INSERT ( OVERLAY( REPEAT(' ', 100) PLACING value FROM 1 for length(value)),id+1,0,black || end_underline),1,0,white || begin_underline) as :TITLE ,value FROM resultset01, colors WHERE colors.cid=1 ORDER BY resultset01.id DESC; \echo :BOLD :YLonBL \echo Graph examples with undefined value range \echo :UNBOLD :BL2 truncate table resultset01; COPY resultset01 (result_version,id,value) from stdin delimiter ',' abort on error; 1,0,Zero 1,100,hundred 1,1000,One thousand 1,10000,Ten thousand 1,15000,fifteen thousand and some other words as bar label 1,20000,Twenty thousand 1,25000,Twenty Five thousand 1,30000,Thirty thousand 1,35000,Thirty five thousand 1,40000,Forty thousand 1,45000,Forty five thousand 1,50000,Fifty thousand 1,55000,Fifty five thousand 1,60000,Sixty thousand 1,65000,Sixty five thousand 1,70000,Seventy thousand 1,75000,Seventy Five thousand 1,80000,Eighty thousand 1,85000,Eighty five thousand 1,90000,Ninety thousand 1,95538,Ninety five thousand five hundreds thirty eight \. \set GRAPH_HIGHT 100 -- setting the available vertical width for undefined value range INSERT INTO resultset01 (result_version,id,normalised_id,value) with maxi as (select max(result_version) as max_result_version, max(id) as max_id from resultset01) select max_result_version +1, id, case when max_id=0 then max_id else (id * (:GRAPH_HIGHT / NULLIFZERO(max_id)))::int end, value from resultset01,maxi where result_version = max_result_version; select * from resultset01; with maxi as (select max(result_version) as max_result_version from resultset01) select id,INSERT ( INSERT( REPEAT ('_', normalised_id) || REPEAT(' ', 100-normalised_id),normalised_id+1,0,black),1,0,green) as :TITLE_N ,value FROM resultset01, colors, maxi WHERE colors.cid=1 and result_version=max_result_version ORDER BY resultset01.id DESC; \echo 'Set variables for the following Yellow echo commands' \set YLonBL `echo -e "\e[40;93m"` -- YELLOW font on BLACK background \set BL2 `echo -e "\e[00m"` -- WHITE on BLACK (default) \set BOLD `tput bold` \set UNBOLD `tput sgr0` ---------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION bar_color (value INT) RETURN VARCHAR AS BEGIN RETURN :BU || CHR(27) || '[48;5;' || CASE WHEN value < 1 THEN '17' WHEN MOD(value,34) = 0 THEN '232' WHEN MOD(value,33) = 0 THEN '233' WHEN MOD(value,32) = 0 THEN '234' WHEN MOD(value,31) = 0 THEN '235' WHEN MOD(value,30) = 0 THEN '236' WHEN MOD(value,29) = 0 THEN '237' WHEN MOD(value,28) = 0 THEN '238' WHEN MOD(value,27) = 0 THEN '239' WHEN MOD(value,26) = 0 THEN '240' WHEN MOD(value,25) = 0 THEN '241' WHEN MOD(value,24) = 0 THEN '242' WHEN MOD(value,23) = 0 THEN '243' WHEN MOD(value,22) = 0 THEN '244' WHEN MOD(value,21) = 0 THEN '245' WHEN MOD(value,20) = 0 THEN '246' WHEN MOD(value,19) = 0 THEN '247' WHEN MOD(value,18) = 0 THEN '248' WHEN MOD(value,17) = 0 THEN '249' WHEN MOD(value,16) = 0 THEN '250' WHEN MOD(value,15) = 0 THEN '251' WHEN MOD(value,14) = 0 THEN '252' WHEN MOD(value,13) = 0 THEN '253' WHEN MOD(value,12) = 0 THEN '117' WHEN MOD(value,11) = 0 THEN '111' WHEN MOD(value,10) = 0 THEN '105' WHEN MOD(value,9) = 0 THEN '99' WHEN MOD(value,8) = 0 THEN '93' WHEN MOD(value,7) = 0 THEN '92' WHEN MOD(value,6) = 0 THEN '91' WHEN MOD(value,5) = 0 THEN '90' WHEN MOD(value,4) = 0 THEN '89' WHEN MOD(value,3) = 0 THEN '88' WHEN MOD(value,2) = 0 THEN '1' ELSE '17' END || 'm' ; END ; \f ' ' \a \t DROP TABLE IF EXISTS fixed_table CASCADE; CREATE TABLE fixed_table -- (result_version int, barid int, id int, normalised_id int, value varchar) AS SELECT * FROM resultset01 UNSEGMENTED ALL NODES KSAFE 0 ; \echo :BOLD :YLonBL \echo 'One method is to create a view..' \echo :UNBOLD :BL2 CREATE OR REPLACE VIEW fixed_table_graph AS with maxi as (select max(result_version) as max_result_version from fixed_table) select to_char(id,'999,999,999,999,999'), INSERT( INSERT( OVERLAY( REPEAT (' ', 100) PLACING to_char(id,'FM999,999,999,999') || ' - ' || value FROM 1 for length(to_char(id,'FM999,999,999,999') || ' - ' || value)) ,normalised_id+1,0,black || end_underline) ,1,0,bar_color(row_number() over())) as my_title, value FROM fixed_table, colors, maxi WHERE colors.cid=1 and result_version=max_result_version ORDER BY fixed_table.id DESC; SELECT * from fixed_table_graph; \echo :BOLD :YLonBL \echo 'Another method is to call sql function only on selected columns..' \echo :UNBOLD :BL2 CREATE OR REPLACE FUNCTION bar_chart (id INT, row_id INT, normalised_id INT, value VARCHAR) RETURN VARCHAR AS BEGIN RETURN INSERT( INSERT( OVERLAY( REPEAT (' ', 100) PLACING to_char(id,'FM999,999,999,999') || ' - ' || value FROM 1 for length(to_char(id,'FM999,999,999,999') || ' - ' || value)) ,normalised_id+1,0,:BL || :EU) ,1,0, :BU || CHR(27) || '[48;5;' || CASE WHEN row_id < 1 THEN '17' WHEN MOD(row_id,34) = 0 THEN '232' WHEN MOD(row_id,33) = 0 THEN '233' WHEN MOD(row_id,32) = 0 THEN '234' WHEN MOD(row_id,31) = 0 THEN '235' WHEN MOD(row_id,30) = 0 THEN '236' WHEN MOD(row_id,29) = 0 THEN '237' WHEN MOD(row_id,28) = 0 THEN '238' WHEN MOD(row_id,27) = 0 THEN '239' WHEN MOD(row_id,26) = 0 THEN '240' WHEN MOD(row_id,25) = 0 THEN '241' WHEN MOD(row_id,24) = 0 THEN '242' WHEN MOD(row_id,23) = 0 THEN '243' WHEN MOD(row_id,22) = 0 THEN '244' WHEN MOD(row_id,21) = 0 THEN '245' WHEN MOD(row_id,20) = 0 THEN '246' WHEN MOD(row_id,19) = 0 THEN '247' WHEN MOD(row_id,18) = 0 THEN '248' WHEN MOD(row_id,17) = 0 THEN '249' WHEN MOD(row_id,16) = 0 THEN '250' WHEN MOD(row_id,15) = 0 THEN '251' WHEN MOD(row_id,14) = 0 THEN '252' WHEN MOD(row_id,13) = 0 THEN '253' WHEN MOD(row_id,12) = 0 THEN '117' WHEN MOD(row_id,11) = 0 THEN '111' WHEN MOD(row_id,10) = 0 THEN '105' WHEN MOD(row_id,9) = 0 THEN '99' WHEN MOD(row_id,8) = 0 THEN '93' WHEN MOD(row_id,7) = 0 THEN '92' WHEN MOD(row_id,6) = 0 THEN '91' WHEN MOD(row_id,5) = 0 THEN '90' WHEN MOD(row_id,4) = 0 THEN '89' WHEN MOD(row_id,3) = 0 THEN '88' WHEN MOD(row_id,2) = 0 THEN '1' ELSE '17' END || 'm' ) ; END ; with maxi as (select max(result_version) as max_result_version from fixed_table) select to_char(id,'999,999,999,999,999'), bar_chart(id, row_number() over(), normalised_id, value ),value FROM fixed_table, maxi WHERE result_version=max_result_version ORDER BY fixed_table.id DESC; \f '|' \a \t ---------------------------------------------------------------------------- \set GRAPH_HIGHT 40 -- setting the available width for undefined value range \set ERASE `tput el1` -- erase to the begining of the line \set GRonBL `echo -e "\e[40;32m"` -- GREEN font on BLACK background \set REonBL `echo -e "\e[40;31m"` -- RED font on BLACK background \set WHonBL `echo -e "\e[40;38m"` -- WHITE font on BLACK background \set YLonBL `echo -e "\e[40;93m"` -- YELLOW font on BLACK background \set BL `echo -e "\e[00m"` -- WHITE on BLACK (default) \set GR `echo -e "\e[42;30m"` -- GREEN \set RE `echo -e "\e[41;30m"` -- RED \set LEGEND ' Legend: ' :RE ' ' :BL '=Uncompressed Bytes, ' :GR ' ' :BL '=Compressed Bytes, ' :GRonBL '+' :BL '/' :REonBL '-' :BL 'Compression Ratio' \set BOLD `tput bold` \set UNBOLD `tput sgr0` \set TITLE :BOLD :YLonBL 'Compression Ratio per Table:' :WHonBL :UNBOLD create local temporary table resultset02 ( result_version int, s_table varchar(80), compressed_bytes int, normalised_compressed_bytes int, uncompressed_Bytes int, normalised_uncompressed_Bytes int, projections_row_count int, compression_ratio numeric, normalised_compression_ratio numeric ) ON COMMIT PRESERVE ROWS KSAFE 0; \echo \echo :BOLD :YLonBL \echo Since the next query queries USER_AUDITS and obtains the latest audits \echo It is advised to audit those tables fast: :GRonBL SELECT AUDIT('','TABLE',5,99); :YLonBL \echo This will AUDIT all the DB, Table granularityi, error‑tolerance=5, confidence‑level=99% \echo :UNBOLD :BL2 INSERT INTO resultset02 (result_version, s_table, compressed_bytes, uncompressed_bytes, projections_row_count, compression_ratio) WITH MAXUA AS (SELECT audited_schema_name || '.' || audited_object_name AS S_TABLE, object_type, MAX(audit_end_timestamp) AS MAXAUDIT FROM v_catalog.user_audits WHERE object_type = 'TABLE' GROUP BY 1,2), PS AS (SELECT anchor_table_schema || '.' || anchor_table_name AS S_TABLE, SUM(used_bytes) AS compressed_bytes, sum(row_count) AS projections_row_count FROM v_monitor.projection_storage GROUP BY 1) SELECT 1 as result_version, UA.audited_schema_name || '.' || UA.audited_object_name AS s_table, PS.compressed_bytes::NUMERIC(20,0) AS compressed_bytes, UA.size_bytes::NUMERIC(20,0) AS uncompressed_bytes, PS.projections_row_count AS projections_row_count, (UA.size_bytes / NULLIFZERO(PS.compressed_bytes))::NUMERIC(10,1) AS compression_ratio FROM v_catalog.user_audits UA, MAXUA, PS WHERE UA.object_type = 'TABLE' AND UA.audited_schema_name || '.' || UA.audited_object_name = MAXUA.S_TABLE AND PS.S_TABLE = MAXUA.S_TABLE AND UA.audit_end_timestamp = MAXUA.MAXAUDIT ORDER BY 2 desc; \echo :BOLD :YLonBL \echo Loading samples for demo... \echo To check a real database - comment the following COPY statment and its sample data: \echo :UNBOLD :BL2 COPY resultset02 (result_version, s_table, compressed_bytes, normalised_compressed_bytes, uncompressed_Bytes, normalised_uncompressed_Bytes, projections_row_count, compression_ratio, normalised_compression_ratio) from stdin delimiter ',' abort on error; 2,store.store_sales_fact,4857088014,,6151650000,,200000000,1.300000000000000, 2,public.orderFact,812,,123,,12,0.200000000000000, 2,GDPR.staging_t,173304640744,,135172000000,,4000000000,0.800000000000000, 2,public.vendor_dimension,51202,,61639,,2000,1.200000000000000, 2,public.shipping_dimension,138065310,,271636000,,20000000,2.000000000000000, 2,public.promotion_dimension,9216,,11026,,200,1.200000000000000, 2,public.employee_dimension,678684,,901495,,20000,1.300000000000000, 2,GDPR.staging_t_with_names,1450515260,,46931000000,,4000000000,32.400000000000000, 2,public.product_dimension,7872778,,9303210,,200000,1.200000000000000, 2,online_sales.online_page_dimension,195930,,599801,,20000,3.100000000000000, 2,store.store_dimension,79670,,89368,,2000,1.100000000000000, 2,public.my_table,0,,0,,0,, 2,public.high_comp_t,0,,0,,0,, 2,public.customer_dimension,86582132,,111396600,,2000000,1.300000000000000, 2,public.warehouse_dimension,37232,,42453,,2000,1.100000000000000, 2,public.low_comp_t,0,,0,,0,, 2,public.iot,6069885,,58719324,,5760750,9.700000000000000, 2,GDPR.staging_t_with_duplicates,1453159610,,139512000000,,4000000000,96.000000000000000, 2,GDPR.fact_t,296097109099,,166432033286,,4000000800,0.600000000000000, 2,public.inventory_fact,14611984,,14386000,,2000000,1.000000000000000, 2,online_sales.online_sales_fact,76745896,,65831400,,2000000,0.900000000000000, 2,store.store_orders_fact,6424619110,,9325390000,,200000000,1.500000000000000, 2,public.date_dimension,50890,,109529,,2192,2.200000000000000, 2,online_sales.call_center_dimension,4890880,,9165480,,200000,1.900000000000000, 2,DEMO_DUP.stage_table,464,,117,,16,0.300000000000000, 2,public.custDim,512,,89,,10,0.200000000000000, \. INSERT INTO resultset02 (result_version, s_table, compressed_bytes, normalised_compressed_bytes, uncompressed_bytes, normalised_uncompressed_bytes, projections_row_count, compression_ratio, normalised_compression_ratio) with maxi as (select max(result_version) as max_result_version, max(compressed_bytes) as max_compressed_bytes, max(uncompressed_bytes) as max_uncompressed_bytes from resultset02) select max_result_version +1, s_table, compressed_bytes, case when max_compressed_bytes=0 then max_compressed_bytes else CEILING(compressed_bytes * (:GRAPH_HIGHT / NULLIFZERO(max_compressed_bytes))) end, uncompressed_bytes, case when max_uncompressed_bytes=0 then max_uncompressed_bytes else CEILING(uncompressed_bytes * (:GRAPH_HIGHT / NULLIFZERO(max_uncompressed_bytes))) end, projections_row_count, compression_ratio, case when compressed_bytes > uncompressed_bytes then -1*compressed_bytes / NULLIFZERO(uncompressed_bytes) else uncompressed_bytes / NULLIFZERO(compressed_bytes) end as normalised_compression_ratio from resultset02,maxi where result_version = max_result_version; \echo :TITLE \echo :LEGEND with maxi as (select max(result_version) as max_result_version from resultset02) select s_table, (uncompressed_bytes / (1024^3))::NUMERIC(20,1) AS 'Uncompressed GB', (compressed_bytes / (1024^3))::NUMERIC(20,1) AS 'Compressed GB', (projections_row_count / 1000)::numeric(20,0) || ' k' AS 'Projections Row Count', case when uncompressed_bytes > compressed_bytes then green || REPEAT ('_', normalised_compressed_bytes) || red || REPEAT ('_', normalised_uncompressed_bytes - normalised_compressed_bytes) || green_font || ' ' || normalised_compression_ratio::numeric(5,1) || black else red || REPEAT ('_', normalised_uncompressed_bytes) || green || REPEAT ('_', normalised_compressed_bytes - normalised_uncompressed_bytes) || red_font || normalised_compression_ratio::numeric(5,1) || black end as ' ' FROM resultset02, colors, maxi WHERE colors.cid=1 and result_version=max_result_version ORDER BY resultset02.uncompressed_bytes DESC; \echo :BOLD :YLonBL \echo VERTICAL GRAPHS examples \echo :UNBOLD :BL2 \set GRAPH_HIGHT 28 INSERT INTO resultset01 (result_version,barid,id,normalised_id,value) with maxi as (select max(result_version) as max_result_version, max(id) as max_id from resultset01) select max_result_version +1, row_number() over() as barid, id, case when max_id=0 then max_id else (id * (:GRAPH_HIGHT / NULLIFZERO(max_id)))::int end, value from resultset01,maxi where result_version = max_result_version; -- select * from resultset01; \echo :BOLD :YLonBL \echo Bottom Bars labels: \echo :UNBOLD :BL2 WITH BARST AS (SELECT SUM(CASE WHEN barid=1 THEN normalised_id ELSE 0 END) AS barid_1, SUM(CASE WHEN barid=1 THEN id ELSE 0 END) AS id_1, SUM(CASE WHEN barid=2 THEN normalised_id ELSE 0 END) AS barid_2, SUM(CASE WHEN barid=2 THEN id ELSE 0 END) AS id_2, SUM(CASE WHEN barid=3 THEN normalised_id ELSE 0 END) AS barid_3, SUM(CASE WHEN barid=3 THEN id ELSE 0 END) AS id_3, SUM(CASE WHEN barid=4 THEN normalised_id ELSE 0 END) AS barid_4, SUM(CASE WHEN barid=4 THEN id ELSE 0 END) AS id_4, SUM(CASE WHEN barid=5 THEN normalised_id ELSE 0 END) AS barid_5, SUM(CASE WHEN barid=5 THEN id ELSE 0 END) AS id_5, SUM(CASE WHEN barid=6 THEN normalised_id ELSE 0 END) AS barid_6, SUM(CASE WHEN barid=6 THEN id ELSE 0 END) AS id_6, SUM(CASE WHEN barid=7 THEN normalised_id ELSE 0 END) AS barid_7, SUM(CASE WHEN barid=7 THEN id ELSE 0 END) AS id_7, SUM(CASE WHEN barid=8 THEN normalised_id ELSE 0 END) AS barid_8, SUM(CASE WHEN barid=8 THEN id ELSE 0 END) AS id_8, SUM(CASE WHEN barid=9 THEN normalised_id ELSE 0 END) AS barid_9, SUM(CASE WHEN barid=9 THEN id ELSE 0 END) AS id_9, SUM(CASE WHEN barid=10 THEN normalised_id ELSE 0 END) AS barid_10, SUM(CASE WHEN barid=10 THEN id ELSE 0 END) AS id_10, SUM(CASE WHEN barid=11 THEN normalised_id ELSE 0 END) AS barid_11, SUM(CASE WHEN barid=11 THEN id ELSE 0 END) AS id_11 FROM resultset01), MYROWS as (select row_number() over() -2 as rid from ( select 1 from ( select now() as se union all select now() + :GRAPH_HIGHT as se) a timeseries ts as '1 day' over (order by se)) b) select CASE WHEN rid = -1 THEN to_char(id_1,'FM99,999') WHEN barid_1 > rid THEN :BAR ELSE ' ' END AS id_1, CASE WHEN rid = -1 THEN to_char(id_2,'FM99,999') WHEN barid_2 > rid THEN :BAR ELSE ' ' END AS id_2, CASE WHEN rid = -1 THEN to_char(id_3,'FM99,999') WHEN barid_3 > rid THEN :BAR ELSE ' ' END AS id_3, CASE WHEN rid = -1 THEN to_char(id_4,'FM99,999') WHEN barid_4 > rid THEN :BAR ELSE ' ' END AS id_4, CASE WHEN rid = -1 THEN to_char(id_5,'FM99,999') WHEN barid_5 > rid THEN :BAR ELSE ' ' END AS id_5, CASE WHEN rid = -1 THEN to_char(id_6,'FM99,999') WHEN barid_6 > rid THEN :BAR ELSE ' ' END AS id_6, CASE WHEN rid = -1 THEN to_char(id_7,'FM99,999') WHEN barid_7 > rid THEN :BAR ELSE ' ' END AS id_7, CASE WHEN rid = -1 THEN to_char(id_8,'FM99,999') WHEN barid_8 > rid THEN :BAR ELSE ' ' END AS id_8, CASE WHEN rid = -1 THEN to_char(id_9,'FM99,999') WHEN barid_9 > rid THEN :BAR ELSE ' ' END AS id_9, CASE WHEN rid = -1 THEN to_char(id_10,'FM99,999') WHEN barid_10 > rid THEN :BAR ELSE ' ' END AS id_10, CASE WHEN rid = -1 THEN to_char(id_11,'FM99,999') WHEN barid_11 > rid THEN :BAR ELSE ' ' END AS id_11 from BARST, MYROWS ORDER BY MYROWS.rid DESC; \echo :BOLD :YLonBL \echo Top Bar Labels: \echo :UNBOLD :BL2 WITH BARST AS (SELECT SUM(CASE WHEN barid=1 THEN normalised_id ELSE 0 END) AS barid_1, SUM(CASE WHEN barid=1 THEN id ELSE 0 END) AS id_1, SUM(CASE WHEN barid=2 THEN normalised_id ELSE 0 END) AS barid_2, SUM(CASE WHEN barid=2 THEN id ELSE 0 END) AS id_2, SUM(CASE WHEN barid=3 THEN normalised_id ELSE 0 END) AS barid_3, SUM(CASE WHEN barid=3 THEN id ELSE 0 END) AS id_3, SUM(CASE WHEN barid=4 THEN normalised_id ELSE 0 END) AS barid_4, SUM(CASE WHEN barid=4 THEN id ELSE 0 END) AS id_4, SUM(CASE WHEN barid=5 THEN normalised_id ELSE 0 END) AS barid_5, SUM(CASE WHEN barid=5 THEN id ELSE 0 END) AS id_5, SUM(CASE WHEN barid=6 THEN normalised_id ELSE 0 END) AS barid_6, SUM(CASE WHEN barid=6 THEN id ELSE 0 END) AS id_6, SUM(CASE WHEN barid=7 THEN normalised_id ELSE 0 END) AS barid_7, SUM(CASE WHEN barid=7 THEN id ELSE 0 END) AS id_7, SUM(CASE WHEN barid=8 THEN normalised_id ELSE 0 END) AS barid_8, SUM(CASE WHEN barid=8 THEN id ELSE 0 END) AS id_8, SUM(CASE WHEN barid=9 THEN normalised_id ELSE 0 END) AS barid_9, SUM(CASE WHEN barid=9 THEN id ELSE 0 END) AS id_9, SUM(CASE WHEN barid=10 THEN normalised_id ELSE 0 END) AS barid_10, SUM(CASE WHEN barid=10 THEN id ELSE 0 END) AS id_10, SUM(CASE WHEN barid=11 THEN normalised_id ELSE 0 END) AS barid_11, SUM(CASE WHEN barid=11 THEN id ELSE 0 END) AS id_11 FROM resultset01), MYROWS as (select row_number() over() -1 as rid from ( select 1 from ( select now() as se union all select now() + :GRAPH_HIGHT as se) a timeseries ts as '1 day' over (order by se)) b) select CASE WHEN barid_1 > rid THEN :BAR WHEN barid_1 = rid THEN to_char(id_1,'99,999') ELSE ' ' END AS id_1, CASE WHEN barid_2 > rid THEN :BAR WHEN barid_2 = rid THEN to_char(id_2,'99,999') ELSE ' ' END AS id_2, CASE WHEN barid_3 > rid THEN :BAR WHEN barid_3 = rid THEN to_char(id_3,'99,999') ELSE ' ' END AS id_3, CASE WHEN barid_4 > rid THEN :BAR WHEN barid_4 = rid THEN to_char(id_4,'99,999') ELSE ' ' END AS id_4, CASE WHEN barid_5 > rid THEN :BAR WHEN barid_5 = rid THEN to_char(id_5,'99,999') ELSE ' ' END AS id_5, CASE WHEN barid_6 > rid THEN :BAR WHEN barid_6 = rid THEN to_char(id_6,'99,999') ELSE ' ' END AS id_6, CASE WHEN barid_7 > rid THEN :BAR WHEN barid_7 = rid THEN to_char(id_7,'99,999') ELSE ' ' END AS id_7, CASE WHEN barid_8 > rid THEN :BAR WHEN barid_8 = rid THEN to_char(id_8,'99,999') ELSE ' ' END AS id_8, CASE WHEN barid_9 > rid THEN :BAR WHEN barid_9 = rid THEN to_char(id_9,'99,999') ELSE ' ' END AS id_9, CASE WHEN barid_10 > rid THEN :BAR WHEN barid_10 = rid THEN to_char(id_10,'99,999') ELSE ' ' END AS id_10, CASE WHEN barid_11 > rid THEN :BAR WHEN barid_11 = rid THEN to_char(id_11,'99,999') ELSE ' ' END AS id_11 from BARST, MYROWS ORDER BY MYROWS.rid DESC; \echo :BOLD :YLonBL \echo Top Bar Labels other bars: \echo :UNBOLD :BL2 \pset border 0 WITH BARST AS (SELECT SUM(CASE WHEN barid=1 THEN normalised_id ELSE 0 END) AS barid_1, SUM(CASE WHEN barid=1 THEN id ELSE 0 END) AS id_1, SUM(CASE WHEN barid=2 THEN normalised_id ELSE 0 END) AS barid_2, SUM(CASE WHEN barid=2 THEN id ELSE 0 END) AS id_2, SUM(CASE WHEN barid=3 THEN normalised_id ELSE 0 END) AS barid_3, SUM(CASE WHEN barid=3 THEN id ELSE 0 END) AS id_3, SUM(CASE WHEN barid=4 THEN normalised_id ELSE 0 END) AS barid_4, SUM(CASE WHEN barid=4 THEN id ELSE 0 END) AS id_4, SUM(CASE WHEN barid=5 THEN normalised_id ELSE 0 END) AS barid_5, SUM(CASE WHEN barid=5 THEN id ELSE 0 END) AS id_5, SUM(CASE WHEN barid=6 THEN normalised_id ELSE 0 END) AS barid_6, SUM(CASE WHEN barid=6 THEN id ELSE 0 END) AS id_6, SUM(CASE WHEN barid=7 THEN normalised_id ELSE 0 END) AS barid_7, SUM(CASE WHEN barid=7 THEN id ELSE 0 END) AS id_7, SUM(CASE WHEN barid=8 THEN normalised_id ELSE 0 END) AS barid_8, SUM(CASE WHEN barid=8 THEN id ELSE 0 END) AS id_8, SUM(CASE WHEN barid=9 THEN normalised_id ELSE 0 END) AS barid_9, SUM(CASE WHEN barid=9 THEN id ELSE 0 END) AS id_9, SUM(CASE WHEN barid=10 THEN normalised_id ELSE 0 END) AS barid_10, SUM(CASE WHEN barid=10 THEN id ELSE 0 END) AS id_10, SUM(CASE WHEN barid=11 THEN normalised_id ELSE 0 END) AS barid_11, SUM(CASE WHEN barid=11 THEN id ELSE 0 END) AS id_11 FROM resultset01), MYROWS as (select row_number() over() -1 as rid from ( select 1 from ( select now() as se union all select now() + :GRAPH_HIGHT as se) a timeseries ts as '1 day' over (order by se)) b) select CASE WHEN barid_1 > rid THEN :BAR2 WHEN barid_1 = rid THEN to_char(id_1,'FM99,999') ELSE ' ' END AS id_1, CASE WHEN barid_2 > rid THEN :BAR2 WHEN barid_2 = rid THEN to_char(id_2,'FM99,999') ELSE ' ' END AS id_2, CASE WHEN barid_3 > rid THEN :BAR2 WHEN barid_3 = rid THEN to_char(id_3,'FM99,999') ELSE ' ' END AS id_3, CASE WHEN barid_4 > rid THEN :BAR2 WHEN barid_4 = rid THEN to_char(id_4,'FM99,999') ELSE ' ' END AS id_4, CASE WHEN barid_5 > rid THEN :BAR2 WHEN barid_5 = rid THEN to_char(id_5,'FM99,999') ELSE ' ' END AS id_5, CASE WHEN barid_6 > rid THEN :BAR2 WHEN barid_6 = rid THEN to_char(id_6,'FM99,999') ELSE ' ' END AS id_6, CASE WHEN barid_7 > rid THEN :BAR2 WHEN barid_7 = rid THEN to_char(id_7,'FM99,999') ELSE ' ' END AS id_7, CASE WHEN barid_8 > rid THEN :BAR2 WHEN barid_8 = rid THEN to_char(id_8,'FM99,999') ELSE ' ' END AS id_8, CASE WHEN barid_9 > rid THEN :BAR2 WHEN barid_9 = rid THEN to_char(id_9,'FM99,999') ELSE ' ' END AS id_9, CASE WHEN barid_10 > rid THEN :BAR2 WHEN barid_10 = rid THEN to_char(id_10,'FM99,999') ELSE ' ' END AS id_10, CASE WHEN barid_11 > rid THEN :BAR2 WHEN barid_11 = rid THEN to_char(id_11,'FM99,999') ELSE ' ' END AS id_11 from BARST, MYROWS ORDER BY MYROWS.rid DESC; \f ' ' \a \t \echo :BOLD :YLonBL \echo Smooth Bars with bottom Labels: :UNBOLD :BL2 \pset border 0 WITH BARST AS (SELECT SUM(CASE WHEN barid=1 THEN normalised_id ELSE 0 END) AS barid_1, SUM(CASE WHEN barid=1 THEN id ELSE 0 END) AS id_1, SUM(CASE WHEN barid=2 THEN normalised_id ELSE 0 END) AS barid_2, SUM(CASE WHEN barid=2 THEN id ELSE 0 END) AS id_2, SUM(CASE WHEN barid=3 THEN normalised_id ELSE 0 END) AS barid_3, SUM(CASE WHEN barid=3 THEN id ELSE 0 END) AS id_3, SUM(CASE WHEN barid=4 THEN normalised_id ELSE 0 END) AS barid_4, SUM(CASE WHEN barid=4 THEN id ELSE 0 END) AS id_4, SUM(CASE WHEN barid=5 THEN normalised_id ELSE 0 END) AS barid_5, SUM(CASE WHEN barid=5 THEN id ELSE 0 END) AS id_5, SUM(CASE WHEN barid=6 THEN normalised_id ELSE 0 END) AS barid_6, SUM(CASE WHEN barid=6 THEN id ELSE 0 END) AS id_6, SUM(CASE WHEN barid=7 THEN normalised_id ELSE 0 END) AS barid_7, SUM(CASE WHEN barid=7 THEN id ELSE 0 END) AS id_7, SUM(CASE WHEN barid=8 THEN normalised_id ELSE 0 END) AS barid_8, SUM(CASE WHEN barid=8 THEN id ELSE 0 END) AS id_8, SUM(CASE WHEN barid=9 THEN normalised_id ELSE 0 END) AS barid_9, SUM(CASE WHEN barid=9 THEN id ELSE 0 END) AS id_9, SUM(CASE WHEN barid=10 THEN normalised_id ELSE 0 END) AS barid_10, SUM(CASE WHEN barid=10 THEN id ELSE 0 END) AS id_10, SUM(CASE WHEN barid=11 THEN normalised_id ELSE 0 END) AS barid_11, SUM(CASE WHEN barid=11 THEN id ELSE 0 END) AS id_11 FROM resultset01), MYROWS as (select row_number() over() -3 as rid from ( select 1 from ( select now() as se union all select now() + :GRAPH_HIGHT as se) a timeseries ts as '1 day' over (order by se)) b) select CASE WHEN rid = -1 THEN CHR(27) || b_light_blue || to_char(id_1,'99999') || black WHEN rid = -2 THEN 'Bar 01' WHEN barid_1 > rid THEN :BAR5 ELSE ' ' END AS id_1, CASE WHEN rid = -1 THEN CHR(27) || b_light_blue || to_char(id_2,'99999') || black WHEN rid = -2 THEN 'Bar 02' WHEN barid_2 > rid THEN :BAR5 ELSE ' ' END AS id_2, CASE WHEN rid = -1 THEN CHR(27) || b_light_blue || to_char(id_3,'99999') || black WHEN rid = -2 THEN 'Bar 03' WHEN barid_3 > rid THEN :BAR5 ELSE ' ' END AS id_3, CASE WHEN rid = -1 THEN CHR(27) || b_light_blue || to_char(id_4,'99999') || black WHEN rid = -2 THEN 'Bar 04' WHEN barid_4 > rid THEN :BAR5 ELSE ' ' END AS id_4, CASE WHEN rid = -1 THEN CHR(27) || b_light_blue || to_char(id_5,'99999') || black WHEN rid = -2 THEN 'Bar 05' WHEN barid_5 > rid THEN :BAR5 ELSE ' ' END AS id_5, CASE WHEN rid = -1 THEN CHR(27) || b_light_blue || to_char(id_6,'99999') || black WHEN rid = -2 THEN 'Bar 06' WHEN barid_6 > rid THEN :BAR5 ELSE ' ' END AS id_6, CASE WHEN rid = -1 THEN CHR(27) || b_light_blue || to_char(id_7,'99999') || black WHEN rid = -2 THEN 'Bar 07' WHEN barid_7 > rid THEN :BAR5 ELSE ' ' END AS id_7, CASE WHEN rid = -1 THEN CHR(27) || b_light_blue || to_char(id_8,'99999') || black WHEN rid = -2 THEN 'Bar 08' WHEN barid_8 > rid THEN :BAR5 ELSE ' ' END AS id_8, CASE WHEN rid = -1 THEN CHR(27) || b_light_blue || to_char(id_9,'99999') || black WHEN rid = -2 THEN 'Bar 09' WHEN barid_9 > rid THEN :BAR5 ELSE ' ' END AS id_9, CASE WHEN rid = -1 THEN CHR(27) || b_light_blue || to_char(id_10,'99999') || black WHEN rid = -2 THEN 'Bar 10' WHEN barid_10 > rid THEN :BAR5 ELSE ' ' END AS id_10, CASE WHEN rid = -1 THEN CHR(27) || b_light_blue || to_char(id_11,'99999') || black WHEN rid = -2 THEN 'Bar 11' WHEN barid_11 > rid THEN :BAR5 ELSE ' ' END AS id_11 FROM BARST, MYROWS, colors WHERE colors.cid=1 ORDER BY MYROWS.rid DESC; \f '|' \a \t ---------------------------------------------------------------------------------------------- \echo :BOLD :YLonBL \echo '------------------------' \echo 'ROSes count as bar chart' \echo '------------------------' :UNBOLD :BL2 CREATE LOCAL TEMPORARY TABLE ros_count_result_set ( result_version int, -- ros_count_result_set table result set version barid int, node_name varchar(128), schema_name varchar(128), projection_name varchar(128), container_count int, normalised_cc int, group_barid int, maxp int -- max container_count PARTITION BY schema_name || projection_name ) ON COMMIT PRESERVE ROWS KSAFE 0; INSERT INTO ros_count_result_set (result_version,barid,node_name,schema_name,projection_name,container_count) SELECT 1 as result_version, 1 as barid, node_name, schema_name, projection_name, sum(delete_vector_count/NULLIFZERO(cnt))::int + count(*) as container_count FROM storage_containers JOIN (select projection_id , count(*) cnt from projection_columns group by 1) as proj_cols on storage_containers.projection_id = proj_cols.projection_id WHERE storage_type = 'ROS' GROUP BY 3,4,5 ORDER BY 6 DESC,3,4,5 limit 20; -- select * from ros_count_result_set order by container_count desc; \echo :BOLD :YLonBL \echo Loading samples for demo... \echo To check a real database - comment the following COPY statment and its sample data: \echo :UNBOLD :BL2 COPY ros_count_result_set (result_version,barid,node_name,schema_name,projection_name,container_count) from stdin delimiter ',' abort on error; 2,1,v_vdb_node0001,GDPR,fact_t_b0,22 2,1,v_vdb_node0002,GDPR,fact_t_b0,32 2,1,v_vdb_node0003,GDPR,fact_t_b0,22 2,1,v_vdb_node0004,GDPR,fact_t_b0,22 2,1,v_vdb_node0001,GDPR,fact_t_b1,22 2,1,v_vdb_node0002,GDPR,fact_t_b1,30 2,1,v_vdb_node0003,GDPR,fact_t_b1,22 2,1,v_vdb_node0004,GDPR,fact_t_b1,22 2,1,v_vdb_node0001,GDPR,staging_t_b0,81 2,1,v_vdb_node0002,GDPR,staging_t_b0,81 2,1,v_vdb_node0003,GDPR,staging_t_b0,90 2,1,v_vdb_node0004,GDPR,staging_t_b0,90 2,1,v_vdb_node0001,GDPR,staging_t_b1,81 2,1,v_vdb_node0002,GDPR,staging_t_b1,82 2,1,v_vdb_node0003,GDPR,staging_t_b1,89 2,1,v_vdb_node0004,GDPR,staging_t_b1,90 2,1,v_vdb_node0001,store,store_orders_01_PROJ_b0,8 2,1,v_vdb_node0002,store,store_orders_01_PROJ_b0,7 2,1,v_vdb_node0003,store,store_orders_01_PROJ_b0,8 2,1,v_vdb_node0004,store,store_orders_01_PROJ_b0,9 2,1,v_vdb_node0001,store,store_orders_02_PROJ_b1,8 2,1,v_vdb_node0002,store,store_orders_02_PROJ_b1,0 2,1,v_vdb_node0003,store,store_orders_02_PROJ_b1,8 2,1,v_vdb_node0004,store,store_orders_02_PROJ_b1,9 2,1,v_vdb_node0001,GDPR,staging_t_with_duplicates_b0,2 2,1,v_vdb_node0002,GDPR,staging_t_with_duplicates_b0,2 2,1,v_vdb_node0003,GDPR,staging_t_with_duplicates_b0,4 2,1,v_vdb_node0004,GDPR,staging_t_with_duplicates_b0,3 2,1,v_vdb_node0001,GDPR,staging_t_with_duplicates_b1,2 2,1,v_vdb_node0002,GDPR,staging_t_with_duplicates_b1,4 \. ------------------------------------------ ------------------------------------------ INSERT INTO ros_count_result_set (result_version, barid, container_count, maxp, group_barid, normalised_cc, node_name, schema_name, projection_name) with maxi as (select max(result_version) as max_result_version, max(container_count) as max_id from ros_count_result_set) select max_result_version +1, row_number() over(ORDER BY projection_name,schema_name,node_name) as barid, container_count, max(container_count) OVER (PARTITION BY schema_name || projection_name) AS maxp, row_number() over(PARTITION BY schema_name || projection_name order by node_name) as group_barid, case when max_id=0 then max_id else (container_count * (:GRAPH_HIGHT / NULLIFZERO(max_id)))::int end, node_name, schema_name, projection_name from ros_count_result_set, maxi where result_version = max_result_version ORDER BY maxp desc,group_barid; select * from ros_count_result_set where result_version = (select max(result_version) from ros_count_result_set) order by barid; select * from ros_count_result_set where result_version = (select max(result_version) from ros_count_result_set) order by maxp desc,group_barid; \echo :BOLD :YLonBL \echo First look with Bottom labels: :UNBOLD :BL2 WITH MAXI AS (select max(result_version) as max_result_version from ros_count_result_set), BARST AS (SELECT SUM(CASE WHEN barid=1 THEN normalised_cc ELSE 0 END) AS barid_1, SUM(CASE WHEN barid=1 THEN container_count ELSE 0 END) AS id_1, SUM(CASE WHEN barid=2 THEN normalised_cc ELSE 0 END) AS barid_2, SUM(CASE WHEN barid=2 THEN container_count ELSE 0 END) AS id_2, SUM(CASE WHEN barid=3 THEN normalised_cc ELSE 0 END) AS barid_3, SUM(CASE WHEN barid=3 THEN container_count ELSE 0 END) AS id_3, SUM(CASE WHEN barid=4 THEN normalised_cc ELSE 0 END) AS barid_4, SUM(CASE WHEN barid=4 THEN container_count ELSE 0 END) AS id_4, SUM(CASE WHEN barid=5 THEN normalised_cc ELSE 0 END) AS barid_5, SUM(CASE WHEN barid=5 THEN container_count ELSE 0 END) AS id_5, SUM(CASE WHEN barid=6 THEN normalised_cc ELSE 0 END) AS barid_6, SUM(CASE WHEN barid=6 THEN container_count ELSE 0 END) AS id_6, SUM(CASE WHEN barid=7 THEN normalised_cc ELSE 0 END) AS barid_7, SUM(CASE WHEN barid=7 THEN container_count ELSE 0 END) AS id_7, SUM(CASE WHEN barid=8 THEN normalised_cc ELSE 0 END) AS barid_8, SUM(CASE WHEN barid=8 THEN container_count ELSE 0 END) AS id_8, SUM(CASE WHEN barid=9 THEN normalised_cc ELSE 0 END) AS barid_9, SUM(CASE WHEN barid=9 THEN container_count ELSE 0 END) AS id_9, SUM(CASE WHEN barid=10 THEN normalised_cc ELSE 0 END) AS barid_10, SUM(CASE WHEN barid=10 THEN container_count ELSE 0 END) AS id_10, SUM(CASE WHEN barid=11 THEN normalised_cc ELSE 0 END) AS barid_11, SUM(CASE WHEN barid=11 THEN container_count ELSE 0 END) AS id_11 FROM ros_count_result_set, MAXI WHERE result_version = max_result_version), MYROWS as (select row_number() over() -2 as rid from ( select 1 from ( select now() as se union all select now() + :GRAPH_HIGHT as se) a timeseries ts as '1 day' over (order by se)) b) -- debug -- select barid_1, id_1, barid_2, id_2, barid_3, id_3, barid_4, id_4, barid_5, id_5, barid_6, id_6, barid_7, id_7, barid_8, id_8, barid_9, id_9, barid_10, id_10, barid_11, id_11, MYROWS.rid select CASE WHEN rid = -1 THEN to_char(id_1,'FM99,999') WHEN barid_1 > rid THEN :BAR ELSE ' ' END AS id_1, CASE WHEN rid = -1 THEN to_char(id_2,'FM99,999') WHEN barid_2 > rid THEN :BAR ELSE ' ' END AS id_2, CASE WHEN rid = -1 THEN to_char(id_3,'FM99,999') WHEN barid_3 > rid THEN :BAR ELSE ' ' END AS id_3, CASE WHEN rid = -1 THEN to_char(id_4,'FM99,999') WHEN barid_4 > rid THEN :BAR ELSE ' ' END AS id_4, CASE WHEN rid = -1 THEN to_char(id_5,'FM99,999') WHEN barid_5 > rid THEN :BAR ELSE ' ' END AS id_5, CASE WHEN rid = -1 THEN to_char(id_6,'FM99,999') WHEN barid_6 > rid THEN :BAR ELSE ' ' END AS id_6, CASE WHEN rid = -1 THEN to_char(id_7,'FM99,999') WHEN barid_7 > rid THEN :BAR ELSE ' ' END AS id_7, CASE WHEN rid = -1 THEN to_char(id_8,'FM99,999') WHEN barid_8 > rid THEN :BAR ELSE ' ' END AS id_8, CASE WHEN rid = -1 THEN to_char(id_9,'FM99,999') WHEN barid_9 > rid THEN :BAR ELSE ' ' END AS id_9, CASE WHEN rid = -1 THEN to_char(id_10,'FM99,999') WHEN barid_10 > rid THEN :BAR ELSE ' ' END AS id_10, CASE WHEN rid = -1 THEN to_char(id_11,'FM99,999') WHEN barid_11 > rid THEN :BAR ELSE ' ' END AS id_11 from BARST, MYROWS ORDER BY MYROWS.rid DESC; ------------------------------------------ ------------------------------------------ \echo \echo :BOLD :YLonBL \echo '---------------------------------------------------------------------------------------------------------------------' \echo TAILOR-MADE BAR CHART: Changing bars order and group the bars to compare between same projections on different nodes: \echo '---------------------------------------------------------------------------------------------------------------------' \echo :UNBOLD :BL2 CREATE OR REPLACE FUNCTION bar ( legend INT ) RETURN VARCHAR AS BEGIN RETURN CHR(27) || CASE WHEN MOD(legend,10) = 0 THEN :B_L_GREEN WHEN MOD(legend,9) = 0 THEN :B_D_GREEN WHEN MOD(legend,8) = 0 THEN :B_PURPLE WHEN MOD(legend,7) = 0 THEN :B_PINK WHEN MOD(legend,6) = 0 THEN :B_L_RED WHEN MOD(legend,5) = 0 THEN :B_D_RED WHEN MOD(legend,4) = 0 THEN :B_L_BLUE WHEN MOD(legend,3) = 0 THEN :B_TURQUOISE WHEN MOD(legend,2) = 0 THEN :B_D_BLUE ELSE :B_YELLOW END || ' ' || CHR(27) || :DEFAULT_C ; END ; CREATE OR REPLACE FUNCTION bar_legend ( barid INT, groupid INT ) -- groupid for future use RETURN VARCHAR AS BEGIN RETURN CHR(27) || :B_D_GRAY || to_char(barid,'FM09') || CHR(27) || :DEFAULT_C ; END ; CREATE OR REPLACE FUNCTION draw_xy_axis (bar_num INT, max_container_count INT, rid INT, group_id INT, barid INT) -- PURPOSE: draw y axis and group all bars per projection -- USAGE: draw_xy_axis(max_container_count,rid) RETURN VARCHAR AS BEGIN RETURN CASE WHEN bar_num = 1 and rid > -1 THEN REPEAT(' ',1) || TO_CHAR((max_container_count / :GRAPH_HIGHT) * rid +1, '999,999,999') || ' ' || :Y_AXIS || ' ' -- y-axis WHEN bar_num = 1 and (rid = 0 or rid = -2) THEN REPEAT(' ',17) -- leading spaces at rows 0 and -2 WHEN bar_num = 1 and rid = -1 THEN REPEAT(' ',14) || :X_AXIS0 || REPEAT(:X_AXIS1,1) -- leading spaces and x-y axis connection at row -1 --WHEN bar_num > 0 and rid = -1 THEN :X_AXIS2 -- x-axis line: two characters in a T shape WHEN group_id = 1 and rid > -1 THEN ' ' -- one space between each bar group WHEN group_id = 1 and rid = -2 THEN ' ' -- :X_AXIS1 -- one ' ' between each x-axis bar group labels ELSE '' END || CASE WHEN group_id = 1 and rid = -1 THEN :X_AXIS1 -- one '=' between each x-axis bar group labels ELSE '' END || CASE WHEN bar_num > 0 and rid = -1 THEN :X_AXIS2 -- x-axis line: two characters in a T shape ELSE '' END || CASE WHEN rid = -2 THEN CHR(27) || :B_D_GRAY || to_char(bar_num,'FM09') || CHR(27) || :DEFAULT_C -- X-axis labels ELSE '' END || CASE WHEN barid > rid and rid > -1 THEN bar(group_id) -- draw the bar or two spaces as bar filler WHEN barid <= rid and rid > -1 THEN ' ' ELSE '' END; END ; \t \a \f '' \set GRAPH_HIGHT 38 with max_res as (select max(result_version) as max_result_ver from ros_count_result_set), max_ros as (select max(container_count) as max_container_count from ros_count_result_set,max_res where max_result_ver = result_version), orig_result as (select result_version, group_barid, node_name, schema_name, projection_name, container_count as id, maxp, normalised_cc from ros_count_result_set, max_res where result_version = max_result_ver order by maxp desc, schema_name,projection_name,node_name), MAXI as (select *,row_number() over(order by maxp desc, schema_name,projection_name,node_name) as barid from orig_result), BARST AS (SELECT SUM(CASE WHEN barid=1 THEN normalised_cc ELSE 0 END) AS barid_1, SUM(CASE WHEN barid=1 THEN id ELSE 0 END) AS id_1, SUM(CASE WHEN barid=1 THEN group_barid ELSE 0 END) AS group_id1, SUM(CASE WHEN barid=2 THEN normalised_cc ELSE 0 END) AS barid_2, SUM(CASE WHEN barid=2 THEN id ELSE 0 END) AS id_2, SUM(CASE WHEN barid=2 THEN group_barid ELSE 0 END) AS group_id2, SUM(CASE WHEN barid=3 THEN normalised_cc ELSE 0 END) AS barid_3, SUM(CASE WHEN barid=3 THEN id ELSE 0 END) AS id_3, SUM(CASE WHEN barid=3 THEN group_barid ELSE 0 END) AS group_id3, SUM(CASE WHEN barid=4 THEN normalised_cc ELSE 0 END) AS barid_4, SUM(CASE WHEN barid=4 THEN id ELSE 0 END) AS id_4, SUM(CASE WHEN barid=4 THEN group_barid ELSE 0 END) AS group_id4, SUM(CASE WHEN barid=5 THEN normalised_cc ELSE 0 END) AS barid_5, SUM(CASE WHEN barid=5 THEN id ELSE 0 END) AS id_5, SUM(CASE WHEN barid=5 THEN group_barid ELSE 0 END) AS group_id5, SUM(CASE WHEN barid=6 THEN normalised_cc ELSE 0 END) AS barid_6, SUM(CASE WHEN barid=6 THEN id ELSE 0 END) AS id_6, SUM(CASE WHEN barid=6 THEN group_barid ELSE 0 END) AS group_id6, SUM(CASE WHEN barid=7 THEN normalised_cc ELSE 0 END) AS barid_7, SUM(CASE WHEN barid=7 THEN id ELSE 0 END) AS id_7, SUM(CASE WHEN barid=7 THEN group_barid ELSE 0 END) AS group_id7, SUM(CASE WHEN barid=8 THEN normalised_cc ELSE 0 END) AS barid_8, SUM(CASE WHEN barid=8 THEN id ELSE 0 END) AS id_8, SUM(CASE WHEN barid=8 THEN group_barid ELSE 0 END) AS group_id8, SUM(CASE WHEN barid=9 THEN normalised_cc ELSE 0 END) AS barid_9, SUM(CASE WHEN barid=9 THEN id ELSE 0 END) AS id_9, SUM(CASE WHEN barid=9 THEN group_barid ELSE 0 END) AS group_id9, SUM(CASE WHEN barid=10 THEN normalised_cc ELSE 0 END) AS barid_10, SUM(CASE WHEN barid=10 THEN id ELSE 0 END) AS id_10, SUM(CASE WHEN barid=10 THEN group_barid ELSE 0 END) AS group_id10, SUM(CASE WHEN barid=11 THEN normalised_cc ELSE 0 END) AS barid_11, SUM(CASE WHEN barid=11 THEN id ELSE 0 END) AS id_11, SUM(CASE WHEN barid=11 THEN group_barid ELSE 0 END) AS group_id11, SUM(CASE WHEN barid=12 THEN normalised_cc ELSE 0 END) AS barid_12, SUM(CASE WHEN barid=12 THEN id ELSE 0 END) AS id_12, SUM(CASE WHEN barid=12 THEN group_barid ELSE 0 END) AS group_id12, SUM(CASE WHEN barid=13 THEN normalised_cc ELSE 0 END) AS barid_13, SUM(CASE WHEN barid=13 THEN id ELSE 0 END) AS id_13, SUM(CASE WHEN barid=13 THEN group_barid ELSE 0 END) AS group_id13, SUM(CASE WHEN barid=14 THEN normalised_cc ELSE 0 END) AS barid_14, SUM(CASE WHEN barid=14 THEN id ELSE 0 END) AS id_14, SUM(CASE WHEN barid=14 THEN group_barid ELSE 0 END) AS group_id14, SUM(CASE WHEN barid=15 THEN normalised_cc ELSE 0 END) AS barid_15, SUM(CASE WHEN barid=15 THEN id ELSE 0 END) AS id_15, SUM(CASE WHEN barid=15 THEN group_barid ELSE 0 END) AS group_id15, SUM(CASE WHEN barid=16 THEN normalised_cc ELSE 0 END) AS barid_16, SUM(CASE WHEN barid=16 THEN id ELSE 0 END) AS id_16, SUM(CASE WHEN barid=16 THEN group_barid ELSE 0 END) AS group_id16, SUM(CASE WHEN barid=17 THEN normalised_cc ELSE 0 END) AS barid_17, SUM(CASE WHEN barid=17 THEN id ELSE 0 END) AS id_17, SUM(CASE WHEN barid=17 THEN group_barid ELSE 0 END) AS group_id17, SUM(CASE WHEN barid=18 THEN normalised_cc ELSE 0 END) AS barid_18, SUM(CASE WHEN barid=18 THEN id ELSE 0 END) AS id_18, SUM(CASE WHEN barid=18 THEN group_barid ELSE 0 END) AS group_id18, SUM(CASE WHEN barid=19 THEN normalised_cc ELSE 0 END) AS barid_19, SUM(CASE WHEN barid=19 THEN id ELSE 0 END) AS id_19, SUM(CASE WHEN barid=19 THEN group_barid ELSE 0 END) AS group_id19, SUM(CASE WHEN barid=20 THEN normalised_cc ELSE 0 END) AS barid_20, SUM(CASE WHEN barid=20 THEN id ELSE 0 END) AS id_20, SUM(CASE WHEN barid=20 THEN group_barid ELSE 0 END) AS group_id20, SUM(CASE WHEN barid=21 THEN normalised_cc ELSE 0 END) AS barid_21, SUM(CASE WHEN barid=21 THEN id ELSE 0 END) AS id_21, SUM(CASE WHEN barid=21 THEN group_barid ELSE 0 END) AS group_id21, SUM(CASE WHEN barid=22 THEN normalised_cc ELSE 0 END) AS barid_22, SUM(CASE WHEN barid=22 THEN id ELSE 0 END) AS id_22, SUM(CASE WHEN barid=22 THEN group_barid ELSE 0 END) AS group_id22, SUM(CASE WHEN barid=23 THEN normalised_cc ELSE 0 END) AS barid_23, SUM(CASE WHEN barid=23 THEN id ELSE 0 END) AS id_23, SUM(CASE WHEN barid=23 THEN group_barid ELSE 0 END) AS group_id23, SUM(CASE WHEN barid=24 THEN normalised_cc ELSE 0 END) AS barid_24, SUM(CASE WHEN barid=24 THEN id ELSE 0 END) AS id_24, SUM(CASE WHEN barid=24 THEN group_barid ELSE 0 END) AS group_id24, SUM(CASE WHEN barid=25 THEN normalised_cc ELSE 0 END) AS barid_25, SUM(CASE WHEN barid=25 THEN id ELSE 0 END) AS id_25, SUM(CASE WHEN barid=25 THEN group_barid ELSE 0 END) AS group_id25, SUM(CASE WHEN barid=26 THEN normalised_cc ELSE 0 END) AS barid_26, SUM(CASE WHEN barid=26 THEN id ELSE 0 END) AS id_26, SUM(CASE WHEN barid=26 THEN group_barid ELSE 0 END) AS group_id26, SUM(CASE WHEN barid=27 THEN normalised_cc ELSE 0 END) AS barid_27, SUM(CASE WHEN barid=27 THEN id ELSE 0 END) AS id_27, SUM(CASE WHEN barid=27 THEN group_barid ELSE 0 END) AS group_id27, SUM(CASE WHEN barid=28 THEN normalised_cc ELSE 0 END) AS barid_28, SUM(CASE WHEN barid=28 THEN id ELSE 0 END) AS id_28, SUM(CASE WHEN barid=28 THEN group_barid ELSE 0 END) AS group_id28, SUM(CASE WHEN barid=29 THEN normalised_cc ELSE 0 END) AS barid_29, SUM(CASE WHEN barid=29 THEN id ELSE 0 END) AS id_29, SUM(CASE WHEN barid=29 THEN group_barid ELSE 0 END) AS group_id29, SUM(CASE WHEN barid=30 THEN normalised_cc ELSE 0 END) AS barid_30, SUM(CASE WHEN barid=30 THEN id ELSE 0 END) AS id_30, SUM(CASE WHEN barid=30 THEN group_barid ELSE 0 END) AS group_id30 FROM MAXI), MYROWS as (select row_number() over() -3 as rid from ( select 1 from ( select now() as se union all select now() + :GRAPH_HIGHT as se) a timeseries ts as '1 day' over (order by se)) b) select -- draw_xy_axis(1,max_container_count,rid,group_id1) || CASE WHEN barid_1 > rid THEN bar(group_id1) ELSE ' ' END AS id_1, draw_xy_axis(1,max_container_count,rid,group_id1,barid_1) AS id_1, draw_xy_axis(2,max_container_count,rid,group_id2,barid_2) AS id_2, draw_xy_axis(3,max_container_count,rid,group_id3,barid_3) AS id_3, draw_xy_axis(4,max_container_count,rid,group_id4,barid_4) AS id_4, draw_xy_axis(5,max_container_count,rid,group_id5,barid_5) AS id_5, draw_xy_axis(6,max_container_count,rid,group_id6,barid_6) AS id_6, draw_xy_axis(7,max_container_count,rid,group_id7,barid_7) AS id_7, draw_xy_axis(8,max_container_count,rid,group_id8,barid_8) AS id_8, draw_xy_axis(9,max_container_count,rid,group_id9,barid_9) AS id_9, draw_xy_axis(10,max_container_count,rid,group_id10,barid_10) AS id_10, draw_xy_axis(11,max_container_count,rid,group_id11,barid_11) AS id_11, draw_xy_axis(12,max_container_count,rid,group_id12,barid_12) AS id_12, draw_xy_axis(13,max_container_count,rid,group_id13,barid_13) AS id_13, draw_xy_axis(14,max_container_count,rid,group_id14,barid_14) AS id_14, draw_xy_axis(15,max_container_count,rid,group_id15,barid_15) AS id_15, draw_xy_axis(16,max_container_count,rid,group_id16,barid_16) AS id_16, draw_xy_axis(17,max_container_count,rid,group_id17,barid_17) AS id_17, draw_xy_axis(18,max_container_count,rid,group_id18,barid_18) AS id_18, draw_xy_axis(19,max_container_count,rid,group_id19,barid_19) AS id_19, draw_xy_axis(20,max_container_count,rid,group_id20,barid_20) AS id_20, draw_xy_axis(21,max_container_count,rid,group_id21,barid_21) AS id_21, draw_xy_axis(22,max_container_count,rid,group_id22,barid_22) AS id_22, draw_xy_axis(23,max_container_count,rid,group_id23,barid_23) AS id_23, draw_xy_axis(24,max_container_count,rid,group_id24,barid_24) AS id_24, draw_xy_axis(25,max_container_count,rid,group_id25,barid_25) AS id_25, draw_xy_axis(26,max_container_count,rid,group_id26,barid_26) AS id_26, draw_xy_axis(27,max_container_count,rid,group_id27,barid_27) AS id_27, draw_xy_axis(28,max_container_count,rid,group_id28,barid_28) AS id_28, draw_xy_axis(29,max_container_count,rid,group_id29,barid_29) AS id_29, draw_xy_axis(30,max_container_count,rid,group_id30,barid_30) AS id_30 from BARST, MYROWS, max_ros ORDER BY MYROWS.rid DESC; \t \a \f ' ' \pset border 1 \echo :BOLD :YLonBL \echo 'Legend:' \echo :UNBOLD :BL2 with max_res as (select max(result_version) as max_result_ver from ros_count_result_set), orig_res as (select result_version, group_barid, node_name, schema_name, projection_name, node_name || '.' || schema_name || '.' || projection_name AS Projection_Details, container_count, maxp, row_number() over(order by maxp desc, schema_name,projection_name,node_name) AS Label, normalised_cc from ros_count_result_set, max_res where result_version = max_result_ver order by maxp desc, schema_name,projection_name,node_name) select Label, group_barid as 'Node #', node_name, schema_name, projection_name, container_count from orig_res ORDER BY Label; \t \a \f ' ' \echo :BOLD :YLonBL \echo 'The same ROS COUNT but as horizonal bar chart:' \echo :UNBOLD :BL2 with max_res as (select max(result_version) as max_result_ver from ros_count_result_set), orig_res as (select result_version, group_barid, node_name, schema_name, projection_name, node_name || '.' || schema_name || '.' || projection_name AS Projection_Details, container_count, maxp, row_number() over(order by maxp desc, schema_name,projection_name,node_name) AS Label, normalised_cc from ros_count_result_set, max_res where result_version = max_result_ver order by maxp desc, schema_name,projection_name,node_name) select white,to_char(Label,'FM09') as Label2, black, ' ', CASE WHEN MOD(Label,2) = 0 THEN CHR(27) || b_light_blue ELSE CHR(27) || b_dark_blue END, to_char(container_count,'999,999'), black, ' ', INSERT( INSERT ( OVERLAY( REPEAT(' ', 100) PLACING Projection_Details FROM 1 for length(Projection_Details)),normalised_cc+1,0,black || end_underline),1,0,white || begin_underline) as TITLE from orig_res,colors WHERE colors.cid=1 ORDER BY Label2; ---------------------------------------------------------------------------------------------- \echo :BOLD :YLonBL \echo '--------------------------------------' \echo 'Table Size and Frequent Access Heatmap' \echo '--------------------------------------' \echo :UNBOLD :BL2 \t \a \f '|' CREATE OR REPLACE FUNCTION heatmap (heat INT, value INT) RETURN VARCHAR AS BEGIN RETURN CHR(27) || '[48;5;' || CASE WHEN heat < 1 THEN '16' WHEN MOD(heat,22) = 0 THEN '196' WHEN MOD(heat,21) = 0 THEN '202' WHEN MOD(heat,20) = 0 THEN '208' WHEN MOD(heat,19) = 0 THEN '214' WHEN MOD(heat,18) = 0 THEN '220' WHEN MOD(heat,17) = 0 THEN '226' WHEN MOD(heat,16) = 0 THEN '190' WHEN MOD(heat,15) = 0 THEN '154' WHEN MOD(heat,14) = 0 THEN '84' WHEN MOD(heat,13) = 0 THEN '51' WHEN MOD(heat,12) = 0 THEN '45' WHEN MOD(heat,11) = 0 THEN '39' WHEN MOD(heat,10) = 0 THEN '33' WHEN MOD(heat,9) = 0 THEN '32' WHEN MOD(heat,8) = 0 THEN '27' WHEN MOD(heat,7) = 0 THEN '26' WHEN MOD(heat,6) = 0 THEN '21' WHEN MOD(heat,5) = 0 THEN '20' WHEN MOD(heat,4) = 0 THEN '19' WHEN MOD(heat,3) = 0 THEN '18' WHEN MOD(heat,2) = 0 THEN '17' ELSE '16' END || 'm' || TO_CHAR(value,'999,999,999,999') || CHR(27) || :DEFAULT_C ; END ; \echo :BOLD :YLonBL \echo 'All Heatmap colors from 1 the coldest and rare to 22 the hotest and frequent:' \echo :UNBOLD :BL2 WITH loop_22 AS (SELECT ROW_NUMBER() OVER() AS id FROM ( SELECT 1 FROM ( SELECT NOW() as se UNION ALL SELECT NOW() + 22 - 1 as se) a TIMESERIES ts AS '1 DAY' over (ORDER BY se)) b) SELECT heatmap(id,id) FROM loop_22; \echo \echo :BOLD :YLonBL \echo 'Create a temp table for next query result set:' \echo :UNBOLD :BL2 CREATE LOCAL TEMPORARY TABLE table_size_and_freq_use ( result_version int, -- result set version anchor_table_schema varchar(128), anchor_table_name varchar(128), MB numeric, max_accessed_in_24_hours numeric ) ON COMMIT PRESERVE ROWS KSAFE 0; \echo :BOLD :YLonBL \echo 'Table Size and Frequent Access - query real values:' \echo :UNBOLD :BL2 INSERT INTO table_size_and_freq_use (result_version, anchor_table_schema, anchor_table_name, MB, max_accessed_in_24_hours) WITH frequent AS (SELECT table_schema, table_name, time_slice(time, 24, 'HOUR') AS time_window, COUNT(*) AS accessed FROM dc_projections_used GROUP BY 1,2,3), maxi AS (SELECT table_schema, table_name, MAX(accessed) AS max_accessed_in_24_hours FROM frequent GROUP BY 1,2 ORDER BY 3 DESC), tblsize AS (SELECT anchor_table_schema, anchor_table_name, (SUM(used_bytes)/1024/1024)::numeric(7,3) as MB FROM projection_storage GROUP BY 1,2 ORDER BY 1 desc) SELECT 1, tblsize.anchor_table_schema, tblsize.anchor_table_name, tblsize.MB, maxi.max_accessed_in_24_hours FROM tblsize, maxi WHERE maxi.table_schema = tblsize.anchor_table_schema AND maxi.table_name = tblsize.anchor_table_name ORDER BY 3 DESC; SELECT * FROM table_size_and_freq_use; CREATE OR REPLACE FUNCTION normelize (max_graph_value INT, max_value INT, value INT) RETURN INT AS BEGIN RETURN CEIL((max_graph_value -1) / max_value * value); -- CEIL rounds the returned value up to the next whole number. END; \echo :BOLD :YLonBL \echo 'Table Size and Frequent Access Heatmap:' \echo :UNBOLD :BL2 \t \a \f '' SELECT INSERT( REPEAT (' ',50), 1, LENGTH(anchor_table_schema || '.' || anchor_table_name), anchor_table_schema || '.' || anchor_table_name ), -- Schema.Table heatmap(normelize(22,(SELECT max(MB)::INT FROM table_size_and_freq_use),MB::INT),MB::INT), -- Table size in MB heatmap(normelize(22,(SELECT MAX(max_accessed_in_24_hours)::INT FROM table_size_and_freq_use WHERE result_version = (SELECT MAX(result_version) FROM table_size_and_freq_use)), max_accessed_in_24_hours::INT),max_accessed_in_24_hours::INT) -- Table max accessed times in 24 h FROM table_size_and_freq_use WHERE result_version = (SELECT MAX(result_version) FROM table_size_and_freq_use) ORDER BY table_size_and_freq_use.max_accessed_in_24_hours DESC; \echo :BOLD :YLonBL \echo 'To support unknown table names length:' \echo :UNBOLD :BL2 \t \f ' ' SELECT heatmap(normelize(22,(SELECT max(MB)::INT FROM table_size_and_freq_use),MB::INT),MB::INT) AS ' Table MB ', -- Table size in MB heatmap(normelize(22,(SELECT MAX(max_accessed_in_24_hours)::INT FROM table_size_and_freq_use WHERE result_version = (SELECT MAX(result_version) FROM table_size_and_freq_use)), max_accessed_in_24_hours::INT),max_accessed_in_24_hours::INT) AS ' Accessed in 24h', -- Table max accessed times in 24 h anchor_table_schema || '.' || anchor_table_name AS ' Schema.Table' FROM table_size_and_freq_use WHERE result_version = (SELECT MAX(result_version) FROM table_size_and_freq_use) ORDER BY table_size_and_freq_use.max_accessed_in_24_hours DESC; \echo \echo :BOLD :YLonBL \echo 'Use the attached group_concat.cpp UDX from Maurizio Felici to produce the following DB heatmap:' \echo :UNBOLD :BL2 CREATE OR REPLACE FUNCTION heatmap2 (heat INT, rowid INT) RETURN VARCHAR AS BEGIN RETURN CHR(27) || '[48;5;' || CASE WHEN heat < 1 THEN '17' WHEN MOD(heat,21) = 0 THEN '196' WHEN MOD(heat,20) = 0 THEN '202' WHEN MOD(heat,29) = 0 THEN '208' WHEN MOD(heat,18) = 0 THEN '214' WHEN MOD(heat,17) = 0 THEN '220' WHEN MOD(heat,16) = 0 THEN '226' WHEN MOD(heat,15) = 0 THEN '190' WHEN MOD(heat,14) = 0 THEN '154' WHEN MOD(heat,13) = 0 THEN '84' WHEN MOD(heat,12) = 0 THEN '51' WHEN MOD(heat,11) = 0 THEN '45' WHEN MOD(heat,10) = 0 THEN '39' WHEN MOD(heat,9) = 0 THEN '33' WHEN MOD(heat,8) = 0 THEN '32' WHEN MOD(heat,7) = 0 THEN '27' WHEN MOD(heat,6) = 0 THEN '26' WHEN MOD(heat,5) = 0 THEN '21' WHEN MOD(heat,4) = 0 THEN '20' WHEN MOD(heat,3) = 0 THEN '19' WHEN MOD(heat,2) = 0 THEN '18' ELSE '17' END || 'm' || 'L' || CHR(27) || :DEFAULT_C ; END ; \echo :BOLD :YLonBL \echo 'Table Size and Frequent Access - generate random values for heatmap demo:' \echo 'Insert 1000 sample records to demonstrate a heatmap for 1000 tables..' \echo 'The first Insert generates 800 tables with Random integers from 0 through 50 million as max_accessed_in_24_hours' \echo :UNBOLD :BL2 INSERT INTO table_size_and_freq_use (result_version, anchor_table_schema, anchor_table_name, MB, max_accessed_in_24_hours) WITH myrows1 AS (SELECT ROW_NUMBER() OVER() +1 AS numerator FROM ( SELECT 1 FROM ( SELECT NOW() AS se UNION ALL SELECT NOW() + 800 AS se) a TIMESERIES ts AS '1 day' OVER (ORDER BY se)) b), myrows2 AS (SELECT numerator, MOD(numerator,20) AS grby, RANDOMINT(50000000) AS max_accessed_in_24_hours FROM myrows1) SELECT 2 AS result_version, 'Schema_' || grby AS anchor_table_schema, 'Table' || numerator AS anchor_table_name, numerator AS MB, max_accessed_in_24_hours FROM myrows2; \echo :BOLD :YLonBL \echo 'The 2nd Insert generates 200 tables with Random integers from 0 through 100 million as max_accessed_in_24_hours' \echo :UNBOLD :BL2 INSERT INTO table_size_and_freq_use (result_version, anchor_table_schema, anchor_table_name, MB, max_accessed_in_24_hours) WITH myrows1 AS (SELECT ROW_NUMBER() OVER() +195783800 AS numerator FROM ( SELECT 1 FROM ( SELECT NOW() AS se UNION ALL SELECT NOW() + 200 AS se) a TIMESERIES ts AS '1 day' OVER (ORDER BY se)) b), myrows2 AS (SELECT numerator, MOD(numerator,20) AS grby, RANDOMINT(100000000) AS max_accessed_in_24_hours FROM myrows1) SELECT 2 AS result_version, 'Schema_' || grby AS anchor_table_schema, 'Table' || numerator AS anchor_table_name, numerator AS MB, max_accessed_in_24_hours FROM myrows2; WITH maxi AS (SELECT MAX(length(anchor_table_schema)) AS max_schema_len, MAX(max_accessed_in_24_hours)::INT AS top_max FROM table_size_and_freq_use WHERE result_version = (SELECT MAX(result_version) FROM table_size_and_freq_use)), stage1 AS (SELECT heatmap2(normelize(22,top_max::INT,max_accessed_in_24_hours::INT), MB::INT) AS accessed24, anchor_table_schema FROM table_size_and_freq_use, maxi WHERE result_version = (SELECT MAX(result_version) FROM table_size_and_freq_use) ORDER BY table_size_and_freq_use.MB ) SELECT OVERLAY( REPEAT(' ', 50) PLACING anchor_table_schema || ' ' || :ARROW_RIGHT FROM 50 - (length(anchor_table_schema)+2) for length(anchor_table_schema)+2) AS anchor_table_schema, public.group_concat(accessed24 using parameters delim='') AS 'Table accessed times in 24 h HEATMAP' FROM stage1, maxi GROUP BY anchor_table_schema; SELECT ' ' || bold || yellow_font || 'The above heatmap of ' || green_font || count(1) || yellow_font || ' Tables LEGEND:' || black || unbold AS ' ' FROM table_size_and_freq_use,colors WHERE result_version = (SELECT MAX(result_version) FROM table_size_and_freq_use) AND colors.cid=1 GROUP BY yellow_font,green_font,black,bold,unbold; \echo ' ' \t --\a \f ' ' WITH last AS (SELECT MAX(result_version) AS last_version FROM table_size_and_freq_use), maxi AS (SELECT MAX(max_accessed_in_24_hours)::INT AS top_max FROM table_size_and_freq_use, last WHERE result_version = last_version), calc AS ( SELECT 1 AS rowid, COUNT(1) AS counti FROM table_size_and_freq_use,last,maxi WHERE result_version = last_version AND max_accessed_in_24_hours BETWEEN 0 AND top_max/10 UNION ALL SELECT 2, COUNT(1) FROM table_size_and_freq_use,last,maxi WHERE result_version = last_version AND max_accessed_in_24_hours BETWEEN top_max/10 AND 2*top_max/10 UNION ALL SELECT 3, COUNT(1) FROM table_size_and_freq_use,last,maxi WHERE result_version = last_version AND max_accessed_in_24_hours BETWEEN 2*top_max/10 AND 3*top_max/10 UNION ALL SELECT 4, COUNT(1) FROM table_size_and_freq_use,last,maxi WHERE result_version = last_version AND max_accessed_in_24_hours BETWEEN 3*top_max/10 AND 4*top_max/10 UNION ALL SELECT 5, COUNT(1) FROM table_size_and_freq_use,last,maxi WHERE result_version = last_version AND max_accessed_in_24_hours BETWEEN 4*top_max/10 AND 5*top_max/10 UNION ALL SELECT 6, COUNT(1) FROM table_size_and_freq_use,last,maxi WHERE result_version = last_version AND max_accessed_in_24_hours BETWEEN 5*top_max/10 AND 6*top_max/10 UNION ALL SELECT 7, COUNT(1) FROM table_size_and_freq_use,last,maxi WHERE result_version = last_version AND max_accessed_in_24_hours BETWEEN 6*top_max/10 AND 7*top_max/10 UNION ALL SELECT 8, COUNT(1) FROM table_size_and_freq_use,last,maxi WHERE result_version = last_version AND max_accessed_in_24_hours BETWEEN 7*top_max/10 AND 8*top_max/10 UNION ALL SELECT 9, COUNT(1) FROM table_size_and_freq_use,last,maxi WHERE result_version = last_version AND max_accessed_in_24_hours BETWEEN 8*top_max/10 AND 9*top_max/10 UNION ALL SELECT 10, COUNT(1) FROM table_size_and_freq_use,last,maxi WHERE result_version = last_version AND max_accessed_in_24_hours BETWEEN 9*top_max/10 AND 10*top_max/10), max2 AS (SELECT MAX(counti) AS max_counti FROM calc) SELECT ' ' || TO_CHAR(counti,'999,999,999,999,999') || ' Tables with ' || heatmap(normelize(22,top_max,((rowid -1) * top_max/10)::INT),((rowid -1) * top_max/10)::INT) || ' to ' || heatmap(normelize(22,top_max,( rowid * top_max/10)::INT),( rowid * top_max/10)::INT) || ' accesses' FROM calc,maxi,max2 ORDER BY rowid; \t \f ' ' \echo \echo \echo :BOLD :YLonBL \echo 'Get the five most frequent accessed and least accessed tables..' \echo :UNBOLD :BL2 WITH maxi AS (SELECT MAX(max_accessed_in_24_hours)::INT AS top_max FROM table_size_and_freq_use WHERE result_version = (SELECT MAX(result_version) FROM table_size_and_freq_use)) (SELECT REPEAT(' ',20) AS ' ', heatmap(normelize(22,(SELECT max(MB)::INT FROM table_size_and_freq_use),MB::INT),MB::INT) AS ' Table MB ', -- Table size in MB heatmap(normelize(22,top_max::INT,max_accessed_in_24_hours::INT),max_accessed_in_24_hours::INT) AS ' Accessed in 24h', -- Table max accessed times in 24 h anchor_table_schema || '.' || anchor_table_name AS ' Schema.Table' FROM table_size_and_freq_use, maxi WHERE result_version = (SELECT MAX(result_version) FROM table_size_and_freq_use) ORDER BY table_size_and_freq_use.max_accessed_in_24_hours DESC LIMIT 5) UNION ALL (SELECT REPEAT(' ',20) AS ' ', heatmap(normelize(22,(SELECT max(MB)::INT FROM table_size_and_freq_use),MB::INT),MB::INT) AS ' Table MB ', -- Table size in MB heatmap(normelize(22,top_max::INT,max_accessed_in_24_hours::INT),max_accessed_in_24_hours::INT) AS ' Accessed in 24h', -- Table max accessed times in 24 h anchor_table_schema || '.' || anchor_table_name AS ' Schema.Table' FROM table_size_and_freq_use, maxi WHERE result_version = (SELECT MAX(result_version) FROM table_size_and_freq_use) ORDER BY table_size_and_freq_use.max_accessed_in_24_hours ASC LIMIT 5) ; \echo :BOLD :YLonBL \echo 'Tables size and frequent use data sampling.' \echo 'The following is a smaller sample of a much larger data set to help tune a database..' :UNBOLD :BL2 WITH maxi AS (SELECT MAX(max_accessed_in_24_hours)::INT AS top_max FROM table_size_and_freq_use WHERE result_version = (SELECT MAX(result_version) FROM table_size_and_freq_use)) SELECT REPEAT(' ',20) AS ' ', heatmap(normelize(22,(SELECT max(MB)::INT FROM table_size_and_freq_use),MB::INT),MB::INT) AS ' Table MB ', -- Table size in MB heatmap(normelize(22,top_max::INT,max_accessed_in_24_hours::INT),max_accessed_in_24_hours::INT) AS ' Accessed in 24h', -- Table max accessed times in 24 h anchor_table_schema || '.' || anchor_table_name AS ' Schema.Table' FROM maxi, table_size_and_freq_use TABLESAMPLE(3) WHERE result_version = (SELECT MAX(result_version) FROM table_size_and_freq_use) ORDER BY table_size_and_freq_use.max_accessed_in_24_hours DESC; ----------------------------------------------------------------- -- Create a word cloud from any text to visualize word frequency ----------------------------------------------------------------- DROP TABLE IF EXISTS vdraw_big_text_table CASCADE; CREATE TABLE vdraw_big_text_table (word VARCHAR(100)); CREATE PROJECTION vdraw_big_text_table_my_p AS SELECT word FROM vdraw_big_text_table ORDER BY word SEGMENTED BY HASH(word) ALL NODES KSAFE; SELECT REFRESH('vdraw_big_text_table'); CREATE LOCAL TEMPORARY TABLE resultset03 (word_id INT, word VARCHAR(100), wcount INT, print_order INT) ON COMMIT PRESERVE ROWS KSAFE 0; \! tail -32 ./vdraw.sql | tr '\r\n' ' ' | vsql -ec "COPY vdraw_big_text_table (word) FROM STDIN DELIMITER '|' RECORD TERMINATOR ' ' ABORT ON ERROR;" CREATE OR REPLACE FUNCTION line_color (color_id INT) RETURN VARCHAR AS BEGIN RETURN CHR(27) || '[38;5;' || CASE WHEN color_id = 1 THEN '238' WHEN color_id = 2 THEN '239' WHEN color_id = 3 THEN '240' WHEN color_id = 4 THEN '241' WHEN color_id = 5 THEN '242' WHEN color_id = 6 THEN '243' WHEN color_id = 7 THEN '244' WHEN color_id = 8 THEN '245' WHEN color_id = 9 THEN '246' WHEN color_id = 10 THEN '247' WHEN color_id = 11 THEN '248' WHEN color_id = 12 THEN '249' WHEN color_id = 13 THEN '250' WHEN color_id = 14 THEN '251' WHEN color_id = 15 THEN '252' WHEN color_id = 16 THEN '253' WHEN color_id = 17 THEN '254' WHEN color_id = 18 THEN '255' WHEN color_id = 19 THEN '45' WHEN color_id = 20 THEN '51' WHEN color_id = 21 THEN '48' WHEN color_id = 22 THEN '226' WHEN color_id = 23 THEN '220' WHEN color_id = 24 THEN '214' WHEN color_id = 25 THEN '208' WHEN color_id = 26 THEN '202' WHEN color_id = 27 THEN '9' WHEN color_id = 28 THEN '196' ELSE '238' END || 'm' ; END; INSERT INTO resultset03(word, wcount, word_id, print_order) WITH words AS (SELECT UPPER(TRIM(BOTH ' `~.,-[ ]()’' FROM word)) AS word, COUNT(1) AS wcount FROM vdraw_big_text_table WHERE UPPER(TRIM(BOTH ' `~.,-[ ]()’' FROM word)) not in ('AND','THE','OF','TO','IN','A','WITH','FOR','ON','AS','WAS','BY','SUCH','AT', 'IS','WHICH','ARE','ALSO') GROUP BY 1 ORDER BY 2 DESC), tagid AS (SELECT word, wcount, row_number() over() as word_id FROM words ORDER BY wcount DESC), words_count AS (SELECT count(1) AS maxid FROM tagid) SELECT word, wcount, word_id, CASE WHEN word_id between CEIL(maxid * 0.9) and maxid THEN 1 WHEN word_id between CEIL(maxid * 0.7) and CEIL(maxid * 0.8) THEN 2 WHEN word_id between CEIL(maxid * 0.5) and CEIL(maxid * 0.6) THEN 3 WHEN word_id between CEIL(maxid * 0.3) and CEIL(maxid * 0.4) THEN 4 WHEN word_id between CEIL(maxid * 0.1) and CEIL(maxid * 0.2) THEN 5 WHEN word_id between CEIL(maxid * 0.08) and CEIL(maxid * 0.09) THEN 6 WHEN word_id between CEIL(maxid * 0.06) and CEIL(maxid * 0.07) THEN 7 WHEN word_id between CEIL(maxid * 0.04) and CEIL(maxid * 0.05) THEN 8 WHEN word_id between CEIL(maxid * 0.02) and CEIL(maxid * 0.03) THEN 9 WHEN word_id between 1 and CEIL(maxid * 0.01) THEN 10 WHEN word_id between CEIL(maxid * 0.01) and CEIL(maxid * 0.02) THEN 11 WHEN word_id between CEIL(maxid * 0.03) and CEIL(maxid * 0.04) THEN 12 WHEN word_id between CEIL(maxid * 0.05) and CEIL(maxid * 0.06) THEN 13 WHEN word_id between CEIL(maxid * 0.07) and CEIL(maxid * 0.08) THEN 14 WHEN word_id between CEIL(maxid * 0.09) and CEIL(maxid * 0.1) THEN 15 WHEN word_id between CEIL(maxid * 0.4) and CEIL(maxid * 0.5) THEN 16 WHEN word_id between CEIL(maxid * 0.6) and CEIL(maxid * 0.7) THEN 17 WHEN word_id between CEIL(maxid * 0.8) and CEIL(maxid * 0.9) THEN 18 END AS print_order FROM tagid,words_count; \echo '--------------------------------------------------------------------------------------------------------------------------' \echo :YLonBL 'Create a word cloud from any text to visualize word frequency.' \echo 'The following is a text sample demo from Wikipedia about Vertica at' :LBonBL 'https://en.wikipedia.org/wiki/Vertica' :BL2 \echo '--------------------------------------------------------------------------------------------------------------------------' \set UNBOLD '''':UNBOLD'''' \set BOLD '''':BOLD'''' \t \pset recordsep ' ' -- specifies the character used to delimit table records (by default a newline) \set MAX_WORDS_TO_RETRIEVE 100 (SELECT line_color(1)) UNION ALL (SELECT word from resultset03 WHERE print_order = 1 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT line_color(3)) UNION ALL (SELECT word from resultset03 WHERE print_order = 2 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT line_color(5)) UNION ALL (SELECT word from resultset03 WHERE print_order = 3 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT line_color(7)) UNION ALL (SELECT word from resultset03 WHERE print_order = 4 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT line_color(9)) UNION ALL (SELECT word from resultset03 WHERE print_order = 5 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT line_color(11)) UNION ALL (SELECT word from resultset03 WHERE print_order = 6 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT line_color(12)) UNION ALL (SELECT word from resultset03 WHERE print_order = 7 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT line_color(13)) UNION ALL (SELECT word from resultset03 WHERE print_order = 8 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT line_color(14)) UNION ALL (SELECT word from resultset03 WHERE print_order = 9 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT line_color(18) || :BOLD ) UNION ALL (SELECT word from resultset03 WHERE print_order = 10 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT :UNBOLD || line_color(14)) UNION ALL (SELECT word from resultset03 WHERE print_order = 11 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT line_color(13)) UNION ALL (SELECT word from resultset03 WHERE print_order = 12 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT line_color(12)) UNION ALL (SELECT word from resultset03 WHERE print_order = 13 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT line_color(11)) UNION ALL (SELECT word from resultset03 WHERE print_order = 14 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT line_color(9)) UNION ALL (SELECT word from resultset03 WHERE print_order = 15 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT line_color(7)) UNION ALL (SELECT word from resultset03 WHERE print_order = 16 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT line_color(5)) UNION ALL (SELECT word from resultset03 WHERE print_order = 17 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT line_color(1)) UNION ALL (SELECT word from resultset03 WHERE print_order = 18 ORDER BY word_id LIMIT :MAX_WORDS_TO_RETRIEVE) UNION ALL (SELECT CHR(27) || '[00m'); \pset recordsep '\n' -- specifies the character used to delimit table records (by default a newline) \echo '' \echo '--------------------------------------------------------------------------------' \echo ' In this word cloud demo we used only gray colors' \echo ' The following colors can be use to visualize word frequency with higher range:' \echo '--------------------------------------------------------------------------------' with myrows as (select row_number() over() as id from ( select 1 from ( select now() as se union all select now() + 28 - 1 as se) a timeseries ts as '1 day' over (order by se)) b) select line_color(id) || 'TEXT COLORS IN LINE ' || id || CHR(27) || '[00m' from myrows; -------------------------------------------------------------------------------------------------------------------------------------------------- \q -- Remove this line after installing highcharts from: https://www.highcharts.com/download -- Highcharts Installation instructions: https://www.highcharts.com/docs/getting-started/installation \pset recordsep ' ' -- specifies the character used to delimit table records (by default a newline) \o ./examples/wordcloud/vdraw_demo.htm (SELECT 'var text = ''') UNION ALL (SELECT UPPER(TRIM(BOTH ' `~.,-[ ]()’' FROM word)) AS word FROM vdraw_big_text_table WHERE UPPER(TRIM(BOTH ' `~.,-[ ]()’' FROM word)) not in ('AND','THE','OF','TO','IN','A','WITH','FOR','ON','AS','WAS','BY','SUCH','AT', 'IS','WHICH','ARE','ALSO')) UNION ALL (SELECT ''';'); \o \pset recordsep '\n' -- specifies the character used to delimit table records (by default a newline) \! tail -91 ./vdraw.sql | head -22 | cat - ./examples/wordcloud/vdraw_demo.htm > vdraw_temp_file && mv vdraw_temp_file ./examples/wordcloud/vdraw_demo.htm \! tail -66 ./vdraw.sql | head -31 >> ./examples/wordcloud/vdraw_demo.htm \! (cd ./examples/wordcloud ; firefox vdraw_demo.htm) \q -- End of SQL script -- Please do not remove this line -------------------------------------------------------------------------------------------------------------------------------------------------- -- HTML CODE SAMPLE and TEXT ARCHIVE START BELOW THIS LINE -- PLEASE DO NOT REMOVE or ADD ANY LINE BELOW -- -------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ wordcloud BEGIN --------------------------------------------------------------------- Vertica on Highcharts Example
-------------------- END OF HTML CODE FOR WORD CLOUD HERE ---------------- -------------------------------------------------------------------------- -- SAMPLE TEXT ARCHIVE OF 32 LINES FROM WIKIPEDIA START BELOW THIS LINE -- Vertica Systems is an analytic database management software company. VERTICA Vertica was founded in 2005 by database researcher Michael Stonebraker and Andrew Palmer. Palmer was the founding CEO; later, Ralph Breslauer and Christopher P. Lynch served as CEOs. Lynch joined as Chairman and CEO in 2010 and was responsible for Verticas acquisition by Hewlett Packard in March, 2011. The acquisition expanded the HP Software software portfolio for enterprise companies and the public sector group. As part of the Micro Focus-Hewlett Packard Enterprise merger, Vertica joined Micro Focus in September, 2017. The column-oriented Vertica Analytics Platform was designed to manage large, fast-growing volumes of data and provide very fast query performance when used for data warehouses and other query-intensive applications. The product claims to greatly improve query performance over traditional relational database systems, and to provide high availability and exabyte scalability on commodity enterprise servers. Vertica is infrastructure-independent, supporting deployments on multiple cloud platforms (AWS, Google, Azure), on-premise and natively on Hadoop nodes. Its design features include: Column-oriented storage organization, which increases performance of sequential record access at the expense of common transactional operations such as single record retrieval, updates, and deletes. Massively parallel processing (MPP) architecture to distribute queries on independent nodes and scale performance linearly. Standard SQL interface with many analytics capabilities built-in, such as time series gap filling/interpolation, event-based windowing and sessionization, pattern matching, event series joins, statistical computation (e.g., regression analysis), and geospatial analysis. In-database machine learning including categorization, fitting and prediction to enhance processing speed by eliminating the need for down-sampling and data movement. Vertica offers a variety of in-database algorithms, including linear regression, logistic regression, k-means clustering, Naive Bayes classification, random forest decision trees, and support vector machine regression and classification. Vertica also allows deployment of ML models to multiple clusters. Compression, which reduces storage costs and I/O bandwidth. High compression is possible because columns of homogeneous datatype are stored together and because updates to the main store are batched. Shared-nothing architecture, which reduces system contention for shared resources and allows gradual degradation of performance in the face of hardware failure. Easy to use and maintain through automated workload management, data replication, server recovery, query optimization, and storage optimization. Native integration with open source big data technologies like Apache Kafka and Apache Spark. Support for standard programming interfaces, including ODBC, JDBC, ADO.NET, and OLEDB. High-performance and parallel data transfer to statistical tools such as built-in machine learning algorithms based on R, and the ability to store machine learning models, and use them for in-database scoring. Verticas specialized approach aims to significantly increase query performance in data warehouses, while reducing the total cost of ownership by reducing the hardware footprint. One example of a use case detailed in a research paper shows a performance improvement of hundreds of times with Vertica in a specific application due to the use of the vertical DBMS approach. In late 2011, the Vertica Analytics Platform Community Edition was made available for free with certain limitations, such as a maximum of one terabyte of raw data, three-node (servers) cluster, and community-based support. The Vertica Analytics Platform runs on clusters of Linux-based commodity servers. It is also available on the Amazon Elastic Compute Cloud , Microsoft Azure and the Google Cloud Platform, ensuring no infrastructure or platform lock in. The product integrates with Hadoop to leverage HDFS via External Tables with ORC and Parquet Readers and can be installed on Hadoop nodes in a co-located manner as Vertica for SQL on Hadoop (a separate offering, priced by per node). These combined capabilities allow users to choose where to analyze their data, including across multiple data lakes. A range of BI, data visualization, and ETL tools are certified to work with and integrate with the Vertica Analytics Platform. Vertica also offers a certified and secure interface with the popular Kafka message bus, allowing streaming data ingestion. This capability combined with Verticas high performance analytics supports use cases like Internet of Things, Edge Analytics and near real time Fraud Prevention. Several of Vertica’s features were originally prototyped within the C-Store column-oriented database, an academic open source research project at MIT and other universities. The systems architecture is described in a 2012 VLDB paper. \.