Using Python 2 with Exasol 7.1.20 or later

Python 2 has reached end of life and is no longer supported in Exasol. This section describes a workaround method to continue to use Python 2.7 in Exasol 7.1.20 or later versions by using the old script language container. However, we recommend that you switch to using Python 3 in all UDFs.

These script language containers are provided as is and are not maintained anymore.

Pre-update Check

Run this script to output a list of the UDFs that likely use the built-in Python 2.

This script will detect most occurences of Python 2 usage in UDFs. We cannot guarantee that all occurences are detected. 

with
num_of_pairs as(
    SELECT
        p.system_value
        , length(p.system_value)-length(replace(p.system_value, '=')) as num_of_pairs
    FROM
        exa_parameters p
    WHERE
        1=1
        and p.parameter_name in ('SCRIPT_LANGUAGES')
)
, lang_pairs as(
    SELECT
        regexp_substr(nop.system_value, '[^ =]+=[^ ]+', 1, level) as pair_val
        , nop.system_value
    FROM
        num_of_pairs nop
    connect by
        level <= nop.num_of_pairs
)
, parsed as(
    SELECT
        lp.*
        , instr(lp.pair_val, '=') as eq_pos
        , substr(lp.pair_val, 1, local.eq_pos-1) as alias_name
        , substr(lp.pair_val, local.eq_pos + 1, length(lp.pair_val) - local.eq_pos) as alias_content
    FROM
        lang_pairs lp
)
select
    case
        when count(*) = 0 then 'You are not using the pre-shipped Python 2 in UDFs.'
        else 'You have '||to_char(count(*))||' UDFs using the pre-shipped Python 2:

' || GROUP_CONCAT('"'||s.script_schema||'"."'||s.script_name||'"' separator '
')
    end as check_results 
FROM
    parsed p
        join exa_dba_scripts s
        on upper(p.alias_name)=s.script_language
WHERE
    1=1
    and p.alias_content = 'builtin_python'
;

Update From 7.1.x To 7.1.20 or newer

To use Python 2.7 with UDFs after updating from Exasol 7.1.x to Exasol 7.1.20 or later you need to change the SCRIPT_LANGUAGES parameter to activate a script language container that supports Python 2.7. The new parameter value can be generated automatically or manually.

