This document assumes you are already using or used or aware of the base set up from Timo’s Extension write back to MYSQL. You can use the same XAMPP or your web server and PHP (which is used to INSERT/Writeback to Snowflake DB).
Step 1: Create Role in Snowflake
Create a Role That would have access to INSERT Operation to the Database/Schema/Table Object. The same Role would be used in the PHP properties
Step 2: Assign the Role to Database Object
As mentioned in MYSQL blog create a Table in Snowflake under a Database for example under Sales DataBase and name it Writeback and just as screenshot above you can give the option to give Writeback to any Table under Sales Database like did above or you could just select the Table “Tableauinsert” only and give the permission only on that Table (which is recommended). I have a created a new role called Writeback just for the purpose that users who belong to that this Role can do Insert/Select/Delete operations and assigned this Role to Whole DB “Sales”.
Step 3: Download and Install required Snowflake Drivers
- PHP – I installed a separate PHP version 7.2.11. You can just use your existing PHP version as well which comes default with XAMPP, but make sure you have PHP version 7.1 or greater for Snowflake ODBC Drivers compatibility
- Next, you do need Snowflake PDO drivers that need to be installed (download this from git https://github.com/snowflakedb/pdo_snowflake)
- Next configure your PHP.INI file to add the extension=pdo_snowflake.so
- If you don’t have .ini file or having issues with adding the PDO extension you can still execute the .php file with below command . $PHP_HOME/bin/php -dextension=modules/pdo_snowflake.so INSERTDB.php
- Note that INSERTDB is my PHP file and all the syntax are the same
- Now coming to the PHP file itself everything stays the same expect the Snowflake PDO connector needs to be called and few other syntaxes like a warehouse to use etc. Note only the blue highlighted code is modified for snowflake (PDO connection and specify the which warehouse to use to run the insert operation)
PHP File Content , You do need a Warehouse -Snowflake computing Node to run INSERT operations( I am including only the info that is needed for Snowflake change, everything else remains the same from MSQL blog)
$account = “demo130”;
$user = “rmakkena”;
$password = “xxxxxx”;
$warehouse = “RITHESHDEMO”;
$dbh = new PDO(“snowflake:account=$account;insecure_mode=true”, $user, $password);
$sth = $dbh->query(“use warehouse RITHESHDEMO”);
Step 4: Follow the instructions from Timo’s blog post
Step 5: Update your MarkSelection JS file to refer to this PHP file created above.
Step 6: Follow the same steps from Timo’s original blog to test the insert records update from Tableau Dashboard and you’re done with Tableau writeback to Snowflake
Note: If you are aware of Snowflake concepts, you can use Transient Tables for these Insert Operations or even get creative and use Temporary Tables if the Tableau requirement is What-If analysis for Session only ( Users will do What if Analysis on Tableau by doing writeback-modify in WebEdit mode and once they close the report the loose the session –this is particularly good for added security& enabling all users to do to have access to Writeback and do instant What-if Analysis ).