...

1. Data connector & Data load

   1. downloads the files from remote source to GCS

   2. loads the raw content to the transform dataset (<account>_T)

   Data transformation (with

   1. *

      1. *if options-> load_dataset & load_gcp_project values are set - the loaded raw content will be available in load_gcp_project.load_dataset instead of rtux-data-integration.<account>_T

2. Data transformation (with logic/SQL provided by the client)

   1. Steps #1-#4 from the Generic Flow

3. DI automation

...

[
  {
    "connector": {
      "type": "sftp|gcs|plentymarket|plytix|boxalino",
      "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"
        }
      }
    }
  }
]

...

1. The relevant data sources are available in .csv or JSONL (prefered) format

2. The files have a timestamp in the naming or in the access path (ex: product_20221206.jsonl)

   1. this will help automating the integration

3. The files required to update a certain data type (ex: product, order, etc) are available in the same path

4. The files are available on an endpoint (SFTP, GCS, 3rd party API, public URL) to which Boxalino has access

   1. for GCS/ GCP sources: access being shared to Boxalino`s Service Account boxalino-di-api@rtux-data-integration.iam.gserviceaccount.commust be given https://boxalino.atlassian.net/wiki/spaces/BPKB/pages/928874497/DI-SAAS+ELT+Flow#Access-sharing-(BQ-and-GCS)

   2. for AWS / SFTP : the client`s own AWS/SFTP environment with a Boxalino user & credentials must be provided
      

