Apache Spark - Connecting to MSSQL Databases using JDBC and Dataset Operations

Following the instructions from Databricks Connecting to SQL Database developed a program in Scala to retrieve data from MSSQL Server.  In the process have to read some documentation and some research on Google to merge dataframes, extract required fields from the resulting RDDs which contained duplicate column names, and writing the results to a single file.  Following were some of the tasks that were executed to create a single file containing the required data.

Adding MS SQL Server JDBC to library dependencies in sbt file

Instructions as provided at Microsoft JDBC Driver For SQL Server was added to the sbt file.

Reading Data from SQL

Instructions for reading data from SQL worked as detailed, it was not clear where the display function was.  As a result could not use the function display as documented.

Table schema

Part of the table schema used in the application was:

 |-- LabNumFr: integer (nullable = false)

 |-- Analyte: string (nullable = false)

 |-- Result: double (nullable = true)

Pushdown Queries to Database Engine

For performance reasons pushdown queries to database engine were used.  Three distinct datasets each representing a different Analyte(Mg, P, and K) were created using three different pushdown queries.  Sample contents were:

+--------+-------+-------+
|LabNumFr|Analyte| Result|
+--------+-------+-------+
|   78304|     Mg|117.259|
|   78404|     Mg| 95.881|
|   78410|     Mg| 32.595|
|   78423|     Mg|338.594|
|   78426|     Mg| 14.832|
+--------+-------+-------+

Merging Datasets using Alias

Joining datasets for further analysis was required.  Noting that LabNumFr was the key for joining the results, the datasets were joined using the dataset join method.  Since the datasets have identical column names, dataset's alias had to be used to select the column.  The query was similar to:

val dfPMg = dfMg.alias("Mg").join(dfP.alias("P"), "LabNumFr").join(dfK.alias("K"), "LabNumFr")

Merging Datasets

A single dataset containing results of Mg, P, and K was obtained using select command on dataset as below:

dfPMg.select("Mg.Result", "P.Result", "K.Result")

Writing results to single file using coalesce

Finally the results were all written to a single file using coalesc command without which the data was written to several files.  The command was:

dfPMg.select("Mg.LabNumFr", "Mg.Result", "P.Result", "K.Result")

.rdd.cache()

.coalesce(1)

.saveAsTextFile("/tmp/results.csv")

Using Jersey2Config to generate Swagger 2.0 definition files

Swagger Core Jersey 2.X Project Setup 1.5

Generating swagger.json using Swagger core required some patience.  Following the documentation at https://github.com/swagger-api/swagger-core/wiki/Swagger-Core-Jersey-2.X-Project-Setup-1.5#using-the-application-class using Servlet 3.1 finally was able to create swagger.json.  https://github.com/htotapally/Spring5.git is the link to source code.  web.xml contains the swagger initialization code, shown below:

<web-app version="3.1" xmlns="http://xmlns.jcp.org/xml/ns/javaee"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd">

<servlet>

<servlet-name>Jersey2Config</servlet-name>

<servlet-class>io.swagger.jersey.config.JerseyJaxrsConfig</servlet-class>

<init-param>

<param-name>api.version</param-name>

<param-value>1.0.0</param-value>

</init-param>

<init-param>

<param-name>swagger.api.basepath</param-name>

<param-value>/tt/api</param-value>

</init-param>

<init-param>

<param-name>swagger.api.title</param-name>

<param-value>My Awesome API</param-value>

</init-param>

<load-on-startup>2</load-on-startup>

</servlet>

<servlet>

<servlet-name>jersey</servlet-name>

<servlet-class>org.glassfish.jersey.servlet.ServletContainer</servlet-class>

<init-param>

<param-name>jersey.config.server.provider.packages</param-name>

<param-value>

            io.swagger.jaxrs.listing,com.sbt

        </param-value>

</init-param>

<load-on-startup>1</load-on-startup>

</servlet>

<servlet-mapping>

<servlet-name>jersey</servlet-name>

