Support Forums : DataStax

DataStax Support Forums » DataStax Enterprise

Hive Query Problems

(13 posts) (5 voices)

Started 1 year ago by
Latest reply from
This topic is not a support question

Tags:

hive

Anonymous
Unregistered
I'm having a strange problem running a hive query against my Datastax Enterprise 1.0 cluster:

If I use the automatic cassandra to Hive mapping, then I can't execute a query with a group by:

select myfield, count(*) from mytable group by myfield;

it fails with the following error:
```
FAILED: Hive Internal Error: java.lang.NullPointerException(null)
java.lang.NullPointerException
        at org.apache.hadoop.hive.ql.exec.Utilities.getColumnNamesFromSortCols(Utilities.java:1314)
```
Just doing a select on myfield works (so the column name is valid), it's only when it's in a group by that it doesn't seem to work.

I tried doing a manual mapping of the column family to a hive table. In that case, i can execute the query above BUT all my numeric columns (ints and bigints) show up in Hive as null despite showing up correctly in Cassandra and in the automapped table. I did a describe extended mytable on both the auto-created table and the manually created table and verified that the datatypes match for all the columns.

Any thoughts would be greatly appreciated.
Posted 1 year ago #
tjake
Moderator

Hi,

There is an example on how to map numeric fields in the demo hive query.

There is a SERDE setting called cassandra.cf.validatorType

create external table StockHist(row_key string, column_name string, value double)
STORED BY 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler'
WITH SERDEPROPERTIES ("cassandra.ks.name" = "PortfolioDemo",
"cassandra.cf.validatorType" = "UTF8Type,UTF8Type,DoubleType"
);

So specifying this along with the name mapping may fix your issue.

Can you give us more info on the Cassandra CF columns and types so we can try to reproduce and see if there is a bug in the automapping?

Thanks

Posted 1 year ago #
Anonymous
Unregistered

That seemed to do the trick. Now I can see non-null values for the numeric columns. I can also execute the groupBy query as well. I have a new problem, though: the groupBy doesn't produce the expected results. I'm doing select myfield2, count(*) from mytable group by myfield2 but all I ever get is the touple NULL, XX (where XX is the total number of rows in my table) as output. If I do a select on the someStringcol column, I see the actual values. It just looks like whatever is responsible for doing the comparison of the strings in the group by returns NULL.