"connector": {
  "type": "gcs",
    "options": {
      "source": {
        "project": "<private-gcp-project>",
        "bucket": "<GCS-bucket-name>",
        "pattern": "path/to/data/__NODASHDATE__/"
      }
  },
  "load": {
    "options": {
      "format": "NEWLINE_DELIMITED_JSON",
      "autodetect": true,
      "schema": "",
      "skipRows": 0,
      "max_bad_records": 0,
      "write_disposition": "WRITE_TRUNCATE"
    },
    "doc_attribute": {
      "properties": [
        {
          "source": "file1___NODASHDATE__.jsonl"
        }
      ],
      ..
    },
    "doc_attribute_value": {
      "properties": [
        {
          "source": "file2___NODASHDATE__.jsonl"
        }
      ],
      ..
    },
    "doc_product": {
      "entity": [
        {
          "source": "file3___NODASHDATE__.jsonl",
          "autodetect": 0,
          "schema": "SCHEMA FOR LOAD TO BQ"
          "name": ""
        }
      ]
    }
}

"connector": {
      "type": "sftp",
      "options": {
        "hostname": "<server>",
        "port": 22,
        "username": "<boxalino user><user for Boxalino access>",
        "password": "<boxalino password><password for Boxalino access>",
        "source": {
          "pattern": "___NODASHDATE__",
          "files": [
          ],
          "path": "<path on server>"
        }
      },
      "load": {
        "options": {
          "format": "CSV",
          "field_delimiter": ";",
          "autodetect": true,
          "schema": "",
          "skipRows": 1,
          "max_bad_records": 0,
          "quote": "",
          "write_disposition": "WRITE_TRUNCATE",
          "create_disposition": "",
          "encoding": ""
        },
        "doc_attribute": {
          "properties": [
            {
              "source": "file1___NODASHDATE__.csv"
            }
          ],
          ..
        },
        "doc_attribute_value": {
          "properties": [
            {
              "source": "file2___NODASHDATE__.csv"
            }
          ],
          ..
        },
        "doc_product": {
          "entity": [
            {
              "source": "file3___NODASHDATE__.csv",
              "autodetect": 0,
              "schema": "SCHEMA FOR LOAD TO BQfield1:STRING,field2:INT64,field3:FLOAT64,field4:BOOL"
              "name": "used to create suffix for BQ table"
            }
          ]
        }
    },

...

2. Data Transformation

Once the data is loaded in GCS and BQ, it is time to transform it in the necessary data structure.

The transformation happens by preparing a BQ SQL for every node part of the doc_X data structure (ex: title, stock, price, string_attributes, etc). The output of the SQL will be of a certain format/structure for each property.

The DI-SAAS 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.

• it will load the files to BQ in a T (transform) dataset (ex: SELECT * FROM rtux-data-integration.<account>_T.<tm>_<doc_X>_<property>_<name>)

• it will run the transform flow (step #1-#3 from Data Integration )

  • generating each doc_X JSONL content

  • loading the doc_X JSONL in your GCS bucket

  • loading the doc_X JSONL to BQ

• it will run the SYNC request Sync Request for the process

  • loads the content in core tables in BQ

  • loads the content in the respective data index (for products)

REQUEST DEFINITION

As an integrator, please create the bellow request to the provided endpoint.

...

Endpoint

...

production

...

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

...

Action

...

/sync

...

Method

...

POST

...

Body

...

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

...

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

...

...

Content-Type

...

application/json

...

...

client

...

account name

...

...

mode

...

data sync mode: F for full, D for delta

...

...

type

...

product, user, content, user_content, order.

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

...

...

tm

...

(optional) time , in format: YmdHis;

technical: used to identify the documents version

...

...

ts

...

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

...

...

dev

...

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

2. Data Transformation

Once the data is loaded in GCS and BQ, it is time to transform it in the necessary data structure.

The transformation happens by preparing a BQ SQL for every node part of the doc_X data structure (ex: title, stock, price, string_attributes, etc). The output of the SQL will be of a certain format/structure for each property.

Dataform

The transformation happens with the help of Google Dataform https://cloud.google.com/dataform .

This implies the following:

1. The client has access to a GCP project

2. The client will create a Dataform repository https://cloud.google.com/dataform/docs/repositories

3. The client has access to a GitHub or GitLab repository (to connect it to the Dataform repository) https://cloud.google.com/dataform/docs/connect-repository

4. The client has given “Dataform Admin” permission to Boxalino Service Account boxalino-dataform@rtux-data-integration.iam.gserviceaccount.com

When using dataform for transforming the exported data (your custom CSV/JSONL files) into Boxalino Data Structure, the JSON body (for the SYNC REQUEST) has the following information (next to connector and di):

"transform": {
    "vars": {
      "var1": "value"
    },
    "tags": [
      "order"
    ],
    "dataform": {
      "project": "rtux-data-integration",
      "location": "europe-west1",
      "repository": "boxalino-di-saas-elt",
      "workspace": "dataform"
    }
  }

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.

• it will load the files to BQ in a T (transform) dataset (ex: SELECT * FROM rtux-data-integration.<account>_T.<tm>_<doc_X>_<property>_<name>)

• it will run the transform flow (step #1-#3 from Data Integration )

  • generating each doc_X JSONL content

  • loading the doc_X JSONL in your GCS bucket

  • loading the doc_X JSONL to BQ

• it will run the SYNC request Sync Request for the process

  • loads the content in core tables in BQ

  • loads the content in the respective data index (for products)

REQUEST DEFINITION

As an integrator, please create the bellow request to the provided endpoint.

...

The request above created the following resources:

...

GCS (raw data, as migrated from the connector)

1. gs://prod_rtux-data-integration_<account>/product/202303112000/F_product.jsonl

2. gs://prod_rtux-data-integration_<account>/product/202303112000/F_crossell.jsonl

3. gs://prod_rtux-data-integration_<account>/product/202303112000/F_urlrecord.jsonl

...

BQ (the T dataset - raw data as loaded from BQ)

1. rtux-data-integration.<account>_T.202303112000-doc_product-entity

2. rtux-data-integration.<account>_T.202303112000-doc_product-product_relations-crossell

3. rtux-data-integration.<account>_T.202303112000-doc_product-link

...

1	Action	/sync
2	Method	POST
3	Body	https://boxalino.atlassian.net/wiki/spaces/BPKB/pages/928874497/DI-SAAS+ELT+Flow#DI-SAAS-Request-(Overview)
4	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 Image Added
5		Content-Type	application/json
6		client	account name
7		mode	data sync mode: F for full, D for delta, E for enrichments
8		type	product, user, content, user_content, order. if left empty - it will check for all tables with the given tm
9		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
10		ts	(optional) timestamp, must be millisecond based in UNIX timestamp
11		dev	(optional) use this to mark the content for the dev data index
12		loadOnly	(optional) if true, the data will only be loaded to BQ
13		transformOnly	(optional) if true, the data will only be transformed (ex: dataform tag execution)

curl "https://boxalino-di-saas-krceabfwya-ew.a.run.app/sync" \
  -X POST \
  -d "[{\"connector\":{\"type\":\"gcs\",\"options\":{\"source\":{\"bucket\":\"my-account-bucket\",\"pattern\":\"boxalino/product/__NODASHDATE__/\"}},\"load\":{\"options\":{\"format\":\"NEWLINE_DELIMITED_JSON\",\"autodetect\":true,\"schema\":\"\",\"skipRows\":0,\"max_bad_records\":0,\"write_disposition\":\"WRITE_TRUNCATE\"},\"doc_product\":{\"entity\":[{\"source\":\"product.jsonl\",\"autodetect\":0,\"schema\":\"ProductId:INT64,Name:STRING,ProductTypeId:INT64,Sku:STRING,GroupId:STRING,Price:FLOAT64,SalePrice:FLOAT64,created:DATETIME\"}],\"product_relations\":[{\"source\":\"crosssell.jsonl\",\"name\":\"crosssell\"}],\"link\":[{\"source\":\"urlrecord.jsonl\"}]}}},\"di\":{\"configuration\":{\"languages\":[\"de\",\"fr\"],\"currencies\":[\"CHF\"],\"mapping\":{\"languagesCountryCode\":{\"de\":\"CH_de\",\"fr\":\"CH_fr\"}},\"default\":{\"language\":\"de\",\"currency\":\"CHF\"}}}}]" \
  -H "Content-Type: application/json" \
  -H "account: <boxalino-account-name>" \
  -H "mode: F" \
  -H "tm: 202303112000" \
  -H "type: product" \
  -H "Authorization: <base64_encode(api_key:api_secret)>" 

...

...

[
  {
    "connector": {
      "type": "boxalino",
       "load": {
        "options": {
          "format": "NEWLINE_DELIMITED_JSON",
          "autodetect": true,
          "schema": "",
          "skipRows": 0,
          "max_bad_records": 0,
          "write_disposition": "WRITE_TRUNCATE"
        },
        "doc_product": {
          "entity": [
            {
              "source": "product.jsonl",
              "autodetect": 0,
              "schema": "ProductId:INT64,Name:STRING,ProductTypeId:INT64,Sku:STRING,GroupId:STRING,Price:FLOAT64,SalePrice:FLOAT64,created:DATETIME"
            }
          ],
          "product_relations": [
            {
              "source": "crosssell.jsonl",
              "name": "crosssell"
            }
          ],
          "link": [
            {
              "source": "urlrecord.jsonl"
            }
          ]
        }
      }
    },
    "di": {
      "configuration": {
        "languages": [
          "de",
          "fr"
        ],
        "currencies": [
          "CHF"
        ],
        "mapping": {
          "languagesCountryCode": {"de":"CH_de", "fr":"CH_fr"}
        },
        "default": {
          "language": "de",
          "currency": "CHF"
        }
      }
    }
  }
]

The DI-SAAS CORE request

In order to achieve this, there are 2 requests to be made:

1. load data to GCS https://boxalino.atlassian.net/wiki/spaces/BPKB/pages/928874497/DI-SAAS+ELT+Flow#Loading-content-to-Boxalino-GCS-(connector%3A-boxalino)

2. load data from GCS to <client>_core.<destination> table

REQUEST DEFINITION

As an integrator, please create the bellow request to the provided endpoint.

	Endpoint	production	https://boxalino-di-saas-krceabfwya-ew.a.run.app
1	Action	/core
2	Method	POST
3	Body	https://boxalino.atlassian.net/wiki/spaces/BPKB/pages/928874497/DI-SAAS+ELT+Flow#DI-SAAS-Request-(Overview)
4	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 Image Added
5		Content-Type	application/json
6		client	account name
7		mode	data sync mode: F for full, D for delta, E for enrichments
8		type	product, user, content, user_content, order. if left empty - it will check for all tables with the given tm
9		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
10		dev	(optional) use this to mark the content for the dev data index
11

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>>"

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>>"

Loading content to GCS (connector: boxalino, gcs)

There are 2 available flows, based on the size of your data:

1. The content is exported as the body of your POST request

2. The content is exported with the help of a public GCS Signed URL (https://cloud.google.com/storage/docs/access-control/signed-urls )

Option #1 is recommended for data volume less than 32MB.

Option #2 is allowed for any data size.

The LOAD request will create a GCS file in your project`s GCS buckte: gs://prod_rtux-data-integration_<account>/<type>/<tm>/<mode>_<doc>

• there is no content validation at this step

A. Loading content less than 32 MB

Files under 32MB can be loaded directly as REQUEST BODY CONTENT in Boxalino`s GCS.

The sample request bellow would create the languagefeed_20220317.csv in your GCS bucket gs: [ "de", "fr" ], "currencies": [ "CHF" ], "mapping": { "languagesCountryCode": {"de":"CH_de", "fr":"CH_fr"} }, "default": { "language": "de", "currency": "CHF" } } } } ]

Loading content to Boxalino GCS (connector: boxalino)

There are 2 available flows, based on the size of your data:

1. The content is exported as the body of your POST request

2. The content is exported with the help of a public GCS Signed URL (https://cloud.google.com/storage/docs/access-control/signed-urls )

Option #1 is recommended for data volume less than 32MB.

Option #2 is allowed for any data size.

The LOAD request will create a GCS file in your project`s GCS buckte: gs://prod_rtux-data-integration_<account>/<type>/<tm>/<mode>_<doc>

• there is no content validation at this step

A. Loading content less than 32 MB

Files under 32MB can be loaded directly as REQUEST BODY CONTENT in Boxalino`s GCS.

The sample request bellow would create the languagefeed_20220317.csv in your GCS bucket gs://prod_rtux-data-integration_<account>/<type>/<tm>/<mode>_<filename>

...

//prod_rtux-data-integration_<account>/<type>/<tm>/<mode>_<filename>

curl "https://boxalino-di-process-krceabfwya-ew.a.run.app/saas/load" \
  -X POST \
  -H "Content-Type: application/json" \
  -H "client: <account>" \
  -H "dev: true|false" \
  -H "tm: YYYYmmddHHiiss" \
  -H "type: product|content|order|user|communication_history|communication_planning|user_generated_content" \
  -H "mode: T" \
  -H "gcp_project: <private-client-GCP-project>" \
  -H "gcs_bucket: <private-client-GCS-bucket>" \
  -H "doc: <filename>" \
  -d "<JSONL>" \
  -H "Authorization: Basic <encode of the account>"

curl --connect-timeout 30 --max-time 300 "https://boxalino-di-process-krceabfwya-ew.a.run.app/saas/load" \
  -X POST \
  -H "Content-Type: application/json" \
  -H "client: <account>" \
  -H "tm: 20230301161554" \
  -H "type: product" \
  -H "mode: T" \
  -H "doc: doc_language.json" \
  -d "{\"language\":\"en\",\"country_code\":\"en-GB\",\"creation_tm\":\"2023-03-01 16:15:54\",\"client_id\":0,\"src_sys_id\":0}\n{\"language\":\"de\",\"country_code\":\"de-CH\",\"creation_tm\":\"2023-03-01 16:15:54\",\"client_id\":0,\"src_sys_id\":0}" \
  -H "Authorization: Basic <encode of the account>" 

B. Loading undefined data size

For content over 32MB, we provide an endpoint to access a Signed GCS Url that would put all your streamed content into a file (currently there is no defined file size limit in GCS)

1. Make a request for public upload link

This is the generic POST request:

curl --connect-timeout 

60 --max-time 300 "https://boxalino-di-

process-krceabfwya-ew.a.run.app/

saas/load/url" \
  -X POST \
  -H "Content-Type: application/json

" \
  -H "

client: 

<account>" \
  -H "

dev: 

true|false" \
  -H "

tm: 

YYYYmmddHHiiss" \
  -

H "

type: product|content|order|user|communication_history|communication_planning|user_generated_content" \
  -H "mode: T" \
  -H "gcp_project: <private-client-GCP-project>" \
  -H "gcs_bucket: <private-client-GCS-bucket>" \
  -H "chunk: <id>" \
  -H "doc: <filename>" \
  -H "Authorization: Basic <encode of the account>"

B. Loading undefined data size

For content over 32MB, we provide an endpoint to access a Signed GCS Url that would put all your streamed content into a file.

1. Make a request for public upload link

...

The response will be an upload link that can only be used in order to create the file in the clients` GCS bucket. The link is valid for 30 minutes.

curl --connect-timeout 60 --max-time 300 "https://boxalino-di-

process-krceabfwya-ew.a.run.app/

saas/load/url" \
  -X POST 

\
  -H "Content-Type: application/json" \
  -H "client: <account>" \
  -H "

tm: 

20230301161554" \
  -H "

type: 

product" \
  -H "

mode: 

T" \
  -H "

doc: 

product-entity.jsonl" \
  -H "

Authorization: Basic <encode of the account>"

2. Upload the content on the public link

curl --connect-timeout 60 --timeout 0 <GCS-signed-url> \
    -X PUT \
    -H "chunkContent-Type: <id>application/octet-stream" \
    -Hd "doc: <filename>" \
  -H "Authorization: Basic <encode of the account>"

The response will be an upload link that can only be used in order to create the file in the clients` GCS bucket. The link is valid for 30 minutes.

curl --connect-timeout 60 --max-time 300 "https://boxalino-di-stage-krceabfwya-ew.a.run.app/transformer/load/url" \
  -X POST \
  -H "Content-Type: application/json" \
  -H "client: <account>" \
  -H "tm: 20230301161554" \
  -H "type: product" \
  -H "mode: F" \
  -H "doc: product-entity.jsonl" \
  -H "Authorization: Basic <encode of the account>"

2. Upload the content on the public link

curl --connect-timeout 60 --timeout 0 <GCS-signed-url> \
    -X PUT \
    -H "Content-Type: application/octet-stream" \
    -d "<YOUR DOCUMENT JSONL CONTENT (STREAM)>"

...

...

Technical notes

...

Every request to our nodes returns a response (200 or 400). This can be used internally for testing purposes as well.

...

<YOUR DOCUMENT JSONL CONTENT (STREAM)>"

Technical notes

1. Every request to our nodes returns a response (200 or 400). This can be used internally for testing purposes as well.

2. It is possible to review the STATUS of the requests at any given time Status Review

Access sharing (BQ and GCS)

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

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:

1. storage.objects.get,

2. storage.buckets.get

3. storage.objects.list

This is achieved through Storage Legacy Bucket Reader & Storage Legacy Object Reader roles.

5. Image Added

...

BigQuery Access (BQ)

If the goal is to load the raw GCS file to a privately owned BQ resource (defined as: load → options → load_dataset), you have to ensure that the dataset EXISTS and it is 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)

Image Added

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.

...