Tip
At Boxalino we are strong advisors for ELT flows (vs ETL). The major benefit is speed, transparency and maintenence: data is loaded directly into a destination system (BQ), and transformed in-parallel (Dataform).

Code Block

[
  {
    "connector": {
      "type": "sftp|gcs|plentymarket|plytix|boxalino|shopify|wordpress",
      "options": {
          // specific for each connector type
      },
      "load": {
        "options": {
          // for loading the data in BQ (BQ parameters)
          "format": "CSV|NEWLINE_DELIMITED_JSON",
          "field_delimiter": ";",
          "autodetect": true,
          "schema": "",
          "skipRows": 1(CSV)|0(JSONL),
          "max_bad_records": 0,
          "quote": "",
          "write_disposition": "WRITE_TRUNCATE",
          "create_disposition": "",
          "encoding": "",
          "allow_quoted_newlines" : 1,
          "allow_jagged_rows" : 0
        },
        "doc_X": {
          "property_node_from_doc_data_structure": [
            {
              "source": "file___NODASHDATE__.jsonl",
              "name": "<used to create suffix for BQ table>",
              "schema": ""
            }
          ]
        }
      }
    },
    "di": {
      "configuration": {
        "languages": [
          "de",
          "fr"
        ],
        "currencies": [
          "CHF"
        ],
        "mapping": {
          "languagesCountryCode": {"de":"CH_de", "fr":"CH_fr"}
        },
        "default": {
          "language": "de",
          "currency": "CHF"
        }
      }
    }
  }
]

...

The DI-SAAS SYNC request

The DI request will use the same headers (client, tm, mode, type, authorization)and a JSON request body that would provide mapping details between the loaded .jsonl files and data meaning.

...

Info
There should be a process within your own project that triggers the data sync between a 3rd party source (connector) and Boxalino.

1

Endpoint

production

https://boxalino-di-saas-krceabfwya-ew.a.run.app

12

Action

/sync

23

Method

POST

34

Body

https://boxalino.atlassian.net/wiki/spaces/BPKB/pages/928874497/DI-SAAS+ELT+Flow#DI-SAAS-Request-(Overview)

45

Headers

Authorization

Basic base64<<DATASYNC API key : DATASYNC API Secret>>

note: use the API credentials from your Boxalino account that have the ADMIN role assigned

56

Content-Type

application/json

67

client

account name

78

mode

data sync mode: F for full, D for delta, E for enrichments

89

type

product, user, content, user_content, order.

if left empty - it will check for all tables with the given tm

910

tm

(optional) time , in format: YmdHis;

