For Tabulify, a query is a script that when executed returns a content resource.
In the case of a Sql Query (ie a SELECT statement), the result is a data set in tabular format.
The selector of a query is known as a script selector. This selector is composed of a data selector inside its expression.
The syntax is:
(dataSelector)@connection
where:
In the next steps, we will show this concepts by running a query of the Tpcds benchmark.
Tpcds offers 100 queries that are all located in the tpcds-queries connection
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 head command.
tabli data head --limit 20 sqlite/query_11.sql@tpcds_query
The first 20 rows of the data resource (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
(sqlite/query_11.sql@tpcds_query)@connection
Printing the query against the sqlite connection will show us the customers that have generated growing sales from one year to another in the s and w sales type.
tabli data print (sqlite/query_11.sql@tpcds_query)@sqlite
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]
Don't forget that a query is just a data resource. It can therefore be used on every command as any other data resource.
For instance, if you want to copy its output to a table called query_11, you would issue the following command:
tabli data copy (sqlite/query_11.sql@tpcds_query)@sqlite query_11@sqlite
And the content of the new table is just the same than the output of the query 11.
tabli data print query_11@sqlite
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]
For instance, if you want to copy its output:
you would issue the following command
tabli data copy (sqlite/query_11.sql@tpcds_query)@sqlite query_11.csv@cd
And the content of the new csv is also just the same than the output of the query 11.
tabli data print query_11.csv@cd
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]
Now that we can query our data, we are going to learn how to modify the content of a table