https://linen.dev logo
Join Slack
Powered by
Hi there! My team has run into a strange error, we...
# plugins-general
o
Hi there! My team has run into a strange error, we can't overcome ourselves and would really appreciate your support and help. We are working with the extractor 
tap facebook-ads
and loader 
target-snowflake
to pull facebook-ads api data into our data warehouse. We are running into an error which suggests one or more of the field-names in the table 
ADS_INSIGHTS__UNIQUE_ACTIONS
cannot be pulled because it begins with a numeric character.
Copy code
target-snowflake | target_snowflake.exceptions.SnowflakeError: ('Exception writing records', SQLError('Identifier must only contain alphanumerics, or underscores, and start with alphas. Got `a` for `1D_CLICK`',))
In order to bypass the error, we tried to exclude the table from being pulled entirely but the error persists no matter what patterns we try to define in order to exclude the table (meaning the table is pulled regardless of what exclusion syntax we include). We have configured the 
tap
the following way (last version):
Copy code
meltano select --list tap-facebook
Legend:
	selected
	excluded
	automatic

Enabled patterns:
	ads_insights_age_and_gender.*
	ads_insights_region.*
	ads_insights_country.*
	ads_insights_platform_and_device.*
	ads_insights.engagement_rate_ranking
	ads_insights.cost_per_unique_click
	ads_insights.reach
	ads_insights.inline_link_clicks
	ads_insights.account_id
	ads_insights.adset_id
	ads_insights.video_p50_watched_actions
	ads_insights.video_30_sec_watched_actions
	ads_insights.date_stop
	ads_insights.clicks
	ads_insights.video_p25_watched_actions
	ads_insights.canvas_avg_view_time
	ads_insights.quality_ranking
	ads_insights.ad_name
	ads_insights.conversion_rate_ranking
	ads_insights.outbound_clicks
	ads_insights.video_play_curve_actions
	ads_insights.unique_inline_link_click_ctr
	ads_insights.cost_per_unique_inline_link_click
	ads_insights.inline_post_engagement
	ads_insights.website_ctr
	ads_insights.unique_actions
	ads_insights.spend
	ads_insights.adset_name
	ads_insights.action_values
	ads_insights.actions
	ads_insights.impressions
	ads_insights.canvas_avg_view_percent
	ads_insights.unique_clicks
	ads_insights.video_p100_watched_actions
	ads_insights.account_name
	ads_insights.cost_per_action_type
	ads_insights.objective
	ads_insights.ctr
	ads_insights.cpp
	ads_insights.ad_id
	ads_insights.inline_link_click_ctr
	ads_insights.unique_inline_link_clicks
	ads_insights.campaign_name
	ads_insights.social_spend
	ads_insights.cpm
	ads_insights.cost_per_inline_link_click
	ads_insights.unique_ctr
	ads_insights.campaign_id
	ads_insights.cost_per_inline_post_engagement
	ads_insights.frequency
	ads_insights.date_start
	ads_insights.unique_link_clicks_ctr
	ads_insights.video_p75_watched_actions
	ads_insights.cpc
	!*.*unique_actions*.*
"table": "ADS_INSIGHTS__UNIQUE_ACTIONS", "status": "failed"
"path": ["ads_insights", "unique_actions"]
Please see the log below:
d
@ofer_kulka I think you might be running into https://gitlab.com/meltano/meltano/-/issues/2381, which is stoping the 
!*.*unique_actions*.*
rule from taking effect.
I hope to look into fixing it next week, but as a workaround you can add the following metadata rule to your 
meltano.yml
:
Copy code
extractors:
- name: tap-facebook
  # ...
  metadata:
    '*':
      '*.*':
        inclusion: available
