Snowflake Databases
========

This folder contains queries against databases within the Snowflake warehouse. Changes to different databases are managed within different subfolders. Currently, two databases are supported: `FACTS` and `PROD`. Within each subfolder are the Liquibase properties files and SQL files corresponding to that database.

While this folder is still getting stood up, please consult the `dw` folder one level up regarding which tables in Snowflake can be updated through this deployment process.

### Usage

Database changes are performed using Liquibase. To test out locally for the database `database`, do the following:

1. Create DATABSECHANGELOG & DATABASECHANGELOGLOCK tables
These must be created manually bc liquibase's automated table creation uses the wrong TIMESTAMP type.

```
CREATE TABLE DATABASECHANGELOG (
    ID VARCHAR(255) NOT NULL,
    AUTHOR VARCHAR(255) NOT NULL,
    FILENAME VARCHAR(255) NOT NULL,
    DATEEXECUTED TIMESTAMP_LTZ(9) NOT NULL,
    ORDEREXECUTED NUMBER(38,0) NOT NULL,
    EXECTYPE VARCHAR(10) NOT NULL,
    MD5SUM VARCHAR(35),
    DESCRIPTION VARCHAR(255),
    COMMENTS VARCHAR(255),
    TAG VARCHAR(255),
    LIQUIBASE VARCHAR(20),
    CONTEXTS VARCHAR(255),
    LABELS VARCHAR(255),
    DEPLOYMENT_ID VARCHAR(10)
);

CREATE TABLE DATABASECHANGELOGLOCK (
    ID NUMBER(38,0) NOT NULL,
    LOCKED BOOLEAN NOT NULL,
    LOCKGRANTED TIMESTAMP_NTZ(9),
    LOCKEDBY VARCHAR(255),
    constraint PK_DATABASECHANGELOGLOCK primary key (ID)
);
```

2. Setup db.properties File
	cd ${database}/build
	cp db.properties.shadow db.properties

Fill in `db.properties` with your Snowflake credentials. Outside variables can be injected into your file `changelog/ddl/db-change.sql` with:

	../../../tools/bash/inject_variables.sh changelog/ddl/db-change.sql -v KEY1 VALUE1 -v KEY2 VALUE2

3. Run liquibase
Then, to apply your changes, run:

	`mvn initialize liquibase:update -DchangeLogFile=changelog/ddl/db-change.sql`

To rollback, run:

	mvn initialize liquibase:rollback -DchangeLogFile=changelog/ddl/db-change.sql -Dliquibase.rollbackCount=1

### Deploy Job

The [Jenkins db-deploy job](https://pipeline.theorchard.io/job/db-deploy) will auto hyrdate the following key words:

* `<S3_BUCKET>` - Hydrated as a Jenkins param. For QA use `s3://qa-orcdbucket/temp/`, For PROD use `s3://prod-orcdbucket/temp/`
* `<AWS_ACCESS_KEY_ID>` - Job will hydrate appropriate values for QA & PROD automatically
* `<AWS_SECRET_ACCESS_KEY>` - Job will hydrate appropriate values for QA & PROD automatically
* `<SCHEMA_NAME>` - Job willy hydrate either `QA` or `PROD` depending on environment being deployed to

#### Example

(Jenkins S3_BUCKET param should be `qa-orcdbucket/temp` for qa & `prod-orcdbucket/temp` for prod)
```
-- save rollback data
COPY INTO 's3://<S3_BUCKET>/temp/MOV-3125/SF-fact_analytics_error/'
FROM ( 
    SELECT *
    FROM facts.<SCHEMA_NAME>.fact_analytics_error
    WHERE
        storeid = 496
        AND formatname = 'UHD'
        AND uuid IN (
            SELECT
                TO_VARCHAR(srg.row_id)
            FROM  staging_raw_googleplay srg
            INNER JOIN dim_day dd ON dd.displaydate = srg.transaction_date
            INNER JOIN dim_release dr ON TO_VARCHAR(dr.releaseid) = REPLACE(srg.partner_reporting_id, '_FEATURE', '')
            INNER JOIN dim_track dt ON dt.upc = dr.releaseid AND dt.track_type = 'video'
            INNER JOIN dim_country dc ON dc.country_code = srg.country
            INNER JOIN map_transactiontype mt ON mt.storeid = 496 
                AND mt.inputvalue = TO_VARCHAR((CASE srg.video_category WHEN 'Season' THEN 'VS' ELSE srg.transaction_type END))
            INNER JOIN dim_transactiontype dtt ON mt.outputvalue = dtt.transactiontypeabbr
            WHERE srg.download_date < '2018-01-09' AND srg.resolution = 'UHD'
        )
)
CREDENTIALS = ( AWS_KEY_ID = '<AWS_ACCESS_KEY_ID>' AWS_SECRET_KEY = '<AWS_SECRET_ACCESS_KEY>' )
FILE_FORMAT = ( field_optionally_enclosed_by = "'" );
```