Connecting Tableau to Drupal on Pantheon
Data-driven content management
Although creating, searching for, updating, and publishing content in Drupal is a snap, understanding and making decisions based on that content can be challenging. Questions like, "What are the most viewed, untranslated case studies?" or "Does an accelerated blogging cadence increase page views?" are difficult or impossible to answer within Drupal alone.
Though the data that could help answer those questions may live in Drupal, content editors or administrators are unlikely to find answers on their own because the information is made available, if at all, through complex UIs only understood by Drupal site builders, developers, or power users.
This problem--where those most knowledgeable about some dataset are only able to ask questions of that data by proxy through a specialist--extends far beyond just content management in Drupal.
Tableau (my employer) takes pride in helping solve this type of problem for organizations the world over, and because Drupal runs on pervasive database technologies like MySQL and PostgreSQL, we also happen to work well with Drupal: just add database credentials, connect, and go.
Container cloud complications
If you run Drupal on Pantheon, you may be familiar with (and likely benefit from) their container-based architecture. The efficiency and agility that containers provide are what allow Pantheon to offer development and test environments at scale. Containers also enable Pantheon to offer you highly available (distributed) and horizontally elastic applications by default.
Although these features are a Drupal developer's dream, the underlying technology complicates things for data-driven content managers. When Pantheon updates servers, migrates endpoints, or does other maintenance work transparent to end-users, database connection details change, breaking Tableau's connection to the Drupal database.
There are a few options for working around this problem, each with its drawbacks:
- Send out updated credentials whenever they break: just instruct every Tableau user to e-mail you when the dashboard they built stops updating; you can find the new credentials and send them back. Rinse and repeat for every user with access and every site: welcome to your new full-time job.
- Give content editors access to the Pantheon dashboard: train them to navigate to the specific environment you want them to connect to, suss out the MySQL details, and be sure not to hit that "delete live site" button you just gave them access to...
- Use Pantheon's CLI to replicate the DB locally on a schedule: on the whole, not a bad option, but what happens when the DB server goes offline or your replication script starts failing? Didn't you go with Pantheon to get out of the infrastructure management and monitoring game in the first place?
Introducing the Pantheon Switchboard
We, being a data-driven marketing organization who coincidentally has a large Tableau installation base, and one that happens to host many Drupal sites on Pantheon, know the struggle well.
To that end, we've developed and open sourced the Pantheon Switchboard, a Docker image that mashes up the Pantheon command line interface and MySQL proxy, allowing Tableau users (both those connecting ad-hoc using the desktop client as well as those scheduling extracts with our cloud or on-premise servers) to reliably and seamlessly connect to MySQL databases hosted on Pantheon, despite those periodic database connection detail changes.
The Switchboard's container approach attempts to strike the right balance between infrastructure requirements and the type of self-service simplicity that Tableau users expect.
Complete details on installation and usage are available on the project's README.
Deploying to Google Compute Engine
For production use, we're enamored with the simplicity of deploying containers on GCE using their Container-Optimized VM images; feel free to use this as a recipe to get started:
# switchboard-manifest.ymlversion: v1kind: Podspec: containers: - name: my-drupal-site-proxy image: tableaumkt/pantheon-mysql-proxy imagePullPolicy: Always ports: - name: mysql containerPort: 3306 hostPort: 11337 protocol: TCP env: - name: PROXY_DB_UN value: un_to_connect_to_drupal_proxy - name: PROXY_DB_PW value: pw_to_connect_to_proxy_here - name: PANTHEON_SITE value: my-drupal-site - name: PANTHEON_ENV value: test - name: PANTHEON_EMAIL value: email-w-access-to-dashboard@example.com - name: PANTHEON_PASS value: password_for_email_here - name: my-wp-site-proxy image: tableaumkt/pantheon-mysql-proxy imagePullPolicy: Always ports: - name: mysql containerPort: 3306 hostPort: 11338 protocol: TCP env: - name: PROXY_DB_UN value: un_to_connect_to_wp_proxy_here - name: PROXY_DB_PW value: pw_to_connect_to_proxy_here - name: PANTHEON_SITE value: my-wp-site - name: PANTHEON_ENV value: dev - name: PANTHEON_EMAIL value: email-w-access-to-dashboard@example.com - name: PANTHEON_PASS value: password_for_email_here # - Additional containers/proxies here. restartPolicy: Always dnsPolicy: Default
Then spin up a VM in Google's Cloud with their CLI, using the above manifest as a template:
gcloud compute instances create pantheon-switchboard-test \ --image container-vm \ --metadata-from-file google-container-manifest=switchboard-manifest.yml \ --zone us-central1-a \ --machine-type f1-micro
After which you should:
- Provision a permanent IP and assign it to the VM (or use the VM's ephemeral IP for testing)
- Set up network rules to only allow connections from a specific range of IPs (like your corporate network or if you use Tableau Online, its IP), and to the ports you specified in your manifest, and optionally,
- Route a domain to the VM's IP.
Once wired up, you should be able to connect to your Pantheon databases using the PROXY_DB_UN, PROXY_DB_PW, and host port specified in your manifest, along with the IP (or domain) you configured in your Google Cloud console.