- Created by Sarabjeet Kaur, last modified by NIDHI BHANDARI on Nov 15, 2023
You are viewing an old version of this page. View the current version.
Compare with Current View Page History
« Previous Version 3 Current »
Applies to:
Kyvos Enterprise Kyvos Cloud (Managed Services on AWS) Kyvos Azure Marketplace
Kyvos AWS Marketplace Kyvos Single Node Installation (Kyvos SNI) Kyvos Free (Limited offering for AWS)
Samples for the types of queries that work with Kyvos:
Examples of Kyvos supported queries Expand source
/*============================================================================
################################################################################
SQL Query examples for working cases
################################################################################
==============================================================================*/
/* Case#1 */
SELECT *
FROM `kyvos_browsing_automationcubes`.`order date` `Order Date`
LIMIT 10
/* Note: SELECT * query with LIMIT clause will provide all the columns and result as dummy data */
/* Case#2 */
SELECT `order date`.`category` AS `category`
FROM `kyvos_browsing_automationcubes`.`order date` `Order Date`
GROUP BY `order date`.`category`
ORDER BY `order date`.`category`
/* Example of how to select dimension */
/* Case#3 */
SELECT Min(Year(`order_date1`.`orderdate`)) AS `temp_tc___561953214__0_`,
Count(1) AS `x__alias__1`
FROM `kyvos_dataaccuracycubes`.`order date` `order_date1`
LEFT JOIN (SELECT `order_date1`.`productname` AS `productname`,
Max(`order_date1`.`max sales`) AS `x__alias__0`
FROM `kyvos_dataaccuracycubes`.`order date` `order_date1`
WHERE ( NOT ( `order_date1`.`productname` IS NULL ) )
GROUP BY `order_date1`.`productname`
ORDER BY `x__alias__0` ASC,
`productname` ASC
LIMIT 100) `t0`
ON ( `order_date1`.`productname` = `t0`.`productname` )
HAVING ( Count(1) > 0 )
/* Example of how to use left join and aggregated sql query */
/* Case#4 */
SELECT Min(Year(`order_date1`.`orderdate`)) AS `temp_tc___561953214__0_`,
Count(1) AS `x__alias__1`
FROM `kyvos_dataaccuracycubes`.`order date` `order_date1`
JOIN (SELECT `order_date1`.`productname` AS `productname`,
Max(`order_date1`.`max sales`) AS `x__alias__0`
FROM `kyvos_dataaccuracycubes`.`order date` `order_date1`
WHERE ( NOT ( `order_date1`.`productname` IS NULL ) )
GROUP BY `order_date1`.`productname`
ORDER BY `x__alias__0` ASC,
`productname` ASC
LIMIT 100) `t0`
ON ( `order_date1`.`productname` = `t0`.`productname` )
HAVING ( Count(1) > 0 )
/* Example of how to use join and aggregated SQL query */
/* Case#5 */
SELECT `t0`.`avg_avg_discount_ok` AS `avg_avg_discount_ok`,
`t0`.`avg_avg_quantity_ok` AS `avg_avg_quantity_ok`,
`t0`.`avg_avg sales_ok` AS `avg_avg sales_ok`,
`t3`.`x_measure__4` AS `sum_calculation_702843039217930242_ok`,
`t0`.`sum_avg shippingcost_ok` AS `sum_avg shippingcost_ok`,
`t0`.`sum_min_profit_ok` AS `sum_min_profit_ok`,
`t0`.`sum_min_sales_ok` AS `sum_min_sales_ok`,
`t0`.`yr_shipdate_ok` AS `yr_shipdate_ok`
FROM (
SELECT year(`ORDER date`.`shipdate`) AS `yr_shipdate_ok`,
avg(`ORDER date`.`avg discount`) AS `avg_avg_discount_ok`,
avg(cast(`ORDER date`.`avg quantity` AS DOUBLE)) AS `avg_avg_quantity_ok`,
avg(`ORDER date`.`avg sales`) AS `avg_avg sales_ok`,
sum(`ORDER date`.`avg shippingcost`) AS `sum_avg shippingcost_ok`,
sum(`ORDER date`.`min profit`) AS `sum_min_profit_ok`,
sum(`ORDER date`.`min sales`) AS `sum_min_sales_ok`
FROM `kyvos_dataaccuracycubes`.`ORDER date` `ORDER date`
GROUP BY year(`ORDER date`.`shipdate`) ) t0
JOIN
(
SELECT `t1`.`yr_shipdate_ok` AS `yr_shipdate_ok`,
sum(`t2`.`x_measure__3`) AS `x_measure__4`
FROM (
SELECT `ORDER date`.`country` AS `country`,
year(`ORDER date`.`shipdate`) AS `yr_shipdate_ok`
FROM `kyvos_dataaccuracycubes`.`ORDER date` `ORDER date`
GROUP BY `ORDER date`.`country`,
year(`ORDER date`.`shipdate`) ) t1
JOIN
(
SELECT `ORDER date`.`country` AS `country`,
sum(`ORDER date`.`sum sales`) AS `x_measure__3`
FROM `kyvos_dataaccuracycubes`.`ORDER date` `ORDER date`
GROUP BY `ORDER date`.`country` ) t2
ON (
`t1`.`country` = `t2`.`country`)
GROUP BY `t1`.`yr_shipdate_ok` ) t3
ON (
`t0`.`yr_shipdate_ok` = `t3`.`yr_shipdate_ok`)
/* Example of how to use multiple joins and aggregated SQL query */
/* Case#6 */
SELECT Count(`product-sales_detail_50gb`.`order_quantity`) AS
`cnt_order_quantity_ok`,
`product-sales_detail_50gb`.`product_category` AS `product_category`
,
`product-sales_detail_50gb`.`product_name` AS
`product_name`
FROM `kyvos_browsing_automationcubes`.`product-sales_detail_50gb`
`Product-Sales_Detail_50GB`
WHERE ( ( `product-sales_detail_50gb`.`product_name` IN ( 'Tripod Stand' ) )
OR ( `product-sales_detail_50gb`.`product_name` IS NULL )
OR ( ( `product-sales_detail_50gb`.`product_name` >= '32 inch' )
AND ( `product-sales_detail_50gb`.`product_name` <= '46 inch' ) )
OR ( ( `product-sales_detail_50gb`.`product_name` >=
'Connector Cables and Ax.'
)
AND ( `product-sales_detail_50gb`.`product_name` <=
'Lens Attachment' )
)
OR ( ( `product-sales_detail_50gb`.`product_name` >= 'Lens Kit' )
AND ( `product-sales_detail_50gb`.`product_name` <= 'TV Remote' )
) )
GROUP BY `product-sales_detail_50gb`.`product_category`,
`product-sales_detail_50gb`.`product_name`
UNION
SELECT Avg(`product-sales_detail_50gb`.`discount`) AS `avg_discount_ok`,
`product-sales_detail_50gb`.`product_category` AS `product_category`,
`product-sales_detail_50gb`.`product_name` AS `product_name`
FROM `kyvos_browsing_automationcubes`.`product-sales_detail_50gb`
`Product-Sales_Detail_50GB`
WHERE ( ( `product-sales_detail_50gb`.`product_name` IN ( 'Tripod Stand' ) )
OR ( `product-sales_detail_50gb`.`product_name` IS NULL )
OR ( ( `product-sales_detail_50gb`.`product_name` >= '32 inch' )
AND ( `product-sales_detail_50gb`.`product_name` <= '46 inch' ) )
OR ( ( `product-sales_detail_50gb`.`product_name` >=
'Connector Cables and Ax.'
)
AND ( `product-sales_detail_50gb`.`product_name` <=
'Lens Attachment' )
)
OR ( ( `product-sales_detail_50gb`.`product_name` >= 'Lens Kit' )
AND ( `product-sales_detail_50gb`.`product_name` <= 'TV Remote' )
) )
GROUP BY `product-sales_detail_50gb`.`product_category`,
`product-sales_detail_50gb`.`product_name`;
/* Example of how to use UNION/UNION ALL and and aggregated SQL query*/
/* Case#7 */
SELECT `country`,
count(*)
FROM `kyvos_browsing_automationcubes`.`ORDER date`
WHERE country IN("Albania",
"Azerbaijan",
"Cuba",
"US",
"India")
GROUP BY `country`
EXCEPT
SELECT `country`,
count(*)
FROM `kyvos_browsing_automationcubes`.`ORDER date`
WHERE country IN("US",
"India")
GROUP BY `country`
EXCEPT
SELECT `country`,
count(*)
FROM `kyvos_browsing_automationcubes`.`ORDER date`
WHERE `country` ='US'
GROUP BY `country`;
/* Example of how to use Except clause */
/* Case#8 */
SELECT `a`.`country`,
`a`.`state`,
`a`.`region`,
`a`.`city`,
Sum(`a`.`gbp_rt`)
FROM `kyvos_browsing_automationcubes`.`order date` `a`
WHERE EXISTS (SELECT `b`.`region`
FROM `kyvos_browsing_automationcubes`.`order date` `b`
WHERE `a`.`country` = `b`.`country`
AND `a`.`country` = 'Afghanistan'
GROUP BY `b`.`region`)
GROUP BY `a`.`country`,
`a`.`state`,
`a`.`city`,
`a`.`region`;
SELECT `a`.`country`,
`a`.`state`,
`a`.`region`,
`a`.`city`,
Sum(`a`.`gbp_rt`)
FROM `kyvos_browsing_automationcubes`.`order date` `a`
WHERE NOT EXISTS (SELECT `b`.`region`
FROM `kyvos_browsing_automationcubes`.`order date` `b`
WHERE `b`.`country` = 'US'
GROUP BY `b`.`region`)
GROUP BY `a`.`country`,
`a`.`state`,
`a`.`city`,
`a`.`region`;
/* Example of how to use Exists clause */
/* Case#9 */
SELECT `country`,
Count(*)
FROM `kyvos_browsing_automationcubes`.`order date`
WHERE country IN( "US", "India" )
GROUP BY `country`
INTERSECT
SELECT `country`,
Count(*)
FROM `kyvos_browsing_automationcubes`.`order date`
WHERE country IN( "US", "India" )
GROUP BY `country`
/* Example of how to use intersect clause */
Types of queries that do not work with Kyvos.
Examples of non-supported queries Expand source
/*============================================================================
################################################################################
SQL Query examples for not working cases
################################################################################
==============================================================================*/
/* Case#1 */
SELECT *
FROM `kyvos_browsing_automationcubes`.`order date` `Order Date`
/* Note: Could not execute query as query is not supported on aggregated cube data. */
/* Case#2 */
SELECT Max(`order date`.`category`) AS `category`
FROM `kyvos_browsing_automationcubes`.`order date` `Order Date`
ORDER BY `order date`.`category`
/* Note: Could not execute query as query is not supported on aggregated cube data. GROUP BY `category` is not used on used dimension .you cannot use a measure as a dimension and dimension fields as measures unless they are defined in the cube separately. */
/* Case#3 */
SELECT Sum(`order date`.`category`) AS `category`
FROM `kyvos_browsing_automationcubes`.`order date` `Order Date`
GROUP BY `order date`.`category`
ORDER BY `order date`.`category`
/* Note: Summary function 'Sum' is not supported on dimension column:[category].*/
/* Case#4 */
SELECT Sum(( CASE
WHEN ( `retail_distribution_cube`.`price status` = 'Markdown' )
THEN 1
ELSE -1
end )) AS
`temp_tc___2981869157__0_`,
Min(`retail_distribution_cube`.`price status`) AS
`temp_tc___4115986948__0_`,
`retail_distribution_cube`.`price status` AS `price_status`
FROM `kyvos_browsing_automationcubes`.`retail_distribution_cube`
`Retail_Distribution_Cube`
GROUP BY `retail_distribution_cube`.`price status`
/* Note: Constant value(1) other than zero is not supported in expression. */
/* Case#5 */
SELECT COUNT(DATEDIFF(TO_DATE(CAST(DATE_ADD(NEXT_DAY(CAST(CONCAT(DATE_ADD(CAST(`order_date`.`orderdate` AS TIMESTAMP), CAST(7 * -1 AS INT)), SUBSTR(CAST(CAST(`order_date`.`orderdate` AS TIMESTAMP) AS TIMESTAMP), 11)) AS TIMESTAMP),'SU'),-7) AS DATE)), TO_DATE(`order_date`.`orderdate`))) AS `temp_calculation_313844629194178560_10736926100`, SUM(DATEDIFF(TO_DATE(CAST(DATE_ADD(NEXT_DAY(CAST(CONCAT(DATE_ADD(CAST(`order_date`.`orderdate` AS TIMESTAMP), CAST(7 * -1 AS INT)), SUBSTR(CAST(CAST(`order_date`.`orderdate` AS TIMESTAMP) AS TIMESTAMP), 11)) AS TIMESTAMP),'SU'),-7) AS DATE)), TO_DATE(`order_date`.`orderdate`))) AS `temp_calculation_313844629194178560_37800143780`, COUNT(1) AS `x_alias_0` FROM `tableautest`.`order date` `order_date` HAVING (COUNT(1) > 0)
/* Note: Summary function 'Count' is not supported on dimension column:[orderdate].*/
/* Case#6 */
SELECT a11.country country,
Count(a11.citycount) citycount
FROM `Kyvos_Browsing_AutomationCubes`.`Order Date` orderdate
JOIN (SELECT porderdate.country country
FROM (SELECT orderdate.country
country,
Rank ()
OVER(
ORDER BY Count(orderdate.citycount) DESC)
citycount
FROM `Kyvos_Browsing_AutomationCubes`.`Order Date`
GROUP BY orderdate.country) porderdate
WHERE ( porderdate.citycount <= 10 )) pa12
ON ( orderdate.country = pa12.country )
GROUP BY orderdate.country
/* Note: Window functions (Rank) are not supported.*/
/* Case#7 */
SELECT Min(Year(`order_date1`.`orderdate`)) AS `temp_tc___561953214__0_`,
Count(1) AS `x__alias__1`
FROM `kyvos_dataaccuracycubes`.`order date` `order_date1`
FULL OUTER JOIN (SELECT `order_date1`.`productname` AS `productname`,
Max(`order_date1`.`max sales`) AS `x__alias__0`
FROM `kyvos_dataaccuracycubes`.`order date` `order_date1`
WHERE ( NOT ( `order_date1`.`productname` IS NULL ) )
GROUP BY `order_date1`.`productname`
ORDER BY `x__alias__0` ASC,
`productname` ASC
LIMIT 100) `t0`
ON ( `order_date1`.`productname` = `t0`.`productname` )
HAVING ( Count(1) > 0 )
/* Note: Could not execute query as query with 'FullOuter' join is not supported on aggregated cube data. */
/* Case#8 */
SELECT Min(Year(`order_date1`.`orderdate`)) AS `temp_tc___561953214__0_`,
Count(1) AS `x__alias__1`
FROM `kyvos_dataaccuracycubes`.`order date` `order_date1`
RIGHT OUTER JOIN (SELECT `order_date1`.`productname` AS `productname`,
Max(`order_date1`.`max sales`) AS `x__alias__0`
FROM `kyvos_dataaccuracycubes`.`order date` `order_date1`
WHERE ( NOT ( `order_date1`.`productname` IS NULL ) )
GROUP BY `order_date1`.`productname`
ORDER BY `x__alias__0` ASC,
`productname` ASC
LIMIT 100) `t0`
ON ( `order_date1`.`productname` = `t0`.`productname` )
HAVING ( Count(1) > 0 )
/* Note: Could not execute query as query with 'RightOuter' join is not supported on aggregated cube data. */
/* Case#9 */
SELECT Min(Year(`customer`.`orderdate`)) AS `temp_tc___561953214__0_`,
Count(1) AS `x__alias__1`
FROM `kyvos_dataaccuracycubes`.`order date` `customer`
LEFT JOIN (SELECT `customer`.`productname` AS `productname`,
Max(`customer`.`max sales`) AS `x__alias__0`
FROM `kyvos_dataaccuracycubes`.`customer` `customer`
WHERE ( NOT ( `customer`.`productname` IS NULL ) )
GROUP BY `customer`.`productname`
ORDER BY `x__alias__0` ASC,
`productname` ASC
LIMIT 100) `t0`
ON ( `customer`.`productname` = `t0`.`productname` )
HAVING ( Count(1) > 0 )
/* Note: SQL query over multiple cubes is not supported. */
- No labels