---json
{
"name":"Query Execution",
"canonical":"howto:database:query_execution",
"title":"How to execute and shows the result of a SQL Query",
"description":"This howto will shows you to execute and shows the result of a SQL Query against the tpcds data"
}
---
====== Tabli - How to execute and shows the result of a SQL Query ======
===== About ======
[[docs:system:tpcds|{{ :howto:database:tpc.svg?200|}}]]
[[docs:system:tpcds|Tpcds]] offers 100 [[docs:resource:select|queries]]. We will load the [[docs:system:tpcds|tpcds data]] and execute the [[docs:resource:select|query]] ''number 11''.
===== Steps =====
==== Query file location ====
The file containing this query is located below the [[docs:connection:tpcds_query|tpcds-query connection]].
To get the absolute path and other information, you can use the [[docs:tabli:data:info|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 [[docs:connection:howtos|sqlite howto connection]]
tabli data drop *@sqlite
Loading the data with the [[docs:tabli:data:transfer|transfer command]]. The ''query_11'' is a sales query, we are then loading the ''sales'' data and their [[docs:resource:dependency|dependencies]]
tabli data transfer --with-dependencies *sales@tpcds @sqlite
where:
* ''*sales@tpcds'' is a [[docs:resource:data_selector|data selector]] that:
* selects all tables that finish with the word sales (ie ''*sales'')
* in the [[docs:system:tpcds|TPC-DS]] connection (ie ''tpcds'')
* ''%%--with-dependencies%%'' add the dependencies of the selection.
* ''@sqlite'' defines the target connection (the [[docs:connection:howtos|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 [[docs:resource:select|query]] is selected via a [[docs:resource:script_selector|script selector]] where:
* the first part is a [[docs:resource:data_selector|data selector]] that locates one or more files that contains the ''query''
* and the second part is the [[docs:connection:connection|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 [[docs:resource:data_selector|data selector]] that select the file ''query_11.sql'' in the sub-directory ''sqlite'' of the [[docs:connection:tpcds_query|tpcds-queries]] connection.
* ''sqlite'' is the connection where the ''query'' is executed.
A ''query data uri'' can be used with all [[docs:tabli:data:start|command]].
Example with the [[docs:tabli:data:print|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 Jose.Guzman@stJp.edu
AAAAAAAAFLAAAAAA Joseph Riley Joseph.Riley@znxF.com
AAAAAAAAJGBAAAAA Bonnie Harrison Bonnie.Harrison@AKYSOPlHEYkd.com
AAAAAAAAKMDAAAAA Alexander White Alexander.White@7v2XsptkFrc50K.org
AAAAAAAAOFCAAAAA Timothy Grogan Timothy.Grogan@pn3NH2BDng.org