bridg-people v0.1.0

step1: export sys_contact details for the given client

using following query

./presto --user ubuntu --execute "SELECT first_name, last_name, email FROM mysql.ecinity.sys_contact WHERE account_id IN (41768,42521,42522,42523,42524) AND first_name IS NOT NULL AND LENGTH(first_name) > 1 AND last_name IS NOT NULL AND LENGTH(last_name) > 1 AND email IS NOT NULL AND LENGTH(email) > 1 ;" --output-format CSV_HEADER > blatt_contacts_2.csv

step2: get the output file

(konagrill_contacts.csv)

clone standalone-reveal-contacts-app

run reveal_contact.js providing <the_ouput_file_path> and <account_name>

node reveal_contacts.js --contacts_file_path ./roja_contacts_2.csv --client_code roja

step3: connect to people database and verify the tables

connection: psql -h db-people-master.prod.bridg.com people bridg pwd: XXXXXXXXXXXXX

• check table created for the given account <account_name>_provider_temp and <account_name>_aggregate_temp

• INSERT INTO bridg_contacts_people (SELECT * FROM roja_provider_temp) ON CONFLICT DO NOTHING;

• INSERT INTO aggregate_people (SELECT * FROM roja_aggregate_temp) ON CONFLICT DO NOTHING;

• INSERT INTO bridg_contacts_people (SELECT * FROM <account_name>_provider_temp) ON CONFLICT DO NOTHING;

• INSERT INTO aggregate_people (SELECT * FROM <account_name>_aggregate_temp) ON CONFLICT DO NOTHING;

reveal contacts for chipotle:

before:

people=> select count(1) from bridg_contacts_people;

count
---------
 5223475
(1 row)

INSERT INTO bridg_contacts_people (email, data)
SELECT email, data from chipotle_provider_temp
ON CONFLICT DO NOTHING;

result : INSERT 0 233829

after:

people=> select count(1) from bridg_contacts_people;                                                                                                                   
count
---------
 5457304
(1 row)

before:

verify data before inserting

select json_extract(data, '$.names') from chipotle_aggregate_temp limit 10;

people=> select count(1) from aggregate_people;
count
---------
 5237376
(1 row)

INSERT INTO aggregate_people (email, data) 
SELECT email, data from chipotle_aggregate_temp 
ON CONFLICT DO NOTHING;

result: INSERT 0 233522

after:

people=> select count(1) from aggregate_people;                                                                                                                         
count
---------
 5470898
(1 row)

verify data after inserting

select json_extract(data, '$.names') from aggregate_people limit 10;

create table aggregate_contacts_test as 
with 
names as (
select TRY_CAST(json_extract(data, '$.names') as array<map<varchar,varchar>>) as names 
from people.public.aggregate_people_view
)
select * from names;

Few example commands:

node reveal_contacts.js --contacts_file_path ../stonefire_contacts.csv --client_code stonefire

node reveal_contacts.js --contacts_file_path ./konagrill_contacts.csv --client_code konagrill

INSERT INTO bridg_contacts_people (SELECT * FROM konagrill_provider_temp) ON CONFLICT DO NOTHING;

INSERT INTO aggregate_people (SELECT * FROM konagrill_aggregate_temp) ON CONFLICT DO NOTHING;

my sql query

* SELECT first_name, last_name, email FROM sys_contact WHERE account_id IN (SELECT id FROM sys_account WHERE id = 37766 OR managing_agency_account_id = 37766) AND first_name IS NOT NULL AND LENGTH(first_name) > 1 AND last_name IS NOT NULL AND LENGTH(last_name) > 1 AND email IS NOT NULL AND LENGTH(email) > 1 limit 10;

//alternate query
* SELECT sc.first_name, sc.last_name, sc.email FROM sys_contact sc, sys_account sa WHERE sc.account_id = sa.id AND (sa.id = 37766 OR sa.managing_agency_account_id = 37766) AND first_name IS NOT NULL AND LENGTH(first_name) > 1 AND last_name IS NOT NULL AND LENGTH(last_name) > 1 AND email IS NOT NULL AND LENGTH(email) > 1;

./presto --user ubuntu --schema mysql --execute "SELECT first_name, last_name, email FROM sys_contact WHERE account_id IN (SELECT id FROM sys_account WHERE id = 37766 OR managing_agency_account_id = 37766) AND first_name IS NOT NULL AND LENGTH(first_name) > 1 AND last_name IS NOT NULL AND LENGTH(last_name) > 1 AND email IS NOT NULL AND LENGTH(email) > 1 limit 10;" --output-format CSV_HEADER > chipotle_contacts.csv

./presto --user ubuntu --schema chipotle --execute "SELECT * FROM chipotle.txn limit 10;" --output-format CSV_HEADER > chipotle_txns.csv