<url-pattern>/api/*</url-pattern>

</servlet-mapping>

</web-app>

Used the command: mvn clean install -Dp.type=war to generate the war file to be deploy in Tomcat.

Once deployed http://localhost:8080/tt/api/swagger.json provided the description of the Rest services provided in the EmployeeResource.  Finally used to Swagger plugin to Chrome to test the API.  All worked out as expected once the Swagger project was set up correctly

Jasper Reports Excel Issues-Fixed

Have been using Jasper Reports for generating reports in excel formats.  Had to merge several excel files, and was using the POI libraries.  The requirement was to retain cell style.  Opening the resulting excel spreadsheet was always throwing the following error:

Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
Repaired Records: Format from /xl/styles.xml part (Styles)

The error stopped when upgraded the POI libraries to 3.15 version.

Sqoop1.99.7 Installation

Expecting that Sqoop would work out of box downloaded sqoop-1.99.7-bin-hadoop200.tar.gz and followed the installation instructions from sqoop 1.99.1 and others as they were the first search results and the installation instructions for various versions were available by just changing the version number.  Documentation for installation instructions were available upto version 1.99.6 at url https://sqoop.apache.org/docs/1.99.6/Installation.html.  But documentation to 1.99.7 could not be found based on similar url.  Following the available documentation encountered hadoop configuration ClassNotFoundExceptions when tried to start sqoop server.

Documentation upto 1.99.6 refers to a non-existing catalina.properties.  Web search lead to several non-working solutions.  Some of the working solutions suggested to copy required hadoop libraries to to sqoop lib directory.   Finally stumbled across the documentation for 1.99.7 installation instructions at htps://sqoop.apache.org/docs/1.99.7/admin/Installation.html.  The documentation is very clear that environment variables related to HADOOP have to be set.  Based on these instructions added HADOOP_HOME to the user profile, and encountered the following error:

Caused by: org.apache.sqoop.common.SqoopException: MAPREDUCE_0002:Failure on submission engine initialization - Invalid Hadoop configuration directory (not a directory or permission issues): /etc/hadoop/conf/

Based on the solution presented at: http://brianoneill.blogspot.com/2014/10/sqoop-1993-w-hadoop-2-installation.html, modified the property org.apache.sqoop.submission.engine.mapreduce.configuration.diretory in <SQOOP_HOME>/conf/sqoop.properties file to point to the correct hdfs configuration.

org.apache.sqoop.submission.engine.mapreduce.configuration.directory=<location to hadoop configuration, e.g /etc/hadoop/conf>

Finally <SQOOP_HOME>/sqoop.sh server start resulted in a successful start of sqoop sever with the following output:

etting conf dir: bin/../conf

Sqoop home directory: <sqoop home>/sqoop

Starting the Sqoop2 server...

0    [main] INFO  org.apache.sqoop.core.SqoopServer  - Initializing Sqoop server.

7    [main] INFO  org.apache.sqoop.core.PropertiesConfigurationProvider  - Starting config file poller thread

Sqoop2 server started.

HBase 1.2.4 and Phoenix 4.9.2 Spark integration

While the link: https://blogs.apache.org/phoenix/entry/spark_integration_in_apache_phoenix provides good example of Spark integration with Apache Phoenix, library dependencies and incompatible library versions made it a challenge.  Tried to integrate Apache HBase 1.2.4, and Apache Phoenix 4.8.2, and 4.9.2.  Setting up HBase in distributed mode was a breeze.  Adding Phoenix libraries to HBase, and testing JDBC Driver with SQurrelSQL was easy.  Running the example by Josh Mahonin in the above link required some work.  IntelliJ returned the following compilation error messages while compiling:

Error:

scalac: missing or invalid dependency detected while loading class file

'ProductRDDFunctions.class'. 

Could not access type Logging in package org.apache.spark, 

because it (or its dependencies) are missing. 

Check your build definition for missing or conflicting dependencies. 

(Re-run with `-Ylog-classpath` to see the problematic classpath. 

A full rebuild may help if 'ProductRDDFunctions.class' was compiled against 

an incompatible version of org.apache.spark.

After a little bit of research on google found the following fix:
https://github.com/kalyanhadooptraining/phoenix/commit/98cf1b408358c0f9687b1aadf91ede64fdc0a05d by Kalyanhadooptraining

Applied the fixes as above, replaced Spark version from 1.6.1 to 2.1.0 in pom.xml, rebuilt Phoenix libraries.  After copying the newly build Phoenix libraries to HBase and restart, Spark integration with Apache Phoenix as described by Josh Mahonin worked as expected.  Was able to verify the results using SQuirrelSQL client.

  

JasperReports-Subreport spanning multiple pages introdcues blank page

Jasper reports introduces a blank page just before a sub report when the band element split type is set to prevent.  The issue has been notices since I started using Jasper Reports 4.x.  Tried different settings, and different options.  Have been updating to the latest version, but without luck.  Since blank page in the middle of the report was not acceptable, had been researching for a solution.  Finally I stumbled across the link:
http://community.jaspersoft.com/jasperreports-library/issues/5010
Finally after implementing the post fangebault on October 11, 2013 - 8:51am, blank page doesnot show up in the middle of the report anymore.

As posted by aibatalov on October 5, 2015 - 11:32pm

It's awful bug. When you have many rows with big height document become to long and with a lot of empty space, because each big row transferred to the next page, and only after that is splitted.
When it will be fixed? 5 years open - JasperSoft ignore us?

Cloudera 3.5.2 Problems starting Job Tracker

Cloudera 5.3.2 Problems starting Task Tracker

Following the the instructions provided at: http://edpflager.com/?p=1945 installed tried to setup a Single Node Hadoop Machine.  Task tracker would not start while trying to run the command:

for x in `cd /etc/init.d ; ls hadoop-0.20-mapreduce-*` ; do sudo service $x start ; done

Search on internet did not provide a working solution.  While searching for the log files, stumbled across the location of the log files: http://localhost:50030/logs/, and the location of the log file for task tracker: http://localhost:50030/logs/hadoop-hadoop-tasktracker-localhost.localdomain.log.  The log files provided clear details why the task tracker did not start.  The detailed messages were:

2015-03-29 07:40:30,293 WARN org.apache.hadoop.mapred.TaskTracker: 

TaskTracker local dir /var/lib/hadoop-hdfs/cache/mapred/mapred/local 

error File /var/lib/hadoop-hdfs/cache/mapred/mapred/local does not exist, 

removing from local dirs
2015-03-29 07:40:30,294 ERROR org.apache.hadoop.mapred.TaskTracker: 

Can not start task tracker because 

org.apache.hadoop.util.DiskChecker$DiskErrorException: No mapred local directories are writable
 at org.apache.hadoop.mapred.TaskTracker$LocalStorage.checkDirs(TaskTracker.java:284)
 at org.apache.hadoop.mapred.TaskTracker.<init>(TaskTracker.java:1770)
 at org.apache.hadoop.mapred.TaskTracker.main(TaskTracker.java:4124)

From the log statements it is clear that the start scripts to start the map reduce jobs did not create the folder /var/lib/hadoo-hdfs/cache/mapred/mapred/local.  Physically created the directory, and changed ownership to hdfs, group membership to hdfs.  Tried to restart the tasktracker without success, and following are the log statments:

2015-03-29 12:55:52,241 INFO org.apache.hadoop.mapred.TaskTracker: 

registered UNIX signal handlers for [TERM, HUP, INT]
2015-03-29 12:55:52,783 INFO org.mortbay.log: Logging to org.slf4j.impl.

Log4jLoggerAdapter(org.mortbay.log) via org.mortbay.log.Slf4jLog
2015-03-29 12:55:52,836 INFO org.apache.hadoop.http.HttpServer: 

Added global filter 'safety' (class=org.apache.hadoop.http.HttpServer$QuotingInputFilter)
2015-03-29 12:55:52,839 INFO org.apache.hadoop.http.HttpServer: 

Added filter static_user_filter (class=org.apache.hadoop.http.lib.StaticUserWebFilter$StaticUserFilter) to context task
2015-03-29 12:55:52,839 INFO org.apache.hadoop.http.HttpServer: 

Added filter static_user_filter (class=org.apache.hadoop.http.lib.StaticUserWebFilter$StaticUserFilter) to context logs
2015-03-29 12:55:52,839 INFO org.apache.hadoop.http.HttpServer: 

Added filter static_user_filter (class=org.apache.hadoop.http.lib.StaticUserWebFilter$StaticUserFilter) to context static
2015-03-29 12:55:53,031 WARN org.apache.hadoop.mapred.TaskTracker: 

TaskTracker local dir /var/lib/hadoop-hdfs/cache/mapred/mapred/local 

error Operation not permitted, removing from local dirs
2015-03-29 12:55:53,032 ERROR org.apache.hadoop.mapred.TaskTracker: 

Can not start task tracker because org.apache.hadoop.util.DiskChecker$DiskErrorException: 

No mapred local directories are writable
 at org.apache.hadoop.mapred.TaskTracker$LocalStorage.checkDirs(TaskTracker.java:284)
 at org.apache.hadoop.mapred.TaskTracker.<init>(TaskTracker.java:1770)
 at org.apache.hadoop.mapred.TaskTracker.main(TaskTracker.java:4124)

Then removed the directory /var/lib/hadoop-hdfs/cache/mapred/mapred.   Restarting task tracker worked, and the log statements show as:

2015-03-29 12:58:34,898 INFO org.apache.hadoop.mapred.TaskTracker: registered UNIX signal handlers for [TERM, HUP, INT]
2015-03-29 12:58:35,501 INFO org.mortbay.log: Logging to org.slf4j.impl.Log4jLoggerAdapter(org.mortbay.log) via org.mortbay.log.Slf4jLog
2015-03-29 12:58:35,551 INFO org.apache.hadoop.http.HttpServer: Added global filter 'safety' (class=org.apache.hadoop.http.HttpServer$QuotingInputFilter)
2015-03-29 12:58:35,554 INFO org.apache.hadoop.http.HttpServer: Added filter static_user_filter (class=org.apache.hadoop.http.lib.StaticUserWebFilter$StaticUserFilter) to context task
2015-03-29 12:58:35,554 INFO org.apache.hadoop.http.HttpServer: Added filter static_user_filter (class=org.apache.hadoop.http.lib.StaticUserWebFilter$StaticUserFilter) to context logs
2015-03-29 12:58:35,554 INFO org.apache.hadoop.http.HttpServer: Added filter static_user_filter (class=org.apache.hadoop.http.lib.StaticUserWebFilter$StaticUserFilter) to context static
2015-03-29 12:58:35,798 INFO org.apache.hadoop.mapred.TaskLogsTruncater: Initializing logs' truncater with mapRetainSize=-1 and reduceRetainSize=-1
2015-03-29 12:58:35,803 INFO org.apache.hadoop.mapred.TaskTracker: Starting tasktracker with owner as mapred
2015-03-29 12:58:35,803 INFO org.apache.hadoop.conf.Configuration.deprecation: slave.host.name is deprecated. Instead, use dfs.datanode.hostname
2015-03-29 12:58:35,805 INFO org.apache.hadoop.mapred.TaskTracker: Good mapred local directories are: /var/lib/hadoop-hdfs/cache/mapred/mapred/local
2015-03-29 12:58:35,820 INFO org.apache.hadoop.conf.Configuration.deprecation: session.id is deprecated. Instead, use dfs.metrics.session-id
2015-03-29 12:58:35,821 INFO org.apache.hadoop.metrics.jvm.JvmMetrics: Initializing JVM Metrics with processName=TaskTracker, sessionId=
2015-03-29 12:58:35,848 INFO org.apache.hadoop.ipc.CallQueueManager: Using callQueue class java.util.concurrent.LinkedBlockingQueue
2015-03-29 12:58:35,886 INFO org.apache.hadoop.ipc.Server: Starting Socket Reader #1 for port 49740
2015-03-29 12:58:35,931 INFO org.apache.hadoop.ipc.Server: IPC Server Responder: starting
2015-03-29 12:58:35,932 INFO org.apache.hadoop.ipc.Server: IPC Server listener on 49740: starting
2015-03-29 12:58:35,933 INFO org.apache.hadoop.mapred.TaskTracker: TaskTracker up at: localhost/127.0.0.1:49740
2015-03-29 12:58:35,933 INFO org.apache.hadoop.mapred.TaskTracker: Starting tracker tracker_localhost:localhost/127.0.0.1:49740
2015-03-29 12:58:35,961 INFO org.apache.hadoop.mapred.TaskTracker: Starting thread: Map-events fetcher for all reduce tasks on tracker_localhost:localhost/127.0.0.1:49740
2015-03-29 12:58:35,967 INFO org.apache.hadoop.util.ProcessTree: setsid exited with exit code 0
2015-03-29 12:58:35,977 INFO org.apache.hadoop.mapred.TaskTracker:  Using ResourceCalculatorPlugin : org.apache.hadoop.util.LinuxResourceCalculatorPlugin@4d8088da
2015-03-29 12:58:35,979 WARN org.apache.hadoop.mapred.TaskTracker: TaskTracker's totalMemoryAllottedForTasks is -1 and reserved physical memory is not configured. TaskMemoryManager is disabled.
2015-03-29 12:58:35,980 INFO org.apache.hadoop.mapred.IndexCache: IndexCache created with max memory = 10485760
2015-03-29 12:58:35,987 INFO org.apache.hadoop.http.HttpServer: Jetty bound to port 50060

Once the task tracker started successfully, the first word count map reduce job ran successfully.