Automatic generation of the new SCRIPT_LANGUAGES parameter value

  1. Get the current value of the SCRIPT_LANGUAGES parameter and save the output in case you need to restore it. 

    SELECT 'ALTER SYSTEM SET SCRIPT_LANGUAGES=''' || system_value || ''';'
    FROM  EXA_PARAMETERS
    WHERE PARAMETER_NAME='SCRIPT_LANGUAGES';

    Expected result (the example shows the default values - actual values may be different if the parameter has been changed):

    ALTER SYSTEM SET SCRIPT_LANGUAGES='PYTHON=builtin_python R=builtin_r JAVA=builtin_java PYTHON3=builtin_python3';

  2. Run the following query before updating to 7.1.20:

    with minor_version as (
      select CAST(SUBSTR(PARAM_VALUE,INSTR(PARAM_VALUE,'.',-1,1)+1) as INT) as minor_version
      from  EXA_METADATA
      where PARAM_NAME='databaseProductVersion' and PARAM_VALUE like '7.1%'
    ),
    container_name as (
      select 'ScriptLanguages-standard-EXASOL-7.1.0-slc-v4.0.0-CM4RWW6R' as container_name
      from minor_version
      where minor_version>=7
      union all
      select 'ScriptLanguages-release-standard-exasol-7.1.0-1.1.0' as container_name
      from minor_version
      where minor_version<7
    ),
    num_of_pairs as(
      SELECT
        p.system_value,
        length(p.system_value)-length(replace(p.system_value, '=')) as num_of_pairs
      FROM exa_parameters p
      WHERE p.parameter_name in ('SCRIPT_LANGUAGES')
    ),
    lang_pairs as(
      SELECT
        regexp_substr(nop.system_value, '[^ =]+=[^ ]+', 1, level) as pair_val,
        nop.system_value
      FROM num_of_pairs nop
      connect by level <= nop.num_of_pairs
    ),
    parsed_lang_pairs as(
      SELECT
        instr(lp.pair_val, '=') as eq_pos,
        substr(lp.pair_val, 1, local.eq_pos-1) as alias_name,
        substr(lp.pair_val, local.eq_pos + 1, length(lp.pair_val) - local.eq_pos) as alias_content
      FROM lang_pairs lp
    ),
    new_alias_content as (
      select
        alias_name,
        'localzmq+protobuf:///bfsdefault/default/EXAClusterOS/' || container_name || '/?lang=python#/buckets/bfsdefault/default/EXAClusterOS/' || container_name || '/exaudf/exaudfclient'  as alias_content
      from container_name
      join parsed_lang_pairs on true
      where alias_content='builtin_python'
      union all
      select
        alias_name,
        alias_content
      from parsed_lang_pairs
      where alias_content<>'builtin_python'
    ),
    new_system_value as (
      select GROUP_CONCAT(alias_name || '=' || alias_content SEPARATOR ' ') as new_system_value
      from new_alias_content
    )
    select 'ALTER SYSTEM SET SCRIPT_LANGUAGES=''' || new_system_value || ''';' as command_text from new_system_value;

  3. Run the ALTER SYSTEM statement returned by the query. For example:

    ALTER SYSTEM SET SCRIPT_LANGUAGES='JAVA=builtin_java PYTHON3=builtin_python3 PYTHON=localzmq+protobuf:///bfsdefault/default/EXAClusterOS/ScriptLanguages-release-standard-exasol-7.1.0-1.1.0/?lang=python#/buckets/bfsdefault/default/EXAClusterOS/ScriptLanguages-release-standard-exasol-7.1.0-1.1.0/exaudf/exaudfclient R=builtin_r';

    We recommend that you test parameter changes using ALTER SESSION before making system-wide changes using ALTER SYSTEM.

Manual generation of the new SCRIPT_LANGUAGES parameter value

  1. Get the current value of the SCRIPT_LANGUAGES parameter and save the output in case you need to restore it. 

    SELECT 'ALTER SYSTEM SET SCRIPT_LANGUAGES=''' || system_value || ''';'
    FROM  EXA_PARAMETERS
    WHERE PARAMETER_NAME='SCRIPT_LANGUAGES';

    Expected result (the example shows the default values - actual values may be different if the parameter has been changed):

    ALTER SYSTEM SET SCRIPT_LANGUAGES='PYTHON=builtin_python R=builtin_r JAVA=builtin_java PYTHON3=builtin_python3';

  2. Using the output from the query in the previous step, replace builtin_python with localzmq+protobuf:///bfsdefault/default/EXAClusterOS/<container_name>/?lang=python#/buckets/bfsdefault/default/EXAClusterOS/<container_name>/exaudf/exaudfclient

    Replace <container_name> in this string with the following value depending on which version you are upgrading from:

    Exasol 7.1.7 - 7.1.19 ScriptLanguages-standard-EXASOL-7.1.0-slc-v4.0.0-CM4RWW6R
    Exasol 7.1.0 - 7.1.6 ScriptLanguages-release-standard-exasol-7.1.0-1.1.0

  3. Run the updated ALTER SYSTEM statement after changing the values as described in the previous step. For example:

    ALTER SYSTEM SET SCRIPT_LANGUAGES='PYTHON=localzmq+protobuf:///bfsdefault/default/EXAClusterOS/ScriptLanguages-standard-EXASOL-7.1.0-slc-v4.0.0-CM4RWW6R/?lang=python#/buckets/bfsdefault/default/EXAClusterOS/<container_name>/exaudf/exaudfclient PYTHON3=builtin_python3 R=builtin_r JAVA=builtin_java';

    We recommend that you test parameter changes using ALTER SESSION before making system-wide changes using ALTER SYSTEM.

Fresh Install Of 7.1 Or Update From 7.0

If you do a fresh install of the latest 7.1 release, or if you update from Exasol 7.0, you need to install a pre-built script language container from GitHub and then update the SCRIPT_LANGUAGE parameter to point to the container.

For more details of how to install a script language container, see Adding New Packages to Existing Script Languages.

  1. Download the latest version of the container that contained Python 2:

    Container: https://github.com/exasol/script-languages-release/releases/download/5.0.0/standard-EXASOL-7.1.0_release.tar.gz

    Checksum:https://github.com/exasol/script-languages-release/releases/download/5.0.0/standard-EXASOL-7.1.0_release.tar.gz.sha512sum

  2. Control the checksum of the downloaded container.

  3. Upload the container to BucketFS.

  4. Run the following query to generate the ALTER SYSTEM command to update the SCRIPT_LANGUAGES parameter.

    Replace the variables <bucketfs_name>, <bucket_name>, <path_in_bucket>, and <container_name> in the query to match the path to the uploaded container in BucketFS.

    with minor_version as (
      select CAST(SUBSTR(PARAM_VALUE,INSTR(PARAM_VALUE,'.',-1,1)+1) as INT) as minor_version
      from  EXA_METADATA
      where PARAM_NAME='databaseProductVersion' and PARAM_VALUE like '7.1%'
    ),
    container_path as (
      select '<bucketfs_name>/<bucket_name>/<path_in_bucket>/<container_name>' as container_path
    ),
    num_of_pairs as(
      SELECT
        p.system_value,
        length(p.system_value)-length(replace(p.system_value, '=')) as num_of_pairs
      FROM exa_parameters p
      WHERE p.parameter_name in ('SCRIPT_LANGUAGES')
    ),
    lang_pairs as(
      SELECT
        regexp_substr(nop.system_value, '[^ =]+=[^ ]+', 1, level) as pair_val,
        nop.system_value
      FROM num_of_pairs nop
      connect by level <= nop.num_of_pairs
    ),
    parsed_lang_pairs as(
      SELECT
        instr(lp.pair_val, '=') as eq_pos,
        substr(lp.pair_val, 1, local.eq_pos-1) as alias_name,
        substr(lp.pair_val, local.eq_pos + 1, length(lp.pair_val) - local.eq_pos) as alias_content
      FROM lang_pairs lp
    ),
    new_alias_content as (
      select
        alias_name,
        'localzmq+protobuf:///' || container_path || '/?lang=python#/buckets/' || container_path || '/exaudf/exaudfclient'  as alias_content
      from container_path
      join parsed_lang_pairs on true
      where alias_content='builtin_python'
      union all
      select
        alias_name,
        alias_content
      from parsed_lang_pairs
      where alias_content<>'builtin_python'
    ),
    new_system_value as (
      select GROUP_CONCAT(alias_name || '=' || alias_content SEPARATOR ' ') as new_system_value
      from new_alias_content
    )
    select 'ALTER SYSTEM SET SCRIPT_LANGUAGES=''' || new_system_value || ''';' as command_text from new_system_value;

  5. Run the ALTER SYSTEMstatement returned by the query.

    We recommend that you test any parameter changes using ALTER SESSION before making system-wide changes using ALTER SYSTEM.