-- ########################### -- RUNNING _v20260107stop_on_error.sql stop_on_error;
reponse success
-- ########################### -- RUNNING clear.sh
/*
sure mode, no confirmation prompt
clearing directory INTERNAL_FILES_DIR (../STORAGE/INT) ...
done
clearing directory MAX_PERF_FILES_DIR (../STORAGE/MAX_PERF) ...
done
clearing directory TRANSAC_FILES_DIR (../STORAGE/TRANSAC) ...
done
*/
-- ########################### -- RUNNING _v20260107doc_data_full.sql -- --Create / insert / refresh
--create col_type
create col_type <end_user|col_type_name> as <number|text> --In stormbase there are only 2 primary data types: text and number. --On top of this primary types you define your col_type (the functional data type). --Why ? in SB you can join 2 columns if and only if they have the same col_type. create col_type t_site_id as text;
reponse success
create col_type t_dept_id as text;
reponse success
create col_type t_item_id as text;
reponse success
create col_type t_customer_id as text;
reponse success
create col_type t_date as text;
reponse success
create col_type t_customer_info as text;
reponse success
--the end_user col_type is used for permissions (explained later) create col_type end_user as text;
reponse success
--Assuming you an integer value in a column, will you define it as text or number? --The answer is simple. Are you going to sum this column? If yes then it is a number column, otherwise it is a text column. --So if your integer column contains for example a status code, use text. And if it contains quantities, use number. --create table
create table <|merge|big> table_name ( column_name1 <number|text|col_type_name>, ...) -- In Stormbase, a table has a type: raw (no specific type), merge or big. -- In traditional BI, big tables would be your fact tables (at the center of your star schema). -- First column of a merge table becomes is primary key of the table. -- Merge tables have an "insert or update" logic, based on the primary key. -- In traditional BI, merge tables would be the dimension tables of your star schema. --items and customers are merge tables, last insert wins and updates using the primary key --they will be used as dimensions in views create merge table items( item_id t_item_id, art_label text, dept t_dept_id, avg_week_sales number, sales_price number);
reponse success
create merge table customers( customer_id t_customer_id, customer_name text);
reponse success
--these tables are raw tables (first column is a primary key) create table item_tags( item_id t_item_id, tag text);
reponse success
create table fidelity_cards( customer_id t_customer_id, card_label t_customer_info, valid_from t_date, valid_until t_date);
reponse success
create table item_customer_infos( customer_id t_customer_id, item_id t_item_id, info t_customer_info, valid_from t_date, valid_until t_date);
reponse success
create big table sales( item_id t_item_id, customer_id t_customer_id, sales_date t_date, sales_qty number, line_id text, packaging_id t_item_id);
reponse success
create big table inventory( item_id t_item_id, inv_qty number);
reponse success
--create view
create view view_name as select * from table_name1, table_name2, ... where table_name1.column_nameA = table_name2.column_nameB ... --In SB you query view not tables, so we create "one table views" create view v_items as select * from items;
reponse success
create view v_item_tags as select * from item_tags;
reponse success
create view v_fidelity_cards as select * from fidelity_cards;
reponse success
create view v_item_customer_infos as select * from item_customer_infos;
reponse success
--most important, this view will link fact with dimensions create view v_sales as select * from sales, items, customers where items.item_id=sales.item_id and customers.customer_id=sales.customer_id;
reponse success
create view v_inventory as select * from inventory, items where items.item_id=inventory.item_id;
reponse success
--insert values
insert into table_name values('col1_value','col2_value',...) insert into items values('artA','the article A','dept #1',10,1.5);
reponse success
insert into items values('artB','the article B','dept #2',10,3.2);
reponse success
insert into items values('box1','a box','packaging',10,0);
reponse success
insert into customers values('C1','customer #1')('C2','customer #2');
reponse success
insert into item_tags values('artA','tag #1');
reponse success
insert into item_tags values('artA','tag #2');
reponse success
insert into fidelity_cards values('C1','SILVER','20191201','20191231');
reponse success
insert into fidelity_cards values('C1','GOLD','20201201','20201231');
reponse success
insert into item_customer_infos values('C1','artA','FREQUENT BUYER of artA in 2019','20190101','20191231');
reponse success
insert into item_customer_infos values('C1','artB','FREQUENT BUYER of artB in 2020','20200101','20201231');
reponse success
insert into item_customer_infos values('C2','artB','FREQUENT BUYER of artB in 2020','20200101','20201231');
reponse success
insert into sales values('artA','C1','20191231',5,'ID01','box1');
reponse success
insert into sales values('artB','C2','20200102',6,'ID02','');
reponse success
insert into sales values('artB','C1','20191231',4,'ID03','');
reponse success
insert into sales values('artB','C2','20200102',7,'ID04','box1');
reponse success
insert into sales values('artC','C1','20200102',8,'ID05','');
reponse success
insert into sales values('artA','C1','20191231',5,'ID06','box1');
reponse success
insert into sales values('artA','C2','20191231',5,'ID07','box1');
reponse success
insert into inventory values('artA',32);
reponse success
insert into inventory values('artC',12);
reponse success
--refresh dirty view
Refresh the views after insert
Dirty means that data has been inserted in the first table of the view since last refresh
refresh dirty view refresh dirty view;
reponse success
--view data model & parameters
Desc (describe) command
desc <|col_type|table|view|context|computed_columns|parameter> desc col_type <|verbose> desc table the_table <|verbose> desc view the_view <|verbose> set CACHE='n';
reponse success
desc;
object_name object_type t_site_id col_type t_dept_id col_type t_item_id col_type t_customer_id col_type t_date col_type t_customer_info col_type end_user col_type items table customers table item_tags table fidelity_cards table item_customer_infos table sales table inventory table v_items view v_item_tags view v_fidelity_cards view v_item_customer_infos view v_sales view v_inventory view TCP_PORT_TRANSAC parameter LOG_VERBOSE parameter CPU_COUNT parameter SLEEP_AFTER_SQL parameter ARRAY_BLOCK_SIZE_BIG parameter ARRAY_BLOCK_SIZE_SMALL parameter PROD_SERVER parameter MAX_PERF_USE_COMPRESSION parameter PARETO_LIMIT parameter CACHE parameter MAX_PERF_CHANGE_COUNT parameter INIT_FILE_PATH parameter
desc col_type;
col_type_name data_type ival_count t_site_id text 0 t_dept_id text 3 t_item_id text 5 t_customer_id text 3 t_date text 7 t_customer_info text 4 end_user text 0
desc col_type verbose;
col_type_name data_type ival_count t_site_id text 0 t_dept_id text 3 t_item_id text 5 t_customer_id text 3 t_date text 7 t_customer_info text 4 end_user text 0 sys#type#items#art_label text 3 sys#type#items#avg_week_sales number 1 sys#type#items#sales_price number 3 sys#type#customers#customer_name text 2 sys#type#item_tags#tag text 2 sys#type#sales#sales_qty number 5 sys#type#sales#line_id text 7 sys#type#inventory#inv_qty number 2
desc table;
table_name column_count line_count has_delete has_update parent_view_hidden items 5 3 n n n customers 2 2 n n n item_tags 2 2 n n n fidelity_cards 4 2 n n n item_customer_infos 5 3 n n n sales 6 7 n n n inventory 2 2 n n n
desc table items;
table_name column_name column_type col_type_name items item_id text t_item_id items art_label text sys#type#items#art_label items dept text t_dept_id items avg_week_sales number sys#type#items#avg_week_sales items sales_price number sys#type#items#sales_price
desc table item_tags;
table_name column_name column_type col_type_name item_tags item_id text t_item_id item_tags tag text sys#type#item_tags#tag
desc table item_tags verbose;
table_name column_name column_type col_type_name item_tags item_id text t_item_id item_tags tag text sys#type#item_tags#tag
desc view;
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_items 1 3 n n n 10 0 13 13 0 0 v_item_tags 1 2 n n n 10 0 12 12 0 0 v_fidelity_cards 1 2 n n n 10 0 12 12 0 0 v_item_customer_infos 1 3 n n n 10 0 13 13 0 0 v_sales 3 7 n n n 10 1 16 17 0 0 v_inventory 2 2 n n n 10 0 12 12 0 0
desc view v_items;
view_name table_name column_names v_items items item_id,art_label,dept,avg_week_sales,sales_price
desc view v_item_tags;
view_name table_name column_names v_item_tags item_tags item_id,tag
desc view v_sales;
view_name table_name column_names v_sales sales item_id,customer_id,sales_date,sales_qty,line_id,packaging_id v_sales items item_id,art_label,dept,avg_week_sales,sales_price v_sales customers customer_id,customer_name
desc view v_sales verbose;
view_name table_name column_name column_type col_type_name v_sales sales item_id text t_item_id v_sales sales customer_id text t_customer_id v_sales sales sales_date text t_date v_sales sales sales_qty number sys#type#sales#sales_qty v_sales sales line_id text sys#type#sales#line_id v_sales sales packaging_id text t_item_id v_sales items item_id text t_item_id v_sales items art_label text sys#type#items#art_label v_sales items dept text t_dept_id v_sales items avg_week_sales number sys#type#items#avg_week_sales v_sales items sales_price number sys#type#items#sales_price v_sales customers customer_id text t_customer_id v_sales customers customer_name text sys#type#customers#customer_name
--upsert (merge table)
Insert into a merge table will upsert (update if PK already exits, insert otherwise)
select * from v_items;
item_id art_label dept avg_week_sales sales_price artA the article A dept #1 10 1.500 artB the article B dept #2 10 3.200 box1 a box packaging 10 0
insert into items values('artA','### the article A ###','dept #1',10,12.123);
reponse success
refresh dirty view;
reponse success
select * from v_items;
item_id art_label dept avg_week_sales sales_price artA ### the article A ### dept #1 10 12.123 artB the article B dept #2 10 3.200 box1 a box packaging 10 0
--save
Save data on disk (persistence)
save save;
reponse success
-- insert into sales values('artA','C2','20191231',5,'ID07','box1');
reponse success
desc table callback where(1,'sales');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 8 n n n
bounce; --insert is lost desc table callback where(1,'sales');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 7 n n n
insert into sales values('artA','C2','20191231',5,'ID07','box1');
reponse success
--save data on disk save;
reponse success
bounce; --insert is not lost desc table callback where(1,'sales');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 8 n n n
insert into sales values('artA','C2','20191231',5,'ID07_','box1');
reponse success
--table is updated desc table callback where(1,'sales');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 9 n n n
--but not the view select count(*) from v_sales;
count(*) 7
--because view is dirty desc view callback where(1,'v_sales');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 7 y n n 10 1 16 17 0 0
save;
reponse success
bounce; --view is still dirty if we bounce SB desc view callback where(1,'v_sales') ;
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 7 y n n 10 1 16 17 0 0
refresh dirty view;
reponse success
--view is no longer dirty desc view callback where(1,'v_sales') ;
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 9 n n n 10 1 18 19 0 0
--and count is correct select count(*) from v_sales ;
count(*) 9
insert from file
insert into table_name select * from file(<'path/to/file'|'file_name under CSV_FILES_DIR/table_name'>) desc parameter verbose callback where(1,'FILE_SEPARATOR');
param_name current_value comment is_default default_value FILE_SEPARATOR , one character, CSV file separator y ,
desc parameter verbose callback where(1,'HEADER');
param_name current_value comment is_default default_value HEADER y y/n, y: CSV files have a header line y y
--path/to/file (contains at least one /) system '(echo ''item_id,customer_id,sales_date,sales_qty,line_id''>/tmp/foo.csv)';
reponse
system '(echo ''item_id_01,customer_id_01,sales_date_01,1,line_id_01''>>/tmp/foo.csv)';
reponse
insert into sales select * from file('/tmp/foo.csv');
reponse success
--using CSV_FILES_DIR desc parameter verbose callback where(1,'CSV_FILES_DIR');
param_name current_value comment is_default default_value CSV_FILES_DIR ../STORAGE/CSV string, CSV file location used in "insert from file" when file path does not start with / y ../STORAGE/CSV
system 'mkdir -p ../STORAGE/CSV/sales';
reponse
system '(echo ''item_id,customer_id,sales_date,sales_qty,line_id''>../STORAGE/CSV/sales/foo.csv)';
reponse
system '(echo ''item_id_02,customer_id_03,sales_date_04,1,line_id_05''>>../STORAGE/CSV/sales/foo.csv)';
reponse
insert into sales select * from file('foo.csv');
reponse success
--the column order does not matter --in HEADER:y mode, SB is flexible regarding the columns provided in the csv file --header is not case sensitive desc parameter verbose callback where(1,'NEW_COLUMN_NULL_STRING');
param_name current_value comment is_default default_value NEW_COLUMN_NULL_STRING NO_DATA string, default value when a column is added to a table or when a column is not provided in a CSV file y NO_DATA
system '(echo ''sales_DATE,item_id,sales_qty_new,line_id''>/tmp/foo.csv)';
reponse
system '(echo ''sales_date_03,item_id_03,3,line_id_03''>>/tmp/foo.csv)';
reponse
system '(echo ''sales_date_03,item_id_04,4,line_id_04''>>/tmp/foo.csv)';
reponse
insert into sales select * from file('/tmp/foo.csv');
reponse success
--same with a merge table system '(echo ''___art_label,dept,item_id''>/tmp/foo.csv)';
reponse
system '(echo ''art_label_01,dept_01,item_id_01''>>/tmp/foo.csv)';
reponse
system '(echo ''art_label_02,dept_02,artA''>>/tmp/foo.csv)';
reponse
insert into items select * from file('/tmp/foo.csv');
reponse success
--SB is not flexible if HEADER:n set HEADER='n';
reponse success
continue_on_error(63);
reponse success
insert into items select * from file('/tmp/foo.csv');
reponse error 63 (continue): end of line found where non expected
stop_on_error;
reponse success
set HEADER='y';
reponse success
--view new lines refresh dirty view;
reponse success
select sales#item_id,sales#customer_id,sales_date,sales_qty,line_id from v_sales callback sort(5,'asc');
sales#item_id sales#customer_id sales_date sales_qty line_id artA C1 20191231 5 ID01 artB C2 20200102 6 ID02 artB C1 20191231 4 ID03 artB C2 20200102 7 ID04 artC C1 20200102 8 ID05 artA C1 20191231 5 ID06 artA C2 20191231 5 ID07 artA C2 20191231 5 ID07 artA C2 20191231 5 ID07_ item_id_01 customer_id_01 sales_date_01 1 line_id_01 item_id_03 NO_DATA sales_date_03 0 line_id_03 item_id_04 NO_DATA sales_date_03 0 line_id_04 item_id_02 customer_id_03 sales_date_04 1 line_id_05
select * from v_items callback sort(1,'asc');
item_id art_label dept avg_week_sales sales_price artA NO_DATA dept_02 0 0 artB the article B dept #2 10 3.200 box1 a box packaging 10 0 item_id_01 NO_DATA dept_01 0 0
--parameter ESCAPE_SEPARATOR_INSIDE_DOUBLE_QUOTE desc parameter verbose callback where(1,'ESCAPE_SEPARATOR_INSIDE_DOUBLE_QUOTE');
param_name current_value comment is_default default_value ESCAPE_SEPARATOR_INSIDE_DOUBLE_QUOTE n y/n, y: ,"abc,def", in a csv file with , separator is interpreted as one value: abc,def y n
system '(echo ''art_label,dept,item_id''>/tmp/foo.csv)';
reponse
system '(echo ''"art_label_03,art_label_03",dept_03,"item_id_03,item_id_03"''>>/tmp/foo.csv)';
reponse
continue_on_error(63);
reponse success
insert into items select * from file('/tmp/foo.csv');
reponse error 63 (continue): end of line found where non expected
stop_on_error;
reponse success
set escape_separator_inside_double_quote='y';
reponse success
insert into items select * from file('/tmp/foo.csv');
reponse success
--buffer load for huge files, in this mode SB is not flexible even if HEADER:y set file_load_without_buffer='n';
reponse success
system '(echo ''item_id,art_label,dept,avg_week_sales,sales_price''>/tmp/foo.csv)';
reponse
system '(echo ''"item_id_04,item_id_04","art_label_04,art_label_04",dept_04,99,12.123''>>/tmp/foo.csv)';
reponse
insert into items select * from file('/tmp/foo.csv');
reponse success
--view new lines refresh dirty view;
reponse success
select sales#item_id,sales#customer_id,sales_date,sales_qty,line_id from v_sales;
sales#item_id sales#customer_id sales_date sales_qty line_id artA C1 20191231 5 ID01 artB C2 20200102 6 ID02 artB C1 20191231 4 ID03 artA C1 20191231 5 ID06 artA C2 20191231 5 ID07 artA C2 20191231 5 ID07 artA C2 20191231 5 ID07_ item_id_01 customer_id_01 sales_date_01 1 line_id_01 item_id_02 customer_id_03 sales_date_04 1 line_id_05 item_id_03 NO_DATA sales_date_03 0 line_id_03 item_id_04 NO_DATA sales_date_03 0 line_id_04 artB C2 20200102 7 ID04 artC C1 20200102 8 ID05
select * from v_items;
item_id art_label dept avg_week_sales sales_price artA NO_DATA dept_02 0 0 artB the article B dept #2 10 3.200 box1 a box packaging 10 0 item_id_01 NO_DATA dept_01 0 0 item_id_03,item_id_03 art_label_03,art_label_03 dept_03 0 0 item_id_04,item_id_04 art_label_04,art_label_04 dept_04 99 12.123
bounce; --delete
--before situation select customer_id, count(*) from v_sales group by customer_id;
customer_id count(*) C1 4 C2 3
desc table callback where(1,'sales');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 9 n n n
--run delete delete sales where customer_id='C1';
reponse success
desc table callback where(1,'sales');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 9 y n n
--apply delete refresh dirty view;
reponse success
--delete has been applied select customer_id, count(*) from v_sales group by customer_id;
customer_id count(*) C2 5
desc table callback where(1,'sales');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 5 n n n
--nothing to delete case (won't trigger table/view rebuild) delete sales where customer_id='C1';
reponse success
desc table callback where(1,'sales');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 5 n n n
bounce; --run select delete sales where customer_id='C1';
reponse success
bounce; --apply select refresh dirty view;
reponse success
--delete is lost select customer_id, count(*) from v_sales group by customer_id;
customer_id count(*) C1 4 C2 5
delete sales where customer_id='C1';
reponse success
refresh dirty table;
reponse success
--delete has not been applied desc view callback where(1,'v_sales');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 9 y n n 10 1 18 19 0 0
continue_on_error(207);
reponse success
--#SB no_cache select customer_id, count(*) from v_sales group by customer_id;
customer_id count(*) C1 4 C2 5
--but table is updated desc table callback like(1,'sales');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 5 n n n
save;
reponse success
bounce; refresh dirty view;
reponse success
--delete has been applied select customer_id, count(*) from v_sales group by customer_id;
customer_id count(*) C2 5
--refresh_online
Unlike refresh, refresh_online won't generate downtime
refresh_online dirty view insert into sales values('artA','C1','20191231',5,'ID11','box1');
reponse success
insert into sales values('artA','C1','20191231',5,'ID11','box1');
reponse success
desc table callback like(1,'sales') sort(1,'asc');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 7 n n n
refresh_online dirty view;
reponse success
--a new view is created on top of new data desc view callback like(1,'sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 5 y n n 10 0 15 15 1 1 v_sales_#refresh_online#_00001 3 2 n n y 15 0 12 12 0 0
--select against v_sales will also be executed against the refresh_online view select count(*) from v_sales;
count(*) 7
-- --let's do it again insert into sales values('artA','C1','20191231',5,'ID11','box1');
reponse success
refresh_online dirty view;
reponse success
--now 2 refresh_online views, the first one is now obsolete (check column mp_r_online_count) desc view callback like(1,'sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 5 y n n 10 0 15 15 2 2 v_sales_#refresh_online#_00001 3 2 n n y 15 0 12 12 0 0 v_sales_#refresh_online#_00002 3 3 n n y 15 0 13 13 0 0
select count(*) from v_sales;
count(*) 8
-- --the refresh will recompute the view, and the refresh_online view becomes obsolete refresh dirty view;
reponse success
desc view callback like(1,'sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 8 n n n 10 1 17 18 0 0 v_sales_#refresh_online#_00001 3 2 n n y 15 0 12 12 0 0 v_sales_#refresh_online#_00002 3 3 n n y 15 0 13 13 0 0
select count(*) from v_sales;
count(*) 8
-- -- --a background thread will clean (drop) the obsolete refresh_online view --the clean can also be triggered by a refresh_force refresh_force dirty view;
reponse success
desc view callback like(1,'sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 8 n n n 10 1 17 18 0 0
select count(*) from v_sales;
count(*) 8
--partitions
Size of table is limited to 4G lines, hence need for partition
desc table callback like(1,'sales') sort(1,'asc');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 8 n n n
insert into sales values('artA','C1','20191231',5,'ID11','box1');
reponse success
desc table callback like(1,'sales') sort(1,'asc');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 9 n n n
set partition_line_count=1;
reponse success
desc parameter callback where(1,'PARTITION_LINE_COUNT');
param_name current_value comment is_default default_value PARTITION_LINE_COUNT 1 integer, maximum #line in a table partition (SB will created a new partition during insert if needed), 0 means no partition n 3000000000
--next insert will trigger a new partition insert into sales values('artA','C1','20191231',5,'ID11','box1');
reponse success
desc table callback like(1,'sales') sort(1,'asc');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 9 n n n sales_#partition#_00001 6 1 n n n
refresh dirty view;
reponse success
--and corresponding view desc view callback like(1,'sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 9 n n n 10 1 18 19 0 0 v_sales_#partition#_00001 3 1 n n n 10 0 11 11 0 0
--select against v_sales will also be executed against partition --#SB log_verbose explain select count(*) from v_sales;
logs SB_f_get_max_perf: pview v_sales, tab_pos_in_view 0, col_pos 0, data_type 4, pareto_type 50, retry 7777 -> elapsed (parsing) : 0 start SQL execution with compression SB_f_get_max_perf: pview v_sales, tab_pos_in_view 0, col_pos 0, data_type 4, pareto_type 11, retry 8888 SB_f_audit_pos, add max_perf step2: ival_often__v_sales.sales.item_id SB_f_get_max_perf: pview v_sales, tab_pos_in_view 0, col_pos 0, data_type 7, pareto_type 11, retry 8888 SB_f_get_max_perf --> return NULL SB_f_get_max_perf: pview v_sales, tab_pos_in_view 0, col_pos 0, data_type 5, pareto_type 11, retry 8888 SB_f_get_max_perf --> return NULL SB_f_get_max_perf: pview v_sales, tab_pos_in_view 0, col_pos 0, data_type 4, pareto_type 11, retry 8888 SB_f_audit_pos, add max_perf step2: ival_often__v_sales.sales.item_id SB_f_get_max_perf: pview v_sales, tab_pos_in_view 0, col_pos 0, data_type 7, pareto_type 11, retry 8888 SB_f_get_max_perf --> return NULL SB_f_get_max_perf: pview v_sales, tab_pos_in_view 0, col_pos 0, data_type 5, pareto_type 11, retry 8888 SB_f_get_max_perf --> return NULL -> elapsed : 0 sec (step #1) SB_f_get_max_perf: pview v_sales, tab_pos_in_view 0, col_pos 0, data_type 4, pareto_type 12, retry 8888 SB_f_audit_pos, add max_perf step2: ival_rare__v_sales.sales.item_id SB_f_get_max_perf: pview v_sales, tab_pos_in_view 0, col_pos 0, data_type 5, pareto_type 12, retry 8888 SB_f_get_max_perf --> return NULL SB_f_get_max_perf: pview v_sales, tab_pos_in_view 0, col_pos 0, data_type 4, pareto_type 12, retry 8888 SB_f_audit_pos, add max_perf step2: ival_rare__v_sales.sales.item_id SB_f_get_max_perf: pview v_sales, tab_pos_in_view 0, col_pos 0, data_type 5, pareto_type 12, retry 8888 SB_f_get_max_perf --> return NULL -> elapsed : 0 sec (step #1) running sql with multi thread, cpu_count : 10 rel_count 8 booster management : - ACTIVATE_NEW_DEV : n - MAX_GBY_BOOSTER_LEN : 200 000 - rel_count : 18 - is_internal : FALSE - is_select_all : FALSE - is_col_type_check : FALSE - bit_count : 0 - count_distinct_size : 0 - gby_col_count : 0 -> gby_no_booster : FALSE -> gby_booster_max_ival_group_by : 1 -> gby_booster_resultset_len : 1 signature (often): 00100000000000000 optimized function used: _f_max_perf_run_sequential001000 signature (rare): 00100000000000000 optimized function used: _f_max_perf_run_sequential001000 -> elapsed : 0 sec (step #2) rarely_used_irels_count : 1 often_used_irels_count : 18 thread_count : 2 -> elapsed : 0 sec (step #3) -> elapsed : 0 sec (step #4) -> running select against child view : v_sales_#partition#_00001 SB_f_get_max_perf: pview v_sales_#partition#_00001, tab_pos_in_view 0, col_pos 0, data_type 4, pareto_type 50, retry 7777 -> elapsed (parsing) : 0 start SQL execution with compression SB_f_get_max_perf: pview v_sales_#partition#_00001, tab_pos_in_view 0, col_pos 0, data_type 4, pareto_type 11, retry 8888 SB_f_audit_pos, add max_perf step2: ival_often__v_sales_#partition#_00001.sales_#partition#_00001.item_id SB_f_get_max_perf: pview v_sales_#partition#_00001, tab_pos_in_view 0, col_pos 0, data_type 7, pareto_type 11, retry 8888 SB_f_get_max_perf --> return NULL SB_f_get_max_perf: pview v_sales_#partition#_00001, tab_pos_in_view 0, col_pos 0, data_type 5, pareto_type 11, retry 8888 SB_f_get_max_perf --> return NULL SB_f_get_max_perf: pview v_sales_#partition#_00001, tab_pos_in_view 0, col_pos 0, data_type 4, pareto_type 11, retry 8888 SB_f_audit_pos, add max_perf step2: ival_often__v_sales_#partition#_00001.sales_#partition#_00001.item_id SB_f_get_max_perf: pview v_sales_#partition#_00001, tab_pos_in_view 0, col_pos 0, data_type 7, pareto_type 11, retry 8888 SB_f_get_max_perf --> return NULL SB_f_get_max_perf: pview v_sales_#partition#_00001, tab_pos_in_view 0, col_pos 0, data_type 5, pareto_type 11, retry 8888 SB_f_get_max_perf --> return NULL -> elapsed : 0 sec (step #1) SB_f_get_max_perf: pview v_sales_#partition#_00001, tab_pos_in_view 0, col_pos 0, data_type 4, pareto_type 12, retry 8888 SB_f_audit_pos, add max_perf step2: ival_rare__v_sales_#partition#_00001.sales_#partition#_00001.item_id SB_f_get_max_perf: pview v_sales_#partition#_00001, tab_pos_in_view 0, col_pos 0, data_type 5, pareto_type 12, retry 8888 SB_f_get_max_perf --> return NULL SB_f_get_max_perf: pview v_sales_#partition#_00001, tab_pos_in_view 0, col_pos 0, data_type 4, pareto_type 12, retry 8888 SB_f_audit_pos, add max_perf step2: ival_rare__v_sales_#partition#_00001.sales_#partition#_00001.item_id SB_f_get_max_perf: pview v_sales_#partition#_00001, tab_pos_in_view 0, col_pos 0, data_type 5, pareto_type 12, retry 8888 SB_f_get_max_perf --> return NULL -> elapsed : 0 sec (step #1) running sql with multi thread, cpu_count : 10 rel_count 1 booster management : - ACTIVATE_NEW_DEV : n - MAX_GBY_BOOSTER_LEN : 200 000 - rel_count : 11 - is_internal : FALSE - is_select_all : FALSE - is_col_type_check : FALSE - bit_count : 0 - count_distinct_size : 0 - gby_col_count : 0 -> gby_no_booster : FALSE -> gby_booster_max_ival_group_by : 1 -> gby_booster_resultset_len : 1 signature (often): 00100000000000000 optimized function used: _f_max_perf_run_sequential001000 signature (rare): 00100000000000000 optimized function used: _f_max_perf_run_sequential001000 -> elapsed : 0 sec (step #2) rarely_used_irels_count : 0 often_used_irels_count : 11 thread_count : 2 -> elapsed : 0 sec (step #3) f_max_perf_post_select_step1 start post select re index the group by end post select re index the group by, merge_gby_len 0 start post select merge results, merge_gby_len: 0 end post select merge results put values dimensions, merge_gby_len: 0 put values dimensions, merge_gby_len: 0 put values expressions, merge_gby_len: 0 do formulas, merge_gby_len: 0 -> elapsed #3 : 0 start housekeeping end housekeeping
--#SB explain select count(*) from v_sales callback like(1,'partition');
logs -> running select against child view : v_sales_#partition#_00001
-- bounce; refresh dirty view;
reponse success
save;
reponse success
--refresh_online & partitions
A partition can have its refresh_online views attached
select count(*) from v_sales;
count(*) 5
desc view callback like(1,'sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 5 n n n 10 0 15 15 0 0
-- insert into sales values('artA','C1','20191231',5,'ID11','box1');
reponse success
desc view callback like(1,'sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 5 y n n 10 0 15 15 0 0
--I have a refresh_online view on top of the table refresh_online dirty view;
reponse success
select count(*) from v_sales;
count(*) 6
desc view callback like(1,'sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 5 y n n 10 0 15 15 1 1 v_sales_#refresh_online#_00001 3 1 n n y 15 0 11 11 0 0
-- insert into sales values('artA','C1','20191231',5,'ID11','box1');
reponse success
set PARTITION_LINE_COUNT=1;
reponse success
insert into sales values('artA','C1','20191231',5,'ID11','box1');
reponse success
desc table callback like(1,'sales') sort(1,'asc');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 7 n n n sales_#partition#_00001 6 1 n n n
refresh_online dirty view;
reponse success
--I have a refresh_online view on top of the partition desc view callback like(1,'sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 5 y n n 10 0 15 15 2 2 v_sales_#partition#_00001 3 0 y y n 10 0 0 0 1 1 v_sales_#partition#_00001_#refresh_online#_00001 3 1 n n y 10 0 11 11 0 0 v_sales_#refresh_online#_00001 3 1 n n y 15 0 11 11 0 0 v_sales_#refresh_online#_00002 3 2 n n y 15 0 12 12 0 0
select count(*) from v_sales;
count(*) 8
--#SB no_cache explain select count(*) from v_sales callback like(1,'running select');
logs -> running select against child view : v_sales_#partition#_00001 -> running select against child view : v_sales_#refresh_online#_00002 -> running select against child view : v_sales_#partition#_00001_#refresh_online#_00001
-- refresh dirty view;
reponse success
select count(*) from v_sales;
count(*) 8
desc view callback like(1,'sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 7 n n n 10 1 16 17 0 0 v_sales_#partition#_00001 3 1 n n n 10 0 11 11 0 0 v_sales_#partition#_00001_#refresh_online#_00001 3 1 n n y 10 0 11 11 0 0 v_sales_#refresh_online#_00001 3 1 n n y 15 0 11 11 0 0 v_sales_#refresh_online#_00002 3 2 n n y 15 0 12 12 0 0
-- refresh_force dirty view;
reponse success
select count(*) from v_sales;
count(*) 8
desc view callback like(1,'sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 7 n n n 10 1 16 17 0 0 v_sales_#partition#_00001 3 1 n n n 10 0 11 11 0 0
--refresh advanced
Applies deletes + updates + data model changes on tables and views)
refresh dirty tableRecompute view but not max_perf
refresh view view_name Recompute permissions if changes and drop cache
refresh permissionRecompute computed columns if changes and create a new version of stormbase.so under _SO_LINUX
refresh computed_columnDrop cache
refresh cache Does all above and recomputes max_perf and re execute init file
refresh dirty view --under construction --deletes & refresh_online
Some big tables have a "delete before insert" logic along with refresh_online needs
In previous version of Stormbase such tables were defined as big_online, now all big tables support this functionality
Big_online keyword is kept for backward compatibility
--I run a refresh_force to clean the old child view (not needed in client/server mode) --delete sales where regex(item_id,'.*'); refresh_force dirty view;
reponse success
-- set partition_line_count=100; select list(line_id) from v_sales;
list(line_id) ID02,ID04,ID07,ID07_,ID11
desc table callback like(1,'sales') sort(1,'asc');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 7 n n n sales_#partition#_00001 6 1 n n n
desc view callback like(1,'v_sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 7 n n n 10 1 16 17 0 0 v_sales_#partition#_00001 3 1 n n n 10 0 11 11 0 0
--partial delete on big table not allowed by refresh_online insert into sales values('artA','C1','20191231',5,'ID12','box1')('artA','C1','20191231',5,'ID13','box1');
reponse success
delete sales where line_id='ID12';
reponse success
continue_on_error(141);
reponse success
refresh_online dirty view;
reponse error 141 (continue): "refresh_online dirty views" is not allowed
stop_on_error;
reponse success
refresh_force dirty view;
reponse success
select list(line_id) from v_sales;
list(line_id) ID02,ID04,ID07,ID07_,ID11,ID13
desc view callback like(1,'v_sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 7 n n n 10 1 16 17 0 0 v_sales_#partition#_00001 3 1 n n n 10 0 11 11 0 0 v_sales_#partition#_00002 3 1 n n n 10 0 11 11 0 0
--full delete on big table is allowed by refresh_online delete sales where regex(item_id,'.*');
reponse success
insert into sales values('artA','C1','20191231',5,'ID14','box1');
reponse success
--refresh_online is accepted refresh_online dirty view;
reponse success
select list(line_id) from v_sales;
list(line_id) ID14
desc table callback like(1,'sales') sort(1,'asc');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 0 n n y sales_#partition#_00001 6 0 n n y sales_#partition#_00002 6 1 n n y
desc view callback like(1,'v_sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 7 y n n 10 1 16 17 1 0 v_sales_#partition#_00001 3 1 y n n 10 0 11 11 1 0 v_sales_#partition#_00001_#refresh_online#_00001 3 0 n n y 10 0 10 10 0 0 v_sales_#partition#_00002 3 1 y n n 10 0 11 11 1 1 v_sales_#partition#_00002_#refresh_online#_00001 3 1 n n y 10 0 11 11 0 0 v_sales_#refresh_online#_00001 3 0 n n y 10 0 10 10 0 0
--redo delete sales where regex(item_id,'.*');
reponse success
insert into sales values('artA','C1','20191231',5,'ID15','box1')('artA','C1','20191231',5,'ID16','box1');
reponse success
refresh_online dirty view;
reponse success
select list(line_id) from v_sales;
list(line_id) ID15,ID16
--the main view is flagged as with hide_parent_view --so the select will not see this view desc table callback like(1,'sales') sort(1,'asc');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 0 n n y sales_#partition#_00001 6 0 n n y sales_#partition#_00002 6 2 n n y
desc view callback like(1,'v_sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 7 y n n 10 1 16 17 2 0 v_sales_#partition#_00001 3 1 y n n 10 0 11 11 2 0 v_sales_#partition#_00001_#refresh_online#_00001 3 0 n n y 10 0 10 10 0 0 v_sales_#partition#_00001_#refresh_online#_00002 3 0 n n y 10 0 10 10 0 0 v_sales_#partition#_00002 3 1 y n n 10 0 11 11 2 2 v_sales_#partition#_00002_#refresh_online#_00001 3 1 n n y 10 0 11 11 0 0 v_sales_#partition#_00002_#refresh_online#_00002 3 2 n n y 10 0 12 12 0 0 v_sales_#refresh_online#_00001 3 0 n n y 10 0 10 10 0 0 v_sales_#refresh_online#_00002 3 0 n n y 10 0 10 10 0 0
--after bounce, data is still here save;
reponse success
bounce; desc table callback like(1,'sales') sort(1,'asc');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 0 n n y sales_#partition#_00001 6 0 n n y sales_#partition#_00002 6 2 n n y
desc view callback like(1,'v_sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 7 y n n 10 1 16 17 2 0 v_sales_#partition#_00001 3 1 y n n 10 0 11 11 2 0 v_sales_#partition#_00001_#refresh_online#_00001 3 0 n n y 10 0 10 10 0 0 v_sales_#partition#_00001_#refresh_online#_00002 3 0 n n y 10 0 10 10 0 0 v_sales_#partition#_00002 3 1 y n n 10 0 11 11 2 2 v_sales_#partition#_00002_#refresh_online#_00001 3 1 n n y 10 0 11 11 0 0 v_sales_#partition#_00002_#refresh_online#_00002 3 2 n n y 10 0 12 12 0 0 v_sales_#refresh_online#_00001 3 0 n n y 10 0 10 10 0 0 v_sales_#refresh_online#_00002 3 0 n n y 10 0 10 10 0 0
select list(line_id) from v_sales;
list(line_id) ID15,ID16
--after clear_max_perf the non dirty views are rebuilt save;
reponse success
system './clear_max_perf.sh sure';
reponse sure mode, no confirmation prompt clearing directory ../STORAGE/MAX_PERF... done
bounce; desc table callback like(1,'sales') sort(1,'asc');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 0 n n y sales_#partition#_00001 6 0 n n y sales_#partition#_00002 6 2 n n y
desc view callback like(1,'v_sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 7 y y n 10 0 0 0 2 0 v_sales_#partition#_00001 3 1 y y n 10 0 0 0 2 0 v_sales_#partition#_00001_#refresh_online#_00001 3 0 n n y 10 0 10 10 0 0 v_sales_#partition#_00001_#refresh_online#_00002 3 0 n n y 10 0 10 10 0 0 v_sales_#partition#_00002 3 1 y y n 10 0 0 0 2 2 v_sales_#partition#_00002_#refresh_online#_00001 3 1 n n y 10 0 11 11 0 0 v_sales_#partition#_00002_#refresh_online#_00002 3 2 n n y 10 0 12 12 0 0 v_sales_#refresh_online#_00001 3 0 n n y 10 0 10 10 0 0 v_sales_#refresh_online#_00002 3 0 n n y 10 0 10 10 0 0
select list(line_id) from v_sales;
list(line_id) ID15,ID16
--refresh dirty view cleans everything refresh_force dirty view;
reponse success
desc table callback like(1,'sales') sort(1,'asc');
table_name column_count line_count has_delete has_update parent_view_hidden sales 6 0 n n n sales_#partition#_00001 6 0 n n n sales_#partition#_00002 6 2 n n n
desc view callback like(1,'v_sales') sort(1,'asc');
view_name table_count rel_count is_dirty is_mp_dirty is_partial first_iline mp_rare_count mp_often_count mp_count r_online_count mp_r_online_count v_sales 3 0 n n n 10 0 10 10 2 0 v_sales_#partition#_00001 3 0 n n n 10 0 10 10 2 0 v_sales_#partition#_00001_#refresh_online#_00001 3 0 y n y 10 0 10 10 0 0 v_sales_#partition#_00001_#refresh_online#_00002 3 0 y n y 10 0 10 10 0 0 v_sales_#partition#_00002 3 2 n n n 10 0 12 12 0 0 v_sales_#refresh_online#_00001 3 0 y n y 10 0 10 10 0 0 v_sales_#refresh_online#_00002 3 0 y n y 10 0 10 10 0 0
select list(line_id) from v_sales;
list(line_id) ID15,ID16
-- ########################### -- RUNNING _v20260107save.sql save;
reponse success
-- ########################### -- RUNNING _v20260107shutdown.sql shutdown;