Sabbiu Shah
02/08/2023, 8:37 AMsource-connector to add account_id in response body and schema.
- If I go with this approach I'll have to create source myself and cannot utilize existing one.
2. Load data to different postgres schema, per account. And, combine data from all postgres schema into a global one.
- How should I approach the combination of data?
- Will I be able to run normalization on the combined data (using airbyte's normalization module)?
Question:
What is the recommended approach to achieve this? Is there some easy to use tools developed already? Is there a better approach to handle this scenario?
What I have found...
• https://docs.airbyte.com/understanding-airbyte/namespaces#--custom-format
• https://airbytehq.slack.com/archives/C019WEENQRM/p1633512549383200?thread_ts=1633498797.374500&cid=C019WEENQRM
using prefixes (or custom namespace) to dispatch your source data per account in the destination. and then, using a custom SQL operations at the end of the sync to union all dispatched tables back into a single table but with an added column denoting which account it is coming from
Akilesh V
02/08/2023, 8:59 AMSabbiu Shah
02/08/2023, 9:05 AM<prefix>_categories tables.
After that I will have to write a code that combines and puts data into a single table. And, then run the normalization? How can I handle the combination and normalization part?Akilesh V
02/08/2023, 10:11 AMSELECT col1, col2, col3, 'tenant_id' as source
FROM table1
UNION
SELECT colA, colB, colC, 'tenant_id' as source
FROM table2;
but my recommended would be write custom dbtSabbiu Shah
02/08/2023, 1:07 PM-- initially, I have tables for two customers "cus_1" and "cus_2"
select _airbyte_ab_id, _airbyte_data, _airbyte_emitted_at, 'cus_1' as source from _airbyte_raw_cus1_categories
union
select _airbyte_ab_id, _airbyte_data, _airbyte_emitted_at, 'cus_2' as source from _airbyte_raw_cus2_categories;
select _airbyte_ab_id, _airbyte_data, _airbyte_emitted_at, 'cus_1' as source from _airbyte_raw_cus1_locations
union
select _airbyte_ab_id, _airbyte_data, _airbyte_emitted_at, 'cus_2' as source from _airbyte_raw_cus2_locations;
--- create combined categories table
create table _airbyte_raw_categories
(
_airbyte_ab_id varchar not null
constraint _airbyte_raw_categories_pkey
primary key,
_airbyte_data jsonb,
_airbyte_emitted_at timestamp with time zone default CURRENT_TIMESTAMP
);
alter table _airbyte_raw_categories
owner to postgres;
--- create combined locations table
create table _airbyte_raw_locations
(
_airbyte_ab_id varchar not null
constraint _airbyte_raw_locations_pkey
primary key,
_airbyte_data jsonb,
_airbyte_emitted_at timestamp with time zone default CURRENT_TIMESTAMP
);
alter table _airbyte_raw_locations
owner to postgres;
--- source and _airbyte_ab_id to new table
create table _airbyte_source
(
_airbyte_ab_id varchar not null
constraint _airbyte_source_pkey
primary key,
source varchar
);
alter table _airbyte_source owner to postgres;
--- lets link source and airbyte_id for future reference
-- think about
-- 1. copy only incremental data (using _airbyte_emitted_at)
-- 2. handle on conflict (copy conflicted result to new table??)
INSERT INTO _airbyte_source
SELECT _airbyte_ab_id, source from (
select _airbyte_ab_id, 'cus_1' as source from _airbyte_raw_cus1_categories
union
select _airbyte_ab_id, 'cus_2' as source from _airbyte_raw_cus2_categories
union
select _airbyte_ab_id, 'cus_1' as source from _airbyte_raw_cus1_locations
union
select _airbyte_ab_id, 'cus_2' as source from _airbyte_raw_cus2_locations
) as idsourcemapping;
select * from _airbyte_source;
-- lets merge airbyte raw categories
INSERT INTO _airbyte_raw_categories
SELECT * from (
select * from _airbyte_raw_cus1_categories -- add where clause for incremental update
union
select * from _airbyte_raw_cus2_categories
) as allcategories;
select * from _airbyte_raw_categories;
-- lets merge airbyte raw locations
INSERT INTO _airbyte_raw_locations
SELECT * FROM (
select * from _airbyte_raw_cus1_locations -- add where clause for incremental update
union
select * from _airbyte_raw_cus2_locations
) as alllocations;
select * from _airbyte_raw_locations;
-- then, use normalization module for the combined tablesNataly Merezhuk (Airbyte)
02/13/2023, 7:23 PM