Monday, November 7, 2016

SparkRDD MongoDB Example



donghua@ubuntu:~$ ./spark-1.6.1/bin/spark-shell --packages org.mongodb.spark:mongo-spark-connector_2.10:0.1 --conf "spark.mongodb.input.uri=mongodb://127.0.01/nasa.eva" --conf "spark.mongodb.output.uri=mongodb://127.0.0.1/nasa.astronautTotals"
Ivy Default Cache set to: /home/donghua/.ivy2/cache
The jars for the packages stored in: /home/donghua/.ivy2/jars
:: loading settings :: url = jar:file:/home/donghua/spark-1.6.1/lib/spark-assembly-1.6.1-hadoop2.6.0.jar!/org/apache/ivy/core/settings/ivysettings.xml
org.mongodb.spark#mongo-spark-connector_2.10 added as a dependency
:: resolving dependencies :: org.apache.spark#spark-submit-parent;1.0
        confs: [default]
        found org.mongodb.spark#mongo-spark-connector_2.10;0.1 in central
        found org.mongodb#mongo-java-driver;3.2.2 in central
:: resolution report :: resolve 343ms :: artifacts dl 9ms
        :: modules in use:
        org.mongodb#mongo-java-driver;3.2.2 from central in [default]
        org.mongodb.spark#mongo-spark-connector_2.10;0.1 from central in [default]
        ---------------------------------------------------------------------
        |                  |            modules            ||   artifacts   |
        |       conf       | number| search|dwnlded|evicted|| number|dwnlded|
        ---------------------------------------------------------------------
        |      default     |   2   |   0   |   0   |   0   ||   2   |   0   |
        ---------------------------------------------------------------------
:: retrieving :: org.apache.spark#spark-submit-parent
        confs: [default]
        0 artifacts copied, 2 already retrieved (0kB/9ms)