Note
if the data was loaded in Boxalino GCS (https://boxalino.atlassian.net/wiki/spaces/BPKB/pages/928874497/DI-SAAS+ELT+Flow#Loading-content-to-Boxalino-GCS-(connector%3A-boxalino) ) - must re-use the tm from the load step

technical: used to identify the documents version

1011

ts

(optional) timestamp, must be millisecond based in UNIX timestamp

1112

dev

(optional) use this to mark the content for the dev data index

1213

loadOnly

(optional) if true, the data will only be loaded to BQ

1314

transformOnly

(optional) if true, the data will only be transformed (ex: dataform tag execution)

...

Info
There should be a process within your own project that triggers the data sync between a 3rd party source (connector) and Boxalino.

1

Endpoint

production

https://boxalino-di-saas-krceabfwya-ew.a.run.app

12

Action

/core

23

Method

POST

34

Body

https://boxalino.atlassian.net/wiki/spaces/BPKB/pages/928874497/DI-SAAS+ELT+Flow#DI-SAAS-Request-(Overview)

45

Headers

Authorization

Basic base64<<DATASYNC API key : DATASYNC API Secret>>

note: use the API credentials from your Boxalino account that have the ADMIN role assigned

56

Content-Type

application/json

67

client

account name

78

mode

data sync mode: F for full, D for delta, E for enrichments

89

type

product, user, content, user_content, order.

if left empty - it will check for all tables with the given tm

910

tm

(optional) time , in format: YmdHis;

Note
if the data was loaded in Boxalino GCS (https://boxalino.atlassian.net/wiki/spaces/BPKB/pages/928874497/DI-SAAS+ELT+Flow#Loading-content-to-Boxalino-GCS-(connector%3A-boxalino) ) - must re-use the tm from the load step

technical: used to identify the documents version

1011

dev

(optional) use this to mark the content for the dev data index

1112

Expand

title	Loading data to _core dataset

For the integration bellow, the rti JSON payload is loaded in the client`s dataset.

Note
The same tm value is used.

Code Block

curl  "https://boxalino-di-stage-krceabfwya-ew.a.run.app/transformer/load" \
  -X POST \
  -H "Content-Type: application/json" \
  -H "client: <account>" \
  -H "tm: 20231106000000" \
  -H "type: content" \
  -H "mode: T" \
  -H "doc: <filename>.json" \
  -d "<JSONL data>" \
  -H "Authorization: Basic <encode<apiKey:apiSecret>>"

Code Block

curl "https://boxalino-di-saas-krceabfwya-ew.a.run.app/core" \
  -X POST \
  -d "[{\"connector\":{\"type\":\"boxalino\",\"load\":{\"options\":{\"format\":\"NEWLINE_DELIMITED_JSON\",\"field_delimiter\":\"\",\"autodetect\":true,\"schema\":\"\",\"skipRows\":0,\"max_bad_records\":0,\"quote\":\"\",\"write_disposition\":\"WRITE_TRUNCATE\",\"create_disposition\":\"\",\"encoding\":\"\"},\"doc_content\":{\"rti\":[{\"source\":\"<filename>*.json\",\"autodetect\":1,\"destination\":\"<table name from core dataset>\",\"primary_field\":\"<primary field in your table>\"}]}}}}]" \
  -H "Content-Type: application/json" \
  -H "mode: F" \
  -H "dev: 0" \
  -H "tm: 20231106000000" \
  -H "type: content" \
  -H "client: <client>" \
  -H "Authorization: Basic <encode<apiKey:apiSecret>>"

Note

Note the JSON payload for table definition
"doc_content": {
"rti": [
{
"source": "rti*.jsonl",
"autodetect": 1,
“project”: “project-id”, //optional, only in the case the load is in a different GCP project
“dataset”:”dataset-name”, //optional, only in the case when the load is in a different GCP project
"destination": "doc_content_rti", // BQ table
"primary_field": "id" //Column used as primary key
}
]
}

The final output table is <client>_core.doc_content_rti.
Depending on the write_disposition property, the data is either rewritten in the table or appended.
If the optional project/ dataset details are set, the load is happening in a different GCP project (project-id.dataset-name.doc_content_rti).

...

Panel

panelIconId	1f44c
panelIcon	:ok_hand:
panelIconText	👌
bgColor	#FFEBE6

The use of the header chunk is required if the same file/document is exported in batches/sections.
Repeat steps 1+2 for every data batch loaded in GCS.
Make sure to increment the value of the chunkproperty for each /transformersaas/load/url request.

Tip

Step #1 - #2 must be repeated for every file that is required to be added for the given process (same tm, mode & type)

Only after all the files are available in GCS, you can move on to step#3.

Tip
After all required documents (doc) for the given `type` data sync (ex: product, order, etc) have been made available in GCS, the DI-SAAS request can be called https://boxalino.atlassian.net/wiki/spaces/BPKB/pages/928874497/DI-SAAS+ELT+Flow#The-DI-request , assigning connector → type : boxalino.

...

The DI-SAAS flow can be used to access privately owned files in GCS and to update/create privately owned BigQuery resources as well.

Tip
The Service Account that manages this flow (`boxalino-di-api@rtux-data-integration.iam.gserviceaccount.com`) is part of your clients` Google User Grou@Boxalino `bi-with-ai.<client>@boxalino.com`

Google Cloud Storage GCS

If your connector is gcs and you want to push and read data to a privately owned resource, share access to boxalino-di-api@rtux-data-integration.iam.gserviceaccount.com or bi-with-ai.<client>@boxalino.com (your Boxalino users group)

Required permissions (https://cloud.google.com/storage/docs/access-control/iam-roles ):

storage.objects.get,
storage.buckets.get
storage.objects.list

...

If the goal is to load the raw GCS file to a privately owned BQ resource (defined as: load → options → load_dataset_stage), you have to ensure that the dataset EXISTS and it is in using location EU.

We recommend to create a dedicated dataset for this purpose. To avoid BQ storage costs, we recommend to set an expiration date for the tables (ex: 7 days)

The same Service Account boxalino-di-api@rtux-data-integration.iam.gserviceaccount.com or bi-with-ai.<client>@boxalino.com (your Boxalino users group) must have read/write access to the dataset where the GCS data is loaded. For simplicity, BigQuery Admin is suggested.

Note
Share access only on the resources that Boxalino is allowed to access. For example, if your integrations only work on a temporary dataset - `boxalino_saas_stage` or `boxalino_saas`- you can share access at resource level (instead of project level).

...

Version	Old Version 15	New Version Current
Changes made by	Dana Negrescu	Dana Negrescu
Saved on	Nov 19, 2024	Feb 12, 2025

Content Comparison

Versions Compared

Key

The DI-SAAS SYNC request

Google Cloud Storage GCS