Export dashboard report to BigQuery #18038
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: Export dashboard report to BigQuery | |
| on: | |
| workflow_dispatch: | |
| schedule: | |
| - cron: '10 * * * *' | |
| jobs: | |
| export: | |
| name: Export dashboard report to BigQuery | |
| runs-on: ubuntu-20.04 | |
| container: google/cloud-sdk:latest | |
| steps: | |
| - name: Auth with gcloud | |
| uses: google-github-actions/setup-gcloud@v0 | |
| with: | |
| service_account_key: ${{ secrets.GOOGLE_SERVICE_ACCOUNT_BASE64 }} | |
| - name: Install CloudFoundry CLI | |
| shell: bash | |
| run: | | |
| apt-get update | |
| apt-get install -y ca-certificates wget | |
| wget -q -O - https://packages.cloudfoundry.org/debian/cli.cloudfoundry.org.key | apt-key add - | |
| echo "deb https://packages.cloudfoundry.org/debian stable main" | tee /etc/apt/sources.list.d/cloudfoundry-cli.list | |
| apt-get update | |
| apt-get install -y postgresql-client cf7-cli | |
| - name: Install conduit plugin | |
| shell: bash | |
| run: | | |
| cf install-plugin conduit -f | |
| - name: Download csv | |
| shell: bash | |
| env: | |
| CF_USERNAME: ${{ secrets.GOVPAAS_PROD_USERNAME }} | |
| CF_PASSWORD: ${{ secrets.GOVPAAS_PROD_PASSWORD }} | |
| PAAS_ORGANISATION: dfe | |
| # This is a shared PAAS space | |
| PAAS_SPACE: early-careers-framework-prod | |
| run: | | |
| cf api https://api.london.cloud.service.gov.uk | |
| cf auth | |
| cf target -o "${{ env.PAAS_ORGANISATION }}" -s "${{ env.PAAS_SPACE }}" | |
| cf conduit npq-registration-prod-db -c '{"read_only": true}' -- psql -d rdsbroker_858b741f_16c5_4f39_a5ab_77da5d31e5e7 -c "copy(select data from reports where identifier='dashboard') to STDOUT" | sed 's/\\n/\n/g' | sed '${/^$/d;}' > ~/data.csv | |
| - name: Upload csv to BigQuery | |
| shell: bash | |
| run: | | |
| bq load --autodetect --replace npq_registration.applications ~/data.csv |