Reply
Explorer
Posts: 14
Registered: ‎05-22-2016

Tutorial 2: do not get the result in the final step

Following the steps in Tutorial 2, I have double checked the steps and confirmed

1. the raw data has been imported to hdfs

 

[cloudera@quickstart ~]$ sudo -u hdfs hadoop fs -mkdir /user/hive/warehouse/original_access_logs

[cloudera@quickstart ~]$ sudo -u hdfs hadoop fs -copyFromLocal /opt/examples/log_files/access.log.2 /user/hive/warehouse/original_access_logs

 

 

2. Created the intermediate_access_logs table based on the  location of /user/hive/warehouse/original_access_logs/access.log.2

Created the table tokenized_access_logs,

and populated tokenized_access_logs

 

CREATE EXTERNAL TABLE intermediate_access_logs (
......
LOCATION '/user/hive/warehouse/original_access_logs';

CREATE EXTERNAL TABLE tokenized_access_logs (
......
LOCATION '/user/hive/warehouse/tokenized_access_logs';

ADD JAR /usr/lib/hive/lib/hive-contrib.jar;

INSERT OVERWRITE TABLE tokenized_access_logs SELECT * FROM intermediate_access_logs;

 

 

3. I double checked the tokenized_access_logs that content is there:

select * From tokenized_access_logs limit 10;

 

However, when executing the last script

 

select count(*),url from tokenized_access_logs
where url like '%\/product\/%'
group by url order by count(*) desc;

It returns:

The operation has no results.

Why there is no results like the tutorial? where could it go wrong?

 

Thank you very much.

 

 

 

Explorer
Posts: 14
Registered: ‎05-22-2016

Re: Tutorial 2: do not get the result in the final step

More findings:

 

1. I was executing the query in Impala, and it returns "The operation has no results.";

2. I then modify the query to: select * from tokenized_access_logs

    and it still shows "The operation has no results."

3. I then switch to Hive Query Editor, just run select * from tokenized_access_logs limit 10, it returns 10 records, so I know there are records in the table

4. In Hive Query Editor: I execute the full script:

select count(*),url from tokenized_access_logs
where url like '%\/product\/%'
group by url order by count(*) desc;

It gives me the following error:

Error while compiling statement: FAILED: SemanticException [Error 10128]: Line 3:22 Not yet supported place for UDAF 'count'

I am new to Hadoop, this is very confusing to me: 

1. For the same table, Impala returns no result while Hive returns result;

2. For the same script, the tutorial shows result while I don't see any result;

3. Can the query be modified to run in Hive? if yes, how should it be?

 

 

Thank you very much.

Highlighted
Cloudera Employee
Posts: 435
Registered: ‎07-12-2013

Re: Tutorial 2: do not get the result in the final step

It sounds like you may have skipped the 'invalidate metadata' step before
running the query in Impala. Impala does not lookup metadata for every
query until you tell it to, to save a bit more time during queries - so you
have to refresh it when there are new tables, etc.
Explorer
Posts: 14
Registered: ‎05-22-2016

Re: Tutorial 2: do not get the result in the final step

Well, the table is confirmed to be there.

 

If I take off the order by clause, it will generate result for me, as long as there is "order by" clause, no resule will be generated.

 

And further to the tutorial script, what query should I put there if I want to get the top 10 and order by (*) desc?

 

Thank you.

Explorer
Posts: 14
Registered: ‎05-22-2016

Re: Tutorial 2: do not get the result in the final step

[ Edited ]

To clarify:

 

The last sentence in my preceding post:

 

what query should I put there if I want to get the top 10 and order by (*) desc

 

What I meant in the "order by (*)" is order by any one of the available columns (in this case, it is "url") or simply order by count(1) .

 

BTW: I don't understand why the tutorial uses order by count(*)? count(*) will create more workload than count(1) in SQL.

 

 

Explorer
Posts: 14
Registered: ‎05-22-2016

Re: Tutorial 2: do not get the result in the final step

Hello,

 

Can anyone from Cloudera explain to me why the order clause fails on my test? Thanks.