Grokbase › Groups › Hive › user › March 2010
FAQ
Hi all,
I have a query below

FROM (
SELECT h.*
FROM (
-- Pull from the access_log
SELECT ip,
-- Reformat the time from the access log
time, dt,
--method, resource, protocol, status, length, referer, agent,
-- Extract the product_id for the hit from the URL
cast( regexp_extract(resource,'\&q=([^\&]+)', 1) AS STRING) AS
product_name
FROM a_log
) h
)hit
-- Insert the hit data into a seperate search table
INSERT OVERWRITE TABLE search
SELECT ip, time, dt,
product_name
WHERE product_name IS NOT NULL;


it suppose to populate the search table with only if product_name is present
but i get all of it..

any help would be appreciated

thanks
prakash sejwani
econify infotech
mumbai

Search Discussions

  • Zheng Shao at Mar 9, 2010 at 8:36 am ⇧
    What do you mean by "product_name" is "present"?
    If it is not present, does the regexp_extract return NULL?

    Zheng

    On Tue, Mar 9, 2010 at 12:13 AM, prakash sejwani
    wrote:
    Hi all,
    I have a query below

    FROM (
    SELECT h.*
    FROM (
    -- Pull from the access_log
    SELECT ip,
    -- Reformat the time from the access log
    time, dt,
    --method, resource, protocol, status, length, referer, agent,
    -- Extract the product_id for the hit from the URL
    cast( regexp_extract(resource,'\&q=([^\&]+)', 1) AS STRING) AS
    product_name
    FROM a_log
    ) h
    )hit
    -- Insert the hit data into a seperate search table
    INSERT OVERWRITE TABLE search
    SELECT ip, time, dt,
    product_name
    WHERE product_name IS NOT NULL;


    it suppose to populate the search table with only if product_name is present
    but i get all of it..

    any help would be appreciated

    thanks
    prakash sejwani
    econify infotech
    mumbai


    --
    Yours,
    Zheng
  • Prakash sejwani at Mar 9, 2010 at 8:42 am ⇧
    yes, regexp_extract return NULL or Blank
    On Tue, Mar 9, 2010 at 2:05 PM, Zheng Shao wrote:

    What do you mean by "product_name" is "present"?
    If it is not present, does the regexp_extract return NULL?

    Zheng

    On Tue, Mar 9, 2010 at 12:13 AM, prakash sejwani
    wrote:
    Hi all,
    I have a query below

    FROM (
    SELECT h.*
    FROM (
    -- Pull from the access_log
    SELECT ip,
    -- Reformat the time from the access log
    time, dt,
    --method, resource, protocol, status, length, referer, agent,
    -- Extract the product_id for the hit from the URL
    cast( regexp_extract(resource,'\&q=([^\&]+)', 1) AS STRING) AS
    product_name
    FROM a_log
    ) h
    )hit
    -- Insert the hit data into a seperate search table
    INSERT OVERWRITE TABLE search
    SELECT ip, time, dt,
    product_name
    WHERE product_name IS NOT NULL;


    it suppose to populate the search table with only if product_name is present
    but i get all of it..

    any help would be appreciated

    thanks
    prakash sejwani
    econify infotech
    mumbai


    --
    Yours,
    Zheng
  • Zheng Shao at Mar 9, 2010 at 8:44 am ⇧
    So I guess you didn't exclude the Blank ones?

    On Tue, Mar 9, 2010 at 12:41 AM, prakash sejwani
    wrote:
    yes, regexp_extract return NULL or Blank
    On Tue, Mar 9, 2010 at 2:05 PM, Zheng Shao wrote:

    What do you mean by "product_name" is "present"?
    If it is not present, does the regexp_extract return NULL?

    Zheng

    On Tue, Mar 9, 2010 at 12:13 AM, prakash sejwani
    wrote:
    Hi all,
    I have a query below

    FROM (
    SELECT h.*
    FROM (
    -- Pull from the access_log
    SELECT ip,
    -- Reformat the time from the access log
    time, dt,
    --method, resource, protocol, status, length, referer, agent,
    -- Extract the product_id for the hit from the URL
    cast( regexp_extract(resource,'\&q=([^\&]+)', 1) AS STRING) AS
    product_name
    FROM a_log
    ) h
    )hit
    -- Insert the hit data into a seperate search table
    INSERT OVERWRITE TABLE search
    SELECT ip, time, dt,
    product_name
    WHERE product_name IS NOT NULL;


    it suppose to populate the search table with only if product_name is
    present
    but i get all of it..

    any help would be appreciated

    thanks
    prakash sejwani
    econify infotech
    mumbai


    --
    Yours,
    Zheng


    --
    Yours,
    Zheng
  • Prakash sejwani at Mar 9, 2010 at 8:46 am ⇧
    yes right can you give me a tip how to exclude blank values
    On Tue, Mar 9, 2010 at 2:13 PM, Zheng Shao wrote:

    So I guess you didn't exclude the Blank ones?

    On Tue, Mar 9, 2010 at 12:41 AM, prakash sejwani
    wrote:
    yes, regexp_extract return NULL or Blank
    On Tue, Mar 9, 2010 at 2:05 PM, Zheng Shao wrote:

    What do you mean by "product_name" is "present"?
    If it is not present, does the regexp_extract return NULL?

    Zheng

    On Tue, Mar 9, 2010 at 12:13 AM, prakash sejwani
    wrote:
    Hi all,
    I have a query below

    FROM (
    SELECT h.*
    FROM (
    -- Pull from the access_log
    SELECT ip,
    -- Reformat the time from the access log
    time, dt,
    --method, resource, protocol, status, length, referer, agent,
    -- Extract the product_id for the hit from the URL
    cast( regexp_extract(resource,'\&q=([^\&]+)', 1) AS STRING) AS
    product_name
    FROM a_log
    ) h
    )hit
    -- Insert the hit data into a seperate search table
    INSERT OVERWRITE TABLE search
    SELECT ip, time, dt,
    product_name
    WHERE product_name IS NOT NULL;


    it suppose to populate the search table with only if product_name is
    present
    but i get all of it..

    any help would be appreciated

    thanks
    prakash sejwani
    econify infotech
    mumbai


    --
    Yours,
    Zheng


    --
    Yours,
    Zheng
  • Zheng Shao at Mar 9, 2010 at 8:52 am ⇧
    WHERE product_name IS NOT NULL AND product_name <> ''

    On Tue, Mar 9, 2010 at 12:45 AM, prakash sejwani
    wrote:
    yes right can you give me a tip how to exclude blank values
    On Tue, Mar 9, 2010 at 2:13 PM, Zheng Shao wrote:

    So I guess you didn't exclude the Blank ones?

    On Tue, Mar 9, 2010 at 12:41 AM, prakash sejwani
    wrote:
    yes, regexp_extract return NULL or Blank
    On Tue, Mar 9, 2010 at 2:05 PM, Zheng Shao wrote:

    What do you mean by "product_name" is "present"?
    If it is not present, does the regexp_extract return NULL?

    Zheng

    On Tue, Mar 9, 2010 at 12:13 AM, prakash sejwani
    wrote:
    Hi all,
    I have a query below

    FROM (
    SELECT h.*
    FROM (
    -- Pull from the access_log
    SELECT ip,
    -- Reformat the time from the access log
    time, dt,
    --method, resource, protocol, status, length, referer, agent,
    -- Extract the product_id for the hit from the URL
    cast( regexp_extract(resource,'\&q=([^\&]+)', 1) AS STRING)
    AS
    product_name
    FROM a_log
    ) h
    )hit
    -- Insert the hit data into a seperate search table
    INSERT OVERWRITE TABLE search
    SELECT ip, time, dt,
    product_name
    WHERE product_name IS NOT NULL;


    it suppose to populate the search table with only if product_name is
    present
    but i get all of it..

    any help would be appreciated

    thanks
    prakash sejwani
    econify infotech
    mumbai


    --
    Yours,
    Zheng


    --
    Yours,
    Zheng


    --
    Yours,
    Zheng
  • Prakash sejwani at Mar 9, 2010 at 8:54 am ⇧
    I am sending sample file to populate the a_log in hive and check if its
    needed by you
    On Tue, Mar 9, 2010 at 2:15 PM, prakash sejwani wrote:

    yes right can you give me a tip how to exclude blank values

    On Tue, Mar 9, 2010 at 2:13 PM, Zheng Shao wrote:

    So I guess you didn't exclude the Blank ones?

    On Tue, Mar 9, 2010 at 12:41 AM, prakash sejwani
    wrote:
    yes, regexp_extract return NULL or Blank
    On Tue, Mar 9, 2010 at 2:05 PM, Zheng Shao wrote:

    What do you mean by "product_name" is "present"?
    If it is not present, does the regexp_extract return NULL?

    Zheng

    On Tue, Mar 9, 2010 at 12:13 AM, prakash sejwani
    wrote:
    Hi all,
    I have a query below

    FROM (
    SELECT h.*
    FROM (
    -- Pull from the access_log
    SELECT ip,
    -- Reformat the time from the access log
    time, dt,
    --method, resource, protocol, status, length, referer, agent,
    -- Extract the product_id for the hit from the URL
    cast( regexp_extract(resource,'\&q=([^\&]+)', 1) AS STRING)
    AS
    product_name
    FROM a_log
    ) h
    )hit
    -- Insert the hit data into a seperate search table
    INSERT OVERWRITE TABLE search
    SELECT ip, time, dt,
    product_name
    WHERE product_name IS NOT NULL;


    it suppose to populate the search table with only if product_name is
    present
    but i get all of it..

    any help would be appreciated

    thanks
    prakash sejwani
    econify infotech
    mumbai


    --
    Yours,
    Zheng


    --
    Yours,
    Zheng
  • Prakash sejwani at Mar 9, 2010 at 8:57 am ⇧
    Thanks it worked for me
    On Tue, Mar 9, 2010 at 2:23 PM, prakash sejwani wrote:

    I am sending sample file to populate the a_log in hive and check if its
    needed by you
    On Tue, Mar 9, 2010 at 2:15 PM, prakash sejwani wrote:

    yes right can you give me a tip how to exclude blank values

    On Tue, Mar 9, 2010 at 2:13 PM, Zheng Shao wrote:

    So I guess you didn't exclude the Blank ones?

    On Tue, Mar 9, 2010 at 12:41 AM, prakash sejwani
    wrote:
    yes, regexp_extract return NULL or Blank
    On Tue, Mar 9, 2010 at 2:05 PM, Zheng Shao wrote:

    What do you mean by "product_name" is "present"?
    If it is not present, does the regexp_extract return NULL?

    Zheng

    On Tue, Mar 9, 2010 at 12:13 AM, prakash sejwani
    wrote:
    Hi all,
    I have a query below

    FROM (
    SELECT h.*
    FROM (
    -- Pull from the access_log
    SELECT ip,
    -- Reformat the time from the access log
    time, dt,
    --method, resource, protocol, status, length, referer,
    agent,
    -- Extract the product_id for the hit from the URL
    cast( regexp_extract(resource,'\&q=([^\&]+)', 1) AS STRING)
    AS
    product_name
    FROM a_log
    ) h
    )hit
    -- Insert the hit data into a seperate search table
    INSERT OVERWRITE TABLE search
    SELECT ip, time, dt,
    product_name
    WHERE product_name IS NOT NULL;


    it suppose to populate the search table with only if product_name is
    present
    but i get all of it..

    any help would be appreciated

    thanks
    prakash sejwani
    econify infotech
    mumbai


    --
    Yours,
    Zheng


    --
    Yours,
    Zheng

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categorieshive, hadoop
postedMar 9, '10 at 8:14a
activeMar 9, '10 at 8:57a
posts8
users2
websitehive.apache.org

2 users in discussion

Prakash sejwani: 5 posts Zheng Shao: 3 posts

Content

People

Support

Translate

site design / logo © 2022 Grokbase