如何在SAS SQL连接Impala时配置mem_limit=1g参数？

用户使用SAS SQL连接Impala执行查询时遇到Scratch限制问题，大数据团队建议通过设置Impala会话参数set mem_limit=1g解决，但不清楚在SAS中如何正确配置。

用户原始的SAS SQL连接代码如下：

PROC SQL;
    CONNECT TO IMPALA (USER="&TDUSER" PW="&***" DSN="BIGDATA" DATABASE= abc);
    CREATE TABLE SIL_MONITORED AS SELECT * FROM CONNECTION TO IMPALA
    (
        SELECT DISTINCT a.partyid, a.baselinerecordstatuscode, cast(b.cin as decimal(10)) as cin, a.business_date
        /*cast(unix_timestamp(a.business_date, "yyyy-MM-dd") as timestamp) as business_date*/
        FROM abc.baseline_party as a
        LEFT JOIN
        abc.baseline_relationship as b
        on a.partyid = b.partyid
        where a.business_date = (select max(business_date) from abc.baseline_party)
        and upper(a.baselinerecordstatuscode) = 'MONITORED') ;
QUIT;

大数据侧给出的解决方案：

set mem_limit=1g

用户曾尝试通过CONOPTS传递SCRATCH_LIMIT参数，但未被大数据端识别，无效代码如下：

PROC SQL;
    CONNECT TO IMPALA (USER="&TDUSER" PW="&***" DSN="BIGDATAPROD" DATABASE= abc
        CONOPTS='SCRATCH_LIMIT=200g');
    CREATE TABLE SIL_MONITORED AS SELECT * FROM CONNECTION TO IMPALA
    /* 后续查询内容省略 */
QUIT;

正确配置方法 #

方法1：在Impala查询块中直接执行SET语句

将SET mem_limit=1g;放在Impala的查询语句开头，会在当前Impala会话中直接生效：

PROC SQL;
    CONNECT TO IMPALA (USER="&TDUSER" PW="&***" DSN="BIGDATA" DATABASE= abc);
    CREATE TABLE SIL_MONITORED AS SELECT * FROM CONNECTION TO IMPALA
    (
        SET mem_limit=1g;
        SELECT DISTINCT a.partyid, a.baselinerecordstatuscode, cast(b.cin as decimal(10)) as cin, a.business_date
        FROM abc.baseline_party as a
        LEFT JOIN abc.baseline_relationship as b
            ON a.partyid = b.partyid
        WHERE a.business_date = (SELECT max(business_date) FROM abc.baseline_party)
          AND upper(a.baselinerecordstatuscode) = 'MONITORED'
    ) ;
QUIT;

方法2：通过CONOPTS传递会话初始化参数

如果使用的Impala ODBC驱动支持，可通过CONOPTS参数直接传递Impala的SET命令，在建立连接时初始化会话：

PROC SQL;
    CONNECT TO IMPALA (USER="&TDUSER" PW="&***" DSN="BIGDATA" DATABASE= abc
        CONOPTS='SET mem_limit=1g;'
    );
    CREATE TABLE SIL_MONITORED AS SELECT * FROM CONNECTION TO IMPALA
    (
        SELECT DISTINCT a.partyid, a.baselinerecordstatuscode, cast(b.cin as decimal(10)) as cin, a.business_date
        FROM abc.baseline_party as a
        LEFT JOIN abc.baseline_relationship as b
            ON a.partyid = b.partyid
        WHERE a.business_date = (SELECT max(business_date) FROM abc.baseline_party)
          AND upper(a.baselinerecordstatuscode) = 'MONITORED'
    ) ;
QUIT;

说明 #

之前尝试的SCRATCH_LIMIT是SAS自身的参数，并非Impala识别的会话参数，因此无法被大数据端生效。需要直接传递Impala原生的mem_limit参数，以上两种方法均可在SAS中实现Impala会话级别的参数配置。

内容的提问来源于stack exchange，提问作者Vijay