search

airbytehq / airbyte

The leading data integration platform for ETL / ELT data pipelines from APIs, databases & files to data warehouses, data lakes & data lakehouses. Both self-hosted and Cloud-hosted.
https://airbyte.com
Other
15.94k stars 4.09k forks source link

[destination-oracle] Cannot create destination after upgrading connector version to 1.0.0 failed with ORA-00902: invalid datatype #37400

Closed vamsikurre closed 4 months ago

vamsikurre commented 6 months ago

Connector Name

destination-oracle

Connector Version

1.0.0

What step the error happened?

Configuring a new connector

Relevant information

Caused by: Error : 902, Position : 140, Sql = CREATE TABLE APPS.airbyte_connection_test_308069c1c5f6407db5787f709ccc768b ( "_AIRBYTE_RAW_ID" VARCHAR(64) PRIMARY KEY, "_AIRBYTE_DATA" JSON, "_AIRBYTE_EXTRACTED_AT" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, "_AIRBYTE_LOADED_AT" TIMESTAMP WITH TIME ZONE DEFAULT NULL, "_AIRBYTE_META" JSON ) , OriginalSql = CREATE TABLE APPS.airbyte_connection_test_308069c1c5f6407db5787f709ccc768b ( "_AIRBYTE_RAW_ID" VARCHAR(64) PRIMARY KEY, "_AIRBYTE_DATA" JSON, "_AIRBYTE_EXTRACTED_AT" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, "_AIRBYTE_LOADED_AT" TIMESTAMP WITH TIME ZONE DEFAULT NULL, "_AIRBYTE_META" JSON ) , Error Msg = ORA-00902: invalid datatype

Was JSON valid datatype in oracle, in previous connector version 0.2.0 it used to be NCLOB

Relevant log output

