Lakeland Events Archive - Nightly Run #601
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: Lakeland Events Archive | |
| run-name: ${{ format('{0} - {1}', github.workflow, github.event_name == 'schedule' && 'Nightly Run' || 'Manual Run') }} | |
| on: | |
| workflow_dispatch: | |
| inputs: | |
| max_days: | |
| description: "The maximum number of days to export" | |
| required: false | |
| type: string | |
| default: "30" | |
| schedule: | |
| # Follows POSIX cron syntax https://pubs.opengroup.org/onlinepubs/9699919799/utilities/crontab.html#tag_20_25_07 | |
| # * is a special character in YAML, so must be quoted | |
| # Run at 09:55 UTC every day which is 03:55 CST/04:55 CDT | |
| - cron: '55 9 * * *' | |
| env: | |
| TABLE_NAME: LAKELAND | |
| BQ_DATASET_NAME: lakeland | |
| jobs: | |
| Mysql_To_BigQuery_Sync: | |
| name: Archive MySQL data to BigQuery | |
| runs-on: ubuntu-20.04 | |
| steps: | |
| # 1. Local checkout & config | |
| - name: Checkout repository | |
| uses: actions/checkout@v4 | |
| - name: Set up config.py file from config.py.template | |
| uses: ./.github/actions/OGD_automation_config | |
| with: | |
| log_level: "DEBUG" | |
| sql_host: ${{vars.OGD_LOGGER_HOST}} | |
| vpn_user: ${{secrets.VPN_USER}} # Assumes we're using the same SOE-AD user credentials for VPN connection and SSH'ing into the MySQL server | |
| vpn_pass: ${{secrets.VPN_PASS}} | |
| sql_user: ${{secrets.OGD_ARCHIVING_USER}} | |
| sql_pass: ${{secrets.OGD_ARCHIVING_PASS}} | |
| sql_db: "opengamedata" | |
| sql_table: "${{ env.TABLE_NAME }}" | |
| bq_project_id: ${{ secrets.OGD_BQ_PROJECT_ID }} | |
| bq_dataset_id: "${{ env.BQ_DATASET_NAME }}" | |
| bq_table_basename: "${{ env.BQ_DATASET_NAME }}_daily" | |
| # 2. Remote config | |
| - name: Install OpenConnect | |
| run: sudo apt-get update && sudo apt-get install openconnect | |
| - name: Connect to VPN | |
| run: echo ${{ secrets.VPN_PASS }} | sudo openconnect --protocol=gp -u ${{ secrets.VPN_USER }} --passwd-on-stdin soe.vpn.wisc.edu & | |
| - name: Set up Google Cloud SDK | |
| uses: google-github-actions/setup-gcloud@v0 | |
| with: | |
| project_id: ${{ secrets.OGD_BQ_PROJECT_ID }} | |
| service_account_key: ${{ secrets.OGD_ARCHIVER_BQ_KEY }} | |
| export_default_credentials: true | |
| - name: Get Dependencies | |
| uses: ./.github/actions/OGD_automation_dependencies | |
| # 3. Run script | |
| - name: Execute sync | |
| run: python3.8 main.py ${{ env.TABLE_NAME }} --max_days=${{ github.event.inputs.max_days || 7 }} | |
| - name: Upload logs as artifacts | |
| if: failure() | |
| uses: actions/upload-artifact@v4 | |
| with: | |
| name: ${{ env.TABLE_NAME }}-automation-logs | |
| path: ./*.log | |
| Cleanup_Synced_Rows: | |
| name: Cleanup Synced Rows | |
| runs-on: ubuntu-22.04 | |
| needs: Mysql_To_BigQuery_Sync | |
| steps: | |
| # 1. Remote config | |
| - name: Install OpenConnect | |
| run: sudo apt-get update && sudo apt-get install openconnect | |
| - name: Connect to VPN | |
| run: echo ${{ secrets.VPN_PASS }} | sudo openconnect --protocol=gp -u ${{ secrets.VPN_USER }} --passwd-on-stdin soe.vpn.wisc.edu & | |
| - name: Setup server key # TODO: figure out/set up key secret for logging in to logger host | |
| run: | | |
| mkdir -p ~/.ssh | |
| echo '${{secrets.SERVICE_KEY}}' >> ./key.txt | |
| chmod 600 ./key.txt | |
| # 2. Cleanup | |
| - name: Run delete of archived data at least a week old | |
| run: | |
| ssh -o StrictHostKeyChecking=no -i ./key.txt ${{secrets.VPN_USER}}@${{vars.OGD_LOGGER_HOST}} "mysql -u${{secrets.OGD_ARCHIVING_USER}} -p${{secrets.OGD_ARCHIVING_PASS}} -e 'delete from opengamedata.${{ env.TABLE_NAME }} where synced=1 and datediff(now(), server_time) > 7'" |