DataStax Support Forums » User Favorites: dipesh

DataStax Support Forums » User Favorites: dipesh dipesh Software, Support, and Training for Apache Cassandra en-US Wed, 19 Jun 2013 22:18:30 +0000 http://bbpress.org/?v=1.0.3 <![CDATA[Search]]> q http://www.datastax.com/support-forums/search.php dipesh on "How do you use composite columns (from cassandra) in a hive table?" http://www.datastax.com/support-forums/topic/how-do-you-use-composite-columns-from-cassandra-in-a-hive-table/page/2#post-9072 Tue, 19 Feb 2013 17:02:21 +0000 dipesh 9072@http://www.datastax.com/support-forums/ Finally, I am back to this. Sorry for the delay. Composite Column Name = UTF8Type + LongType/IntegerType/UTF8Type Note: For all above combinations of second column, I am getting the same exception. My Doubts: - Looking at the exception log, "value" seems pretty odd. Is that a problem? - createCompositeKey method in the UDF java code, value of third (end-of-component) and fourth (lastIsOne) is hard coded to '0' and 'true' respectively. Frankly, I didn't understand it completely, even when I read the java-doc of Composite type several times. Please advise. Here is the Exception from the Log: <pre><code>2013-02-19 22:15:32,802 null map = 100%, reduce = 0% [2013-02-19 22:15:34,916] FATAL {ExecReducer} - org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row (tag=0) {"key":{"_col0":"CSCO","_col1":"price","_col2":1360821839400},"value":{"_col0":{"count":1,"sum":25.13}},"alias":0} at org.apache.hadoop.hive.ql.exec.ExecReducer.reduce(ExecReducer.java:256) at org.apache.hadoop.mapred.ReduceTask.runOldReducer(ReduceTask.java:518) at org.apache.hadoop.mapred.ReduceTask.run(ReduceTask.java:419) at org.apache.hadoop.mapred.LocalJobRunner$Job.run(LocalJobRunner.java:256) Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: java.io.IOException: InvalidRequestException(why:Not enough bytes to read value of component 0) at org.apache.hadoop.hive.ql.exec.FileSinkOperator.processOp(FileSinkOperator.java:603) at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:471) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:762) at org.apache.hadoop.hive.ql.exec.SelectOperator.processOp(SelectOperator.java:84) at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:471) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:762) at org.apache.hadoop.hive.ql.exec.SelectOperator.processOp(SelectOperator.java:84) at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:471) at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:762) at org.apache.hadoop.hive.ql.exec.GroupByOperator.forward(GroupByOperator.java:959) at org.apache.hadoop.hive.ql.exec.GroupByOperator.processAggr(GroupByOperator.java:798) at org.apache.hadoop.hive.ql.exec.GroupByOperator.processOp(GroupByOperator.java:724) at org.apache.hadoop.hive.ql.exec.Operator.process(Operator.java:471) at org.apache.hadoop.hive.ql.exec.ExecReducer.reduce(ExecReducer.java:247) ... 3 more Caused by: java.io.IOException: InvalidRequestException(why:Not enough bytes to read value of component 0) at org.apache.hadoop.hive.cassandra.output.CassandraAbstractPut.commitChanges(CassandraAbstractPut.java:69) at org.apache.hadoop.hive.cassandra.output.CassandraPut.write(CassandraPut.java:139) at org.apache.hadoop.hive.cassandra.output.HiveCassandraOutputFormat$1.write(HiveCassandraOutputFormat.java:69) at org.apache.hadoop.hive.ql.exec.FileSinkOperator.processOp(FileSinkOperator.java:589) ... 16 more</code></pre> Here is my UDF based on your sample code. <pre><code>package com.cisco.iep.hive.plugins; import java.nio.ByteBuffer; import org.apache.cassandra.utils.ByteBufferUtil; import org.apache.hadoop.hive.ql.exec.UDF; import org.apache.hadoop.io.BytesWritable; public class WriteCompositeStringLong extends UDF{ public BytesWritable evaluate(final String strCol, final Long longCol) { ByteBuffer byteBuff = createCompositeKey(strCol, longCol, 0, false); return new BytesWritable(byteBuff.array()); } private ByteBuffer createCompositeKey(String strCol, Long longCol, int endOfComponent, boolean lastIsOne) { ByteBuffer bytes = ByteBufferUtil.bytes(strCol); int totalSize = 0; if (strCol != null) { totalSize += 2 + bytes.remaining() + 1; if (longCol != null) { totalSize += 2 + 8 + 1; if (endOfComponent != -1) { totalSize += 2 + 1 + 1; } } } ByteBuffer bb = ByteBuffer.allocate(totalSize); if (strCol != null) { bb.putShort((short) bytes.remaining()); bb.put(bytes); bb.put(longCol == null && lastIsOne ? (byte) 1 : (byte) 0); if (longCol != null) { //bb.putShort((short) 16); //bb.put(UUIDGen.decompose(intCol)); bb.putShort((short) 8); //8 for Long value bb.putLong(longCol); bb.put(endOfComponent == -1 && lastIsOne ? (byte) 1 : (byte) 0); if (endOfComponent != -1) { // We are putting a byte only because our test use ints that // fit in a byte *and* IntegerType.fromString() will // return something compatible (i.e, putting a full int here // would break 'fromStringTest') bb.putShort((short) 1); bb.put((byte) endOfComponent); bb.put(lastIsOne ? (byte) 1 : (byte) 0); } } } bb.rewind(); return bb; } }</code></pre> I could paste the all other missing pieces like Hive Create Table script, Source Table details etc. I feel guilty of not resolving this issue yet even after your so much help from you already. Really appreciate your time and help over this!! dipesh on "Mapping composite column names on Hive" http://www.datastax.com/support-forums/topic/mapping-composite-column-names-on-hive#post-9018 Thu, 14 Feb 2013 11:45:49 +0000 dipesh 9018@http://www.datastax.com/support-forums/ If possible, can you please share a sample code for serializer and deserializer? And/or provide necessary pointers that could help in that regard. Here is what I have, but not working. <pre><code>package com.cisco.iep.hive.plugins; import java.io.ByteArrayOutputStream; import java.nio.ByteBuffer; import java.util.ArrayList; import java.util.List; import org.apache.hadoop.hive.ql.exec.UDF; import org.apache.hadoop.io.BytesWritable; import org.apache.hadoop.io.LongWritable; import org.apache.hadoop.io.Text; public class WriteCompositeLongString extends UDF{ public BytesWritable evaluate(final LongWritable l, final Text s) { List<byte[]> b = new ArrayList<byte[]>(2); b.add(longWritableToByteArray(l)); b.add(textToByteArray(s)); return new BytesWritable(makeComposite(b)); } private byte[] textToByteArray(Text s){ if(s==null){ return null; } return s.getBytes(); } private byte[] longWritableToByteArray(final LongWritable l){ if(l==null){ return null; } return ByteBuffer.allocate(Long.SIZE).putLong(l.get()).array(); } private byte[] makeComposite(final List<byte[]> b) { ByteArrayOutputStream bos = new ByteArrayOutputStream(); for (int i = 0; i < b.size(); i++) { bos.write((byte) ((b.get(i).length >> (7 + 1)) & 0xFF)); bos.write((byte) (b.get(i).length & 0xFF)); for (int j = 0; j < b.get(i).length; j++) { bos.write(b.get(i)[j] & 0xFF); } bos.write((byte) 0); } return bos.toByteArray(); } }</code></pre> Thanks in advance!! dipesh on "How do you use composite columns (from cassandra) in a hive table?" http://www.datastax.com/support-forums/topic/how-do-you-use-composite-columns-from-cassandra-in-a-hive-table/page/2#post-8989 Mon, 11 Feb 2013 05:23:03 +0000 dipesh 8989@http://www.datastax.com/support-forums/ Thanks again, however I will need couple of more days to try out due to other priorities. alexliu on "How do you use composite columns (from cassandra) in a hive table?" http://www.datastax.com/support-forums/topic/how-do-you-use-composite-columns-from-cassandra-in-a-hive-table#post-8945 Wed, 06 Feb 2013 17:56:32 +0000 alexliu 8945@http://www.datastax.com/support-forums/ Here is composite column encoding according to the source code /* * The encoding of a CompositeType column name should be: * <component><component><component> ... * where <component> is: * <length of value><value><'end-of-component' byte> * where <length of value> is a 2 bytes unsigned short the and the * 'end-of-component' byte should always be 0 for actual column name. * However, it can set to 1 for query bounds. This allows to query for the * equivalent of 'give me the full super-column'. That is, if during a slice * query uses: * start = <3><"foo".getBytes()><0> * end = <3><"foo".getBytes()><1> * then he will be sure to get *all* the columns whose first component is "foo". * If for a component, the 'end-of-component' is != 0, there should not be any * following component. The end-of-component can also be -1 to allow * non-inclusive query. For instance: * start = <3><"foo".getBytes()><-1> * allows to query everything that is greater than <3><"foo".getBytes()>, but * not <3><"foo".getBytes()> itself. */ here is a sample code to create encoding composite column private ByteBuffer createCompositeKey(String s, UUID uuid, int i, boolean lastIsOne) { ByteBuffer bytes = ByteBufferUtil.bytes(s); int totalSize = 0; if (s != null) { totalSize += 2 + bytes.remaining() + 1; if (uuid != null) { totalSize += 2 + 16 + 1; if (i != -1) { totalSize += 2 + 1 + 1; } } } ByteBuffer bb = ByteBuffer.allocate(totalSize); if (s != null) { bb.putShort((short) bytes.remaining()); bb.put(bytes); bb.put(uuid == null && lastIsOne ? (byte)1 : (byte)0); if (uuid != null) { bb.putShort((short) 16); bb.put(UUIDGen.decompose(uuid)); bb.put(i == -1 && lastIsOne ? (byte)1 : (byte)0); if (i != -1) { // We are putting a byte only because our test use ints that fit in a byte *and* IntegerType.fromString() will // return something compatible (i.e, putting a full int here would break 'fromStringTest') bb.putShort((short) 1); bb.put((byte)i); bb.put(lastIsOne ? (byte)1 : (byte)0); } } } bb.rewind(); return bb; } You need change your code to the same encoding as the composite column name dipesh on "How do you use composite columns (from cassandra) in a hive table?" http://www.datastax.com/support-forums/topic/how-do-you-use-composite-columns-from-cassandra-in-a-hive-table#post-8942 Wed, 06 Feb 2013 16:58:47 +0000 dipesh 8942@http://www.datastax.com/support-forums/ Thanks alexliu!! I could get Composite Column working on SELECT statement, however got an exception when tried to write data into Composite Column using following hive script and custom UDF. Here is relevant log on Exception: <blockquote> [2013-02-06 22:23:15,252] FATAL {ExecReducer} - org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row (tag=0) {"key":{"_col0":"blr_01","_col1":1350465782,"_col2":"WARN"},"value":{"_col0":1},"alias":0} at org.apache.hadoop.hive.ql.exec.ExecReducer.reduce(ExecReducer.java:256) at org.apache.hadoop.mapred.ReduceTask.runOldReducer(ReduceTask.java:518) at org.apache.hadoop.mapred.ReduceTask.run(ReduceTask.java:419) at org.apache.hadoop.mapred.LocalJobRunner$Job.run(LocalJobRunner.java:256) Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: java.io.IOException: InvalidRequestException(why:Not enough bytes to read value of component 0) at org.apache.hadoop.hive.ql.exec.FileSinkOperator.processOp(FileSinkOperator.java:603) ... </blockquote> Here is my hive script: <pre><code>CREATE TEMPORARY FUNCTION setCompositeLongString AS 'com.cisco.iep.hive.plugins.WriteCompositeLongString'; INSERT OVERWRITE TABLE TestTable SELECT src_id, setCompositeLongString(cast(alert_time as BIGINT), severity), COUNT(alert_type) FROM alert_instance GROUP BY src_id, cast(alert_time as BIGINT), severity;</code></pre> And here is custom UDF to write data into Composite Column: <pre><code>package com.cisco.iep.hive.plugins; import java.io.ByteArrayOutputStream; import java.nio.ByteBuffer; import java.util.ArrayList; import java.util.List; import org.apache.hadoop.hive.ql.exec.UDF; import org.apache.hadoop.io.BytesWritable; import org.apache.hadoop.io.LongWritable; import org.apache.hadoop.io.Text; public class WriteCompositeLongString extends UDF{ public BytesWritable evaluate(final LongWritable l, final Text s) { List<byte[]> b = new ArrayList<byte[]>(2); b.add(longWritableToByteArray(l)); b.add(textToByteArray(s)); return new BytesWritable(makeComposite(b)); } private byte[] textToByteArray(Text s){ if(s==null){ return null; } return s.getBytes(); } private byte[] longWritableToByteArray(final LongWritable l){ if(l==null){ return null; } return ByteBuffer.allocate(Long.SIZE).putLong(l.get()).array(); } private byte[] makeComposite(final List<byte[]> b) { ByteArrayOutputStream bos = new ByteArrayOutputStream(); for (int i = 0; i < b.size(); i++) { bos.write((byte) ((b.get(i).length >> (7 + 1)) & 0xFF)); bos.write((byte) (b.get(i).length & 0xFF)); for (int j = 0; j < b.get(i).length; j++) { bos.write(b.get(i)[j] & 0xFF); } bos.write((byte) 0); } return bos.toByteArray(); } }</code></pre> Please advise. alexliu on "How do you use composite columns (from cassandra) in a hive table?" http://www.datastax.com/support-forums/topic/how-do-you-use-composite-columns-from-cassandra-in-a-hive-table#post-8747 Mon, 28 Jan 2013 20:24:56 +0000 alexliu 8747@http://www.datastax.com/support-forums/ non-cql composite row key is the same format as a composite column. But cql composite row key is in a little different format, so you should modify the UDF for cql composite row key. alexliu on "How do you use composite columns (from cassandra) in a hive table?" http://www.datastax.com/support-forums/topic/how-do-you-use-composite-columns-from-cassandra-in-a-hive-table#post-8746 Mon, 28 Jan 2013 19:24:07 +0000 alexliu 8746@http://www.datastax.com/support-forums/ you should be able to do the same to Composite Row Key, because that composite row key is stored the same format as composite column. Basically you retrieve the key as binary data, then use UDF to retrieve/parse the data. dipesh on "How do you use composite columns (from cassandra) in a hive table?" http://www.datastax.com/support-forums/topic/how-do-you-use-composite-columns-from-cassandra-in-a-hive-table#post-8593 Wed, 23 Jan 2013 06:38:08 +0000 dipesh 8593@http://www.datastax.com/support-forums/ I am wondering, will this technique work with Composite Row Key as well? So the table (CF) would look like this: create column family TestTable with key_validation_class = 'CompositeType (UTF8Type, UTF8Type)' and comparator = 'UTF8Type' ; pierantonio.merlino on "Mapping composite column names on Hive" http://www.datastax.com/support-forums/topic/mapping-composite-column-names-on-hive#post-7442 Thu, 08 Nov 2012 14:07:24 +0000 pierantonio.merlino 7442@http://www.datastax.com/support-forums/ Thank you for the answer. I solved the problem mapping the composite columns in to a struct in Hive and writing a custom serializer/deserializer. Piotr Kołaczkowski on "Mapping composite column names on Hive" http://www.datastax.com/support-forums/topic/mapping-composite-column-names-on-hive#post-7159 Mon, 29 Oct 2012 09:37:26 +0000 Piotr Kołaczkowski 7159@http://www.datastax.com/support-forums/ There is no dedicated support in Hive for composite columns. In wide-row mode, it scans each row sequentially and fetches each column to produce a triple (row-key, column-name, value). Outputting as triples is hardcoded. Anyway, this could make a nice feature-request. pierantonio.merlino on "Mapping composite column names on Hive" http://www.datastax.com/support-forums/topic/mapping-composite-column-names-on-hive#post-6763 Thu, 04 Oct 2012 10:00:30 +0000 pierantonio.merlino 6763@http://www.datastax.com/support-forums/ Hi, I've to map Cassandra columns families on Hive tables. Since I've dynamic fields in Cassandra, I use the following string <pre><code>CREATE EXTERNAL TABLE Table1 (keyName string, columnName string, value string) STORED BY 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler' WITH SERDEPROPERTIES ("cassandra.columns.mapping" = ":key,:column,:value" );</code></pre> in order to create the external table on Hive. The result is a table where each row is as follows: <code>rowName1 columnName1 value1</code> <code>rowName2 columnName2 value2</code> ... Since I use composite column names in Cassandra (Composite(columnNameA, columnNameB)), is it possible to map them into two different columns on Hive? What I want is as follows: <code>rowName1 columnNameA1 columnNameB1 value1</code>. How can I map these columns on Hive? Thanks in advance. tjake on "How do you use composite columns (from cassandra) in a hive table?" http://www.datastax.com/support-forums/topic/how-do-you-use-composite-columns-from-cassandra-in-a-hive-table#post-1458 Thu, 29 Mar 2012 15:52:29 +0000 tjake 1458@http://www.datastax.com/support-forums/ awsome, thanks! Anonymous on "How do you use composite columns (from cassandra) in a hive table?" http://www.datastax.com/support-forums/topic/how-do-you-use-composite-columns-from-cassandra-in-a-hive-table#post-1457 Thu, 29 Mar 2012 15:51:33 +0000 Anonymous 1457@http://www.datastax.com/support-forums/ I got it. Here is the UDF that works <pre><code>import java.nio.ByteBuffer; import org.apache.hadoop.hive.ql.exec.UDF; import org.apache.hadoop.io.BytesWritable; public final class TestExtract extends UDF { public String evaluate(final BytesWritable s) { if (s == null) { return null; } // byte buffer ByteBuffer bb = ByteBuffer.wrap(s.getBytes()); // first part of composite int colASize = (bb.get() & 0xFF) << (7 + 1); colASize = (bb.get() & 0xFF); byte[] colA = new byte[colASize]; bb.get(colA); ByteBuffer bf = ByteBuffer.wrap(colA); bb.get(); // second part of composite int colBSize = (bb.get() & 0xFF) << (7 + 1); colBSize = (bb.get() & 0xFF); byte[] colB = new byte[colBSize]; bb.get(colB); // return concatenated string return new String(bf.getLong() + "_" + new String(colB)); } }</code></pre> Anonymous on "How do you use composite columns (from cassandra) in a hive table?" http://www.datastax.com/support-forums/topic/how-do-you-use-composite-columns-from-cassandra-in-a-hive-table#post-1451 Wed, 28 Mar 2012 22:38:15 +0000 Anonymous 1451@http://www.datastax.com/support-forums/ Here is what the data looks like from cassandra-cli (column=1332972763782:Test1, value=abc123, timestamp=1332972760270111) (column=1332972763782:Test2, value=test132, timestamp=1332972760269241) (column=1332972763782:Test3, value=blah, timestamp=1332972760270101) (column=1332972763782:Test4, value=blah2, timestamp=1332972760269340) (column=1332972763782:Test5, value=hello123, timestamp=1332972760270320) Anonymous on "How do you use composite columns (from cassandra) in a hive table?" http://www.datastax.com/support-forums/topic/how-do-you-use-composite-columns-from-cassandra-in-a-hive-table#post-1449 Wed, 28 Mar 2012 22:26:32 +0000 Anonymous 1449@http://www.datastax.com/support-forums/ I went ahead and created a test example with DSE 2.0. I am still getting an error. Can you please confirm if my mapping is wrong create keyspace TestData; -- column family, compositeType columns are (timeinmiilis, string) create column family TestTable with key_validation_class = UTF8Type and comparator = 'CompositeType (LongType, UTF8Type)' and comment = 'A Test Column Family' and compression_options={sstable_compression:SnappyCompressor, chunk_length_kb:6}; // test UDF java program for hive, it concatenates the long and string with an under_score in the middle package com.foo.hive.udf; import java.nio.ByteBuffer; import org.apache.hadoop.hive.ql.exec.UDF; import org.apache.hadoop.io.BytesWritable; public final class TestExtract extends UDF { public String evaluate(final BytesWritable s) { if (s == null) { return null; } // byte buffer ByteBuffer bb = ByteBuffer.wrap(s.getBytes()); // header byte bb.get(); // first part of composite int colASize = (bb.get() & 0xFF) << (7 + 1); colASize = (bb.get() & 0xFF); byte[] colA = new byte[colASize]; bb.get(colA); // second part of composite int colBSize = (bb.get() & 0xFF) << (7 + 1); colBSize = (bb.get() & 0xFF); byte[] colB = new byte[colBSize]; bb.get(colB); // return concatenated string return new String(new String(colA) + '_' + new String(colB)); } } --export the jar export HIVE_AUX_JARS_PATH=/tmp/custom.jar --login to hive use TestData; -- drop if exists drop table TestTable; --create hive mapping CREATE EXTERNAL TABLE TestTable (row_key string, param_name binary, param_value string) STORED BY 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler' WITH SERDEPROPERTIES ("cassandra.ks.name" = "TestData", "cassandra.columns.mapping" = ":key,:column,:value", "cassandra.cf.validatortype"= "BytesType, BytesType, BytesType") TBLPROPERTIES ( "cassandra.ks.name" = "TestData", "cassandra.range.size" = "100", "cassandra.slice.predicate.size" = "100"); -- create temp function create temporary function my_test as 'com.foo.hive.udf.TestExtract'; -- select select row_key, my_test(param_name), param_value from TestTable limit 5; --output comes out as e8cf5f471d0211e18c8e6431504ba7b8 _6 abc123 e8cf5f471d0211e18c8e6431504ba7b8 _6 test132 e8cf5f471d0211e18c8e6431504ba7b8 _6 blah e8cf5f471d0211e18c8e6431504ba7b8 _6 blah2 e8cf5f471d0211e18c8e6431504ba7b8 _6 hello123 What am I doing wrong here?