https://linen.dev logo
Join Slack
Powered by
Hey everyone, I am trying to port a singer pipelin...
# announcements
e
Hey everyone, I am trying to port a singer pipeline into meltano. I need to modify the type constraints for some of my columns. This is easy enough in singer, by modifying 
catalog.json
, but meltano seems to recreate catalog.json for each run. I couldn’t find a corresponding cli command in the docs, and tried the following command, to no avail: 
meltano config <some-tap> set schema <some_table> properties <some_col>
d
@emre Welcome! Did you see https://meltano.com/docs/command-line-interface.html#singer-metadata? If you can tell me what exact property you'd like to change, I'll gladly help you figure out the correct command 🙂
e
Hey @douwe_maan, I did see that, but assumed we could only modify the 
metadata
section of the corresponding 
catalog.json
. Tried a couple different things but can’t change it. Let me describe in detail.
d
Ah yes, Meltano is currently only capable of making changes to 
metadata
, not arbitrary properties. By design, I must add, because the rest of the file (the basic schema) is really the tap's responsibility and shouldn't need to be changed if the tap is behaving correctly. So if you'd like to make changes there, we might be looking in a bug in the tap instead...
But yes, please describe in more detail and let's figure this out 🙂
e
Oh ok, basically tap-google-sheets casts numeric values in the following style:
Copy code
"anyOf": [
              {
                "type": "null"
              },
              {
                "type": "number",
                "multipleOf": 1e-15
              },
              {
                "type": "string"
              }
            ]
The multipleOf bit is causing errors, and my columns are integers anyways, so I want to swap with “type”: [“null”, “integer”]
Im sure you figured it out 😅, but this is the value of key 
streams[x].schema.properties.<colname>
d
Ah! I take it 
multipleOf
is causing errors in your target? What target may that be? I know versions exists of most if not all of them that have fixed that bug, e.g: https://github.com/meltano/target-postgres/pull/2
e
Yes, target-jsonl is getting a 
decimal.DivisionImpossible
error. Couldn’t pinpoint the exact culprit row yet. Didn’t want to move on to other targets before getting used to the basic ones.
d
@emre Ah all right! In this case, a more "serious" one is probably more likely to work 😉
This'd be a great bug to file on https://github.com/andyh1203/target-jsonl though
cc @andy_huynh
Interestingly, 
target-jsonl
already seems to incorporate the fix for 
target-postgres
that was contributed in https://github.com/meltano/target-postgres/pull/2/files: https://github.com/andyh1203/target-jsonl/blob/master/target_jsonl.py
@emre While we're debugging this, could you share the 
meltano elt
output around the error message and failure? I'm sure we can figure out which row is causing trouble here 🙂
e
sure, target-jsonl error traceback:
Copy code
Traceback (most recent call last):
  File "/Users/emreakgun/Documents/meltano_sandbox/projects/sheets-snowflake/.meltano/loaders/target-jsonl/venv/bin/target-jsonl", line 11, in <module>
    load_entry_point('target-jsonl', 'console_scripts', 'target-jsonl')()
  File "/Users/emreakgun/Documents/meltano_sandbox/projects/sheets-snowflake/load/target-jsonl/target_jsonl.py", line 94, in main
    state = persist_messages(input_messages, config.get('destination_path', ''), config.get('do_timestamp_file', True))
  File "/Users/emreakgun/Documents/meltano_sandbox/projects/sheets-snowflake/load/target-jsonl/target_jsonl.py", line 59, in persist_messages
    validators[o['stream']].validate(float_to_decimal(o['record']))
  File "/Users/emreakgun/Documents/meltano_sandbox/projects/sheets-snowflake/.meltano/loaders/target-jsonl/venv/lib/python3.7/site-packages/jsonschema/validators.py", line 129, in validate
    for error in self.iter_errors(*args, **kwargs):
  File "/Users/emreakgun/Documents/meltano_sandbox/projects/sheets-snowflake/.meltano/loaders/target-jsonl/venv/lib/python3.7/site-packages/jsonschema/validators.py", line 105, in iter_errors
    for error in errors:
  File "/Users/emreakgun/Documents/meltano_sandbox/projects/sheets-snowflake/.meltano/loaders/target-jsonl/venv/lib/python3.7/site-packages/jsonschema/_validators.py", line 304, in properties_draft4
    schema_path=property,
  File "/Users/emreakgun/Documents/meltano_sandbox/projects/sheets-snowflake/.meltano/loaders/target-jsonl/venv/lib/python3.7/site-packages/jsonschema/validators.py", line 121, in descend
    for error in self.iter_errors(instance, schema):
  File "/Users/emreakgun/Documents/meltano_sandbox/projects/sheets-snowflake/.meltano/loaders/target-jsonl/venv/lib/python3.7/site-packages/jsonschema/validators.py", line 105, in iter_errors
    for error in errors:
  File "/Users/emreakgun/Documents/meltano_sandbox/projects/sheets-snowflake/.meltano/loaders/target-jsonl/venv/lib/python3.7/site-packages/jsonschema/_validators.py", line 364, in anyOf_draft4
    errs = list(validator.descend(instance, subschema, schema_path=index))
  File "/Users/emreakgun/Documents/meltano_sandbox/projects/sheets-snowflake/.meltano/loaders/target-jsonl/venv/lib/python3.7/site-packages/jsonschema/validators.py", line 121, in descend
    for error in self.iter_errors(instance, schema):
  File "/Users/emreakgun/Documents/meltano_sandbox/projects/sheets-snowflake/.meltano/loaders/target-jsonl/venv/lib/python3.7/site-packages/jsonschema/validators.py", line 105, in iter_errors
    for error in errors:
  File "/Users/emreakgun/Documents/meltano_sandbox/projects/sheets-snowflake/.meltano/loaders/target-jsonl/venv/lib/python3.7/site-packages/jsonschema/_validators.py", line 127, in multipleOf
    failed = instance % dB
