# 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