Then you can run 
meltano select tap-facebook --list
and 
ads_insights.unique_actions
and its subprops should no longer show up under "Selected attributes"
If they still do, try changing 
!*.*unique_actions*.*
to 
!*.unique_actions
Probably using something like:
Copy code
- !*.*.1d_click
- !*.*.7d_click
- !*.*.28d_click
- !*.*.1d_view
- !*.*.7d_view
- !*.*.28d_view
If that doesn't work, let me know, and I can help you figure out the right patterns to use. I'm also happy to jump on a call to help you figure this out!
o
@douwe_maan - as always, I really appreciate your thorough response and quick one too! Thanks for your help, i'll definitely follow up with you if the suggestions do not solve the issue described. I'll have a go with my team in the morning. One comment, which I could be wrong about, is that we under the impression that the problem is with a table rather than a field with the name 
ads_insights__unique_actions
, perhaps that is one of our mistakes. This is a piece of the log file that caught our attention: 
"table": "ADS_INSIGHTS__UNIQUE_ACTIONS", "status": "failed"
 
"path": ["ads_insights", "unique_actions"]
But perhaps it's our incorrect interpretation of which object the error is occurring against. Definitely could also go via the route of excluding the number fields, does the transformation feature convert them into non number field-names?
Copy code
- !*.*.1d_click
- !*.*.7d_click
- !*.*.28d_click
- !*.*.1d_view
- !*.*.7d_view
- !*.*.28d_view
d
The 
ads_insights
stream has a 
unique_actions
property containing an array of objects with 
1d_click
(etc) subproperties. It looks like target-snowflake is flattening 
ads_insights.unique_actions
into its own 
ADS_INSIGHTS__UNIQUE_ACTIONS
table with 
1d_click
(etc) columns, while other targets may leave a 
unique_actions
column on the 
ads_insights
table with a (JSON-encoded) array value
Since 
1d_click
is not a direct subproperty of 
unique_actions
, the selection rules I suggested above may not actually work, because Meltano doesn't currently drill down into array values
!*.unique_actions
should still work to drop that field entirely, and prevent a table from being created for it, and you'd want to do the same for the other stream properties that use that 
ads_action_stats.json
It's possible that we're looking at a different issue here than https://gitlab.com/meltano/meltano/-/issues/2381 and that that exclusion rule still won't work, but let's see where we get 🙂
u
Hi @douwe_maan, Thanks for the feedback. Added 
metadata
field:
Copy code
metadata:
    '*':
      '*.*':
        inclusion: available
I've just excluded:
Copy code
- !*.*.1d_click
- !*.*.7d_click
- !*.*.28d_click
- !*.*.1d_view
- !*.*.7d_view
- !*.*.28d_view
I've got the error:
Copy code
yaml.constructor.ConstructorError: could not determine a constructor for the tag '!*.*.1d_click'
  in "/project/meltano.yml", line 97, column 7
