Donnie Roberson

Built on the best Distribution of Apache Cassandra. DataStax Enterprise is the always-on data platform designed to allow you to effortlessly build and scale your apps, integrating graph, search, analytics, administration, developer tooling, and monitoring into a single unified platform. We power your apps' real-time moments so you can create instant insights and powerful customer experiences.

Tableau is an industy leading BI too that focuses on data visualization, dashboarding and data discovery. It can be characterized as a "Visual Analytics" application - you don't just use it to visualize data, but instead you conduct analysis through seeing the data in visuals.

So why not utilize the power of Both DataStax Enterprise and Tableau together.

In this simple tutorial we will walk through connecting Tableau to a DataStax Enterprise (DSE) 6.0 Docker Container utilizing DSE Alwayson SQL via the Spark ODBC driver and creating a simple Tableau workbook. We will Utilize DSE analytics to perform the computations, joins and aggregates on the server side (DSE) which is faster and more effecient, while harnesing Tableau to visualize the data.

This Tutorial was created using a DSE 6.0 Docker Container, Tableau Desktop 10.5 running on Windows and Spark ODBC driver 1.2.6.1007.

<h2>Prerequisites</h2>

<ul>
	<li>Basic understanding of Docker images and containers.</li>
	<li>Docker installed on your local system, see <a href="https://docs.docker.com/engine/installation/">Docker Installation Instructions</a>.</li>
	<li>A working DSE Docker container running an Analytics workload with the DataStax config volume mounted and the following AlwaysOnSQL/Spark UI ports bound.
	<ul>
		<li>10000, 7080, 7077, 4040, 9077&nbsp;</li>
	</ul>
	</li>
</ul>

<code>docker run -e DS_LICENSE=accept --name my-dse -p 10000:10000 -p 7080:7080 -p 7077:7077 -p 4040:4040 -p 9077:9077 -v ~/config:/config -d datastax/dse-server -k&nbsp; 
&nbsp;</code>

<ul>
	<li>Our <a href="https://hub.docker.com/r/datastax/dse-server/">Docker Images</a> are hosted on Docker Hub. For documentation including configuration options, environment variables, and compose examples head over to <a href="https://academy.datastax.com/quick-downloads?utm_campaign=Docker_2019&amp;utm_medium=web&amp;utm_source=docker&amp;utm_term=-&amp;utm_content=Web_Academy_Downloads">DataStax Academy</a></li>
	<li>A DataStax Academy Account to Download and Install Simba ODBC Driver for Apache Spark: If you do not already have one, visit <a href="https://academy.datastax.com/">https://academy.datastax.com/</a></li>
	<li>A working Tableau Desktop install and basic Tableau understanding.
	<ul>
		<li>You can download a free Tableau trial from <a href="https://www.tableau.com/products/trial">https://www.tableau.com/products/trial</a></li>
	</ul>
	</li>
</ul>

<h2>The Simba ODBC Driver for Apache Spark</h2>

<ul>
	<li>Download the Simba ODBC Driver for Apache Spark for your version of Microsoft Windows (32bit or 64bit) running Tableau Desktop by visiting <a href="https://academy.datastax.com/download-drivers">https://academy.datastax.com/download-drivers</a></li>
	<li>Install the Simba ODBC Driver for Apache Spark.</li>
</ul>

<h2>Enabling Alwayson SQL</h2>

For advanced configuration management, we’re providing a simple mechanism to let you change or modify configurations without replacing or customizing the containers. You can add any of the approved config files to a mounted host volume and we’ll handle the hard work of mapping them within the container. You actually enabled this feature with the flag <code>-v ~/config:/config</code> when you started your DSE container and we will use it to enable alwayson sql.

You can read more about that feature <a href="https://docs.datastax.com/en/docker/doc/docker/docker60/dockerDSEVolumes.html">here</a>.

