= This page does not exist anymore | Learn | Tabulify - Growing data proficiency of the world

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
howto:learn:7_query [2021/07/21 00:59]
gerardnico
— (current)
Line 1: Line 1:
-====== Learning Tabulify - Step 7 - How to execute a Query ====== 
- 
-===== Query ===== 
-For ''Tabulify'', a  [[resource:query|query]] is a [[resource:script|script]] that when executed returns a [[resource:content|content resource]]. 
- 
-In the case of a [[resource:select|Sql Query (ie a SELECT statement)]], the result is a ''data set''  in [[resource:tabular|tabular format]]. 
- 
-The [[resource:selector|selector]] of a [[resource:query|query]] is known as a [[resource:script_selector|script selector]]. This ''selector'' is composed of a [[resource:data_selector|data selector]] inside its expression. 
- 
-The syntax is: 
-<code bash> 
-(dataSelector)@datastore 
-</code> 
-where: 
-  * ''dataSelector'' is a [[resource:data_selector|data selector]] that locates [[resource:resource|data resource]] that contains the [[resource:query|query]] as [[resource:content|data]] 
-  * ''@datastore'' defined the [[system:system|datastore]] where the [[resource:query|query]] will run. 
- 
-In the next steps, we will show this concepts by running a query of the [[system:tpcds|Tpcds]] benchmark. 
- 
-===== Query 11 ===== 
- 
-[[system:tpcds|Tpcds]] offers 100 queries that are all located in the [[connection:tpcds_query|tpcds-queries]] datastore 
- 
- 
-We will execute the ''query 11'' that lists the customers that have generated growing sales from one year to another in the ''s'' and ''w'' sales type. 
- 
- 
-Below is the 20 first lines shown with the [[tabli:data:head|head command]]. 
-<unit> 
-<code dos> 
-tabli data head --limit 20 sqlite/[email protected]_query 
-</code> 
-<console> 
-The first 20 rows of the data resource (sqlite\[email protected]_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 
-</console> 
-</unit> 
-  * To execute the ''query 11'', we just need to wrap the previous selector and use it in any other command. The query data resource will be then: 
-<code bash> 
-(sqlite/[email protected]_query)@datastore 
-</code> 
-===== Printing the query ===== 
- 
-[[tabli:data:print|Printing]] the query against [[connection:howtos|the sqlite datastore]] will show us the customers that have generated growing sales from one year to another in the ''s'' and ''w'' sales type. 
- 
-<unit> 
-<code bash> 
-tabli data print (sqlite/[email protected]_query)@sqlite 
-</code> 
-<console> 
-customer_id        customer_first_name   customer_last_name   customer_email_address                
-----------------   -------------------   ------------------   ----------------------------------    
-AAAAAAAABMDAAAAA   Jose                  Guzman               [email protected]                  
-AAAAAAAAFLAAAAAA   Joseph                Riley                [email protected]                 
-AAAAAAAAJGBAAAAA   Bonnie                Harrison             [email protected]      
-AAAAAAAAKMDAAAAA   Alexander             White                [email protected]    
-AAAAAAAAOFCAAAAA   Timothy               Grogan               [email protected] 
-</console> 
-</unit> 
- 
-===== A query is just a data resource ===== 
- 
-<note important> 
-Don't forget that a [[resource:query|query]] is just a [[resource:resource|data resource]] and therefore can then be used on every command as any other [[resource:resource|data resource]]. 
-</note> 
- 
-For instance, if you want to [[tabli:data:copy|copy]] its output to a [[resource:table|table]] called ''query_11'', you would issue the following command: 
-<unit> 
-<code bash> 
-tabli data copy (sqlite/[email protected]_query)@sqlite  [email protected] 
-</code> 
-</unit> 
-And the content of the new [[resource:table|table]] is just the same than the output  of the ''query 11''. 
-<unit> 
-<code bash> 
-tabli data print [email protected] 
-</code> 
-<console> 
-customer_id        customer_first_name   customer_last_name   customer_email_address                
-----------------   -------------------   ------------------   ----------------------------------    
-AAAAAAAABMDAAAAA   Jose                  Guzman               [email protected]                  
-AAAAAAAAFLAAAAAA   Joseph                Riley                [email protected]                 
-AAAAAAAAJGBAAAAA   Bonnie                Harrison             [email protected]      
-AAAAAAAAKMDAAAAA   Alexander             White                [email protected]    
-AAAAAAAAOFCAAAAA   Timothy               Grogan               [email protected] 
-</console> 
-</unit> 
- 
-===== Next ===== 
-Now that we can query our data, we are going to learn how to modify the content of a table 
- 
-<btn> 
-[[8_data_manipulation|How to modify the content of a table]] 
-</btn> 
  
Powered by ComboStrap