About
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]