<ul>
	<li>Alwayson sql is enabled via the dse.yaml. I have created a custom dse.yaml with the setting preconfigured to use for this demo.
	<ul>
		<li>Download the dse.yaml found <a href="https://github.com/roberd13/tableau-dse6-demo/blob/master/DemoData/dse.yaml">here</a> to your docker host machine and place it in the ~/config directory created when starting the DSE container

		<ul>
			<li>If running linux or mac you can run the following command</li>
		</ul>
		</li>
	</ul>
	</li>
</ul>

<code>wget -L https://raw.githubusercontent.com/roberd13/tableau-dse6-demo/master/DemoData/dse.yaml -O ~/config/dse.yaml </code>

<ul>
	<li>Restart your dse container so it will pick up the custom dse.yaml and start alwayson sql</li>
</ul>

<code>docker restart my-dse </code>

<h2>Demo Data</h2>

<ul>
	<li>You will need to get the demo data on the DSE Docker Container from here using the following commands</li>
</ul>

Create directory for the demo data

<code>docker exec -it my-dse bash "/opt/dse/demodata/cqlscript.sh"</code>

Download the script to create the keyspace, table and load the data

<code>docker exec -it my-dse wget -L https://raw.githubusercontent.com/roberd13/tableau-dse6-demo/master/DemoData/cqlscript.sh -O /opt/dse/demodata/cqlscript.sh </code>

Change the permissions to allow execution of the script

<code>docker exec -it my-dse chmod +x /opt/dse/demodata/cqlscript.sh </code>

Download the demo data

<code>docker exec -it my-dse wget -L https://raw.githubusercontent.com/roberd13/tableau-dse6-demo/master/DemoData/videos.csv -O /opt/dse/demodata/videos.csv docker exec -it my-dse wget -L https://raw.githubusercontent.com/roberd13/tableau-dse6-demo/master/DemoData/videos_by_actor.csv -O /opt/dse/demodata/videos_by_actor.csv </code>

<ul>
	<li>Run the cqlscript.sh script to create the keyspace, tables and load the data using the new dsebulk tool. A Keyspace named killr_video with 2 tables videos and videos_by_actor will be created.</li>
</ul>

<code>docker exec -it &nbsp;my-dse bash "/opt/dse/demodata/cqlscript.sh"</code>

<h2>Now lets Create a Connection to DSE in Tableau</h2>

<ul>
	<li>Lets make sure that AlwaysOnSQL is running <code>docker exec -it my-dse dse client-tool alwayson-sql status</code></li>
	<li>Open Tableau and create a connection to DSE by selecting Spark SQL under To a Server. You may need to click more to show this option</li>
	<li>Setup your connection by
	<ul>
		<li>Add the ipaddress of the Docker host port 10000 is bound from the DSE container to hosts</li>
		<li>Set Port to 10000</li>
		<li>Set Type to SparkThriftServer</li>
		<li>Authentication to username</li>
		<li>Click Sign in</li>
	</ul>
	</li>
</ul>

<img alt="Spark SQL Tableau" data-entity-type="file" data-entity-uuid="85eba4ff-286a-40b9-ba9b-1a88f05cec1b" src="https://www.datastax.com/sites/default/files/inline-images/sparksql_tableau.png" />

<ul>
	<li>When your workbook opens after Signing in. On the Data Source tab under connections.
	<ul>
		<li>Search Schema using the drop down and Select Killr_video</li>
		<li>Search Table using the drop down and your tables will be listed</li>
		<li>Drag videos and videos_by_actor to the tables area</li>
	</ul>
	</li>
</ul>

<img alt="Data Source" data-entity-type="file" data-entity-uuid="273c0161-85cd-4129-b8a6-d6dfffa4bd83" src="https://www.datastax.com/sites/default/files/inline-images/Data%20Source.png" />

