Functions

Flycs lets you define BigQuery functions. These functions will be created in BigQuery during the CI/CD pipeline and usable in your transformations.

Note

Javascript functions using external code libraries are not supported.

Example definition of a javascript UDF function called “func_decode_html” in YAML:

QUERY: |
    if (name === null) {
    return null
    }
    name=name.split('&').join('&');
    name=name.split(''').join("'");
    name=name.split('é').join("é");
    name=name.split('ô').join("ô");
    name=name.split('Ë').join("Ë");
    name=name.split('ï').join("ï");
    name=name.split('É').join("É");
    name=name.split('è').join("è");
    name=name.split('â').join("â");
    name=name.split('À').join("À");
    name=name.split('î').join("î");
    name=name.split('ü').join("ü");
    name=name.split('ê').join("ê");
    name=name.split('ë').join("ë");
    name=name.split('Ö').join("Ö");
    name=name.split('ç').join("ç");
    name=name.split('ö').join("ö");
    name=name.split('È').join("È");
    name=name.split('"').join("'");
    name=name.split('Ç').join("Ç");
    name=name.split('Ü').join("Ü");
    name=name.split('à').join("à");
    name=name.split('Ô').join("Ô");
    name=name.split('ä').join("ä");
    return name;

VERSION: 1.0.0
DESCRIPTION: "A function used to decode HTML entities"
KIND: function
LANGUAGE: javascript
ARGUMENT_LIST:
- NAME: name
  TYPE: STRING
RETURN_TYPE: STRING

Example definition of a SQL UDF function called “simple_mult_func” in YAML:

QUERY: |
  arg_IN * 2

VERSION: 1.0.0
DESCRIPTION: "A function used to multiply the input by 2"
KIND: function
ARGUMENT_LIST:
  - NAME: arg_IN
    TYPE: INTEGER
RETURN_TYPE: INTEGER

The functions definition must be created into a folder called functions in contrast with transformation that must be created into a queries folder. Here is an example tree from a flycs repository:

bigquery
└── demo
    ├── queries
    │   ├── datalake
    │   │   ├── history.yaml
    │   │   └── simple_copy.yaml
    │   ├── data_mart
    │   │   ├── salary.yaml
    │   │   └── simple_copy.yaml
    │   ├── data_warehouse
    │   │   ├── manipulating_pii_fields.yaml
    │   │   └── simple_copy.yaml
    │   └── staging
    │       ├── simple_copy.yaml
    │       └── time_test.yaml
    └── functions
    │   └── staging
            └── simple_mult_func.yaml
    │   └── data_warehouse
    │       └── func_decode_html.yaml
    └── views
        └── data_warehouse
            └── view_simple_copy.yaml

Example definition of the previous javascript function using python SDK:

from flycs_sdk.functions import Function, Argument

my_function = Function(
    name="func_decode_html",
    query="""
        if (name === null) {
        return null
        }
        name=name.split('&').join('&');
        name=name.split(''').join("'");
        name=name.split('é').join("é");
        name=name.split('ô').join("ô");
        name=name.split('Ë').join("Ë");
        name=name.split('ï').join("ï");
        name=name.split('É').join("É");
        name=name.split('è').join("è");
        name=name.split('â').join("â");
        name=name.split('À').join("À");
        name=name.split('î').join("î");
        name=name.split('ü').join("ü");
        name=name.split('ê').join("ê");
        name=name.split('ë').join("ë");
        name=name.split('Ö').join("Ö");
        name=name.split('ç').join("ç");
        name=name.split('ö').join("ö");
        name=name.split('È').join("È");
        name=name.split('"').join("'");
        name=name.split('Ç').join("Ç");
        name=name.split('Ü').join("Ü");
        name=name.split('à').join("à");
        name=name.split('Ô').join("Ô");
        name=name.split('ä').join("ä");
        return name;""",
    version="1.0.0",
    description="A function used to decode HTML entities",
    argument_list=[Argument(name="name", type="STRING")],
    return_type="STRING",
    language="javascript",
    destination_data_mart=None, # only required when creating a function in a data_mart project,
)

Example of a transformation that uses the function “simple_mult_func” defined in the staging stage :

QUERY: |
  SELECT val, self.staging.simple_mult_func(val) AS result
  FROM UNNEST([1,2,3,4]) AS val;

VERSION: 1.0.0

STATIC: true