Query
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:
- @connection defined the connection where the query will run.
In the next steps, we will show this concepts by running a query of the Tpcds benchmark.
Query 11
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
- 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:
(sqlite/query_11.sql@tpcds_query)@connection
Printing the query
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]
A query is just a data resource
Don't forget that a query is just a data resource. It can therefore be used on every command as any other data resource.
How to copy a query into another table
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]
How to download a query as a CSV
For instance, if you want to copy its output:
- to a CSV called query_11.csv
- into the current directory (cd)
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]
Next
Now that we can query our data, we are going to learn how to modify the content of a table