d
@umut_yalcinkaya Try wrapping the 
!*.*...
patterns in double quotes; it looks like YAML is currently trying to parse them as something other than strings
u
@douwe_maan Seems like workaround is useless. Generally, something wrong with 
exclude
statements. Is it possible to make a small call to check with you on Monday ? We are about run 
elt
jobs in production.
d
Is it possible to make a small call to check with you on Monday?
Gladly, do you see a time on https://calendly.com/douwem/half-hour-meeting?month=2020-12&date=2020-12-07 that might work?
u
Thanks for the invitation. I've select a period from your calendar and invited @ofer_kulka
o
yes, looking forward for the call. The sooner the better but I understand we hae one scheduled for Wednesday.
d
@ofer_kulka I can do today or tomorrow morning as well if that works better!
o
thanks for the heads up. Are you US or Europe based? i'll check with the engineer's availability
d
@ofer_kulka Based in Mexico City, so US Central time. You can click my avatar/username here in Slack to see my local time 🙂
Tomorrow I'll be available starting 10:30am, your 17:30
o
that works for us, are you still available then?
d
@ofer_kulka Yep, I'll send an invite to ofer@getpenta.com
o
thanks
d
@ofer_kulka Looks like @umut_yalcinkaya is available now!
If you like, we can meet now in https://gitlab.zoom.us/my/gitlab.douwemaan
o
yea i'll join. thanks
d
@ofer_kulka It's working now! We ended up going back to the datamill-co variant of target-snowflake, and instead we patched tap-facebook to exclude deselected fields in its schema messages: https://gitlab.com/meltano/tap-facebook/-/merge_requests/8
And I'll release a new version of tap-facebook with that fix
o
Great news, you are a rock star! Thanks so much for you time and efforts!
d
And https://gitlab.com/meltano/tap-facebook/-/merge_requests/8 has been merged, so @umut_yalcinkaya you can remove the 
@deselected-fields
from the 
pip_url
again!
And I've created a new issue to fix this for all taps in Meltano: https://gitlab.com/meltano/meltano/-/issues/2475
u
Copy code
target-snowflake | INFO MillisLoggingCursor: 86 millis spent executing:   \n              SHOW TABLES LIKE 'ADS_INSIGHTS_AGE_AND_GENDER' IN SCHEMA "MARKETING"."ANALYTICS"  \n
target-snowflake | INFO MillisLoggingCursor: 551 millis spent executing:   \n              SELECT column_name, data_type, is_nullable  \n              FROM "MARKETING".information_schema.columns  \n              WHERE table_schema = 'ANALYTICS' AND table_name = 'ADS_INSIGHTS_AGE_AND_GENDER'  \n
target-snowflake | INFO MillisLoggingCursor: 58 millis spent executing: ROLLBACK
target-snowflake | ERROR Exception writing records
target-snowflake | Traceback (most recent call last):
target-snowflake |   File "/app/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/snowflake.py", line 119, in write_batch
target-snowflake |     json_schema.get_type(current_table_schema['schema']['properties'][key_property]),
target-snowflake | KeyError: 'age'
target-snowflake | CRITICAL ('Exception writing records', KeyError('age'))
target-snowflake | INFO MillisLoggingCursor: 60 millis spent executing: ROLLBACK
target-snowflake | Traceback (most recent call last):
target-snowflake |   File "/app/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/snowflake.py", line 119, in write_batch
target-snowflake |     json_schema.get_type(current_table_schema['schema']['properties'][key_property]),
target-snowflake | KeyError: 'age'
target-snowflake |
target-snowflake | During handling of the above exception, another exception occurred:
target-snowflake |
target-snowflake | Traceback (most recent call last):
target-snowflake |   File "/app/.meltano/loaders/target-snowflake/venv/bin/target-snowflake", line 8, in <module>
target-snowflake |     sys.exit(cli())
target-snowflake |   File "/app/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/__init__.py", line 57, in cli
target-snowflake |     main(args.config)
target-snowflake |   File "/app/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/__init__.py", line 51, in main
target-snowflake |     target_tools.main(target)
target-snowflake |   File "/app/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_postgres/target_tools.py", line 28, in main
target-snowflake |     stream_to_target(input_stream, target, config=config)
target-snowflake |   File "/app/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_postgres/target_tools.py", line 77, in stream_to_target
target-snowflake |     raise e
target-snowflake |   File "/app/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_postgres/target_tools.py", line 70, in stream_to_target
target-snowflake |     state_tracker.flush_streams(force=True)
target-snowflake |   File "/app/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_postgres/stream_tracker.py", line 47, in flush_streams
target-snowflake |     self._write_batch_and_update_watermarks(stream)
target-snowflake |   File "/app/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_postgres/stream_tracker.py", line 67, in _write_batch_and_update_watermarks
target-snowflake |     self.target.write_batch(stream_buffer)
target-snowflake |   File "/app/.meltano/loaders/target-snowflake/venv/lib/python3.8/site-packages/target_snowflake/snowflake.py", line 164, in write_batch
target-snowflake |     raise SnowflakeError(message, ex)
target-snowflake | target_snowflake.exceptions.SnowflakeError: ('Exception writing records', KeyError('age'))
meltano          | Loading failed (1): target_snowflake.exceptions.SnowflakeError: ('Exception writing records', KeyError('age'))
meltano          | ELT could not be completed: Target failed
@douwe_maan something wrong with last merge. I've removed 
@deselected-fields
and run 
meltano elt tap-facebook target-snowflake
. So it prompts below message. Seems like a key error when parsing 
age
field on 
ADS_INSIGHTS_AGE_AND_GENDER
table.
o
Thanks for looking into this and helping out again 🙂 What we noticed though is that the pipeline successfully completes if we first drop the destination schema and then create a new schema, thus discarding the older data.
d
Interesting, this error is coming from here: https://github.com/datamill-co/target-snowflake/blob/master/target_snowflake/snowflake.py#L109-L124, and suggests that the type for the 
age
property in the schema doesn't match the type that was actually created in the DB
But it's failing to raise the "`key_properties` type change detected for" exception with additional details because the error code itself raises the KeyError you're seeing
Can you run 
meltano elt
in debug mode using 
meltano --log-level=debug elt
so that we get to see the offending 
SCHEMA
message for ADS_INSIGHTS_AGE_AND_GENDER?
And I suggest adding some 
print
statements ahead of https://github.com/datamill-co/target-snowflake/blob/master/target_snowflake/snowflake.py#L114 so that we can find out what is actually contained in 
current_table_schema
at the time the error is raised, and what the values of 
self.json_schema_to_sql_type(remote_column_schema)
and 
self.json_schema_to_sql_type(stream_buffer.schema['properties'][key_property])
are
That should help us figure out what it's actually complaining about
u
@douwe_maan its giving same error while I was running 
meltano --log-level=debug elt tap-facebook target-snowflake
. As @ofer_kulka said, dropping destination schema working around the issue. https://meltano.slack.com/archives/C013EKWA2Q1/p1607621277222300?thread_ts=1607020241.194800&amp;cid=C013EKWA2Q1
d
@umut_yalcinkaya Can you share the 
SCHEMA
message for the 
ADS_INSIGHTS_AGE_AND_GENDER
stream that was printed in a 
tap-facebook (out)
line?
The target appears to be complaining about a mismatch between the schema it received from the tap, and the actual database schema that was created by the target the previous time
So it makes sense that dropping the schema works around the issue 🙂
o
Thanks for the help! We will get to looking at it tomorrow morning with Umut. Strange that it behaves differently after the MR of yesterday. do you see any correlation to that?
d
@ofer_kulka The MR that went into the release had exactly the same code as the 
deselected-fields
branch Umut and I were using and got working yesterday, so nothing should have changed between those points. This new error could be related to the bug fix, but it doesn't look like it to me on the surface. Seeing the SCHEMA messages would help say that for sure 🙂 My hunch is that we're looking at something similar to https://meltano.slack.com/archives/CFG3C3C66/p1606866556000500?thread_ts=1605650543.370000&amp;cid=CFG3C3C66, where the problem is with the 
age
column claiming possible types 
integer
, 
string
, and 
null
, which the database can't represent, which could lead to the target complaining about the mismatch between the stream schema and the existing database schema, as we're seeing.
So to help debug this further, I'd like to see the 
SCHEMA
message for the offending stream, as well as the values for 
self.json_schema_to_sql_type(remote_column_schema)
 and 
self.json_schema_to_sql_type(stream_buffer.schema['properties'][key_property])
at the mentioned code site, since their mismatch causes the error we're seeing
o
@umut_yalcinkaya
d
@ofer_kulka @umut_yalcinkaya Shall we jump on another call today? Let me know when you'll be available, and we can meet in https://gitlab.zoom.us/my/gitlab.douwemaan
o
Hi yes sir
i'm available whenever.
d
@ofer_kulka I am too, for the next 3 hours or so.
When both of you are ready, give me a ping 🙂
u
Hi @ofer_kulka @douwe_maan I will be available in 10 min.
d
Great, that works for me
@ofer_kulka @umut_yalcinkaya https://gitlab.zoom.us/my/gitlab.douwemaan
pip_url: git+<https://gitlab.com/meltano/tap-facebook.git@age-property-type>
o
thanks very much for your help and assistance!
d
My pleasure, as always 🙂
Open in Slack
PreviousNext
Powered by