https://linen.dev logo
Join Slack
Powered by
hey all! new to Meltano so apologies if this is a ...
# announcements
a
hey all! new to Meltano so apologies if this is a trivial question. I followed the "Getting Started" guide and I'm trying to use tap-csv with target-postgres to load a CSV. I'm running
Copy code
meltano --log-level=debug elt tap-csv target-postgres --job_id=csv-postgres
and it looks like the tap is working as expected, I see debug records like:
Copy code
tap-csv (out)         | {"type": "RECORD", "stream": "yellow_cabs", "record": {"69934": "628", "2020-11-24 10:59:36": "2017-01-03 13:37:15", "381": "1", "8944": "NULL", "4479": "NULL", "ADD_ANSWER": "LOGIN", "NULL": "NULL", "26189": "NULL", "127.0.0.1": "50.136.93.146"}}
but when it gets to the load I'm getting:
Copy code
target-postgres       | ERROR Exception writing records
target-postgres       | Traceback (most recent call last):
target-postgres       |   File "/home/ashish/workspace/meltano-projects/nyc-taxi-data/.meltano/loaders/target-postgres/venv/lib/python3.8/site-packages/target_postgres/postgres.py", line 295, in write_batch
target-postgres       |     written_batches_details = self.write_batch_helper(cur,
target-postgres       |   File "/home/ashish/workspace/meltano-projects/nyc-taxi-data/.meltano/loaders/target-postgres/venv/lib/python3.8/site-packages/target_postgres/sql_base.py", line 808, in write_batch_helper
target-postgres       |     for table_batch in denest.to_table_batches(schema, key_properties, records):
target-postgres       |   File "/home/ashish/workspace/meltano-projects/nyc-taxi-data/.meltano/loaders/target-postgres/venv/lib/python3.8/site-packages/target_postgres/denest.py", line 20, in to_table_batches
target-postgres       |     table_schemas = _get_streamed_table_schemas(schema,
target-postgres       |   File "/home/ashish/workspace/meltano-projects/nyc-taxi-data/.meltano/loaders/target-postgres/venv/lib/python3.8/site-packages/target_postgres/denest.py", line 47, in _get_streamed_table_schemas
target-postgres       |     key_prop_schemas[key] = schema['properties'][key]
target-postgres       | KeyError: 'id'
target-postgres       | CRITICAL ('Exception writing records', KeyError('id'))
target-postgres       | Traceback (most recent call last):
target-postgres       |   File "/home/ashish/workspace/meltano-projects/nyc-taxi-data/.meltano/loaders/target-postgres/venv/lib/python3.8/site-packages/target_postgres/postgres.py", line 295, in write_batch
target-postgres       |     written_batches_details = self.write_batch_helper(cur,
target-postgres       |   File "/home/ashish/workspace/meltano-projects/nyc-taxi-data/.meltano/loaders/target-postgres/venv/lib/python3.8/site-packages/target_postgres/sql_base.py", line 808, in write_batch_helper
target-postgres       |     for table_batch in denest.to_table_batches(schema, key_properties, records):
target-postgres       |   File "/home/ashish/workspace/meltano-projects/nyc-taxi-data/.meltano/loaders/target-postgres/venv/lib/python3.8/site-packages/target_postgres/denest.py", line 20, in to_table_batches
target-postgres       |     table_schemas = _get_streamed_table_schemas(schema,
target-postgres       |   File "/home/ashish/workspace/meltano-projects/nyc-taxi-data/.meltano/loaders/target-postgres/venv/lib/python3.8/site-packages/target_postgres/denest.py", line 47, in _get_streamed_table_schemas
target-postgres       |     key_prop_schemas[key] = schema['properties'][key]
target-postgres       | KeyError: 'id'
would appreciate any insight!
d
@ashish_datta If you look at the record, you'll see that none of the fields have useful keys, which is why target-postgres is complaining about the missing 
id
key. tap-csv seems to use the first row in the CSV file for column names, but that's not how your CSV was set up. Can you modify the CSV and add a row of column names, with one of them 
id
?
a
ah wow can't believe I missed that. threw a header on there and it went through:
Copy code
meltano               | INFO Extract & load complete!
awesome, thanks!
d
@ashish_datta Awesome!
Another option would be to modify the tap to allow you to specify a header row (keys) through a tap setting, but that'd require writing some new code 🙂
a
May end up dusting off the Python to do that! I noticed there's also no way to specify a generated PK (ex. row number+filename), was looking at loading this data https://registry.opendata.aws/nyc-tlc-trip-records-pds/ and the data rows have no unique identifier
d
You're not required to specify 
key_properties
, but the target won't be able to deduplicate rows
https://meltano.com/plugins/extractors/spreadsheets-anywhere.html / https://github.com/ets/tap-spreadsheets-anywhere is more flexible than tap-csv, but I don't think it supports generated PKs either
I'm sure @eric_simmerman would be open to discussing adding that (or another way to address your issue), though 🙂
a
ah got it! didn't realize key_properties was optional, will give that a shot and look at tap-spreadsheets
actually I think tap-csv requires a config for "keys", https://gitlab.com/meltano/tap-csv/-/blob/master/tap_csv/__init__.py#L62
d
You may be able to keep 
keys: []
, though
11 Views
Open in Slack
PreviousNext
Powered by