Tableau Redshift to Snowflake Migration Script.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import zipfile\n",
    "import xml.etree.ElementTree as ET"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ACCOUNT_NAME = '<ACCOUNT_NAME>'\n",
    "USER_NAME = '<USER_NAME>'\n",
    "DB_NAME = '<DB_NAME>'\n",
    "WAREHOUSE = '<WAREHOUSE>'\n",
    "SCHEMA = '<SCHEMA>'\n",
    "ROLE_NAME = '<ROLE_NAME>'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "packaged_workbook_path = os.path.abspath('My Workbook.twbx')\n",
    "file_ext = f'.{packaged_workbook_path.split(\".\")[-1]}'\n",
    "extract_dir = os.path.abspath(f'snowflake_migration_{os.path.basename(packaged_workbook_path).strip(file_ext)}/')\n",
    "packaged_workbook_path, extract_dir"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, unpack your packaged (*.tbwx) workbook by renaming and unzipping the file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def unpack(packaged_workbook_path, extract_dir):\n",
    "    \n",
    "    packaged_workbook_path = os.path.abspath(packaged_workbook_path)\n",
    "    file_ext = f'.{packaged_workbook_path.split(\".\")[-1]}'\n",
    "    zip_file_path = packaged_workbook_path.replace(file_ext, '.zip')\n",
    "    \n",
    "    if not os.path.exists(packaged_workbook_path):\n",
    "        raise Exception(f'File {packaged_workbook_path} does not exist')\n",
    "    \n",
    "    os.rename(packaged_workbook_path, zip_file_path)\n",
    "    print(f'Renamed {packaged_workbook_path} to {zip_file_path}')\n",
    "    \n",
    "    with zipfile.ZipFile(zip_file_path, 'r') as zip_ref:\n",
    "        zip_ref.extractall(extract_dir)\n",
    "    \n",
    "    print(f'Unzipped {zip_file_path} to {extract_dir}')\n",
    "    \n",
    "    file_ext_wb = file_ext.strip('x')\n",
    "    \n",
    "    file_path_wb = packaged_workbook_path.replace(file_ext, file_ext_wb)\n",
    "    file_path_wb = os.path.join(extract_dir, os.path.basename(file_path_wb))\n",
    "    \n",
    "    return file_path_wb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path_wb = unpack(packaged_workbook_path, extract_dir)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then we parse the xml tree of the unpacked workbook to find the named connections and relation section and substitute the relevant Redshift sections with our Snowflake equivalents:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def migrate_to_snowflake(workbook_book_path):\n",
    "    \n",
    "    tree = ET.parse(workbook_book_path)\n",
    "    root = tree.getroot()\n",
    "     for ds in root.findall('.//datasource'):\n",
    "#         print([(k,v) for k,v in ds.items()])\n",
    "        if ds.get('hasconnection') or ds.get('hasconnection') is None:\n",
    "#             print('Child: ', [c.tag for c in ds.getchildren()], '\\n')\n",
    "            for nm in ds.findall('.//named-connection'):\n",
    "        \n",
    "        \n",
    "    for ds in root.findall('.//datasource'):\n",
    "    \n",
    "        if ds.get('hasconnection') or ds.get('hasconnection') is None:\n",
    "            \n",
    "            for nm in ds.findall('.//named-connection'):\n",
    "    \n",
    "                print(nm.tag, nm.get('name'), nm.get('caption'))\n",
    "                nm.set('caption', f'{ACCOUNT_NAME}.snowflakecomputing.com')\n",
    "                nm.set('name', nm.get('name').replace('redshift', 'snowflake'))\n",
    "                print('>>', nm.tag, nm.get('name'), nm.get('caption'))\n",
    "\n",
    "                for cn in nm.getchildren():\n",
    "                    print('\\t', cn.tag, cn.get('class'), cn.get('server'))\n",
    "                    cn.set('class', 'snowflake')\n",
    "                    cn.set('schema', f'{SCHEMA}')\n",
    "                    cn.set('dbname', f'{DB_NAME}')\n",
    "                    cn.set('server', f'{ACCOUNT_NAME}.snowflakecomputing.com')\n",
    "                    cn.set('service', f'{ROLE_NAME}')\n",
    "                    cn.set('username', f'{USER_NAME}')\n",
    "                    cn.set('warehouse', f'{WAREHOUSE}')\n",
    "                    cn.set('port', '')\n",
    "                    print('\\t>>', cn.tag, cn.get('class'), cn.get('server'))\n",
    "                    print('\\n')\n",
    "\n",
    "            for rel in ds.iter('relation'):\n",
    "                if rel.get('connection') is not None:\n",
    "                    print(rel.tag, rel.get('connection'))\n",
    "                    rel.set('connection', rel.get('connection').replace('redshift', 'snowflake'))\n",
    "                    print('>>', rel.tag, rel.get('connection'))\n",
    "                    print('\\n')\n",
    "\n",
    "    return tree"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tree = migrate_to_snowflake(file_path_wb)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lastly, save the updated XML to a new file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def save_migrated_workbook(tree, file_path_wb):\n",
    "    file_ext_wb = f'.{file_path_wb.split(\".\")[-1]}'\n",
    "    new_path = file_path_wb.replace(file_ext_wb, f'_converted{file_ext_wb}')\n",
    "    if not os.path.exists(new_path):\n",
    "        tree.write(new_path)\n",
    "        print(f'Migrated {file_path_wb} to {new_path}')\n",
    "    else:\n",
    "        print(f'{new_path} already exists!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "save_migrated_workbook(tree, file_path_wb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}