# How to import participants from Apple dataset

## Requirements:

1. Apple data set in a TSV/CSV format with csv file containing 3 columns **isrc, artist_name, artist_id**

## Goal:

Our goal is to match as many participants from apple dataset to orchard dataset and import it into **art_relations.participant_identifier** table.

## Steps:

1. The tsv/csv might contain more than the 3 columns required, hence it will be good to some preprocessing to remove the not required columns. Also the file may contain multiple artists for each isrc with artist_name and artists_id in pipe delimited column value. We need to flatten the pipe delimited artist_name and artist_id to a single artists per line.

   <u>Example of Single artist per line</u>

   ```bash
   isrc,artist_name,artist_id
   USA370300010,The Houndz,3937656
   USA370300011,The Houndz,3937656
   ...
   ```

   Example of Multiple artists per line

   ```bash
   isrc,artist_name,artist_id
   USA370300886	Kenny Burrell & Lamont Johnson	119995|3940778
   USA370300887	Kenny Burrell & Lamont Johnson	119995|3940778
   ...
   ```

2. Once we have the single line per csv, import the csv file to a staging table in MySQL. The structure of the staging table can be as follows

   ```sql
   
   create table orchard_apple_tracks
   (
   	track_isrc varchar(16) null,
   	primary_artist_name varchar(1024) null,
   	primary_artist_id int null
   );
   ```

3. Create a table **participant_identifier_matched** similar to participant_identifer table to populate the matched records into this table. The table has a unique contraint on vendor_id, subaccount_id, store_id, orchard_artist_name_md5 columns

4. Once the staging table is imported with the CSV data, we will run the matching query to match the apple dataset artists with orchard dataset. The query for the matching should return unique records for **participant_identifier** table and the matches are inserted into the  **participant_identifier_matched** we created in step 3. The query for the SQL script is in the file - **participant_identifier_matcher.sql**

   ```SQL
   --  participant_identifier_matcher.sql
   -- Mactcher SQL 
   INSERT INTO participant_identifier_matched
   SELECT NULL,
          vendor_id,
          subaccount_id,
          1 AS store_id,
          orchard_artist_name_md5,
          orchard_artist_name as orchard_artist_name,
          store_artist_id,
          created_at,
          last_updated
   FROM (SELECT ta.NAME,
                p.vendor_id,
                p.subaccount_id,
                1                    AS store_id,
                Md5(trim(ta.NAME))         AS orchard_artist_name_md5,
                trim(ta.NAME)              AS orchard_artist_name,
                oa.primary_artist_id AS store_artist_id,
                Now()                AS created_at,
                Now()                AS last_updated,
                Count(*)
         FROM orchard_apple_tracks oa
                  INNER JOIN track t
                             ON t.isrc = oa.track_isrc
                  INNER JOIN track_artist ta
                             ON ta.track_id = t.id
                  INNER JOIN releases r
                             ON r.release_id = t.track_id
                  INNER JOIN project p
                             ON p.project_id = r.project_id
         WHERE ta.type = 'performer'
           AND oa.primary_artist_id IS NOT NULL
         GROUP BY orchard_artist_name,
                  p.vendor_id,
                  p.subaccount_id
         HAVING Count(*) = 1) temp
   WHERE NOT EXISTS(SELECT vendor_id,
                           subaccount_id,
                           store_id,
                           orchard_artist_name_md5
                    FROM participant_identifier pi
                    WHERE pi.vendor_id = temp.vendor_id
                      and pi.subaccount_id = temp.subaccount_id
                      and pi.store_id = temp.store_id
                      and pi.orchard_artist_name_md5 = temp.orchard_artist_name_md5);
   ```

   

5. Once the participant_identifier_matcher file is populated export the matched records in the table to a CSV file. The CSV fle should have the following format

   ```bash
   vendor_id,subaccount_id,store_id,orchard_artist_name_md5,orchard_artist_name,store_artist_id
   6888,0,1,8d0d7d986db54e9d9bef1e4d75ae5be3,!distain,193578324
   7123,0,1,9d3aedbbab2ab07a2d7273b4c1a50a05,!maavin,1380579437
   ```

6. Once you have the matched CSV file, upload the file to Amazon S3 location 

7. We need to import the file in to participant_identifier table in QA. Use the SQL script using the database PR. Ask systems to stage the file from S3 to QA machine before you run database migration. The database migration script **"database/art_relations/build/changelog/dml/ARTARCH-230-populate-participant-identifier-with-apple-store-ids.sql"** loads the CSV file into participant_identifier table using the following SQL command

   ```SQL
   --- ARTARCH-230-populate-participant-identifier-with-apple-store-ids.sql
   --liquibase formatted SQL
   
   --changeset sbhowmick:1
   
   CREATE TABLE IF NOT EXISTS `ROLLBACK-ARTARCH-230-participant_identifier`
   SELECT * FROM participant_identifier;
   
   LOAD DATA INFILE '/tmp/art_relations_participant_identifier_apple.csv' INTO TABLE participant_identifier FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
   IGNORE 1 ROWS
   (vendor_id,subaccount_id,store_id,orchard_artist_name_md5,orchard_artist_name,store_artist_id) SET ID = NULL, created_at = CURRENT_TIMESTAMP(), last_updated = CURRENT_TIMESTAMP();
   
   --rollback DROP TABLE IF EXISTS participant_identifier;
   --rollback RENAME TABLE `ROLLBACK-ARTARCH-230-participant_identifier` to participant_identifier;
   ```

8. Use the Jenkins db_deploy job which will execute the database migration script to load the csv in to the participant_identifier table