Comments (9)
shawnzhu
commented on August 25, 2024
And when using CAST(xxx AS timestamp), it reports error as well:
Unsupported column type: timestamp(3)
from trino-db2.
shawnzhu
commented on August 25, 2024
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.
shawnzhu
commented on August 25, 2024
Need to fix this for a current task
from trino-db2.
shawnzhu
commented on August 25, 2024
Two problems so far:
- map existing timestamp data type when read. Given table contains a column named
conference_startwith typeTIMESTAMP, in Db2, the default precision is6, so it should read the columnconference_startwith typeTIMESTAMP(6)instead ofTIMESTAMP(3). the number3comes 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.
shawnzhu
commented on August 25, 2024
#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.
shawnzhu
commented on August 25, 2024
#62 will fix the ☝️ error but got new problem described under #62 (comment)
from trino-db2.
shawnzhu
commented on August 25, 2024
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.
shawnzhu
commented on August 25, 2024
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.
shawnzhu
commented on August 25, 2024
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.