Export dashboard report to BigQuery #17315
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 |