Extract TS BI Server history with CS Tools using key pair snowflake auth. #227
This file contains hidden or 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: Extract TS BI Server history with CS Tools using key pair snowflake auth. | |
| on: | |
| workflow_dispatch: | |
| inputs: | |
| cs_tools_version: | |
| description: "The CS Tools version to target for a manual run." | |
| required: false | |
| type: string | |
| schedule: | |
| # Runs every day at 5:30 AM UTC | |
| - cron: "30 5 * * *" | |
| jobs: | |
| fetch-ts-bi-data: | |
| runs-on: ubuntu-latest | |
| env: | |
| # CS TOOLS IS COMMAND LINE LIBRARY WRAPPING TS APIS | |
| # https://thoughtspot.github.io/cs_tools/ | |
| CS_TOOLS_VERSION: ${{ github.event_name == 'workflow_dispatch' && inputs.cs_tools_version || 'v1.6.4' }} | |
| CS_TOOLS_THOUGHTSPOT__URL: ${{ secrets.THOUGHTSPOT_URL }} | |
| CS_TOOLS_THOUGHTSPOT__USERNAME: ${{ secrets.THOUGHTSPOT_USERNAME }} | |
| CS_TOOLS_THOUGHTSPOT__SECRET_KEY: ${{ secrets.THOUGHTSPOT_SECRET_KEY }} | |
| P8_KEY_PATH: tools_key.p8 | |
| # COMMON PARAMETERS FOR THE SNOWFLAKE SYNCER | |
| # https://thoughtspot.github.io/cs_tools/syncer/snowflake/ | |
| steps: | |
| - name: Get 7 days ago | |
| run: echo "days_ago_7=$(date -d "-7 days" +'%Y-%m-%d')" >> $GITHUB_ENV | |
| - name: Get 1 days ago | |
| run: echo "days_ago_1=$(date -d "-1 days" +'%Y-%m-%d')" >> $GITHUB_ENV | |
| # SETUP PYTHON. | |
| - name: Set up Python | |
| uses: actions/setup-python@v5 | |
| # UPDATE PIP. | |
| - name: Ensure pip is up to date. | |
| run: python -m pip install --upgrade pip | |
| # INSTALL A SPECIFIC VERSION OF cs_tools. | |
| - name: Install a pinned version of CS Tools | |
| run: python -m pip install "cs_tools[cli] @ https://github.com/thoughtspot/cs_tools/archive/${{ env.CS_TOOLS_VERSION }}.zip" | |
| # ENSURE SYNCER DEPENDENCIES ARE INSTALLED. | |
| # found in: https://github.com/thoughtspot/cs_tools/blob/master/sync/<dialect>/MANIFEST.json | |
| - name: Install a pinned version of CS Tools | |
| run: python -m pip install "snowflake-sqlalchemy >= 1.6.1" | |
| - name: Decode PEM file | |
| run: echo "${{ secrets.SNOWFLAKE_PRIVATE_KEY }}" > $P8_KEY_PATH | |
| - name: Set Snowflake connection string | |
| run: | | |
| echo "DECLARATIVE_SYNCER_SYNTAX=account_name=${{ secrets.SNOWFLAKE_PRIVATE_ACCOUNT }}&username=${{ secrets.SNOWFLAKE_PRIVATE_USERNAME }}&secret=${{ secrets.SNOWFLAKE_PRIVATE_SECRET }}&warehouse=${{ secrets.SNOWFLAKE_PRIVATE_WAREHOUSE }}&role=${{ secrets.SNOWFLAKE_PRIVATE_ROLE }}&database=${{ secrets.SNOWFLAKE_PRIVATE_DATABASE }}&schema=${{ secrets.SNOWFLAKE_PRIVATE_SCHEMA }}&authentication=key-pair&private_key_path=${P8_KEY_PATH}" >> $GITHUB_ENV | |
| # RUNS THE searchable tml COMMAND. | |
| # https://thoughtspot.github.io/cs_tools/tools/searchable | |
| # | |
| # THE CLI OPTION --config ENV: TELLS CS TOOLS TO PULL THE INFORMATION FROM ENVIRONMENT VARIABLES. | |
| - name: Fetch Activity History from ThoughtSpot | |
| run: >- | |
| cs_tools tools | |
| searchable bi-server | |
| --from-date $days_ago_7 | |
| --to-date $days_ago_1 | |
| --syncer 'snowflake://${{ env.DECLARATIVE_SYNCER_SYNTAX }}&load_strategy=UPSERT' | |
| --config ENV: | |
| # CLEANUP | |
| - name: Clean up sensitive files | |
| if: always() | |
| run: rm -f ${{ env.P8_KEY_PATH }} |