Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

How to make hive queries including scala and python functions?

Solved Go to solution
Highlighted

Re: How to make hive queries including scala and python functions?

@Hugo Felix

Here's a test, that should help to determine if your syntax is off or if your environment is misconfigured.

First, create a test Hive table and populate it with data:

CREATE TABLE IF NOT EXISTS testtable
    (id string, text string)
    STORED AS ORC;

INSERT INTO TABLE testtable VALUES 
    ('1111', 'The service was great, and the agent was very helpful'), 
    ('2222', 'I enjoyed the event but the food was terrible'),
    ('3333', 'Unhappy with the organization of the event')

Then create a file called "my_py_udf.py" (as shown below). It can be placed anywhere, but in my example I placed it at /tmp/my_py_udf.py.

import sys
 
for line in sys.stdin:
    
    id, text = line.replace('\n',' ').split('\t')
    
    positive = set(["love", "good", "great", "happy", "cool", "best", "awesome", "nice", "helpful", "enjoyed"])
    negative = set(["hate", "bad", "stupid", "terrible", "unhappy"])
    
    words = text.split()
    word_count = len(words)
    
    positive_matches = [1 for word in words if word in positive]
    negative_matches = [-1 for word in words if word in negative]
    
    st = sum(positive_matches) + sum(negative_matches)
    
    if st > 0:
        print '\t'.join([text, 'positive', str(word_count)])
    elif st < 0:
        print '\t'.join([text, 'negative', str(word_count)])
    else:
        print '\t'.join([text, 'neutral', str(word_count)])

Then from within Hive, execute the following commands:

ADD FILE my_py_udf.py;

SELECT
TRANSFORM (id, text)
USING 'python my_py_udf.py'
AS  (text, sentiment, word_count)
FROM testtable;

Your resulting output should look like this:

The service was great, and the agent was very helpful   positive    10
I enjoyed the event but the food was terrible           neutral     9
Unhappy with the organization of the event              neutral     7
Highlighted

Re: How to make hive queries including scala and python functions?

Contributor

@Dan Zaratsian

I was able to reproduce the code you've gave me. It runs ok.

The problem with the twitter table persists.

If I add the Json serde in the query I get error processing row, if not, it hangs a lot of time and returns map operator run initialized . I think I have to add the Serde and therefore the problem is not from here.

Here's the code:

import sys
for line in sys.stdin:
     text = line.split('\t')
        
    positive = set(["love", "good", "great", "happy", "cool", "best", "awesome", "nice", "helpful", "enjoyed"])
    negative = set(["hate", "bad", "stupid", "terrible", "unhappy"])

    words = text.split()
    word_count = len(words)
        
    positive_matches = [1 for word in words if word in positive]
    negative_matches = [-1 for word in words if word in negative]
        
    st = sum(positive_matches) + sum(negative_matches)
        
    if st > 0:
        print ('\t'.join([text, 'positive', str(word_count)]))
    elif st < 0:
        print ('\t'.join([text, 'negative', str(word_count)]))
    else:
        print ('\t'.join([text, 'neutral', str(word_count)]))

I was able to run the tweets table with this test:

import sys
     
for line in sys.stdin:

    print ('\t'.join([line]))
ADD JAR /tmp/json-serde-1.3.8-jar-with-dependencies.jar;

ADD FILE /tmp/teste.py;

SELECT
    TRANSFORM (text)
    USING 'python teste.py'
    FROM tweets;
Highlighted

Re: How to make hive queries including scala and python functions?

@Hugo Felix

Nice! Glad to see that you got it working. If you upgrade to Spark 2.x, then you should not need to add the serde (just something to keep in mind). If you're all set, can you please mark thread as accepted. Thanks!

Highlighted

Re: How to make hive queries including scala and python functions?

Contributor

@Dan Zaratsian

Only got your 2nd example working... that to check if the prolem was with the syntax or system misconfiguration.

With the function you gave me i cannot check the tweets table, it gives the row error.

Can you please check this code one last time?

import sys
     
