如何让 Hadoop 接受带有复合 WHERE 子句的 SAS 直通查询?

Question

我试图通过在内部(Hadoop端(WHERE 子句中发送附加条件来缩小传递查询(即使用 PROC SQL，而不是 LIBNAME(到 Hadoop 的结果 SAS 数据集的范围。

例如，工作的代码如下所示(dtpart宏变量在前面定义，以便向下选择HDFS中的单个分区(：

proc sql;
connect to hadoop (server="&srvid" port=10000 user="&uid" pw="&passwd" schema=default );
create table work.creative_lkup as
select 
advertiser_id,
creative_id,
creative,
rendering_id,
creative_type, 
input("&dtpart.", yymmdd10.) as last_updt_dt format=mmddyy10.
from connection to hadoop (
select
`advertiser id` as advertiser_id,
`creative id` as creative_id,
creative,
`rendering id` as rendering_id,
`creative type` as creative_type
from default.match_table_creatives 
where date_partition = "&dtpart." 
) 
WHERE advertiser_id = '12345';
disconnect from hadoop;
quit;

注意：如您所见，Hadoop 中的字段名称不是 SAS 标准的(因此包含空格的名称周围有反引号(，对于某些表，字段和表名称变得非常冗长。 这使得简单地对我的Hadoop连接使用LIBNAME语句是不切实际的，因此我必须使用CONNECT TO在PROC SQL中使用传递查询。

我想做的是将"WHERE advertiser_id = '12345'"部分移动到Hadoop。 像这样：

proc sql;
connect to hadoop (server="&srvid" port=10000 user="&uid" pw="&passwd" schema=default );
create table work.creative_lkup as
select 
advertiser_id,
creative_id,
creative,
rendering_id,
creative_type, 
input("&dtpart.", yymmdd10.) as last_updt_dt format=mmddyy10.
from connection to hadoop (
select
`advertiser id` as advertiser_id,
`creative id` as creative_id,
creative,
`rendering id` as rendering_id,
`creative type` as creative_type
from default.match_table_creatives 
where date_partition = "&dtpart." 
and `advertiser id` = '12345'
);
disconnect from hadoop;
quit;

不幸的是，当我这样做时，我从 Hadoop 传回 SAS 时收到一个很好的模糊错误：

ERROR: Prepare error: Error while processing statement: FAILED: Execution Error, return code 1 from 
org.apache.hadoop.hive.ql.exec.mr.MapRedTask

我的问题是：是否有任何设置、选项或其他项目可以包含在我的 PROC SQL 语句中，以便正确利用 WHERE 子句？

似乎仅当 WHERE 子句中存在 AND 时才会出现此问题。 我已经阅读了一些关于SPDE的内容(ACCELWHERE=选项听起来很有希望(，但我不知道如何在PROC SQL中使用此类选项，我在网上找到的参考资料似乎只指向在LIBNAME语句中使用。

谢谢。

Answer 1

看起来问题在于连接字符串中指定的用户(保存在SAS宏变量&uid中(实际上并不存在于Hadoop中。

宏变量 &uid 保存通用用户 ID 的名称(DEV 和 PROD 环境的不同名称 - 因此需要一个宏变量(。 假设用户存在于Hadoop中，因为我能够使用此userID从SAS连接到Hadoop并通过简单的查询检索记录。

显然，情况并非如此。

如果Hadoop环境中没有用户，HDFS中就没有主文件夹。 我们的Hadoop设置仍将处理"简单"查询(即，没有对字段进行转换;只有WHERE条件可以针对分区字段;没有连接;等等(，因为不需要构建MapReducer任务来从HDFS中的底层平面文件中获取数据。 只有当存在其他 WHERE 条件(即分区字段之外(时，才需要创建 MapReducer 任务。 任务以及任务所需的任何临时文件在运行查询的用户的主文件夹中创建。 由于此用户不存在主文件夹，因此 MapReducer 对象会引发错误(尽管没有在 Hive 中生成日志文件，因为没有地方生成日志文件，甚至没有创建此类文件的任务(。

它甚至允许在没有有效用户ID的情况下选择数据，这很愚蠢，但这是我们当前的设置。

因此，为了测试这个理论，我运行了以下代码(注意：我使用了我的用户名而不是宏变量(。

proc sql;
connect to hadoop (server="&srvid" port=10000 user="myuserid" pw="unnecessary" schema=default);
create table work.creative_lkup2 as
select 
advertiser_id,
creative_id,
creative,
rendering_id,
creative_type, 
input("&dtpart.", yymmdd10.) as last_updt_dt format=mmddyy10.
from connection to hadoop (
select
c.`advertiser id` as advertiser_id,
c.`creative id` as creative_id,
c.creative,
c.`rendering id` as rendering_id,
c.`creative type` as creative_type
from default.match_table_creatives c
where c.date_partition = "&dtpart." and c.`advertiser id` = "12345" 
);
disconnect from hadoop;
quit;

这返回了大约 80k 条记录(10 秒的 CPU 时间，但由于来自 AWS 的 IO，它花了将近 10 分钟(。

为了感谢所有查看此问题并做出回应的人，我想从中提供可能对您有所帮助的额外经验教训：

由于Hadoop中的字段都定义为字符串，因此SAS必须假定这些字段是最长的字符长度：32,767。 因此，数据量(主要是空白(过多，导致传输速率缓慢。

我们想出的解决方法涉及使用 EXECUTE (...AS HADOOP 语句，用于创建和填充一个临时表，该表在将 VARCHAR 字段引入 SAS 之前具有正确大小的 VARCHAR 字段(已经向下选择为我们想要的数据(。

proc sql;
connect to hadoop (server="&srvid" port=10000 user="myuserid" pw="doesnt_matter" schema=default);
execute (
create table if not exists default.test_lkup_cre (
advertiser_id Varchar(10),
creative_id Varchar(10),
creative Varchar(200),
rendering_id Varchar(10),
creative_type Varchar(16)
)
) by hadoop;
execute (
insert into table default.test_lkup_cre
select
c.`advertiser id` as advertiser_id,
c.`creative id` as creative_id,
c.creative,
c.`rendering id` as rendering_id,
c.`creative type` as creative_type
from default.match_table_creatives c
where c.date_partition = "&dtpart." and c.`advertiser id` = "12345"
) by hadoop;
create table work.creative_lkup3 as
select 
advertiser_id,
creative_id,
creative,
rendering_id,
creative_type, 
input("&dtpart.", yymmdd10.) as last_updt_dt format=mmddyy10.
from connection to hadoop (
select
advertiser_id,
creative_id,
creative,
rendering_id,
creative_type
from default.test_lkup_cre
);
disconnect from hadoop;
quit;

这在短短 46 秒内返回了相同的 ~80k 记录。

希望这也对其他人有所帮助。