howto:sql_view:create_from

About

This howto shows you how to create sql views with select query stored in SQL file

How to create one view from one query

The Select Query

We are going to use the tpcds query_11.sql to create this view.

tabul data print --type text sqlite/query_11.sql@tpcds_query

with year_total as (
 select c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(ss_ext_list_price-ss_ext_discount_amt) year_total
       ,'s' sale_type
 from customer
     ,store_sales
     ,date_dim
 where c_customer_sk = ss_customer_sk
   and ss_sold_date_sk = d_date_sk
 group by c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag 
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year 
 union all
 select c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(ws_ext_list_price-ws_ext_discount_amt) year_total
       ,'w' sale_type
 from customer
     ,web_sales
     ,date_dim
 where c_customer_sk = ws_bill_customer_sk
   and ws_sold_date_sk = d_date_sk
 group by c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag 
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
         )
  select  
                  t_s_secyear.customer_id
                 ,t_s_secyear.customer_first_name
                 ,t_s_secyear.customer_last_name
                 ,t_s_secyear.customer_email_address
 from year_total t_s_firstyear
     ,year_total t_s_secyear
     ,year_total t_w_firstyear
     ,year_total t_w_secyear
 where t_s_secyear.customer_id = t_s_firstyear.customer_id
         and t_s_firstyear.customer_id = t_w_secyear.customer_id
         and t_s_firstyear.customer_id = t_w_firstyear.customer_id
         and t_s_firstyear.sale_type = 's'
         and t_w_firstyear.sale_type = 'w'
         and t_s_secyear.sale_type = 's'
         and t_w_secyear.sale_type = 'w'
         and t_s_firstyear.dyear = 2001
         and t_s_secyear.dyear = 2001+1
         and t_w_firstyear.dyear = 2001
         and t_w_secyear.dyear = 2001+1
         and t_s_firstyear.year_total > 0
         and t_w_firstyear.year_total > 0
         and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else 0.0 end
             > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else 0.0 end
 order by t_s_secyear.customer_id
         ,t_s_secyear.customer_first_name
         ,t_s_secyear.customer_last_name
         ,t_s_secyear.customer_email_address
limit 100;

The creation

Drop the view and all existing if they exists

tabul data drop --no-strict-selection *@sqlite

With the tabul create command, to create view called query_11 from the query_11.sql file, you would execute the following command:

tabul data create \
   --target-attribute media-type=view \
   sqlite/query_11.sql@tpcds_query  \
   @sqlite

where:

–target-attribute media-type=view specifies the media type as view.
- It means that we want to create a sql view and not a sql table (the default, for the create operation in a relational system)
sqlite/query_11.sql@tpcds_query is a data selector that selects the query_11.sql tpcds sql file as source
@sqlite select the sql howto connection as target

Output:

Results of the create operation
input                             target
-------------------------------   ---------------
sqlite/query_11.sql@tpcds_query   query_11@sqlite

How to create multiple views from multiple SQL files?

The Create command

Because Tabulify is capable to process multiple files at once, we can also create for all TpcsDs query, a view.

tabul data create \
   --target-attribute media-type=view \
   sqlite/*.sql@tpcds_query  \
   'tpcds_$1@sqlite'
# single quotes around the tpcds_$1@sqlite are mandatory so that bash will not see $1 as a environment variable

where:

sqlite/*.sql@tpcds_query is a data selector that:
- selects all sql files thanks to the star (glob expression)
- in the sqlite directory of the tpcds query directory
- as source
tpcds_$1@sqlite is a target uri that will:
- create for each source,
- a target with a name that:
  - starts with tpcds_
  - followed by the first matching part ($1) of the star (glob expression)
- as target

Listing the views

With the data list command, we can see all of them as been created:

tabul data list *@sqlite

path               media_type
----------------   ----------
query_11           view
tpcds_query_1      view
tpcds_query_2      view
tpcds_query_3      view
tpcds_query_4      view
tpcds_query_5      view
tpcds_query_6      view
tpcds_query_7      view
tpcds_query_8      view
tpcds_query_9      view
tpcds_query_10     view
tpcds_query_11     view
tpcds_query_12     view
tpcds_query_13     view
tpcds_query_14     view
tpcds_query_15     view
tpcds_query_18     view
tpcds_query_19     view
tpcds_query_20     view
tpcds_query_21     view
tpcds_query_22     view
tpcds_query_23     view
tpcds_query_24     view
tpcds_query_24_1   view
tpcds_query_25     view
tpcds_query_26     view
tpcds_query_27     view
tpcds_query_28     view
tpcds_query_29     view
tpcds_query_30     view
tpcds_query_31     view
tpcds_query_32     view
tpcds_query_33     view
tpcds_query_34     view
tpcds_query_35     view
tpcds_query_36     view
tpcds_query_37     view
tpcds_query_38     view
tpcds_query_40     view
tpcds_query_41     view
tpcds_query_42     view
tpcds_query_43     view
tpcds_query_44     view
tpcds_query_45     view
tpcds_query_46     view
tpcds_query_47     view
tpcds_query_48     view
tpcds_query_49     view
tpcds_query_50     view
tpcds_query_52     view
tpcds_query_53     view
tpcds_query_54     view
tpcds_query_55     view
tpcds_query_56     view
tpcds_query_57     view
tpcds_query_58     view
tpcds_query_59     view
tpcds_query_60     view
tpcds_query_61     view
tpcds_query_62     view
tpcds_query_63     view
tpcds_query_64     view
tpcds_query_65     view
tpcds_query_66     view
tpcds_query_67     view
tpcds_query_68     view
tpcds_query_69     view
tpcds_query_70     view
tpcds_query_71     view
tpcds_query_72     view
tpcds_query_73     view
tpcds_query_74     view
tpcds_query_75     view
tpcds_query_76     view
tpcds_query_77     view
tpcds_query_78     view
tpcds_query_79     view
tpcds_query_80     view
tpcds_query_81     view
tpcds_query_82     view
tpcds_query_83     view
tpcds_query_84     view
tpcds_query_85     view
tpcds_query_86     view
tpcds_query_88     view
tpcds_query_89     view
tpcds_query_90     view
tpcds_query_91     view
tpcds_query_92     view
tpcds_query_93     view
tpcds_query_94     view
tpcds_query_95     view
tpcds_query_96     view
tpcds_query_98     view
tpcds_query_99     view

Table of Contents