Question

JDBC writer targeting Vertica DB : COM.safe.fme.TableDoesntExistException

A
Badge

Hi all,

I am a FME begginer trying to load data into a Vertica DB.

I am using the JDBC Writer to create table and load data. Table creation works perfectly, but then data are not loading, :

JDBC Writer: Connected to 'Vertica Database' version '08.01.0001' using driver 'vertica.jar' version '08.01.0107' built against JDBC version '4.0'

JDBC Writer: Connection class 'com.vertica.jdbc.VerticaJdbc4ConnectionImpl' is being loaded from '/C:/Program Files/FME/jre/lib/ext/vertica-jdbc-8.1.1-7.jar'

JDBC Writer: Committing feature 0

JDBC Writer: Creating table using SQL: CREATE TABLE "bi_core_staging"."events" ("Geometry_AreaInSquareMeters" double precision, "SeasonFieldId" integer, "Source_CallerLineNumber" integer, "Context_User__id" integer, "DateTime" timestamp, "EventType_ProtocolVersion" integer, "FieldExtIds__" date, "FieldId" integer, "_InsertDateTime" timestamp, "CropId" integer, "GeowerId" integer, "Context_Customer__id" integer, "EventType_Version" varchar(5), "Source_CallerFilePath" varchar(20), "Source_CallerMemberName" varchar(20), "Source_MainAssembly" varchar(20), "UserExtId" varchar(20), "Context_Customer_Name" varchar(20), "EventType_Action" varchar(20), "Context_ApplicationName" varchar(20), "EventType_ResourceType" varchar(20), "ConsumptionType" varchar(20), "Mode" varchar(20), "EventType_Resource" varchar(30), "BatchId" varchar(40), "Context_ServiceName" varchar(40), "JobId" varchar(40), "Geometry_CentroidWkt" varchar(50), "CoverageImages__" varchar(2000), "Geometry_GeometryWkt" varchar(65000), "_DataAsList__" varchar(65000), "MapProducts__" varchar(65000), "Fields__" varchar(65000))

JDBC Writer: Failed to create table 'bi_core_staging.events'. Error: [Vertica][VJDBC](4213) ROLLBACK: Object "events" already exists

JDBC Writer: Table 'bi_core_staging.events' does not exist

COM.safe.fme.TableDoesntExistException

at COM.safe.fme.JdbcWriter.checkTable(JdbcWriter.java:1767)

at COM.safe.fme.JdbcWriter.prepareForWriting(JdbcWriter.java:490)

at COM.safe.fme.JdbcWriter.write(JdbcWriter.java:280)

COM.safe.fme.TableDoesntExistException

COM.safe.fme.TableDoesntExistException

Destination Feature Type Routing Correlator(RoutingFactory): COM.safe.fme.TableDoesntExistException

Many thanks for your help!

3 replies

ravenkopelman
Rank
Badge

From your log I believe that in your Feature Type you have Table Handling = Create if Needed, and that we in fact fail to create the table because it already exists (possibly because this was not your first attempt at running the workspace). All fine, so long as the existing table has the schema you want.

More concerning is that we can't find that table and then give up before writing. My suspicion is that the Vertica JDBC driver implements some of the metadata interfaces in an unusual way, but we'll have to dig a bit to figure out how to accommodate it. Tracked internally by PR#82583.

As a workaround you could consider using ODBC to connect instead.

A
Badge

Yes, you are right, the option Table Handling = Create if Needed is activated and the table created from a previous attempt.

To fix this issue, the connexion string was changed as this:

jdbc:vertica://serverName:port/dbName?searchpath=dbShemaName

ravenkopelman
Rank
Badge

From your log I believe that in your Feature Type you have Table Handling = Create if Needed, and that we in fact fail to create the table because it already exists (possibly because this was not your first attempt at running the workspace). All fine, so long as the existing table has the schema you want.

More concerning is that we can't find that table and then give up before writing. My suspicion is that the Vertica JDBC driver implements some of the metadata interfaces in an unusual way, but we'll have to dig a bit to figure out how to accommodate it. Tracked internally by PR#82583.

As a workaround you could consider using ODBC to connect instead.

We were unable to reproduce this issue using Vertica 9, and Vertica 8 is no longer available in trial form. That being said, the "table exists" code that was erroring for you has been largely removed in FME 2018.1 (for causing a similar symptom with Ingres).

Reply


Community Stats

28,724
Posts
109,518
Replies
36,564
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.