teradata转换为配置单元sql使用case when

6yjfywim 于 2021-06-24 发布在 Hive

关注(0)|答案(2)|浏览(674)

我试图将下面的teradatasql转换为配置单元sql，但得到的表是空的。
teradata sql:

SELECT              
 TRIM(CAST(BOOK_ID AS BIGINT)) || '_' ||TRIM(CAST(REF_ID AS BIGINT)) 
 AS BOOK_REF,
 CASE WHEN (PHOTO_COUNT > 0) AND (INDEX(PICTURE_URL , ';')>0) THEN 
 SUBSTRING(PICTURE_URL FROM 1 FOR POSITION(';' IN PICTURE_URL)-1)
 ELSE PICTURE_URL
 END AS MAIN_IMAGE 

 FROM GENERIC_BOOKS;

Hive：

SELECT 
 CASE WHEN (PHOTO_COUNT > 0) AND (instr(A.PICTURE_URL, ';') > 0) THEN 
 SUBSTRING(A.PICTURE_URL, 1, FIND_IN_SET(';', A.PICTURE_URL))-1
    ELSE A.PICTURE_URL
    END AS ITEM_MAIN_IMAGE
 FROM GENERIC_BOOKS;

图片url例如：https://booking.com/00/s/otawwde2a=/z/wkeaaoswfurc~gng/$\u 57.jpg？集合\u id=880005007；https://booking.com/00/s/otaww2mda=/z/lqcaaoswrzxc~gni/$\u 57.jpg？集合\u id=880005007；https://booking.com/00/s/otaww2mda=/z/xaiaaosw7j1c~gnl/$\u 57.jpg？集合\u id=880005007；https://booking.com/00/s/otaww2mda=/z/aa8aaoswyt1c~gnv/$\u 57.jpg？集合\u id=8800005007
对于本例，预期的主映像应该是：https://booking.com/00/s/otawwde2a=/z/wkeaaoswfurc~gng/$\u 57.jpg？集合\u id=880005007

Hive teradata case-when

来源：https://stackoverflow.com/questions/63331268/teradata-conversion-to-hive-sql-using-case-when

2条答案

按热度按时间

hgqdbh6s1#

下面的查询可能是您提到的teradata查询的等效查询。

SELECT              
 concat(TRIM(BOOK_ID), '_', TRIM(REF_ID)) AS BOOK_REF,
 CASE WHEN (PHOTO_COUNT >0) AND (INSTR(PICTURE_URL,'\;')>0) THEN 
 SUBSTR(split(PICTURE_URL,'\\;')[1],-1)
 ELSE PICTURE_URL
 END AS MAIN_IMAGE FROM GENERIC_BOOKS;

使用以下代码样本进行测试，

create table if not exists generic_books(book_id string, ref_id string, photo_count string, picture_url string) row format delimited fields terminated by ',' stored as textfile;

insert into table generic_books values ("1","1","1","AAA\;BBB"); 

insert into table generic_books values ("a","b","CDB","AAA\;BBB");

/*hive> select * from generic_books;
OK
1       1       CDB     AAA;BBB
a       b       CDB     AAA;BBB
1       1       1       AAA;BBB

* /

hive> SELECT
    >  concat(TRIM(BOOK_ID), '_', TRIM(REF_ID)) AS BOOK_REF,
    >  CASE WHEN (PHOTO_COUNT >0) AND (INSTR(PICTURE_URL,'\;')>0) THEN
    >  SUBSTR(split(PICTURE_URL,'\\;')[0],-1)
    >  ELSE PICTURE_URL
    >  END AS MAIN_IMAGE FROM GENERIC_BOOKS;

/*OK
1_1     AAA;BBB
a_b     AAA;BBB
1_1     A

* /

注意：如果您可以共享问题中查询的teradata输入和输出，我可以相应地更新答案。

赞(0）回复(0）举报 2021-06-24

8hhllhi22#

HiveSQL不支持标准的sql位置和teradata的索引（不知道为什么两者都使用，不同的语法可以得到相同的结果）。
两者都可以用locate代替。
另外，标准sql子字符串也没有在hive中实现，它是substr:

CASE WHEN (PHOTO_COUNT >0) AND (locate(';', PICTURE_URL)>0) THEN 
 SUBSTR(PICTURE_URL, 1, locate(';', PICTURE_URL)-1)

赞(0）回复(0）举报 2021-06-24

我来回答

teradata转换为配置单元sql使用case when

2条答案

相关问题

热门标签

最新问答