In response to your question about the Cassandra CF columns and types, here's the output of the describe on the column family:
`
ColumnFamily: MyTable
Key Validation Class: org.apache.cassandra.db.marshal.UUIDType
Default column value validator: org.apache.cassandra.db.marshal.BytesType
Columns sorted by: org.apache.cassandra.db.marshal.UTF8Type
Row cache size / save period in seconds / keys to save : 0.0/0/all
Key cache size / save period in seconds: 200000.0/14400
GC grace seconds: 864000
Compaction min/max thresholds: 4/32
Read repair chance: 1.0
Replicate on write: true
Built indexes: []
Column Metadata:
Column Name: myfield
Validation Class: org.apache.cassandra.db.marshal.UTF8Type
Column Name: myfield2
Validation Class: org.apache.cassandra.db.marshal.UTF8Type
Column Name: myfield3
Validation Class: org.apache.cassandra.db.marshal.UTF8Type
Column Name: myfield4
Validation Class: org.apache.cassandra.db.marshal.LongType
Column Name: myfield5
Validation Class: org.apache.cassandra.db.marshal.LongType
Column Name: myfield6
Validation Class: org.apache.cassandra.db.marshal.IntegerType
Column Name: myfield7
Validation Class: org.apache.cassandra.db.marshal.UTF8Type
Column Name: myfield8
Validation Class: org.apache.cassandra.db.marshal.UTF8Type
Column Name: myfield9
Validation Class: org.apache.cassandra.db.marshal.UTF8Type
Column Name: myfield10
Validation Class: org.apache.cassandra.db.marshal.LongType
Column Name: myfield11
Validation Class: org.apache.cassandra.db.marshal.LongType
Column Name: myfield12
Validation Class: org.apache.cassandra.db.marshal.IntegerType
Column Name: myfield13
Validation Class: org.apache.cassandra.db.marshal.UTF8Type
Column Name: myfield14
Validation Class: org.apache.cassandra.db.marshal.LongType
Column Name: myfield15
Validation Class: org.apache.cassandra.db.marshal.LongType
Column Name: myfield16
Validation Class: org.apache.cassandra.db.marshal.IntegerType
Column Name: myfield17
Validation Class: org.apache.cassandra.db.marshal.UTF8Type
Column Name: myfield18
Validation Class: org.apache.cassandra.db.marshal.UTF8Type
Column Name: myfield19
Validation Class: org.apache.cassandra.db.marshal.UTF8Type
Compaction Strategy: org.apache.cassandra.db.compaction.SizeTieredCompactionStrateg
1

Posted 1 year ago #
zznate

Can you post the hive table create script you used against the above schema as well as the query? That would help us try to reproduce.

Posted 1 year ago #

Anonymous
Unregistered

Here's the Hive table create script

create external table myhivetable(row_key string, myfield string, myfield2 string, myfield3 string,
  myfield4 int, myfield5 int,myfield6 bigint,
   myfield7 string, myfield8 string, myfield9 string,
   myfield10 int, myfield11 int, myfield12 bigint,
   myfield13 string, myfield14 int, myfield15 int,myfield16 bigint,
   myfield17 string, myfield18 string, myfield19 string)
  stored  by 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler'
  with
  serdeproperties  ("cassandra.columns.mapping" = ":key,myfield,myfield1,myfield2,myfield3,myfield4,myfield5,myfield6,myfield7,myfield8,myfield9,myfield10,myfield11,myfield21,myfield13,myfield14,myfield15,myfield16,myfield17,myfield18",
  "cassandra.cf.validatorType" = "org.apache.cassandra.db.marshal.UUIDType,org.apache.cassandra.db.marshal.UTF8Type,org.apache.cassandra.db.marshal.UTF8Type,org.apache.cassandra.db.marshal.UTF8Type,org.apache.cassandra.db.marshal.LongType,org.apache.cassandra.db.marshal.LongType,org.apache.cassandra.db.marshal.IntegerType,org.apache.cassandra.db.marshal.UTF8Type,org.apache.cassandra.db.marshal.UTF8Type,org.apache.cassandra.db.marshal.UTF8Type,org.apache.cassandra.db.marshal.LongType,org.apache.cassandra.db.marshal.LongType,org.apache.cassandra.db.marshal.IntegerType,org.apache.cassandra.db.marshal.UTF8Type,org.apache.cassandra.db.marshal.LongType,org.apache.cassandra.db.marshal.LongType,org.apache.cassandra.db.marshal.IntegerType,org.apache.cassandra.db.marshal.UTF8Type,org.apache.cassandra.db.marshal.UTF8Type,org.apache.cassandra.db.marshal.UTF8Type")
  tblproperties ("cassandra.range.size" = "100","cassandra.slice.predicate.size" = "100","cassandra.ks.name" = "MyKeyspace", "cassandra.cf.name" = "MyTable");

Posted 1 year ago #

zznate

We've opened an issue internally for investigating this further. I'll post an update here as soon as I know more.

Does groupBy work on any of the other types? Long or Integer?

Also, if you have a few rows of sample data, that would help. Contact me directly if you don't feel comfortable posting it here.

Thanks!

Posted 1 year ago #

Anonymous
Unregistered

I have the same issue for group by when I try grouping by a numeric column as well. So far as I can tell, this happens for all columns. I was looking more closely at the describe extended output for both my auto-created table and my manually created table. the only discernible difference I can see is that in the auto-created table, the numBuckets property is set to 0 whereas it is -1 in the manual table. Could this make a difference? If so, how can I modify that? I couldn't find anything in the documentation about that specifically.

Here are a few rows of data from the table:

01341453-9154-30d7-7e0e-d2f85f8e5970    A1 ABBCD28554   DESKTOP 419     1304929690000   0       ABABB74926   BFFCC73322   DESKTOP NULL    NULL    NULL    S1     0       1304928983000   -7       01341453-9154-30D7-7E0E-D2F85F8E5970    ABBCA702221
01461557-695d-40cf-a418-0ae5d2562dc7    A2 AAAFE31920  DESKTOP NULL    NULL    NULL    MBFFEE82655  MBFFEE69677  DESKTOP 300     1305910710000   305     S2     0       1305910386000   -4       01461557-695D-40CF-A418-0AE5D2562DC7    ABBCA243802
01427859-4097-2a60-9cbb-8e351d451feb    A3     FDEEA31765   DESKTOP 0       1299483991000   0       BFFEE115573  BFFEE73159   DESKTOP NULL    NULL    NULL    S1     0       1299483990000   -7       01427859-4097-2A60-9CBB-8E351D451FEB    ABBCA306426
0170f2e2-efb9-fa56-a3a9-fc5a395d9c59    A1 CCEEEE32078   DESKTOP NULL    NULL    NULL    BFFEE135412  BFFEE74397   DESKTOP 300     1305623569000   232     S1     0       1305623262000   -7       0170F2E2-EFB9-FA56-A3A9-FC5A395D9C59    ABBCA755429
00d64cc6-202f-314f-67dc-f53d40cb0169    A1 EFFGGA25558   DESKTOP 2388    1305522068000   4103    BFFEE99154   BFFEE71523   DESKTOP 300     1305461033000   2014    S2     0       1305460722000    0       -4      00D64CC6-202F-314F-67DC-F53D40CB0169    ABBCA922718

Posted 1 year ago #

zznate

the numBuckets property should not make much of a difference there.

Thanks for the sample data - I've added it to our internal issue tracking system. I'll update this post as soon as we have more information. Thanks for your patience - if there is anything else we can do to help, let us know.

Posted 1 year ago #

fmeyer
Member

@zznate,

I'm testing the same structure against 0.8 and whenever I try to use the "group by" statement I get the same error.

select bd.f1, count (DISTINCT bd.f2) from bd group by bd.f1;
        FAILED: Hive Internal Error: java.lang.NullPointerException(null)                                                           java.lang.NullPointerException                                                                                                      at org.apache.hadoop.hive.ql.exec.Utilities.getColumnNamesFromSortCols(Utilities.java:1314)                                 at org.apache.hadoop.hive.ql.optimizer.GroupByOptimizer$BucketGroupByProcessor.checkBucketGroupBy(GroupByOptimizer.java:196)                                                                                                                            at org.apache.hadoop.hive.ql.optimizer.GroupByOptimizer$BucketGroupByProcessor.process(GroupByOptimizer.java:128)           at org.apache.hadoop.hive.ql.lib.DefaultRuleDispatcher.dispatch(DefaultRuleDispatcher.java:89)
        at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.dispatch(DefaultGraphWalker.java:88)
        at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.walk(DefaultGraphWalker.java:128)
        at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.startWalking(DefaultGraphWalker.java:102)
        at org.apache.hadoop.hive.ql.optimizer.GroupByOptimizer.transform(GroupByOptimizer.java:92)
        at org.apache.hadoop.hive.ql.optimizer.Optimizer.optimize(Optimizer.java:85)
        at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:6625)
        at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:238)
        at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:340)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:736)
        at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:164)
        at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:241)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:456)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:616)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:156)

Posted 1 year ago #

zznate

If you want to post (or send direct) some sample schema and data i'll add this to the ticket.

Posted 1 year ago #
Anonymous
Unregistered

Nate,
Since it doesn't look like there is a workaround for this issue forthcoming, I've been trying to build custom map-reduce jobs to do what I need to do. Unfortunately, I'm having some issues using the ColumnFamilyInputFormat (mainly class cast exceptions on the output key format... no matter what I do, it always says it received a HeapByteBuffer instead of Text or LongWritable when I write the output in the mapper). Is there any documentation out there for writing custom map-reduce code when using DSE? I couldn't find anything on the site.

Posted 1 year ago #
zznate

We actually use the ColumnFamily[Input|Output]Format from the Cassandra source tree. The example examples directory there might be of some use as well:
http://svn.apache.org/viewvc/cassandra/branches/cassandra-1.0/examples/hadoop_word_count/src/

Posted 1 year ago #
Anonymous
Unregistered

Hi..
can anybody suggest me if I could aggregate all the values of a column in an array using Hive UDAF.

Posted 1 year ago #

RSS feed for this topic

Reply

You must log in to post.