http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query21.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query21.sql b/kylin-it/src/test/resources/query/sql_tableau/query21.sql new file mode 100644 index 0000000..3e3208b --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query21.sql @@ -0,0 +1,27 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + + select test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt,sum(test_kylin_fact.price) as GMV + , count(*) as TRANS_CNT + from test_kylin_fact + inner JOIN EDW.TEST_CAL_DT AS TEST_CAL_DT + ON test_kylin_fact.cal_dt = test_cal_dt.cal_dt + where test_cal_dt.week_beg_dt between DATE '2013-05-01' and DATE '2013-08-01' + group by test_kylin_fact.lstg_format_name, test_cal_dt.week_beg_dt + having sum(price)>500 + limit 1
http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query22.sql.disabled ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query22.sql.disabled b/kylin-it/src/test/resources/query/sql_tableau/query22.sql.disabled new file mode 100644 index 0000000..17ec731 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query22.sql.disabled @@ -0,0 +1,28 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select fact.cal_dt, sum(fact.price) from test_kylin_fact fact +left join test_cal_dt cal + on fact.cal_dt=cal.cal_dt +inner join +( + select test_kylin_fact.cal_dt, count(1) from test_kylin_fact left join test_cal_dt + on test_kylin_fact.cal_dt=test_cal_dt.cal_dt group by test_kylin_fact.cal_dt order by 2 desc limit 2 +) cal_2 + on fact.cal_dt = cal_2.cal_dt +group by fact.cal_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query23.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query23.sql b/kylin-it/src/test/resources/query/sql_tableau/query23.sql new file mode 100644 index 0000000..10430ab --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query23.sql @@ -0,0 +1,23 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + + +select fact.cal_dt, sum(fact.price) from test_kylin_fact fact +left join EDW.TEST_CAL_DT cal on fact.cal_dt=cal.cal_dt +where cal.cal_dt = date '2012-05-17' or cal.cal_dt = date '2013-05-17' +group by fact.cal_dt http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query24.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query24.sql b/kylin-it/src/test/resources/query/sql_tableau/query24.sql new file mode 100644 index 0000000..29a69aa --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query24.sql @@ -0,0 +1,24 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_kylin_fact.cal_dt, count(1) as cnt_1 +from test_kylin_fact +left join EDW.TEST_CAL_DT AS TEST_CAL_DT on test_kylin_fact.cal_dt=test_cal_dt.cal_dt +group by test_kylin_fact.cal_dt +order by 2 desc +limit 3 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query25.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query25.sql b/kylin-it/src/test/resources/query/sql_tableau/query25.sql new file mode 100644 index 0000000..cdc98b7 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query25.sql @@ -0,0 +1,28 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT "TEST_KYLIN_FACT"."CAL_DT", SUM("TEST_KYLIN_FACT"."PRICE") AS "sum_PRICE_ok" + FROM "TEST_KYLIN_FACT" + INNER JOIN "EDW"."TEST_CAL_DT" AS "TEST_CAL_DT" ON ("TEST_KYLIN_FACT"."CAL_DT" = "TEST_CAL_DT"."CAL_DT") + INNER JOIN ( + SELECT COUNT(1) AS "XTableau_join_flag", + SUM("TEST_KYLIN_FACT"."PRICE") AS "X__alias__A", + "TEST_KYLIN_FACT"."CAL_DT" AS "none_CAL_DT_ok" FROM "TEST_KYLIN_FACT" + INNER JOIN "EDW"."TEST_CAL_DT" AS "TEST_CAL_DT" ON ("TEST_KYLIN_FACT"."CAL_DT" = "TEST_CAL_DT"."CAL_DT") + GROUP BY "TEST_KYLIN_FACT"."CAL_DT" ORDER BY 2 DESC LIMIT 10 ) "t0" ON ("TEST_KYLIN_FACT"."CAL_DT" = "t0"."none_CAL_DT_ok") + GROUP BY "TEST_KYLIN_FACT"."CAL_DT" http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query27.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query27.sql b/kylin-it/src/test/resources/query/sql_tableau/query27.sql new file mode 100644 index 0000000..beeb168 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query27.sql @@ -0,0 +1,24 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT "TEST_KYLIN_FACT"."CAL_DT", SUM("TEST_KYLIN_FACT"."PRICE") AS "sum_PRICE_ok" FROM "TEST_KYLIN_FACT" + INNER JOIN ( + SELECT COUNT(1) AS "XTableau_join_flag", SUM("TEST_KYLIN_FACT"."PRICE") AS "X__alias__A", "TEST_KYLIN_FACT"."CAL_DT" AS "none_CAL_DT_ok" FROM "TEST_KYLIN_FACT" + GROUP BY "TEST_KYLIN_FACT"."CAL_DT" ORDER BY 2 DESC LIMIT 7 ) + + "t0" ON ("TEST_KYLIN_FACT"."CAL_DT" = "t0"."none_CAL_DT_ok") GROUP BY "TEST_KYLIN_FACT"."CAL_DT" http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query28.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query28.sql b/kylin-it/src/test/resources/query/sql_tableau/query28.sql new file mode 100644 index 0000000..48cc342 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query28.sql @@ -0,0 +1,23 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +-- This query don't result exact same average number as H2 DB + +select lstg_format_name, avg(price) as GMV + from test_kylin_fact + group by lstg_format_name http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_tableau/query67.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_tableau/query67.sql b/kylin-it/src/test/resources/query/sql_tableau/query67.sql new file mode 100644 index 0000000..25cc110 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_tableau/query67.sql @@ -0,0 +1,23 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT (CASE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" WHEN 'Auction' THEN '111' ELSE '222' END) AS "LSTG_FORMAT_NAME__group_", + SUM("TEST_KYLIN_FACT"."PRICE") AS "sum_PRICE_ok" +FROM "TEST_KYLIN_FACT" "TEST_KYLIN_FACT" +--group by (CASE "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" WHEN 'Auction' THEN '111' ELSE '222' END) ORDER BY 1 ASC +GROUP BY "TEST_KYLIN_FACT"."LSTG_FORMAT_NAME" ORDER BY 1 ASC http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_topn/query81.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_topn/query81.sql b/kylin-it/src/test/resources/query/sql_topn/query81.sql new file mode 100644 index 0000000..93868e7 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_topn/query81.sql @@ -0,0 +1,28 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_cal_dt.week_beg_dt, sum(price) as GMV + from test_kylin_fact +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 + 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 + where test_cal_dt.week_beg_dt between DATE '2013-09-01' and DATE '2013-10-01' and (lstg_format_name='FP-GTC' or 'a' = 'b') + group by test_cal_dt.week_beg_dt \ No newline at end of file http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_topn/query82.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_topn/query82.sql b/kylin-it/src/test/resources/query/sql_topn/query82.sql new file mode 100644 index 0000000..57e9de0 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_topn/query82.sql @@ -0,0 +1,27 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select test_kylin_fact.cal_dt, seller_id + FROM test_kylin_fact + 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 + 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 + group by + test_kylin_fact.cal_dt, test_kylin_fact.seller_id order by sum(test_kylin_fact.price) desc limit 20 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_topn/query83.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_topn/query83.sql b/kylin-it/src/test/resources/query/sql_topn/query83.sql new file mode 100644 index 0000000..514beb4 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_topn/query83.sql @@ -0,0 +1,29 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT + seller_id + FROM test_kylin_fact + 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 + 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 + where test_kylin_fact.cal_dt < DATE '2013-02-01' + group by + test_kylin_fact.seller_id order by sum(test_kylin_fact.price) desc limit 20 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_verifyCount/query04.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_verifyCount/query04.sql b/kylin-it/src/test/resources/query/sql_verifyCount/query04.sql new file mode 100644 index 0000000..9d3e409 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_verifyCount/query04.sql @@ -0,0 +1,19 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select * from test_kylin_fact limit 100 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_verifyCount/query04.sql.expected ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_verifyCount/query04.sql.expected b/kylin-it/src/test/resources/query/sql_verifyCount/query04.sql.expected new file mode 100644 index 0000000..29d6383 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_verifyCount/query04.sql.expected @@ -0,0 +1 @@ +100 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_verifyCount/query05.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_verifyCount/query05.sql b/kylin-it/src/test/resources/query/sql_verifyCount/query05.sql new file mode 100644 index 0000000..753a85a --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_verifyCount/query05.sql @@ -0,0 +1,19 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select price from test_kylin_fact limit 100 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_verifyCount/query05.sql.expected ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_verifyCount/query05.sql.expected b/kylin-it/src/test/resources/query/sql_verifyCount/query05.sql.expected new file mode 100644 index 0000000..29d6383 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_verifyCount/query05.sql.expected @@ -0,0 +1 @@ +100 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_verifyCount/query06.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_verifyCount/query06.sql b/kylin-it/src/test/resources/query/sql_verifyCount/query06.sql new file mode 100644 index 0000000..39578a4 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_verifyCount/query06.sql @@ -0,0 +1,19 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select lstg_format_name from test_kylin_fact limit 100 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_verifyCount/query06.sql.expected ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_verifyCount/query06.sql.expected b/kylin-it/src/test/resources/query/sql_verifyCount/query06.sql.expected new file mode 100644 index 0000000..29d6383 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_verifyCount/query06.sql.expected @@ -0,0 +1 @@ +100 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_verifyCount/query07.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_verifyCount/query07.sql b/kylin-it/src/test/resources/query/sql_verifyCount/query07.sql new file mode 100644 index 0000000..0afb493 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_verifyCount/query07.sql @@ -0,0 +1,19 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select price,lstg_format_name from test_kylin_fact limit 100 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_verifyCount/query07.sql.expected ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_verifyCount/query07.sql.expected b/kylin-it/src/test/resources/query/sql_verifyCount/query07.sql.expected new file mode 100644 index 0000000..29d6383 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_verifyCount/query07.sql.expected @@ -0,0 +1 @@ +100 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_verifyCount/query08.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_verifyCount/query08.sql b/kylin-it/src/test/resources/query/sql_verifyCount/query08.sql new file mode 100644 index 0000000..0f467f8 --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_verifyCount/query08.sql @@ -0,0 +1,19 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +select lstg_format_name,sum(price) as sp from test_kylin_fact group by lstg_format_name limit 1 offset 2 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/sql_verifyCount/query08.sql.expected ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/sql_verifyCount/query08.sql.expected b/kylin-it/src/test/resources/query/sql_verifyCount/query08.sql.expected new file mode 100644 index 0000000..d00491f --- /dev/null +++ b/kylin-it/src/test/resources/query/sql_verifyCount/query08.sql.expected @@ -0,0 +1 @@ +1 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/tableau_probing/query01.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/tableau_probing/query01.sql b/kylin-it/src/test/resources/query/tableau_probing/query01.sql new file mode 100644 index 0000000..11fce3e --- /dev/null +++ b/kylin-it/src/test/resources/query/tableau_probing/query01.sql @@ -0,0 +1,19 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT 1 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/tableau_probing/query02.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/tableau_probing/query02.sql b/kylin-it/src/test/resources/query/tableau_probing/query02.sql new file mode 100644 index 0000000..f2f9f42 --- /dev/null +++ b/kylin-it/src/test/resources/query/tableau_probing/query02.sql @@ -0,0 +1,19 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT 1 AS "COL" http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/tableau_probing/query03.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/tableau_probing/query03.sql b/kylin-it/src/test/resources/query/tableau_probing/query03.sql new file mode 100644 index 0000000..19613c9b --- /dev/null +++ b/kylin-it/src/test/resources/query/tableau_probing/query03.sql @@ -0,0 +1,19 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT "COL" FROM (SELECT 1 AS "COL") AS "SUBQUERY" http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/tableau_probing/query04.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/tableau_probing/query04.sql b/kylin-it/src/test/resources/query/tableau_probing/query04.sql new file mode 100644 index 0000000..da0d3a2 --- /dev/null +++ b/kylin-it/src/test/resources/query/tableau_probing/query04.sql @@ -0,0 +1,19 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT TOP 1 "COL" FROM (SELECT 1 AS "COL") AS "CHECKTOP" http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/tableau_probing/query05.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/tableau_probing/query05.sql b/kylin-it/src/test/resources/query/tableau_probing/query05.sql new file mode 100644 index 0000000..7fae0d7 --- /dev/null +++ b/kylin-it/src/test/resources/query/tableau_probing/query05.sql @@ -0,0 +1,19 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT "COL" FROM (SELECT 1 AS "COL") AS "CHECKTOP" LIMIT 1 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/tableau_probing/query06.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/tableau_probing/query06.sql b/kylin-it/src/test/resources/query/tableau_probing/query06.sql new file mode 100644 index 0000000..1a609bb --- /dev/null +++ b/kylin-it/src/test/resources/query/tableau_probing/query06.sql @@ -0,0 +1,19 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT "SUBCOL" AS "COL" FROM ( SELECT 1 AS "SUBCOL" ) "SUBQUERY" GROUP BY 1 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/tableau_probing/query07.sql ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/tableau_probing/query07.sql b/kylin-it/src/test/resources/query/tableau_probing/query07.sql new file mode 100644 index 0000000..e2722d6 --- /dev/null +++ b/kylin-it/src/test/resources/query/tableau_probing/query07.sql @@ -0,0 +1,19 @@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, software +-- distributed under the License is distributed on an "AS IS" BASIS, +-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +-- See the License for the specific language governing permissions and +-- limitations under the License. +-- + +SELECT "SUBCOL" AS "COL" FROM ( SELECT 1 AS "SUBCOL" ) "SUBQUERY" GROUP BY 2 http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/kylin-it/src/test/resources/query/temp/.gitignore ---------------------------------------------------------------------- diff --git a/kylin-it/src/test/resources/query/temp/.gitignore b/kylin-it/src/test/resources/query/temp/.gitignore new file mode 100644 index 0000000..d1b811b --- /dev/null +++ b/kylin-it/src/test/resources/query/temp/.gitignore @@ -0,0 +1 @@ +*.sql http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/pom.xml ---------------------------------------------------------------------- diff --git a/pom.xml b/pom.xml index fbe5e52..ba87d90 100644 --- a/pom.xml +++ b/pom.xml @@ -627,6 +627,7 @@ <module>jdbc</module> <module>invertedindex</module> <module>assembly</module> + <module>kylin-it</module> </modules> <profiles> @@ -647,6 +648,7 @@ <maxmem>2048m</maxmem> </configuration> </plugin> + <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-surefire-plugin</artifactId> @@ -655,47 +657,22 @@ <reportsDirectory>${project.basedir}/../target/surefire-reports</reportsDirectory> <excludes> <exclude>**/IT*.java</exclude> - - <!--Build cube/II need to be run separately--> - <exclude>**/BuildCube*.java</exclude> - <exclude>**/BuildII*.java</exclude> </excludes> <systemProperties> <property> <name>useSandbox</name> - <value>true</value> + <value>false</value> </property> - </systemProperties> - </configuration> - </plugin> - <plugin> - <groupId>org.apache.maven.plugins</groupId> - <artifactId>maven-failsafe-plugin</artifactId> - <version>2.16</version> - <executions> - <execution> - <id>integration-tests</id> - <phase>integration-test</phase> - <goals> - <goal>integration-test</goal> - <goal>verify</goal> - </goals> - </execution> - </executions> - <configuration> - <excludes> - <exclude>**/*$*</exclude> - <exclude>**/ITKafka*.java</exclude> - - </excludes> - <systemProperties> <property> - <name>useSandbox</name> - <value>true</value> + <name>buildCubeUsingProvidedData</name> + <value>false</value> </property> </systemProperties> + <argLine>-Xms1G -Xmx4G -XX:PermSize=128M -XX:MaxPermSize=512M</argLine> </configuration> </plugin> + + </plugins> </build> </profile> @@ -734,41 +711,6 @@ </executions> </plugin> - <plugin> - <groupId>org.apache.maven.plugins</groupId> - <artifactId>maven-surefire-plugin</artifactId> - <version>2.16</version> - <configuration> - <reportsDirectory>${project.basedir}/../target/surefire-reports</reportsDirectory> - <excludes> - <exclude>**/BuildCubeWithEngineTest.java</exclude> - <exclude>**/BuildIIWithEngineTest.java</exclude> - <exclude>**/SampleCubeSetupTest.java</exclude> - <exclude>**/KylinQueryTest.java</exclude> - <exclude>**/SnapshotManagerTest.java</exclude> - <exclude>**/HiveTableReaderTest.java</exclude> - <exclude>**/TableControllerTest.java</exclude> - - <!--test case under server folder are problematic when tested against minicluster--> - <exclude>**/ServiceTestBase.java</exclude> - <exclude>**/JDBCDriverTest.java</exclude> - <exclude>%regex[.*ControllerTest.*]</exclude> - <exclude>%regex[.*ServiceTest.*]</exclude> - - </excludes> - <systemProperties> - <property> - <name>useSandbox</name> - <value>false</value> - </property> - <property> - <name>buildCubeUsingProvidedData</name> - <value>false</value> - </property> - </systemProperties> - <argLine>-Xms1G -Xmx4G -XX:PermSize=128M -XX:MaxPermSize=512M</argLine> - </configuration> - </plugin> <!-- Apache-RAT checks for files without headers. If run on a messy developer's sandbox, it will fail. This serves as a reminder to only build a release in a clean http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/query/pom.xml ---------------------------------------------------------------------- diff --git a/query/pom.xml b/query/pom.xml index 479c3e1..2d1bea9 100644 --- a/query/pom.xml +++ b/query/pom.xml @@ -17,7 +17,8 @@ limitations under the License. --> -<project xmlns="http://maven.apache.org/POM/4.0.0"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd";> +<project xmlns="http://maven.apache.org/POM/4.0.0"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"; + xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd";> <modelVersion>4.0.0</modelVersion> <!-- The basics. --> <artifactId>kylin-query</artifactId> @@ -52,7 +53,7 @@ <artifactId>kylin-invertedindex</artifactId> <version>${project.parent.version}</version> </dependency> - + <dependency> <groupId>org.apache.calcite</groupId> <artifactId>calcite-linq4j</artifactId> @@ -165,7 +166,7 @@ <artifactId>hive-jdbc</artifactId> <scope>provided</scope> </dependency> - <dependency> + <dependency> <groupId>org.apache.hbase</groupId> <artifactId>hbase-testing-util</artifactId> <version>${hbase-hadoop2.version}</version> @@ -183,64 +184,6 @@ </dependency> </dependencies> - <build> - <plugins> - <!-- experiment "shade" instead of "assembly" - <plugin> - <groupId>org.apache.maven.plugins</groupId> - <artifactId>maven-shade-plugin</artifactId> - <version>2.2</version> - <executions> - <execution> - <phase>package</phase> - <goals> - <goal>shade</goal> - </goals> - <configuration> - <shadedArtifactAttached>true</shadedArtifactAttached> - <shadedClassifierName>jar-with-dependencies</shadedClassifierName> - <transformers> - <transformer implementation="org.apache.maven.plugins.shade.resource.ServicesResourceTransformer" /> - </transformers> - <filters> - <filter> - <artifact>*:*</artifact> - <excludes> - <exclude>META-INF/*.SF</exclude> - <exclude>META-INF/*.DSA</exclude> - <exclude>META-INF/*.RSA</exclude> - </excludes> - </filter> - </filters> - </configuration> - </execution> - </executions> - </plugin> - <plugin> - <artifactId>maven-assembly-plugin</artifactId> - <configuration> - <archive> - <manifest> - <mainClass>fully.qualified.MainClass</mainClass> - </manifest> - </archive> - <descriptorRefs> - <descriptorRef>jar-with-dependencies</descriptorRef> - </descriptorRefs> - </configuration> - <executions> - <execution> - <id>make-assembly</id> - <phase>package</phase> - <goals> - <goal>single</goal> - </goals> - </execution> - </executions> - </plugin> - --> - </plugins> - </build> <profiles> <profile> @@ -309,6 +252,5 @@ </build> </profile> </profiles> - - + </project> http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/query/src/test/java/org/apache/kylin/query/test/H2Database.java ---------------------------------------------------------------------- diff --git a/query/src/test/java/org/apache/kylin/query/test/H2Database.java b/query/src/test/java/org/apache/kylin/query/test/H2Database.java deleted file mode 100644 index cab51d3..0000000 --- a/query/src/test/java/org/apache/kylin/query/test/H2Database.java +++ /dev/null @@ -1,130 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. -*/ - -package org.apache.kylin.query.test; - -import java.io.File; -import java.io.FileOutputStream; -import java.io.IOException; -import java.io.InputStream; -import java.sql.Connection; -import java.sql.SQLException; -import java.sql.Statement; -import java.util.HashMap; -import java.util.Map; - -import org.apache.kylin.common.KylinConfig; -import org.apache.kylin.metadata.MetadataManager; -import org.apache.kylin.metadata.model.ColumnDesc; -import org.apache.kylin.metadata.model.TableDesc; -import org.slf4j.Logger; -import org.slf4j.LoggerFactory; - -public class H2Database { - @SuppressWarnings("unused") - private static final Logger logger = LoggerFactory.getLogger(H2Database.class); - - private static final String[] ALL_TABLES = new String[] { "edw.test_cal_dt", "default.test_category_groupings", "default.test_kylin_fact", "edw.test_seller_type_dim", "edw.test_sites", "default.streaming_table" }; - private static final Map<String, String> javaToH2DataTypeMapping = new HashMap<String, String>(); - - static { - javaToH2DataTypeMapping.put("short", "smallint"); - javaToH2DataTypeMapping.put("long", "bigint"); - javaToH2DataTypeMapping.put("byte", "tinyint"); - javaToH2DataTypeMapping.put("string", "varchar"); - } - - private final Connection h2Connection; - - private final KylinConfig config; - - public H2Database(Connection h2Connection, KylinConfig config) { - this.h2Connection = h2Connection; - this.config = config; - } - - public void loadAllTables() throws SQLException { - for (String tableName : ALL_TABLES) { - loadH2Table(tableName); - } - } - - private void loadH2Table(String tableName) throws SQLException { - MetadataManager metaMgr = MetadataManager.getInstance(config); - TableDesc tableDesc = metaMgr.getTableDesc(tableName.toUpperCase()); - File tempFile = null; - - try { - tempFile = File.createTempFile("tmp_h2", ".csv"); - FileOutputStream tempFileStream = new FileOutputStream(tempFile); - String normalPath = "/data/" + tableDesc.getIdentity() + ".csv"; - InputStream csvStream = metaMgr.getStore().getResource(normalPath).inputStream; - - org.apache.commons.io.IOUtils.copy(csvStream, tempFileStream); - - csvStream.close(); - tempFileStream.close(); - - } catch (IOException e) { - e.printStackTrace(); - } - - String cvsFilePath = tempFile.getPath(); - Statement stmt = h2Connection.createStatement(); - - String createDBSql = "CREATE SCHEMA IF NOT EXISTS DEFAULT;\nCREATE SCHEMA IF NOT EXISTS EDW;\nSET SCHEMA DEFAULT;\n"; - stmt.executeUpdate(createDBSql); - - String sql = generateCreateH2TableSql(tableDesc, cvsFilePath); - stmt.executeUpdate(sql); - - if (tempFile != null) - tempFile.delete(); - } - - private String generateCreateH2TableSql(TableDesc tableDesc, String csvFilePath) { - StringBuilder ddl = new StringBuilder(); - StringBuilder csvColumns = new StringBuilder(); - - ddl.append("CREATE TABLE " + tableDesc.getIdentity() + "\n"); - ddl.append("(" + "\n"); - - for (int i = 0; i < tableDesc.getColumns().length; i++) { - ColumnDesc col = tableDesc.getColumns()[i]; - if (i > 0) { - ddl.append(","); - csvColumns.append(","); - } - ddl.append(col.getName() + " " + getH2DataType((col.getDatatype())) + "\n"); - csvColumns.append(col.getName()); - } - ddl.append(")" + "\n"); - ddl.append("AS SELECT * FROM CSVREAD('" + csvFilePath + "', '" + csvColumns + "', 'charset=UTF-8 fieldSeparator=,');"); - - return ddl.toString(); - } - - private static String getH2DataType(String javaDataType) { - String hiveDataType = javaToH2DataTypeMapping.get(javaDataType.toLowerCase()); - if (hiveDataType == null) { - hiveDataType = javaDataType; - } - return hiveDataType.toLowerCase(); - } - -} http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/query/src/test/java/org/apache/kylin/query/test/ITCombinationTest.java ---------------------------------------------------------------------- diff --git a/query/src/test/java/org/apache/kylin/query/test/ITCombinationTest.java b/query/src/test/java/org/apache/kylin/query/test/ITCombinationTest.java deleted file mode 100644 index cbafc75..0000000 --- a/query/src/test/java/org/apache/kylin/query/test/ITCombinationTest.java +++ /dev/null @@ -1,78 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. -*/ - -package org.apache.kylin.query.test; - -import java.sql.SQLException; -import java.util.Arrays; -import java.util.Collection; - -import org.apache.kylin.storage.hbase.HBaseStorage; -import org.apache.kylin.storage.hbase.cube.v1.coprocessor.observer.ObserverEnabler; -import org.junit.AfterClass; -import org.junit.BeforeClass; -import org.junit.runner.RunWith; -import org.junit.runners.Parameterized; - -/** - */ -@RunWith(Parameterized.class) -public class ITCombinationTest extends ITKylinQueryTest { - - @BeforeClass - public static void setUp() throws SQLException { - System.out.println("setUp in ITCombinationTest"); - } - - @AfterClass - public static void tearDown() { - clean(); - HBaseStorage.overwriteStorageQuery = null; - } - - /** - * return all config combinations, where first setting specifies join type - * (inner or left), and the second setting specifies whether to force using - * coprocessors(on, off or unset). - */ - @Parameterized.Parameters - public static Collection<Object[]> configs() { - // return Arrays.asList(new Object[][] { { "inner", "unset" }, { "left", "unset" }, { "inner", "off" }, { "left", "off" }, { "inner", "on" }, { "left", "on" }, }); - return Arrays.asList(new Object[][] { { "inner", "on", "v2" }, { "left", "on", "v1" }, { "left", "on", "v2" } }); - } - - public ITCombinationTest(String joinType, String coprocessorToggle, String queryEngine) throws Exception { - - ITKylinQueryTest.clean(); - - ITKylinQueryTest.joinType = joinType; - ITKylinQueryTest.setupAll(); - - if (coprocessorToggle.equals("on")) { - ObserverEnabler.forceCoprocessorOn(); - } else if (coprocessorToggle.equals("off")) { - ObserverEnabler.forceCoprocessorOff(); - } else if (coprocessorToggle.equals("unset")) { - // unset - } - - if ("v1".equalsIgnoreCase(queryEngine)) { - HBaseStorage.overwriteStorageQuery = HBaseStorage.v1CubeStorageQuery; - } - } -} http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/query/src/test/java/org/apache/kylin/query/test/ITIIQueryTest.java ---------------------------------------------------------------------- diff --git a/query/src/test/java/org/apache/kylin/query/test/ITIIQueryTest.java b/query/src/test/java/org/apache/kylin/query/test/ITIIQueryTest.java deleted file mode 100644 index 0d0197d..0000000 --- a/query/src/test/java/org/apache/kylin/query/test/ITIIQueryTest.java +++ /dev/null @@ -1,92 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. -*/ - -package org.apache.kylin.query.test; - -import java.util.Arrays; -import java.util.Collection; -import java.util.Map; - -import org.apache.kylin.metadata.realization.RealizationType; -import org.apache.kylin.query.routing.Candidate; -import org.junit.AfterClass; -import org.junit.BeforeClass; -import org.junit.Ignore; -import org.junit.Test; -import org.junit.runner.RunWith; -import org.junit.runners.Parameterized; - -import com.google.common.collect.Maps; - -/** - */ -@RunWith(Parameterized.class) -public class ITIIQueryTest extends ITKylinQueryTest { - @BeforeClass - public static void setUp() throws Exception { - - // give II higher priority than other realizations - Map<RealizationType, Integer> priorities = Maps.newHashMap(); - priorities.put(RealizationType.INVERTED_INDEX, 0); - priorities.put(RealizationType.CUBE, 1); - priorities.put(RealizationType.HYBRID, 1); - Candidate.setPriorities(priorities); - - } - - @AfterClass - public static void tearDown() throws Exception { - ITKylinQueryTest.tearDown();//invoke super class - - Map<RealizationType, Integer> priorities = Maps.newHashMap(); - priorities.put(RealizationType.INVERTED_INDEX, 1); - priorities.put(RealizationType.CUBE, 0); - priorities.put(RealizationType.HYBRID, 0); - Candidate.setPriorities(priorities); - } - - @Parameterized.Parameters - public static Collection<Object[]> configs() { - return Arrays.asList(new Object[][] { { "inner" }, { "left" } }); - } - - public ITIIQueryTest(String joinType) throws Exception { - - ITKylinQueryTest.clean(); - - ITKylinQueryTest.joinType = joinType; - ITKylinQueryTest.setupAll(); - - } - - @Test - public void testSingleRunQuery() throws Exception { - super.testSingleRunQuery(); - } - - @Test - public void testDetailedQuery() throws Exception { - execAndCompQuery("src/test/resources/query/sql_ii", null, true); - } - - @Override - @Test - @Ignore("Skip Precisely Distinct Count Queries for II") - public void testPreciselyDistinctCountQuery() { - } -} http://git-wip-us.apache.org/repos/asf/kylin/blob/1428bbc4/query/src/test/java/org/apache/kylin/query/test/ITKylinQueryTest.java ---------------------------------------------------------------------- diff --git a/query/src/test/java/org/apache/kylin/query/test/ITKylinQueryTest.java b/query/src/test/java/org/apache/kylin/query/test/ITKylinQueryTest.java deleted file mode 100644 index 2b4cf0d..0000000 --- a/query/src/test/java/org/apache/kylin/query/test/ITKylinQueryTest.java +++ /dev/null @@ -1,254 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one - * or more contributor license agreements. See the NOTICE file - * distributed with this work for additional information - * regarding copyright ownership. The ASF licenses this file - * to you under the Apache License, Version 2.0 (the - * "License"); you may not use this file except in compliance - * with the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. -*/ - -package org.apache.kylin.query.test; - -import static org.junit.Assert.assertTrue; - -import java.io.File; -import java.sql.DriverManager; -import java.util.List; -import java.util.Properties; - -import org.apache.commons.lang3.StringUtils; -import org.apache.kylin.common.KylinConfig; -import org.apache.kylin.metadata.project.ProjectInstance; -import org.apache.kylin.query.enumerator.OLAPQuery; -import org.apache.kylin.query.relnode.OLAPContext; -import org.apache.kylin.query.schema.OLAPSchemaFactory; -import org.apache.kylin.storage.hbase.cube.v1.coprocessor.observer.ObserverEnabler; -import org.apache.kylin.storage.hbase.steps.HBaseMetadataTestCase; -import org.dbunit.database.DatabaseConnection; -import org.dbunit.database.IDatabaseConnection; -import org.junit.AfterClass; -import org.junit.BeforeClass; -import org.junit.Ignore; -import org.junit.Test; - -@Ignore("KylinQueryTest is contained by ITCombinationTest") -public class ITKylinQueryTest extends KylinTestBase { - - @BeforeClass - public static void setUp() throws Exception { - printInfo("setUp in KylinQueryTest"); - joinType = "left"; - - setupAll(); - } - - @AfterClass - public static void tearDown() throws Exception { - printInfo("tearDown"); - printInfo("Closing connection..."); - clean(); - } - - protected static void setupAll() throws Exception { - //setup env - HBaseMetadataTestCase.staticCreateTestMetadata(); - config = KylinConfig.getInstanceFromEnv(); - - //setup cube conn - File olapTmp = OLAPSchemaFactory.createTempOLAPJson(ProjectInstance.DEFAULT_PROJECT_NAME, config); - Properties props = new Properties(); - props.setProperty(OLAPQuery.PROP_SCAN_THRESHOLD, "10000"); - cubeConnection = DriverManager.getConnection("jdbc:calcite:model=" + olapTmp.getAbsolutePath(), props); - - //setup h2 - h2Connection = DriverManager.getConnection("jdbc:h2:mem:db" + (h2InstanceCount++), "sa", ""); - // Load H2 Tables (inner join) - H2Database h2DB = new H2Database(h2Connection, config); - h2DB.loadAllTables(); - } - - protected static void clean() { - if (cubeConnection != null) - closeConnection(cubeConnection); - if (h2Connection != null) - closeConnection(h2Connection); - - ObserverEnabler.forceCoprocessorUnset(); - HBaseMetadataTestCase.staticCleanupTestMetadata(); - } - - @Ignore("this is only for debug") - @Test - public void testTempQuery() throws Exception { - execAndCompQuery("src/test/resources/query/temp", null, true); - } - - @Test - public void testSingleRunQuery() throws Exception { - - String queryFileName = "src/test/resources/query/sql_tableau/query20.sql"; - - File sqlFile = new File(queryFileName); - if (sqlFile.exists()) { - runSQL(sqlFile, true, true); - runSQL(sqlFile, true, false); - } - } - - @Test - public void testSingleExecuteQuery() throws Exception { - - String queryFileName = "src/test/resources/query/sql_tableau/query20.sql"; - - File sqlFile = new File(queryFileName); - String sql = getTextFromFile(sqlFile); - IDatabaseConnection kylinConn = new DatabaseConnection(cubeConnection); - - executeQuery(kylinConn, queryFileName, sql, true); - } - - @Ignore - @Test - public void testTableauProbing() throws Exception { - batchExecuteQuery("src/test/resources/query/tableau_probing"); - } - - @Test - public void testCommonQuery() throws Exception { - execAndCompQuery("src/test/resources/query/sql", null, true); - } - - @Test - public void testVerifyQuery() throws Exception { - verifyResultRowCount("src/test/resources/query/sql_verifyCount"); - } - - @Test - public void testOrderByQuery() throws Exception { - execAndCompQuery("src/test/resources/query/sql_orderby", null, true); - // FIXME - // as of optiq 0.8, we lost metadata type with "order by" clause, e.g. sql_orderby/query01.sql - // thus, temporarily the "order by" clause was cross out, and the needSort is set to true - // execAndCompQuery("src/test/resources/query/sql_orderby", null, false); - } - - @Test - public void testLookupQuery() throws Exception { - execAndCompQuery("src/test/resources/query/sql_lookup", null, true); - } - - @Test - public void testCachedQuery() throws Exception { - execAndCompQuery("src/test/resources/query/sql_cache", null, true); - } - - @Test - public void testDerivedColumnQuery() throws Exception { - execAndCompQuery("src/test/resources/query/sql_derived", null, true); - } - - @Test - public void testDistinctCountQuery() throws Exception { - if ("left".equalsIgnoreCase(joinType)) { - batchExecuteQuery("src/test/resources/query/sql_distinct"); - } - } - - @Test - public void testPreciselyDistinctCountQuery() throws Exception { - if ("left".equalsIgnoreCase(joinType)) { - execAndCompQuery("src/test/resources/query/sql_distinct_precisely", null, true); - } - } - - @Test - public void testStreamingTableQuery() throws Exception { - execAndCompQuery("src/test/resources/query/sql_streaming", null, true); - } - - @Test - public void testTableauQuery() throws Exception { - execAndCompResultSize("src/test/resources/query/sql_tableau", null, true); - } - - @Test - public void testSubQuery() throws Exception { - execAndCompQuery("src/test/resources/query/sql_subquery", null, true); - } - - @Test - public void testCaseWhen() throws Exception { - execAndCompQuery("src/test/resources/query/sql_casewhen", null, true); - } - - @Ignore - @Test - public void testHiveQuery() throws Exception { - execAndCompQuery("src/test/resources/query/sql_hive", null, true); - } - - @Test - public void testH2Query() throws Exception { - this.execQueryUsingH2("src/test/resources/query/sql_orderby", false); - } - - @Test - public void testInvalidQuery() throws Exception { - - printInfo("-------------------- Test Invalid Query --------------------"); - String queryFolder = "src/test/resources/query/sql_invalid"; - List<File> sqlFiles = getFilesFromFolder(new File(queryFolder), ".sql"); - for (File sqlFile : sqlFiles) { - String queryName = StringUtils.split(sqlFile.getName(), '.')[0]; - printInfo("Testing Query " + queryName); - String sql = getTextFromFile(sqlFile); - IDatabaseConnection cubeConn = new DatabaseConnection(cubeConnection); - try { - cubeConn.createQueryTable(queryName, sql); - } catch (Throwable t) { - continue; - } finally { - cubeConn.close(); - } - throw new IllegalStateException(queryName + " should be error!"); - } - } - - @Test - public void testDynamicQuery() throws Exception { - execAndCompDynamicQuery("src/test/resources/query/sql_dynamic", null, true); - } - - @Ignore("simple query will be supported by ii") - @Test - public void testLimitEnabled() throws Exception { - runSqlFile("src/test/resources/query/sql_optimize/enable-limit01.sql"); - assertLimitWasEnabled(); - } - - @Test - public void testTopNQuery() throws Exception { - if ("left".equalsIgnoreCase(joinType)) { - this.execAndCompQuery("src/test/resources/query/sql_topn", null, true); - } - } - - private void assertLimitWasEnabled() { - OLAPContext context = getFirstOLAPContext(); - assertTrue(context.storageContext.isLimitEnabled()); - } - - private OLAPContext getFirstOLAPContext() { - return OLAPContext.getThreadLocalContexts().iterator().next(); - } - -}
