# Amazon Data Pulse

Amazon Music is now offering a data sharing system on AWS Lake Formation. 
The data sharing system (DataPulse) centralizes massive datasets in a scalable and secure manner. 
It provides access to this data for analytics, supporting a wide variety of AWS services.

https://musiccentral.amazon.com/help/datapulse

# Data Structure: DDL 

## fraud_report 

Daily count: `~5 K` records per day

```sql
CREATE EXTERNAL TABLE `fraud_report`(
  `master_vendor_code` string,
  `report_day` timestamp,
  `track_global_asin` string,
  `track_classic_asin` string,
  `track_dmid` bigint,
  `track_isrc` string,
  `track_name` string,
  `artist_name` string,
  `album_name` string,
  `label_name` string,
  `digital_upc` string,
  `ean` string,
  `track_fraud_streams` bigint,
  `legit_streams` bigint,
  `track_fraud_rate` double)
PARTITIONED BY (
  `cadence` string,
  `region` string,
  `report_date` string,
  `entity_name` string,
  `service` string,
  `music_territory` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://fcrm-fraud-report-prod/fraud-report/'
TBLPROPERTIES (
  'CRAWL_RUN_ID'='9e6dcc60-16fb-4c0f-8cf2-6bb8d6aa2322',
  'CrawlerSchemaDeserializerVersion'='1.0',
  'CrawlerSchemaSerializerVersion'='1.0',
  'UPDATED_BY_CRAWLER'='fraud-report',
  'averageRecordSize'='294',
  'classification'='parquet',
  'compressionType'='none',
  'objectCount'='561044',
  'partition_filtering.enabled'='true',
  'recordCount'='1574380',
  'sizeKey'='3705384571',
  'typeOfData'='file')
```

# daily_play_events

Daily count: `~250 M` records per day

```sql
CREATE EXTERNAL TABLE `daily_play_events`(
  `datasetdate` date, 
  `musicterritory` string, 
  `subscriptionterm` string, 
  `subscriptiontype` string, 
  `eventtype` string, 
  `eventtypeid` string, 
  `deviceeventdate` timestamp, 
  `eventrecorddate` timestamp, 
  `playduration` int, 
  `selectionsourcelisttype` string, 
  `selectionsourceid` string, 
  `programtype` string, 
  `encryptedcustomerid` string, 
  `encryptedsecondarycustomerid` string, 
  `cohortdate` string, 
  `devicetimezone` string, 
  `trackasin` string, 
  `trackisrc` string, 
  `trackgrid` string, 
  `trackartist` string, 
  `isfollowingartist` boolean, 
  `trackgenre` string, 
  `tracktitle` string, 
  `albumasin` string, 
  `albumphysicalupc` string, 
  `albumdigitalupc` string, 
  `albumean` string, 
  `albumtitle` string, 
  `contenttype` string, 
  `stationencstationkey` string, 
  `stationname` string, 
  `stationcategory` string, 
  `stationseedid` string, 
  `stationseedtype` string, 
  `playlistparentasin` string, 
  `playlistparentname` string, 
  `playlistparentgenre` string, 
  `playlistasin` string, 
  `playlistname` string, 
  `playlistgenre` string, 
  `playlisttracknumber` int, 
  `devicetype` string, 
  `devicesubtype` string, 
  `devicedesc` string, 
  `deviceplatform` string, 
  `devicesubplatform` string, 
  `postalcode` string, 
  `voiceplayindicator` string, 
  `bitrates` string, 
  `trackgenres` string, 
  `trackmoods` string, 
  `trackthemes` string, 
  `alllabelsplaysgrteqthirtysec` bigint, 
  `playmode` string, 
  `subscriptionplanname` string, 
  `entityname` string, 
  `alllabelplaysbysource` bigint, 
  `alllabelplaysbydevice` bigint, 
  `alllabelplaysbysourceanddevice` bigint, 
  `alllabelplaysbyplaylist` bigint, 
  `alllabelplaysbyplaylistandstation` bigint, 
  `subscriptiongroup` string, 
  `subscriptiondetail` string, 
  `subscriptionproduct` string)
PARTITIONED BY ( 
  `service` string, 
  `region` string, 
  `marketplace` string, 
  `territory` string, 
  `year` string, 
  `month` string, 
  `day` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://amrep-datasets-prod/daily_play_events/'
TBLPROPERTIES (
)
```

## daily_customer_metadata

Daily count: `~30 M` records per day

```sql
CREATE EXTERNAL TABLE `daily_customer_metadata`(
  `dataset_date` date, 
  `encrypted_customer_id` string, 
  `marketplace_id` int, 
  `sme_account_follows` bigint, 
  `personal_playlists` bigint, 
  `sme_playlists` bigint, 
  `third_party_playlists` bigint, 
  `total_playlists` bigint, 
  `promoted_playlists` bigint, 
  `total_tracks_in_collection` bigint, 
  `sme_tracks_in_collection` bigint, 
  `entity_name` string)
PARTITIONED BY ( 
  `region` string, 
  `marketplace` string, 
  `territory` string, 
  `year` string, 
  `month` string, 
  `day` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://music-paper-reporting-datasets-prod/daily_customer_metadata/'
TBLPROPERTIES (
  'CRAWL_RUN_ID'='67874e15-13dd-4321-9b51-ac9cfd72e6d0', 
  'CrawlerSchemaDeserializerVersion'='1.0', 
  'CrawlerSchemaSerializerVersion'='1.0', 
  'UPDATED_BY_CRAWLER'='daily_customer_metadata', 
  'averageRecordSize'='126', 
  'classification'='parquet', 
  'compressionType'='none', 
  'objectCount'='8151', 
  'partition_filtering.enabled'='true', 
  'recordCount'='711016309', 
  'sizeKey'='58987883911', 
  'typeOfData'='file')
```

## daily_playlist_metadata

Daily count: `~4.5 M` records per day

```sql
CREATE EXTERNAL TABLE `daily_playlist_metadata`(
  `dataset_date` date, 
  `marketplace_id` bigint, 
  `music_territory` string, 
  `playlist_parent_asin` string, 
  `playlist_parent_name` string, 
  `playlist_parent_genre` string, 
  `playlist_asin` string, 
  `playlist_name` string, 
  `playlist_genre` string, 
  `track_count` bigint, 
  `playlist_followers_count` bigint, 
  `encrypted_playlist_owner_id` string, 
  `entity_name` string)
PARTITIONED BY ( 
  `region` string, 
  `marketplace` string, 
  `territory` string, 
  `year` string, 
  `month` string, 
  `day` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://music-paper-reporting-datasets-prod/daily_playlist_metadata/'
TBLPROPERTIES (
  'CRAWL_RUN_ID'='af08ef65-2c51-4d90-b921-48eca7177cb3', 
  'CrawlerSchemaDeserializerVersion'='1.0', 
  'CrawlerSchemaSerializerVersion'='1.0', 
  'UPDATED_BY_CRAWLER'='daily_playlist_metadata', 
  'averageRecordSize'='145', 
  'classification'='parquet', 
  'compressionType'='none', 
  'objectCount'='16601', 
  'partition_filtering.enabled'='true', 
  'recordCount'='129722850', 
  'sizeKey'='13557857783', 
  'typeOfData'='file')
```

# Use Cases

## Trigger by lambda
* passes filly equipped partition.
* need to check presense report_date / download_date

## Trigger with no arguments
* passes NO partition info (should respect report_name, report_date, licensor?)