This is an automated email from the ASF dual-hosted git repository. kaspersor pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/metamodel.git
commit 71a02aa9bbfb7ed80a257ea6afa779e86fcc5135 Author: 李小保 <[email protected]> AuthorDate: Tue Jan 29 14:58:00 2019 +0800 add pagination function to the Hive sql. 1.Version 1 only support row_number() ; 2.Version 2 support limit offset ,ref https://issues.apache.org/jira/browse/HIVE-11531 --- .../metamodel/jdbc/dialects/HiveQueryRewriter.java | 136 +++++++++++++++++++++ .../metamodel/dialects/HiveQueryRewriterTest.java | 85 +++++++++++++ 2 files changed, 221 insertions(+) diff --git a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/HiveQueryRewriter.java b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/HiveQueryRewriter.java index 6944ab8..3f28aac 100644 --- a/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/HiveQueryRewriter.java +++ b/jdbc/src/main/java/org/apache/metamodel/jdbc/dialects/HiveQueryRewriter.java @@ -18,7 +18,12 @@ */ package org.apache.metamodel.jdbc.dialects; +import java.util.List; +import org.apache.metamodel.MetaModelException; import org.apache.metamodel.jdbc.JdbcDataContext; +import org.apache.metamodel.query.FromItem; +import org.apache.metamodel.query.Query; +import org.apache.metamodel.query.SelectItem; import org.apache.metamodel.schema.ColumnType; /** @@ -26,8 +31,139 @@ import org.apache.metamodel.schema.ColumnType; */ public class HiveQueryRewriter extends DefaultQueryRewriter { + private int majorVersion; + public HiveQueryRewriter(JdbcDataContext dataContext) { super(dataContext); + String version = dataContext.getDatabaseVersion(); + String[] parts = version.split("\\."); + if(parts.length < 2) { + throw new RuntimeException("Illegal Hive Version: " + version + " (expected A.B.* format)"); + } else { + majorVersion = Integer.valueOf(parts[0]); + } + } + + @Override + public final boolean isFirstRowSupported(final Query query) { + switch (majorVersion){ + case 2: + case 3: + return true; + default: + return super.isFirstRowSupported(query); + } + } + + @Override + public final boolean isMaxRowsSupported() { + switch (majorVersion){ + case 2: + case 3: + return true; + default: + return super.isMaxRowsSupported(); + } + } + + /** + * {@inheritDoc} + * + * If the Max rows and/or First row property of the query is set, then we + * will use the database's LIMIT and OFFSET functions. + */ + @Override + public String rewriteQuery(Query query) { + switch (majorVersion){ + case 2: + case 3: + return rewriteQueryForHive2(query); + default: + return rewriteQueryForHive1(query); + } + + } + + private String rewriteQueryForHive1(Query query){ + + Integer maxRows = query.getMaxRows(); + Integer firstRow = query.getFirstRow(); + + if(firstRow != null && firstRow > 1){ + if(query.getOrderByClause().getItemCount() == 0){ + throw new MetaModelException("OFFSET requires an ORDER BY clause"); + } + } + + + if (maxRows == null && (firstRow == null || firstRow.intValue() == 1)) { + return super.rewriteQuery(query); + } + + if ((firstRow == null || firstRow.intValue() == 1) && maxRows != null && maxRows > 0) { + // We prefer to use the "LIMIT n" approach, if + // firstRow is not specified. + return super.rewriteQuery(query) + " LIMIT " + maxRows; + } + + final Query innerQuery = query.clone(); + innerQuery.setFirstRow(null); + innerQuery.setMaxRows(null); + + final Query outerQuery = new Query(); + final FromItem subQuerySelectItem = new FromItem(innerQuery).setAlias("metamodel_subquery"); + outerQuery.from(subQuerySelectItem); + + final List<SelectItem> innerSelectItems = innerQuery.getSelectClause().getItems(); + for (SelectItem selectItem : innerSelectItems) { + outerQuery.select(new SelectItem(selectItem, subQuerySelectItem)); + } + + + final String rewrittenOrderByClause = rewriteOrderByClause(innerQuery, innerQuery.getOrderByClause()); + final String rowOver = "ROW_NUMBER() " + "OVER(" + rewrittenOrderByClause + ")"; + innerQuery.select(new SelectItem(rowOver, "metamodel_row_number")); + innerQuery.getOrderByClause().removeItems(); + + final String baseQueryString = rewriteQuery(outerQuery); + + if (maxRows == null) { + return baseQueryString + " WHERE metamodel_row_number > " + (firstRow - 1); + } + + return baseQueryString + " WHERE metamodel_row_number BETWEEN " + firstRow + " AND " + + (firstRow - 1 + maxRows); + + } + + private String rewriteQueryForHive2(Query query){ + Integer maxRows = query.getMaxRows(); + Integer firstRow = query.getFirstRow(); + + if(firstRow != null && firstRow > 1){ + if(query.getOrderByClause().getItemCount() == 0){ + throw new MetaModelException("OFFSET requires an ORDER BY clause"); + } + } + + String queryString = super.rewriteQuery(query); + + if (maxRows != null || firstRow != null) { + + if (maxRows == null) { + maxRows = Integer.MAX_VALUE; + } + queryString = queryString + " LIMIT " + maxRows; + + if (firstRow != null && firstRow > 1) { + // offset is 0-based + int offset = firstRow - 1; + queryString = queryString + " OFFSET " + offset; + } + } + + return queryString; + } @Override diff --git a/jdbc/src/test/java/org/apache/metamodel/dialects/HiveQueryRewriterTest.java b/jdbc/src/test/java/org/apache/metamodel/dialects/HiveQueryRewriterTest.java new file mode 100644 index 0000000..423b120 --- /dev/null +++ b/jdbc/src/test/java/org/apache/metamodel/dialects/HiveQueryRewriterTest.java @@ -0,0 +1,85 @@ +/** + * 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.metamodel.dialects; + +import static org.apache.metamodel.jdbc.JdbcDataContext.DATABASE_PRODUCT_HIVE; +import static org.apache.metamodel.jdbc.JdbcDataContext.DATABASE_PRODUCT_SQLSERVER; + +import junit.framework.TestCase; +import org.apache.metamodel.jdbc.JdbcDataContext; +import org.apache.metamodel.jdbc.dialects.HiveQueryRewriter; +import org.apache.metamodel.jdbc.dialects.MysqlQueryRewriter; +import org.apache.metamodel.jdbc.dialects.SQLServerQueryRewriter; +import org.apache.metamodel.query.FilterItem; +import org.apache.metamodel.query.FromItem; +import org.apache.metamodel.query.OperatorType; +import org.apache.metamodel.query.Query; +import org.apache.metamodel.query.SelectItem; +import org.apache.metamodel.schema.ColumnType; +import org.apache.metamodel.schema.MutableColumn; +import org.apache.metamodel.schema.MutableSchema; +import org.apache.metamodel.schema.MutableTable; +import org.apache.metamodel.util.TimeComparator; +import org.easymock.EasyMock; +import org.junit.Assert; + +public class HiveQueryRewriterTest extends TestCase { + + + @Override + protected void setUp() throws Exception { + super.setUp(); + + + } + + public void testHive1SqlWithPagination() { + final JdbcDataContext mockContext = EasyMock.createMock(JdbcDataContext.class); + EasyMock.expect(mockContext.getDatabaseProductName()).andReturn(DATABASE_PRODUCT_HIVE).anyTimes(); + EasyMock.expect(mockContext.getDatabaseVersion()).andReturn("1.1.1.1").anyTimes(); + EasyMock.expect(mockContext.getIdentifierQuoteString()).andReturn("quoteString").anyTimes(); + + EasyMock.replay(mockContext); + HiveQueryRewriter qr = new HiveQueryRewriter(mockContext); + + MutableColumn col1 = new MutableColumn("kkbh"); + MutableColumn col2 = new MutableColumn("kkmc"); + Query q = new Query().from(new MutableTable("5_t_kk_kkxx")).select(col1).select(col2) + .where(col1, OperatorType.EQUALS_TO, "5207281832").orderBy(col1).setFirstRow(5).setMaxRows(9); + String sql = qr.rewriteQuery(q); + assertEquals(sql,"SELECT metamodel_subquery.kkbh, metamodel_subquery.kkmc FROM (SELECT kkbh, kkmc, ROW_NUMBER() OVER( ORDER BY kkbh ASC) AS metamodel_row_number FROM 5_t_kk_kkxx WHERE kkbh = '5207281832') metamodel_subquery WHERE metamodel_row_number BETWEEN 5 AND 13"); + } + + public void testHive2SqlWithPagination() { + final JdbcDataContext mockContext = EasyMock.createMock(JdbcDataContext.class); + EasyMock.expect(mockContext.getDatabaseProductName()).andReturn(DATABASE_PRODUCT_HIVE).anyTimes(); + EasyMock.expect(mockContext.getDatabaseVersion()).andReturn("2.1.1.1").anyTimes(); + EasyMock.expect(mockContext.getIdentifierQuoteString()).andReturn("quoteString").anyTimes(); + + EasyMock.replay(mockContext); + HiveQueryRewriter qr = new HiveQueryRewriter(mockContext); + + MutableColumn col1 = new MutableColumn("kkbh"); + MutableColumn col2 = new MutableColumn("kkmc"); + Query q = new Query().from(new MutableTable("5_t_kk_kkxx")).select(col1).select(col2) + .where(col1, OperatorType.EQUALS_TO, "5207281832").orderBy(col1).setFirstRow(5).setMaxRows(9); + String sql = qr.rewriteQuery(q); + assertEquals(sql,"SELECT kkbh, kkmc FROM 5_t_kk_kkxx WHERE kkbh = '5207281832' ORDER BY kkbh ASC LIMIT 9 OFFSET 4"); + } +} \ No newline at end of file
