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/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:

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

How to modify the content of a table




Related Pages
Learning Tabulify - Step 6 - How to transfer Data Resources

In Tabulify, when you want to manipulate data, it's called a transfer. You want to move data, you transfer You want to copy data, you transfer You want to insert data, you transfer You want to...
Learning Tabulify - Step 9 - How to fill a data resource with generated data ?

Tabulify integrates natively a data generator. You can generate realistic production data and start working on your project right away. anonymize production data in your development environment because...

Task Runner