# bulk-asset-uploader

## Summary

The bulk-asset-uploader is the kick-off script which invokes the step machine found at:

QA:

[qa-bulk-assets-ingester-sfn](https://console.aws.amazon.com/states/home?region=us-east-1#/statemachines/view/arn:aws:states:us-east-1:437795906767:stateMachine:qa-bulk-assets-ingester-sfn)
| [Terraform Definition](https://github.com/theorchard/terraform-infra/blob/master/qa/bulk-assets-ingester/sfn_bulk_assets_ingester.tf)
| [SFN Definition](https://github.com/theorchard/terraform-infra/blob/master/qa/bulk-assets-ingester/sfn-bulk-assets-ingester.json)

Prod:

[prod-bulk-assets-ingester-sfn](https://console.aws.amazon.com/states/home?region=us-east-1#/statemachines/view/arn:aws:states:us-east-1:437795906767:stateMachine:prod-bulk-assets-ingester-sfn)
| [Terraform Definition](https://github.com/theorchard/terraform-infra/blob/master/prod/bulk-assets-ingester/sfn_bulk_assets_ingester.tf)
| [SFN Definition](https://github.com/theorchard/terraform-infra/blob/master/prod/bulk-assets-ingester/sfn-bulk-assets-ingester.json)

## Detail

### Environment Vars
```
Environment=QA  # PROD
AWS_ACCESS_KEY_ID=<AWS_KEY>
AWS_SECRET_ACCESS_KEY=<AWS_SECRET>

# ########################################

# Environment
APPLICATION_NAME=bulk-asset-uploader

# LOGGER_DSN is not needed for OWSLogger 2.x
LOGGER_DSN=
LOGGER_LEVEL='INFO'

# Sentry
export SENTRY_DSN=

# Snowflake
SNOWFLAKE_ACCOUNT=orchard
SNOWFLAKE_USER=<username>
SNOWFLAKE_PASSWORD=<pass>
SNOWFLAKE_ROLE=DEV_ENGINEERING
SNOWFLAKE_WAREHOUSE=DEV_OWS_WAREHOUSE
SNOWFLAKE_DATABASE=
SNOWFLAKE_SCHEMA=
SNOWFLAKE_KEY_PASSPHRASE=<PEM.8 File passphrase>

# APP SPECIFIC --------------- #
BULK_ASSETS_INGESTER_SFN_ARN=arn:aws:states:us-east-1:437795906767:stateMachine:qa-bulk-assets-ingester-sfn
BULK_ASSETS_INGESTER_SFN_NAME=qa-bulk-assets-ingester-sfn
CONCURRENT_STATE_MACHINE_COUNT=3

# ------- Snowflake source table
SOURCE_TABLE_NAME=Smithsonian_ASSET_ALL

OUTPUT_S3_BUCKET=qa-bulk-assets-ingester
OUTPUT_S3_LOCATION=bulk-assets
```

### SQL

This kick off script executes the following SQL, which places a JSON file in the bucket defined in `OUTPUT_S3_BUCKET`

```
-- Drop file into trigger bucket
COPY INTO 's3://<OUTPUT_S3_BUCKET>/<OUTPUT_S3_LOCATION>/<SOURCE_TABLE_NAME>_<time stamp>'
FROM (
	SELECT array_agg(object_construct(*)) WITHIN GROUP(ORDER BY ORCH_UPC asc, "VOLUME" asc, TRACK asc)
	FROM (
		SELECT *
		FROM <SNOWFLAKE_DATABASE>.<SNOWFLAKE_SCHEMA>.<SOURCE_TABLE_NAME> 
		ORDER BY ORCH_UPC ASC, "VOLUME" ASC, TRACK ASC
		LIMIT <LIMIT>
		OFFSET <OFFSET>
	))
CREDENTIALS = (
    aws_key_id=<AWS_KEY>
    aws_secret_key=<AWS_SECRET>
  )
FILE_FORMAT = (
 	TYPE=JSON
 	COMPRESSION=NONE
 )
 ;
```

The values of the `SNOWFLAKE_S3_BUCKET` and the `SNOWFLAKE_S3_FOLDER` should match the values of
`bulk_assets_ingester_input_bucket` and `bulk_assets_ingester_prefix` from the
[lambda-bulk-assets-ingester Terraform](https://github.com/theorchard/terraform-infra/blob/master/qa/bulk-assets-ingester/variables.tf)
respectively. (This is tied to the [Cloudwatch Event](#cloudwatch-event) below.)

### JSON Output File

The format of the output json file created by this query is a list of JSON objects, composed as follows

*(all data expanded here for clarity is on one line in the actual `.json` file):*

```
Example:

[
  {
    "ASSET_TYPE": "cover",
    "FILE_NAME": "6420565334145.tif",
    "FILE_SIZE": 27043352,
    "FOREIGN_REL_ID": "6420565334145",
    "ORCH_PROJECT_CODE": "6420565334145",
    "ORCH_RELEASE_ID": 3265123,
    "ORCH_UPC": 6420565334145,
    "PATH": "6420565334145/",
    "S3_BUCKET": "prod-orcd-catmusic-drop",
    "SESSION_ID": "",
    "TRACK": 0,
    "UPLOAD_DATE": "2021-01-29",
    "VENDOR_ID": 8995,
    "VOLUME": 0
  },
  {
    "ASSET_TYPE": "cover",
    "FILE_NAME": "6420565344274.wav",
    "FILE_SIZE": 27023716,
    "FOREIGN_REL_ID": "6420565344274",
    "ORCH_PROJECT_CODE": "6420565344274",
    "ORCH_RELEASE_ID": 3265177,
    "ORCH_UPC": 6420565344274,
    "PATH": "6420565344274/",
    "S3_BUCKET": "prod-orcd-catmusic-drop",
    "SESSION_ID": "",
    "TRACK": 1,
    "UPLOAD_DATE": "2021-01-29",
    "VENDOR_ID": 8995,
    "VOLUME": 1
  }
  .
  .
  .
  etc.
]
```

### Cloudwatch Event

This PutObject operation on S3 caused by this script depositing a file invokes a Cloudwatch Event which triggers the
[trigger_sfn](https://github.com/theorchard/lambda-bulk-asset-ingester/tree/master/lambda/trigger_sfn) lambda in
[lambda-bulk-asset-ingester](https://github.com/theorchard/lambda-bulk-asset-ingester/), thus beginning the bulk asset
ingestion orchestration.

QA:
([Terraform Definition](https://github.com/theorchard/terraform-infra/blob/master/qa/bulk-assets-ingester/sfn_bulk_assets_ingester_cloudwatch_event.tf)
| [Rule Definition](https://github.com/theorchard/terraform-infra/blob/master/qa/bulk-assets-ingester/sfn-bulk-assets-ingester-cloudwatch-event-rule.json))

Prod:
([Terraform Definition](https://github.com/theorchard/terraform-infra/blob/master/prod/bulk-assets-ingester/sfn_bulk_assets_ingester_cloudwatch_event.tf)
| [Rule Definition](https://github.com/theorchard/terraform-infra/blob/master/prod/bulk-assets-ingester/sfn-bulk-assets-ingester-cloudwatch-event-rule.json))