phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From JamesRTaylor <...@git.apache.org>
Subject [GitHub] phoenix pull request #262: PHOENIX 153 implement TABLESAMPLE clause
Date Tue, 11 Jul 2017 18:37:52 GMT
Github user JamesRTaylor commented on a diff in the pull request:

    https://github.com/apache/phoenix/pull/262#discussion_r126773527
  
    --- Diff: phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryWithTableSampleIT.java
---
    @@ -0,0 +1,261 @@
    +/*
    + * 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.phoenix.end2end;
    +
    +import static org.junit.Assert.assertEquals;
    +import static org.junit.Assert.assertFalse;
    +import static org.junit.Assert.assertTrue;
    +
    +import java.sql.Connection;
    +import java.sql.DriverManager;
    +import java.sql.PreparedStatement;
    +import java.sql.ResultSet;
    +import java.sql.SQLException;
    +import java.util.Properties;
    +
    +import org.apache.phoenix.exception.PhoenixParserException;
    +import org.apache.phoenix.util.PropertiesUtil;
    +import org.apache.phoenix.util.TestUtil;
    +import org.junit.Before;
    +import org.junit.Test;
    +
    +
    +public class QueryWithTableSampleIT extends ParallelStatsEnabledIT {
    +    private String tableName;
    +    private String joinedTableName;
    +    
    +    @Before
    +    public void generateTableNames() {
    +        tableName = "T_" + generateUniqueName();
    +        joinedTableName = "T_" + generateUniqueName();
    +    }
    +        
    +    @Test(expected=PhoenixParserException.class)
    +    public void testSingleQueryWrongSyntax() throws Exception {
    +        Properties props = PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES);
    +        Connection conn = DriverManager.getConnection(getUrl(), props);
    +        try {
    +            prepareTableWithValues(conn, 100);
    +            String query = "SELECT i1, i2 FROM " + tableName +" tablesample 15 ";
    +
    +            ResultSet rs = conn.createStatement().executeQuery(query);
    +            inspect(rs);
    +        } finally {
    +            conn.close();
    +        }
    +    }
    +    
    +    @Test(expected=PhoenixParserException.class)
    +    public void testSingleQueryWrongSamplingRate() throws Exception {
    +        Properties props = PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES);
    +        Connection conn = DriverManager.getConnection(getUrl(), props);
    +        try {
    +            prepareTableWithValues(conn, 100);
    +            String query = "SELECT i1, i2 FROM " + tableName +" tablesample (175) ";
    +
    +            ResultSet rs = conn.createStatement().executeQuery(query);
    +            inspect(rs);
    +        } finally {
    +            conn.close();
    +        }
    +    }
    +    
    +    @Test
    +    public void testSingleQueryZeroSamplingRate() throws Exception {
    +        Properties props = PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES);
    +        Connection conn = DriverManager.getConnection(getUrl(), props);
    +        try {
    +            prepareTableWithValues(conn, 100);
    +            String query = "SELECT i1, i2 FROM " + tableName +" tablesample (0) ";
    +            ResultSet rs = conn.createStatement().executeQuery(query);              
         
    +            assertFalse(rs.next());
    +        } finally {
    +            conn.close();
    +        }
    +    }
    +    
    +    @Test
    +    public void testSingleQuery() throws Exception {
    +        Properties props = PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES);
    +        Connection conn = DriverManager.getConnection(getUrl(), props);
    +        try {
    +            prepareTableWithValues(conn, 100);
    +            String query = "SELECT i1, i2 FROM " + tableName +" tablesample (45) ";
    +            ResultSet rs = conn.createStatement().executeQuery(query);
    +            
    +            assertTrue(rs.next());
    +            assertEquals(2, rs.getInt(1));
    +            assertEquals(200, rs.getInt(2));
    +            
    +            assertTrue(rs.next());
    +            assertEquals(6, rs.getInt(1));
    +            assertEquals(600, rs.getInt(2));
    +
    +        } finally {
    +            conn.close();
    +        }
    +    }
    +    
    +    @Test
    +    public void testSingleQueryWithWhereClause() throws Exception {
    +        Properties props = PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES);
    +        Connection conn = DriverManager.getConnection(getUrl(), props);
    +        try {
    +            prepareTableWithValues(conn, 100);
    +            String query = "SELECT i1, i2 FROM " + tableName +" tablesample (22) where
i2>=300 and i1<14 LIMIT 4 ";
    +            ResultSet rs = conn.createStatement().executeQuery(query);
    +            
    +            assertTrue(rs.next());
    +            assertEquals(8, rs.getInt(1));
    +            
    +            assertTrue(rs.next());
    +            assertEquals(10, rs.getInt(1));
    +            
    +            assertTrue(rs.next());
    +            assertEquals(12, rs.getInt(1));          
    +            
    +
    +        } finally {
    +            conn.close();
    +        }
    +    }
    +    
    +    @Test
    +    public void testSingleQueryWithAggregator() throws Exception {
    +        Properties props = PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES);
    +        Connection conn = DriverManager.getConnection(getUrl(), props);
    +        try {
    +            prepareTableWithValues(conn, 100);
    +            String query = "SELECT count(i1) FROM " + tableName +" tablesample (22) where
i2>=3000 or i1<2 ";
    +            ResultSet rs = conn.createStatement().executeQuery(query);
    +            
    +            assertTrue(rs.next());
    +            assertEquals(14, rs.getInt(1));
    +        } finally {
    +            conn.close();
    +        }
    +    }
    +    
    +    @Test
    +    public void testSingleQueryWithUnion() throws Exception {
    +        Properties props = PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES);
    +        Connection conn = DriverManager.getConnection(getUrl(), props);
    +        try {
    +            prepareTableWithValues(conn, 100);
    +            String query = "SELECT * FROM " + tableName +" tablesample (100) where i1<2
union all SELECT * FROM " + tableName +" tablesample (2) where i2<6000";
    +            ResultSet rs = conn.createStatement().executeQuery(query);
    +            
    +            assertTrue(rs.next());
    +            assertEquals(0, rs.getInt(1));
    +            assertTrue(rs.next());
    +            assertEquals(1, rs.getInt(1));
    +            assertTrue(rs.next());
    +            assertEquals(30, rs.getInt(1));
    +            assertTrue(rs.next());
    +            assertEquals(44, rs.getInt(1));
    +        } finally {
    +            conn.close();
    +        }
    +    }
    +    
    +    @Test
    +    public void testSingleQueryWithSubQuery() throws Exception {
    +        Properties props = PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES);
    +        Connection conn = DriverManager.getConnection(getUrl(), props);
    +        try {
    +            prepareTableWithValues(conn, 100);
    +            String query = "SELECT count(*) FROM (SELECT * FROM " + tableName +" tablesample
(50))";
    +            ResultSet rs = conn.createStatement().executeQuery(query);
    +            
    +            assertTrue(rs.next());
    +            assertEquals(50, rs.getInt(1));
    +        } finally {
    +            conn.close();
    +        }
    +    }
    +    
    +    @Test
    +    public void testSingleQueryWithJoins() throws Exception {
    +        Properties props = PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES);
    +        Connection conn = DriverManager.getConnection(getUrl(), props);
    +        try {
    +            prepareTableWithValues(conn, 100);
    +            String query = "SELECT count(*) FROM " + tableName +" as A tablesample (45),
"+joinedTableName+" as B tablesample (75) where A.i1=B.i1";
    +            System.out.println(query);
    +            ResultSet rs = conn.createStatement().executeQuery(query);
    +
    +            assertTrue(rs.next());
    +            assertEquals(31, rs.getInt(1));
    +        } finally {
    +            conn.close();
    +        }
    +    }
    +    
    +    final private static void inspect(final ResultSet rs) throws SQLException{
    +    	while(rs.next()){
    +    		System.out.println(rs.getInt(1)+","+rs.getInt(2));
    --- End diff --
    
    Minor nit: we don't write to system.out for testing (though I understand the value while
debugging). Perhaps simplest to just switch to logging instead.


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

Mime
View raw message