Skip to main content
  • EN

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!

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.

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

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

30,108
Posts
114,293
Replies
37,743
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.