[SNOW-90] Introduce CI job to test schemachange updates against a clone
#39
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 ] | |
| permissions: | |
| contents: read | |
| # We define global environment variables needed to run the SnowSQL and schemachange commands | |
| env: | |
| SNOWSQL_PWD: ${{ secrets.SNOWSQL_PWD }} | |
| SNOWSQL_ACCOUNT: ${{ secrets.SNOWSQL_ACCOUNT }} | |
| SNOWSQL_USER: ${{ secrets.SNOWSQL_USER }} | |
| SNOWSQL_WAREHOUSE: ${{ secrets.SNOWSQL_WAREHOUSE }} | |
| SNOWSQL_CLONE_ROLE: DATA_ENGINEER | |
| SNOWSQL_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 }} | |
| jobs: | |
| create_clone_and_run_schemachange: | |
| runs-on: ubuntu-latest | |
| if: contains(github.event.pull_request.labels.*.name, 'create_clone_and_run_schemachange') | |
| environment: dev | |
| env: | |
| SNOWSQL_PWD: ${{ secrets.SNOWSQL_PWD }} | |
| SNOWSQL_ACCOUNT: ${{ secrets.SNOWSQL_ACCOUNT }} | |
| SNOWSQL_USER: ${{ secrets.SNOWSQL_USER }} | |
| SNOWSQL_WAREHOUSE: ${{ secrets.SNOWSQL_WAREHOUSE }} | |
| SNOWSQL_CLONE_ROLE: DATA_ENGINEER | |
| # We need to run schemachange on SYSADMIN for it to work | |
| SNOWSQL_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: Install SnowSQL | |
| run: | | |
| curl -O https://sfc-repo.snowflakecomputing.com/snowsql/bootstrap/1.2/linux_x86_64/snowsql-1.2.9-linux_x86_64.bash | |
| SNOWSQL_DEST=~/bin SNOWSQL_LOGIN_SHELL=~/.profile bash snowsql-1.2.9-linux_x86_64.bash | |
| - name: Verify SnowSQL installation | |
| run: ~/bin/snowsql -v | |
| - 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: Zero-copy clone the database | |
| shell: bash | |
| run: | | |
| ~/bin/snowsql -r $SNOWSQL_CLONE_ROLE -q "CREATE OR REPLACE DATABASE $SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE CLONE $SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE_ORIG;" | |
| - name: Run schemachange on the clone | |
| shell: bash | |
| run: | | |
| schemachange \ | |
| -f synapse_data_warehouse \ | |
| -a $SNOWSQL_ACCOUNT \ | |
| -u $SNOWSQL_USER \ | |
| -r $SNOWSQL_SCHEMACHANGE_ROLE \ | |
| -w $SNOWSQL_WAREHOUSE \ | |
| --config-folder synapse_data_warehouse | |
| drop_clone: | |
| runs-on: ubuntu-latest | |
| if: contains(github.event.pull_request.labels.*.name, 'drop_clone') | |
| environment: dev | |
| env: | |
| SNOWSQL_PWD: ${{ secrets.SNOWSQL_PWD }} | |
| SNOWSQL_ACCOUNT: ${{ secrets.SNOWSQL_ACCOUNT }} | |
| SNOWSQL_USER: ${{ secrets.SNOWSQL_USER }} | |
| SNOWSQL_WAREHOUSE: ${{ secrets.SNOWSQL_WAREHOUSE }} | |
| SNOWSQL_CLONE_ROLE: DATA_ENGINEER | |
| #SNOWSQL_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: Install SnowSQL | |
| run: | | |
| curl -O https://sfc-repo.snowflakecomputing.com/snowsql/bootstrap/1.2/linux_x86_64/snowsql-1.2.9-linux_x86_64.bash | |
| SNOWSQL_DEST=~/bin SNOWSQL_LOGIN_SHELL=~/.profile bash snowsql-1.2.9-linux_x86_64.bash | |
| - name: Verify SnowSQL installation | |
| run: ~/bin/snowsql -v | |
| - 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: | | |
| ~/bin/snowsql -r $SNOWSQL_CLONE_ROLE -q "DROP DATABASE IF EXISTS $SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE;" |