Forem: FreshBooks

Manipulating Complex Structures in BigQuery: A Guide to DDL Operations

iamtodor — Fri, 26 May 2023 07:41:17 +0000

This guide aims to provide a comprehensive understanding of handling changes in complex structures within BigQuery using Data Definition Language (DDL) statements. It explores scenarios involving top-level columns as well as nested columns, addressing limitations with the existing on_schema_change configuration in dbt for BigQuery.

Introduction
How-to
- Define schema
- Add records
- Add top-level field
- Change top-level field type
  - CAST
  - ALTER COLUMN
- Juggle with STRUCT
  - tmp table
  - update STRUCT using SET
  - CREATE OR REPLACE TABLE
Bonus notes
- Create a regular table
- Create an external table
Contact info

Introduction

Currently, dbt's on_schema_change configuration only tracks schema changes related to top-level columns in BigQuery. Nested column changes, such as adding, removing, or modifying a STRUCT, are not captured. This guide delves into extending the functionality of on_schema_change to encompass nested columns, enabling a more comprehensive schema change tracking mechanism. What exactly are top-level as well as nested ones I'm going to show further.

Moreover, it's important to note that BigQuery explicitly states on their Add a nested column to a RECORD column page that adding a new nested field to an existing RECORD column using a SQL DDL statement is not supported:

Adding a new nested field to an existing RECORD column by using a SQL DDL statement is not supported.

When it comes to drops one or more columns, from ALTER TABLE DROP COLUMN statement
page:

You cannot use this statement to drop the following:

Partitioned columns

Clustered columns

Nested columns inside existing RECORD fields

Columns in a table that has row access policies

This is the ongoing proposal with the discussion around
on_schema_change should handle non-top-level schema changes topic.

This limitation further highlights the need for alternative approaches to manipulate complex structures.

How-to

Define schema

To begin, let's dive into the SQL syntax and create the "person" table. This table will store information about individuals, including their ID, name, and address.



CREATE TABLE IF NOT EXISTS dataset_name.person (
    id INT64,
    name STRING,
    address STRUCT <
        country STRING,
        city STRING 
    >
)

Add records

Add a couple of records to the table.



INSERT INTO
    dataset_name.person (id, name, address)
VALUES
    (1, "John", STRUCT("USA", "New-York")),
    (2, "Jennifer", STRUCT("Canada", "Toronto"))

How schema in UI looks like

How data is represented while querying it with



SELECT
    *
FROM
    dataset_name.person

Add top-level field

Imagine we were tasked to add a new field has_car, that has an INT64 type.



ALTER TABLE
    dataset_name.person
ADD
    COLUMN IF NOT EXISTS has_car INT64;

-- add record right away
INSERT INTO
    dataset_name.person (id, name, has_car, address)
VALUES
    (3, "James", 0, STRUCT("USA", "New-York"))

When you add a new column to an existing BigQuery table, the past records will have null values for that newly added column. This behavior is expected because the new column was not present at the time those records were inserted.

Change top-level field type

Then your customer changes their mind and now the has_car column has to have a BOOL type instead of INT64. Here are 2 possible ways to tackle this task.

Before diving deep into the possible approaches, worth to mention, that BigQuery has Conversion rules, that you need to consider. For
instance, you can cast BOOL to INT64, but you cannot cast INT64 to DATETIME.

In BigQuery, CAST and ALTER COLUMN are two different approaches for modifying the data type of a column in a table.
Let's explore each approach:

`CAST`

The CAST() function is used to convert the data type of a column or an expression in a SQL query. It allows you to convert a column from one data type to another during the query execution. However, it does not permanently modify the data type of the column in the table's schema.

The following is an example of using CAST to convert a column's data type in a query:



SELECT
    id,
    name,
    address,
    CAST(has_car as BOOL) as has_car
FROM
    dataset_name.person

`ALTER COLUMN`

The ALTER COLUMN statement is used to modify the data type of a column in the table's schema. It allows you to permanently change the data type of a column in the table, affecting all existing and future data in that column.

Here's an example of using ALTER COLUMN to modify the data type of a column:



ALTER TABLE
    dataset_name.person
ALTER COLUMN
    has_car
SET
    DATA TYPE BOOL;

It's important to note that ALTER COLUMN is a DDL statement and can only be executed as a separate operation outside of a regular SQL query. Once the column's data type is altered, it will affect all future operations and queries performed on that table.

In summary, CAST is used to convert the data type of a column during query execution, while ALTER COLUMN is used to permanently modify the data type of a column in the table's schema. The choice between the two depends on whether you want to temporarily convert the data type for a specific query or permanently change the data type for the column in the table.

Juggle with STRUCT

If we want to apply changes to nested fields, such as adding, removing, or modifying STRUCT itself there are few different ways to do so.

temp table

First, quite simple is using the temp table.



CREATE TABLE IF NOT EXISTS dataset_name.person_tmp (
    id INT64,
    name STRING,
    has_car INT64,
    address STRUCT <
        country STRING,
        city STRING,
        zip_code INT64
    >
);

-- fill then new zip_code field with the default 0 value
INSERT INTO
    dataset_name.person_tmp
SELECT
    id,
    name,
    has_car,
    (
        SELECT
            as STRUCT address.country,
            address.city,
            0 as zip_code
    ) as address
FROM
    dataset_name.person;

ALTER TABLE
    IF EXISTS `dataset_name.person` RENAME TO `person_past`;

ALTER TABLE
    IF EXISTS `dataset_name.person_tmp` RENAME TO `person`;

DROP TABLE dataset_name.person_tmp;

However, this approach has some drawbacks and considerations to keep in mind: when modifying a BigQuery table using a temporary table, you need to create a new table with the desired modifications and then copy the data from the original table to the temporary table.

Costs

As this process involves duplicating the data. It will increase storage usage, leading to additional storage costs as well as it consumes additional query processing resources.

Performance

It may impact performance, especially for large tables as you have a limited amount of production resources that are shared.

Complexity and consistency

Using a temporary table to modify a BigQuery table introduces additional steps and complexity to the process. You need to write queries to create the temporary table, copy data, modify the data, overwrite the original table, and then drop the temporary table. This adds complexity to the overall workflow and may require more code and query execution time.

Last, but not least, during the modification process, there might be a period where the original table is not accessible or is in an inconsistent state. If other processes or applications depend on the original table's data, this downtime or inconsistency could impact their operations.

So this is not the very best way.

update STRUCT using SET

Another scenario is to change the nested field type. Imagine we would like to update the zip_code type from STRING to INT64. Now we don't want to use the tmp table way. So the second way is to UPDATE STRUCT using



ALTER TABLE
    dataset_name.person
ADD
    COLUMN IF NOT EXISTS address_new STRUCT < country STRING,
    city STRING,
    zip_code STRING >;

UPDATE
    `dataset_name.person`
SET
    address_new = (
        SELECT
            AS STRUCT address.country,
            address.city,
            CAST(address.zip_code as STRING)
    )
WHERE
    TRUE;

ALTER TABLE
    dataset_name.person RENAME COLUMN address TO address_past;

ALTER TABLE
    dataset_name.person RENAME COLUMN address_new TO address;

ALTER TABLE
    dataset_name.person DROP COLUMN address_past;

In this case, only the STRUCT field will be duplicated. That is good enough.

CREATE OR REPLACE TABLE

Another last approach is using CREATE OR REPLACE TABLE.



CREATE
OR REPLACE TABLE dataset_name.person AS
SELECT
    id,
    name,
    has_car,
    (
        SELECT
            AS STRUCT address.country,
            address.city,
            CAST(address.zip_code as STRING)
    ) as address
FROM
    dataset_name.person

In the same way, we can remove nested fields. We can just select the needed fields and omit the ones we don't interested in.



SELECT
    address.*
FROM
    `dataset_name.person`;

SELECT
    * REPLACE (
        (
            SELECT
                AS STRUCT address.*
            EXCEPT
                (zip_code)
        ) AS address
    )
FROM
    `dataset_name.person`

Bonus notes

If you have some table schema from a separate dataset, that you need to create in your particular dataset the easiest the way is using CLI commands as it's a much faster and less error-prone way to create tables.

Create a regular table

This is the example of how to save table schema using Table ID to JSON format with bq show command



bq show \
    --schema \
    --format=prettyjson \
    project_name:dataset_name.table_name > table_name.json

And now you can create a table in your dataset using bq mk command:



bq mk \
    --table \
    your_dataset_name.table_name \
    table_name.json

Create an external table

Here is the example of creating a table definition in JSON format using bq mkdef:



bq mkdef \
    --source_format=NEWLINE_DELIMITED_JSON \
    --autodetect=false \
    'gs://bucket_name/prefix/*.json' > table_def

The mkdef command is to create a table definition in JSON format for data stored in Cloud Storage or Google Drive. It will be used to create an external table.



bq mk \

    --table \

    --external_table_definition=nicereply_csat_raw_def \

    dataset_name.table_name \

    table_name.json

Contact info

If you found this article helpful, I invite you to connect with me on LinkedIn. I am always looking to expand my network and connect with like-minded individuals in the data industry. Additionally, you can also reach out to me for any questions or feedback on the article. I'd be more than happy to engage in a conversation and help out in any way I can. So don’t hesitate to contact me, and let’s connect and learn together.

Building CI/CD for Vertex AI pipelines: The production

Oleksandr Borodavka — Wed, 26 Apr 2023 12:10:08 +0000

Hi there! As you probably already know from the first few articles of this series, we tested some new ideas and tools with a POC for one simple pipeline. Today we will review a new generalized version of CI/CD for Vertex AI pipelines we have built based on that experience and some further investigations.

A bit of context

Let's recall some base points to refresh the context:

Vertex AI is used to build training pipelines.
GitHub Actions is our CI/CD tool.
We built the declarative framework that allows us to standardize the format and operations for all our components and pipelines.
The specifications and implementations of all our components and pipelines are kept in one GitHub repository.
There are three environments: development(DEV), staging(STAGE) and production(PROD).

Stating the task

What do we want to achieve?
In simple words, we want to automate everything as much as possible. Ideally, when new changes appear in our code repository we want these changes applied in production in as short time a period as possible without any manual effort. Moreover, it should be done in a stable, safe, reproducible, and effective way.

Running a little ahead, there are three absolutely awesome things in our CI/CD practice. In all deployment environments, our Continuous Integration system:

automatically rebuilds components and runs their unit and integration tests
builds pipelines changed in Pull Requests
and rebuilds dependent pipelines.

The way of code

Okay how can we achieve this?
Since developers work with the codebase in the form of Pull Requests we can use them as starting points for the workflows. There are two moments that we have to automate.

The first one is when a pull request is opened (or updated). Here we want to run the more basic and faster checks for the coming changes to provide quick feedback for a developer. These generally are unit tests and building jobs (just a build to check if configurations are okay) on our DEV environment. Then, if everything is fine, we have to run integration tests for components and run our pipelines on the STAGE environment to be sure it is ready to be merged.

