Using Python 2 with Exasol 7.1.20 or later

This article describes how to continue to use Python 2.7 in Exasol 7.1.20 or later versions that do not support Python 2.

Python 2 has reached end of life and is no longer supported in Exasol, and we recommend that you switch to using Python 3 in all Python UDFs. This article 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.

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 the UDFs. We cannot guarantee that all occurences are detected.

Copy 
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 later

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

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.

    Copy 
    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):

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

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

    Copy 
    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.

    Copy 
    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):

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

    Copy 
    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 Exasol 7.1 release or update from Exasol 7.0, you must install a pre-built script language container from GitHub and then update the SCRIPT_LANGUAGE parameter to point to the container.

To learn 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.

    Copy 
    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.