| Both sides previous revision
Previous revision
|
|
howto:learn:7_query [2021/07/21 00:59]
gerardnico |
— (current) |
| ====== 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> | |
| |