KYLIN-2314 make CI cube snowflake and add test queries
Project: http://git-wip-us.apache.org/repos/asf/kylin/repo Commit: http://git-wip-us.apache.org/repos/asf/kylin/commit/73fbbb90 Tree: http://git-wip-us.apache.org/repos/asf/kylin/tree/73fbbb90 Diff: http://git-wip-us.apache.org/repos/asf/kylin/diff/73fbbb90 Branch: refs/heads/master Commit: 73fbbb90e7cb0618bd27e146c89463cfcfb97e4b Parents: c6c6719 Author: Yang Li <liy...@apache.org> Authored: Sat Dec 24 15:34:48 2016 +0800 Committer: Yang Li <liy...@apache.org> Committed: Sat Dec 24 16:47:17 2016 +0800 ---------------------------------------------------------------------- .../kylin/provision/BuildCubeWithStream.java | 10 +++++++-- .../apache/kylin/query/ITKylinQueryTest.java | 5 +++++ .../resources/query/sql_snowflake/query01.sql | 23 ++++++++++++++++++++ .../resources/query/sql_snowflake/query02.sql | 13 +++++++++++ .../resources/query/sql_snowflake/query03.sql | 14 ++++++++++++ .../resources/query/sql_snowflake/query04.sql | 19 ++++++++++++++++ .../resources/query/sql_snowflake/query05.sql | 20 +++++++++++++++++ .../resources/query/sql_snowflake/query06.sql | 21 ++++++++++++++++++ 8 files changed, 123 insertions(+), 2 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/kylin/blob/73fbbb90/kylin-it/src/test/java/org/apache/kylin/provision/BuildCubeWithStream.java ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/java/org/apache/kylin/provision/BuildCubeWithStream.java b/kylin-it/src/test/java/org/apache/kylin/provision/BuildCubeWithStream.java index 8abb84c..194ea35 100644 --- a/kylin-it/src/test/java/org/apache/kylin/provision/BuildCubeWithStream.java +++ b/kylin-it/src/test/java/org/apache/kylin/provision/BuildCubeWithStream.java @@ -311,6 +311,9 @@ public class BuildCubeWithStream { } public static void main(String[] args) throws Exception { + long start = System.currentTimeMillis(); + int exitCode = 0; + BuildCubeWithStream buildCubeWithStream = null; try { beforeClass(); @@ -320,11 +323,14 @@ public class BuildCubeWithStream { logger.info("Build is done"); buildCubeWithStream.cleanup(); logger.info("Going to exit"); - System.exit(0); } catch (Throwable e) { logger.error("error", e); - System.exit(1); + exitCode = 1; } + long millis = System.currentTimeMillis() - start; + System.out.println("Time elapsed: " + (millis / 1000) + " sec - in " + BuildCubeWithStream.class.getName()); + + System.exit(exitCode); } } http://git-wip-us.apache.org/repos/asf/kylin/blob/73fbbb90/kylin-it/src/test/java/org/apache/kylin/query/ITKylinQueryTest.java ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/java/org/apache/kylin/query/ITKylinQueryTest.java b/kylin-it/src/test/java/org/apache/kylin/query/ITKylinQueryTest.java index e86afa9..4329141 100644 --- a/kylin-it/src/test/java/org/apache/kylin/query/ITKylinQueryTest.java +++ b/kylin-it/src/test/java/org/apache/kylin/query/ITKylinQueryTest.java @@ -189,6 +189,11 @@ public class ITKylinQueryTest extends KylinTestBase { } @Test + public void testSnowflakeQuery() throws Exception { + execAndCompQuery(getQueryFolderPrefix() + "src/test/resources/query/sql_snowflake", null, true); + } + + @Test public void testDateTimeQuery() throws Exception { execAndCompQuery(getQueryFolderPrefix() + "src/test/resources/query/sql_datetime", null, true); } http://git-wip-us.apache.org/repos/asf/kylin/blob/73fbbb90/kylin-it/src/test/resources/query/sql_snowflake/query01.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_snowflake/query01.sql b/kylin-it/src/test/resources/query/sql_snowflake/query01.sql new file mode 100644 index 0000000..e37d8f6 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_snowflake/query01.sql @@ -0,0 +1,23 @@ +SELECT + +count(*) as cnt + +FROM TEST_KYLIN_FACT as TEST_KYLIN_FACT +INNER JOIN TEST_ORDER as TEST_ORDER +ON TEST_KYLIN_FACT.ORDER_ID = TEST_ORDER.ORDER_ID +INNER JOIN TEST_ACCOUNT as BUYER_ACCOUNT +ON TEST_ORDER.BUYER_ID = BUYER_ACCOUNT.ACCOUNT_ID +INNER JOIN TEST_ACCOUNT as SELLER_ACCOUNT +ON TEST_KYLIN_FACT.SELLER_ID = SELLER_ACCOUNT.ACCOUNT_ID +INNER JOIN EDW.TEST_CAL_DT as TEST_CAL_DT +ON TEST_KYLIN_FACT.CAL_DT = TEST_CAL_DT.CAL_DT +INNER JOIN TEST_CATEGORY_GROUPINGS as TEST_CATEGORY_GROUPINGS +ON TEST_KYLIN_FACT.LEAF_CATEG_ID = TEST_CATEGORY_GROUPINGS.LEAF_CATEG_ID AND TEST_KYLIN_FACT.LSTG_SITE_ID = TEST_CATEGORY_GROUPINGS.SITE_ID +INNER JOIN EDW.TEST_SITES as TEST_SITES +ON TEST_KYLIN_FACT.LSTG_SITE_ID = TEST_SITES.SITE_ID +INNER JOIN EDW.TEST_SELLER_TYPE_DIM as TEST_SELLER_TYPE_DIM +ON TEST_KYLIN_FACT.SLR_SEGMENT_CD = TEST_SELLER_TYPE_DIM.SELLER_TYPE_CD +INNER JOIN TEST_COUNTRY as BUYER_COUNTRY +ON BUYER_ACCOUNT.ACCOUNT_COUNTRY = BUYER_COUNTRY.COUNTRY +INNER JOIN TEST_COUNTRY as SELLER_COUNTRY +ON SELLER_ACCOUNT.ACCOUNT_COUNTRY = SELLER_COUNTRY.COUNTRY http://git-wip-us.apache.org/repos/asf/kylin/blob/73fbbb90/kylin-it/src/test/resources/query/sql_snowflake/query02.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_snowflake/query02.sql b/kylin-it/src/test/resources/query/sql_snowflake/query02.sql new file mode 100644 index 0000000..3ec55bf --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_snowflake/query02.sql @@ -0,0 +1,13 @@ +SELECT + +count(*) as cnt, SELLER_COUNTRY.NAME + +FROM TEST_KYLIN_FACT as TEST_KYLIN_FACT +INNER JOIN TEST_ACCOUNT as SELLER_ACCOUNT +ON TEST_KYLIN_FACT.SELLER_ID = SELLER_ACCOUNT.ACCOUNT_ID +INNER JOIN TEST_CATEGORY_GROUPINGS as TEST_CATEGORY_GROUPINGS +ON TEST_KYLIN_FACT.LEAF_CATEG_ID = TEST_CATEGORY_GROUPINGS.LEAF_CATEG_ID AND TEST_KYLIN_FACT.LSTG_SITE_ID = TEST_CATEGORY_GROUPINGS.SITE_ID +INNER JOIN TEST_COUNTRY as SELLER_COUNTRY +ON SELLER_ACCOUNT.ACCOUNT_COUNTRY = SELLER_COUNTRY.COUNTRY + +group by SELLER_COUNTRY.NAME \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/73fbbb90/kylin-it/src/test/resources/query/sql_snowflake/query03.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_snowflake/query03.sql b/kylin-it/src/test/resources/query/sql_snowflake/query03.sql new file mode 100644 index 0000000..0c2ae5f --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_snowflake/query03.sql @@ -0,0 +1,14 @@ +SELECT + +count(*) as cnt, sum(price) as sum_price, SELLER_COUNTRY.NAME + +FROM TEST_KYLIN_FACT as TEST_KYLIN_FACT +INNER JOIN TEST_ACCOUNT as SELLER_ACCOUNT +ON TEST_KYLIN_FACT.SELLER_ID = SELLER_ACCOUNT.ACCOUNT_ID +INNER JOIN TEST_CATEGORY_GROUPINGS as TEST_CATEGORY_GROUPINGS +ON TEST_KYLIN_FACT.LEAF_CATEG_ID = TEST_CATEGORY_GROUPINGS.LEAF_CATEG_ID AND TEST_KYLIN_FACT.LSTG_SITE_ID = TEST_CATEGORY_GROUPINGS.SITE_ID +INNER JOIN TEST_COUNTRY as SELLER_COUNTRY +ON SELLER_ACCOUNT.ACCOUNT_COUNTRY = SELLER_COUNTRY.COUNTRY + +where SELLER_ACCOUNT.ACCOUNT_SELLER_LEVEL=1 +group by SELLER_COUNTRY.NAME \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/73fbbb90/kylin-it/src/test/resources/query/sql_snowflake/query04.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_snowflake/query04.sql b/kylin-it/src/test/resources/query/sql_snowflake/query04.sql new file mode 100644 index 0000000..866da5e --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_snowflake/query04.sql @@ -0,0 +1,19 @@ +SELECT + +count(*) as cnt + +FROM TEST_KYLIN_FACT as TEST_KYLIN_FACT +INNER JOIN TEST_ORDER as TEST_ORDER +ON TEST_KYLIN_FACT.ORDER_ID = TEST_ORDER.ORDER_ID +INNER JOIN TEST_ACCOUNT as BUYER_ACCOUNT +ON TEST_ORDER.BUYER_ID = BUYER_ACCOUNT.ACCOUNT_ID +INNER JOIN TEST_ACCOUNT as SELLER_ACCOUNT +ON TEST_KYLIN_FACT.SELLER_ID = SELLER_ACCOUNT.ACCOUNT_ID +INNER JOIN TEST_CATEGORY_GROUPINGS as TEST_CATEGORY_GROUPINGS +ON TEST_KYLIN_FACT.LEAF_CATEG_ID = TEST_CATEGORY_GROUPINGS.LEAF_CATEG_ID AND TEST_KYLIN_FACT.LSTG_SITE_ID = TEST_CATEGORY_GROUPINGS.SITE_ID +INNER JOIN TEST_COUNTRY as BUYER_COUNTRY +ON BUYER_ACCOUNT.ACCOUNT_COUNTRY = BUYER_COUNTRY.COUNTRY +INNER JOIN TEST_COUNTRY as SELLER_COUNTRY +ON SELLER_ACCOUNT.ACCOUNT_COUNTRY = SELLER_COUNTRY.COUNTRY + +where BUYER_ACCOUNT.ACCOUNT_COUNTRY='CN' \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/73fbbb90/kylin-it/src/test/resources/query/sql_snowflake/query05.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_snowflake/query05.sql b/kylin-it/src/test/resources/query/sql_snowflake/query05.sql new file mode 100644 index 0000000..31a1032 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_snowflake/query05.sql @@ -0,0 +1,20 @@ +SELECT + +LSTG_FORMAT_NAME, count(*) as cnt, sum(price) as sum_price + +FROM TEST_KYLIN_FACT as TEST_KYLIN_FACT +INNER JOIN TEST_ORDER as TEST_ORDER +ON TEST_KYLIN_FACT.ORDER_ID = TEST_ORDER.ORDER_ID +INNER JOIN TEST_ACCOUNT as BUYER_ACCOUNT +ON TEST_ORDER.BUYER_ID = BUYER_ACCOUNT.ACCOUNT_ID +INNER JOIN TEST_ACCOUNT as SELLER_ACCOUNT +ON TEST_KYLIN_FACT.SELLER_ID = SELLER_ACCOUNT.ACCOUNT_ID +INNER JOIN TEST_CATEGORY_GROUPINGS as TEST_CATEGORY_GROUPINGS +ON TEST_KYLIN_FACT.LEAF_CATEG_ID = TEST_CATEGORY_GROUPINGS.LEAF_CATEG_ID AND TEST_KYLIN_FACT.LSTG_SITE_ID = TEST_CATEGORY_GROUPINGS.SITE_ID +INNER JOIN TEST_COUNTRY as BUYER_COUNTRY +ON BUYER_ACCOUNT.ACCOUNT_COUNTRY = BUYER_COUNTRY.COUNTRY +INNER JOIN TEST_COUNTRY as SELLER_COUNTRY +ON SELLER_ACCOUNT.ACCOUNT_COUNTRY = SELLER_COUNTRY.COUNTRY + +where BUYER_COUNTRY.NAME='China' and SELLER_COUNTRY.NAME='Japan' +group by LSTG_FORMAT_NAME \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/73fbbb90/kylin-it/src/test/resources/query/sql_snowflake/query06.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_snowflake/query06.sql b/kylin-it/src/test/resources/query/sql_snowflake/query06.sql new file mode 100644 index 0000000..440c6d5 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_snowflake/query06.sql @@ -0,0 +1,21 @@ +SELECT + +BUYER_ACCOUNT.ACCOUNT_ID, BUYER_ACCOUNT.ACCOUNT_CONTACT, count(*) as cnt, sum(price) as sum_price + +FROM TEST_KYLIN_FACT as TEST_KYLIN_FACT +INNER JOIN TEST_ORDER as TEST_ORDER +ON TEST_KYLIN_FACT.ORDER_ID = TEST_ORDER.ORDER_ID +INNER JOIN TEST_ACCOUNT as BUYER_ACCOUNT +ON TEST_ORDER.BUYER_ID = BUYER_ACCOUNT.ACCOUNT_ID +INNER JOIN TEST_ACCOUNT as SELLER_ACCOUNT +ON TEST_KYLIN_FACT.SELLER_ID = SELLER_ACCOUNT.ACCOUNT_ID +INNER JOIN TEST_CATEGORY_GROUPINGS as TEST_CATEGORY_GROUPINGS +ON TEST_KYLIN_FACT.LEAF_CATEG_ID = TEST_CATEGORY_GROUPINGS.LEAF_CATEG_ID AND TEST_KYLIN_FACT.LSTG_SITE_ID = TEST_CATEGORY_GROUPINGS.SITE_ID +INNER JOIN TEST_COUNTRY as BUYER_COUNTRY +ON BUYER_ACCOUNT.ACCOUNT_COUNTRY = BUYER_COUNTRY.COUNTRY +INNER JOIN TEST_COUNTRY as SELLER_COUNTRY +ON SELLER_ACCOUNT.ACCOUNT_COUNTRY = SELLER_COUNTRY.COUNTRY + +where BUYER_ACCOUNT.ACCOUNT_BUYER_LEVEL > 2 and SELLER_COUNTRY.NAME = 'France' + and CAL_DT > '2013-01-01' +group by BUYER_ACCOUNT.ACCOUNT_ID, BUYER_ACCOUNT.ACCOUNT_CONTACT \ No newline at end of file