Closed asyarif93 closed 2 years ago
harshithmullapudi
commented
2 years ago Hey @asyarif93 I think the problem here is because the schema doesn't have the ref https://github.com/airbytehq/airbyte/blob/c97fe680bf6c01e92755584ccb63d753014e2e51/airbyte-integrations/connectors/source-zendesk-support/source_zendesk_support/schemas/tickets.json#L119. The fix I think would be to provide the required object here.
asyarif93
commented
2 years ago I bit not understand, doesn't via have ref to via_channel? and there's via_channel file in schema/shared
harshithmullapudi
commented
2 years ago Oops my bad. Yeah I could see the file. I just tried reproducing this issue. When I tried this
tickets and tickets_via tables created and I am able to see that data. asyarif93
commented
2 years ago this is odd. I'll try to make separate connection. to replicate my issue
asyarif93
commented
2 years ago I tried to make new connection by creating new source, with same destination (different prefix). via fields is no longer null and tickets_via table is created. I'm still puzzled why in old source it doesn't write query properly, as it has same exact version.
asyarif93
commented
2 years ago I think catalog.json created by source previous version might have been broken. solution might be updating schema. However in current ui, updating schema means sync from scratch (something I want to avoid)
misteryeo
commented
2 years ago @asyarif93 is this issue still ongoing for you?
asyarif93
commented
2 years ago it no longer happened in my system since I have update the schema in airbyte UI
misteryeo
commented
2 years ago Thanks! I'll close this issue out as we haven't had other reports of this so far.
Enviroment
Current Behavior
in table
ticketsitem in fieldviais{"channel":"api","source":{"rel":null,"from":{},"to":{}}}however in tableticketstheviafield isnullExpected Behavior
in table
tickets_custom_fieldsthevaluefield should be string or json objectLogs
If applicable, please upload the logs from the failing operation. For sync jobs, you can download the full logs from the UI by going to the sync attempt page and clicking the download logs button at the top right of the logs display window.
LOG
``` replace this with your long log output here ```Queries
Queries
```sql /* {"app": "dbt", "dbt_version": "0.19.0", "profile_name": "normalize", "target_name": "prod", "node_id": "model.airbyte_utils.tickets_scd"} */ create or replace table `d291209`.zendesk_staging.`tickets_scd` OPTIONS() as ( with __dbt__CTE__tickets_ab1 as ( -- SQL model to parse JSON blob stored in a single column and extract into separated field columns as described by the JSON Schema select json_extract_scalar(_airbyte_data, "$['id']") as id, json_extract_scalar(_airbyte_data, "$['url']") as url, json_extract(table_alias._airbyte_data, "$['']") as via, json_extract_array(_airbyte_data, "$['tags']") as tags, json_extract_scalar(_airbyte_data, "$['type']") as type, json_extract_scalar(_airbyte_data, "$['due_at']") as due_at, json_extract_scalar(_airbyte_data, "$['status']") as status, json_extract_scalar(_airbyte_data, "$['subject']") as subject, json_extract_scalar(_airbyte_data, "$['brand_id']") as brand_id, json_extract_scalar(_airbyte_data, "$['group_id']") as group_id, json_extract_scalar(_airbyte_data, "$['priority']") as priority, json_extract_scalar(_airbyte_data, "$['is_public']") as is_public, json_extract_scalar(_airbyte_data, "$['recipient']") as recipient, json_extract_scalar(_airbyte_data, "$['created_at']") as created_at, json_extract_scalar(_airbyte_data, "$['problem_id']") as problem_id, json_extract_scalar(_airbyte_data, "$['updated_at']") as updated_at, json_extract_scalar(_airbyte_data, "$['assignee_id']") as assignee_id, json_extract_scalar(_airbyte_data, "$['description']") as description, json_extract_scalar(_airbyte_data, "$['external_id']") as external_id, json_extract_scalar(_airbyte_data, "$['raw_subject']") as raw_subject, json_extract_array(_airbyte_data, "$['email_cc_ids']") as email_cc_ids, json_extract_array(_airbyte_data, "$['follower_ids']") as follower_ids, json_extract_array(_airbyte_data, "$['followup_ids']") as followup_ids, json_extract_scalar(_airbyte_data, "$['requester_id']") as requester_id, json_extract_scalar(_airbyte_data, "$['submitter_id']") as submitter_id, json_extract_array(_airbyte_data, "$['custom_fields']") as custom_fields, json_extract_scalar(_airbyte_data, "$['has_incidents']") as has_incidents, json_extract_scalar(_airbyte_data, "$['forum_topic_id']") as forum_topic_id, json_extract_scalar(_airbyte_data, "$['ticket_form_id']") as ticket_form_id, json_extract_scalar(_airbyte_data, "$['organization_id']") as organization_id, json_extract_array(_airbyte_data, "$['collaborator_ids']") as collaborator_ids, json_extract_scalar(_airbyte_data, "$['allow_attachments']") as allow_attachments, json_extract_scalar(_airbyte_data, "$['allow_channelback']") as allow_channelback, json_extract_scalar(_airbyte_data, "$['generated_timestamp']") as generated_timestamp, json_extract(table_alias._airbyte_data, "$['satisfaction_rating']") as satisfaction_rating, json_extract_array(_airbyte_data, "$['sharing_agreement_ids']") as sharing_agreement_ids, _airbyte_emitted_at from `d291209`.zendesk_staging._airbyte_raw_tickets as table_alias -- tickets ), __dbt__CTE__tickets_ab2 as ( -- SQL model to cast each column to its adequate SQL type converted from the JSON schema type select cast(id as int64 ) as id, cast(url as string ) as url, via, tags, cast(type as string ) as type, cast(nullif(due_at, '') as timestamp ) as due_at, cast(status as string ) as status, cast(subject as string ) as subject, cast(brand_id as int64 ) as brand_id, cast(group_id as int64 ) as group_id, cast(priority as string ) as priority, cast(is_public as boolean) as is_public, cast(recipient as string ) as recipient, cast(nullif(created_at, '') as timestamp ) as created_at, cast(problem_id as int64 ) as problem_id, cast(nullif(updated_at, '') as timestamp ) as updated_at, cast(assignee_id as int64 ) as assignee_id, cast(description as string ) as description, cast(external_id as string ) as external_id, cast(raw_subject as string ) as raw_subject, email_cc_ids, follower_ids, followup_ids, cast(requester_id as int64 ) as requester_id, cast(submitter_id as int64 ) as submitter_id, custom_fields, cast(has_incidents as boolean) as has_incidents, cast(forum_topic_id as int64 ) as forum_topic_id, cast(ticket_form_id as int64 ) as ticket_form_id, cast(organization_id as int64 ) as organization_id, collaborator_ids, cast(allow_attachments as boolean) as allow_attachments, cast(allow_channelback as boolean) as allow_channelback, cast(generated_timestamp as int64 ) as generated_timestamp, cast(satisfaction_rating as string ) as satisfaction_rating, sharing_agreement_ids, _airbyte_emitted_at from __dbt__CTE__tickets_ab1 -- tickets ), __dbt__CTE__tickets_ab3 as ( -- SQL model to build a hash column based on the values of this record select to_hex(md5(cast(concat(coalesce(cast(id as string ), ''), '-', coalesce(cast(url as string ), ''), '-', coalesce(cast(via as string ), ''), '-', coalesce(cast(array_to_string(tags, "|", "") as string ), ''), '-', coalesce(cast(type as string ), ''), '-', coalesce(cast(due_at as string ), ''), '-', coalesce(cast(status as string ), ''), '-', coalesce(cast(subject as string ), ''), '-', coalesce(cast(brand_id as string ), ''), '-', coalesce(cast(group_id as string ), ''), '-', coalesce(cast(priority as string ), ''), '-', coalesce(cast(is_public as string ), ''), '-', coalesce(cast(recipient as string ), ''), '-', coalesce(cast(created_at as string ), ''), '-', coalesce(cast(problem_id as string ), ''), '-', coalesce(cast(updated_at as string ), ''), '-', coalesce(cast(assignee_id as string ), ''), '-', coalesce(cast(description as string ), ''), '-', coalesce(cast(external_id as string ), ''), '-', coalesce(cast(raw_subject as string ), ''), '-', coalesce(cast(array_to_string(email_cc_ids, "|", "") as string ), ''), '-', coalesce(cast(array_to_string(follower_ids, "|", "") as string ), ''), '-', coalesce(cast(array_to_string(followup_ids, "|", "") as string ), ''), '-', coalesce(cast(requester_id as string ), ''), '-', coalesce(cast(submitter_id as string ), ''), '-', coalesce(cast(array_to_string(custom_fields, "|", "") as string ), ''), '-', coalesce(cast(has_incidents as string ), ''), '-', coalesce(cast(forum_topic_id as string ), ''), '-', coalesce(cast(ticket_form_id as string ), ''), '-', coalesce(cast(organization_id as string ), ''), '-', coalesce(cast(array_to_string(collaborator_ids, "|", "") as string ), ''), '-', coalesce(cast(allow_attachments as string ), ''), '-', coalesce(cast(allow_channelback as string ), ''), '-', coalesce(cast(generated_timestamp as string ), ''), '-', coalesce(cast(satisfaction_rating as string ), ''), '-', coalesce(cast(array_to_string(sharing_agreement_ids, "|", "") as string ), '')) as string ))) as _airbyte_tickets_hashid, tmp.* from __dbt__CTE__tickets_ab2 tmp -- tickets ), __dbt__CTE__tickets_ab4 as ( -- SQL model to prepare for deduplicating records based on the hash record column select row_number() over ( partition by _airbyte_tickets_hashid order by _airbyte_emitted_at asc ) as _airbyte_row_num, tmp.* from __dbt__CTE__tickets_ab3 tmp -- tickets from `d291209`.zendesk_staging._airbyte_raw_tickets )-- SQL model to build a Type 2 Slowly Changing Dimension (SCD) table for each record identified by their primary key select id, url, via, tags, type, due_at, status, subject, brand_id, group_id, priority, is_public, recipient, created_at, problem_id, updated_at, assignee_id, description, external_id, raw_subject, email_cc_ids, follower_ids, followup_ids, requester_id, submitter_id, custom_fields, has_incidents, forum_topic_id, ticket_form_id, organization_id, collaborator_ids, allow_attachments, allow_channelback, generated_timestamp, satisfaction_rating, sharing_agreement_ids, generated_timestamp as _airbyte_start_at, lag(generated_timestamp) over ( partition by id order by generated_timestamp is null asc, generated_timestamp desc, _airbyte_emitted_at desc ) as _airbyte_end_at, case when lag(generated_timestamp) over ( partition by id order by generated_timestamp is null asc, generated_timestamp desc, _airbyte_emitted_at desc ) is null then 1 else 0 end as _airbyte_active_row, _airbyte_emitted_at, _airbyte_tickets_hashid from __dbt__CTE__tickets_ab4 -- tickets from `d291209`.zendesk_staging._airbyte_raw_tickets where _airbyte_row_num = 1 ); ```Steps to Reproduce
ticketsAre you willing to submit a PR?
Not Sure