grant privileges to dpe_engineer #75
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --- | |
| name: Test Changes with Cloned DB | |
| on: | |
| pull_request: | |
| types: [ labeled, synchronize, closed ] | |
| push: | |
| permissions: | |
| contents: read | |
| jobs: | |
| create_clone_and_run_schemachange: | |
| runs-on: ubuntu-latest | |
| if: contains(github.event.pull_request.labels.*.name, 'create_clone_and_run_schemachange') && github.event.pull_request.state == 'open' | |
| environment: dev | |
| env: | |
| SNOWFLAKE_PASSWORD: ${{ secrets.SNOWSQL_PWD }} | |
| SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWSQL_ACCOUNT }} | |
| SNOWFLAKE_USER: ${{ secrets.SNOWSQL_USER }} | |
| SNOWFLAKE_WAREHOUSE: ${{ secrets.SNOWSQL_WAREHOUSE }} | |
| SNOWFLAKE_CLONE_ROLE: DATA_ENGINEER | |
| SNOWFLAKE_SCHEMACHANGE_ROLE: SYSADMIN | |
| SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE_ORIG: ${{ vars.SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE }} | |
| SNOWFLAKE_SYNAPSE_STAGE_STORAGE_INTEGRATION: ${{ vars.SNOWFLAKE_SYNAPSE_STAGE_STORAGE_INTEGRATION }} | |
| SNOWFLAKE_SYNAPSE_STAGE_URL: ${{ vars.SNOWFLAKE_SYNAPSE_STAGE_URL }} | |
| CLONE_NAME: "${{ vars.SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE }}_${{ github.head_ref }}" | |
| STACK: ${{ vars.STACK }} | |
| steps: | |
| - uses: actions/checkout@v4 | |
| - uses: actions/setup-python@v4 | |
| with: | |
| python-version: '3.10' | |
| - name: Install python libraries | |
| shell: bash | |
| run: | | |
| pip install schemachange==3.6.1 | |
| pip install numpy==1.26.4 | |
| pip install pandas==1.5.3 | |
| - name: Configure Snowflake connection | |
| run: | | |
| # Create temporary files for config.toml and our private key | |
| config_file=$(mktemp) | |
| # Write to config.toml file | |
| echo 'default_connection_name = "dpe"' >> $config_file | |
| echo '[connections.dpe]' >> $config_file | |
| echo "account = \"${SNOWFLAKE_ACCOUNT}\"" >> $config_file | |
| echo "user = \"${SNOWFLAKE_USER}\"" >> $config_file | |
| echo "role = \"${SNOWFLAKE_SCHEMACHANGE_ROLE}\"" >> $config_file | |
| echo "password = \"${SNOWFLAKE_PASSWORD}\"" >> $config_file | |
| echo "warehouse = \"${SNOWFLAKE_WAREHOUSE}\"" >> $config_file | |
| echo 'authenticator = "SNOWFLAKE"' >> $config_file | |
| # Write config.toml path to global environment | |
| echo "SNOWFLAKE_CONFIG_PATH=$config_file" >> $GITHUB_ENV | |
| - name: Install Snowflake CLI | |
| uses: Snowflake-Labs/[email protected] | |
| with: | |
| default-config-file-path: ${{ env.SNOWFLAKE_CONFIG_PATH }} | |
| - name: Verify Snowflake CLI installation and connection | |
| run: | | |
| snow --version | |
| snow connection test | |
| - name: Sanitize Clone Name | |
| run: | | |
| CLONE_NAME_SANITIZED="${CLONE_NAME//[^a-zA-Z0-9_]/_}" | |
| echo "Clone name has been updated! The clone name will be: ${CLONE_NAME_SANITIZED}" | |
| echo "SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE=${CLONE_NAME_SANITIZED}" >> $GITHUB_ENV | |
| - name: Zero-copy clone the database | |
| shell: bash | |
| run: | | |
| snow sql -q "USE ROLE DPE_ENGINEER; CREATE OR REPLACE DATABASE $SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE CLONE $SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE_ORIG;" | |
| - name: Grant permissions to DPE_ENGINEER on cloned database schemas of interest | |
| shell: bash | |
| run: | | |
| snow sql -q "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ${SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE}.SYNAPSE TO ROLE DPE_ENGINEER;" | |
| snow sql -q "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ${SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE}.SYNAPSE_RAW TO ROLE DPE_ENGINEER;" | |
| - name: Run schemachange on the clone | |
| shell: bash | |
| run: | | |
| schemachange \ | |
| -f synapse_data_warehouse \ | |
| -a $SNOWFLAKE_ACCOUNT \ | |
| -u $SNOWFLAKE_USER \ | |
| -r DPE_ENGINEER \ | |
| -w $SNOWFLAKE_WAREHOUSE \ | |
| --config-folder synapse_data_warehouse | |
| drop_clone: | |
| runs-on: ubuntu-latest | |
| if: github.event.pull_request.merged == true || github.event.action == 'closed' | |
| environment: dev | |
| env: | |
| SNOWFLAKE_PASSWORD: ${{ secrets.SNOWSQL_PWD }} | |
| SNOWFLAKE_ACCOUNT: ${{ secrets.SNOWSQL_ACCOUNT }} | |
| SNOWFLAKE_USER: ${{ secrets.SNOWSQL_USER }} | |
| SNOWFLAKE_WAREHOUSE: ${{ secrets.SNOWSQL_WAREHOUSE }} | |
| SNOWFLAKE_CLONE_ROLE: DATA_ENGINEER | |
| CLONE_NAME: "${{ vars.SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE }}_${{ github.head_ref }}" | |
| steps: | |
| - uses: actions/checkout@v4 | |
| - uses: actions/setup-python@v4 | |
| with: | |
| python-version: '3.10' | |
| - name: Configure Snowflake connection | |
| run: | | |
| # Create temporary files for config.toml and our private key | |
| config_file=$(mktemp) | |
| # Write to config.toml file | |
| echo 'default_connection_name = "dpe"' >> $config_file | |
| echo '[connections.dpe]' >> $config_file | |
| echo "account = \"${SNOWFLAKE_ACCOUNT}\"" >> $config_file | |
| echo "user = \"${SNOWFLAKE_USER}\"" >> $config_file | |
| echo "role = \"${SNOWFLAKE_CLONE_ROLE}\"" >> $config_file | |
| echo "password = \"${SNOWFLAKE_PASSWORD}\"" >> $config_file | |
| echo "warehouse = \"${SNOWFLAKE_WAREHOUSE}\"" >> $config_file | |
| echo 'authenticator = "SNOWFLAKE"' >> $config_file | |
| # Write config.toml path to global environment | |
| echo "SNOWFLAKE_CONFIG_PATH=$config_file" >> $GITHUB_ENV | |
| - name: Install Snowflake CLI | |
| uses: Snowflake-Labs/[email protected] | |
| with: | |
| default-config-file-path: ${{ env.SNOWFLAKE_CONFIG_PATH }} | |
| - name: Verify Snowflake CLI installation and connection | |
| run: | | |
| snow --version | |
| snow connection test | |
| - name: Sanitize Clone Name | |
| run: | | |
| CLONE_NAME_SANITIZED="${CLONE_NAME//[^a-zA-Z0-9_]/_}" | |
| echo "SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE=${CLONE_NAME_SANITIZED}" >> $GITHUB_ENV | |
| echo "Clone name has been updated! The clone name will be: ${SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE}" | |
| echo $SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE | |
| - name: Drop the clone | |
| shell: bash | |
| run: | | |
| snow sql -r $SNOWFLAKE_CLONE_ROLE -q "DROP DATABASE IF EXISTS $SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE;" |