Another question about the

tap-spreadsheets-anywhere

tap. I'm pulling json files from S3, each file contains 1 JSON object. From what I can tell the tap requires the json files contains an array. Is there any way to configure the tap to work with JSON docs that contain just 1 object that's not wrapped in

[...]

?

@jacob_mulligan have you seen https://hub.meltano.com/extractors/tap-spreadsheets-anywhere#common-config-examples?

Yes, pass type jsonl instead of json

@Matt Menzenski is that hub example incorrect then since its configured as

json

?

My understanding is that if the files only ever contain exactly one record, both json and jsonl will work. But if a file can contain multiple records you’ll need to use jsonl type. If using json type in that scenario (multiple records not wrapped in an array), records after the first will not be captured.

Here's the error I get when using JSONL.

Unable to write Catalog entry for 'ccda_documents' - it will be skipped due to error Expecting property name enclosed in double quotes: line 2 column 1 (char 2)

are you able to share an example JSON file?

And here's the error with JSON:

ERROR Unable to write Catalog entry for 'ccda_documents' - it will be skipped due to error 'str' object has no attribute 'items'

looking at that error message, the questions I would ask are “is it truly valid JSON”? and “are there newline characters in the JSON object?”

let me take a look and see if I can get a sample file.

Copy code

ERROR Unable to write Catalog entry for 'ccda_documents' - it will be skipped due to error 'str' object has no attribute 'items'

This error matches my experience trying to use JSON type FWIW

it's auto generated JSON by Rails from an XML document 🥲

👍🏼 then it’s probably valid JSON

no, each file will only contain 1 object

Is the actual record on multiple lines like that, or is it minified? I believe the tap expects the JSON record to be minified (all on a single line)

the record is not minified, it is indeed on 10k+ lines

yeah, I’m not sure what the best approach would be but I’m sure there’s something

i have some more time tomorrow and will look into it 👀 to give me a head start, do you happen to know the line of file/function/line of code that would be failing?

here’s the PR where I added

jsonl

support, if that’s useful - you might start by adding test cases that have newlines in the JSON objects and seeing what error is thrown. https://github.com/ets/tap-spreadsheets-anywhere/pull/28/files

ty! I'll start there.

I believe the generator_wrapper function will be where a lot of this happens https://github.com/ets/tap-spreadsheets-anywhere/blob/main/tap_spreadsheets_anywhere/json_handler.py#L9 that

for obj in root_iterator:

is essentially equal to

for line in file:

I believe

@Matt Menzenski for what its worth I'm having success with both json and jsonl format type when I test with a file of jsonl like this thinkspin

{"user_id": 1, "first_name": "John", "last_name": "Doe"}
{"user_id": 2, "first_name": "Sarah", "last_name": "Smith"}
{"user_id": 3, "first_name": "Joe", "last_name": "Momma"}
{"user_id": 4, "first_name": "Steve", "last_name": "Madden"}

with my meltano.yml

- name: tap-spreadsheets-anywhere
    variant: ets
    pip_url: git+<https://github.com/ets/tap-spreadsheets-anywhere.git>
    config:
      tables:
      - path: s3://<bucket>
        format: jsonl
        key_properties: [user_id]
        name: user_names
        start_date: '2020-01-01T00:00:00Z'
        pattern: "spreadsheets_test/user_names\\.json"

or as

- name: tap-spreadsheets-anywhere
    variant: ets
    pip_url: git+<https://github.com/ets/tap-spreadsheets-anywhere.git>
    config:
      tables:
      - path: s3://<bucket>
        format: json
        key_properties: [user_id]
        name: user_names
        start_date: '2020-01-01T00:00:00Z'
        pattern: "spreadsheets_test/user_names\\.json"

that’s interesting, I wouldn’t have expected that behavior

I was trying to confirm that the hub docs section was incorrect before updating but it worked and now I'm confused lol

aha! I misremembered - it’s actually the opposite of what I described https://meltano.slack.com/archives/C01UW1W4D5Y/p1670443192551259?thread_ts=1670282159.443659&cid=C01UW1W4D5Y

ahh good find - ok that makes sense. 5 months ago...I forget things fast 😭

showing some ignorance here, python isn't my first language!.. it looks like smart_open, the package under the hood that actually pulls the data from S3, is streaming data 1 line at a time here. these lines are then immediately being iterated over one at a time. i'm not really sure how to modify this to handle files with multi-line JSON(L). the streaming piece aside, a few options i was thinking about: 1. ingest the whole file first then use a JSON formatter to convert multiline JSON to be single line 2. wrap multiline json in

[..]

to create a list of 1 which we already know the package handles today