在列中找到值 [英] Locate value in column

问题描述

我正在将Elastic Load Balancing Access日志传递给Athena，我想获取位于URL列中的值。下面的代码适用于mySQL，但是Athena使用SQL可以从下面的方法中获取县的价值吗？

I am passing Elastic Load Balancing Access logs to Athena and I want to get a value that is located in the URL column. The below works for mySQL but Athena uses SQL is there a way I can grab the value of county from the below?

create table elb_logs(row varchar(100), url varchar(100));
insert into elb_logs values("Row1", "Lauguage=English&Country=USA&Gender=Male");
insert into elb_logs values("Row2", "Gender=Female&Language=French&Country=");
insert into elb_logs values("Row3", "Country=Canada&Gender=&Language=English");
insert into elb_logs values("Row4", "Gender=&Language=English");

SELECT `row`, IF(LOCATE('Country=', url)>0, 
  COALESCE(
    NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(url, 'Country=', -1), '&', 1), ''), 
    'Blank string is not valid!'), 
 'Missing Country!') AS ColumnB     
FROM `elb_logs`

+------+----------------------------+
| row  | ColumnB                    |
+------+----------------------------+
| Row1 | USA                        |
| Row2 | Blank string is not valid! |
| Row3 | Canada                     |
| Row4 | Missing Country!           |
+------+----------------------------+

推荐答案

SPLIT_PART 函数似乎在这里可用：

The SPLIT_PART function would seem to be usable here:

SELECT
    row,
    CASE WHEN POSITION('Country=' IN url) > 0
         THEN SPLIT_PART(SPLIT_PART(url, 'Country=', 2), '&', 1)
         ELSE 'Missing Country!' END AS ColumnB
FROM elb_log;

这篇关于在列中找到值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！