After completing this tutorial you will understand how to:
leverage Spark to infer a schema on a CSV dataset and persist it to Hive without explicitly declaring the DDL
deploy the Spark Thrift Server on the
Hortonworks Sandbox
connect and ODBC tool (Tableau) to the Spark Thrift Server via
the Hive ODBC driver, leveraging caching for ad-hoc visualization
Assumption 1: It is assumed that you have downloaded and deployed the Hortonworks sandbox, installed the Hive ODBC driver on your host machine, and installed
Tableau (or your preferred ODBC-based reporting tool).
Assumption 2: Please ensure that your host machine's /etc/hosts file has the appropriate entry mapping sandbox.hortonworks.com to the IP of your sandbox (e.g., 172.16.35.171 sandbox.hortonworks.com sandbox). Deploying the Spark Thrift Server
Within Ambari, click on the Hosts tab and then
select the sandbox.hortonworks.com node from the list.
Now you can click “Add” and choose Spark Thrift
Server from the list to deploy a thrift server.
After installing, start the thrift server via
the service menu.
Loading
the Data
The code blocks below are each intended to be executed in their own Zeppelin notebook cells. Each cell begins with a '%' indicating the interpreter to be used.
Open Zeppelin and create a new notebook: http://sandbox.hortonworks.com:9995
Download and take a peek at the first few lines
of the data:
%sh
wget https://dl.dropboxusercontent.com/u/3136860/Crime_Data.csv
hdfs dfs -put Crime_Data.csv /tmp
head Crime_Data.csv
Load the CSV reader dependency:
%dep
z.load("com.databricks:spark-csv_2.10:1.4.0")
Read the CSV file and infer the schema:
%pyspark
sqlContext = HiveContext(sc)
data = sqlContext.read.load("/tmp/Crime_Data.csv", format="com.databricks.spark.csv", header="true", inferSchema="true")
data.printSchema()
Persist the data to Hive:
%pyspark
data.registerAsTable("staging")
sqlContext.sql("CREATE TABLE crimes STORED AS ORC AS SELECT * FROM staging")
Verify the data is present and able to be
queried:
%sql
select Description, count(*) cnt from crimes
group by Description order by cnt desc
Connecting
Tableau via ODBC
Connect using the Hortonworks Hadoop Hive
connector:
Run the “Initial SQL” to cache the crimes table:
Verify the table is cached in the Thrift Server
UI: http://sandbox.hortonworks.com:4040/storage/
Select the default schema and drag the crimes
table into the tables area
Go to the worksheet and start exploring the
data using the cached table!
... View more