The second moment is when the PR is approved and merged. Here we have the changes which have already been tested, reviewed, and merged into the main branch, so it is ready for delivery. All the processes are run again but now on the PROD environment this time.

The implementation

For both stages, we use GitHub Actions workflows and some CLI commands of the Python framework, since routines related to code analysis are more expedient to implement with the framework’s specific code. It probably doesn’t make sense to review all the code, that would be too long and too specific. However, we can take a look at the general structure and a bit of a simplified version of the workflows that have detail enough to present the idea.

Here is the structure of the source code:

pipelines/
  pipeline1.yaml
  ...
components/
  component1/
    src/
      ...
    tests/
      unit/
          ...
      integration/
          test.yaml
    config.yaml

There is a folder with pipeline specifications and a folder with components. Each component contains source files, tests, and a configuration.

And it is how the GitHub Actions directory looks like:

.github
  actions
    build_component
    build_pipeline
    run_pipeline
    test_component
    test_component_integration
  workflows
    pr_merged.yml
    pr_opened.yml

Where the actions is a directory with reusable composite actions, which do all the operations we need with components and pipelines (build, test, and run). And in the workflows directory, we have two workflows that are automatically triggered by GitHub when a Pull Request is opened/updated or merged respectively.

PR is opened

Let's take a look at the first workflow:

name: MLOps Pull Request - Opened

on:
  # The workflow will be run automatically when a PR is 
  # opened to the main branch or changes to the PR are pushed
  pull_request:
    types: [opened, synchronize, reopened]
    branches:
      - "main"
    paths:
      - "pipelines/**/*.yaml"
      - "components/**/*.yaml"
      - "components/**/*.py"

# Use concurrency to ensure that only a single workflow 
# using the same concurrency group will run at a time
concurrency: dev_stage_environment

