-
Notifications
You must be signed in to change notification settings - Fork 4
168 lines (142 loc) · 7.58 KB
/
test_with_clone.yaml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
---
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 connections
run: |
# Config file for DATA_ENGINEER
config_file_dpe=$(mktemp)
echo 'default_connection_name = "dpe"' >> $config_file_dpe
echo '[connections.dpe]' >> $config_file_dpe
echo "account = \"${SNOWFLAKE_ACCOUNT}\"" >> $config_file_dpe
echo "user = \"${SNOWFLAKE_USER}\"" >> $config_file_dpe
echo "role = \"${SNOWFLAKE_CLONE_ROLE}\"" >> $config_file_dpe
echo "password = \"${SNOWFLAKE_PASSWORD}\"" >> $config_file_dpe
echo "warehouse = \"${SNOWFLAKE_WAREHOUSE}\"" >> $config_file_dpe
echo 'authenticator = "SNOWFLAKE"' >> $config_file_dpe
# Config file for SYSADMIN
echo '[connections.sysadmin]' >> $config_file_dpe
echo "account = \"${SNOWFLAKE_ACCOUNT}\"" >> $config_file_dpe
echo "user = \"${SNOWFLAKE_USER}\"" >> $config_file_dpe
echo "role = \"${SNOWFLAKE_SCHEMACHANGE_ROLE}\"" >> $config_file_dpe
echo "password = \"${SNOWFLAKE_PASSWORD}\"" >> $config_file_dpe
echo "warehouse = \"${SNOWFLAKE_WAREHOUSE}\"" >> $config_file_dpe
echo 'authenticator = "SNOWFLAKE"' >> $config_file_dpe
# Write config paths to environment
echo "SNOWFLAKE_CONFIG_PATH_DPE=$config_file_dpe" >> $GITHUB_ENV
- name: Install Snowflake CLI with DATA_ENGINEER config
uses: Snowflake-Labs/[email protected]
with:
default-config-file-path: ${{ env.SNOWFLAKE_CONFIG_PATH_DPE }}
- name: Verify Snowflake CLI installation and connections
run: |
snow --version
snow connection test -c sysadmin
snow connection test -c dpe
- 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: Grant permissions to DATA_ENGINEER on cloned database
shell: bash
run: |
snow connection set-default sysadmin
snow sql -q "GRANT OWNERSHIP ON ALL TABLES IN SCHEMA ${SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE}.SYNAPSE_RAW TO ROLE DATA_ENGINEER REVOKE CURRENT GRANTS;"
snow sql -q "GRANT OWNERSHIP ON ALL DYNAMIC TABLES IN SCHEMA ${SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE}.SYNAPSE_RAW TO ROLE DATA_ENGINEER REVOKE CURRENT GRANTS;"
snow sql -q "GRANT OWNERSHIP ON ALL TABLES IN SCHEMA ${SNOWFLAKE_SYNAPSE_DATA_WAREHOUSE_DATABASE}.SYNAPSE TO ROLE DATA_ENGINEER REVOKE CURRENT GRANTS;"
- name: Run schemachange on the clone as DATA_ENGINEER
shell: bash
run: |
schemachange \
-f synapse_data_warehouse \
-a $SNOWFLAKE_ACCOUNT \
-u $SNOWFLAKE_USER \
-r $SNOWFLAKE_CLONE_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: 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;"