https://linen.dev logo
Join Slack
Powered by
Anyone here that can help me get my `tap-mongodb` ...
# troubleshooting
w
Anyone here that can help me get my 
tap-mongodb
extractor working with a 
target-postgres
loader? I have no issue using a JSON loader with my 
tap-mongodb
extractor but when I use the 
target-postgres
loader, I see an error like: 
psycopg2.errors.UndefinedColumn: column "_id" named in key does not exist
which stems from the SQL command: 
CREATE TABLE IF NOT EXISTS tap_mongodb."test" (PRIMARY KEY ("_id"))
I saw that this error message has been observed a few times in this channel, but I haven't found a reasonable resolution. If you can give me some ideas for troubleshooting this, please let me know! Thanks!
v
Which 
target-postgres
variant are you using?
w
I used the default 
target-postgres
loader and I started to have some success by explicitly defining my 
schema
in 
meltano.yaml
My error went away by doing that ^
v
glad something worked for you! I was going to recommend trying the 
meltanolabs
variant if it works though maybe just leave it 😄
w
Appreciate the quick response, though!
@visch I've got my MongoDB data successfully loaded into Postgres in a column called 
document
. It's a jsonb column and each entry stores a dictionary of key-value pairs. Any chance you know the best way to destructure the 
document
column into multiple columns based on the dictionary keys in that 
document
entry? I'm wondering if I can avoid dbt for now - it strikes me as something that could potentially be handled with 
meltano.yaml
.
v
Let me know how it goes!
w
Thanks!
@visch Whenever I set my 
data_flattening_max_level
to anything non-zero using https://hub.meltano.com/loaders/target-postgres, I don't end up seeing anything in the 
document
column of my Postgres database. I guess I'm a bit confused about how I actually can set up denesting the dictionary with data flattening.
v
Could you give some tap output like 
meltano invoke tap-mongodb > out
and send it over with a row or two of data?
w
Copy code
{"type": "RECORD", "stream": "something", "record": {"_id": "638e447c6710c9d3aac4867b", "document": {"_id": "638e447c6710c9d3aac4867b", "uploaded_file": "file.txt", "UNB": {"SYNTAXIDENTIFIER_1": {"SyntaxIdentifier_1": "UNOB", "SyntaxVersionNumber_2": "2", "ServiceCodeListDirectoryVersionNumber_3": null, "CharacterEncoding_4": null}, "INTERCHANGESENDER_2": {"InterchangeSenderIdentification_1": "BLA", "IdentificationCodeQualifier_2": null, "InterchangeSenderInternalIdentification_3": null, "InterchangeSenderInternalSubIdentification_4": null}, "INTERCHANGERECIPIENT_3": {"InterchangeRecipientIdentification_1": "RECP", "IdentificationCodeQualifier_2": null, "InterchangeRecipientInternalIdentification_3": null, "InterchangeRecipientInternalSubIdentification_4": null}, "DATEANDTIMEOFPREPARATION_4": {"Date_1": "222424", "Time_2": "1414"}, "InterchangeControlReference_5": "SOMEREF", "RECIPIENTSREFERENCEPASSWORDDETAILS_6": null, "ApplicationReference_7": "CUSCAR", "ProcessingPriorityCode_8": null, "AcknowledgementRequest_9": null, "InterchangeAgreementIdentifier_10": null, "TestIndicator_11": null}, "transactions": [ **list of dictionaries, which themselves contain dictionaries and lists of nested dictionaries], "UNZ": {"InterchangeControlCount_1": "1", "InterchangeControlReference_2": "SOMEREF"}, "controlErrors": {"UNB": [], "UNZ": []}, "version": "v1"}, "_sdc_deleted_at": null}, "version": 1673473501056, "time_extracted": "2023-01-11T21:45:02.134990Z"}
Here's one example. I wonder if my record is just of a form that is not easily flattened....
Open in Slack
PreviousNext
Powered by