<ul>
	<li>Be sure to keep a Live connection, if you use Extract, all of the data will be loaded into Tableau.</li>
	<li>Create an inner join clause for Release Year = Release Year (Videos By Actor. (click on the blue part of the link between the 2 tables to be able to edit this inner join)</li>
</ul>

<img alt="Join Clause" data-entity-type="file" data-entity-uuid="43c5e55a-283d-48eb-b3e1-93360339c5f6" src="https://www.datastax.com/sites/default/files/inline-images/Join%20clause.png" />

<ul>
	<li>Click Update Now to see a sample of your data</li>
</ul>

<img alt="Updated Data" data-entity-type="file" data-entity-uuid="4e6fd8e0-367e-4455-9b85-3e750f48e0dc" src="https://www.datastax.com/sites/default/files/inline-images/Updated%20Data.png" />

<h2>Now Lets See the Count of Movies grouped by Movie Ratings Per Year</h2>

<ul>
	<li>Click on Sheet 1 Tab at the bottom
	<ul>
		<li>Convert Release Year (Videos By Actor) to Discrete</li>
	</ul>
	</li>
</ul>

<img alt="Discrete" data-entity-type="file" data-entity-uuid="723fb7ee-e456-48c2-b3b4-2a41417f29b5" src="https://www.datastax.com/sites/default/files/inline-images/discrete.png" />

<ul>
	<li>Drag Mpaa Rating to Color in the Marks section</li>
</ul>

<img alt="Marks" data-entity-type="file" data-entity-uuid="6c5d1134-2f5f-48e3-8c5a-02e9f70a579e" src="https://www.datastax.com/sites/default/files/inline-images/Marks.png" />

<ul>
	<li>Drag Release Year (Videos By Actor) to Columns</li>
	<li>Drag Mpaa Rating to Rows</li>
	<li>Change the Mpaa Rating under rows to a Count Measure type by using the drop down&nbsp;</li>
</ul>

<img alt="Measure" data-entity-type="file" data-entity-uuid="a48cce97-0fd9-4b46-9f37-e685e75d4e16" src="https://www.datastax.com/sites/default/files/inline-images/measure.png" />

<ul>
	<li>Select Stacked Bar on the Show Me snap on&nbsp;</li>
</ul>

<img alt="ShowMe" data-entity-type="file" data-entity-uuid="b36bae41-2788-4b26-af93-af3cc231d086" src="https://www.datastax.com/sites/default/files/inline-images/showme.png" />

<ul>
	<li>Click Show Me to hide the snap on</li>
	<li>You now have a color coded graph with the Count of Movies grouped by Movie Ratings Per Year&nbsp;</li>
</ul>

<img alt="Graph" data-entity-type="file" data-entity-uuid="9f1db7ca-9655-4af1-8815-19100f5b9e1d" src="https://www.datastax.com/sites/default/files/inline-images/graph.png" />

If you are curious what Tableau did under the hood, you can visit http://dockerhostipaddress:7080 which will take you to the Spark Master UI, you can then click AlwaysOnSQL which will direct you to the AlwaysOnSQL Application UI and you can see all of the queries performed during this tutorial. 

<h3>Master UI</h3>

<img alt="Master UI" data-entity-type="file" data-entity-uuid="4f363028-f5f6-444d-9821-b339a73ed93b" src="https://www.datastax.com/sites/default/files/inline-images/Master%20UI.png" />

<h3>Application UI</h3>

<img alt="Thrift UI" data-entity-type="file" data-entity-uuid="1d9501ee-a9bc-49a5-bc51-da811b6bdd8e" src="https://www.datastax.com/sites/default/files/inline-images/Thrift%20UI.png" />

Visualizing Your Data In Tableau Using DSE 6 Docker Images

Donnie Roberson

Discover more

Share

More Technology

Getting Started with DataStax Astra DB and Amazon Bedrock

Why You Can't Miss Cassandra Summit

Mitigate the OpenAI Debacle with Open Source and Cloud

Supercharge AI Assistants with Superagent and DataStax

NoSQL and Vector DB
for Generative AI,
Instantly, At Scale

Donnie Roberson

Discover more

Share

More Technology

Getting Started with DataStax Astra DB and Amazon Bedrock

Why You Can't Miss Cassandra Summit

Mitigate the OpenAI Debacle with Open Source and Cloud

Supercharge AI Assistants with Superagent and DataStax

NoSQL and Vector DBfor Generative AI,Instantly, At Scale

NoSQL and Vector DB
for Generative AI,
Instantly, At Scale