# About
This is documentation how to configure a own [[Tooling/Extraction/Airbyte]] instance on GCP to connect it to [[Exact Online]]
# Setup tooling
Installing terminal macOS
- https://iterm2.com/downloads.html
Installing Homebrew package manager
- https://brew.sh
install or upgrade google-cloud-cdk cask
*Install*
```sh
brew install --cask google-cloud-sdk
```
*Upgrade*
```sh
brew upgrade google-cloud-sdk
```
[20241129 Download google-cloud-cdk.mp4](**[20241129 Download google-cloud-cdk.mp4](https://drive.google.com/open?id=12MNG1LTvfF34hBMaOXQkiwW3-L8_oA-2&usp=drive_fs)**)
Set Environment variables
```
PROJECT_ID={}
INSTANCE_NAME={}
gcloud init
```
*Or better create a configuration before setting env vars*
[[#^bae1fa]]
# Deploy Airbyte instance
## Documentation
- Git Repository met de meest recente tag https://github.com/Jeroendevr/airbyte_exact/tree/v1.0.0
- Documentation from airbyte for this airbyte version can be found at the recent tag documentation https://github.com/Jeroendevr/airbyte_exact/blob/v1.0.0/docs/deploying-airbyte/on-gcp-compute-engine.md
## Create Airbyte Service accounts
### Attaching Service Accounts
### Configuration
*Roles*
- BigQuery Data editor
- BigQuery User
- Storage Admin
- BigQuery Admin
- Artifact Registry Administrator
- Compute Admin
Set service account to vm
Access scope allow default access
```sh
gcloud config set account {account}@{..}.iam.gserviceaccount.com
gcloud auth login
```
## Creating VM instance
[20250317 Create VM instance v2](https://drive.google.com/open?id=1RzvmQS-NZr5j5Xc4PO3N83mT7Cjqy8Fs&usp=drive_fs)
#### Settings of the VM
- `e2.medium` instance 2vCPU and 4GB memory. Is sufficient for most of our workloads.
- Otherwise take `e2-custom-2-6144 (2 vCPUs, 6 GB Memory)` for some extra ram
- As time of writing Ubuntu LTS 24
- Security
- Attach the Service account
## Connect with vm
*With environment variables set*
```sh
gcloud --project=$PROJECT_ID compute ssh $INSTANCE_NAME
```
*With configuration*
```sh
gcloud compute ssh {INSTANCE_NAME}
```
[20241129 Connect to VM instance.mp4](https://drive.google.com/file/d/12OjyezuadG2ZFRe2SCYxwrdZa2jfoLUP/view?usp=share_link)
### Install Docker on VM
[20241129 Install docker on VM.mp4](https://drive.google.com/file/d/12Q2r0uS9InIZgWV7H2F6CNWfU_2fXkQP/view?usp=share_link)
```sh
sudo apt-get update
sudo apt-get install -y apt-transport-https ca-certificates curl gnupg2 software-properties-common
curl -fsSL https://download.docker.com/linux/debian/gpg | sudo apt-key add --
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/debian buster stable"
sudo apt-get update
sudo apt-get install -y docker-ce docker-ce-cli containerd.io
sudo usermod -a -G docker $USER
sudo apt-get -y install docker-compose-plugin
docker compose version
logout
```
## Install Airbyte on VM Instance
[20241129 Install Airbyte on VM.mp4](https://drive.google.com/file/d/12WCikm5-qKlU-3Z97H24FVy4K85Iwd8m/view?usp=share_link)
Connect with vm
```sh
gcloud --project=$PROJECT_ID compute ssh $INSTANCE_NAME
```
### Install Airbyte
```sh
mkdir airbyte && cd airbyte
wget https://raw.githubusercontent.com/Jeroendevr/airbyte_exact/refs/tags/v1.0.0/run-ab-platform.sh
chmod +x run-ab-platform.sh
./run-ab-platform.sh -b
```
## Allow connection to Airbyte on VM
[20241206 Global firewall policy.mp4](https://drive.google.com/file/d/13fi8-hgxpiv98Cqq8BaPOCIdld4k0ikU/view?usp=share_link)
### Firewall policy settings
Priority 2000(+)
*Source filters*
IPv4 Range Enter your own ip adress from [watismijnip.nl](https://watismijnip.nl)
*Protocols and ports*
TCP port 8000
Associate policy with VPC network (default if there is not a specifc one)
## Connect to Airbyte on VM Instance
[20241206 Login to airbyte.mp4](https://drive.google.com/file/d/13jc2TONz8JTc7yDTS-d8fd15xgXl3Q3T/view?usp=share_link)
Default airbyte, password
## Create Exact Online Docker Container Connector
### Install Docker desktop using homebrew
[20241206 Install Docker.mp4](https://drive.google.com/file/d/13lJUGTM6926EYgEO2mEir8Cq6Dq_W-bk/view?usp=share_link)
```sh
brew install --cask docker
```
### Build the source docker image
#### Option A: Building the docker image with airbyte-ci
See the chapter at [GitHub documenatation](https://github.com/maxq-analytics/airbyte_exact/blob/v1.0.0/docs/connector-development/tutorials/building-a-python-source.md#iterating-on-your-implementation)
[20241220 Building Exact Connector](https://drive.google.com/file/d/13s9GjKw9NKoi76GOCTttmryTLBOa1GVd/view?usp=share_link)
Clone the repository from the MaxQ Github repository
The Python Dependency tool used to manage your Virtual Environment (Venv) is [Poetry](https://python-poetry.org)
To activate the Venv run
```sh
poetry run airbyte-ci connectors --name=source-exact build
```
A notification `It looks like you are not running this command from the airbyte repo (airbytehq/airbyte).` is correct if then name of your clone is not this same.
*Build complete*
A webpage opens with `source-exact test report` if the build is succesfull
## Upload to Artifact Registry
- [Youtube demo](https://www.youtube.com/watch?v=4YF20PODv30&ab_channel=Airbyte) by airbyte itself
- [Allow artifact registry access](https://github.com/maxq-analytics/airbyte_exact/blob/db8024aa1ab94c445f8c20ab261c461f6c6fe5d5/docs/operator-guides/using-custom-connectors.md#on-gcp---artifact-registry) on GCP Artifact Registry
- See [GCP Quistart](https://cloud.google.com/artifact-registry/docs/docker/store-docker-container-images#auth) Configure Authentication
[20241223 Authenticatie Artifact Repo.mp4](https://drive.google.com/file/d/13sZ9ED0UZSyxEgGMWVsqnRiad8999ssa/view?usp=share_link)
### Create Artifact Registry Repository
In GCP -> Artifact registry -> create repository
*Setting*
- name {airbyte-source}
- Docker
- Region {europe-west4}
### Authenticate docker to gcp
Execute the following on your local environment. Run the same authentication flow on your Compute Engine instance.
```sh
gcloud auth configure-docker europe-[region-]docker.pkg.dev
```
*region*
if region is specified in the GCP ui also specified, also specify it during authenthication, tagging and pushing.
### Tag image to registry
Set your local docker image to the artifact registry
Run on your local machine
```sh
docker tag airbyte/source-exact:dev europe-[region-]docker.pkg.dev/{project_id}/{airbyte-source}/source-exact
docker push europe-[region-]docker.pkg.dev/{project_id}/{airbyte-source}/source-exact
```
## Configure Airbyte Instance
Relevant Documentation
- [Exact online API portal](https://support.exactonline.com/community/s/knowledge-base#All-All-DNO-Content-restintro)
Prerequisite
- Exact online account with the following
- Rights to [manage subscriptions](https://support.exactonline.com/community/s/knowledge-base#All-All-HNO-Process-general-exactonlineappcentre-appcent-strtusapp) (for adding Apps)
![[Exact manage subscriptions.png]]
- In [dutch](https://support.exactonline.com/community/s/knowledge-base#All-All-HNO-Process-general-exactonlineappcentre-appcent-strtusapp)
## Register a new app in Exact Online
- Name
- Redirect URI - This can be anything.
Related video’s :
- [20241118 Login to ExactOnline Appstore.mp4](https://drive.google.com/file/d/11U1hMytoDJC5iHp45RcA5mX-StDNZ75f/view?usp=share_link)
- [20241118 Register App.mp4](https://drive.google.com/file/d/11U1hMytoDJC5iHp45RcA5mX-StDNZ75f/view?usp=share_link)
- [20241118 Retrieve Code.mp4](https://drive.google.com/file/d/11srZ84ihfPoYl21hHvRpdbJvpX5RqWk9/view?usp=share_link)
- [20241118 Retrieve Access and Refresh token](https://drive.google.com/file/d/11t2GVHEnuoRjW_CioLVkTHVaOkN0Majb/view?usp=sharing)
### Create initial OAuth 2.0 Token
- In your browser go to the following URL (after replacing the variables).
https://start.exactonline.nl/api/oauth2/auth?client_id={client_id}&redirect_uri=https://google.com&response_type=code
For example e.g.
https://start.exactonline.nl/api/oauth2/auth?client_id=afea3950-01c4-4761-8649-807bb8cfa772&redirect_uri=https://google.com&response_type=code
This leads to a redirect, copy the code from the response after `?code=` this starts with `stamp`
This code needs to be decoded using something like Cyberchef
and use that within the following `curl` request.
```sh
curl -X POST 'https://start.exactonline.nl/api/oauth2/token' \
--data-urlencode 'grant_type=authorization_code' \
--data-urlencode 'client_id=<CLIENT_ID>' \
--data-urlencode 'client_secret=<CLIENT_SECRET>' \
--data-urlencode 'redirect_uri=<REDIRECT_URI>' \
--data-urlencode 'code=<CODE>'
```
If access_token is expired following curl can be used to refresh the token
```sh
curl -X POST 'https://start.exactonline.nl/api/oauth2/token' \
--data-urlencode 'grant_type=refresh_token' \
--data-urlencode 'refresh_token=<REFERSH_TOKEN>' \
--data-urlencode 'client_id=<CLIENT_ID>' \
--data-urlencode 'client_secret=<CLIENT_SECRET>' \
```
*Related Video’s:*
- [20241118 Configure tokens in Airbyte.mp4](https://drive.google.com/file/d/127Z9kJbztp7ATGFzGKXkilAmE5nTXPH6/view?usp=share_link)
### Add connector to VM
#### Add connector to airbyte instance
- In Airbyte click add docker connector
- europe-[region-]docker.pkg.dev/{project_id}/{airbyte-source}/source-exact
- Docker tag : latest
- [20241223 Add custom connector to docker.mp4](https://drive.google.com/file/d/13vYnrz-pwcoYJXb-8VvTNOgY8zdt9xT0/view?usp=share_link)
# gcloud configurations
^bae1fa
Creating a configuration with the `gcloud` cli makes for easy switching
```
gcloud config configurations create {config_name}
gcloud config set account {mailadres of gcp project}
gcloud auth login
gcloud config set project {project_id}
gcloud config set compute/zone {europe-west4-c}
gcloud config set compute/region {europe-west4}
```
# Troubleshooting
## Cannot add connector to Airbyte
When selecting add new docker connector
*Internal Server Error: Get Spec job Failed*
- Is your service account added to the VM's gcloud configuration?
- Try if you can connect to the artifact registry with `docker pull`
```sh
gcloud config set account {service account}
```
# Security best practice
# Allowlisting
Airbyte can connect from the following IP-adresses to sources.
### IPv4
34.106.109.131
34.106.196.165
34.106.60.246
34.106.229.69
34.106.127.139
34.106.218.58
34.106.115.240
34.106.225.141
13.37.4.46
13.37.142.60
35.181.124.238
# Streams
The typical streams we need to extract for a good financial overview are
See [Exact Online Rest documentation](https://start.exactonline.nl/docs/HlpRestAPIResources.aspx)
- [Accounts](https://start.exactonline.nl/docs/HlpRestAPIResourcesDetails.aspx?name=CRMAccounts)
- [ActiveEmployment](https://start.exactonline.nl/docs/HlpRestAPIResourcesDetails.aspx?name=PayrollActiveEmployments)
- [Budgets](https://start.exactonline.nl/docs/HlpRestAPIResourcesDetails.aspx?name=BudgetBudgets)
- [Deleted](https://start.exactonline.nl/docs/HlpRestAPIResourcesDetails.aspx?name=SyncDeleted)
- [Departments](https://start.exactonline.nl/docs/HlpRestAPIResourcesDetails.aspx?name=HRMDepartments)
- [Employees](https://start.exactonline.nl/docs/HlpRestAPIResourcesDetails.aspx?name=PayrollEmployees)
- [EmploymentOrganizations](https://start.exactonline.nl/docs/HlpRestAPIResourcesDetails.aspx?name=PayrollEmploymentOrganizations)
- [Employments](https://start.exactonline.nl/docs/HlpRestAPIResourcesDetails.aspx?name=PayrollEmployments)
- [TransactionLines](https://start.exactonline.nl/docs/HlpRestAPIResourcesDetails.aspx?name=FinancialTransactionTransactionLines)
- [GeneralLedgerAccountclassificationmappings](https://start.exactonline.nl/docs/HlpRestAPIResourcesDetails.aspx?name=FinancialGLAccountClassificationMappings)
- [GeneralLedgerClassifications](https://start.exactonline.nl/docs/HlpRestAPIResourcesDetails.aspx?name=BulkFinancialGLClassifications)
- [InvoiceTerms](https://start.exactonline.nl/docs/HlpRestAPIResourcesDetails.aspx?name=ProjectInvoiceTerms)
- [Project Time Transactions](https://start.exactonline.nl/docs/HlpRestAPIResourcesDetails.aspx?name=ProjectTimeTransactions)
- [Projects](https://start.exactonline.nl/docs/HlpRestAPIResourcesDetails.aspx?name=ProjectProjects)
- [SalesInvoices](https://start.exactonline.nl/docs/HlpRestAPIResourcesDetails.aspx?name=SalesInvoiceSalesInvoices)
- [Schedules](https://start.exactonline.nl/docs/HlpRestAPIResourcesDetails.aspx?name=HRMSchedules)
# Other
## Division ID
The division ID is needed to make a request to Exact Online's endpoint, this can be found while logging into your exact online account and navigating to
[Rest API reference documentation](https://start.exactonline.nl/docs/HlpRestAPIResources.aspx?)
Under *resource URI* the endpoints starts with `/api/v1/1234567/` where `12345676` is your division id