jobs:
  git_diff:
    # Get a list of changed files in the PR for the future analysis
    runs-on: ubuntu-latest
    outputs:
      diff: ${{ steps.getter.outputs.diff }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Get git diff
        id: getter
        run: |
          GIT_DIFF="$(echo $(git diff --name-only origin/main...origin/${GITHUB_HEAD_REF}))"
          echo "::set-output name=diff::$GIT_DIFF"
  get_component_list:
    # Get a list of names for the added/changed components
    needs: git_diff
    runs-on: ubuntu-latest
    outputs:
      names: ${{ steps.getter.outputs.names }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Get list
        id: getter
        run: |
          NAMES="$(make get_components --paths='${{ needs.git_diff.outputs.diff }}')"
          echo "::set-output name=names::$NAMES"
  test_and_build_components_on_dev:
    # Test and build changed components on the DEV environment
    needs: get_component_list
    runs-on: ubuntu-latest
    environment: development
    strategy:
      # Use matrix strategy to run the tasks in parallel
      matrix:
        name: ${{ fromJson(needs.get_component_list.outputs.names) }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Test component
        uses: ./.github/actions/test_component
        with:
          component_name: ${{ matrix.name }}
      - name: Build component
        uses: ./.github/actions/build_component
        with:
          component_name: ${{ matrix.name }}

  get_pipeline_list:
    # Get a list of names for the added/changed pipelines
    needs: [ git_diff, test_and_build_components_on_dev ]
    runs-on: ubuntu-latest
    outputs:
      names: ${{ steps.getter.outputs.names }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Get list
        id: getter
        run: |
          NAMES="$(make get_pipelines --paths='${{ needs.git_diff.outputs.diff }}')"
          echo "::set-output name=names::$NAMES"
  build_pipelines_on_dev:
    # Build changed pipelines on the DEV environment
    needs: get_pipeline_list
    runs-on: ubuntu-latest
    environment: development
    strategy:
      # Use matrix strategy to run the tasks in parallel
      matrix:
        name: ${{ fromJson(needs.get_pipeline_list.outputs.names) }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Build pipeline
        uses: ./.github/actions/build_pipeline
        with:
          pipeline_name: ${{ matrix.name }}

  get_indirect_pipeline_list:
    # Get a list of names for the indirectly changed pipelines
    # (when a related component was changed)
    needs: [ git_diff, build_pipelines_on_dev ]
    runs-on: ubuntu-latest
    outputs:
      names: ${{ steps.getter.outputs.names }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Get list
        id: getter
        run: |
          NAMES="$(make get_indirect_pipelines --paths='${{ needs.git_diff.outputs.diff }}')"
          echo "::set-output name=names::$NAMES"
  build_indirect_pipelines_on_dev:
    # Build indirectly changed pipelines on the DEV environment
    needs: get_indirect_pipeline_list
    runs-on: ubuntu-latest
    environment: development
    strategy:
      # Use matrix strategy to run the tasks in parallel
      matrix:
        name: ${{ fromJson(needs.get_indirect_pipeline_list.outputs.names) }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Build pipeline
        uses: ./.github/actions/build_pipeline
        with:
          pipeline_name: ${{ matrix.name }}

  build_components_and_test_integration_on_stage:
    # Build changed components and run integration tests 
    # for them on the STAGE environment
    needs: [ build_pipelines_on_dev, build_indirect_pipelines_on_dev, get_component_list ]
    runs-on: ubuntu-latest
    environment: staging
    strategy:
      # Use matrix strategy to run the tasks in parallel
      matrix:
        name: ${{ fromJson(needs.get_component_list.outputs.names) }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Build component
        uses: ./.github/actions/build_component
        with:
          component_name: ${{ matrix.name }}
      - name: Test component integration
        uses: ./.github/actions/test_component_integration
        with:
          component_name: ${{ matrix.name }}

  build_and_run_pipelines_on_stage:
    # Build changed pipelines and run them on the STAGE environment
    needs: [ build_components_and_test_integration_on_stage, get_pipeline_list ]
    runs-on: ubuntu-latest
    environment: staging
    strategy:
      # Use matrix strategy to run the tasks in parallel
      matrix:
        name: ${{ fromJson(needs.get_pipeline_list.outputs.names) }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Build pipeline
        uses: ./.github/actions/build_pipeline
        with:
          pipeline_name: ${{ matrix.name }}
      - name: Run pipeline
        uses: ./.github/actions/run_pipeline
        with:
          pipeline_name: ${{ matrix.name }}

  build_and_run_indirect_pipelines_on_stage:
    # Build indirectly changed pipelines and run them on the STAGE environment
    needs: [ build_and_run_pipelines_on_stage, get_indirect_pipeline_list ]
    runs-on: ubuntu-latest
    environment: staging
    strategy:
      # Use matrix strategy to run the tasks in parallel
      matrix:
        name: ${{ fromJson(needs.get_indirect_pipeline_list.outputs.names) }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Build pipeline
        uses: ./.github/actions/build_pipeline
        with:
          pipeline_name: ${{ matrix.name }}
      - name: Run pipeline
        uses: ./.github/actions/run_pipeline
        with:
          pipeline_name: ${{ matrix.name }}

It is automatically called when a pull request is opened or updated. Due to the concurrency feature, it will be run once at a time. However, all the similar jobs, like tests will be run in parallel.

The logic inside is as follows:

Analyze the changes in a pull request and find out which components and/or pipelines were affected.
Build them in the predefined order.
Run automated tests for the components.
Run the pipelines to retrain models and deliver them for the final usage.

It is universal and works with any components and pipelines when they follow the framework agreements. Also, it is safe, works without duplicates, runs the jobs in the right order, and parallelizes them when it is possible.

The current workflow operates on development and staging environments.

PR is merged

The second workflow is run when PR is merged.

name: MLOps Pull Request - Merged

on:
  # The workflow will be run automatically when a PR is closed
  pull_request:
    types:
      - closed
    branches:
      - "main"
    paths:
      - "pipelines/**/*.yaml"
      - "components/**/*.yaml"
      - "components/**/*.py"

# Use concurrency to ensure that only a single workflow 
# using the same concurrency group will run at a time
concurrency: prod_environment

jobs:
  if_merged:
    # There is no way to trigger the workflow when it was merged 
    # (for now we know only it was closed)
    # so we have to check it at the first job
    if: github.event.pull_request.merged == true
    runs-on: ubuntu-latest
    steps:
      - run: echo The PR was merged
  git_diff:
    # Get a list of changed files in the PR for the future analysis
    needs: if_merged
    runs-on: ubuntu-latest
    outputs:
      diff: ${{ steps.getter.outputs.diff }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
        with:
          fetch-depth: 0
      - name: Get git diff
        id: getter
        run: |
          GIT_DIFF="$(echo $(git diff --name-only ${GITHUB_SHA}^ ${GITHUB_SHA}))"
          echo "::set-output name=diff::$GIT_DIFF"
  get_component_list:
    # Get a list of names for the added/changed components
    needs: git_diff
    runs-on: ubuntu-latest
    outputs:
      names: ${{ steps.getter.outputs.names }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Get list
        id: getter
        run: |
          NAMES="$(make get_components --paths='${{ needs.git_diff.outputs.diff }}')"
          echo "::set-output name=names::$NAMES"
  test_and_build_components_on_prod:
    # Test and build changed components on the PROD environment
    needs: get_component_list
    runs-on: ubuntu-latest
    environment: production
    strategy:
      # Use matrix strategy to run the tasks in parallel
      matrix:
        name: ${{ fromJson(needs.get_component_list.outputs.names) }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Test component
        uses: ./.github/actions/test_component
        with:
          component_name: ${{ matrix.name }}
      - name: Build component
        uses: ./.github/actions/build_component
        with:
          component_name: ${{ matrix.name }}
      - name: Test component integration
        uses: ./.github/actions/test_component_integration
        with:
          component_name: ${{ matrix.name }}

  get_pipeline_list:
    # Get a list of names for the added/changed pipelines
    needs: [ git_diff, test_and_build_components_on_prod ]
    runs-on: ubuntu-latest
    outputs:
      names: ${{ steps.getter.outputs.names }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Get list
        id: getter
        run: |
          NAMES="$(make get_pipelines --paths='${{ needs.git_diff.outputs.diff }}')"
          echo "::set-output name=names::$NAMES"
  build_and_run_pipelines_on_prod:
    # Build and run changed pipelines on the PROD environment
    needs: [ test_and_build_components_on_prod, get_pipeline_list ]
    runs-on: ubuntu-latest
    environment: production
    strategy:
      # Use matrix strategy to run the tasks in parallel
      matrix:
        name: ${{ fromJson(needs.get_pipeline_list.outputs.names) }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Build pipeline
        uses: ./.github/actions/build_pipeline
        with:
          pipeline_name: ${{ matrix.name }}
      - name: Run pipeline
        uses: ./.github/actions/run_pipeline
        with:
          pipeline_name: ${{ matrix.name }}

  get_indirect_pipeline_list:
    # Get a list of names for the indirectly changed pipelines
    # (when a related component was changed)
    needs: [ git_diff, build_and_run_pipelines_on_prod ]
    runs-on: ubuntu-latest
    outputs:
      names: ${{ steps.getter.outputs.names }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Get list
        id: getter
        run: |
          NAMES="$(make get_indirect_pipelines --paths='${{ needs.git_diff.outputs.diff }}')"
          echo "::set-output name=names::$NAMES"
  build_and_run_indirect_pipelines_on_prod:
    # Build indirectly changed pipelines and run them on the PROD environment
    needs: [ build_and_run_pipelines_on_prod, get_indirect_pipeline_list ]
    runs-on: ubuntu-latest
    environment: production
    strategy:
      # Use matrix strategy to run the tasks in parallel
      matrix:
        name: ${{ fromJson(needs.get_indirect_pipeline_list.outputs.names) }}
    steps:
      - name: Checkout
        uses: actions/checkout@v3
      - name: Build pipeline
        uses: ./.github/actions/build_pipeline
        with:
          pipeline_name: ${{ matrix.name }}
      - name: Run pipeline
        uses: ./.github/actions/run_pipeline
        with:
          pipeline_name: ${{ matrix.name }}

This workflow is quite similar to the previous one, just runs all the jobs in the production environment.

Conclusion

That’s it! The presented solution has been working well for us for more than 6 months already. We have some ideas on how to make it even better and maybe we will share the results with the community in the next articles.

I hope this will be be useful to you in your MLOps journey and helps you to save some time while building your own CI/CD process for ML pipelines.

Please, feel free to share any thoughts, questions, or proposals in the comments.

Thank you and happy coding!

The Practical Guide to Utilizing DBT Packages for Data Transformation

iamtodor — Thu, 12 Jan 2023 10:18:36 +0000

Table of content

What are packages
Why use it
Local packages
Dbt hub packages
Verify packages are installed
Macros usage
Models usage
dbt_modules under the hood
Disclaimer
Contact

What are packages

dbt packages are collections of macros, models, and other resources that are used to extend the functionality of dbt. Packages can be used to share common code and resources across multiple dbt projects, and can be published and installed from the dbt Hub, from GitHub or can be stored locally and installed by specifying the path to the project.

In dbt, libraries like these are called packages.

Why use it

dbt packages are so powerful because so many of the analytic problems we encountered are shared across organizations.

There are a few general benefits to using packages in dbt:

Reusability: packages allow you to reuse code across multiple projects and models. This can save you a lot of time and effort, as you don't have to copy and paste the same code into multiple places.
Collaboration: packaging your models in a package allows multiple people to work on the same models at the same time. You can use version control systems like git to manage changes to the models, and use tools like the dbt test command to ensure that the models are correct and reliable.
Sharing: packaging your models or macros in a package allows you to share them with others. You can publish your package on the dbt Hub or on GitHub, and others can install and use your models in their own dbt projects.
Managing: packages make it easier to manage your codebase. You can use version control to track changes to your package, and you can easily install and update packages in your dbt project.
Modularity: packaging your models in a package allows you to break your data pipeline into smaller, more manageable pieces, which are easier to understand and maintain. This could make it streamline development and upkeep your dbt project over time. This is especially useful if you are working on a large project with many different models and transformations.

Overall, using packages can help you to build more efficient, maintainable, and scalable data pipelines with dbt.

For example, if your aim is to extract the day of the week, there is no sense to reinvent the wheel and develop this macro on your own. Rather, we might want to find the right package and make use of it {{ dbt_date.day_of_week(column_name) }}.

Local packages

In dbt, you can use local packages to organize and reuse code within a single dbt project. Local packages are stored within your project directory and are only available to the models in that project. The best use-case for local packages is some module that you want to live in the same repository, nearby to the main project.

To create a reusable local package do the following:

Consider you have the following dbt project dir structure

>>> tree -L 1 .
.
├── data
├── dbt_project.yml
├── macros
├── models
├── packages
├── packages.yml
├── profiles.yml
└── target

Create packages dir, so here we would put our first local package.

mkdir packages

Create packages.yml file, so here we would link our first local package.

touch packages.yml

Before moving on please verify that you have the following dir structure

>>> tree -L 1 .
.
├── data
├── dbt_modules
├── dbt_project.yml
├── macros
├── models
├── packages
├── packages.yml
├── profiles.yml
├── snapshots
└── target

Jump into packages dir and init your package with the name local_utils. The name of package is arbitrary.

cd packages
dbt init local_utils

It will create a package with the following structure:

>>> tree local_utils
local_utils
├── README.md
├── analysis
├── data
├── dbt_project.yml
├── macros
├── models
│   └── example
│       ├── my_first_dbt_model.sql
│       ├── my_second_dbt_model.sql
│       └── schema.yml
├── snapshots
└── tests

Next, you need to change the project name in dbt_project.yml from my_new_project to a meaningful and self-explainable name. This name will be used further as macro or model references. Let's call it local_utils.
Specify our local package in the before-mentioned packages.yml as follows:

packages:
  - local: /opt/dbt/packages/local_utils/

Make sure that you provide your absolute path to the packages. Otherwise, it would not work.

Save the packages.yml file and run the dbt deps command to install the package. This will link the package and make it available to your dbt models.

Here is an example of what the dbt deps command might look like when you install your local package:

>>> dbt deps
Running with dbt=0.21.1
Installing /opt/dbt/packages/local_utils/
  Installed from <local @ /opt/dbt/packages/local_utils/>

Now you can observe a newly created dbt_modules dir, that contains binary file local_utils. It means than our local package is ready to be used.

>>> tree dbt_modules
dbt_modules
└── utils -> /opt/dbt/packages/local_utils/

Dbt hub packages

In dbt, you can use packages from the dbt Hub to share your code with others and to reuse code from other users in your own projects. The dbt Hub is a community-driven library of packages that you can use to extend the functionality of dbt.

Probably, the best examples of third-party packages driven by the community would be:

There are a few benefits to using dbt Hub packages:

Reusable code: dbt Hub packages allow you to reuse code that has been shared by other users, teams and companies. This can save you a lot of time and effort, as you don't have to write the same logic from scratch, test and maintain it.
The major advantage of any open-source:

Community support: When you use packages from the dbt Hub, you can benefit from the support and expertise of the dbt community. If you have questions or run into issues with a package, you can ask for help on the dbt community forums or Slack channel.
Collaboration: By sharing your own packages on the dbt Hub, you can make your code available to other users. This can help to foster collaboration and improve the overall quality of your code.

Overall, using dbt Hub packages can help you to build more efficient, maintainable, and scalable data pipelines with dbt, and to collaborate with others in the dbt community.

To install a package from the dbt Hub in your dbt project, you will need to add the package to your packages.yml file.

Here is the basic process:

Go to the dbt Hub and search for the package you want to install.
Click on the package to view its details.
Copy the package name and version from the installation instructions.
Open your packages.yml file and add the package name and version to the packages list. It should look something like this:

packages:
  - name: dbt-labs/dbt_utils
    version: 0.7.6

Save the packages.yml file and run the dbt deps command to install the package. This will download the package and make it available to your dbt models.

Here is an example of what the dbt deps command might look like when you install dbt hub package:

>>> dbt deps
Installing dbt-labs/dbt_utils@0.7.6
  Installed from version 0.7.6

Unlike of local package, hub package was downloaded to dbt_modules dir physically.

>>> tree -L 2 dbt_modules
dbt_modules
└── dbt_utils
    ├── CHANGELOG.md
    ├── LICENSE
    ├── README.md
    ├── RELEASE.md
    ├── dbt_project.yml
    ├── docker-compose.yml
    ├── etc
    ├── integration_tests
    ├── macros
    └── run_test.sh

Verify packages are installed

To verify that a package is installed in your dbt project, you can check the packages.yml file and run the dbt deps command.

Check the packages.yml file: This file lists all of the packages that are installed in your dbt project. Look for the name of the package you want to verify. If it is listed in the packages list, then it is installed.
Run the dbt deps command:
1. This command will show you a list of all of the packages that are installed in your dbt project. Look for the name of the package you want to verify. If it is listed, then it is installed.
2. In the root dbt project dir, you observe a new dir dbt_modules/ which contains the compiled packages that are ready to be used. NOTE: dir dbt_modules/ has to be added to .gitignore.

>>> tree -L 1 .
.
├── data
├── dbt_modules
├── dbt_project.yml
├── macros
├── models
├── packages
├── packages.yml
├── profiles.yml
├── snapshots
└── target

If your packages.yml file contains package that is not installed then you would not be able to run any dbt command:

>>> dbt list
Encountered an error:
Compilation Error
  dbt found 1 package(s) specified in packages.yml, but only 0 package(s) installed in dbt_modules. Run dbt deps to install package dependencies.

So this is our guarantee that in runtime we would not have any issues related to the package installation.

Macros usage

In dbt, you can use packages to define custom macros that can be called from your dbt models. Here is an example of how you might use a package to define a custom macro.

Here are a few examples of how you might use macros in dbt to perform common data transformations.

For instance, lets create the following macros in our local package under local_utils/macros/cents_to_dollars.sql

{% macro cents_to_dollars(column_name, precision=2) %}
    ({{ column_name }} / 100)::numeric(16, {{ precision }})
{% endmacro %}

Next we can call our macros as {{ local_utils.cents_to_dollars(your_column_name) }}. The local_utils package names comes from the name in our package dbt_project.yml file.

Usage a macros from dbt hub packages is pretty much the same. Imagine we want to generate a surrogate key based on a few columns. This is the functional dbt-utils we previously installed provides: {{ dbt_utils.generate_surrogate_key([field_a, field_b[,...]]) }}

So the macros usage pattern from the third-party package is {{ package_name.macros_name() }}.

Models usage

As we created our own local package local_utils as a prerequisite, which has the following structure:

>>> tree packages/local_utils
local_utils
├── README.md
├── analysis
├── data
├── dbt_project.yml
├── macros
├── models
│   └── example
│       ├── my_first_dbt_model.sql
│       ├── my_second_dbt_model.sql
│       └── schema.yml
├── snapshots
└── tests

The most important function in dbt is ref(); its impossible to build even moderately complex models without it. ref() is how you reference one model within another inside your package. Here is how this looks in practice:

select
    *
from
    {{ref("model_a")}}

So if we would like to reference my_first_dbt_model from my_second_dbt_model within local_utils package then we do the following:

select
    *
from
    {{ ref("my_first_dbt_model") }}

If we want to reference my_first_dbt_model from our main project then we need to slightly change the way we call it. There is also a two-argument variant of the ref function. With this variant, you can pass both a package name and model name to ref to avoid ambiguity:

select
    *
from
    {{ ref("package_name", "model_name") }}

Our particular case would be as:

select
    *
from
    {{ ref("local_utils", "my_first_dbt_model") }}

Note: The package_name should only include the name of the package, not the maintainer. For example, if we use the dbt-labs/dbt-utils package, type dbt-utils in that argument, and not dbt-labs/dbt-utils.

dbt_modules under the hood

The dbt_modules directory is a directory that is used by dbt to store packages and their models. When you install a package using the dbt deps command, the package and its models are downloaded and stored in the dbt_modules directory.

The dbt_modules directory is located in the root directory of your dbt project. It contains subdirectories for each installed package, and each package directory contains the packages models, macros, and other resources.

The way dbt installs local and dbt hub packages is different.

Considering to have the following package.yml content:

packages:
  - package: dbt-labs/dbt_utils
    version: 0.7.6
  - local: /opt/dbt/packages/local_utils/

You would have the following modules under generated dbt_modules dir:

>>> tree -L 2 dbt_modules
dbt_modules
├── dbt_utils
│   ├── CHANGELOG.md
│   ├── LICENSE
│   ├── README.md
│   ├── RELEASE.md
│   ├── dbt_project.yml
│   ├── docker-compose.yml
│   ├── etc
│   ├── integration_tests
│   ├── macros
│   └── run_test.sh
└── utils -> /opt/dbt/packages/local_utils/

As I mentioned before, it creates a symlink for local packages, and for the dbt hub package, it simply copies all the needed files in the same name folder.

We use Google Cloud Composer to orchestrate all the transformation jobs. We basically copy our project to GCP bucket with gsutil -m rsync. Unfortunately, it does not support symbolic links:

Since gsutil rsync is intended to support data operations (like moving a data set to the cloud for computational processing) and it needs to be compatible both in the cloud and across common operating systems, there are no plans for gsutil rsync to support operating system-specific file types like symlinks.

Taken from gutils rsync's documentation Be Careful When Synchronizing Over Os-Specific File Types (Symlinks, Devices, Etc.)

The possible solution is to compress everything locally to archive, copy it to the bucket, and then unpack it to composer:

tar -czvf dbt-project.tar.gz dbt-project
gsutil -m rsync dbt-project.tar.gz gs://$BUCKET/prefix

Here’s what those switches actually mean:

-c: Create an archive.
-z: Compress the archive with gzip.
-v: Display progress in the terminal while creating the archive, also known as “verbose” mode. The v is always optional in these commands, but it’s helpful.
-f: Allows you to specify the filename of the archive.

These are pitfalls that we met when working with dbt packages.

Disclaimer

All this experience applies to dbt v0.21.1
I am aware of since v1.0 they changed the default value to dbt_packages instead of dbt_modules
I like to think that most of the guide still appears to be applicable to the latest dbt version

Contact

Building CI/CD for Vertex AI pipelines: The real world

Oleksandr Borodavka — Tue, 20 Sep 2022 15:11:50 +0000

Our first solution for a CI/CD implementation is a great start for us. It works well when you do not change many things at the same time when the team is small and updates are not frequent. However, the real workflow usually is not so simple. Let's take a look at some possible situations that can happen if we stay with the first implementation.

Concurrency mess

Issue

Let's look at what we get if we open a pull request with several changes. For instance, we have two changed components and one pipeline, and the pipeline contains both of these components.

In this case, GitHub Actions runs 3 workflows in parallel. Since the components are related to the pipeline, the pipeline will be built after each component. So the same job will be run 3 times. Also, we do not have any guarantee about the order of execution. It can be done in any order. Like on the diagram, firstly the pipeline will be built with component2, then with component1, and as a result, we will receive the pipeline only with updated component1, but none of the builds will be with both updated components.

Solution

We can avoid such situations by running the processes in a predefined order. In our case, there are no relations like component-to-component or pipeline-to-pipeline, we have only relations component-to-pipeline. So, if we would be able to run firstly all processes for components and after all processes for pipelines, it would guarantee us the correct build in the end.
Our solution is as follows, within one GHA workflow we do the following steps:

Analyze the list of changed files in a pull request
- Find changed components
- Find changed pipelines
- Find pipelines related to changed components
Run jobs for all component
Run jobs for all pipelines

It can be done with the matrix strategy and some additional logic in the code. The matrix strategy is a feature of GitHub Action that lets to run many jobs in parallel for a matrix of parameters. In our case, we can extract a list of changed components/pipelines and run all necessary tasks for them with a single job definition.

Doing all jobs in one workflow deprives us of duplicates as well. We also gain one more important outcome, we do not need to manually add and maintain workflows for every pipeline and component anymore as we did in the first implementation. Everything is being built on the fly.

Many Pull Requests

Issue

Great, but what if we have two or more open pull requests? Each PR is run in the required order, it is already solved in the previous step. However, since we store all the configurations and docker images in the cloud, they are shared resources. It means that one CI/CD process can interfere with another process, rewrite common resources, and so on.

Here we have a mess again. In addition, if component1 from PR1 is buggy and it is built in time between component1 and pipeline1 from PR2, then PR2 will fail, just because of a bug in PR1. Or even worse, when the situation is reversed and a buggy component can pass due to overwrite.

Solution

Probably the simplest solution here will be to deny concurrent CI/CD jobs. Just process one pull request at a time.

Pull Request 1 -> Pull Request 2 -> ...

With GitHub Actions, it can be done with the concurrency feature. That allows us to define concurrency groups to ensure that only a single job or workflow using the same group will run at a time.
Yes, it can be a bottleneck in the future, especially if the builds take significant time. But it solves the issue and it works for us now. Also, it is quite easy to implement, so let's go further.

Versioning

Issue

Another dangerous area is versions of docker images. We use containerized components that allow us to work with them as with independent applications, it increases reusability, and so on. It means that a ready-to-use component consists of two entities: a specification file and a docker image.
Let's look at what can happen if we always use the image:latest version of the docker image.

Situation 1

For instance, we already have a pipeline specification.

{
  "pipelineSpec": {
    ...
    "deploymentSpec": {
      "component1": {
        "image": "gcr.io/components/component1:latest"
        ...

We do not want to change anything, just want to use it to run the pipeline. Maybe we do it automatically on schedule. At the same time, we can have an ongoing CI/CD job that already rebuilt a part of the components, but some part is still in progress. Outcomes can be unpredictable.

Situation 2

Say two developers work with the same component. Tom updates component1, then works with component2, and at this moment Bet updates component1 as well. When Tom builds pipeline1 the right version of component2 will be used, but the latest image of component1 was overwritten by Bet. Tom will receive something unexpected as result. It is good if the difference between these parallel changes is obvious. Otherwise, it can take a lot of time to understand what is wrong.

Solution

By linking the concrete versions of components with image:version and doing only one CI/CD job at a time, we will be fine in the Staging and Production environments. The situation in the Development environment is still tricky. As an option, we can provide a unique dev environment for each developer. However, it will increase the price and require additional work on setting up and maintaining a variety of environments.
As a solution for the dev, we can add another CLI command (or extend the existing one) to rebuild the whole pipeline with all related components.

In this way, we will avoid the occasional overwriting of components. Everything will be built from the current code and used locally without a chance of interruption.

Conclusion

Here we have considered some important aspects that are not so obvious at the start. However ignoring it can make the CI/CD processes unstable and unpredictable.
Next time we will apply this knowledge to get a new version of CI/CD.

To be continued...

Configuring python linting to be part of CI/CD using GitHub actions

iamtodor — Thu, 15 Sep 2022 06:44:07 +0000

Hello everyone, I am a DataOps Engineer at FreshBooks. In this article I would like to share my experience on configuration best practices for GitHub actions pipelines for linting.

Freshbooks DataOps team has a linter configuration that developers can run before submitting a PR. We had an idea to integrate lint checks into our regular CI/CD pipeline. This adoption would eliminate potential errors, bugs, stylistic errors. We will basically enforce the common code style across the team.

FreshBooks uses GitHub as a home for our code base, so we would like to use it as much as possible. Recently I finished this configuration so the linter and its checks are now part of a GitHub actions CI/CD workflow.

This article has two major parts: the first one is linter configuration, and the second one is GitHub workflow configuration itself. Feel free to read all the parts, or skip some and jump into specific one you are interested in.

Linters configuration
- Disable unwanted checks
- Documentation
- Import error
- Tweaks for airflow code
GitHub workflow actions CI/CD configurations
- When to run it
- What files does it run against
- Run linter itself
Conclusion
Contact

Linters configuration

Here are the linters and checks we are going to use:

Disclaimer: author assumes you are familiar with the above-mentioned linters, tools, and checks.

I would like to share how to configure them for the python project. I prepared a full github actions python configuration demo repository.

We use flakeheaven as a flake8 wrapper, which is very easy to configure in one single pyproject.toml. The whole pyproject.toml configuration file can be found in
a demo repo.

I would say the config file is self-explainable, so I will not stop here for long. Just a few notes about tiny tweaks.

Disable unwanted checks

A few checks that we don't want to see complaints about:

Documentation

The default flakeheaven configuration assumes every component is documented.



>>> python -m flakeheaven lint utils.py

utils.py
     1:   1 C0114 Missing module docstring (missing-module-docstring) [pylint]
  def custom_sum(first: int, second: int) -> int:
  ^
     1:   1 C0116 Missing function or method docstring (missing-function-docstring) [pylint]
  def custom_sum(first: int, second: int) -> int:
  ^
     5:   1 C0116 Missing function or method docstring (missing-function-docstring) [pylint]
  def custom_multiplication(first: int, second: int) -> int:

We are ok if not every module will be documented. We are also ok if not every function or method will be documented. We are not going to push documentation for documentation's sake. So we want to disable C0114 and C0116 checks from pylint.

Import error

Our linter requirements live in a separate file and we don't aim to mix it with our main production requirements. Hence, linter would complain about import libraries as linter env does not have production libraries, quite obvious.



>>> python -m flakeheaven lint . 

dags/dummy.py
     3:   1 E0401 Unable to import 'airflow' (import-error) [pylint]
  from airflow import DAG
  ^
     4:   1 E0401 Unable to import 'airflow.operators.dummy_operator' (import-error) [pylint]
  from airflow.operators.dummy_operator import DummyOperator
  ^

So we need to disable E0401 check from pylint.

We assume that the developer who writes the code and imports the libs is responsible for writing reliable tests. So if the test does not pass it means that it's something with the import or code (logic) itself. Thus, the import check is not something we would like to put as a linter job.

Also, there is another possible solution to disable this check by including # noqa: E0401 after the import statement.



from airflow import DAG  # noqa: E0401
from airflow.operators.dummy_operator import DummyOperator  # noqa: E0401

Tweaks for airflow code

To configure code for Airflow DAGs there are also a few tweaks. Here is the dummy example dummy.py.

If we run flakeheaven with the default configuration we would see the following error:



>>> python -m flakeheaven lint .                                                       

dags/dummy.py
    17:   9 W503 line break before binary operator [pycodestyle]
  >> dummy_operator_2
  ^
    18:   9 W503 line break before binary operator [pycodestyle]
  >> dummy_operator_3
  ^
    19:   9 W503 line break before binary operator [pycodestyle]
  >> [dummy_operator_4, dummy_operator_5, dummy_operator_6, dummy_operator_7]
  ^

However, we want to keep each task specified in a new line, hence we need to disable W503 from pycodestyle.

Next, with the default configuration we would get the next warning:



>>> python -m flakeheaven lint .                                                       

dags/dummy.py
    15:   5 W0104 Statement seems to have no effect (pointless-statement) [pylint]
  (
  ^

This is about how we specify task order. The workaround here is to exclude W0104 from pylint.

More info about rules could be found on flake8 rules page.

GitHub workflow actions CI/CD configurations

Disclaimer: author assumes you are familiar with GitHub actions.

We configure GitHub Workflow to be triggered on every PR against the main (master) branch.

The whole py_linter.yml config can be found in a demo repo. I will walk you through it step by step.

When to run it

We are interested in running linter only when a PR has .py files. For instance, when we update README.md there is no sense in running a python linter.

What files does it run against

We are interested in running a linter only against the modified files. Let's say, we take a look at the provided repo, if I update dags/dummy.py I don't want to waste time and resources running the linter against main.py. For this purpose we use Paths Filter GitHub Action, which is very flexible.

If we have modified a .py file and any other files such as .toml in one PR, we don't want to run a linter against the non-python files, so we configure filtering only for .py files no matter the location: root, tests, src, etc.

The changed file can have the following statuses: added, modified, or deleted. There is no reason to run the linter against deleted files as your workflow would simply fail, because that particular changed file is no longer in the repo. So we need to configure what changes we consider triggering the linter.

I define the variable where I can find the output (only the .py files) from the previous filter. This variable would contain modified .py files that I can further pass to a flakeheaven, black, and isort. By default, the output is disabled and "Paths Changes Filter" allows you to customize it: you can list the files in .csv, .json, or in a shell mode. Linters accept files separated simply by space, so our choice here is shell mode.

Run linter itself

The next and last step is to run the linter itself.

Before we run the linter on changed files we run a check to see if there are actual changes in .py files by checking if there are any .py files from the previous step.

Next, using the before-mentioned output variable we can safety pass the content from this steps.filter.outputs.py_scripts_filter_files variable to linter.

Conclusion

That's all I would like to share. I hope it is useful for you, and that you can utilize this experience and knowledge.

I wish you to see these successful checks every time you push your code :)

If you have any questions feel free to ask in a comment section, I will do my best to provide a comprehensive answer for you.

Question to you: do you have linter checks as a part of your CI/CD?

Contact

Building CI/CD for Vertex AI pipelines: The first solution

Oleksandr Borodavka — Thu, 07 Jul 2022 11:21:54 +0000

Hey everyone, I am a Senior Data Engineer (MLOps) at FreshBooks. We are currently working on transitioning to fully automated end-to-end ML pipelines. And I want to share some ideas, solutions, and challenges we are dealing with during this journey.

A starting point

We are already building training pipelines with Vertex AI.

Example of Vertex AI pipeline from the GC blog article

That works pretty well. We can include into our pipelines data preparation steps, train several models and choose the best one, dump metadata, send notifications, etc. The Vertex AI pipelines are great themselves, but most of the processes during development and maintaining them are manual.

What's the issue here? Say we already have not just a couple of pipelines but a couple dozen. We probably already have a list of our favourite components which are used in most pipelines and say a bug was fixed in one of them. In this scenario we would need to rebuild all the related pipelines, check them locally, then deploy and check them in a real environment. What if we forgot to rebuild something? Or part of a pipeline was broken after the update? Things become trickier... In addition, all these routine operations take some time.

How can we do better?

Okay, we definitely need some automation here.

Illustration of CI/CD from synopsys

CI/CD practices are widely used in typical software development. And we can use it in our case as well.
To start doing it we need:

Some CI/CD tool, to build and run all the processes. Yeah, we can write some custom scripts for this goal, but there are a lot of stable solutions that we can use.
Automated tests, to check if everything works fine. Spreading changes without quality control is probably not a good idea.

GitHub Actions

GitHub Actions was a good choice for us since we already store all sources in GitHub, making it easy to start working with. There is secret storage and a marketplace with prebuilt actions. You do not need to set up any infrastructure, everything is provided by GitHub for free (with limitations). Alongside that is a mature modern tool that provides many ways of customization, like self-hosted runners and reusable workflows.

Tests

What about tests? Let's firstly answer what we want to test. So, a Vertex AI pipeline is a set of components organized as a DAG. Each component can be presented as a small containerized application. We can test such applications as any other software with Unit tests. Also, we can build a simple isolated pipeline(s) just for one target component to be sure it is configured and built well, and works in the Vertex AI environment. These can be our integration tests for components.
For the pipeline itself, we can add some components inside, to do ML-specific things like model validation. When we run the pipeline in a test environment and all stages of the pipeline are successfully done we can consider it works properly. It will be the integration tests for the pipelines.

Repository structure

There is one more thing we should touch on before writing the CI/CD scripts and that is the organization of code in our repository.



pipelines/
  pipeline1.yaml
  ...
components/
  component1/
    src/
      ...
    tests/
      unit/
          ...
      integration/
          test.yaml
    config.yaml

There is a folder with pipeline specifications and a folder with components. Each component contains source files, tests, and a configuration.
We also built an MLOps Framework to simplify some routines, that is why you see yaml files instead of the usual json. We probably will publish another article about it.

CI/CD workflows

Looks like everything is ready to bring GitHub Actions to the scene.

To build CI/CD in our case we need to be able:

run unit tests for a component
build a component
build a pipeline
run pipelines in different environments

In order to not copy-paste almost the same code, we can use the reusable workflows feature of GHA.

test_component.yml - runs unit tests for a component specified by input parameters



name: Test component

on:
  # the section defines the workflow as reusable and describes the input parameters
  workflow_call:                              
    inputs:
      tests_path:
        required: true
        type: string

jobs:
  tests:
    # run the job on a fresh ubuntu instance
    runs-on: ubuntu-latest
    name: "Run tests for the component"
    steps:
      # get the repo to the instance
      - name: Checkout
        uses: actions/checkout@v3
      # install requirements for the framework
      - name: Install python requirements
        run: make install
      # tun tests using the path to the tests from input parameters
      - name: Run tests
        run: make test ${{ inputs.tests_path }}

build_component.yml - builds a component specified by input parameters



name: Build component

on:
  # the section defines the workflow as reusable and describes the input parameters
  workflow_call:
    inputs:
      component_name:
        required: true
        type: string
    secrets:
      WORKLOAD_IDENTITY_PROVIDER:
        required: true
      SERVICE_ACCOUNT:
        required: true

jobs:
  build:
    # run the job on a fresh ubuntu instance
    runs-on: ubuntu-latest
    name: "Build the component"
    # set permissions necessary for google-github-actions/auth
    permissions:
      contents: "read"
      id-token: "write"
    steps:
      # get the repo to the instance
      - name: Checkout
        uses: actions/checkout@v3

      # Install docker
      - name: Install docker
        run: make install_docker

      # auth to GCP with workload_identity
      # also create credentials_file to use it in the next steps
      - id: "auth"
        name: "Authenticate to Google Cloud"
        uses: "google-github-actions/auth@v0"
        with:
          token_format: "access_token"
          workload_identity_provider: ${{ secrets.WORKLOAD_IDENTITY_PROVIDER }}
          service_account: ${{ secrets.SERVICE_ACCOUNT }}
          create_credentials_file: true
          export_environment_variables: true
      # login to GCR with the token from the previous step
      - name: Login to GCR
        uses: docker/login-action@v2
        with:
          registry: gcr.io
          username: oauth2accesstoken
          password: ${{ steps.auth.outputs.access_token }}

      # install python requirements
      - name: Install requirements
        run: make install
      # run build component command with input parameters
      - name: Build the component
        run: make build component ${{ inputs.component_name }}

Since we build components as docker images we have to install docker on the runner and login to GCR. All necessary credentials are stored in the repository secrets.

run_pipeline.yml - run a pipeline specified by input parameters



name: Run pipeline

on:
  # the section defines the workflow as reusable and describes the input parameters
  workflow_call:
    inputs:
      pipeline_name:
        required: true
        type: string
    secrets:
      WORKLOAD_IDENTITY_PROVIDER:
        required: true
      SERVICE_ACCOUNT:
        required: true

jobs:
  build:
    # run the job on a fresh ubuntu instance
    runs-on: ubuntu-latest
    name: "Run pipeline"
    # set permissions necessary for google-github-actions/auth
    permissions:
      contents: "read"
      id-token: "write"
    steps:
      # get the repo to the instance
      - name: Checkout
        uses: actions/checkout@v3

      # auth to GCP with workload_identity
      # also create credentials_file and export it to environment variables in order to use it in the next steps
      - id: "auth"
        name: "Authenticate to Google Cloud"
        uses: "google-github-actions/auth@v0"
        with:
          token_format: "access_token"
          workload_identity_provider: ${{ secrets.WORKLOAD_IDENTITY_PROVIDER }}
          service_account: ${{ secrets.SERVICE_ACCOUNT }}
          create_credentials_file: true
          export_environment_variables: true

      # install python requirements for framework
      - name: Install python dependecies
        run: make install
      # run "run pipeline" command with input parameters  
      - name: Run pipeline
        run: make run ${{ inputs.pipeline_name }}

build_pipeline.yml looks almost the same so we can skip it and move further.

Great, we have all building blocks that do the main operations. Now we just need to use them in order to build and run pipelines when changes happen. Let's use pipeline1 and component1 from the example above.

build_pipeline1.yml - builds and tests the pipeline1 when it is changed



name: Build pipeline1

on:
  # the workflow can be run manually
  workflow_dispatch:
  # the workflow will be run automatically when a PR is opened to the main branch
  # or changes to the PR related to the pipeline are pushed
  pull_request:
    types: [opened, synchronize, reopened]
    branches:
      - "main"
    paths:
      - "pipelines/pipeline1.yaml"

jobs:
  build:
    # call build_pipeline workflow with target parameters
    uses: ./.github/workflows/build_pipeline.yml
    with:
      pipeline_name: "pipeline1"
    secrets:
      WORKLOAD_IDENTITY_PROVIDER: ${{ secrets.DEV_WORKLOAD_IDENTITY_PROVIDER }}
      SERVICE_ACCOUNT: ${{ secrets.DEV_SERVICE_ACCOUNT }}
  tests:
    # call run_pipeline workflow with target parameters
    # to check it in dev env
    # this step requires the previous step to be done successfully
    needs: build
    uses: ./.github/workflows/run_pipeline.yml
    with:
      pipeline_name: "pipeline1"
    secrets:
      WORKLOAD_IDENTITY_PROVIDER: ${{ secrets.DEV_WORKLOAD_IDENTITY_PROVIDER }}
      SERVICE_ACCOUNT: ${{ secrets.DEV_SERVICE_ACCOUNT }}

build_component1.yml - builds (and tests) the component1 component when it is changed and builds the related pipeline after this



name: Build component1

on:

  # the workflow can be run manually

  workflow_dispatch:

  # the workflow will be run automatically when a PR is opened to the main branch

  # or changes to the PR related to the component1 are pushed

  pull_request:

    types: [opened, synchronize, reopened]

    branches:

      - "main"

    paths:

      - "components/component1/**"

jobs:

  tests:

    # call test_component workflow with target parameters

    uses: ./.github/workflows/test_component.yml

    with:

      tests_path: components/component1/tests

  build:

    # call build_component workflow with target parameters

    # this step requires the previous step to be done successfully

    needs: tests

    uses: ./.github/workflows/build_component.yml

    with:

      component_name: "component1"

    secrets:

      WORKLOAD_IDENTITY_PROVIDER: ${{ secrets.DEV_WORKLOAD_IDENTITY_PROVIDER }}

      SERVICE_ACCOUNT: ${{ secrets.DEV_SERVICE_ACCOUNT }}

  integration-tests:

    # call run_pipeline workflow with target parameters

    # to check the component in dev env

    # this step requires the previous step to be done successfully

    needs: build

    uses: ./.github/workflows/run_pipeline.yml

    with:

      pipeline_name: "component1-test"

    secrets:

      WORKLOAD_IDENTITY_PROVIDER: ${{ secrets.DEV_WORKLOAD_IDENTITY_PROVIDER }}

      SERVICE_ACCOUNT: ${{ secrets.DEV_SERVICE_ACCOUNT }}

  build-pipeline1:

    # call build_pipeline workflow with target parameters

    # this step requires the previous step to be done successfully

    needs: integration-tests

    uses: ./.github/workflows/build_pipeline.yml

    with:

      pipeline_name: "pipeline1"

    secrets:

      WORKLOAD_IDENTITY_PROVIDER: ${{ secrets.DEV_WORKLOAD_IDENTITY_PROVIDER }}

      SERVICE_ACCOUNT: ${{ secrets.DEV_SERVICE_ACCOUNT }}

  test-pipeline1:

    # call run_pipeline workflow with target parameters

    # to check it in dev env

    # this step requires the previous step to be done successfully

    needs: build-pipeline1

    uses: ./.github/workflows/run_pipeline.yml

    with:

      pipeline_name: "pipeline1"

    secrets:

      WORKLOAD_IDENTITY_PROVIDER: ${{ secrets.DEV_WORKLOAD_IDENTITY_PROVIDER }}

      SERVICE_ACCOUNT: ${{ secrets.DEV_SERVICE_ACCOUNT }}

Conclusion

That is it. When changes come the related workflows will be run by GHA. Tests run, components and pipelines rebuild and if everything goes well we will be able to merge the Pull Request and update these changes to Production by running these workflows manually or adding another step to trigger it automatically.

But, are we happy now?

Automatically Send Invoices With WhatsApp

lygel — Mon, 25 Apr 2022 16:35:25 +0000

In this tutorial we look at how you can create a shareable link when an Invoice is created in FreshBooks. Then send this link to your client over Whatsapp. So that the client can view the invoice immediately on his/her mobile. The same concept can be applied for checkout links, expenses and more.

Prerequisites

A FreshBooks Developer account.
A Twilio Sandbox account.
Basic knowledge of Async, Await and Node.js.
A code editor (e.g. VS Code, Sublime, Atom etc.)
How to generate a bearer token in Postman

Setup your express app locally

First we setup our express app, which listens on port 3000 and has a uri available at '/webhooks/ready'



const app = express();
app.use(express.json()); 
app.use(express.urlencoded({
    extended: true
}));

app.get('/', function (req, res) {
  res.end('Hello World 1',()=>{
  console.log(`Get Body ${JSON.stringify(req.body)}`)
  });

})

app.post('/webhooks/ready',function (req,res){
    res.end('Thanks for your business POST',()=>{
      console.log(`POST Body ${JSON.stringify(req.body)}`)
      });
    var name = req.body.name;
    if(name == "invoice.create" || name == "invoice.update"){
      var { account_id, object_id } = req.body;  
      sendShareLink(account_id,object_id);
    };
  })

  app.listen(3000,()=>{
    console.log("listening on port 3000")
  })

Create a public web server

I am making use of ‘ngrok’ to create a publicly accessible web server. You can download ngrok using this link. Once you have installed ngrok, you can start ngrok and expose your local web server. Don’t forget to make note of your https url provided by ngrok, we will use this to register a webhook. ngrok will relay our calls to our localhost server at port 3000

Register for webhooks

FreshBooks need to notify our application on invoice creation. To get notified, we need to register and listen to the FreshBooks webhook for the event 'invoice.created'. Register for webhooks using the URI generated earlier using ngrok e.g. https://d7b0-213-127-111-74.ngrok.io . This part has not yet been built into the application at time of writing. For now we do this using postman. You can use ngrok inspect to get the verifier code for the webhook.

You can inspect calls coming in via a web UI provided by ngrok. After you start the ngrok agent, open http://localhost:4040 in a browser on the same machine

Getting a shareable link and client contact

We first generate a FreshBooks client to interact using the FreshBooks nodeJs sdk. We initialise the client with the clientID of our app and the bearer token which we provided using env variables.

When you generate an invoice using the FreshBooks UI, it triggers a webhook call to our previously registered link. When our app receives this api call we retrieve the invoice id. The invoice id is then used to generate an invoice link using the FreshBooks Client.

To create a shareable invoice link we use the nodejs sdk, we use the get shareable link api to get an invoice link against the invoice id. Additionally we also retrieve the mobile number of the client.



const postWhatsapp = require('./postWhatsapp');
const clientId = process.env.CLIENTID;
const token = process.env.TOKEN;

let accountId;
let invoiceId;



module.exports = async (accountId,invoiceId)=>{
    try {
        const { Client } = await import("@freshbooks/api");
        const app = new Client(clientId,token);
        const shareLink = await app.invoices.shareLink(accountId,invoiceId);
        const invoiceInfo = await app.invoices.single(accountId,invoiceId);
        const client  = await app.clients.single(accountId,invoiceInfo.data.customerId);   


        postWhatsapp(shareLink.data.shareLink, client.data.mobPhone);

      } catch (error) {
      console.log(error);  
    }

};

Sending your invoice over whatsapp

Once we have a shareable link, we use the Twilio SDK to initialise a client using our ‘Twilio SID’ and ‘Auth Token’. Using this twilio client we send a whatsapp message which includes the shareable link to the invoice.



const twilio = require('twilio');

const accountSid  =  process.env.ACCSID; 
const authToken   =  process.env.AUTHTOK; 
const client      =  require('twilio')(accountSid, authToken); 
let shareLink;
let mobNo

module.exports= (shareLink,mobNo)=>{
  client.messages 
  .create({ 
     body: `Here is your share link ${shareLink}`, 
     from: 'whatsapp:+14155238886',       
     to: `whatsapp:${mobNo}` 
   }) 
  .then(message => console.log(message.sid)) 
  .catch(error=>{
    console.log(error);
  })
  .done();

}

If your looking for more information on the Twilio whatsapp api, you can check this link.

Now whenever you create an invoice for a client, your server receives a notice, gets the share link, and sends it to them via WhatsApp.

You can checkout the entire code at my personal repo

lygel07 / freshbooks-whatsapp-link

API Client Design Across Languages - Part 2 - Making Requests

Andrew McIntosh — Tue, 29 Mar 2022 18:53:59 +0000

It's been a while since my last post (API Client Design Across Languages - Part 1), but life and work have gotten in the way. Regardless, I'm am finally continuing my dive into how API clients can differ in style and usage across languages while still maintaining the same features.

The first post focused on the basic structure of different API clients. In this post I will go into how a client may make requests against the API.

Request Libraries

Languages vary in how well supported making HTTP requests is in their core implementations or standard libraries. Almost by definition, languages used on the web generally have easy ways to make HTTP requests. However, there are often dedicated request libraries that can make this simpler or cleaner, and in general I recommend their use unless the language has very clear and clean support.

There are reasons to not want to include a request library in an SDK as every additional dependency added is one that developers using it will have to add as well. Keeping a small dependency graph also makes it easier to maintain updates. But, letting a library do the low-level work is also good for maintenance and security, and often someone looking to use your SDK will already be using a request library.

I'll show some examples of the choices FreshBooks made for different languages below.

Sane Request Defaults

Both to make it easier for developers to use our SDKs, and to make our own lives easier in supporting them, we set some defaults for HTTP requests.

Timeouts are especially important. If a request takes too long, it may can impact both the user (slowing the response to their customer) and us (if our servers get saturated by slow requests, we stop service our customers). Most HTTP clients have easily set timeouts but often they are not enabled by default.

Setting user-agent strings is also helpful. Including things like the SDK language and version helps FreshBooks figure our SDK usage and what languages are popular with our developers. Of course we let users over-ride the user-agent if desired. It can also help an API support team track down a reported error if the client has a unique user-agent string.

Interface

The SDK should try to be as consistent and intuitive as possible, especially if the API itself is a fairly CRUD-heavy RESTful API versus one with more unique behaviours around resources.

Try to make to keep things standardized like resorce pluralization (eg. clients, invoices vs. client, invoices).

Try to keep method signature variables in a similar order. For instance:

clients.get(id, filters)
clients.create(data, filters)
clients.update(id, data, filters)

versus

invoices.get(id, filters)
invoices.create(data)
invoices.update(data, id, filters)

The more standard and intuitive the SDK is, the easier it is for developers and the fewer support tickets.

FreshBook's SDKs

Like last time, I'll show some examples of how FreshBook's SDKs are built in a few different languages.

Python

In python we're using the requests library for simplicity. Requests is widely used (see the Stripe and Auth0 SDKs) so it isn't too onerous of a requirement. In fact, FreshBooks' Python SDK is very light on dependencies in general.

You can see where we instantiate a session (to allow for retries), and make the HTTP requests). In the client we can instantiate the shared client code for each different resource.

Usage looks like:

invoice = freshBooksClient.invoices.get(account_id, invoice_id)
clients = freshBooksClient.clients.list(account_id)

assert clients[0].organization == "FreshBooks"

Node.js

Like Python, our Node.js SDK is using a well-known library axios. While it is not quite as ubiquitous as Python's requests, it is very commonly used. For instance, it is used by Auth0 (if you're looking for a different example, Shopify makes use of Got). You can find it configured here. The shared client code takes reqeust and response transform functions for each resource to convert the repsonses to objects.

PHP

Like Node.js, the PHP ecosystem has quite a number of good HTTP request libraries. Guzzle is perhaps one of the most well known, but there are many other popular libraries out there. Luckily, PHP also has some interface standards around HTTP clients and messages, particularly PSR-7, PSR-17, and PSR-18,

Implementing the FreshBooks SDK to these standards means that we don't force any particular library on developers. They are free to choose any library that implements those standards.

In our README we provide an example for those who have no particular preference:

Requires a PSR-18 implementation client. If you do not already have a compatible client, you can install one with it.

composer require amcintosh/freshbooks php-http/guzzle7-adapter

Again, here is the configuration, and the resources using shared client code.

Usage looks like:

$invoice = $freshBooksClient->invoices()->get($accountId, $invoiceId);
$clients = $freshBooksClient->clients()->list($accountId);

echo $clients->clients[0]->organization; // 'FreshBooks'

Up Next

So there you can see a number of different HTTP client options, and examples of how FreshBooks' SDKs utilize them.

I hope you found something interesting or useful here and I hope you can catch the next post where I plan to go into request data and response structures.

API Client Design Across Languages - Part 1

Andrew McIntosh — Thu, 11 Nov 2021 17:39:03 +0000

In my recent post Some Best Practices On Building An Integration, I espoused the benefits of using API owner supplied tools and libraries, and mentioned areas where a well-built SDK hides complexity from, or otherwise makes things easier for, a developer.

A colleague suggested that it might be useful to present examples of some of these areas to give some pointers for someone who needs to implement that functionality themselves, can't make use of an SDK, or simply for someone looking to build their own API client. So, this is part 1 of a deep dive into functionality in FreshBooks' (and some other API owner's) SDKs.

Basic Structure

This first post won't go too much into functionality as I think it's best to start on structure.

A RESTful API is language agnostic and clients built any number of languages must all support the same API features and resources. However, the actual design of the client and usage of the client itself can, and probably should, be different language to language. For example, a Ruby client versus a Java client will still call the same API endpoint, but the form of the methods to make that call, and the form of the returned data could look very different.

I feel it's best to build an API client in a way that is natural to the specific language it's written in. This extends from the project layout, to the client initialization, the method calls themselves, and the returned data. This makes things more intuitive and easy for a developer to use.

The language influences the design primarily in two ways: language capabilities, and common language conventions.

Capabilities

By capabilities, I'm talking about language design and features. A statically typed language usually needs a bit more structure than a dynamically typed one. For instance, an API client in a language like PHP or Python could return JSON results as associative arrays (array and dictionary respectively), as you don't have to declare the various return value's types are. It would be difficult to do the same in Java with a HashMap (possible, but it would not be clean), so you're much more likely to build data objects for the responses with all the fields included and nicely typed.

Other features play in as well. How does the language handle functions with different options? Function overloadings? Optional arguments? These all affect the design.

Conventions

Beyond what you can do with a language, there's also what you should do. You can write your Python or Ruby in a very Java-like way, but it might not feel as natural to a Ruby developer using your library. Of course conventions aren't so cut-and-dry as capabilities; there are many ways to do something and sometimes one is considered "more right" than others, but often not as well. Looking at how other libraries are implemented and getting to know a language helps informs a lot of design choices. The best advice is to try to make things clear.

FreshBook's SDKs

At the time of writing, FreshBooks has first-party Python and Node.js SDKs, and a community-supported Java one (all three are listed here). As I said, I'm going to walk through some of the differences in the design, but today I'll get started with the basics of client initialization and configuration.

First, let's talk about the configuration the FreshBooks' SDKs need to support:

We require the clients to be initialized with their application's unique client id for the user-agent string, so that's a required parameter.
To use the API requires authentication. Depending on what a developer has implemented, they'll either have a valid OAuth2 access token to initialize the client with, or they'll want to go through the authorization flow, which would require their client secret and redirect urls. Ideally the SDK supports both.
If they have an expired token, they may want to refresh it, which would require the refresh token to be supplied.
The developer may want to override some of the default settings like user-agent string, timeouts, or disabling automatic retries on failures.

Java

I'll start with the Java SDK because the features of the Java language makes it a good first example to set the others against.

Java supports function overloading, but with the number of possible options mentioned above, that would get very compicated combination-wise. You could just use nullable parameters, but that would be confusing and ugly. For example:

public FreshBooksClient(
    String clientId, String clientSecret, String redirectUri,
    String accessToken, String userAgent, Integer timeout
) {
    ...

which could like anything like:

client = new FreshBooksClient(
    <client_id>, <client_secret>, <url>, null, null, null);
client = new FreshBooksClient(
    <client_id>, null, null, <access_token>, null);
client = new FreshBooksClient(
    <client_id>, null, null, <access_token>, null, 30);

This is what the builder pattern is for. You can see the full code for
the client and the builder on github but essentially the client is not initialized directly. You initialize a "client builder", which has a constructor for each of the base cases ("client_id" versus "client_id, secret, url") and different methods for the various options, and the builder returns a client.

private FreshBooksClient(FreshBooksClientBuilder builder) {
    ...
}

public FreshBooksClientBuilder(
    String clientId, 
    String clientSecret, 
    String redirectUri
) {
    ...
}

public FreshBooksClientBuilder(String clientId) {
    ...
}

public FreshBooksClientBuilder withAccessToken(
    String accessToken
) {
    ...
}

public FreshBooksClientBuilder withReadTimeout(
    int timeout
) {
    ...
}

Which allows you to instantiate the client in the various differing ways cleanly:

client = new FreshBooksClient.FreshBooksClientBuilder(
        <client_id>, <client_secret>, <url>)
    .build();
client = new FreshBooksClient.FreshBooksClientBuilder(
        <client_id>)
    .withAccessToken(<valid token>)
    .build();
client = new FreshBooksClient.FreshBooksClientBuilder(
        <client_id>)
    .withAccessToken(<valid token>)
    .withReadTimeout(30)
    .build();

This requires much more structure in the client, but allows much cleaner usage.

Python

By comparison, Python allows for a much more concise implementation. Python is an object-oriented language and you could implement a builder pattern, but as python also supports named parameters, and there actually aren't too many options for the client, we can get away with something much simpler and more in the pythonic style (again, full code on github).

def __init__(
    self, 
    client_id: str, 
    client_secret: Optional[str] = None, 
    redirect_uri: Optional[str] = None,
    access_token: Optional[str] = None, 
    refresh_token: Optional[str] = None,
    user_agent: Optional[str] = None, 
    timeout: Optional[int] = DEFAULT_TIMEOUT,
    auto_retry: bool = True
):

which allows for:

client = Client(
    <client_id>, 
    client_secret=<client_secret>, 
    redirect_uri=<url>
)
client = Client(
    <client_id>, 
    access_token=<valid token>
)
client = Client(
    <client_id>, 
    access_token=<valid token>, 
    timeout=30
)

As you can see, the language features of Python can lead to a very different implementation and usage than Java.

Node.js

FreshBooks' Node.js SDK is written in TypeScript. Again, there are different ways to go about implementation, but we took a fairly common javascript pattern and passed a configuration object as a parameter. The Stripe Node.js Library does something similar (in general Stripe is a great place to look for any "how have others"-type API questions.)

export interface Options {
    clientSecret?: string
    redirectUri?: string
    accessToken?: string
    refreshToken?: string
    apiUrl?: string
    retryOptions?: IAxiosRetryConfig
    userAgent?: string
}

constructor(clientId: string, options: Options = {}) {
    const defaultRetry = {
        retries: 10,
        retryDelay: axiosRetry.exponentialDelay,
        retryCondition: APIClient.isNetworkRateLimitOrIdempotentRequestError,
    }
    const {
        clientSecret,
        redirectUri,
        accessToken,
        refreshToken,
        apiUrl = process.env.FRESHBOOKS_API_URL || API_BASE_URL,
        retryOptions = defaultRetry,
    } = options

with initialization looking like:

client = new Client(<client_id>, {
    clientSecret: <client_secret>
    redirectUri:  <url>
})

client = new Client(<client_id>, {
    accessToken: <valid token>,
})

This also happens to be a fairly common pattern in PHP, thus a possible future FreshBooks PHP SDK would likely look similar. auth0's PHP SDK has an example of this.

Up Next

I hope you found it interesting seeing the different ways a client for the same API can look language-to-language. As I said, I'll dive a bit more into functionality differences next time, but feel free to dig around the projects and if you have any questions, please reach out.

Getting Started with FreshBooks NodeJS SDK - Expenses & Invoices

jimioniay — Thu, 21 Oct 2021 14:08:02 +0000

Getting Started with FreshBooks NodeJS SDK - Expenses & Invoices
In this tutorial, we’ll be looking into the FreshBooks NodeJs SDK and how simple and easy it is to create, update and fetch Invoices, Expenses, Clients, Items, Payments, Projects, Time Entries etc. We have done all the heavy-lifting making it super convenient for you!

We have handled http calls, http retries, Idempotency, consistent request and response structures and many more.This way you get to focus on your business logic rather than figuring out how the FreshBooks API works.

Prerequisites

A FreshBooks Developer account. If you don't have one, you can create one here.
Authenticate yourself on the FreshBooks API using Oauth2.0. No idea how to do that? No problem, we have an excellent tutorial here.
Basic knowledge of Async, Await and Node.js.
A code editor (e.g. VS Code, Sublime, Atom etc.)

Let's get started!
Install the FreshBooks Nodejs SDK

In your Node project directory, install the FreshBooks NodeJs Client via npm or yarn

npm install @freshbooks/api

yarn install @freshbooks/api

Get your FreshBooks Client ID

Login to the FreshBooks Dashboard, click on the Settings/Gear Icon, then click on Developer Portal. Select your Oauth App and then note the Client ID (you’ll need it).
(By the way, this tutorial assumes you have created an existing Oauth App in the past and understand the dynamics of FreshBooks Authentication. If you haven’t, then this tutorial on how to create one.)

Instantiate the FreshBooks Client
Using the block of code, we can instantiate the FreshBooks Client:

import { Client } from '@freshbooks/api';
import winston from 'winston'; // This is optional

//This logger is also optional
const logger = winston.createLogger({
   level: 'error',
   transports: [
       new winston.transports.File({ filename: 'error.log', level: 'error' }),
       new winston.transports.File({ filename: 'combined.log' }),
   ],
});


// Get CLIENT ID from STEP 2 ABOVE
const clientId = '<CLIENT ID>';

// Get token from authentication or helper function or configuration
const token = '<BEARER TOKEN>';

// Instantiate new FreshBooks API client
const freshBooksClient = new Client(token, {
   clientId
}, logger);

Set a value for your Client ID and your Bearer Token. This tutorial assumes you have a helper function that helps generate the bearer tokens and refresh tokens from the /auth/oauth/token endpoints. If you don’t, you can check out authentication tutorial

Confirm Instantiation
Using the function below, we can confirm the instantiations works

const confirmClientInstantiation = async () => {
   try {
       const { data: { firstName, roles } } = await    freshBooksClient.users.me()
       accountId = roles[0].accountId;
       logger.info(`Hello ${firstName}`)
       return {
           firstName,
           accountId
       }
   } catch ({ code, message }) {
       // Handle error if API call failed
       logger.error(`Error fetching user: ${code} - ${message}`)
       return {
           error: {
               code, message
           }
       }
   }
}
console.log(await confirmClientInstantiation());

If everything works as expected you should see a response similar to the below when you invoke the function. It also returns some useful information (especially the accountid. Store in a variable as you’ll need it in other method calls).

{ firstName: 'John', accountId: 'Zz2EMMR' }

If there is something wrong, you will receive a response that looks like this:

{
  error: {
    code: 'unauthenticated',
    message: 'This action requires authentication to continue.'
  }
}

Create A Client
If everything works as expected, you should be able to create a Client, an Invoice, etc.
For simplicity, we'll create a Client. Once we do that, this same Client will be created immediately on the FreshBooks dashboard
we will create a Client and the same client created immediately on the FreshBooks Dashboard

const createAClient = async () => {
   let client =
   {
       fName: "John",
       lName: "Doe",
       email: 'no-reply@example.com',
   }
   console.log(accountId)
   try {
       const { ok, data } = await freshBooksClient.clients.create(client, accountId)
       return ok && { data };
   } catch ({ code, message }) {
       return {
           error: { code, message }
       }
   }
}

console.log(await createAClient())

List Expenses
We should also be able to list Expenses using the sample block below:

//Fetch Expenses
const fetchExpenses = async () => {
   try {
       const { ok, data } = await freshBooksClient.expenses.list(accountId);
       return ok && data
   } catch ({ code, message }) {
       console.error(`Error fetching expenses for accountid:  ${accountId}. The response message got was ${code} - ${message}`)
   }
}

console.log(await fetchExpenses());

If everything checks out, you should get a list of expenses These expenses are also listed on the FreshBooks Dashboard

{
  expenses: [
    {
       …
      id: '7538415',
      taxAmount2: null,
      taxAmount1: null,
      visState: 0,
      status: 0,
      vendor: 'FreshBooks Payments',
      notes: 'CC Payment Transaction Fee Invoice: #2021-09',
      updated: 2021-04-17T06:45:36.000Z,
      ...
    }
  ]

Conclusion
This implementation simply scratched the surface of the possibilities of the Node.js SDK as there are several use cases that can be achieved with it.

Some Best Practices On Building An Integration

Andrew McIntosh — Tue, 19 Oct 2021 10:57:35 +0000

Hi, I’m Andrew McIntosh. I’m a software engineer at FreshBooks. I’ve moved around development teams a couple of times, but right now I’m working on our API and Integrations team, trying to make things better for developers looking to build API integrations. If that’s you, or you want that to be you, here are five bits of advice on how you can build an (or build a better) API integration:

Get To Know The API
Use Libraries, Tools, and SDKs
Limit The Scope of Requests
Properly Handle Errors
Do More Asynchronous Work

Get To Know The API

This might seem obvious, but read the docs of whatever API you’re working with. This is really the starting point on figuring out how you can do the thing you want to do. Reading through, you might even find a better way to do something than you initially thought. A good API is intuitive and predictable, but even good APIs can have odd, unexpected behaviour in places. Look at examples too as they can really help in cases where the documentation is dated or sparse.

Keeping API documentation up to date is not always easy, so if you find some place where it’s wrong, someone will be very happy if you report it.

Use Libraries, Tools, and SDKs

A company with an API wants it to be used, so often they invest time and effort into making things that will make your life using it easier (like this!). Take advantage of the work they did for you and consider using libraries or SDKs they’ve provided. If a company doesn’t have an SDK, or if what they have doesn’t fit your language or framework of choice, take a look for a 3rd-party solution (often API docs will list a bunch of these in addition to company-build ones).

Using an SDK or library isn’t generally required, but there are advantages to not having to reinvent the wheel. Many of the topics I’m going to cover next might already be handled by a well-built SDK! Often SDK developers know the API quite well (especially if they’re employees of the company) and they can often simplify, clarify, or even paper over those oddities or inconsistencies I mentioned in documentation.

For example, for historical reasons a lot of the oldest accounting endpoints in FreshBooks’ API return dates in North American Eastern Time (US/Eastern aka EST/EDT, time zones are complicated), while all newer endpoints to return dates in UTC (we’re moving everything to UTC, but it takes time). If you’re using one of our SDKs, we hide that from you and return all the dates in UTC. We’ve done the work so YOU don’t have to figure out which is which!

Just like documentation, if you find a bug or missing feature in a tool, the owner would love your feedback, and if it’s open source and you’re keen, you could even try to fix it yourself.

Limit The Scope of Requests

For your own benefit, as well as the API owner’s you want to fetch data in an efficient way. This means not grabbing data that you don’t care about and will just throw away, as well as not fetching too much data at once and then having memory or performance issues trying to process it that can spill over into slow responsiveness for your users.

This means that you should look at how an API handles filters (to limit returned records to only those you care about), pagination (fetch records in smaller batches so you can handle them in chunks), sorting (so those batches come in an order that works for you), and maybe even what fields are included in the response (so the record itself isn’t filled with data you don’t need). Here is FreshBooks’ Search, Paging and Includes documentation.

For example, one integration I was debugging would sync invoices from another service to a particular client. It would check to see if that existed before creating it:

const matchingClients = await freshbooksClient.clients.list(accountId)
const matchingClient = matchingClients.data.clients.find(
    (currClient: Client) => currClient.organization === clientOrganization
)

But it was sometimes creating duplicate clients. This was because FreshBooks defaults to returning 30 records at a time with the newest ones first. This worked fine when it first created the client, but as customers used the app and made more clients, the client to sync with got bumped off of the first 30 results and was no longer found. In addition to that, the code was fetching as many clients as it could, and then filtered them in memory. It either needed a filter or pagination (or both).

const clientSearchQueryBuilder = new SearchQueryBuilder().like('Organization_like', clientOrganization)

const matchingClients = await freshBooksApi.clients.list(
    currentUser.fbAccountId,
    [searchQueryBuilder]
);
if (matchingClients.data.clients.length > 0) {
    const matchingClient = matchingClients[0]
}

Let the API do that work!

Properly Handle Errors

There’s a lot to proper error handling, so let's look at things in pieces.

API Errors

A lot of API docs will have information on error codes, states, messages. You don’t want your integration to break unexpectedly, so you should look to handle these. Logging response messages is really helpful when building an application to understand business rules or validation failures. When you’re up and running in production, it’s equally important to help you know why something might be failing. For example, a good API won’t just give you a 422 Unprocessable Entity, but might return a message like 422 - At least one field among first_name, last_name, email or organization is required (a FreshBooks client validation error message)

The API isn’t your code, and you don’t control what comes out. This is a black box, and it’s helpful to follow defensive coding practices. You should check http response codes, wrap your calls in try/catches, etc.. Don’t assume that a response you get has an object or resource data structure as a failure may return an error data structure instead, so you should be prepared to parse or otherwise handle either. If a REST APIs backend service is down, a company’s API gateway might not even return you JSON but instead give you an HTML error page that your JSON-expecting code will just fail to parse. In short, don’t assume that the response will always come in the form you expect, and ensure you handle cases when it doesn’t.

Timeouts and Connection Exceptions

The API you're using isn’t perfect. It could be slow or offline. In these cases, your integration could run into issues if all the processors or workers are stuck waiting on the API. For this reason, you should configure timeouts on your calls. HTTP clients have easily set timeouts but often they are not enabled by default. Again, a good SDK will have some sane defaults for you (we default to 30 seconds but let you easily override).

In general there are connect timeouts (how long you wait to establish a connection), and read timeouts (how long you wait for the response). Some clients let you set them together or individually, but you’ll want to set both to protect from an unresponsive server (connect), or a really slow response (read).

Figuring out exactly what the timeout values should be is very dependent on your integration and the API you’re using, but even setting them to something high (15-30 seconds) can save you a lot of pain.

Rate Limits

Most APIs will limit traffic to prevent a bad actor from hindering other integration’s performance or degrading the entire system. You should build your integration to respect and accommodate these limits. Running up against them constantly doesn’t do you any good, as your work won’t get done faster, and could result in your integration being banned.

As mentioned above, it’s best to build your integration with efficient calls in mind. Reducing the number of calls you need to make means you’re less likely to hit call limits.

Another good practice is to properly handle rate limit errors (generally HTTP 429 errors), and then rather than retry right away, wait for a bit before making the next call. If the next call is still limited, wait even longer. This is called an exponential backoff, and again, most HTTP libraries will have a way to enable this and many SDKs will have implemented this (our SDKs do it here and here).

Do More Asynchronous Work

If your integration is handling a lot of data via an API, you should consider moving as much work to asynchronous tasks as possible. In this way you can not block your users, manage and throttle your work loads, and easily retry failures. The above advice on handling rate limits gets a lot easier if your processing code isn’t blocking user actions while retrying. It also lets you throttle the calls in whatever queue system you’re using.

Look into Amazon’s SQS, Google’s Cloud Tasks queues, Python’s celery and something like CloudAMQP, or Bull with Redis. There are a lot of options out there.

Another good idea is to use webhooks if the API supports them (FreshBooks does). This allows you to register to receive messages when an event happens. Rather than polling an API every 5 minutes to see if a new resource has been created, you can tell the API to send you a message when that happens. This can save you a lot of calls and overhead.

Going back to that old integration I was debugging, it would sync invoices with FreshBooks every 20 minutes, but the process involved gathering up all the invoices it hadn’t pushed and looping through them. However, the process was driven by a synchronous HTTP call that would timeout after just a couple minutes, killing the process. It could take days to move everything over. Calling the process more often would only help a little as there were only so many calls the service could handle at one time and these long-running calls could block workers from handling user interaction with the app. We redesigned the whole sync process of the integration so that everything was done in small asynchronous tasks. On first signup we had an async task that would fetch each invoice (paginated), and put a message on the queue to process that invoice. The invoice processing tasks could thus be retried, scaled up, or throttled as needed. We also utilized webhooks for real-time updates. Each event received would just create another invoice process task. Instead of days, things could be processed in seconds, minutes, or perhaps hours for very large workloads.

Go Out And Build

Well, I hope that gives you a few ideas on building a robust integration. If you know the API you’re using and the tools available, keep your calls efficient, handle the unexpected gracefully, and keep your time consuming processing away from the user’s actions, you’re on a great path to succeed. If you have any questions, please reach out to me, and if you have anything related to FreshBooks’ API you can email newapi@freshbooks.com.