decimal.InvalidOperation: [<class 'decimal.DivisionImpossible'>]
All the data in json are either integers or strings. The tap obviously parses integers as .0 floats.
d
The error is the exact same one we saw in https://gitlab.com/meltano/target-snowflake/-/issues/23, but there the issue was with precision since a very low 
multipleOf
was used (
1e-38
), which we fixed with 
decimal.getcontext().prec = 40
. In your case, 
1e-15
doesn't look so bad though 😕
In any case, this is technically a bug in the target, but I can see how it would have been easier if you could just modify the 
catalog.json
file yourself, which makes more sense when using taps with dynamic schemas, as opposed to those built for a very specific SaaS API.
It would help if we knew what row was at fault though. Are you comfortable going into 
/Users/emreakgun/Documents/meltano_sandbox/projects/sheets-snowflake/.meltano/loaders/target-jsonl/venv/lib/python3.7/site-packages/jsonschema/_validators.py
at line 127 and adding some print-based debugging?
e
I am trying that right now 😅
sorry, had to go for a couple hours. This was the output
Copy code
<class 'decimal.Decimal'> # print(type(dB))
1E-15 # print(dB)
<class 'decimal.Decimal'> # print(type(instance))
21700000000000.0 # print(instance)
The odd thing is this value is supposed to be simply 217, it should never be that big, and isn’t throughout the sheet.
I guess I will try some more tomorrow, and try my luck with 
target-snowflake
, see If the tap is producing the inflated value or the target is inflating as it parses.
d
@emre If you run 
meltano invoke tap-google-sheets > records.jsonl
, you may be able to find the offending record in that file by hand. You can then pipe it into 
target-jsonl
to reproduce the behavior of 
meltano elt
: 
cat records.jsonl | meltano invoke target-jsonl
e
Found the issue. Decimal class can’t handle very large values, or rather when the gap between the two division operands get too large. https://stackoverflow.com/questions/42868278/decimal-invalidoperation-divisionimpossible-for-very-large-numbers My problem row had indeed a very large number, its supposed to be a phone number but with more digits than required for some reason: 
21700000000000
. For our case, 
10**13
seems to be the largest integer value that the 
Decimal
can take module of 
1e-15
.
I mean, I would rather read phone numbers as string anyways, but meltano currently doesn’t support that. At least as far as I understand. Currently, I am deriving a stringifed column of the phone numbers over at the google sheets side, and using that instead. Meanwhile, I might open an issue over at 
jsonschema
. Are there any plans to customize the underlying schema properties of streams as well, and not just metadata? I think it would be beneficial for taps that can be more “volatile”.
d
@emre So this phone number used to be stored in a numeric field, and is now stored in a string field, and that fixed it?
Are there any plans to customize the underlying schema properties of streams as well, and not just metadata? I think it would be beneficial for taps that can be more “volatile”.
No concrete plans yet since you're the first to bring this up, but I'm not opposed in principle if this turns out to be a more common issue that can't always just be fixed by correcting the type hints on the source sheet/table side. If changing the cell from numeric to string fixed the problem, I'd argue that that's the more appropriate solution than explicitly telling Meltano to disregard and override the type inferred by the tap, so I wouldn't want to introduce that feature until it's clear there are cases where a more appropriate solution is not available 🙂
Opening an issue on jsonschema to deal with these issues better sounds like a good idea.
e
Yeah, apparently 
tap-google-sheets
is using cell formats to attempt to determine data type. Setting the format to text let the tap assign a string type to the property.
I did open an issue, but was rejected. They told me that they support 
Decimal
, but it is the programmers responsibility to provide the right decimal precision.
d
@emre Right. I can see the value in explicitly setting types on the Meltano side, but doesn't setting them correctly on the actual source side seem like the more appropriate fix?
Ah... That's a shame, but I understand why that's their position.
Something for all users of jsonschema to be aware of then, especially when designing Singer targets
e
btw increasing decimal precision fixes the 
divisionImpossibe
error I have been getting. So this particular fix might be more suited at the target side.
like catching divisionImpossible errors on validation, and trying with higher precision.
d
Yeah, increasing the precision is what we did in target-snowflake as well: https://gitlab.com/meltano/target-snowflake/-/merge_requests/27
I'm sure @andy_huynh would gladly accept a PR to https://github.com/andyh1203/target-jsonl 🙂
e
About the explicit setting issue: You are absolutely right, when the data source can be configured to suit the tap and target, than the source itself should be modified. But I haven’t tried yet, but 
target-snowflake
seems like it doesn’t accept 
anyOf
statements as a data type. My tap isn’t assigning integers, and floats are assigned with an 
anyOf
. I will either have to cast most of my columns to string in my sheets, or fork 
tap-google-sheets
to eliminate 
anyOf
statements.
My point is, 
--discover
can be clunky depending on the source and robustness of tap, and I would very much appreciate an option to enforce my own data types, if I trust the underlying data and its schema, but can’t communicate this to the tap by the tools at my disposal.
d
Ah, that's interesting — 
target-snowflake
not supporting 
anyOf
is a very good point. Overriding the types could cause trouble as well, though, since targets do actually validate the incoming 
RECORD
messages against the schema, and changing the schema won't affect the actual values or how they're cast, so it could lead to unexpected errors in production too. I agree this is something we'll want to address in Meltano though, even if it will come with some warnings. Would you be up for creating an issue in https://gitlab.com/meltano/meltano/-/issues so that we can discuss it some more and come up with the best way to have Meltano help out here?
e
d
@emre Awesome, thanks! Commented 🙂
4 Views
Open in Slack
PreviousNext
Powered by