Custom table creation throwing error in PostgreSQL

Hi

While creating custom table schema for associated custom json schema, I face following error:
ERROR: there is no unique constraint matching given keys for referenced table “events”
SQL state: 42830

The column “event_id” in “events” table is not unique so this error shows up.

Please help me , how to fix the issue and store custom data in Postgre DB.

You should have name column as a unique constraint, please follow this [link](PostgreSQL: Documentation: 9.1: Constraints)

Thanks

I have not created ‘events’ table and its columns. It is already created while setup of AWS stack.

Can you paste the table definition for your events table and your custom table? I didn’t think the Postgres loader defined any constraints but it sounds like maybe it does (or has one missing on a table).

Below is the custom table:
CREATE TABLE IF NOT EXISTS atomic.com_tuskbrowser_browser_event_1 (
“schema_vendor” VARCHAR(128) NOT NULL,
“schema_name” VARCHAR(128) NOT NULL,
“schema_format” VARCHAR(128) NOT NULL,
“schema_version” VARCHAR(128) NOT NULL,
“root_id” uuid NOT NULL,
“root_tstamp” TIMESTAMP NOT NULL,
“ref_root” VARCHAR(255) NOT NULL,
“ref_tree” VARCHAR(1500) NOT NULL,
“ref_parent” VARCHAR(255) NOT NULL,
“cocoon_token” VARCHAR(65535) NOT NULL,
“aver” VARCHAR(65535),
“bat_charging” BOOLEAN,
“bat_lvl” SMALLINT,
“carrier” VARCHAR(65535),
“country” CHAR(2),
“dev_type” VARCHAR(65535),
“device_id” VARCHAR(65535),
“email” VARCHAR(65535),
“event_timestamp” VARCHAR(65535),
“ip” VARCHAR(65535),
“mac” VARCHAR(65535),
“mem_alloc” DOUBLE PRECISION,
“mfg” VARCHAR(65535),
“model” VARCHAR(65535),
“page_uri” VARCHAR(65535),
“platform” VARCHAR(65535),
“storage_ext_available” BIGINT,
“storage_ext_total” DOUBLE PRECISION,
“storage_int_available” BIGINT,
“storage_int_total” BIGINT,
“tz” SMALLINT,
“user_agent_string” VARCHAR(65535),
FOREIGN KEY (root_id) REFERENCES atomic.events(event_id)
)

Please check and let me know, what is the issue and what could be the solution. It is needs urgent solution.

If you want a quick fix I would drop the FOREIGN key from your table. Out of interest can you link the source to the loader you are using? I would have thought it wouldn’t be trying to enforce a constraint as there’s no truly unique constraint in the atomic.events table.

Edit: Ok so it looks like schema-ddl can generate a foreign key but that’s just for Redshift as far as I can tell the postgres-loader shouldn’t.

Hey

Thanks for your suggestion. Let me test the entire stack if tracking data received in db.

Will update you.

Hey,

I have created the table without foreign key references and tracking data sent to db but no data coming into custom table but data coming into ‘events’ table for sure.

Tell me , how to connect those 2 tables?

Hi

Please tell me , how to proceed here.

Thanks

Hi @achintya.c - there’s not really enough information in your question to easily answer this. Are you running into errors, is the data being shredded correctly, is the Postgres connector able to connect and sent data to the cluster? What queries are failing / succeeding and are they providing any output?

As far my tracker is concerned, there is no error.
Postgres are getting data but not all data. Some basic data such as app id, platform etc are getting sent to database but custom data such as email, device type, device id etc are not reaching database.

Also I am getting some custom data such battery level. batter state etc are coming through to enriched bucket in s3.

I suppose the custom table not getting data because it is not connected to ‘events’ table which stores basic data.

Is there any column which can be used reference for custom table?

I hope I have described the issue with good amount of information. If you feel I am missing something, please let me know.

Hi

I think below URL is the correct way to setup custom schema: GitHub - snowplow/iglu-example-schema-registry: Example static schema registry for Iglu