log4j:WARN No appenders could be found for logger (org.apache.hadoop.metrics2.lib.MutableMetricsFactory).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
Using Spark's repl log4j profile: org/apache/spark/log4j-defaults-repl.properties
To adjust logging level use sc.setLogLevel("INFO")
Welcome to
      ____              __
     / __/__  ___ _____/ /__
    _\ \/ _ \/ _ `/ __/  '_/
   /___/ .__/\_,_/_/ /_/\_\   version 1.6.1
      /_/

Using Scala version 2.10.5 (Java HotSpot(TM) 64-Bit Server VM, Java 1.8.0_111)
Type in expressions to have them evaluated.
Type :help for more information.
16/11/07 21:21:53 WARN Utils: Your hostname, ubuntu resolves to a loopback address: 127.0.1.1; using 192.168.6.152 instead (on interface ens33)
16/11/07 21:21:53 WARN Utils: Set SPARK_LOCAL_IP if you need to bind to another address
Spark context available as sc.
16/11/07 21:21:58 WARN Connection: BoneCP specified but not present in CLASSPATH (or one of dependencies)
16/11/07 21:21:58 WARN Connection: BoneCP specified but not present in CLASSPATH (or one of dependencies)
16/11/07 21:22:04 WARN ObjectStore: Version information not found in metastore. hive.metastore.schema.verification is not enabled so recording the schema version 1.2.0
16/11/07 21:22:05 WARN ObjectStore: Failed to get database default, returning NoSuchObjectException
16/11/07 21:22:07 WARN Connection: BoneCP specified but not present in CLASSPATH (or one of dependencies)
16/11/07 21:22:08 WARN Connection: BoneCP specified but not present in CLASSPATH (or one of dependencies)
SQL context available as sqlContext.

scala> import com.mongodb.spark._
import com.mongodb.spark._

scala> import com.mongodb.spark.rdd.MongoRDD
import com.mongodb.spark.rdd.MongoRDD

scala> import org.bson.Document
import org.bson.Document

scala> var rdd = sc.loadFromMongoDB()
rdd: com.mongodb.spark.rdd.MongoRDD[org.bson.Document] = MongoRDD[0] at RDD at MongoRDD.scala:160

scala> print(rdd.count())
375
scala> print(rdd.first())
Document{{_id=58207883126541824d4bcf58, EVA #=1, Country=USA, Crew=Ed White, Vehicle=Gemini IV, Date=06/03/1965, Duration=0:36, Purpose=First U.S. EVA. Used HHMU and took  photos.  Gas flow cooling of 25ft umbilical overwhelmed by vehicle ingress work and helmet fogged.  Lost overglove.  Jettisoned thermal gloves and helmet sun visor}}
scala> exit;
warning: there were 1 deprecation warning(s); re-run with -deprecation for details


# part 2, inside spark-shell
## Example to set new namespace without using command line parameters

scala> import com.mongodb.spark._
import com.mongodb.spark._

scala> import com.mongodb.spark.rdd.MongoRDD
import com.mongodb.spark.rdd.MongoRDD

scala> import org.bson.Document
import org.bson.Document

scala> import com.mongodb.spark.config._
import com.mongodb.spark.config._

scala> var readConf = ReadConfig(sc)
readConf: com.mongodb.spark.config.ReadConfig.Self = ReadConfig(nasa,eva,Some(mongodb://127.0.01/nasa.eva),1000,64,_id,15,ReadPreferenceConfig(primary,None),ReadConcernConfig(None))

scala> var readConf2 = ReadConfig(Map("collection"->"eva"), Some(readConf))
readConf2: com.mongodb.spark.config.ReadConfig = ReadConfig(nasa,eva,Some(mongodb://127.0.01/nasa.eva),1000,64,_id,15,ReadPreferenceConfig(primary,None),ReadConcernConfig(None))

scala> val newRDD = sc.loadFromMongoDB(readConfig=readConf2)
newRDD: com.mongodb.spark.rdd.MongoRDD[org.bson.Document] = MongoRDD[0] at RDD at MongoRDD.scala:160


scala> newRDD.first()
res1: org.bson.Document = Document{{_id=58207883126541824d4bcf58, EVA #=1, Country=USA, Crew=Ed White, Vehicle=Gemini IV, Date=06/03/1965, Duration=0:36, Purpose=First U.S. EVA. Used HHMU and took  photos.  Gas flow cooling of 25ft umbilical overwhelmed by vehicle ingress work and helmet fogged.  Lost overglove.  Jettisoned thermal gloves and helmet sun visor}}

Monday, October 17, 2016

Example on datetimeformat in cqlsh copy command to transfer data in/out of Cassandra

donghua@cqlsh:mykeyspace> create table t3 (id int, d timestamp, primary key(id));
donghua@cqlsh:mykeyspace> desc t3;

CREATE TABLE mykeyspace.t3 (
    id int PRIMARY KEY,
    d timestamp
) WITH bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
    AND comment = ''
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.1
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND read_repair_chance = 0.0
    AND speculative_retry = '99PERCENTILE';
donghua@cqlsh:mykeyspace> insert into t3 (id,d) values (1,totimestamp(now()));
donghua@cqlsh:mykeyspace> insert into t3 (id,d) values (2,'2016-10-17T10:00:00');
donghua@cqlsh:mykeyspace> insert into t3 (id,d) values (3,'2016-10-17T10:00:00+0800');
donghua@cqlsh:mykeyspace> insert into t3 (id,d) values (4,'2016-10-17T10:00:00+0000');
donghua@cqlsh:mykeyspace> select * from t3;

 id | d
----+---------------------------------
  1 | 2016-10-17 10:20:35.416000+0000
  2 | 2016-10-17 02:00:00.000000+0000
  4 | 2016-10-17 10:00:00.000000+0000
  3 | 2016-10-17 02:00:00.000000+0000

(4 rows)

donghua@cqlsh:mykeyspace> copy t3 TO '/tmp/t3-1.csv' with header=true;
Reading options from the command line: {'header': 'true'}
Using 1 child processes

Starting copy of mykeyspace.t3 with columns [id, d].
Processed: 4 rows; Rate:       5 rows/s; Avg. rate:       4 rows/s
4 rows exported to 1 files in 0.945 seconds.


ddonghua@cqlsh:mykeyspace> copy t3 TO '/tmp/t3-2.csv' with header=true and datetimeformat='%m/%d/%Y';
Reading options from the command line: {'datetimeformat': '%m/%d/%Y', 'header': 'true'}
Using 1 child processes

Starting copy of mykeyspace.t3 with columns [id, d].
Processed: 4 rows; Rate:       5 rows/s; Avg. rate:       4 rows/s
4 rows exported to 1 files in 1.070 seconds.


[donghua@localhost ~]$ cat /tmp/t3-1.csv
id,d
2,2016-10-17 02:00:00.000+0000
1,2016-10-17 10:20:35.416+0000
3,2016-10-17 02:00:00.000+0000
4,2016-10-17 10:00:00.000+0000
[donghua@localhost ~]$ cat /tmp/t3-2.csv
id,d
2,10/17/2016
1,10/17/2016
3,10/17/2016
4,10/17/2016


donghua@cqlsh:mykeyspace> truncate table t3;
donghua@cqlsh:mykeyspace> select * from t3;

 id | d
----+---

(0 rows)
donghua@cqlsh:mykeyspace> copy t3 from '/tmp/t3-1.csv' with header=true;
Reading options from the command line: {'header': 'true'}
Using 1 child processes

Starting copy of mykeyspace.t3 with columns [id, d].
Processed: 4 rows; Rate:       4 rows/s; Avg. rate:       7 rows/s
4 rows imported from 1 files in 0.548 seconds (0 skipped).
donghua@cqlsh:mykeyspace> select * from t3;

 id | d
----+---------------------------------
  1 | 2016-10-17 10:20:35.416000+0000
  2 | 2016-10-17 02:00:00.000000+0000
  4 | 2016-10-17 10:00:00.000000+0000
  3 | 2016-10-17 02:00:00.000000+0000

(4 rows)

donghua@cqlsh:mykeyspace> truncate table t3;
donghua@cqlsh:mykeyspace> copy t3 from '/tmp/t3-2.csv' with header=true and datetimeformat='%m/%d/%Y';
Reading options from the command line: {'datetimeformat': '%m/%d/%Y', 'header': 'true'}
Using 1 child processes

Starting copy of mykeyspace.t3 with columns [id, d].
Processed: 4 rows; Rate:       5 rows/s; Avg. rate:       7 rows/s
4 rows imported from 1 files in 0.544 seconds (0 skipped).
donghua@cqlsh:mykeyspace> select * from t3;

 id | d
----+---------------------------------
  1 | 2016-10-17 00:00:00.000000+0000
  2 | 2016-10-17 00:00:00.000000+0000
  4 | 2016-10-17 00:00:00.000000+0000
  3 | 2016-10-17 00:00:00.000000+0000

(4 rows)
donghua@cqlsh:mykeyspace>



Thursday, October 13, 2016

CassandraSQLContext not found after spark-cassandra-connector upgrade to v2.0

scala> val cass=new CassandraSQLContext(sc)
:33: error: not found: type CassandraSQLContext
       val cass=new CassandraSQLContext(sc)
                    ^

scala> import org.apache.spark.sql.cassandra.CassandraSQLContext
:32: error: object CassandraSQLContext is not a member of package org.apache.spark.sql.cassandra
       import org.apache.spark.sql.cassandra.CassandraSQLContext
              ^
==================
https://github.com/datastax/spark-cassandra-connector/blob/07b47effeb10480b4b80b2686d0e6874aefa0a24/CHANGES.txt


2.0.0 M1
* Added support for left outer joins with C* table (SPARKC-181)
* Removed CassandraSqlContext and underscore based options (SPARKC-399)
* Upgrade to Spark 2.0.0-preview (SPARKC-396)
- Removed Twitter demo because there is no spark-streaming-twitter package available anymore
- Removed Akka Actor demo becaues there is no support for such streams anymore
- Bring back Kafka project and make it compile
- Update several classes to use our Logging instead of Spark Logging because Spark Logging became private
- Update few components and tests to make them work with Spark 2.0.0
- Fix Spark SQL - temporarily
- Update plugins and Scala version



Intergrate Apache Spark with after enabling user authorization in Apache Casasndra

[donghua@localhost conf]$  $SPARK_HOME/bin/spark-sql --hiveconf spark.cassandra.connection.host=127.0.0.1 --hiveconf spark.cassandra.auth.username=donghua --hiveconf spark.cassandra.auth.password=new_pasword
16/10/13 21:44:09 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
16/10/13 21:44:23 WARN ObjectStore: Version information not found in metastore. hive.metastore.schema.verification is not enabled so recording the schema version 1.2.0
16/10/13 21:44:23 WARN ObjectStore: Failed to get database default, returning NoSuchObjectException
16/10/13 21:44:25 WARN Utils: Your hostname, localhost.localdomain resolves to a loopback address: 127.0.0.1; using 10.0.2.15 instead (on interface enp0s3)
16/10/13 21:44:25 WARN Utils: Set SPARK_LOCAL_IP if you need to bind to another address
16/10/13 21:44:27 WARN SparkContext: Use an existing SparkContext, some configuration may not take effect.
spark-sql> create TEMPORARY TABLE t1 USING org.apache.spark.sql.cassandra OPTIONS (table "t1",keyspace "mykeyspace", cluster "Test Cluster",pushdown "true");
16/10/13 21:44:40 WARN SparkStrategies$DDLStrategy: CREATE TEMPORARY TABLE t1 USING... is deprecated, please use CREATE TEMPORARY VIEW viewName USING... instead
16/10/13 21:44:42 ERROR SparkSQLDriver: Failed in [create TEMPORARY TABLE t1 USING org.apache.spark.sql.cassandra OPTIONS (table "t1",keyspace "mykeyspace", cluster "Test Cluster",pushdown "true")]
com.datastax.driver.core.exceptions.UnauthorizedException: User donghua has no SELECT permission on or any of its parents

        at com.datastax.driver.core.exceptions.UnauthorizedException.copy(UnauthorizedException.java:59)
        at com.datastax.driver.core.exceptions.UnauthorizedException.copy(UnauthorizedException.java:25)
        at com.datastax.driver.core.DriverThrowables.propagateCause(DriverThrowables.java:37)
        at com.datastax.driver.core.DefaultResultSetFuture.getUninterruptibly(DefaultResultSetFuture.java:245)
        at com.datastax.driver.core.AbstractSession.execute(AbstractSession.java:64)
        at com.datastax.driver.core.AbstractSession.execute(AbstractSession.java:47)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at com.datastax.spark.connector.cql.SessionProxy.invoke(SessionProxy.scala:33)
        at com.sun.proxy.$Proxy29.execute(Unknown Source)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at com.datastax.spark.connector.cql.SessionProxy.invoke(SessionProxy.scala:33)
        at com.sun.proxy.$Proxy29.execute(Unknown Source)
        at com.datastax.spark.connector.rdd.partitioner.DataSizeEstimates$$anonfun$tokenRanges$1.apply(DataSizeEstimates.scala:40)
        at com.datastax.spark.connector.rdd.partitioner.DataSizeEstimates$$anonfun$tokenRanges$1.apply(DataSizeEstimates.scala:38)
        at com.datastax.spark.connector.cql.CassandraConnector$$anonfun$withSessionDo$1.apply(CassandraConnector.scala:111)
        at com.datastax.spark.connector.cql.CassandraConnector$$anonfun$withSessionDo$1.apply(CassandraConnector.scala:110)
        at com.datastax.spark.connector.cql.CassandraConnector.closeResourceAfterUse(CassandraConnector.scala:140)
        at com.datastax.spark.connector.cql.CassandraConnector.withSessionDo(CassandraConnector.scala:110)
        at com.datastax.spark.connector.rdd.partitioner.DataSizeEstimates.tokenRanges$lzycompute(DataSizeEstimates.scala:38)
        at com.datastax.spark.connector.rdd.partitioner.DataSizeEstimates.tokenRanges(DataSizeEstimates.scala:37)
        at com.datastax.spark.connector.rdd.partitioner.DataSizeEstimates.totalDataSizeInBytes$lzycompute(DataSizeEstimates.scala:88)
        at com.datastax.spark.connector.rdd.partitioner.DataSizeEstimates.totalDataSizeInBytes(DataSizeEstimates.scala:87)
        at org.apache.spark.sql.cassandra.CassandraSourceRelation$.apply(CassandraSourceRelation.scala:260)
        at org.apache.spark.sql.cassandra.DefaultSource.createRelation(DefaultSource.scala:55)
        at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:330)
        at org.apache.spark.sql.execution.datasources.CreateTempViewUsing.run(ddl.scala:87)
        at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult$lzycompute(commands.scala:60)
        at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult(commands.scala:58)
        at org.apache.spark.sql.execution.command.ExecutedCommandExec.doExecute(commands.scala:74)
        at org.apache.spark.sql.execution.SparkPlan$$anonfun$execute$1.apply(SparkPlan.scala:115)
        at org.apache.spark.sql.execution.SparkPlan$$anonfun$execute$1.apply(SparkPlan.scala:115)
        at org.apache.spark.sql.execution.SparkPlan$$anonfun$executeQuery$1.apply(SparkPlan.scala:136)
        at org.apache.spark.rdd.RDDOperationScope$.withScope(RDDOperationScope.scala:151)
        at org.apache.spark.sql.execution.SparkPlan.executeQuery(SparkPlan.scala:133)
        at org.apache.spark.sql.execution.SparkPlan.execute(SparkPlan.scala:114)
        at org.apache.spark.sql.execution.QueryExecution.toRdd$lzycompute(QueryExecution.scala:86)
        at org.apache.spark.sql.execution.QueryExecution.toRdd(QueryExecution.scala:86)
        at org.apache.spark.sql.Dataset.(Dataset.scala:186)
        at org.apache.spark.sql.Dataset.(Dataset.scala:167)
        at org.apache.spark.sql.Dataset$.ofRows(Dataset.scala:65)
        at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:582)
        at org.apache.spark.sql.SQLContext.sql(SQLContext.scala:682)
        at org.apache.spark.sql.hive.thriftserver.SparkSQLDriver.run(SparkSQLDriver.scala:62)
        at org.apache.spark.sql.hive.thriftserver.SparkSQLCLIDriver.processCmd(SparkSQLCLIDriver.scala:331)
        at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:376)
        at org.apache.spark.sql.hive.thriftserver.SparkSQLCLIDriver$.main(SparkSQLCLIDriver.scala:247)
        at org.apache.spark.sql.hive.thriftserver.SparkSQLCLIDriver.main(SparkSQLCLIDriver.scala)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.spark.deploy.SparkSubmit$.org$apache$spark$deploy$SparkSubmit$$runMain(SparkSubmit.scala:736)
        at org.apache.spark.deploy.SparkSubmit$.doRunMain$1(SparkSubmit.scala:185)
        at org.apache.spark.deploy.SparkSubmit$.submit(SparkSubmit.scala:210)
        at org.apache.spark.deploy.SparkSubmit$.main(SparkSubmit.scala:124)
        at org.apache.spark.deploy.SparkSubmit.main(SparkSubmit.scala)
Caused by: com.datastax.driver.core.exceptions.UnauthorizedException: User donghua has no SELECT permission on or any of its parents

        at com.datastax.driver.core.Responses$Error.asException(Responses.java:134)
        at com.datastax.driver.core.DefaultResultSetFuture.onSet(DefaultResultSetFuture.java:179)
        at com.datastax.driver.core.RequestHandler.setFinalResult(RequestHandler.java:173)
        at com.datastax.driver.core.RequestHandler.access$2500(RequestHandler.java:43)
        at com.datastax.driver.core.RequestHandler$SpeculativeExecution.setFinalResult(RequestHandler.java:788)
        at com.datastax.driver.core.RequestHandler$SpeculativeExecution.onSet(RequestHandler.java:607)
        at com.datastax.driver.core.Connection$Dispatcher.channelRead0(Connection.java:1012)
        at com.datastax.driver.core.Connection$Dispatcher.channelRead0(Connection.java:935)
        at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:105)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:318)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:304)
        at io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:266)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:318)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:304)
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:318)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:304)
        at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:276)
        at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:263)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:318)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:304)
        at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:846)
        at io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:823)
        at io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:339)
        at io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:255)
        at io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:112)
        at java.lang.Thread.run(Thread.java:745)
com.datastax.driver.core.exceptions.UnauthorizedException: User donghua has no SELECT permission on or any of its parents

        at com.datastax.driver.core.exceptions.UnauthorizedException.copy(UnauthorizedException.java:59)
        at com.datastax.driver.core.exceptions.UnauthorizedException.copy(UnauthorizedException.java:25)
        at com.datastax.driver.core.DriverThrowables.propagateCause(DriverThrowables.java:37)
        at com.datastax.driver.core.DefaultResultSetFuture.getUninterruptibly(DefaultResultSetFuture.java:245)
        at com.datastax.driver.core.AbstractSession.execute(AbstractSession.java:64)
        at com.datastax.driver.core.AbstractSession.execute(AbstractSession.java:47)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at com.datastax.spark.connector.cql.SessionProxy.invoke(SessionProxy.scala:33)
        at com.sun.proxy.$Proxy29.execute(Unknown Source)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at com.datastax.spark.connector.cql.SessionProxy.invoke(SessionProxy.scala:33)
        at com.sun.proxy.$Proxy29.execute(Unknown Source)
        at com.datastax.spark.connector.rdd.partitioner.DataSizeEstimates$$anonfun$tokenRanges$1.apply(DataSizeEstimates.scala:40)
        at com.datastax.spark.connector.rdd.partitioner.DataSizeEstimates$$anonfun$tokenRanges$1.apply(DataSizeEstimates.scala:38)
        at com.datastax.spark.connector.cql.CassandraConnector$$anonfun$withSessionDo$1.apply(CassandraConnector.scala:111)
        at com.datastax.spark.connector.cql.CassandraConnector$$anonfun$withSessionDo$1.apply(CassandraConnector.scala:110)
        at com.datastax.spark.connector.cql.CassandraConnector.closeResourceAfterUse(CassandraConnector.scala:140)
        at com.datastax.spark.connector.cql.CassandraConnector.withSessionDo(CassandraConnector.scala:110)
        at com.datastax.spark.connector.rdd.partitioner.DataSizeEstimates.tokenRanges$lzycompute(DataSizeEstimates.scala:38)
        at com.datastax.spark.connector.rdd.partitioner.DataSizeEstimates.tokenRanges(DataSizeEstimates.scala:37)
        at com.datastax.spark.connector.rdd.partitioner.DataSizeEstimates.totalDataSizeInBytes$lzycompute(DataSizeEstimates.scala:88)
        at com.datastax.spark.connector.rdd.partitioner.DataSizeEstimates.totalDataSizeInBytes(DataSizeEstimates.scala:87)
        at org.apache.spark.sql.cassandra.CassandraSourceRelation$.apply(CassandraSourceRelation.scala:260)
        at org.apache.spark.sql.cassandra.DefaultSource.createRelation(DefaultSource.scala:55)
        at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:330)
        at org.apache.spark.sql.execution.datasources.CreateTempViewUsing.run(ddl.scala:87)
        at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult$lzycompute(commands.scala:60)
        at org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult(commands.scala:58)
        at org.apache.spark.sql.execution.command.ExecutedCommandExec.doExecute(commands.scala:74)
        at org.apache.spark.sql.execution.SparkPlan$$anonfun$execute$1.apply(SparkPlan.scala:115)
        at org.apache.spark.sql.execution.SparkPlan$$anonfun$execute$1.apply(SparkPlan.scala:115)
        at org.apache.spark.sql.execution.SparkPlan$$anonfun$executeQuery$1.apply(SparkPlan.scala:136)
        at org.apache.spark.rdd.RDDOperationScope$.withScope(RDDOperationScope.scala:151)
        at org.apache.spark.sql.execution.SparkPlan.executeQuery(SparkPlan.scala:133)
        at org.apache.spark.sql.execution.SparkPlan.execute(SparkPlan.scala:114)
        at org.apache.spark.sql.execution.QueryExecution.toRdd$lzycompute(QueryExecution.scala:86)
        at org.apache.spark.sql.execution.QueryExecution.toRdd(QueryExecution.scala:86)
        at org.apache.spark.sql.Dataset.(Dataset.scala:186)
        at org.apache.spark.sql.Dataset.(Dataset.scala:167)
        at org.apache.spark.sql.Dataset$.ofRows(Dataset.scala:65)
        at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:582)
        at org.apache.spark.sql.SQLContext.sql(SQLContext.scala:682)
        at org.apache.spark.sql.hive.thriftserver.SparkSQLDriver.run(SparkSQLDriver.scala:62)
        at org.apache.spark.sql.hive.thriftserver.SparkSQLCLIDriver.processCmd(SparkSQLCLIDriver.scala:331)
        at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:376)
        at org.apache.spark.sql.hive.thriftserver.SparkSQLCLIDriver$.main(SparkSQLCLIDriver.scala:247)
        at org.apache.spark.sql.hive.thriftserver.SparkSQLCLIDriver.main(SparkSQLCLIDriver.scala)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.spark.deploy.SparkSubmit$.org$apache$spark$deploy$SparkSubmit$$runMain(SparkSubmit.scala:736)
        at org.apache.spark.deploy.SparkSubmit$.doRunMain$1(SparkSubmit.scala:185)
        at org.apache.spark.deploy.SparkSubmit$.submit(SparkSubmit.scala:210)
        at org.apache.spark.deploy.SparkSubmit$.main(SparkSubmit.scala:124)
        at org.apache.spark.deploy.SparkSubmit.main(SparkSubmit.scala)
Caused by: com.datastax.driver.core.exceptions.UnauthorizedException: User donghua has no SELECT permission on or any of its parents

        at com.datastax.driver.core.Responses$Error.asException(Responses.java:134)
        at com.datastax.driver.core.DefaultResultSetFuture.onSet(DefaultResultSetFuture.java:179)
        at com.datastax.driver.core.RequestHandler.setFinalResult(RequestHandler.java:173)
        at com.datastax.driver.core.RequestHandler.access$2500(RequestHandler.java:43)
        at com.datastax.driver.core.RequestHandler$SpeculativeExecution.setFinalResult(RequestHandler.java:788)
        at com.datastax.driver.core.RequestHandler$SpeculativeExecution.onSet(RequestHandler.java:607)
        at com.datastax.driver.core.Connection$Dispatcher.channelRead0(Connection.java:1012)
        at com.datastax.driver.core.Connection$Dispatcher.channelRead0(Connection.java:935)
        at io.netty.channel.SimpleChannelInboundHandler.channelRead(SimpleChannelInboundHandler.java:105)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:318)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:304)
        at io.netty.handler.timeout.IdleStateHandler.channelRead(IdleStateHandler.java:266)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:318)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:304)
        at io.netty.handler.codec.MessageToMessageDecoder.channelRead(MessageToMessageDecoder.java:103)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:318)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:304)
        at io.netty.handler.codec.ByteToMessageDecoder.fireChannelRead(ByteToMessageDecoder.java:276)
        at io.netty.handler.codec.ByteToMessageDecoder.channelRead(ByteToMessageDecoder.java:263)
        at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:318)
        at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:304)
        at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:846)
        at io.netty.channel.epoll.AbstractEpollStreamChannel$EpollStreamUnsafe.epollInReady(AbstractEpollStreamChannel.java:823)
        at io.netty.channel.epoll.EpollEventLoop.processReady(EpollEventLoop.java:339)
        at io.netty.channel.epoll.EpollEventLoop.run(EpollEventLoop.java:255)
        at io.netty.util.concurrent.SingleThreadEventExecutor$2.run(SingleThreadEventExecutor.java:112)
        at java.lang.Thread.run(Thread.java:745)


Steps to fix:

cassandra@cqlsh> grant select on system.size_estimates to donghua;
cassandra@cqlsh> list all permissions of donghua;

 role             | username         | resource                      | permission
------------------+------------------+-------------------------------+------------
          donghua |          donghua |         |      ALTER

          donghua |          donghua |         |       DROP

          donghua |          donghua |         |     SELECT

          donghua |          donghua |         |     MODIFY

          donghua |          donghua |         |  AUTHORIZE

          donghua |          donghua | |     SELECT

 mykeyspace_owner | mykeyspace_owner |         |     CREATE
 mykeyspace_owner | mykeyspace_owner |         |      ALTER
 mykeyspace_owner | mykeyspace_owner |         |       DROP
 mykeyspace_owner | mykeyspace_owner |         |     SELECT
 mykeyspace_owner | mykeyspace_owner |         |     MODIFY
 mykeyspace_owner | mykeyspace_owner |         |  AUTHORIZE

(12 rows)



spark-sql> create TEMPORARY TABLE t1 USING org.apache.spark.sql.cassandra OPTIONS (table "t1",keyspace "mykeyspace", cluster "Test Cluster",pushdown "true");
16/10/13 21:46:46 WARN SparkStrategies$DDLStrategy: CREATE TEMPORARY TABLE t1 USING... is deprecated, please use CREATE TEMPORARY VIEW viewName USING... instead
Time taken: 2.462 seconds
spark-sql> select count(*) from t1;
445674
Time taken: 12.389 seconds, Fetched 1 row(s)
spark-sql>

How to fix OperationTimedOut when using cqlsh query large dataset

cqlsh:mykeyspace2> select d,min(e),max(e) from mykeyspace.t1;
OperationTimedOut: errors={'127.0.0.1': 'Client request timeout. See Session.execute[_async](timeout)'}, last_host=127.0.0.1


[donghua@localhost cluster]$ touch  ~/.cassandra/cqlshrc

[donghua@localhost cluster]$ cat  ~/.cassandra/cqlshrc
[connection]
client_timeout = 3600 # default is 10 seconds
# Can also be set to None to disable:
# client_timeout = None

[donghua@localhost cluster]$ cqlsh
Connected to SingleServer Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.9 | CQL spec 3.4.2 | Native protocol v4]
Use HELP for help.
cqlsh> select d,min(e),max(e) from mykeyspace.t1;

 d | system.min(e) | system.max(e)
---+---------------+---------------
 5 |       1.00033 |     159.99989

(1 rows)

Wednesday, October 12, 2016

How to include multiple JAR files for Spark-shell and spark-sql


#Below are additional JAR files required to connect Apache Cassandra

[donghua@localhost ~]$ ls  -l /home/donghua/spark-casssandra-connector/*
-rw-rw-r--. 1 donghua donghua  231320 Oct 12 15:39 /home/donghua/spark-casssandra-connector/commons-beanutils-1.8.0.jar
-rw-rw-r--. 1 donghua donghua   38460 Oct 12 15:39 /home/donghua/spark-casssandra-connector/joda-convert-1.2.jar
-rw-rw-r--. 1 donghua donghua  581571 Oct 12 15:39 /home/donghua/spark-casssandra-connector/joda-time-2.3.jar
-rw-rw-r--. 1 donghua donghua   62226 Oct 12 15:40 /home/donghua/spark-casssandra-connector/jsr166e-1.1.0.jar
-rw-rw-r--. 1 donghua donghua 2112017 Oct 12 15:39 /home/donghua/spark-casssandra-connector/netty-all-4.0.33.Final.jar
-rw-rw-r--. 1 donghua donghua 4573750 Oct 12 15:41 /home/donghua/spark-casssandra-connector/scala-reflect-2.11.8.jar
-rw-rw-r--. 1 donghua donghua 6036067 Oct 12 15:39 /home/donghua/spark-casssandra-connector/spark-cassandra-connector-2.0.0-M2-s_2.11.jar

[donghua@localhost ~]$ cat spark-2.0.1-bin-hadoop2.7/conf/
docker.properties.template    log4j.properties.template     slaves.template               spark-defaults.conf.template
fairscheduler.xml.template    metrics.properties.template   spark-defaults.conf           spark-env.sh.template
[donghua@localhost ~]$ cat spark-2.0.1-bin-hadoop2.7/conf/spark-defaults.conf
#
# Licensed to the Apache Software Foundation (ASF) under one or more
# contributor license agreements.  See the NOTICE file distributed with
# this work for additional information regarding copyright ownership.
# The ASF licenses this file to You under the Apache License, Version 2.0
# (the "License"); you may not use this file except in compliance with
# the License.  You may obtain a copy of the License at
#
#    http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#

# Default system properties included when running spark-submit.
# This is useful for setting default environmental settings.

# Example:
# spark.master                     spark://master:7077
# spark.eventLog.enabled           true
# spark.eventLog.dir               hdfs://namenode:8021/directory
# spark.serializer                 org.apache.spark.serializer.KryoSerializer
# spark.driver.memory              5g
# spark.executor.extraJavaOptions  -XX:+PrintGCDetails -Dkey=value -Dnumbers="one two three"
#
spark.driver.extraClassPath  /home/donghua/spark-casssandra-connector/*
spark.executor.extraClassPath /home/donghua/spark-casssandra-connector/*

# To execute without using --jars to include additional jar files
[donghua@localhost spark-2.0.1-bin-hadoop2.7]$ $SPARK_HOME/bin/spark-shell --conf spark.cassandra.connection.host=127.0.0.1