2024-04-18 10:50:11 platform > Docker volume job log path: /tmp/workspace/7db958cc-f580-415f-8d32-9417262e3717/0/logs.log
2024-04-18 10:50:11 platform > Executing worker wrapper. Airbyte version: 0.56.0
2024-04-18 10:50:11 platform > Attempt 0 to save workflow id for cancellation
2024-04-18 10:50:11 platform > Using default value for environment variable SIDECAR_KUBE_CPU_LIMIT: '2.0'
2024-04-18 10:50:11 platform > 
2024-04-18 10:50:11 platform > ----- START CHECK -----
2024-04-18 10:50:11 platform > 
2024-04-18 10:50:11 platform > Using default value for environment variable SOCAT_KUBE_CPU_LIMIT: '2.0'
2024-04-18 10:50:11 platform > Using default value for environment variable SIDECAR_KUBE_CPU_REQUEST: '0.1'
2024-04-18 10:50:11 platform > Using default value for environment variable SOCAT_KUBE_CPU_REQUEST: '0.1'
2024-04-18 10:50:11 platform > Checking if airbyte/destination-oracle:1.0.0 exists...
2024-04-18 10:50:11 platform > airbyte/destination-oracle:1.0.0 was found locally.
2024-04-18 10:50:11 platform > Creating docker container = destination-oracle-check-7db958cc-f580-415f-8d32-9417262e3717-0-ltztn with resources io.airbyte.config.ResourceRequirements@167e29ce[cpuRequest=,cpuLimit=,memoryRequest=,memoryLimit=,additionalProperties={}] and allowedHosts null
2024-04-18 10:50:11 platform > Preparing command: docker run --rm --init -i -w /data/7db958cc-f580-415f-8d32-9417262e3717/0 --log-driver none --name destination-oracle-check-7db958cc-f580-415f-8d32-9417262e3717-0-ltztn --network host -v /orbhome/9xalcoa//datajump/workspace/:/data -v oss_local_root:/local -e DEPLOYMENT_MODE=OSS -e WORKER_CONNECTOR_IMAGE=airbyte/destination-oracle:1.0.0 -e AUTO_DETECT_SCHEMA=true -e LAUNCHDARKLY_KEY= -e SOCAT_KUBE_CPU_REQUEST=0.1 -e SOCAT_KUBE_CPU_LIMIT=2.0 -e FIELD_SELECTION_WORKSPACES= -e USE_STREAM_CAPABLE_STATE=true -e WORKER_ENVIRONMENT=DOCKER -e AIRBYTE_ROLE=dev -e APPLY_FIELD_SELECTION=false -e WORKER_JOB_ATTEMPT=0 -e OTEL_COLLECTOR_ENDPOINT=http://host.docker.internal:4317 -e FEATURE_FLAG_CLIENT=config -e AIRBYTE_VERSION=0.56.0 -e WORKER_JOB_ID=7db958cc-f580-415f-8d32-9417262e3717 airbyte/destination-oracle:1.0.0 check --config source_config.json
2024-04-18 10:50:11 platform > Reading messages from protocol version 0.2.0
2024-04-18 10:50:13 platform > INFO main i.a.i.d.o.OracleDestination(main):202 starting destination: class io.airbyte.integrations.destination.oracle.OracleDestination
2024-04-18 10:50:13 platform > INFO main i.a.c.i.b.IntegrationCliParser$Companion(parseOptions):146 integration args: {check=null, config=source_config.json}
2024-04-18 10:50:13 platform > INFO main i.a.c.i.b.IntegrationRunner(runInternal):123 Running integration: io.airbyte.cdk.integrations.base.ssh.SshWrappedDestination
2024-04-18 10:50:13 platform > INFO main i.a.c.i.b.IntegrationRunner(runInternal):124 Command: CHECK
2024-04-18 10:50:13 platform > INFO main i.a.c.i.b.IntegrationRunner(runInternal):125 Integration config: IntegrationConfig{command=CHECK, configPath='source_config.json', catalogPath='null', statePath='null'}
2024-04-18 10:50:13 platform > WARN main c.n.s.JsonMetaSchema(newValidator):278 Unknown keyword order - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2024-04-18 10:50:13 platform > WARN main c.n.s.JsonMetaSchema(newValidator):278 Unknown keyword airbyte_secret - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2024-04-18 10:50:13 platform > INFO main i.a.c.i.b.s.SshTunnel$Companion(getInstance):433 Starting connection with method: NO_TUNNEL
2024-04-18 10:50:13 platform > INFO main c.z.h.HikariDataSource(<init>):79 HikariPool-1 - Starting...
2024-04-18 10:50:13 platform > INFO main c.z.h.HikariDataSource(<init>):81 HikariPool-1 - Start completed.
2024-04-18 10:50:14 platform > INFO main i.a.c.d.j.DefaultJdbcDatabase(unsafeQuery$lambda$6):128 closing connection
2024-04-18 10:50:14 platform > INFO main i.a.c.d.j.DefaultJdbcDatabase(unsafeQuery$lambda$6):128 closing connection
2024-04-18 10:50:14 platform > ERROR main i.a.i.d.o.OracleOperations(createTableIfNotExists):61 Error while creating table. java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1823) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1778) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:303) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-5.1.0.jar:?]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-5.1.0.jar:?]
    at io.airbyte.cdk.db.jdbc.JdbcDatabase.execute$lambda$0(JdbcDatabase.kt:36) ~[airbyte-cdk-core-0.29.10.jar:?]
    at io.airbyte.cdk.db.jdbc.DefaultJdbcDatabase.execute(DefaultJdbcDatabase.kt:30) ~[airbyte-cdk-core-0.29.10.jar:?]
    at io.airbyte.cdk.db.jdbc.JdbcDatabase.execute(JdbcDatabase.kt:36) ~[airbyte-cdk-core-0.29.10.jar:?]
    at io.airbyte.integrations.destination.oracle.OracleOperations.createTableIfNotExists(OracleOperations.java:58) ~[io.airbyte.airbyte-integrations.connectors-destination-oracle.jar:?]
    at io.airbyte.cdk.integrations.destination.jdbc.AbstractJdbcDestination$Companion.attemptTableOperations(AbstractJdbcDestination.kt:416) ~[airbyte-cdk-db-destinations-0.29.10.jar:?]
    at io.airbyte.cdk.integrations.destination.jdbc.AbstractJdbcDestination.check(AbstractJdbcDestination.kt:75) ~[airbyte-cdk-db-destinations-0.29.10.jar:?]
    at io.airbyte.cdk.integrations.base.ssh.SshWrappedDestination.check$lambda$1(SshWrappedDestination.kt:80) ~[airbyte-cdk-core-0.29.10.jar:?]
    at io.airbyte.cdk.integrations.base.ssh.SshTunnel$Companion.sshWrap(SshTunnel.kt:555) [airbyte-cdk-core-0.29.10.jar:?]
    at io.airbyte.cdk.integrations.base.ssh.SshWrappedDestination.check(SshWrappedDestination.kt:74) [airbyte-cdk-core-0.29.10.jar:?]
    at io.airbyte.cdk.integrations.base.IntegrationRunner.runInternal(IntegrationRunner.kt:163) [airbyte-cdk-core-0.29.10.jar:?]
    at io.airbyte.cdk.integrations.base.IntegrationRunner.run(IntegrationRunner.kt:115) [airbyte-cdk-core-0.29.10.jar:?]
    at io.airbyte.integrations.destination.oracle.OracleDestination.main(OracleDestination.java:203) [io.airbyte.airbyte-integrations.connectors-destination-oracle.jar:?]
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00902: invalid datatype

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    ... 26 more

