drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Khurram Faraaz (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DRILL-3701) question : getTime and getTimestamp compatibility from JDBC
Date Mon, 24 Aug 2015 17:41:45 GMT
Khurram Faraaz created DRILL-3701:
-------------------------------------

             Summary: question : getTime and getTimestamp compatibility from JDBC
                 Key: DRILL-3701
                 URL: https://issues.apache.org/jira/browse/DRILL-3701
             Project: Apache Drill
          Issue Type: Bug
          Components: Execution - Data Types
    Affects Versions: 1.2.0
            Reporter: Khurram Faraaz
            Assignee: Daniel Barclay (Drill)



When we use getTime instead of getTimestamp function from JDBC program to get a timestamp
type column from a parquet file, I see this Exception. Should we support this from JDBC and
return only the TIME portion of the TIMESTAMP value when getTime is used over a column type
timestamp ? (that is what Drill does today on sqlline prompt when we do an explicit cast to
TIME)

org.apache.drill.exec.vector.accessor.InvalidAccessException: Requesting value of type Time
for an object of type TIMESTAMP:OPTIONAL is not allowed.

Note from below query output that the sixth column (col5) is of type TIMESTAMP in the parquet
file and holds timestamp data.

{code}
0: jdbc:drill:schema=dfs.tmp> SELECT * FROM FEWRWSPQQ_101 limit 3;
+-------+-----------+-----------+---------+---------------+--------------------------+-------------+--------+-------+-------------------------------------------------------+
| col0  |   col1    |   col2    |  col3   |     col4      |           col5           |   
col6     |  col7  | col8  |                         col9                          |
+-------+-----------+-----------+---------+---------------+--------------------------+-------------+--------+-------+-------------------------------------------------------+
| 1     | 65534     | 256.0     | 1234.9  | 20:26:18.580  | 2014-03-02 00:28:02.338  | 1952-08-14
 | false  | CA    | AXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXZ  |
| 2     | 10000000  | -256.0    | 11.0    | 10:59:58.119  | 2014-01-02 00:28:02.228  | 1981-03-14
 | true   | WI    | BXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXD  |
| 3     | -1        | 255.9993  | 0.0     | 22:49:49.300  | 2014-09-02 00:28:02.616  | 2000-01-03
 | false  | NY    | CXXXXXXXXXXXXXXXXXXXXXXXXXCXXXXXXXXXXXXXXXXXXXXXXXXB  |
+-------+-----------+-----------+---------+---------------+--------------------------+-------------+--------+-------+-------------------------------------------------------+
3 rows selected (0.185 seconds)
{code}

Stack trace reported on prompt from where JDBC program is executed.
{code}
...
field {
    major_type {
      minor_type: TIMESTAMP
      mode: OPTIONAL
    }
    name_part {
      type: NAME
      name: "col5"
    }
    value_count: 22
    buffer_length: 198
...
Requesting value of type Time for an object of type TIMESTAMP:OPTIONAL is not allowed.
org.apache.drill.exec.vector.accessor.InvalidAccessException: Requesting value of type Time
for an object of type TIMESTAMP:OPTIONAL is not allowed.
	at org.apache.drill.exec.vector.accessor.AbstractSqlAccessor.newInvalidAccessException(AbstractSqlAccessor.java:116)
	at org.apache.drill.exec.vector.accessor.AbstractSqlAccessor.getTime(AbstractSqlAccessor.java:106)
	at org.apache.drill.exec.vector.accessor.BoundCheckingAccessor.getTime(BoundCheckingAccessor.java:129)
	at org.apache.drill.jdbc.impl.TypeConvertingSqlAccessor.getTime(TypeConvertingSqlAccessor.java:689)
	at org.apache.drill.jdbc.impl.AvaticaDrillSqlAccessor.getTime(AvaticaDrillSqlAccessor.java:219)
	at net.hydromatic.avatica.AvaticaResultSet.getTime(AvaticaResultSet.java:250)
	at org.apache.drill.jdbc.impl.DrillResultSetImpl.getTime(DrillResultSetImpl.java:248)
	at DataFromDrill.main(DataFromDrill.java:30)
{code}

JDBC snippet to execute the above SQL

{code}
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.sql.*;

public class DataFromDrill {
    public static void main(String s[]) {

        try {
                final String URL_STRING = "jdbc:drill:schema=dfs.tmp;drillbit=<ip-address>";
                Class.forName("org.apache.drill.jdbc.Driver").newInstance();
                Connection conn = DriverManager.getConnection(URL_STRING,"root","mapr");
                Statement stmt = conn.createStatement();

                String query = "select * from FEWRWSPQQ_101";
                
                ResultSet rs = stmt.executeQuery(query);

                while (rs.next()) {
                    System.out.println(rs.getTime(6));
                }

                if (rs != null)
                    rs.close();
                conn.close();
        } catch ( Exception e ) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }
}
{code}

Explicit cast to TIME of the TIMESTAMP type column results in a successful cast from sqlline,
and the TIME portion of the timestamp value is returned. 

{code}
0: jdbc:drill:schema=dfs.tmp> SELECT cast(col5 as time) FROM FEWRWSPQQ_101;
+---------------+
|    EXPR$0     |
+---------------+
| 00:28:02.338  |
| 00:28:02.228  |
| 00:28:02.616  |
| 00:28:02.404  |
| 00:28:02.309  |
| 00:28:02.638  |
| 00:28:02.748  |
| 00:28:02.321  |
| 00:28:02      |
| 00:28:02      |
| 00:28:02.418  |
| 00:28:02.418  |
| 00:28:02.418  |
| 00:28:02.418  |
| 00:28:02.418  |
| 00:28:02.418  |
| 00:28:02.418  |
| 00:28:02.118  |
| 00:28:02.218  |
| 00:28:02.418  |
| 00:28:02.318  |
| 20:28:02.318  |
+---------------+
22 rows selected (0.176 seconds)
{code}



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message