ETL syntax
Management of ETL filter or SQL query strings¶
For every ETL management window that includes an ETL filter or an SQL query where dynamic syntax is allowed, parameters can be defined to set the execution filters of a routine or an ETL job.
A link to this page will be shown for the management windows of an ETL where an ETL filter or query can be inserted in SQL language.
List of parametric elements¶
A list of the available parametric elements is provided below:
| Parameter | Value returned in the execution step |
|---|---|
| {TABLE} | Staging table |
| {MAP} | ETL Domain code |
| {IMP} | Routine code |
| {JOB} | ETL job code |
| {USER} | Code of the user running the routine or ETL job |
| {PROCESS} | Data collection process code |
| {DATETIME} | Date and time when the routine or ETL job is run. Format: MMDDYYYYHHMMSSLLL (MM month, DD day, YYYY year, HH hours, MM minutes, SS seconds, LLL milliseconds) |
| {DATE} | Date when the routine or ETL job is run. Format: MMDDYYYY (MM month, DD day, YYYY year) |
| {TIME} | Time when the routine or ETL job is run. Format: HHMMSSLLL (HH hours, MM minutes, SS seconds, LLL milliseconds) |
| {DATE_YYYY} | The year (4 digits) when the routine or ETL job is run |
| {DATE_YY} | The year (2 digits) in which the Routine or Job ETL is run |
| {DATE_MM} | The month (2 digits) when the routine or ETL job is run |
| {DATE_DD} | The day (2 digits) when the routine or ETL job is run |
| {TIME_HH} | The time (2 digits) when the routine or ETL job is run |
| {TIME_MI} | The minute (2 digits) when the routine or ETL job is run |
| {TIME_SS} | The second (2 digits) when the routine or ETL job is run |
| {TIME_ML} | The millisecond (2 digits) when the routine or ETL job is run |
| {codice_parametro_ETl} | Parameter value. EXAMPLE: Suppose we have defined a parameter “PARAM_AZIENDA” whose type is “from imported data” which takes the value of the ETL Dimension and contains the entities for which the data are extracted (e.g. A01, A02, A03), you need only write COD_AZIENDA IN ({PARAM_AZIENDA}) because the system translates that instruction into COD_AZIENDA IN ('A01', 'A02', 'A03') |
| {sigla_dimensione_ETL} | The system determines, from the ETL dimensions table, the type of dimension with the indicated acronym. The system can also automatically convert the value contained in the dimension element to the same dimension type. EXAMPLE: if we have a number type ETL Dimension with the acronym {IMP1}, supposing we need to multiply the value of that dimension by -1, and considering that the staging table has all string type columns, you need only write {IMP1}*-1, instead of TO_NUMBER(IMP1) *-1 |
| {AW | [workspace code] |
| {AW | [workspace code] |
| {AW | [workspace code] |
| {AW | [workspace code] |
| {AW | [workspace code] |
Note: it is possible to use the syntax #{}# to prevent the system, when converting certain key words, from delimiting the value between single quotes.
For example, the syntax {PROCESS} is converted to 'BUGDET'; while the syntax #{PROCESS}# is converted to BUDGET.
This is useful, for example, when you want to use a parametric syntax as part of a table name or as a parameter in a stored procedure.
Note: It is also possible to use Unified SQL Syntax.
Warning¶
- Whenever an ETL Dimension needs to be referenced within an ETL filter or an SQL query, personal names must not be used. Instead, the field names of the staging table columns as defined in the Tagetik application database—such as CAMPO1, CAMPO2, etc.—must be used
- Whenever it is necessary to write a filter to apply to the staging table to refer to its fields, it is mandatory to use the names they have on the Tagetik application database, i.e. CAMPO1, CAMPO2 etc.
- Whenever it is necessary to write a filter to be applied to the table on which data is loaded to refer to its fields, it is mandatory to use the names they have on the database where the table is located
- It is not possible to use both characters '{' and '}' in a query in this order except to identify the parameters specified above.