https://linen.dev logo
Join Slack
Powered by
Hello there :slightly_smiling_face: First I want s...
# troubleshooting
m
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 
associations
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
j
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 
airbyte_companies_hashid
?
Thanks, but I can't find such a column or property in the 
_airbyte_raw_hubspot_deals
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 
"deals"
to have associations for 
companies
and 
line_items
. 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%'
p
So you want to associate deals table with which table?
_airbyte_raw_hubspot_line_items
and also 
_airbyte_raw_hubspot_companies
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 
contacts
for example) 
deals
to have an array of 
line_items
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;
                                                        jsonb_pretty
----------------------------------------------------------------------------------------------------------------------------
 {                                                                                                                         +
     "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 
deals
table would have a 
line_items
array inside the json in the 
_airbyte_data
column, then I would join each ID in the array with the 
id
field from the json in the the 
_airbyte_data
column in the 
line_items
table. Sorry, that I can't follow what you mean by columns.
j
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 
deals
, 
companies
which should have the same column(hash_id) using which you can map
a
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
Thanks!
Open in Slack
PreviousNext
Powered by