name: snowflake_deploy
on: pull_request

env:
  TERRAFORM_VERSION: '1.3.0'
  TERRAFORM_FOLDER: 'terraform' 
  SNOWFLAKE_USER: ${{ secrets.SNOWFLAKE_USER }}
  SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}
  SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWFLAKE_ACCOUNT }}
  SNOWFLAKE_REGION: "ap-northeast-1.aws"

jobs:
  test_job:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2

      - name: Configure aws credentials
        uses: aws-actions/configure-aws-credentials@v1
        with:
          aws-access-key-id: ${{ secrets.AWS_TERRAFORM_ANALYTICS_USER_ACCESS_KEY }}
          aws-secret-access-key: ${{ secrets.AWS_TERRAFORM_ANALYTICS_USER_SECRET_ACCESS_KEY }}
          aws-region: ap-northeast-1
          
      - name: setup terraform
        uses: hashicorp/setup-terraform@v1.3.2
        with:
          terraform_version: ${{ env.TERRAFORM_VERSION }}

      - name: terraform init
        id: init
        working-directory: ${{ env.TERRAFORM_FOLDER }}
        run: |
          terraform init
          
      - name: terraform apply
        id: apply
        working-directory: ${{ env.TERRAFORM_FOLDER }}
        run: |
          terraform apply -no-color -auto-approve -parallelism=50

.
├── module
│   ├── database        databaseの作成及び権限の設定
│   │   ├── main.tf
│   │   └── variables.tf
│   ├── stage           stage・integration・file formatの作成及び権限の設定
│   │   ├── main.tf
│   │   └── variables.tf
│   └── task            procedure・taskの作成
│       ├── main.tf
│       └── variables.tf
├── backend.tf          terraformの設定ファイル(stateファイルの置き場など)
├── main.tf             各種モジュール呼び出し＋schema・tableの作成
└── variables.tf

###########################################
# database
###########################################
module database {
  source = "./module/database"
  ...
}

###########################################
# raw schema
###########################################
resource snowflake_schema schema {
  database = module.database.database_name
  name     = "SCHEMA_NAME"
  comment  = "COMMENT"

  is_transient        = false
  is_managed          = false
  data_retention_days = 1
}

###########################################
# table
###########################################
resource snowflake_table table {
  database            = module.database.database_name
  schema              = snowflake_schema.schema.name
  name                = "TABLE_NAME"
  comment             = "COMMENT"
  change_tracking     = false

  column {
    name     = "body"
    type     = "VARIANT"
    nullable = false
  }
}

###########################################
# stage・integration・file format
###########################################
module "stage" {
  source = "./module/stage"
  ...
}

###########################################
# tasl・procedure
###########################################
module "task" {
  source = "./module/task"
  ...
}

###########################################
# database
###########################################
resource snowflake_database database {
  name = var.name
  comment = var.description
  data_retention_time_in_days = 3
}

###########################################
# grant
###########################################
resource snowflake_database_grant grant {
  provider = snowflake.security_admin
  database_name = snowflake_database.database.name
  enable_multiple_grants = true

  privilege = "USAGE"
  roles     = [...]

  with_grant_option = false
}

...

###########################################
# output
###########################################
output "database_name" {
  value = snowflake_database.database.name
}

resource snowflake_storage_integration integration {
  provider = snowflake.account_admin
  name    = "${var.database_name}_INTEGRATION_S3"
  comment = "A storage integration for ${var.description}"
  type    = "EXTERNAL_STAGE"

  enabled = true
  storage_provider         = "S3"
  storage_aws_role_arn     = var.storage_aws_role_arn
  storage_allowed_locations = [var.storage_allowed_location]
}

resource snowflake_integration_grant grant {
  provider = snowflake.account_admin
  integration_name = snowflake_storage_integration.integration.name
  roles         = [...]
  privilege     = "USAGE"
  with_grant_option = false
}

resource snowflake_file_format json_format {
  name        = "JSON"
  database    = var.database_name
  schema      = "PUBLIC"
  format_type = "JSON"
  compression = "AUTO"
  binary_format = "UTF-8"
}

resource snowflake_stage stage {
  name        = "${var.database_name}_${var.schema_name}"
  url         = var.storage_allowed_location
  database    = var.database_name
  schema      = var.schema_name
  storage_integration = snowflake_storage_integration.integration.name
  file_format = "FORMAT_NAME = ${snowflake_file_format.json_format.database}.${snowflake_file_format.json_format.schema}.${snowflake_file_format.json_format.name}"
}

output "stage_name" {
  value = snowflake_stage.stage.name
}

output "file_format" {
  value = "${snowflake_file_format.json_format.database}.${snowflake_file_format.json_format.schema}.${snowflake_file_format.json_format.name}"
}

resource snowflake_procedure procedure {
  name     = "LOAD_${var.database_name}_${var.schema_name}_${var.table_name}"
  database = var.database_name
  schema   = var.schema_name
  language = "JAVASCRIPT"
  comment             = "load into ${var.database_name}.${var.schema_name}.${var.table_name}"
  return_type         = "VARCHAR"
  execute_as          = "CALLER"
  return_behavior     = "IMMUTABLE"
  statement           = <<EOT
    ...(javasvtiptでデータロードするプログラム)
EOT
}

resource snowflake_task task {
  provider = snowflake.task_admin
  comment = var.description

  database = var.database_name
  schema   = var.schema_name

  name          = "${var.database_name}_${var.schema_name}_${var.table_name}_TASK"
  schedule      = "USING CRON 45 9 * * * Asia/Tokyo"
  sql_statement = "CALL ${snowflake_procedure.procedure.database}.${snowflake_procedure.procedure.schema}.${snowflake_procedure.procedure.name}();"

  user_task_timeout_ms                     = 10000
  user_task_managed_initial_warehouse_size = "XSMALL"
  enabled                                  = true
}

output "task_name" {
  value = snowflake_task.task.name
}

terraform {
  required_providers {
    snowflake = {
      source  = "Snowflake-Labs/snowflake"
      version = "0.44.0"
    }
  }
}

provider "snowflake" {
  role = "アドミン権限のロール名"
}

provider "snowflake" {
  alias = "account_admin"
  role  = "アカウントアドミン権限のロール名"
}

GRANT all ON all schemas IN DATABASE *** TO ROLE ***;
GRANT all ON all tables IN DATABASE *** TO ROLE ***;
GRANT all ON all procedures IN DATABASE *** TO ***;
GRANT all ON all stages IN DATABASE *** TO ROLE ***;
GRANT all ON all file formats IN DATABASE *** TO ROLE ***;

variable "all_table_privileges" {
  type    = list(string)
  default = [
    "INSERT",
    "DELETE",
    "SELECT",
    "UPDATE"
    ]
}