bq\_table\_description
type: string
mandatory
| Description of the BigQuery table. | |bq\_table\_schema
type: array
mandatory
|BigQuery table schema. It contains a list of fields corresponding to the number of columns it will contain.
Each field described has three attributes:
- name
- type
- description
bq\_table\_clustering\_fields
type: array
optional
|List of fields used when clustering is enabled.
The table data will be automatically organized based on the contents of the fields you specify. Their order determines the sort order of the data.
If this parameter is set, time partitioning will be automatically enabled on the table. If you don't set partitioning parameters, default values will be used.
| |bq\_table\_timepartitioning\_field
type: string
optional
|If this parameter is set, the table will be partitioned by this field.
If not, the table will be partitioned by pseudo column \_PARTITIONTIME.
The field must be a top-level TIMESTAMP or DATE field. Its mode must be NULLABLE or REQUIRED.
(Refer to BigQuery documentation for more information.)
Note: You can set this parameter to a field that equals to DATE(''). Then, if you relaunch an execution with a partition, and if default\_write\_disposition is set to "WRITE\_APPEND" in the JSON configuration file, Tailer will check if the corresponding partition already exists in the table:
- If it does, it will delete it, and replace it with the current execution data.
- If not, it will add them.
bq\_table\_timepartitioning\_expiration\_ms
type: integer
optional
|Number of milliseconds for which to keep the storage for a partition.
(Refer to BigQuery documentation for more information.)
| |bq\_table\_timepartitioning\_require\_partition\_filter
type: boolean
optional
|If set to true, queries over the partitioned table require a partition filter that can be used for partition elimination to be specified.
(Refer to BigQuery documentation for more information.)
| ### Data types Tailer Platform supports the following data types. #### Numeric types | Name | Description | | --------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `int64` |Integers are numeric values that do not have fractional components.
They range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
| | `float64` | Floating point values are approximate numeric values with fractional components. | | `numeric` |This data type represents decimal values with 38 decimal digits of precision and 9 decimal digits of scale. (Precision is the number of digits that the number contains. Scale is how many of these digits appear after the decimal point.)
It is particularly useful for financial calculations.
| #### Boolean type | Name | Description | | --------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `boolean` | This data type supports the `true`, `false`, and `null` values. It can perform some basic conversions, such as `'true'`, `'True'`, `True`, or 1 becoming `true`. | #### String type | Name | Description | | -------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `string` |Variable-length character data.
When converting data from string to a different data type, makes sure to use safe\_cast when you're unsure about the data quality.
configuration\_type
type: string
mandatory
|Type of data operation.
For a TTT data operation, the value is always "table-to-table".
| |configuration\_id
type: string
mandatory
|ID of the data operation.
You can pick any name you want, but is has to be unique for this data operation type.
Note that in case of conflict, the newly deployed data operation will overwrite the previous one. To guarantee its uniqueness, the best practice is to name your data operation by concatenating:
- your account ID,
- the word "load",
- and the target dataset or table.
short\_description
type: string
optional
| Short description of the context of the data operation. | |doc\_md
type: string
optional
| Path to a file containing a detailed description of the data operation. The file must be in Markdown format. | |environment
type: string
mandatory
|Deployment context.
Values: PROD, PREPROD, STAGING, DEV.
| |account
type: string
mandatory
| Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator. | |activated
type: boolean
optional
|Flag used to enable/disable the execution of the data operation.
Default value: true
| |archived
type: boolean
optional
|Flag used to enable/disable the visibility of the data operation's configuration and runs in Jarvis Studio.
Default value: false
| |start\_date
type: string
mandatory
|Start date of the data operation.
The format must be:
"YYYY, MM, DD"
Where:
- YYYY >= 1970
- MM = \[1, 12]
- DD = \[1, 31]
schedule\_interval
type: string
mandatory
|A Tables to Tables data operation can be launched in two different ways:
- If schedule\_interval is set to "None", the data operation will need to be started with a Workflow, when a given condition is met. (This solution is recommended.)
- If you want the data operation to start at regular intervals, you can define this in the schedule\_interval parameter with a Cron expression.
Example
For the data operation to start everyday at 7:00, you need to set it as follows:
"schedule\_interval": "0 7 \* \* \*",
You can find online tools to help you edit your Cron expression (for example, crontab.guru).
| |max\_active\_runs
type: integer
optional
|This parameter limits the number of concurrent runs for this data operation. As most data operations are run once daily, there is usually no need to set a value higher than 1 here.
Default value: 1
| |task\_concurrency
type: integer
optional
|This parameter limits the number of tasks that might run concurrently.
As a great volume of data might be handled by each task, it is important to make sure to avoid consuming too many resources for one data operation.
Make sure also that the value you set here is high enough for concurrent tasks set in the task\_dependencies parameter to run properly.
Default value: 5
| |catchup
type: boolean
optional
|This parameter allows you to specify if you want to execute the data operation runs that were supposed to happen between start\_date and the actual deployment date.
Example
If you start receiving data from September 1st, but you only finish writing your code on September 7th, you might want to run your data operation from a date in the past: September 1st.
The catchup parameter can have two values:
- If it is set to "true" AND a scheduling\_interval is set AND start\_date is set in the past, Composer/Airflow will execute every run of the data operation scheduled from the start date until the current date.
- If it is set to "false", the data operation will only be executed starting from the current date.
⚠️ If the data operation is scheduled to happen frequently and/or the missed execution period is long, the amount of runs might be important. Make sure you have enough resources to handle all the executions when deploying a data operation with catchup set to "true".
⚠️ If you plan to store a version of your configuration file for further modifications (in a Git repository for example), it's recommended to store it with a catchup set to "false" to avoid to accidentally create a lot of runs when you will deploy and execute your next version in the future.
Default value: false
| |task\_dependencies
type: array of strings
mandatory
|The task\_dependencies parameter allows you to create dependencies between the different tasks specified in the workflow parameter (see below). It will define in which order the workflow tasks will run, some of them running concurrently, others sequentially.
Usually workflow tasks will be run in the following order:
- create\_gbq\_table
- sql
- copy\_gbq\_table
Syntax
- The double chevron
>>means that the first task needs to be completed before the next one can start. - The comma
,means that the tasks will run concurrently. - The square brackets
\[and]allow you to define a set of tasks that will run together.
For detailed information about the syntax, refer to the Airflow documentation.
⚠️ Criticality and tasks chaining: If a warning or a transparent task has a child linked critical task, the job is rejected to prevent inconsistent dependencies or criticalities. See "criticality" parameter below for more details.
ℹ️ You can create conditional tasks that can break a dependency chain depending on predefined conditions without raising an error with the task parameter "criticality" set to "break". See "criticality" parameter below or this article for more information.
Example 1
We have the following tasks that we want to run sequentially: taskA (create\_gbq\_table), taskB (sql) and taskC (copy\_gbq\_table).
The task\_dependencies parameter will be as follows: "task\_dependencies": \[" taskA >> taskB >> taskC "],
Example 2
We have the following tasks that we want to run concurrently: taskA, taskB and taskC.
The task\_dependencies parameter will be as follows: "task\_dependencies": \[" taskA, taskB, taskC "],
Example 3
We have the following 9 tasks we want to order: taskA, taskD, taskG (create\_gbq\_table), taskB, taskE, taskH (sql), taskC, taskF, taskI (copy\_gbq\_table).
The task\_dependencies parameter will be as follows:"task\_dependencies": \[" \[taskA, taskD, taskG] >> \[taskB, taskE, taskH] >> \[taskC, taskF, taskI] "],
Example 4
In the example above, we want taskH to run before taskE so we can use its result for taskE.
The task\_dependencies parameter will be as follows:
"task\_dependencies": \[" \[taskA, taskD, taskG] >> taskH >> \[taskB, taskE] >> \[taskC, taskF, taskI] "],
workflow
type: array of maps
mandatory
|List of tasks the data operations will execute.
Check the section below for detailed information on their parameters.
| |default\_gcp\_project\_id
type: string
mandatory
|Default GCP Project ID.
This parameter can be set for each workflow task sub-object, and will be overridden by that value if it is different.
| |default\_bq\_dataset
type: string
mandatory
|Default BigQuery dataset ID.
This parameter can be set for each workflow task sub-object, and will be overridden by that value if it is different.
| |default\_write\_disposition
type: string
mandatory
|Action that occurs if the destination table already exists (see Google BigQuery documentation).
Possible values:
- "WRITE\_TRUNCATE" (default): The run will write table data from the beginning. If the table already contained lines, they will all be deleted and replaced by the new lines. This option is used most of the time for daily runs to avoid duplicates.
- "WRITE\_APPEND": The run will append new lines to the table. When using this option, make sure not to run the data operation several times.
- "WRITE\_EMPTY": This option only allows adding data to an empty table. If the table already contains data, it returns an error. It is hardly ever used as data operations are usually run periodically, so they will always contain data after the first run.
This parameter can be set for each workflow task sub-object, and will be overridden by that value if it is different.
| |direct\_execution
type: boolean
optional
|Tailer's execution engine has been rewritten to switch from Airflow/Composer to a Kubernetes severless architecture, improving its speed, stability, security and scalability.
To use the new execution mode, the direct\_execution parameter must be set to "true".
Default value: true (from January 5, 2021)
| ## :loop: Workflow task parameters A Tables to Tables workflow can include for types of tasks: * **create\_gbq\_table**: This type of task allows you to create the skeleton of a table based on a DDL file. You could compare it to the baking pan of your table. * **sql**: This type of task allows you to fill that baking pan using a SQL query or a SQL script from a file. * **copy\_gbq\_table**: This type of task allows you to duplicate a table named X into another table named Y. * **expectation:** This type of task allows you to perform data quality tests. See [expectations](/data-pipeline-operations/expectations) for more details. For each workflow sub-object, parameters will differ depending on the task type. {% hint style="info" %} Refer to [this page](/data-pipeline-operations/transform-data-with-tables-to-tables/table-to-table-sql-and-ddl-files) to know how to create the DDL and SQL files corresponding to these tasks. {% endhint %} ### **SQL task parameters** | Parameter | Description | | ---------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |task\_type
type: string
mandatory
|The value has to be set to "sql" for this task type.
As "sql" is the default type, this parameter can be omitted for this task type.
| |id
type: string
mandatory
| ID of the task. It must be unique within the data operation. | |short\_description
type: string
optional
| Short description of what the task does. | |doc\_md
type: string
optional
| Path to a file containing a detailed description of the task. The file must be in Markdown format. | |sql\_file
type: string
mandatory
| Path to the file containing the actual SQL query or the SQL script (can be assertions or [expectations](/data-pipeline-operations/expectations)). This file is going to be read and its content uploaded to Firestore upon deployment of the data operation. | |gcp\_project\_id
type: string
optional
|GCP Project ID.
Overrides default\_gcp\_project\_id.
| |bq\_dataset
type: string
optional
|Name of the BigQuery destination dataset.
Overrides default\_bq\_dataset.
| |write\_disposition
type: string
optional
|Action that occurs if the destination table already exists (see Google BigQuery documentation).
Possible values:
- "WRITE\_TRUNCATE" (default): The run will write table data from the beginning. If the table already contained lines, they will all be deleted and replaced by the new lines. This option is used most of the time for daily runs to avoid duplicates.
- "WRITE\_APPEND": The run will append new lines to the table. When using this option, make sure not to run the data operation several times.
- "WRITE\_EMPTY": This option only allows adding data to an empty table. If the table already contains data, it returns an error. It is hardly ever used as data operations are usually run periodically, so they will always contain data after the first run.
Overrides default\_write\_disposition.
| |table\_name
type: string
mandatory if the SQL file contains a query, optional for a SQL script
|Target table used upon SQL query execution.
This parameter is optional if the SQL file contains a script, i.e. one or more statements separated by a ; (for ex. assertions or expectations). Otherwise, this parameter is mandatory.
criticality
type: string
optional
|Task criticality which defines the impact of an error on this task on the data operation global status and behavior. It allows to create tasks dedicated to warning tests (see expectations) that won't break the execution if it fails or conditional tasks that can lead to different tasks depending on predefined conditions (see this article).
Criticality can be:
- critical: if a critical task fails, the run stops and have the global status "failed".
- warning: if a warning task fails, the run proceeds to the next task, executing the following dependent task if any, and the global status of the run will be "warning" (if no critical task fails afterward). The workflow manager regards warning as success, so any job chained by a workflow configuration will be triggered.
- transparent: if a transparent task fails, the run proceeds to the next task, executing the following dependent task if any, and the global status of the run will be "success" (if no critical or warning task fails). Any job chained by a workflow configuration will be triggered.
- break: if a break task fails, the run skips all the dependent tasks and proceeds to the independent tasks if any. The global status of the run will be "success" (if no critical or warning task fails). Any job chained by a workflow configuration will be triggered. You can use this criticality to create conditional tasks that lead to different tasks depending on predefined conditions. See this article for more information.
⚠️ Tasks chaining: We enforce rules at the validation of the configuration (at deployment time) to prevent inconsistent dependencies or criticalities in a chain of tasks. If a warning or a transparent task has a child linked critical task, the job is rejected. Indeed, if you have 3 tasks A: critical >> B: warning >> C: critical, then you have either an inconsistent dependency (B is not necessary for the task C and therefore should be parallelized with C) or an inconsistent criticality (B is necessary for the critical task C and therefore should be critical itself).
Default value: "critical"
| |temporary\_table
type: boolean
optional
|If set to "true", this parameter will force the deletion of the table at the end of the data operation. If the TTT data operation failed before deleting the temporary table, the table will be deleted automatically after 24 hours.
⚠️Please note that if this flag is set to "true", then the target table is preliminarily deleted if it exists. Description, partitionning and clustering are lost.
Default value: false
| |sql\_query\_template
type: string
optional
|If you want to use variables in your SQL query or script, you need to set this parameter to "TEMPLATE\_CURRENT\_DATE" (only supported value). This variable will be set to the execution date of the data operation (and not today's date).
For example, if you want to retrieve data corresponding to the execution date, you can use the following instruction:
WHERE sale\_date = DATE('{{TEMPLATE\_CURRENT\_DATE}}')
task\_type
type: string
mandatory
| In this case, the value has to be set to "create\_gbq\_table". | |id
type: string
mandatory
| ID of the task. It must be unique within the data operation. | |short\_description
type: string
optional
| Short description of what the task does. | |doc\_md
type: string
optional
| Path to a file containing a detailed description of the task. The file must be in Markdown format. | |gcp\_project\_id
type: string
optional
|GCP Project ID.
Overrides default\_gcp\_project\_id.
| |bq\_dataset
type: string
optional
|Name of the BigQuery destination dataset.
Overrides default\_bq\_dataset.
| |bq\_table
type: string
mandatory
| Name of the BigQuery table name. | |ddl\_file
type: string
mandatory
| Path to the JSON file containing DDL information to create the table. | |force\_delete
type: boolean
optional
| If set to "true", this parameter will force the deletion of the table prior to its creation. | |criticality
type: string
optional
|Task criticality which defines the impact of an error on this task on the data operation global status and behavior.
Criticality can be: critical, warning, transparent or break.
See "criticality" description in the SQL task above for more details
Default value: "critical"
| ### **Table copy task parameters** | **Parameter** | Description | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | |task\_type
type: string
mandatory
| In this case, the value has to be set to "copy\_gbq\_table". | |id
type: string
mandatory
| ID of the task. It must be unique within the data operation. | |short\_description
type: string
optional
| Short description of what the task does. | |doc\_md
type: string
optional
| Path to a file containing a detailed description. The file must be in Markdown format. | |source\_gcp\_project\_id
type: string
mandatory
| GCP project ID for the source BigQuery table. | |source\_bq\_dataset
type: string
mandatory
| BigQuery dataset for the source table. | |source\_bq\_table
type: string
mandatory
| Name of the source table. | |gcp\_project\_id
type: string
optional
|GCP project ID for the destination BigQuery table.
Overrides default\_gcp\_project\_id.
| |bq\_dataset
type: string
optional
|Name of the BigQuery destination dataset.
Overrides default\_bq\_dataset.
| |destination\_bq\_table
type: string
mandatory
| Name of the BigQuery destination table. | |destination\_bq\_table\_date\_suffix
type: boolean
mandatory
| Enables/Disables the date suffix to allow date partitioning in BigQuery. | |destination\_bq\_table\_date\_suffix\_format
type: string
mandatory if destination\_bq\_table\_date\_suffix is set to true
|Date format for the table suffix.
As of now, the only possible value is "%Y%m%d".
criticality
type: string
optional
|Task criticality which defines the impact of an error on this task on the data operation global status and behavior.
Criticality can be: critical, warning, transparent or break.
See "criticality" description in the SQL task above for more details
Default value: "critical"
| # Launch configuration and furthermore Learn how to extract, transform and load Google BigQuery data using the Tables to Tables operation. ## ⚙️ How it works When a Tables to Tables Tailer data operation is triggered by an event (for example a Storage to Tables data operation successful run) or scheduled to start: * A number of workflow tasks (SQL queries and JSON table creation/copy tasks) are run in the order set in the **task\_dependencies** parameter of the data operation configuration file. * You obtain one or several BigQuery tables containing the reorganized data. **📋 How to deploy a Tables to Tables data operation** 1. Access your **tailer** folder (created during [installation](/getting-started/install-tailer-sdk)). 2. Create a working folder as you want. 3. Create SQL and JSON files corresponding to your [workflow tasks](/data-pipeline-operations/transform-data-with-tables-to-tables/table-to-table-sql-and-ddl-files). 4. Prepare your JSON configuration file to gather all this information. Refer to this page to learn about all its [parameters](/data-pipeline-operations/transform-data-with-tables-to-tables/tables-to-tables-configuration-file). 5. Determine how to launch your Tables to Tables data operation: either use the **schedule\_interval** parameter in the JSON configuration file, and/or create a [Workflow configuration file](/data-pipeline-operations/orchestrate-processings-with-workflow/workflow-configuration-file) that will define how to trigger it. 6. Access your working folder by running the following command: ``` cd "[path to your working folder]" ``` 7. To deploy the data operation, run the following command: ``` tailer deploy your-file.json ``` 8. Log in to [Tailer Studio](http://studio.tailer.ai) to check the status and details of your data operation. 9. For your workflow to be executed, you either need to run the data operation corresponding to the previous step of your data pipeline (per your Workflow configuration file), or to launch it manually from [Tailer Studio](http://studio.tailer.ai). 10. Access your output table(s) in BigQuery to check the result of the data operation. ## 💡Modify scripts for other use cases The subject taken with the Iowa dataset aggregates all sales from a year at each iteration to account for new sales by the week. Here are some examples to better address a need or use case: * Proceed to a daily aggregation with a daily CRON trigger after receiving the sales file (applicable by the week, month or quarter) * Modify the script to calculate only the current week and avoid calculating old dates * Link the output data table to a datastudio visualization # Overview Learn what data pipeline operations are and the different types available. ## 🔄 What is a data pipeline? A data pipeline is a series of operations executed sequentially or simultaneously, where the output of one operation is the input of the next one:  Tailer Platform data pipeline operations allow you to connect data sources, consolidate and normalize your data, store them, and feed different tools to obtain analytics, reports and dashboards. ## 🔢 Types of data pipeline operations Tailer Platform offers the following types of data pipeline operations: * [Storage to Storage](/data-pipeline-operations/move-files-with-storage-to-storage): Move files from one storage to another * [API to Storage](/data-pipeline-operations/stream-incoming-data-with-api-to-storage): Stream incoming data into a storage * [XML to CSV](/data-pipeline-operations/xml-conversion): Convert complex XML file into a set of CSV files * [Storage to Tables](/data-pipeline-operations/load-data-with-storage-to-tables): Load data into BigQuery tables * [Tables to Tables](/data-pipeline-operations/transform-data-with-tables-to-tables): Transform data within BigQuery tables * [Table to Storage](/data-pipeline-operations/export-data-with-tables-to-storage): Export data from a table to a storage * [Workflow](/data-pipeline-operations/orchestrate-processings-with-workflow): Trigger the execution of your other data operations # Set constants with Context Learn how to set constants with the Context data operation. ## :bulb:What is a Context? Before getting started with the creation of your data pipeline operations, you can first set constants using a Context configuration. Constants are objects which act as placeholders. Their value is stored through a Context configuration, and can be called in all your subsequent data operation configurations. When you deploy a data operation, you are asked the Context you want to use. The constants are then replaced by their value according to the Context. Constants can be used for example to replace GCP credential parameters so they can remain confidential, to manage different GCS bucket names for development and production environnement, etc. ## ✅ Supported data operations All data operations can contain the constants set with Context. {% hint style="warning" %} When you want to apply context to your configurations, you should always make sure than the latest version is specified. For example, add in you configuration`"version": "3"`for a table-to-storage configuration. {% endhint %} ## ⚙️ How it works You set a constant as follows through a Context data operation: ``` "test_integer": { "value": 123456, "type": "integer", "resource": "value", "description": "Some random value" } ``` Once you have deployed your Context configuration, you can start using the constants it contains in your other data operations using the name of the constant wrapped in double curly brackets. For example, to set the max\_active\_run to your "test\_integer": ``` "max_active_runs": {{test_integer}} ``` {% hint style="danger" %} **make sure there is no space between your constant and the brackets** ``` {{test_integer}} -> OK {{ test_integer }} -> KO ``` {% endhint %} When you deploy a configuration file, you will be asked the Context you want to use. The constants are then replaced by the values defined in the Context configuration. You can use the constants in the SQL files of your table-to-table and table-to-storage, for example: ```sql SELECT * FROM {{bq_dataset}}.my_table ``` There are two generic constants you can use in a your data operation configurations without setting them explicitly in your Context configuration: * {{FD\_ACCOUNT}} will take the value of the "account" parameter of your Context configuration * {{FD\_ENV}} will take the value of the "environment" parameter of your Context configuration * {{FD\_CONTEXT}} will take the value of the "context" parameter of your Context configuration When you deploy a configuration using a Context, the ID of the configuration deployed in Tailer is the concatenation of the Context's `account_id`, the Context's `configuration_id`, your data operation's `configuration_id` and your data operation's `environment`. With this concatenation, you don't risk to erase configurations for different contexts. {% hint style="warning" %} Please note that you cannot define the following custom constant: * any constant prefixed with **FD\_** * the protected name : **TEMPLATE\_CURRENT\_DATE** {% endhint %} ## **📋 How to set and use a constant** 1. Access your **tailer** folder (created during [installation](/getting-started/install-tailer-sdk)). 2. Create a working folder as you want, and create a JSON file for your Context data operation inside. 3. Set all the required constants in your JSON configuration file. Refer to this page to learn about all the [parameters](/data-pipeline-operations/set-constants-with-context/context-configuration-file). 4. Access your working folder by running the following command: ``` cd "[path to your working folder]" ``` 5. To deploy the Context data operation, run the following command: ``` tailer deploy configuration your-file.json ``` 6. Log in to [Tailer Studio](http://studio.tailer.ai) to check the status and details of your data operation. 7. You can now use your constants in other data operation configurations files. Dont forget to add `"version" : "2"` for STS, STT, TTT and TTS data operations. If your constant is a string, do not forget to include them wrapped in double quotes, for example: `"gcp_project_id": "{{gcp_dlk_project_id}}"` 8. You can now deploy your configuration file. You will be asked the Context you want to use. If you want to skip this question, you can use the --context flag when you deploy, for example: `tailer deploy configuration your-data-operation.json --context your-context-name` # Context configuration file This is the description of the JSON configuration file for a Context data operation. The configuration file is in JSON format. It contains the following sections: * [Global parameters](#global-parameters): General information about the data operation. * [Constant parameters](#constant-parameters): Information related to the constants to set. ## :eye\_in\_speech\_bubble: Example Here is an example of Context configuration file: ```json { "$schema": "http://jsonschema.tailer.ai/schema/context-veditor", "configuration_type": "context", "configuration_id": "context_dev", "environment": "DEV", "account": "000099", "doc_md": "readme.md", "gcp_project_id": "fd-io-jarvis-demo-dlk", "activated": true, "archived": false, "parameters": { "test_integer": { "value": 123456, "type": "integer", "resource": "value", "description": "Some random value" }, "test_float": { "value": 123456.78910, "type": "float", "resource": "value", "description": "Some random value" }, "test_boolean": { "value": true, "type": "boolean", "resource": "value", "description": "Some random value" }, "gcp_project_id_exc": { "value": "fd-io-jarvis-demo-exc", "type": "string", "resource": "gcp_project_id", "description": "The Default Exchange GCP Project ID" }, "gcs_source_bucket_n_in": { "value": "fd-io-exc-demo-n-in", "type": "string", "resource": "gcs_bucket", "description": "The Default Exchange Source IN Bucket ID" }, "gcp_credentials_secret_source_n_in": { "value": { "cipher_aes": "xxx", "ciphertext": "xxx", "enc_session_key": "xxx", "tag": "xxx" }, "type": "object", "resource": "gcp_credentials_secret", "description": "The GCP Credentials used to load data from the gcp_credentials_secret_source_n_in bucket" }, "gcp_project_id_dlk": { "value": "fd-io-jarvis-demo-dlk", "type": "string", "resource": "gcp_project_id", "description": "The Default Exchange GCP Project ID" }, "gcs_mirror_bucket_n_in": { "value": "mirror-fd-io-demo-n-in", "type": "string", "resource": "gcs_bucket", "description": "The Default Exchange Mirror IN Bucket ID" }, "gcp_credentials_secret_mirror_n_in": { "value": { "cipher_aes": "xxx", "ciphertext": "xxx", "enc_session_key": "xxx", "tag": "xxx" }, "type": "object", "resource": "gcp_credentials_secret", "description": "The GCP Credentials used to store data in the gcp_credentials_secret_mirror_n_in bucket" }, "gcp_credentials_secret_bigquery_dlk": { "value": { "cipher_aes": "xxx", "ciphertext": "xxx", "enc_session_key": "xxx", "tag": "xxx" }, "type": "object", "resource": "gcp_credentials_secret", "description": "The GCP Credentials used to load data in the gcp_project_id_dlk Bigquery" } } } ``` ## :globe\_with\_meridians: Global parameters General information about the configuration. | Parameter | Description | | ------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | |$schema
type: string
optional
configuration\_type
type: string
mandatory
|Type of configuration.
For an Context configuration, the value is always "context".
| |configuration\_id
type: string
mandatory
|ID of the configuration.
You can pick any name you want, but is has to be unique for this configuration type in your account. For this configuration, the account is added as a prefix to your configuration\_id to generate the definitive configuration id that will be displayed in Tailer Studio.
Note that in case of conflict, the newly deployed configuration will overwrite the previous one.
| |environment
type: string
mandatory
|Deployment environment.
Values: PROD, PREPROD, STAGING, DEV.
| |account
type: string
mandatory
| Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator. | |doc\_md
type: string
mandatory
| Path to a file containing a detailed description of the data operation. The file must be in Markdown format. | |gcp\_project\_id
type: string
mandatory
|Main Google Cloud Platform project ID for this configuration.
A same Context can be used for several GCP projects so this gcp project ID is not limitative. If not specified, then you'll be asked to provide it at the deployment.
| |activated
type: boolean
optional
|Flag used to enable/disable the execution of the data operation.
If not specified, the default value will be "true".
| |archived
type: boolean
optional
|Flag used to enable/disable the visibility of the data operation's configuration and runs in Tailer Studio.
If not specified, the default value will be "false".
| ## :symbols: Constant parameters Information related to the constants to set. | Parameter | **Description** | | ---------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------- | |parameters
type: array
mandatory
| Array containing a list of constants, all of them including a name, and the parameters listed below. | |value
type: string
mandatory
| Value that will be used to replace the placeholder in data operation configuration files. | |type
type: string
mandatory
|Type of constant.
Possible values:
- integer
- string
- float
- object
resource
type: string
mandatory
|Type of resource.
Possible values:
- value
- gcp\_project\_id
- gcs\_bucket
- gcp\_credentials\_secret
description
type: string
mandatory
| Description of the constant. | # Move files with Storage to Storage Learn how to transfer files across storages using the Storage to Storage operation. ## :bulb: What is Storage to Storage? A Storage to Storage (STS) data pipeline operation allows you to retrieve files from one source storage, and to run a multiple copy job to one or several destination storages. ## ✅ Supported source and destination storage types * Google Cloud Storage bucket * Amazon S3 bucket * SFTP directory ## ⚙️ How it works Every time a new file matching the specified rule appears in the source directory, it will be: * removed from the source directory, * if options have been set accordingly, copied to an archive directory located in the same storage, inside a folder named as the filename date, * and transferred to one or more output directories (destinations) located in different storages. ## **📋 How to deploy a Storage to Storage data operation** 1. Access your **tailer** folder (created during [installation](/getting-started/install-tailer-sdk)). 2. Create a working folder as you want, and create a JSON file for your data operation inside. 3. Prepare your JSON configuration file. Refer to this page to learn about all the [parameters](/data-pipeline-operations/move-files-with-storage-to-storage/storage-to-storage-configuration-file). 4. Access your working folder by running the following command: ``` cd "[path to your working folder]" ``` 5. To deploy the data operation, run the following command: ``` tailer deploy configuration your-file.json ``` 6. Log in to [Tailer Studio](http://studio.tailer.ai) to check the status and details of your data operation. 7. Add a file with the right template in the source folder 8. Access your output folder(s), and archive folder, if any, to check the result of the data operation. # Storage to Storage configuration file This is the description of the JSON configuration file of a Storage to Storage data operation. The configuration file is in JSON format. It contains the following sections: * Global parameters: General information about the data operation. * Source parameters: One source block, containing information about the data source. * Destination parameters: One or several destination blocks, containing information about the data destinations. ## :eye\_in\_speech\_bubble: Example Here is an example of STS configuration file for a GCS to SFTP transfer: ```json { "$schema": "http://jsonschema.tailer.ai/schema/storage-to-storage-veditor", "configuration_type": "storage-to-storage", "configuration_id": "copy-my-files-gcs-to-sftp", "doc_md": "readme.md", "environment": "PROD", "account": "000099", "version": "3", "activated": true, "archived": false, "filename_templates": [ { "filename_template": "{{FD_DATE}}_sales_file.txt", "file_description": "This is a description for sales_file.txt." }, { "filename_template": "{{FD_DATE}}_products.txt", "file_description": "This is a description for proucts.txt." } ], "source": { "type": "gcs", "gcp_project_id": "my_gcp_project", "gcs_source_bucket" : "my-input-bucket", "gcs_source_prefix" : "input-folder", "archive_prefix": "archive-folder", "gcp_credentials_secret": { "cipher_aes": "b42724dcbbf0aba89a0f106d1c4", "tag": "5c8816ea0a7aded7c6f2df61f5b9", "ciphertext": "fd096e", "enc_session_key": "8f6f7c" } }, "destinations": [ { "type": "sftp", "generate_top_file": "REPLACE_EXTENSION", "sftp_host": "sftp.domain.com", "sftp_port": 22, "sftp_userid": "john_doe", "sftp_password_secret": { "cipher_aes": "3926f71cd00d10b07d0fee4e", "tag": "1f5c066351d91041343a2ab37aebe", "ciphertext": "921776fd04228fe8aaa42af04", "enc_session_key": "2fb0ad2b0df9771" }, "sftp_destination_dir": "/", "sftp_destination_dir_create": false } ] } ``` ## :globe\_with\_meridians: Global parameters | Parameter | Description | | ------------------------------------------------------------------------------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |$schema
type: string
optional
configuration\_type
type: string
mandatory
|Type of data operation.
For an STS data operation, the value is always "storage-to-storage".
| |configuration\_id
type: string
mandatory
|ID of the data operation.
You can pick any name you want, but is has to be unique for this data operation type.
Note that in case of conflict, the newly deployed data operation will overwrite the previous one. To guarantee its uniqueness, the best practice is to name your data operation by concatenating:
- your account ID,
- the source bucket name,
- and the source directory name.
environment
type: string
mandatory
|Deployment context.
Values: PROD, PREPROD, STAGING, DEV.
| |account
type: string
mandatory
| Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator. | |version
type: string
optional
| Version of the configuration in order to use new features. | |filename\_templates
type: string
mandatory
|List of filename templates that will be processed.
You can set the value to "\*" for all files to be copied. However, this is not recommended, as unnecessary or sensitive files might be included by mistake. Besides, the date value specified in filename\_template will be used to sort files in the archive folder. If no date value is specified, all files will be stored together under one folder named /ALL.
The best practice is to specify one or more filename templates with the filename\_template and file\_description parameters as described in the next paragraphe.
| |activated
type: boolean
optional
|Flag used to enable/disable the execution of the data operation.
If not specified, the default value will be "true".
| |archived
type: boolean
optional
|Flag used to enable/disable the visibility of the data operation's configuration and runs in Tailer Studio.
If not specified, the default value will be "false".
| |max\_active\_runs
type: integer
optional
|This parameter limits the number of concurrent runs for this data operation.
If not set, the default value is 5.
| |empty\_file\_policy
type: string
optional
|This parameter will tell Tailer how to behave when an empty file (0 bytes) is read.
- "NONE" the file is ignored and left in place.
- "PROCESS" the file will be processed normally.
- "TRIGGER\_FAILED\_STATUS" a Tailer run will be traced and set to FAILED.
- "TRIGGER\_SUCCESS\_STATUS" a Tailer run will be traced and set to SUCCESS.
The default value is: "NONE"
| |short\_description
type: string
optional
| Short description of the Data Operation | |doc\_md
type: string
optional
| Path to a file containing a detailed description. The file must be in Markdown format. | ### **"Filename Templates" sub-object parameters** The "**filename\_templates**" object contains the definition of expected source files to copy to the destinations. | Parameter | Description | | ----------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |filename\_template
type: string
mandatory
|Template for the files to be processed.
The following placeholders are currently supported:
- "FD\_DATE" looks for an 8-digit date (e.g. "20191015").
- "FD\_DATE\_YEAR\_4" looks for 4-digit year (e.g "2021").
- "FD\_DATE\_YEAR\_2" looks for 2-digit year (e.g "21").
- "FD\_DATE\_MONTH" looks for 2-digit month (e.g "05").
- "FD\_DATE\_DAY" looks for 2-digit day (e.g "12").
- "FD\_TIME" looks for a 6-digit time (e.g. "124213").
- "FD\_BLOB\_XYZ", where XYZ is a non-zero positive integer, looks for a string of characters of XYZ length.
Information:
- if "FD\_DATE" is specified, it will have priority upon "FD\_DATE\_YEAR\_X".
- if "FD\_DATE\_YEAR\_4" or "FD\_DATE\_YEAR\_2" is specified, the final date will be concatenated with "FD\_DATE\_MONTH" and "FD\_DATE\_DAY".
- if "FD\_DATE\_YEAR\_2" is specified, it will be prefixed by "20".
- if "FD\_DATE\_YEAR\_4" or "FD\_DATE\_YEAR\_2" is specified only "FD\_DATE\_MONTH" and "FD\_DATE\_DAY" will be set to "01".
Example 1
This template:
"stores\_{{FD\_DATE}}{{FD\_TIME}}.txt"
will allow you to process this type of files:
"stores\_20201116\_124213.txt"
Example 2
This template:
"{{FD\_DATE}}{{FD\_BLOB\_5}}fixedvalue\_{{FD\_BLOB\_11}}.gz"
will allow you to process this type of files:
"20201116\_12397\_fixedvalue\_12312378934.gz"
| |file\_description
type: string
optional
| Short description of the files that will match the filename template. | ## :arrow\_down: Source parameters There can only be one source block, as STS data operations can only process one source at a time. ### **Google Cloud Storage source** Example: ```json { "source": { "type": "gcs", "gcp_project_id": "my_gcp_project", "gcs_source_bucket" : "my_bucket", "gcs_source_prefix" : "INPUT_SOMEDIR", "archive_prefix": "archive", "gcp_credentials_secret": { "cipher_aes": "b42724dcbbf6c3310aba89a0f106d1c4", "tag": "5c8816ea0a7aded9cb47c6f2df61f5b9", "ciphertext": "fdf09c6e", "enc_session_key": "8f63f7c" } } } ``` | Parameter | **Description** | | --------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |type
type: string
mandatory
|Type of source.
In this case : "gcs".
| |gcp\_project\_id
type: string
mandatory
|Set the project where deploy the source configuration and associated cloud functions
If not set, the user will be prompted to choose a profile where deploy the configuration
| |gcs\_source\_bucket
type: string
mandatory
| Name of the source bucket. | |gcs\_source\_prefix
type: string
mandatory
| Path where the files will be found, e.g. "some/sub/dir". | |archive\_prefix
type: string
optional
|Path where the source files will be archived.
If present and populated, the STS data operation will archive the source files in the location specified, in the GCS source bucket.
If not present or empty, there will be no archiving.
| |gcp\_credentials\_secret
type: dict
mandatory
|Encrypted credentials needed to read/move data from the source bucket.
You should have generated credentials when setting up GCP. To learn how to encrypt them, refer to this page.
| ### **Amazon S3 source** Example: ```json { "source": { "type": "s3", "s3_source_bucket": "my_s3_bucket", "s3_source_prefix": "input/my_source/", "archive_prefix": "archive" "aws_access_key": "3VJ3F6JJQBA2", "aws_access_key_secret": { "cipher_aes": "e6f5a68d4de8af89e83ea93e42facbed", "tag": "20e174e34c5d0c537be77d85ed8dda33", "ciphertext": "60a98b884110aab84", "enc_session_key": "9c4648e" } } } ``` | Parameter | **Description** | | --------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |type
type: string
mandatory
|Type of source.
In this case : "s3".
| |s3\_source\_bucket
type: string
mandatory
| Name of the source S3 bucket. | |s3\_source\_prefix
type: string
mandatory
| Path where the files will be found, e.g. "some/sub/dir". | |archive\_prefix
type: string
optional
|Path where the source files will be archived.
If present and populated, the STS data operation will archive the source files in the location specified, in the GCS source bucket.
If not present or empty, there will be no archiving.
| |aws\_access\_key
type: string
mandatory
| Amazon S3 access key ID. | |aws\_access\_key\_secret
type: dict
mandatory
|Encrypted Amazon S3 access private key.
This is needed to read/move data from the source bucket. To learn how to encrypt the private key value, refer to this page.
| ### **Azure source** Example: ```json { "source": { "type": "azure", "azure_source_storage": "my_azure_storage", "azure_source_prefix": "input/my_source/", "archive_prefix": "archive", "azure_connection_string_secret": { "cipher_aes": "f1c4xxxx", "tag": "0052fxxxx", "ciphertext": "7e1a3xxxx", "enc_session_key": "2dc2xxxx" } } } ``` | Parameter | **Description** | | ------------------------------------------------------------------------------------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |type
type: string
mandatory
|Type of source.
In this case : "azure"
| |azure\_source\_storage
type: string
mandatory
| Name of the source Azure storage. | |azure\_source\_prefix
type: string
mandatory
| Path where the files will be found, e.g. "some/sub/dir". | |archive\_prefix
type: string
optional
|Path where the source files will be archived.
If present and populated, the STS data operation will archive the source files in the location specified, in the GCS source bucket.
If not present or empty, there will be no archiving.
| |azure\_connection\_string\_secret
type: dict
mandatory
|Encrypted Azure access private key.
This is needed to read/move data from the source bucket. To learn how to encrypt the private key value, refer to this page.
| ### **SFTP source** Example: ```json { "source": { "type": "sftp", "sftp_source_directory": "/", "sftp_source_filename": "20190621_test_file.txt", "archive_prefix": "archive", "sftp_host": "sftp.domain.com", "sftp_port": 22, "sftp_userid": "john_doe", "sftp_authentication_method": "USERNAME_PASSWORD", "sftp_password_secret": { "cipher_aes": "3926f71cd00f8d2b812d10b07d0fee4e", "tag": "1f5c066351db5f91041343a2ab37aebe", "ciphertext": "921776fd0caa71a04228fe8aaa42af04", "enc_session_key": "2fb2f8d271" } } } ``` | **Parameter** | Description | | ------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |type
type: string
mandatory
|Type of source.
In this case : "sftp".
| |sftp\_source\_directory
type: string
mandatory
| Sub-path to switch to before downloading the file. | |sftp\_source\_filename
type: string
mandatory
| File to retrieve. | |archive\_prefix
type: string
optional
|Path where the source files will be archived.
If present and populated, the STS data operation will archive the source files in the location specified, in the GCS source bucket.
If not present or empty, there will be no archiving.
| |sftp\_host
type: string
mandatory
| SFTP host, e.g. "sftp.something.com". | |sftp\_port
type: integer
mandatory
| SFTP port, e.g. "22". | |sftp\_userid
type: string
mandatory
| SFTP user ID, e.g. "john\_doe". | |sftp\_authentication\_method
type: string
optional
|Authentication method used to connect to the SFTP server.
The following methods are supported:
- USERNAME\_PASSWORD
- PRIVATE\_KEY
Default : USERNAME\_PASSWORD
| |sftp\_password\_secret
type: dict
optional
|Encrypted SFTP password for the user ID.
This is needed to read/move data from the source SFTP.
This attribute MUST be set if sftp\_authentication\_method is set to USERNAME\_PASSWORD
To learn how to encrypt the password, refer to this page.
| |sftp\_private\_key\_secret
type: dict
optional
|Encrypted SFTP private key.
This attribute MUST be set if sftp\_authentication\_method is set to PRIVATE\_KEY.
To learn how to encrypt the password, refer to this page.
| |sftp\_private\_key\_passphrase\_secret
type: dict
optional
|Encrypted SFTP private key passphrase if provided
This attribute MUST be set if sftp\_authentication\_method is set to PRIVATE\_KEY.
To learn how to encrypt the password, refer to this page.
| ## :arrow\_up: Destination parameters These parameters allow you specify a list of destinations. You can add as many "destination" sub-objects as you want, they will all be processed. ### **Google Cloud Storage destination** Example: ```json { "destinations": [ { "type": "gcs", "gcs_destination_bucket": "my_dest_bucket", "gcs_destination_prefix": "DEV/output", "gcp_credentials_secret": { "cipher_aes": "b42724dcbbf6c3310aba89a0f106d1c4", "tag": "5c8816ea0a7aded9cb47c6f2df61f5b9", "ciphertext": "fdf09c6e", "enc_session_key": "8f634f7f7c" } } ] } ``` | Parameter | Description | | ----------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |type
type: string
mandatory
|Type of destination.
In this case : "gcs".
| |gcs\_destination\_bucket
type: string
mandatory
| Google Cloud Storage destination bucket. | |gcs\_destination\_prefix
type: string
mandatory
| Google Cloud Storage destination path, e.g. "/subdir/subdir\_2" to send the files to "gs\://BUCKET/subdir/subdir\_2/source\_file.ext" | |gcp\_credentials\_secret
type: dict
mandatory
|Encrypted credentials needed to read/write/move data from the destination bucket.
You should have generated credentials when setting up GCP. To learn how to encrypt them, refer to this page.
| ### **Amazon S3 destination** Example: ```json { "destinations": [ { "type": "s3", "s3_bucket" : "my_dest_bucket", "s3_destination_prefix": "PROD/output", "aws_access_key": "J3F6JLUVJQ", "aws_access_key_secret": { "cipher_aes": "e6f5a68dxxxx", "tag": "20e1xxxx", "ciphertext": "60a84xxxx", "enc_session_key": "9c4619048exxxx" } } ] } ``` | Parameter | Description | | ---------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |type
type: string
mandatory
|Type of destination.
In this case : "s3".
| |s3\_bucket
type: string
mandatory
| Amazon S3 bucket name. | |s3\_destination\_prefix
type: string
mandatory
| Amazon S3 destination path, e.g. "subdir\_A/subdir\_B" to send the files to "s3://bucket/subdir\_A/subdir\_B/source\_file.ext". | |aws\_access\_key
type: string
mandatory
| Amazon S3 access key ID. | |aws\_access\_key\_secret
type: dict
mandatory
|Encrypted Amazon S3 access private key.
This is needed to read/write/move data from the destination bucket.
To learn how to encrypt the private key value, refer to this page.
| ### Azure destination Example: ```json { "destinations": [ { "type": "azure", "azure_destination_prefix": "my_azure_bucket/output", "azure_connection_string_secret": { "cipher_aes": "3926fxxxx", "tag": "1f5cxxxx", "ciphertext": "9217xxxx", "enc_session_key": "2fb0xxxx" } } ] } ``` | Parameter | Description | | ------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | |type
type: string
mandatory
|Type of destination.
In this case : "azure".
| |azure\_destination\_prefix
type: string
mandatory
| Complete Azure destination path, i.e. storage name and subdirectory if needed. | |azure\_connection\_string\_secret
type: dict
mandatory
|Encrypted Azure access private key.
This is needed to read/move data from the source bucket. To learn how to encrypt the private key value, refer to this page.
| ### **SFTP destination** Example: ```json { "destinations": [ { "type": "sftp", "generate_top_file": "REPLACE_EXTENSION", "sftp_destination_dir": "/", "sftp_destination_dir_create": false, "sftp_host": "sftp.domain.com", "sftp_port": 22, "sftp_userid": "john_doe", "sftp_password_secret": { "cipher_aes": "3926f71cd00f8d2b812d10b07d0fee4e", "tag": "1f5c066351db5f91041343a2ab37aebe", "ciphertext": "921776fd0caa71a04228fe8aaa42af04", "enc_session_key": "2fb0adf8d271" } } ] } ``` | Parameter | Description | | ------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |type
type: string
mandatory
|Type of destination.
In this case : "sftp".
| |generate\_top\_file
type: string
optional
|This flag, if set, will generate a TOP file along with the file copied.
Possible values are:
- "REPLACE\_EXTENSION": if the source file is "20190708\_data.txt", the TOP file will be "20190708\_data.top".
- "ADD\_EXTENSION": if the source file is "20190708\_data.txt", the TOP file will be "20190708\_data.txt.top".
sftp\_destination\_dir
type: string
mandatory
| Path to switch to before uploading the file. | |sftp\_destination\_dir\_create
type: string
mandatory
| Will try to create the subdir specified in **sftp\_destination\_dir** on the SFTP filesystem before switching to it and copying files. | |sftp\_host
type: string
mandatory
| SFTP host, e.g. "sftp.something.com". | |sftp\_port
type: string
mandatory
| SFTP port, e.g. "22". | |sftp\_userid
type: string
mandatory
| SFTP user ID, e.g. "john\_doe". | |sftp\_authentication\_method
type: string
optional
|Authentication method used to connect to the SFTP server.
The following methods are supported:
- USERNAME\_PASSWORD
- PRIVATE\_KEY
Default : USERNAME\_PASSWORD
| |sftp\_password\_secret
type: dict
optional
|Encrypted SFTP password for the user ID.
This is needed to read/move data from the source SFTP.
This attribute MUST be set if sftp\_authentication\_method is set to USERNAME\_PASSWORD.
To learn how to encrypt the password, refer to this page.
| |sftp\_private\_key\_secret
type: dict
optional
|Encrypted SFTP private key.
This attribute MUST be set if sftp\_authentication\_method is set to PRIVATE\_KEY.
To learn how to encrypt the password, refer to this page.
| |sftp\_private\_key\_passphrase\_secret
type: dict
optional
|Encrypted SFTP private key passphrase if provided.
This attribute MUST be set if sftp\_authentication\_method is set to PRIVATE\_KEY.
To learn how to encrypt the password, refer to this page.
| # Load data with Storage to Tables Learn how to transfer data from files to database tables using the Storage to Tables operation. ## :bulb: What is Storage to Tables? A Storage to Tables (STT) data pipeline operation allows you to load data files from a Google Cloud Storage (GCS) bucket into one or several BigQuery databases. {% hint style="warning" %} Note that the uniqueness of the configuration is checked against the GCS bucket name AND directory combination. This means that you can have only **one configuration per bucket/directory combination**, as any new configuration will overwrite the previous one. {% endhint %} ## ✅ Supported file types ### **Source data files** * CSV and any delimited flat files * New line delimited JSON files * These two file types can be compressed using gzip ### **Databases** * Google BigQuery ## ⚙️ How it works Every time a new file matching the specified rule appears in a given directory of a Google Cloud Storage bucket: * it will be removed from the source directory, * if options have been set accordingly, the file will be copied to an archive directory located in the same storage, inside a folder named with the date contained in the filename, * the file data will be loaded into a BigQuery table matching its filename template for each database specified. ## 🤖 Automated metadata Automatic metadata feature will add specific columns during the ingestion process related to the inpput source. The added columns are: ``` tlr_ingestion_timestamp_utc (TIMESTAMP) tlr_input_file_source_type (STRING) tlr_input_file_name (STRING) tlr_input_file_full_resource_name (STRING) ```
$schema
type: string
optional
| The url of the json-schema that contains the properties that your configuration must verify. Most Code Editor can use that to validate your configuration, display help boxes and enlighten issues. | |configuration\_type
type: string
mandatory
|Type of data operation.
For an STT data operation, the value is always "storage-to-tables".
configuration\_id
type: string
mandatory
|ID of the data operation.
You can pick any name you want, but is has to be unique for this data operation type.
Note that in case of conflict, the newly deployed data operation will overwrite the previous one. To guarantee its uniqueness, the best practice is to name your data operation by concatenating:
- your account ID,
- the source bucket name,
- and the source directory name.
version
type: string
mandatory
environment
type: string
mandatory
|Deployment context.
Values: PROD, PREPROD, STAGING, DEV.
account
type: string
mandatory
| Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator. | |activated
type: boolean
optional
|Flag used to enable/disable the execution of the data operation.
If not specified, the default value will be "true".
archived
type: boolean
optional
|Flag used to enable/disable the visibility of the data operation's configuration and runs in Tailer Studio.
If not specified, the default value will be "false".
max\_active\_runs
type: integer
optional
|This parameter limits the number of concurrent runs for this data operation.
If not set, the default value is 50.
| |short\_description
type: string
optional
| Short description of the context of the configuration. | |doc\_md
type: string
optional
| Path to a file containing a detailed description. The file must be in Markdown format. | ## :arrow\_down: Source parameters (GCS) The destination section contains all information related to the data source provider. ```json "source": { "type": "gcs", "gcp_project_id": "my-project", "gcs_source_bucket": "mirror-fd-io-exc-demo-wbd--n-in", "gcs_source_prefix": "testjul", "gcs_archive_prefix": "archive", "gcp_credentials_secret": { "cipher_aes": "223xxx", "tag": "8ddxxx", "ciphertext": "4c7xxx", "enc_session_key": "830xxx" } } ``` | Parameter | Description | | --------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |type
type: string
mandatory
|Source type.
The only supported source type for now is "gcs".
| |gcp\_project\_id
type: string
mandatory
|Specify the Google Cloud Platform project where to deploy the data operation and its associated cloud functions.
If not set, the user will be prompted to choose a project.
| |gcs\_source\_bucket
type: string
mandatory
|Name of the source bucket.
⚠️ See restrictions below
gcs\_source\_prefix
type: string
mandatory
|Path where the files will be found, e.g. "some/sub/dir".
⚠️ See restrictions below
gcs\_archive\_prefix
type: string optional
|Path where the source files will be archived.
If present and populated, the STT data operation will archive the source files in the location specified, in the GCS source bucket.
If not present or empty, there will be no archiving.
| |gcp\_credentials\_secret
type: dict
mandatory
|Encrypted credentials needed to read/move data from the source bucket.
You should have generated credentials when setting up GCP. To learn how to encrypt them, refer to this page.
| ## :arrow\_up: Destination parameters (BigQuery) The destination section contains all the information related to the data destinations. The **destinations** parameter is an array containing maps. Each map can contain a type of destination and many actual "tables" as ultimate destination. Example: ```json "destinations": [ { "type": "bigquery", "gcp_project_id": "my-project", "gbq_dataset": "dlk_demo_wbd_psa", "source_format": "CSV", "create_disposition": "CREATE_IF_NEEDED", "write_disposition": "WRITE_TRUNCATE", "skip_leading_rows": 1, "field_delimiter": "|", "add_tailer_metadata": true, "gcp_credentials_secret": { "cipher_aes": "223008256918c292ff3ec1axxx", "tag": "8dd3db4c71bfb963d475d1bbd1xxx", "ciphertext": "4c74df268d5a7541f8264c2e7a282fxxx", "enc_session_key": "830606cbc4f9401a29c7977d364398xxx" }, "tables": [ { "table_name": "sales_details_test", "short_description": "Daily detailed Sales with tickets and tickets lines", "filename_template": "input_{{FD_DATE}}-{{FD_TIME}}-ORS-ventes.csv", "ddl_mode": "file", "ddl_file": "ddl/sales_details.json", "doc_md": "ddl/sales_details.md", "add_tailer_metadata": true, "skip_leading_rows": 0, "write_disposition": "WRITE_APPEND" }, { "table_name": "stores", "short_description": "Full Stores referential", "filename_template": "input_{{FD_DATE}}-{{FD_TIME}}-ORS-magasins.csv", "ddl_mode": "file", "ddl_file": "ddl/stores.json", "doc_md": "ddl/stores.md", "add_tailer_metadata": false } ] } ] ``` ### Global destination parameters | Parameter | Description | | ------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |type
type: string
mandatory
|Type of destination.
The only supported destination type for now is "bigquery".
| |gcp\_project\_id
type: string
optional
|Default GCP Project ID.
This parameter can be set for each table sub-object, and will be overridden by that value if it is different.
| |gbq\_dataset
type: string
optional
|Default BigQuery Dataset.
This parameter can be set for each table sub-object, and will be overridden by that value if it is different.
| |gcp\_credentials\_secret
type: object
optional
|Encrypted credentials needed to interact with Storage and BigQuery.
You should have generated credentials when setting up GCP. To learn how to encrypt them, refer to this page.
| |source\_format
type: string
optional
|Default source format for input files.
Possible values (case sensitive):
- "CSV" (default)
- "JSON"
- "PARQUET" (see BigQuery doc for more info)
- "AVRO" (see BigQuery doc for more info)
This parameter can be set for each table sub-object, and will be overridden by that value if it is different.
| |create\_disposition
type: string
optional
|Specifies behavior for creating tables (see Google BigQuery documentation).
Possible values:
- "CREATE\_IF\_NEEDED" (default): If the table does not exist, BigQuery creates the table.
- "CREATE\_NEVER": The table must already exist.
This parameter can be set for each table sub-object, and will be overridden by that value if it is different.
| |write\_disposition
type: string
optional
|Action that occurs if the destination table already exists (see Google BigQuery documentation).
Possible values:
- "WRITE\_TRUNCATE" (default): The run will write table data from the beginning. If the table already contained lines, they will all be deleted and replaced by the new lines. This option is used most of the time for daily runs to avoid duplicates.
- "WRITE\_APPEND": The run will append new lines to the table. When using this option, make sure not to run the data operation several times.
- "WRITE\_EMPTY": This option only allows adding data to an empty table. If the table already contains data, it returns an error. It is hardly ever used as data operations are usually run periodically, so they will always contain data after the first run.
This parameter can be set for each table sub-object, and will be overridden by that value if it is different.
skip\_leading\_rows
type: integer
optional
|Number of rows to skip when reading data, CSV only.
This parameter can be set for each table sub-object, and will be overridden by that value if it is different.
Default value: 1
field\_delimiter
type: string
optional
|Separator for fields in a CSV file, e.g. ";".
Note: For Tab separator, set to "\t".
This parameter can be set for each table sub-object, and will be overridden by that value if it is different.
Default value:
quote\_character
type: string
optional
|Character used to quote data sections, CSV only (see Google BigQuery documentation).
Note: For quote and double quotes, set to "'" and """ respectively.
This parameter can be set for each table sub-object, and will be overridden by that value if it is different.
Default value: ""
null\_marker
type: string
optional
|Represents a null value, CSV only (see Google BigQuery documentation).
This parameter can be set for each table sub-object, and will be overridden by that value if it is different.
Default value: ""
bq\_load\_job\_ignore\_unknown\_values
type: boolean
optional
|Ignore extra values not represented in the table schema (see Google BigQuery documentation).
This parameter can be set for each table sub-object, and will be overridden by that value if it is different.
Default value: false
bq\_load\_job\_max\_bad\_records
type: integer
optional
|Number of invalid rows to ignore (see Google BigQuery documentation).
This parameter can be set for each table sub-object, and will be overridden by that value if it is different.
Default value: 0
bq\_load\_job\_schema\_update\_options
type: array
optional
|Specifies updates to the destination table schema to allow as a side effect of the load job (see Google BigQuery documentation).
This parameter can be set for each table sub-object, and will be overridden by that value if it is different.
Default value: \[]
bq\_load\_job\_allow\_quoted\_newlines
type: boolean
optional
|Allows quoted data containing newline characters, CSV only (see Google BigQuery documentation).
This parameter can be set for each table sub-object, and will be overridden by that value if it is different.
Default value: false
bq\_load\_job\_allow\_jagged\_rows
type : boolean
optional
|Allows missing trailing optional columns, CSV only (see Google BigQuery documentation).
This parameter can be set for each table sub-object, and will be overridden by that value if it is different.
Default value: false
add\_tailer\_metadata
type : boolean
optional
|\[NEW]
Allows automatic metadata feature that add specific columns during the ingestion process related to the input source.
The added columns are:tlr\_ingestion\_timestamp\_utc(TIMESTAMP) tlr\_input\_file\_source\_type(STRING)tlr\_input\_file\_name(STRING) tlr\_input\_file\_full\_resource\_name(STRING)
This parameter can be set for each table sub-object, and will be overridden by that value if it is different.
Default value : false
table\_name
type: string
mandatory
| Name of the destination BigQuery table. | |short\_description
type: string
optional
| Short description of the destination BigQuery table. | |filename\_template
type: string
mandatory
|Template for the files to be processed.
The following placeholders are currently supported:
- "FD\_DATE" looks for an 8-digit date (e.g. "20191015").
- "FD\_DATE\_YEAR\_4" looks for 4-digit year (e.g "2021").
- "FD\_DATE\_YEAR\_2" looks for 2-digit year (e.g "21").
- "FD\_DATE\_MONTH" looks for 2-digit month (e.g "05").
- "FD\_DATE\_DAY" looks for 2-digit day (e.g "12").
- "FD\_TIME" looks for a 6-digit time (e.g. "124213").
- "FD\_BLOB\_n", where "n" is a non-zero positive integer, looks for a string of characters of "n" length.
- FD\_TABLE\_NAME: This is a special template used when you have to process a large number of different files sharing the same destination schema. This template has to be used in conjunction with the table\_name parameter.
Information:
- if "FD\_DATE" is specified, it will have priority upon "FD\_DATE\_YEAR\_X".
- if "FD\_DATE\_YEAR\_4" or "FD\_DATE\_YEAR\_2" is specified, the final date will be concatenated with "FD\_DATE\_MONTH" and "FD\_DATE\_DAY".
- if "FD\_DATE\_YEAR\_2" is specified, it will be prefixed by "20".
- if "FD\_DATE\_YEAR\_4" or "FD\_DATE\_YEAR\_2" is specified only "FD\_DATE\_MONTH" and "FD\_DATE\_DAY" will be set to "01".
Example 1
This template:
"stores\_{{FD\_DATE}}{{FD\_TIME}}.txt"
will allow you to process this type of files:
"stores\_20201116\_124213.txt"
Example 2
This template:
"{{FD\_DATE}}{{FD\_BLOB\_5}}fixedvalue{{FD\_BLOB\_11}}.gz"
will allow you to process this type of files:
"20201116\_12397\_fixedvalue\_12312378934.gz"
Example 3
If table\_name is set to: "table\_{{FD\_TABLE\_NAME}}"
and filename\_template to: "{{FD\_DATE}}\_{{FD\_TIME}}fixedvalue{{FD\_TABLE\_NAME}}.csv"
A file named "20201116\_124523\_fixedvalue\_stores.csv" will be loaded into a table named: "table\_stores\_20191205"
A file named "20190212\_063412\_fixedvalue\_visits.csv" will be loaded into a table named: "table\_visits\_20190212"
| |ddl\_mode
type: string
optional
|This parameter allows you to specify how the schema of the table will be obtained.
Possible values:
- "file": Legacy mode. The table schema is described in the DDL file specified in the ddl\_file parameter.
- " file\_template": The table schema is described in a DDL file provided in the source directory together with the source file. It must have the same filename as the source file, with the ".ddl.json" suffix.
- "header" (CSV file only): The columns of the CSV file first line are automatically used as columns for the database table. All the columns are given the STRING type. No DDL file needs to be provided.
- "autodetect" (not recommended): Google’s default mode. The schema is automatically detected from the source file. This mode doesn’t work well with CSV files, but gives good results with structured formats such as JSON. (see Google BigQuery documentation).
Default value: file
| |ddl\_file
type: string
mandatory if ddl\_mode is set to "file"
| Path to the [DDL file](/data-pipeline-operations/load-data-with-storage-to-tables/storage-to-tables-ddl-files) where the destination schema is described. | |doc\_md
type: string
optional
add\_tailer\_metadata
type : boolean
optional
|\[NEW]
Allows automatic metadata feature that add specific columns during the ingestion process related to the input source.
The added columns are:tlr\_ingestion\_timestamp\_utc(TIMESTAMP) tlr\_input\_file\_source\_type(STRING)tlr\_input\_file\_name(STRING) tlr\_input\_file\_full\_resource\_name(STRING)
Default value : false
schema
type: array
mandatory
|GBQ table schema. It contains a list of fields corresponding to the number of columns found in the CSV.
Each field described has three attributes:
- name: Name of the field. Keep column names as in the input file.
- type: Use only the STRING type at this stage to avoid cast errors. The type can modified with a transformation operation later on.
- description: Text describing the meaning of the data.
$schema
type: string
optional
| The url of the json-schema that contains the properties that your configuration must verify. Most Code Editor can use that to validate your configuration, display help boxes and enlighten issues. | |configuration\_type
type: string
mandatory
|Type of data operation.
For an ATS data operation, the value is always "api-to-storage".
configuration\_id
type: string
mandatory
|ID of the data operation.
You can pick any name you want, but is has to be unique for this data operation type.
Note that in case of conflict, the newly deployed data operation will overwrite the previous one. To guarantee its uniqueness, the best practice is to name your data operation by concatenating:
- your account ID,
- the source bucket name,
- and the source directory name.
environment
type: string
Mandatory
|Deployment context.
Values: PROD, PREPROD, STAGING, DEV.
account
type: string
mandatory
| Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator. | |activated
type: boolean
optional
|Flag used to enable/disable the execution of the data operation.
If not specified, the default value will be "true".
archived
type: boolean
optional
|Flag used to enable/disable the visibility of the data operation's configuration and runs in Tailer Studio.
If not specified, the default value will be "false".
max\_active\_runs
type: integer
optional
|This parameter limits the number of concurrent runs for this data operation.
If not set, the default value is 1.
| |short\_description
type: string
optional
| Short description of the context of the configuration. | |doc\_md
type: string
optional
| Path to a file containing a detailed description. The file must be in Markdown format. | ## Source parameters (Pub/Sub) The destination section contains all information related to the data source provider. ``` "source": { "type": "pubsub", "gcp_project_id": "fd-io-jarvis-demo-dlk", "pubsub_topic_suffix": "example-products", "protocol_buffers_file": "000099-ats-example-products.proto" } ``` | Parameter | Description | | --------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |type
type: string
mandatory
|Source type.
The only supported source type for now is "pubsub".
| |gcp\_project\_id
type: string
mandatory
|Specify the Google Cloud Platform project where to deploy the data operation and its associated cloud functions.
If not set, the user will be prompted to choose a project.
| |pubsub\_topic\_suffix
type: string
mandatory
| Name of the Pub/Sub topic that will be created. | |protocol\_buffers\_file
type: string
optional
|Filename pointing to a Protocol Buffers 2 file.
If specified, all incoming data streamed through the topic will be checked against the definition included in the Protocol Buffers file.
| ### Protocol Buffers File syntax Pub/Sub allow to verify an incoming payload using a Protocol Buffers definition. Protocol Buffers (proto2) langage guide :type
type: string
mandatory
|Type of destination.
In this case : "gcs".
| |gcs\_destination\_bucket
type: string
mandatory
| Google Cloud Storage destination bucket. | |gcs\_destination\_prefix
type: string
mandatory
| Google Cloud Storage destination path, e.g. "/subdir/subdir\_2" to send the files to "gs\://BUCKET/subdir/subdir\_2/source\_file.ext" | |gcp\_credentials\_secret
type: dict
mandatory
|Encrypted credentials needed to read/write/move data from the destination bucket.
You should have generated credentials when setting up GCP. To learn how to encrypt them, refer to this page.
| |gcs\_filename\_template
type: dict
mandatory
| Filename template that will be used to write incoming data to a GCS storage. | |description
type: dict
optional
| Short description of the destination. | # API To Storage usage examples ## Pub/Sub to GCS examples ### Pub/Sub endpoint Once the ATS configuration file has been deployed, a Pub/Sub endpoint will be available and ready to receive data. The Pub/Sub URL will be constructed as follow: `https://pubsub.googleapis.com/v1/projects/[GCP_PROJECT_ID]/topics/ats-topic-[CONFIGURATION_ID]:publish` Considering the following value coming from your ATS configuration : * gcp\_project\_id (from "source" attribute: fd-io-jarvis-demo-dlk * configuration\_id : 000099-ats-example-products The resulting Pub/Sub endpoint will be: `https://pubsub.googleapis.com/v1/projectsfd-io-jarvis-demo-dlk/topics/ats-topic-000099-ats-example-products:publish` ### How to build a data payload Pub/Sub expects a Base64 encoded JSON string. API To Storage processor expects an array of objects build with the following schema: ``` { "input_data": [ { JSON Object 1 }, { JSON Object 2 }, { JSON Object 3 }, { JSON Object 4 }, ... ] } ``` Example: ```json { "input_data": [ { "product_id": "123456789", "label": "Some label ABC", "description": "A specific description for product 123456789", "new_attribute": "test" }, { "product_id": "987654321", "label": "Some label YUI", "description": "A specific description for product 987654321" }, { "product_id": "66668888", "label": "Some label XYZ", "description": "A specific description for product 66668888" } ] } ``` Then, the JSON string must be Base64 encoded. Using the previous example, the result will be: `eyAgICAiaW5wdXQtZGF0YSI6IFsgICAgICAgIHsgICAgICAgICAgICAicHJvZHVjdF9pZCI6ICIxMjM0NTY3ODkiLCAgICAgICAgICAgICJsYWJlbCI6ICJTb21lIGxhYmVsIEFCQyIsICAgICAgICAgICAgImRlc2NyaXB0aW9uIjogIkEgc3BlY2lmaWMgZGVzY3JpcHRpb24gZm9yIHByb2R1Y3QgMTIzNDU2Nzg5IiwgICAgICAgICAgICAibmV3X2F0dHJpYnV0ZSI6ICJ0ZXN0IiAgICAgICAgfSwgICAgICAgIHsgICAgICAgICAgICAicHJvZHVjdF9pZCI6ICI5ODc2NTQzMjEiLCAgICAgICAgICAgICJsYWJlbCI6ICJTb21lIGxhYmVsIFlVSSIsICAgICAgICAgICAgImRlc2NyaXB0aW9uIjogIkEgc3BlY2lmaWMgZGVzY3JpcHRpb24gZm9yIHByb2R1Y3QgOTg3NjU0MzIxIiAgICAgICAgfSwgICAgICAgIHsgICAgICAgICAgICAicHJvZHVjdF9pZCI6ICI2NjY2ODg4OCIsICAgICAgICAgICAgImxhYmVsIjogIlNvbWUgbGFiZWwgWFlaIiwgICAgICAgICAgICAiZGVzY3JpcHRpb24iOiAiQSBzcGVjaWZpYyBkZXNjcmlwdGlvbiBmb3IgcHJvZHVjdCA2NjY2ODg4OCIgICAgICAgIH0gICAgXX0=` ### Send data payload to Pub/Sub Using CURL, we can now simply send the data payload to Pub/Sub: ```bash curl --request POST \ --http2 \ --header "content-type:application/json" \ --header "Authorization: Bearer ${TOKEN}" \ --data '{"messages": [ {"attributes": {}, "data": "eyAgICAiaW5wdXQtZGF0YSI6IFsgICAgICAgIHsgICAgICAgICAgICAicHJvZHVjdF9pZCI6ICIxMjM0NTY3ODkiLCAgICAgICAgICAgICJsYWJlbCI6ICJTb21lIGxhYmVsIEFCQyIsICAgICAgICAgICAgImRlc2NyaXB0aW9uIjogIkEgc3BlY2lmaWMgZGVzY3JpcHRpb24gZm9yIHByb2R1Y3QgMTIzNDU2Nzg5IiwgICAgICAgICAgICAibmV3X2F0dHJpYnV0ZSI6ICJ0ZXN0IiAgICAgICAgfSwgICAgICAgIHsgICAgICAgICAgICAicHJvZHVjdF9pZCI6ICI5ODc2NTQzMjEiLCAgICAgICAgICAgICJsYWJlbCI6ICJTb21lIGxhYmVsIFlVSSIsICAgICAgICAgICAgImRlc2NyaXB0aW9uIjogIkEgc3BlY2lmaWMgZGVzY3JpcHRpb24gZm9yIHByb2R1Y3QgOTg3NjU0MzIxIiAgICAgICAgfSwgICAgICAgIHsgICAgICAgICAgICAicHJvZHVjdF9pZCI6ICI2NjY2ODg4OCIsICAgICAgICAgICAgImxhYmVsIjogIlNvbWUgbGFiZWwgWFlaIiwgICAgICAgICAgICAiZGVzY3JpcHRpb24iOiAiQSBzcGVjaWZpYyBkZXNjcmlwdGlvbiBmb3IgcHJvZHVjdCA2NjY2ODg4OCIgICAgICAgIH0gICAgXX0="}]}' \ "https://pubsub.googleapis.com/v1/projectsfd-io-jarvis-demo-dlk/topics/ats-topic-000099-ats-example-products:publish" ``` Note that the Pub/Sub endpoint is not publicly callable. You need to specify a JWT in the HTTP Authorization header to go though. The user MUST have the Pub/Sub Publisher role to the according GCP Project to have access. This is a link to GCP documentation on how to generate a JWT from a service account credential file :$schema
type: string
optional
| The url of the json-schema that contains the properties that your configuration must verify. Most Code Editor can use that to validate your configuration, display help boxes and enlighten issues. | |configuration\_type
type: string
mandatory
|Type of data operation.
For a TTT data operation, the value is always "table-to-table".
| |configuration\_id
type: string
mandatory
|ID of the data operation.
You can pick any name you want, but is has to be unique for this data operation type.
Note that in case of conflict, the newly deployed data operation will overwrite the previous one. To guarantee its uniqueness, the best practice is to name your data operation by concatenating:
- your account ID,
- the word "load",
- and the target dataset or table.
short\_description
type: string
optional
| Short description of the context of the data operation. | |doc\_md
type: string
optional
| Path to a file containing a detailed description of the data operation. The file must be in Markdown format. | |environment
type: string
mandatory
|Deployment context.
Values: PROD, PREPROD, STAGING, DEV.
| |account
type: string
mandatory
| Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator. | |activated
type: boolean
optional
|Flag used to enable/disable the execution of the data operation.
Default value: true
| |archived
type: boolean
optional
|Flag used to enable/disable the visibility of the data operation's configuration and runs in Jarvis Studio.
Default value: false
| |start\_date
type: string
mandatory
|Start date of the data operation.
The format must be:
"YYYY, MM, DD"
Where:
- YYYY >= 1970
- MM = \[1, 12]
- DD = \[1, 31]
schedule\_interval
type: string
mandatory
|A Tables to Tables data operation can be launched in two different ways:
- If schedule\_interval is set to "None", the data operation will need to be started with a Workflow, when a given condition is met. (This solution is recommended.)
- If you want the data operation to start at regular intervals, you can define this in the schedule\_interval parameter with a Cron expression.
Example
For the data operation to start everyday at 7:00, you need to set it as follows:
"schedule\_interval": "0 7 \* \* \*",
You can find online tools to help you edit your Cron expression (for example, crontab.guru).
| |max\_active\_runs
type: integer
optional
|This parameter limits the number of concurrent runs for this data operation. As most data operations are run once daily, there is usually no need to set a value higher than 1 here.
Default value: 1
| |task\_concurrency
type: integer
optional
|This parameter limits the number of tasks that might run concurrently.
As a great volume of data might be handled by each task, it is important to make sure to avoid consuming too many resources for one data operation.
Make sure also that the value you set here is high enough for concurrent tasks set in the task\_dependencies parameter to run properly.
Default value: 5
| |catchup
type: boolean
optional
|This parameter allows you to specify if you want to execute the data operation runs that were supposed to happen between start\_date and the actual deployment date.
Example
If you start receiving data from September 1st, but you only finish writing your code on September 7th, you might want to run your data operation from a date in the past: September 1st.
The catchup parameter can have two values:
- If it is set to "true" AND a scheduling\_interval is set AND start\_date is set in the past, Composer/Airflow will execute every run of the data operation scheduled from the start date until the current date.
- If it is set to "false", the data operation will only be executed starting from the current date.
⚠️ If the data operation is scheduled to happen frequently and/or the missed execution period is long, the amount of runs might be important. Make sure you have enough resources to handle all the executions when deploying a data operation with catchup set to "true".
⚠️ If you plan to store a version of your configuration file for further modifications (in a Git repository for example), it's recommended to store it with a catchup set to "false" to avoid to accidentally create a lot of runs when you will deploy and execute your next version in the future.
Default value: false
| |task\_dependencies
type: array of strings
mandatory
|The task\_dependencies parameter allows you to create dependencies between the different tasks specified in the workflow parameter (see below). It will define in which order the workflow tasks will run, some of them running concurrently, others sequentially.
Usually workflow tasks will be run in the following order:
- create\_gbq\_table
- sql
- copy\_gbq\_table
Syntax
- The double chevron
>>means that the first task needs to be completed before the next one can start. - The comma
,means that the tasks will run concurrently. - The square brackets
\[and]allow you to define a set of tasks that will run together.
For detailed information about the syntax, refer to the Airflow documentation.
⚠️ Criticality and tasks chaining: If a warning or a transparent task has a child linked critical task, the job is rejected to prevent inconsistent dependencies or criticalities. See "criticality" parameter below for more details.
ℹ️ You can create conditional tasks that can break a dependency chain depending on predefined conditions without raising an error with the task parameter "criticality" set to "break". See "criticality" parameter below or this article for more information.
Example 1
We have the following tasks that we want to run sequentially: taskA (create\_gbq\_table), taskB (sql) and taskC (copy\_gbq\_table).
The task\_dependencies parameter will be as follows: "task\_dependencies": \[" taskA >> taskB >> taskC "],
Example 2
We have the following tasks that we want to run concurrently: taskA, taskB and taskC.
The task\_dependencies parameter will be as follows: "task\_dependencies": \[" taskA, taskB, taskC "],
Example 3
We have the following 9 tasks we want to order: taskA, taskD, taskG (create\_gbq\_table), taskB, taskE, taskH (sql), taskC, taskF, taskI (copy\_gbq\_table).
The task\_dependencies parameter will be as follows:"task\_dependencies": \[" \[taskA, taskD, taskG] >> \[taskB, taskE, taskH] >> \[taskC, taskF, taskI] "],
Example 4
In the example above, we want taskH to run before taskE so we can use its result for taskE.
The task\_dependencies parameter will be as follows:
"task\_dependencies": \[" \[taskA, taskD, taskG] >> taskH >> \[taskB, taskE] >> \[taskC, taskF, taskI] "],
workflow
type: array of maps
mandatory
|List of tasks the data operations will execute.
Check the section below for detailed information on their parameters.
| |default\_gcp\_project\_id
type: string
mandatory
|Default GCP Project ID.
This parameter can be set for each workflow task sub-object, and will be overridden by that value if it is different.
| |default\_bq\_dataset
type: string
mandatory
|Default BigQuery dataset ID.
This parameter can be set for each workflow task sub-object, and will be overridden by that value if it is different.
| |default\_bq\_data\_location
type: string
optional
The default BigQuery data location.
Default value: EU
default\_write\_disposition
type: string
mandatory
|Action that occurs if the destination table already exists (see Google BigQuery documentation).
Possible values:
- "WRITE\_TRUNCATE" (default): The run will write table data from the beginning. If the table already contained lines, they will all be deleted and replaced by the new lines. This option is used most of the time for daily runs to avoid duplicates.
- "WRITE\_APPEND": The run will append new lines to the table. When using this option, make sure not to run the data operation several times.
- "WRITE\_EMPTY": This option only allows adding data to an empty table. If the table already contains data, it returns an error. It is hardly ever used as data operations are usually run periodically, so they will always contain data after the first run.
This parameter can be set for each workflow task sub-object, and will be overridden by that value if it is different.
| ## :loop: Workflow task parameters A Tables to Tables workflow can include four types of tasks: * **create\_gbq\_table**: This type of task allows you to create the skeleton of a table based on a DDL file. You could compare it to the baking pan of your table. * **sql**: This type of task allows you to fill that baking pan using a SQL query or a SQL script from a file. * **copy\_gbq\_table**: This type of task allows you to duplicate a table named X into another table named Y. * **expectation:** This type of task allows you to perform data quality tests. See [expectations](/data-pipeline-operations/expectations) for more details. * **delete\_gbq\_table:** This type of task allows you to delete a table or a partition of a table. For each workflow sub-object, parameters will differ depending on the task type. {% hint style="info" %} Refer to [this page](/data-pipeline-operations/transform-data-with-tables-to-tables/table-to-table-sql-and-ddl-files) to know how to create the DDL and SQL files corresponding to these tasks. {% endhint %} ### **SQL task parameters**| Parameter | Description |
|---|---|
task_type type: string optional | The value has to be set to "sql" for this task type. As "sql" is the default type, this parameter can be omitted for this task type. |
id type: string mandatory | ID of the task. It must be unique within the data operation. |
short_description type: string optional | Short description of what the task does. |
doc_md type: string optional | Path to a file containing a detailed description of the task. The file must be in Markdown format. |
sql_file type: string mandatory | Path to the file containing the actual SQL query or the SQL script (can be assertions or expectations). This file is going to be read and its content uploaded to Firestore upon deployment of the data operation. |
gcp_project_id type: string optional | GCP Project ID. Overrides default_gcp_project_id. |
bq_dataset type: string optional | Name of the BigQuery destination dataset. Overrides default_bq_dataset. |
write_disposition type: string optional | Action that occurs if the destination table already exists (see Google BigQuery documentation).
Overrides default_write_disposition. |
table_name type: string mandatory if the SQL file contains a query, optional for a SQL script | Target table used upon SQL query execution.
|
| {{FD_DATE_YEAR_4}} : YYYY => 2021 | |
| {{FD_DATE_YEAR_2}} : YY => 21 | |
| {{FD_DATE_MONTH}} : MM => 05 | |
| {{FD_DATE_DAY}} : DD => 13 | |
criticality type: string optional | Task criticality which defines the impact of an error on this task on the data operation global status and behavior. It allows to create tasks dedicated to warning tests (see expectations) that won't break the execution if it fails or conditional tasks that can lead to different tasks depending on predefined conditions (see this article).
⚠️ Tasks chaining: We enforce rules at the validation of the configuration (at deployment time) to prevent inconsistent dependencies or criticalities in a chain of tasks. If a warning or a transparent task has a child linked critical task, the job is rejected. Indeed, if you have 3 tasks Default value: "critical" |
temporary_table type: boolean optional | If set to "true", this parameter will force the deletion of the table at the end of the data operation. If the TTT data operation failed before deleting the temporary table, the table will be deleted automatically after 24 hours. ⚠️Please note that if this flag is set to "true", then the target table is preliminarily deleted if it exists. Description, partitionning and clustering are lost. Default value: false |
sql_query_template type: string optional | If you want to use variables in your SQL query or script, you need to set this parameter to "TEMPLATE_CURRENT_DATE" (only supported value). This variable will be set to the execution date of the data operation (and not today's date). For example, if you want to retrieve data corresponding to the execution date, you can use the following instruction:
|
task\_type
type: string
mandatory
| In this case, the value has to be set to "create\_gbq\_table". | |id
type: string
mandatory
| ID of the task. It must be unique within the data operation. | |short\_description
type: string
optional
| Short description of what the task does. | |doc\_md
type: string
optional
| Path to a file containing a detailed description of the task. The file must be in Markdown format. | |gcp\_project\_id
type: string
optional
|GCP Project ID.
Overrides default\_gcp\_project\_id.
| |bq\_dataset
type: string
optional
|Name of the BigQuery destination dataset.
Overrides default\_bq\_dataset.
| |bq\_table
type: string
mandatory
| Name of the BigQuery table name. | |ddl\_file
type: string
mandatory
| Path to the JSON file containing DDL information to create the table. | |force\_delete
type: boolean
optional
| If set to "true", this parameter will force the deletion of the table prior to its creation. | |criticality
type: string
optional
|Task criticality which defines the impact of an error on this task on the data operation global status and behavior.
Criticality can be: critical, warning, transparent or break.
See "criticality" description in the SQL task above for more details
Default value: "critical"
| ### **Table copy task parameters** | **Parameter** | Description | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | |task\_type
type: string
mandatory
| In this case, the value has to be set to "copy\_gbq\_table". | |id
type: string
mandatory
| ID of the task. It must be unique within the data operation. | |short\_description
type: string
optional
| Short description of what the task does. | |doc\_md
type: string
optional
| Path to a file containing a detailed description. The file must be in Markdown format. | |source\_gcp\_project\_id
type: string
mandatory
| GCP project ID for the source BigQuery table. | |source\_bq\_dataset
type: string
mandatory
| BigQuery dataset for the source table. | |source\_bq\_table
type: string
mandatory
| Name of the source table. | |gcp\_project\_id
type: string
optional
|GCP project ID for the destination BigQuery table.
Overrides default\_gcp\_project\_id.
| |bq\_dataset
type: string
optional
|Name of the BigQuery destination dataset.
Overrides default\_bq\_dataset.
| |destination\_bq\_table
type: string
mandatory
| Name of the BigQuery destination table. | |destination\_bq\_table\_date\_suffix
type: boolean
optional
|Enables/Disables the date suffix to allow date partitioning in BigQuery.
default: false
destination\_bq\_table\_date\_suffix\_format
type: string
mandatory if destination\_bq\_table\_date\_suffix is set to true
|Date format for the table suffix.
As of now, the only possible value is "%Y%m%d".
criticality
type: string
optional
|Task criticality which defines the impact of an error on this task on the data operation global status and behavior.
Criticality can be: critical, warning, transparent or break.
See "criticality" description in the SQL task above for more details
Default value: "critical"
| ### Table Deletion task parameters| Parameter | Description |
task_type type: string mandatory | In this case, the value has to be set to "delete_gbq_table". |
id type: string mandatory | ID of the task. It must be unique within the data operation. |
short_description type: string optional | Short description of what the task does. |
doc_md type: string optional | Path to a file containing a detailed description. The file must be in Markdown format. |
bq_dataset type: string mandatory | BigQuery dataset for the table. |
bq_table type: string mandatory | Name of the table. |
| {{FD_DATE_YEAR_4}} : YYYY => 2021 | |
| {{FD_DATE_YEAR_2}} : YY => 21 | |
| {{FD_DATE_MONTH}} : MM => 05 | |
| {{FD_DATE_DAY}} : DD => 13 | |
| default_bq_data_location type: string optional | The default BigQuery data location. Default value: EU |
criticality type: string optional | Task criticality which defines the impact of an error on this task on the data operation global status and behavior. Criticality can be: critical, warning, transparent or break. See "criticality" description in the SQL task above for more details Default value: "critical" |
bq\_table\_description
type: string
mandatory
| Description of the BigQuery table. | |bq\_table\_schema
type: array
mandatory
|BigQuery table schema. It contains a list of fields corresponding to the number of columns it will contain.
Each field described has three attributes:
- name
- type
- description
bq\_table\_clustering\_fields
type: array
optional
|List of fields used when clustering is enabled.
The table data will be automatically organized based on the contents of the fields you specify. Their order determines the sort order of the data.
If this parameter is set, time partitioning will be automatically enabled on the table. If you don't set partitioning parameters, default values will be used.
| |bq\_table\_timepartitioning\_field
type: string
optional
|If this parameter is set, the table will be partitioned by this field.
If not, the table will be partitioned by pseudo column \_PARTITIONTIME.
The field must be a top-level TIMESTAMP or DATE field. Its mode must be NULLABLE or REQUIRED.
(Refer to BigQuery documentation for more information.)
Note: You can set this parameter to a field that equals to DATE(''). Then, if you relaunch an execution with a partition, and if default\_write\_disposition is set to "WRITE\_APPEND" in the JSON configuration file, Tailer will check if the corresponding partition already exists in the table:
- If it does, it will delete it, and replace it with the current execution data.
- If not, it will add them.
bq\_table\_timepartitioning\_type
type: string
optional
|Sets the partition type. Use one of the following values: "HOUR", "DAY", "MONTH" or "YEAR".
If not present, default is "DAY".
| |bq\_table\_timepartitioning\_expiration\_ms
type: integer
optional
|Number of milliseconds for which to keep the storage for a partition.
(Refer to BigQuery documentation for more information.)
| |bq\_table\_timepartitioning\_require\_partition\_filter
type: boolean
optional
|If set to true, queries over the partitioned table require a partition filter that can be used for partition elimination to be specified.
(Refer to BigQuery documentation for more information.)
| ### DDL Data Types Tailer Platform supports the following data types. #### Numeric types | Name | Description | | --------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `int64` |Integers are numeric values that do not have fractional components.
They range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
| | `float64` | Floating point values are approximate numeric values with fractional components. | | `numeric` |This data type represents decimal values with 38 decimal digits of precision and 9 decimal digits of scale. (Precision is the number of digits that the number contains. Scale is how many of these digits appear after the decimal point.)
It is particularly useful for financial calculations.
| #### Boolean type | Name | Description | | --------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `boolean` | This data type supports the `true`, `false`, and `null` values. It can perform some basic conversions, such as `'true'`, `'True'`, `True`, or 1 becoming `true`. | #### String type | Name | Description | | -------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `string` |Variable-length character data.
When converting data from string to a different data type, makes sure to use safe\_cast when you're unsure about the data quality.
$schema
type: string
optional
| The url of the json-schema that contains the properties that your configuration must verify. Most Code Editor can use that to validate your configuration, display help boxes and enlighten issues. | |configuration\_type
type: string
mandatory
|Type of data operation.
For a TTS data operation, the value is always "table-to-storage".
| |configuration\_id
type: string
mandatory
|ID of the data operation.
You can pick any name you want, but is has to be unique for this data operation type.
Note that in case of conflict, the newly deployed data operation will overwrite the previous one. To guarantee its uniqueness, the best practice is to name your data operation by concatenating:
- your account ID,
- the word "extract",
- and a description of the data to extract.
short\_description
type: string
optional
| Short description of the table to storage data operation. | |environment
type: string
mandatory
|Deployment context.
Values: PROD, PREPROD, STAGING, DEV.
| |account
type: string
mandatory
| Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator. | |version
type: string
mandatory, otherwise default is 1 and in that case refers to the deprecated V1.
Version of the configuration. Must be "3" in order to use the latest features.
Default : "1" for backward compatibility purposes but only version "3" supports the latest features. Version 1 is deprecated.
| |activated
type: boolean
optional
|Flag used to enable/disable the execution of the data operation.
Default value: true
| |archived
type: boolean
optional
|Flag used to enable/disable the visibility of the data operation's configuration and runs in Tailer¯Studio.
Default value: false
| |doc\_md
type: string
optional
| Path to a file containing a detailed description of the data operation. The file must be in Markdown format. | |start\_date
type: string
optional
|Start date of the data operation.
The format must be:
"YYYY, MM, DD"
Where:
- YYYY >= 1970
- MM = \[1, 12]
- DD = \[1, 31]
schedule\_interval
type: string
optional
|A Tables to Tables data operation can be launched in two different ways:
- If schedule\_interval is set to "None", the data operation will need to be started with a Workflow, when a given condition is met. (This solution is recommended.)
- If you want the data operation to start at regular intervals, you can define this in the schedule\_interval parameter with a Cron expression.
Note:
⚠️ You need to define a start\_date to schedule a data operation, otherwise the schedule\_interval is ignored.
Example
For the data operation to start everyday at 7:00, you need to set it as follows:
"schedule\_interval": "0 7 \* \* \*",
You can find online tools to help you edit your Cron expression (for example, crontab.guru).
| |print\_header
type: boolean
optional
Print a header row in the exported data.
Default value: true
| |destination\_format
type: string
optional
|Define the format of the output file :
Possible values: "NEWLINE\_DELIMITED\_JSON" (JSON file), "AVRO", "PARQUET"
Note that if you specify "NEWLINE\_DELIMITED\_JSON", the field-delimiter parameter is not taken into account.
Default value: "CSV"
gcs\_dest\_bucket
type: string
mandatory
|Google Cloud Storage destination bucket.
This is the bucket where the data is going to be extracted.
| |gcs\_dest\_prefix
type: string
mandatory
|Path in the GCS bucket where the files will be extracted, e.g. "some/sub/dir".
Note that you can use {{FD\_DATE}} inside the path to include the current ISO date.
e.g. "some/sub/dir/{{FD\_DATE}}"
gcp\_project\_id
type: string
mandatory
| ID of the Google Cloud project containing the BigQuery instance. | |field\_delimiter
type: string
optional
|Separator for fields in the CSV output file, e.g. ";".
Note: For Tab separator, set to "\t".
Default value: "
| |compression
type: string
optional
|Compression mode for the output file.
Possible values: "None", "GZIP", "SNAPPY".
Note that if you specify "GZIP", a ".gz" extension will be added at the end of the filename.
Default value: "None"
sql\_query\_template
type: string
optional
If you want to use variables in your SQL query or script, you need to set this parameter to "TEMPLATE\_CURRENT\_DATE" (only supported value). This variable will be set to the execution date of the data operation (and not today's date).
For example, if you want to retrieve data corresponding to the execution date, you can use the following instruction:
WHERE sale\_date = DATE('{{TEMPLATE\_CURRENT\_DATE}}')
bq\_data\_location
type: string
optional
Bigquery location used by default in all tasks.
If not specified the value 'EU' will be set.
The list of available values can be found here :
generate\_top\_file
type: boolean
optional
|If true, generates an empty file when the data export is complete.
This file name is defined by the file name template. For exemple if the file name template is "{{FD\_DATE}}-my\_data\_extraction.csv" then the top file generated on 2022-01-01 will be named as: 20220101-my\_data\_extraction.csv.top
Default value: false
| |delete\_dest\_bucket\_content
type: boolean
optional
|If set to true, this parameter will trigger the preliminary deletion of any items present in the destination directory.
This can prevent an issue when a new run of the same operation is needed after a fix. If the first run had generated file-0.csv and file-1.csv, and then the 2nd run only returns and erases file-0.csv, then you need to delete the destination bucket at the begining of the 2nd run, or you will end up with a file-0.csv from the 2nd run and a file-1.csv from the first run.
⚠️ If several table-to-storage operations write in the same directory at the same time, and if this parameter is true, then some extracted files can be deleted by mistake. The best practice is to have a dedicated subdirectory for each operation.
Default value: false
| |tasks
type: array of maps
mandatory
List of tasks the data operations will execute.
Check the section below for detailed information on their parameters.
| ## :envelope\_with\_arrow:Tasks Parametrers With le latest version, it is now possible to export the data to different locations in one configuration. And this is possible thanks to the parameter "tasks". Every task specifies an export. The tasks will use the parameters defined in the global configuration by default. If a parameter is specified in a task and in the global parameters, then the parameter in the task will overwrite the default parameter. | Parameter | Description | | ----------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |task\_id
type: string
mandatory
sql\_file
type: string
mandatory
| Path to the file containing the extraction query. | |output\_filename
type: string
mandatory
|Template for the output filename.
You can use the following placeholders inside the name:
- {{FD\_DATE}}: The date format will be YYYYMMDD
- {{FD\_TIME}}: The time format will be hhmmss
⚠️ BigQuery splits the content in several numbered files if you export more than 1 GB of data. A number starting at 0 and left-padded to 12 digits is added before the extension and after a "-". To ensure a consistent behavior, this number is always added, even if you export less than 1 GB.
For example, an operation with the output\_filename "{{FD\_DATE}}-{{FD\_TIME}}\_my\_data\_extraction.csv" executed the 2022-01-01 on 06:32:16 will generate a file: 20220101-063216\_my\_data\_extraction-000000000000.csv
copy\_table
type: boolean
optional
|Parameter used to enable a copy of the output data in a BigQuery table.
Default value: false
| |dest\_gcp\_project\_id
mandatory if copy\_table is set to "true"
| ID of the GCP project that will contain the table copy. | |dest\_gbq\_dataset
mandatory if copy\_table is set to "true"
| Name of the BigQuery dataset that will contain the table copy. | |dest\_gbq\_table\_suffix
optional, to use only if copy\_table is set to "true"
|The only supported value for this parameter is "dag\_execution\_date".
This will add "\_yyyymmdd" at the end of the table name to enable ingestion time partitioning.
Default value: None
bq\_data\_location
type: string
optional
Bigquery location used in this specific task.
If not specified the value used will be the global "bq\_data\_location" set at the configuration root.
The list of available values can be found here :
configuration\_type
type: string
mandatory
|Type of data operation.
For a TTS data operation, the value is always "table-to-storage".
| |configuration\_id
type: string
mandatory
|ID of the data operation.
You can pick any name you want, but is has to be unique for this data operation type.
Note that in case of conflict, the newly deployed data operation will overwrite the previous one. To guarantee its uniqueness, the best practice is to name your data operation by concatenating:
- your account ID,
- the word "extract",
- and a description of the data to extract.
short\_description
type: string
optional
| Short description of the table to storage data operation. | |environment
type: string
mandatory
|Deployment context.
Values: PROD, PREPROD, STAGING, DEV.
| |account
type: string
mandatory
| Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator. | |activated
type: boolean
optional
|Flag used to enable/disable the execution of the data operation.
Default value: true
| |archived
type: boolean
optional
|Flag used to enable/disable the visibility of the data operation's configuration and runs in Tailer¯Studio.
Default value: false
| |gcs\_dest\_bucket
type: string
mandatory
|Google Cloud Storage destination bucket.
This is the bucket where the data is going to be extracted.
| |gcs\_dest\_prefix
type: string
mandatory
| Path in the GCS bucket where the files will be extracted, e.g. "some/sub/dir". | |delete\_dest\_bucket\_content
type: boolean
optional
|If set to true, this parameter will trigger the preliminary deletion of any items present in the destination directory.
This can prevent an issue when a new run of the same operation is needed after a fix. If the first run had generated file-0.csv and file-1.csv, and then the 2nd run only returns and erases file-0.csv, then you need to delete the destination bucket at the begining of the 2nd run, or you will end up with a file-0.csv from the 2nd run and a file-1.csv from the first run.
⚠️ If several table-to-storage operations write in the same directory at the same time, and if this parameter is true, then some extracted files can be deleted by mistake. The best practice is to have a dedicated subdirectory for each operation.
Default value: false
| |gcp\_project
type: string
mandatory
| ID of the Google Cloud project containing the BigQuery instance. | |gcp\_project\_id
type: string
optional
| Enter the same value as gcp\_project to avoid the question of project selection during a deployment with tailer deploy configuration command. | |field\_delimiter
type: string
optional
|Separator for fields in the CSV output file, e.g. ";".
Note: For Tab separator, set to "\t".
Default value: "
| |print\_header
type: boolean
optional
|Print a header row in the exported data.
Default value: true
| |sql\_file
type: string
mandatory
| Path to the file containing the extraction query. | |compression
type: string
optional
|Compression mode for the output file.
Possible values: "None", "GZIP"
Note that if you specify "GZIP", a ".gz" extension will be added at the end of the filename.
Default value: "None"
output\_filename
type: string
mandatory
|Template for the output filename.
You can use the following placeholders inside the name:
- {{FD\_DATE}}: The date format will be YYYYMMDD
- {{FD\_TIME}}: The time format will be hhmmss
⚠️ BigQuery splits the content in several numbered files if you export more than 1 GB of data. A number starting at 0 and left-padded to 12 digits is added before the extension and after a "-". To ensure a consistent behavior, this number is always added, even if you export less than 1 GB.
For example, an operation with the output\_filename "{{FD\_DATE}}-{{FD\_TIME}}\_my\_data\_extraction.csv" executed the 2022-01-01 on 06:32:16 will generate a file: 20220101-063216\_my\_data\_extraction-000000000000.csv
destination\_format
type: string
optional
|Define the format of the output file :
Possible values: "NEWLINE\_DELIMITED\_JSON" (JSON file), "AVRO"
Note that if you specify "NEWLINE\_DELIMITED\_JSON", the field-delimiter parameter is not taken into account.
Default value: "CSV"
copy\_table
type: boolean
optional
|Parameter used to enable a copy of the output data in a BigQuery table.
Default value: false
| |dest\_gcp\_project\_id
mandatory if copy\_table is set to "true"
| ID of the GCP project that will contain the table copy. | |dest\_gbq\_dataset
mandatory if copy\_table is set to "true"
| Name of the BigQuery dataset that will contain the table copy. | |dest\_gbq\_table
mandatory if copy\_table is set to "true"
| Name of the BigQuery table copy. | |dest\_gbq\_table\_suffix
optional, to use only if copy\_table is set to "true"
|The only supported value for this parameter is "dag\_execution\_date".
This will add "\_yyyymmdd" at the end of the table name to enable ingestion time partitioning.
| # Orchestrate processings with Workflow Learn how to trigger data operations using the Workflow data operation. ## ✅ Compatible data operations All data operations can be triggered with a Workflow data operation. ## ⚙️ How it works After you've deployed the data operation of your choice, you can deploy a Workflow data operation to determine how to trigger it. Some data operations can also be launched through events or scheduling, so the Workflow data operation is optional. ## **📋 How to deploy a Workflow data operation** A Workflow data operation is never deployed alone, as it is only used to trigger another data operation. To know how to deploy one, refer to the documentation associated to the data operation you want to trigger. To learn about the Workflow configuration file parameters, refer to [this page](/data-pipeline-operations/orchestrate-processings-with-workflow/workflow-configuration-file). # \[V2] Workflow configuration file This is the description of the JSON configuration file for a V2 Workflow data operation. A Workflow data operation is used to specify one or several data operation IDs that need to be successfully executed for one target data operation to be triggered. ## :eye\_in\_speech\_bubble: Example ```json { "$schema": "http://jsonschema.tailer.ai/schema/workflow-veditor", "configuration_type": "workflow", "configuration_id": "workflow-Load_Configuration", "version":"2", "environment": "DEV", "short_description": "This is a short description", "doc_md": "readme.md", "account": "000099", "activated": true, "archived": false, "gcp_project_id": "fd-io-tailer-demo-dlk", "schedule_interval_reset": "59 23 * * *", "authorized_job_ids": [ "gbq-to-gbq|000099_assert_of_configuration_DEV" ], "target_dag": { "configuration_type":"table-to-table", "configuration_id":"Load_Configuration_DEV" } } ``` ## :gear: Parameters | Parameter | Description | | ------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |$schema
type: string
optional
| The url of the json-schema that contains the properties that your configuration must verify. Most Code Editor can use that to validate your configuration, display help boxes and enlighten issues. | |configuration\_type
type: string
mandatory
|Type of configuration file.
In this case, the value has to be "workflow".
configuration\_id
type: string
mandatory
|ID of the workflow.
You can pick any name you want, but is has to be unique for this type of configuration file.
Note that in case of conflict, the newly deployed data operation will overwrite the previous one.
| |version
type: string
mandatory
|Version of the configuration. Must be "2" in order to use the latest features.
Default : "1" for backward compatibility purposes but only version "2" supports the latest features. Version 1 is deprecated.
| |environment
type: string
mandatory
|Deployment context.
Values: PROD, PREPROD, STAGING, DEV.
| |short\_description
type: string
optional
| Short description of the context of the configuration file. | |doc\_md
type: string
optional
| Path to a file containing a detailed description of the data operation. The file must be in Markdown format. | |account
type: string
mandatory
| Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator. | |activated
type: boolean
optional
|Flag used to enable/disable the execution of the workflow.
If not specified, the default value will be "true".
| |archived
type: boolean
optional
|Flag used to enable/disable the visibility of the workflow's configuration and runs in Tailer Studio.
If not specified, the default value will be "false".
| |gcp\_project\_id
type: string
mandatory
| ID of the Google Cloud project containing the BigQuery instance to be triggered. | |schedule\_interval\_reset
type: string
optional
|You can choose to reset your workflow regularly by specifying here a Cron expression (see for ex. crontab.guru). When you reset a workflow, all the triggering conditions are set to false, so all the previous runs are forgotten.
Example:
For a daily job, you may want to reset the workflow everyday at 23:59, so the runs on previous days won't be taken into account for the current day. You need to set it as follows:
"schedule\_interval\_reset": "59 23 \* \* \*"
Default: None
authorized\_job\_ids
type: string array
mandatory
|Data operations that need to be executed and successful for the current workflow to be triggered. It can be retrieved from the Runs on Tailer Studio, in the Run Details > Job Id.
It is possible to wait for several configurations which will be separated by a comma.
Example :
"gbq-to-gbq
target\_dag
type: dict
mandatory
|Data operation to trigger. Target\_dag is split in two key-value.
configuration\_type (string) : you specify the type of data operation with one of the following values :
- "storage-to-storage"
- "storage-to-table"
- "table-to-table"
- "table-to-storage"
- "vm-launcher"
configuration\_id (string) : you need to specify the data operation configuration\_id, as it is specified in the last part of your job\_id. If you deployed your target job without any context, then it's the configuration\_id concatenated with the environment (for ex: yourConfId\_PROD). If you deployed your target job with a context, then it's the context id, starting with the account id, concatenated with the configuration id (for ex: 000099\_yourContextId\_yourConfId).
configuration\_type
type: string
mandatory
|Type of configuration file.
In this case, the value has to be "workflow".
configuration\_id
type: string
mandatory
|ID of the workflow.
You can pick any name you want, but is has to be unique for this type of configuration file.
Note that in case of conflict, the newly deployed data operation will overwrite the previous one.
| |environment
type: string
mandatory
|Deployment context.
Values: PROD, PREPROD, STAGING, DEV.
| |short\_description
type: string
optional
| Short description of the context of the configuration file. | |account
type: string
mandatory
| Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator. | |gcp\_project
type: string
optional
| ID of the Google Cloud project containing the BigQuery instance to be triggered. | |gcp\_project\_id
type: string
optional
| Enter the same value as gcp\_project to avoid the question of project selection during a deployment with tailer deploy configuration command. | |activated
type: boolean
optional
|Flag used to enable/disable the execution of the workflow.
If not specified, the default value will be "true".
| |archived
type: boolean
optional
|Flag used to enable/disable the visibility of the workflow's configuration and runs in Tailer Studio.
If not specified, the default value will be "false".
| |authorized\_job\_ids
type: string array
mandatory
| Data operations that need to be executed and successful for the current workflow to be triggered. | |target\_dag
type: string
mandatory
|Data operation to trigger.
For Tables to Tables data operations, you need to specify the data operation configuration\_id, concatenated with the environment ("\_PROD" or "\_DEV").
Example: "000099\_load\_stores\_details\_PROD"
For Storage to Storage, Storage to Tables et Table to Storage data operations, you only specify the type of data operation with one of the following values:
- storage-to-storage
- storage-to-tables
- table-to-storage
extra\_parameters
type: dict
mandatory
|This array contains the list of data operations to trigger.
Leave it empty for Tables to Tables data operations, as the target\_dag parameter already contains this information.
For other data operations, list one or several firestore\_conf\_doc\_id parameters.
| |firestore\_conf\_doc\_id
type: string
optional
|List of data operations to trigger.
Specify the configuration\_id of the data operations to trigger.
| # Convert XML to CSV Learn how to convert XML files into CSV files using a Convert XML to CSV data operation. ## 💡 What is the Convert XML to CSV operation? The Convert XML to CSV data operation allows you to retrieve all the information contained in a possibly complex XML file into a set of CSV files, all located in a Google Cloud Storage bucket. You can later convert your CSV files into database tables using a [Storage to Tables](/data-pipeline-operations/load-data-with-storage-to-tables) data operation. {% hint style="warning" %} Note that the XML file provided must be well-formed and valid against a matching XSD file (defining its elements and attributes, and the rules that apply to them). The XML and XSD file must have the same name (suffix excluded). Refer to [this page](/data-pipeline-operations/xml-conversion/untitled-1) for more details. If the XML file contains entities not set in the XSD, then the corresponding data will not be exported to CSV files. {% endhint %} ## ✅ Supported file types ### **Source files** * XML + XSD file pair(s) ### **Export files** * Multiple TSV (*Tab* Separated Values) + DDL file pairs ## ⚙️ How it works Every time a new file matching the specified XML file name pattern appears in a given directory of a Google Cloud Storage bucket: * The XML file is checked against the matching XSD file. * If the XML file is valid, the conversion process is launched. * A set of CSV files with their matching DDL files (describing their schema) is generated in the working directory. * The source XML files are deleted from the working directory. * If set, a filtering occurs at the end of the process to remove unwanted CSV files. ## **📋 How to deploy a Convert XML to CSV data operation** 1. Access your **tailer** folder (created during [installation](https://app.gitbook.com/s/getting-started/install-tailer-sdk.md)). 2. Create a working folder as you want, and create a JSON file for your data operation inside. 3. Place the XSD file in the same location as your JSON file. 4. Prepare your JSON configuration file. Refer to this page to learn about all the [parameters](/data-pipeline-operations/xml-conversion/untitled-1). 5. Access your working folder by running the following command: ``` cd "[path to your working folder]" ``` 6. To deploy the data operation, run the following command: ``` tailer deploy configuration your-configuration.json ``` 7. Log in to [Tailer Studio](http://studio.tailer.ai) to check the status and details of your data operation. 8. For your Convert XML to CSV data operation to be executed, you need to place a file into the source folder. 9. Access the GCS bucket to check your output files (CSV and DDL files). # Convert XML to CSV configuration file This is the description of the JSON configuration file for a Convert XML to CSV data operation. The configuration file is in JSON format. It contains the following sections: * [Global parameters](#global-parameters): General information about the data operation. * [Working folder parameters](#working-folder-parameters): Information related to the working folder for the files. * [Conversion parameters](#conversion-parameters): Information about the input file to process and the output files generated. ## :eye\_in\_speech\_bubble: Example Here is an example of Convert XML to CSV configuration file: ```json { "$schema": "http://jsonschema.tailer.ai/schema/xml-conversion-veditor", "configuration_type": "xml-conversion", "configuration_id": "000099-test-xml-conversion", "environment": "DEV", "account": "000099", "activated": true, "archived": false, "doc_md": "readme.md", "gcp_project_id": "fd-io-jarvis-demo-dlk", "gcs_bucket": "fd-io-demo-ds", "gcs_working_directory": "test_xml_conversion", "credentials": { "gcp-credentials.json": { "content": { "cipher_aes": "dd34e56f4...", "tag": "3d968340...", "ciphertext": "046ffe41f9c00448ea0f816119...", "enc_session_key": "36a0f8ffe1b1f0..." } } }, "filename_templates": [ { "filename_template": "coupon_{{FD_DATE}}.xml", "file_description": "This is a description.", "xsd_schema_file": "coupon.xsd", "output_suffix_filters":[ "advantage.tsv", "barcodeType.tsv" ] } ] } ``` ## :globe\_with\_meridians: Global parameters General information about the data operation. | Parameter | Description | | ------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |$schema
type: string
optional
| The url of the json-schema that contains the properties that your configuration must verify. Most Code Editor can use that to validate your configuration, display help boxes and enlighten issues. | |configuration\_type
type: string
mandatory
|Type of data operation.
For a Convert XML to CSV data operation, the value is always "xml-conversion".
| |configuration\_id
type: string
mandatory
|ID of the data operation.
You can pick any name you want, but is has to be unique for this data operation type.
Note that in case of conflict, the newly deployed data operation will overwrite the previous one. To guarantee its uniqueness, the best practice is to name your data operation by concatenating:
- your account ID,
- "xml-conversion".
- and a description of the data to convert.
environment
type: string
mandatory
|Deployment context.
Values: PROD, PREPROD, STAGING, DEV.
| |account
type: string
mandatory
| Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator. | |activated
type: boolean
optional
|Flag used to enable/disable the execution of the data operation.
If not specified, the default value will be "true".
| |archived
type: boolean
optional
|Flag used to enable/disable the visibility of the data operation's configuration and runs in Tailer Studio.
If not specified, the default value will be "false".
| |doc\_md
type: string
optional
| Path to a file containing a detailed description of the data operation. The file must be in Markdown format. | ## :briefcase: Working folder parameters Information related to the input/output working directory in Google Cloud Storage. | Parameter | Description | | ---------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |gcp\_project\_id
type: string
mandatory
| Google Cloud Platform project ID for the bucket where the data is going to be converted. | |gcs\_bucket
type: string
mandatory
| Name of the GCS bucket where the data is going to be converted. | |gcs\_working\_directory
type: string
mandatory
| Path in the GCS bucket where the input files will be placed, and the output files generated, e.g. "some/sub/dir". | |gcp\_credentials\_secret
type: dict
mandatory
|Encrypted credentials needed to read and write data in the GCS bucket.
You should have generated credentials when setting up GCP. To learn how to encrypt them, refer to this page.
| ## :currency\_exchange: Conversion parameters Information about the input file to process and the output files generated. | Parameter | Description | | ------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |filename\_templates
type: array
mandatory
| Array containing one or several **filename\_template** parameters (see below). | |filename\_template
type: string
mandatory
|When a file with a name matching the template set here is added to the specified GCS folder, the conversion will be launched automatically.
The following placeholders are currently supported:
- "FD\_DATE" looks for an 8-digit date (e.g. "20191015").
- "FD\_TIME" looks for a 6-digit time (e.g. "124213").
- "FD\_BLOB\_XYZ", where XYZ is a non-zero positive integer, looks for a string of characters of XYZ length.
Example 1
This template:
"stores\_{{FD\_DATE}}{{FD\_TIME}}.txt"
will allow you to process this type of files:
"stores\_20201116\_124213.txt"
Example 2
This template:
"{{FD\_DATE}}{{FD\_BLOB\_5}}fixedvalue{{FD\_BLOB\_11}}.gz"
will allow you to process this type of files:
"20201116\_12397\_fixedvalue\_12312378934.gz"
| |file\_description
type: string
mandatory
| A short description of the file template entry. | |xsd\_schema\_file
type: string
mandatory
|Name of the XSD file that will be used to validate the XML file before the conversion.
In the current version, only one XSD can be used per XML entry.
The XSD file name must be identical to the corresponding XML file name, excluding suffixes. For example "coupon.xsd" can be used to validate "coupon\_20210404.xml".
output\_suffix\_filters
type: array of string
optional
|Names of the output files to be kept after the conversion.
If the XML file contains many child entities, the conversion will create a lot of CSV files (one for each entity). This filter allows you to prevent unnecessary file upload to the output bucket.
It works by finding an occurrence of the string in the filename. For example, if a file named "coupon\_20210404\_advantage.tsv" is generated and the filter "advantage.tsv" was added, then this file will be kept.
| # Use advanced features with VM Launcher Learn how to use advanced features inside VMs using the VM Launcher data operation. A VM Launcher (VML) data operation allows you to launch a Google Compute Engine virtual machine (VM), use the VM to perform one of the following actions, and then stop the VM automatically. * [Execute a program](/data-pipeline-operations/execute-code-processings-with-vm-launcher/process-code-with-vm-launcher) in the language of your choice (Python, R, JavaScript, etc.) * [Encrypt and decrypt data](/data-pipeline-operations/execute-code-processings-with-vm-launcher/encrypt-decrypt-data-with-vm-launcher) using PGP (Pretty Good Privacy) # Process code with VM Launcher Learn how to use a VM Launcher data operation to process some code on a VM. ## 💡 What is the VM Launcher data operation for code processing? The VM Launcher data operation allows you to start a Google Compute Engine VM where you can execute a script in the language of your choice, and then to stop the VM automatically to save resources. ## ✅ Supported languages All languages (Python, R, JavaScript, etc.) ## ⚙️ How it works Every time a script file appears in a given directory of a Google Cloud Storage bucket: * A VM with the specified characteristics is started on GCE. * The instructions set in the JSON configuration file `script_to_execute` parameter are executed, and the script is launched. * Once the execution is complete, the VM is stopped automatically. ## **📋 How to deploy a VM Launcher data operation for code processing** 1. Access your **tailer** folder (created during [installation](https://app.gitbook.com/s/-MIIsP_DvP2J-c1szWrQ/getting-started/install-tailer-sdk.md)). 2. Create a working folder as you want. 3. Create a JSON file for your data operation in your working folder. Refer to this page to learn about all the [parameters](/data-pipeline-operations/xml-conversion/untitled-1). 4. Access your working folder by running the following command: ``` cd "[path to your working folder]" ``` 5. To deploy the data operation, run the following command: ``` tailer deploy configuration your-configuration.json ``` 6. Log in to [Tailer Studio](http://studio.tailer.ai) to check the status and details of your data operation. 7. For execute your VM Launcher data operation, place a script in the working folder. # VM Launcher configuration file for code processing This is the description of the JSON configuration file for a VM Launcher code processing data operation. The configuration file is in JSON format. It contains the following sections: * Global parameters: General information about the data operation. * Script parameters: Information about the script location and instructions to execute on the VM. * VM parameters: Information related to the VM where to execute the script. ## :eye\_in\_speech\_bubble: Example Here is an example of VM Launcher configuration file for code processing: ```json { "$schema": "http://jsonschema.tailer.ai/schema/vm-launcher-veditor", "configuration_type": "vm-launcher", "configuration_id": "000010_my-vm-job", "version": "2", "environment": "DEV", "account": "000099", "doc_md": "readme.md", "start_date": "2022, 11, 16", "schedule_interval": "0 7 * * *", "activated": false, "archived": false, "gcp_project_id": "my-project", "gcs_bucket": "my-bucket", "gcs_working_directory": "/", "credentials": { "gcp-credentials.json": { "content": { "cipher_aes": "xxx", "tag": "xxx", "ciphertext": ""xxx, "enc_session_key": "xxx" } } }, "script_to_execute": [ "mkdir -p input_DEV", "cd ./input_DEV && python3 my-python-script.py" ], "vm_delete": true, "vm_core_number": "2", "vm_memory_amount": "4", "vm_disk_size": "20", "vm_compute_zone": "europe-west1-b", "vm_custom_os_image_family": "ubuntu-2004-lts", "vm_custom_os_image_project": "ubuntu-os-cloud" } ``` ## :globe\_with\_meridians: Global parameters | Parameter | Description | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |$schema
type: string
optional
| The url of the json-schema that contains the properties that your configuration must verify. Most Code Editor can use that to validate your configuration, display help boxes and enlighten issues. | |configuration\_type
type: string
mandatory
|Type of data operation.
For an STS data operation, the value is always "storage-to-storage".
| |configuration\_id
type: string
mandatory
|ID of the data operation.
You can pick any name you want, but is has to be unique for this data operation type.
Note that in case of conflict, the newly deployed data operation will overwrite the previous one. To guarantee its uniqueness, the best practice is to name your data operation by concatenating:
- your account ID,
- the source bucket name,
- and the source directory name.
version
type: string
optional
Version of the configuration. Must be "2" in order to use the latest features.
Default : "1" but only version "2" supports start\_date and schedule\_interval. Version 1 is deprecated.
| |environment
type: string
mandatory
|Deployment context.
Values: PROD, PREPROD, STAGING, DEV.
| |account
type: string
mandatory
| Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator. | |doc\_md
type: string
optional
| Path to a file containing a detailed description of the data operation. The file must be in Markdown format. | |start\_date
type: string
optional
only available for version: "2" and latest
mandatory if you want to specify a schedule\_interval
|Start date of the data operation.
The format must be: "YYYY, MM, DD"
Where:
- YYYY >= 1970
- MM = \[1, 12]
- DD = \[1, 31]
schedule\_interval
type: string
optional
only available for version: "2" and latest
|A VM Launcher can be launched in two different ways:
- If schedule\_interval is set to "None", the data operation will need to be started with a Workflow, when a given condition is met.
- If you want the data operation to start at regular intervals, you can define this in the schedule\_interval parameter with a Cron expression.
Note:
⚠️ You need to define a start\_date to schedule a data operation, otherwise the schedule\_interval is ignored.
Example:
For the VM Launcher to start everyday at 7:00, you need to set it as follows:
"schedule\_interval": "0 7 \* \* \*",
You can find online tools to help you edit your Cron expression (for example, crontab.guru).
| |activated
type: boolean
optional
|Flag used to enable/disable the execution of the data operation.
If not specified, the default value will be "true".
| |archived
type: boolean
optional
|Flag used to enable/disable the visibility of the data operation's configuration and runs in Tailer Studio.
If not specified, the default value will be "false".
| ## :writing\_hand: Script parameters Information about the script location and instructions to execute it. | Parameter | Description | | ---------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | |gcp\_project\_id
type: string
mandatory
| Google Cloud Platform project ID for the bucket containing the script. | |gcs\_bucket
type: string
mandatory
| Name of the GCS bucket containing the script. | |gcs\_working\_directory
type: string
mandatory
| Path in the GCS bucket containing the script, e.g. "some/sub/dir". | |gcp\_credentials\_secret
type: dict
mandatory
|Encrypted credentials needed to read/move data from the source bucket.
You should have generated credentials when setting up GCP. To learn how to encrypt them, refer to this page.
| |script\_to\_execute
type: array
mandatory
| List of Unix commands to be executed (similar to a Bash script) on the VM. | ## :computer: VM parameters Information related to the Google Cloud Compute Engine VM where the script will be executed. | Parameter | Description | | ---------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |vm\_delete
type: boolean
optional
|If set to "true", this parameter will force the deletion of the VM at the end of the data operation. Running Compute Engine VMs will incur extra costs, so it is recommended to leave this parameter on "true".
Default value: true
| |vm\_core\_number
type: string
optional
|Virtual CPU (vCPU) count.
It is recommended to leave the default parameter, as this should allow sufficient performance to run a standard script.
Default value: 2
| |vm\_memory\_amount
type: string
optional
|System memory size (in GB).
It is recommended to leave the default parameter, as this should allow sufficient performance to run a standard script.
Default value: 4
| |vm\_disk\_size
type: string
optional
|Persistent disk size (in GB).
It is recommended to leave the default parameter, as this should provide enough space to store the data to process.
Default value: 20
| |vm\_compute\_zone
type: string
optional
Select the zone where the vm can execute its jobs
Default value: europe-west1-b
vm\_custom\_os\_image\_family
type: string
optional
|Image family of the custom image.
Note that for the time being, custom OS images MUST be based on a Ubuntu 20.04 LTS.
Default value: ubuntu-2004-lts
| |vm\_custom\_os\_image\_project
type: string
optional
|GCP Project hosting the custom image.
Note that this parameter is mandatory if vm\_custom\_os\_image\_family is set.
Default value: ubuntu-os-cloud
| # Encrypt/Decrypt data with VM Launcher Learn how to use a VM Launcher data operation to encrypt and decrypt data. ## :bulb: What is the VM Launcher data operation for data encryption/decryption? The VM Launcher data operation allows you to start a Google Compute Engine VM where you can encrypt and decrypt data using Pretty Good Privacy (PGP), and then to stop the VM automatically to save resources. PGP is a popular solution providing cryptographic privacy and authentication for data communication. ## :gear: How **it** works Every time the VM Launcher data operation is launched: * A VM with the specified characteristics is started on GCE. * The files placed in the bucket are either encrypted or decrypted using PGP. * Once the execution is complete, the VM is stopped automatically. ## Pretty Good Privacy (PGP) PGP is a protocol used for encrypting, decrypting and signing messages or files using a key pair. Every PGP user has both a public and private key. A public key is the key that other people use to encrypt a message that only you can open. A private key is the key that allows you to decrypt the messages sent to you based on your public key. A public key can be shared, but a private key should never be shared.  ## \*\*\*\*:clipboard: **How to deploy a** VM Launcher **data operation for data encryption/decryption** 1. Access your **tailer** folder (created during [installation](https://app.gitbook.com/s/-MIIsP_DvP2J-c1szWrQ/getting-started/install-tailer-sdk.md)). 2. Create a working folder as you want. 3. Create a JSON file for your data operation in your working folder. Refer to this page to learn about all the [parameters](https://app.gitbook.com/s/-MIIsP_DvP2J-c1szWrQ/xml-conversion/untitled-1.md). 4. Access your working folder by running the following command: ``` cd "[path to your working folder]" ``` 5. To deploy the data operation, run the following command: ``` tailer deploy configuration your-configuration.json ``` 6. Log in to [Tailer Studio](http://studio.tailer.ai) to check the status and details of your data operation. 7. Execute your VM Launcher data operation. # VM Launcher configuration file for data encryption This is the description of the JSON configuration file for a VM Launcher data encryption data operation. The configuration file is in JSON format. It contains the following sections: * Global parameters: General information about the data operation. * Working directory parameters: Information about the input/output directories. * Credential parameters: Information about the credentials for the input/output buckets and the PGP public key. ## :eye\_in\_speech\_bubble: Example Here is an example of VM Launcher configuration file for data encryption: ```json { "configuration_type": "vm-launcher", "configuration_id": "pgp-encrypt", "environment": "DEV", "account": "000099", "activated": true, "archive": false, "pgp_mode": "ENCRYPT", "gcp_project_id": "my-project", "gcs_source_bucket": "my-buckeet", "gcs_source_prefix": "output", "destination_gcs_bucket": "my-bucket", "destination_gcs_path": "output_encrypted", "vm_delete": true, "credentials": { "input-credentials.json": { "content": { "cipher_aes": "xxx", "tag": "xxx", "ciphertext": "xxx", "enc_session_key": "xxx" } }, "output-credentials.json": { "content": { "cipher_aes": "xxx", "tag": "xxx", "ciphertext": "xxx", "enc_session_key": "xxx" } }, "public_key.pgp": { "recipient": "my-recipient@my-domain.com", "content": { "cipher_aes": "xxx", "tag": "xxx", "ciphertext": "xxx", "enc_session_key": "xxx" } } } } ``` ## :globe\_with\_meridians: Global parameters | Parameter | Description | | ------------------------------------------------------------------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |configuration\_type
type: string
mandatory
|Type of data operation.
For an STS data operation, the value is always "storage-to-storage".
| |configuration\_id
type: string
mandatory
|ID of the data operation.
You can pick any name you want, but is has to be unique for this data operation type.
Note that in case of conflict, the newly deployed data operation will overwrite the previous one. To guarantee its uniqueness, the best practice is to name your data operation by concatenating:
- your account ID,
- the source bucket name,
- and the source directory name.
environment
type: string
mandatory
|Deployment context.
Values: PROD, PREPROD, STAGING, DEV.
| |account
type: string
mandatory
| Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator. | |activated
type: boolean
optional
|Flag used to enable/disable the execution of the data operation.
If not specified, the default value will be "true".
| |archived
type: boolean
optional
|Flag used to enable/disable the visibility of the data operation's configuration and runs in Tailer Studio.
If not specified, the default value will be "false".
| |pgp\_mode
type: string
optional
|PGP mode.
For data encryption, the value is always "ENCRYPT".
| ## :briefcase: Working directory parameters Information about the script location and instructions to execute it. | Parameter | Description | | --------------------------------------------------------------------------------- | ---------------------------------------------------------------------------- | |gcp\_project\_id
type: string
mandatory
| Google Cloud Platform project ID for the bucket containing the script. | |gcs\_source\_bucket
type: string
mandatory
| Name of the GCS bucket containing the files to encrypt. | |gcs\_source\_prefix
type: string
mandatory
| Path in the GCS bucket containing the files to encrypt, e.g. "some/sub/dir". | |destination\_gcs\_bucket
type: dict
mandatory
| Name of the GCS bucket containing the encrypted files. | |destination\_gcs\_path
type: array
mandatory
| Path in the GCS bucket containing the encrypted files, e.g. "some/sub/dir". | ## :desktop: VM parameters Information related to the Google Cloud Compute Engine VM where the script will be executed. | Parameter | Description | | ----------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |vm\_delete
type: string
mandatory
|If set to "true", this parameter will force the deletion of the VM at the end of the data operation. Running Compute Engine VMs will incur extra costs, so it is recommended to leave this parameter on "true".
Default value: true
| |vm\_core\_number
type: string
mandatory
|Virtual CPU (vCPU) count.
It is recommended to leave the default parameter, as this should allow sufficient performance to run a standard script.
Default value: 2
| |vm\_memory\_amount
type: string
mandatory
|System memory size (in GB).
It is recommended to leave the default parameter, as this should allow sufficient performance to run a standard script.
Default value: 4
| |vm\_disk\_size
type: string
mandatory
|Persistent disk size (in GB).
It is recommended to leave the default parameter, as this should provide enough space to store the data to process.
Default value: 20
| ## :closed\_lock\_with\_key: Credential parameters | Parameter | Description | | -------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | |credentials
type:array
mandatory
|Array containing three entities:
input credentials for the input bucket,
output credentials for the output bucket,
and the public PGP key.
configuration\_type
type: string
mandatory
|Type of data operation.
For an STS data operation, the value is always "storage-to-storage".
| |configuration\_id
type: string
mandatory
|ID of the data operation.
You can pick any name you want, but is has to be unique for this data operation type.
Note that in case of conflict, the newly deployed data operation will overwrite the previous one. To guarantee its uniqueness, the best practice is to name your data operation by concatenating:
- your account ID,
- "pgp-decrypt",
- and a description of the data to decrypt.
environment
type: string
mandatory
|Deployment context.
Values: PROD, PREPROD, STAGING, DEV.
| |account
type: string
mandatory
| Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator. | |activated
type: boolean
optional
|Flag used to enable/disable the execution of the data operation.
If not specified, the default value will be "true".
| |archived
type: boolean
optional
|Flag used to enable/disable the visibility of the data operation's configuration and runs in Tailer Studio.
If not specified, the default value will be "false".
| |pgp\_mode
type: string
optional
|PGP mode.
For data decryption, the value is always "DECRYPT".
| ## 💼 Working directory parameters Information about the script location and instructions to execute it. | Parameter | Description | | --------------------------------------------------------------------------------- | -------------------------------------------------------------------------------- | |gcp\_project\_id
type: string
mandatory
| Google Cloud Platform project ID for the bucket containing the files to decrypt. | |gcs\_source\_bucket
type: string
mandatory
| Name of the GCS bucket containing the files to decrypt. | |gcs\_source\_prefix
type: string
mandatory
| Path in the GCS bucket containing the files to decrypt, e.g. "some/sub/dir". | |destination\_gcs\_bucket
type: dict
mandatory
| Name of the GCS bucket containing the decrypted files. | |destination\_gcs\_path
type: array
mandatory
| Path in the GCS bucket containing the decrypted files, e.g. "some/sub/dir". | ## 🖥 VM parameters Information related to the Google Cloud Compute Engine VM where the script will be executed. | Parameter | Description | | ----------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |vm\_delete
type: string
mandatory
|If set to "true", this parameter will force the deletion of the VM at the end of the data operation. Running Compute Engine VMs will incur extra costs, so it is recommended to leave this parameter on "true".
Default value: true
| |vm\_core\_number
type: string
mandatory
|Virtual CPU (vCPU) count.
It is recommended to leave the default parameter, as this should allow sufficient performance to run a standard script.
Default value: 2
| |vm\_memory\_amount
type: string
mandatory
|System memory size (in GB).
It is recommended to leave the default parameter, as this should allow sufficient performance to run a standard script.
Default value: 4
| |vm\_disk\_size
type: string
mandatory
|Persistent disk size (in GB).
It is recommended to leave the default parameter, as this should provide enough space to store the data to process.
Default value: 20
| ## 🔐 Credential parameters | Parameter | Description | | -------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------- | |credentials
type:array
mandatory
|Array containing three entities:
input credentials for the input bucket,
output credentials for the output bucket,
and the private PGP key.
impact
type: integer
optional
|'Impact' is an ITIL measure of the extent of the Incident and of the potential damage caused by the Incident before it can be resolved. Learn more
If not specified, the default value will be 2.
| |urgency
type: integer
optional
|'Urgency' is a measure of how quickly a resolution of the Incident is required. Learn more
If not specified, the default value will be 2.
| |alert\_enabled
type: boolean
mandatory
| Flag used to enable/disable the execution of the alerting (i.e. send an alert to a recipient when the run failed) | |alert\_status (beta)
type: array
optional
|Specifies the Run Status that will trigger the alert.
Possible values: FAILED, SUCCESS, NO\_MATCH, CHECKED.
Default value: "FAILED"
alert\_environment
type: array
mandatory
|Specifies the environments that will trigger the alert.
Possible values: PROD, PREPROD, STAGING, DEV.
| |alert\_info
type: string
optional
|Short information describing the alert.
You can refer it as a variable in your triggering message (as email) with the @alert\_info parameter.
alert
type: array of maps
optional
|List of alert messages the data operation will trigger if it fails.
Check the section below for detailed information on their parameters.
| ## :warning: Alert parameters An alert will be able to trigger different types of messages. Currently, only an email alert can be sent. For each alert message, parameters will differ depending on the message type. ### :incoming\_envelope: Email alert An email alert will send an email with specific parameters each time the data operation fails in the specified environments. By default, Tailer provides an email template with detailed information. Thus, you don't have to fill in all the parameters such as subject, body, etc. But you can also personalize all the parameters to set precisely how the alert email should look like. For that, you can use the alert variables described below. | Parameter | Description | | --------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |email\_from
type: string
optional
|Surcharge the "email from" attribute.
Default value: "
email\_to
type: string
mandatory
|List of email recipients
You can specify more than one recipient by separating the email addresses with a ;
example: "
email\_reply\_to
type: string
optional
|Surcharge the "email reply to" attribute.
Default value: "
email\_subject
type: string
optional
|Subject of the triggered alert email.
Default value: "TAILER RUN ALERT: @jobid FAILED at @execution\_date"
You can personalize the subject with the alert variables described below.
| |email\_body\_type
type: string
optional
|Format type of the email body.
Default value: "html"
Other possible value: "txt"
| |email\_body
type: string
optional
|Body of the alert email.
Default value: "See attached html file below"
You can personalize the body with the alert variables described below. Be careful to use the right body format according to the email\_body\_type parameter.
| ### :jigsaw: Alert message variables Alert messages can be personalized with alert message variables. Those variables are contextualized during the data operation run. | Variable | Description | | -------------------------------------- | ------------------------------------------------------------ | | **@url\_to\_tailer\_studio\_run\_id** | Tailer Studio URL for the run | | **@url\_to\_tailer\_studio\_conf\_id** | Tailer Studio URL for the run current configuration ID | | **@account** | Account of the data operation's run | | **@environment** | Environment of the data operation's run | | **@status** | Status of the data operation's run | | @**configuration\_id** | Configuration ID of the data operation | | **@configuration\_type** | Configuration type of the data operation's run | | **@execution\_date** | Execution date of the data operation's run | | **@short\_description** | Short description of the data operation | | **@job\_id** | Job ID of the data operation | | **@run\_id** | Run ID of the data operation's run | | **@updated\_by** | Email address of the user who last updated the configuration | | **@update\_date** | Date tile of the last configuration update | | **@impact** | Monitoring impact level parameter | | **@urgency** | Monitoring urgency level parameter | | **@alert\_info** | Alert info set in the monitoring parameter | # Asserting Data quality with Expectations This is the guide page to Fashion Data Expectation stored procedures. ## :map: A short introduction to “Expectations” What are expectations? A set of SQL Stored Procedures that ease the writing of tests. Too often, writing tests in SQL requires assertions that are complex to write, that are not factorized. In a complex project, those assertions are spread amongst many projects and scripts and this can be a tedious tasks to maintain them. Fashion Data Expectations are a way to solve these problems: * a set of stored procedures that manage a large number of common assertions (check for primary key, check for integrity constraints, regular expression, etc.) * one liners that are still part of the SQL ecosystem, so they live with your SQL code and your Tailer configurations and can benefit from classic SQL syntax. * fast execution with parallel processing. * full assertion metrics including number of rejected lines, assertion processing time, timestamping, metrics history, etc. Let’s say that you imported data into a BigQuery project and you want to check for a primary key constraint with a certain threshold. In SQL you would write something like that: ```sql ASSERT (( (select count(distinct PK_products) from `dlk-demo.dlk_demo_pda.products`) - (select count(*) from `dlk-demo.dlk_demo_pda.products`) ) = 0 ) as "pk issue with table dlk-demo.dlk_demo_pda.products"; ``` With Fashion Data Expectations, you just write: ```sql CALL `tailer-ai.expect.primarykey_named`('dlk-demo.dlk_demo_pda', 'products', 'PK_products', 0); ``` ## :airplane\_departure: Getting started ### Launching Expectation in the BigQuery console You can launch an expectation directly from your BigQuery console and check your test. If the call is properly formed, BigQuery will launch the jobs described in the procedure and you will see the test status and the related metrics in the result of the last job. This eases the developpement of a set of expectations and can also be useful for ensuring adhoc quality of an element. ```sql -- Expectations have usually the following format -- CALL `tailer-ai.expect.EXPECTATION`('PROJECT_ID.DATASET_ID', 'TABLE_ID', SOME_PARAMETERS); CALL `tailer-ai.expect.table_count_greater`('dlk-demo.dlk_demo_pda', 'products', 10000, 0); ``` {% hint style="warning" %} In your expectation call, always specify the name of the project, otherwise the expectation will search for your table in “tailer-ai” (and will fail) instead of wherever your data is. {% endhint %} {% hint style="danger" %} **You need to request access** to the tailer-ai project **from your Tailer Platform administrator** before beeing able to call these expectations. {% endhint %} ### :gear: Creating an Expectation in a Tailer Table to Table configuration To create an expectation, you need two elements: * a dedicated task in a table-to-table configuration * a dedicated SQL file The dedicated task must be of type “expectation”. For example: ```json { "id": "expects_tables", "task_type": "expectation", "short_description": "Check for data integrity (pk, count, dates,...).", "doc_md": "000001_load_PDA_products.md", "sql_file": "000001_load_PDA_products_expects_r7.sql", "criticality": "warning" } ``` In your SQL file, you can add as much expectations as you want: ```sql -- assert count greater than 0 CALL `tailer-ai.expect.table_count_greater`('dlk-demo.dlk_demo_pda', 'products', 100000, 0); -- assert primary key is ok CALL `tailer-ai.expect.primarykey`('dlk-demo.dlk_demo_pda', 'products', 0); -- assert freshness on the final table (we want to have at least 10k products for today iteration) CALL `tailer-ai.expect.values_to_contain`('dlk-demo.dlk_demo_pda', 'products', 'max_importdate', cast(current_date() as string), 10000, 0); -- assert freshness on the psa table (we want to have at least 10k product for today psa) CALL `tailer-ai.expect.table_count_greater`('dlk-demo.dlk_demo_psa', concat('products_', replace(cast(current_date() as string), '-', '')), 100000, 0); -- assert freshness on the psa table for yesterday(we want to have at least 10k product for yesterday psa) CALL `tailer-ai.expect.table_count_greater`('dlk-demo.dlk_demo_psa', concat('products_', replace(cast(date_sub(current_date(), interval 1 day) as string), '-', '')), 100000, 0); ``` Your call to a stored procedure will be treated as a SQL instruction. This allows writing great expectations with powerful features. For example, doing “CONCAT” or using “DATE\_SUB” or “CURRENT\_DATE” enable counting with a sliding window on a specific table: ```sql -- Check line count for the table products_YYYYMMDD where YYYYMMDD is yesterday's date CALL `tailer-ai.expect.table_count_greater`( 'dlk-demo.dlk_demo_psa', concat('products_', replace(cast(date_sub(current_date(), interval 1 day) as string), '-', '')), 100000, 0 ); ``` {% hint style="info" %} In your SQL expectation file, only expectations will be executed. Classic SQL commands or comments will be ignored. {% endhint %} ## 💡 Analyzing raw metrics Everytime an expectation embedded in a table-to-table data operation is executed, it generates some metrics that are added to the tailer\_common.expectation\_results table in your project. For example: ```sql SELECT * FROM `dlk-demo.tailer_common.expectation_results` LIMIT 1000 ``` Here are the fields of this: | Field Name | Type | Description | | ------------------- | ------ | --------------------------------------- | | job\_id | STRING | Identifier of the Job | | dag\_id | STRING | Identifier of the Direct Acyclic Graph | | account | STRING | Account Name | | environment | STRING | Execution Environnement (DEV, PROD,...) | | run\_id | STRING | Identifier of the Execution | | configuration\_type | STRING | Configuration Type | | configuration\_id | STRING | Identifier of the Configuration | | task\_id | STRING | Identifier of the Task | | execution\_date | STRING | Execution Date | | criticality | STRING | Task Criticality | | expectation\_result | STRING | Expectation Result | The field called “expectation\_result” contains additionnal informations about the expectation in JSON format. The generic output is defined here, and some specific results could be added for some expectations (see details in the [list of expectations](/data-pipeline-operations/expectations/list-of-expectations)): ```json { "dataset": "dlk-demo.dlk_demo_pda", "tablename": "products", "column_name": "", "procedure_name": "table_count_greater", "date_count": "2021-12-03T14:02:51.284016", "all_count": 158357, "target_dataset": "", "target_table_name": "", "target_value": [ "100000" ], "reject_count": 58357, "reject_threshold": 0.01, "passed": false, "start_date": "1642411639", "end_date": "1642411644" } ``` {% hint style="info" %} The metrics of a test launched from the BigQuery console won't be inserted into the metrics table. Only the results of the tests embedded into a table-to-table configuration will be stored. {% endhint %} ## :desktop: Tailer Studio integration You can find an Expectations Overview in Tailer Studio. Click in the left pannel on "Expectations Overview" in the "Data Quality" section and see all the expectations that has been recently tested.  ## **📋** A list of available Expectations A complete documentation of all avaibable expectations and their specific parameters is available on the next page: [List of Expectations](/data-pipeline-operations/expectations/list-of-expectations). ## 🌍 Multiple regions {% hint style="info" %} If you need expectations in a specific BigQuery location where they are not already available, please feel free to contact us! {% endhint %} The first set of expectations is located in region EU. So this call will work if you apply it to a BigQuery table located in EU: ```sql CALL `tailer-ai.expect.primarykey`('my_project.my_dataset_EU', 'products', 'PK_products', 0); ``` We've also released expectations on a few other region, each in a specific dataset. For example, you can use primarykey on a table in europe-west1 region using excpect\_euw1: ```sql CALL `tailer-ai.expect_euw1.primarykey`('my_project.my_dataset_europe_west1', 'products', 'PK_products', 0); ``` # List of Expectations ### Fashion Data Expectations * Key constraints * [primarykey\_named](#primarykey_named-dataset-tablename-column-threshold) * [primarykey](#primarykey-dataset-tablename-threshold) * [primarykey\_where](#primarykey_where-dataset-tablename-threshold-where_clause) * [foreignkey](#foreignkey-dataset-tablename-column-target_dataset-target_tablename-target_column-threshold) * [foreignkey\_where](#foreignkey_where-dataset-tablename-column-filter_condition-target_dataset-target_tablename-target_co) * Temporal continuity * [everyday\_since](#everyday_since-dataset-tablename-column-start_date-exception-minimum) * [everyday\_increasing\_since](#everyday_increasing_since-dataset-tablename-value) * [everyweek\_since](#everyweek_since-dataset-tablename-column-start_date-exception-minimum) * [everymonth\_since](#everymonth_since-dataset-tablename-column-start_date-exception-minimum) * Row count * [table\_count\_greater](#table_count_greater-dataset-tablename-value) * [table\_count\_between](#table_count_between-dataset-tablename-value) * [table\_count\_equal](#table_count_equal-dataset-tablename-value) * [table\_count\_equal\_other\_table](#table_count_equal_other_table-dataset-tablename-target_dataset-target_tablename-threshold) * Column properties * [unique](#unique-dataset-tablename-column) * [not\_null](#not_null-dataset-tablename-column-threshold) * [null](#null-dataset-tablename-column-threshold) * [type](#type-dataset-tablename-column-type) * [values\_to\_contain](#values_to_contain-dataset-tablename-column-value-minimum-threshold) * [values\_to\_be\_between](#values_to_be_between-dataset-tablename-column-value-threshold) * [values\_to\_be\_in\_set](#values_to_be_in_set-dataset-tablename-column-value-threshold) * [values\_to\_not\_be\_in\_set](#values_to_not_be_in_set-dataset-tablename-column-value-threshold) ### Key constraints #### primarykey\_named(dataset, tablename, column, threshold) ```sql CALL `tailer-ai.expect.primarykey_named`('my-project.my_dataset', 'sales_details', 'PK_sales_details', 0); CALL `tailer-ai.expect.primarykey_named`('my-project.my_dataset', 'sales_details', 'CONCAT(ticket_id, "-", line_number)', 0.0001); ``` Expect a column in a table to respect a pseudo Primary Key constraint. This procedure checks that every value of the specified column is not null and unique within the current table. This is enforced by counting the total number of rows within the table and comparing it to the number of distinct element in the column. A threshold percentage can be provided, so the test is passed if the number of rejected rows divided by the table total row count represents less than the threshold. Use 0 if no rejected row is allowed. * **Parameters** * **dataset** (*STRING*) – The dataset of the table (and its GCP project) * **tablename** (*STRING*) – The table name * **column** (*STRING*) – The column name, or an sql operation that creates a pseudo column that can be inserted into a count distinct * **threshold** (*FLOAT64*) – The threshold to use to trigger an assertion failure * **Returns** The last SQL job of the procedure returns a line as described [here](/data-pipeline-operations/expectations#analyzing-raw-metrics). When this expectation is embedded in a table-to-table data operation, then this line is inserted into the tailer\_common.expectation\_results table in your GCP project. #### primarykey(dataset, tablename, threshold) ```sql CALL `tailer-ai.expect.primarykey`('my-project.my_dataset', 'sales_details', 0); ``` Expect a table to have a column that respects a pseudo Primary Key constraint. This procedure looks for a column with a name that starts wiht 'PK' or with a desdcription that contains '#PK'. Then it checks that every value of this column is not null and unique within the current table. This is enforced by counting the total number of rows within the table and comparing it to the number of distinct element in the column. A threshold percentage can be provided, so the test is passed if the number of rejected rows divided by the table total row count represents less than the threshold. Use 0 if no rejected row is allowed. * **Parameters** * **dataset** (*STRING*) – The dataset of the table (and its GCP project) * **tablename** (*STRING*) – The table name * **threshold** (*FLOAT64*) – The threshold to use to trigger an assertion failure * **Returns** The last SQL job of the procedure returns a line as described [here](/data-pipeline-operations/expectations#analyzing-raw-metrics). When this expectation is embedded in a table-to-table data operation, then this line is inserted into the tailer\_common.expectation\_results table in your GCP project. #### primarykey\_where(dataset, tablename, threshold, where\_clause) ```sql CALL `tailer-ai.expect.primarykey_where`('my-project.my_dataset', 'stocks', 0, 'WHERE date >= current_date - 7'); ``` Expect a table to have a column that respects a pseudo Primary Key constraint. Apply a WHERE condition before testing the primary key constraint to limit the number of rows requested. The test performed is the as with the primarykey expectation. See more detail above. **Parameters** * **Parameters** * **dataset** (*STRING*) – The dataset of the table (and its GCP project) * **tablename** (*STRING*) – The table name * **threshold** (*FLOAT64*) – The threshold to use to trigger an assertion failure * **where\_clause** (*STRING*) – A proper WHERE clause that will filter the table before applying the test. * **Returns** The last SQL job of the procedure returns a line as described [here](/data-pipeline-operations/expectations#analyzing-raw-metrics). When this expectation is embedded in a table-to-table data operation, then this line is inserted into the tailer\_common.expectation\_results table in your GCP project. #### foreignkey(dataset, tablename, column, target\_dataset, target\_tablename, target\_column, threshold) ```sql CALL `tailer-ai.expect.foreignkey`('my-project.my_dataset', 'sales_details', 'customer_id', 'my-project.my_dataset', 'customers', 'customer_id', 0.001); ``` Expect a column in a table to respect a pseudo Foreign Key constraint. This procedure checks that every non-null value in the column can be found in the values of the target column of the reference target table. A threshold percentage can be provided, so the test is passed if the number of rejected rows divided by the table total row count is less than the threshold. Use 0 if no rejected row is allowed. * **Parameters** * **dataset** (*STRING*) – The dataset of the table (and its GCP project) * **tablename** (*STRING*) – The table name * **column** (*STRING*) – The column name * **target\_dataset** (*STRING*) – The target dataset of the foreign table (and its GCP project) * **target\_tablename** (*STRING*) – The foreign table name * **target\_column** (*STRING*) – The foreign key column of the foreign table * **threshold** (*FLOAT64*) – The threshold to use to trigger an assertion failure * **Returns** The last SQL job of the procedure returns a line as described [here](/data-pipeline-operations/expectations#analyzing-raw-metrics). When this expectation is embedded in a table-to-table data operation, then this line is inserted into the tailer\_common.expectation\_results table in your GCP project. #### foreignkey\_where(dataset, tablename, column, filter\_condition, target\_dataset, target\_tablename, target\_column, threshold) ```sql CALL `tailer-ai.expect.foreignkey`('my-project.my_dataset', 'sales_details', 'customer_id', 'and SALE_DATE >= current_date - 90', 'my-project.my_dataset', 'customers', 'customer_id', 0.001); ``` Expect a column in a table to respect a pseudo Foreign Key constraint. Add a filter condition to the WHERE clause before testing the foreign key constraint to limit the number of rows requested. This procedure checks that every non-null value in the column can be found in the values of the target column of the reference target table. A threshold percentage can be provided, so the test is passed if the number of rejected rows divided by the table total row count is less than the threshold. Use 0 if no rejected row is allowed. * **Parameters** * **dataset** (*STRING*) – The dataset of the table (and its GCP project) * **tablename** (*STRING*) – The table name * **column** (*STRING*) – The column name * **filter\_condition** (*STRING*) – The filter condition that will be applied on the table to check * **target\_dataset** (*STRING*) – The target dataset of the foreign table (and its GCP project) * **target\_tablename** (*STRING*) – The foreign table name * **target\_column** (*STRING*) – The foreign key column of the foreign table * **threshold** (*FLOAT64*) – The threshold to use to trigger an assertion failure * **Returns** The last SQL job of the procedure returns a line as described [here](/data-pipeline-operations/expectations#analyzing-raw-metrics). When this expectation is embedded in a table-to-table data operation, then this line is inserted into the tailer\_common.expectation\_results table in your GCP project. ### Temporal continuity #### everyday\_since(dataset, tablename, column, start\_date, exception, minimum) ```sql CALL `tailer-ai.expect.everyday_since`('my-project.my_dataset', 'sales_details', 'sale_date', DATE_SUB(current_date, interval 31 day), ['2022-01-01', '2021-12-25', cast(current_date as string)], 1000); ``` Expect a table to have a minimum number of rows per day since a start date. An exception list can be provided to avoid an error when a date has no data for a good reason. This procedure counts the number of rows of the specified table grouped by date. If a day between the specified start\_date and today is missing, or if a daily count is below minimum, then the test fails, except if the date is specified in the exception list. * **Parameters** * **dataset** (*STRING*) – The dataset of the table (and its GCP project) * **tablename** (*STRING*) – The table name * **column** (*STRING*) – The column name * **start\_date** (*STRING*) – The date to start the control * **exception** (*ARRAY\$schema
type: string
optional
| The url of the json-schema that contains the properties that your configuration must verify. Most Code Editor can use that to validate your configuration, display help boxes and enlighten issues. | |configuration\_type
type: string
mandatory
|Type of data operation.
For an File Utilities data operation, the value is always "file-utilities"
| |configuration\_id
type: string
mandatory
|ID of the data operation.
You can pick any name you want, but is has to be unique for this data operation type.
Note that in case of conflict, the newly deployed data operation will overwrite the previous one.
To guarantee its uniqueness, the best practice is to include in your data operation name:
- your account ID
- the source bucket
- the source path
environment
type: string
mandatory
|Deployment context.
Values: PROD, PREPROD, STAGING, DEV.
| |account
type: string
mandatory
| Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator. | |activated
type: boolean
optional
|Flag used to enable/disable the execution of the data operation.
Default value: true
| |archived
type: boolean
optional
|Flag used to enable/disable the visibility of the data operation's configuration and runs in Tailer Studio.
Default value: false
| |doc\_md
type: string
optional
| Path to a file containing a detailed description of the data operation. The file must be in Markdown format. | |version
type: string
mandatory
| Use only version 2, version 1 is depreciated. | |gcp\_project\_id
type: string
mandatory
|Set the project where deploy the configuration and the associated cloud functions.
If not set, the user will be prompted to choose a project id.
| |gcs\_bucket
type: string
mandatory
| Name of the bucket. | |gcs\_path
type: string
mandatory
| Path where the files will be found, e.g. "some/sub/dir". | |gcs\_destination\_suffix
type: string
mandatory
| Google Cloud Storage destination path, e.g. "/subdir/subdir\_2" to send the files to "gs\://BUCKET/subdir/subdir\_2/source\_file.ext" | |launch\_mode
type: string
mandatory
| Choice of triggering system. Choose "gcs" to trigger the operation on file creation on a bucket. Futur modes will be implemented. | |filename\_templates
type: string
mandatory
|List of filename templates that will be processed.
You can set the value to "\*" for all files to be copied. However, this is not recommended, as unnecessary or sensitive files might be included by mistake. Besides, the date value specified in filename\_template will be used to sort files in the archive folder. If no date value is specified, all files will be stored together under one folder named /ALL.
The best practice is to specify one or more filename templates with the filename\_template and file\_description parameters as described in the next paragraphe.
| |task\_dependencies
type: array of strings
mandatory
|The task\_dependencies parameter allows you to create dependencies between the different tasks specified in the workflow parameter (see below). It will define in which order the workflow tasks will run, some of them running concurrently, others sequentially.
Syntax
- The double chevron
>>means that the first task needs to be completed before the next one can start. - The comma
,means that the tasks will run concurrently. - The square brackets
\[and]allow you to define a set of tasks that will run together.
For detailed information about the syntax, refer to the Airflow documentation.
Example 1
We have the following tasks that we want to run sequentially: taskA (create\_gbq\_table), taskB (sql) and taskC (copy\_gbq\_table).
The task\_dependencies parameter will be as follows: "task\_dependencies": \[" taskA >> taskB >> taskC "],
Example 2
We have the following tasks that we want to run concurrently: taskA, taskB and taskC.
The task\_dependencies parameter will be as follows: "task\_dependencies": \[" taskA, taskB, taskC "],
Example 3
We have the following 9 tasks we want to order: taskA, taskD, taskG (create\_gbq\_table), taskB, taskE, taskH (sql), taskC, taskF, taskI (copy\_gbq\_table).
The task\_dependencies parameter will be as follows:"task\_dependencies": \[" \[taskA, taskD, taskG] >> \[taskB, taskE, taskH] >> \[taskC, taskF, taskI] "],
Example 4
In the example above, we want taskH to run before taskE so we can use its result for taskE.
The task\_dependencies parameter will be as follows:
"task\_dependencies": \[" \[taskA, taskD, taskG] >> taskH >> \[taskB, taskE] >> \[taskC, taskF, taskI] "],
credentials
type:array
mandatory
|Encrypted credentials needed to read/write data from the source bucket.
You should have generated credentials when setting up GCP. To learn how to encrypt them, refer to this page.
| ## :desktop: PGP encrypt task parameters Information related to the Google Cloud Compute Engine VM where the script will be executed. | Parameter | Description | | ---------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |task\_id
type: string
mandatory
| ID of the task. It must be unique within the data operation. | |task\_type
type: string
mandatory
| The value has to be set to "pgp" for this task type. | |pgp\_mode
type: string
mandatory
|PGP mode.
For data encryption, the value is always "encrypt".
| |public\_key.pgp
type:array
mandatory in encrypt mode
|Encrypted public key. This array contains two entities:
- the recipient "username" of the public key
- the content "schema" credentials after passing it through tailer encrypt.
configuration\_type
type: string
mandatory
|Type of data operation.
For an File Utilities data operation, the value is always "file-utilities"
| |configuration\_id
type: string
mandatory
|ID of the data operation.
You can pick any name you want, but is has to be unique for this data operation type.
Note that in case of conflict, the newly deployed data operation will overwrite the previous one.
To guarantee its uniqueness, the best practice is to include in your data operation name:
- your account ID
- the source bucket
- the source path
environment
type: string
mandatory
|Deployment context.
Values: PROD, PREPROD, STAGING, DEV.
| |account
type: string
mandatory
| Your account ID is a 6-digit number assigned to you by your Tailer Platform administrator. | |activated
type: boolean
optional
|Flag used to enable/disable the execution of the data operation.
Default value: true
| |archived
type: boolean
optional
|Flag used to enable/disable the visibility of the data operation's configuration and runs in Tailer Studio.
Default value: false
| |doc\_md
type: string
optional
| Path to a file containing a detailed description of the data operation. The file must be in Markdown format. | |version
type: string
optional
| Enter 2 is you are using context & environment and 1 if not. | |gcp\_project\_id
type: string
mandatory
|Set the project where deploy the configuration and the associated cloud functions.
If not set, the user will be prompted to choose a project id.
| |gcs\_bucket
type: string
mandatory
| Name of the bucket. | |gcs\_path
type: string
mandatory
| Path where the files will be found, e.g. "some/sub/dir". | |gcs\_destination\_suffix
type: string
mandatory
| Google Cloud Storage destination path, e.g. "/subdir/subdir\_2" to send the files to "gs\://BUCKET/subdir/subdir\_2/source\_file.ext" | |launch\_mode
type: string
mandatory
| Choice of triggering system. Choose "gcs" to trigger the operation on file creation on a bucket. Futur modes will be implemented. | |filename\_templates
type: string
mandatory
|List of filename templates that will be processed.
You can set the value to "\*" for all files to be copied. However, this is not recommended, as unnecessary or sensitive files might be included by mistake. Besides, the date value specified in filename\_template will be used to sort files in the archive folder. If no date value is specified, all files will be stored together under one folder named /ALL.
The best practice is to specify one or more filename templates with the filename\_template and file\_description parameters as described in the next paragraphe.
| |task\_dependencies
type: array of strings
mandatory
|The task\_dependencies parameter allows you to create dependencies between the different tasks specified in the workflow parameter (see below). It will define in which order the workflow tasks will run, some of them running concurrently, others sequentially.
Syntax
- The double chevron
>>means that the first task needs to be completed before the next one can start. - The comma
,means that the tasks will run concurrently. - The square brackets
\[and]allow you to define a set of tasks that will run together.
For detailed information about the syntax, refer to the Airflow documentation.
Example 1
We have the following tasks that we want to run sequentially: taskA (create\_gbq\_table), taskB (sql) and taskC (copy\_gbq\_table).
The task\_dependencies parameter will be as follows: "task\_dependencies": \[" taskA >> taskB >> taskC "],
Example 2
We have the following tasks that we want to run concurrently: taskA, taskB and taskC.
The task\_dependencies parameter will be as follows: "task\_dependencies": \[" taskA, taskB, taskC "],
Example 3
We have the following 9 tasks we want to order: taskA, taskD, taskG (create\_gbq\_table), taskB, taskE, taskH (sql), taskC, taskF, taskI (copy\_gbq\_table).
The task\_dependencies parameter will be as follows:"task\_dependencies": \[" \[taskA, taskD, taskG] >> \[taskB, taskE, taskH] >> \[taskC, taskF, taskI] "],
Example 4
In the example above, we want taskH to run before taskE so we can use its result for taskE.
The task\_dependencies parameter will be as follows:
"task\_dependencies": \[" \[taskA, taskD, taskG] >> taskH >> \[taskB, taskE] >> \[taskC, taskF, taskI] "],
credentials
type:array
mandatory
|Encrypted credentials needed to read/move data from the source bucket.
You should have generated credentials when setting up GCP. To learn how to encrypt them, refer to this page.
| ## :desktop: PGP decrypt task parameters Information related to the Google Cloud Compute Engine VM where the script will be executed. | Parameter | Description | | ------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |task\_id
type: string
mandatory
| ID of the task. It must be unique within the data operation. | |task\_type
type: string
mandatory
| The value has to be set to "pgp" for this task type. | |pgp\_mode
type: string
mandatory
|PGP mode.
For data decryption, the value is always "decrypt".
| |private\_key.pgp
type:array
mandatory
|Encrypted private key. This array contains three entities:
- the passphrase "password" of the private key
- the recipient "username" of the private key
- the content "schema" credentials after passing it through tailer encrypt.
| Column | Descriptions |
|---|---|
timestamp type: timestamp optional | For your different use cases, it can be interesting to have the last calculation date of your dataset to Firestore. This column is optional but recommanded. |
firestore_path type: string mandatory | Variable read by the Python code to build the target path of documents and collections in Firestore. Each category and sub-category must be separated by a pipe "|". ⚠ Remember to remove the "|" that could be in the variables that are used as path names, or it would be interpreted as a category separator! ⚠ The Firestore path is a succession of collections and documents. You must at all costs end up on a collection of documents. The defined path must therefore contain an even number of categories and sub-categories. See a screenshot of an exemple below. |
other variables type: string optional | The other variables are the ones you want to display in your firestore document. You can define as many variable as you like, as described in the first example below. We'll even see below how to create sub categories with the second example. |
| Variables | Descriptions |
|---|---|
data type: struct of string or struct of struct of string mandatory | Allows you to create a list of sub-elements that correspond N times to the element (for example for a product, you can create a list of sales sorted by date) |
Name of the python file to execute.
It must be uploaded in the working directory of the GCS bucket defined in the vm-launcher configuration file before the first execution.
You can modify the Python script (provided in the next page) and rename it if you like.
Python script first argument:
The relative path, in the working directory, of the folder that contains the data files.
You can specify sub-directories in the next argument.
'{"next\_execution":
{
"sub\_dir":"next\_execution",
"file\_template":"freshness\_next\_execution\_data-\*.json"}
}'
|Python script second argument:
This is a stringified list of json objects that contain informations on the files to process. Please note that you must escape the double quote characters with a backslash.
In this example, there's only one use-case, named "next\_execution", but you could have several of them. For each use-case, you need to specify a "sub\_dir" and a "file\_template":
- sub\_dir is the relative path of the sub directory where the data files are stored. Here it's "next\_execution".
- file\_template is the template of the names of your data files. It can contain a wildcard "\*" that stands for a set of any characters. In our example, this wildcard handles the fact that we could have several data files named for example freshness\_next\_execution\_data-00000000.json and reshness\_next\_execution\_data-00000001.json.
See the screenshots of the bucket and files below.
| ## :eye\_in\_speech\_bubble: Example Here is an example of VM Launcher configuration file that loads data from the tailer-freshness Google Cloud Storage Bucket. The Firestore destination will be specified inside the Python script (see next page). ```json { "configuration_type": "vm-launcher", "configuration_id": "000001_json_to_firestore_freshness_next_execution", "environment": "DEV", "account": "000099", "activated": true, "archived": false, "direct_execution": true, "gcp_project_id": "my-project", "gcs_bucket": "my-bucket", "gcs_working_directory": "gbq-to-firestore", "credentials": { "gcp-credentials.json": { "content": { "cipher_aes": "xxx", "tag": "xxx", "ciphertext": "xxx", "enc_session_key": "xxx" } } }, "script_to_execute": [ "pip3 install google-cloud-firestore simplejson pytz", "python3 file-to-firestore.py ./000001 '{\"next_execution\":{\"sub_dir\":\"next_execution\",\"file_template\":\"freshness_next_execution_data-*.json\"}}'" ], "vm_delete": true, "vm_core_number": "2", "vm_memory_amount": "4", "vm_disk_size": "20", "vm_compute_zone": "europe-west1-b", "vm_custom_os_image_family": "ubuntu-2004-lts", "vm_custom_os_image_project": "ubuntu-os-cloud" } ``` This configuration uses the gbq-to-firestore directory of the tailer-freshness GCS bucket as a working directory. You can see the Python file and the data directory are located here:  Insead the data directory, you find data files as specified:  # File-to-firestore python file This is the description of the SQL file used for a Table to Storage data operation. To run a GBQ to Firestore data operation, you need a precise python script like this one, used for the example. ## :eye\_in\_speech\_bubble: Example of file-to-firestore.py : ```python import argparse import os import fnmatch import datetime import pytz import simplejson as json from google.cloud import firestore if __name__ == "__main__": parser = argparse.ArgumentParser( description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter) parser.add_argument("source_gcs", help="GCS Bucket.", type=str) parser.add_argument("items", help="GCS Bucket.", type=str) args = parser.parse_args() source_directory = args.source_gcs.strip().strip("/") # Process items # files_to_process_infos = json.loads(args.items) print("GCS source : {}".format(source_directory)) print("Items to process : {}".format(files_to_process_infos)) fs_client = firestore.Client(project="project_id") batch = firestore.WriteBatch(client=fs_client) for item_to_process in files_to_process_infos.keys(): print("\nProcessing : {}".format(item_to_process)) for root, dirs, files in os.walk(source_directory + "/" + files_to_process_infos[item_to_process]["sub_dir"]): for filename in fnmatch.filter(files, files_to_process_infos[item_to_process]["file_template"]): full_filename = source_directory + "/" + files_to_process_infos[item_to_process]["sub_dir"] + "/" + filename with open(full_filename, "r", encoding="utf-8") as input_file: print("Processing file {}".format(full_filename)) batch_index = 1 total_writes = 0 payload = {} for line in input_file.readlines(): payload = json.loads(line) payload["update_time"] = datetime.datetime.now(pytz.timezone("UTC")) # build document path # doc_ref = None for fs_path_index, fs_item_path in enumerate(payload["firestore_path"].split("|")): # First pass to instantiate object # if fs_path_index == 0: doc_ref = fs_client.collection(fs_item_path) continue # Add collection or document # if fs_path_index % 2 == 0: # collection # doc_ref = doc_ref.collection(fs_item_path) else: # document # doc_ref = doc_ref.document(fs_item_path) if batch_index >= 499: total_writes += batch_index batch.commit() batch_index = 1 print(total_writes) else: batch_index += 1 del payload["firestore_path"] batch.set(doc_ref, payload, merge=True) # Final commit # batch.commit() total_writes += batch_index print(total_writes) ``` ## :globe\_with\_meridians: Global parameters **To use the python deployment automation on Firestore, the SQL must follow a specific pattern.**| Variables | Descriptions |
|---|---|
project_id line 30 mandatory | For your different use cases, it can be interesting to have the last calculation date of your dataset to Firestore. |
| Information | Description |
|---|---|
Configuration Type Sorting option | Type of data operation.
|
| Job ID | ID of the job linked to the execution of a configuration. A job ID is the concatenation of data operation, ID of the configuration and optionally the environnement and the file used or produced depending of the data operation. This ID is unique and linked to the page of the configuration related. |
| Last Execution | Date time (UTC+0) of the last execution of your data operation. |
| Next Execution | Date time (UTC+0) of the next execution of your data operation. This is one of our main objectives. It estimates from previous executions the next time it will come. ⚠️The more regular the executions, the more precise it will be. Conversely, if there are few executions, or some that come irregularly, the calculation will be less accurate. |
| Confidence | It's an indicator about the arrival confidence interval. The value is correlated with the potential number of hours of delay for the data operation : the higher the number is, the more likely the operation is to be late.
|
| Frequency Sorting option | Type of frequency detected with cron schedule expressions or with frequency calculation. ex: Daily, Weekly, Monthly Yearly and Unfollow. Yearly is about a data operation with very few regular executions with long waiting times Unfollow is about data operation with less than 2 regular executions in production. |
Status Sorting option | Freshness status of the data operation for the current day.
|
Timing Sorting option | Freshness timing of the data operation for the current day.
|