Stack Trace: java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
    at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1823)
    at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1778)
    at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:303)
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
    at io.airbyte.cdk.db.jdbc.JdbcDatabase.execute$lambda$0(JdbcDatabase.kt:36)
    at io.airbyte.cdk.db.jdbc.DefaultJdbcDatabase.execute(DefaultJdbcDatabase.kt:30)
    at io.airbyte.cdk.db.jdbc.JdbcDatabase.execute(JdbcDatabase.kt:36)
    at io.airbyte.integrations.destination.oracle.OracleOperations.createTableIfNotExists(OracleOperations.java:58)
    at io.airbyte.cdk.integrations.destination.jdbc.AbstractJdbcDestination$Companion.attemptTableOperations(AbstractJdbcDestination.kt:416)
    at io.airbyte.cdk.integrations.destination.jdbc.AbstractJdbcDestination.check(AbstractJdbcDestination.kt:75)
    at io.airbyte.cdk.integrations.base.ssh.SshWrappedDestination.check$lambda$1(SshWrappedDestination.kt:80)
    at io.airbyte.cdk.integrations.base.ssh.SshTunnel$Companion.sshWrap(SshTunnel.kt:555)
    at io.airbyte.cdk.integrations.base.ssh.SshWrappedDestination.check(SshWrappedDestination.kt:74)
    at io.airbyte.cdk.integrations.base.IntegrationRunner.runInternal(IntegrationRunner.kt:163)
    at io.airbyte.cdk.integrations.base.IntegrationRunner.run(IntegrationRunner.kt:115)
    at io.airbyte.integrations.destination.oracle.OracleDestination.main(OracleDestination.java:203)
Caused by: Error : 902, Position : 140, Sql = CREATE TABLE APPS.airbyte_connection_test_308069c1c5f6407db5787f709ccc768b (
  "_AIRBYTE_RAW_ID" VARCHAR(64) PRIMARY KEY,
  "_AIRBYTE_DATA" JSON,
  "_AIRBYTE_EXTRACTED_AT" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "_AIRBYTE_LOADED_AT" TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  "_AIRBYTE_META" JSON
  )
, OriginalSql =   CREATE TABLE APPS.airbyte_connection_test_308069c1c5f6407db5787f709ccc768b (
  "_AIRBYTE_RAW_ID" VARCHAR(64) PRIMARY KEY,
  "_AIRBYTE_DATA" JSON,
  "_AIRBYTE_EXTRACTED_AT" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "_AIRBYTE_LOADED_AT" TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  "_AIRBYTE_META" JSON
  )
, Error Msg = ORA-00902: invalid datatype

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
    ... 26 more

2024-04-18 10:50:14 platform > INFO main c.z.h.HikariDataSource(close):349 HikariPool-1 - Shutdown initiated...
2024-04-18 10:50:14 platform > INFO main c.z.h.HikariDataSource(close):351 HikariPool-1 - Shutdown completed.
2024-04-18 10:50:14 platform > INFO main i.a.c.i.b.IntegrationRunner(runInternal):252 Completed integration: io.airbyte.cdk.integrations.base.ssh.SshWrappedDestination
2024-04-18 10:50:14 platform > INFO main i.a.i.d.o.OracleDestination(main):204 completed destination: class io.airbyte.integrations.destination.oracle.OracleDestination
2024-04-18 10:50:14 platform > Check connection job received output: io.airbyte.config.StandardCheckConnectionOutput@411384eb[status=failed,message=State code: 42000; Error code: 902; Message: ORA-00902: invalid datatype
,additionalProperties={}]
2024-04-18 10:50:14 platform > 
2024-04-18 10:50:14 platform > ----- END CHECK -----
2024-04-18 10:50:14 platform >

Contribute

hugovandernotte commented 6 months ago

Hey,

I have the same issue...

evantahler commented 6 months ago

re-categorized to the proper team

evantahler commented 6 months ago

@hugovandernotte are you interested in making and trying out the needed changes?

hugovandernotte commented 6 months ago

Hey @evantahler,

That's a kind proposal but I won't be able to do in a near future...

evantahler commented 5 months ago

As this is a community connector, so we will not be able to commit to any fixes or work on the connector. Is there a specific version of Oracle that you are using that doesn't support JSON columns? In our testing, things seem OK (including creating table with type JSON). Is it the case the in all versions of the database, NCLOB is better than JSON, or does this need to be a user option?

If you wanted to modify the connector, to change JSON back to NCLOB, look here

hugovandernotte commented 5 months ago

Thanks for your answer, we are using the 19c version, and it seems that from the 21c one, the JSOn type is supported, so we will try an update on our side, I will let you know whether it fixes the issue or not :)

evantahler commented 5 months ago

Yep! using https://en.wikipedia.org/wiki/Oracle_Database, JSON types appeared version 21 of the database, and version 23 is current.