Comments (9)
And when using CAST(xxx AS timestamp)
, it reports error as well:
Unsupported column type: timestamp(3)
from trino-db2.
This might be caused by recent feature change in upstream where it supports precision on data type TIMESTAMP
which cause incompatibility with Db2's type TIMESTAMP
.
The fix can be borrowed from postgresql connector.
from trino-db2.
Need to fix this for a current task
from trino-db2.
Two problems so far:
- map existing timestamp data type when read. Given table contains a column named
conference_start
with typeTIMESTAMP
, in Db2, the default precision is6
, so it should read the columnconference_start
with typeTIMESTAMP(6)
instead ofTIMESTAMP(3)
. the number3
comes from trino where it's the default precision for typeTIMESTAMP
: https://github.com/trinodb/trino/blob/dd0f786b88297e8a538c67423a5f322800577c9c/presto-spi/src/main/java/io/prestosql/spi/type/TimestampType.java#L31 - keep the timestamp precision when write. if no precision given, use default precision
6
. see Datetime values - IBM Db2 on Cloud Since Db2 10, the maximum precision of timestamp is the same as that of trino 🎉 so the precision adapting logic is much simpler.
from trino-db2.
#60 will fix the 1) problem from ☝️ comment. See:
presto:kzhu_test> desc warehouse.kzhu_test.test_dt;
Column | Type | Extra | Comment
------------+--------------+-------+---------
conf_start | timestamp(6) | |
conf_end | timestamp(3) | |
(2 rows)
Query 20210313_023359_00005_pdzd3, FINISHED, 1 node
Splits: 19 total, 19 done (100.00%)
1.37 [2 rows, 152B] [1 rows/s, 111B/s]
However, when trying to create a table contains a column in data type timestamp with precision failed:
presto:kzhu_test> create table warehouse.kzhu_test.test_dt_2 AS select * from warehouse.kzhu_test.test_dt with no data;
Query 20210313_023537_00006_pdzd3 failed: Unsupported column type: timestamp(6)
Need to tweak the method toWriteMapping()
from trino-db2.
#62 will fix the ☝️ error but got new problem described under #62 (comment)
from trino-db2.
Full stacktrace of ☝️ exception:
2021-03-14T02:08:54.402Z ERROR SplitRunner-8-106 io.prestosql.execution.executor.TaskExecutor Error processing Split 20210314_020853_00002_g6v7g.1.0-0 io.prestosql.plugin.jdbc.JdbcSplit@788becc6 (start = 1062339.353571, wall = 375 ms, cpu = 0 ms, wait = 0 ms, calls = 1): JDBC_ERROR: [jcc][t4][1092][13956][4.25.13] Invalid data conversion: Wrong result column type for requested conversion. ERRORCODE=-4461, SQLSTATE=42815
io.prestosql.spi.PrestoException: [jcc][t4][1092][13956][4.25.13] Invalid data conversion: Wrong result column type for requested conversion. ERRORCODE=-4461, SQLSTATE=42815
at io.prestosql.plugin.jdbc.JdbcRecordCursor.handleSqlException(JdbcRecordCursor.java:250)
at io.prestosql.plugin.jdbc.JdbcRecordCursor.getLong(JdbcRecordCursor.java:163)
at io.prestosql.spi.connector.RecordPageSource.getNextPage(RecordPageSource.java:106)
at io.prestosql.operator.TableScanOperator.getOutput(TableScanOperator.java:301)
at io.prestosql.operator.Driver.processInternal(Driver.java:379)
at io.prestosql.operator.Driver.lambda$processFor$8(Driver.java:283)
at io.prestosql.operator.Driver.tryWithLock(Driver.java:675)
at io.prestosql.operator.Driver.processFor(Driver.java:276)
at io.prestosql.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:1076)
at io.prestosql.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:163)
at io.prestosql.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:484)
at io.prestosql.$gen.Presto_347____20210314_020416_2.run(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][t4][1092][13956][4.25.13] Invalid data conversion: Wrong result column type for requested conversion. ERRORCODE=-4461, SQLSTATE=42815
at com.ibm.db2.jcc.am.b6.a(b6.java:810)
at com.ibm.db2.jcc.am.b6.a(b6.java:66)
at com.ibm.db2.jcc.am.b6.a(b6.java:133)
at com.ibm.db2.jcc.am.ResultSet.getObject(ResultSet.java:1961)
at io.prestosql.plugin.jdbc.StandardColumnMappings.lambda$timestampReadFunction$23(StandardColumnMappings.java:454)
at io.prestosql.plugin.jdbc.JdbcRecordCursor.getLong(JdbcRecordCursor.java:160)
... 13 more
from trino-db2.
One more thing I learned is it also needs to support both TIMESTAMP
(or called TIMESTAMP WITHOUT TIME ZONE
) and TIMESTAMP WITH TIMEZONE
type.
from trino-db2.
Fixed by #62
I've tested read/write table with column in data type timestamp(x).
It will takes time to figure out how to support column with type TIMESTAMP WITH TIME ZONE
in Db2.
from trino-db2.
Related Issues (20)
- Supports IAM Authentication HOT 1
- Keeps using uppercase table name for 'CREATE TABLE' statement HOT 1
- Make 'INLINE LENGTH' parameter configurable
- INSERT INTO <table_name> AS SELECT doesn't work HOT 1
- Picks up prestosql v332 HOT 2
- Not able to delete or update HOT 1
- ODBC connector or bridge for DB2 HOT 3
- DB2 Driver HOT 1
- Switch build environment to Java@11
- Support ANALYZE HOT 1
- Support trinodb 351
- Publish to maven central
- Explore possibility of improving read performance by implementing baseJdbcClient.getSplits()
- CREATE TABLE AS SELECT statement doesn't respect NOT NULL constraint
- java.lang.IllegalArgumentException: Precision is out of range: 10 HOT 3
- Failed to read column with data type "TIMESTAMP" HOT 1
- Update to 359 HOT 1
- Upgrade to trino 364
- How to install?
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from trino-db2.