for line in sys.stdin:

    id, text = line.replace('\n',' ').split('\t')
        
    positive = set(["love", "good", "great", "happy", "cool", "best", "awesome", "nice", "helpful", "enjoyed"])
    negative = set(["hate", "bad", "stupid", "terrible", "unhappy"])

    words = text.split()
    word_count = len(words)
        
    positive_matches = [1 for word in words if word in positive]
    negative_matches = [-1 for word in words if word in negative]
        
    st = sum(positive_matches) + sum(negative_matches)
        
    if st > 0:
        print ('\t'.join([text, 'positive', str(word_count)]))
    elif st < 0:
        print ('\t'.join([text, 'negative', str(word_count)]))
    else:
        print ('\t'.join([text, 'neutral', str(word_count)]))

Best regards

Highlighted

Re: How to make hive queries including scala and python functions?

Contributor
@Dan Zaratsian

Found the error:

createdAt, screenName, text = line.replace('\n',' ').split('\t')

It only works when I have only 1 variable. With more than 1 it crashes.

Is there any alternative to the split('\t') ?

Highlighted

Re: How to make hive queries including scala and python functions?

Contributor

@Dan Zaratsian

After some days I've reached the conclusion that the problem must be on the Json Serde because when I upload your table into Hive it works ok.

I'm currently using

json-serde-1.3.8-jar-with-dependencies.jar ...

Many thanks in advance.

Best regards

Re: How to make hive queries including scala and python functions?

@Hugo Felix

Thanks for the update! Hortonworks is currently on HDP 2.6, so if you have the option, it sounds like it would be beneficial to upgrade.

Also as a quick reference, the most recent Hortonworks Sandbox can be downloaded from here: https://hortonworks.com/downloads/#sandbox

Here's a link to the documentation as well: https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.6.1/index.html

Highlighted

Re: How to make hive queries including scala and python functions?

Contributor

@Dan Zaratsian

I don't see how updating HDP can solve my problem.

I think the problem maybe of the serde when creating the table.

I'm currently using this:

ADD JAR hdfs://192.168.0.73:8020/user/admin/oozie-workflows/lib/json-serde-1.3.8-jar-with-dependencies.jar;
CREATE EXTERNAL TABLE tweets (
  id bigint, 
  created_at string,
  source STRING,
   favorited BOOLEAN,
   retweeted_status STRUCT<
     text:STRING,
     user:STRUCT<screen_name:STRING,name:STRING>,
     retweet_count:INT>,
   entities STRUCT<
     urls:ARRAY<STRUCT<expanded_url:STRING>>,
     user_mentions:ARRAY<STRUCT<screen_name:STRING,name:STRING>>,
     hashtags:ARRAY<STRUCT<text:STRING>>>,
  lang string,
  retweet_count int,
  text string,
  user STRUCT<
     screen_name:STRING,
     name:STRING,
     friends_count:INT,
     followers_count:INT,
     statuses_count:INT,
     verified:BOOLEAN,
     utc_offset:INT,
     time_zone:STRING>
       )
PARTITIONED BY (datehour int)
ROW FORMAT SERDE 
'org.apache.hive.hcatalog.data.JsonSerDe' 
WITH SERDEPROPERTIES ( "ignore.malformed.json" = "true")
LOCATION
  'hdfs://192.168.0.73:8020/user/flume/tweets'
Highlighted

Re: How to make hive queries including scala and python functions?

@Hugo Felix

It could solve the problem because serdes are build-in and updated along with Hive updates. It works fine for me in recent versions of HDP, so that is why I wanted to mention it.

I saw that you opened another question specifically for the Hive serde issue.
Your original question "How to make hive queries include scala and python functions" is answered as part of my posts, so when you get a chance, could you please accept the best answer. There are a lot of responses in this thread, so that may help someone else out.

I do have one other thought to debug your JSON serde error. It could be that the way that you stored JSON within Hive is incorrect. If that is the case, then when you try to execute a python UDF against that Hive record, it isn't able to find the right structure. If you execute a "select *" against your hive table, how does the output look?

Highlighted

Re: How to make hive queries including scala and python functions?

Contributor

Hello @Dan Zaratsian and many thanks once more.
Sorry you're right, I've forgotten to select best answer.
I can make queries in the hive table with this serde, except when I query the text column.

For instance, if I use your script with ID and Lang columns it runs smoothly. If I query the text column, it gives error.

I'll try to update HDP to version 2.6.1.0 and then let you know.
Best regards

Don't have an account?
Coming from Hortonworks? Activate your account here