Learning Tabulify - Step 7 - How to execute a Query

Learning Tabulify - Step 7 - How to execute a Query

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:

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/[email protected]_query
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
  • 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/[email protected]_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/[email protected]_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 and therefore can then 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/[email protected]_query)@sqlite  [email protected]

And the content of the new table is just the same than the output of the query 11.

tabli data print [email protected]
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

How to modify the content of a table

Powered by ComboStrap