Hello there :slightly_smiling_face: First I want s...
# troubleshooting
Hello there 🙂 First I want say that using Airbyte has bean a pretty great experience so far. The UI and available resources are awesome! Not sure if this is the right place but I am struggling with the Hubspot source output: I cannot find a way to join companies and line items to the deals data. Looking at the source code I noticed that
are specified for certain data types. Are these associations simply missing? But I feel more like I am doing something wrong since I imagine that anyone using the source would have a need for these links. So I am probably simply not seeing something in the existing data. Thanks, Jorin
Hey is this the same issue you are facing?
Hi @Harshith (Airbyte), thanks for getting back to me! To clarify, this is no syncing issue. The sync finishes successfully. I am not using normalization at the moment though. My issue is simply that I don't find any suitable IDs in any tables to link line items to deals. And the same issue with companies. I try to find out which field to use for joining. Thanks
You can use
Thanks, but I can't find such a column or property in the
table. (This is in Postgres btw. Probably not a destination-specific thing though)
I am wondering if the associations I am trying to use are simply missing at the moment. Similar to this PR, I would expect
to have associations for
. But I also don't feel like I understand the inner workings of this good enough. Maybe these associations are not needed and there are already ways to join the data.
If any data is a nested json of one column there will be hashid columns which you will find to associate them
There are no hash IDs anywhere in the data in the deals table. Basically this finds nothing at all:
select * from airbyte._airbyte_raw_hubspot_deals where _airbyte_data::text like '%hash%'
So you want to associate deals table with which table?
and also
Can you share the columns for both of these tables?
Sorry, not sure what you mean by that. To rephrase it: There is a relationship between deals and line items (let's forget about companies for a moment). The relation ship is many-to-many. So I would expect either a separate table to link deals to line items or (like we can see with
for example)
to have an array of
in the JSON. Does that make sense?
A deal has this contacts array, but no line_items array. So instead I try to figure out if there is a different way to join the two tables:
Copy code
select jsonb_pretty(_airbyte_data) from airbyte._airbyte_raw_hubspot_deals limit 1;
 {                                                                                                                         +
     "id": "173151854",                                                                                                    +
     "archived": false,                                                                                                    +
     "contacts": [                                                                                                         +
         "170801"                                                                                                          +
     ],                                                                                                                    +
     "createdAt": "2017-07-14T13:34:03.412Z",                                                                              +
     "dealstage": {                                                                                                        +
I am more curious on the column names just
As far as I can see the columns I need do not exist yet. That's why I cannot tell their names. Ideally the
table would have a
array inside the json in the
column, then I would join each ID in the array with the
field from the json in the the
column in the
table. Sorry, that I can't follow what you mean by columns.
So this is what my understanding here is 1. We have table hubspot right which is like the main table which has all the data -> this will have a hash_id based column 2. We also have those nested tables
which should have the same column(hash_id) using which you can map
Hey Harshith, thanks for bearing with me. Unfortunately what I am seeing doesn't match what you describe. There is no main hubspot table. These are the tables Airbyte generates in Postgres:
Copy code
\dt airbyte._airbyte_raw_hubspot_*
                             List of relations
 Schema  |                      Name                      | Type  |  Owner
 airbyte | _airbyte_raw_hubspot_companies                 | table | airbyte
 airbyte | _airbyte_raw_hubspot_contact_lists             | table | airbyte
 airbyte | _airbyte_raw_hubspot_contacts                  | table | airbyte
 airbyte | _airbyte_raw_hubspot_contacts_list_memberships | table | airbyte
 airbyte | _airbyte_raw_hubspot_deal_pipelines            | table | airbyte
 airbyte | _airbyte_raw_hubspot_deals                     | table | airbyte
 airbyte | _airbyte_raw_hubspot_line_items                | table | airbyte
 airbyte | _airbyte_raw_hubspot_owners                    | table | airbyte
 airbyte | _airbyte_raw_hubspot_products                  | table | airbyte
 airbyte | _airbyte_raw_hubspot_property_history          | table | airbyte
 airbyte | _airbyte_raw_hubspot_quotes                    | table | airbyte
(11 rows)
Maybe systems other than Postgres actually work completely different? But it's also fine to stop this here. I feel like I already took too much of your time. I will search more in the tables and then I might try forking Airbyte to add the associations I think are missing. Let's see if that works. Luckily it is open source. Thank you for your help 🙏
Sure do get back to us if you are still unable to figure out