[SNOW-90] Introduce CI job to test schemachange updates against a clone
#66
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_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 "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 "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 $SNOWFLAKE_ACCOUNT \ | |
| -u $SNOWFLAKE_USER \ | |
| -r $SNOWFLAKE_SCHEMACHANGE_ROLE \ | |
| -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: 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: Install Snowflake CLI | |
| uses: Snowflake-Labs/[email protected] | |
| - name: Set up Snowflake CLI connection | |
| run: | | |
| snow connection create --name default \ | |
| --account-name $SNOWFLAKE_ACCOUNT \ | |
| --username $SNOWFLAKE_USER \ | |
| --password $SNOWFLAKE_PASSWORD \ | |
| --role $SNOWFLAKE_CLONE_ROLE \ | |
| --warehouse $SNOWFLAKE_WAREHOUSE \ | |
| --database $SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE_ORIG | |
| - 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;" |