Sounds like your custom contexts and/or unstruct events are failing validation and ending up in the bad JSON stream, check associated s3 bucket.

1 Like

I will check and let you know.

It is correct that the schema showing errors in bad buckets in s3. Now my question is that, if the schema error is fixed , will it reach the database? The tables ‘event’ and my custom table has no one to one relationship.

I will try to fix the schema error but not sure , how to establish tables relationship.
Please suggest.

I appreciate you are thinking ahead in the pipeline but lets take this one step at a time, first port of call is to correct the the schema violations. We can then look at the contexts/unstruct records in the good stream and hopefully answer your questions.

Hi

Below is the error I received in bad bucket:
“messages”: [{
“schemaKey”: “iglu:com.xxxxxxx/xxxxxxx/jsonschema/1-0-0”,
“error”: {
“error”: “ResolutionError”,
“lookupHistory”: [{
“repository”: “Iglu Central”,
“errors”: [{
“error”: “NotFound”
}
],
“attempts”: 21,
“lastAttempt”: “2022-03-08T13:36:01.342Z”
}, {
“repository”: “Iglu Central - Mirror 01”,
“errors”: [{
“error”: “NotFound”
}
],
“attempts”: 21,
“lastAttempt”: “2022-03-08T13:36:01.531Z”
}, {
“repository”: “Iglu Client Embedded”,
“errors”: [{
“error”: “NotFound”
}
],
“attempts”: 1,
“lastAttempt”: “2022-03-02T15:30:04.612Z”
}, {
“repository”: “Iglu Server”,
“errors”: [{
“error”: “RepoFailure”,
“message”: “no protocol: sp-iglu-lb-xxxxxxxxx/api/schemas/com.xxxxxxx/xxxxxxx/jsonschema/1-0-0”
}
],
“attempts”: 21,
“lastAttempt”: “2022-03-08T13:36:01.252Z”
}
]
}
}
]
},

This suggests that your configuration (as part of your Iglu Resolver) is not able to find the schemas you are looking for. In your resolver you’ll want to ensure you provide the endpoints of your Iglu server - rather than the path directly to your schemas e.g.,

https://sp-iglu-lb-1498571896.us-west-1.elb.amazonaws.com

rather than

sp-iglu-lb-1498571896.us-west-1.elb.amazonaws.com/api/schemas/com.tuskbrowser/browser_event/jsonschema/1-0-0

It also looks like Iglu Central isn’t being found which is quite unusual as both Iglu Central and the mirror should be publicly accessible. Does the infrastructure you are running the loader on have a route and / or permission to the public internet?

2 Likes

Hi

I will check if the infrastructure having all the necessary access to internet. Below is the latest error I can see in s3 bad bucket:
“schemaKey”: “iglu:com.xxxxxxxx/xxxxxxxxx/jsonschema/1-0-0”,
“error”: {
“error”: “ResolutionError”,
“lookupHistory”: [{
“repository”: “Iglu Central”,
“errors”: [{
“error”: “NotFound”
}
],
“attempts”: 7,
“lastAttempt”: “2022-03-09T11:05:12.517Z”
}, {
“repository”: “Iglu Central - Mirror 01”,
“errors”: [{
“error”: “NotFound”
}
],
“attempts”: 7,
“lastAttempt”: “2022-03-09T11:05:12.718Z”
}, {
“repository”: “Iglu Client Embedded”,
“errors”: [{
“error”: “NotFound”
}
],
“attempts”: 1,
“lastAttempt”: “2022-03-09T09:38:21.152Z”
}, {
“repository”: “Iglu Server”,
“errors”: [{
“error”: “RepoFailure”,
“message”: “no protocol: sp-iglu-lb-/xxx/xxx/xxx/jsonschema/1-0-0”
}
],
“attempts”: 7,
“lastAttempt”: “2022-03-09T11:05:12.360Z”

Can you post your Iglu Resolver file? I think that there is likely some configuration errors in this file that are preventing the schemas from being looked up.