=====
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:

.. code-block:: 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:

.. code-block:: 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:


.. code-block:: shell

    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:

.. code-block:: python

    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 :

.. code-block:: yaml

    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