How to execute and shows the result of a SQL Query

Undraw Server

About

Tpc

Tpcds offers 100 queries. We will load the tpcds data and execute the query number 11.

Steps

Query file location

The file containing this query is located below the tpcds-query connection.

To get the absolute path and other information, you can use the data info. Example:

tabli data info sqlite/query_11.sql@tpcds_query
Information about the data resource (sqlite\query_11.sql@tpcds_query)
ATTRIBUTE      VALUE
------------   -----------------------------------------------------------------
AbsolutePath   D:\code\bytle-mono\db-jdbc\src\main\sql\tpcds\sqlite\query_11.sql
Characterset   ISO-8859-1
Connection     tpcds_query
Count          81
DataUri        sqlite\query_11.sql@tpcds_query
LogicalName    query_11
Name           query_11.sql
Path           sqlite\query_11.sql
Size           2900
Type           text/plain

This query shows customers that have generated growing sales from one year to another in the s and w sales type. Below is an excerpt.

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

Loading the sales data

To prevent any conflict, you can execute the below command to drop all tables from the sqlite howto connection

tabli data drop *@sqlite

Loading the data with the transfer command. The query_11 is a sales query, we are then loading the sales data and their dependencies

tabli data transfer --with-dependencies *sales@tpcds @sqlite

where:

  • *sales@tpcds is a data selector that:
    • selects all tables that finish with the word sales (ie *sales)
    • in the TPC-DS connection (ie tpcds)
  • --with-dependencies add the dependencies of the selection.
  • @sqlite defines the target connection (the sqlite howto connection)
Source                         Target                          Latency (ms)   Row Count   Error   Message
----------------------------   -----------------------------   ------------   ---------   -----   -------
call_center@tpcds              call_center@sqlite              27             2
catalog_page@tpcds             catalog_page@sqlite             257            11718
catalog_sales@tpcds            catalog_sales@sqlite            6528           89807
customer@tpcds                 customer@sqlite                 93             1000
customer_address@tpcds         customer_address@sqlite         64             1000
customer_demographics@tpcds    customer_demographics@sqlite    17941          1920800
date_dim@tpcds                 date_dim@sqlite                 4154           73049
household_demographics@tpcds   household_demographics@sqlite   68             7200
income_band@tpcds              income_band@sqlite              24             20
item@tpcds                     item@sqlite                     382            2000
promotion@tpcds                promotion@sqlite                26             3
ship_mode@tpcds                ship_mode@sqlite                24             20
store@tpcds                    store@sqlite                    27             2
store_sales@tpcds              store_sales@sqlite              5914           120527
time_dim@tpcds                 time_dim@sqlite                 1076           86400
warehouse@tpcds                warehouse@sqlite                95             1
web_page@tpcds                 web_page@sqlite                 35             2
web_sales@tpcds                web_sales@sqlite                896            11876
web_site@tpcds                 web_site@sqlite                 73             2

Executing the query

A query is selected via a script selector where:

  • the first part is a data selector that locates one or more files that contains the query
  • and the second part is the connection where the query should run.

In the below script selector

(sqlite/query_11.sql@tpcds_query)@sqlite

  • sqlite/query_11.sql@tpcds_query is a data selector that select the file query_11.sql in the sub-directory sqlite of the tpcds-queries connection.
  • sqlite is the connection where the query is executed.

A query data uri can be used with all command.

Example with the data print command.

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]

Task Runner